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表如下:
这里的姓名,性别和班级是文本值,而年龄和成绩是数字值,这在设计对象和处理时要注意区分。那么可以如下设计这个对象:
Java代码
- package org.ourpioneer.excel.bean;
- /**
- * 学生信息
- *
- * @author Nanlei
- *
- */
- 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();
- this.name = name;
- this.gender = gender;
- this.age = age;
- this.sclass = sclass;
- this.score = score;
- }
- //省略了getter和setter方法
- @Override
- public String toString() {
- return "Student [age=" + age + ", gender=" + gender + ", name=" + name
- + ", sclass=" + sclass + ", score=" + score + "]";
- }
- }
提供一个有参数的构造方法,用于生成对象写入Excel文档。这个对象就能刻画Excel文件中的数据了,下面就是写程序将Excel文件加载并处理,然后将内容读出,读取顺序是工作簿->工作表->行->单元格。这样一分析就很简单了。我们定义两个Excel文件,内容相同,只是版本不同,分2003和2007来处理。
创建工作簿时可以接收一个输入流对象,那么输入流对象可以从文件对象来生成,这样就可以继续进行了。取出工作表,取出行,遍历单元格,数据就拿到了。代码如下:
Java代码
- 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
- *
- * @author Nanlei
- *
- */
- public class ReadExcel {
- private static String xls2003 = "C:\\student.xls";
- private static String xlsx2007 = "C:\\student.xlsx";
- /**
- * 读取Excel2003的示例方法
- *
- * @param filePath
- * @return
- */
- private static List<Student> readFromXLS2003(String filePath) {
- File excelFile = null;// Excel文件对象
- InputStream is = null;// 输入流对象
- String cellStr = null;// 单元格,最终按字符串处理
- List<Student> studentList = new ArrayList<Student>();// 返回封装数据的List
- Student 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;
- }
- /**
- * 主函数
- *
- * @param args
- */
- public static void main(String[] args) {
- long start = System.currentTimeMillis();
- List<Student> 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*格式的。方法如下:
Java代码
- public static List<Student> readFromXLSX2007(String filePath) {
- File excelFile = null;// Excel文件对象
- InputStream is = null;// 输入流对象
- String cellStr = null;// 单元格,最终按字符串处理
- List<Student> studentList = new ArrayList<Student>();// 返回封装数据的List
- Student 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的组件不是从单元格开始创建文件的,还是从工作簿开始创建,进而创建工作表,行和单元格,最终将整个工作簿写入文件,完成操作。我们来看具体写法。
Java代码
- 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
- *
- * @author Nanlei
- *
- */
- public class GenerateExcel {
- private static String xls2003 = "C:\\student.xls";
- private static String xlsx2007 = "C:\\student.xlsx";
- private static List<Student> studentList = null;
- private static Student[] students = new Student[4];
- /**
- * 静态块初始化数据
- */
- static {
- studentList = new ArrayList<Student>();
- students[0] = new Student("张三", "男", 23, "一班", 94);
- students[1] = new Student("李四", "女", 20, "一班", 92);
- students[2] = new Student("王五", "男", 21, "一班", 87);
- students[3] = 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();
- }
- }
- }
- }
- /**
- * 主函数
- *
- * @param args
- */
- 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为例,先从工作簿来说,设置列宽,因为生成表格列应该固定,而行是遍历生成的,所以可以在工作簿级别来设置列宽,那么可以如下来进行。
首先是创建工作簿和工作表了:
Java代码
- // 创建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,然后就可以对每列设置列宽了。而行高一般针对不同的行有不同的设置,比如表头行,合计行,数据行等,那么分别设置会比较好。设置完列宽和行高,剩下就是对单元格的设置,比如居中,边框,字体等。设置好样式后将样式应用于所需要的单元格,就得到了整体的效果,比如:
Java代码
- // 创建样式
- 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);
这里我们定义的是表头的样式,其中垂直居中时的设置要设置两次,而且方法名不同,要注意。边框就很简单了,上下左右设置四次,字体加粗就是布尔值设定了,之后是文本自动换行,意思就是在固定单元格长度不变时是否自动折行。然后将字体样式加入到单元格样式中即可。
下面针对之前的例子设计表头,如下:
Java代码
- // 创建表头
- 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);
这里的代码会有大段的重复,因为都是一个套路下来的,最后别忘加入样式就行了,这样表格头部就做好了。剩下的是遍历数据出行。
Java代码
- 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中的合并单元格也是很常用的操作,比如本例中,将班级都设置成一班后,那么就想让班级这列合并,该如何操作呢?
Java代码
- // 合并班级
- sheet.addMergedRegion(new CellRangeAddress(1, 4, 3, 3));
这是对sheet级别的操作,因为是在sheet上合并单元格,就是加一个合并的区域,这个区域接收四个参数,就是开始的行,结束的行,开始的列,结束的列,这个位置一般比较确定,或者用循环变量计算得出。班级,从第二行开始,第五行结束,对应索引是1,4,而列不变,就是在列上合并,就都是3即可。
至此我们已经将数据遍历得出生成了Excel表格,设置了单元格样式也进行了合并,那么还有合计没有说,用合计可以直接设置公式,也可以程序计算好后直接填充,后者一般用于比较复杂的报表,就不用在POI中设置合并的位置,减少复杂度。结合本例,我们来计算平均年龄和成绩之和。如下:
Java代码
- // 数据分析行
- int dadaRowNum = sheet.getLastRowNum();
- XSSFRow totalRow = sheet.createRow(dadaRowNum + 1);// 获取已有的行数,加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公式进行运算还是程序运算好后直接赋值显示。
最后是生成文件的步骤,这已经介绍过了,都很简单:
Java代码
- // 生成文件
- 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) {
- }
- }
- }
最后是执行测试了,写个主函数来运行:
Java代码
- public static void main(String[] args) {
- long start = System.currentTimeMillis();
- generateExcel2007(xlsx2007);
- long end = System.currentTimeMillis();
- System.out.println((end - start) + " ms done!");
- }
将以上所有内容封装在静态方法generateExcel2007(String filePath)中,执行即可得到生成的Excel报表了,我们得到了这样的一个报表:
这就是最终得到的结果了。POI操作Excel的基本设置就基本涵盖了,剩下的就是灵活运用,生成符合自己需求的报表了。
下一篇
将结合一个具体实例来介绍复杂报表的分析和制作,完成如下图所示的报表: