Apache POI组件操作Excel制作报表.doc

1、Apache POI 组件操作 Excel 制作报表原文来自 http:/ 的 POI 组件是 Java 操作 Microsoft Office 办公套件的强大 API，其中对Word， Excel 和 PowperPoint 都有支持，当然使用较多的还是 Excel，因为 Word 和PowerPoint 用程序动态操作的应用较少。那么本文就结合 POI 来介绍一下操作 Excel 的方法。Office 2007 的文件结构完全不同于 2003，所以对于两个版本的 Office 组件，POI 有不同的处理 API，分开使用即可。首先来说几个 Excel 的基本概念。对于一个 Excel 文件

2、，这称为一个工作簿（Workbook） ，打开 Excel 之后，在下方会有 sheet1/2/3 这样的选项卡，点击可以切换到不同的 sheet 中，这个 sheet 称作工作表。每个工作表就是我们编辑的区域，这是一张二维表，阿拉伯数字控制行数，从 1 开始，而程序中还是 0，类似数组和集合。字母控制列数，从 A 开始， Z 以后是两个字母控制。对于每一行，我们称为 Row，列就是Column，行列可以确定唯一的一个元素，那么就是单元格，称为 Cell。POI 组件可以方便的操纵这些元素，但初次接触 POI 可能会有畏惧心理，因为要对每个单元格进行设置，那么不管是用数组还是集合，从工作簿，工

3、作表，行下来的代码量都不会小，这是不能避免的，但是按照这个处理顺序走，就一定可以得到结果。有了这些基础的概念之后，我们就可以操作 Excel 了。先来看一下所需的依赖，因为涉及到 2007，就要额外加一些依赖。下面从读取 Excel 开始，首先建立一个 Excel 2003 以下版本的 xls 文件。设定几列来看。来存储学生信息的 Excel 表如下：这里的姓名，性别和班级是文本值，而年龄和成绩是数字值，这在设计对象和处理时要注意区分。那么可以如下设计这个对象：package org.ourpioneer.excel.bean;/* 学生信息*/public class Student pri

4、vate String name;private String gender;private int age;private String sclass;private int score;public Student() super();public Student(String name, String gender, int age, String sclass, int score) super();this.name = name;this.gender = gender;this.age = age;this.sclass = sclass;this.score = score;/

5、 省略了getter和setter方法Overridepublic String toString() return “Student age=“ + age + “, gender=“ + gender + “, name=“ + name+ “, sclass=“ + sclass + “, score=“ + score + “;提供一个有参数的构造方法，用于生成对象写入 Excel 文档。这个对象就能刻画 Excel文件中的数据了，下面就是写程序将 Excel 文件加载并处理，然后将内容读出，读取顺序是工作簿-工作表-行-单元格。这样一分析就很简单了。我们定义两个 Excel 文件，内

6、容相同，只是版本不同，分 2003 和 2007 来处理。创建工作簿时可以接收一个输入流对象，那么输入流对象可以从文件对象来生成，这样就可以继续进行了。取出工作表，取出行，遍历单元格，数据就拿到了。代码如下：package org.ourpioneer.excel;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import org.ap

7、ache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.ourpioneer.excel.bean.Student;/* POI读取 Excel示例，分2003和2007*/public class ReadExcel private static String xls2003 =

8、“C:student.xls“;/ private static String xlsx2007 = “C:student.xlsx“;/* 读取Excel2003的示例方法* param filePath*/public static List readFromXLS2003(String filePath) File excelFile = null;/ Excel文件对象InputStream is = null;/ 输入流对象String cellStr = null;/ 单元格，最终按字符串处理List studentList = new ArrayList();/ 返回封装数据的L

9、istStudent student = null;/ 每一个学生信息对象try excelFile = new File(filePath);is = new FileInputStream(excelFile);/ 获取文件输入流HSSFWorkbook workbook2003 = new HSSFWorkbook(is);/ 创建Excel2003文件对象HSSFSheet sheet = workbook2003.getSheetAt(0);/ 取出第一个工作表，索引是0/ 开始循环遍历行，表头不处理，从1开始for (int i = 1; i list = readFromXLS2

10、003(xls2003);for (Student student : list) System.out.println(student);long end = System.currentTimeMillis();System.out.println(end - start) + “ ms done!“);做几点说明，如果不处理表头，那么就从准备处理的行开始，而整个 sheet 对行的索引是从 0 开始的，而 Excel 中是 1，这点和数组/集合类似。对于单元格中的数字，默认按double 类型处理，所以只能字符串转 double，再取出 int 值。最后执行主函数，得到如下内容：这样就拿

