Java POI 导出Excel 表格

       最近给客户做了一个绩效计提的功能，需要能够在后台预览Excel表格数据，并添加导出功能，本身个人对Excel表的一些操作并不是特别熟练，所以统计表的结构对我个人来说算是比较复杂的了，涉及到多次的跨行处理，跨列处理，并动态设置单元格对应的公式，以及一些特定的行或列设置不同的一个样式。先来给大家看一下几个绩效表中结果最简单的一个。下图是导出的Excel表格

         虽然之前也做过导出Excel表类似的功能，但是之前所做的都是动态生成没一行的数据，并没有涉及到跨行，跨列，动态设置单元格这些操作，在做的过程中，也百度很多poi 导出Excel表的一些案例，但是都是比较简单，或者比较片面的一些例子。所以就想着写一篇博客记录一下自己的感受，把自己碰到的一些问题给记录一下，也希望能给有同样业务需求的朋友一个参考。

        如何使用poi去导出Excel表格，网上有很多案例，相信大部分人看了以后都能够理解和引用的，那么如何使用poi及poi的一些常用方法，简单介绍一下，主要着重写一下碰到的问题，以及我个人的解决方法。

首先给大家看一下，读取Excel表模板及导出Excel表格的io流文件处理方法

@RequestMapping({ "exportExcel" })
	@Action( description = "导出excel方法",platformModelCode=LoanformUtil.riskManage,platformCode=LoanformUtil.platformCode, detail = "" , execOrder = ActionExecOrder.BEFORE , exectype = "" , ownermodel = SysAuditModelType.NULL )
	public void exportExcelTest(HttpServletRequest request, HttpServletResponse response) {
		try {
			// 月份
			String monthNumber = RequestUtil.getString(request, "monthNumber");
			String excelName = monthNumber+"提前结清表";
			//模板文件路径
			//String path = this.getClass().getClassLoader().getResource("/").getPath();
            String fileName  = SystemProperties.getValue("advanceModel");
			//String fileName  = "E://advanceModel.xlsx";
            FileInputStream inputStream = new FileInputStream(fileName);

			String curdateStr = DateFunc.getDefDateFormat(new Date());

			// 设置response参数，可以打开下载页面
			response.reset();
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			response.setHeader("Content-Disposition", "attachment;filename="+ new String(excelName.getBytes("GBK"), "iso-8859-1") + ".xlsx");
			
			QueryFilter filter = new QueryFilter();
			filter.addFilter("monthNumber", monthNumber);			
			
			//顺序要和Excel模板表的字段顺序一致
			String keys[] = { "departmentName", "managerName", "serialNumber", "customerName", "sx","loanMoney", "loanTerm"
					, "loanDays", "loanMonthRate", "loanYearRate", "loanFormalityRate", "loanYearFormalityRate", "loanYearComprehensiveRate"
					, "repayType", "yearLoanBalance", "loanSettleDate", "personCalCoeStandard","personCalRatio"
					, "personComprehensiveCalRatio"
					, "personEndCalMoney", "personEndCalBonus", "personEndCalMarketingCost", "remark"};
			
			//获取部门合计数据
			List<LoanPerformanceTotal> total = this.loanPerformanceTotalService.getBySqlKeyAll(filter, "getAdvanceDepartTotal");
			
    		//获取输出流
            ServletOutputStream os = response.getOutputStream();
			
            this.loanPerformanceAdvanceService.createXSSFWorkBook(total, keys, new XSSFWorkbook(inputStream), 6, "提前结清", monthNumber).write(os);

            os.flush();  
            os.close(); 
		} catch (Exception e) {
			logger.error("导出提前结清Excel表出错"+e.getMessage());
			e.printStackTrace();
		}
	}

因为表头比较复杂，懒得自己去画，所以我的处理方法是，直接使用客户给的模板的表头，通过读取模板的方法，然后再指定行开始插入数据，再进行导出，当然，也可以不用读取模板的方法。这个主要看个人。

一 、如何使用Jakarta POI，及简介

       Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成，其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF，在各种组件中目前只有用于操作Excel的HSSF相对成熟。官方主页http://poi.apache.org/index.html，API文档http://poi.apache.org/apidocs/index.html

（HSSF开头的针对于03版本的，XSSF开头的针对07版本）

jar包下载及引用

1、官方下载：http://poi.apache.org/download.html这里可以下载到它的最新版本和文档，目前最新版本是3.7，这里使用比较稳定的3.6版

2、将根目录下的poi-3.6-20091214.jar和Lib目录下三个通用包 commons-logging-1.1.jar junit-3.8.1.jar log4j-1.2.13.jar拷贝到项目的Lib下

3、使用思路  以03版本的为例，07版本一样（类以XSSF开头）

HSSF（用于操作Excel的组件）提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象，样式和格式，有以下几种常用的对象：

常用组件：

HSSFWorkbook     excel的文档对象

HSSFSheet            excel的表单

HSSFRow               excel的行

HSSFCell                excel的格子单元

HSSFFont               excel字体

样式：

HSSFCellStyle         cell样式

 

基本操作步骤：

首先，我们应该要知道的是，一个Excel文件对应一个workbook，一个workbook中有多个sheet组成，一个sheet是由多个行(row)和列(cell)组成。那么我们用poi要导出一个Excel表格

的正确顺序应该是：

a、用HSSFWorkbook打开或者创建“Excel文件对象”

b、用HSSFWorkbook对象返回或者创建Sheet对象

c、用Sheet对象返回行对象，用行对象得到Cell对象

d、对Cell对象读写。

e、将生成的HSSFWorkbook放入HttpServletResponse中响应到前端页面

 

二、常用方法，我的模板使用的是07版本的，所以我以XSSF*为例。

