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

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

/**
 * 在指定的工作簿、行、列书写值。
 * 作者：朱志杰
 * Sep 19, 2012 3:25:01 PM
 * @param sheetOrder 工作簿序号，基于0.
 * @param colum 列 基于1
 * @param row 行 基于1
 * @param content 将要被书写的内容。
 * @throws Exception 书写后保存异常。
 */
public void write(int sheetOrder,int colum, int row, String content) throws Exception {
	FileInputStream fis = new FileInputStream(path);
	Workbook workbook = WorkbookFactory.create(fis);
	if(fis != null) {
           fis.close();
       }
	Sheet sheet = workbook.getSheetAt(sheetOrder);
	Row rows = sheet.createRow(row-1);
	Cell cell = rows.createCell(colum-1);
	cell.setCellValue(content);
	FileOutputStream fileOut = new FileOutputStream(path);
	workbook.write(fileOut);
	fileOut.close();

}
 

@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
	String codedFileName = "临时文件";
	Workbook workbook = ExcelExportUtil.exportExcel((ExportParams) model.get(MapExcelConstants.PARAMS), (List<ExcelExportEntity>) model.get(MapExcelConstants.ENTITY_LIST), (Collection<? extends Map<?, ?>>) model.get(MapExcelConstants.MAP_LIST));
	if (model.containsKey(MapExcelConstants.FILE_NAME)) {
		codedFileName = (String) model.get(MapExcelConstants.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();
}
 

/**
 * Gets the output data.
 *
 * Does not require a HTTP request/response
 *
 * @param out
 * @param reference
 * @param parameters
 * @throws IOException
 */
@EntityCustomAction(action = "get-export", viewKey = EntityView.VIEW_SHOW)
public ActionReturn getExport(final OutputStream out, final EntityReference reference,
		final Map<String, Object> parameters) {

	final String userId = getUserId(reference);
	final String siteId = getSiteId(reference);

	try {
		if (this.sakaiProxy.hasUserSitePermission(userId, RosterFunctions.ROSTER_FUNCTION_EXPORT, siteId)) {
			final RosterSite site = getSite(reference, siteId);
			final Workbook workbook = getExportData(userId, site, parameters);
			workbook.write(out);
			out.close();
			final ActionReturn actionReturn = new ActionReturn("base64",
					"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", out);
			return actionReturn;

		} else {
			throw new EntityException(MSG_NO_EXPORT_PERMISSION, reference.getReference());
		}
	} catch (final IOException e) {
		log.error(MSG_NO_FILE_CREATED, e);
		throw new EntityException(MSG_NO_FILE_CREATED, reference.getReference());
	}
}
 

@Test
public void test_FontOne() throws IOException
{
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("sheet 01");
    Row row = sheet.createRow(1);
    Font font = wb.createFont();
    font.setBold(true);
    font.setColor((short) 13);
    font.setFontHeightInPoints((short) 24);
    font.setFontName("宋体");
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);
    Cell cell = row.createCell(1);
    cell.setCellValue("这是测试字体格式的");
    cell.setCellStyle(cellStyle);
    FileOutputStream fileOutputStream = new FileOutputStream("D://font.xlsx");
    wb.write(fileOutputStream);
    wb.close();
}
 

/**
 * 写入excel并填充内容,一个sheet只能写65536行以下，超出会报异常，写入时建议使用AbstractExcel2007Writer
 * @param fileName
 * @throws IOException
 */