11、到对象的 List 了，之后要持久到数据库或者直接做业务逻辑就随心所欲了。下面来看 2007 的处理，处理流程和 2003 是类似的，区别就是使用的对象，2003 中对象是HSSF*格式的，而 2007 是 XSSF*格式的。方法如下：public static List readFromXLSX2007(String filePath) File excelFile = null;/ Excel文件对象InputStream is = null;/ 输入流对象String cellStr = null;/ 单元格，最终按字符串处理List studentList = new ArrayLis

12、t();/ 返回封装数据的ListStudent student = null;/ 每一个学生信息对象try excelFile = new File(filePath);is = new FileInputStream(excelFile);/ 获取文件输入流XSSFWorkbook workbook2007 = new XSSFWorkbook(is);/ 创建Excel2003文件对象XSSFSheet sheet = workbook2007.getSheetAt(0);/ 取出第一个工作表，索引是0/ 开始循环遍历行，表头不处理，从1开始for (int i = 1; i stude

13、ntList = null;private static Student students = new Student4;/* 静态块初始化数据*/static studentList = new ArrayList();students0 = new Student(“张三“, “男“, 23, “一班“, 94);students1 = new Student(“李四“, “女“, 20, “一班“, 92);students2 = new Student(“王五“, “男“, 21, “一班“, 87);students3 = new Student(“赵六“, “女“, 22, “一班

14、“, 83);studentList.addAll(Arrays.asList(students);/* 创建2003文件的方法* param filePath*/public static void generateExcel2003(String filePath) / 先创建工作簿对象HSSFWorkbook workbook2003 = new HSSFWorkbook();/ 创建工作表对象并命名HSSFSheet sheet = workbook2003.createSheet(“学生信息统计表“);/ 遍历集合对象创建行和单元格for (int i = 0; i ();csl0

15、= new CruiseServiceLocation(“T001“, “北京市“ , “北京总部“, “bj“, “清华大学“ ,20);csl1 = new CruiseServiceLocation(“T001“, “北京市“ , “北京总部“, “bj“, “北京大学“ ,30);csl2 = new CruiseServiceLocation(“T001“, “北京市“ , “海淀经销商“, “bjhd“,“西直门“, 15);csl3 = new CruiseServiceLocation(“T001“, “北京市“ , “海淀经销商“, “bjhd“,“首都机场“, 50);cs

16、l4 = new CruiseServiceLocation(“T001“, “北京市“ , “海淀经销商“, “bjhd“,“中关村“, 23);csl5 = new CruiseServiceLocation(“T001“, “北京市“ , “东城经销商“, “bjdc“,“北京火车站“, 4);csl6 = new CruiseServiceLocation(“T001“, “北京市“ , “东城经销商“, “bjdc“,“北京西站“, 12);csl7 = new CruiseServiceLocation(“T001“, “辽宁省“ , “大连经销商“, “lndl“,“河口软件园“

17、, 15);csl8 = new CruiseServiceLocation(“T001“, “辽宁省“ , “大连经销商“, “lndl“,“七贤岭腾飞软件园“, 13);csl9 = new CruiseServiceLocation(“T001“, “辽宁省“ , “大连经销商“, “lndl“,“高新园区信达街“, 11);csl10 = new CruiseServiceLocation(“T001“, “辽宁省“, “大连第二经销商“, “lndl2“,“数码广场“, 8);csl11 = new CruiseServiceLocation(“T001“, “辽宁省“, “大连第二

18、经销商“, “lndl2“,“马栏广场“, 17);csl12 = new CruiseServiceLocation(“T001“, “辽宁省“, “大连第二经销商“, “lndl2“,“五一广场“, 12);csl13 = new CruiseServiceLocation(“T001“, “辽宁省“, “沈阳经销商“, “lnsy“,“沈阳故宫“, 16);csl14 = new CruiseServiceLocation(“T001“, “辽宁省“, “沈阳经销商“, “lnsy“,“沈阳北站“, 4);csl15 = new CruiseServiceLocation(“T001“,

19、 “吉林省“, “长春经销商“, “jlcc“,“吉林大学“, 4);csl16 = new CruiseServiceLocation(“T001“, “吉林省“, “长春经销商“, “jlcc“,“长春火车站“, 4);csl17 = new CruiseServiceLocation(“T002“, “黑龙江省“, “哈尔滨经销商“, “hljhrb“,“哈工大“, 12);csl18 = new CruiseServiceLocation(“T002“, “黑龙江省“, “齐齐哈尔经销商“,“hljqqhr“, “火车站“, 21);csl19 = new CruiseServiceL

20、ocation(“T003“, “河北省“, “石家庄经销商“, “hbsjz“,“火车站“, 4);csl20 = new CruiseServiceLocation(“, “, “, “, “, 0);/ 合并算法捕捉最后一行有问题，增补一行无效数据，计算时去除cruiseServiceLocationList.addAll(Arrays.asList(csl);注意在最后增补一条无效数据，因为算法的限制，读取最后一行比较时可能会将其略过，所以这样保证所有数据都能被正常读出。这个数据结构根据事业部代号，省份和经销商名称来区分各个元素。算法就是根据标识位的不同来区分是否该进行特殊处理了，这之

21、前数据要排好顺序，就可以分门别类进行了，来看一下合计算法：/ 合计量的计算CruiseServiceLocation cslTotal = null;List cslList = new ArrayList();/ 合并计算控制double totalDealer = 0;double totalProvince = 0;double totalDivision = 0;int locationNum = 0;/ 循环遍历Listfor (int i = 0; i indexComboProvice = new ArrayList();List indexComboDivision = new

22、 ArrayList();它们是用来计算合并数量的，最后用在跨行数量上，因为每出一条合计，整体跨行数就要增加，那么需要将这些数据记录到 List 中，方便处理。我们就引入了这些辅助变量。 先来看经销商合并算法：if (csl.getTotalDealer() != 0) row.createCell(0).setCellStyle(style);row.createCell(1).setCellStyle(style);XSSFCell t_dealer = row.createCell(2);t_dealer.setCellValue(“经销商合计“);t_dealer.setCellSty

23、le(biStyle);sheet.addMergedRegion(new CellRangeAddress(i + 1, i + 1, 2, 4);XSSFCell t_dealer_value = row.createCell(5);t_dealer_value.setCellValue(csl.getTotalDealer();t_dealer_value.setCellStyle(biStyle);sheet.addMergedRegion(new CellRangeAddress(i- csl.getLocationNum() + 1, i, 2, 2);sheet.addMerge

24、dRegion(new CellRangeAddress(i- csl.getLocationNum() + 1, i, 3, 3);这个就很简单了，因为不涉及到跨行，只是跨列而已，这都是预先已经设定好的，不难。而省份和经销商的跨行计算就略微复杂：if (csl.getTotalProvince() != 0) XSSFCell t_province = row.createCell(1);row.createCell(0).setCellStyle(style);row.createCell(3).setCellStyle(style);row.createCell(4).setCellSt

25、yle(style);t_province.setCellValue(“省份合计“);t_province.setCellStyle(biStyle);sheet.addMergedRegion(new CellRangeAddress(i + 1, i + 1, 1, 4);XSSFCell t_province_value = row.createCell(5);t_province_value.setCellValue(csl.getTotalProvince();t_province_value.setCellStyle(biStyle);indexComboProvice.add(i

26、);/ 合并行if (comboProvince = 0) sheet.addMergedRegion(new CellRangeAddress(1, i, 1, 1); else if (comboProvince = 1) sheet.addMergedRegion(new CellRangeAddress(indexComboProvice.get(comboProvince - 1)+ comboProvince + 1, i, 1, 1); else sheet.addMergedRegion(new CellRangeAddress(indexComboProvice.get(co

27、mboProvince - 1)+ comboProvince, i, 1, 1);comboProvince+;这里为了将所有单元格都加入样式，所以没有数据填充的单元格仅做样式处理即可。合并行的时候要看这是第几次合并，因为算法不同。第一次时比较简单，只需数出有第一个合计中有多少经销商即可。而后续的就需要记录上次合并出现的位置，然后再加上第二个经销商的数量，之后进行合并。那么事业部的算法也是如此。if (csl.getTotalDivision() != 0) XSSFCell t_division = row.createCell(0);row.createCell(1).setCellSt

28、yle(style);row.createCell(2).setCellStyle(style);row.createCell(3).setCellStyle(style);row.createCell(4).setCellStyle(style);t_division.setCellValue(“事业部合计“);t_division.setCellStyle(biStyle);sheet.addMergedRegion(new CellRangeAddress(i + 1, i + 1, 0, 4);XSSFCell t_division_value = row.createCell(5);

29、t_division_value.setCellValue(csl.getTotalDivision();t_division_value.setCellStyle(biStyle);indexComboDivision.add(i);/ 合并行if (comboDivision = 0) sheet.addMergedRegion(new CellRangeAddress(1, i, 0, 0); else if (comboDivision = 1) sheet.addMergedRegion(new CellRangeAddress(indexComboDivision.get(comb

30、oDivision - 1)+ comboDivision + 1, i, 0, 0); else sheet.addMergedRegion(new CellRangeAddress(indexComboDivision.get(comboDivision - 1)+ comboDivision, i, 0, 0);comboDivision+;最后生成文件，就得到了我们要的报表了。四、结合 Spring MVC，在 Web 应用程序中生成 Excel 文件并进行下载操作为什么选择 Spring MVC 框架，理由很简单，因为 Spring 的 MVC 框架对 Excel 视图的扩展使用的正

31、是 POI 组件，那么使用 Spring 来整合就非常方便了，当然其它的框架都有自己的处理办法，这个可以具体咨询其对视图扩展支持的配置方法。使用 Spring 对 Excel 的支持，需要扩展 MVC 的视图组件，就是要创建一个自定义的View，这个在后面会详细介绍，通过这个 view，就可以将 Excel 报表展现出来了，非常方便。基于 Spring MVC 的程序，那么首先要构建起来 Spring MVC 的环境，这个很简单，下面我们来看看如何搭建 Spring MVC 环境。我们使用 Spring 3.0.5 的库，但是配置方式不用注解，用传统的配置方式来说明。使用 Maven 管理项目

32、，引入必要的依赖，如下：并建立如下的项目结构：在 web.xml 描述符中，首先配置 Spring 配置文件的路径：contextConfigLocationclasspath:org/ourpioneer/spring/*.xml,classpath:org/ourpioneer/spring/*/*.xmlorg.springframework.web.context.ContextLoaderListener其次配置一个字符过滤器，使用 Spring 提供的就好，这样就不用过多考虑字符编码的问题了：EncodingFilterorg.springframework.web.filter.

33、CharacterEncodingFilterencodingUTF-8forceEncodingtrueEncodingFilter/*最后是 Spring MVC 的 ServletDispatcher 配置了：boardorg.springframework.web.servlet.DispatcherServletcontextConfigLocation/WEB-INF/Dispatcher-Servlet.xmlboard*.do我们配置了一个初始化文件，在 Servlet 加载时会加载这个配置文件，并使用*.do 风格的映射地址。在这个初始配置文件中，主要配置了映射和拦截器等信息

34、。内容如下：ExcelController其中扩展名拦截器是对生成 Excel 文件后的扩展名进行处理的，这个后面会说，下面就是配置映射，这里我们只有一个测试，那么就写一个映射即可，最后使用这个地址来测试，配置好映射信息后，那么就要配置其中的每个映射，那么来看看 ExcelController 的配置：这个也很简单，注入两个属性，一个 service 和一个 view。为什么要用 view，因为Spring MVC 已经和 POI 进行了整合，这两个 bean 分别配置一下即可，这里不再说明了。详见附件中的完整源码。下面来看看控制器：package org.ourpioneer.excel.w

35、eb.controller;import java.util.HashMap;import java.util.List;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.ourpioneer.excel.bean.CruiseServiceLocation;import org.ourpioneer.excel.service.ReportService;import org.ourpioneer.excel.web.views.Repor

36、tView;import org.springframework.web.servlet.ModelAndView;import org.springframework.web.servlet.mvc.AbstractController;/* Excel处理控制器*/public class ExcelController extends AbstractController private ReportView reportView;private ReportService reportService;public void setReportView(ReportView report

37、View) this.reportView = reportView;public void setReportService(ReportService reportService) this.reportService = reportService;Overrideprotected ModelAndView handleRequestInternal(HttpServletRequest request,HttpServletResponse response) throws Exception HashMap model = new HashMap();List cslList =

38、reportService.getCruiseLocationList();model.put(“cruiseServiceLocationList“, cslList);return new ModelAndView(reportView, model);控制器扩展了 AbstractController，覆盖了 handleRequestInternal 方法，这样就可以编写我们自定义的处理代码了。类开始是对 service 和 view 的注入，业务代码中从 Service 中获取了数据，然后放入 model 中，这个 model 是一个 HashMap，也是 Spring MVC 中传

39、递数据的纽带，使用方便。就像 Struts2 中的值栈一样，不过这个是显式使用，而值栈更像是隐式存在的数据桥梁。那么来看看 Service：package org.ourpioneer.excel.service;import java.util.Arrays;import java.util.List;import org.ourpioneer.excel.bean.CruiseServiceLocation;/* 报表数据业务类*/public class ReportService /* 获取数据*/public List getCruiseLocationList() CruiseSe

40、rviceLocation csl = new CruiseServiceLocation21;csl0 = new CruiseServiceLocation(“T001“, “北京市“, “北京总部“, “bj“, “清华大学“ , 20);csl1 = new CruiseServiceLocation(“T001“, “北京市“, “北京总部“, “bj“, “北京大学“ , 30);/ 中间的略去csl18 = new CruiseServiceLocation(“T002“, “黑龙江省“, “齐齐哈尔经销商“,“hljqqhr“, “火车站“, 21);csl19 = new C

41、ruiseServiceLocation(“T003“, “河北省“, “石家庄经销商“, “hbsjz“,“火车站“, 4);csl20 = new CruiseServiceLocation(“, “, “, “, “, 0);/ 合并算法捕捉最后一行有问题，增补一行无效数据，计算时去除return Arrays.asList(csl);这是很简单的类了，数据是我们准备好的，实际应用中就可以操作数据库了，这都没是由什么难度。下面来看一下展示 Excel 的 View：package org.ourpioneer.excel.web.views;import java.util.ArrayL

42、ist;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.p

43、oi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.util.CellRangeAddress;import org.ourpioneer.excel.bean.CruiseServic

44、eLocation;import org.springframework.web.servlet.view.document.AbstractExcelView;/* 报表的Excel视图*/public class ReportView extends AbstractExcelView Overrideprotected void buildExcelDocument(Map model,HSSFWorkbook workbook, HttpServletRequest request,HttpServletResponse response) throws Exception / 从mo

45、del中获取数据对象List cruiseServiceLocationList = (List) model.get(“cruiseServiceLocationList“);/ 创建工作表HSSFSheet sheet = workbook.createSheet(“巡航服务统计报表“);sheet.setColumnWidth(0, 32 * 80);sheet.setColumnWidth(1, 32 * 80);sheet.setColumnWidth(2, 32 * 140);sheet.setColumnWidth(3, 32 * 80);sheet.setColumnWidth

46、(4, 32 * 180);/ 单元格样式HSSFCellStyle style = workbook.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER);style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);style.setWrapText(true);style.setBorderBottom(HSSFCellStyle.BORDER_THIN);style.setBorderLeft(HSSFCellStyle.BORDER_THIN);style.set

47、BorderRight(HSSFCellStyle.BORDER_THIN);style.setBorderTop(HSSFCellStyle.BORDER_THIN);/ 加粗居中样式HSSFFont font = workbook.createFont();font.setBoldweight(Font.BOLDWEIGHT_BOLD);HSSFCellStyle biStyle = workbook.createCellStyle();biStyle.setFont(font);biStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);biStyl

48、e.setVerticalAlignment(CellStyle.VERTICAL_CENTER);biStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);biStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);biStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);biStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);/ 制作表头HSSFRow header = sheet.createRow(0);HSSFCell d

49、ivisionHeader = header.createCell(0);divisionHeader.setCellValue(“事业部“ );divisionHeader.setCellStyle(style);HSSFCell provinceHeader = header.createCell(1);provinceHeader.setCellValue(“省份“ );provinceHeader.setCellStyle(style);HSSFCell dealerNameHeader = header.createCell(2);dealerNameHeader.setCellValue(“经销商“);dealerN