https://issues.apache.org/bugzilla/show_bug.cgi?id=57270
Bug ID: 57270
Summary: java.lang.NullPointerException at
org.apache.poi.POIXMLDocument.write(POIXMLDocument.jav
a:201) at
ExcelCompare.WriteExcel.addRow(WriteExcel.java:103)
at
ExcelCompare.MainClassExcelCompare.main(MainClassExcel
Compare.java:122)
Product: POI
Version: 3.11-dev
Hardware: PC
Status: NEW
Severity: blocker
Priority: P2
Component: XSSF
Assignee: [email protected]
Reporter: [email protected]
Created attachment 32230
--> https://issues.apache.org/bugzilla/attachment.cgi?id=32230&action=edit
Demo1.xslx
package ExcelCompare;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* Sample Java program to read and write Excel file in Java using Apache POI
*
*/
public class MainClassExcelCompare {
public static void main(String[] args) {
try {
int temp;
File excel1 = new
File("C://Users/ckothakapax076037/Desktop/Demo1.xlsx");
FileInputStream fis1 = new FileInputStream(excel1);
XSSFWorkbook book1 = new XSSFWorkbook(fis1);
XSSFSheet sheet1 = book1.getSheetAt(0);
//org.apache.poi.ss.usermodel.Workbook book1 =
WorkbookFactory.create(fis1);
// org.apache.poi.ss.usermodel.Sheet sheet1 = book1.getSheetAt(0);
File excel2 = new
File("C://Users/ckothakapax076037/Desktop/Demo2.xlsx");
FileInputStream fis2 = new FileInputStream(excel2);
XSSFWorkbook book2 = new XSSFWorkbook(fis2);
XSSFSheet sheet2 = book2.getSheetAt(0);
// org.apache.poi.ss.usermodel.Workbook book2 =
WorkbookFactory.create(fis2);
// org.apache.poi.ss.usermodel.Sheet sheet2 = book2.getSheetAt(0);
WriteExcel obj1 = new WriteExcel();
obj1.setOutputFile("C://Users/ckothakapax076037/Desktop/Result.xlsx");
//Get iterator to all the rows in current sheet
Iterator<Row> itr1 = sheet1.iterator();
Iterator<Row> itr2 = sheet2.iterator();
// Iterating through all cells row by row
while (itr1.hasNext()&&itr2.hasNext()) {
temp=0;
Row row1 = itr1.next();
Row row2 = itr2.next();
//Get iterator to all cells of current row
Iterator<Cell> cellIterator1 = row1.cellIterator();
Iterator<Cell> cellIterator2 = row2.cellIterator();
CellStyle style = book1.createCellStyle();
style = book1.createCellStyle();
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
while (cellIterator1.hasNext()&&cellIterator2.hasNext()) {
Cell cell1 = cellIterator1.next();
Cell cell2 = cellIterator2.next();
switch (cell1.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell1.getStringCellValue() + "\t");
System.out.print(cell2.getStringCellValue() + "\t");
if(!cell1.getStringCellValue().equalsIgnoreCase(cell2.getStringCellValue()))
{
temp++;
cell1.setCellStyle(style);
}
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell1.getNumericCellValue() + "\t");
System.out.print(cell2.getNumericCellValue() + "\t");
if(cell1.getNumericCellValue()!=cell2.getNumericCellValue())
{
temp++;
cell1.setCellStyle(style);
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell1.getBooleanCellValue() + "\t");
System.out.print(cell2.getBooleanCellValue() + "\t");
break;
case Cell.CELL_TYPE_BLANK:
System.out.print(cell1.getNumericCellValue() + "\t");
System.out.print(cell2.getNumericCellValue() + "\t");
if(cell2.getStringCellValue()!=" ")
{
temp++;
cell1.setCellStyle(style);
}
break;
default:
}
}
System.out.print("\n");
System.out.print("Flag value:"+temp);
System.out.print("\n");
if (temp>=1)
{
obj1.addRow(cellIterator1,cellIterator2);
}
}
book1.close();
fis1.close();
book2.close();
fis2.close();
obj1.closerActivity();
} catch (FileNotFoundException fe) {
fe.printStackTrace();
} catch (IOException ie) {
ie.printStackTrace();
} catch (Exception ee) {
ee.printStackTrace();
}
}
}
/* sub class*/
package ExcelCompare;
import java.io.FileOutputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcel {
private static String OutputFile;
private static XSSFWorkbook myWorkBook = new XSSFWorkbook();
private static XSSFSheet mySheet = myWorkBook.createSheet("Report");
public static int i=0;
public void setOutputFile(String OutputFile1) {
OutputFile = OutputFile1;
}
public void addRow(Iterator<Cell> cellIterator1,Iterator<Cell> cellIterator2)
{
try {
XSSFRow row = mySheet.createRow(i++);
while (cellIterator1.hasNext()) {
int j=0;
Cell cell1 = cellIterator1.next();
switch (cell1.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell1.getStringCellValue() + "\t");
row.createCell(j).setCellValue(cell1.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell1.getStringCellValue() + "\t");
row.createCell(j).setCellValue(cell1.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
System.out.print(cell1.getStringCellValue() + "\t");
row.createCell(j).setCellValue(cell1.getStringCellValue());
break;
default:
System.out.print(cell1.getStringCellValue() + "\t");
row.createCell(j).setCellValue(cell1.getStringCellValue());
}
j++;
}
while (cellIterator2.hasNext()) {
int j=0;
Cell cell2 = cellIterator2.next();
switch (cell2.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell2.getStringCellValue() + "\t");
row.createCell(j).setCellValue(cell2.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell2.getStringCellValue() + "\t");
row.createCell(j).setCellValue(cell2.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
System.out.print(cell2.getStringCellValue() + "\t");
row.createCell(j).setCellValue(cell2.getStringCellValue());
break;
default:
System.out.print(cell2.getStringCellValue() + "\t");
row.createCell(j).setCellValue(cell2.getStringCellValue());
}
j++;
}
FileOutputStream out = new FileOutputStream(OutputFile);
System.out.print("\n");
myWorkBook.write(out);
out.close();
myWorkBook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public void closerActivity()
{
try {
System.out.println(" Hi i am in close");
} catch (Exception e) {
e.printStackTrace();
}
}
}
I want to compare two excel sheets Demo1.xslx and Demo2.xslx and put result
back in to Result.xslx
I dont want put everything in Result.xslx but only rows which doesnot matched
in both Demo1 and Demo2
I also want to highlight the cells in Demo1 which are not same as Demo2.
Please help me out..
Thanks..
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
