List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook createSheet
@Override
public SXSSFSheet createSheet(String sheetname)
From source file:excelmasivo.ExcelMasivo.java
/** * @param args the command line arguments *///from w w w .j a v a 2s. c om public static void main(String[] args) { String driver = "oracle.jdbc.OracleDriver"; String user = "DRKL"; String pass = "DRKL"; String url = "jdbc:oracle:thin:@localhost:1521:XE"; String query = "SELECT * FROM PRODUCTOS ORDER BY TO_NUMBER(SUBSTR(CODIGO_PRODUCTO,7))"; Connection con; Statement st; ResultSet rs; ResultSetMetaData rsm; SXSSFWorkbook libro = new SXSSFWorkbook(); SXSSFSheet hoja = libro.createSheet("Reporte"); SXSSFRow fila; SXSSFCell celda; FileOutputStream out; int x = 0; CellStyle cs = libro.createCellStyle(); cs.getFillForegroundColor(); Font f = libro.createFont(); //f.setBoldweight(Font.BOLDWEIGHT_BOLD); f.setFontHeightInPoints((short) 12); cs.setFont(f); try { Class.forName(driver); con = DriverManager.getConnection(url, user, pass); st = con.createStatement(); rs = st.executeQuery(query); rsm = rs.getMetaData(); while (rs.next()) { //crear la fila fila = hoja.createRow(x++); for (int i = 1; i <= rsm.getColumnCount(); i++) { //recorrer las columnas celda = fila.createCell(i); celda.setCellStyle(cs); celda.setCellValue(rs.getString(i)); //System.out.print(rs.getString(i)); } //System.out.println(); if (x % 50000 == 0) { System.out.println("Se procesaron:" + x); } } out = new FileOutputStream(new File("D:\\java\\Productos_" + GregorianCalendar.MILLISECOND + ".xlsx")); libro.write(out); out.close(); System.out.println("Archivo generado con exito"); } catch (ClassNotFoundException | SQLException | FileNotFoundException ex) { Logger.getLogger(ExcelMasivo.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExcelMasivo.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:exporter.ExcelExporter.java
public static void writeXlsx(OutputStream outputStream, String dbResource, String queryString, String... args) throws NamingException { Context initContext = null;//from ww w . j a va 2 s.c o m try { initContext = new InitialContext(); } catch (NamingException ex) { Logger.getLogger(ExcelExporter.class.getName()).log(Level.SEVERE, null, ex); } Context envContext = (Context) initContext.lookup("java:/comp/env/"); DataSource datasource = (DataSource) envContext.lookup(dbResource); try { PreparedStatement ps = datasource.getConnection().prepareStatement(queryString); ResultSet rSet = ps.executeQuery(); ResultSetMetaData rsMetaData = rSet.getMetaData(); int columnCount = rsMetaData.getColumnCount(); SXSSFWorkbook workBook = new SXSSFWorkbook(); SXSSFSheet sheet = (SXSSFSheet) workBook.createSheet("cics"); String currentLine = null; int rowNum = 0; int types[] = new int[columnCount]; Row intestazione = sheet.createRow(rowNum); for (int i = 0; i < columnCount; i++) { intestazione.createCell(i).setCellValue(rsMetaData.getColumnLabel(i + 1)); types[i] = rsMetaData.getColumnType(i + 1); } rowNum++; while (rSet.next()) { rowNum++; Row currentRow = sheet.createRow(rowNum); for (int k = 0; k < columnCount; k++) { switch (types[k]) { case Types.INTEGER: currentRow.createCell(k).setCellValue(rSet.getInt(k + 1)); break; case Types.FLOAT: currentRow.createCell(k).setCellValue(rSet.getFloat(k + 1)); break; case Types.BIGINT: currentRow.createCell(k).setCellValue(rSet.getInt(k + 1)); break; case Types.DOUBLE: currentRow.createCell(k).setCellValue(rSet.getDouble(k + 1)); break; case Types.DATE: currentRow.createCell(k).setCellValue(rSet.getDate(k + 1)); break; case Types.TIMESTAMP: currentRow.createCell(k).setCellValue(rSet.getTimestamp(k + 1)); break; default: currentRow.createCell(k).setCellValue(rSet.getString(k + 1)); break; } currentRow.createCell(k).setCellValue(rSet.getString(k + 1)); } } try { workBook.write(outputStream); } catch (IOException ex) { Logger.getLogger(ExcelExporter.class.getName()).log(Level.SEVERE, null, ex); } } catch (SQLException ex) { Logger.getLogger(ExcelExporter.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:exporter.XlsxExporter.java
public static void writeXLSX(OutputStream outputStream, String resourceDbPath, String queryString, String... args) {/* w w w . jav a 2 s . com*/ try { ResourceBundle rb = ResourceBundle.getBundle(resourceDbPath); Class.forName(rb.getString("driver")); Connection conn = DriverManager.getConnection(rb.getString("url"), rb.getString("user"), rb.getString("password")); PreparedStatement pStatement = conn.prepareStatement(queryString, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); int paramCount = 1; for (String arg : args) { pStatement.setString(paramCount++, arg); } ResultSet rSet = pStatement.executeQuery(); ResultSetMetaData rsMetaData = rSet.getMetaData(); int columnCount = rsMetaData.getColumnCount(); SXSSFWorkbook workBook = new SXSSFWorkbook(); SXSSFSheet sheet = (SXSSFSheet) workBook.createSheet("cics"); String currentLine = null; int rowNum = 0; int types[] = new int[columnCount]; Row intestazione = sheet.createRow(rowNum); for (int i = 0; i < columnCount; i++) { intestazione.createCell(i).setCellValue(rsMetaData.getColumnLabel(i + 1)); types[i] = rsMetaData.getColumnType(i + 1); } rowNum++; while (rSet.next()) { rowNum++; Row currentRow = sheet.createRow(rowNum); for (int k = 0; k < columnCount; k++) { switch (types[k]) { case Types.INTEGER: currentRow.createCell(k).setCellValue(rSet.getInt(k + 1)); break; case Types.FLOAT: currentRow.createCell(k).setCellValue(rSet.getFloat(k + 1)); break; case Types.BIGINT: currentRow.createCell(k).setCellValue(rSet.getInt(k + 1)); break; case Types.DOUBLE: currentRow.createCell(k).setCellValue(rSet.getDouble(k + 1)); break; case Types.DATE: currentRow.createCell(k).setCellValue(rSet.getDate(k + 1)); break; case Types.TIMESTAMP: currentRow.createCell(k).setCellValue(rSet.getTimestamp(k + 1)); break; default: currentRow.createCell(k).setCellValue(rSet.getString(k + 1)); break; } currentRow.createCell(k).setCellValue(rSet.getString(k + 1)); } } rSet.close(); pStatement.close(); conn.close(); workBook.write(outputStream); System.out.println("Done"); } catch (ClassNotFoundException ex) { System.out.println(ex.getMessage() + "Exception in try"); } catch (SQLException ex) { System.out.println(ex.getMessage() + "Exception in try"); } catch (IOException ex) { System.out.println(ex.getMessage() + "Exception in try"); } }
From source file:gov.nih.nci.ncicb.tcga.dcc.common.web.view.TCGAExcelSXSSView.java
protected void buildExcelDocument(final Map model, final SXSSFWorkbook workbook, final HttpServletRequest request, final HttpServletResponse response) throws Exception { //Set up/*from w w w. jav a2 s. co m*/ final String fileName = (String) model.get(ATTRIBUTE_FILE_NAME); final String title = (String) model.get(ATTRIBUTE_TITLE); final Map<String, String> columns = (Map<String, String>) model.get(ATTRIBUTE_COLUMN_HEADERS); final List<Object> data = (List<Object>) model.get(ATTRIBUTE_DATA); final DateFormat dateFormat = (DateFormat) model.get(ATTRIBUTE_DATE_FORMAT); response.setHeader("Content-Disposition", "attachment; filename=" + fileName); final Sheet sheet = workbook.createSheet(title); //Writing the columns headers int i = 0; final Row columnNameRow = sheet.createRow(0); for (Map.Entry<String, String> e : columns.entrySet()) { final Cell cell = columnNameRow.createCell(i); cell.setCellValue(e.getValue()); i++; } //Writing the data if (data != null) { for (int j = 0; j < data.size(); j++) { final Row row = sheet.createRow(j + 1); int k = 0; for (Map.Entry<String, String> e : columns.entrySet()) { final Object obj = data.get((j)); final Object o = BeanToTextExporter.getAndInvokeGetter(obj, e.getKey()); final String value = getExportString(o, dateFormat); final Cell cell = row.createCell(k); cell.setCellValue(value); k++; } } } }
From source file:it.isislab.sof.client.application.ui.Controller.java
public void getresultExcel(String... params) { if (params == null) { // c.printf("Error few parameters!\n Usage: getresult simID [destinationDirPath]"); } else {/*from w w w. ja v a 2 s.co m*/ Simulations sims = SofManager.getSimulationsData(session); if (sims == null) { // c.printf("No such simulation"); } Simulation sim = null; try { for (Simulation s : sims.getSimulations()) if (s.getId().equals(params[0])) { sim = s; break; } } catch (IndexOutOfBoundsException e) { // c.printf("No such simulation"); } //if no path is specified, saves in current directory String path = (params.length < 2) ? System.getProperty("user.dir") : params[1]; path += File.separator + "SIM-" + sim.getId() + ".xls"; SXSSFWorkbook workbook = new SXSSFWorkbook(); Sheet sheet = workbook.createSheet("Simulation ID " + sim.getId()); int row_num = 0; if (sim.getLoop()) { List<Loop> loops = sim.getRuns().getLoops(); Collections.sort(loops, new Comparator<Loop>() { @Override public int compare(Loop o1, Loop o2) { return Integer.compare(o1.getId(), o2.getId()); } }); for (Loop l : loops) { Row row_loop = sheet.createRow(++row_num); Cell c_loop_id = row_loop.createCell(0); c_loop_id.setCellValue("Loop ID " + l.getId()); class PointTree { public Input getI() { return i; } public void setI(Input i) { this.i = i; } public Output getO() { return o; } public void setO(Output o) { this.o = o; } private Input i; private Output o; } HashMap<Integer, PointTree> mapio = new HashMap<Integer, PointTree>(); if (l.getInputs() != null) { for (Input i : l.getInputs().getinput_list()) { PointTree p = new PointTree(); p.setI(i); mapio.put(i.id, p); } if (l.getOutputs() != null && l.getOutputs().getOutput_list() != null) for (Output i : l.getOutputs().getOutput_list()) { mapio.get(i.getIdInput()).setO(i); } else { System.out.println("No output found."); } for (Integer pt : mapio.keySet()) { Row row_input_id = sheet.createRow(++row_num); Cell c_input_id = row_input_id.createCell(1); c_input_id.setCellValue("Input ID " + pt); Row row_input_names = sheet.createRow(++row_num); Row row_input_values = sheet.createRow(++row_num); Row row_output_id = sheet.createRow(++row_num); Cell c_output_id = row_output_id.createCell(1); c_output_id.setCellValue("Output ID " + pt); Row row_output_names = sheet.createRow(++row_num); Row row_output_values = sheet.createRow(++row_num); int cell_input = 1, cell_output = 1; for (Parameter p : mapio.get(pt).getI().param_element) { Cell c_input_name = row_input_names.createCell(cell_input); Cell c_input_value = row_input_values.createCell(cell_input); cell_input++; c_input_name.setCellValue(p.getvariable_name()); if (p.getparam() instanceof ParameterDouble) c_input_value.setCellValue(((ParameterDouble) p.getparam()).getvalue()); else if (p.getparam() instanceof ParameterString) c_input_value.setCellValue(((ParameterString) p.getparam()).getvalue()); else if (p.getparam() instanceof ParameterLong) c_input_value.setCellValue(((ParameterLong) p.getparam()).getvalue()); } if (mapio.get(pt).getO() != null) for (Parameter p : mapio.get(pt).getO().output_params) { Cell c_output_name = row_output_names.createCell(cell_output); Cell c_output_value = row_output_values.createCell(cell_output); cell_output++; c_output_name.setCellValue(p.getvariable_name()); if (p.getparam() instanceof ParameterDouble) c_output_value.setCellValue(((ParameterDouble) p.getparam()).getvalue()); else if (p.getparam() instanceof ParameterString) c_output_value.setCellValue(((ParameterString) p.getparam()).getvalue()); else if (p.getparam() instanceof ParameterLong) c_output_value.setCellValue(((ParameterLong) p.getparam()).getvalue()); } } } } } try { FileOutputStream out = new FileOutputStream(new File(path)); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } }
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)./* ww w. j a v a2s. 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 w w.j av 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;//from ww w .j a v a 2 s . co m 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:org.apache.tika.eval.reports.Report.java
License:Apache License
private void dumpReportToWorkbook(Statement st, SXSSFWorkbook wb) throws IOException, SQLException { ResultSet rs = st.executeQuery(sql); SXSSFSheet sheet = wb.createSheet("tika-eval Report"); sheet.trackColumnForAutoSizing(0);//w ww . j a v a 2 s. c o m int rowCount = 0; ResultSetMetaData meta = rs.getMetaData(); Set<String> colNames = new HashSet<>(); Row xssfRow = sheet.createRow(rowCount++); //write headers and cache them to check against styles for (int i = 1; i <= meta.getColumnCount(); i++) { Cell cell = xssfRow.createCell(i - 1); cell.setCellValue(meta.getColumnLabel(i)); colNames.add(meta.getColumnLabel(i)); } ResultSetMetaData resultSetMetaData = rs.getMetaData(); while (rs.next()) { xssfRow = sheet.createRow(rowCount++); for (int i = 1; i <= meta.getColumnCount(); i++) { Cell cell = xssfRow.createCell(i - 1); XSLXCellFormatter formatter = cellFormatters.get(meta.getColumnLabel(i)); if (formatter == null) { formatter = getDefaultFormatter(resultSetMetaData.getColumnType(i)); } if (formatter != null) { formatter.applyStyleAndValue(i, rs, cell); } else { writeCell(meta, i, rs, cell); } } } sheet.autoSizeColumn(0); if (!includeSql) { return; } SXSSFSheet sqlSheet = wb.createSheet("tika-eval SQL"); sqlSheet.setColumnWidth(0, 100 * 250); Row sqlRow = sqlSheet.createRow(0); short height = 5000; sqlRow.setHeight(height); Cell cell = sqlRow.createCell(0); cell.setCellStyle(sqlCellStyle); cell.setCellValue(sql.trim());//.replaceAll("[\r\n]+", "\r\n")); }
From source file:org.dashbuilder.dataset.backend.DataSetBackendServicesImpl.java
License:Apache License
@Override public String exportDataSetExcel(DataSet dataSet) { // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters if (dataSet == null) throw new IllegalArgumentException("Null dataSet specified!"); int columnCount = dataSet.getColumns().size(); int rowCount = dataSet.getRowCount() + 1; //Include header row; int row = 0;// w w w .j ava 2 s. c om SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk Map<String, CellStyle> styles = createStyles(wb); Sheet sh = wb.createSheet("Sheet 1"); // General setup sh.setDisplayGridlines(true); sh.setPrintGridlines(false); sh.setFitToPage(true); sh.setHorizontallyCenter(true); PrintSetup printSetup = sh.getPrintSetup(); printSetup.setLandscape(true); // Create header Row header = sh.createRow(row++); header.setHeightInPoints(20f); for (int i = 0; i < columnCount; i++) { Cell cell = header.createCell(i); cell.setCellStyle(styles.get("header")); cell.setCellValue(dataSet.getColumnByIndex(i).getId()); } // Create data rows for (; row < rowCount; row++) { Row _row = sh.createRow(row); for (int cellnum = 0; cellnum < columnCount; cellnum++) { Cell cell = _row.createCell(cellnum); Object value = dataSet.getValueAt(row - 1, cellnum); if (value instanceof Short || value instanceof Long || value instanceof Integer || value instanceof BigInteger) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(styles.get("integer_number_cell")); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(styles.get("decimal_number_cell")); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("date_cell")); cell.setCellValue((Date) value); } else if (value instanceof Interval) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(((Interval) value).getName()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(value.toString()); } } } // Adjust column size for (int i = 0; i < columnCount; i++) { sh.autoSizeColumn(i); } Path tempExcelFilePath = null; try { tempExcelFilePath = ioService.createTempFile("export", "xlsx", null); OutputStream os = Files.newOutputStream(tempExcelFilePath); wb.write(os); os.flush(); os.close(); } catch (Exception e) { log.error("Error in excel export: ", e); } // Dispose of temporary files backing this workbook on disk if (!wb.dispose()) log.warn("Could not dispose of temporary file associated to data export!"); return tempExcelFilePath.toString(); }