List of usage examples for org.apache.poi.ss.util CellRangeAddress valueOf
public static CellRangeAddress valueOf(String ref)
From source file:com.dataart.spreadsheetanalytics.model.A1RangeAddress.java
License:Apache License
protected A1RangeAddress(String a1address) { this.addresses = new LinkedList<>(); if (!a1address.contains(RANGE_DELIMITER)) { this.addresses.add(A1Address.fromA1Address(a1address)); return;/* ww w . jav a2 s.co m*/ } CellRangeAddress addrs = CellRangeAddress.valueOf(a1address); int fromR = addrs.getFirstRow(); int fromC = addrs.getFirstColumn(); int toR = addrs.getLastRow(); int toC = addrs.getLastColumn(); for (int row = fromR; row <= toR; row++) { for (int col = fromC; col <= toC; col++) { this.addresses.add(A1Address.fromRowColumn(row, col)); } } }
From source file:com.firstonesoft.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*from w w w.j a v a 2 s .c o m*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Resumen de Horas"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); //header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } //row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); //set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } //finally set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 30 * 256); //30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); //6 characters wide } sheet.setColumnWidth(10, 10 * 256); //10 characters wide // Write the output to a file String file = "D://timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.github.cutstock.excel.model.SheetBuilder.java
License:Apache License
public void mergeRegion(IExcelRectangle titleRect) { String mergeStr = getMergeString(titleRect); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeStr)); }
From source file:com.github.cutstock.excel.utils.ExcelUtil.java
License:Apache License
public static void mergeRegion(Sheet sheet, IExcelRectangle titleRect) { sheet.addMergedRegion(CellRangeAddress.valueOf(getMergeString(titleRect))); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertDate(Sheet sheet, CellStyle style) { Row dateRow = sheet.createRow(6);/*from w w w . j a v a2 s .c o m*/ sheet.addMergedRegion(CellRangeAddress.valueOf("$B$7:$C$7")); Locale frenchLocale = new Locale("fr", "FR"); SimpleDateFormat dateFormat = new SimpleDateFormat("dd MMMM yyyy", frenchLocale); String dateString = dateFormat.format(new Date()); Cell dateCell = dateRow.createCell(1); dateCell.setCellStyle(style); dateCell.setCellValue(dateString); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertContacts(Sheet sheet, CellStyle pulsioNameStyle, CellStyle contactsStyle, Pulsiodetails pulsiodetails) {/*from ww w .j a v a 2 s .c o m*/ Row pulsioNameRow = sheet.createRow(9); Cell pulsioNameCell = pulsioNameRow.createCell(1); pulsioNameCell.setCellValue("PULSIO"); pulsioNameCell.setCellStyle(pulsioNameStyle); Row firstContactsRow = sheet.createRow(10); Cell firstContactsCell = firstContactsRow.createCell(1); firstContactsCell.setCellValue("Contact: " + pulsiodetails.getContact1()); firstContactsCell.setCellStyle(contactsStyle); Row secondContactsRow = sheet.createRow(11); Cell secondContactsCell = secondContactsRow.createCell(1); secondContactsCell.setCellValue(pulsiodetails.getContact2()); secondContactsCell.setCellStyle(contactsStyle); sheet.addMergedRegion(CellRangeAddress.valueOf("$B$11:$C$11")); sheet.addMergedRegion(CellRangeAddress.valueOf("$B$12:$C$12")); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertTitle(Sheet sheet, CellStyle style) { Row titleRow = sheet.createRow(14);//www.j av a 2 s. com Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Bon de livraison"); titleCell.setCellStyle(style); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$15:$J$15")); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertTableHeaders(Sheet sheet, CellStyle headerRowLeftCellStyleStyle, CellStyle headerRowMiddleCellStyle, CellStyle headerRowRightCellStyle) { Row tableHeadersRow = sheet.createRow(18); tableHeadersRow.setHeightInPoints((short) 35); sheet.addMergedRegion(CellRangeAddress.valueOf("$B$19:$E$19")); Cell titleCell = tableHeadersRow.createCell(1); titleCell.setCellValue("Titre"); titleCell.setCellStyle(headerRowLeftCellStyleStyle); for (int i = 2; i <= 4; i++) { tableHeadersRow.createCell(i).setCellStyle(headerRowMiddleCellStyle); }//from www .j a v a 2 s.co m Cell quantityCell = tableHeadersRow.createCell(5); quantityCell.setCellValue("Qunatite/carton"); quantityCell.setCellStyle(headerRowMiddleCellStyle); Cell boxesCountCell = tableHeadersRow.createCell(6); boxesCountCell.setCellValue("Nbre cartons"); boxesCountCell.setCellStyle(headerRowMiddleCellStyle); Cell totalQuantityCell = tableHeadersRow.createCell(7); totalQuantityCell.setCellValue("Quantite totale"); totalQuantityCell.setCellStyle(headerRowMiddleCellStyle); Cell palettesCountCell = tableHeadersRow.createCell(8); palettesCountCell.setCellValue("Nbre Palettes"); palettesCountCell.setCellStyle(headerRowRightCellStyle); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
/** * * @param sheet/*from w w w. j a v a 2s . c o m*/ * @param leftStyle * @param middleStyle * @param rightStyle * @param footerStyle * @return index of the last table row created */ private static int insertTableBody(Sheet sheet, CellStyle leftStyle, CellStyle middleStyle, CellStyle rightStyle, CellStyle footerStyle, List<BookBoxModel> bookBoxModels) { Integer index = 18; Integer currentBookTitleIndex = 1; Set<Integer> rowsToSum = new HashSet<Integer>(); Set<Integer> totalsToSum = new HashSet<Integer>(); String cellMergeString; String cellFormula; try { int currentBookNumber = bookBoxModels.get(0).getBookNumber(); for (int i = 0; i < bookBoxModels.size(); i++) { index++; BookBoxModel currentModel = bookBoxModels.get(i); Row row = sheet.createRow(index); if (currentBookNumber != currentModel.getBookNumber()) { for (int j = 2; j <= 4; j++) { row.createCell(j).setCellStyle(footerStyle); } addTotalTitleRow(row, index, sheet, footerStyle, currentBookTitleIndex, rowsToSum, totalsToSum); index++; currentBookTitleIndex++; row = sheet.createRow(index); } for (int j = 2; j <= 4; j++) { row.createCell(j).setCellStyle(middleStyle); } //Book Title Row Cell titleCell = row.createCell(1); cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString)); titleCell.setCellStyle(leftStyle); String isbn = currentModel.getISBN() != null ? currentModel.getISBN() : ""; String cellString = String.format("%s %s", currentModel.getTitle(), isbn); titleCell.setCellValue(cellString); Cell quantityCell = row.createCell(5); quantityCell.setCellStyle(middleStyle); quantityCell.setCellValue(Double.parseDouble(currentModel.getBooksCount().toString())); Cell boxCountCell = row.createCell(6); boxCountCell.setCellStyle(middleStyle); boxCountCell.setCellValue(Double.parseDouble(currentModel.getBoxesCount().toString())); Cell totalQuantityCell = row.createCell(7); totalQuantityCell.setCellStyle(rightStyle); cellFormula = String.format("F%s*G%s", index + 1, index + 1); totalQuantityCell.setCellFormula(cellFormula); Cell palettesCountCell = row.createCell(8); palettesCountCell.setCellStyle(rightStyle); // excel is not 0-based! rowsToSum.add(index + 1); currentBookNumber = currentModel.getBookNumber(); } index++; Row row = sheet.createRow(index); for (int i = 2; i <= 4; i++) { row.createCell(i).setCellStyle(footerStyle); } addTotalTitleRow(row, index, sheet, footerStyle, currentBookTitleIndex, rowsToSum, totalsToSum); //Total row index++; Row footerTotalRow = sheet.createRow(index); Cell footerTotalRowTitleCell = footerTotalRow.createCell(1); for (int i = 2; i <= 4; i++) { footerTotalRow.createCell(i).setCellStyle(footerStyle); } cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString)); footerTotalRowTitleCell.setCellStyle(footerStyle); footerTotalRowTitleCell.setCellValue("Total"); Cell footerTotalRowQuantityCell = footerTotalRow.createCell(5); footerTotalRowQuantityCell.setCellStyle(footerStyle); Cell footerTotalRowBoxCountCell = footerTotalRow.createCell(6); footerTotalRowBoxCountCell.setCellStyle(footerStyle); Cell footerTotalRowTotalQuantityCell = footerTotalRow.createCell(7); footerTotalRowTotalQuantityCell.setCellStyle(footerStyle); Cell footerTotalRowPalettesCountCell = footerTotalRow.createCell(8); footerTotalRowPalettesCountCell.setCellStyle(footerStyle); //build cell formulas StringBuilder totalBoxesCountformulaBuilder = new StringBuilder(); StringBuilder totalBooksCountformulaBuilder = new StringBuilder(); StringBuilder totalPaletsCountformulaBuilder = new StringBuilder(); // Example: SUM(H22;H25;H28;H31;H34) totalBoxesCountformulaBuilder.append("SUM("); totalBooksCountformulaBuilder.append("SUM("); totalPaletsCountformulaBuilder.append("SUM("); for (Integer integer : totalsToSum) { totalBoxesCountformulaBuilder.append("G").append(integer).append(","); totalBooksCountformulaBuilder.append("H").append(integer).append(","); totalPaletsCountformulaBuilder.append("I").append(integer).append(","); } totalBoxesCountformulaBuilder.deleteCharAt(totalBoxesCountformulaBuilder.length() - 1); totalBooksCountformulaBuilder.deleteCharAt(totalBooksCountformulaBuilder.length() - 1); totalPaletsCountformulaBuilder.deleteCharAt(totalPaletsCountformulaBuilder.length() - 1); totalBoxesCountformulaBuilder.append(")"); totalBooksCountformulaBuilder.append(")"); totalPaletsCountformulaBuilder.append(")"); footerTotalRowBoxCountCell.setCellFormula(totalBoxesCountformulaBuilder.toString()); footerTotalRowTotalQuantityCell.setCellFormula(totalBooksCountformulaBuilder.toString()); footerTotalRowPalettesCountCell.setCellFormula(totalPaletsCountformulaBuilder.toString()); } catch (Exception e) { e.printStackTrace(); } return index; }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void addTotalTitleRow(Row row, Integer index, Sheet sheet, CellStyle footerStyle, Integer currentBookTitleIndex, Set<Integer> rowsToSum, Set<Integer> totalsToSum) { //Total Book Title Row Cell totalRowTitleCell = row.createCell(1); String cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString)); totalRowTitleCell.setCellStyle(footerStyle); totalRowTitleCell.setCellValue("Total Title " + currentBookTitleIndex++); Cell totalRowQuantityCell = row.createCell(5); totalRowQuantityCell.setCellStyle(footerStyle); Cell totalRowBoxCountCell = row.createCell(6); totalRowBoxCountCell.setCellStyle(footerStyle); Cell totalRowTotalQuantityCell = row.createCell(7); totalRowTotalQuantityCell.setCellStyle(footerStyle); Cell totalRowPalettesCountCell = row.createCell(8); totalRowPalettesCountCell.setCellStyle(footerStyle); //build cell formulas StringBuilder totalBoxesCountformulaBuilder = new StringBuilder(); StringBuilder totalBooksCountformulaBuilder = new StringBuilder(); StringBuilder totalPaletsCountformulaBuilder = new StringBuilder(); // Example: SUM(H22;H25;H28;H31;H34) totalBoxesCountformulaBuilder.append("SUM("); totalBooksCountformulaBuilder.append("SUM("); totalPaletsCountformulaBuilder.append("SUM("); for (Integer integer : rowsToSum) { totalBoxesCountformulaBuilder.append("G").append(integer).append(","); totalBooksCountformulaBuilder.append("H").append(integer).append(","); totalPaletsCountformulaBuilder.append("I").append(integer).append(","); }/* ww w .j ava2s . c o m*/ totalBoxesCountformulaBuilder.deleteCharAt(totalBoxesCountformulaBuilder.length() - 1); totalBooksCountformulaBuilder.deleteCharAt(totalBooksCountformulaBuilder.length() - 1); totalPaletsCountformulaBuilder.deleteCharAt(totalPaletsCountformulaBuilder.length() - 1); totalBoxesCountformulaBuilder.append(")"); totalBooksCountformulaBuilder.append(")"); totalPaletsCountformulaBuilder.append(")"); totalRowBoxCountCell.setCellFormula(totalBoxesCountformulaBuilder.toString()); totalRowTotalQuantityCell.setCellFormula(totalBooksCountformulaBuilder.toString()); totalRowPalettesCountCell.setCellFormula(totalPaletsCountformulaBuilder.toString()); // excel is not 0-based totalsToSum.add(index + 1); rowsToSum.clear(); }