Java Code Examples for org.apache.poi.ss.usermodel.Workbook#close()
The following examples show how to use
org.apache.poi.ss.usermodel.Workbook#close() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: FlatFileExtractor.java From Open-Lowcode with Eclipse Public License 2.0 | 6 votes |
/** * Extracts to excel a tree of objects * * @param objecttree object trees * @return the binary file */ public SFile extractToExcel(NodeTree<E> objecttree) { try { Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("Export Data"); loadWorkbook(sheet, objecttree); ByteArrayOutputStream documentinmemory = new ByteArrayOutputStream(); workbook.write(documentinmemory); workbook.close(); SFile returnresult = new SFile("OpenLowcodeExport-" + sdf.format(new Date()) + ".xlsx", documentinmemory.toByteArray()); return returnresult; } catch (IOException e) { String exceptionstring = "Exception in extracting objects to array " + definition.getName() + ", original IOException " + e.getMessage(); logger.severe(exceptionstring); for (int i = 0; i < e.getStackTrace().length; i++) { logger.severe(" " + e.getStackTrace()[i]); } throw new RuntimeException(exceptionstring); } }
Example 2
Source File: FileExportUtil.java From myexcel with Apache License 2.0 | 6 votes |
/** * 导出 * * @param workbook workbook * @param file file * @throws IOException IOException */ public static void export(Workbook workbook, File file) throws IOException { String suffix = Constants.XLSX; if (workbook instanceof HSSFWorkbook) { if (file.getName().endsWith(suffix)) { String absolutePath = file.getAbsolutePath(); file = Paths.get(absolutePath.substring(0, absolutePath.length() - 1)).toFile(); } suffix = Constants.XLS; } if (!file.getName().endsWith(suffix)) { file = Paths.get(file.getAbsolutePath() + suffix).toFile(); } try (OutputStream os = new FileOutputStream(file)) { workbook.write(os); } finally { if (workbook instanceof SXSSFWorkbook) { ((SXSSFWorkbook) workbook).dispose(); } workbook.close(); } }
Example 3
Source File: FlatFileExtractor.java From Open-Lowcode with Eclipse Public License 2.0 | 6 votes |
/** * * @param objectarray * @param specificaliaslist * @return */ public SFile extractToExcel(E[] objectarray, String[] specificaliaslist) { try { Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("Export Data"); Sheet referencessheet = workbook.createSheet("Reference Values"); loadWorkbook(sheet,referencessheet, objectarray, specificaliaslist); workbook.setActiveSheet(0); // setting active sheet to export data ByteArrayOutputStream documentinmemory = new ByteArrayOutputStream(); workbook.write(documentinmemory); workbook.close(); SFile returnresult = new SFile("OpenLowcodeExport-" + sdf.format(new Date()) + ".xlsx", documentinmemory.toByteArray()); return returnresult; } catch (IOException e) { String exceptionstring = "Exception in extracting objects to array " + definition.getName() + ", original IOException " + e.getMessage(); logger.severe(exceptionstring); for (int i = 0; i < e.getStackTrace().length; i++) { logger.severe(" " + e.getStackTrace()[i]); } throw new RuntimeException(exceptionstring); } }
Example 4
Source File: ExcelCellFormatterUnitTest.java From tutorials with MIT License | 5 votes |
@Test public void givenStringCell_whenGetCellStringValue_thenReturnStringValue() throws IOException { Workbook workbook = new XSSFWorkbook(fileLocation); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); ExcelCellFormatter formatter = new ExcelCellFormatter(); assertEquals("String Test", formatter.getCellStringValue(row.getCell(STRING_CELL_INDEX))); workbook.close(); }
Example 5
Source File: DataImporterController.java From curly with Apache License 2.0 | 5 votes |
private void openWorkbook(Workbook workbook) throws IOException { try { for (int i = 0; i < workbook.getNumberOfSheets(); i++) { worksheetSelector.getItems().add(workbook.getSheetName(i)); } sheetReader = (String sheetName) -> readSheet(workbook.getSheet(sheetName)); Platform.runLater(() -> worksheetSelector.getSelectionModel().selectFirst()); } finally { workbook.close(); } }
Example 6
Source File: CellValueAndNotFormulaHelper.java From tutorials with MIT License | 5 votes |
public Object getCellValueByEvaluatingFormula(String fileLocation, String cellLocation) throws IOException { Object cellValue = new Object(); FileInputStream inputStream = new FileInputStream(new File(fileLocation)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); FormulaEvaluator evaluator = workbook.getCreationHelper() .createFormulaEvaluator(); CellAddress cellAddress = new CellAddress(cellLocation); Row row = sheet.getRow(cellAddress.getRow()); Cell cell = row.getCell(cellAddress.getColumn()); if (cell.getCellType() == CellType.FORMULA) { switch (evaluator.evaluateFormulaCell(cell)) { case BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case NUMERIC: cellValue = cell.getNumericCellValue(); break; case STRING: cellValue = cell.getStringCellValue(); break; default: cellValue = null; } } workbook.close(); return cellValue; }
Example 7
Source File: FileExportUtil.java From myexcel with Apache License 2.0 | 5 votes |
/** * 加密导出 * * @param workbook workbook * @param file file * @param password password * @throws Exception Exception */ public static void encryptExport(final Workbook workbook, File file, final String password) throws Exception { if (workbook instanceof HSSFWorkbook) { throw new IllegalArgumentException("Document encryption for.xls is not supported"); } String suffix = Constants.XLSX; if (!file.getName().endsWith(suffix)) { file = Paths.get(file.getAbsolutePath() + suffix).toFile(); } try (FileOutputStream fos = new FileOutputStream(file)) { workbook.write(fos); if (workbook instanceof SXSSFWorkbook) { ((SXSSFWorkbook) workbook).dispose(); } final POIFSFileSystem fs = new POIFSFileSystem(); final EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard); final Encryptor enc = info.getEncryptor(); enc.confirmPassword(password); try (OPCPackage opc = OPCPackage.open(file, PackageAccess.READ_WRITE); OutputStream os = enc.getDataStream(fs)) { opc.save(os); } try (FileOutputStream fileOutputStream = new FileOutputStream(file)) { fs.writeFilesystem(fileOutputStream); } } finally { workbook.close(); } }
Example 8
Source File: AttachmentExportUtil.java From myexcel with Apache License 2.0 | 5 votes |
private static void clear(Workbook workbook) { if (workbook instanceof SXSSFWorkbook) { ((SXSSFWorkbook) workbook).dispose(); } try { workbook.close(); } catch (IOException e) { e.printStackTrace(); } }
Example 9
Source File: ExcelCellFormatterUnitTest.java From tutorials with MIT License | 5 votes |
@Test public void givenBooleanCell_whenGetCellStringValue_thenReturnBooleanStringValue() throws IOException { Workbook workbook = new XSSFWorkbook(fileLocation); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); ExcelCellFormatter formatter = new ExcelCellFormatter(); assertEquals("TRUE", formatter.getCellStringValue(row.getCell(BOOLEAN_CELL_INDEX))); workbook.close(); }
Example 10
Source File: ExportController.java From mumu with Apache License 2.0 | 5 votes |
/** * 导出 * @param modelName 模型名称 * @param excelType excel格式 * @param response * @return * @throws IOException */ @RequestMapping(value = { "/excel/{modelName}","/excel/{modelName}/{excelType}" }, method = RequestMethod.GET) public void exportExcel(@PathVariable String modelName,@PathVariable(required = false) String excelType, HttpServletResponse response) throws IOException { //默认导出xls格式excel if(excelType==null||"".equals(excelType)){ excelType="XLS"; } List<SysExportModel> models = modelService.queryExportModelByCondition(modelName); // 模型不存在 直接结束 if (models == null || models.size() == 0) { return; } // 获取导出数据 SysExportModel model = models.get(0); List<List<Object>> exportData = commonService.getAllData(model.getModelName(), model.getEnames(), null); List<String> exportHeaderNames = new ArrayList<String>(); String[] headerNames = model.getCnames().split(","); for (String headerName : headerNames) { exportHeaderNames.add(headerName); } response.reset(); // 文件下载 response.setContentType("application/vnd.ms-excel"); String filename = "报表"+modelName+"("+ new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+ ")"; filename = new String(filename.getBytes("gbk"), "iso-8859-1"); response.setHeader("Content-disposition", "attachment;filename="+ filename + "."+excelType.toLowerCase()); response.setBufferSize(1024); //获取excel表单 ExcelGenerater excelGenerater=new ExcelGenerater(); ExcelGeneraterBean excelGeneraterBean = excelGenerater.create(modelName, exportHeaderNames, exportData); Workbook workbook = excelGeneraterBean.getWorkbook(); //写入数据 到流 workbook.write(response.getOutputStream()); workbook.close(); }
Example 11
Source File: ExcelUtil.java From game-server with MIT License | 5 votes |
/** * 获取表头元数据 * * @param filePath * @return 属性名称列表、字段类型、描述说明 */ public static Args.Three<List<String>, List<String>, List<String>> getMetaData(String filePath, String sheetName) throws Exception { Workbook workBook = getWorkBook(filePath); if (workBook == null) { return null; } Sheet sheet = workBook.getSheet(sheetName); if (sheet == null) { return null; } List<String> fieldList = new ArrayList<>(); List<String> typeList = new ArrayList<>(); List<String> descList = new ArrayList<>(); //前三行为元数据 for (int i = 0; i < 3; i++) { Row row = sheet.getRow(i); if (row == null) { continue; } int lastCellNum = row.getPhysicalNumberOfCells(); for (int j = 0; j < lastCellNum; j++) { String value = row.getCell(j).toString(); switch (i) { case 0: fieldList.add(value); break; case 1: typeList.add(value); break; default: descList.add(value); break; } } } workBook.close(); return Args.of(fieldList, typeList, descList); }
Example 12
Source File: ExcelCellMergerUnitTest.java From tutorials with MIT License | 5 votes |
@Test public void givenCellRefString_whenAddMergeRegion_thenMergeRegionCreated() throws IOException { Workbook workbook = new XSSFWorkbook(fileLocation); Sheet sheet = workbook.getSheetAt(0); assertEquals(0, sheet.getNumMergedRegions()); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:C1")); assertEquals(1, sheet.getNumMergedRegions()); workbook.close(); }
Example 13
Source File: ExcelCellFormatterUnitTest.java From tutorials with MIT License | 5 votes |
@Test public void givenFormualCell_whenGetCellStringValueForFormula_thenReturnOriginalFormulatring() throws IOException { Workbook workbook = new XSSFWorkbook(fileLocation); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); ExcelCellFormatter formatter = new ExcelCellFormatter(); assertEquals("3", formatter.getCellStringValueWithFormula(row.getCell(FORMULA_CELL_INDEX), workbook)); workbook.close(); }
Example 14
Source File: ExcelToQuestionUtils.java From Exam-Online with Apache License 2.0 | 5 votes |
public static List<Question> readQuestions(InputStream inputStream) throws EncryptedDocumentException, InvalidFormatException, IOException { Workbook workbook = WorkbookFactory.create(inputStream); List<Question> questions = readQuestions(workbook.getSheetAt(0)); workbook.close(); return questions; }
Example 15
Source File: ExcelToQuestionUtils.java From Exam-Online with Apache License 2.0 | 5 votes |
public static List<Question> readQuestions(File file) throws EncryptedDocumentException, InvalidFormatException, IOException { Workbook workbook = WorkbookFactory.create(file); List<Question> questions = readQuestions(workbook.getSheetAt(0)); workbook.close(); return questions; }
Example 16
Source File: XlsLoader.java From xlsmapper with Apache License 2.0 | 4 votes |
/** * Excelファイルの異なる形式の複数シートを読み込み、任意のクラスにマップする。 * <p>複数のシートの形式を一度に読み込む際に使用します。</p> * * @param xlsIn 読み込み元のExcelファイルのストリーム。 * @param classes マッピング先のクラスタイプの配列。 * @return マッピングした複数のシートの結果。 * {@link Configuration#isIgnoreSheetNotFound()}の値がtrueで、シートが見つからない場合、マッピング結果には含まれません。 * @throws IllegalArgumentException {@literal xlsIn == null or classes == null} * @throws IllegalArgumentException {@literal calsses.length == 0} * @throws IOException ファイルの読み込みに失敗した場合 * @throws XlsMapperException マッピングに失敗した場合 */ @SuppressWarnings({"unchecked", "rawtypes"}) public MultipleSheetBindingErrors<Object> loadMultipleDetail(final InputStream xlsIn, final Class<?>[] classes) throws XlsMapperException, IOException { ArgUtils.notNull(xlsIn, "xlsIn"); ArgUtils.notEmpty(classes, "classes"); final AnnotationReader annoReader = new AnnotationReader(configuration.getAnnotationMapping().orElse(null)); final MultipleSheetBindingErrors<Object> multipleStore = new MultipleSheetBindingErrors<>(); Workbook book = null; try { book = WorkbookFactory.create(xlsIn); } catch (InvalidFormatException e) { throw new XlsMapperException(MessageBuilder.create("file.failLoadExcel.notSupportType").format(), e); } finally { if(book != null) { book.close(); } } for(Class<?> clazz : classes) { final XlsSheet sheetAnno = clazz.getAnnotation(XlsSheet.class); if(sheetAnno == null) { throw new AnnotationInvalidException(sheetAnno, MessageBuilder.create("anno.notFound") .varWithClass("property", clazz) .varWithAnno("anno", XlsSheet.class) .format()); } try { final Sheet[] xlsSheet = configuration.getSheetFinder().findForLoading(book, sheetAnno, annoReader, clazz); for(Sheet sheet : xlsSheet) { multipleStore.addBindingErrors(loadSheet(sheet, (Class)clazz, annoReader)); } } catch(SheetNotFoundException ex){ if(!configuration.isIgnoreSheetNotFound()){ logger.warn(MessageBuilder.create("log.skipNotFoundSheet").format(), ex); throw ex; } } } return multipleStore; }
Example 17
Source File: ExcelReaderImpl.java From tephra with MIT License | 4 votes |
@Override public JSONObject read(InputStream inputStream, MediaWriter mediaWriter) { JSONObject object = new JSONObject(); try { Workbook workbook = WorkbookFactory.create(inputStream); JSONArray sheets = new JSONArray(); workbook.forEach(sheet -> { JSONObject sheetJson = new JSONObject(); sheetJson.put("name", sheet.getSheetName()); sheetJson.put("first", sheet.getFirstRowNum()); sheetJson.put("last", sheet.getLastRowNum()); JSONArray rows = new JSONArray(); sheet.forEach(row -> { JSONObject rowJson = new JSONObject(); rowJson.put("first", row.getFirstCellNum()); rowJson.put("last", row.getLastCellNum()); JSONArray cells = new JSONArray(); row.forEach(cell -> { JSONObject cellJson = new JSONObject(); cellJson.put("type", cell.getCellType().name().toLowerCase()); switch (cell.getCellType()) { case STRING: cellJson.put("value", cell.getStringCellValue()); break; case NUMERIC: cellJson.put("value", cell.getNumericCellValue()); break; case BOOLEAN: cellJson.put("value", cell.getBooleanCellValue()); break; case FORMULA: cellJson.put("formula", cell.getCellFormula()); break; default: } cells.add(cellJson); }); rowJson.put("cells", cells); rows.add(rowJson); }); sheetJson.put("rows", rows); sheets.add(sheetJson); }); object.put("sheets", sheets); workbook.close(); inputStream.close(); } catch (Exception e) { logger.warn(e, "读取并解析Excel数据时发生异常!"); } return object; }
Example 18
Source File: ExcelPOIHelper.java From tutorials with MIT License | 4 votes |
public void writeExcel() throws IOException { Workbook workbook = new XSSFWorkbook(); try { Sheet sheet = workbook.createSheet("Persons"); sheet.setColumnWidth(0, 6000); sheet.setColumnWidth(1, 4000); Row header = sheet.createRow(0); CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont font = ((XSSFWorkbook) workbook).createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 16); font.setBold(true); headerStyle.setFont(font); Cell headerCell = header.createCell(0); headerCell.setCellValue("Name"); headerCell.setCellStyle(headerStyle); headerCell = header.createCell(1); headerCell.setCellValue("Age"); headerCell.setCellStyle(headerStyle); CellStyle style = workbook.createCellStyle(); style.setWrapText(true); Row row = sheet.createRow(2); Cell cell = row.createCell(0); cell.setCellValue("John Smith"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(20); cell.setCellStyle(style); File currDir = new File("."); String path = currDir.getAbsolutePath(); String fileLocation = path.substring(0, path.length() - 1) + "temp.xlsx"; FileOutputStream outputStream = new FileOutputStream(fileLocation); workbook.write(outputStream); } finally { if (workbook != null) { workbook.close(); } } }
Example 19
Source File: MSExcelWriter.java From hadoopoffice with Apache License 2.0 | 4 votes |
/** * Writes the document in-memory representation to the OutputStream. Afterwards, it closes all related workbooks. * * @throws java.io.IOException in case of issues writing * * */ @Override public void close() throws IOException { try { // prepare metadata prepareMetaData(); // write if (this.oStream!=null) { if (this.howc.getPassword()==null) { // no encryption finalizeWriteNotEncrypted(); } else // encryption if (this.currentWorkbook instanceof HSSFWorkbook) { // old Excel format finalizeWriteEncryptedHSSF(); } else if (this.currentWorkbook instanceof XSSFWorkbook) { finalizeWriteEncryptedXSSF(); } else { LOG.error("Could not write encrypted workbook, because type of workbook is unknown"); } } } finally { // close filesystems if (this.ooxmlDocumentFileSystem!=null) { ooxmlDocumentFileSystem.close(); } // close main workbook if (this.currentWorkbook!=null) { this.currentWorkbook.close(); } // close linked workbooks for (Workbook currentWorkbookItem: this.listOfWorkbooks) { if (currentWorkbookItem!=null) { currentWorkbookItem.close(); } } } try { // do we need to sign => sign if (this.signUtil!=null) { // sign LOG.info("Signing document \""+this.howc.getFileName()+"\""); if (this.howc.getSigCertificate()==null) { LOG.error("Cannot sign document \""+this.howc.getFileName()+"\". No certificate for key provided"); } else if (!(this.currentWorkbook instanceof XSSFWorkbook)){ LOG.warn("Signing of docuemnts in old Excel format not supported for \""+this.howc.getFileName()+"\""); }else { try { ArrayList<X509Certificate> certList = new ArrayList<>(); certList.add(this.howc.getSigCertificate()); this.signUtil.sign(this.howc.getSigKey(), certList, this.howc.getPassword(), MSExcelWriter.getHashAlgorithm(this.howc.getSigHash())); } catch (XMLSignatureException|MarshalException|IOException|FormatNotUnderstoodException e) { LOG.error("Cannot sign document \""+this.howc.getFileName()+"\" "+e); } } } } finally { if (this.signUtil!=null) { this.signUtil.close(); } } }
Example 20
Source File: AbstractXlsView.java From java-technology-stack with MIT License | 2 votes |
/** * The actual render step: taking the POI {@link Workbook} and rendering * it to the given response. * @param workbook the POI Workbook to render * @param response current HTTP response * @throws IOException when thrown by I/O methods that we're delegating to */ protected void renderWorkbook(Workbook workbook, HttpServletResponse response) throws IOException { ServletOutputStream out = response.getOutputStream(); workbook.write(out); workbook.close(); }