最近接了一个任务,要求把数据库内容提取成excel,一个excel动态创建多个sheet,一个sheet里面显示一条信息,然后再根据此条信息的结果,再去子表获取结果,如一条学生信息,对应显示该学生的所有详细信息。如图所示:
首先下载poi的jar包,要求几个jar包的版本必须一致:
dom4j-1.1.jar
poi-ooxml-3.9.jar
poi-ooxml-schemas-3.9.jar
poi-3.9.jar
工程中导入:
相关代码:
public static void WriteExcelNew(HSSFWorkbook workbook,int sheetNum,String sheetTitle,String[] headers,String[]headersScore,ServiceGetInfor service,StudentInfo
info,String tableName,int pagebegin,int pageEnd) {
Sheet sheet = null;
sheet=workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
List<ScorInfo> list=service.DetailInfor(info.getStudentid);//根据学生信息获取成绩
//加入标题
Row row = sheet.createRow(0);
Cell cell=null;
for (int i = 0; i < headers.length; i++)
{
cell = row.createCell((short) i);
RichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());
}
//导入一行内容
row = sheet.createRow(1);
row.createCell(0).setCellValue(info.getName());
row.createCell(1).setCellValue(info.getSex());
row.createCell(2).setCellValue(info.getAge());
row.createCell(3).setCellValue(info.getClass());
row = sheet.createRow(2);
//加入成绩明细标题
for (int i = 0; i < headersScore.length; i++)
{
cell = row.createCell((short) i);
RichTextString text = new HSSFRichTextString(headersCfmx[i]);
cell.setCellValue(text.toString());
}
//加入所有成绩明细
for(int j=0;j<list.size();j++)
{
row = sheet.createRow(j + 3);
ScorInfo sinfo=list.get(j);
cell=row.createCell(0,Cell.CELL_TYPE_STRING);
cell.setCellValue(sinfo.getChineses());
cell=row.createCell(1,Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(sinfo.getMath());
cell=row.createCell(2,Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(cfmx.getSl());
cell=row.createCell(3,Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(sinfo.getEnglish());
}
}
Main函数:
try { ServiceGetInfor service=new ServiceGetInfor();
OutputStream out = new FileOutputStream("F:\\test.xls");
List<StudentInfo > list = service.BaseInfor();//获取所有学生信息
String[] headers = { "姓名","性别","年龄","班级" };
String[]headersscore={"学期","语文","数学","英语"};
HSSFWorkbook workbook = new HSSFWorkbook();
for(int i=0;i<list.size();i++)//根据所有学生的信息,在一个excel中创建多个sheet
{
GhdjBaseInfo info=list.get(i);
String studenName=info.getStudentId();//sheet的名称以学生的名字命名
WriteExcelNew(workbook,i,studentName,headers,headersscoreservice,info,tablleName,pagebegin,pageEnd);
}
//原理就是将所有的数据一起写入,然后再关闭输入流。
workbook.write(out);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
说明:需要先获取学生的所有信息和成绩的所有信息,成绩信息的获取根据学生的id来获取,学生和成绩信息分别存放在List中