List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setCellFormula
@Override public final void setCellFormula(String formula) throws FormulaParseException, IllegalStateException
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void generarTotal1(HSSFRow rowTotal1, int indexInicioGrupo, int indexFinGrupo, String[] aColsGrue, String[] aCols20, String[] aCols10, HSSFCell cellTerr) { HSSFCell cellTotal1Territorio = rowTotal1.createCell(indexCol); cellTotal1Territorio.setCellValue("Total " + cellTerr.getStringCellValue()); cellTotal1Territorio.setCellStyle(styleTerritorioTotal1); HSSFCell cellTotal1Boleta = rowTotal1.createCell(indexCol + 2); cellTotal1Boleta.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Boleta.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 2) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 2) + indexFinGrupo + ")"); cellTotal1Boleta.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red20g = rowTotal1.createCell(indexCol + 3); cellTotal1Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 3) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 3) + indexFinGrupo + ")"); cellTotal1Red20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red20c = rowTotal1.createCell(indexCol + 4); cellTotal1Red20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 4) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 4) + indexFinGrupo + ")"); cellTotal1Red20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue20g = rowTotal1.createCell(indexCol + 5); cellTotal1Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 5) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 5) + indexFinGrupo + ")"); cellTotal1Blue20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue20c = rowTotal1.createCell(indexCol + 6); cellTotal1Blue20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 6) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 6) + indexFinGrupo + ")"); cellTotal1Blue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green20g = rowTotal1.createCell(indexCol + 7); cellTotal1Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 7) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 7) + indexFinGrupo + ")"); cellTotal1Green20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green20c = rowTotal1.createCell(indexCol + 8); cellTotal1Green20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 8) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 8) + indexFinGrupo + ")"); cellTotal1Green20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres20g = rowTotal1.createCell(indexCol + 9); cellTotal1Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 9) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 9) + indexFinGrupo + ")"); cellTotal1Tres20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres20c = rowTotal1.createCell(indexCol + 10); cellTotal1Tres20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres20c/*from w w w .ja va2 s . com*/ .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 10) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 10) + indexFinGrupo + ")"); cellTotal1Tres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo20g = rowTotal1.createCell(indexCol + 11); cellTotal1Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo20g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 11) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 11) + indexFinGrupo + ")"); cellTotal1Duo20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo20c = rowTotal1.createCell(indexCol + 12); cellTotal1Duo20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo20c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 12) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 12) + indexFinGrupo + ")"); cellTotal1Duo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red10g = rowTotal1.createCell(indexCol + 13); cellTotal1Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 13) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 13) + indexFinGrupo + ")"); cellTotal1Red10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red10c = rowTotal1.createCell(indexCol + 14); cellTotal1Red10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 14) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 14) + indexFinGrupo + ")"); cellTotal1Red10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue10g = rowTotal1.createCell(indexCol + 15); cellTotal1Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 15) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 15) + indexFinGrupo + ")"); cellTotal1Blue10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue10c = rowTotal1.createCell(indexCol + 16); cellTotal1Blue10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 16) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 16) + indexFinGrupo + ")"); cellTotal1Blue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green10g = rowTotal1.createCell(indexCol + 17); cellTotal1Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 17) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 17) + indexFinGrupo + ")"); cellTotal1Green10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green10c = rowTotal1.createCell(indexCol + 18); cellTotal1Green10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 18) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 18) + indexFinGrupo + ")"); cellTotal1Green10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres10g = rowTotal1.createCell(indexCol + 19); cellTotal1Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 19) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 19) + indexFinGrupo + ")"); cellTotal1Tres10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres10c = rowTotal1.createCell(indexCol + 20); cellTotal1Tres10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 20) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 20) + indexFinGrupo + ")"); cellTotal1Tres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo10g = rowTotal1.createCell(indexCol + 21); cellTotal1Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 21) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 21) + indexFinGrupo + ")"); cellTotal1Duo10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo10c = rowTotal1.createCell(indexCol + 22); cellTotal1Duo10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 22) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 22) + indexFinGrupo + ")"); cellTotal1Duo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM20g = rowTotal1.createCell(indexCol + 23); cellTotal1SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM20g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 23) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 23) + indexFinGrupo + ")"); cellTotal1SM20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM20c = rowTotal1.createCell(indexCol + 24); cellTotal1SM20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM20c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 24) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 24) + indexFinGrupo + ")"); cellTotal1SM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM10g = rowTotal1.createCell(indexCol + 25); cellTotal1SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM10g .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 25) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 25) + indexFinGrupo + ")"); cellTotal1SM10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM10c = rowTotal1.createCell(indexCol + 26); cellTotal1SM10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM10c .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 26) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 26) + indexFinGrupo + ")"); cellTotal1SM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SMSoftg = rowTotal1.createCell(indexCol + 27); cellTotal1SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SMSoftg .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 27) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 27) + indexFinGrupo + ")"); cellTotal1SMSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SMSoftc = rowTotal1.createCell(indexCol + 28); cellTotal1SMSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SMSoftc .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 28) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 28) + indexFinGrupo + ")"); cellTotal1SMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K20g = rowTotal1.createCell(indexCol + 29); cellTotal1K20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K20g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 29) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 29) + indexFinGrupo + ")"); cellTotal1K20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K20c = rowTotal1.createCell(indexCol + 30); cellTotal1K20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K20c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 30) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 30) + indexFinGrupo + ")"); cellTotal1K20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K10g = rowTotal1.createCell(indexCol + 31); cellTotal1K10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K10g.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 31) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 31) + indexFinGrupo + ")"); cellTotal1K10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1K10c = rowTotal1.createCell(indexCol + 32); cellTotal1K10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1K10c.setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 32) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 32) + indexFinGrupo + ")"); cellTotal1K10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KSoftg = rowTotal1.createCell(indexCol + 33); cellTotal1KSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KSoftg .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 33) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 33) + indexFinGrupo + ")"); cellTotal1KSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KSoftc = rowTotal1.createCell(indexCol + 34); cellTotal1KSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KSoftc .setCellFormula("SUM(" + CellReference.convertNumToColString(indexCol + 34) + indexInicioGrupo + ":" + CellReference.convertNumToColString(indexCol + 34) + indexFinGrupo + ")"); cellTotal1KSoftc.setCellStyle(styleCantidadAzul); String formTotal1Grue = ""; for (int i = 0; i < aColsGrue.length; i++) { formTotal1Grue += aColsGrue[i] + (indexFinGrupo + 1) + "+"; } formTotal1Grue += "0"; String formTotal120 = ""; for (int i = 0; i < aCols20.length; i++) { formTotal120 += aCols20[i] + (indexFinGrupo + 1) + "+"; } formTotal120 += "0"; String formTotal110 = ""; for (int i = 0; i < aCols10.length; i++) { formTotal110 += aCols10[i] + (indexFinGrupo + 1) + "+"; } formTotal110 += "0"; HSSFCell cellTotal1g = rowTotal1.createCell(indexCol + 35); cellTotal1g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1g.setCellFormula( "(INT((" + formTotal120 + ")/10))+" + "(INT((" + formTotal110 + ")/20)) +(" + formTotal1Grue + ")"); cellTotal1g.setCellStyle(styleCantidadGris); HSSFCell cellTotal1c = rowTotal1.createCell(indexCol + 36); cellTotal1c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1c.setCellFormula("MOD((" + formTotal110 + "),20)+ MOD((" + formTotal120 + "),10)"); cellTotal1c.setCellStyle(styleCantidadGris); HSSFCell cellTotal1ca = rowTotal1.createCell(indexCol + 37); cellTotal1ca.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1ca.setCellFormula( "(INT(" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1) + "/50))"); cellTotal1ca.setCellStyle(styleCantidadNegrita); HSSFCell cellTotal1gr = rowTotal1.createCell(indexCol + 38); cellTotal1gr.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1gr.setCellFormula( "(MOD(" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1) + ",50))"); cellTotal1gr.setCellStyle(styleCantidadNegrita); }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void generarDetalles(HSSFRow row1, int indexFilaActual, ResumenVentasCantadas rv) { String formulaSumGruesa = "("; String formulaSumCaje10 = "((";//"(INT(("; String formulaSumCaje20 = "((";//"(INT(("; HSSFCell cellPRed20g = row1.createCell(indexCol + 3); cellPRed20g.setCellValue(rv.getGruesas706());//rv.getGruesas706() cellPRed20g.setCellStyle(styleCantidad); HSSFCell cellPRed20c = row1.createCell(indexCol + 4); cellPRed20c.setCellValue(rv.getRestocaje706());//rv.getRestocaje706() cellPRed20c.setCellStyle(styleCantidadCaje); indexFilaActual = indexFilaActual + 1; formulaSumGruesa += CellReference.convertNumToColString(indexCol + 3) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 4) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 3) + ","; cols20 += CellReference.convertNumToColString(indexCol + 4) + ","; HSSFCell cellPBlue20g = row1.createCell(indexCol + 5); cellPBlue20g.setCellValue(rv.getGruesas710());//rv.getGruesas710() cellPBlue20g.setCellStyle(styleCantidad); HSSFCell cellPBlue20c = row1.createCell(indexCol + 6); cellPBlue20c.setCellValue(rv.getRestocaje710());//rv.getRestocaje710() cellPBlue20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 5) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 6) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 5) + ","; cols20 += CellReference.convertNumToColString(indexCol + 6) + ","; HSSFCell cellPGreen20g = row1.createCell(indexCol + 7); cellPGreen20g.setCellValue(rv.getGruesas708());//rv.getGruesas708() cellPGreen20g.setCellStyle(styleCantidad); HSSFCell cellPGreen20c = row1.createCell(indexCol + 8); cellPGreen20c.setCellValue(rv.getRestocaje708()); //rv.getRestocaje708() cellPGreen20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 7) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 8) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 7) + ","; cols20 += CellReference.convertNumToColString(indexCol + 8) + ","; HSSFCell cellPTres20g = row1.createCell(indexCol + 9); cellPTres20g.setCellValue(rv.getGruesas704());//rv.getGruesas704() cellPTres20g.setCellStyle(styleCantidad); HSSFCell cellPTres20c = row1.createCell(indexCol + 10); cellPTres20c.setCellValue(rv.getRestocaje704());//rv.getRestocaje704() cellPTres20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 9) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 10) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 9) + ","; cols20 += CellReference.convertNumToColString(indexCol + 10) + ","; HSSFCell cellPDuo20g = row1.createCell(indexCol + 11); cellPDuo20g.setCellValue(rv.getGruesas702());//rv.getGruesas702() cellPDuo20g.setCellStyle(styleCantidad); HSSFCell cellPDuo20c = row1.createCell(indexCol + 12); cellPDuo20c.setCellValue(rv.getRestocaje702());//rv.getRestocaje702() cellPDuo20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 11) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 12) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 11) + ","; cols20 += CellReference.convertNumToColString(indexCol + 12) + ","; HSSFCell cellPRed10g = row1.createCell(indexCol + 13); cellPRed10g.setCellValue(rv.getGruesas705());//rv.getGruesas705() cellPRed10g.setCellStyle(styleCantidad); HSSFCell cellPRed10c = row1.createCell(indexCol + 14); cellPRed10c.setCellValue(rv.getRestocaje705());//rv.getRestocaje705() cellPRed10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 13) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 14) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 13) + ","; cols10 += CellReference.convertNumToColString(indexCol + 14) + ","; HSSFCell cellPBlue10g = row1.createCell(indexCol + 15); cellPBlue10g.setCellValue(rv.getGruesas709());//rv.getGruesas709() cellPBlue10g.setCellStyle(styleCantidad); HSSFCell cellPBlue10c = row1.createCell(indexCol + 16); cellPBlue10c.setCellValue(rv.getRestocaje709());//rv.getRestocaje709() cellPBlue10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 15) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 16) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 15) + ","; cols10 += CellReference.convertNumToColString(indexCol + 16) + ","; HSSFCell cellPGreen10g = row1.createCell(indexCol + 17); cellPGreen10g.setCellValue(rv.getGruesas707());//rv.getGruesas707() cellPGreen10g.setCellStyle(styleCantidad); HSSFCell cellPGreen10c = row1.createCell(indexCol + 18); cellPGreen10c.setCellValue(rv.getRestocaje707());//rv.getRestocaje707() cellPGreen10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 17) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 18) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 17) + ","; cols10 += CellReference.convertNumToColString(indexCol + 18) + ","; HSSFCell cellPTres10g = row1.createCell(indexCol + 19); cellPTres10g.setCellValue(rv.getGruesas703());//rv.getGruesas703() cellPTres10g.setCellStyle(styleCantidad); HSSFCell cellPTres10c = row1.createCell(indexCol + 20); cellPTres10c.setCellValue(rv.getRestocaje703());//rv.getRestocaje703() cellPTres10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 19) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 20) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 19) + ","; cols10 += CellReference.convertNumToColString(indexCol + 20) + ","; HSSFCell cellPDuo10g = row1.createCell(indexCol + 21); cellPDuo10g.setCellValue(rv.getGruesas701());//rv.getGruesas701() cellPDuo10g.setCellStyle(styleCantidad); HSSFCell cellPDuo10c = row1.createCell(indexCol + 22); cellPDuo10c.setCellValue(rv.getRestocaje701());//rv.getRestocaje701() cellPDuo10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 21) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 22) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 21) + ","; cols10 += CellReference.convertNumToColString(indexCol + 22) + ","; HSSFCell cellSM20g = row1.createCell(indexCol + 23); cellSM20g.setCellValue(rv.getGruesas411());//rv.getGruesas413() cellSM20g.setCellStyle(styleCantidad); HSSFCell cellSM20c = row1.createCell(indexCol + 24); cellSM20c.setCellValue(rv.getRestocaje411());//rv.getRestocaje413() cellSM20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 23) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 24) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 23) + ","; cols20 += CellReference.convertNumToColString(indexCol + 24) + ","; HSSFCell cellSM10g = row1.createCell(indexCol + 25); cellSM10g.setCellValue(rv.getGruesas412());//rv.getGruesas412() cellSM10g.setCellStyle(styleCantidad); HSSFCell cellSM10c = row1.createCell(indexCol + 26); cellSM10c.setCellValue(rv.getRestocaje412());//rv.getRestocaje412() cellSM10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 25) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 26) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 25) + ","; cols10 += CellReference.convertNumToColString(indexCol + 26) + ","; HSSFCell cellSMSoftg = row1.createCell(indexCol + 27); cellSMSoftg.setCellValue(rv.getGruesas413());//rv.getGruesas411() cellSMSoftg.setCellStyle(styleCantidad); HSSFCell cellSMSoftc = row1.createCell(indexCol + 28); cellSMSoftc.setCellValue(rv.getRestocaje413());//rv.getRestocaje411() cellSMSoftc.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 27) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 28) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 27) + ","; cols20 += CellReference.convertNumToColString(indexCol + 28) + ","; HSSFCell cellK20g = row1.createCell(indexCol + 29); cellK20g.setCellValue(rv.getGruesas198());//rv.getGruesas198() cellK20g.setCellStyle(styleCantidad); HSSFCell cellK20c = row1.createCell(indexCol + 30); cellK20c.setCellValue(rv.getRestocaje198());//rv.getRestocaje198() cellK20c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 29) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 30) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 29) + ","; cols10 += CellReference.convertNumToColString(indexCol + 30) + ","; HSSFCell cellK10g = row1.createCell(indexCol + 31); cellK10g.setCellValue(rv.getGruesas218());//rv.getGruesas218() cellK10g.setCellStyle(styleCantidad); HSSFCell cellK10c = row1.createCell(indexCol + 32); cellK10c.setCellValue(rv.getRestocaje218());//rv.getRestocaje218() cellK10c.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 31) + indexFilaActual + "+"; formulaSumCaje10 += CellReference.convertNumToColString(indexCol + 32) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 31) + ","; cols10 += CellReference.convertNumToColString(indexCol + 32) + ","; HSSFCell cellKSoftg = row1.createCell(indexCol + 33); cellKSoftg.setCellValue(rv.getGruesas204());//rv.getGruesas218() cellKSoftg.setCellStyle(styleCantidad); HSSFCell cellKSoftc = row1.createCell(indexCol + 34); cellKSoftc.setCellValue(rv.getRestocaje204());//rv.getRestocaje218() cellKSoftc.setCellStyle(styleCantidadCaje); formulaSumGruesa += CellReference.convertNumToColString(indexCol + 33) + indexFilaActual + "+"; formulaSumCaje20 += CellReference.convertNumToColString(indexCol + 34) + indexFilaActual + "+"; colsGrue += CellReference.convertNumToColString(indexCol + 33) + ","; cols20 += CellReference.convertNumToColString(indexCol + 34) + ","; formulaSumGruesa += "0)"; String formulaTotalGruesas = "(INT" + formulaSumCaje10 + "0)/20))+(INT" + formulaSumCaje20 + "0)/10))+" + formulaSumGruesa;//from w w w . j a v a 2 s . com String formulaTotalCaje = "MOD" + formulaSumCaje10 + "0),20)+ MOD" + formulaSumCaje20 + "0),10)"; HSSFCell cellTotalg = row1.createCell(indexCol + 35); cellTotalg.setCellFormula(formulaTotalGruesas); cellTotalg.setCellStyle(styleCantidadGris1); HSSFCell cellTotalc = row1.createCell(indexCol + 36); cellTotalc.setCellFormula(formulaTotalCaje); cellTotalc.setCellStyle(styleCantidadGris1); HSSFCell cellTotalca = row1.createCell(indexCol + 37); cellTotalca.setCellFormula( "(INT(" + CellReference.convertNumToColString(indexCol + 35) + indexFilaActual + "/50))"); cellTotalca.setCellStyle(styleCantidad); HSSFCell cellTotalgr = row1.createCell(indexCol + 38); cellTotalgr.setCellFormula( "(MOD(" + CellReference.convertNumToColString(indexCol + 35) + indexFilaActual + ",50))"); cellTotalgr.setCellStyle(styleCantidad); }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void generarTotalPais(HSSFRow rowTotalPais, CellStyle styleTotal3, HSSFSheet sheet, String[] aIndexTotales, String[] aColsGrue, String[] aCols20, String[] aCols10) { //GeneraTotal1Pais HSSFCell cellTotal1Pais = rowTotalPais.createCell(indexCol); cellTotal1Pais.setCellValue("Total Pas"); cellTotal1Pais.setCellStyle(styleTerritorioTotal1); HSSFCell cellTotal1Boleta = rowTotalPais.createCell(indexCol + 2); cellTotal1Boleta.setCellType(HSSFCell.CELL_TYPE_FORMULA); String formBoletaTotal1Pais = ""; String formRed20Total1Paisg = ""; String formRed20Total1Paisc = ""; String formBlue20Total1Paisg = ""; String formBlue20Total1Paisc = ""; String formGreen20Total1Paisg = ""; String formGreen20Total1Paisc = ""; String formTres20Total1Paisg = ""; String formTres20Total1Paisc = ""; String formDuo20Total1Paisg = ""; String formDuo20Total1Paisc = ""; String formRed10Total1Paisg = ""; String formRed10Total1Paisc = ""; String formBlue10Total1Paisg = ""; String formBlue10Total1Paisc = ""; String formGreen10Total1Paisg = ""; String formGreen10Total1Paisc = ""; String formTres10Total1Paisg = ""; String formTres10Total1Paisc = ""; String formDuo10Total1Paisg = ""; String formDuo10Total1Paisc = ""; String formSM20Total1Paisg = ""; String formSM20Total1Paisc = ""; String formSM10Total1Paisg = ""; String formSM10Total1Paisc = ""; String formSMSoftTotal1Paisg = ""; String formSMSoftTotal1Paisc = ""; String formKY20Total1Paisg = ""; String formKY20Total1Paisc = ""; String formKY10Total1Paisg = ""; String formKY10Total1Paisc = ""; String formKYSoftTotal1Paisg = ""; String formKYSoftTotal1Paisc = ""; String formRed20Total2Paisg = ""; String formBlue20Total2Paisg = ""; String formGreen20Total2Paisg = ""; String formTres20Total2Paisg = ""; String formDuo20Total2Paisg = ""; String formRed10Total2Paisg = ""; String formBlue10Total2Paisg = ""; String formGreen10Total2Paisg = ""; String formTres10Total2Paisg = ""; String formDuo10Total2Paisg = ""; String formSM20Total2Paisg = ""; String formSM10Total2Paisg = ""; String formSMSoftTotal2Paisg = ""; String formKY20Total2Paisg = ""; String formKY10Total2Paisg = ""; String formKYSoftTotal2Paisg = ""; for (int i = 0; i < aIndexTotales.length; i++) { String indiceFila = aIndexTotales[i]; if (indiceFila != null && indiceFila.length() > 0) { formBoletaTotal1Pais += CellReference.convertNumToColString(indexCol + 2) + aIndexTotales[i] + "+"; formRed20Total1Paisg += CellReference.convertNumToColString(indexCol + 3) + aIndexTotales[i] + "+"; formRed20Total1Paisc += CellReference.convertNumToColString(indexCol + 4) + aIndexTotales[i] + "+"; // "(INT("+CellReference.convertNumToColString(indexCol + 4)+aIndexTotales[i]+")/10)"; formBlue20Total1Paisg += CellReference.convertNumToColString(indexCol + 5) + aIndexTotales[i] + "+"; formBlue20Total1Paisc += CellReference.convertNumToColString(indexCol + 6) + aIndexTotales[i] + "+"; formGreen20Total1Paisg += CellReference.convertNumToColString(indexCol + 7) + aIndexTotales[i] + "+"; formGreen20Total1Paisc += CellReference.convertNumToColString(indexCol + 8) + aIndexTotales[i] + "+"; formTres20Total1Paisg += CellReference.convertNumToColString(indexCol + 9) + aIndexTotales[i] + "+"; formTres20Total1Paisc += CellReference.convertNumToColString(indexCol + 10) + aIndexTotales[i] + "+"; formDuo20Total1Paisg += CellReference.convertNumToColString(indexCol + 11) + aIndexTotales[i] + "+"; formDuo20Total1Paisc += CellReference.convertNumToColString(indexCol + 12) + aIndexTotales[i] + "+"; formRed10Total1Paisg += CellReference.convertNumToColString(indexCol + 13) + aIndexTotales[i] + "+"; formRed10Total1Paisc += CellReference.convertNumToColString(indexCol + 14) + aIndexTotales[i] + "+"; // "(INT("+CellReference.convertNumToColString(indexCol + 4)+aIndexTotales[i]+")/10)"; formBlue10Total1Paisg += CellReference.convertNumToColString(indexCol + 15) + aIndexTotales[i] + "+"; formBlue10Total1Paisc += CellReference.convertNumToColString(indexCol + 16) + aIndexTotales[i] + "+"; formGreen10Total1Paisg += CellReference.convertNumToColString(indexCol + 17) + aIndexTotales[i] + "+"; formGreen10Total1Paisc += CellReference.convertNumToColString(indexCol + 18) + aIndexTotales[i] + "+"; formTres10Total1Paisg += CellReference.convertNumToColString(indexCol + 19) + aIndexTotales[i] + "+"; formTres10Total1Paisc += CellReference.convertNumToColString(indexCol + 20) + aIndexTotales[i] + "+"; formDuo10Total1Paisg += CellReference.convertNumToColString(indexCol + 21) + aIndexTotales[i] + "+"; formDuo10Total1Paisc += CellReference.convertNumToColString(indexCol + 22) + aIndexTotales[i] + "+"; formSM20Total1Paisg += CellReference.convertNumToColString(indexCol + 23) + aIndexTotales[i] + "+"; formSM20Total1Paisc += CellReference.convertNumToColString(indexCol + 24) + aIndexTotales[i] + "+"; formSM10Total1Paisg += CellReference.convertNumToColString(indexCol + 25) + aIndexTotales[i] + "+"; formSM10Total1Paisc += CellReference.convertNumToColString(indexCol + 26) + aIndexTotales[i] + "+"; formSMSoftTotal1Paisg += CellReference.convertNumToColString(indexCol + 27) + aIndexTotales[i] + "+"; formSMSoftTotal1Paisc += CellReference.convertNumToColString(indexCol + 28) + aIndexTotales[i] + "+"; formKY20Total1Paisg += CellReference.convertNumToColString(indexCol + 29) + aIndexTotales[i] + "+"; formKY20Total1Paisc += CellReference.convertNumToColString(indexCol + 30) + aIndexTotales[i] + "+"; formKY10Total1Paisg += CellReference.convertNumToColString(indexCol + 31) + aIndexTotales[i] + "+"; formKY10Total1Paisc += CellReference.convertNumToColString(indexCol + 32) + aIndexTotales[i] + "+"; formKYSoftTotal1Paisg += CellReference.convertNumToColString(indexCol + 33) + aIndexTotales[i] + "+"; formKYSoftTotal1Paisc += CellReference.convertNumToColString(indexCol + 34) + aIndexTotales[i] + "+"; }/*from w w w . j a va 2 s. c o m*/ } System.out.println("Formula Boletas 1 : " + formBoletaTotal1Pais); formRed20Total1Paisg += "0"; formRed20Total1Paisc += "0"; formRed20Total2Paisg = "(" + formRed20Total1Paisg + ")/50"; formRed20Total1Paisg = formRed20Total1Paisg + "+(INT((" + formRed20Total1Paisc + ")/10))"; formRed20Total1Paisc = "+(MOD((" + formRed20Total1Paisc + "),10))"; formBlue20Total1Paisg += "0"; formBlue20Total1Paisc += "0"; formBlue20Total2Paisg = "(" + formBlue20Total1Paisg + ")/50"; formBlue20Total1Paisg = formBlue20Total1Paisg + "+(INT((" + formBlue20Total1Paisc + ")/10))"; formBlue20Total1Paisc = "+(MOD((" + formBlue20Total1Paisc + "),10))"; formGreen20Total1Paisg += "0"; formGreen20Total1Paisc += "0"; formGreen20Total2Paisg = "(" + formGreen20Total1Paisg + ")/50"; formGreen20Total1Paisg = formGreen20Total1Paisg + "+(INT((" + formGreen20Total1Paisc + ")/10))"; formGreen20Total1Paisc = "+(MOD((" + formGreen20Total1Paisc + "),10))"; formTres20Total1Paisg += "0"; formTres20Total1Paisc += "0"; formTres20Total2Paisg = "(" + formTres20Total1Paisg + ")/50"; formTres20Total1Paisg = formTres20Total1Paisg + "+(INT((" + formTres20Total1Paisc + ")/10))"; formTres20Total1Paisc = "+(MOD((" + formTres20Total1Paisc + "),10))"; formDuo20Total1Paisg += "0"; formDuo20Total1Paisc += "0"; formDuo20Total2Paisg = "(" + formDuo20Total1Paisg + ")/50"; formDuo20Total1Paisg = formDuo20Total1Paisg + "+(INT((" + formDuo20Total1Paisc + ")/10))"; formDuo20Total1Paisc = "+(MOD((" + formDuo20Total1Paisc + "),10))"; formRed10Total1Paisg += "0"; formRed10Total1Paisc += "0"; formRed10Total2Paisg = "(" + formRed10Total1Paisg + ")/50"; formRed10Total1Paisg = formRed10Total1Paisg + "+(INT((" + formRed10Total1Paisc + ")/20))"; formRed10Total1Paisc = "+(MOD((" + formRed10Total1Paisc + "),20))"; formBlue10Total1Paisg += "0"; formBlue10Total1Paisc += "0"; formBlue10Total2Paisg = "(" + formBlue10Total1Paisg + ")/50"; formBlue10Total1Paisg = formBlue10Total1Paisg + "+(INT((" + formBlue10Total1Paisc + ")/20))"; formBlue10Total1Paisc = "+(MOD((" + formBlue10Total1Paisc + "),20))"; formGreen10Total1Paisg += "0"; formGreen10Total1Paisc += "0"; formGreen10Total2Paisg = "(" + formGreen10Total1Paisg + ")/50"; formGreen10Total1Paisg = formGreen10Total1Paisg + "+(INT((" + formGreen10Total1Paisc + ")/20))"; formGreen10Total1Paisc = "+(MOD((" + formGreen10Total1Paisc + "),20))"; formTres10Total1Paisg += "0"; formTres10Total1Paisc += "0"; formTres10Total2Paisg = "(" + formTres10Total1Paisg + ")/50"; formTres10Total1Paisg = formTres10Total1Paisg + "+(INT((" + formTres10Total1Paisc + ")/20))"; formTres10Total1Paisc = "+(MOD((" + formTres10Total1Paisc + "),20))"; formDuo10Total1Paisg += "0"; formDuo10Total1Paisc += "0"; formDuo10Total2Paisg = "(" + formDuo10Total1Paisg + ")/50"; formDuo10Total1Paisg = formDuo10Total1Paisg + "+(INT((" + formDuo10Total1Paisc + ")/20))"; formDuo10Total1Paisc = "+(MOD((" + formDuo10Total1Paisc + "),20))"; formSM20Total1Paisg += "0"; formSM20Total1Paisc += "0"; formSM20Total2Paisg = "(" + formSM20Total1Paisg + ")/50"; formSM20Total1Paisg = formSM20Total1Paisg + "+(INT((" + formSM20Total1Paisc + ")/10))"; formSM20Total1Paisc = "+(MOD((" + formSM20Total1Paisc + "),10))"; formSM10Total1Paisg += "0"; formSM10Total1Paisc += "0"; formSM10Total2Paisg = "(" + formSM10Total1Paisg + ")/50"; formSM10Total1Paisg = formSM10Total1Paisg + "+(INT((" + formSM10Total1Paisc + ")/20))"; formSM10Total1Paisc = "+(MOD((" + formSM10Total1Paisc + "),20))"; formSMSoftTotal1Paisg += "0"; formSMSoftTotal1Paisc += "0"; formSMSoftTotal2Paisg = "(" + formSMSoftTotal1Paisg + ")/50"; formSMSoftTotal1Paisg = formSMSoftTotal1Paisg + "+(INT((" + formSMSoftTotal1Paisc + ")/10))"; formSMSoftTotal1Paisc = "+(MOD((" + formSMSoftTotal1Paisc + "),10))"; formKY20Total1Paisg += "0"; formKY20Total1Paisc += "0"; formKY20Total2Paisg = "(" + formKY20Total1Paisg + ")/50"; formKY20Total1Paisg = formKY20Total1Paisg + "+(INT((" + formKY20Total1Paisc + ")/10))"; formKY20Total1Paisc = "+(MOD((" + formKY20Total1Paisc + "),10))"; formKY10Total1Paisg += "0"; formKY10Total1Paisc += "0"; formKY10Total2Paisg = "(" + formKY10Total1Paisg + ")/50"; formKY10Total1Paisg = formKY10Total1Paisg + "+(INT((" + formKY10Total1Paisc + ")/20))"; formKY10Total1Paisc = "+(MOD((" + formKY10Total1Paisc + "),20))"; formKYSoftTotal1Paisg += "0"; formKYSoftTotal1Paisc += "0"; formKYSoftTotal2Paisg = "(" + formKYSoftTotal1Paisg + ")/50"; formKYSoftTotal1Paisg = formKYSoftTotal1Paisg + "+(INT((" + formKYSoftTotal1Paisc + ")/10))"; formKYSoftTotal1Paisc = "+(MOD((" + formKYSoftTotal1Paisc + "),10))"; formBoletaTotal1Pais += "0"; cellTotal1Boleta.setCellFormula(formBoletaTotal1Pais); cellTotal1Boleta.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red20g = rowTotalPais.createCell(indexCol + 3); cellTotal1Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Red20c = rowTotalPais.createCell(indexCol + 4); cellTotal1Red20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red20g.setCellFormula(formRed20Total1Paisg); cellTotal1Red20g.setCellStyle(styleCantidadAzul); cellTotal1Red20c.setCellFormula(formRed20Total1Paisc); cellTotal1Red20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue20g = rowTotalPais.createCell(indexCol + 5); cellTotal1Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Blue20c = rowTotalPais.createCell(indexCol + 6); cellTotal1Blue20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue20g.setCellFormula(formBlue20Total1Paisg); cellTotal1Blue20g.setCellStyle(styleCantidadAzul); cellTotal1Blue20c.setCellFormula(formBlue20Total1Paisc); cellTotal1Blue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green20g = rowTotalPais.createCell(indexCol + 7); cellTotal1Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Green20c = rowTotalPais.createCell(indexCol + 8); cellTotal1Green20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green20g.setCellFormula(formGreen20Total1Paisg); cellTotal1Green20g.setCellStyle(styleCantidadAzul); cellTotal1Green20c.setCellFormula(formGreen20Total1Paisc); cellTotal1Green20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres20g = rowTotalPais.createCell(indexCol + 9); cellTotal1Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Tres20c = rowTotalPais.createCell(indexCol + 10); cellTotal1Tres20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres20g.setCellFormula(formTres20Total1Paisg); cellTotal1Tres20g.setCellStyle(styleCantidadAzul); cellTotal1Tres20c.setCellFormula(formTres20Total1Paisc); cellTotal1Tres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo20g = rowTotalPais.createCell(indexCol + 11); cellTotal1Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Duo20c = rowTotalPais.createCell(indexCol + 12); cellTotal1Duo20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo20g.setCellFormula(formDuo20Total1Paisg); cellTotal1Duo20g.setCellStyle(styleCantidadAzul); cellTotal1Duo20c.setCellFormula(formDuo20Total1Paisc); cellTotal1Duo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Red10g = rowTotalPais.createCell(indexCol + 13); cellTotal1Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Red10c = rowTotalPais.createCell(indexCol + 14); cellTotal1Red10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Red10g.setCellFormula(formRed10Total1Paisg); cellTotal1Red10g.setCellStyle(styleCantidadAzul); cellTotal1Red10c.setCellFormula(formRed10Total1Paisc); cellTotal1Red10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Blue10g = rowTotalPais.createCell(indexCol + 15); cellTotal1Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Blue10c = rowTotalPais.createCell(indexCol + 16); cellTotal1Blue10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Blue10g.setCellFormula(formBlue10Total1Paisg); cellTotal1Blue10g.setCellStyle(styleCantidadAzul); cellTotal1Blue10c.setCellFormula(formBlue10Total1Paisc); cellTotal1Blue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Green10g = rowTotalPais.createCell(indexCol + 17); cellTotal1Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Green10c = rowTotalPais.createCell(indexCol + 18); cellTotal1Green10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Green10g.setCellFormula(formGreen10Total1Paisg); cellTotal1Green10g.setCellStyle(styleCantidadAzul); cellTotal1Green10c.setCellFormula(formGreen10Total1Paisc); cellTotal1Green10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Tres10g = rowTotalPais.createCell(indexCol + 19); cellTotal1Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Tres10c = rowTotalPais.createCell(indexCol + 20); cellTotal1Tres10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Tres10g.setCellFormula(formTres10Total1Paisg); cellTotal1Tres10g.setCellStyle(styleCantidadAzul); cellTotal1Tres10c.setCellFormula(formTres10Total1Paisc); cellTotal1Tres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1Duo10g = rowTotalPais.createCell(indexCol + 21); cellTotal1Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1Duo10c = rowTotalPais.createCell(indexCol + 22); cellTotal1Duo10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1Duo10g.setCellFormula(formDuo10Total1Paisg); cellTotal1Duo10g.setCellStyle(styleCantidadAzul); cellTotal1Duo10c.setCellFormula(formDuo10Total1Paisc); cellTotal1Duo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM20g = rowTotalPais.createCell(indexCol + 23); cellTotal1SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1SM20c = rowTotalPais.createCell(indexCol + 24); cellTotal1SM20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM20g.setCellFormula(formSM20Total1Paisg); cellTotal1SM20g.setCellStyle(styleCantidadAzul); cellTotal1SM20c.setCellFormula(formSM20Total1Paisc); cellTotal1SM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SM10g = rowTotalPais.createCell(indexCol + 25); cellTotal1SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1SM10c = rowTotalPais.createCell(indexCol + 26); cellTotal1SM10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SM10g.setCellFormula(formSM10Total1Paisg); cellTotal1SM10g.setCellStyle(styleCantidadAzul); cellTotal1SM10c.setCellFormula(formSM10Total1Paisc); cellTotal1SM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1SMSoftg = rowTotalPais.createCell(indexCol + 27); cellTotal1SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1SMSoftc = rowTotalPais.createCell(indexCol + 28); cellTotal1SMSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1SMSoftg.setCellFormula(formSMSoftTotal1Paisg); cellTotal1SMSoftg.setCellStyle(styleCantidadAzul); cellTotal1SMSoftc.setCellFormula(formSMSoftTotal1Paisc); cellTotal1SMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KY20g = rowTotalPais.createCell(indexCol + 29); cellTotal1KY20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1KY20c = rowTotalPais.createCell(indexCol + 30); cellTotal1KY20c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KY20g.setCellFormula(formKY20Total1Paisg); cellTotal1KY20g.setCellStyle(styleCantidadAzul); cellTotal1KY20c.setCellFormula(formKY20Total1Paisc); cellTotal1KY20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KY10g = rowTotalPais.createCell(indexCol + 31); cellTotal1KY10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1KY10c = rowTotalPais.createCell(indexCol + 32); cellTotal1KY10c.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KY10g.setCellFormula(formKY10Total1Paisg); cellTotal1KY10g.setCellStyle(styleCantidadAzul); cellTotal1KY10c.setCellFormula(formKY10Total1Paisc); cellTotal1KY10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal1KYSoftg = rowTotalPais.createCell(indexCol + 33); cellTotal1KYSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); HSSFCell cellTotal1KYSoftc = rowTotalPais.createCell(indexCol + 34); cellTotal1KYSoftc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal1KYSoftg.setCellFormula(formKYSoftTotal1Paisg); cellTotal1KYSoftg.setCellStyle(styleCantidadAzul); cellTotal1KYSoftc.setCellFormula(formKYSoftTotal1Paisc); cellTotal1KYSoftc.setCellStyle(styleCantidadAzul); String formTotalPaisGrue = ""; if (aColsGrue != null) { for (int i = 0; i < aColsGrue.length; i++) { formTotalPaisGrue += aColsGrue[i] + (rowTotalPais.getRowNum() + 1) + "+"; System.out.println("Row pais: " + rowTotalPais.getRowNum() + 1); } formTotalPaisGrue += "0"; } String formTotalPais20 = ""; if (aCols20 != null) { for (int i = 0; i < aCols20.length; i++) { formTotalPais20 += aCols20[i] + (rowTotalPais.getRowNum() + 1) + "+"; } formTotalPais20 += "0"; } String formTotalPais10 = ""; if (aCols10 != null) { for (int i = 0; i < aCols10.length; i++) { formTotalPais10 += aCols10[i] + (rowTotalPais.getRowNum() + 1) + "+"; } formTotalPais10 += "0"; } System.out.println("form: " + formTotalPaisGrue); HSSFCell cellTotalPaisg = rowTotalPais.createCell(indexCol + 35); cellTotalPaisg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotalPaisg.setCellFormula("(INT((" + formTotalPais20 + ")/10))+" + "(INT((" + formTotalPais10 + ")/20)) +(" + formTotalPaisGrue + ")"); cellTotalPaisg.setCellStyle(styleCantidadGris); HSSFCell cellTotalPaisc = rowTotalPais.createCell(indexCol + 36); cellTotalPaisc.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotalPaisc.setCellFormula("MOD((" + formTotalPais10 + "),20)+ MOD((" + formTotalPais20 + "),10)"); cellTotalPaisc.setCellStyle(styleCantidadGris); HSSFCell cellTotalPaisca = rowTotalPais.createCell(indexCol + 37); cellTotalPaisca.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotalPaisca.setCellFormula("(INT(" + CellReference.convertNumToColString(indexCol + 35) + (rowTotalPais.getRowNum() + 1) + "/50))"); cellTotalPaisca.setCellStyle(styleCantidadNegrita); HSSFCell cellTotalPaisgr = rowTotalPais.createCell(indexCol + 38); cellTotalPaisgr.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotalPaisgr.setCellFormula("(MOD(" + CellReference.convertNumToColString(indexCol + 35) + (rowTotalPais.getRowNum() + 1) + ",50))"); cellTotalPaisgr.setCellStyle(styleCantidadNegrita); //GeneraTotal2Pais int indexTotal2Pais = ++indexRow; HSSFRow rowTotal2Pais = sheet.createRow((indexTotal2Pais)); HSSFCell cellTotal2Pais = rowTotal2Pais.createCell(indexCol); cellTotal2Pais.setCellValue("Total Pas - Cajas"); cellTotal2Pais.setCellStyle(styleTerritorioTotal1); HSSFCell cellTotal2PaisBoletas = rowTotal2Pais.createCell(indexCol + 2); cellTotal2PaisBoletas.setCellValue(""); cellTotal2PaisBoletas.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisRed20g = rowTotal2Pais.createCell(indexCol + 3); cellTotal2PaisRed20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisRed20g.setCellFormula(formRed20Total2Paisg); cellTotal2PaisRed20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisRed20c = rowTotal2Pais.createCell(indexCol + 4); cellTotal2PaisRed20c.setCellValue(""); cellTotal2PaisRed20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisBlue20g = rowTotal2Pais.createCell(indexCol + 5); cellTotal2PaisBlue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisBlue20g.setCellFormula(formBlue20Total2Paisg); cellTotal2PaisBlue20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisBlue20c = rowTotal2Pais.createCell(indexCol + 6); cellTotal2PaisBlue20c.setCellValue(""); cellTotal2PaisBlue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisGreen20g = rowTotal2Pais.createCell(indexCol + 7); cellTotal2PaisGreen20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisGreen20g.setCellFormula(formGreen20Total2Paisg); cellTotal2PaisGreen20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisGreen20c = rowTotal2Pais.createCell(indexCol + 8); cellTotal2PaisGreen20c.setCellValue(""); cellTotal2PaisGreen20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisTres20g = rowTotal2Pais.createCell(indexCol + 9); cellTotal2PaisTres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisTres20g.setCellFormula(formTres20Total2Paisg); cellTotal2PaisTres20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisTres20c = rowTotal2Pais.createCell(indexCol + 10); cellTotal2PaisTres20c.setCellValue(""); cellTotal2PaisTres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisDuo20g = rowTotal2Pais.createCell(indexCol + 11); cellTotal2PaisDuo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisDuo20g.setCellFormula(formDuo20Total2Paisg); cellTotal2PaisDuo20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisDuo20c = rowTotal2Pais.createCell(indexCol + 12); cellTotal2PaisDuo20c.setCellValue(""); cellTotal2PaisDuo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisRed10g = rowTotal2Pais.createCell(indexCol + 13); cellTotal2PaisRed10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisRed10g.setCellFormula(formRed10Total2Paisg); cellTotal2PaisRed10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisRed10c = rowTotal2Pais.createCell(indexCol + 14); cellTotal2PaisRed10c.setCellValue(""); cellTotal2PaisRed10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisBlue10g = rowTotal2Pais.createCell(indexCol + 15); cellTotal2PaisBlue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisBlue10g.setCellFormula(formBlue10Total2Paisg); cellTotal2PaisBlue10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisBlue10c = rowTotal2Pais.createCell(indexCol + 16); cellTotal2PaisBlue10c.setCellValue(""); cellTotal2PaisBlue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisGreen10g = rowTotal2Pais.createCell(indexCol + 17); cellTotal2PaisGreen10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisGreen10g.setCellFormula(formGreen10Total2Paisg); cellTotal2PaisGreen10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisGreen10c = rowTotal2Pais.createCell(indexCol + 18); cellTotal2PaisGreen10c.setCellValue(""); cellTotal2PaisGreen10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisTres10g = rowTotal2Pais.createCell(indexCol + 19); cellTotal2PaisTres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisTres10g.setCellFormula(formTres10Total2Paisg); cellTotal2PaisTres10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisTres10c = rowTotal2Pais.createCell(indexCol + 20); cellTotal2PaisTres10c.setCellValue(""); cellTotal2PaisTres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisDuo10g = rowTotal2Pais.createCell(indexCol + 21); cellTotal2PaisDuo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisDuo10g.setCellFormula(formDuo10Total2Paisg); cellTotal2PaisDuo10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisDuo10c = rowTotal2Pais.createCell(indexCol + 22); cellTotal2PaisDuo10c.setCellValue(""); cellTotal2PaisDuo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSM20g = rowTotal2Pais.createCell(indexCol + 23); cellTotal2PaisSM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisSM20g.setCellFormula(formSM20Total2Paisg); cellTotal2PaisSM20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSM20c = rowTotal2Pais.createCell(indexCol + 24); cellTotal2PaisSM20c.setCellValue(""); cellTotal2PaisSM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSM10g = rowTotal2Pais.createCell(indexCol + 25); cellTotal2PaisSM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisSM10g.setCellFormula(formSM10Total2Paisg); cellTotal2PaisSM10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSM10c = rowTotal2Pais.createCell(indexCol + 26); cellTotal2PaisSM10c.setCellValue(""); cellTotal2PaisSM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSMSoftg = rowTotal2Pais.createCell(indexCol + 27); cellTotal2PaisSMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisSMSoftg.setCellFormula(formSMSoftTotal2Paisg); cellTotal2PaisSMSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisSMSoftc = rowTotal2Pais.createCell(indexCol + 28); cellTotal2PaisSMSoftc.setCellValue(""); cellTotal2PaisSMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKY20g = rowTotal2Pais.createCell(indexCol + 29); cellTotal2PaisKY20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisKY20g.setCellFormula(formKY20Total2Paisg); cellTotal2PaisKY20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKY20c = rowTotal2Pais.createCell(indexCol + 30); cellTotal2PaisKY20c.setCellValue(""); cellTotal2PaisKY20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKY10g = rowTotal2Pais.createCell(indexCol + 31); cellTotal2PaisKY10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisKY10g.setCellFormula(formKY10Total2Paisg); cellTotal2PaisKY10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKY10c = rowTotal2Pais.createCell(indexCol + 32); cellTotal2PaisKY10c.setCellValue(""); cellTotal2PaisKY10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKYSoftg = rowTotal2Pais.createCell(indexCol + 33); cellTotal2PaisKYSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2PaisKYSoftg.setCellFormula(formKYSoftTotal2Paisg); cellTotal2PaisKYSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2PaisKYSoftc = rowTotal2Pais.createCell(indexCol + 34); cellTotal2PaisKYSoftc.setCellValue(""); cellTotal2PaisKYSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotalPais2caja = rowTotal2Pais.createCell(indexCol + 37); cellTotalPais2caja.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotalPais2caja.setCellFormula("(INT(" + CellReference.convertNumToColString(indexCol + 35) + (rowTotalPais.getRowNum() + 1) + "/50))"); cellTotalPais2caja.setCellStyle(styleCantidadNegrita); //GeneraTotal3Pais int indexTotal3Pais = ++indexRow; HSSFRow rowTotal3Pais = sheet.createRow((indexTotal3Pais)); HSSFCell cellTotal3Pais = rowTotal3Pais.createCell(indexCol); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 1) + "$" + indexRow)); cellTotal3Pais.setCellValue("Part. De Emb / Pas"); cellTotal3Pais.setCellStyle(styleTerritorioTotal3); HSSFCell cellTotal3Pais1 = rowTotal3Pais.createCell(indexCol + 1); cellTotal3Pais1.setCellStyle(styleTerritorioTotal3); HSSFCell cellTotal3PaisBoletas = rowTotal3Pais.createCell(indexCol + 2); cellTotal3PaisBoletas.setCellValue(""); cellTotal3PaisBoletas.setCellStyle(styleTotal3); HSSFCell cellTotal3Red20g = rowTotal3Pais.createCell(indexCol + 3); cellTotal3Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Red20g.setCellFormula(CellReference.convertNumToColString(indexCol + 3) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Red20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisRed20c = rowTotal3Pais.createCell(indexCol + 4); cellTotal3PaisRed20c.setCellValue(""); cellTotal3PaisRed20c.setCellStyle(styleTotal3); HSSFCell cellTotal3Blue20g = rowTotal3Pais.createCell(indexCol + 5); cellTotal3Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Blue20g.setCellFormula(CellReference.convertNumToColString(indexCol + 5) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Blue20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisBlue20c = rowTotal3Pais.createCell(indexCol + 6); cellTotal3PaisBlue20c.setCellValue(""); cellTotal3PaisBlue20c.setCellStyle(styleTotal3); HSSFCell cellTotal3Green20g = rowTotal3Pais.createCell(indexCol + 7); cellTotal3Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Green20g.setCellFormula(CellReference.convertNumToColString(indexCol + 7) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Green20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisGreen20c = rowTotal3Pais.createCell(indexCol + 8); cellTotal3PaisGreen20c.setCellValue(""); cellTotal3PaisGreen20c.setCellStyle(styleTotal3); HSSFCell cellTotal3Tres20g = rowTotal3Pais.createCell(indexCol + 9); cellTotal3Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Tres20g.setCellFormula(CellReference.convertNumToColString(indexCol + 9) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Tres20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisTres20c = rowTotal3Pais.createCell(indexCol + 10); cellTotal3PaisTres20c.setCellValue(""); cellTotal3PaisTres20c.setCellStyle(styleTotal3); HSSFCell cellTotal3Duo20g = rowTotal3Pais.createCell(indexCol + 11); cellTotal3Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Duo20g.setCellFormula(CellReference.convertNumToColString(indexCol + 11) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Duo20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisDuo20c = rowTotal3Pais.createCell(indexCol + 12); cellTotal3PaisDuo20c.setCellValue(""); cellTotal3PaisDuo20c.setCellStyle(styleTotal3); HSSFCell cellTotal3Red10g = rowTotal3Pais.createCell(indexCol + 13); cellTotal3Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Red10g.setCellFormula(CellReference.convertNumToColString(indexCol + 13) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Red10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisRed10c = rowTotal3Pais.createCell(indexCol + 14); cellTotal3PaisRed10c.setCellValue(""); cellTotal3PaisRed10c.setCellStyle(styleTotal3); HSSFCell cellTotal3Blue10g = rowTotal3Pais.createCell(indexCol + 15); cellTotal3Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Blue10g.setCellFormula(CellReference.convertNumToColString(indexCol + 15) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Blue10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisBlue10c = rowTotal3Pais.createCell(indexCol + 16); cellTotal3PaisBlue10c.setCellValue(""); cellTotal3PaisBlue10c.setCellStyle(styleTotal3); HSSFCell cellTotal3Green10g = rowTotal3Pais.createCell(indexCol + 17); cellTotal3Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Green10g.setCellFormula(CellReference.convertNumToColString(indexCol + 17) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Green10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisGreen10c = rowTotal3Pais.createCell(indexCol + 18); cellTotal3PaisGreen10c.setCellValue(""); cellTotal3PaisGreen10c.setCellStyle(styleTotal3); HSSFCell cellTotal3Tres10g = rowTotal3Pais.createCell(indexCol + 19); cellTotal3Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Tres10g.setCellFormula(CellReference.convertNumToColString(indexCol + 19) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Tres10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisTres10c = rowTotal3Pais.createCell(indexCol + 20); cellTotal3PaisTres10c.setCellValue(""); cellTotal3PaisTres10c.setCellStyle(styleTotal3); HSSFCell cellTotal3Duo10g = rowTotal3Pais.createCell(indexCol + 21); cellTotal3Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Duo10g.setCellFormula(CellReference.convertNumToColString(indexCol + 21) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3Duo10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisDuo10c = rowTotal3Pais.createCell(indexCol + 22); cellTotal3PaisDuo10c.setCellValue(""); cellTotal3PaisDuo10c.setCellStyle(styleTotal3); HSSFCell cellTotal3SM20g = rowTotal3Pais.createCell(indexCol + 23); cellTotal3SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SM20g.setCellFormula(CellReference.convertNumToColString(indexCol + 23) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3SM20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisSM20c = rowTotal3Pais.createCell(indexCol + 24); cellTotal3PaisSM20c.setCellValue(""); cellTotal3PaisSM20c.setCellStyle(styleTotal3); HSSFCell cellTotal3SM10g = rowTotal3Pais.createCell(indexCol + 25); cellTotal3SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SM10g.setCellFormula(CellReference.convertNumToColString(indexCol + 25) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3SM10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisSM10c = rowTotal3Pais.createCell(indexCol + 26); cellTotal3PaisSM10c.setCellValue(""); cellTotal3PaisSM10c.setCellStyle(styleTotal3); HSSFCell cellTotal3SMSoftg = rowTotal3Pais.createCell(indexCol + 27); cellTotal3SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SMSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 27) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3SMSoftg.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisSMSoftc = rowTotal3Pais.createCell(indexCol + 28); cellTotal3PaisSMSoftc.setCellValue(""); cellTotal3PaisSMSoftc.setCellStyle(styleTotal3); HSSFCell cellTotal3KY20g = rowTotal3Pais.createCell(indexCol + 29); cellTotal3KY20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3KY20g.setCellFormula(CellReference.convertNumToColString(indexCol + 29) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3KY20g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisKY20c = rowTotal3Pais.createCell(indexCol + 30); cellTotal3PaisKY20c.setCellValue(""); cellTotal3PaisKY20c.setCellStyle(styleTotal3); HSSFCell cellTotal3KY10g = rowTotal3Pais.createCell(indexCol + 31); cellTotal3KY10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3KY10g.setCellFormula(CellReference.convertNumToColString(indexCol + 31) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3KY10g.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisKY10c = rowTotal3Pais.createCell(indexCol + 32); cellTotal3PaisKY10c.setCellValue(""); cellTotal3PaisKY10c.setCellStyle(styleTotal3); HSSFCell cellTotal3KYSoftg = rowTotal3Pais.createCell(indexCol + 33); cellTotal3KYSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3KYSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 33) + (indexTotal2Pais) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexTotal2Pais)); cellTotal3KYSoftg.setCellStyle(styleTotal3); HSSFCell cellTotal3PaisKYSoftc = rowTotal3Pais.createCell(indexCol + 32); cellTotal3PaisKYSoftc.setCellValue(""); cellTotal3PaisKYSoftc.setCellStyle(styleTotal3); }
From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java
public boolean createTotalCells(List totalCellIndexes, HSSFSheet sheet, HSSFCellStyle[] totalStyle, Short totalStyleHeight, int rowStart, int cellStart, int rowCurrent) { HSSFRow row;/* w ww. j ava 2 s .c om*/ HSSFCell cell; int rowTotal; if (rowCurrent == rowStart) rowCurrent++; rowTotal = rowCurrent - 1; row = sheet.createRow(rowCurrent); row.setHeight(totalStyleHeight); for (Iterator it = totalCellIndexes.iterator(); it.hasNext();) { Integer cellIndex = (Integer) it.next(); cell = row.createCell(cellIndex); String columnLetter = CellReference.convertNumToColString(cellIndex); cell.setCellFormula("SUM(" + columnLetter + (rowStart + 1) + ":" + columnLetter + (rowTotal + 1) + ")"); cell.setCellStyle(totalStyle[cellIndex]); } return true; }
From source file:com.siva.javamultithreading.ExcelUtil.java
public static void copyCell(HSSFWorkbook newWorkbook, HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newWorkbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); }/*from w ww . j a v a2 s.c o m*/ newCell.setCellStyle(newCellStyle); } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
From source file:com.smi.travel.controller.excel.account.AccountReportSummary.java
private void getApReport(HSSFWorkbook wb, List ApNirvana) { UtilityFunction util = new UtilityFunction(); SimpleDateFormat df = new SimpleDateFormat(); df.applyPattern("dd/MM/yyyy"); UtilityExcelFunction excelFunction = new UtilityExcelFunction(); String sheetName = "Sheet1";// name of sheet HSSFSheet sheet = wb.createSheet(sheetName); APNirvana dataheader = new APNirvana(); HSSFDataFormat currency = wb.createDataFormat(); // Set align Text HSSFCellStyle styleC20 = wb.createCellStyle(); styleC20.setAlignment(styleC20.ALIGN_CENTER); HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); if (ApNirvana != null) { dataheader = (APNirvana) ApNirvana.get(0); }//w w w. j a va2 s . co m // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0); HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Change Ap Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Row 2 HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Print On : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(dataheader.getSystemdate()); cell22.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B2:D2")); HSSFCell cell23 = row2.createCell(4); cell23.setCellValue("By : "); cell23.setCellStyle(styleC21); HSSFCell cell24 = row2.createCell(5); cell24.setCellValue(dataheader.getUser()); cell24.setCellStyle(styleC22); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Report of : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(dataheader.getDatefrom()); cell32.setCellStyle(styleC22); if (!"".equalsIgnoreCase(dataheader.getDateto())) { HSSFCell cell33 = row3.createCell(2); cell33.setCellValue("To"); cell33.setCellStyle(styleC20); HSSFCell cell34 = row3.createCell(3); cell34.setCellValue(dataheader.getDateto()); cell34.setCellStyle(styleC22); } // sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); // Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Department : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(dataheader.getDepartmentheader()); cell42.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4")); // Row 5 HSSFRow row5 = sheet.createRow(4); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Page : "); cell51.setCellStyle(styleC21); HSSFCell cell52 = row5.createCell(5); cell52.setCellValue(""); cell52.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row6 = sheet.createRow(8); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("No."); cell61.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue("Ref InvNo"); cell62.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell63 = row6.createCell(2); cell63.setCellValue("Invoice Date"); sheet.autoSizeColumn(2); cell63.setCellStyle(styleC3Center); HSSFCell cell64 = row6.createCell(3); cell64.setCellValue("Pay No."); cell64.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell65 = row6.createCell(4); cell65.setCellValue("Ap Code"); cell65.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell66 = row6.createCell(5); cell66.setCellValue("Invoice Sup"); cell66.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCellStyle styleC3Right = wb.createCellStyle(); styleC3Right.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Right.setAlignment(styleC3Right.ALIGN_RIGHT); styleC3Right.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Right.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Right.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Right.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCell cell67 = row6.createCell(6); cell67.setCellValue("Acc Code"); cell67.setCellStyle(styleC3Right); sheet.autoSizeColumn(6); HSSFCell cell68 = row6.createCell(7); cell68.setCellValue("Cur"); cell68.setCellStyle(styleC3Right); sheet.autoSizeColumn(7); HSSFCell cell69 = row6.createCell(8); cell69.setCellValue("Gross"); cell69.setCellStyle(styleC3Right); sheet.autoSizeColumn(8); HSSFCell cell70 = row6.createCell(9); cell70.setCellValue("Amount"); cell70.setCellStyle(styleC3Right); sheet.autoSizeColumn(9); //Detail of Table int count = 9; HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC22.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); for (int i = 0; i < ApNirvana.size(); i++) { APNirvana data = (APNirvana) ApNirvana.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(i + 1); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getRefinvoiceno()); cell1.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(2); cell2.setCellValue( data.getTransdate() == null ? "" : util.ConvertString(df.format(data.getTransdate()))); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(3); cell3.setCellValue(data.getPayno()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(4); cell4.setCellValue(data.getVendorid()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(5); cell5.setCellValue(data.getVendorname()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(6); cell6.setCellValue(data.getPuraccount1()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(7); cell7.setCellValue(data.getCurrencyid()); cell7.setCellStyle(styleC23); HSSFCell cell8 = row.createCell(8); cell8.setCellValue((data.getVatamt() != null) ? data.getVatamt().doubleValue() : new BigDecimal("0").doubleValue()); cell8.setCellStyle(styleC25); HSSFCell cell9 = row.createCell(9); cell9.setCellValue((data.getBasevatamt() != null) ? data.getBasevatamt().doubleValue() : new BigDecimal("0").doubleValue()); cell9.setCellStyle(styleC25); if (i == (ApNirvana.size() - 1)) { row = sheet.createRow(count + i + 1); for (int k = 0; k < 7; k++) { HSSFCellStyle styleSum = wb.createCellStyle(); styleSum.setAlignment(styleC24.ALIGN_RIGHT); styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCell cellSum = row.createCell(k); if (k == 0) { styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN); } cellSum.setCellStyle(styleSum); } HSSFCellStyle styleSum = wb.createCellStyle(); styleSum.setAlignment(styleSum.ALIGN_RIGHT); styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSum.setBorderRight(HSSFCellStyle.BORDER_THIN); styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleSum.setDataFormat(currency.getFormat("#,##0.00")); String sumGross = "SUM(I" + 10 + ":I" + (count + i + 1) + ")"; String sumAmount = "SUM(J" + 10 + ":J" + (count + i + 1) + ")"; HSSFCell cell5Sum = row.createCell(7); cell5Sum.setCellValue("Total"); cell5Sum.setCellStyle(styleSum); HSSFCell cell6Sum = row.createCell(8); cell6Sum.setCellFormula(sumGross); cell6Sum.setCellStyle(styleSum); HSSFCell cell7Sum = row.createCell(9); cell7Sum.setCellFormula(sumAmount); cell7Sum.setCellStyle(styleSum); } for (int j = 0; j < 13; j++) { sheet.autoSizeColumn(j); } } }
From source file:com.smi.travel.controller.excel.account.AccountReportSummary.java
public void genSaleVatReport(HSSFWorkbook wb, List outputTaxViewList) { String sheetNameWendy = "Wendy"; // name of sheet String sheetNameInbound = "Inbound"; String sheetNameOutbound = "Outbound"; String sheetNameWendyOutbound = "Wendy + Outbound"; HSSFSheet sheetWendy = wb.createSheet(sheetNameWendy); HSSFSheet sheetOutbound = wb.createSheet(sheetNameOutbound); HSSFSheet sheetWendyOutbound = wb.createSheet(sheetNameWendyOutbound); HSSFSheet sheetInbound = wb.createSheet(sheetNameInbound); sheetWendy.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); sheetOutbound.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); sheetWendyOutbound.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); sheetInbound.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); UtilityExcelFunction excelFunction = new UtilityExcelFunction(); // OutputTaxView dataheader = new OutputTaxView(); HSSFFont fontHeader = wb.createFont(); fontHeader.setFontHeightInPoints((short) 15); fontHeader.setFontName("Arial"); fontHeader.setBoldweight(fontHeader.BOLDWEIGHT_BOLD); HSSFFont fontHeaderDetail = wb.createFont(); fontHeaderDetail.setFontName("Arial"); fontHeaderDetail.setFontHeightInPoints((short) 13); HSSFFont fontHeaderDetailTotal = wb.createFont(); fontHeaderDetailTotal.setFontName("Arial"); fontHeaderDetailTotal.setFontHeightInPoints((short) 9); fontHeaderDetailTotal.setBoldweight(fontHeaderDetailTotal.BOLDWEIGHT_BOLD); HSSFFont fontDetail = wb.createFont(); fontDetail.setFontName("Arial"); fontDetail.setFontHeightInPoints((short) 8); HSSFFont fontHeaderTable = wb.createFont(); fontHeaderTable.setFontName("Arial"); fontHeaderTable.setFontHeightInPoints((short) 8); fontHeaderTable.setBoldweight(fontHeaderTable.BOLDWEIGHT_BOLD); HSSFFont symbolcheck = wb.createFont(); symbolcheck.setFontName("Wingdings"); symbolcheck.setFontHeightInPoints((short) 18); HSSFFont symbol = wb.createFont();// ww w.jav a2 s. co m symbol.setFontName("Wingdings"); symbol.setFontHeightInPoints((short) 9); HSSFFont fontTaxNo = wb.createFont(); fontTaxNo.setFontName("Arial"); fontTaxNo.setFontHeightInPoints((short) 11); HSSFDataFormat currency = wb.createDataFormat(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); styleC21.setVerticalAlignment(styleC21.VERTICAL_CENTER); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); styleC22.setVerticalAlignment(styleC22.VERTICAL_CENTER); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); styleC23.setVerticalAlignment(styleC23.VERTICAL_CENTER); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC25.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); styleC25.setFont(fontDetail); styleC25.setVerticalAlignment(styleC25.VERTICAL_CENTER); HSSFCellStyle styleC25CN = wb.createCellStyle(); styleC25CN.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25CN.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25CN.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC25CN.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC25CN.setDataFormat(currency.getFormat("(#,##0.00)")); styleC25CN.setAlignment(styleC25CN.ALIGN_RIGHT); styleC25CN.setFont(fontDetail); styleC25CN.setVerticalAlignment(styleC25CN.VERTICAL_CENTER); HSSFCellStyle styleC26 = wb.createCellStyle(); styleC26.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC26.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC26.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC26.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC26.setDataFormat(currency.getFormat("#,##0")); styleC26.setAlignment(styleC26.ALIGN_CENTER); styleC26.setFont(fontDetail); styleC26.setVerticalAlignment(styleC26.VERTICAL_CENTER); HSSFCellStyle styleC27 = wb.createCellStyle(); styleC27.setAlignment(styleC27.ALIGN_RIGHT); styleC27.setDataFormat(currency.getFormat("#,##0.00")); styleC27.setVerticalAlignment(styleC27.VERTICAL_CENTER); HSSFCellStyle styleC28 = wb.createCellStyle(); styleC28.setAlignment(styleC28.ALIGN_CENTER); styleC28.setDataFormat(currency.getFormat("#,##0")); styleC28.setVerticalAlignment(styleC28.VERTICAL_CENTER); HSSFCellStyle styleC29 = wb.createCellStyle(); styleC29.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC29.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC29.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC29.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC29.setVerticalAlignment(styleC29.VERTICAL_CENTER); HSSFCellStyle styleC30 = wb.createCellStyle(); styleC30.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC30.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC30.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC30.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC30.setAlignment(styleC30.ALIGN_CENTER); styleC30.setVerticalAlignment(styleC30.VERTICAL_CENTER); styleC30.setFont(fontDetail); HSSFCellStyle stylebordertotal = wb.createCellStyle(); stylebordertotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylebordertotal.setVerticalAlignment(stylebordertotal.VERTICAL_CENTER); HSSFCellStyle stylebordertotalleft = wb.createCellStyle(); stylebordertotalleft.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylebordertotalleft.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylebordertotalleft.setVerticalAlignment(stylebordertotalleft.VERTICAL_CENTER); HSSFCellStyle styleTaxNo = wb.createCellStyle(); styleTaxNo.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTaxNo.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTaxNo.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTaxNo.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTaxNo.setDataFormat(currency.getFormat("#,##0")); styleTaxNo.setAlignment(styleTaxNo.ALIGN_CENTER); styleTaxNo.setFont(fontTaxNo); styleTaxNo.setVerticalAlignment(styleTaxNo.VERTICAL_CENTER); // Header Table HSSFCellStyle styleC3 = wb.createCellStyle(); styleC3.setAlignment(styleC3.ALIGN_CENTER); styleC3.setVerticalAlignment(styleC3.VERTICAL_CENTER); styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC3.setFont(fontHeaderTable); styleC3.setWrapText(true); // Header Table HSSFCellStyle styleC6 = wb.createCellStyle(); styleC6.setAlignment(styleC6.ALIGN_LEFT); styleC6.setVerticalAlignment(styleC6.VERTICAL_CENTER); styleC6.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC6.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC6.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC6.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC6.setWrapText(true); styleC6.setFont(fontHeaderDetailTotal); // Header Table HSSFCellStyle styleC4 = wb.createCellStyle(); styleC4.setAlignment(styleC4.ALIGN_RIGHT); styleC4.setFont(fontHeaderDetailTotal); styleC4.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC4.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC4.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC4.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC4.setVerticalAlignment(styleC4.VERTICAL_CENTER); int count = 11; int countW = 11; int countI = 11; int countO = 11; int countWO = 11; int countWTemp = 0; int countITemp = 0; int countOTemp = 0; int countWOTemp = 0; int tempcountW = 0; int tempcountI = 0; int tempcountO = 0; int tempcountWO = 0; int w = 0; int x = 0; int y = 0; int z = 0; int rowSumW = 0; int rowSumI = 0; int rowSumO = 0; int rowSumWO = 0; BigDecimal grossW = new BigDecimal(BigInteger.ZERO); BigDecimal grossO = new BigDecimal(BigInteger.ZERO); BigDecimal grossI = new BigDecimal(BigInteger.ZERO); BigDecimal grossWO = new BigDecimal(BigInteger.ZERO); BigDecimal vatW = new BigDecimal(BigInteger.ZERO); BigDecimal vatO = new BigDecimal(BigInteger.ZERO); BigDecimal vatI = new BigDecimal(BigInteger.ZERO); BigDecimal vatWO = new BigDecimal(BigInteger.ZERO); BigDecimal grossWCN = new BigDecimal(BigInteger.ZERO); BigDecimal grossOCN = new BigDecimal(BigInteger.ZERO); BigDecimal grossICN = new BigDecimal(BigInteger.ZERO); BigDecimal grossWOCN = new BigDecimal(BigInteger.ZERO); BigDecimal vatWCN = new BigDecimal(BigInteger.ZERO); BigDecimal vatOCN = new BigDecimal(BigInteger.ZERO); BigDecimal vatICN = new BigDecimal(BigInteger.ZERO); BigDecimal vatWOCN = new BigDecimal(BigInteger.ZERO); BigDecimal grossWTotal = new BigDecimal(BigInteger.ZERO); BigDecimal grossOTotal = new BigDecimal(BigInteger.ZERO); BigDecimal grossITotal = new BigDecimal(BigInteger.ZERO); BigDecimal grossWOTotal = new BigDecimal(BigInteger.ZERO); BigDecimal vatWTotal = new BigDecimal(BigInteger.ZERO); BigDecimal vatOTotal = new BigDecimal(BigInteger.ZERO); BigDecimal vatITotal = new BigDecimal(BigInteger.ZERO); BigDecimal vatWOTotal = new BigDecimal(BigInteger.ZERO); BigDecimal grossWCNTotal = new BigDecimal(BigInteger.ZERO); BigDecimal grossOCNTotal = new BigDecimal(BigInteger.ZERO); BigDecimal grossICNTotal = new BigDecimal(BigInteger.ZERO); BigDecimal grossWOCNTotal = new BigDecimal(BigInteger.ZERO); BigDecimal vatWCNTotal = new BigDecimal(BigInteger.ZERO); BigDecimal vatOCNTotal = new BigDecimal(BigInteger.ZERO); BigDecimal vatICNTotal = new BigDecimal(BigInteger.ZERO); BigDecimal vatWOCNTotal = new BigDecimal(BigInteger.ZERO); sheetWendy.setColumnWidth(0, 256 * 6); sheetWendy.setColumnWidth(1, 256 * 9); sheetWendy.setColumnWidth(2, 260 * 8); sheetWendy.setColumnWidth(3, 259 * 24); sheetWendy.setColumnWidth(4, 256 * 12); sheetWendy.setColumnWidth(5, 250 * 5); sheetWendy.setColumnWidth(6, 256 * 5); sheetWendy.setColumnWidth(7, 180 * 2); sheetWendy.setColumnWidth(8, 180 * 2); sheetWendy.setColumnWidth(9, 180 * 2); sheetWendy.setColumnWidth(10, 180 * 2); sheetWendy.setColumnWidth(11, 180 * 2); sheetWendy.setColumnWidth(12, 180 * 2); sheetWendy.setColumnWidth(13, 180 * 2); sheetWendy.setColumnWidth(14, 180 * 2); sheetWendy.setColumnWidth(15, 180 * 2); sheetWendy.setColumnWidth(16, 180 * 2); sheetWendy.setColumnWidth(17, 180 * 2); sheetWendy.setColumnWidth(18, 180 * 2); sheetWendy.setColumnWidth(19, 180 * 2); sheetOutbound.setColumnWidth(0, 256 * 6); sheetOutbound.setColumnWidth(1, 256 * 9); sheetOutbound.setColumnWidth(2, 260 * 8); sheetOutbound.setColumnWidth(3, 259 * 24); sheetOutbound.setColumnWidth(4, 256 * 12); sheetOutbound.setColumnWidth(5, 250 * 5); sheetOutbound.setColumnWidth(6, 256 * 5); sheetOutbound.setColumnWidth(7, 180 * 2); sheetOutbound.setColumnWidth(8, 180 * 2); sheetOutbound.setColumnWidth(9, 180 * 2); sheetOutbound.setColumnWidth(10, 180 * 2); sheetOutbound.setColumnWidth(11, 180 * 2); sheetOutbound.setColumnWidth(12, 180 * 2); sheetOutbound.setColumnWidth(13, 180 * 2); sheetOutbound.setColumnWidth(14, 180 * 2); sheetOutbound.setColumnWidth(15, 180 * 2); sheetOutbound.setColumnWidth(16, 180 * 2); sheetOutbound.setColumnWidth(17, 180 * 2); sheetOutbound.setColumnWidth(18, 180 * 2); sheetOutbound.setColumnWidth(19, 180 * 2); sheetWendyOutbound.setColumnWidth(0, 256 * 6); sheetWendyOutbound.setColumnWidth(1, 256 * 9); sheetWendyOutbound.setColumnWidth(2, 260 * 8); sheetWendyOutbound.setColumnWidth(3, 259 * 24); sheetWendyOutbound.setColumnWidth(4, 256 * 12); sheetWendyOutbound.setColumnWidth(5, 250 * 5); sheetWendyOutbound.setColumnWidth(6, 256 * 5); sheetWendyOutbound.setColumnWidth(7, 180 * 2); sheetWendyOutbound.setColumnWidth(8, 180 * 2); sheetWendyOutbound.setColumnWidth(9, 180 * 2); sheetWendyOutbound.setColumnWidth(10, 180 * 2); sheetWendyOutbound.setColumnWidth(11, 180 * 2); sheetWendyOutbound.setColumnWidth(12, 180 * 2); sheetWendyOutbound.setColumnWidth(13, 180 * 2); sheetWendyOutbound.setColumnWidth(14, 180 * 2); sheetWendyOutbound.setColumnWidth(15, 180 * 2); sheetWendyOutbound.setColumnWidth(16, 180 * 2); sheetWendyOutbound.setColumnWidth(17, 180 * 2); sheetWendyOutbound.setColumnWidth(18, 180 * 2); sheetWendyOutbound.setColumnWidth(19, 180 * 2); sheetInbound.setColumnWidth(0, 256 * 6); sheetInbound.setColumnWidth(1, 256 * 9); sheetInbound.setColumnWidth(2, 260 * 8); sheetInbound.setColumnWidth(3, 259 * 24); sheetInbound.setColumnWidth(4, 256 * 12); sheetInbound.setColumnWidth(5, 250 * 5); sheetInbound.setColumnWidth(6, 256 * 5); sheetInbound.setColumnWidth(7, 180 * 2); sheetInbound.setColumnWidth(8, 180 * 2); sheetInbound.setColumnWidth(9, 180 * 2); sheetInbound.setColumnWidth(10, 180 * 2); sheetInbound.setColumnWidth(11, 180 * 2); sheetInbound.setColumnWidth(12, 180 * 2); sheetInbound.setColumnWidth(13, 180 * 2); sheetInbound.setColumnWidth(14, 180 * 2); sheetInbound.setColumnWidth(15, 180 * 2); sheetInbound.setColumnWidth(16, 180 * 2); sheetInbound.setColumnWidth(17, 180 * 2); sheetInbound.setColumnWidth(18, 180 * 2); sheetInbound.setColumnWidth(19, 180 * 2); HSSFCellStyle styleSymbol = wb.createCellStyle(); styleSymbol.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleSymbol.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSymbol.setBorderRight(HSSFCellStyle.BORDER_THIN); styleSymbol.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSymbol.setAlignment(styleSymbol.ALIGN_CENTER); styleSymbol.setVerticalAlignment(styleSymbol.VERTICAL_CENTER); styleSymbol.setFont(symbol); HSSFCellStyle styleSymbolCheck = wb.createCellStyle(); styleSymbolCheck.setFont(symbolcheck); styleSymbolCheck.setAlignment(styleSymbolCheck.ALIGN_CENTER); //Set Header All Page OutputTaxView dataHeader = new OutputTaxView(); if (!outputTaxViewList.isEmpty()) { dataHeader = (OutputTaxView) outputTaxViewList.get(0); } HSSFCellStyle styleC1 = wb.createCellStyle(); styleC1.setAlignment(styleC1.ALIGN_CENTER); int countWendy = dataHeader.getCountWendy(); int countOutbound = dataHeader.getCountOutbound(); int countInbound = dataHeader.getCountInbound(); int countWendyOutbound = dataHeader.getCountWendy() + dataHeader.getCountOutbound(); // set Header Wendy Report (Row 1) HSSFRow row1 = sheetWendy.createRow(0); HSSFCell cell1 = row1.createCell(0); cell1.setCellValue(""); styleC1.setFont(fontHeader); cell1.setCellStyle(styleC1); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("A1:T1")); // Row 2 HSSFRow row2 = sheetWendy.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue(" " + dataHeader.getHeaderMonth() + " " + dataHeader.getHeaderYear()); styleC23.setFont(fontHeaderDetail); cell21.setCellStyle(styleC23); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("A2:T2")); // Row 3 HSSFRow row3 = sheetWendy.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue( "?? .. ? ?"); cell31.setCellStyle(styleC22); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("A3:D3")); HSSFCell cell32 = row3.createCell(5); cell32.setCellValue("?? "); cell32.setCellStyle(styleC21); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("F3:J3")); HSSFCell cell33 = row3.createCell(11); cell33.setCellValue(""); cell33.setCellStyle(styleSymbol); HSSFCell cell34 = row3.createCell(12); cell34.setCellValue(""); cell34.setCellStyle(styleC21); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("M3:O3")); HSSFCell cell35 = row3.createCell(16); cell35.setCellStyle(styleSymbol); HSSFCell cell36 = row3.createCell(17); cell36.setCellStyle(styleSymbol); HSSFCell cell37 = row3.createCell(18); cell37.setCellStyle(styleSymbol); row3.setHeightInPoints((short) 16); HSSFRow rowX = sheetWendy.createRow(3); rowX.setHeightInPoints((short) 2); // Row 4 HSSFRow row4 = sheetWendy.createRow(4); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue( "?? .. ? ?"); cell41.setCellStyle(styleC22); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("A5:D5")); HSSFCell cell42 = row4.createCell(4); cell42.setCellValue("? "); cell42.setCellStyle(styleC21); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("E5:G5")); HSSFCell cell43 = row4.createCell(7); cell43.setCellValue("0"); cell43.setCellStyle(styleTaxNo); HSSFCell cell44 = row4.createCell(8); cell44.setCellValue("1"); cell44.setCellStyle(styleTaxNo); HSSFCell cell45 = row4.createCell(9); cell45.setCellValue("0"); cell45.setCellStyle(styleTaxNo); HSSFCell cell46 = row4.createCell(10); cell46.setCellValue("5"); cell46.setCellStyle(styleTaxNo); HSSFCell cell47 = row4.createCell(11); cell47.setCellValue("5"); cell47.setCellStyle(styleTaxNo); HSSFCell cell48 = row4.createCell(12); cell48.setCellValue("2"); cell48.setCellStyle(styleTaxNo); HSSFCell cell49 = row4.createCell(13); cell49.setCellValue("3"); cell49.setCellStyle(styleTaxNo); HSSFCell cell50 = row4.createCell(14); cell50.setCellValue("0"); cell50.setCellStyle(styleTaxNo); HSSFCell cell51 = row4.createCell(15); cell51.setCellValue("2"); cell51.setCellStyle(styleTaxNo); HSSFCell cell52 = row4.createCell(16); cell52.setCellValue("0"); cell52.setCellStyle(styleTaxNo); HSSFCell cell53 = row4.createCell(17); cell53.setCellValue("1"); cell53.setCellStyle(styleTaxNo); HSSFCell cell54 = row4.createCell(18); cell54.setCellValue("3"); cell54.setCellStyle(styleTaxNo); HSSFCell cell55 = row4.createCell(19); cell55.setCellValue("2"); cell55.setCellStyle(styleTaxNo); row4.setHeightInPoints((short) 16); HSSFRow rowDepartment = sheetWendy.createRow(6); HSSFCell cellDepartment = rowDepartment.createCell(0); cellDepartment.setCellValue("Department : WENDY"); cellDepartment.setCellStyle(styleC6); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("A7:T7")); rowDepartment.setHeightInPoints((short) 16); //wendy HSSFRow row5 = sheetWendy.createRow(7); HSSFCell cell81 = row5.createCell(0); cell81.setCellValue(""); cell81.setCellStyle(styleC3); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("A8:A11")); HSSFCell cell82 = row5.createCell(1); cell82.setCellValue("??"); cell82.setCellStyle(styleC3); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("B8:C9")); HSSFCell cell83 = row5.createCell(3); cell83.setCellValue("?"); cell83.setCellStyle(styleC3); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("D8:D11")); HSSFCell cell84 = row5.createCell(4); cell84.setCellValue( "/?"); cell84.setCellStyle(styleC3); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("E8:E11")); HSSFCell cell85 = row5.createCell(5); cell85.setCellValue("??"); cell85.setCellStyle(styleC3); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("F8:G9")); HSSFCell cell86 = row5.createCell(7); cell86.setCellValue("?"); cell86.setCellStyle(styleC3); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("H8:N11")); HSSFCell cell87 = row5.createCell(14); cell87.setCellValue(" "); cell87.setCellStyle(styleC3); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("O8:T11")); HSSFRow row7 = sheetWendy.createRow(9); HSSFCell cell88 = row7.createCell(1); cell88.setCellValue("//"); cell88.setCellStyle(styleC3); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("B10:B11")); HSSFCell cell89 = row7.createCell(2); cell89.setCellValue(""); cell89.setCellStyle(styleC3); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("C10:C11")); HSSFCell cell90 = row7.createCell(5); cell90.setCellValue("??"); cell90.setCellStyle(styleC3); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("F10:F11")); HSSFCell cell91 = row7.createCell(6); cell91.setCellValue(""); cell91.setCellStyle(styleC3); sheetWendy.addMergedRegion(CellRangeAddress.valueOf("G10:G11")); HSSFRow row6 = sheetWendy.createRow(8); for (int i = 0; i < 20; i++) { if (i > 1 && i != 3 && i != 4 && i != 5 && i != 7 && i != 14) { HSSFCell cell92 = row5.createCell(i); cell92.setCellStyle(styleC3); } if (i == 0 || i == 3 || i == 4 || i == 7 || i == 8 || i == 13 || i == 19) { HSSFCell cell94 = row7.createCell(i); cell94.setCellStyle(styleC3); } if (i < 8 || i == 13 || i == 19) { HSSFCell cell000 = row6.createCell(i); cell000.setCellStyle(styleC3); } if (i > 0) { HSSFCell cellDepartmentTemp = rowDepartment.createCell(i); cellDepartmentTemp.setCellStyle(styleC6); } } HSSFRow row8 = sheetWendy.createRow(10); row8.setHeightInPoints((short) 31); for (int k = 0; k < 20; k++) { HSSFCell cell0000 = row8.createCell(k); cell0000.setCellStyle(styleC3); } // set Header Outbound Report (Row 1) HSSFRow row1Outbound = sheetOutbound.createRow(0); HSSFCell cell1Outbound = row1Outbound.createCell(0); cell1Outbound.setCellValue(""); styleC1.setFont(fontHeader); cell1Outbound.setCellStyle(styleC1); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("A1:T1")); // Row 2 HSSFRow row2Outbound = sheetOutbound.createRow(1); HSSFCell cell21Outbound = row2Outbound.createCell(0); cell21Outbound.setCellValue(" " + dataHeader.getHeaderMonth() + " " + dataHeader.getHeaderYear()); styleC23.setFont(fontHeaderDetail); cell21Outbound.setCellStyle(styleC23); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("A2:T2")); // Row 3 HSSFRow row3Outbound = sheetOutbound.createRow(2); HSSFCell cell31Outbound = row3Outbound.createCell(0); cell31Outbound.setCellValue( "?? .. ? ?"); cell31Outbound.setCellStyle(styleC22); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("A3:D3")); HSSFCell cell32Outbound = row3Outbound.createCell(5); cell32Outbound.setCellValue("?? "); cell32Outbound.setCellStyle(styleC21); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("F3:J3")); HSSFCell cell33Outbound = row3Outbound.createCell(11); cell33Outbound.setCellValue(""); cell33Outbound.setCellStyle(styleSymbol); HSSFCell cell34Outbound = row3Outbound.createCell(12); cell34Outbound.setCellValue(""); cell34Outbound.setCellStyle(styleC21); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("M3:O3")); HSSFCell cell35Outbound = row3Outbound.createCell(16); cell35Outbound.setCellStyle(styleSymbol); HSSFCell cell36Outbound = row3Outbound.createCell(17); cell36Outbound.setCellStyle(styleSymbol); HSSFCell cell37Outbound = row3Outbound.createCell(18); cell37Outbound.setCellStyle(styleSymbol); row3Outbound.setHeightInPoints((short) 16); HSSFRow rowXOutbound = sheetOutbound.createRow(3); rowXOutbound.setHeightInPoints((short) 2); // Row 4 HSSFRow row4Outbound = sheetOutbound.createRow(4); HSSFCell cell41Outbound = row4Outbound.createCell(0); cell41Outbound.setCellValue( "?? .. ? ?"); cell41Outbound.setCellStyle(styleC22); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("A5:D5")); HSSFCell cell42Outbound = row4Outbound.createCell(4); cell42Outbound .setCellValue("? "); cell42Outbound.setCellStyle(styleC21); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("E5:G5")); HSSFCell cell43Outbound = row4Outbound.createCell(7); cell43Outbound.setCellValue("0"); cell43Outbound.setCellStyle(styleTaxNo); HSSFCell cell44Outbound = row4Outbound.createCell(8); cell44Outbound.setCellValue("1"); cell44Outbound.setCellStyle(styleTaxNo); HSSFCell cell45Outbound = row4Outbound.createCell(9); cell45Outbound.setCellValue("0"); cell45Outbound.setCellStyle(styleTaxNo); HSSFCell cell46Outbound = row4Outbound.createCell(10); cell46Outbound.setCellValue("5"); cell46Outbound.setCellStyle(styleTaxNo); HSSFCell cell47Outbound = row4Outbound.createCell(11); cell47Outbound.setCellValue("5"); cell47Outbound.setCellStyle(styleTaxNo); HSSFCell cell48Outbound = row4Outbound.createCell(12); cell48Outbound.setCellValue("2"); cell48Outbound.setCellStyle(styleTaxNo); HSSFCell cell49Outbound = row4Outbound.createCell(13); cell49Outbound.setCellValue("3"); cell49Outbound.setCellStyle(styleTaxNo); HSSFCell cell50Outbound = row4Outbound.createCell(14); cell50Outbound.setCellValue("0"); cell50Outbound.setCellStyle(styleTaxNo); HSSFCell cell51Outbound = row4Outbound.createCell(15); cell51Outbound.setCellValue("2"); cell51Outbound.setCellStyle(styleTaxNo); HSSFCell cell52Outbound = row4Outbound.createCell(16); cell52Outbound.setCellValue("0"); cell52Outbound.setCellStyle(styleTaxNo); HSSFCell cell53Outbound = row4Outbound.createCell(17); cell53Outbound.setCellValue("1"); cell53Outbound.setCellStyle(styleTaxNo); HSSFCell cell54Outbound = row4Outbound.createCell(18); cell54Outbound.setCellValue("3"); cell54Outbound.setCellStyle(styleTaxNo); HSSFCell cell55Outbound = row4Outbound.createCell(19); cell55Outbound.setCellValue("2"); cell55Outbound.setCellStyle(styleTaxNo); row4Outbound.setHeightInPoints((short) 16); HSSFRow rowDepartmentO = sheetOutbound.createRow(6); HSSFCell cellDepartmentO = rowDepartmentO.createCell(0); cellDepartmentO.setCellValue("Department : OUTBOUND"); cellDepartmentO.setCellStyle(styleC6); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("A7:T7")); rowDepartmentO.setHeightInPoints((short) 16); //Outbound HSSFRow row5Outbound = sheetOutbound.createRow(7); HSSFCell cell81Outbound = row5Outbound.createCell(0); cell81Outbound.setCellValue(""); cell81Outbound.setCellStyle(styleC3); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("A8:A11")); HSSFCell cell82Outbound = row5Outbound.createCell(1); cell82Outbound.setCellValue("??"); cell82Outbound.setCellStyle(styleC3); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("B8:C9")); HSSFCell cell83Outbound = row5Outbound.createCell(3); cell83Outbound.setCellValue("?"); cell83Outbound.setCellStyle(styleC3); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("D8:D11")); HSSFCell cell84Outbound = row5Outbound.createCell(4); cell84Outbound.setCellValue( "/?"); cell84Outbound.setCellStyle(styleC3); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("E8:E11")); HSSFCell cell85Outbound = row5Outbound.createCell(5); cell85Outbound.setCellValue("??"); cell85Outbound.setCellStyle(styleC3); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("F8:G9")); HSSFCell cell86Outbound = row5Outbound.createCell(7); cell86Outbound.setCellValue("?"); cell86Outbound.setCellStyle(styleC3); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("H8:N11")); HSSFCell cell87Outbound = row5Outbound.createCell(14); cell87Outbound.setCellValue(" "); cell87Outbound.setCellStyle(styleC3); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("O8:T11")); HSSFRow row7Outbound = sheetOutbound.createRow(9); HSSFCell cell88Outbound = row7Outbound.createCell(1); cell88Outbound.setCellValue("//"); cell88Outbound.setCellStyle(styleC3); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("B10:B11")); HSSFCell cell89Outbound = row7Outbound.createCell(2); cell89Outbound.setCellValue(""); cell89Outbound.setCellStyle(styleC3); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("C10:C11")); HSSFCell cell90Outbound = row7Outbound.createCell(5); cell90Outbound.setCellValue("??"); cell90Outbound.setCellStyle(styleC3); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("F10:F11")); HSSFCell cell91Outbound = row7Outbound.createCell(6); cell91Outbound.setCellValue(""); cell91Outbound.setCellStyle(styleC3); sheetOutbound.addMergedRegion(CellRangeAddress.valueOf("G10:G11")); HSSFRow row6Outbound = sheetOutbound.createRow(8); for (int i = 0; i < 20; i++) { if (i > 1 && i != 3 && i != 4 && i != 5 && i != 7 && i != 14) { HSSFCell cell92Outbound = row5Outbound.createCell(i); cell92Outbound.setCellStyle(styleC3); } if (i == 0 || i == 3 || i == 4 || i == 7 || i == 8 || i == 13 || i == 19) { HSSFCell cell94Outbound = row7Outbound.createCell(i); cell94Outbound.setCellStyle(styleC3); } if (i < 8 || i == 13 || i == 19) { HSSFCell cell000Outbound = row6Outbound.createCell(i); cell000Outbound.setCellStyle(styleC3); } if (i > 0) { HSSFCell cellDepartmentTemp = rowDepartmentO.createCell(i); cellDepartmentTemp.setCellStyle(styleC6); } } HSSFRow row8Outbound = sheetOutbound.createRow(10); row8Outbound.setHeightInPoints((short) 31); for (int k = 0; k < 20; k++) { HSSFCell cell0000Outbound = row8Outbound.createCell(k); cell0000Outbound.setCellStyle(styleC3); } // set Header Wendy + Outbound Report (Row 1) HSSFRow row1WendyOutbound = sheetWendyOutbound.createRow(0); HSSFCell cell1WendyOutbound = row1WendyOutbound.createCell(0); cell1WendyOutbound.setCellValue(""); styleC1.setFont(fontHeader); cell1WendyOutbound.setCellStyle(styleC1); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("A1:T1")); // Row 2 HSSFRow row2WendyOutbound = sheetWendyOutbound.createRow(1); HSSFCell cell21WendyOutbound = row2WendyOutbound.createCell(0); cell21WendyOutbound.setCellValue(" " + dataHeader.getHeaderMonth() + " " + dataHeader.getHeaderYear()); styleC23.setFont(fontHeaderDetail); cell21WendyOutbound.setCellStyle(styleC23); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("A2:T2")); // Row 3 HSSFRow row3WendyOutbound = sheetWendyOutbound.createRow(2); HSSFCell cell31WendyOutbound = row3WendyOutbound.createCell(0); cell31WendyOutbound.setCellValue( "?? .. ? ?"); cell31WendyOutbound.setCellStyle(styleC22); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("A3:D3")); HSSFCell cell32WendyOutbound = row3WendyOutbound.createCell(5); cell32WendyOutbound.setCellValue("?? "); cell32WendyOutbound.setCellStyle(styleC21); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("F3:J3")); HSSFCell cell33WendyOutbound = row3WendyOutbound.createCell(11); cell33WendyOutbound.setCellValue(""); cell33WendyOutbound.setCellStyle(styleSymbol); HSSFCell cell34WendyOutbound = row3WendyOutbound.createCell(12); cell34WendyOutbound.setCellValue(""); cell34WendyOutbound.setCellStyle(styleC21); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("M3:O3")); HSSFCell cell35WendyOutbound = row3WendyOutbound.createCell(16); cell35WendyOutbound.setCellStyle(styleSymbol); HSSFCell cell36WendyOutbound = row3WendyOutbound.createCell(17); cell36WendyOutbound.setCellStyle(styleSymbol); HSSFCell cell37WendyOutbound = row3WendyOutbound.createCell(18); cell37WendyOutbound.setCellStyle(styleSymbol); row3WendyOutbound.setHeightInPoints((short) 16); HSSFRow rowXWendyOutbound = sheetWendyOutbound.createRow(3); rowXWendyOutbound.setHeightInPoints((short) 2); // Row 4 HSSFRow row4WendyOutbound = sheetWendyOutbound.createRow(4); HSSFCell cell41WendyOutbound = row4WendyOutbound.createCell(0); cell41WendyOutbound.setCellValue( "?? .. ? ?"); cell41WendyOutbound.setCellStyle(styleC22); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("A5:D5")); HSSFCell cell42WendyOutbound = row4WendyOutbound.createCell(4); cell42WendyOutbound .setCellValue("? "); cell42WendyOutbound.setCellStyle(styleC21); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("E5:G5")); HSSFCell cell43WendyOutbound = row4WendyOutbound.createCell(7); cell43WendyOutbound.setCellValue("0"); cell43WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell44WendyOutbound = row4WendyOutbound.createCell(8); cell44WendyOutbound.setCellValue("1"); cell44WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell45WendyOutbound = row4WendyOutbound.createCell(9); cell45WendyOutbound.setCellValue("0"); cell45WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell46WendyOutbound = row4WendyOutbound.createCell(10); cell46WendyOutbound.setCellValue("5"); cell46WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell47WendyOutbound = row4WendyOutbound.createCell(11); cell47WendyOutbound.setCellValue("5"); cell47WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell48WendyOutbound = row4WendyOutbound.createCell(12); cell48WendyOutbound.setCellValue("2"); cell48WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell49WendyOutbound = row4WendyOutbound.createCell(13); cell49WendyOutbound.setCellValue("3"); cell49WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell50WendyOutbound = row4WendyOutbound.createCell(14); cell50WendyOutbound.setCellValue("0"); cell50WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell51WendyOutbound = row4WendyOutbound.createCell(15); cell51WendyOutbound.setCellValue("2"); cell51WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell52WendyOutbound = row4WendyOutbound.createCell(16); cell52WendyOutbound.setCellValue("0"); cell52WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell53WendyOutbound = row4WendyOutbound.createCell(17); cell53WendyOutbound.setCellValue("1"); cell53WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell54WendyOutbound = row4WendyOutbound.createCell(18); cell54WendyOutbound.setCellValue("3"); cell54WendyOutbound.setCellStyle(styleTaxNo); HSSFCell cell55WendyOutbound = row4WendyOutbound.createCell(19); cell55WendyOutbound.setCellValue("2"); cell55WendyOutbound.setCellStyle(styleTaxNo); row4WendyOutbound.setHeightInPoints((short) 16); HSSFRow rowDepartmentWendyOutbound = sheetWendyOutbound.createRow(6); HSSFCell cellDepartmentWendyOutbound = rowDepartmentWendyOutbound.createCell(0); cellDepartmentWendyOutbound.setCellValue("Department : WENDY + OUTBOUND"); cellDepartmentWendyOutbound.setCellStyle(styleC6); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("A7:T7")); rowDepartmentWendyOutbound.setHeightInPoints((short) 16); //wendy HSSFRow row5WendyOutbound = sheetWendyOutbound.createRow(7); HSSFCell cell81WendyOutbound = row5WendyOutbound.createCell(0); cell81WendyOutbound.setCellValue(""); cell81WendyOutbound.setCellStyle(styleC3); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("A8:A11")); HSSFCell cell82WendyOutbound = row5WendyOutbound.createCell(1); cell82WendyOutbound.setCellValue("??"); cell82WendyOutbound.setCellStyle(styleC3); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("B8:C9")); HSSFCell cell83WendyOutbound = row5WendyOutbound.createCell(3); cell83WendyOutbound.setCellValue("?"); cell83WendyOutbound.setCellStyle(styleC3); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("D8:D11")); HSSFCell cell84WendyOutbound = row5WendyOutbound.createCell(4); cell84WendyOutbound.setCellValue( "/?"); cell84WendyOutbound.setCellStyle(styleC3); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("E8:E11")); HSSFCell cell85WendyOutbound = row5WendyOutbound.createCell(5); cell85WendyOutbound.setCellValue("??"); cell85WendyOutbound.setCellStyle(styleC3); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("F8:G9")); HSSFCell cell86WendyOutbound = row5WendyOutbound.createCell(7); cell86WendyOutbound.setCellValue("?"); cell86WendyOutbound.setCellStyle(styleC3); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("H8:N11")); HSSFCell cell87WendyOutbound = row5WendyOutbound.createCell(14); cell87WendyOutbound .setCellValue(" "); cell87WendyOutbound.setCellStyle(styleC3); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("O8:T11")); HSSFRow row7WendyOutbound = sheetWendyOutbound.createRow(9); HSSFCell cell88WendyOutbound = row7WendyOutbound.createCell(1); cell88WendyOutbound.setCellValue("//"); cell88WendyOutbound.setCellStyle(styleC3); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("B10:B11")); HSSFCell cell89WendyOutbound = row7WendyOutbound.createCell(2); cell89WendyOutbound.setCellValue(""); cell89WendyOutbound.setCellStyle(styleC3); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("C10:C11")); HSSFCell cell90WendyOutbound = row7WendyOutbound.createCell(5); cell90WendyOutbound.setCellValue("??"); cell90WendyOutbound.setCellStyle(styleC3); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("F10:F11")); HSSFCell cell91WendyOutbound = row7WendyOutbound.createCell(6); cell91WendyOutbound.setCellValue(""); cell91WendyOutbound.setCellStyle(styleC3); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf("G10:G11")); HSSFRow row6WendyOutbound = sheetWendyOutbound.createRow(8); for (int i = 0; i < 20; i++) { if (i > 1 && i != 3 && i != 4 && i != 5 && i != 7 && i != 14) { HSSFCell cell92 = row5WendyOutbound.createCell(i); cell92.setCellStyle(styleC3); } if (i == 0 || i == 3 || i == 4 || i == 7 || i == 8 || i == 13 || i == 19) { HSSFCell cell94 = row7WendyOutbound.createCell(i); cell94.setCellStyle(styleC3); } if (i < 8 || i == 13 || i == 19) { HSSFCell cell000 = row6WendyOutbound.createCell(i); cell000.setCellStyle(styleC3); } if (i > 0) { HSSFCell cellDepartmentTemp = rowDepartmentWendyOutbound.createCell(i); cellDepartmentTemp.setCellStyle(styleC6); } } HSSFRow row8WendyOutbound = sheetWendyOutbound.createRow(10); row8WendyOutbound.setHeightInPoints((short) 31); for (int k = 0; k < 20; k++) { HSSFCell cell0000 = row8WendyOutbound.createCell(k); cell0000.setCellStyle(styleC3); } // set Header Inbound Report (Row 1) HSSFRow row1Inbound = sheetInbound.createRow(0); HSSFCell cell1Inbound = row1Inbound.createCell(0); cell1Inbound.setCellValue(""); styleC1.setFont(fontHeader); cell1Inbound.setCellStyle(styleC1); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("A1:T1")); // Row 2 HSSFRow row2Inbound = sheetInbound.createRow(1); HSSFCell cell21Inbound = row2Inbound.createCell(0); cell21Inbound.setCellValue(" " + dataHeader.getHeaderMonth() + " " + dataHeader.getHeaderYear()); styleC23.setFont(fontHeaderDetail); cell21Inbound.setCellStyle(styleC23); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("A2:T2")); // Row 3 HSSFRow row3Inbound = sheetInbound.createRow(2); HSSFCell cell31Inbound = row3Inbound.createCell(0); cell31Inbound.setCellValue( "?? .. ? ?"); cell31Inbound.setCellStyle(styleC22); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("A3:D3")); HSSFCell cell32Inbound = row3Inbound.createCell(5); cell32Inbound.setCellValue("?? "); cell32Inbound.setCellStyle(styleC21); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("F3:J3")); HSSFCell cell33Inbound = row3Inbound.createCell(11); cell33Inbound.setCellValue(""); cell33Inbound.setCellStyle(styleSymbol); HSSFCell cell34Inbound = row3Inbound.createCell(12); cell34Inbound.setCellValue(""); cell34Inbound.setCellStyle(styleC21); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("M3:O3")); HSSFCell cell35Inbound = row3Inbound.createCell(16); cell35Inbound.setCellStyle(styleSymbol); HSSFCell cell36Inbound = row3Inbound.createCell(17); cell36Inbound.setCellStyle(styleSymbol); HSSFCell cell37Inbound = row3Inbound.createCell(18); cell37Inbound.setCellStyle(styleSymbol); row3Inbound.setHeightInPoints((short) 16); HSSFRow rowXInbound = sheetInbound.createRow(3); rowXInbound.setHeightInPoints((short) 2); // Row 4 HSSFRow row4Inbound = sheetInbound.createRow(4); HSSFCell cell41Inbound = row4Inbound.createCell(0); cell41Inbound.setCellValue( "?? .. ? ?"); cell41Inbound.setCellStyle(styleC22); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("A5:D5")); HSSFCell cell42Inbound = row4Inbound.createCell(4); cell42Inbound .setCellValue("? "); cell42Inbound.setCellStyle(styleC21); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("E5:G5")); HSSFCell cell43Inbound = row4Inbound.createCell(7); cell43Inbound.setCellValue("0"); cell43Inbound.setCellStyle(styleTaxNo); HSSFCell cell44Inbound = row4Inbound.createCell(8); cell44Inbound.setCellValue("1"); cell44Inbound.setCellStyle(styleTaxNo); HSSFCell cell45Inbound = row4Inbound.createCell(9); cell45Inbound.setCellValue("0"); cell45Inbound.setCellStyle(styleTaxNo); HSSFCell cell46Inbound = row4Inbound.createCell(10); cell46Inbound.setCellValue("5"); cell46Inbound.setCellStyle(styleTaxNo); HSSFCell cell47Inbound = row4Inbound.createCell(11); cell47Inbound.setCellValue("5"); cell47Inbound.setCellStyle(styleTaxNo); HSSFCell cell48Inbound = row4Inbound.createCell(12); cell48Inbound.setCellValue("2"); cell48Inbound.setCellStyle(styleTaxNo); HSSFCell cell49Inbound = row4Inbound.createCell(13); cell49Inbound.setCellValue("3"); cell49Inbound.setCellStyle(styleTaxNo); HSSFCell cell50Inbound = row4Inbound.createCell(14); cell50Inbound.setCellValue("0"); cell50Inbound.setCellStyle(styleTaxNo); HSSFCell cell51Inbound = row4Inbound.createCell(15); cell51Inbound.setCellValue("2"); cell51Inbound.setCellStyle(styleTaxNo); HSSFCell cell52Inbound = row4Inbound.createCell(16); cell52Inbound.setCellValue("0"); cell52Inbound.setCellStyle(styleTaxNo); HSSFCell cell53Inbound = row4Inbound.createCell(17); cell53Inbound.setCellValue("1"); cell53Inbound.setCellStyle(styleTaxNo); HSSFCell cell54Inbound = row4Inbound.createCell(18); cell54Inbound.setCellValue("3"); cell54Inbound.setCellStyle(styleTaxNo); HSSFCell cell55Inbound = row4Inbound.createCell(19); cell55Inbound.setCellValue("2"); cell55Inbound.setCellStyle(styleTaxNo); row4Inbound.setHeightInPoints((short) 16); HSSFRow rowDepartmentI = sheetInbound.createRow(6); HSSFCell cellDepartmentI = rowDepartmentI.createCell(0); cellDepartmentI.setCellValue("Department : INBOUND"); cellDepartmentI.setCellStyle(styleC6); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("A7:T7")); rowDepartmentI.setHeightInPoints((short) 16); //Inbound HSSFRow row5Inbound = sheetInbound.createRow(7); HSSFCell cell81Inbound = row5Inbound.createCell(0); cell81Inbound.setCellValue(""); cell81Inbound.setCellStyle(styleC3); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("A8:A11")); HSSFCell cell82Inbound = row5Inbound.createCell(1); cell82Inbound.setCellValue("??"); cell82Inbound.setCellStyle(styleC3); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("B8:C9")); HSSFCell cell83Inbound = row5Inbound.createCell(3); cell83Inbound.setCellValue("?"); cell83Inbound.setCellStyle(styleC3); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("D8:D11")); HSSFCell cell84Inbound = row5Inbound.createCell(4); cell84Inbound.setCellValue( "/?"); cell84Inbound.setCellStyle(styleC3); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("E8:E11")); HSSFCell cell85Inbound = row5Inbound.createCell(5); cell85Inbound.setCellValue("??"); cell85Inbound.setCellStyle(styleC3); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("F8:G9")); HSSFCell cell86Inbound = row5Inbound.createCell(7); cell86Inbound.setCellValue("?"); cell86Inbound.setCellStyle(styleC3); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("H8:N11")); HSSFCell cell87Inbound = row5Inbound.createCell(14); cell87Inbound.setCellValue(" "); cell87Inbound.setCellStyle(styleC3); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("O8:T11")); HSSFRow row7Inbound = sheetInbound.createRow(9); HSSFCell cell88Inbound = row7Inbound.createCell(1); cell88Inbound.setCellValue("//"); cell88Inbound.setCellStyle(styleC3); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("B10:B11")); HSSFCell cell89Inbound = row7Inbound.createCell(2); cell89Inbound.setCellValue(""); cell89Inbound.setCellStyle(styleC3); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("C10:C11")); HSSFCell cell90Inbound = row7Inbound.createCell(5); cell90Inbound.setCellValue("??"); cell90Inbound.setCellStyle(styleC3); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("F10:F11")); HSSFCell cell91Inbound = row7Inbound.createCell(6); cell91Inbound.setCellValue(""); cell91Inbound.setCellStyle(styleC3); sheetInbound.addMergedRegion(CellRangeAddress.valueOf("G10:G11")); HSSFRow row6Inbound = sheetInbound.createRow(8); for (int i = 0; i < 20; i++) { if (i > 1 && i != 3 && i != 4 && i != 5 && i != 7 && i != 14) { HSSFCell cell92Inbound = row5Inbound.createCell(i); cell92Inbound.setCellStyle(styleC3); } if (i == 0 || i == 3 || i == 4 || i == 7 || i == 8 || i == 13 || i == 19) { HSSFCell cell94Inbound = row7Inbound.createCell(i); cell94Inbound.setCellStyle(styleC3); } if (i < 8 || i == 13 || i == 19) { HSSFCell cell000Inbound = row6Inbound.createCell(i); cell000Inbound.setCellStyle(styleC3); } if (i > 0) { HSSFCell cellDepartmentTemp = rowDepartmentI.createCell(i); cellDepartmentTemp.setCellStyle(styleC6); } } HSSFRow row8Inbound = sheetInbound.createRow(10); row8Inbound.setHeightInPoints((short) 31); for (int k = 0; k < 20; k++) { HSSFCell cell0000Inbound = row8Inbound.createCell(k); cell0000Inbound.setCellStyle(styleC3); } int tempContW = 0; int tempContO = 0; int tempContI = 0; int tempContWO = 0; int cCount = 0; int cCountO = 0; int cCountI = 0; int cCountWO = 0; int cCountRowPrintW = 30; int cCountRowPrintO = 30; int cCountRowPrintWO = 30; int cCountRowPrintI = 30; float heightPoint = (float) 17.5; for (int i = 0; i < outputTaxViewList.size(); i++) { OutputTaxView data = (OutputTaxView) outputTaxViewList.get(i); if ("Wendy".equalsIgnoreCase(data.getDepartment())) { // System.out.println("===== cCount Wendy ===== : " + cCount); if (cCount == cCountRowPrintW) { HSSFRow rowtotal = sheetWendy.createRow(count + w + countWTemp); rowtotal.setHeightInPoints(heightPoint); // System.out.println("H : " + (count+tempContW+1+countWTemp) + " - H : " + (count+w+countWTemp) + " = " + grossWCN.multiply(BigDecimal.valueOf(2))); String totalGross = "SUM(H" + (count + tempContW + 1 + countWTemp) + ":H" + (count + w + countWTemp) + ") -" + grossWCN.multiply(BigDecimal.valueOf(2)); String totalVat = "SUM(O" + (count + tempContW + 1 + countWTemp) + ":O" + (count + w + countWTemp) + ") -" + vatWCN.multiply(BigDecimal.valueOf(2)); HSSFCellStyle styleTotal = wb.createCellStyle(); styleTotal.setFont(excelFunction.getHeaderTable(wb.createFont())); styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTotal.setAlignment(styleC22.ALIGN_RIGHT); HSSFCell cellTotal0 = rowtotal.createCell(0); cellTotal0.setCellStyle(stylebordertotalleft); HSSFCell cellTotal00 = rowtotal.createCell(1); cellTotal00.setCellStyle(stylebordertotal); HSSFCell cellTotal01 = rowtotal.createCell(2); cellTotal01.setCellStyle(stylebordertotal); HSSFCell cellTotal02 = rowtotal.createCell(3); cellTotal02.setCellStyle(stylebordertotal); HSSFCell cellTotal03 = rowtotal.createCell(4); cellTotal03.setCellStyle(stylebordertotal); HSSFCell cellTotal04 = rowtotal.createCell(5); cellTotal04.setCellStyle(stylebordertotal); HSSFCell cellTotal05 = rowtotal.createCell(6); cellTotal05.setCellValue("TOTAL : "); cellTotal05.setCellStyle(styleC4); HSSFCell cellTotal06 = rowtotal.createCell(7); cellTotal06.setCellFormula(totalGross); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = rowtotal.createCell(14); cellTotal07.setCellFormula(totalVat); cellTotal07.setCellStyle(styleC25); sheetWendy.addMergedRegion(CellRangeAddress .valueOf("H" + (count + w + countWTemp + 1) + ":N" + (count + w + countWTemp + 1))); sheetWendy.addMergedRegion(CellRangeAddress .valueOf("O" + (count + w + countWTemp + 1) + ":T" + (count + w + countWTemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotal.createCell(k); cell0000.setCellStyle(styleC25); } } countWTemp = countWTemp + 1; grossWCN = BigDecimal.ZERO; vatWCN = BigDecimal.ZERO; tempContW = i; cCount = 0; } HSSFRow row = sheetWendy.createRow(count + w + countWTemp); row.setHeightInPoints(heightPoint); HSSFCell celldata0 = row.createCell(0); celldata0.setCellValue(String.valueOf(data.getOrder())); celldata0.setCellStyle(styleC26); HSSFCell celldata1 = row.createCell(1); celldata1.setCellValue(String.valueOf(data.getTaxdate())); celldata1.setCellStyle(styleC30); HSSFCell celldata2 = row.createCell(2); celldata2.setCellValue(String.valueOf(data.getTaxno())); celldata2.setCellStyle(styleC30); HSSFCell celldata3 = row.createCell(3); celldata3.setCellValue(String.valueOf(data.getDescription())); celldata3.setCellStyle(styleC25); HSSFCell celldata4 = row.createCell(4); celldata4.setCellValue(String.valueOf(data.getAgttaxno())); celldata4.setCellStyle(styleC25); HSSFCell celldata5 = row.createCell(5); if ("1".equalsIgnoreCase(String.valueOf(data.getMain()))) { celldata5.setCellValue(""); } else { celldata5.setCellValue(""); } celldata5.setCellStyle(styleSymbol); HSSFCell celldata6 = row.createCell(6); celldata6.setCellValue(String.valueOf(data.getBranchno())); celldata6.setCellStyle(styleC30); HSSFCell celldata7 = row.createCell(7); HSSFCell celldata8 = row.createCell(14); if ("CN".equalsIgnoreCase(String.valueOf(data.getType()))) { celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getGross())) ? 0 : (data.getGross()).doubleValue()); celldata7.setCellStyle(styleC25CN); celldata8.setCellValue( "".equalsIgnoreCase(String.valueOf(data.getVat())) ? 0 : (data.getVat()).doubleValue()); celldata8.setCellStyle(styleC25CN); } else { celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getGross())) ? 0 : (data.getGross()).doubleValue()); celldata7.setCellStyle(styleC25); celldata8.setCellValue( "".equalsIgnoreCase(String.valueOf(data.getVat())) ? 0 : (data.getVat()).doubleValue()); celldata8.setCellStyle(styleC25); } sheetWendy.addMergedRegion(CellRangeAddress .valueOf("H" + (count + w + countWTemp + 1) + ":N" + (count + w + countWTemp + 1))); sheetWendy.addMergedRegion(CellRangeAddress .valueOf("O" + (count + w + countWTemp + 1) + ":T" + (count + w + countWTemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = row.createCell(k); cell0000.setCellStyle(styleC25); } } if ("CN".equalsIgnoreCase(String.valueOf(data.getType()))) { if (data.getGross() != null) { grossWCN = grossWCN.add(data.getGross()); grossWCNTotal = grossWCNTotal.add(data.getGross()); } else { grossWCN = grossWCN.add(BigDecimal.ZERO); grossWCNTotal = grossWCNTotal.add(BigDecimal.ZERO); } if (data.getVat() != null) { vatWCN = vatWCN.add(data.getVat()); vatWCNTotal = vatWCNTotal.add(data.getVat()); } else { vatWCN = vatWCN.add(BigDecimal.ZERO); vatWCNTotal = vatWCNTotal.add(BigDecimal.ZERO); } } else { if (data.getGross() != null) { grossW = grossW.add(data.getGross()); grossWTotal = grossWTotal.add(data.getGross()); } else { grossW = grossW.add(BigDecimal.ZERO); grossWTotal = grossWTotal.add(BigDecimal.ZERO); } if (data.getVat() != null) { vatW = vatW.add(data.getVat()); vatWTotal = vatWTotal.add(data.getVat()); } else { vatW = vatW.add(BigDecimal.ZERO); vatWTotal = vatWTotal.add(BigDecimal.ZERO); } } tempcountW = count + w + 1; cCount = cCount + 1; w++; } //Outbound if ("Outbound".equalsIgnoreCase(data.getDepartment())) { // System.out.println("===== cCountO Outbound ===== : " + cCountO); if (cCountO == cCountRowPrintO) { HSSFRow rowtotal = sheetOutbound.createRow(count + x + countOTemp); rowtotal.setHeightInPoints(heightPoint); // System.out.println("H : " + (count+tempContO+1+countOTemp) + " - H : " + (count+x+countOTemp) + " = " + grossOCN.multiply(BigDecimal.valueOf(2))); String totalGross = "SUM(H" + (count + tempContO + 1 + countOTemp) + ":H" + (count + x + countOTemp) + ") -" + grossOCN.multiply(BigDecimal.valueOf(2)); String totalVat = "SUM(O" + (count + tempContO + 1 + countOTemp) + ":O" + (count + x + countOTemp) + ") -" + vatOCN.multiply(BigDecimal.valueOf(2)); HSSFCellStyle styleTotal = wb.createCellStyle(); styleTotal.setFont(excelFunction.getHeaderTable(wb.createFont())); styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTotal.setAlignment(styleC22.ALIGN_RIGHT); HSSFCell cellTotal0 = rowtotal.createCell(0); cellTotal0.setCellStyle(stylebordertotalleft); HSSFCell cellTotal00 = rowtotal.createCell(1); cellTotal00.setCellStyle(stylebordertotal); HSSFCell cellTotal01 = rowtotal.createCell(2); cellTotal01.setCellStyle(stylebordertotal); HSSFCell cellTotal02 = rowtotal.createCell(3); cellTotal02.setCellStyle(stylebordertotal); HSSFCell cellTotal03 = rowtotal.createCell(4); cellTotal03.setCellStyle(stylebordertotal); HSSFCell cellTotal04 = rowtotal.createCell(5); cellTotal04.setCellStyle(stylebordertotal); HSSFCell cellTotal05 = rowtotal.createCell(6); cellTotal05.setCellValue("TOTAL : "); cellTotal05.setCellStyle(styleC4); HSSFCell cellTotal06 = rowtotal.createCell(7); cellTotal06.setCellFormula(totalGross); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = rowtotal.createCell(14); cellTotal07.setCellFormula(totalVat); cellTotal07.setCellStyle(styleC25); sheetOutbound.addMergedRegion(CellRangeAddress .valueOf("H" + (count + x + countOTemp + 1) + ":N" + (count + x + countOTemp + 1))); sheetOutbound.addMergedRegion(CellRangeAddress .valueOf("O" + (count + x + countOTemp + 1) + ":T" + (count + x + countOTemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotal.createCell(k); cell0000.setCellStyle(styleC25); } } countOTemp = countOTemp + 1; grossOCN = BigDecimal.ZERO; vatOCN = BigDecimal.ZERO; tempContO = x; cCountO = 0; } HSSFRow row = sheetOutbound.createRow(count + x + countOTemp); row.setHeightInPoints(heightPoint); HSSFCell celldata0 = row.createCell(0); celldata0.setCellValue(String.valueOf(data.getOrder())); celldata0.setCellStyle(styleC26); HSSFCell celldata1 = row.createCell(1); celldata1.setCellValue(String.valueOf(data.getTaxdate())); celldata1.setCellStyle(styleC30); HSSFCell celldata2 = row.createCell(2); celldata2.setCellValue(String.valueOf(data.getTaxno())); celldata2.setCellStyle(styleC30); HSSFCell celldata3 = row.createCell(3); celldata3.setCellValue(String.valueOf(data.getDescription())); celldata3.setCellStyle(styleC25); HSSFCell celldata4 = row.createCell(4); celldata4.setCellValue(String.valueOf(data.getAgttaxno())); celldata4.setCellStyle(styleC25); HSSFCell celldata5 = row.createCell(5); if ("1".equalsIgnoreCase(String.valueOf(data.getMain()))) { celldata5.setCellValue(""); } else { celldata5.setCellValue(""); } celldata5.setCellStyle(styleSymbol); HSSFCell celldata6 = row.createCell(6); celldata6.setCellValue(String.valueOf(data.getBranchno())); celldata6.setCellStyle(styleC30); HSSFCell celldata7 = row.createCell(7); HSSFCell celldata8 = row.createCell(14); if ("CN".equalsIgnoreCase(String.valueOf(data.getType()))) { celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getGross())) ? 0 : (data.getGross()).doubleValue()); celldata7.setCellStyle(styleC25CN); celldata8.setCellValue( "".equalsIgnoreCase(String.valueOf(data.getVat())) ? 0 : (data.getVat()).doubleValue()); celldata8.setCellStyle(styleC25CN); } else { celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getGross())) ? 0 : (data.getGross()).doubleValue()); celldata7.setCellStyle(styleC25); celldata8.setCellValue( "".equalsIgnoreCase(String.valueOf(data.getVat())) ? 0 : (data.getVat()).doubleValue()); celldata8.setCellStyle(styleC25); } sheetOutbound.addMergedRegion(CellRangeAddress .valueOf("H" + (count + x + countOTemp + 1) + ":N" + (count + x + countOTemp + 1))); sheetOutbound.addMergedRegion(CellRangeAddress .valueOf("O" + (count + x + countOTemp + 1) + ":T" + (count + x + countOTemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = row.createCell(k); cell0000.setCellStyle(styleC25); } } if ("CN".equalsIgnoreCase(String.valueOf(data.getType()))) { if (data.getGross() != null) { grossOCN = grossOCN.add(data.getGross()); grossOCNTotal = grossOCNTotal.add(data.getGross()); } else { grossOCN = grossOCN.add(BigDecimal.ZERO); grossOCNTotal = grossOCNTotal.add(BigDecimal.ZERO); } if (data.getVat() != null) { vatOCN = vatOCN.add(data.getVat()); vatOCNTotal = vatOCNTotal.add(data.getVat()); } else { vatOCN = vatOCN.add(BigDecimal.ZERO); vatOCNTotal = vatOCNTotal.add(BigDecimal.ZERO); } } else { if (data.getGross() != null) { grossO = grossO.add(data.getGross()); grossOTotal = grossOTotal.add(data.getGross()); } else { grossO = grossO.add(BigDecimal.ZERO); grossOTotal = grossOTotal.add(BigDecimal.ZERO); } if (data.getVat() != null) { vatO = vatO.add(data.getVat()); vatOTotal = vatOTotal.add(data.getVat()); } else { vatO = vatO.add(BigDecimal.ZERO); vatOTotal = vatOTotal.add(BigDecimal.ZERO); } } tempcountO = count + x + 1; cCountO = cCountO + 1; x++; } //Wendy + Outbound if ("Wendy".equalsIgnoreCase(data.getDepartment()) || "Outbound".equalsIgnoreCase(data.getDepartment())) { System.out.println("===== cCountWO WendyOutbound ===== : " + cCountWO); if (cCountWO == cCountRowPrintWO) { HSSFRow rowtotal = sheetWendyOutbound.createRow(count + z + countWOTemp); rowtotal.setHeightInPoints(heightPoint); System.out.println("H : " + (count + tempContWO + 1 + countWOTemp) + " - H : " + (count + z + countWOTemp) + " = " + grossWOCN.multiply(BigDecimal.valueOf(2))); String totalGross = "SUM(H" + (count + tempContWO + 1 + countWOTemp) + ":H" + (count + z + countWOTemp) + ") -" + grossWOCN.multiply(BigDecimal.valueOf(2)); String totalVat = "SUM(O" + (count + tempContWO + 1 + countWOTemp) + ":O" + (count + z + countWOTemp) + ") -" + vatWOCN.multiply(BigDecimal.valueOf(2)); HSSFCellStyle styleTotal = wb.createCellStyle(); styleTotal.setFont(excelFunction.getHeaderTable(wb.createFont())); styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTotal.setAlignment(styleC22.ALIGN_RIGHT); HSSFCell cellTotal0 = rowtotal.createCell(0); cellTotal0.setCellStyle(stylebordertotalleft); HSSFCell cellTotal00 = rowtotal.createCell(1); cellTotal00.setCellStyle(stylebordertotal); HSSFCell cellTotal01 = rowtotal.createCell(2); cellTotal01.setCellStyle(stylebordertotal); HSSFCell cellTotal02 = rowtotal.createCell(3); cellTotal02.setCellStyle(stylebordertotal); HSSFCell cellTotal03 = rowtotal.createCell(4); cellTotal03.setCellStyle(stylebordertotal); HSSFCell cellTotal04 = rowtotal.createCell(5); cellTotal04.setCellStyle(stylebordertotal); HSSFCell cellTotal05 = rowtotal.createCell(6); cellTotal05.setCellValue("TOTAL : "); cellTotal05.setCellStyle(styleC4); HSSFCell cellTotal06 = rowtotal.createCell(7); cellTotal06.setCellFormula(totalGross); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = rowtotal.createCell(14); cellTotal07.setCellFormula(totalVat); cellTotal07.setCellStyle(styleC25); sheetWendyOutbound.addMergedRegion(CellRangeAddress .valueOf("H" + (count + z + countWOTemp + 1) + ":N" + (count + z + countWOTemp + 1))); sheetWendyOutbound.addMergedRegion(CellRangeAddress .valueOf("O" + (count + z + countWOTemp + 1) + ":T" + (count + z + countWOTemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotal.createCell(k); cell0000.setCellStyle(styleC25); } } countWOTemp = countWOTemp + 1; grossWOCN = BigDecimal.ZERO; vatWOCN = BigDecimal.ZERO; tempContWO = z; cCountWO = 0; } HSSFRow row = sheetWendyOutbound.createRow(count + z + countWOTemp); row.setHeightInPoints(heightPoint); HSSFCell celldata0 = row.createCell(0); celldata0.setCellValue(String.valueOf(data.getOrder())); celldata0.setCellStyle(styleC26); HSSFCell celldata1 = row.createCell(1); celldata1.setCellValue(String.valueOf(data.getTaxdate())); celldata1.setCellStyle(styleC30); HSSFCell celldata2 = row.createCell(2); celldata2.setCellValue(String.valueOf(data.getTaxno())); celldata2.setCellStyle(styleC30); HSSFCell celldata3 = row.createCell(3); celldata3.setCellValue(String.valueOf(data.getDescription())); celldata3.setCellStyle(styleC25); HSSFCell celldata4 = row.createCell(4); celldata4.setCellValue(String.valueOf(data.getAgttaxno())); celldata4.setCellStyle(styleC25); HSSFCell celldata5 = row.createCell(5); if ("1".equalsIgnoreCase(String.valueOf(data.getMain()))) { celldata5.setCellValue(""); } else { celldata5.setCellValue(""); } celldata5.setCellStyle(styleSymbol); HSSFCell celldata6 = row.createCell(6); celldata6.setCellValue(String.valueOf(data.getBranchno())); celldata6.setCellStyle(styleC30); HSSFCell celldata7 = row.createCell(7); HSSFCell celldata8 = row.createCell(14); if ("CN".equalsIgnoreCase(String.valueOf(data.getType()))) { celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getGross())) ? 0 : (data.getGross()).doubleValue()); celldata7.setCellStyle(styleC25CN); celldata8.setCellValue( "".equalsIgnoreCase(String.valueOf(data.getVat())) ? 0 : (data.getVat()).doubleValue()); celldata8.setCellStyle(styleC25CN); } else { celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getGross())) ? 0 : (data.getGross()).doubleValue()); celldata7.setCellStyle(styleC25); celldata8.setCellValue( "".equalsIgnoreCase(String.valueOf(data.getVat())) ? 0 : (data.getVat()).doubleValue()); celldata8.setCellStyle(styleC25); } sheetWendyOutbound.addMergedRegion(CellRangeAddress .valueOf("H" + (count + z + countWOTemp + 1) + ":N" + (count + z + countWOTemp + 1))); sheetWendyOutbound.addMergedRegion(CellRangeAddress .valueOf("O" + (count + z + countWOTemp + 1) + ":T" + (count + z + countWOTemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = row.createCell(k); cell0000.setCellStyle(styleC25); } } if ("CN".equalsIgnoreCase(String.valueOf(data.getType()))) { if (data.getGross() != null) { grossWOCN = grossWOCN.add(data.getGross()); grossWOCNTotal = grossWOCNTotal.add(data.getGross()); } else { grossWOCN = grossWOCN.add(BigDecimal.ZERO); grossWOCNTotal = grossWOCNTotal.add(BigDecimal.ZERO); } if (data.getVat() != null) { vatWOCN = vatWOCN.add(data.getVat()); vatWOCNTotal = vatWOCNTotal.add(data.getVat()); } else { vatWOCN = vatWOCN.add(BigDecimal.ZERO); vatWOCNTotal = vatWOCNTotal.add(BigDecimal.ZERO); } } else { if (data.getGross() != null) { grossWO = grossWO.add(data.getGross()); grossWOTotal = grossWOTotal.add(data.getGross()); } else { grossWO = grossWO.add(BigDecimal.ZERO); grossWOTotal = grossWOTotal.add(BigDecimal.ZERO); } if (data.getVat() != null) { vatWO = vatWO.add(data.getVat()); vatWOTotal = vatWOTotal.add(data.getVat()); } else { vatWO = vatWO.add(BigDecimal.ZERO); vatWOTotal = vatWOTotal.add(BigDecimal.ZERO); } } tempcountWO = count + z + 1; cCountWO = cCountWO + 1; z++; } if ("Inbound".equalsIgnoreCase(data.getDepartment())) { // System.out.println("===== cCountI Inbound ===== : " + cCountI); if (cCountI == cCountRowPrintI) { HSSFRow rowtotal = sheetInbound.createRow(count + y + countITemp); rowtotal.setHeightInPoints(heightPoint); String totalGross = "SUM(H" + (count + tempContI + 1 + countITemp) + ":H" + (count + y + countITemp) + ") -" + grossICN.multiply(BigDecimal.valueOf(2)); String totalVat = "SUM(O" + (count + tempContI + 1 + countITemp) + ":O" + (count + y + countITemp) + ") -" + vatICN.multiply(BigDecimal.valueOf(2)); HSSFCellStyle styleTotal = wb.createCellStyle(); styleTotal.setFont(excelFunction.getHeaderTable(wb.createFont())); styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTotal.setAlignment(styleC22.ALIGN_RIGHT); HSSFCell cellTotal0 = rowtotal.createCell(0); cellTotal0.setCellStyle(stylebordertotalleft); HSSFCell cellTotal00 = rowtotal.createCell(1); cellTotal00.setCellStyle(stylebordertotal); HSSFCell cellTotal01 = rowtotal.createCell(2); cellTotal01.setCellStyle(stylebordertotal); HSSFCell cellTotal02 = rowtotal.createCell(3); cellTotal02.setCellStyle(stylebordertotal); HSSFCell cellTotal03 = rowtotal.createCell(4); cellTotal03.setCellStyle(stylebordertotal); HSSFCell cellTotal04 = rowtotal.createCell(5); cellTotal04.setCellStyle(stylebordertotal); HSSFCell cellTotal05 = rowtotal.createCell(6); cellTotal05.setCellValue("TOTAL : "); cellTotal05.setCellStyle(styleC4); HSSFCell cellTotal06 = rowtotal.createCell(7); cellTotal06.setCellFormula(totalGross); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = rowtotal.createCell(14); cellTotal07.setCellFormula(totalVat); cellTotal07.setCellStyle(styleC25); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("H" + (count + y + countITemp + 1) + ":N" + (count + y + countITemp + 1))); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("O" + (count + y + countITemp + 1) + ":T" + (count + y + countITemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotal.createCell(k); cell0000.setCellStyle(styleC25); } } countITemp = countITemp + 1; grossICN = BigDecimal.ZERO; vatICN = BigDecimal.ZERO; tempContI = y; cCountI = 0; } HSSFRow row = sheetInbound.createRow(count + y + countITemp); row.setHeightInPoints(heightPoint); HSSFCell celldata0 = row.createCell(0); celldata0.setCellValue(String.valueOf(data.getOrder())); celldata0.setCellStyle(styleC26); HSSFCell celldata1 = row.createCell(1); celldata1.setCellValue(String.valueOf(data.getTaxdate())); celldata1.setCellStyle(styleC30); HSSFCell celldata2 = row.createCell(2); celldata2.setCellValue(String.valueOf(data.getTaxno())); celldata2.setCellStyle(styleC30); HSSFCell celldata3 = row.createCell(3); celldata3.setCellValue(String.valueOf(data.getDescription())); celldata3.setCellStyle(styleC25); HSSFCell celldata4 = row.createCell(4); celldata4.setCellValue(String.valueOf(data.getAgttaxno())); celldata4.setCellStyle(styleC25); HSSFCell celldata5 = row.createCell(5); if ("1".equalsIgnoreCase(String.valueOf(data.getMain()))) { celldata5.setCellValue(""); } else { celldata5.setCellValue(""); } celldata5.setCellStyle(styleSymbol); HSSFCell celldata6 = row.createCell(6); celldata6.setCellValue(String.valueOf(data.getBranchno())); celldata6.setCellStyle(styleC30); HSSFCell celldata7 = row.createCell(7); HSSFCell celldata8 = row.createCell(14); if ("CN".equalsIgnoreCase(String.valueOf(data.getType()))) { celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getGross())) ? 0 : (data.getGross()).doubleValue()); celldata7.setCellStyle(styleC25CN); celldata8.setCellValue( "".equalsIgnoreCase(String.valueOf(data.getVat())) ? 0 : (data.getVat()).doubleValue()); celldata8.setCellStyle(styleC25CN); } else { celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getGross())) ? 0 : (data.getGross()).doubleValue()); celldata7.setCellStyle(styleC25); celldata8.setCellValue( "".equalsIgnoreCase(String.valueOf(data.getVat())) ? 0 : (data.getVat()).doubleValue()); celldata8.setCellStyle(styleC25); } sheetInbound.addMergedRegion(CellRangeAddress .valueOf("H" + (count + y + countITemp + 1) + ":N" + (count + y + countITemp + 1))); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("O" + (count + y + countITemp + 1) + ":T" + (count + y + countITemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = row.createCell(k); cell0000.setCellStyle(styleC25); } } if ("CN".equalsIgnoreCase(String.valueOf(data.getType()))) { if (data.getGross() != null) { grossICN = grossICN.add(data.getGross()); grossICNTotal = grossICNTotal.add(data.getGross()); } else { grossICN = grossICN.add(BigDecimal.ZERO); grossICNTotal = grossICNTotal.add(BigDecimal.ZERO); } if (data.getVat() != null) { vatICN = vatICN.add(data.getVat()); vatICNTotal = vatICNTotal.add(data.getVat()); } else { vatICN = vatICN.add(BigDecimal.ZERO); vatICNTotal = vatICNTotal.add(BigDecimal.ZERO); } } else { if (data.getGross() != null) { grossI = grossI.add(data.getGross()); grossITotal = grossITotal.add(data.getGross()); } else { grossI = grossI.add(BigDecimal.ZERO); grossITotal = grossITotal.add(BigDecimal.ZERO); } if (data.getVat() != null) { vatI = vatI.add(data.getVat()); vatITotal = vatITotal.add(data.getVat()); } else { vatI = vatI.add(BigDecimal.ZERO); vatITotal = vatITotal.add(BigDecimal.ZERO); } } tempcountI = count + y + 1; cCountI = cCountI + 1; y++; } if (i == (outputTaxViewList.size() - 1)) { if (tempcountW != 0 || countWendy == 0) { tempcountW = (tempcountW != 0 ? tempcountW : 11); System.out.println("Temp Count W : " + tempcountW); System.out.println("Count W Temp : " + countWTemp); HSSFRow rowtotal = sheetWendy.createRow(tempcountW + countWTemp); rowtotal.setHeightInPoints(heightPoint); String totalGross = "SUM(H" + (count + tempContW + 1 + countWTemp) + ":H" + (tempcountW + countWTemp) + ") -" + grossWCN.multiply(BigDecimal.valueOf(2)); String totalVat = "SUM(O" + (count + tempContW + 1 + countWTemp) + ":O" + (tempcountW + countWTemp) + ") -" + vatWCN.multiply(BigDecimal.valueOf(2)); HSSFCellStyle styleTotal = wb.createCellStyle(); styleTotal.setFont(excelFunction.getHeaderTable(wb.createFont())); styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTotal.setAlignment(styleC22.ALIGN_RIGHT); HSSFCell cellTotal0 = rowtotal.createCell(0); cellTotal0.setCellStyle(stylebordertotalleft); HSSFCell cellTotal00 = rowtotal.createCell(1); cellTotal00.setCellStyle(stylebordertotal); HSSFCell cellTotal01 = rowtotal.createCell(2); cellTotal01.setCellStyle(stylebordertotal); HSSFCell cellTotal02 = rowtotal.createCell(3); cellTotal02.setCellStyle(stylebordertotal); HSSFCell cellTotal03 = rowtotal.createCell(4); cellTotal03.setCellStyle(stylebordertotal); HSSFCell cellTotal04 = rowtotal.createCell(5); cellTotal04.setCellStyle(stylebordertotal); HSSFCell cellTotal05 = rowtotal.createCell(6); cellTotal05.setCellValue("TOTAL : "); cellTotal05.setCellStyle(styleC4); HSSFCell cellTotal06 = rowtotal.createCell(7); cellTotal06.setCellFormula(totalGross); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = rowtotal.createCell(14); cellTotal07.setCellFormula(totalVat); cellTotal07.setCellStyle(styleC25); sheetWendy.addMergedRegion(CellRangeAddress .valueOf("H" + (tempcountW + countWTemp + 1) + ":N" + (tempcountW + countWTemp + 1))); sheetWendy.addMergedRegion(CellRangeAddress .valueOf("O" + (tempcountW + countWTemp + 1) + ":T" + (tempcountW + countWTemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotal.createCell(k); cell0000.setCellStyle(styleC25); } } HSSFRow rowtotalwenBorder = sheetWendy.createRow(tempcountW + countWTemp + 1); for (int k = 0; k < 20; k++) { HSSFCell cell0000 = rowtotalwenBorder.createCell(k); cell0000.setCellStyle(styleC25); } sheetWendy.addMergedRegion(CellRangeAddress .valueOf("A" + (tempcountW + countWTemp + 2) + ":T" + (tempcountW + countWTemp + 2))); HSSFRow rowtotalwen = sheetWendy.createRow(tempcountW + countWTemp + 2); rowtotalwen.setHeightInPoints(heightPoint); HSSFCell cellTotalWen0 = rowtotalwen.createCell(0); cellTotalWen0.setCellStyle(stylebordertotalleft); HSSFCell cellTotalWen00 = rowtotalwen.createCell(1); cellTotalWen00.setCellStyle(stylebordertotal); HSSFCell cellTotalWen01 = rowtotalwen.createCell(2); cellTotalWen01.setCellStyle(stylebordertotal); HSSFCell cellTotalWen02 = rowtotalwen.createCell(3); cellTotalWen02.setCellStyle(stylebordertotal); HSSFCell cellTotalWen03 = rowtotalwen.createCell(4); cellTotalWen03.setCellStyle(stylebordertotal); HSSFCell cellTotalWen04 = rowtotalwen.createCell(5); cellTotalWen04.setCellStyle(stylebordertotal); HSSFCell cellTotalWen05 = rowtotalwen.createCell(6); cellTotalWen05.setCellValue("TOTAL WENDY : "); cellTotalWen05.setCellStyle(styleC4); HSSFCell cellTotalWen06 = rowtotalwen.createCell(7); cellTotalWen06.setCellValue((grossWTotal.subtract(grossWCNTotal)).doubleValue()); cellTotalWen06.setCellStyle(styleC25); HSSFCell cellTotalWen07 = rowtotalwen.createCell(14); cellTotalWen07.setCellValue((vatWTotal.subtract(vatWCNTotal)).doubleValue()); cellTotalWen07.setCellStyle(styleC25); sheetWendy.addMergedRegion(CellRangeAddress .valueOf("H" + (tempcountW + countWTemp + 3) + ":N" + (tempcountW + countWTemp + 3))); sheetWendy.addMergedRegion(CellRangeAddress .valueOf("O" + (tempcountW + countWTemp + 3) + ":T" + (tempcountW + countWTemp + 3))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotalwen.createCell(k); cell0000.setCellStyle(styleC25); } } } if (x != 0 || countOutbound == 0) { tempcountO = (x != 0 ? count + x : 11); HSSFRow rowtotal = sheetOutbound.createRow(tempcountO + countOTemp); rowtotal.setHeightInPoints(heightPoint); String totalGross = "SUM(H" + (count + tempContO + 1 + countOTemp) + ":H" + (tempcountO + countOTemp) + ") -" + grossOCN.multiply(BigDecimal.valueOf(2)); String totalVat = "SUM(O" + (count + tempContO + 1 + countOTemp) + ":O" + (tempcountO + countOTemp) + ") -" + vatOCN.multiply(BigDecimal.valueOf(2)); HSSFCellStyle styleTotal = wb.createCellStyle(); styleTotal.setFont(excelFunction.getHeaderTable(wb.createFont())); styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTotal.setAlignment(styleC22.ALIGN_RIGHT); HSSFCell cellTotal0 = rowtotal.createCell(0); cellTotal0.setCellStyle(stylebordertotalleft); HSSFCell cellTotal00 = rowtotal.createCell(1); cellTotal00.setCellStyle(stylebordertotal); HSSFCell cellTotal01 = rowtotal.createCell(2); cellTotal01.setCellStyle(stylebordertotal); HSSFCell cellTotal02 = rowtotal.createCell(3); cellTotal02.setCellStyle(stylebordertotal); HSSFCell cellTotal03 = rowtotal.createCell(4); cellTotal03.setCellStyle(stylebordertotal); HSSFCell cellTotal04 = rowtotal.createCell(5); cellTotal04.setCellStyle(stylebordertotal); HSSFCell cellTotal05 = rowtotal.createCell(6); cellTotal05.setCellValue("TOTAL : "); cellTotal05.setCellStyle(styleC4); HSSFCell cellTotal06 = rowtotal.createCell(7); cellTotal06.setCellFormula(totalGross); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = rowtotal.createCell(14); cellTotal07.setCellFormula(totalVat); cellTotal07.setCellStyle(styleC25); sheetOutbound.addMergedRegion(CellRangeAddress .valueOf("H" + (tempcountO + countOTemp + 1) + ":N" + (tempcountO + countOTemp + 1))); sheetOutbound.addMergedRegion(CellRangeAddress .valueOf("O" + (tempcountO + countOTemp + 1) + ":T" + (tempcountO + countOTemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotal.createCell(k); cell0000.setCellStyle(styleC25); } } HSSFRow rowtotalOutBorder = sheetOutbound.createRow(tempcountO + countOTemp + 1); for (int k = 0; k < 20; k++) { HSSFCell cell0000 = rowtotalOutBorder.createCell(k); cell0000.setCellStyle(styleC25); } sheetOutbound.addMergedRegion(CellRangeAddress .valueOf("A" + (tempcountO + countOTemp + 2) + ":T" + (tempcountO + countOTemp + 2))); HSSFRow rowtotalout = sheetOutbound.createRow(tempcountO + countOTemp + 2); rowtotalout.setHeightInPoints(heightPoint); HSSFCell cellTotalOut0 = rowtotalout.createCell(0); cellTotalOut0.setCellStyle(stylebordertotalleft); HSSFCell cellTotalOut00 = rowtotalout.createCell(1); cellTotalOut00.setCellStyle(stylebordertotal); HSSFCell cellTotalOut01 = rowtotalout.createCell(2); cellTotalOut01.setCellStyle(stylebordertotal); HSSFCell cellTotalOut02 = rowtotalout.createCell(3); cellTotalOut02.setCellStyle(stylebordertotal); HSSFCell cellTotalOut03 = rowtotalout.createCell(4); cellTotalOut03.setCellStyle(stylebordertotal); HSSFCell cellTotalOut04 = rowtotalout.createCell(5); cellTotalOut04.setCellStyle(stylebordertotal); HSSFCell cellTotalOut05 = rowtotalout.createCell(6); cellTotalOut05.setCellValue("TOTAL OUTBOUND : "); cellTotalOut05.setCellStyle(styleC4); HSSFCell cellTotalOut06 = rowtotalout.createCell(7); cellTotalOut06.setCellValue((grossOTotal.subtract(grossOCNTotal)).doubleValue()); cellTotalOut06.setCellStyle(styleC25); HSSFCell cellTotalOut07 = rowtotalout.createCell(14); cellTotalOut07.setCellValue((vatOTotal.subtract(vatOCNTotal)).doubleValue()); cellTotalOut07.setCellStyle(styleC25); sheetOutbound.addMergedRegion(CellRangeAddress .valueOf("H" + (tempcountO + countOTemp + 3) + ":N" + (tempcountO + countOTemp + 3))); sheetOutbound.addMergedRegion(CellRangeAddress .valueOf("O" + (tempcountO + countOTemp + 3) + ":T" + (tempcountO + countOTemp + 3))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotalout.createCell(k); cell0000.setCellStyle(styleC25); } } } if (z != 0 || countWendyOutbound == 0) { tempcountWO = (z != 0 ? count + z : 11); HSSFRow rowtotal = sheetWendyOutbound.createRow(tempcountWO + countWOTemp); rowtotal.setHeightInPoints(heightPoint); String totalGross = "SUM(H" + (count + tempContWO + 1 + countWOTemp) + ":H" + (tempcountWO + countWOTemp) + ") -" + grossWOCN.multiply(BigDecimal.valueOf(2)); String totalVat = "SUM(O" + (count + tempContWO + 1 + countWOTemp) + ":O" + (tempcountWO + countWOTemp) + ") -" + vatWOCN.multiply(BigDecimal.valueOf(2)); HSSFCellStyle styleTotal = wb.createCellStyle(); styleTotal.setFont(excelFunction.getHeaderTable(wb.createFont())); styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTotal.setAlignment(styleC22.ALIGN_RIGHT); HSSFCell cellTotal0 = rowtotal.createCell(0); cellTotal0.setCellStyle(stylebordertotalleft); HSSFCell cellTotal00 = rowtotal.createCell(1); cellTotal00.setCellStyle(stylebordertotal); HSSFCell cellTotal01 = rowtotal.createCell(2); cellTotal01.setCellStyle(stylebordertotal); HSSFCell cellTotal02 = rowtotal.createCell(3); cellTotal02.setCellStyle(stylebordertotal); HSSFCell cellTotal03 = rowtotal.createCell(4); cellTotal03.setCellStyle(stylebordertotal); HSSFCell cellTotal04 = rowtotal.createCell(5); cellTotal04.setCellStyle(stylebordertotal); HSSFCell cellTotal05 = rowtotal.createCell(6); cellTotal05.setCellValue("TOTAL : "); cellTotal05.setCellStyle(styleC4); HSSFCell cellTotal06 = rowtotal.createCell(7); cellTotal06.setCellFormula(totalGross); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = rowtotal.createCell(14); cellTotal07.setCellFormula(totalVat); cellTotal07.setCellStyle(styleC25); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf( "H" + (tempcountWO + countWOTemp + 1) + ":N" + (tempcountWO + countWOTemp + 1))); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf( "O" + (tempcountWO + countWOTemp + 1) + ":T" + (tempcountWO + countWOTemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotal.createCell(k); cell0000.setCellStyle(styleC25); } } HSSFRow rowtotalOutBorder = sheetWendyOutbound.createRow(tempcountWO + countWOTemp + 1); for (int k = 0; k < 20; k++) { HSSFCell cell0000 = rowtotalOutBorder.createCell(k); cell0000.setCellStyle(styleC25); } sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf( "A" + (tempcountWO + countWOTemp + 2) + ":T" + (tempcountWO + countWOTemp + 2))); HSSFRow rowtotalout = sheetWendyOutbound.createRow(tempcountWO + countWOTemp + 2); rowtotalout.setHeightInPoints(heightPoint); HSSFCell cellTotalOut0 = rowtotalout.createCell(0); cellTotalOut0.setCellStyle(stylebordertotalleft); HSSFCell cellTotalOut00 = rowtotalout.createCell(1); cellTotalOut00.setCellStyle(stylebordertotal); HSSFCell cellTotalOut01 = rowtotalout.createCell(2); cellTotalOut01.setCellStyle(stylebordertotal); HSSFCell cellTotalOut02 = rowtotalout.createCell(3); cellTotalOut02.setCellStyle(stylebordertotal); HSSFCell cellTotalOut03 = rowtotalout.createCell(4); cellTotalOut03.setCellStyle(stylebordertotal); HSSFCell cellTotalOut04 = rowtotalout.createCell(5); cellTotalOut04.setCellStyle(stylebordertotal); HSSFCell cellTotalOut05 = rowtotalout.createCell(6); cellTotalOut05.setCellValue("TOTAL WENDY + OUTBOUND : "); cellTotalOut05.setCellStyle(styleC4); HSSFCell cellTotalOut06 = rowtotalout.createCell(7); cellTotalOut06.setCellValue((grossWOTotal.subtract(grossWOCNTotal)).doubleValue()); cellTotalOut06.setCellStyle(styleC25); HSSFCell cellTotalOut07 = rowtotalout.createCell(14); cellTotalOut07.setCellValue((vatWOTotal.subtract(vatWOCNTotal)).doubleValue()); cellTotalOut07.setCellStyle(styleC25); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf( "H" + (tempcountWO + countWOTemp + 3) + ":N" + (tempcountWO + countWOTemp + 3))); sheetWendyOutbound.addMergedRegion(CellRangeAddress.valueOf( "O" + (tempcountWO + countWOTemp + 3) + ":T" + (tempcountWO + countWOTemp + 3))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotalout.createCell(k); cell0000.setCellStyle(styleC25); } } } if (y != 0 || countInbound == 0) { tempcountI = (y != 0 ? count + y : 11); HSSFRow rowtotal = sheetInbound.createRow(tempcountI + countITemp); rowtotal.setHeightInPoints(heightPoint); String totalGross = "SUM(H" + (count + tempContI + 1 + countITemp) + ":H" + (tempcountI + countITemp) + ") -" + grossICN.multiply(BigDecimal.valueOf(2)); String totalVat = "SUM(O" + (count + tempContI + 1 + countITemp) + ":O" + (tempcountI + countITemp) + ")-" + vatICN.multiply(BigDecimal.valueOf(2)); HSSFCellStyle styleTotal = wb.createCellStyle(); styleTotal.setFont(excelFunction.getHeaderTable(wb.createFont())); styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTotal.setAlignment(styleC22.ALIGN_RIGHT); HSSFCell cellTotal0 = rowtotal.createCell(0); cellTotal0.setCellStyle(stylebordertotalleft); HSSFCell cellTotal00 = rowtotal.createCell(1); cellTotal00.setCellStyle(stylebordertotal); HSSFCell cellTotal01 = rowtotal.createCell(2); cellTotal01.setCellStyle(stylebordertotal); HSSFCell cellTotal02 = rowtotal.createCell(3); cellTotal02.setCellStyle(stylebordertotal); HSSFCell cellTotal03 = rowtotal.createCell(4); cellTotal03.setCellStyle(stylebordertotal); HSSFCell cellTotal04 = rowtotal.createCell(5); cellTotal04.setCellStyle(stylebordertotal); HSSFCell cellTotal05 = rowtotal.createCell(6); cellTotal05.setCellValue("TOTAL : "); cellTotal05.setCellStyle(styleC4); HSSFCell cellTotal06 = rowtotal.createCell(7); cellTotal06.setCellFormula(totalGross); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = rowtotal.createCell(14); cellTotal07.setCellFormula(totalVat); cellTotal07.setCellStyle(styleC25); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("H" + (tempcountI + countITemp + 1) + ":N" + (tempcountI + countITemp + 1))); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("O" + (tempcountI + countITemp + 1) + ":T" + (tempcountI + countITemp + 1))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotal.createCell(k); cell0000.setCellStyle(styleC25); } } HSSFRow rowtotalInBorder = sheetInbound.createRow(tempcountI + countITemp + 1); for (int k = 0; k < 20; k++) { HSSFCell cell0000 = rowtotalInBorder.createCell(k); cell0000.setCellStyle(styleC25); } sheetInbound.addMergedRegion(CellRangeAddress .valueOf("A" + (tempcountI + countITemp + 2) + ":T" + (tempcountI + countITemp + 2))); HSSFRow rowtotalin = sheetInbound.createRow(tempcountI + countITemp + 2); rowtotalin.setHeightInPoints(heightPoint); HSSFCell cellTotalIn0 = rowtotalin.createCell(0); cellTotalIn0.setCellStyle(stylebordertotalleft); HSSFCell cellTotalIn00 = rowtotalin.createCell(1); cellTotalIn00.setCellStyle(stylebordertotal); HSSFCell cellTotalIn01 = rowtotalin.createCell(2); cellTotalIn01.setCellStyle(stylebordertotal); HSSFCell cellTotalIn02 = rowtotalin.createCell(3); cellTotalIn02.setCellStyle(stylebordertotal); HSSFCell cellTotalIn03 = rowtotalin.createCell(4); cellTotalIn03.setCellStyle(stylebordertotal); HSSFCell cellTotalIn04 = rowtotalin.createCell(5); cellTotalIn04.setCellStyle(stylebordertotal); HSSFCell cellTotalIn05 = rowtotalin.createCell(6); cellTotalIn05.setCellValue("TOTAL INBOUND : "); cellTotalIn05.setCellStyle(styleC4); HSSFCell cellTotalIn06 = rowtotalin.createCell(7); cellTotalIn06.setCellValue((grossITotal.subtract(grossICNTotal)).doubleValue()); cellTotalIn06.setCellStyle(styleC25); HSSFCell cellTotalIn07 = rowtotalin.createCell(14); cellTotalIn07.setCellValue((vatITotal.subtract(vatICNTotal)).doubleValue()); cellTotalIn07.setCellStyle(styleC25); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("H" + (tempcountI + countITemp + 3) + ":N" + (tempcountI + countITemp + 3))); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("O" + (tempcountI + countITemp + 3) + ":T" + (tempcountI + countITemp + 3))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotalin.createCell(k); cell0000.setCellStyle(styleC25); } } } HSSFRow rowtotalInBorder = sheetInbound.createRow(count + y + countITemp + 3); for (int k = 0; k < 20; k++) { HSSFCell cell0000 = rowtotalInBorder.createCell(k); cell0000.setCellStyle(styleC25); } sheetInbound.addMergedRegion(CellRangeAddress .valueOf("A" + (count + y + countITemp + 4) + ":T" + (count + y + countITemp + 4))); // HSSFRow rowtotalbor = sheetInbound.createRow(count + y + countITemp+3); // rowtotalbor.setHeightInPoints(heightPoint); // HSSFCell cellTotalBor0 = rowtotalbor.createCell(0); // cellTotalBor0.setCellStyle(stylebordertotalleft); // HSSFCell cellTotalBor00 = rowtotalbor.createCell(1); // cellTotalBor00.setCellStyle(stylebordertotal); // HSSFCell cellTotalBor01 = rowtotalbor.createCell(2); // cellTotalBor01.setCellStyle(stylebordertotal); // HSSFCell cellTotalBor02 = rowtotalbor.createCell(3); // cellTotalBor02.setCellStyle(stylebordertotal); // HSSFCell cellTotalBor03 = rowtotalbor.createCell(4); // cellTotalBor03.setCellStyle(stylebordertotal); // HSSFCell cellTotalBor04 = rowtotalbor.createCell(5); // cellTotalBor04.setCellStyle(stylebordertotal); HSSFRow rowtotalwen = sheetInbound.createRow(count + y + countITemp + 4); rowtotalwen.setHeightInPoints(heightPoint); HSSFCell cellTotalWen0 = rowtotalwen.createCell(0); cellTotalWen0.setCellStyle(stylebordertotalleft); HSSFCell cellTotalWen00 = rowtotalwen.createCell(1); cellTotalWen00.setCellStyle(stylebordertotal); HSSFCell cellTotalWen01 = rowtotalwen.createCell(2); cellTotalWen01.setCellStyle(stylebordertotal); HSSFCell cellTotalWen02 = rowtotalwen.createCell(3); cellTotalWen02.setCellStyle(stylebordertotal); HSSFCell cellTotalWen03 = rowtotalwen.createCell(4); cellTotalWen03.setCellStyle(stylebordertotal); HSSFCell cellTotalWen04 = rowtotalwen.createCell(5); cellTotalWen04.setCellStyle(stylebordertotal); HSSFCell cellTotalWen05 = rowtotalwen.createCell(6); cellTotalWen05.setCellValue("TOTAL WENDY : "); cellTotalWen05.setCellStyle(styleC4); HSSFCell cellTotalWen06 = rowtotalwen.createCell(7); cellTotalWen06.setCellValue((grossWTotal.subtract(grossWCNTotal)).doubleValue()); cellTotalWen06.setCellStyle(styleC25); HSSFCell cellTotalWen07 = rowtotalwen.createCell(14); cellTotalWen07.setCellValue((vatWTotal.subtract(vatWCNTotal)).doubleValue()); cellTotalWen07.setCellStyle(styleC25); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("H" + (count + y + countITemp + 5) + ":N" + (count + y + countITemp + 5))); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("O" + (count + y + countITemp + 5) + ":T" + (count + y + countITemp + 5))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotalwen.createCell(k); cell0000.setCellStyle(styleC25); } } HSSFRow rowtotalout = sheetInbound.createRow(count + y + countITemp + 5); rowtotalout.setHeightInPoints(heightPoint); HSSFCell cellTotalOut0 = rowtotalout.createCell(0); cellTotalOut0.setCellStyle(stylebordertotalleft); HSSFCell cellTotalOut00 = rowtotalout.createCell(1); cellTotalOut00.setCellStyle(stylebordertotal); HSSFCell cellTotalOut01 = rowtotalout.createCell(2); cellTotalOut01.setCellStyle(stylebordertotal); HSSFCell cellTotalOut02 = rowtotalout.createCell(3); cellTotalOut02.setCellStyle(stylebordertotal); HSSFCell cellTotalOut03 = rowtotalout.createCell(4); cellTotalOut03.setCellStyle(stylebordertotal); HSSFCell cellTotalOut04 = rowtotalout.createCell(5); cellTotalOut04.setCellStyle(stylebordertotal); HSSFCell cellTotalOut05 = rowtotalout.createCell(6); cellTotalOut05.setCellValue("TOTAL OUTBOUND : "); cellTotalOut05.setCellStyle(styleC4); HSSFCell cellTotalOut06 = rowtotalout.createCell(7); cellTotalOut06.setCellValue((grossOTotal.subtract(grossOCNTotal)).doubleValue()); cellTotalOut06.setCellStyle(styleC25); HSSFCell cellTotalOut07 = rowtotalout.createCell(14); cellTotalOut07.setCellValue((vatOTotal.subtract(vatOCNTotal)).doubleValue()); cellTotalOut07.setCellStyle(styleC25); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("H" + (count + y + countITemp + 6) + ":N" + (count + y + countITemp + 6))); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("O" + (count + y + countITemp + 6) + ":T" + (count + y + countITemp + 6))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotalout.createCell(k); cell0000.setCellStyle(styleC25); } } HSSFRow rowtotalin = sheetInbound.createRow(count + y + countITemp + 6); rowtotalin.setHeightInPoints(heightPoint); HSSFCell cellTotalIn0 = rowtotalin.createCell(0); cellTotalIn0.setCellStyle(stylebordertotalleft); HSSFCell cellTotalIn00 = rowtotalin.createCell(1); cellTotalIn00.setCellStyle(stylebordertotal); HSSFCell cellTotalIn01 = rowtotalin.createCell(2); cellTotalIn01.setCellStyle(stylebordertotal); HSSFCell cellTotalIn02 = rowtotalin.createCell(3); cellTotalIn02.setCellStyle(stylebordertotal); HSSFCell cellTotalIn03 = rowtotalin.createCell(4); cellTotalIn03.setCellStyle(stylebordertotal); HSSFCell cellTotalIn04 = rowtotalin.createCell(5); cellTotalIn04.setCellStyle(stylebordertotal); HSSFCell cellTotalIn05 = rowtotalin.createCell(6); cellTotalIn05.setCellValue("TOTAL INBOUND : "); cellTotalIn05.setCellStyle(styleC4); HSSFCell cellTotalIn06 = rowtotalin.createCell(7); cellTotalIn06.setCellValue((grossITotal.subtract(grossICNTotal)).doubleValue()); cellTotalIn06.setCellStyle(styleC25); HSSFCell cellTotalIn07 = rowtotalin.createCell(14); cellTotalIn07.setCellValue((vatITotal.subtract(vatICNTotal)).doubleValue()); cellTotalIn07.setCellStyle(styleC25); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("H" + (count + y + countITemp + 7) + ":N" + (count + y + countITemp + 7))); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("O" + (count + y + countITemp + 7) + ":T" + (count + y + countITemp + 7))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotalin.createCell(k); cell0000.setCellStyle(styleC25); } } String totalGrossAll = "SUM(H" + (count + y + countITemp + 5) + ":H" + (count + y + countITemp + 7) + ")"; String totalVatAll = "SUM(O" + (count + y + countITemp + 5) + ":O" + (count + y + countITemp + 7) + ")"; HSSFRow rowtotalall = sheetInbound.createRow(count + y + countITemp + 7); rowtotalall.setHeightInPoints(heightPoint); HSSFCell cellTotalAll0 = rowtotalall.createCell(0); cellTotalAll0.setCellStyle(stylebordertotalleft); HSSFCell cellTotalAll00 = rowtotalall.createCell(1); cellTotalAll00.setCellStyle(stylebordertotal); HSSFCell cellTotalAll01 = rowtotalall.createCell(2); cellTotalAll01.setCellStyle(stylebordertotal); HSSFCell cellTotalAll02 = rowtotalall.createCell(3); cellTotalAll02.setCellStyle(stylebordertotal); HSSFCell cellTotalAll03 = rowtotalall.createCell(4); cellTotalAll03.setCellStyle(stylebordertotal); HSSFCell cellTotalAll04 = rowtotalall.createCell(5); cellTotalAll04.setCellStyle(stylebordertotal); HSSFCell cellTotalAll05 = rowtotalall.createCell(6); cellTotalAll05.setCellValue("TOTAL WENDY + OUTBOUND + INBOUND : "); cellTotalAll05.setCellStyle(styleC4); HSSFCell cellTotalAll06 = rowtotalall.createCell(7); cellTotalAll06.setCellFormula(totalGrossAll); cellTotalAll06.setCellStyle(styleC25); HSSFCell cellTotalAll07 = rowtotalall.createCell(14); cellTotalAll07.setCellFormula(totalVatAll); cellTotalAll07.setCellStyle(styleC25); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("H" + (count + y + countITemp + 8) + ":N" + (count + y + countITemp + 8))); sheetInbound.addMergedRegion(CellRangeAddress .valueOf("O" + (count + y + countITemp + 8) + ":T" + (count + y + countITemp + 8))); for (int k = 8; k < 20; k++) { if (k != 7 && k != 14) { HSSFCell cell0000 = rowtotalall.createCell(k); cell0000.setCellStyle(styleC25); } } } } }
From source file:com.smi.travel.controller.excel.booking.OutboundProductSummary.java
private void getPaymentSummary(HSSFWorkbook wb, List listPaymentSummary) { List<PaymentOutboundAllDetail> paymentSummaryList = listPaymentSummary; String sheetName = "PaymentSummary";// name of sheet HSSFSheet sheet = wb.createSheet(sheetName); UtilityExcelFunction excelFunction = new UtilityExcelFunction(); // Set align Text HSSFCellStyle styleAlignRight = wb.createCellStyle(); styleAlignRight.setAlignment(styleAlignRight.ALIGN_RIGHT); HSSFCellStyle styleAlignLeft = wb.createCellStyle(); styleAlignLeft.setAlignment(styleAlignLeft.ALIGN_LEFT); HSSFDataFormat currency = wb.createDataFormat(); // Set align Text HSSFCellStyle styleNumber = wb.createCellStyle(); styleNumber.setAlignment(styleNumber.ALIGN_RIGHT); styleNumber.setDataFormat(currency.getFormat("#,##0.00")); // set Header Report (Row 1) HSSFCellStyle styleHeader1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0);/*from w w w . j a v a2 s . c om*/ HSSFCell cell1 = row1.createCell(0); cell1.setCellValue("Payment Outbound Summary"); styleHeader1.setFont(excelFunction.getHeaderFont(wb.createFont())); cell1.setCellStyle(styleHeader1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:I1")); if (paymentSummaryList != null && paymentSummaryList.size() != 0) { PaymentOutboundAllDetail poad = new PaymentOutboundAllDetail(); poad = paymentSummaryList.get(0); // ROW 1 HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Invoice Sup : "); cell21.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(poad.getHeaderinvoicesupcode()); cell22.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); HSSFCell cell23 = row2.createCell(2); cell23.setCellValue("Staff : "); cell23.setCellStyle(styleAlignRight); sheet.autoSizeColumn(2); HSSFCell cell24 = row2.createCell(3); cell24.setCellValue(poad.getHeaderstaff()); cell24.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(3); // ROW 2 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Pay Date : "); cell31.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(poad.getDatefromto()); cell32.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); HSSFCell cell33 = row3.createCell(2); cell33.setCellValue("Ref No : "); cell33.setCellStyle(styleAlignRight); sheet.autoSizeColumn(2); HSSFCell cell34 = row3.createCell(3); cell34.setCellValue(poad.getHeaderrefno()); cell34.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(3); HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Inv Date : "); cell41.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(poad.getInvdatefromto()); cell42.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); HSSFCell cell43 = row4.createCell(2); cell43.setCellValue("Inv Name : "); cell43.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell44 = row4.createCell(3); cell44.setCellValue(poad.getHeaderinvname()); cell44.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); HSSFRow row5 = sheet.createRow(4); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Country : "); cell51.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell52 = row5.createCell(1); cell52.setCellValue(poad.getHeadercountry()); cell52.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); HSSFCell cell53 = row5.createCell(2); cell53.setCellValue("City : "); cell53.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell54 = row5.createCell(3); cell54.setCellValue(poad.getHeadercity()); cell54.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); HSSFRow row6 = sheet.createRow(5); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Product Type : "); cell61.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue(poad.getHeaderproducttype()); cell62.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); HSSFCell cell63 = row6.createCell(2); cell63.setCellValue("Product : "); cell63.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell64 = row6.createCell(3); cell64.setCellValue(poad.getHeaderproductname()); cell64.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); } // Header Table HSSFCellStyle styleHeader = wb.createCellStyle(); styleHeader.setFont(excelFunction.getHeaderTable(wb.createFont())); styleHeader.setAlignment(styleHeader.ALIGN_CENTER); styleHeader.setBorderTop(styleHeader.BORDER_THIN); styleHeader.setBorderLeft(styleHeader.BORDER_THIN); styleHeader.setBorderBottom(styleHeader.BORDER_THIN); styleHeader.setBorderRight(styleHeader.BORDER_THIN); styleHeader.setVerticalAlignment(styleHeader.VERTICAL_CENTER); HSSFCellStyle styleDetailTable = wb.createCellStyle(); styleDetailTable.setAlignment(styleDetailTable.ALIGN_LEFT); styleDetailTable.setBorderLeft(styleDetailTable.BORDER_THIN); styleDetailTable.setBorderRight(styleDetailTable.BORDER_THIN); styleDetailTable.setVerticalAlignment(styleDetailTable.VERTICAL_CENTER); HSSFCellStyle styleDetailTableCenter = wb.createCellStyle(); styleDetailTableCenter.setAlignment(styleDetailTableCenter.ALIGN_CENTER); styleDetailTableCenter.setBorderTop(styleDetailTableCenter.BORDER_THIN); styleDetailTableCenter.setBorderBottom(styleDetailTableCenter.BORDER_THIN); styleDetailTableCenter.setBorderRight(styleDetailTableCenter.BORDER_THIN); styleDetailTableCenter.setBorderLeft(styleDetailTableCenter.BORDER_THIN); styleDetailTableCenter.setWrapText(true); styleDetailTableCenter.setDataFormat(currency.getFormat("#,##0")); styleDetailTableCenter.setVerticalAlignment(styleDetailTableCenter.VERTICAL_CENTER); HSSFCellStyle styleDetailTableNumber = wb.createCellStyle(); styleDetailTableNumber.setAlignment(styleDetailTableNumber.ALIGN_RIGHT); styleDetailTableNumber.setBorderLeft(styleDetailTableNumber.BORDER_THIN); styleDetailTableNumber.setBorderRight(styleDetailTableNumber.BORDER_THIN); styleDetailTableNumber.setDataFormat(currency.getFormat("#,##0.00")); styleDetailTableNumber.setWrapText(true); styleDetailTableNumber.setVerticalAlignment(styleDetailTableNumber.VERTICAL_CENTER); HSSFCellStyle styleDetailTableBorderBottom = wb.createCellStyle(); styleDetailTableBorderBottom.setBorderTop(styleDetailTableBorderBottom.BORDER_THIN); HSSFCellStyle styleBorderTop = wb.createCellStyle(); styleBorderTop.setBorderBottom(styleBorderTop.BORDER_THIN); styleBorderTop.setFont(excelFunction.getHeaderTable(wb.createFont())); styleBorderTop.setAlignment(styleBorderTop.ALIGN_CENTER); HSSFCellStyle styleBorderRight = wb.createCellStyle(); styleBorderRight.setBorderRight(styleBorderRight.BORDER_THIN); styleBorderRight.setAlignment(styleBorderRight.ALIGN_CENTER); HSSFCellStyle styleBorderBottomAndRight = wb.createCellStyle(); styleBorderBottomAndRight.setBorderRight(styleBorderBottomAndRight.BORDER_THIN); styleBorderBottomAndRight.setBorderBottom(styleBorderBottomAndRight.BORDER_THIN); styleBorderBottomAndRight.setAlignment(styleBorderBottomAndRight.ALIGN_CENTER); styleBorderBottomAndRight.setVerticalAlignment(styleBorderBottomAndRight.VERTICAL_CENTER); HSSFCellStyle styleAlignLeftBorderTopRight = wb.createCellStyle(); // use styleAlignLeftBorderTopRight.setAlignment(styleAlignLeftBorderTopRight.ALIGN_LEFT); styleAlignLeftBorderTopRight.setBorderTop(styleAlignLeftBorderTopRight.BORDER_THIN); styleAlignLeftBorderTopRight.setBorderRight(styleAlignLeftBorderTopRight.BORDER_THIN); styleAlignLeftBorderTopRight.setVerticalAlignment(styleAlignLeftBorderTopRight.VERTICAL_CENTER); HSSFCellStyle styleAlignLeftBorderTopLeft = wb.createCellStyle(); // use styleAlignLeftBorderTopLeft.setAlignment(styleAlignLeftBorderTopLeft.ALIGN_LEFT); styleAlignLeftBorderTopLeft.setBorderTop(styleAlignLeftBorderTopLeft.BORDER_THIN); styleAlignLeftBorderTopLeft.setBorderLeft(styleAlignLeftBorderTopLeft.BORDER_THIN); styleAlignLeftBorderTopLeft.setVerticalAlignment(styleAlignLeftBorderTopLeft.VERTICAL_CENTER); HSSFCellStyle styleBorderTopP = wb.createCellStyle(); // use styleBorderTopP.setBorderTop(styleBorderTopP.BORDER_THIN); HSSFCellStyle styleAlignRightBorderBottomRight = wb.createCellStyle();//use styleAlignRightBorderBottomRight.setAlignment(styleAlignRightBorderBottomRight.ALIGN_LEFT); styleAlignRightBorderBottomRight.setBorderBottom(styleAlignRightBorderBottomRight.BORDER_THIN); styleAlignRightBorderBottomRight.setBorderRight(styleAlignRightBorderBottomRight.BORDER_THIN); styleAlignRightBorderBottomRight.setVerticalAlignment(styleAlignRightBorderBottomRight.VERTICAL_CENTER); HSSFCellStyle styleAlignRightBorderBottomLeft = wb.createCellStyle(); styleAlignRightBorderBottomLeft.setAlignment(styleAlignRightBorderBottomLeft.ALIGN_LEFT); styleAlignRightBorderBottomLeft.setBorderBottom(styleAlignRightBorderBottomLeft.BORDER_THIN); styleAlignRightBorderBottomLeft.setBorderLeft(styleAlignRightBorderBottomLeft.BORDER_THIN); styleAlignRightBorderBottomLeft.setVerticalAlignment(styleAlignRightBorderBottomLeft.VERTICAL_CENTER); HSSFCellStyle styleBorderBottom = wb.createCellStyle(); //use styleBorderBottom.setBorderBottom(styleBorderBottom.BORDER_THIN); HSSFCellStyle styleAlignRightBorderRight = wb.createCellStyle(); //use styleAlignRightBorderRight.setAlignment(styleAlignRightBorderRight.ALIGN_RIGHT); styleAlignRightBorderRight.setBorderRight(styleAlignRightBorderRight.BORDER_THIN); HSSFCellStyle styleAlignLeftBorderRight = wb.createCellStyle(); styleAlignLeftBorderRight.setAlignment(styleAlignLeftBorderRight.ALIGN_LEFT); styleAlignLeftBorderRight.setBorderRight(styleAlignLeftBorderRight.BORDER_THIN); HSSFCellStyle styleAlignRightBorderLeft = wb.createCellStyle();//use styleAlignRightBorderLeft.setAlignment(styleAlignRightBorderLeft.ALIGN_RIGHT); styleAlignRightBorderLeft.setBorderLeft(styleAlignRightBorderLeft.BORDER_THIN); HSSFCellStyle styleAlignRightBorderAllNumber = wb.createCellStyle(); styleAlignRightBorderAllNumber.setAlignment(styleAlignRightBorderAllNumber.ALIGN_RIGHT); styleAlignRightBorderAllNumber.setDataFormat(currency.getFormat("#,##0.00")); styleAlignRightBorderAllNumber.setBorderTop(styleAlignRightBorderAllNumber.BORDER_THIN); styleAlignRightBorderAllNumber.setBorderBottom(styleAlignRightBorderAllNumber.BORDER_THIN); styleAlignRightBorderAllNumber.setBorderRight(styleAlignRightBorderAllNumber.BORDER_THIN); styleAlignRightBorderAllNumber.setBorderLeft(styleAlignRightBorderAllNumber.BORDER_THIN); styleAlignRightBorderAllNumber.setVerticalAlignment(styleAlignRightBorderAllNumber.VERTICAL_CENTER); styleAlignRightBorderAllNumber.setWrapText(true); HSSFCellStyle styleAlignRightBorderAll = wb.createCellStyle(); styleAlignRightBorderAll.setAlignment(styleAlignRightBorderAll.ALIGN_RIGHT); styleAlignRightBorderAll.setBorderTop(styleAlignRightBorderAll.BORDER_THIN); styleAlignRightBorderAll.setBorderBottom(styleAlignRightBorderAll.BORDER_THIN); styleAlignRightBorderAll.setBorderRight(styleAlignRightBorderAll.BORDER_THIN); styleAlignRightBorderAll.setBorderLeft(styleAlignRightBorderAll.BORDER_THIN); styleAlignRightBorderAll.setVerticalAlignment(styleAlignRightBorderAll.VERTICAL_CENTER); styleAlignRightBorderAll.setWrapText(true); HSSFCellStyle styleAlignLeftBorderAllNumber = wb.createCellStyle(); styleAlignLeftBorderAllNumber.setAlignment(styleAlignLeftBorderAllNumber.ALIGN_LEFT); styleAlignLeftBorderAllNumber.setDataFormat(currency.getFormat("#,##0.00")); styleAlignLeftBorderAllNumber.setBorderTop(styleAlignLeftBorderAllNumber.BORDER_THIN); styleAlignLeftBorderAllNumber.setBorderBottom(styleAlignLeftBorderAllNumber.BORDER_THIN); styleAlignLeftBorderAllNumber.setBorderRight(styleAlignLeftBorderAllNumber.BORDER_THIN); styleAlignLeftBorderAllNumber.setBorderLeft(styleAlignLeftBorderAllNumber.BORDER_THIN); styleAlignLeftBorderAllNumber.setVerticalAlignment(styleAlignLeftBorderAllNumber.VERTICAL_CENTER); styleAlignLeftBorderAllNumber.setWrapText(true); HSSFCellStyle styleAlignLeftBorderAll = wb.createCellStyle(); styleAlignLeftBorderAll.setAlignment(styleAlignLeftBorderAll.ALIGN_LEFT); styleAlignLeftBorderAll.setBorderTop(styleAlignLeftBorderAll.BORDER_THIN); styleAlignLeftBorderAll.setBorderBottom(styleAlignLeftBorderAll.BORDER_THIN); styleAlignLeftBorderAll.setBorderRight(styleAlignLeftBorderAll.BORDER_THIN); styleAlignLeftBorderAll.setBorderLeft(styleAlignLeftBorderAll.BORDER_THIN); styleAlignLeftBorderAll.setVerticalAlignment(styleAlignLeftBorderAll.VERTICAL_CENTER); styleAlignLeftBorderAll.setWrapText(true); HSSFCellStyle styleAlignRightBorderAllNumberRate = wb.createCellStyle(); styleAlignRightBorderAllNumberRate.setAlignment(styleAlignRightBorderAllNumberRate.ALIGN_RIGHT); styleAlignRightBorderAllNumberRate.setDataFormat(currency.getFormat("#,##0.0000")); styleAlignRightBorderAllNumberRate.setBorderTop(styleAlignRightBorderAllNumberRate.BORDER_THIN); styleAlignRightBorderAllNumberRate.setBorderBottom(styleAlignRightBorderAllNumberRate.BORDER_THIN); styleAlignRightBorderAllNumberRate.setBorderRight(styleAlignRightBorderAllNumberRate.BORDER_THIN); styleAlignRightBorderAllNumberRate.setBorderLeft(styleAlignRightBorderAllNumberRate.BORDER_THIN); styleAlignRightBorderAllNumberRate.setVerticalAlignment(styleAlignRightBorderAllNumberRate.VERTICAL_CENTER); styleAlignRightBorderAllNumberRate.setWrapText(true); HSSFRow row4 = sheet.createRow(6); for (int x = 0; x < 46; x++) { // if( (x > 8 && x < 12) || ( x>17 && x < 26) || x == 30 || x == 31){ row4.createCell(x).setCellStyle(styleBorderTop); // } } HSSFRow row5 = sheet.createRow(7); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("REF NO"); cell51.setCellStyle(styleHeader); sheet.autoSizeColumn(0); HSSFCell cell52 = row5.createCell(1); cell52.setCellValue("ISSUE DATE"); cell52.setCellStyle(styleHeader); sheet.autoSizeColumn(1); HSSFCell cell53 = row5.createCell(2); cell53.setCellValue("TOUR CODE"); cell53.setCellStyle(styleHeader); sheet.autoSizeColumn(2); HSSFCell cell54 = row5.createCell(3); cell54.setCellValue("INV NO"); cell54.setCellStyle(styleHeader); sheet.autoSizeColumn(3); HSSFCell cell55 = row5.createCell(4); cell55.setCellValue("INV DATE"); cell55.setCellStyle(styleHeader); sheet.autoSizeColumn(4); HSSFCell cell56 = row5.createCell(5); cell56.setCellValue("STAFF"); cell56.setCellStyle(styleHeader); sheet.autoSizeColumn(5); HSSFCell cell57 = row5.createCell(6); cell57.setCellValue("INV TO"); cell57.setCellStyle(styleHeader); sheet.autoSizeColumn(6); HSSFCell cell58 = row5.createCell(7); cell58.setCellValue("PAX"); cell58.setCellStyle(styleHeader); sheet.autoSizeColumn(7); HSSFCell cell59 = row5.createCell(10); cell59.setCellValue("COUNTRY"); cell59.setCellStyle(styleHeader); sheet.autoSizeColumn(10); HSSFCell cell60 = row5.createCell(11); cell60.setCellValue("CITY"); cell60.setCellStyle(styleHeader); sheet.autoSizeColumn(11); HSSFCell cell61 = row5.createCell(12); cell61.setCellValue("P TYPE"); cell61.setCellStyle(styleHeader); sheet.autoSizeColumn(12); HSSFCell cell62 = row5.createCell(13); cell62.setCellValue("DEPARTTURE"); cell62.setCellStyle(styleHeader); sheet.autoSizeColumn(13); HSSFCell cell63 = row5.createCell(14); cell63.setCellValue("NO TICKET"); cell63.setCellStyle(styleHeader); sheet.autoSizeColumn(14); HSSFCell cell64 = row5.createCell(15); cell64.setCellValue("DESCRIPTION"); cell64.setCellStyle(styleHeader); sheet.autoSizeColumn(15); HSSFCell cell65 = row5.createCell(16); cell65.setCellValue("PERIOD OF USED"); cell65.setCellStyle(styleHeader); sheet.autoSizeColumn(16); HSSFCell cell66 = row5.createCell(18); cell66.setCellValue("QTY"); cell66.setCellStyle(styleHeader); sheet.autoSizeColumn(18); HSSFCell cell67 = row5.createCell(21); cell67.setCellValue("BEFORE VAT"); cell67.setCellStyle(styleHeader); sheet.autoSizeColumn(21); HSSFCell cell68 = row5.createCell(22); cell68.setCellValue("PRICE"); cell68.setCellStyle(styleHeader); sheet.autoSizeColumn(22); HSSFCell cell69 = row5.createCell(23); cell69.setCellValue("ACC"); cell69.setCellStyle(styleHeader); sheet.autoSizeColumn(23); HSSFCell cell70 = row5.createCell(24); cell70.setCellValue("PAID"); cell70.setCellStyle(styleHeader); sheet.autoSizeColumn(24); HSSFCell cell71 = row5.createCell(33); cell71.setCellValue("AMOUNT LOCAL (R)"); cell71.setCellStyle(styleHeader); sheet.autoSizeColumn(33); HSSFCell cell72 = row5.createCell(34); cell72.setCellValue("AMOUNT LOCAL (P)"); cell72.setCellStyle(styleHeader); sheet.autoSizeColumn(34); HSSFCell cell73 = row5.createCell(35); cell73.setCellValue("VAT"); cell73.setCellStyle(styleHeader); sheet.autoSizeColumn(35); HSSFCell cell74 = row5.createCell(36); cell74.setCellValue("WHT"); cell74.setCellStyle(styleHeader); sheet.autoSizeColumn(36); HSSFCell cell75 = row5.createCell(37); cell75.setCellValue("GROSS PROFIT"); cell75.setCellStyle(styleHeader); sheet.autoSizeColumn(37); HSSFCell cell76 = row5.createCell(39); cell76.setCellValue("PAYCOM"); cell76.setCellStyle(styleHeader); sheet.autoSizeColumn(39); HSSFCell cell77 = row5.createCell(43); cell77.setCellValue("PROFIT BALANCE"); cell77.setCellStyle(styleHeader); sheet.autoSizeColumn(43); HSSFCell cell78 = row5.createCell(44); cell78.setCellValue("RECEIPT NO"); cell78.setCellStyle(styleHeader); sheet.autoSizeColumn(44); HSSFCell cell79 = row5.createCell(45); cell79.setCellValue("RECEIPT DATE"); cell79.setCellStyle(styleHeader); sheet.autoSizeColumn(45); // HSSFCell cell80 = row5.createCell(45); // cell80.setCellValue(""); // cell80.setCellStyle(styleHeader); // sheet.autoSizeColumn(45); sheet.addMergedRegion(CellRangeAddress.valueOf("H8:J8")); sheet.addMergedRegion(CellRangeAddress.valueOf("Q8:R8")); sheet.addMergedRegion(CellRangeAddress.valueOf("S8:U8")); sheet.addMergedRegion(CellRangeAddress.valueOf("Y8:AG8")); sheet.addMergedRegion(CellRangeAddress.valueOf("AL8:AM8")); sheet.addMergedRegion(CellRangeAddress.valueOf("AN8:AQ8")); // row5.createCell(11).setCellStyle(styleBorderRight); // row5.createCell(25).setCellStyle(styleBorderRight); // row5.createCell(31).setCellStyle(styleBorderRight); String merge[] = { "A", "B", "C", "D", "E", "F", "G", "K", "L", "M", "N", "O", "P", "V", "W", "X", "AH", "AI", "AJ", "AK", "AR", "AS", "AT" }; for (int x = 0; x < merge.length; x++) { sheet.addMergedRegion(CellRangeAddress.valueOf(merge[x] + "8:" + merge[x] + "9")); } HSSFRow row6 = sheet.createRow(8); for (int x = 0; x < 46; x++) { row6.createCell(x).setCellStyle(styleBorderBottomAndRight); } HSSFCell cell073 = row6.createCell(7); cell073.setCellValue("AD"); cell073.setCellStyle(styleHeader); sheet.autoSizeColumn(7); HSSFCell cell074 = row6.createCell(8); cell074.setCellValue("CH"); cell074.setCellStyle(styleHeader); sheet.autoSizeColumn(8); HSSFCell cell075 = row6.createCell(9); cell075.setCellValue("IN"); cell075.setCellStyle(styleHeader); sheet.autoSizeColumn(9); HSSFCell cell0018 = row6.createCell(16); cell0018.setCellValue("IN"); cell0018.setCellStyle(styleHeader); sheet.autoSizeColumn(16); HSSFCell cell0019 = row6.createCell(17); cell0019.setCellValue("OUT"); cell0019.setCellStyle(styleHeader); sheet.autoSizeColumn(17); HSSFCell cell0020 = row6.createCell(18); cell0020.setCellValue("ROOM"); cell0020.setCellStyle(styleHeader); sheet.autoSizeColumn(18); HSSFCell cell0021 = row6.createCell(19); cell0021.setCellValue("NIGHT"); cell0021.setCellStyle(styleHeader); sheet.autoSizeColumn(19); HSSFCell cell0022 = row6.createCell(20); cell0022.setCellValue("TTL"); cell0022.setCellStyle(styleHeader); sheet.autoSizeColumn(20); HSSFCell cell076 = row6.createCell(24); cell076.setCellValue("PV NO"); cell076.setCellStyle(styleHeader); sheet.autoSizeColumn(24); HSSFCell cell077 = row6.createCell(25); cell077.setCellValue("DATE"); cell077.setCellStyle(styleHeader); sheet.autoSizeColumn(25); HSSFCell cell078 = row6.createCell(26); cell078.setCellValue("INV SUP"); cell078.setCellStyle(styleHeader); sheet.autoSizeColumn(26); HSSFCell cell079 = row6.createCell(27); cell079.setCellValue("INV NO"); cell079.setCellStyle(styleHeader); sheet.autoSizeColumn(27); HSSFCell cell0079 = row6.createCell(28); cell0079.setCellValue("INV DATE"); cell0079.setCellStyle(styleHeader); sheet.autoSizeColumn(28); HSSFCell cell080 = row6.createCell(29); cell080.setCellValue("AMOUNT"); cell080.setCellStyle(styleHeader); sheet.autoSizeColumn(29); HSSFCell cell081 = row6.createCell(30); cell081.setCellValue("CUR"); cell081.setCellStyle(styleHeader); sheet.autoSizeColumn(30); HSSFCell cell082 = row6.createCell(31); cell082.setCellValue("REAL RATE"); cell082.setCellStyle(styleHeader); sheet.autoSizeColumn(31); HSSFCell cell083 = row6.createCell(32); cell083.setCellValue("PAY RATE"); cell083.setCellStyle(styleHeader); sheet.autoSizeColumn(32); HSSFCell cell084 = row6.createCell(37); cell084.setCellValue("REAL"); cell084.setCellStyle(styleHeader); sheet.autoSizeColumn(37); HSSFCell cell85 = row6.createCell(38); cell85.setCellValue("PAY"); cell85.setCellStyle(styleHeader); sheet.autoSizeColumn(38); HSSFCell cell86 = row6.createCell(39); cell86.setCellValue("DATE"); cell86.setCellStyle(styleHeader); sheet.autoSizeColumn(39); HSSFCell cell87 = row6.createCell(40); cell87.setCellValue("PAYEE"); cell87.setCellStyle(styleHeader); sheet.autoSizeColumn(40); HSSFCell cell88 = row6.createCell(41); cell88.setCellValue("PV NO"); cell88.setCellStyle(styleHeader); sheet.autoSizeColumn(41); HSSFCell cell89 = row6.createCell(42); cell89.setCellValue("COM"); cell89.setCellStyle(styleHeader); sheet.autoSizeColumn(42); int count = 9; for (int i = 0; i < listPaymentSummary.size(); i++) { PaymentOutboundAllDetail data = (PaymentOutboundAllDetail) listPaymentSummary.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell celldata00 = row.createCell(0); celldata00.setCellValue(String.valueOf(data.getRefno())); celldata00.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata01 = row.createCell(1); celldata01.setCellValue(String.valueOf(data.getIssuedate())); celldata01.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata02 = row.createCell(2); celldata02.setCellValue(String.valueOf(data.getTourcode())); celldata02.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata03 = row.createCell(3); celldata03.setCellValue(String.valueOf(data.getInvno())); celldata03.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata04 = row.createCell(4); celldata04.setCellValue(String.valueOf(data.getInvdate())); celldata04.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata05 = row.createCell(5); celldata05.setCellValue(String.valueOf(data.getStaff())); celldata05.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata06 = row.createCell(6); celldata06.setCellValue(String.valueOf(data.getInvto())); celldata06.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata07 = row.createCell(7); celldata07.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAdult())) ? 0 : (new BigDecimal(data.getAdult())).doubleValue()); celldata07.setCellStyle(styleDetailTableCenter); HSSFCell celldata08 = row.createCell(8); celldata08.setCellValue("".equalsIgnoreCase(String.valueOf(data.getChild())) ? 0 : (new BigDecimal(data.getChild())).doubleValue()); celldata08.setCellStyle(styleDetailTableCenter); HSSFCell celldata09 = row.createCell(9); celldata09.setCellValue("".equalsIgnoreCase(String.valueOf(data.getInfant())) ? 0 : (new BigDecimal(data.getInfant())).doubleValue()); celldata09.setCellStyle(styleDetailTableCenter); HSSFCell celldata10 = row.createCell(10); celldata10.setCellValue(String.valueOf(data.getCountry())); celldata10.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata11 = row.createCell(11); celldata11.setCellValue(String.valueOf(data.getCity())); celldata11.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata12 = row.createCell(12); celldata12.setCellValue(String.valueOf(data.getProducttype())); celldata12.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata13 = row.createCell(13); celldata13.setCellValue(String.valueOf(data.getDepartdate())); celldata13.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata14 = row.createCell(14); celldata14.setCellValue(String.valueOf(data.getNoticket())); celldata14.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata15 = row.createCell(15); celldata15.setCellValue(String.valueOf(data.getDescription())); celldata15.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata16 = row.createCell(16); celldata16.setCellValue(String.valueOf(data.getPeriodin())); celldata16.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata17 = row.createCell(17); celldata17.setCellValue(String.valueOf(data.getPeriodout())); celldata17.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata18 = row.createCell(18); celldata18.setCellValue("".equalsIgnoreCase(String.valueOf(data.getQtyroom())) ? 0 : (new BigDecimal(data.getQtyroom())).doubleValue()); celldata18.setCellStyle(styleDetailTableCenter); HSSFCell celldata19 = row.createCell(19); celldata19.setCellValue("".equalsIgnoreCase(String.valueOf(data.getQtynight())) ? 0 : (new BigDecimal(data.getQtynight())).doubleValue()); celldata19.setCellStyle(styleDetailTableCenter); HSSFCell celldata20 = row.createCell(20); celldata20.setCellValue("".equalsIgnoreCase(String.valueOf(data.getQtyttl())) ? 0 : (new BigDecimal(data.getQtyttl())).doubleValue()); celldata20.setCellStyle(styleDetailTableCenter); HSSFCell celldata21 = row.createCell(21); celldata21.setCellValue( "".equalsIgnoreCase(String.valueOf(data.getBeforevat())) || data.getBeforevat() == null ? 0 : (new BigDecimal(data.getBeforevat())).doubleValue()); celldata21.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell celldata22 = row.createCell(22); celldata22.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPrice())) ? 0 : (new BigDecimal(data.getPrice())).doubleValue()); celldata22.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell celldata23 = row.createCell(23); celldata23.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAcc())) ? 0 : (new BigDecimal(data.getAcc())).doubleValue()); celldata23.setCellStyle(styleDetailTableCenter); HSSFCell celldata24 = row.createCell(24); celldata24.setCellValue(String.valueOf(data.getPvno())); celldata24.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata25 = row.createCell(25); celldata25.setCellValue(String.valueOf(data.getPaydate())); celldata25.setCellStyle(styleDetailTableCenter); HSSFCell celldata26 = row.createCell(26); celldata26.setCellValue(String.valueOf(data.getInvsup())); celldata26.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata27 = row.createCell(27); celldata27.setCellValue(String.valueOf(data.getPayinvno())); celldata27.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata28 = row.createCell(28); celldata28.setCellValue(String.valueOf(data.getPayinvdate())); celldata28.setCellStyle(styleDetailTableCenter); HSSFCell celldata29 = row.createCell(29); celldata29.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmount())) ? 0 : (new BigDecimal(data.getAmount())).doubleValue()); celldata29.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell celldata30 = row.createCell(30); celldata30.setCellValue(String.valueOf(data.getPaycur())); celldata30.setCellStyle(styleDetailTableCenter); HSSFCell celldata31 = row.createCell(31); celldata31.setCellValue("".equalsIgnoreCase(String.valueOf(data.getRealrate())) ? 0 : (new BigDecimal(data.getRealrate())).doubleValue()); celldata31.setCellStyle(styleAlignRightBorderAllNumberRate); HSSFCell celldata32 = row.createCell(32); celldata32.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPayrate())) ? 0 : (new BigDecimal(data.getPayrate())).doubleValue()); celldata32.setCellStyle(styleAlignRightBorderAllNumberRate); HSSFCell celldata33 = row.createCell(33); celldata33.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmountlocalr())) ? 0 : (new BigDecimal(data.getAmountlocalr())).doubleValue()); celldata33.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell celldata34 = row.createCell(34); celldata34.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmountlocalp())) ? 0 : (new BigDecimal(data.getAmountlocalp())).doubleValue()); celldata34.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell celldata35 = row.createCell(35); celldata35.setCellValue("".equalsIgnoreCase(String.valueOf(data.getVat())) ? 0 : (new BigDecimal(data.getVat())).doubleValue()); celldata35.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell celldata36 = row.createCell(36); celldata36.setCellValue("".equalsIgnoreCase(String.valueOf(data.getWht())) ? 0 : (new BigDecimal(data.getWht())).doubleValue()); celldata36.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell celldata37 = row.createCell(37); celldata37.setCellValue("".equalsIgnoreCase(String.valueOf(data.getGrossreal())) ? 0 : (new BigDecimal(data.getGrossreal())).doubleValue()); celldata37.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell celldata38 = row.createCell(38); celldata38.setCellValue("".equalsIgnoreCase(String.valueOf(data.getGrosspay())) ? 0 : (new BigDecimal(data.getGrosspay())).doubleValue()); celldata38.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell celldata39 = row.createCell(39); celldata39.setCellValue(String.valueOf(data.getPaycomdate().trim())); celldata39.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata40 = row.createCell(40); celldata40.setCellValue(String.valueOf(data.getPaycomstaff())); celldata40.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata41 = row.createCell(41); celldata41.setCellValue(String.valueOf(data.getPaycompvno())); celldata41.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata42 = row.createCell(42); System.out.println("data.getPaycommission() : " + data.getPaycommission()); celldata42.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPaycommission())) ? 0 : (new BigDecimal(data.getPaycommission().replaceAll(",", ""))).doubleValue()); celldata42.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell celldata43 = row.createCell(43); celldata43.setCellValue("".equalsIgnoreCase(String.valueOf(data.getBalanceprofit())) ? 0 : (new BigDecimal(data.getBalanceprofit())).doubleValue()); celldata43.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell celldata44 = row.createCell(44); celldata44.setCellValue(String.valueOf(data.getReceiptno())); celldata44.setCellStyle(styleAlignLeftBorderAll); HSSFCell celldata45 = row.createCell(45); celldata45.setCellValue(String.valueOf(data.getReceiptdate())); celldata45.setCellStyle(styleAlignLeftBorderAll); if (i == (listPaymentSummary.size() - 1)) { row = sheet.createRow(count + i + 1); for (int k = 0; k < 46; k++) { HSSFCellStyle styleSum = wb.createCellStyle(); styleSum.setAlignment(styleSum.ALIGN_RIGHT); styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCell cellSum = row.createCell(k); if (k == 0) { styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN); } if (k == 45) { styleSum.setBorderRight(HSSFCellStyle.BORDER_THIN); } cellSum.setCellStyle(styleSum); } HSSFCellStyle styleSum = wb.createCellStyle(); styleSum.setFont(excelFunction.getTotalDetailBoldFont(wb.createFont())); styleSum.setAlignment(styleSum.ALIGN_RIGHT); styleSum.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSum.setBorderRight(HSSFCellStyle.BORDER_THIN); styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSum.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleSum.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleSumCenter = wb.createCellStyle(); styleSumCenter.setFont(excelFunction.getTotalDetailBoldFont(wb.createFont())); styleSumCenter.setAlignment(styleSum.ALIGN_CENTER); styleSumCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSumCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); styleSumCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSumCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleSumCenter.setDataFormat(currency.getFormat("#,##0")); String sumAd = "SUM(H" + 10 + ":H" + (count + i + 1) + ")"; String sumCh = "SUM(I" + 10 + ":I" + (count + i + 1) + ")"; String sumIn = "SUM(J" + 10 + ":J" + (count + i + 1) + ")"; String sumRoom = "SUM(S" + 10 + ":S" + (count + i + 1) + ")"; String sumNight = "SUM(T" + 10 + ":T" + (count + i + 1) + ")"; String sumTtl = "SUM(U" + 10 + ":U" + (count + i + 1) + ")"; String sumBeforeVat = "SUM(V" + 10 + ":V" + (count + i + 1) + ")"; String sumPrice = "SUM(W" + 10 + ":W" + (count + i + 1) + ")"; String sumAmount = "SUM(AD" + 10 + ":AD" + (count + i + 1) + ")"; String sumLocalR = "SUM(AH" + 10 + ":AH" + (count + i + 1) + ")"; String sumLocalP = "SUM(AI" + 10 + ":AI" + (count + i + 1) + ")"; String sumVat = "SUM(AJ" + 10 + ":AJ" + (count + i + 1) + ")"; String sumWht = "SUM(AK" + 10 + ":AK" + (count + i + 1) + ")"; String sumGrossReal = "SUM(AL" + 10 + ":AL" + (count + i + 1) + ")"; String sumGrossPay = "SUM(AM" + 10 + ":AM" + (count + i + 1) + ")"; String sumPaycomCom = "SUM(AQ" + 10 + ":AQ" + (count + i + 1) + ")"; String sumProfitBalance = "SUM(AR" + 10 + ":AR" + (count + i + 1) + ")"; HSSFCell cell5Sum = row.createCell(6); cell5Sum.setCellValue("Total"); cell5Sum.setCellStyle(styleSumCenter); HSSFCell cell7Sum = row.createCell(7); cell7Sum.setCellFormula(sumAd); cell7Sum.setCellStyle(styleSumCenter); HSSFCell cell8Sum = row.createCell(8); cell8Sum.setCellFormula(sumCh); cell8Sum.setCellStyle(styleSumCenter); HSSFCell cell9Sum = row.createCell(9); cell9Sum.setCellFormula(sumIn); cell9Sum.setCellStyle(styleSumCenter); HSSFCell cell18Sum = row.createCell(18); cell18Sum.setCellFormula(sumRoom); cell18Sum.setCellStyle(styleSumCenter); HSSFCell cell19Sum = row.createCell(19); cell19Sum.setCellFormula(sumNight); cell19Sum.setCellStyle(styleSumCenter); HSSFCell cell20Sum = row.createCell(20); cell20Sum.setCellFormula(sumTtl); cell20Sum.setCellStyle(styleSumCenter); HSSFCell cell21Sum = row.createCell(21); cell21Sum.setCellFormula(sumBeforeVat); cell21Sum.setCellStyle(styleSum); HSSFCell cell22Sum = row.createCell(22); cell22Sum.setCellFormula(sumPrice); cell22Sum.setCellStyle(styleSum); HSSFCell cell29Sum = row.createCell(29); cell29Sum.setCellFormula(sumAmount); cell29Sum.setCellStyle(styleSum); HSSFCell cell33Sum = row.createCell(33); cell33Sum.setCellFormula(sumLocalR); cell33Sum.setCellStyle(styleSum); HSSFCell cell34Sum = row.createCell(34); cell34Sum.setCellFormula(sumLocalP); cell34Sum.setCellStyle(styleSum); HSSFCell cell35Sum = row.createCell(35); cell35Sum.setCellFormula(sumVat); cell35Sum.setCellStyle(styleSum); HSSFCell cell36Sum = row.createCell(36); cell36Sum.setCellFormula(sumWht); cell36Sum.setCellStyle(styleSum); HSSFCell cell37Sum = row.createCell(37); cell37Sum.setCellFormula(sumGrossReal); cell37Sum.setCellStyle(styleSum); HSSFCell cell38Sum = row.createCell(38); cell38Sum.setCellFormula(sumGrossPay); cell38Sum.setCellStyle(styleSum); HSSFCell cell42Sum = row.createCell(42); cell42Sum.setCellFormula(sumPaycomCom); cell42Sum.setCellStyle(styleSum); HSSFCell cell43Sum = row.createCell(43); cell43Sum.setCellFormula(sumProfitBalance); cell43Sum.setCellStyle(styleSum); } } sheet.setColumnWidth(2, 256 * 20); sheet.setColumnWidth(3, 256 * 15); sheet.setColumnWidth(4, 256 * 15); sheet.setColumnWidth(5, 256 * 30); sheet.setColumnWidth(6, 256 * 15); sheet.setColumnWidth(10, 256 * 15); sheet.setColumnWidth(11, 256 * 15); sheet.setColumnWidth(12, 256 * 15); sheet.setColumnWidth(16, 256 * 15); sheet.setColumnWidth(17, 256 * 15); sheet.setColumnWidth(18, 256 * 10); sheet.setColumnWidth(19, 256 * 10); sheet.setColumnWidth(20, 256 * 10); sheet.setColumnWidth(21, 256 * 15); sheet.setColumnWidth(22, 256 * 15); sheet.setColumnWidth(23, 256 * 15); sheet.setColumnWidth(24, 256 * 15); sheet.setColumnWidth(25, 256 * 15); sheet.setColumnWidth(26, 256 * 30); sheet.setColumnWidth(27, 256 * 15); sheet.setColumnWidth(28, 256 * 15); sheet.setColumnWidth(29, 256 * 15); sheet.setColumnWidth(35, 256 * 10); sheet.setColumnWidth(36, 256 * 10); sheet.setColumnWidth(37, 256 * 15); sheet.setColumnWidth(38, 256 * 15); sheet.setColumnWidth(39, 256 * 15); sheet.setColumnWidth(40, 256 * 20); sheet.setColumnWidth(41, 256 * 15); sheet.setColumnWidth(42, 256 * 15); }
From source file:com.smi.travel.controller.excel.checking.airticket.AirlineSummary.java
private void getSummaryAirline(HSSFWorkbook wb, List summaryAirline) { List<SummaryAirline> listAR = summaryAirline; String sheetName = "Sheet1";// name of sheet HSSFSheet sheet = wb.createSheet(sheetName); UtilityExcelFunction excelFunction = new UtilityExcelFunction(); // Set align Text HSSFCellStyle styleAlignRight = wb.createCellStyle(); styleAlignRight.setAlignment(styleAlignRight.ALIGN_RIGHT); HSSFCellStyle styleAlignLeft = wb.createCellStyle(); styleAlignLeft.setAlignment(styleAlignLeft.ALIGN_LEFT); HSSFCellStyle styleAlignCenter = wb.createCellStyle(); styleAlignCenter.setAlignment(styleAlignCenter.ALIGN_CENTER); HSSFCellStyle styleBorderLeft = wb.createCellStyle(); styleBorderLeft.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); HSSFCellStyle styleBorderRight = wb.createCellStyle(); styleBorderRight.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleNumber = wb.createCellStyle(); styleNumber.setAlignment(styleNumber.ALIGN_RIGHT); styleNumber.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleNumberBorderRight = wb.createCellStyle(); styleNumberBorderRight.setAlignment(styleNumberBorderRight.ALIGN_RIGHT); styleNumberBorderRight.setDataFormat(currency.getFormat("#,##0.00")); styleNumberBorderRight.setBorderRight(styleNumberBorderRight.BORDER_THIN); // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0);/* www .j av a2 s. c o m*/ HSSFCell cell0 = row1.createCell(0); cell0.setCellValue("LIST SUMMARY AIRLINE"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cell0.setCellStyle(styleC1); sheet.autoSizeColumn(0); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:I1")); // Row 2 HSSFRow row2 = sheet.createRow(1); HSSFCell cell1 = row2.createCell(0); cell1.setCellValue("Invoice Date : "); cell1.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell2 = row2.createCell(1); Date date = new Date(); SimpleDateFormat sm = new SimpleDateFormat("dd-MM-yyyy"); String strDate = sm.format(date); cell2.setCellValue(strDate); cell2.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); HSSFCell cell3 = row2.createCell(2); cell3.setCellValue(" To "); cell3.setCellStyle(styleAlignCenter); sheet.autoSizeColumn(2); HSSFCell cell4 = row2.createCell(3); cell4.setCellValue(strDate); cell4.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(3); HSSFCell cell5 = row2.createCell(4); cell5.setCellValue("Print on : "); cell5.setCellStyle(styleAlignRight); sheet.autoSizeColumn(4); HSSFCell cell6 = row2.createCell(5); cell6.setCellValue(strDate); cell6.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(5); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Print By : "); cell31.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue("Adminstarator"); cell32.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); sheet.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); HSSFCell cell33 = row3.createCell(4); cell33.setCellValue("Rounting Detail : "); cell33.setCellStyle(styleAlignRight); sheet.autoSizeColumn(4); HSSFCell cell34 = row3.createCell(5); cell34.setCellValue("XXXXXXXXXX"); cell34.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(5); // Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Type Rounting : "); cell41.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue("XXXXXXXX"); cell42.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); sheet.addMergedRegion(CellRangeAddress.valueOf("B4:D4")); HSSFCell cell43 = row4.createCell(4); cell43.setCellValue("Passenger : "); cell43.setCellStyle(styleAlignRight); sheet.autoSizeColumn(4); HSSFCell cell44 = row4.createCell(5); cell44.setCellValue("XXXXXXXXXX"); cell44.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(5); // Row 5 HSSFRow row5 = sheet.createRow(4); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Air : "); cell51.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell52 = row5.createCell(1); cell52.setCellValue("XXXXXXXX"); cell52.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5")); HSSFCell cell53 = row5.createCell(4); cell53.setCellValue("Sale Staff : "); cell53.setCellStyle(styleAlignRight); sheet.autoSizeColumn(4); HSSFCell cell54 = row5.createCell(5); cell54.setCellValue("XXXXXXXXXX"); cell54.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(5); // Row 6 HSSFRow row6 = sheet.createRow(5); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Agent Name : "); cell61.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue("XXXXXXXX"); cell62.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); sheet.addMergedRegion(CellRangeAddress.valueOf("B6:D6")); HSSFCell cell63 = row6.createCell(4); cell63.setCellValue("Department : "); cell63.setCellStyle(styleAlignRight); sheet.autoSizeColumn(4); HSSFCell cell64 = row6.createCell(5); cell64.setCellValue("XXXXXXXXXX"); cell64.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(5); // Row 7 HSSFRow row7 = sheet.createRow(6); HSSFCell cell71 = row7.createCell(0); cell71.setCellValue("Term Pay : "); cell71.setCellStyle(styleAlignRight); sheet.autoSizeColumn(0); HSSFCell cell72 = row7.createCell(1); cell72.setCellValue("XXXXXXXX"); cell72.setCellStyle(styleAlignLeft); sheet.autoSizeColumn(1); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:D5")); // Header Table HSSFCellStyle styleHeader = wb.createCellStyle(); styleHeader.setFont(excelFunction.getHeaderTable(wb.createFont())); styleHeader.setAlignment(styleHeader.ALIGN_CENTER); styleHeader.setBorderTop(styleHeader.BORDER_THIN); styleHeader.setBorderLeft(styleHeader.BORDER_THIN); styleHeader.setBorderBottom(styleHeader.BORDER_THIN); styleHeader.setBorderRight(styleHeader.BORDER_THIN); HSSFCellStyle styleDetailTable = wb.createCellStyle(); styleDetailTable.setAlignment(styleDetailTable.ALIGN_LEFT); styleDetailTable.setBorderLeft(styleDetailTable.BORDER_THIN); styleDetailTable.setBorderRight(styleDetailTable.BORDER_THIN); HSSFCellStyle styleDetailTableNumber = wb.createCellStyle(); styleDetailTableNumber.setDataFormat(currency.getFormat("#,##0.00")); styleDetailTableNumber.setAlignment(styleDetailTableNumber.ALIGN_RIGHT); styleDetailTableNumber.setBorderLeft(styleDetailTableNumber.BORDER_THIN); styleDetailTableNumber.setBorderRight(styleDetailTableNumber.BORDER_THIN); HSSFCellStyle styleDetailTableBorderBottom = wb.createCellStyle(); styleDetailTableBorderBottom.setBorderTop(styleDetailTableBorderBottom.BORDER_THIN); HSSFRow rowH = sheet.createRow(8); HSSFCell cellH1 = rowH.createCell(0); cellH1.setCellValue("Rounting"); cellH1.setCellStyle(styleHeader); sheet.autoSizeColumn(0); HSSFCell cellH2 = rowH.createCell(1); cellH2.setCellValue("Pax"); cellH2.setCellStyle(styleHeader); sheet.autoSizeColumn(1); HSSFCell cellH3 = rowH.createCell(2); cellH3.setCellValue("Net Sales"); cellH3.setCellStyle(styleHeader); sheet.autoSizeColumn(2); HSSFCell cellH4 = rowH.createCell(3); cellH4.setCellValue("Tax"); cellH4.setCellStyle(styleHeader); sheet.autoSizeColumn(3); HSSFCell cellH5 = rowH.createCell(4); cellH5.setCellValue("Ins"); cellH5.setCellStyle(styleHeader); sheet.autoSizeColumn(4); HSSFCell cellH6 = rowH.createCell(5); cellH6.setCellValue("Comms"); cellH6.setCellStyle(styleHeader); sheet.autoSizeColumn(5); HSSFCell cellH7 = rowH.createCell(6); cellH7.setCellValue("Amount Wendy"); cellH7.setCellStyle(styleHeader); sheet.autoSizeColumn(6); HSSFCell cellH8 = rowH.createCell(7); cellH8.setCellValue("Amount Inbound"); cellH8.setCellStyle(styleHeader); sheet.autoSizeColumn(7); HSSFCell cellH9 = rowH.createCell(8); cellH9.setCellValue("Diff"); cellH9.setCellStyle(styleHeader); sheet.autoSizeColumn(8); if (listAR != null && listAR.size() != 0) { int count = 9 + listAR.size(); int start = 11; int end = 0; int num = 0; for (int r = 9; r < count; r++) { if (num <= (listAR.size() - 1)) { HSSFRow rowH1 = sheet.createRow(r); HSSFCell cellH11 = rowH1.createCell(0); cellH11.setCellValue(listAR.get(num).getRouting()); cellH11.setCellStyle(styleDetailTable); sheet.autoSizeColumn(0); HSSFCell cellH21 = rowH1.createCell(1); cellH21.setCellValue((listAR.get(num).getPax() != null) ? listAR.get(num).getPax().doubleValue() : new BigDecimal("0").doubleValue()); cellH21.setCellStyle(styleDetailTableNumber); sheet.autoSizeColumn(1); HSSFCell cellH31 = rowH1.createCell(2); cellH31.setCellValue( (listAR.get(num).getNetsale() != null) ? listAR.get(num).getNetsale().doubleValue() : new BigDecimal("0").doubleValue()); cellH31.setCellStyle(styleDetailTableNumber); sheet.autoSizeColumn(2); HSSFCell cellH41 = rowH1.createCell(3); cellH41.setCellValue((listAR.get(num).getTax() != null) ? listAR.get(num).getTax().doubleValue() : new BigDecimal("0").doubleValue()); cellH41.setCellStyle(styleDetailTableNumber); sheet.autoSizeColumn(3); HSSFCell cellH51 = rowH1.createCell(4); cellH51.setCellValue((listAR.get(num).getIns() != null) ? listAR.get(num).getIns().doubleValue() : new BigDecimal("0").doubleValue()); cellH51.setCellStyle(styleDetailTableNumber); sheet.autoSizeColumn(4); HSSFCell cellH61 = rowH1.createCell(5); cellH61.setCellValue( (listAR.get(num).getComms() != null) ? listAR.get(num).getComms().doubleValue() : new BigDecimal("0").doubleValue()); cellH61.setCellStyle(styleDetailTableNumber); sheet.autoSizeColumn(5); HSSFCell cellH71 = rowH1.createCell(6); cellH71.setCellValue((listAR.get(num).getAmountwendy() != null) ? listAR.get(num).getAmountwendy().doubleValue() : new BigDecimal("0").doubleValue()); cellH71.setCellStyle(styleDetailTableNumber); sheet.autoSizeColumn(6); HSSFCell cellH81 = rowH1.createCell(7); cellH81.setCellValue((listAR.get(num).getAmountinbound() != null) ? listAR.get(num).getAmountinbound().doubleValue() : new BigDecimal("0").doubleValue()); cellH81.setCellStyle(styleDetailTableNumber); sheet.autoSizeColumn(7); HSSFCell cellH91 = rowH1.createCell(8); cellH91.setCellValue( (listAR.get(num).getDiff() != null) ? listAR.get(num).getDiff().doubleValue() : new BigDecimal("0").doubleValue()); cellH91.setCellStyle(styleDetailTableNumber); sheet.autoSizeColumn(8); num++; } } String sumPax = "SUM(B" + 10 + ":B" + (count - 1) + ")"; String sumNetSales = "SUM(C" + 10 + ":C" + (count - 1) + ")"; String sumTax = "SUM(D" + 10 + ":D" + (count - 1) + ")"; String sumIns = "SUM(E" + 10 + ":E" + (count - 1) + ")"; String sumComms = "SUM(F" + 10 + ":F" + (count - 1) + ")"; String sumAmountWendy = "SUM(G" + 10 + ":G" + (count - 1) + ")"; String sumAmountInbound = "SUM(H" + 10 + ":H" + (count - 1) + ")"; String sumDiff = "SUM(I" + 10 + ":I" + (count - 1) + ")"; HSSFRow row = sheet.createRow(count); HSSFCell cell6Sum = row.createCell(1); cell6Sum.setCellFormula(sumPax); cell6Sum.setCellStyle(styleDetailTableNumber); HSSFCell cell7Sum = row.createCell(2); cell7Sum.setCellFormula(sumNetSales); cell7Sum.setCellStyle(styleDetailTableNumber); HSSFCell cell8Sum = row.createCell(3); cell8Sum.setCellFormula(sumTax); cell8Sum.setCellStyle(styleDetailTableNumber); HSSFCell cell9Sum = row.createCell(4); cell9Sum.setCellFormula(sumIns); cell9Sum.setCellStyle(styleDetailTableNumber); HSSFCell cell10Sum = row.createCell(5); cell10Sum.setCellFormula(sumComms); cell10Sum.setCellStyle(styleDetailTableNumber); HSSFCell cell11Sum = row.createCell(6); cell11Sum.setCellFormula(sumAmountWendy); cell11Sum.setCellStyle(styleDetailTableNumber); HSSFCell cell12Sum = row.createCell(7); cell12Sum.setCellFormula(sumAmountInbound); cell12Sum.setCellStyle(styleDetailTableNumber); HSSFCell cell13Sum = row.createCell(8); cell13Sum.setCellFormula(sumDiff); cell13Sum.setCellStyle(styleDetailTableNumber); HSSFRow rowL = sheet.createRow(count + 1); rowL.createCell(0).setCellStyle(styleDetailTableBorderBottom); rowL.createCell(1).setCellStyle(styleDetailTableBorderBottom); rowL.createCell(2).setCellStyle(styleDetailTableBorderBottom); rowL.createCell(3).setCellStyle(styleDetailTableBorderBottom); rowL.createCell(4).setCellStyle(styleDetailTableBorderBottom); rowL.createCell(5).setCellStyle(styleDetailTableBorderBottom); rowL.createCell(6).setCellStyle(styleDetailTableBorderBottom); rowL.createCell(7).setCellStyle(styleDetailTableBorderBottom); rowL.createCell(8).setCellStyle(styleDetailTableBorderBottom); } }
From source file:com.smi.travel.controller.excel.checking.airticket.AirlineSummary.java
public void genTicketFareSummaryAirlineReport(HSSFWorkbook wb, List ticketSummaryAirline) { String sheetNamePax = "Pax"; // name of sheet String sheetNameDetail = "Detail"; String sheetNameRounting = "Rounting"; HSSFSheet sheetPax = wb.createSheet(sheetNamePax); HSSFSheet sheetDetail = wb.createSheet(sheetNameDetail); HSSFSheet sheetRounting = wb.createSheet(sheetNameRounting); UtilityExcelFunction excelFunction = new UtilityExcelFunction(); TicketSummaryAirlineView dataheader = new TicketSummaryAirlineView(); HSSFDataFormat currency = wb.createDataFormat(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC22.ALIGN_CENTER); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC25.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleC26 = wb.createCellStyle(); styleC26.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC26.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC26.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC26.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC26.setDataFormat(currency.getFormat("#,##0")); styleC26.setAlignment(styleC22.ALIGN_CENTER); HSSFCellStyle styleC27 = wb.createCellStyle(); styleC27.setAlignment(styleC27.ALIGN_RIGHT); styleC27.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleC28 = wb.createCellStyle(); styleC28.setAlignment(styleC28.ALIGN_CENTER); styleC28.setDataFormat(currency.getFormat("#,##0")); HSSFCellStyle styleC29 = wb.createCellStyle(); styleC29.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC29.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC29.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC29.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC30 = wb.createCellStyle(); styleC30.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC30.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC30.setBorderTop(HSSFCellStyle.BORDER_THIN); styleC30.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC30.setAlignment(styleC22.ALIGN_CENTER); HSSFCellStyle stylebordertotal = wb.createCellStyle(); stylebordertotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCellStyle stylebordertotalleft = wb.createCellStyle(); stylebordertotalleft.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylebordertotalleft.setBorderBottom(HSSFCellStyle.BORDER_THIN); if (!ticketSummaryAirline.isEmpty()) { dataheader = (TicketSummaryAirlineView) ticketSummaryAirline.get(0); for (int x = 1; x < 4; x++) { if (x == 1) { // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheetPax.createRow(0); HSSFCell cell1 = row1.createCell(0); cell1.setCellValue("List Summary Airline"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cell1.setCellStyle(styleC1); sheetPax.addMergedRegion(CellRangeAddress.valueOf("A1:E1")); // Row 2 HSSFRow row2 = sheetPax.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Invoice Date : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(dataheader.getHeaderdatefrom()); cell22.setCellStyle(styleC22); //sheetPax.addMergedRegion(CellRangeAddress.valueOf("B2:C2")); HSSFCell cell23 = row2.createCell(2); if (!"".equalsIgnoreCase(dataheader.getHeaderdateto())) { cell23.setCellValue(" to " + dataheader.getHeaderdateto()); cell23.setCellStyle(styleC22); }/* w ww.j a va 2s . co m*/ HSSFCell cell24 = row2.createCell(3); cell24.setCellValue("Print on : "); cell24.setCellStyle(styleC21); HSSFCell cell25 = row2.createCell(4); cell25.setCellValue(dataheader.getHeaderprinton()); cell25.setCellStyle(styleC22); // Row 3 HSSFRow row3 = sheetPax.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Issue Date : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(dataheader.getHeaderissuedatefrom()); cell32.setCellStyle(styleC22); HSSFCell cell33 = row3.createCell(2); if (!"".equalsIgnoreCase(dataheader.getHeaderissuedateto())) { cell33.setCellValue(" to " + dataheader.getHeaderissuedateto()); cell33.setCellStyle(styleC22); } HSSFCell cell34 = row3.createCell(3); cell34.setCellValue("Routing Detail : "); cell34.setCellStyle(styleC21); HSSFCell cell35 = row3.createCell(4); cell35.setCellValue(dataheader.getHeaderroutingdetail()); cell35.setCellStyle(styleC22); // Row 4 HSSFRow row4 = sheetPax.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Type Routing : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(dataheader.getHeadertyperouting()); cell42.setCellStyle(styleC22); HSSFCell cell43 = row4.createCell(3); cell43.setCellValue("Passenger : "); cell43.setCellStyle(styleC21); HSSFCell cell44 = row4.createCell(4); cell44.setCellValue(dataheader.getHeaderpassenger()); cell44.setCellStyle(styleC22); // Row 5 HSSFRow row5 = sheetPax.createRow(4); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Airline Code : "); cell51.setCellStyle(styleC21); HSSFCell cell52 = row5.createCell(1); cell52.setCellValue(dataheader.getHeaderair()); cell52.setCellStyle(styleC22); HSSFCell cell53 = row5.createCell(3); cell53.setCellValue("Sale Staff : "); cell53.setCellStyle(styleC21); HSSFCell cell54 = row5.createCell(4); cell54.setCellValue(dataheader.getHeadersalestaff()); cell54.setCellStyle(styleC22); // Row 6 HSSFRow row6 = sheetPax.createRow(5); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Agent Name : "); cell61.setCellStyle(styleC21); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue(dataheader.getHeaderagentname()); cell62.setCellStyle(styleC22); HSSFCell cell63 = row6.createCell(3); cell63.setCellValue("Department : "); cell63.setCellStyle(styleC21); HSSFCell cell64 = row6.createCell(4); cell64.setCellValue(dataheader.getHeaderdepartment()); cell64.setCellStyle(styleC22); // Row 7 HSSFRow row7 = sheetPax.createRow(6); HSSFCell cell71 = row7.createCell(0); cell71.setCellValue("Term Pay : "); cell71.setCellStyle(styleC21); HSSFCell cell72 = row7.createCell(1); cell72.setCellValue(dataheader.getHeadertermpay()); cell72.setCellStyle(styleC22); HSSFCell cell73 = row7.createCell(3); cell73.setCellValue("Print By : "); cell73.setCellStyle(styleC21); HSSFCell cell74 = row7.createCell(4); cell74.setCellValue(dataheader.getHeaderprintby()); cell74.setCellStyle(styleC22); } if (x == 2) { // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheetDetail.createRow(0); HSSFCell cell1 = row1.createCell(0); cell1.setCellValue("List Summary Airline"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cell1.setCellStyle(styleC1); sheetDetail.addMergedRegion(CellRangeAddress.valueOf("A1:E1")); // Row 2 HSSFRow row2 = sheetDetail.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Invoice Date : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(dataheader.getHeaderdatefrom()); cell22.setCellStyle(styleC22); //sheetDetail.addMergedRegion(CellRangeAddress.valueOf("B2:C2")); HSSFCell cell23 = row2.createCell(2); if (!"".equalsIgnoreCase(dataheader.getHeaderdateto())) { cell23.setCellValue(" to " + dataheader.getHeaderdateto()); cell23.setCellStyle(styleC22); } HSSFCell cell24 = row2.createCell(3); cell24.setCellValue("Print on : "); cell24.setCellStyle(styleC21); HSSFCell cell25 = row2.createCell(4); cell25.setCellValue(dataheader.getHeaderprinton()); cell25.setCellStyle(styleC22); // Row 3 HSSFRow row3 = sheetDetail.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Issue Date : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(dataheader.getHeaderissuedatefrom()); cell32.setCellStyle(styleC22); HSSFCell cell33 = row3.createCell(2); if (!"".equalsIgnoreCase(dataheader.getHeaderissuedateto())) { cell33.setCellValue(" to " + dataheader.getHeaderissuedateto()); cell33.setCellStyle(styleC22); } HSSFCell cell34 = row3.createCell(3); cell34.setCellValue("Routing Detail : "); cell34.setCellStyle(styleC21); HSSFCell cell35 = row3.createCell(4); cell35.setCellValue(dataheader.getHeaderroutingdetail()); cell35.setCellStyle(styleC22); // Row 4 HSSFRow row4 = sheetDetail.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Type Routing : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(dataheader.getHeadertyperouting()); cell42.setCellStyle(styleC22); HSSFCell cell43 = row4.createCell(3); cell43.setCellValue("Passenger : "); cell43.setCellStyle(styleC21); HSSFCell cell44 = row4.createCell(4); cell44.setCellValue(dataheader.getHeaderpassenger()); cell44.setCellStyle(styleC22); // Row 5 HSSFRow row5 = sheetDetail.createRow(4); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Airline Code : "); cell51.setCellStyle(styleC21); HSSFCell cell52 = row5.createCell(1); cell52.setCellValue(dataheader.getHeaderair()); cell52.setCellStyle(styleC22); HSSFCell cell53 = row5.createCell(3); cell53.setCellValue("Sale Staff : "); cell53.setCellStyle(styleC21); HSSFCell cell54 = row5.createCell(4); cell54.setCellValue(dataheader.getHeadersalestaff()); cell54.setCellStyle(styleC22); // Row 6 HSSFRow row6 = sheetDetail.createRow(5); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Agent Name : "); cell61.setCellStyle(styleC21); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue(dataheader.getHeaderagentname()); cell62.setCellStyle(styleC22); HSSFCell cell63 = row6.createCell(3); cell63.setCellValue("Department : "); cell63.setCellStyle(styleC21); HSSFCell cell64 = row6.createCell(4); cell64.setCellValue(dataheader.getHeaderdepartment()); cell64.setCellStyle(styleC22); // Row 7 HSSFRow row7 = sheetDetail.createRow(6); HSSFCell cell71 = row7.createCell(0); cell71.setCellValue("Term Pay : "); cell71.setCellStyle(styleC21); HSSFCell cell72 = row7.createCell(1); cell72.setCellValue(dataheader.getHeadertermpay()); cell72.setCellStyle(styleC22); HSSFCell cell73 = row7.createCell(3); cell73.setCellValue("Print By : "); cell73.setCellStyle(styleC21); HSSFCell cell74 = row7.createCell(4); cell74.setCellValue(dataheader.getHeaderprintby()); cell74.setCellStyle(styleC22); } if (x == 3) { // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheetRounting.createRow(0); HSSFCell cell1 = row1.createCell(0); cell1.setCellValue("List Summary Airline"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cell1.setCellStyle(styleC1); sheetRounting.addMergedRegion(CellRangeAddress.valueOf("A1:E1")); // Row 2 HSSFRow row2 = sheetRounting.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Invoice Date : "); cell21.setCellStyle(styleC21); HSSFCell cell22 = row2.createCell(1); cell22.setCellValue(dataheader.getHeaderdatefrom()); cell22.setCellStyle(styleC22); //sheetRounting.addMergedRegion(CellRangeAddress.valueOf("B2:C2")); HSSFCell cell23 = row2.createCell(2); if (!"".equalsIgnoreCase(dataheader.getHeaderdateto())) { cell23.setCellValue(" to " + dataheader.getHeaderdateto()); cell23.setCellStyle(styleC22); } HSSFCell cell24 = row2.createCell(3); cell24.setCellValue("Print on : "); cell24.setCellStyle(styleC21); HSSFCell cell25 = row2.createCell(4); cell25.setCellValue(dataheader.getHeaderprinton()); cell25.setCellStyle(styleC22); // Row 3 HSSFRow row3 = sheetRounting.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Issue Date : "); cell31.setCellStyle(styleC21); HSSFCell cell32 = row3.createCell(1); cell32.setCellValue(dataheader.getHeaderissuedatefrom()); cell32.setCellStyle(styleC22); HSSFCell cell33 = row3.createCell(2); if (!"".equalsIgnoreCase(dataheader.getHeaderissuedateto())) { cell33.setCellValue(" to " + dataheader.getHeaderissuedateto()); cell33.setCellStyle(styleC22); } HSSFCell cell34 = row3.createCell(3); cell34.setCellValue("Routing Detail : "); cell34.setCellStyle(styleC21); HSSFCell cell35 = row3.createCell(4); cell35.setCellValue(dataheader.getHeaderroutingdetail()); cell35.setCellStyle(styleC22); // Row 4 HSSFRow row4 = sheetRounting.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Type Routing : "); cell41.setCellStyle(styleC21); HSSFCell cell42 = row4.createCell(1); cell42.setCellValue(dataheader.getHeadertyperouting()); cell42.setCellStyle(styleC22); HSSFCell cell43 = row4.createCell(3); cell43.setCellValue("Passenger : "); cell43.setCellStyle(styleC21); HSSFCell cell44 = row4.createCell(4); cell44.setCellValue(dataheader.getHeaderpassenger()); cell44.setCellStyle(styleC22); // Row 5 HSSFRow row5 = sheetRounting.createRow(4); HSSFCell cell51 = row5.createCell(0); cell51.setCellValue("Airline Code : "); cell51.setCellStyle(styleC21); HSSFCell cell52 = row5.createCell(1); cell52.setCellValue(dataheader.getHeaderair()); cell52.setCellStyle(styleC22); HSSFCell cell53 = row5.createCell(3); cell53.setCellValue("Sale Staff : "); cell53.setCellStyle(styleC21); HSSFCell cell54 = row5.createCell(4); cell54.setCellValue(dataheader.getHeadersalestaff()); cell54.setCellStyle(styleC22); // Row 6 HSSFRow row6 = sheetRounting.createRow(5); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Agent Name : "); cell61.setCellStyle(styleC21); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue(dataheader.getHeaderagentname()); cell62.setCellStyle(styleC22); HSSFCell cell63 = row6.createCell(3); cell63.setCellValue("Department : "); cell63.setCellStyle(styleC21); HSSFCell cell64 = row6.createCell(4); cell64.setCellValue(dataheader.getHeaderdepartment()); cell64.setCellStyle(styleC22); // Row 7 HSSFRow row7 = sheetRounting.createRow(6); HSSFCell cell71 = row7.createCell(0); cell71.setCellValue("Term Pay : "); cell71.setCellStyle(styleC21); HSSFCell cell72 = row7.createCell(1); cell72.setCellValue(dataheader.getHeadertermpay()); cell72.setCellStyle(styleC22); HSSFCell cell73 = row7.createCell(3); cell73.setCellValue("Print By : "); cell73.setCellStyle(styleC21); HSSFCell cell74 = row7.createCell(4); cell74.setCellValue(dataheader.getHeaderprintby()); cell74.setCellStyle(styleC22); } } // Header Table HSSFCellStyle styleC3 = wb.createCellStyle(); styleC3.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3.setAlignment(styleC3.ALIGN_CENTER); styleC3.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3.setBorderTop(HSSFCellStyle.BORDER_THIN); int count = 9; int tempcount = 0; int tempcount2 = 0; int tempcount3 = 0; int rowdetail = 0; int rowrouting = 0; int x = 0; int y = 0; for (int i = 0; i < ticketSummaryAirline.size(); i++) { TicketSummaryAirlineView data = (TicketSummaryAirlineView) ticketSummaryAirline.get(i); if ("pax".equalsIgnoreCase(data.getPage())) { //pax HSSFRow row8 = sheetPax.createRow(8); HSSFCell cell81 = row8.createCell(0); cell81.setCellValue("Payment Type"); cell81.setCellStyle(styleC3); // sheetPax.autoSizeColumn(0); HSSFCell cell82 = row8.createCell(1); cell82.setCellValue("Type Routing"); cell82.setCellStyle(styleC3); // sheetPax.autoSizeColumn(1); HSSFCell cell83 = row8.createCell(2); cell83.setCellValue("Pax"); // sheetPax.autoSizeColumn(2); cell83.setCellStyle(styleC3); HSSFCell cell84 = row8.createCell(3); cell84.setCellValue("Net Sales"); cell84.setCellStyle(styleC3); // sheetPax.autoSizeColumn(3); HSSFCell cell85 = row8.createCell(4); cell85.setCellValue("Tax"); cell85.setCellStyle(styleC3); // sheetPax.autoSizeColumn(4); HSSFCell cell86 = row8.createCell(5); cell86.setCellValue("Ins."); cell86.setCellStyle(styleC3); // sheetPax.autoSizeColumn(5); HSSFCell cell87 = row8.createCell(6); cell87.setCellValue("Comms"); cell87.setCellStyle(styleC3); // sheetPax.autoSizeColumn(6); HSSFCell cell88 = row8.createCell(7); cell88.setCellValue("Amount Wendy"); cell88.setCellStyle(styleC3); // sheetPax.autoSizeColumn(7); HSSFCell cell89 = row8.createCell(8); cell89.setCellValue("Amount Outbound"); cell89.setCellStyle(styleC3); // sheetPax.autoSizeColumn(8); HSSFCell cell90 = row8.createCell(9); cell90.setCellValue("Amount Inbound"); cell90.setCellStyle(styleC3); // sheetPax.autoSizeColumn(9); HSSFRow row = sheetPax.createRow(count + i); HSSFCell celldata0 = row.createCell(0); celldata0.setCellValue(String.valueOf(data.getPaymenttypeP())); celldata0.setCellStyle(styleC30); HSSFCell celldata1 = row.createCell(1); celldata1.setCellValue(String.valueOf(data.getTyperoutingP())); celldata1.setCellStyle(styleC30); HSSFCell celldata2 = row.createCell(2); celldata2.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPaxP())) ? 0 : (new BigDecimal(data.getPaxP())).doubleValue()); celldata2.setCellStyle(styleC26); HSSFCell celldata3 = row.createCell(3); celldata3.setCellValue("".equalsIgnoreCase(String.valueOf(data.getNetsalesP())) ? 0 : (new BigDecimal(data.getNetsalesP())).doubleValue()); celldata3.setCellStyle(styleC25); HSSFCell celldata4 = row.createCell(4); celldata4.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTaxP())) ? 0 : (new BigDecimal(data.getTaxP())).doubleValue()); celldata4.setCellStyle(styleC25); HSSFCell celldata5 = row.createCell(5); celldata5.setCellValue("".equalsIgnoreCase(String.valueOf(data.getInsP())) ? 0 : (new BigDecimal(data.getInsP())).doubleValue()); celldata5.setCellStyle(styleC25); HSSFCell celldata6 = row.createCell(6); celldata6.setCellValue("".equalsIgnoreCase(String.valueOf(data.getCommsP())) ? 0 : (new BigDecimal(data.getCommsP())).doubleValue()); celldata6.setCellStyle(styleC25); HSSFCell celldata7 = row.createCell(7); celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmountwendyP())) ? 0 : (new BigDecimal(data.getAmountwendyP())).doubleValue()); celldata7.setCellStyle(styleC25); HSSFCell celldata8 = row.createCell(8); celldata8.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmountoutboundP())) ? 0 : (new BigDecimal(data.getAmountoutboundP())).doubleValue()); celldata8.setCellStyle(styleC25); HSSFCell celldata9 = row.createCell(9); celldata9.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmountinboundP())) ? 0 : (new BigDecimal(data.getAmountinboundP())).doubleValue()); celldata9.setCellStyle(styleC25); tempcount = count + i + 1; } //detail if ("detail".equalsIgnoreCase(data.getPage())) { //Total Pax HSSFRow rowtotal = sheetPax.createRow(tempcount); String totalPax = "SUM(C" + 10 + ":C" + (tempcount) + ")"; String totalNet = "SUM(D" + 10 + ":D" + (tempcount) + ")"; String totalTax = "SUM(E" + 10 + ":E" + (tempcount) + ")"; String totalIns = "SUM(F" + 10 + ":F" + (tempcount) + ")"; String totalComms = "SUM(G" + 10 + ":G" + (tempcount) + ")"; String totalAmountWen = "SUM(H" + 10 + ":H" + (tempcount) + ")"; String totalAmountOut = "SUM(I" + 10 + ":I" + (tempcount) + ")"; String totalAmountIn = "SUM(J" + 10 + ":J" + (tempcount) + ")"; HSSFCellStyle styleTotal = wb.createCellStyle(); styleTotal.setFont(excelFunction.getHeaderTable(wb.createFont())); styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTotal.setAlignment(styleC22.ALIGN_RIGHT); HSSFCell cellTotal0 = rowtotal.createCell(0); cellTotal0.setCellStyle(styleC29); HSSFCell cellTotal00 = rowtotal.createCell(1); cellTotal00.setCellValue("Total"); cellTotal00.setCellStyle(styleTotal); HSSFCell cellTotal01 = rowtotal.createCell(2); cellTotal01.setCellFormula(totalPax); cellTotal01.setCellStyle(styleC26); HSSFCell cellTotal02 = rowtotal.createCell(3); cellTotal02.setCellFormula(totalNet); cellTotal02.setCellStyle(styleC25); HSSFCell cellTotal03 = rowtotal.createCell(4); cellTotal03.setCellFormula(totalTax); cellTotal03.setCellStyle(styleC25); HSSFCell cellTotal04 = rowtotal.createCell(5); cellTotal04.setCellFormula(totalIns); cellTotal04.setCellStyle(styleC25); HSSFCell cellTotal05 = rowtotal.createCell(6); cellTotal05.setCellFormula(totalComms); cellTotal05.setCellStyle(styleC25); HSSFCell cellTotal06 = rowtotal.createCell(7); cellTotal06.setCellFormula(totalAmountWen); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = rowtotal.createCell(8); cellTotal07.setCellFormula(totalAmountOut); cellTotal07.setCellStyle(styleC25); HSSFCell cellTotal08 = rowtotal.createCell(9); cellTotal08.setCellFormula(totalAmountIn); cellTotal08.setCellStyle(styleC25); // if(tempcount != 0){ // rowdetail = tempcount+3; // } HSSFRow row8 = sheetDetail.createRow(8); HSSFCell cell81 = row8.createCell(0); cell81.setCellValue("Invoice No"); cell81.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(0); HSSFCell cell82 = row8.createCell(1); cell82.setCellValue("Invoice Date"); cell82.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(1); HSSFCell cell83 = row8.createCell(2); cell83.setCellValue("Department"); // sheetDetail.autoSizeColumn(2); cell83.setCellStyle(styleC3); HSSFCell cell84 = row8.createCell(3); cell84.setCellValue("Staff"); cell84.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(3); HSSFCell cell85 = row8.createCell(4); cell85.setCellValue("Term Pay"); cell85.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(4); HSSFCell cell86 = row8.createCell(5); cell86.setCellValue("Passenger"); cell86.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(5); HSSFCell cell87 = row8.createCell(6); cell87.setCellValue("Type Payment"); cell87.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(6); HSSFCell cell88 = row8.createCell(7); cell88.setCellValue("Type Routing"); cell88.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(7); HSSFCell cell89 = row8.createCell(8); cell89.setCellValue("Routing"); cell89.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(8); HSSFCell cell90 = row8.createCell(9); cell90.setCellValue("Pax"); cell90.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(9); HSSFCell cell91 = row8.createCell(10); cell91.setCellValue("Air"); cell91.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(10); HSSFCell cell92 = row8.createCell(11); cell92.setCellValue("Ticket No"); cell92.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(11); HSSFCell cell93 = row8.createCell(12); cell93.setCellValue("Ref No."); cell93.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(12); HSSFCell cell94 = row8.createCell(13); cell94.setCellValue("Issue Date"); cell94.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(13); HSSFCell cell95 = row8.createCell(14); cell95.setCellValue("Net Sales"); cell95.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(14); HSSFCell cell96 = row8.createCell(15); cell96.setCellValue("Tax"); cell96.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(15); HSSFCell cell97 = row8.createCell(16); cell97.setCellValue("Ins."); cell97.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(16); HSSFCell cell98 = row8.createCell(17); cell98.setCellValue("Comms"); cell98.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(17); HSSFCell cell99 = row8.createCell(18); cell99.setCellValue("Amount Wendy"); cell99.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(18); HSSFCell cell910 = row8.createCell(19); cell910.setCellValue("Amount Outbound"); cell910.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(19); HSSFCell cell911 = row8.createCell(20); cell911.setCellValue("Amount Inbound"); cell911.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(20); HSSFCell cell912 = row8.createCell(21); cell912.setCellValue("Amt No Invoice"); cell912.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(21); HSSFCell cell913 = row8.createCell(22); cell913.setCellValue("Amt Business Trip"); cell913.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(22); HSSFCell cell914 = row8.createCell(23); cell914.setCellValue("Amt Annual Leave"); cell914.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(23); HSSFCell cell915 = row8.createCell(24); cell915.setCellValue("Amt Refund"); cell915.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(24); HSSFCell cell916 = row8.createCell(25); cell916.setCellValue("Remarks"); cell916.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(25); HSSFCell cell917 = row8.createCell(26); cell917.setCellValue("Diff"); cell917.setCellStyle(styleC3); // sheetDetail.autoSizeColumn(26); HSSFRow row = sheetDetail.createRow(count + x); HSSFCell celldata0 = row.createCell(0); celldata0.setCellValue(String.valueOf(data.getInvnoD())); celldata0.setCellStyle(styleC29); HSSFCell celldata1 = row.createCell(1); celldata1.setCellValue(String.valueOf(data.getInvdateD())); celldata1.setCellStyle(styleC30); HSSFCell celldata2 = row.createCell(2); celldata2.setCellValue(String.valueOf(data.getDepartmentD())); celldata2.setCellStyle(styleC29); HSSFCell celldata3 = row.createCell(3); celldata3.setCellValue(String.valueOf(data.getStaffD())); celldata3.setCellStyle(styleC29); HSSFCell celldata4 = row.createCell(4); celldata4.setCellValue(String.valueOf(data.getTermpayD())); celldata4.setCellStyle(styleC29); HSSFCell celldata5 = row.createCell(5); celldata5.setCellValue(String.valueOf(data.getPassengerD())); celldata5.setCellStyle(styleC29); HSSFCell celldata6 = row.createCell(6); celldata6.setCellValue(String.valueOf(data.getTypepaymentD())); celldata6.setCellStyle(styleC29); HSSFCell celldata7 = row.createCell(7); celldata7.setCellValue(String.valueOf(data.getTyperoutingD())); celldata7.setCellStyle(styleC29); HSSFCell celldata8 = row.createCell(8); celldata8.setCellValue(String.valueOf(data.getRoutingD())); celldata8.setCellStyle(styleC29); HSSFCell celldata9 = row.createCell(9); celldata9.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPaxD())) ? 0 : (new BigDecimal(data.getPaxD())).doubleValue()); celldata9.setCellStyle(styleC26); HSSFCell celldata10 = row.createCell(10); celldata10.setCellValue(String.valueOf(data.getAirD())); celldata10.setCellStyle(styleC30); HSSFCell celldata11 = row.createCell(11); celldata11.setCellValue(String.valueOf(data.getTicketnoD())); celldata11.setCellStyle(styleC29); HSSFCell celldata12 = row.createCell(12); celldata12.setCellValue(String.valueOf(data.getRefnoD())); celldata12.setCellStyle(styleC29); HSSFCell celldata13 = row.createCell(13); celldata13.setCellValue(String.valueOf(data.getIssuedateD())); celldata13.setCellStyle(styleC29); HSSFCell celldata14 = row.createCell(14); celldata14.setCellValue("".equalsIgnoreCase(String.valueOf(data.getNetsalesD())) ? 0 : (new BigDecimal(data.getNetsalesD())).doubleValue()); celldata14.setCellStyle(styleC25); HSSFCell celldata15 = row.createCell(15); celldata15.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTaxD())) ? 0 : (new BigDecimal(data.getTaxD())).doubleValue()); celldata15.setCellStyle(styleC25); HSSFCell celldata16 = row.createCell(16); celldata16.setCellValue("".equalsIgnoreCase(String.valueOf(data.getInsD())) ? 0 : (new BigDecimal(data.getInsD())).doubleValue()); celldata16.setCellStyle(styleC25); HSSFCell celldata17 = row.createCell(17); celldata17.setCellValue("".equalsIgnoreCase(String.valueOf(data.getCommsD())) ? 0 : (new BigDecimal(data.getCommsD())).doubleValue()); celldata17.setCellStyle(styleC25); HSSFCell celldata18 = row.createCell(18); celldata18.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmountwendyD())) ? 0 : (new BigDecimal(data.getAmountwendyD())).doubleValue()); celldata18.setCellStyle(styleC25); HSSFCell celldata19 = row.createCell(19); celldata19.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmountoutboundD())) ? 0 : (new BigDecimal(data.getAmountoutboundD())).doubleValue()); celldata19.setCellStyle(styleC25); HSSFCell celldata20 = row.createCell(20); celldata20.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmountinboundD())) ? 0 : (new BigDecimal(data.getAmountinboundD())).doubleValue()); celldata20.setCellStyle(styleC25); HSSFCell celldata21 = row.createCell(21); celldata21.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmtnoinvoiceD())) ? 0 : (new BigDecimal(data.getAmtnoinvoiceD())).doubleValue()); celldata21.setCellStyle(styleC25); HSSFCell celldata22 = row.createCell(22); celldata22.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmtbusinesstripD())) ? 0 : (new BigDecimal(data.getAmtbusinesstripD())).doubleValue()); celldata22.setCellStyle(styleC25); HSSFCell celldata23 = row.createCell(23); celldata23.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmtannualleaveD())) ? 0 : (new BigDecimal(data.getAmtannualleaveD())).doubleValue()); celldata23.setCellStyle(styleC25); HSSFCell celldata24 = row.createCell(24); celldata24.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmtrefundD())) ? 0 : (new BigDecimal(data.getAmtrefundD())).doubleValue()); celldata24.setCellStyle(styleC25); HSSFCell celldata25 = row.createCell(25); celldata25.setCellValue(String.valueOf(data.getRemarksD())); celldata25.setCellStyle(styleC29); HSSFCell celldata26 = row.createCell(26); celldata26.setCellValue("".equalsIgnoreCase(String.valueOf(data.getDiffD())) ? 0 : (new BigDecimal(data.getDiffD())).doubleValue()); celldata26.setCellStyle(styleC25); x++; } if ("routing".equalsIgnoreCase(data.getPage())) { tempcount2 = count + x; HSSFRow rowtotal = sheetDetail.createRow(tempcount2); String totalPax = "SUM(J" + 10 + ":J" + (tempcount2) + ")"; String totalNet = "SUM(O" + 10 + ":O" + (tempcount2) + ")"; String totalTax = "SUM(P" + 10 + ":P" + (tempcount2) + ")"; String totalIns = "SUM(Q" + 10 + ":Q" + (tempcount2) + ")"; String totalComms = "SUM(R" + 10 + ":R" + (tempcount2) + ")"; String totalAmountWen = "SUM(S" + 10 + ":S" + (tempcount2) + ")"; String totalAmountOut = "SUM(T" + 10 + ":T" + (tempcount2) + ")"; String totalAmountIn = "SUM(U" + 10 + ":U" + (tempcount2) + ")"; String totalAmountNoInv = "SUM(V" + 10 + ":V" + (tempcount2) + ")"; String totalAmountBuss = "SUM(W" + 10 + ":W" + (tempcount2) + ")"; String totalAmountAnn = "SUM(X" + 10 + ":X" + (tempcount2) + ")"; String totalAmountRef = "SUM(Y" + 10 + ":Y" + (tempcount2) + ")"; String totalAmountDiff = "SUM(AA" + 10 + ":AA" + (tempcount2) + ")"; HSSFCellStyle styleTotal = wb.createCellStyle(); styleTotal.setFont(excelFunction.getHeaderTable(wb.createFont())); styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTotal.setAlignment(styleC22.ALIGN_RIGHT); HSSFCell cellTotal000 = rowtotal.createCell(0); cellTotal000.setCellStyle(stylebordertotalleft); HSSFCell cellTotal001 = rowtotal.createCell(1); cellTotal001.setCellStyle(stylebordertotal); HSSFCell cellTotal002 = rowtotal.createCell(2); cellTotal002.setCellStyle(stylebordertotal); HSSFCell cellTotal003 = rowtotal.createCell(3); cellTotal003.setCellStyle(stylebordertotal); HSSFCell cellTotal004 = rowtotal.createCell(4); cellTotal004.setCellStyle(stylebordertotal); HSSFCell cellTotal005 = rowtotal.createCell(5); cellTotal005.setCellStyle(stylebordertotal); HSSFCell cellTotal006 = rowtotal.createCell(6); cellTotal006.setCellStyle(stylebordertotal); HSSFCell cellTotal007 = rowtotal.createCell(7); cellTotal007.setCellStyle(stylebordertotal); HSSFCell cellTotal008 = rowtotal.createCell(10); cellTotal008.setCellStyle(stylebordertotal); HSSFCell cellTotal009 = rowtotal.createCell(11); cellTotal009.setCellStyle(stylebordertotal); HSSFCell cellTotal010 = rowtotal.createCell(12); cellTotal010.setCellStyle(stylebordertotal); HSSFCell cellTotal011 = rowtotal.createCell(13); cellTotal011.setCellStyle(stylebordertotal); HSSFCell cellTotal012 = rowtotal.createCell(25); cellTotal012.setCellStyle(stylebordertotal); HSSFCell cellTotal00 = rowtotal.createCell(8); cellTotal00.setCellValue("Total"); cellTotal00.setCellStyle(styleTotal); HSSFCell cellTotal01 = rowtotal.createCell(9); cellTotal01.setCellFormula(totalPax); cellTotal01.setCellStyle(styleC26); HSSFCell cellTotal02 = rowtotal.createCell(14); cellTotal02.setCellFormula(totalNet); cellTotal02.setCellStyle(styleC25); HSSFCell cellTotal03 = rowtotal.createCell(15); cellTotal03.setCellFormula(totalTax); cellTotal03.setCellStyle(styleC25); HSSFCell cellTotal04 = rowtotal.createCell(16); cellTotal04.setCellFormula(totalIns); cellTotal04.setCellStyle(styleC25); HSSFCell cellTotal05 = rowtotal.createCell(17); cellTotal05.setCellFormula(totalComms); cellTotal05.setCellStyle(styleC25); HSSFCell cellTotal06 = rowtotal.createCell(18); cellTotal06.setCellFormula(totalAmountWen); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = rowtotal.createCell(19); cellTotal07.setCellFormula(totalAmountOut); cellTotal07.setCellStyle(styleC25); HSSFCell cellTotal08 = rowtotal.createCell(20); cellTotal08.setCellFormula(totalAmountIn); cellTotal08.setCellStyle(styleC25); HSSFCell cellTotal09 = rowtotal.createCell(21); cellTotal09.setCellFormula(totalAmountNoInv); cellTotal09.setCellStyle(styleC25); HSSFCell cellTotal10 = rowtotal.createCell(22); cellTotal10.setCellFormula(totalAmountBuss); cellTotal10.setCellStyle(styleC25); HSSFCell cellTotal11 = rowtotal.createCell(23); cellTotal11.setCellFormula(totalAmountAnn); cellTotal11.setCellStyle(styleC25); HSSFCell cellTotal12 = rowtotal.createCell(24); cellTotal12.setCellFormula(totalAmountRef); cellTotal12.setCellStyle(styleC25); HSSFCell cellTotal13 = rowtotal.createCell(26); cellTotal13.setCellFormula(totalAmountDiff); cellTotal13.setCellStyle(styleC25); // if(tempcount2 != 0){ // rowrouting = tempcount2+3; // } HSSFRow row8 = sheetRounting.createRow(8); HSSFCell cell81 = row8.createCell(0); cell81.setCellValue("Routing"); cell81.setCellStyle(styleC3); // sheetRounting.autoSizeColumn(0); HSSFCell cell82 = row8.createCell(1); cell82.setCellValue("Pax"); cell82.setCellStyle(styleC3); // sheetRounting.autoSizeColumn(1); HSSFCell cell83 = row8.createCell(2); cell83.setCellValue("Net Sales"); // sheetRounting.autoSizeColumn(2); cell83.setCellStyle(styleC3); HSSFCell cell84 = row8.createCell(3); cell84.setCellValue("Tax"); cell84.setCellStyle(styleC3); // sheetRounting.autoSizeColumn(3); HSSFCell cell85 = row8.createCell(4); cell85.setCellValue("Ins."); cell85.setCellStyle(styleC3); // sheetRounting.autoSizeColumn(4); HSSFCell cell86 = row8.createCell(5); cell86.setCellValue("Comms"); cell86.setCellStyle(styleC3); // sheetRounting.autoSizeColumn(5); HSSFCell cell87 = row8.createCell(6); cell87.setCellValue("Amount Wendy"); cell87.setCellStyle(styleC3); // sheetRounting.autoSizeColumn(6); HSSFCell cell88 = row8.createCell(7); cell88.setCellValue("Amount Outbound"); cell88.setCellStyle(styleC3); // sheetRounting.autoSizeColumn(7); HSSFCell cell89 = row8.createCell(8); cell89.setCellValue("Amount Inbound"); cell89.setCellStyle(styleC3); // sheetRounting.autoSizeColumn(8); HSSFCell cell90 = row8.createCell(9); cell90.setCellValue("Diff"); cell90.setCellStyle(styleC3); // sheetRounting.autoSizeColumn(9); HSSFRow row = sheetRounting.createRow(count + y); HSSFCell celldata0 = row.createCell(0); celldata0.setCellValue(String.valueOf(data.getRoutingR())); celldata0.setCellStyle(styleC29); HSSFCell celldata1 = row.createCell(1); celldata1.setCellValue("".equalsIgnoreCase(String.valueOf(data.getPaxR())) ? 0 : (new BigDecimal(data.getPaxR())).doubleValue()); celldata1.setCellStyle(styleC26); HSSFCell celldata2 = row.createCell(2); celldata2.setCellValue("".equalsIgnoreCase(String.valueOf(data.getNetsalesR())) ? 0 : (new BigDecimal(data.getNetsalesR())).doubleValue()); celldata2.setCellStyle(styleC25); HSSFCell celldata3 = row.createCell(3); celldata3.setCellValue("".equalsIgnoreCase(String.valueOf(data.getTaxR())) ? 0 : (new BigDecimal(data.getTaxR())).doubleValue()); celldata3.setCellStyle(styleC25); HSSFCell celldata4 = row.createCell(4); celldata4.setCellValue("".equalsIgnoreCase(String.valueOf(data.getInsR())) ? 0 : (new BigDecimal(data.getInsR())).doubleValue()); celldata4.setCellStyle(styleC25); HSSFCell celldata5 = row.createCell(5); celldata5.setCellValue("".equalsIgnoreCase(String.valueOf(data.getCommsR())) ? 0 : (new BigDecimal(data.getCommsR())).doubleValue()); celldata5.setCellStyle(styleC25); HSSFCell celldata6 = row.createCell(6); celldata6.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmountwendyR())) ? 0 : (new BigDecimal(data.getAmountwendyR())).doubleValue()); celldata6.setCellStyle(styleC25); HSSFCell celldata7 = row.createCell(7); celldata7.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmountoutboundR())) ? 0 : (new BigDecimal(data.getAmountoutboundR())).doubleValue()); celldata7.setCellStyle(styleC25); HSSFCell celldata8 = row.createCell(8); celldata8.setCellValue("".equalsIgnoreCase(String.valueOf(data.getAmountinboundR())) ? 0 : (new BigDecimal(data.getAmountinboundR())).doubleValue()); celldata8.setCellStyle(styleC25); HSSFCell celldata9 = row.createCell(9); celldata9.setCellValue("".equalsIgnoreCase(String.valueOf(data.getDiffR())) ? 0 : (new BigDecimal(data.getDiffR())).doubleValue()); celldata9.setCellStyle(styleC25); y++; } if (i == (ticketSummaryAirline.size() - 1)) { tempcount3 = count + y; HSSFRow rowtotal = sheetRounting.createRow(tempcount3); String totalPax = "SUM(B" + 10 + ":B" + (tempcount3) + ")"; String totalNet = "SUM(C" + 10 + ":C" + (tempcount3) + ")"; String totalTax = "SUM(D" + 10 + ":D" + (tempcount3) + ")"; String totalIns = "SUM(E" + 10 + ":E" + (tempcount3) + ")"; String totalComms = "SUM(F" + 10 + ":F" + (tempcount3) + ")"; String totalAmountWen = "SUM(G" + 10 + ":G" + (tempcount3) + ")"; String totalAmountOut = "SUM(H" + 10 + ":H" + (tempcount3) + ")"; String totalAmountIn = "SUM(I" + 10 + ":I" + (tempcount3) + ")"; String totalAmountDiff = "SUM(J" + 10 + ":J" + (tempcount3) + ")"; HSSFCellStyle styleTotal = wb.createCellStyle(); styleTotal.setFont(excelFunction.getHeaderTable(wb.createFont())); styleTotal.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderTop(HSSFCellStyle.BORDER_THIN); styleTotal.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTotal.setAlignment(styleC22.ALIGN_RIGHT); HSSFCell cellTotal00 = rowtotal.createCell(0); cellTotal00.setCellValue("Total"); cellTotal00.setCellStyle(styleTotal); HSSFCell cellTotal01 = rowtotal.createCell(1); cellTotal01.setCellFormula(totalPax); cellTotal01.setCellStyle(styleC26); HSSFCell cellTotal02 = rowtotal.createCell(2); cellTotal02.setCellFormula(totalNet); cellTotal02.setCellStyle(styleC25); HSSFCell cellTotal03 = rowtotal.createCell(3); cellTotal03.setCellFormula(totalTax); cellTotal03.setCellStyle(styleC25); HSSFCell cellTotal04 = rowtotal.createCell(4); cellTotal04.setCellFormula(totalIns); cellTotal04.setCellStyle(styleC25); HSSFCell cellTotal05 = rowtotal.createCell(5); cellTotal05.setCellFormula(totalComms); cellTotal05.setCellStyle(styleC25); HSSFCell cellTotal06 = rowtotal.createCell(6); cellTotal06.setCellFormula(totalAmountWen); cellTotal06.setCellStyle(styleC25); HSSFCell cellTotal07 = rowtotal.createCell(7); cellTotal07.setCellFormula(totalAmountOut); cellTotal07.setCellStyle(styleC25); HSSFCell cellTotal08 = rowtotal.createCell(8); cellTotal08.setCellFormula(totalAmountIn); cellTotal08.setCellStyle(styleC25); HSSFCell cellTotal09 = rowtotal.createCell(9); cellTotal09.setCellFormula(totalAmountDiff); cellTotal09.setCellStyle(styleC25); } } } for (int i = 0; i < 30; i++) { sheetPax.autoSizeColumn(i); sheetDetail.autoSizeColumn(i); sheetRounting.autoSizeColumn(i); } sheetDetail.setColumnWidth(15, 256 * 12); sheetDetail.setColumnWidth(16, 256 * 12); sheetPax.setColumnWidth(5, 256 * 12); sheetRounting.setColumnWidth(5, 256 * 12); sheetRounting.setColumnWidth(9, 256 * 12); }