链接:http://poi.apache.org/
Excel数据导出步骤:
使用poi 完成账户数据的导出功能 导入poi jar包并添加到classpath中
1、查询数据
2、定义导出头
String fileName="测试数据.xls";
resp.setHeader("Content-disposition","attachment;filename="new String(fileName.getBytes("gb2312"),"ISO8859-1")); //设置文件头编码格式
resp.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");//设置类型
resp.setHeader("Cache-Control","no-cache");//设置头
resp.setDateHeader("Expires", 0);//设置日期头
3、创建工作簿HSSFWorkbook 对象
HSSFWorkbook book=new HSSFWorkbook();
4、由工作簿创建表HSSFSheet对象
HSSFSheet sheet=book.createSheet("账户表数据");
5、创建行对象Row
Row row=sheet.createRow(i);
6、创建单元格 Cell
Cell cell=row.createCell(0);
日期格式处理:
CellStyle cellStyle=book.createCellStyle(); CellStyle cellStyle=book.createCellStyle(); cellStyle.setDataFormat(book.createDataFormat().getFormat("yyyy-MM-dd")); cell.setCellStyle(cellStyle) |
7、写出流 刷新缓冲流 关闭流对象
book.write(resp.getOutputStream());
resp.getOutputStream().flush();
resp.getOutputStream().close();
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
1、导入相应jar包
2、相应方法
1 /** 2 * excel导出 3 * @param req 4 * @param resp 5 * void 6 * @throws IOException 7 */ 8 private void exportAccountData(HttpServletRequest req, 9 HttpServletResponse resp) throws IOException { 10 11 String sql=" select id,aname,type,money,remark,create_time as createTime,update_Time as updateTime from account " 12 + " where user_id=?"; 13 14 User user=(User) req.getSession().getAttribute("user"); 15 16 List<Account> list=MyBaseDao.queryRows(sql, new Object[]{user.getId()}, Account.class); 17 18 if(null!=list&&list.size()>0){ 19 20 String fileName="账户数据.xls"; 21 resp.setHeader("Content-disposition","attachment;filename=" 22 +new String(fileName.getBytes("gb2312"),"ISO8859-1")); //设置文件头编码格式 23 resp.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");//设置类型 24 resp.setHeader("Cache-Control","no-cache");//设置头 25 resp.setDateHeader("Expires", 0);//设置日期头 26 27 HSSFWorkbook book=new HSSFWorkbook(); 28 29 HSSFSheet sheet=book.createSheet(); 30 CellStyle cellStyle=book.createCellStyle(); 31 32 cellStyle.setDataFormat(book.createDataFormat().getFormat("yyyy-MM-dd")); 33 34 for(int i=0;i<list.size();i++){ 35 Account account=list.get(i); 36 Row row=sheet.createRow(i); 37 38 Cell cell1=row.createCell(0); 39 cell1.setCellValue(account.getId()); 40 41 Cell cell2=row.createCell(1); 42 cell2.setCellValue(account.getAname()); 43 Cell cell3=row.createCell(2); 44 cell3.setCellValue(account.getMoney()); 45 Cell cell4=row.createCell(3); 46 cell4.setCellValue(account.getType()); 47 Cell cell5=row.createCell(4); 48 cell5.setCellValue(account.getRemark()); 49 50 Cell cell6=row.createCell(5); 51 cell6.setCellValue(account.getCreateTime()); 52 cell6.setCellStyle(cellStyle); 53 54 Cell cell7=row.createCell(6); 55 cell7.setCellStyle(cellStyle); 56 cell7.setCellValue(account.getUpdateTime()); 57 } 58 59 book.write(resp.getOutputStream()); 60 61 resp.getOutputStream().flush(); 62 resp.getOutputStream().close(); 63 } 64 }
3、jsp中添加excel导出
queryAccount.jsp