java读取数据库生成excel并实现下载.docx

java读取数据库生成excel并实现下载Java读取数据库生成Excel并实现下载 1. 问题场景 考试系统开发中，需要有导出成绩单功能，从数据库读取数据，写入excel，并实现网页下载。 2. 实现方法 Excel工具类：ExcelExport.java import java.io.ByteArrayOutputStream; import java.io.OutputStream; import .URLEncoder; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; publicclass ExcelExport / 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet(sheetName); / 第一步，创建一个webbook，对应一个Excel文件 public HSSFWorkbook generateExcel public HSSFWorkbook generateSheet(HSSFWorkbook wb, String sheetName, String returnnew HSSFWorkbook; fields, List<GradeExcelData> list) / 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short HSSFRow row = sheet.createRow(0); / 第四步，创建单元格，并设置值表头设置表头居中 HSSFCellStyle style = wb.createCellStyle; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); / 创建一个居中格式 /设置表头字段名 HSSFCell cell; int m=0; for(String fieldName:fields) cell = row.createCell(m); cell.setCellValue(fieldName); cell.setCellStyle(style); m+; for (int i = 0; i < list.size; i+) row = sheet.createRow(i + 1); GradeExcelData data = list.get(i); / 第五步，创建单元格，并设置值 row.createCell(0).setCellValue(data.getStuId); row.createCell(1).setCellValue(data.getName); row.createCell(2).setCellValue(data.getScore); row.createCell(3).setCellValue(data.getPassOrNot); returnwb; try OutputStream out = response.getOutputStream; ByteArrayOutputStream baos = new ByteArrayOutputStream; wb.write( baos); byte xlsBytes = baos .toByteArray; out.write( xlsBytes); out.close; catch (Exception e) response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("学生成绩单", "utf-8") + ".xls"); publicvoid export(HSSFWorkbook wb,HttpServletResponse response) / 第六步，实现文件下载保存 e.printStackTrace; Excel辅助对象：GradeExcelData.java publicclassGradeExcelData private String stuId; private String name; private String passOrNot; privateintscore; public String getStuId publicvoid setStuId(String stuId) public String getName publicvoid setName(String name) public String getPassOrNot publicvoid setPassOrNot(String passOrNot) publicint getScore publicvoid setScore(int score) this.score = score; returnscore; this.passOrNot = passOrNot; returnpassOrNot; this.name = name; returnname; this.stuId = stuId; returnstuId; service层调用： publicboolean export(String eId,HttpServletResponse response) try List<GradeExcelData> list = cardDao.getExcelData(eId); String fields = "学号","姓名","得分","是否通过" ExcelExport export = new ExcelExport; HSSFWorkbook wb = export.generateExcel; wb = export.generateSheet(wb, "学生成绩单", fields, list); export.export(wb, response); returntrue; / TODO Auto-generated catch block e.printStackTrace; returnfalse; catch (Exception e)