Java Code Examples for org.apache.poi.xssf.usermodel.XSSFWorkbook#write()

private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	int row = this.putTables(wb, sh, context);
	row = this.putCharts(wb, sh, context, row);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "kpis-dashboard.xlsx");
	file = null;
	return oid;
}
 

private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	this.putCharts(wb, sh, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "objectives-dashboard.xlsx");
	file = null;
	return oid;
}
 

private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	BscReportPropertyUtils.loadData();
	
	int row = this.putDateRange(wb, sh, context);
	this.putCharts(wb, sh, context, row);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "perspectives-dashboard.xlsx");
	file = null;
	return oid;
}
 

private String createExcel(Context context) throws Exception {
	String visionOid = (String)context.get("visionOid");
	VisionVO vision = null;
	BscStructTreeObj treeObj = (BscStructTreeObj)this.getResult(context);
	for (VisionVO visionObj : treeObj.getVisions()) {
		if (visionObj.getOid().equals(visionOid)) {
			vision = visionObj;
		}
	}
	BscReportPropertyUtils.loadData();
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	int row = 0;
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	row += this.createHead(wb, sh, row, vision, context);
	row = this.createMainBody(wb, sh, row, vision, context);
	this.createFoot(wb, sh, row, vision, context);
	
	this.putSignature(wb, sh, row+2, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "personal-report.xlsx");
	file = null;
	return oid;
}
 

private String createExcel(Context context) throws Exception {
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	BscReportPropertyUtils.loadData();
	
	int row = 0;
	
	row = this.putVision(wb, sh, context, row);
	row = this.putDateRangePerspectives(wb, sh, context, row);
	row = this.putChartsPerspectives(wb, sh, context, row);
	row = this.putDateRangeObjectives(wb, sh, context, row);
	row = this.putChartsObjectives(wb, sh, context, row);		
	row = this.putDateRangeKpis(wb, sh, context, row);
	row = this.putChartsKpis(wb, sh, context, row);	
	row = this.putDateRangeCharts(wb, sh, context, row);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "dashboard-new.xlsx");
	file = null;
	return oid;
}
 

/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process( String fileName ) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet( "sheet1" );
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream( "template.xlsx" );
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile( "sheet", ".xml" );
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File( "template.xlsx" );
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 

/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
public void process(String fileName) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet("sheet1");
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream("template.xlsx");
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile("sheet", ".xml");
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File("template.xlsx");
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 

/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process( String fileName ) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet( "sheet1" );
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream( "template.xlsx" );
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile( "sheet", ".xml" );
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File( "template.xlsx" );
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 

/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process( OutputStream out ) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet( "sheet1" );
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream( "template.xlsx" );
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile( "sheet", ".xml" );
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File( "template.xlsx" );
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 

/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process( String fileName ) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet( "sheet1" );
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream( "template.xlsx" );
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile( "sheet", ".xml" );
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File( "template.xlsx" );
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 

/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process( OutputStream out ) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet( "sheet1" );
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream( "template.xlsx" );
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile( "sheet", ".xml" );
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File( "template.xlsx" );
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 

/**
 * 写入电子表格的主要流程
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process( String fileName ) throws Exception{
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet( "sheet1" );
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream( "template.xlsx" );
	wb.write(os);
	os.close();
	
	// 生成xml文件
	File tmp = File.createTempFile( "sheet", ".xml" );
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();
	
	// 使用产生的数据替换模板
	File templateFile = new File( "template.xlsx" );
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	//删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile()&&templateFile.exists()){
		templateFile.delete();
	}
}
 

/**
 * 写入电子表格的主要流程
 * 
 * @param fileName
 * @throws Exception
 */