//根据名称读取工作簿 
XSSFSheet sheet = workbook.getSheet(modelName);  因为我是通过读取模板的方式所以会根据表的名称来读取对应的表格

workbook.getSheetAt(index)；当然大家也可以使用索引来获取对应的sheet对象。

 row1 = sheet.createRow(rowNum);  创建行

 cell1 = row1.createCell(j); 创建单元格

 cell1.setCellStyle(style); 设置单元格样式，参数为XSSFCellStyle 对象，以下的方法返回。

 sheet.addMergedRegion(new CellRangeAddress(mstartRow, rowNum-1, 1, 1));  跨行处理，四个参数方别为，开始行，结束行，开始列，结束列（下标都是从0 开始）

cell3.setCellValue(advance.getPersonEndCalBonusThree().doubleValue());  设置单元格的值
                         
 cell1.setCellFormula("T" + (rowNum+1) +"*0.7");   设置公式（因为我是读取模板的，所以字段对应的列是固定的，行是动态获取）

/**
 	 * 定义单元格内容样式
 	 * @param workbook
 	 * @return
 	 */
    public static XSSFCellStyle getXSSFStyle(XSSFWorkbook workbook) {  
        //设置字体;  
        XSSFFont font = workbook.createFont();  
        //设置字体大小;  
        font.setFontHeightInPoints((short) 10);  
        //设置字体名字;  
        font.setFontName("Courier New");  
        //font.setItalic(true);  
        //font.setStrikeout(true);  
        //设置样式;  
        XSSFCellStyle style = workbook.createCellStyle();  
        //设置底边框;  
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
        //设置底边框颜色;  
        style.setBottomBorderColor( new XSSFColor(Color.BLACK));  
        //设置左边框;  
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
        //设置左边框颜色;  
        style.setLeftBorderColor(new XSSFColor(Color.BLACK));  
        //设置右边框;  
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);  
        //设置右边框颜色;  
        style.setRightBorderColor(new XSSFColor(Color.BLACK));  
        //设置顶边框;  
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);  
        //设置顶边框颜色;  
        style.setTopBorderColor(new XSSFColor(Color.BLACK));  
        //在样式用应用设置的字体;  
        style.setFont(font);  
        //设置自动换行;  
        style.setWrapText(true);  
        //设置水平对齐的样式为居中对齐;  
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
        //设置垂直对齐的样式为居中对齐;  
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        
        //设置背景颜色
        style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		
		//小数点保留两位
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        return style;  
    }  

操作Excel对象思路，由大到小，也就是说，先有 new XSSFWorkbook(inputStream)对象，再有XSSFSheet对象，类推（先遍历行，再遍历单元格）。

三、碰到的问题

1、版本问题，07版本对应XSSF开头，03版本对应HSSF开头。

org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:133)

解决方法：使用对应的工具类就可以了。

2、合并单元格问题

在这里想说的是，只要有这些提示，代码处理肯定有问题（为什么说这句话，有时候，提示了，但是导出来的效果是正常的），因为这个问题，废了一点功夫才找到原因，原来是在做跨行处理的时候，没处理好，对其中的某个单元格进行了重复跨行处理，也就是冲突造成的，一个单元格对象只能处理一次

需要注意的问题，单元格跨行处理，比如说一个单元格需要跨3行，这种情况，不能是我就只sheet.createRow(rowNum) 一个 row对象，得创建相应的3个row对象，和cell对象。

因为任何处理跨行跨列问题，没有一个好的思路来处理是一个很炒蛋的问题，要么格式乱了，要么冲突了。

我个人解决这个问题的方法是，先简单的把每一行，每一个单元格遍历创建并设置对应的值，因为我知道特殊的行和单元格是需要做处理的，比如最开始的效果图 部门和客户经理的跨行处理，在遍历的时候我可以获取到开始行，结束行，开始列，结束列的，然后我把这些信息先放到一个list中，最后在遍历list来处理跨行跨列问题。当然这只是我个人的逻辑，因为我也没有想到更好的了，如果大家有更好的方法也可以分享。

3、获取不到sheet对象，或者是明明获取到sheet对象了，打了断点的时候，代码也能拿到数据，就是在导出后，没有数据显示。

XSSFSheet sheet = workbook.getSheet(modelName);  如何是读取模板的话，建议大家根据表名称来获取表对象，

如果是更加实用来获取workbook.getSheetAt(index)；得确保索引是对的，查看一下有没有隐藏表这些情况。

（但是得确保索引的对的，比如上图中有个正常结清的表，正常来说，索引是0，但是如果我之前有建其他表，那么正常结清表对应的索引就不是0了，也就是说，一般第一个表sheet1，第二个表sheet2,。。。。，对应的下标就是 0、1 、。。。。，但是如果你把sheet1给删除了，sheet2的下标还是1，所以如果你使用0来查找，是获取不对表对象的（这个点不太确定有没有这个问题，但是在做的过程确实碰到过这个问题，仅供参考））

4、设置公式的时候，代码错误

同样在设置公式的，也要防止冲突，也就是不能重复设置公式。

这种情况，可能直接把数据给搞丢了

以上就是个人在使用poi导出Excel表的一些感受了，其实除了重复跨行处理，重复设置单元格问题，其他都是一些比较细节的问题，那两个问题在百度找了挺长时间，都没有找到合适的方法，只能按着思路一遍一遍去捋，才找到问题的原因是因为冲突，最恶心的是这种情况，代码不会报错。

个人觉得做这个类似功能最重要还是一个正确的逻辑吧，比如说我都是先设置每行，每个单元格数据后，先定位需要特殊处理的行和单元格的下标，然后保存到一个list，最后再做统一的处理。

希望能给看到这边博客的同学一定的参考价值。