List of usage examples for org.apache.poi.ss.usermodel Cell setCellValue
void setCellValue(boolean value);
From source file:com.google.gdt.handler.impl.ExcelHandler.java
License:Open Source License
/** * //w ww .j av a2 s . co m * @param inputFile * @throws IOException * @throws InvalidFormatException */ @Override public void handle(String inputFile, ProgressLevel pLevel) throws IOException, InvalidFormatException { String outPutFile = getOuputFileName(inputFile); OutputStream outputStream = new FileOutputStream(outPutFile); InputStream is = new FileInputStream(inputFile); Workbook wb = WorkbookFactory.create(is); List<Sheet> sheets = getSheets(wb); pLevel.setTrFileName(outPutFile); //iterate over sheet for (int index = 0; index < sheets.size(); index++) { Sheet sheet = sheets.get(index); if (sheets.size() > 1) { pLevel.setString("Translating sheet " + (index + 1) + "/" + sheets.size()); } int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); int rowCount = lastRowNum - firstRowNum; // check for empty sheet, don't perform any operation if (rowCount == 0) { continue; } pLevel.setValue(0); pLevel.setMaxValue(rowCount); pLevel.setStringPainted(true); int pBarUpdate = 0; //iterate over row for (Row row : sheet) { //iterate over cells for (Cell cell : row) { if (isInterrupted) { outputStream.close(); new File(outPutFile).delete(); pLevel.setString("cancelled"); return; } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String inputText = cell.getStringCellValue(); String translatedTxt = inputText; try { translatedTxt = translator.translate(inputText); cell.setCellValue(translatedTxt); } catch (Exception e) { logger.log(Level.SEVERE, "Input File : " + inputFile + " cannot translate the text : " + inputText, e); continue; } } } //cell iteration ends pBarUpdate++; pLevel.setValue(pBarUpdate); } //row iteration ends pLevel.setValue(rowCount); } pLevel.setString("done"); wb.write(outputStream); outputStream.close(); }
From source file:com.grant.report.StockOut.java
public static void main(String[] args) throws SQLException { ItemDAO d = new ItemDAO(); Map<String, Object[]> data = new TreeMap<String, Object[]>(); // data = d.getAllItemOutReport(); //Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Employee Data"); //This data needs to be written (Object[]) /*/* w w w. ja v a 2 s .c om*/ Map<String, Object[]> data = new TreeMap<String, Object[]>(); data.put("1", new Object[] {"ID", "NAME", "LASTNAME"}); data.put("2", new Object[] {1, "Amit", "Shukla"}); data.put("3", new Object[] {2, "Lokesh", "Gupta"}); data.put("4", new Object[] {3, "John", "Adwards"}); data.put("5", new Object[] {4, "Brian", "Schultz"}); */ //Iterate over data and write to sheet Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Integer) { cell.setCellValue((Integer) obj); } } } try { //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx")); workbook.write(out); out.close(); System.out.println("howtodoinjava_demo.xlsx written successfully on disk."); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertDate(Sheet sheet, CellStyle style) { Row dateRow = sheet.createRow(6);/*from w ww . j a va2 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) {/* ww w. j a v a 2 s . c om*/ 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);/* ww w. 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); }//ww w . 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 . ja v a2 s . 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(","); }/* w w w.ja va 2s .co 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(); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertFooter(Sheet sheet, CellStyle footerStyle, int index, String packageNumber, String deliveryAddress, String client) { String mergeString;/*w ww .j a v a 2 s . co m*/ index += 2; Row transportNumberRow = sheet.createRow(index); Cell transportNumberCell = transportNumberRow.createCell(1); transportNumberCell.setCellValue("Num Tpt: " + packageNumber); transportNumberCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 2; Row addressLabelRow = sheet.createRow(index); Cell addressLabelCell = addressLabelRow.createCell(1); addressLabelCell.setCellValue("A livrer chez: "); addressLabelCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 1; Row addressRow = sheet.createRow(index); Cell addressCell = addressRow.createCell(1); addressCell.setCellValue(deliveryAddress); addressCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$I$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 3; Row clientLabelRow = sheet.createRow(index); Cell clientLabelCell = clientLabelRow.createCell(1); clientLabelCell.setCellValue("Pour le compte des Editions: "); clientLabelCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 1; Row clientRow = sheet.createRow(index); Cell clientCell = clientRow.createCell(1); clientCell.setCellValue(client); clientCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$I$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 4; Row dateRow = sheet.createRow(index); Cell dateCell = dateRow.createCell(1); dateCell.setCellValue("Date: ................"); dateCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 3; Row signatureRow = sheet.createRow(index); Cell signatureCell = signatureRow.createCell(1); signatureCell.setCellValue("Signature et tampon: ..................."); signatureCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); }