List of usage examples for org.apache.poi.xssf.streaming SXSSFSheet createRow
@Override public SXSSFRow createRow(int rownum)
From source file:com.plugin.excel.util.ExcelFileHelper.java
License:Apache License
public static void writeFile(String directory, String fileName, Map<String, List<List<ExcelCell>>> sheets, int headerRowHeight, int commentRowHeight) { if (StringUtils.isNotBlank(directory) && StringUtils.isNotBlank(fileName) && sheets != null && !sheets.isEmpty()) { SXSSFWorkbook workbook = new SXSSFWorkbook(); Font invisibleFont = workbook.createFont(); for (Entry<String, List<List<ExcelCell>>> entry : sheets.entrySet()) { // TODO: remove and logging // log.info("writeFile","Started writing sheet: "+entry.getKey()); SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(entry.getKey()); int totalColumn = 0; if (entry.getValue() != null && !entry.getValue().isEmpty()) { int rowNumber = 0; Font dataFont = null; for (List<ExcelCell> rows : entry.getValue()) { // Row row = sheet.getRow(rowNumber)!=null ? sheet.getRow(rowNumber) : rowMap.get(rowNumber); Row row = sheet.createRow(rowNumber); int rowHeight = rowNumber == 0 ? headerRowHeight : commentRowHeight; if (rowNumber == 0 || rowNumber == 1) { if (rowHeight > 0) { row.setHeight((short) rowHeight); }//from w w w . java 2 s . co m addDataValidation(rowNumber, sheet); } rowNumber++; if (rows != null && !rows.isEmpty()) { int cellNum = 0; Font font = null; if (rowNumber > 3 && dataFont != null) { font = dataFont; } else { font = workbook.createFont(); dataFont = font; } // as each row requires different syle with separate font Map<IndexedColors, CellStyle> s_cellStyle = new HashMap<IndexedColors, CellStyle>(); for (ExcelCell cellValue : rows) { Cell cell = row.createCell(cellNum); updateCell(cell, cellValue, s_cellStyle, workbook, font, invisibleFont); ++cellNum; } totalColumn = cellNum; } if (rowNumber == 2) {/* * auto size after DOCUMENTATION-ROW (row=2) so, we don't have to do * multiple times */ autoSize(sheet, totalColumn, false); // rowMap = createRows(workbook, sheet, rowNumber+1, excelConfig.getMaxInputRows()); } } } autoSize(sheet, totalColumn, true); } // addMetaSheet(workbook); writeWorkBook(directory, fileName, workbook); } }
From source file:com.plugin.excel.util.ExcelUtil.java
License:Apache License
/** * @param newSheet/*from ww w.ja va 2 s .co m*/ * the sheet to create from the copy. * @param sheet * the sheet to copy. * @param copyStyle * true copy the style. */ public static void copySheets(SXSSFSheet newSheet, SXSSFSheet sheet, boolean copyStyle) { int maxColumnNum = 0; Map<Integer, CellStyle> styleMap = (copyStyle) ? new HashMap<Integer, CellStyle>() : null; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { Row srcRow = sheet.getRow(i); Row destRow = newSheet.createRow(i); if (srcRow != null) { ExcelUtil.copyRow(sheet, newSheet, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
From source file:eu.alpinweiss.filegen.util.processor.SheetProcessor.java
License:Apache License
public void generateSheetData(ParameterVault parameterVault, CellStyle cs, SXSSFSheet sheet, int columnCount, Map<Integer, Input2TableInfo> hashMap) { Cell cell;// Row and column indexes int idx = 0;//from w ww . ja v a 2 s. c o m int idy = 0; // Generate column headings Row row = sheet.createRow(idx); Input2TableInfo input2TableInfo; for (Integer key : hashMap.keySet()) { input2TableInfo = hashMap.get(key); cell = row.createCell(idy); cell.setCellValue(input2TableInfo.getFieldText()); cell.setCellStyle(cs); sheet.setColumnWidth(idy, (input2TableInfo.getFieldText().trim().length() * 500)); idy++; } ThreadLocalRandom randomGenerator = ThreadLocalRandom.current(); for (int i = 1; i < parameterVault.rowCount() + 1; i++) { row = sheet.createRow(i); if (i != 0 && i % 10000 == 0) { outputWriterHolder.writeValueInLine(Thread.currentThread().getName() + " Processed " + i + " rows"); } for (int colCount = 0; colCount < columnCount; colCount++) { final Cell dataCell = row.createCell(colCount); input2TableInfo = hashMap.get(colCount); final CellStyle cellStyle = input2TableInfo.getCellStyle(); input2TableInfo.generator().generate(parameterVault.setIterationNumber(i), randomGenerator, new ValueVault() { @Override public void storeValue(DataWrapper wrapper) { FieldType fieldType = wrapper.getFieldType(); switch (fieldType) { case DATE: dataCell.setCellValue(wrapper.getDateValue()); dataCell.setCellStyle(cellStyle); break; case FLOAT: case INTEGER: dataCell.setCellType(Cell.CELL_TYPE_NUMERIC); dataCell.setCellValue(wrapper.getNumberValue()); break; case AUTONUMBER: dataCell.setCellType(Cell.CELL_TYPE_NUMERIC); dataCell.setCellValue(wrapper.getNumberValue()); case SEQUENCE: default: dataCell.setCellValue(wrapper.getStringValue()); } } }); } } }
From source file:excelmasivo.ExcelMasivo.java
/** * @param args the command line arguments *//*from ww w .j a v a 2s.co m*/ 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 w ww .j ava 2s. 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) {//from w w w . j a v a 2 s.c om 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:org.apache.metamodel.excel.ExcelUpdateCallbackTest.java
License:Apache License
public void testStreamingAPI() throws Exception { File file = new File("target/streaming-api-test.xlsx"); if (file.exists()) { file.delete();//from ww w . j av a 2 s .c o m } assertFalse(file.exists()); // write using streaming writer { ExcelDataContext dc = new ExcelDataContext(file); ExcelUpdateCallback callback = new ExcelUpdateCallback(dc); SXSSFSheet sheet = (SXSSFSheet) callback.createSheet("foobar"); Field windowSizeField = SXSSFSheet.class.getDeclaredField("_randomAccessWindowSize"); windowSizeField.setAccessible(true); int windowSize = windowSizeField.getInt(sheet); assertEquals(1000, windowSize); Field rowsField = SXSSFSheet.class.getDeclaredField("_rows"); rowsField.setAccessible(true); @SuppressWarnings("unchecked") Map<Integer, SXSSFRow> rows = (Map<Integer, SXSSFRow>) rowsField.get(sheet); assertEquals(0, rows.size()); // create 5x the amound of rows as the streaming sheet will hold in // memory for (int i = 0; i < windowSize * 5; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell(0); cell.setCellValue("value" + i); assertTrue(rows.size() <= 1000); } assertEquals(1000, rows.size()); ExcelUtils.writeAndCloseWorkbook(dc, sheet.getWorkbook()); } assertTrue("Usually the file size will be circa 42000, but it was: " + file.length(), file.length() > 40000 && file.length() < 45000); // read to check results { ExcelDataContext dc = new ExcelDataContext(file); assertEquals("[foobar]", Arrays.toString(dc.getDefaultSchema().getTableNames())); Table table = dc.getDefaultSchema().getTableByName("foobar"); assertEquals("[value0]", Arrays.toString(table.getColumnNames())); DataSet ds = dc.query().from(table).select("value0").execute(); int recordNo = 1; while (ds.next()) { assertEquals("value" + recordNo, ds.getRow().getValue(0)); recordNo++; } assertEquals(5000, recordNo); } }
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.jav a 2 s .co 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.service.DataSetExportServicesImpl.java
License:Apache License
SXSSFWorkbook dataSetToWorkbook(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!");
}/* ww w.j av a 2 s .c o m*/
int columnCount = dataSet.getColumns().size();
int rowCount = dataSet.getRowCount() + 1; //Include header row;
int row = 0;
SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
Map<String, CellStyle> styles = createStyles(wb);
SXSSFSheet sh = wb.createSheet("Sheet 1");
// General setup
sh.setDisplayGridlines(true);
sh.setPrintGridlines(false);
sh.setFitToPage(true);
sh.setHorizontallyCenter(true);
sh.trackAllColumnsForAutoSizing();
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(CellType.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(CellType.NUMERIC);
cell.setCellStyle(styles.get("decimal_number_cell"));
cell.setCellValue(((Number) value).doubleValue());
} else if (value instanceof Date) {
cell.setCellType(CellType.STRING);
cell.setCellStyle(styles.get("date_cell"));
cell.setCellValue((Date) value);
} else if (value instanceof Interval) {
cell.setCellType(CellType.STRING);
cell.setCellStyle(styles.get(TEXT_CELL));
cell.setCellValue(((Interval) value).getName());
} else {
cell.setCellType(CellType.STRING);
cell.setCellStyle(styles.get(TEXT_CELL));
String val = value == null ? "" : value.toString();
cell.setCellValue(val);
}
}
}
// Adjust column size
for (int i = 0; i < columnCount; i++) {
sh.autoSizeColumn(i);
}
return wb;
}
From source file:org.eclipse.sw360.exporter.ExcelExporter.java
License:Open Source License
public InputStream makeExcelExport(List<T> documents) throws IOException, SW360Exception { final SXSSFWorkbook workbook = new SXSSFWorkbook(); final ByteArrayInputStream stream; try {/*from ww w .j a va 2 s. c o m*/ SXSSFSheet sheet = workbook.createSheet("Data"); /** Adding styles to cells */ CellStyle cellStyle = createCellStyle(workbook); CellStyle headerStyle = createHeaderStyle(workbook); /** Create header row */ Row headerRow = sheet.createRow(0); List<String> headerNames = helper.getHeaders(); fillRow(headerRow, headerNames, headerStyle); /** Create data rows */ fillValues(sheet, documents, cellStyle); // removed autosizing of spreadsheet columns for performance reasons /** Copy the streams */ final ByteArrayOutputStream out = new ByteArrayOutputStream(); workbook.write(out); stream = new ByteArrayInputStream(out.toByteArray()); } finally { workbook.dispose(); } return stream; }