@SuppressWarnings("resource")
public void process(String fileName) throws Exception {
	
	// 建立工作簿和电子表格对象
	XSSFWorkbook wb = new XSSFWorkbook();
	XSSFSheet sheet = wb.createSheet("sheet1");
	
	// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
	String sheetRef = sheet.getPackagePart().getPartName().getName();

	// 保存模板
	FileOutputStream os = new FileOutputStream("template.xlsx");
	wb.write(os);
	os.close();

	// 生成xml文件
	File tmp = File.createTempFile("sheet", ".xml");
	Writer fw = new FileWriter(tmp);
	sw = new SpreadsheetWriter(fw);
	generate();
	fw.close();

	// 使用产生的数据替换模板
	File templateFile = new File("template.xlsx");
	FileOutputStream out = new FileOutputStream(fileName);
	substitute(templateFile, tmp, sheetRef.substring(1), out);
	out.close();
	// 删除文件之前调用一下垃圾回收器，否则无法删除模板文件
	System.gc();
	// 删除临时模板文件
	if (templateFile.isFile() && templateFile.exists()) {
		templateFile.delete();
	}
}
 

private String createExcel(Context context) throws Exception {
	String visionOid = (String)context.get("visionOid");
	VisionVO vision = null;
	BscStructTreeObj treeObj = (BscStructTreeObj)this.getResult(context);
	for (VisionVO visionObj : treeObj.getVisions()) {
		if (visionObj.getOid().equals(visionOid)) {
			vision = visionObj;
		}
	}
	BscReportPropertyUtils.loadData();
	String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
	String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;	
	int row = 0;
	XSSFWorkbook wb = new XSSFWorkbook();				
	XSSFSheet sh = wb.createSheet();
	
	row += this.createHead(wb, sh, row, vision, context);
	row = this.createMainBody(wb, sh, row, vision);
	
	this.putSignature(wb, sh, row+1, context);
	
       FileOutputStream out = new FileOutputStream(fileFullPath);
       wb.write(out);
       out.close();
       wb = null;
       
       File file = new File(fileFullPath);
	String oid = UploadSupportUtils.create(
			Constants.getSystem(), UploadTypes.IS_TEMP, false, file, "department-report.xlsx");
	file = null;
	return oid;		
}
 

/**
 * 导出Excel的例子
 * 因为类上面注解是@Controller，此方法需要@ResponseBody注解；如果类是RestController，则不需要ResponseBody
 * @return
 * @throws Exception
 */
@ResponseBody
@GetMapping("/export")
public ResponseEntity<byte[]> exportExcel() throws Exception{
    logger.trace("exportExcel");
    HttpHeaders responseHeaders = new HttpHeaders();
    responseHeaders.setContentDispositionFormData("attachment",new String("导出的文件名.xlsx".getBytes(), "ISO8859-1"));
    responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);

    //中文文件名需要用iso8859-1编码
    InputStream templateIs = this.getClass().getResourceAsStream("/excel-templates/templet.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(templateIs);
    XSSFSheet sheet = workbook.getSheetAt(0);

    List<SampleItem> list = getDataList();

    CellStyle cellStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd"));

    for (int i=0; i<list.size(); i++) {
        SampleItem si = list.get(i);

        XSSFRow row = sheet.createRow(i + 1);

        Cell cell1 = row.createCell(0);
        cell1.setCellValue(si.getDate());
        cell1.setCellStyle(cellStyle);

        Cell cell2 = row.createCell(1);
        cell2.setCellValue(si.getName());

        Cell cell3 = row.createCell(2);
        cell3.setCellValue(si.getScore());
    }

    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    workbook.write(bos);
    workbook.close();
    return new ResponseEntity<byte[]>(bos.toByteArray(), responseHeaders, HttpStatus.OK);
}
 

public static void writeExcelObject(HttpServletResponse response,List<Object> data,String sheetName,String fileName) throws Exception{
	response.setContentType("application/vnd.ms-excel");
	response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
	ServletOutputStream outputStream = response.getOutputStream();
	XSSFWorkbook xssfWorkbook = writeSheetObject(data,sheetName);
	xssfWorkbook.write(outputStream);
	outputStream.flush();
	outputStream.close();
	response.flushBuffer();
	xssfWorkbook.close();
}
 