@SuppressWarnings("resource")
public static void writeExcel(String fileName) throws IOException{
		
		// 创建excel2003对象
		Workbook wb = new HSSFWorkbook();
		
		// 设置文件放置路径和文件名
	    FileOutputStream fileOut = new FileOutputStream(fileName);
	    // 创建新的表单
	    Sheet sheet = wb.createSheet( "newsheet" );
	    // 创建新行
	    for(int i=0;i<20000;i++){
		    Row row = sheet.createRow(i);
		    // 创建单元格
		    Cell cell = row.createCell(0);
		    // 设置单元格值
		    cell.setCellValue(1);
		    row.createCell(1).setCellValue(1+i);
		    row.createCell(2).setCellValue(true);
		    row.createCell(3).setCellValue(0.43d);
		    row.createCell(4).setCellValue('d');
		    row.createCell(5).setCellValue( "" );
		    row.createCell(6).setCellValue( "第七列"+i);
		    row.createCell(7).setCellValue( "第八列"+i);
	    }
	    wb.write(fileOut);
	    fileOut.close();
}
 

public static void writeAndClose(Workbook doc, OutputStream out) throws IOException {
    try {
        doc.write(out);
    } finally {
        closeQuietly(doc);
    }
}
 

private void excelSpreadsheet(OutputStream os, List<SignupMeetingWrapper> meetingWrappers,
		String downloadType) throws IOException {
	EventWorksheet worksheet = new EventWorksheet(getSakaiFacade());
	worksheet.setSignupMeetingService(getSignupMeetingService());

	Workbook wb = worksheet.getEventWorkbook(meetingWrappers, downloadType);
	wb.write(os);
}
 

public  MessageResult exportExcel(HttpServletRequest request, HttpServletResponse response, List<E> list, String name) throws Exception{
    if(list.isEmpty()){
        return  MessageResult.error(-1,"没有数据");
    }
    String physicalPath = request.getSession().getServletContext().getRealPath("/")+"excel/";
    String fileName = physicalPath+name+".xlsx";
    File savefile = new File(physicalPath);
    if (!savefile.exists()) {
        savefile.mkdirs();
    }
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), list.get(0).getClass(), list);
    FileOutputStream fos = new FileOutputStream(fileName);
    workbook.write(fos);
    fos.close();
    response.setContentType("multipart/form-data");
    response.setHeader("Content-Disposition", "attachment;filename="+name+".xlsx");
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    OutputStream out = response.getOutputStream();
    File file = new File(fileName);
    InputStream in = new FileInputStream(file);
    int data=in.read();
    while(data!=-1){
        out.write(data);
        data=in.read();
    }
    in.close();
    out.close();
    file.delete();
    return  MessageResult.success();
}
 

private String createExcelContent(){
    Workbook workBook = new HSSFWorkbook();
    workBook.createSheet();
    Sheet sheet = workBook.getSheetAt(0);
    Row column = sheet.createRow(0);
    Row data1 = sheet.createRow(1);
    sheet.createRow(2);
    Row data2 = sheet.createRow(4);


    Field[] fields = TestClass.class.getDeclaredFields();
    for (int i = 0; i < fields.length; i++) {
        column.createCell(i, CellType.STRING).setCellValue(fields[i].getName());
    }

    column.getCell(5).setCellValue("test");

    data1.createCell(0, CellType.BOOLEAN).setCellValue(true);
    data2.createCell(0, CellType.STRING).setCellValue("True");
    data1.createCell(1, CellType.NUMERIC).setCellValue(2.3);
    data1.createCell(2, CellType.NUMERIC).setCellValue(3.3);
    data1.createCell(3, CellType.NUMERIC).setCellValue(4.3);
    data1.createCell(4, CellType.NUMERIC).setCellValue(5.3);
    data2.createCell(5, CellType.NUMERIC).setCellValue("a,b,c");

    ByteArrayOutputStream os = new ByteArrayOutputStream();
    try {
        workBook.write(os);
        byte[] bytes = os.toByteArray();
        os.close();
        return Base64.getEncoder().encodeToString(bytes);
    } catch (IOException e) {
        e.printStackTrace();
        return null;
    }
}
 

