List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells./*from ww w.j a v a 2 s .co m*/ */ private static void testCreateSampleSheet(String outputFilename) throws IOException { int rownum; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet FileOutputStream out = new FileOutputStream(outputFilename); wb.write(out); out.close(); }
From source file:at.htlpinkafeld.beans.JahresuebersichtBean.java
/** * post processes the XLS for creating/*ww w . j a v a 2 s. com*/ * * @param document xls-doc */ public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); sheet.shiftRows(0, sheet.getLastRowNum(), 2); HSSFRow topRow = sheet.createRow(0); topRow.createCell(0).setCellValue("Jahresbersicht - " + selectedYear.getYear()); topRow.createCell(3).setCellValue("von " + selectedUser.getPersName()); sheet.createRow(1).createCell(0).setCellValue(" "); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); HSSFRow header = sheet.getRow(2); HSSFRow footer = sheet.getRow(sheet.getLastRowNum()); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); cell = footer.getCell(i); cell.setCellStyle(cellStyle); sheet.autoSizeColumn(i); } HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2); bottomRow.createCell(0) .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy"))); }
From source file:at.htlpinkafeld.beans.UserDetailsBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); sheet.shiftRows(0, sheet.getLastRowNum(), 2); HSSFRow topRow = sheet.createRow(0); topRow.createCell(0)/*w w w .j a va2 s . com*/ .setCellValue("Monatsbersicht - " + selectedDate.format(DateTimeFormatter.ofPattern("MM.yyyy"))); topRow.createCell(7).setCellValue("von " + selectedUser); sheet.createRow(1).createCell(0).setCellValue(" "); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); HSSFRow header = sheet.getRow(2); HSSFRow footer = sheet.getRow(sheet.getLastRowNum()); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); cell = footer.getCell(i); cell.setCellStyle(cellStyle); sheet.autoSizeColumn(i); } HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2); bottomRow.createCell(0) .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy"))); }
From source file:br.edu.tglima.model.result.PlanilhaXLS.java
License:Open Source License
/** * Mtodo responsvel por gerar a planilha XLS. * // ww w.ja v a2 s .c om * * @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:br.solutio.licita.controlador.ControladorPregao.java
public void editandoXlsParaExportar(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet planilha = wb.getSheetAt(0); //Move as celulas selecionadas para baixo de acordo com o valor informado planilha.shiftRows(planilha.getFirstRowNum(), planilha.getLastRowNum(), 5); HSSFRow linha0 = planilha.createRow(0); linha0.createCell(0).setCellValue("Instituio Licitadora:"); planilha.addMergedRegion(new CellRangeAddress(0, 0, 0, 1)); linha0.createCell(2)/*ww w.j a v a2s. co m*/ .setCellValue(" " + getEntidade().getInstituicaoLicitadora().getPessoaJuridica().getNomeFantasia()); planilha.addMergedRegion(new CellRangeAddress(0, 0, 2, 6)); HSSFRow linha1 = planilha.createRow(1); linha1.createCell(0).setCellValue("Numero do Pregao:"); planilha.addMergedRegion(new CellRangeAddress(1, 1, 0, 1)); linha1.createCell(2).setCellValue(" " + getEntidade().getNumeroPregao()); planilha.addMergedRegion(new CellRangeAddress(1, 1, 2, 6)); HSSFRow linha2 = planilha.createRow(2); linha2.createCell(0).setCellValue("Numero do Processo:"); planilha.addMergedRegion(new CellRangeAddress(2, 2, 0, 1)); linha2.createCell(2).setCellValue(" " + getEntidade().getNumeroProcesso()); planilha.addMergedRegion(new CellRangeAddress(2, 2, 2, 6)); HSSFRow linha3 = planilha.createRow(3); linha3.createCell(0).setCellValue("Empresa Licitante:"); planilha.addMergedRegion(new CellRangeAddress(3, 3, 0, 1)); linha3.createCell(2).setCellValue("Preencha com o nome de sua Empresa"); planilha.addMergedRegion(new CellRangeAddress(3, 3, 2, 6)); HSSFRow linha4 = planilha.createRow(4); //Nova coluna para a empresas adicionarem seus valores HSSFRow linha5 = planilha.getRow(5); HSSFCell celula5 = linha5.createCell(5); celula5.setCellValue("Valor do Licitante"); //for para ajustar automaticamente o tamnho das colunas for (int i = 0; i < 6; i++) { planilha.autoSizeColumn(i); } //Cor da linha de titulos da tabela HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < linha5.getPhysicalNumberOfCells(); i++) { HSSFCell cell = linha5.getCell(i); cell.setCellStyle(cellStyle); } CellStyle unlockedCellStyle = wb.createCellStyle(); unlockedCellStyle.setLocked(false); HSSFCell celula2 = linha3.getCell(2); celula2.setCellStyle(unlockedCellStyle); }
From source file:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
private static void writeSubActivityTime() { Map userHours = getHoursInfo(); HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage"); String lastSubActivityName = null; List months = new ArrayList(); int count = 0; for (int i = 5; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; }//w w w.ja v a 2 s. c om } HSSFCell cell = row.getCell((short) 4); HSSFCell userNameCell = row.getCell((short) 2); if (cell != null) { String userName = userNameCell.getStringCellValue(); if ("Month".equals(userName.trim()) || "User Name".equals(userName.trim())) { continue; } if ("Normal Working day of a Month".equals(userName.trim())) { months = getMonthInfo(i, row, templateSheet); continue; } if (lastSubActivityName == null) { lastSubActivityName = cell.getStringCellValue(); count = 1; } else { String newSubActivityName = cell.getStringCellValue(); if (newSubActivityName != null) { if (newSubActivityName.equals(lastSubActivityName)) { count++; for (int j = 4, k = 0; j < 1000;) { HSSFCell detailCell = row.getCell((short) (j)); if (detailCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { percentageStyle.setFont(templateSheet.getRow(i - 1).getCell((short) (j + 5)) .getCellStyle().getFont(templateWbk)); HSSFCell precOfType = row.getCell((short) (j + 2)); HSSFCell precOfSubActivity = row.getCell((short) (j + 3)); // String prec = precOfType.getStringCellValue().trim(); double prec = precOfType.getNumericCellValue(); String key = userName + "." + months.get(k); TimeUsage timeUsage = (TimeUsage) userHours.get(key); Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) (j)); region.setColumnTo((short) (j)); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) j).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) (j + 3)); region.setColumnTo((short) (j + 3)); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) (j + 3)).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); if (prec > 0 && timeUsage.getPercentage() > 0) { templateSheet.getRow(i - count + 1).getCell((short) (j + 3)) .setCellStyle(percentageStyle); double oldValue = templateSheet.getRow(i - count + 1) .getCell((short) (j + 3)).getNumericCellValue(); templateSheet.getRow(i - count + 1).getCell((short) (j + 3)) .setCellValue(oldValue + prec); } j += 6; k++; continue; } j++; } } else { lastSubActivityName = newSubActivityName; writePrecOfSubActivity(templateSheet, months, userName, userHours, row, i); count = 1; } } else { lastSubActivityName = newSubActivityName; count = 1; } } } } }
From source file:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
private static void writeIndividualTime() { Map userHours = getHoursInfo(); HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage"); String lastUserName = null;/* w w w .j av a 2 s .c o m*/ List months = new ArrayList(); for (int i = 5; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } HSSFCell cell = row.getCell((short) 2); if (cell != null) { if (lastUserName == null) { lastUserName = cell.getStringCellValue(); } else { String newUserName = cell.getStringCellValue(); if ("Month".equals(newUserName.trim()) || "User Name".equals(newUserName.trim())) { continue; } if ("Normal Working day of a Month".equals(newUserName.trim())) { months = new ArrayList(); row = templateSheet.getRow(i - 1); for (int j = 3; j < 1000; j++) { HSSFCell monthCell = row.getCell((short) j); if (monthCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { String monthValue = monthCell.getStringCellValue(); if (monthValue != null && !monthValue.equals("")) { months.add(monthCell.getStringCellValue()); } } } continue; } if (newUserName != null) { if (newUserName.equals(lastUserName)) { Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 2); region.setColumnTo((short) 2); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) 2).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCell activityCell = row.getCell((short) 3); HSSFCell oldActivityCell = templateSheet.getRow(i - 1).getCell((short) 3); if (activityCell.getStringCellValue().equals(oldActivityCell.getStringCellValue())) { region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 3); region.setColumnTo((short) 3); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) 3).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } for (int j = 4, k = 0; j < 1000;) { HSSFCell detailCell = row.getCell((short) (j)); if (detailCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { percentageStyle.setFont(templateSheet.getRow(i - 1).getCell((short) (j + 5)) .getCellStyle().getFont(templateWbk)); HSSFCell precOfType = row.getCell((short) (j + 2)); String prec = precOfType.getStringCellValue().trim(); String key = newUserName + "." + months.get(k); TimeUsage timeUsage = (TimeUsage) userHours.get(key); if (!prec.equals("")) { if (timeUsage.getHours() > 0) { precOfType.setCellStyle(percentageStyle); precOfType.setCellValue( (new BigDecimal(prec)).doubleValue() / timeUsage.getHours()); } } region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) (j + 4)); region.setColumnTo((short) (j + 4)); templateSheet.addMergedRegion(region); if (timeUsage.getHours() > 0) { templateSheet.getRow(i - 1).getCell((short) (j + 4)) .setCellValue(timeUsage.getHours()); } region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) (j + 5)); region.setColumnTo((short) (j + 5)); templateSheet.addMergedRegion(region); if (timeUsage.getPercentage() > 0) { templateSheet.getRow(i - 1).getCell((short) (j + 5)) .setCellStyle(percentageStyle); templateSheet.getRow(i - 1).getCell((short) (j + 5)) .setCellValue(timeUsage.getPercentage()); } j += 6; k++; continue; } j++; } } else { lastUserName = newUserName; writePrec(templateSheet, months, newUserName, userHours, row, i); } } else { lastUserName = newUserName; } } } } }
From source file:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
public static void writeEffortSummary() { HSSFSheet templateSheet = templateWbk.getSheet("Effort Summary"); String lastCellValue = null;//from w ww .ja v a2s. c o m double days = 0; int count = 1; for (int i = 6; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } else { HSSFCell cell = row.getCell((short) 2); HSSFCell dayCell = row.getCell((short) 6); if (cell != null) { if (lastCellValue == null) { lastCellValue = cell.getStringCellValue(); count = 1; String dayStr = dayCell.getStringCellValue().trim(); days = new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count); templateSheet.getRow(i).getCell((short) 4).setCellValue(days); } else { String newCellValue = cell.getStringCellValue(); if ("Project Name".equals(newCellValue)) { continue; } if (newCellValue != null) { if (newCellValue.equals(lastCellValue)) { Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 2); region.setColumnTo((short) 2); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) 3).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 3); region.setColumnTo((short) 3); templateSheet.addMergedRegion(region); count++; templateSheet.getRow(i - count + 1).getCell((short) 3).setCellValue((double) count); region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 4); region.setColumnTo((short) 4); templateSheet.addMergedRegion(region); String dayStr = dayCell.getStringCellValue().trim(); days += new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i - count + 1).getCell((short) 4).setCellValue(days); } else { lastCellValue = newCellValue; count = 1; templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count); String dayStr = dayCell.getStringCellValue().trim(); days = new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i).getCell((short) 4).setCellValue(days); } } else { lastCellValue = newCellValue; count = 1; templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count); String dayStr = dayCell.getStringCellValue().trim(); days = new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i).getCell((short) 4).setCellValue(days); } } } } } }
From source file:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
public static void writeEffort(int index, String sheetName) { HSSFSheet templateSheet = templateWbk.getSheet(sheetName); String lastCellValue = null;/*from w w w .java2 s . c o m*/ for (int i = 0; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } else { HSSFCell cell = row.getCell((short) index); if (cell != null) { if (lastCellValue == null && cell.getStringCellValue() != null && !cell.getStringCellValue().trim().equals("")) { lastCellValue = cell.getStringCellValue(); } else { String newCellValue = cell.getStringCellValue(); if (newCellValue != null && !newCellValue.trim().equals("")) { if (newCellValue.equals(lastCellValue)) { Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) index); region.setColumnTo((short) index); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) index).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } else { lastCellValue = newCellValue; } } else { lastCellValue = newCellValue; } } } } } }
From source file:cn.fql.template.poi.PoiTest.java
License:Open Source License
public static void writeEffort(int index, String sheetName) { HSSFSheet templateSheet = templateWbk.getSheet(sheetName); String lastCellValue = null;/*from w w w. j a va2 s . c om*/ for (int i = 1118; i < 1232; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } else { HSSFCell cell = row.getCell((short) index); if (lastCellValue == null) { lastCellValue = cell.getRichStringCellValue().getString(); } else { String newCellValue = cell.getRichStringCellValue().getString(); if (cell != null) { if (lastCellValue.equals(newCellValue)) { Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) index); region.setColumnTo((short) index); templateSheet.addMergedRegion(region); } else { lastCellValue = newCellValue; } } } } } }