public static void writeXLSXFile() throws Exception {

		XSSFWorkbook xssfwb = new XSSFWorkbook();
		XSSFSheet sheet = xssfwb.createSheet(sheetName);
		for (int row = 0; row < tableData.size(); row++) {
			XSSFRow xddfrow = sheet.createRow(row);
			rowData = tableData.get(row);
			for (int col = 0; col < rowData.size(); col++) {
				XSSFCell cell = xddfrow.createCell(col);
				cell.setCellValue(rowData.get(col));
				logger.info("Writing " + row + " " + col + "  " + rowData.get(col));
			}
		}

		try (OutputStream fileOutputStream = new FileOutputStream(
				excelFileName)) {

			xssfwb.write(fileOutputStream);
			xssfwb.close();
			fileOutputStream.flush();
			fileOutputStream.close();

		} catch (IOException e) {
			String message = String.format("Exception saving XLSX file %s\n",
					excelFileName) + e.getMessage();
			logger.info(message);
			// NOTE: throw exceptions with user friendly messages to be rendered
			// by the master app
			throw new Exception(message);

		}
	}
 

public static void writeExcelObject(OutputStream outputStream,List<Object> data,String sheetName) throws Exception{
	XSSFWorkbook xssfWorkbook = writeSheetObject(data,sheetName);
	xssfWorkbook.write(outputStream);
	outputStream.flush();
	outputStream.close();
	xssfWorkbook.close();
}
 

/**
 * 创建Excel表格
 * @throws Exception 
 */
public static boolean createXlsx(File target, String[] columnNames, Object[][] data) {
	try {
		int num = 0;
		// 创建工作簿
		XSSFWorkbook wb = new XSSFWorkbook();
		// 工作表
		XSSFSheet sheet = wb.createSheet("学生信息表");
		// 标头行，代表第一行
		XSSFRow header = sheet.createRow(num++);
		// 创建单元格，0代表第一行第一列
		for (int i = 0; i < columnNames.length; i++) {
			//设置excel表格某一行的值
			header.createCell(i).setCellValue(columnNames[i]);
		}
		
		for (int i = 0; i < data.length; i++) {
			//设置操作行为下一行
			XSSFRow row = sheet.createRow(num++);
			for (int j = 0; j < data[i].length; j++) {
				//设置excel表格某一行的值
				row.createCell(j).setCellValue(data[i][j].toString());
			}
		}
		
		/**
		 * 输出表格
		 */
		FileOutputStream fos = new FileOutputStream(target);
		wb.write(fos);
		fos.close();
		return true;
	} catch (Exception e) {
		return false;
	}
}
 

protected boolean writeExcel(File file, String sql) {
    try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);
             FileWriter writer = new FileWriter(file, Charset.forName("utf-8"))) {
        String filename = file.getAbsolutePath();
        conn.setReadOnly(true);
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet1");
        List<String> columns = columnNames();
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCellStyle horizontalCenter = wb.createCellStyle();
        horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
        for (int i = 0; i < columns.size(); i++) {
            XSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(columns.get(i));
            cell.setCellStyle(horizontalCenter);
        }
        int count = 0;
        try ( ResultSet results = conn.createStatement().executeQuery(sql)) {
            while (results.next()) {
                if (cancelled) {
                    updateLogs(message("Cancelled") + " " + filename);
                    return false;
                }
                writeExcel(conn, sheet, results, count);
                count++;
                if (verboseCheck.isSelected() && (count % 50 == 0)) {
                    updateLogs(message("Exported") + " " + count + ": " + filename);
                }
            }
        }
        for (int i = 0; i < columns.size(); i++) {
            sheet.autoSizeColumn(i);
        }
        try ( OutputStream fileOut = new FileOutputStream(file)) {
            wb.write(fileOut);
        }
        return true;
    } catch (Exception e) {
        updateLogs(e.toString());
        return false;
    }
}