private void exportIntoXLS(boolean writeBackResponseInline, String mimeType, IStatement statement, String sqlQuery, Vector extractedFields,
		String exportLimit) throws EMFInternalError, IOException, FileNotFoundException, SpagoBIEngineException {
	IDataStore dataStore = getDataStore(statement, sqlQuery, exportLimit);
	Locale locale = (Locale) getEngineInstance().getEnv().get(EngineConstants.ENV_LOCALE);
	QbeXLSExporter exp = new QbeXLSExporter(dataStore, locale);
	exp.setExtractedFields(extractedFields);

	int resultNumber = getResultNumber(dataStore);
	Integer limit = parseExportLimit(exportLimit);
	boolean showLimitExportMessage = false;
	if (resultNumber > limit) {
		showLimitExportMessage = true;
	}
	Workbook wb = exp.export(exportLimit, showLimitExportMessage);

	File file = File.createTempFile("workbook", ".xls");
	FileOutputStream stream = new FileOutputStream(file);
	wb.write(stream);
	stream.flush();
	stream.close();
	try {
		writeBackToClient(file, null, writeBackResponseInline, "workbook.xls", mimeType);
	} catch (IOException ioe) {
		throw new SpagoBIEngineException("Impossible to write back the responce to the client", ioe);
	} finally {
		if (file != null && file.exists()) {
			try {
				file.delete();
			} catch (Exception e) {
				logger.warn("Impossible to delete temporary file " + file, e);
			}
		}
	}
}
 

public void saveThisExcel(ImportParams params, Class<?> pojoClass, boolean isXSSFWorkbook, Workbook book) throws Exception {
	String path = PoiPublicUtil.getWebRootPath(getSaveExcelUrl(params, pojoClass));
	File savefile = new File(path);
	if (!savefile.exists()) {
		savefile.mkdirs();
	}
	SimpleDateFormat format = new SimpleDateFormat("yyyMMddHHmmss");
	FileOutputStream fos = new FileOutputStream(path + "/" + format.format(new Date()) + "_" + Math.round(Math.random() * 100000) + (isXSSFWorkbook == true ? ".xlsx" : ".xls"));
	book.write(fos);
	fos.close();
}
 

@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request,
                                       HttpServletResponse response) throws Exception {
    String codedFileName = "临时文件";
    Workbook workbook = ExcelExportUtil.exportExcel(
        (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);
    }
    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();
}
 

ExcelTestHelper(final String parent, boolean generateXls) throws Exception {
  this.xls = generateXls;

  // Create a test Excel sheet with all types of supported data
  Workbook wb = generateXls ? new HSSFWorkbook() : new XSSFWorkbook();

  CreationHelper creationHelper = wb.getCreationHelper();
  DataFormat dataFormat = creationHelper.createDataFormat();
  short fmt = dataFormat.getFormat("yyyy-mm-dd hh:mm:ss");
  CellStyle style = wb.createCellStyle();
  style.setDataFormat(fmt);

  Sheet sheetWithHeader = wb.createSheet("Sheet 1");

  // Create header row
  Row headerRow = sheetWithHeader.createRow((short) 0);
  headerRow.createCell(0).setCellValue("Number");
  headerRow.createCell(1).setCellValue("String1");
  headerRow.createCell(2).setCellValue("String2");
  headerRow.createCell(3).setCellValue("MyTime");
  headerRow.createCell(4).setCellValue("Formula");
  headerRow.createCell(5).setCellValue("Boolean");
  headerRow.createCell(6).setCellValue("Error");
  generateSheetData(sheetWithHeader, style, (short)1);

  Sheet sheetWithoutHeader = wb.createSheet("Sheet 2");
  generateSheetData(sheetWithoutHeader, style, (short)0);

  testFilePath = new File(parent, "excelTestFile").getPath();

  // Write the output to a file
  FileOutputStream fileOut = new FileOutputStream(testFilePath);
  wb.write(fileOut);
  fileOut.close();
}
 

/**
 * 输出客户端excel
 */
