ApachePOI组件操作Excel制作报表.doc

Apache POI组件操作Excel制作报表原文来自/blog/845035，本文只为方便阅读，请支持原作者。一、简单的文件读写操作Apache的POI组件是Java操作Microsoft Office办公套件的强大API，其中对Word，Excel和PowperPoint都有支持，当然使用较多的还是Excel，因为Word和PowerPoint用程序动态操作的应用较少。那么本文就结合POI来介绍一下操作Excel的方法。Office 2007的文件结构完全不同于2003，所以对于两个版本的Office组件，POI有不同的处理API，分开使用即可。首先来说几个Excel的基本概念。对于一个Excel文件，这称为一个工作簿（Workbook），打开Excel之后，在下方会有sheet1/2/3这样的选项卡，点击可以切换到不同的sheet中，这个sheet称作工作表。每个工作表就是我们编辑的区域，这是一张二维表，阿拉伯数字控制行数，从1开始，而程序中还是0，类似数组和集合。字母控制列数，从A开始，Z以后是两个字母控制。对于每一行，我们称为Row，列就是Column，行列可以确定唯一的一个元素，那么就是单元格，称为Cell。POI组件可以方便的操纵这些元素，但初次接触POI可能会有畏惧心理，因为要对每个单元格进行设置，那么不管是用数组还是集合，从工作簿，工作表，行下来的代码量都不会小，这是不能避免的，但是按照这个处理顺序走，就一定可以得到结果。有了这些基础的概念之后，我们就可以操作Excel了。先来看一下所需的依赖，因为涉及到2007，就要额外加一些依赖。下面从读取Excel开始，首先建立一个Excel 2003以下版本的xls文件。设定几列来看。来存储学生信息的Excel表如下：这里的姓名，性别和班级是文本值，而年龄和成绩是数字值，这在设计对象和处理时要注意区分。那么可以如下设计这个对象：package org.ourpioneer.excel.bean;/* * 学生信息 */public class Student private 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(); = name;this.gender = gender;this.age = age;this.sclass = sclass;this.score = score;/ 省略了getter和setter方法Overridepublic String toString() return Student age= + age + , gender= + gender + , name= + name+ , sclass= + sclass + , score= + score + ;提供一个有参数的构造方法，用于生成对象写入Excel文档。这个对象就能刻画Excel文件中的数据了，下面就是写程序将Excel文件加载并处理，然后将内容读出，读取顺序是工作簿-工作表-行-单元格。这样一分析就很简单了。我们定义两个Excel文件，内容相同，只是版本不同，分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.apache.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 = 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();/ 返回封装数据的ListStudent 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 = sheet.getLastRowNum(); i+) student = new Student();/ 实例化Student对象HSSFRow row = sheet.getRow(i);/ 获取行对象if (row = null) / 如果为空，不处理continue;/ 循环遍历单元格for (int j = 0; j row.getLastCellNum(); j+) HSSFCell cell = row.getCell(j);/ 获取单元格对象if (cell = null) / 单元格为空设置cellStr为空串cellStr = ; else if (cell.getCellType() = HSSFCell.CELL_TYPE_BOOLEAN) / 对布尔值的处理cellStr = String.valueOf(cell.getBooleanCellValue(); else if (cell.getCellType() = HSSFCell.CELL_TYPE_NUMERIC) / 对数字值的处理cellStr = cell.getNumericCellValue() + ; else / 其余按照字符串处理cellStr = cell.getStringCellValue();/ 下面按照数据出现位置封装到bean中if (j = 0) student.setName(cellStr); else if (j = 1) student.setGender(cellStr); else if (j = 2) student.setAge(new Double(cellStr).intValue(); else if (j = 3) student.setSclass(cellStr); else student.setScore(new Double(cellStr).intValue();studentList.add(student);/ 数据装入List catch (IOException e) e.printStackTrace(); finally / 关闭文件流if (is != null) try is.close(); catch (IOException e) e.printStackTrace();return studentList;/* * 主函数 */public static void main(String args) long start = System.currentTimeMillis();List list = readFromXLS2003(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值。最后执行主函数，得到如下内容：这样就拿到对象的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 ArrayList();/ 返回封装数据的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 = sheet.getLastRowNum(); i+) student = new Student();/ 实例化Student对象XSSFRow row = sheet.getRow(i);/ 获取行对象if (row = null) / 如果为空，不处理continue;/ 循环遍历单元格for (int j = 0; j row.getLastCellNum(); j+) XSSFCell cell = row.getCell(j);/ 获取单元格对象if (cell = null) / 单元格为空设置cellStr为空串cellStr = ; else if (cell.getCellType() = HSSFCell.CELL_TYPE_BOOLEAN) / 对布尔值的处理cellStr = String.valueOf(cell.getBooleanCellValue(); else if (cell.getCellType() = HSSFCell.CELL_TYPE_NUMERIC) / 对数字值的处理cellStr = cell.getNumericCellValue() + ; else / 其余按照字符串处理cellStr = cell.getStringCellValue();/ 下面按照数据出现位置封装到bean中if (j = 0) student.setName(cellStr); else if (j = 1) student.setGender(cellStr); else if (j = 2) student.setAge(new Double(cellStr).intValue(); else if (j = 3) student.setSclass(cellStr); else student.setScore(new Double(cellStr).intValue();studentList.add(student);/ 数据装入List catch (IOException e) e.printStackTrace(); finally / 关闭文件流if (is != null) try is.close(); catch (IOException e) e.printStackTrace();return studentList;再次运行主函数，我们得到如下输出：可以看出，对2007的处理时间明显增长，还是2003版本效率更好，不过在使用Office组件时2007更便捷，而处理2003的程序效率更好。如何使用二者？根据程序业务来综合决定，看看牺牲掉哪部分。下面来做简单的文件写入，也就是准备输入写入Excel文件，为了演示，直接创建对象，而实际应用中数据可以是来自数据库的。写入文件就是文件解析的逆过程。但POI的组件不是从单元格开始创建文件的，还是从工作簿开始创建，进而创建工作表，行和单元格，最终将整个工作簿写入文件，完成操作。我们来看具体写法。package org.ourpioneer.excel;import java.io.File;import java.io.FileOutputStream;import java.util.ArrayList;import java.util.Arrays;import java.util.List;import org.apache.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;/* * 生成Excel示例，2003和2007 */public class GenerateExcel private static String xls2003 = C:student.xls;/ private static String xlsx2007 = C:student.xlsx;private static List studentList = 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, 一班, 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 studentList.size(); i+) / 取出Student对象Student student = studentList.get(i);/ 创建行HSSFRow row = sheet.createRow(i);/ 开始创建单元格并赋值HSSFCell nameCell = row.createCell(0);nameCell.setCellValue(student.getName();HSSFCell genderCell = row.createCell(1);genderCell.setCellValue(student.getGender();HSSFCell ageCell = row.createCell(2);ageCell.setCellValue(student.getAge();HSSFCell sclassCell = row.createCell(3);sclassCell.setCellValue(student.getSclass();HSSFCell scoreCell = row.createCell(4);scoreCell.setCellValue(student.getScore();/ 生成文件File file = new File(filePath);FileOutputStream fos = null;try fos = new FileOutputStream(file);workbook2003.write(fos); catch (Exception e) e.printStackTrace(); finally if (fos != null) try fos.close(); catch (Exception e) e.printStackTrace();/* * 主函数 */public static void main(String args) long start = System.currentTimeMillis();generateExcel2003(xls2003);long end = System.currentTimeMillis();System.out.println(end - start) + ms done!);这样就生成了2003版Excel文件，只是最简单的操作，并没有涉及到单元格格式等操作，而2007的方法就是改改对象的名称，很简单，这里不再贴出了。二、单元格格式的设置现在来看看Excel的基本设置问题，以2007为例，先从工作簿来说，设置列宽，因为生成表格列应该固定，而行是遍历生成的，所以可以在工作簿级别来设置列宽，那么可以如下来进行。首先是创建工作簿和工作表了：/ 创建Excel2007工作簿对象XSSFWorkbook workbook2007 = new XSSFWorkbook();/ 创建工作表对象并命名XSSFSheet sheet = workbook2007.createSheet(学生信息统计表);/ 设置行列的默认宽度和高度sheet.setColumnWidth(0, 32 * 80);/ 对A列设置宽度为80像素sheet.setColumnWidth(1, 32 * 80);sheet.setColumnWidth(2, 32 * 80);sheet.setColumnWidth(3, 32 * 80);sheet.setColumnWidth(4, 32 * 80);在这之前要先创建一个工作表sheet，然后就可以对每列设置列宽了。而行高一般针对不同的行有不同的设置，比如表头行，合计行，数据行等，那么分别设置会比较好。设置完列宽和行高，剩下就是对单元格的设置，比如居中，边框，字体等。设置好样式后将样式应用于所需要的单元格，就得到了整体的效果，比如：/ 创建样式XSSFFont font = workbook2007.createFont();XSSFCellStyle headerStyle = workbook2007.createCellStyle();/ 设置垂直居中headerStyle.setAlignment(HorizontalAlignment.CENTER);headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);/ 设置边框headerStyle.setBorderTop(BorderStyle.THIN);headerStyle.setBorderBottom(BorderStyle.THIN);headerStyle.setBorderLeft(BorderStyle.THIN);headerStyle.setBorderRight(BorderStyle.THIN);/ 字体加粗font.setBold(true);/ 设置长文本自动换行headerStyle.setWrapText(true);headerStyle.setFont(font);XSSFCellStyle cellStyle = workbook2007.createCellStyle();/ 设置垂直居中cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);/ 设置边框cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);/ 设置长文本自动换行cellStyle.setWrapText(true);这里我们定义的是表头的样式，其中垂直居中时的设置要设置两次，而且方法名不同，要注意。边框就很简单了，上下左右设置四次，字体加粗就是布尔值设定了，之后是文本自动换行，意思就是在固定单元格长度不变时是否自动折行。然后将字体样式加入到单元格样式中即可。下面针对之前的例子设计表头，如下：/ 创建表头XSSFRow headerRow = sheet.createRow(0);headerRow.setHeightInPoints(25f);/ 设置行高度XSSFCell nameHeader = headerRow.createCell(0);nameHeader.setCellValue(姓名);nameHeader.setCellStyle(headerStyle);XSSFCell genderHeader = headerRow.createCell(1);genderHeader.setCellValue(性别);genderHeader.setCellStyle(headerStyle);XSSFCell ageHeader = headerRow.createCell(2);ageHeader.setCellValue(年龄);ageHeader.setCellStyle(headerStyle);XSSFCell classHeader = headerRow.createCell(3);classHeader.setCellValue(班级);classHeader.setCellStyle(headerStyle);XSSFCell scoreHeader = headerRow.createCell(4);scoreHeader.setCellValue(成绩);scoreHeader.setCellStyle(headerStyle);这里的代码会有大段的重复，因为都是一个套路下来的，最后别忘加入样式就行了，这样表格头部就做好了。剩下的是遍历数据出行。for (int i = 0; i studentList.size(); i+) XSSFRow row = sheet.createRow(i + 1);row.setHeightInPoints(20f);Student student = studentList.get(i);XSSFCell nameCell = row.createCell(0);nameCell.setCellValue(student.getName();nameCell.setCellStyle(cellStyle);XSSFCell genderCell = row.createCell(1);genderCell.setCellValue(student.getGender();genderCell.setCellStyle(cellStyle);XSSFCell ageCell = row.createCell(2);ageCell.setCellValue(student.getAge();ageCell.setCellStyle(cellStyle);XSSFCell classCell = row.createCell(3);classCell.setCellValue(student.getSclass();classCell.setCellStyle(cellStyle);XSSFCell scoreCell = row.createCell(4);scoreCell.setCellValue(student.getScore();scoreCell.setCellStyle(cellStyle);用for循环就可以完成任务，需要注意的是循环变量仍然从0开始走，这是为了遍历集合的方便，而创建行就是从i+1开始走了，因为0行是表头，已经占用了。下面就是先设定行高，之后开始取值，赋值，设定格式，结构也很统一。完成遍历后，来看看合并，Excel中的合并单元格也是很常用的操作，比如本例中，将班级都设置成一班后，那么就想让班级这列合并，该如何操作呢？/ 合并班级sheet.addMergedRegion(new CellRangeAddress(1, 4, 3, 3);这是对sheet级别的操作，因为是在sheet上合并单元格，就是加一个合并的区域，这个区域接收四个参数，就是开始的行，结束的行，开始的列，结束的列，这个位置一般比较确定，或者用循环变量计算得出。班级，从第二行开始，第五行结束，对应索引是1，4，而列不变，就是在列上合并，就都是3即可。至此我们已经将数据遍历得出生成了Excel表格，设置了单元格样式也进行了合并，那么还有合计没有说，用合计可以直接设置公式，也可以程序计算好后直接填充，后者一般用于比较复杂的报表，就不用在POI中设置合并的位置，减少复杂度。结合本例，我们来计算平均年龄和成绩之和。如下：/ 数据分析行int dadaRowNum = sheet.getLastRowNum();/ 获取已有的行数，加1再出新行XSSFRow totalRow = sheet.createRow(dadaRowNum + 1);totalRow.setHeightInPoints(25f);XSSFCell analyticsCell = totalRow.createCell(0);analyticsCell.setCellValue(数据分析);analyticsCell.setCellStyle(headerStyle);XSSFCell avgAgeCell = totalRow.createCell(1);avgAgeCell.setCellValue(平均年龄);avgAgeCell.setCellStyle(headerStyle);XSSFCell avgAgeValueCell = totalRow.createCell(2);avgAgeValueCell.setCellStyle(headerStyle);avgAgeValueCell.setCellFormula(AVERAGE(C2:C + (dadaRowNum + 1) + );XSSFCell sumScoreCell = totalRow.createCell(3);sumScoreCell.setCellValue(总成绩);sumScoreCell.setCellStyle(headerStyle);XSSFCell sumScoreValueCell = totalRow.createCell(4);sumScoreValueCell.setCellStyle(headerStyle);sumScoreValueCell.setCellFormula(SUM(E2:E + (dadaRowNum + 1) + );这里使用Excel函数的时候我们已经知道数据所在的位置，就直接使用了单元格的代号进行运算了，要注意使用公式时的方法中不用写=号，POI会自动为我们添加进去，这里就直接写公式的内容即可。在实际中可能会有动态计算的任务，那么就根据业务复杂度来选择是用Excel公式进行运算还是程序运算好后直接赋值显示。最后是生成文件的步骤，这已经介绍过了，都很简单：/ 生成文件File file = new File(filePath);OutputStream os = null;try os = new FileOutputStream(file);workbook2007.write(os); catch (IOException e) e.printStackTrace(); finally if (os != null) try os.close(); catch (IOException e) 最后是执行测试了，写个主函数来运行。将以上所有内容封装在静态方法generateExcel2007(String filePath)中，执行即可得到生成的Excel报表了，我们得到了这样的一个报表：这就是最终得到的结果了。POI操作Excel的基本设置就基本涵盖了，剩下的就是灵活运用，生成符合自己需求的报表了。三、复杂报表的分析与设计本篇就来详细分析一下复杂报表的分析与设计问题，并用POI通过程序来生成Excel报表。首先说一点文档相关内容。使用POI组件可以生成Office文档，而Office文档也有一些属性，比如作者，分类，公司等信息。我们若通过程序生成时，这一步就直接略过了，但有时我们会需要这些信息，要写入一些文档信息，那么该如何实现呢？我们分2003和2007两个版本说明，因为操作是不太一样的。看下面的代码：/ 设置核心属性POIXMLProperties.CoreProperties props = workbook2007.getProperties().getCoreProperties();props.setCreator(Nanlei);props.setCategory(POI程序测试);props.setTitle(学生信息表);/ 设置扩展属性POIXMLProperties.ExtendedProperties extProps = workbook2007.getProperties().getExtendedProperties();/ 设置自定义属性POIXMLProperties.CustomProperties customProps = workbook2007.getProperties().getCustomProperties();生成2007的Excel时，只需上述步骤便可加入我们需要的属性了，具体的属性含义可以参考官方文档，这里仅仅添加作者，分类和标题，生成Excel文档后，我们可以查看到入校内容：那么这里就是我们设置的一些信息了。而对于2003，则需要如下的步骤：/ 创建工作簿对象HSSFWorkbook workbook2003 = new HSSFWorkbook();workbook2003.createInformationProperties();SummaryInformation si = workbook2003.getSummaryInformation();si.setAuthor(Nanlei);si.setTitle(学生信息表);si.setComments(POI程序测试);DocumentSummaryInformation dsi = workbook2003.getDocumentSummaryInformation();dsi.setCompany(Roiland);要注意的是第二行，必须执行createInformationProperties()方法，之后才可以设置属性，这和2007的做法是不同的。这里只是给出示例，就不深入讨论每个设置项了。回头来看报表。中国式的复杂报表基本上是合计，合计再合计，就是数值分析到一个阶段后出一次合计，这个阶段可以按照业务的不同元素来划分。本例是根据经销商，省份最终到达事业部。那么设计数据库时就要唯一区分开这些元素，根据这些标识来实现划分，合并等，首先来准备一些数据。static cruiseServiceLocationList = new ArrayList();csl0 = new CruiseServiceLocation(T001, 北京市, 北京总部, bj, 清华大学,20);csl1 = new CruiseServiceLocation(T001, 北京市, 北京总部, bj, 北京大学,30);csl2 = new CruiseServiceLocation(T001, 北京市, 海淀经销商, bjhd,西直门, 15);csl3 = new CruiseServiceLocation(T001, 北京市, 海淀经销商, bjhd,首都机场, 50);csl4 = new CruiseServiceLocation(T001, 北京市, 海淀经销商, bjhd,中关村, 23);csl5 = new CruiseServiceLocation(T001, 北京市, 东城经销商, bjdc,北京火车站, 4);csl6 = new CruiseServiceLocation(T001, 北京市, 东城经销商, bjdc,北京西站, 12);csl7 = new CruiseServiceLocation(T001, 辽宁省, 大连经销商, lndl,河口软件园, 15);csl8 = new CruiseServiceLocation(T001, 辽宁省, 大连经销商, lndl,七贤岭腾飞软件园, 13);csl9 = new CruiseServiceLocation(T001, 辽宁省, 大连经销商, lndl,高新园区信达街, 11);csl10 = new CruiseServiceLocation(T001, 辽宁省, 大连第二经销商, lndl2,数码广场, 8);csl11 = new CruiseServiceLocation(T001, 辽宁省, 大连第二经销商, lndl2,马栏广场, 17);csl12 = new CruiseServiceLocation(T001, 辽宁省, 大连第二经销商, lndl2,五一广场, 12);csl13 = new CruiseSer