Java Code Examples for org.apache.poi.ss.usermodel.Workbook#close()

/**
 * 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);
	}
}
 

/**
 * 导出
 *
 * @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();
    }
}
 

/**
 * 
 * @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);
	}
}
 

@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();
}
 

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();
    }
}
 

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;
}
 

/**
 * 加密导出
 *
 * @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();
    }
}
 

private static void clear(Workbook workbook) {
    if (workbook instanceof SXSSFWorkbook) {
        ((SXSSFWorkbook) workbook).dispose();
    }
    try {
        workbook.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}
 

@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();
}
 

/**
 * 导出
 * @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();
}
 

/**
 * 获取表头元数据
 *
 * @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);
}
 

@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();
}
 

@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();
}
 

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;
}
 

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;
}
 

/**
 * 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;
}
 

@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;
}
 

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();
           
        }
    }
}
 

/**
* 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();
		}
	}
}
 

/**
 * 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();
}