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 vote down vote up
/**
 * 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 vote down vote up
/**
 * 导出
 *
 * @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 vote down vote up
/**
 * 
 * @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 vote down vote up
@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 vote down vote up
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 vote down vote up
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 vote down vote up
/**
 * 加密导出
 *
 * @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 vote down vote up
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 vote down vote up
@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 vote down vote up
/**
 * 导出
 * @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 vote down vote up
/**
 * 获取表头元数据
 *
 * @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 vote down vote up
@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 vote down vote up
@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 vote down vote up
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 vote down vote up
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 vote down vote up
/**
 * 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 vote down vote up
@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 vote down vote up
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 vote down vote up
/**
* 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 vote down vote up
/**
 * 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();
}