Rev 13452 | Blame | Compare with Previous | Last modification | View Log | RSS feed
package in.shop2020.support.services;import java.io.ByteArrayOutputStream;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.CreationHelper;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;public class CourierDetailsReportMerger {private static Logger logger = LoggerFactory.getLogger(CourierDetailsReportMerger.class);public List<List<Cell>> getCourierDetailsReportRows(String fileName){List<List<Cell>> rows = new ArrayList<List<Cell>>();try{/** Creating Input Stream**/FileInputStream myInput = new FileInputStream(fileName);/** Create a POIFSFileSystem object**/POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);/** Create a workbook using the File System**/HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);/** Get the first sheet from workbook**/HSSFSheet mySheet = myWorkBook.getSheetAt(0);/** We now need something to iterate through the cells.**/Iterator<Row> rowIter = mySheet.rowIterator();boolean firstRow = false;while(rowIter.hasNext()){HSSFRow myRow = (HSSFRow) rowIter.next();if(!firstRow){firstRow = true;continue;}Iterator<Cell> cellIter = myRow.cellIterator();List<Cell> cells = new ArrayList<Cell>();while(cellIter.hasNext()){HSSFCell myCell = (HSSFCell) cellIter.next();cells.add(myCell);}rows.add(cells);}}catch (Exception e){e.printStackTrace();}return rows;}public ByteArrayOutputStream mergeCourierDetailsReports(Map<Long, String> warehouseIdFileNames, long providerId, boolean isCod){ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();Workbook wb = new HSSFWorkbook();CreationHelper createHelper = wb.getCreationHelper();Sheet sheet = wb.createSheet("new sheet");CellStyle dateCellStyle = wb.createCellStyle();dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yyyy"));CellStyle weightStyle = wb.createCellStyle();weightStyle.setDataFormat(createHelper.createDataFormat().getFormat("0.000"));// Create the header row and put all the titles in it. Rows are 0 based.Row headerRow = sheet.createRow((short)0);headerRow.createCell(0).setCellValue("Sl No");headerRow.createCell(1).setCellValue("AWB No");headerRow.createCell(2).setCellValue("AWB Date");headerRow.createCell(3).setCellValue("Saholic Order Id");headerRow.createCell(4).setCellValue("Name");headerRow.createCell(5).setCellValue("Address 1");headerRow.createCell(6).setCellValue("Address 2");headerRow.createCell(7).setCellValue("City");headerRow.createCell(8).setCellValue("State");headerRow.createCell(9).setCellValue("Pin Code");headerRow.createCell(10).setCellValue("Telephone No 1");headerRow.createCell(11).setCellValue("Telephone No 2");headerRow.createCell(12).setCellValue("Paymode");headerRow.createCell(13).setCellValue("Amount to be Collected");headerRow.createCell(14).setCellValue("Shipment Value");headerRow.createCell(15).setCellValue("Item ID");headerRow.createCell(16).setCellValue("Packet Weight(in Kg)");headerRow.createCell(17).setCellValue("Product Name");headerRow.createCell(18).setCellValue("Pickup Location");headerRow.createCell(19).setCellValue("Customer A/C Code");int serialNo = 0;for(long warehouseId: warehouseIdFileNames.keySet()){List<List<Cell>> rows = getCourierDetailsReportRows(warehouseIdFileNames.get(warehouseId));for(List<Cell> cells: rows){serialNo++;Row contentRow = sheet.createRow((short)serialNo);contentRow.createCell(0).setCellValue(serialNo);contentRow.createCell(1).setCellValue(cells.get(1).getStringCellValue());Cell awbDateCell = contentRow.createCell(2);awbDateCell.setCellValue(cells.get(2).getDateCellValue());awbDateCell.setCellStyle(dateCellStyle);if(cells.get(3).getCellType()==Cell.CELL_TYPE_NUMERIC){contentRow.createCell(3).setCellValue(cells.get(3).getNumericCellValue());}if(cells.get(3).getCellType()==Cell.CELL_TYPE_STRING){contentRow.createCell(3).setCellValue(cells.get(3).getStringCellValue());}contentRow.createCell(4).setCellValue(cells.get(4).getStringCellValue());contentRow.createCell(5).setCellValue(cells.get(5).getStringCellValue());contentRow.createCell(6).setCellValue(cells.get(6).getStringCellValue());contentRow.createCell(7).setCellValue(cells.get(7).getStringCellValue());contentRow.createCell(8).setCellValue(cells.get(8).getStringCellValue());contentRow.createCell(9).setCellValue(cells.get(9).getStringCellValue());contentRow.createCell(10).setCellValue(cells.get(10).getStringCellValue());contentRow.createCell(11).setCellValue("-");contentRow.createCell(12).setCellValue(cells.get(12).getStringCellValue());contentRow.createCell(13).setCellValue(cells.get(13).getNumericCellValue());contentRow.createCell(14).setCellValue(cells.get(14).getNumericCellValue());Cell weightCell = contentRow.createCell(15);weightCell.setCellValue(cells.get(15).getNumericCellValue());weightCell.setCellStyle(weightStyle);if(cells.get(16).getCellType()==Cell.CELL_TYPE_NUMERIC){contentRow.createCell(16).setCellValue(cells.get(16).getNumericCellValue());}if(cells.get(16).getCellType()==Cell.CELL_TYPE_STRING){contentRow.createCell(16).setCellValue(cells.get(16).getStringCellValue());}contentRow.createCell(17).setCellValue(cells.get(17).getStringCellValue());contentRow.createCell(18).setCellValue(cells.get(18).getStringCellValue());}}// Write the workbook to the output streamtry {wb.write(baosXLS);baosXLS.close();} catch (IOException e) {logger.error("Exception while creating the Courier Details report", e);}return baosXLS;}/*** @param args*/public static void main(String[] args) {System.out.println("Hey There");CourierDetailsReportMerger merger = new CourierDetailsReportMerger();try {FileOutputStream f = new FileOutputStream("/home/rajveer/Desktop/temp/courier-details1.xls");Map<Long, String> warehouseIdFileNames = new HashMap<Long, String>();String fName;fName = "/home/rajveer/Desktop/temp/courier-details-prepaid-1-1-2011-12-5.xls";warehouseIdFileNames.put(1L, fName);fName = "/home/rajveer/Desktop/temp/courier-details-prepaid-2-1-2011-12-5.xls";warehouseIdFileNames.put(2L, fName);fName = "/home/rajveer/Desktop/temp/courier-details-prepaid-5-1-2011-12-5.xls";warehouseIdFileNames.put(5L, fName);ByteArrayOutputStream binXLS = merger.mergeCourierDetailsReports(warehouseIdFileNames, 1, true);binXLS.writeTo(f);f.close();} catch (FileNotFoundException e) {logger.error("Error while creating the Courier Details report", e);} catch (IOException e) {logger.error("IO error while writing the Courier Details report", e);}System.out.println("Successfully generated the detailed courier report");}}