POI实战-java开发excel详解之复杂写入

3.复杂写入

3.1 复杂写入

POI复杂写出主要是同复杂读取一样，在实际开发中，从数据库中读取字符、数字、日期各种类型数据，并通过相应的计算公式写出到Excel中。

这次写出的Excel，不重新创建，而采用Excel模板（图12）形式，将数据从数据库（图13）中读出，并通过公式方式计算每个学生的总成绩、平均分，最后写入模板。

图12

 

图13

代码片段：

[java]  view plain copy

1. public static HSSFWorkbook write(InputStream inputStream) throws IOException, ClassNotFoundException{  
2.         Class clazz = Class.forName(Student.class.getName());  
3.         Field[] fields = clazz.getDeclaredFields();  
4.           
5.         //初始一个workbook  
6.         HSSFWorkbook workbook = new HSSFWorkbook(inputStream);  
7.         List<Student> list = Conn.getData();  
8.         //创建一个sheet  
9.         HSSFSheet sheet = workbook.getSheetAt(0);  
10.             //创建多行,从列名下一行开始创建  
11.             for(int rowIndex = 1; rowIndex <= list.size(); rowIndex++){  
12.                 HSSFRow row = sheet.getRow(rowIndex);  
13.                 if(row == null){  
14.                     row = sheet.createRow(rowIndex);  
15.                 }  
16.                 Student student = list.get(rowIndex-1);  
17.                 //创建多列(不包括ID列)  
18.                 for(int cellnum = 0; cellnum < fields.length-1; cellnum++){  
19.                     HSSFCell cell = row.getCell(cellnum);  
20.                     if(cell == null){  
21.                         cell = row.createCell(cellnum);  
22.                     }  
23.                     switch (cellnum) {  
24.                     case 0:  
25.                         cell.setCellValue(new HSSFRichTextString(student.getName()));  
26.                         break;  
27.                     case 1:  
28.                         cell.setCellValue(new HSSFRichTextString(student.getNo()));  
29.                         break;  
30.                     case 2:  
31.                         cell.setCellValue(new HSSFRichTextString(student.getNativePlace()));  
32.                         break;  
33.                     case 3:  
34.                         cell.setCellValue(new HSSFRichTextString(student.getEdu()));  
35.                         break;  
36.                     case 4:  
37.                         cell.setCellValue(student.getYear());  
38.                         break;  
39.                     case 5:  
40.                         cell.setCellValue(student.getMath());  
41.                         break;  
42.                     case 6:  
43.                         cell.setCellValue(student.getChinese());  
44.                         break;  
45.                     case 7:  
46.                         cell.setCellValue(student.getEnglish());  
47.                         break;  
48.                     case 8:  
49.                         cell.setCellValue(student.getScience());  
50.                         break;  
51.                     case 9:  
52.                         cell.setCellFormula("F"+(rowIndex+1)+"+G"+(rowIndex+1)+"+H"+(rowIndex+1)+"+I"+(rowIndex+1));//写入总成绩公式  
53.                         break;  
54.                     case 10:  
55.                         cell.setCellFormula("AVERAGE(F"+(rowIndex+1)+":I"+(rowIndex+1)+")"); //写入平均成绩公式  
56.                         break;  
57.                     case 11:  
58.                         cell.setCellValue(student.isCity());  
59.                         break;  
60.                     case 12:  
61.                         cell.setCellValue(new HSSFRichTextString(formatDate(student.getSchoolDate())));  
62.                         break;  
63.                     case 13:  
64.                         cell.setCellValue(new HSSFRichTextString(formatDate(student.getBirth())));  
65.                         break;  
66.                     }  
67.                 }  
68.             }  
69.         return workbook;  
70.     }  
71.       
72.     public static String formatDate(Date date){  
73.         SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");  
74.         return dateFormat.format(date);  
75.     }  
76.   
77. Main方法修改部分：  
78. public static void main(String[] args) {  
79.         OutputStream outputStream = null;  
80.         try {  
81.             //读取模板  
82.             InputStream inputStream = new FileInputStream(new File("E:\\helloPOI.xls"));  
83.             HSSFWorkbook workbook = write(inputStream);  
84.             outputStream = new FileOutputStream(new File("E:\\helloPOI.xls"));  
85.             workbook.write(outputStream);  
86. 略.........  

依赖类修改部分：

 

[java]  view plain copy

1. public static List<Student> getData(){  
2.         List<Student> list = new ArrayList<Student>();  
3.         Connection conn = getConn();  
4.         String sql = "SELECT * FROM tpoi_vintage t";  
5.         try {  
6.             conn.setAutoCommit(false);  
7.             PreparedStatement ps = conn.prepareStatement(sql);  
8.             ResultSet rs = ps.executeQuery();  
9.             while(rs.next()){  
10.                 Integer id = rs.getInt("id");  
11.                 String name = rs.getString("name");  
12.                 String no = rs.getString("no");  
13.                 String nativePlace = rs.getString("native");  
14.                 String edu = rs.getString("edu");  
15.                 Integer year = rs.getInt("YEAR");  
16.                 Integer math = rs.getInt("MATH");  
17.                 Integer chinese = rs.getInt("CHINESE");  
18.                 Integer english = rs.getInt("ENGLISH");  
19.                 Integer science = rs.getInt("SCIENCE");  
20.                 Integer isCity = rs.getInt("ISCITY");  
21.                 Date schoolDate = rs.getDate("SCHOOLDATE");  
22.                 Date birth = rs.getDate("BIRTH");  
23.                   
24.                 Student student = new Student();  
25.                 student.setId(id);  
26.                 student.setName(name);  
27.                 student.setNo(no);  
28.                 student.setNativePlace(nativePlace);  
29.                 student.setEdu(edu);  
30.                 student.setYear(year);  
31.                 student.setMath(math);  
32.                 student.setChinese(chinese);  
33.                 student.setEnglish(english);  
34.                 student.setScience(science);  
35.                 if(isCity == 0){  
36.                     student.setCity(false);  
37.                 }else if(isCity == 1){  
38.                     student.setCity(true);  
39.                 }  
40.                 student.setSchoolDate(schoolDate);  
41.                 student.setBirth(birth);  
42.                   
43.                 list.add(student);  
44.                   
45.             }  
46.             conn.commit();  
47.   
48.   
49. public class Student {  
50.     private Integer id;  
51.     private String name;  
52.     private String no;  
53.     private String nativePlace;  
54.     private String edu;  
55.     private Integer year;  
56.     private Integer math;  
57.     private Integer chinese;  
58.     private Integer english;  
59.     private Integer science;  
60.     private Integer scores;  
61.     private Integer ave;  
62.     private boolean isCity;  
63.     private Date schoolDate;  
64.     private Date birth;  
65. 略.. .  

输出结果图14：

 

图14

 

3.2 多层公式

多层公式引用是实际开发中常见的情况，例如有A列、B列、C列数据，A列为初始就有数值，B列为公式依赖于A列，C列为公式依赖于A列和B列，这样的情况，以及各种统计计算。在此多层公式引用情况下，有可能打开Excel后，公式列获取焦点后再手动移开，公式才生效，所以需要对sheet设置sheet.setForceFormulaRecalculation(true)。

现在模拟下多层公式引用，增加一列平均分，通过“总成绩/4”计算，增加一行总计信息。

 

代码片段：

[java]  view plain copy

1. public static HSSFWorkbook write(InputStream inputStream) throws IOException, ClassNotFoundException{  
2.         Class clazz = Class.forName(Student.class.getName());  
3.         Field[] fields = clazz.getDeclaredFields();  
4.           
5.         //初始一个workbook  
6.         HSSFWorkbook workbook = new HSSFWorkbook(inputStream);  
7.         List<Student> list = Conn.getData();  
8.         //创建一个sheet  
9.         HSSFSheet sheet = workbook.getSheetAt(0);  
10.         sheet.setForceFormulaRecalculation(true);  
11.         int currentRowIndex = 0;  
12.             //创建多行,从列名下一行开始创建  
13.             for(int rowIndex = 1; rowIndex <= list.size(); rowIndex++){  
14.                 currentRowIndex = rowIndex;  
15.                 HSSFRow row = sheet.getRow(rowIndex);  
16.                 if(row == null){  
17.                     row = sheet.createRow(rowIndex);  
18.                 }  
19.                 Student student = list.get(rowIndex-1);  
20.                 //创建多列(不包括ID列)  
21.                 for(int cellnum = 0; cellnum < fields.length; cellnum++){  
22.                     HSSFCell cell = row.getCell(cellnum);  
23.                     if(cell == null){  
24.                         cell = row.createCell(cellnum);  
25.                     }  
26.                     switch (cellnum) {  
27.                     case 0:  
28.                         cell.setCellValue(new HSSFRichTextString(student.getName()));  
29.                         break;  
30.                     case 1:  
31.                         cell.setCellValue(new HSSFRichTextString(student.getNo()));  
32.                         break;  
33.                     case 2:  
34.                         cell.setCellValue(new HSSFRichTextString(student.getNativePlace()));  
35.                         break;  
36.                     case 3:  
37.                         cell.setCellValue(new HSSFRichTextString(student.getEdu()));  
38.                         break;  
39.                     case 4:  
40.                         cell.setCellValue(student.getYear());  
41.                         break;  
42.                     case 5:  
43.                         cell.setCellValue(student.getMath());  
44.                         break;  
45.                     case 6:  
46.                         cell.setCellValue(student.getChinese());  
47.                         break;  
48.                     case 7:  
49.                         cell.setCellValue(student.getEnglish());  
50.                         break;  
51.                     case 8:  
52.                         cell.setCellValue(student.getScience());  
53.                         break;  
54.                     case 9:  
55.                         cell.setCellFormula("F"+(rowIndex+1)+"+G"+(rowIndex+1)+"+H"+(rowIndex+1)+"+I"+(rowIndex+1));  
56.                         break;  
57.                     case 10:  
58.                         cell.setCellFormula("AVERAGE(F"+(rowIndex+1)+":I"+(rowIndex+1)+")");  
59.                         break;  
60.                     case 11:  
61.                         cell.setCellFormula("J"+(rowIndex+1)+"/4");  
62.                         break;  
63.                     case 12:  
64.                         cell.setCellValue(student.isCity());  
65.                         break;  
66.                     case 13:  
67.                         cell.setCellValue(new HSSFRichTextString(formatDate(student.getSchoolDate())));  
68.                         break;  
69.                     case 14:  
70.                         cell.setCellValue(new HSSFRichTextString(formatDate(student.getBirth())));  
71.                         break;  
72.                     }  
73.                 }  
74.             }  
75.             currentRowIndex++;  
76.             HSSFRow row = sheet.createRow(currentRowIndex);  
77.             HSSFCell cell0 = row.createCell(0);  
78.             HSSFCell cell5 = row.createCell(5);  
79.             HSSFCell cell6 = row.createCell(6);  
80.             HSSFCell cell7 = row.createCell(7);  
81.             HSSFCell cell8 = row.createCell(8);  
82.             HSSFCell cell9 = row.createCell(9);  
83.             HSSFCell cell10 = row.createCell(10);  
84.             HSSFCell cell11 = row.createCell(11);  
85.             cell0.setCellValue(new HSSFRichTextString("总计"));  
86.             cell5.setCellFormula("SUM(F1:F"+currentRowIndex+")");  
87.             cell6.setCellFormula("SUM(G1:G"+currentRowIndex+")");  
88.             cell7.setCellFormula("SUM(H1:H"+currentRowIndex+")");  
89.             cell8.setCellFormula("SUM(I1:I"+currentRowIndex+")");  
90.             cell9.setCellFormula("SUM(J1:J"+currentRowIndex+")");  
91.             cell10.setCellFormula("AVERAGE(K1:K"+currentRowIndex+")");  
92.             cell11.setCellFormula("AVERAGE(L1:L"+currentRowIndex+")");  
93.         return workbook;  
94.     }  

输出结果：

  

图15