java-使用HSSFWorkbook编辑excel文件

HSSF基本操作
HSSF表格案例

对模板excel进行修改然后导出，返回给前端文件路径。

	public Result createProformaInvoiceXls(Map<String, Object> params, HttpServletRequest request) {

		Result result = null; // 只设置几个基本的信息到result上更新前台
        try {
        	String tempFilePath = ExportPathConstants.EXCEL_PATH + File.separator + "proformaInvoiceTemp" + File.separator + "proformaInvoice.xls";
            final String generate_XLSFile_Dir = ExportPathConstants.EXCEL_PATH + File.separator + "ExportTempFileDir" + File.separator + "ExportTempXLSFileDir"
                    + File.separator;
            // 注意路径和sheet名时不要用特殊字符
            final String fileName = "ProformaInvoice__" + SapDateFormat.formatTimeStamp(new Date()) + ".xls";
			String fileSuffix = ".tmp";
			final String tempFile = fileName + fileSuffix;
            new Thread(() -> {
                try {

					List<Map<String, Object>> lrecns = (List<Map<String, Object>>)params.get("LRECNS");
        			ExportData exportData = getInvoiceInfo("PT_ZLRECD", lrecns);
        			File file = ExportFileUtil.getFile(generate_XLSFile_Dir, tempFile);
        			OutputStream fos = new FileOutputStream(file);
        			if(writePIXlsFile(exportData, fos, file, tempFilePath)) {
						if (file.isFile()) {
							file.renameTo(new File(generate_XLSFile_Dir + fileName));
						}
        			}
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }).start();
        	result =  new Result(Message.SUCCESS, new String[]{generate_XLSFile_Dir, fileName});
        } catch (Exception e) {
            e.printStackTrace();
            result = new Result(Message.FAILED, Message.PLEASE_CONTACT_ADMINISTRATOR + e.getMessage());
        }
        return result;
	}

	private boolean writePIXlsFile(ExportData exportData, OutputStream fos, File newFile, String tempFilePath) throws Exception{
		
		newFile = copyNewFile(fos, newFile, tempFilePath);
		InputStream is = null;
        HSSFWorkbook workbook = null;
        HSSFSheet sheet = null;
        is = new FileInputStream(newFile);
        workbook = new HSSFWorkbook(is);
        sheet = workbook.getSheet("PI");
        //创建样式
		HSSFCellStyle boderStyle = workbook.createCellStyle();
		boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		boderStyle.setBorderBottom(CellStyle.BORDER_THIN); 
		boderStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
		boderStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
		boderStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
		//创建明细
		HSSFRow dataRow = null;
		HSSFCell dataCell = null;
		Date now = new Date();
		sheet = workbook.getSheet("PI");
        dataRow = sheet.getRow(7);
        dataCell = dataRow.getCell(0);
        dataCell.setCellValue("B" + SapDateFormat.formatDate(now) + "A");
        
        workbook.write(fos);
		try {
			fos.close();
		} catch (Exception e) {}
		return true;
	}