public static void responseExcel(HttpServletResponse response, Workbook workbook, String fileName) {
    try (OutputStream os = response.getOutputStream()) {
        // 设定字符集
        response.setCharacterEncoding("UTF-8");
        // 设定Content类型
        response.setContentType("multipart/form-data");
        // 设定Http头部
        response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(os);
    } catch (IOException e) {
        LOGGER.error("输出excel异常", e);
    }
}
 

@Test
@SneakyThrows
public void testExport() {
    ExportParams params = new ExportParams(null, "测试", ExcelType.XSSF);
    Workbook workbook = ExcelExportUtil.exportExcel(params, User.class, USER_EXCEL_LIST);
    File savefile = new File(OUTPUT_PATH);
    if (!savefile.exists()) {
        savefile.mkdirs();
    }
    FileOutputStream fos = new FileOutputStream(OUTPUT_PATH + "User.xlsx");
    workbook.write(fos);
    fos.close();
}
 

/**
 * 写入excel并填充内容,一个sheet只能写65536行以下，超出会报异常，写入时建议使用AbstractExcel2007Writer
 * @param fileName
 * @throws IOException
 */
public static void writeExcel(String fileName) throws IOException{
		
		// 创建excel2003对象
		Workbook wb = new HSSFWorkbook();
		
		// 设置文件放置路径和文件名
	    FileOutputStream fileOut = new FileOutputStream(fileName);
	    // 创建新的表单
	    Sheet sheet = wb.createSheet("newsheet");
	    // 创建新行
	    for(int i=0;i<20000;i++){
		    Row row = sheet.createRow(i);
		    // 创建单元格
		    Cell cell = row.createCell(0);
		    // 设置单元格值
		    cell.setCellValue(1);
		    row.createCell(1).setCellValue(1+i);
		    row.createCell(2).setCellValue(true);
		    row.createCell(3).setCellValue(0.43d);
		    row.createCell(4).setCellValue('d');
		    row.createCell(5).setCellValue("");
		    row.createCell(6).setCellValue("第七列"+i);
		    row.createCell(7).setCellValue("第八列"+i);
	    }
	    wb.write(fileOut);
	    fileOut.close();
}
 

public void saveThisExcel(ImportParams params, Class<?> pojoClass, boolean isXSSFWorkbook,
                          Workbook book) throws Exception {
    String path = PoiPublicUtil.getWebRootPath(getSaveExcelUrl(params, pojoClass));
    File savefile = new File(path);
    if (!savefile.exists()) {
        savefile.mkdirs();
    }
    SimpleDateFormat format = new SimpleDateFormat("yyyMMddHHmmss");
    FileOutputStream fos = new FileOutputStream(path + "/" + format.format(new Date()) + "_"
                                                + Math.round(Math.random() * 100000)
                                                + (isXSSFWorkbook == true ? ".xlsx" : ".xls"));
    book.write(fos);
    fos.close();
}
 

/**
 * 加密导出
 *
 * @param workbook workbook
 * @param fileName fileName
 * @param response response
 * @param password password
 */
public static void encryptExport(final Workbook workbook, String fileName, HttpServletResponse response, final String password) {
    if (workbook instanceof HSSFWorkbook) {
        throw new IllegalArgumentException("Document encryption for.xls is not supported");
    }
    Path path = null;
    try {
        String suffix = Constants.XLSX;
        path = TempFileOperator.createTempFile("encrypt_temp", suffix);
        workbook.write(Files.newOutputStream(path));

        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(path.toFile(), PackageAccess.READ_WRITE);
             OutputStream os = enc.getDataStream(fs)) {
            opc.save(os);
        }
        if (!fileName.endsWith(suffix)) {
            fileName += suffix;
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        setAttachmentConfig(fileName, response);
        fs.writeFilesystem(response.getOutputStream());
    } catch (IOException | InvalidFormatException | GeneralSecurityException e) {
        throw new RuntimeException(e);
    } finally {
        clear(workbook);
        TempFileOperator.deleteTempFile(path);
    }
}
 

public void writeOutputStream(Workbook workbook, OutputStream out) throws IOException {
	workbook.write(out);
}