List of usage examples for org.apache.poi.hssf.usermodel HSSFRow setHeightInPoints
@Override public void setHeightInPoints(float height)
From source file:br.edu.tglima.model.result.PlanilhaXLS.java
License:Open Source License
/** * Mtodo responsvel por gerar a planilha XLS. * /* www . j a va2 s .c o m*/ * * @param arquivo Referente ao local e nome do arquivo. * @return Retorno do tipo Boolean, indicando se o arquivo foi gerado * com sucesso, ou no. */ public boolean gerarPlanilha(File arquivo) { try { /*Verificamos se j existe um arquivo com esse "nome". Caso ele exista, ele ser removido e o novo arquivo ser gerado.*/ if (workbook.getNumberOfSheets() > 0) { workbook.removeSheetAt(0); } HSSFSheet sheet = workbook.createSheet("Valores Exportados"); /* Criando as linhas. --------------------------------------------- */ HSSFRow header1 = sheet.createRow((short) 0); HSSFRow linha02 = sheet.createRow((short) 1); HSSFRow linha03 = sheet.createRow((short) 2); HSSFRow linha04 = sheet.createRow((short) 3); HSSFRow linha05 = sheet.createRow((short) 4); HSSFRow header2 = sheet.createRow((short) 7); HSSFRow linha09 = sheet.createRow((short) 8); HSSFRow linha10 = sheet.createRow((short) 9); HSSFRow linha11 = sheet.createRow((short) 10); HSSFRow linha12 = sheet.createRow((short) 11); HSSFRow linha13 = sheet.createRow((short) 12); HSSFRow linha14 = sheet.createRow((short) 13); HSSFRow linha15 = sheet.createRow((short) 14); HSSFRow linha17 = sheet.createRow((short) 16); HSSFRow header3 = sheet.createRow((short) 19); HSSFRow linha21 = sheet.createRow((short) 20); HSSFRow linha22 = sheet.createRow((short) 21); HSSFRow linha23 = sheet.createRow((short) 22); HSSFRow linha25 = sheet.createRow((short) 24); // ------------------------------------------------------------------------ // /* Mesclando as clulas. ------------------------------------------ */ sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 2)); sheet.addMergedRegion(new CellRangeAddress(19, 19, 0, 2)); // ------------------------------------------------------------------------ // /* Definindo a largura das colunas. ------------------------------- */ sheet.setColumnWidth(0, 12000); sheet.setColumnWidth(1, 5200); sheet.setColumnWidth(2, 6500); // ------------------------------------------------------------------------ // /* Definindo os estilos das clulas. ------------------------------ */ CellStyle headerStyle = workbook.createCellStyle(); Font headerFont = workbook.createFont(); headerFont.setFontHeightInPoints((short) 20); headerStyle.setFont(headerFont); headerStyle.setAlignment(HorizontalAlignment.CENTER); CellStyle cellCentered = workbook.createCellStyle(); cellCentered.setAlignment(HorizontalAlignment.CENTER); CellStyle cellFontBold = workbook.createCellStyle(); Font fontBold = workbook.createFont(); fontBold.setBold(true); cellFontBold.setFont(fontBold); CellStyle cellResulted = workbook.createCellStyle(); Font resultFont = workbook.createFont(); resultFont.setBold(true); cellResulted.setFont(resultFont); cellResulted.setAlignment(HorizontalAlignment.CENTER); Cell cell; // ------------------------------------------------------------------------ // /* Criando as Colunas da Tabela ----------------------------------- */ //Colunas da linha 1 header1.createCell(0).setCellValue("Dados Fornecidos"); //Colunas da linha 2 linha02.createCell(0).setCellValue("Data de Entrada"); linha02.createCell(2).setCellValue(this.rst.getDataEntrada()); //Colunas da Linha 3 linha03.createCell(0).setCellValue("Data de Sada"); linha03.createCell(2).setCellValue(this.rst.getDataSaida()); //Colunas da Linha 4 linha04.createCell(0).setCellValue("Salrio Informado"); linha04.createCell(2).setCellValue(this.rst.getSalario()); //Colunas da Linha 5 linha05.createCell(0).setCellValue("Motivo da Sada"); linha05.createCell(2).setCellValue(this.rst.getMotivoRes()); //Colunas da linha 8 header2.createCell(0).setCellValue("Resciso"); //Colunas da linha 9 linha09.createCell(0).setCellValue("Item"); linha09.createCell(1).setCellValue("Referncia"); linha09.createCell(2).setCellValue("Valor"); //Colunas da Linha 10 linha10.createCell(0).setCellValue("Saldo Salrio"); linha10.createCell(1).setCellValue(this.rst.getTotDiasTrabUltMes()); linha10.createCell(2).setCellValue(this.rst.getUltSalario()); //Colunas da Linha 11 linha11.createCell(0).setCellValue("13 Proporcional"); linha11.createCell(1).setCellValue(this.rst.getTotMesesTrabUltAno()); linha11.createCell(2).setCellValue(this.rst.getVlrDecimo()); //Colunas da Linha 12 linha12.createCell(0).setCellValue("Frias Proporcional"); linha12.createCell(1).setCellValue(this.rst.getTotMesesAqFerias()); linha12.createCell(2).setCellValue(this.rst.getVlrFerias()); //Colunas da Linha 13 linha13.createCell(0).setCellValue("1/3 Frias Proporcional"); linha13.createCell(1).setCellValue("-"); linha13.createCell(2).setCellValue(this.rst.getVlrTercoFerias()); //Colunas da Linha 14 linha14.createCell(0).setCellValue("Frias Vencidas"); linha14.createCell(1).setCellValue(this.rst.getTotFeriasVenc()); linha14.createCell(2).setCellValue(this.rst.getVlrFeriasVenc()); //Colunas da linha 15 linha15.createCell(0).setCellValue("Aviso Prvio"); linha15.createCell(1).setCellValue(this.rst.getTotDiasAviso()); linha15.createCell(2).setCellValue(this.rst.getVlrAvisoP()); //Colunas da linha 17 linha17.createCell(0).setCellValue("Valor Total"); linha17.createCell(1).setCellValue("-"); linha17.createCell(2).setCellValue(this.rst.getVlrTotVenc()); //Colunas da Linha 20 header3.createCell(0).setCellValue("FGTS"); //Colunas da Linha 21 linha21.createCell(0).setCellValue("Valores do FGTS estaro disponveis para saque?"); linha21.createCell(2).setCellValue(this.rst.getReceberFgts()); //Colunas da Linha 22 linha22.createCell(0).setCellValue("Saldo FGTS"); linha22.createCell(2).setCellValue(this.rst.getSaldoFgts()); //Colunas da Linha 23 linha23.createCell(0).setCellValue("Multa de 40%"); linha23.createCell(2).setCellValue(this.rst.getVlrMulta()); //Colunas da Linha 25 linha25.createCell(0).setCellValue("Valor total"); linha25.createCell(2).setCellValue(this.rst.getVlrTotFgts()); // ------------------------------------------------------------------------ // /* Aplicando os estilos nas clulas ------------------------------- */ cell = header1.getCell(0); cell.setCellStyle(headerStyle); header1.setRowStyle(headerStyle); header1.setHeightInPoints(30); cell = header2.getCell(0); cell.setCellStyle(headerStyle); header2.setRowStyle(headerStyle); header2.setHeightInPoints(30); cell = header3.getCell(0); cell.setCellStyle(headerStyle); header3.setRowStyle(headerStyle); header3.setHeightInPoints(30); linha02.getCell(2).setCellStyle(cellCentered); linha03.getCell(2).setCellStyle(cellCentered); linha04.getCell(2).setCellStyle(cellCentered); linha05.getCell(2).setCellStyle(cellCentered); linha09.getCell(1).setCellStyle(cellCentered); linha09.getCell(2).setCellStyle(cellCentered); linha10.getCell(1).setCellStyle(cellCentered); linha10.getCell(2).setCellStyle(cellCentered); linha11.getCell(1).setCellStyle(cellCentered); linha11.getCell(2).setCellStyle(cellCentered); linha12.getCell(1).setCellStyle(cellCentered); linha12.getCell(2).setCellStyle(cellCentered); linha13.getCell(1).setCellStyle(cellCentered); linha13.getCell(2).setCellStyle(cellCentered); linha14.getCell(1).setCellStyle(cellCentered); linha14.getCell(2).setCellStyle(cellCentered); linha15.getCell(1).setCellStyle(cellCentered); linha15.getCell(2).setCellStyle(cellCentered); linha17.getCell(0).setCellStyle(cellFontBold); linha17.getCell(1).setCellStyle(cellCentered); linha17.getCell(2).setCellStyle(cellResulted); linha21.getCell(2).setCellStyle(cellCentered); linha22.getCell(2).setCellStyle(cellCentered); linha23.getCell(2).setCellStyle(cellCentered); linha25.getCell(0).setCellStyle(cellFontBold); linha25.getCell(2).setCellStyle(cellResulted); // ------------------------------------------------------------------------ // /* Escrever, salvar e fechar o arquivo ---------------------------- */ workbook.write(arquivo); // Escrevendo no arquivo. workbook.close(); // Salvando e fechando o arquivo. return true; } catch (Exception e) { System.err.println("No foi possvel gerar seu arquivo!" + " \n" + e.getLocalizedMessage()); return false; } }
From source file:com.haulmont.cuba.gui.export.ExcelExporter.java
License:Apache License
public void exportTable(Table<Entity> table, List<Table.Column> columns, Boolean exportExpanded, ExportDisplay display, List<String> filterDescription, String fileName, ExportMode exportMode) { if (display == null) { throw new IllegalArgumentException("ExportDisplay is null"); }/*w w w .ja v a 2 s . c o m*/ createWorkbookWithSheet(); createFonts(); createFormats(); int r = 0; if (filterDescription != null) { for (r = 0; r < filterDescription.size(); r++) { String line = filterDescription.get(r); HSSFRow row = sheet.createRow(r); if (r == 0) { HSSFRichTextString richTextFilterName = new HSSFRichTextString(line); richTextFilterName.applyFont(boldFont); row.createCell(0).setCellValue(richTextFilterName); } else { row.createCell(0).setCellValue(line); } } r++; } HSSFRow row = sheet.createRow(r); createAutoColumnSizers(columns.size()); float maxHeight = sheet.getDefaultRowHeightInPoints(); CellStyle headerCellStyle = wb.createCellStyle(); headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); for (Table.Column column : columns) { String caption = column.getCaption(); int countOfReturnSymbols = StringUtils.countMatches(caption, "\n"); if (countOfReturnSymbols > 0) { maxHeight = Math.max(maxHeight, (countOfReturnSymbols + 1) * sheet.getDefaultRowHeightInPoints()); headerCellStyle.setWrapText(true); } } row.setHeightInPoints(maxHeight); for (int c = 0; c < columns.size(); c++) { Table.Column column = columns.get(c); String caption = column.getCaption(); HSSFCell cell = row.createCell(c); HSSFRichTextString richTextString = new HSSFRichTextString(caption); richTextString.applyFont(boldFont); cell.setCellValue(richTextString); ExcelAutoColumnSizer sizer = new ExcelAutoColumnSizer(); sizer.notifyCellValue(caption, boldFont); sizers[c] = sizer; cell.setCellStyle(headerCellStyle); } CollectionDatasource datasource = table.getDatasource(); if (exportMode == ExportMode.SELECTED_ROWS && table.getSelected().size() > 0) { Set<Entity> selected = table.getSelected(); List<Entity> ordered = ((Collection<Entity>) datasource.getItems()).stream().filter(selected::contains) .collect(Collectors.toList()); for (Entity item : ordered) { createRow(table, columns, 0, ++r, item.getId()); } } else { if (table instanceof TreeTable) { TreeTable treeTable = (TreeTable) table; HierarchicalDatasource ds = treeTable.getDatasource(); if (table.isAggregatable()) { r = createAggregatableRow(table, columns, ++r, 1, datasource); } for (Object itemId : ds.getRootItemIds()) { r = createHierarhicalRow(treeTable, columns, exportExpanded, r, itemId); } } else if (table instanceof GroupTable && datasource instanceof GroupDatasource && ((GroupDatasource) datasource).hasGroups()) { GroupDatasource ds = (GroupDatasource) datasource; if (table.isAggregatable()) { r = createAggregatableRow(table, columns, ++r, 1, datasource); } for (Object item : ds.rootGroups()) { r = createGroupRow((GroupTable) table, columns, ++r, (GroupInfo) item, 0); } } else { if (table.isAggregatable()) { r = createAggregatableRow(table, columns, ++r, 1, datasource); } for (Object itemId : datasource.getItemIds()) { createRow(table, columns, 0, ++r, itemId); } } } for (int c = 0; c < columns.size(); c++) { sheet.setColumnWidth(c, sizers[c].getWidth() * COL_WIDTH_MAGIC); } ByteArrayOutputStream out = new ByteArrayOutputStream(); try { wb.write(out); } catch (IOException e) { throw new RuntimeException("Unable to write document", e); } if (fileName == null) { fileName = messages.getTools().getEntityCaption(datasource.getMetaClass()); } display.show(new ByteArrayDataProvider(out.toByteArray()), fileName + ".xls", ExportFormat.XLS); }
From source file:com.haulmont.cuba.gui.export.ExcelExporter.java
License:Apache License
public void exportDataGrid(DataGrid<Entity> dataGrid, List<DataGrid.Column> columns, ExportDisplay display, List<String> filterDescription, String fileName, ExportMode exportMode) { if (display == null) { throw new IllegalArgumentException("ExportDisplay is null"); }/* www . j av a2s .com*/ createWorkbookWithSheet(); createFonts(); createFormats(); int r = 0; if (filterDescription != null) { for (r = 0; r < filterDescription.size(); r++) { String line = filterDescription.get(r); HSSFRow row = sheet.createRow(r); if (r == 0) { HSSFRichTextString richTextFilterName = new HSSFRichTextString(line); richTextFilterName.applyFont(boldFont); row.createCell(0).setCellValue(richTextFilterName); } else { row.createCell(0).setCellValue(line); } } r++; } HSSFRow row = sheet.createRow(r); createAutoColumnSizers(columns.size()); float maxHeight = sheet.getDefaultRowHeightInPoints(); CellStyle headerCellStyle = wb.createCellStyle(); headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); for (DataGrid.Column column : columns) { String caption = column.getCaption(); int countOfReturnSymbols = StringUtils.countMatches(caption, "\n"); if (countOfReturnSymbols > 0) { maxHeight = Math.max(maxHeight, (countOfReturnSymbols + 1) * sheet.getDefaultRowHeightInPoints()); headerCellStyle.setWrapText(true); } } row.setHeightInPoints(maxHeight); for (int c = 0; c < columns.size(); c++) { DataGrid.Column column = columns.get(c); String caption = column.getCaption(); HSSFCell cell = row.createCell(c); HSSFRichTextString richTextString = new HSSFRichTextString(caption); richTextString.applyFont(boldFont); cell.setCellValue(richTextString); ExcelAutoColumnSizer sizer = new ExcelAutoColumnSizer(); sizer.notifyCellValue(caption, boldFont); sizers[c] = sizer; cell.setCellStyle(headerCellStyle); } CollectionDatasource datasource = dataGrid.getDatasource(); if (exportMode == ExportMode.SELECTED_ROWS && dataGrid.getSelected().size() > 0) { Set<Entity> selected = dataGrid.getSelected(); List<Entity> ordered = ((Collection<Entity>) datasource.getItems()).stream().filter(selected::contains) .collect(Collectors.toList()); for (Entity item : ordered) { createDataGridRow(dataGrid, columns, 0, ++r, item.getId()); } } else { for (Object itemId : datasource.getItemIds()) { createDataGridRow(dataGrid, columns, 0, ++r, itemId); } } for (int c = 0; c < columns.size(); c++) { sheet.setColumnWidth(c, sizers[c].getWidth() * COL_WIDTH_MAGIC); } ByteArrayOutputStream out = new ByteArrayOutputStream(); try { wb.write(out); } catch (IOException e) { throw new RuntimeException("Unable to write document", e); } if (fileName == null) { fileName = messages.getTools().getEntityCaption(datasource.getMetaClass()); } display.show(new ByteArrayDataProvider(out.toByteArray()), fileName + ".xls", ExportFormat.XLS); }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void creaCabecera(HSSFWorkbook workbook, HSSFSheet sheet) { Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontSubTituloAzul = workbook.createFont(); fontSubTituloAzul.setFontHeightInPoints((short) 8); fontSubTituloAzul.setColor(HSSFColor.DARK_BLUE.index); fontSubTituloAzul.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontSubTituloCaje = workbook.createFont(); fontSubTituloCaje.setFontHeightInPoints((short) 7); fontSubTituloCaje.setColor(HSSFColor.DARK_BLUE.index); fontSubTituloCaje.setBoldweight(Font.BOLDWEIGHT_BOLD); Font font7 = workbook.createFont(); font7.setFontHeightInPoints((short) 6); Font fontTerritorio = workbook.createFont(); fontTerritorio.setItalic(true);//from w ww.j a v a 2s . c o m fontTerritorio.setFontHeightInPoints((short) 12); fontTerritorio.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontTerritorioTotal1 = workbook.createFont(); fontTerritorioTotal1.setItalic(true); fontTerritorioTotal1.setFontHeightInPoints((short) 8); fontTerritorioTotal1.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontTitulo9 = workbook.createFont(); fontTitulo9.setFontHeightInPoints((short) 8); Font fontTitulo = workbook.createFont(); fontTitulo.setFontHeightInPoints((short) 12); fontTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); styleTitulo = workbook.createCellStyle(); styleTitulo.setFont(fontTitulo); styleTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleTerritorio = workbook.createCellStyle(); styleTerritorio.setFont(fontTerritorio); styleTerritorioTotal1 = workbook.createCellStyle(); styleTerritorioTotal1.setFont(fontTerritorioTotal1); styleTerritorioTotal1.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTerritorioTotal1.setBorderTop(HSSFCellStyle.BORDER_THIN); //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTerritorioTotal1.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTitulo9 = workbook.createCellStyle(); styleTitulo9.setFont(fontTitulo9); styleTitulo9.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTitulo9.setBorderTop(HSSFCellStyle.BORDER_THIN); //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTitulo9.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSubTitulo = workbook.createCellStyle(); styleSubTitulo.setFont(fontSubTitulo); styleSubTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleSubTitulo.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje = workbook.createCellStyle(); styleSubTituloCaje.setFont(fontSubTituloCaje); styleSubTituloCaje.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleSubTituloCaje.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado = workbook.createCellStyle(); styleMergeCentrado.setFont(fontSubTituloAzul); styleMergeCentrado.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleMergeCentrado.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleMergeCentrado.setWrapText(true); styleMergeTotal = workbook.createCellStyle(); styleMergeTotal.setFont(fontSubTitulo); styleMergeTotal.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleMergeTotal.setFillPattern(CellStyle.SOLID_FOREGROUND); styleMergeTotal.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleMergeTotal.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataFormat format = workbook.createDataFormat(); styleCantidadAzul = workbook.createCellStyle(); styleCantidadAzul.setFont(fontSubTituloAzul); styleCantidadAzul.setDataFormat(format.getFormat("#,##0")); styleCantidadAzul.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCantidadNegrita = workbook.createCellStyle(); styleCantidadNegrita.setFont(fontSubTitulo); styleCantidadNegrita.setDataFormat(format.getFormat("#,##0")); styleCantidadNegrita.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris = workbook.createCellStyle(); styleCantidadGris.setFont(fontSubTitulo); styleCantidadGris.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleCantidadGris.setFillPattern(CellStyle.SOLID_FOREGROUND); styleCantidadGris.setDataFormat(format.getFormat("#,##0")); styleCantidadGris.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidad = workbook.createCellStyle(); styleCantidad.setFont(fontTitulo9); styleCantidad.setDataFormat(format.getFormat("#,##0")); styleCantidad.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCantidadGris1 = workbook.createCellStyle(); styleCantidadGris1.setFont(fontTitulo9); styleCantidadGris1.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleCantidadGris1.setFillPattern(CellStyle.SOLID_FOREGROUND); styleCantidadGris1.setDataFormat(format.getFormat("#,##0")); styleCantidadGris1.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCantidadCaje = workbook.createCellStyle(); styleCantidadCaje.setFont(font7); styleCantidadCaje.setDataFormat(format.getFormat("#,##0")); styleCantidadCaje.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFRow rowTitulo = sheet.createRow(0); HSSFCell cellTitulo = rowTitulo.createCell(18); cellTitulo.setCellValue("CONTROL DIARIO DE VENTAS"); cellTitulo.setCellStyle(styleTitulo); HSSFRow rowFecha = sheet.createRow(++indexRow); HSSFCell cellFecha = rowFecha.createCell(18); // SimpleDateFormat sdf = new SimpleDateFormat("EEEE, dd 'de' MMMM 'de' yyyy", new Locale("es", "py")); SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy", new Locale("es", "py")); cellFecha.setCellValue("De " + sdf.format(inicio) + " al " + sdf.format(fin)); //Calendar calender = Calendar.getInstance(); //cellFecha.setCellValue(calender.get(getInicio())); cellFecha.setCellStyle(styleTitulo); ++indexRow; HSSFRow rowCabeceraMarca = sheet.createRow((++indexRow)); HSSFRow rowCabeceraProducto = sheet.createRow((++indexRow)); HSSFRow rowCabecerauM = sheet.createRow((++indexRow)); rowCabeceraProducto.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints())); HSSFCell cell0 = rowCabecerauM.createCell(indexCol); cell0.setCellValue("Zona"); cell0.setCellStyle(styleSubTitulo); HSSFCell cell1 = rowCabecerauM.createCell(indexCol + 1); cell1.setCellValue("Vendedor"); cell1.setCellStyle(styleSubTitulo); HSSFCell cell2 = rowCabecerauM.createCell(indexCol + 2); cell2.setCellValue("Boletas"); cell2.setCellStyle(styleSubTitulo); HSSFCell cell3 = rowCabeceraProducto.createCell(indexCol + 3); HSSFCell cell4 = rowCabeceraProducto.createCell(indexCol + 4); cell3.setCellValue("Palermo Red Box 20"); cell3.setCellStyle(styleMergeCentrado); cell4.setCellValue(""); cell4.setCellStyle(styleMergeCentrado); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 3) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 4) + "$" + indexRow)); HSSFCell cell3g = rowCabecerauM.createCell(indexCol + 3); cell3g.setCellValue("Grue"); cell3g.setCellStyle(styleMergeCentrado); HSSFCell cell3c = rowCabecerauM.createCell(indexCol + 4); cell3c.setCellValue("Caj"); cell3c.setCellStyle(styleSubTituloCaje); HSSFCell cell5 = (rowCabeceraProducto).createCell(indexCol + 5); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 5) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 6) + "$" + indexRow)); cell5.setCellValue("Palermo Blue Box 20"); cell5.setCellStyle(styleMergeCentrado); HSSFCell cell5g = rowCabecerauM.createCell(indexCol + 5); cell5g.setCellValue("Grue"); cell5g.setCellStyle(styleMergeCentrado); HSSFCell cell5c = rowCabecerauM.createCell(indexCol + 6); cell5c.setCellValue("Caj"); cell5c.setCellStyle(styleSubTituloCaje); HSSFCell cell7 = (rowCabeceraProducto).createCell(indexCol + 7); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 7) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 8) + "$" + indexRow)); cell7.setCellValue("Palermo Green Box 20"); cell7.setCellStyle(styleMergeCentrado); HSSFCell cell7g = rowCabecerauM.createCell(indexCol + 7); cell7g.setCellValue("Grue"); cell7g.setCellStyle(styleMergeCentrado); HSSFCell cell7c = rowCabecerauM.createCell(indexCol + 8); cell7c.setCellValue("Caj"); cell7c.setCellStyle(styleSubTituloCaje); HSSFCell cell9 = (rowCabeceraProducto).createCell(indexCol + 9); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 9) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 10) + "$" + indexRow)); cell9.setCellValue("Palermo Tres Box 20"); cell9.setCellStyle(styleMergeCentrado); HSSFCell cell9g = rowCabecerauM.createCell(indexCol + 9); cell9g.setCellValue("Grue"); cell9g.setCellStyle(styleMergeCentrado); HSSFCell cell9c = rowCabecerauM.createCell(indexCol + 10); cell9c.setCellValue("Caj"); cell9c.setCellStyle(styleSubTituloCaje); HSSFCell cell11 = (rowCabeceraProducto).createCell(indexCol + 11); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 11) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 12) + "$" + indexRow)); cell11.setCellValue("Palermo Duo Box 20"); cell11.setCellStyle(styleMergeCentrado); HSSFCell cell11g = rowCabecerauM.createCell(indexCol + 11); cell11g.setCellValue("Grue"); cell11g.setCellStyle(styleMergeCentrado); HSSFCell cell11c = rowCabecerauM.createCell(indexCol + 12); cell11c.setCellValue("Caj"); cell11c.setCellStyle(styleSubTituloCaje); HSSFCell cell13 = rowCabeceraProducto.createCell(indexCol + 13); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 13) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 14) + "$" + indexRow)); cell13.setCellValue("Palermo Red Box 10"); cell13.setCellStyle(styleMergeCentrado); HSSFCell cell13g = rowCabecerauM.createCell(indexCol + 13); cell13g.setCellValue("Grue"); cell13g.setCellStyle(styleMergeCentrado); HSSFCell cell13c = rowCabecerauM.createCell(indexCol + 14); cell13c.setCellValue("Caj"); cell13c.setCellStyle(styleSubTituloCaje); HSSFCell cell15 = (rowCabeceraProducto).createCell(indexCol + 15); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 15) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 16) + "$" + indexRow)); cell15.setCellValue("Palermo Blue Box 10"); cell15.setCellStyle(styleMergeCentrado); HSSFCell cell15g = rowCabecerauM.createCell(indexCol + 15); cell15g.setCellValue("Grue"); cell15g.setCellStyle(styleMergeCentrado); HSSFCell cell15c = rowCabecerauM.createCell(indexCol + 16); cell15c.setCellValue("Caj"); cell15c.setCellStyle(styleSubTituloCaje); HSSFCell cell17 = (rowCabeceraProducto).createCell(indexCol + 17); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 17) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 18) + "$" + indexRow)); cell17.setCellValue("Palermo Green Box 10"); cell17.setCellStyle(styleMergeCentrado); HSSFCell cell17g = rowCabecerauM.createCell(indexCol + 17); cell17g.setCellValue("Grue"); cell17g.setCellStyle(styleMergeCentrado); HSSFCell cell17c = rowCabecerauM.createCell(indexCol + 18); cell17c.setCellValue("Caj"); cell17c.setCellStyle(styleSubTituloCaje); HSSFCell cell19 = (rowCabeceraProducto).createCell(indexCol + 19); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 19) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 20) + "$" + indexRow)); cell19.setCellValue("Palermo Tres Box 10"); cell19.setCellStyle(styleMergeCentrado); HSSFCell cell19g = rowCabecerauM.createCell(indexCol + 19); cell19g.setCellValue("Grue"); cell19g.setCellStyle(styleMergeCentrado); HSSFCell cell19c = rowCabecerauM.createCell(indexCol + 20); cell19c.setCellValue("Caj"); cell19c.setCellStyle(styleSubTituloCaje); HSSFCell cell21 = (rowCabeceraProducto).createCell(indexCol + 21); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 21) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 22) + "$" + indexRow)); cell21.setCellValue("Palermo Duo Box 10"); cell21.setCellStyle(styleMergeCentrado); HSSFCell cell21g = rowCabecerauM.createCell(indexCol + 21); cell21g.setCellValue("Grue"); cell21g.setCellStyle(styleMergeCentrado); HSSFCell cell21c = rowCabecerauM.createCell(indexCol + 22); cell21c.setCellValue("Caj"); cell21c.setCellStyle(styleSubTituloCaje); HSSFCell cell23 = (rowCabeceraProducto).createCell(indexCol + 23); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 23) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 24) + "$" + indexRow)); cell23.setCellValue("San Marino TYPE Box 20"); cell23.setCellStyle(styleMergeCentrado); HSSFCell cell23g = rowCabecerauM.createCell(indexCol + 23); cell23g.setCellValue("Grue"); cell23g.setCellStyle(styleMergeCentrado); HSSFCell cell23c = rowCabecerauM.createCell(indexCol + 24); cell23c.setCellValue("Caj"); cell23c.setCellStyle(styleSubTituloCaje); HSSFCell cell25 = (rowCabeceraProducto).createCell(indexCol + 25); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 25) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 26) + "$" + indexRow)); cell25.setCellValue("San Marino TYPE Box 10"); cell25.setCellStyle(styleMergeCentrado); HSSFCell cell25g = rowCabecerauM.createCell(indexCol + 25); cell25g.setCellValue("Grue"); cell25g.setCellStyle(styleMergeCentrado); HSSFCell cell25c = rowCabecerauM.createCell(indexCol + 26); cell25c.setCellValue("Caj"); cell25c.setCellStyle(styleSubTituloCaje); HSSFCell cell27 = (rowCabeceraProducto).createCell(indexCol + 27); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 27) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 28) + "$" + indexRow)); cell27.setCellValue("San Marino TYPE Soft"); cell27.setCellStyle(styleMergeCentrado); HSSFCell cell27g = rowCabecerauM.createCell(indexCol + 27); cell27g.setCellValue("Grue"); cell27g.setCellStyle(styleMergeCentrado); HSSFCell cell27c = rowCabecerauM.createCell(indexCol + 28); cell27c.setCellValue("Caj"); cell27c.setCellStyle(styleSubTituloCaje); HSSFCell cell29 = (rowCabeceraProducto).createCell(indexCol + 29); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 29) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 30) + "$" + indexRow)); cell29.setCellValue("Kentucky Box 20"); cell29.setCellStyle(styleMergeCentrado); HSSFCell cell29g = rowCabecerauM.createCell(indexCol + 29); cell29g.setCellValue("Grue"); cell29g.setCellStyle(styleMergeCentrado); HSSFCell cell29c = rowCabecerauM.createCell(indexCol + 30); cell29c.setCellValue("Caj"); cell29c.setCellStyle(styleSubTituloCaje); HSSFCell cell31 = (rowCabeceraProducto).createCell(indexCol + 31); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 31) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 32) + "$" + indexRow)); cell31.setCellValue("Kentucky Box 10"); cell31.setCellStyle(styleMergeCentrado); HSSFCell cell31g = rowCabecerauM.createCell(indexCol + 31); cell31g.setCellValue("Grue"); cell31g.setCellStyle(styleMergeCentrado); HSSFCell cell31c = rowCabecerauM.createCell(indexCol + 32); cell31c.setCellValue("Caj"); cell31c.setCellStyle(styleSubTituloCaje); HSSFCell cell33 = (rowCabeceraProducto).createCell(indexCol + 33); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 33) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 34) + "$" + indexRow)); cell33.setCellValue("Kentucky Soft"); cell33.setCellStyle(styleMergeCentrado); HSSFCell cell33g = rowCabecerauM.createCell(indexCol + 33); cell33g.setCellValue("Grue"); cell33g.setCellStyle(styleMergeCentrado); HSSFCell cell33c = rowCabecerauM.createCell(indexCol + 34); cell33c.setCellValue("Caj"); cell33c.setCellStyle(styleSubTituloCaje); HSSFCell cell35 = rowCabeceraProducto.createCell(indexCol + 35); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 35) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 36) + "$" + indexRow)); cell35.setCellValue("Total"); cell35.setCellStyle(styleMergeTotal); HSSFCell cell38 = rowCabeceraProducto.createCell(indexCol + 36); cell38.setCellValue(""); cell38.setCellStyle(styleMergeTotal); HSSFCell cell35g = rowCabecerauM.createCell(indexCol + 35); cell35g.setCellValue("Gruesas"); cell35g.setCellStyle(styleSubTitulo); HSSFCell cell35c = rowCabecerauM.createCell(indexCol + 36); cell35c.setCellValue("Caje"); cell35c.setCellStyle(styleSubTitulo); HSSFCell cell37c = rowCabecerauM.createCell(indexCol + 37); cell37c.setCellValue("Cajas"); cell37c.setCellStyle(styleSubTitulo); HSSFCell cell38g = rowCabecerauM.createCell(indexCol + 38); cell38g.setCellValue("+gr"); cell38g.setCellStyle(styleSubTitulo); sheet.createFreezePane(2, 7); }
From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsService.java
License:Open Source License
private void createHeaderForAuthor(final HSSFSheet sheet, final Locale locale, final Entity entity) { HSSFRow headerAuthorLine = sheet.createRow(1); String shift = translationService.translate(COLUMN_HEADER_SHIFT, locale) + " " + entity.getBelongsToField(SHIFT).getStringField(NAME); String user = translationService.translate(COLUMN_HEADER_AUTHOR, locale) + " " + entity.getField(CREATE_USER).toString(); String date = translationService.translate(COLUMN_HEADER_UPDATE_DATE, locale) + " " + DateFormat.getDateInstance().format(entity.getField(UPDATE_DATE)); HSSFCell cellAuthorLine0 = headerAuthorLine.createCell(0); cellAuthorLine0.setCellValue(shift); HSSFCell cellAuthorLine3 = headerAuthorLine.createCell(3); cellAuthorLine3.setCellValue(date);/*from w w w . j a v a 2 s .com*/ HSSFCell cellAuthorLine6 = headerAuthorLine.createCell(6); cellAuthorLine6.setCellValue(user); headerAuthorLine.setHeightInPoints(30); assignmentToShiftXlsStyleHelper.addMarginsAndStylesForAuthor(sheet, 1, assignmentToShiftXlsHelper.getNumberOfDaysBetweenGivenDates(entity)); }
From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsService.java
License:Open Source License
private void createHeaderForAssignmentToShift(final HSSFSheet sheet, final Locale locale, final Entity entity) { List<DateTime> days = assignmentToShiftXlsHelper.getDaysBetweenGivenDates(entity); if (days != null) { HSSFRow headerAssignmentToShift = sheet.createRow(3); String occupationType = translationService.translate(COLUMN_HEADER_OCCUPATIONTYPE, locale); HSSFCell cell0 = headerAssignmentToShift.createCell(0); cell0.setCellValue(occupationType); int columnNumber = 1; for (DateTime day : days) { HSSFCell cellDay = headerAssignmentToShift.createCell(columnNumber); cellDay.setCellValue(translationService.translate(COLUMN_HEADER_DAY, locale, DateFormat.getDateInstance().format(new Date(day.getMillis())))); columnNumber += 3;/*from ww w . jav a 2 s .c o m*/ } headerAssignmentToShift.setHeightInPoints(14); assignmentToShiftXlsStyleHelper.addMarginsAndStylesForAssignmentToShift(sheet, 3, assignmentToShiftXlsHelper.getNumberOfDaysBetweenGivenDates(entity)); } }
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();/*from w w w .j a v a 2 s.com*/ 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.checking.airticket.CheckingAirOthersummary.java
private void genTicketProfitLossReport(HSSFWorkbook wb, List ticketProfitLoss) { String sheetName = "Sheet1";// name of sheet HSSFSheet sheet = wb.createSheet(sheetName); TicketProfitLoss dataheader = new TicketProfitLoss(); UtilityExcelFunction excelFunction = new UtilityExcelFunction(); 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); System.out.println(" ticketProfitLoss.size() " + ticketProfitLoss.size()); if (ticketProfitLoss != null && ticketProfitLoss.size() != 0) { dataheader = (TicketProfitLoss) ticketProfitLoss.get(0); // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0); HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Ticket Profit Loss"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Row 2/*from ww w . j a v a 2 s. com*/ HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Invoice Date From : " + dataheader.getInvoicedatefrom() + " To : " + dataheader.getInvoicedateto()); cell21.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("A2:F2")); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Print by : " + dataheader.getPrintby()); cell31.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("A3:F3")); // HSSFCell cell32 = row3.createCell(4); // cell32.setCellValue("Print on : "+dataheader.getPrinton()); // cell32.setCellStyle(styleC22); // sheet.addMergedRegion(CellRangeAddress.valueOf("C3:D3")); // HSSFCell cell33 = row3.createCell(4); // cell33.setCellValue("Print on : "); // cell33.setCellStyle(styleC21); // HSSFCell cell34 = row3.createCell(5); // cell34.setCellValue(dataheader.getPrinton()); // cell34.setCellStyle(styleC22); // sheet.addMergedRegion(CellRangeAddress.valueOf("E3:F3")); //Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Print on : " + dataheader.getPrinton()); cell41.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("A4:F4")); // 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(5); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue(""); cell61.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue("Inv No."); cell62.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell63 = row6.createCell(2); cell63.setCellValue("Date"); cell63.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell64 = row6.createCell(3); cell64.setCellValue("Dep"); cell64.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell651 = row6.createCell(4); cell651.setCellValue("Agent Name"); cell651.setCellStyle(styleC3Center); sheet.addMergedRegion(CellRangeAddress.valueOf("E6:H6")); HSSFCell cell652 = row6.createCell(5); cell652.setCellStyle(styleC3Center); HSSFCell cell653 = row6.createCell(6); cell653.setCellStyle(styleC3Center); HSSFCell cell654 = row6.createCell(7); cell654.setCellStyle(styleC3Center); HSSFCell cell66 = row6.createCell(8); cell66.setCellValue("Type"); cell66.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell67 = row6.createCell(9); cell67.setCellValue("Rou"); cell67.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell68 = row6.createCell(10); cell68.setCellValue("Pax"); cell68.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell69 = row6.createCell(11); cell69.setCellValue("Air"); cell69.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); HSSFCell cell610 = row6.createCell(12); cell610.setCellValue("Doc No."); cell610.setCellStyle(styleC3Center); sheet.autoSizeColumn(12); HSSFCell cell611 = row6.createCell(13); cell611.setCellValue("Issue Date"); cell611.setCellStyle(styleC3Center); sheet.autoSizeColumn(13); HSSFCell cell612 = row6.createCell(14); cell612.setCellValue("Comm"); cell612.setCellStyle(styleC3Center); sheet.autoSizeColumn(14); HSSFCell cell613 = row6.createCell(15); cell613.setCellValue("Vat"); cell613.setCellStyle(styleC3Center); sheet.autoSizeColumn(15); HSSFCell cell614 = row6.createCell(16); cell614.setCellValue("Total"); cell614.setCellStyle(styleC3Center); sheet.autoSizeColumn(16); } //Detail of Table int count = 6; HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC22.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC23.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC23.setWrapText(true); styleC23.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC24.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC24.setWrapText(true); styleC24.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); styleC25.setWrapText(true); styleC25.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); for (int i = 0; i < ticketProfitLoss.size(); i++) { TicketProfitLoss data = (TicketProfitLoss) ticketProfitLoss.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getNo()); cell0.setCellStyle(styleC23); sheet.autoSizeColumn(0); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getInvno()); cell1.setCellStyle(styleC23); sheet.autoSizeColumn(1); HSSFCell cell2 = row.createCell(2); cell2.setCellValue(data.getDate()); cell2.setCellStyle(styleC23); sheet.autoSizeColumn(2); HSSFCell cell3 = row.createCell(3); cell3.setCellValue(data.getDepartment()); cell3.setCellStyle(styleC23); sheet.autoSizeColumn(3); // String air = data.getDocno().substring(0, 4); // String doc = data.getDocno().substring(4); // System.out.println("Air : " + air + " Doc : " + doc); HSSFCell cell4 = row.createCell(4); cell4.setCellValue(data.getAgentname()); cell4.setCellStyle(styleC24); sheet.addMergedRegion(CellRangeAddress.valueOf("E" + (count + i + 1) + ":H" + (count + i + 1))); HSSFCell cell5 = row.createCell(5); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(6); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(7); cell7.setCellStyle(styleC24); HSSFCell cell18 = row.createCell(8); cell18.setCellValue(data.getType()); cell18.setCellStyle(styleC23); sheet.autoSizeColumn(8); HSSFCell cell9 = row.createCell(9); cell9.setCellValue(data.getRount()); cell9.setCellStyle(styleC23); sheet.autoSizeColumn(9); HSSFCell cell10 = row.createCell(10); cell10.setCellValue(data.getPax()); cell10.setCellStyle(styleC25); sheet.autoSizeColumn(10); HSSFCell cell11 = row.createCell(11); cell11.setCellValue(data.getAir()); cell11.setCellStyle(styleC25); sheet.autoSizeColumn(11); HSSFCell cell12 = row.createCell(12); cell12.setCellValue((data.getDocno()).replaceAll(",", "\n")); cell12.setCellStyle(styleC23); sheet.autoSizeColumn(12); row.setHeightInPoints( (((((data.getDocno()).split(",")).length) + 1) * sheet.getDefaultRowHeightInPoints())); HSSFCell cell13 = row.createCell(13); cell13.setCellValue(data.getIssuedate()); cell13.setCellStyle(styleC23); sheet.autoSizeColumn(13); HSSFCell cell14 = row.createCell(14); cell14.setCellValue( !"".equalsIgnoreCase(data.getLittlecomm()) ? new BigDecimal(data.getLittlecomm()).doubleValue() : 0); cell14.setCellStyle(styleC25); sheet.autoSizeColumn(14); HSSFCell cell15 = row.createCell(15); cell15.setCellValue( !"".equalsIgnoreCase(data.getVat()) ? new BigDecimal(data.getVat()).doubleValue() : 0); cell15.setCellStyle(styleC25); sheet.autoSizeColumn(15); HSSFCell cell16 = row.createCell(16); cell16.setCellValue( !"".equalsIgnoreCase(data.getTotal()) ? new BigDecimal(data.getTotal()).doubleValue() : 0); cell16.setCellStyle(styleC25); sheet.autoSizeColumn(16); if (i == (ticketProfitLoss.size() - 1)) { row = sheet.createRow(count + i + 1); int count2 = count + i + 1; HSSFRow rowtotal = sheet.createRow(count2); String sumComm = "SUM(O" + 7 + ":O" + (count2) + ")"; String sumVat = "SUM(P" + 7 + ":P" + (count2) + ")"; String sumTotal = "SUM(Q" + 7 + ":Q" + (count2) + ")"; sheet.addMergedRegion(CellRangeAddress.valueOf("A" + (count2 + 1) + ":N" + (count2 + 1))); HSSFCell cellTotal0 = rowtotal.createCell(0); cellTotal0.setCellValue("Total"); cellTotal0.setCellStyle(styleC25); HSSFCell cellTotal = rowtotal.createCell(14); cellTotal.setCellFormula(sumComm); cellTotal.setCellStyle(styleC25); HSSFCell cellTotal2 = rowtotal.createCell(15); cellTotal2.setCellFormula(sumVat); cellTotal2.setCellStyle(styleC25); HSSFCell cellTotal3 = rowtotal.createCell(16); cellTotal3.setCellFormula(sumTotal); cellTotal3.setCellStyle(styleC25); for (int k = 1; k < 14; k++) { HSSFCell cellTotal1 = rowtotal.createCell(k); cellTotal1.setCellStyle(styleC25); } // for(int k=0;k<16;k++){ // HSSFCellStyle styleSum = wb.createCellStyle(); // styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); // HSSFCell cellSum = row.createCell(k); // cellSum.setCellStyle(styleSum); // } } // for(int j =0;j<15;j++){ // if(j==4){ // sheet.addMergedRegion(CellRangeAddress.valueOf("E"+(count + i)+":G"+(count + i))); // }else{ // sheet.autoSizeColumn(j); // } // // } } }
From source file:com.zdtx.ifms.specific.service.monitor.IpCamManager.java
@SuppressWarnings("unchecked") public InputStream getExcel(String title) { List<Camera> data = new ArrayList<Camera>(); Page<Camera> page_export = (Page<Camera>) Utils.getSession().getAttribute("page_export"); DetachedCriteria criteria_export = (DetachedCriteria) Utils.getSession().getAttribute("criteria_export"); List<Order> orderList_export = (List<Order>) Utils.getSession().getAttribute("orderList_export"); page_export.setPageSize(page_export.getTotalCount()); Page<Camera> pageResult = dao.getBatch(page_export, criteria_export.getExecutableCriteria(dao.getSession()), orderList_export);/*from w w w . ja va 2s. c o m*/ if (null != pageResult) { if (0 != pageResult.getResult().size()) { data = pageResult.getResult(); } } ExportExcel ee = new ExportExcel() { @Override protected HSSFWorkbook disposeData(HSSFWorkbook wb, Object[] total, List<?> data) throws IOException { HSSFSheet sheet = wb.getSheetAt(0); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); HSSFRow rowss = sheet.createRow(0); rowss.setHeightInPoints(20); HSSFCell hssfCell = rowss.createCell(0); hssfCell = this.createCell(wb, hssfCell, total[0].toString()); HSSFCellStyle style = this.createStyle(wb); HSSFRow row2 = sheet.createRow(1); HSSFCell cellrow01 = row2.createCell(0); cellrow01.setCellStyle(style); HSSFCell cellrow02 = row2.createCell(1); cellrow02.setCellStyle(style); HSSFCell cellrow03 = row2.createCell(2); cellrow03.setCellStyle(style); HSSFCell cellrow04 = row2.createCell(3); cellrow04.setCellStyle(style); HSSFCell cellrow05 = row2.createCell(4); cellrow05.setCellStyle(style); HSSFCell cellrow06 = row2.createCell(5); cellrow06.setCellStyle(style); HSSFCell cellrow07 = row2.createCell(6); cellrow07.setCellStyle(style); cellrow01.setCellValue("No."); cellrow02.setCellValue("Camera Name"); cellrow03.setCellValue("Department"); cellrow04.setCellValue("Camera Model"); cellrow05.setCellValue("IP"); cellrow06.setCellValue("Login Username"); cellrow07.setCellValue("Login Password"); if (null != data && 0 != data.size()) { for (int i = 0; i < data.size(); i++) { Object[] o = (Object[]) data.get(i); CamModel cm = (CamModel) o[0]; Camera a = (Camera) o[1]; HSSFRow row = sheet.createRow(i + 2); HSSFCell cell001 = row.createCell(0); cell001.setCellStyle(style); cell001.setCellValue(i + 1); HSSFCell cell002 = row.createCell(1); cell002.setCellStyle(style); cell002.setCellValue(a.getCameraName()); HSSFCell cell003 = row.createCell(2); cell003.setCellStyle(style); cell003.setCellValue(a.getDeptname()); HSSFCell cell004 = row.createCell(3); cell004.setCellStyle(style); cell004.setCellValue(cm.getModelName()); HSSFCell cell005 = row.createCell(4); cell005.setCellStyle(style); cell005.setCellValue(a.getIpAddress()); HSSFCell cell006 = row.createCell(5); cell006.setCellStyle(style); cell006.setCellValue(a.getAdminName()); HSSFCell cell007 = row.createCell(6); cell007.setCellStyle(style); cell007.setCellValue(a.getAdminPass()); } } return wb; } }; Object[] total = new Object[1]; total[0] = title; String str = title; return ee.export(total, data, str); }
From source file:com.zdtx.ifms.specific.service.task.FuelMileageManager.java
@SuppressWarnings("unchecked") public InputStream getData(Page<Mileageoil> page, List<Mileageoil> list, FuelMileageVo fmvo) { //Page<Mileageoil> pageResult = this.getBetch(page, fmvo); Page<Mileageoil> page_export = (Page<Mileageoil>) Struts2Util.getSession().getAttribute("page_export"); DetachedCriteria criteria_export = (DetachedCriteria) Struts2Util.getSession() .getAttribute("criteria_export"); List<Order> orderList_export = (List<Order>) Struts2Util.getSession().getAttribute("orderList_export"); page_export.setPageSize(page_export.getTotalCount()); Page<Mileageoil> pageResult = baseDao.getBatch(page_export, criteria_export.getExecutableCriteria(baseDao.getSession()), orderList_export); List<Mileageoil> data = new ArrayList<Mileageoil>(); if (null != pageResult) { if (0 != pageResult.getResult().size()) { data = pageResult.getResult(); }/*w ww .jav a2 s .co m*/ } ExportExcel ee = new ExportExcel() { @Override protected HSSFWorkbook disposeData(HSSFWorkbook wb, Object[] total, List<?> data) throws IOException { HSSFSheet sheet = wb.getSheetAt(0); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); HSSFRow rowss = sheet.createRow(0); rowss.setHeightInPoints(20); HSSFCell hssfCell = rowss.createCell(0); hssfCell = this.createCell(wb, hssfCell, "Fuel Mileage"); HSSFCellStyle style = this.createStyle(wb); HSSFRow row2 = sheet.createRow(1); HSSFCell cellrow01 = row2.createCell(0); cellrow01.setCellStyle(style); HSSFCell cellrow02 = row2.createCell(1); cellrow02.setCellStyle(style); HSSFCell cellrow03 = row2.createCell(2); cellrow03.setCellStyle(style); HSSFCell cellrow04 = row2.createCell(3); cellrow04.setCellStyle(style); HSSFCell cellrow05 = row2.createCell(4); cellrow05.setCellStyle(style); HSSFCell cellrow06 = row2.createCell(5); cellrow06.setCellStyle(style); cellrow01.setCellValue("No."); cellrow02.setCellValue("Plate Number"); cellrow03.setCellValue("Vehicle Type"); cellrow04.setCellValue("Date"); cellrow05.setCellValue("Mileage"); cellrow06.setCellValue("Fuel consumption"); if (null != data && 0 != data.size()) { for (int i = 0; i < data.size(); i++) { HSSFRow row = sheet.createRow(i + 2); Mileageoil o = (Mileageoil) data.get(i); HSSFCell cell001 = row.createCell(0); cell001.setCellStyle(style); cell001.setCellValue(i + 1); HSSFCell cell002 = row.createCell(1); cell002.setCellStyle(style); cell002.setCellValue(o.getVehiclename()); HSSFCell cell003 = row.createCell(2); cell003.setCellStyle(style); cell003.setCellValue(o.getTypename()); HSSFCell cell004 = row.createCell(3); cell004.setCellStyle(style); cell004.setCellValue(o.getRiqi()); HSSFCell cell005 = row.createCell(4); cell005.setCellStyle(style); cell005.setCellValue(o.getMileage() + "m"); HSSFCell cell006 = row.createCell(5); cell006.setCellStyle(style); cell006.setCellValue(o.getOilcost() == null ? "0" : o.getOilcost() + "L"); } } return wb; } }; Object[] total = new Object[1]; total[0] = ""; String str = "Fuel Mileage"; return ee.export(total, data, str); }