List of usage examples for org.apache.poi.ss.usermodel Workbook write
void write(OutputStream stream) throws IOException;
From source file:com.qihang.winter.poi.excel.view.JeecgSingleExcelView.java
License:Apache License
@Override protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception { String codedFileName = ""; Workbook workbook = null; if (model.containsKey(NormalExcelConstants.MAP_LIST)) { List<Map<String, Object>> list = (List<Map<String, Object>>) model.get(NormalExcelConstants.MAP_LIST); if (list.size() == 0) { throw new RuntimeException("MAP_LIST IS NULL"); }/*from w w w . j ava2 s . c o m*/ workbook = ExcelExportUtil.exportExcel((ExportParams) list.get(0).get(NormalExcelConstants.PARAMS), (Class<?>) list.get(0).get(NormalExcelConstants.CLASS), (Collection<?>) list.get(0).get(NormalExcelConstants.DATA_LIST)); for (int i = 1; i < list.size(); i++) { new com.qihang.winter.poi.excel.export.ExcelExportServer().createSheet(workbook, (ExportParams) list.get(i).get(NormalExcelConstants.PARAMS), (Class<?>) list.get(i).get(NormalExcelConstants.CLASS), (Collection<?>) list.get(i).get(NormalExcelConstants.DATA_LIST)); } } else { workbook = ExcelExportUtil.exportExcel((ExportParams) model.get(NormalExcelConstants.PARAMS), (Class<?>) model.get(NormalExcelConstants.CLASS), (Collection<?>) model.get(NormalExcelConstants.DATA_LIST)); } if (model.containsKey(NormalExcelConstants.FILE_NAME)) { codedFileName = (String) model.get(NormalExcelConstants.FILE_NAME); } if (workbook instanceof HSSFWorkbook) { codedFileName += HSSF; } else { codedFileName += XSSF; } if (isIE(request)) { codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF8"); } else { codedFileName = new String(codedFileName.getBytes("UTF-8"), "ISO-8859-1"); } response.setHeader("content-disposition", "attachment;filename=" + codedFileName); ServletOutputStream out = response.getOutputStream(); workbook.write(out); out.flush(); }
From source file:com.qihang.winter.poi.excel.view.JeecgTemplateExcelView.java
License:Apache License
@Override protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception { String codedFileName = ""; Workbook workbook = ExcelExportUtil.exportExcel( (com.qihang.winter.poi.excel.entity.TemplateExportParams) model.get(TemplateExcelConstants.PARAMS), (Class<?>) model.get(TemplateExcelConstants.CLASS), (List<?>) model.get(TemplateExcelConstants.LIST_DATA), (Map<String, Object>) model.get(TemplateExcelConstants.MAP_DATA)); if (model.containsKey(NormalExcelConstants.FILE_NAME)) { codedFileName = (String) model.get(NormalExcelConstants.FILE_NAME); }/* w w w .j av a 2 s . co m*/ if (workbook instanceof HSSFWorkbook) { codedFileName += HSSF; } else { codedFileName += XSSF; } if (isIE(request)) { codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF8"); } else { codedFileName = new String(codedFileName.getBytes("UTF-8"), "ISO-8859-1"); } response.setHeader("content-disposition", "attachment;filename=" + codedFileName); ServletOutputStream out = response.getOutputStream(); workbook.write(out); out.flush(); }
From source file:com.qualogy.qafe.service.DocumentExporter.java
License:Apache License
public static void exportExcel(DocumentExportInput input, ByteArrayOutputStream bout) { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Exported data"); if (input.getData() != null) { if (bout != null) { short index = 0; List<String> headerColumns = input.getColumnSequence(); if (headerColumns == null) { headerColumns = getHeaderColumns(input.getData()); }/* w w w .ja v a2s . c o m*/ if (headerColumns != null) { if (input.isGenerateColumnHeader()) { Row row = sheet.createRow(index++); for (int i = 0; i < headerColumns.size(); i++) { String columnHeader = headerColumns.get(i); Cell cell = row.createCell(i); cell.setCellValue(columnHeader); } } for (Map<String, Object> map : input.getData()) { Row row = sheet.createRow(index++); for (int i = 0; i < headerColumns.size(); i++) { String key = headerColumns.get(i); Object value = map.get(key); Cell cell = row.createCell(i); setCellValue(cell, value); } } } } } try { wb.write(bout); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:com.quanticate.opensource.datalistdownload.DeclarativeSpreadsheetWebScript.java
License:Open Source License
/** * Generates the spreadsheet, based on the properties in the header * and a callback for the body./*from w w w . j av a 2s . c o m*/ */ public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status, Map<String, Object> model) throws IOException { Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)"); // Build up the details of the header List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req); String[] headings = new String[propertyDetails.size()]; String[] descriptions = new String[propertyDetails.size()]; boolean[] required = new boolean[propertyDetails.size()]; for (int i = 0; i < headings.length; i++) { Pair<QName, Boolean> property = propertyDetails.get(i); if (property == null || property.getFirst() == null) { headings[i] = ""; required[i] = false; } else { QName column = property.getFirst(); required[i] = property.getSecond(); // Ask the dictionary service nicely for the details PropertyDefinition pd = dictionaryService.getProperty(column); if (pd != null && pd.getTitle(dictionaryService) != null) { // Use the friendly titles, which may even be localised! headings[i] = pd.getTitle(dictionaryService); descriptions[i] = pd.getDescription(dictionaryService); } else { // Nothing friendly found, try to munge the raw qname into // something we can show to a user... String raw = column.getLocalName(); raw = raw.substring(0, 1).toUpperCase() + raw.substring(1); Matcher m = qnameMunger.matcher(raw); if (m.matches()) { headings[i] = m.group(1) + " " + m.group(2); } else { headings[i] = raw; } } } } // Build a list of just the properties List<QName> properties = new ArrayList<QName>(propertyDetails.size()); for (Pair<QName, Boolean> p : propertyDetails) { QName qn = null; if (p != null) { qn = p.getFirst(); } properties.add(qn); } // Output if ("csv".equals(format)) { StringWriter sw = new StringWriter(); CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY); csv.println(headings); populateBody(resource, csv, properties); model.put(MODEL_CSV, sw.toString()); } else if ("odf".equals(format) || "ods".equals(format)) { try { SpreadsheetDocument odf = SpreadsheetDocument.newSpreadsheetDocument(); // Add the header row Table sheet = odf.appendSheet("Export"); org.odftoolkit.simple.table.Row hr = sheet.appendRow(); // TODO // Have the contents populated // TODO // Save it for the template ByteArrayOutputStream baos = new ByteArrayOutputStream(); odf.save(baos); model.put(MODEL_ODF, baos.toByteArray()); } catch (Exception e) { throw new WebScriptException("Error creating ODF file", e); } } else { Workbook wb; if ("xlsx".equals(format)) { wb = new XSSFWorkbook(); // TODO Properties } else { wb = new HSSFWorkbook(); // TODO Properties } // Add our header row Sheet sheet = wb.createSheet("Export"); Row hr = sheet.createRow(0); sheet.createFreezePane(0, 1); Font fb = wb.createFont(); fb.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fi = wb.createFont(); fi.setBoldweight(Font.BOLDWEIGHT_BOLD); fi.setItalic(true); CellStyle csReq = wb.createCellStyle(); csReq.setFont(fb); CellStyle csOpt = wb.createCellStyle(); csOpt.setFont(fi); // Populate the header Drawing draw = null; for (int i = 0; i < headings.length; i++) { Cell c = hr.createCell(i); c.setCellValue(headings[i]); if (required[i]) { c.setCellStyle(csReq); } else { c.setCellStyle(csOpt); } if (headings[i].length() == 0) { sheet.setColumnWidth(i, 3 * 250); } else { sheet.setColumnWidth(i, 18 * 250); } if (descriptions[i] != null && descriptions[i].length() > 0) { // Add a description for it too if (draw == null) { draw = sheet.createDrawingPatriarch(); } ClientAnchor ca = wb.getCreationHelper().createClientAnchor(); ca.setCol1(c.getColumnIndex()); ca.setCol2(c.getColumnIndex() + 1); ca.setRow1(hr.getRowNum()); ca.setRow2(hr.getRowNum() + 2); Comment cmt = draw.createCellComment(ca); cmt.setAuthor(""); cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i])); cmt.setVisible(false); c.setCellComment(cmt); } } // Have the contents populated populateBody(resource, wb, sheet, properties); // Save it for the template ByteArrayOutputStream baos = new ByteArrayOutputStream(); wb.write(baos); model.put(MODEL_EXCEL, baos.toByteArray()); } }
From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java
License:Apache License
private void excerpt(Workbook wb, List<Sheet> sheetsToKeep, OutputStream output) throws IOException { // Make the requested sheets be read only Set<String> keepNames = new HashSet<String>(); for (Sheet s : sheetsToKeep) { keepNames.add(s.getSheetName()); for (Row r : s) { for (Cell c : r) { if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { switch (c.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: double vd = c.getNumericCellValue(); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellValue(vd); break; case Cell.CELL_TYPE_STRING: RichTextString vs = c.getRichStringCellValue(); c.setCellType(Cell.CELL_TYPE_STRING); c.setCellValue(vs); break; case Cell.CELL_TYPE_BOOLEAN: boolean vb = c.getBooleanCellValue(); c.setCellType(Cell.CELL_TYPE_BOOLEAN); c.setCellValue(vb); break; case Cell.CELL_TYPE_ERROR: c.setCellType(Cell.CELL_TYPE_BLANK); break; }/*from w w w . j a va 2 s . co m*/ } } } } // Remove all the other sheets // Note - work backwards! Avoids order changing under us for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) { String name = wb.getSheetName(i); if (!keepNames.contains(name)) { wb.removeSheetAt(i); } } // Save wb.write(output); }
From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java
License:Apache License
private void merge(Workbook excerptWB, Workbook fullWB, String[] sheetsToMerge, OutputStream output) throws IOException { // Identify the sheets in both workbooks List<Sheet> sourceSheets = identifySheets(sheetsToMerge, excerptWB); List<Sheet> destSheets = identifySheets(sheetsToMerge, fullWB); // Process each sheet from the excerpt in turn for (int i = 0; i < sheetsToMerge.length; i++) { Sheet source = sourceSheets.get(i); Sheet dest = destSheets.get(i);//from ww w .j a v a2 s .com for (Row srcR : source) { for (Cell srcC : srcR) { if (srcC.getCellType() == Cell.CELL_TYPE_FORMULA || srcC.getCellType() == Cell.CELL_TYPE_ERROR) { // Don't merge these kinds of cells } else { Row destR = dest.getRow(srcR.getRowNum()); if (destR == null) { // Newly added row to the excerpt file, skip this } else { Cell destC = destR.getCell(srcC.getColumnIndex()); if (destC == null && srcC.getCellType() == Cell.CELL_TYPE_BLANK) { // Both are empty, don't need to do anything } else { if (destC == null) destC = destR.createCell(srcC.getColumnIndex(), srcC.getCellType()); // Sync contents if (srcC.getCellType() == Cell.CELL_TYPE_BLANK) { destC.setCellType(Cell.CELL_TYPE_BLANK); } else if (srcC.getCellType() == Cell.CELL_TYPE_BOOLEAN) { destC.setCellValue(srcC.getBooleanCellValue()); } else if (srcC.getCellType() == Cell.CELL_TYPE_NUMERIC) { destC.setCellValue(srcC.getNumericCellValue()); } else if (srcC.getCellType() == Cell.CELL_TYPE_STRING) { destC.setCellValue(srcC.getStringCellValue()); } // Sync formatting rules // TODO } } } } } } // Re-evaluate all the formulas in the destination workbook, now that // we have updated cells in it FormulaEvaluator eval = fullWB.getCreationHelper().createFormulaEvaluator(); eval.evaluateAll(); // Save the new file fullWB.write(output); }
From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java
License:Apache License
@Test public void sheetListing() throws Exception { String[] names = new String[] { "a", "b", "ccc", "dddd" }; for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) { for (String sn : names) { wb.createSheet(sn);//from w w w. j a va 2 s . c o m } File tmp = File.createTempFile("test", ".xls"); wb.write(new FileOutputStream(tmp)); String[] foundNames = excerpter.getSheetNames(tmp); assertEquals(names.length, foundNames.length); for (int i = 0; i < names.length; i++) { assertEquals(names[i], foundNames[i]); } } }
From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java
License:Apache License
@Test public void excerptGoesReadOnly() throws Exception { for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) { FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator(); Sheet s = wb.createSheet("Test"); // Numeric formulas Row r1 = s.createRow(0);/*from w w w . j a v a 2 s .c om*/ Cell c1 = r1.createCell(0); Cell c2 = r1.createCell(1); Cell c3 = r1.createCell(2); Cell c4 = r1.createCell(3); c1.setCellValue(1); c2.setCellValue(2); c3.setCellFormula("A1+B1"); c4.setCellFormula("(A1+B1)*B1"); // Strings, booleans and errors Row r2 = s.createRow(1); Cell c21 = r2.createCell(0); Cell c22 = r2.createCell(1); Cell c23 = r2.createCell(2); Cell c24 = r2.createCell(3); c21.setCellValue("Testing"); c22.setCellFormula("CONCATENATE(A2,A2)"); c23.setCellFormula("FALSE()"); c24.setCellFormula("A1/0"); // Ensure the formulas are current eval.evaluateAll(); // Run the excerpt File tmp = File.createTempFile("test", ".xls"); wb.write(new FileOutputStream(tmp)); ByteArrayOutputStream baos = new ByteArrayOutputStream(); excerpter.excerpt(new int[] { 0 }, tmp, baos); // Check Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray())); assertEquals(1, newwb.getNumberOfSheets()); s = newwb.getSheetAt(0); r1 = s.getRow(0); assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(0).getCellType()); assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(1).getCellType()); assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(2).getCellType()); assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(3).getCellType()); assertEquals(1.0, s.getRow(0).getCell(0).getNumericCellValue(), 0.001); assertEquals(2.0, s.getRow(0).getCell(1).getNumericCellValue(), 0.001); assertEquals(3.0, s.getRow(0).getCell(2).getNumericCellValue(), 0.001); assertEquals(6.0, s.getRow(0).getCell(3).getNumericCellValue(), 0.001); r2 = s.getRow(1); assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(0).getCellType()); assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(1).getCellType()); assertEquals(Cell.CELL_TYPE_BOOLEAN, r2.getCell(2).getCellType()); assertEquals(Cell.CELL_TYPE_BLANK, r2.getCell(3).getCellType()); assertEquals("Testing", s.getRow(1).getCell(0).getStringCellValue()); assertEquals("TestingTesting", s.getRow(1).getCell(1).getStringCellValue()); assertEquals(false, s.getRow(1).getCell(2).getBooleanCellValue()); } }
From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java
License:Apache License
@Test public void excerptRemovesUnUsed() throws Exception { String[] names = new String[] { "a", "b", "ccc", "dddd", "e", "f", "gg" }; for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) { // Create some dummy content for (String sn : names) { Sheet s = wb.createSheet(sn); s.createRow(0).createCell(0).setCellValue(sn); }//ww w . j av a 2s .c om // Excerpt by index File tmp = File.createTempFile("test", ".xls"); wb.write(new FileOutputStream(tmp)); ByteArrayOutputStream baos = new ByteArrayOutputStream(); int[] excI = new int[] { 0, 1, 2, 4, 5 }; excerpter.excerpt(excI, tmp, baos); // Check Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray())); assertEquals(5, newwb.getNumberOfSheets()); assertEquals(names[excI[0]], newwb.getSheetName(0)); assertEquals(names[excI[1]], newwb.getSheetName(1)); assertEquals(names[excI[2]], newwb.getSheetName(2)); assertEquals(names[excI[3]], newwb.getSheetName(3)); assertEquals(names[excI[4]], newwb.getSheetName(4)); assertEquals(names[excI[0]], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue()); assertEquals(names[excI[1]], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue()); assertEquals(names[excI[2]], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue()); assertEquals(names[excI[3]], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue()); assertEquals(names[excI[4]], newwb.getSheetAt(4).getRow(0).getCell(0).getStringCellValue()); // Excerpt by name String[] excN = new String[] { "b", "ccc", "f", "gg" }; baos = new ByteArrayOutputStream(); excerpter.excerpt(excN, tmp, baos); newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray())); assertEquals(4, newwb.getNumberOfSheets()); assertEquals(excN[0], newwb.getSheetName(0)); assertEquals(excN[1], newwb.getSheetName(1)); assertEquals(excN[2], newwb.getSheetName(2)); assertEquals(excN[3], newwb.getSheetName(3)); assertEquals(excN[0], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue()); assertEquals(excN[1], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue()); assertEquals(excN[2], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue()); assertEquals(excN[3], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue()); // Can't excerpt by invalid index try { excerpter.excerpt(new int[] { 0, 10 }, tmp, null); fail(); } catch (IllegalArgumentException e) { } // Can't excerpt by invalid name try { excerpter.excerpt(new String[] { "a", "invalid" }, tmp, null); fail(); } catch (IllegalArgumentException e) { } } }
From source file:com.rarediscovery.services.logic.Functions.java
/** * FileName,//from w w w.j a v a 2 s . c o m * Map<worsksheetName,WorksheetData> * WorksheetData = columnName[] , dataGrid[][] * * , Map<String,String[][]> tabTodataGridMap */ public static void saveAsExcelWorkbook(String filename, final Map<String, List<String>> sheetToColumns, final Models models, DefaultListModel reportAttributes) { Workbook wb = new HSSFWorkbook(); FileOutputStream fileOut; try { fileOut = new FileOutputStream(filename); //CreationHelper createHelper = wb.getCreationHelper(); // Create worksheets int sheetCount = sheetToColumns.size(); Sheet[] sheets = new Sheet[sheetCount]; int i = 0; for (String sheetName : sheetToColumns.keySet()) { sheets[i] = wb.createSheet(" " + sheetName); String[] reportHeaders = sheetToColumns.get(sheetName).toArray(new String[0]); addContent(sheets[i], reportHeaders, models, reportAttributes); i++; } /* // Create a row and put some cells in it. Rows are 0 based. Row row = sheet1.createRow((short)0); // Create a cell and put a value in it. Cell cell = row.createCell(0); cell.setCellValue(1); // Or do it on one line. row.createCell(1).setCellValue(1.2); row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string")); row.createCell(3).setCellValue(true); */ wb.write(fileOut); fileOut.flush(); fileOut.close(); Functions.log(" Completed generating excel report ! - " + filename); } catch (FileNotFoundException ex) { Logger.getLogger(Driver.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Driver.class.getName()).log(Level.SEVERE, null, ex); } }