3.复杂写入
3.1 复杂写入
POI复杂写出主要是同复杂读取一样,在实际开发中,从数据库中读取字符、数字、日期各种类型数据,并通过相应的计算公式写出到Excel中。
这次写出的Excel,不重新创建,而采用Excel模板(图12)形式,将数据从数据库(图13)中读出,并通过公式方式计算每个学生的总成绩、平均分,最后写入模板。
图12
图13
代码片段:
public static HSSFWorkbook write(InputStream inputStream) throws IOException, ClassNotFoundException{
Class clazz = Class.forName(Student.class.getName());
Field[] fields = clazz.getDeclaredFields();
//初始一个workbook
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
List<Student> list = Conn.getData();
//创建一个sheet
HSSFSheet sheet = workbook.getSheetAt(0);
//创建多行,从列名下一行开始创建
for(int rowIndex = 1; rowIndex <= list.size(); rowIndex++){
HSSFRow row = sheet.getRow(rowIndex);
if(row == null){
row = sheet.createRow(rowIndex);
}
Student student = list.get(rowIndex-1);
//创建多列(不包括ID列)
for(int cellnum = 0; cellnum < fields.length-1; cellnum++){
HSSFCell cell = row.getCell(cellnum);
if(cell == null){
cell = row.createCell(cellnum);
}
switch (cellnum) {
case 0:
cell.setCellValue(new HSSFRichTextString(student.getName()));
break;
case 1:
cell.setCellValue(new HSSFRichTextString(student.getNo()));
break;
case 2:
cell.setCellValue(new HSSFRichTextString(student.getNativePlace()));
break;
case 3:
cell.setCellValue(new HSSFRichTextString(student.getEdu()));
break;
case 4:
cell.setCellValue(student.getYear());
break;
case 5:
cell.setCellValue(student.getMath());
break;
case 6:
cell.setCellValue(student.getChinese());
break