Compare Excel in Java
To compare two excel files we created a struts project in which two excel files can be uploaded and after comparing them result on a pdf file is generated which is available for download.
To read the excel files Apache POI is used and for pdf generation iText library is used.
Following is the process:
FileUploadForm.java:
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.yusata.actions;
import javax.servlet.http.HttpServletRequest;
import org.apache.struts.action.ActionErrors;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionMessage;
import org.apache.struts.upload.FormFile;
/**
*
* @author Mighty Heart
*/
public class FileUploadForm extends ActionForm {
private FormFile file1;
private FormFile file2;
private String message;
public FileUploadForm() {
super();
}
public FormFile getFile1() {
return file1;
}
public void setFile1(FormFile file1) {
this.file1 = file1;
}
public FormFile getFile2() {
return file2;
}
public void setFile2(FormFile file2) {
this.file2 = file2;
}
/**
* @return the message
*/
public String getMessage() {
return message;
}
/**
* @param message the message to set
*/
public void setMessage(String message) {
this.message = message;
}
@Override
public ActionErrors validate(ActionMapping mapping, HttpServletRequest request) {
ActionErrors errors = new ActionErrors();
boolean isFileSize1 = false;
boolean isFileSize2 = false;
isFileSize1 = file1.getFileSize() > 0;
isFileSize2 = file2.getFileSize() > 0;
System.out.println("File Size is : " + isFileSize1);
System.out.println("File Size is : " + isFileSize2);
if (!isFileSize1) {
errors.add("file1", new ActionMessage("error.file.required"));
} else if (!file1.getContentType().equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {
errors.add("file1", new ActionMessage("error.file.type"));
}
if (!isFileSize2) {
errors.add("file2", new ActionMessage("error.file.required"));
} else if (!file1.getContentType().equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {
errors.add("file2", new ActionMessage("error.file.type"));
}
/**
* If the file size is greater than 512MB, show error.
*/
if (file1.getFileSize() > 524288000 || file2.getFileSize() > 524288000) {
errors.add("file", new ActionMessage("error.file.size"));
}
return errors;
}
}
compareSheetAction.java
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.yusata.actions;
import com.yusata.dao.CompareExcelDAO;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.LinkedHashMap;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;
import org.apache.struts.upload.FormFile;
/**
*
* @author Mighty Heart
*/
public class compareSheetAction extends DispatchAction {
HttpSession session = null;
public ActionForward uploadFileAction(ActionMapping mapping, ActionForm form, HttpServletRequest request,
HttpServletResponse response) throws FileNotFoundException, IOException, Exception {
session = request.getSession();
FileUploadForm uploadForm = (FileUploadForm) form;
FileOutputStream outputStream1 = null;
FileOutputStream outputStream2 = null;
FormFile formFile1 = null;
FormFile formFile2 = null;
InputStream in1 = null;
InputStream in2 = null;
String path = null;
String path1 = null;
String path2 = null;
try {
formFile1 = uploadForm.getFile1();
formFile2 = uploadForm.getFile2();
path = getServlet().getServletContext().getRealPath("uploadedFile") + "\\";
path1 = path + formFile1.getFileName();
path2 = path + formFile2.getFileName();
File f1 = new File(path1);
File f2 = new File(path2);
if (f1.exists()) {
f1.delete();
} else {
f1.createNewFile();
}
if (f2.exists()) {
f2.delete();
} else {
f2.createNewFile();
}
System.out.println("Path Is: " + path1);
System.out.println("Path Is: " + path2);
outputStream1 = new FileOutputStream(f1);
outputStream2 = new FileOutputStream(f2);
in1 = formFile1.getInputStream();
int charInInt1;
while ((charInInt1 = in1.read()) != -1) {
outputStream1.write((char) charInInt1);
}
in2 = formFile2.getInputStream();
int charInInt2;
while ((charInInt2 = in2.read()) != -1) {
outputStream2.write((char) charInInt2);
}
} catch (Exception e) {
System.out.println("Exception Occured :-" + e.getMessage());
} finally {
outputStream1.close();
outputStream1.flush();
outputStream2.close();
outputStream2.flush();
in1.close();
in2.close();
}
uploadForm.setMessage("The file " + formFile1.getFileName() + " & " + formFile2.getFileName() + " is uploaded successfully.");
CompareExcelDAO mapDao = new CompareExcelDAO();
LinkedHashMap dataHolder_sheet1 = mapDao.readExcelFile(session, path1, 1);
LinkedHashMap dataHolder_sheet2 = mapDao.readExcelFile(session, path2, 2);
ServletOutputStream op = response.getOutputStream();
response.setContentType("APPLICATION/pdf");
response.setHeader("Content-Disposition", "attachment; filename=Result.pdf");
op.write(mapDao.compareCellData(path, formFile1.getFileName().toString(), formFile2.getFileName().toString(),
dataHolder_sheet1, dataHolder_sheet2).toByteArray());
return null;
}
}
CompareExcelDAO.java:
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.yusata.dao;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.FontFactory;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Iterator;
import java.util.LinkedHashMap;
import javax.servlet.http.HttpSession;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author yusata26
*/
public class CompareExcelDAO {
Connection con = null;
Statement stmt = null;
boolean addPDFTable = false;
String[] SheetNames1;
String[] SheetNames2;
public static final String[] TYPES = {"Number Type", "String Type", "Formula Type", "Blank Type", "Boolean Type", "Error type"};
/**
* To get column names, from column indexes, as per standard excel
* notations.
*/
public static final String[] ExcelColumns = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O",
"P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI",
"AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA",
"BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS",
"BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM",
"CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG",
"DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ"};
public LinkedHashMap readExcelFile(HttpSession session, String fileName, int file) {
LinkedHashMap multipleSheetData = new LinkedHashMap();
LinkedHashMap cellData;
int NumberOfSheets;
try {
FileInputStream myInput = new FileInputStream(fileName);
XSSFWorkbook myWorkBook = new XSSFWorkbook(myInput);
NumberOfSheets = myWorkBook.getNumberOfSheets();
if (file == 1) {
SheetNames1 = new String[NumberOfSheets];
} else {
SheetNames2 = new String[NumberOfSheets];
}
for (int i = 0; i < NumberOfSheets; i++) {
if (file == 1) {
SheetNames1[i] = myWorkBook.getSheetName(i);
} else {
SheetNames2[i] = myWorkBook.getSheetName(i);
}
cellData = new LinkedHashMap();
XSSFSheet mySheet = myWorkBook.getSheetAt(i);
Iterator rowIter = mySheet.rowIterator();
while (rowIter.hasNext()) {
XSSFRow myRow = (XSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
while (cellIter.hasNext()) {
XSSFCell myCell = (XSSFCell) cellIter.next();
cellData.put(ExcelColumns[myCell.getColumnIndex()] + (myRow.getRowNum() + 1), myCell);
}
}
multipleSheetData.put(i + "", cellData);
}
} catch (Exception e) {
System.out.println("Exception. " + e.getMessage());
}
return multipleSheetData;
}
public PdfPTable createFirstTable(String[][] statusSheet) throws DocumentException {
PdfPTable table;
table = new PdfPTable(5);
table.setWidths(new int[]{1, 2, 2, 3, 3});
table.setWidthPercentage(100);
System.out.println("About to create pdf. ");
PdfPCell cell;
cell = new PdfPCell(new Phrase(statusSheet[0][0], FontFactory.getFont(FontFactory.TIMES_BOLD, 11)));
table.addCell(cell);
cell = new PdfPCell(new Phrase(statusSheet[0][1], FontFactory.getFont(FontFactory.TIMES_BOLD, 11)));
table.addCell(cell);
cell = new PdfPCell(new Phrase(statusSheet[0][2], FontFactory.getFont(FontFactory.TIMES_BOLD, 11)));
table.addCell(cell);
cell = new PdfPCell(new Phrase(statusSheet[0][3], FontFactory.getFont(FontFactory.TIMES_BOLD, 11)));
table.addCell(cell);
cell = new PdfPCell(new Phrase(statusSheet[0][4], FontFactory.getFont(FontFactory.TIMES_BOLD, 11)));
table.addCell(cell);
for (int itr = 1; itr < statusSheet.length; itr++) {
if (statusSheet[itr][0] != null) {
addPDFTable = true;
cell = new PdfPCell(new Phrase(statusSheet[itr][0], FontFactory.getFont(FontFactory.TIMES_ROMAN, 10)));
// System.out.println(statusSheet[itr][0]);
// System.out.println(statusSheet[itr][1]);
// System.out.println(statusSheet[itr][2]);
// System.out.println(statusSheet[itr][3]);
// System.out.println(statusSheet[itr][4]);
table.addCell(cell);
cell = new PdfPCell(new Phrase(statusSheet[itr][1], FontFactory.getFont(FontFactory.TIMES_ROMAN, 10)));
table.addCell(cell);
cell = new PdfPCell(new Phrase(statusSheet[itr][2], FontFactory.getFont(FontFactory.TIMES_ROMAN, 10)));
table.addCell(cell);
cell = new PdfPCell(new Phrase("\"" + statusSheet[itr][3] + "\"", FontFactory.getFont(FontFactory.TIMES_ROMAN, 10)));
table.addCell(cell);
cell = new PdfPCell(new Phrase("\"" + statusSheet[itr][4] + "\"", FontFactory.getFont(FontFactory.TIMES_ROMAN, 10)));
table.addCell(cell);
}
}
return table;
}
public ByteArrayOutputStream compareCellData(String path, String file1, String file2, LinkedHashMap dataHolder_sheet1,
LinkedHashMap dataHolder_sheet2) throws Exception {
ByteArrayOutputStream BA = new ByteArrayOutputStream();
String statusSheet[][];
LinkedHashMap cellDataLHM1;
LinkedHashMap cellDataLHM2;
SimpleDateFormat formatter = new SimpleDateFormat("MMM dd, yyyy hh:mm:ss a");
boolean matchFlag = true;
PdfPTable tbl;
System.out.println("******* In Write PDF *******");
Document document = new Document(PageSize.A4);
PdfWriter.getInstance(document, BA);
document.open();
// Image image = Image.getInstance(path + "\\pru.PNG");
// image.setAlignment(Element.ALIGN_LEFT);
// document.add(image);
Paragraph p1 = new Paragraph();
Paragraph p4 = new Paragraph();
Paragraph p2;
Paragraph p3;
p4.add(new Phrase("Comparison Report", FontFactory.getFont(FontFactory.TIMES_BOLD, 20)));
p4.setAlignment(Element.ALIGN_CENTER);
document.add(p4);
p4 = new Paragraph();
p4.add("\n");
p4.add("File1: " + file1);
p4.add("\n");
p4.add("File2: " + file2);
p4.add("\n");
p4.add("Generated On: " + formatter.format(Calendar.getInstance().getTime()));
p4.add("\n");
int pdfSrNum = 1;
try {
for (Object keyForSheets : dataHolder_sheet1.keySet()) {
cellDataLHM1 = (LinkedHashMap) dataHolder_sheet1.get(keyForSheets.toString());
cellDataLHM2 = (LinkedHashMap) dataHolder_sheet2.get(keyForSheets.toString());
statusSheet = new String[cellDataLHM1.size()][5];
statusSheet[0][0] = "Sr.No.";
statusSheet[0][1] = "Status";
statusSheet[0][2] = "Location";
statusSheet[0][3] = "Value1";
statusSheet[0][4] = "Value2";
pdfSrNum = 1;
for (Object key : cellDataLHM1.keySet().toArray()) {
switch (((XSSFCell) cellDataLHM1.get(key.toString())).getCellType()) {
case Cell.CELL_TYPE_STRING:
try {
// if (!((((XSSFCell) cellDataLHM1.get(key.toString())).getStringCellValue()).toString().trim()).equals((((XSSFCell)
cellDataLHM2.get(key.toString())).getStringCellValue()).toString().trim())) {
if (!((((XSSFCell) cellDataLHM1.get(key.toString())).getStringCellValue()).toString()).equals((((XSSFCell)
cellDataLHM2.get(key.toString())).getStringCellValue()).toString())) {
if (!((((XSSFCell) cellDataLHM1.get(key.toString())).getStringCellValue()).toString()).contains("Run Date:")) {
statusSheet[pdfSrNum][0] = Integer.toString(pdfSrNum);
statusSheet[pdfSrNum][1] = "Mismatch";
// statusSheet[pdfSrNum][2] = "[" + key.toString().replace("####", ",") + "]";
statusSheet[pdfSrNum][2] = key.toString();
statusSheet[pdfSrNum][3] = cellDataLHM1.get(key.toString()).toString();
statusSheet[pdfSrNum][4] = cellDataLHM2.get(key.toString()).toString();
pdfSrNum++;
}
}
} catch (Exception e) {
System.out.println("Exception Found. *********" + e.getMessage());
}
break;
case Cell.CELL_TYPE_NUMERIC:
try {
if (DateUtil.isCellDateFormatted(((XSSFCell) cellDataLHM1.get(key.toString())))) {
if (!(((XSSFCell) cellDataLHM1.get(key.toString())).getDateCellValue()).equals(((XSSFCell) cellDataLHM2.get(key.toString())).getDateCellValue())) {
// System.out.println("not match");
statusSheet[pdfSrNum][0] = Integer.toString(pdfSrNum);
statusSheet[pdfSrNum][1] = "Mismatch";
// statusSheet[pdfSrNum][2] = "[" + key.toString().replace("####", ",") + "]";
statusSheet[pdfSrNum][2] = key.toString();
statusSheet[pdfSrNum][3] = cellDataLHM1.get(key.toString()).toString();
statusSheet[pdfSrNum][4] = cellDataLHM2.get(key.toString()).toString();
pdfSrNum++;
// System.out.println("mismatch at " + key.toString());
}
//CellArray.add(myCell.getDateCellValue());
} else {
if (((XSSFCell) cellDataLHM1.get(key.toString())).getNumericCellValue() != ((XSSFCell) cellDataLHM2.get(key.toString())).getNumericCellValue()) {
// System.out.println("not match");
statusSheet[pdfSrNum][0] = Integer.toString(pdfSrNum);
statusSheet[pdfSrNum][1] = "Mismatch";
// statusSheet[pdfSrNum][2] = "[" + key.toString().replace("####", ",") + "]";
statusSheet[pdfSrNum][2] = key.toString();
statusSheet[pdfSrNum][3] = cellDataLHM1.get(key.toString()).toString();
statusSheet[pdfSrNum][4] = cellDataLHM2.get(key.toString()).toString();
pdfSrNum++;
// System.out.println("mismatch at " + key.toString());
}
}
} catch (Exception e) {
System.out.println("Exception Found. *********" + e.getMessage());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
try {
if (((XSSFCell) cellDataLHM1.get(key.toString())).getNumericCellValue() != ((XSSFCell) cellDataLHM2.get(key.toString())).getNumericCellValue()) {
// System.out.println("not match");
statusSheet[pdfSrNum][0] = Integer.toString(pdfSrNum);
statusSheet[pdfSrNum][1] = "Mismatch";
// statusSheet[pdfSrNum][2] = "[" + key.toString().replace("####", ",") + "]";
statusSheet[pdfSrNum][2] = key.toString();
statusSheet[pdfSrNum][3] = cellDataLHM1.get(key.toString()).toString();
statusSheet[pdfSrNum][4] = cellDataLHM2.get(key.toString()).toString();
pdfSrNum++;
// System.out.println("mismatch at " + key.toString());
}
} catch (Exception e) {
System.out.println("Exception Found. *********" + e.getMessage());
}
break;
default:
//cellData.put(myRow.getRowNum() + "####" + myCell.getColumnIndex(), myCell.getRichStringCellValue());
//cellStoreVector.addElement(myCell.getRichStringCellValue());
}
}
p1.add("\n");
p1.add("Sheet" + (Integer.parseInt(keyForSheets.toString()) + 1) + ": " + SheetNames1[Integer.parseInt(keyForSheets.toString())]);
p1.add("\n");
p1.add("Sheet" + (Integer.parseInt(keyForSheets.toString()) + 1) + ": " + SheetNames2[Integer.parseInt(keyForSheets.toString())]);
tbl = createFirstTable(statusSheet);
if (addPDFTable) {
matchFlag = false;
p1.add("\n");
p2 = new Paragraph();
p2.add(tbl);
p2.setAlignment(Element.ALIGN_CENTER);
p1.add(p2);
} else {
p2 = new Paragraph();
p2.add("\nData is same in both Sheets.\n\n");
p1.add(p2);
}
addPDFTable = false;
}
// }
// }
} catch (Exception E) {
E.printStackTrace();
}
p3 = new Paragraph();
if (matchFlag) {
p3.add("Status: Match");
} else {
p3.add("Status: Mismatch");
}
p3.add("\n");
p3.setAlignment(Element.ALIGN_LEFT);
document.add(p4);
document.add(p3);
document.add(p1);
document.close();
return BA;
}
}
Features:
1. Works for multiple sheets on Excel files.
2. Matches data cell by cell.
3. In Result pdf shows mismatch with locations so easy to identify.
0 comments:
Post a Comment