List of usage examples for org.apache.poi.ss.usermodel Sheet addMergedRegion
int addMergedRegion(CellRangeAddress region);
From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java
License:Apache License
private void saveRow(Workbook wb, Sheet sheet, ReportBook reportBook, ReportModel model, int row, CreationHelper createHelper) throws SaveReportException { TableRow tableRow = model.getRowModel().getRow(row); Row sheetRow = sheet.getRow(row);/* w w w. j av a2s. c o m*/ for (int column = 0; column < tableRow.getColCount(); column++) { jdbreport.model.Cell cell = tableRow.getCellItem(column); if (!cell.isChild()) { Cell newCell = sheetRow.getCell(column); if (newCell == null) { newCell = sheetRow.createCell(column); } Object styleId = cell.getStyleId(); if (styleId != null) { CellStyle newStyle = styleMap.get(styleId); if (newStyle != null) { newCell.setCellStyle(newStyle); if (cell.isSpan()) { for (int row1 = row; row1 <= row + cell.getRowSpan(); row1++) { Row spanedRow = sheet.getRow(row1); if (spanedRow == null) { spanedRow = sheet.createRow(row1); } for (int column1 = column; column1 <= column + cell.getColSpan(); column1++) { if (row1 != row || column1 != column) { Cell newCell1 = spanedRow.createCell(column1); newCell1.setCellStyle(newStyle); } } } } } } Object value = cell.getValue(); if (value != null) { if (cell.getValueType() == Type.BOOLEAN) { newCell.setCellType(CellType.BOOLEAN); newCell.setCellValue((Boolean) value); } else if (cell.getValueType() == Type.CURRENCY || cell.getValueType() == Type.FLOAT) { setDoubleValue(wb, createHelper, newCell, styleId, (Number) value); } else if (cell.getValueType() == Type.DATE) { newCell.setCellStyle(getStyle(styleId, Type.DATE, wb, createHelper)); newCell.setCellValue((Date) value); } else if (reportBook.getStyles(cell.getStyleId()).getDecimal() != -1) { try { setDoubleValue(wb, createHelper, newCell, styleId, Utils.parseDouble(value.toString())); } catch (Exception e) { newCell.setCellValue(0); } } else { String text = null; if (value instanceof CellValue<?>) { StringWriter strWriter = new StringWriter(); PrintWriter printWriter = new PrintWriter(strWriter); if (!((CellValue<?>) value).write(printWriter, model, row, column, this, ReportBook.XLS)) { java.awt.Image img = ((CellValue<?>) cell.getValue()).getAsImage(model, row, column); if (img instanceof RenderedImage) { createImage(wb, model, cell, (RenderedImage) img, row, column, createHelper); } } else { text = strWriter.getBuffer().toString(); } } else { newCell.setCellType(CellType.STRING); if (jdbreport.model.Cell.TEXT_HTML.equals(cell.getContentType())) { HTMLDocument doc = getHTMLDocument(cell); List<Content> contentList = Content.getHTMLContentList(doc); if (contentList != null) { RichTextString richText = createRichTextFromContent(contentList, createHelper, wb, newCell.getCellStyle().getFontIndex()); if (richText != null) { newCell.setCellValue(richText); } } } else { text = model.getCellText(cell); } } if (text != null) { newCell.setCellValue(text); } } } if (cell.getPicture() != null) { createImage(wb, model, cell, Utils.getRenderedImage(cell.getPicture().getIcon()), row, column, createHelper); } if (cell.getCellFormula() != null) { newCell.setCellFormula(cell.getCellFormula()); } if (cell.isSpan()) { sheet.addMergedRegion( new CellRangeAddress(row, row + cell.getRowSpan(), column, column + cell.getColSpan())); column += cell.getColSpan(); } } } }
From source file:jgnash.engine.budget.BudgetResultsExport.java
License:Open Source License
public static void exportBudgetResultsModel(final File file, final BudgetResultsModel model) { Resource rb = Resource.get(); Workbook wb;//ww w. java2s . co m String extension = FileUtils.getFileExtension(file.getAbsolutePath()); if (extension.equals("xlsx")) { wb = new XSSFWorkbook(); } else { wb = new HSSFWorkbook(); } CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet Sheet s = wb.createSheet(model.getBudget().getName()); // create header cell styles CellStyle headerStyle = wb.createCellStyle(); // create 2 fonts objects Font amountFont = wb.createFont(); Font headerFont = wb.createFont(); amountFont.setFontHeightInPoints((short) 10); amountFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setFontHeightInPoints((short) 11); headerFont.setColor(IndexedColors.BLACK.getIndex()); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); DataFormat df = wb.createDataFormat(); // Set the other cell style and formatting headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderTop(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setDataFormat(df.getFormat("text")); headerStyle.setFont(headerFont); headerStyle.setAlignment(CellStyle.ALIGN_CENTER); int row = 0; Row r = s.createRow(row); // create period headers for (int i = 0; i < model.getDescriptorList().size(); i++) { Cell c = r.createCell(i * 3 + 1); c.setCellValue( createHelper.createRichTextString(model.getDescriptorList().get(i).getPeriodDescription())); c.setCellStyle(headerStyle); s.addMergedRegion(new CellRangeAddress(row, row, i * 3 + 1, i * 3 + 3)); } { int col = model.getDescriptorList().size() * 3 + 1; Cell c = r.createCell(col); c.setCellValue(createHelper.createRichTextString(rb.getString("Title.Summary"))); c.setCellStyle(headerStyle); s.addMergedRegion(new CellRangeAddress(row, row, col, col + 2)); } // create results header columns row++; r = s.createRow(row); { Cell c = r.createCell(0); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Account"))); c.setCellStyle(headerStyle); for (int i = 0; i <= model.getDescriptorList().size(); i++) { c = r.createCell(i * 3 + 1); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Budgeted"))); c.setCellStyle(headerStyle); c = r.createCell(i * 3 + 2); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Change"))); c.setCellStyle(headerStyle); c = r.createCell(i * 3 + 3); c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Remaining"))); c.setCellStyle(headerStyle); } } // must sort the accounts, otherwise child structure is not correct List<Account> accounts = new ArrayList<>(model.getAccounts()); Collections.sort(accounts); // create account rows for (Account account : accounts) { CellStyle amountStyle = wb.createCellStyle(); amountStyle.setFont(amountFont); DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode()); String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix()); amountStyle.setDataFormat(df.getFormat(pattern)); row++; int col = 0; r = s.createRow(row); CellStyle cs = wb.createCellStyle(); cs.cloneStyleFrom(headerStyle); cs.setAlignment(CellStyle.ALIGN_LEFT); cs.setIndention((short) (model.getDepth(account) * 2)); Cell c = r.createCell(col); c.setCellValue(createHelper.createRichTextString(account.getName())); c.setCellStyle(cs); List<CellReference> budgetedRefList = new ArrayList<>(); List<CellReference> changeRefList = new ArrayList<>(); List<CellReference> remainingRefList = new ArrayList<>(); for (int i = 0; i < model.getDescriptorList().size(); i++) { BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), account); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getBudgeted().doubleValue()); c.setCellStyle(amountStyle); CellReference budgetedRef = new CellReference(row, col); budgetedRefList.add(budgetedRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getChange().doubleValue()); c.setCellStyle(amountStyle); CellReference changeRef = new CellReference(row, col); changeRefList.add(changeRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellStyle(amountStyle); c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString()); CellReference remainingRef = new CellReference(row, col); remainingRefList.add(remainingRef); } // add summary columns addSummaryCell(r, ++col, budgetedRefList, amountStyle); addSummaryCell(r, ++col, changeRefList, amountStyle); addSummaryCell(r, ++col, remainingRefList, amountStyle); } // add group summary rows for (AccountGroup group : model.getAccountGroupList()) { CellStyle amountStyle = wb.createCellStyle(); amountStyle.setFont(amountFont); amountStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); amountStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); amountStyle.setBorderBottom(CellStyle.BORDER_THIN); amountStyle.setBorderTop(CellStyle.BORDER_THIN); amountStyle.setBorderLeft(CellStyle.BORDER_THIN); amountStyle.setBorderRight(CellStyle.BORDER_THIN); DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency()); String pattern = format.toLocalizedPattern().replace("", model.getBaseCurrency().getPrefix()); amountStyle.setDataFormat(df.getFormat(pattern)); row++; int col = 0; r = s.createRow(row); CellStyle cs = wb.createCellStyle(); cs.cloneStyleFrom(headerStyle); cs.setAlignment(CellStyle.ALIGN_LEFT); Cell c = r.createCell(col); c.setCellValue(createHelper.createRichTextString(group.toString())); c.setCellStyle(cs); List<CellReference> budgetedRefList = new ArrayList<>(); List<CellReference> changeRefList = new ArrayList<>(); List<CellReference> remainingRefList = new ArrayList<>(); for (int i = 0; i < model.getDescriptorList().size(); i++) { BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), group); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getBudgeted().doubleValue()); c.setCellStyle(amountStyle); CellReference budgetedRef = new CellReference(row, col); budgetedRefList.add(budgetedRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(results.getChange().doubleValue()); c.setCellStyle(amountStyle); CellReference changeRef = new CellReference(row, col); changeRefList.add(changeRef); c = r.createCell(++col); c.setCellType(Cell.CELL_TYPE_FORMULA); c.setCellStyle(amountStyle); c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString()); CellReference remainingRef = new CellReference(row, col); remainingRefList.add(remainingRef); } // add summary columns addSummaryCell(r, ++col, budgetedRefList, amountStyle); addSummaryCell(r, ++col, changeRefList, amountStyle); addSummaryCell(r, ++col, remainingRefList, amountStyle); } // force evaluation FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll(); short columnCount = s.getRow(1).getLastCellNum(); // autosize all of the columns + 10 pixels for (int i = 0; i <= columnCount; i++) { s.autoSizeColumn(i); s.setColumnWidth(i, s.getColumnWidth(i) + 10); } // Save String filename = file.getAbsolutePath(); if (wb instanceof XSSFWorkbook) { filename = FileUtils.stripFileExtension(filename) + ".xlsx"; } else { filename = FileUtils.stripFileExtension(filename) + ".xls"; } try (FileOutputStream out = new FileOutputStream(filename)) { wb.write(out); } catch (Exception e) { Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e); } }
From source file:jp.ryoyamamoto.poiutils.Sheets.java
License:Apache License
/** * Merges two or more adjacent cells./* w w w . j a va 2s . co m*/ * <p> * Only the data in the upper-left cell of a range will remain in the merged * cell. Data in other cells of the range will be deleted. * </p> * * @param sheet * the sheet that the range is on. * @param range * the range to merge. */ public static void merge(Sheet sheet, CellRangeAddress range) { boolean copied = false; Cell upperLeftCell = Sheets.getCell(sheet, Ranges.getFirstCellReference(range)); for (CellReference reference : Ranges.getCellReferences(range)) { Cell cell = Sheets.getCell(sheet, reference); if (copied == false && Cells.isNotBlank(cell)) { Cells.copy(cell, upperLeftCell); copied = true; } if (cell != upperLeftCell) { Cells.clear(cell); } } sheet.addMergedRegion(range); }
From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java
License:Open Source License
/** * Creates a Microsoft Excel Workbook containing Topup activity provided in * a CSV text file. The format of the created file will be Office Open XML * (OOXML)./*from ww w .j ava2 s . c o m*/ * <p> * It expects the CSV to have the following columns from left to right:<br * /> * topup.uuid, topup.msisdn, topup.amount, network.name, topupStatus.status, * topup.topupTime * <p> * This method has been created to allow for large Excel files to be created * without overwhelming memory. * * * @param topupCSVFile a valid CSV text file. It should contain the full * path and name of the file e.g. "/tmp/export/topups.csv" * @param delimiter the delimiter used in the CSV file * @param excelFile the Microsoft Excel file to be created. It should * contain the full path and name of the file e.g. "/tmp/export/topups.xlsx" * @return whether the creation of the Excel file was successful or not */ public static boolean createExcelExport(final String topupCSVFile, final String delimiter, final String excelFile) { boolean success = true; int rowCount = 0; // To keep track of the row that we are on Row row; Map<String, CellStyle> styles; SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk // Each line of the file is approximated to be 200 bytes in size, // therefore 5000 lines are approximately 1 MB in memory // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk Sheet sheet = wb.createSheet("Airtime Topup"); styles = createStyles(wb); PrintSetup printSetupTopup = sheet.getPrintSetup(); printSetupTopup.setLandscape(true); sheet.setFitToPage(true); // Set up the heading to be seen in the Excel sheet row = sheet.createRow(rowCount); Cell titleCell; row.setHeightInPoints(45); titleCell = row.createCell(0); titleCell.setCellValue("Airtime Topups"); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); titleCell.setCellStyle(styles.get("title")); rowCount++; row = sheet.createRow(rowCount); row.setHeightInPoints(12.75f); for (int i = 0; i < TOPUP_TITLES.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(TOPUP_TITLES[i]); cell.setCellStyle(styles.get("header")); } rowCount++; FileUtils.deleteQuietly(new File(excelFile)); FileOutputStream out; try { FileUtils.touch(new File(excelFile)); // Read the CSV file and populate the Excel sheet with it LineIterator lineIter = FileUtils.lineIterator(new File(topupCSVFile)); String line; String[] lineTokens; int size; while (lineIter.hasNext()) { row = sheet.createRow(rowCount); line = lineIter.next(); lineTokens = StringUtils.split(line, delimiter); size = lineTokens.length; for (int cellnum = 0; cellnum < size; cellnum++) { Cell cell = row.createCell(cellnum); cell.setCellValue(lineTokens[cellnum]); } rowCount++; } out = new FileOutputStream(excelFile); wb.write(out); out.close(); } catch (FileNotFoundException e) { logger.error("FileNotFoundException while trying to create Excel file '" + excelFile + "' from CSV file '" + topupCSVFile + "'."); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } catch (IOException e) { logger.error("IOException while trying to create Excel file '" + excelFile + "' from CSV file '" + topupCSVFile + "'."); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } wb.dispose(); // dispose of temporary files backup of this workbook on disk return success; }
From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java
License:Open Source License
/** * Used to create a MS Excel file from a list of * * @param topups//from w ww.j a v a 2 s . c o m * @param networkHash a map with an UUID as the key and the name of the * network as the value * @param statusHash a map with an UUID as the key and the name of the * transaction status as the value * @param delimiter * @param excelFile the Microsoft Excel file to be created. It should * contain the full path and name of the file e.g. "/tmp/export/topups.xlsx" * @return whether the creation of the Excel file was successful or not */ public static boolean createExcelExport(final List<IncomingLog> topups, final HashMap<String, String> networkHash, final HashMap<String, String> statusHash, final String delimiter, final String excelFile) { boolean success = true; int rowCount = 0; // To keep track of the row that we are on Row row; Map<String, CellStyle> styles; SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk // Each line of the file is approximated to be 200 bytes in size, // therefore 5000 lines are approximately 1 MB in memory // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk Sheet sheet = wb.createSheet("Airtime Topup"); styles = createStyles(wb); PrintSetup printSetupTopup = sheet.getPrintSetup(); printSetupTopup.setLandscape(true); sheet.setFitToPage(true); // Set up the heading to be seen in the Excel sheet row = sheet.createRow(rowCount); Cell titleCell; row.setHeightInPoints(45); titleCell = row.createCell(0); titleCell.setCellValue("Airtime Topups"); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); titleCell.setCellStyle(styles.get("title")); rowCount++; row = sheet.createRow(rowCount); row.setHeightInPoints(12.75f); for (int i = 0; i < TOPUP_TITLES.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(TOPUP_TITLES[i]); cell.setCellStyle(styles.get("header")); } rowCount++; FileUtils.deleteQuietly(new File(excelFile)); FileOutputStream out; try { FileUtils.touch(new File(excelFile)); Cell cell; for (IncomingLog topup : topups) { row = sheet.createRow(rowCount); cell = row.createCell(0); cell.setCellValue(topup.getUuid()); //cell = row.createCell(1); //cell.setCellValue(topup.getMessageid()); cell = row.createCell(2); cell.setCellValue(topup.getDestination()); cell = row.createCell(3); cell.setCellValue(networkHash.get(topup.getOrigin())); cell = row.createCell(4); cell.setCellValue(statusHash.get(topup.getMessage())); cell = row.createCell(5); cell.setCellValue(topup.getLogTime().toString()); rowCount++; } out = new FileOutputStream(excelFile); wb.write(out); out.close(); } catch (IOException e) { logger.error("IOException while trying to create Excel file '" + excelFile + "' from list of topups."); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } wb.dispose(); // dispose of temporary files backup of this workbook on disk return success; }
From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java
License:Open Source License
public static boolean createExcelExport2(final List<OutgoingLog> topups, final HashMap<String, String> networkHash, final HashMap<String, String> statusHash, final String delimiter, final String excelFile) { boolean success = true; int rowCount = 0; // To keep track of the row that we are on Row row;/* w ww . ja va 2 s. com*/ Map<String, CellStyle> styles; SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk // Each line of the file is approximated to be 200 bytes in size, // therefore 5000 lines are approximately 1 MB in memory // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk Sheet sheet = wb.createSheet("Airtime Topup"); styles = createStyles(wb); PrintSetup printSetupTopup = sheet.getPrintSetup(); printSetupTopup.setLandscape(true); sheet.setFitToPage(true); // Set up the heading to be seen in the Excel sheet row = sheet.createRow(rowCount); Cell titleCell; row.setHeightInPoints(45); titleCell = row.createCell(0); titleCell.setCellValue("Airtime Topups"); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); titleCell.setCellStyle(styles.get("title")); rowCount++; row = sheet.createRow(rowCount); row.setHeightInPoints(12.75f); for (int i = 0; i < TOPUP_TITLES.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(TOPUP_TITLES[i]); cell.setCellStyle(styles.get("header")); } rowCount++; FileUtils.deleteQuietly(new File(excelFile)); FileOutputStream out; try { FileUtils.touch(new File(excelFile)); Cell cell; for (OutgoingLog topup : topups) { row = sheet.createRow(rowCount); cell = row.createCell(0); cell.setCellValue(topup.getUuid()); //cell = row.createCell(1); //cell.setCellValue(topup.getMessageid()); cell = row.createCell(2); cell.setCellValue(topup.getDestination()); cell = row.createCell(3); cell.setCellValue(networkHash.get(topup.getOrigin())); cell = row.createCell(4); cell.setCellValue(statusHash.get(topup.getMessage())); cell = row.createCell(5); cell.setCellValue(topup.getLogTime().toString()); rowCount++; } out = new FileOutputStream(excelFile); wb.write(out); out.close(); } catch (IOException e) { logger.error("IOException while trying to create Excel file '" + excelFile + "' from list of topups."); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } wb.dispose(); // dispose of temporary files backup of this workbook on disk return success; }
From source file:main.resources.FileExcel.java
public static void generaXlsx() throws IOException { //nombre del archivo de Excel String nombreArchivo = "quincena.xlsx"; String nombreHoja1 = "fecha";//nombre de la hoja1 Workbook libroTrabajo = new XSSFWorkbook(); Sheet hoja1 = libroTrabajo.createSheet(nombreHoja1); Row row = hoja1.createRow((short) 1); //row.setHeightInPoints(10); //alto de celda Cell cell = row.createCell((short) 1); Cell cell1 = row.createCell((short) 1); cell.setCellValue("Asistencia fecha xxxxxx"); CellStyle cellStyle = libroTrabajo.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //cellStyle.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex()); //cellStyle.setFillPattern(CellStyle.BIG_SPOTS); cellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex()); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex()); hoja1.addMergedRegion(new CellRangeAddress(1, // first row (0-based) primera fila 1, //lasto row (0-based) ultima fila 1, //first column (0-based) numero de columna inicial 5 //last column (0-based) numero de columna final ));//www. j a v a2s .c om cell.setCellStyle(cellStyle); cell1.setCellStyle(cellStyle); //escribir este libro en un OutputStream. try (FileOutputStream fileOut = new FileOutputStream(nombreArchivo)) { //escribir este libro en un OutputStream. libroTrabajo.write(fileOut); fileOut.flush(); } }
From source file:main.resources.FileExcel.java
private void combinarceldas(Sheet hoja, int pFila, int uFila, int nColumna, int nColumnaFinal) { hoja.addMergedRegion(new CellRangeAddress(pFila, uFila, nColumna, nColumnaFinal)); }
From source file:mn.tsagaangeruud.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//w w w . j a va2 s . c om 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("Weekly Timesheet"); 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 = "timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:net.algem.planning.export.PlanningExportService.java
License:Open Source License
/** * Export to Excel destination file.//from w w w .j a v a 2 s .c o m * * @param dayPlan list of day schedules * @param destFile destination file * @throws IOException */ public void exportPlanning(List<DayPlan> dayPlan, File destFile) throws IOException { GemLogger.info("Exporting planning to " + destFile); Hour defStartTime = new Hour(ConfigUtil.getConf(ConfigKey.START_TIME.getKey())); int offset = defStartTime.getHour(); int totalh = 24 - offset; // total time length in hours HSSFWorkbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("Planning"); if (dayPlan.size() > 0) { DateFormat df = new SimpleDateFormat("EEEE dd MMM yyyy"); Header header = sheet.getHeader(); String hd = df.format(dayPlan.get(0).getSchedule().get(0).getDate().getDate()); header.setCenter(HSSFHeader.fontSize((short) 12) + HSSFHeader.startBold() + hd + HSSFHeader.endBold()); } PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); printSetup.setPaperSize(paperSize); sheet.setFitToPage(true); sheet.setHorizontallyCenter(false);// was true before 2.15.8 sheet.setMargin(Sheet.TopMargin, 0.75); // 1.905 sheet.setMargin(Sheet.BottomMargin, 0.4); // 0.4 inch = 1.016 cm sheet.setMargin(Sheet.LeftMargin, 0.4); sheet.setMargin(Sheet.RightMargin, 0.4); Map<String, CellStyle> styles = createStyles(workbook); Row headerRow = sheet.createRow(0); for (int i = 0; i < dayPlan.size(); i++) { Cell roomCell = headerRow.createCell(i + 1); // Set the width (in units of 1/256th of a character width) //sheet.setColumnWidth(i + 1, totalh * 256);// max number of characters must not depend of time length sheet.setColumnWidth(i + 1, 24 * 256); // cours.titre character varying(32) roomCell.setCellValue(dayPlan.get(i).getLabel()); roomCell.setCellStyle(styles.get("header")); } int offsetMn = offset * 60;// offset in minutes List<Row> rows = new ArrayList<>(); System.out.println(" offset = " + offset + " totalh = " + totalh); for (int t = 0, rowNumber = 1; t < totalh * 60; t += 5, rowNumber++) { // 1 row = 5mn Hour hour = new Hour(offsetMn + t); Row row = sheet.createRow(rowNumber); //row.setHeightInPoints(25); row.setHeightInPoints(PrintSetup.A3_PAPERSIZE == paperSize ? 12 : 6); // TIME SUBDIVISIONS if (t % 15 == 0) { Cell cell = row.createCell(0); if (t % 30 == 0) { cell.setCellValue(hour.toString());//show time if (t % 60 == 0) { cell.setCellStyle(styles.get("hour")); } else { cell.setCellStyle(styles.get("hour-half")); } } else { cell.setCellStyle(styles.get("hour-quarter")); } } else { // BETWEEN SUBDIVISION Cell cell = row.createCell(0); if ("23:55".equals(hour.toString())) { // last slice cell.setCellStyle(styles.get("hour-last")); } else { cell.setCellStyle(styles.get("hour")); } if (rowNumber % 3 == 0) { // merge every 3 rows sheet.addMergedRegion(new CellRangeAddress(rowNumber - 2, rowNumber, 0, 0)); } } rows.add(row); } Map<java.awt.Color, CellStyle> coursStyleCache = new HashMap<>(); for (int i = 0; i < dayPlan.size(); i++) { DayPlan plan = dayPlan.get(i); int col = i + 1; for (ScheduleObject event : plan.getSchedule()) { // if event starts before default starting time if (event.getStart().toMinutes() < offsetMn) { event.setStart(new Hour(offset * 60)); } int startRowPos = (event.getStart().toMinutes() - offsetMn) / 5 + 1; int endRowPos = (event.getEnd().toMinutes() - offsetMn) / 5; Cell courseCell = rows.get(startRowPos - 1).createCell(col); courseCell.setCellValue(getLabel(event, workbook));// title text CellStyle style = getCourseStyle(workbook, event, coursStyleCache); courseCell.setCellStyle(style); if (startRowPos != endRowPos) { sheet.addMergedRegion(new CellRangeAddress(startRowPos, endRowPos, col, col)); for (int row = startRowPos; row < endRowPos; row++) { rows.get(row).createCell(col).setCellStyle(style); } } } } try (FileOutputStream out = new FileOutputStream(destFile)) { workbook.write(out); } }