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

《java读取数据库生成excel并实现下载.docx》由会员分享，可在线阅读，更多相关《java读取数据库生成excel并实现下载.docx（5页珍藏版）》请在三一办公上搜索。

1、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.ap

2、ache.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 HSS

3、FSheet sheet = wb.createSheet(sheetName); / 第一步，创建一个webbook，对应一个Excel文件 public HSSFWorkbook generateExcel public HSSFWorkbook generateSheet(HSSFWorkbook wb, String sheetName, String returnnew HSSFWorkbook; fields, List list) / 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short HSSFRow row = sheet.create

4、Row(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

5、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; tr

6、y 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-

7、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(Strin

8、g 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

9、 = stuId; returnstuId; service层调用： publicboolean export(String eId,HttpServletResponse response) try List 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)