List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook createSheet
@Override
public SXSSFSheet createSheet()
From source file:adams.data.io.output.ExcelStreamingSpreadSheetWriter.java
License:Open Source License
/** * Performs the actual writing. The caller must ensure that the writer gets * closed.// ww w . jav a 2 s.co m * * @param content the spreadsheet to write * @param out the writer to write the spreadsheet to * @return true if successfully written */ @Override protected boolean doWrite(SpreadSheet[] content, OutputStream out) { boolean result; SXSSFWorkbook workbook; Sheet sheet; Row row; adams.data.spreadsheet.Row spRow; adams.data.spreadsheet.Cell spCell; Cell cell; int i; int n; int count; CellStyle styleDate; CellStyle styleDateTime; CellStyle styleTime; HashSet<String> names; String name; result = true; try { workbook = new SXSSFWorkbook(m_MaxRows); styleDate = ExcelHelper.getDateCellStyle(workbook, Constants.DATE_FORMAT); styleDateTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIMESTAMP_FORMAT); styleTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIME_FORMAT); count = 0; names = new HashSet<>(); for (SpreadSheet cont : content) { if (m_Stopped) return false; sheet = workbook.createSheet(); if (cont.getName() != null) { name = cont.getName().replace("'", ""); if (names.contains(name)) name += (count + 1); } else { name = m_SheetPrefix + (count + 1); } names.add(name); workbook.setSheetName(count, name); // header row = sheet.createRow(0); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); cell.setCellValue(cont.getHeaderRow().getCell(i).getContent()); } // data for (n = 0; n < cont.getRowCount(); n++) { if (m_Stopped) return false; row = sheet.createRow(n + 1); spRow = cont.getRow(n); for (i = 0; i < cont.getColumnCount(); i++) { cell = row.createCell(i); spCell = spRow.getCell(i); if ((spCell == null) || spCell.isMissing()) { if (m_MissingValue.length() > 0) cell.setCellValue(m_MissingValue); else cell.setCellType(Cell.CELL_TYPE_BLANK); continue; } if (spCell.isFormula() && !m_OutputAsDisplayed) { cell.setCellFormula(spCell.getFormula().substring(1)); } else { if (spCell.isDate()) { cell.setCellValue(spCell.toDate()); cell.setCellStyle(styleDate); } else if (spCell.isTime()) { cell.setCellValue(spCell.toTime()); cell.setCellStyle(styleTime); } else if (spCell.isDateTime()) { cell.setCellValue(spCell.toDateTime()); cell.setCellStyle(styleDateTime); } else if (spCell.isNumeric()) { cell.setCellValue(Utils.toDouble(spCell.getContent())); } else { cell.setCellValue(spCell.getContent()); } } } } // next sheet count++; } // save workbook.write(out); } catch (Exception e) { result = false; getLogger().log(Level.SEVERE, "Failed writing spreadsheet data", e); } return result; }
From source file:com.catdog.common.util.ExcelReadAndWrite.java
License:Apache License
public void testWorkBook() { try {/*from w w w . j a va 2 s .c o m*/ long curr_time = System.currentTimeMillis(); int rowaccess = 100;// /*keep 100 rowsin memory,exceeding rows will be flushed to disk*/ SXSSFWorkbook wb = new SXSSFWorkbook(rowaccess); int sheet_num = 3;//?3SHEET for (int i = 0; i < sheet_num; i++) { Sheet sh = wb.createSheet(); //?SHEET60000ROW for (int rownum = 0; rownum < 60000; rownum++) { Row row = sh.createRow(rownum); //?10CELL for (int cellnum = 0; cellnum < 10; cellnum++) { Cell cell = row.createCell(cellnum); String address = new CellReference(cell).formatAsString(); cell.setCellValue(address); } //??,? if (rownum % rowaccess == 0) { ((SXSSFSheet) sh).flushRows(); } } } /*?*/ FileOutputStream os = new FileOutputStream("d:/biggrid.xlsx"); wb.write(os); os.close(); /**/ System.out.println(":" + (System.currentTimeMillis() - curr_time) / 1000); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.daphne.es.showcase.excel.service.ExcelDataService.java
License:Apache License
/** * ???// w w w . j a va 2s.c om * excel 2007 ?sheet1048576 * @param user * @param contextRootPath * @param searchable */ @Async public void exportExcel2007(final User user, final String contextRootPath, final Searchable searchable) { int rowAccessWindowSize = 1000; //??? int perSheetRows = 100000; //?sheet 10w? int totalRows = 0; // Long maxId = 0L;//??id String fileName = generateFilename(user, contextRootPath, "xlsx"); File file = new File(fileName); BufferedOutputStream out = null; SXSSFWorkbook wb = null; try { long beginTime = System.currentTimeMillis(); wb = new SXSSFWorkbook(rowAccessWindowSize); wb.setCompressTempFiles(true);//?gzip while (true) { Sheet sheet = wb.createSheet(); Row headerRow = sheet.createRow(0); Cell idHeaderCell = headerRow.createCell(0); idHeaderCell.setCellValue("?"); Cell contentHeaderCell = headerRow.createCell(1); contentHeaderCell.setCellValue(""); totalRows = 1; Page<ExcelData> page = null; do { searchable.setPage(0, pageSize); // if (!searchable.containsSearchKey("id_in")) { searchable.addSearchFilter("id", SearchOperator.gt, maxId); } page = findAll(searchable); for (ExcelData data : page.getContent()) { Row row = sheet.createRow(totalRows); Cell idCell = row.createCell(0); idCell.setCellValue(data.getId()); Cell contentCell = row.createCell(1); contentCell.setCellValue(data.getContent()); maxId = Math.max(maxId, data.getId()); totalRows++; } //clear entity manager RepositoryHelper.clear(); } while (page.hasNext() && totalRows <= perSheetRows); if (!page.hasNext()) { break; } } out = new BufferedOutputStream(new FileOutputStream(file)); wb.write(out); IOUtils.closeQuietly(out); if (needCompress(file)) { fileName = compressAndDeleteOriginal(fileName); } long endTime = System.currentTimeMillis(); Map<String, Object> context = Maps.newHashMap(); context.put("seconds", (endTime - beginTime) / 1000); context.put("url", fileName.replace(contextRootPath, "")); notificationApi.notify(user.getId(), "excelExportSuccess", context); } catch (Exception e) { IOUtils.closeQuietly(out); log.error("excel export error", e); Map<String, Object> context = Maps.newHashMap(); context.put("error", e.getMessage()); notificationApi.notify(user.getId(), "excelExportError", context); } finally { // ? wb.dispose(); } }
From source file:com.luna.showcase.excel.service.ExcelDataService.java
License:Apache License
/** * ???/*from w w w. ja v a 2s .c o m*/ * excel 2007 ?sheet1048576 * @param user * @param contextRootPath * @param searchable */ @Async public void exportExcel2007(final User user, final String contextRootPath, final Searchable searchable) { int rowAccessWindowSize = 1000; //??? int perSheetRows = 100000; //?sheet 10w? int totalRows = 0; // Long maxId = 0L;//??id String fileName = generateFilename(user, contextRootPath, "xlsx"); File file = new File(fileName); BufferedOutputStream out = null; SXSSFWorkbook wb = null; try { long beginTime = System.currentTimeMillis(); wb = new SXSSFWorkbook(rowAccessWindowSize); wb.setCompressTempFiles(true);//?gzip while (true) { Sheet sheet = wb.createSheet(); Row headerRow = sheet.createRow(0); Cell idHeaderCell = headerRow.createCell(0); idHeaderCell.setCellValue("?"); Cell contentHeaderCell = headerRow.createCell(1); contentHeaderCell.setCellValue(""); totalRows = 1; Page<ExcelData> page = null; do { searchable.setPage(0, pageSize); // if (!searchable.containsSearchKey("id_in")) { searchable.addSearchFilter("id", SearchOperator.gt, maxId); } page = findAll(searchable); for (ExcelData data : page.getContent()) { Row row = sheet.createRow(totalRows); Cell idCell = row.createCell(0); idCell.setCellValue(data.getId()); Cell contentCell = row.createCell(1); contentCell.setCellValue(data.getContent()); maxId = Math.max(maxId, data.getId()); totalRows++; } //clear entity manager RepositoryHelper.clear(); } while (page.hasNextPage() && totalRows <= perSheetRows); if (!page.hasNextPage()) { break; } } out = new BufferedOutputStream(new FileOutputStream(file)); wb.write(out); IOUtils.closeQuietly(out); if (needCompress(file)) { fileName = compressAndDeleteOriginal(fileName); } long endTime = System.currentTimeMillis(); Map<String, Object> context = Maps.newHashMap(); context.put("seconds", (endTime - beginTime) / 1000); context.put("url", fileName.replace(contextRootPath, "")); notificationApi.notify(user.getId(), "excelExportSuccess", context); } catch (Exception e) { IOUtils.closeQuietly(out); log.error("excel export error", e); Map<String, Object> context = Maps.newHashMap(); context.put("error", e.getMessage()); notificationApi.notify(user.getId(), "excelExportError", context); } finally { // ? wb.dispose(); } }
From source file:com.repository2excel.Main.java
License:Apache License
/** * @param args// w ww . ja v a 2s . c o m */ @SuppressWarnings("deprecation") public static void main(String[] args) { String xmlRepositoryDefFilePath = ""; /** Read user input */ Scanner scnr = new Scanner(System.in); System.out.println("Enter fully qualified path to customCatalog.xml:"); try { xmlRepositoryDefFilePath = scnr.next(); } catch (InputMismatchException e) { // TODO: } finally { scnr.close(); } RepositoryDefinitionReader reader = new RepositoryDefinitionReader(); System.out.println("Begin reading XML Repository definition file..."); HashSet<Item> items = reader.loadRepositoryDefinition(new File(xmlRepositoryDefFilePath)); System.out.println("Finished reading XML file!"); if (items != null && items.size() > 0) { System.out.println("Preparing to export " + items.size() + " items into Excel Spreadsheet..."); SXSSFWorkbook wb = new SXSSFWorkbook(100); Sheet sh = wb.createSheet(); /** Create cell styles */ CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); Iterator<Item> iter = items.iterator(); int rownum = 0; while (iter.hasNext()) { Item item = iter.next(); Row row = sh.createRow(rownum); row.createCell(0, CellType.STRING).setCellValue("Item"); row.createCell(1, CellType.STRING).setCellValue(item.getName()); rownum++; row = sh.createRow(rownum); row.createCell(0, CellType.STRING).setCellValue("Query Cache Size"); row.createCell(1, CellType.STRING).setCellValue(item.getQueryCacheSize()); rownum++; row = sh.createRow(rownum); row.createCell(0, CellType.STRING).setCellValue("Item Cache Size"); row.createCell(1, CellType.STRING).setCellValue(item.getItemCacheSize()); rownum++; HashSet<Property> properties = item.getProperties(); if (properties != null && properties.size() > 0) { Cell cell; row = sh.createRow(rownum); cell = row.createCell(0, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Property"); cell = row.createCell(1, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Type"); cell = row.createCell(2, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Readable"); cell = row.createCell(3, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Writable"); cell = row.createCell(4, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Hidden"); cell = row.createCell(5, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Table"); cell = row.createCell(6, CellType.STRING); cell.setCellStyle(style); cell.setCellValue("Column"); Iterator<Property> pIter = properties.iterator(); while (pIter.hasNext()) { rownum++; row = sh.createRow(rownum); Property property = pIter.next(); /** 0. Name */ cell = row.createCell(0, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.getName()); /** 1. Data Type */ cell = row.createCell(1, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.getDataType()); /** 2. Is Readable */ cell = row.createCell(2, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.isReadable()); /** 3. Is Writable */ cell = row.createCell(3, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.isWriteable()); /** 4. Is Hidden */ cell = row.createCell(4, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.isHidden()); /** 5. Table */ cell = row.createCell(5, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.getTable()); /** 6. Column */ cell = row.createCell(6, CellType.STRING); cell.setCellStyle(style); cell.setCellValue(property.getColumn()); } } rownum++; rownum++; } try { File f = new File("test.xlsx"); FileOutputStream out = new FileOutputStream(f); wb.write(out); out.close(); wb.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { // dispose of temporary files backing this workbook on disk wb.dispose(); } } }
From source file:com.sitech.chn.s98800.s98820.s882q.util.XLSXCovertCSVReader.java
License:Apache License
public static void writeToXlSX(HttpServletResponse response, List data, String FileName) throws Exception { String filePath = ServletActionContext.getServletContext().getRealPath("/upload") + "/" + FileName; SXSSFWorkbook wb = new SXSSFWorkbook(10000); File exprotFile = new File(filePath); if (exprotFile.exists()) { exprotFile.delete();//from w ww . j a v a 2 s. com exprotFile = new File(filePath); } System.out.println("" + new Date()); FileOutputStream fos = new FileOutputStream(exprotFile); Sheet sh = wb.createSheet(); String[] temp = null; for (int i = 0; i < data.size(); i++) { temp = (String[]) data.get(i); Row row = sh.createRow(i); for (int j = 0; j < temp.length; j++) { Cell cell = row.createCell(j); cell.setCellValue(temp[j] == null ? "" : temp[j].replaceAll("\"", "") + " "); } } wb.write(fos); wb.close();// fos.close(); System.out.println("" + new Date() + exprotFile.getAbsolutePath()); InputStream inStream = new BufferedInputStream(new FileInputStream(exprotFile)); response.reset(); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode(FileName, "UTF-8")); response.addHeader("Content-Length", exprotFile.length() + ""); byte[] b = new byte[1024 * 10]; int len; while ((len = inStream.read(b)) > 0) { response.getOutputStream().write(b, 0, len); } inStream.close(); }