Subversion Repositories SmartDukaan

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("Order No");
            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);                
                            contentRow.createCell(3).setCellValue(cells.get(3).getNumericCellValue());
                            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());
                            contentRow.createCell(15).setCellValue(cells.get(15).getNumericCellValue());
                            Cell weightCell = contentRow.createCell(16);
                            weightCell.setCellValue(cells.get(16).getNumericCellValue());
                            weightCell.setCellStyle(weightStyle);
                            contentRow.createCell(17).setCellValue(cells.get(17).getStringCellValue());
                            contentRow.createCell(18).setCellValue(cells.get(18).getStringCellValue());
                            contentRow.createCell(19).setCellValue(cells.get(19).getStringCellValue());
                    }
            }       
                // Write the workbook to the output stream
                try {
                        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");
        }

}