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");

	}

}