比较好用的java导出大数据量Excel
来源:互联网 发布:公司域名是什么样的 编辑:程序博客网 时间:2024/06/05 04:21
转载于:http://blog.csdn.net/u010003835/article/details/51590101
注:代码还没仔细研究过……………..
原理:指定条数生成一个Excel文件,写入到本机,然后将这些Excel一起打成一个压缩包,发给客户端.
1.将多个文件一起生成压缩文件和设置Http响应头的Utils
package com.tiglle.utils;import java.io.File;import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException;import java.text.SimpleDateFormat;import java.util.Date;import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream;import javax.servlet.http.HttpServletResponse; /** * * @author http://javaflex.iteye.com/ * */ public class FileZip { /** * * @param srcfile 文件名数组 * @param zipfile 压缩后文件名称 * @throws IOException */ public static void ZipFiles(File[] srcfile, File zipfile) throws IOException { if(zipfile.exists()){ zipfile.createNewFile(); } byte[] buf = new byte[1024]; try { ZipOutputStream out = new ZipOutputStream(new FileOutputStream( zipfile)); for (int i = 0; i < srcfile.length; i++) { File tempFile = srcfile[i]; FileInputStream in = new FileInputStream(tempFile); out.putNextEntry(new ZipEntry(srcfile[i].getName())); int len; while ((len = in.read(buf)) > 0) { out.write(buf, 0, len); } out.closeEntry(); in.close(); //删除文件,免得占用服务器内存 tempFile.delete(); } out.close(); } catch (IOException e) { e.printStackTrace(); } } /** 设置响应头信息和默认文件名称 */ /** * * @param response * @param defaultFileName 默认文件名称 */ public static void setResponseHeader(HttpServletResponse response,String defaultFileName) { try { response.setContentType("application/octet-stream;charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(defaultFileName, "UTF-8") + ".zip"); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } /** * 根据指定名称加时间生成字符串 */ public static String generateName(String name){ Date date = new Date(); SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); return name + format.format(date); }}
2.装数据的实体类:
package com.tiglle.bean;public class Person { private Integer id; private String name; private String address; private String tel; private Double money=0.0; public Double getMoney() { return money; } public void setMoney(Double money) { this.money = money; } public Person(Integer id, String name, String address, String tel,Double money) { super(); this.id = id; this.name = name; this.address = address; this.tel = tel; this.money=money; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getTel() { return tel; } public void setTel(String tel) { this.tel = tel; } }
3.生成Excel和发送给客户端的servlet
package com.tiglle.servlet;import Java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import java.util.List;import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress;import com.tiglle.bean.Person; import com.tiglle.utils.FileZip;/** * * @author http://javaflex.iteye.com/ * */ public class PersonServlet extends HttpServlet {public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException { doGet(request, response); } public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //文件名获取 String zipFileName = FileZip.generateName("中奖记录"); //设置响应头信息 FileZip.setResponseHeader(response, zipFileName); OutputStream out = null; try { out = response.getOutputStream(); //模拟从数据库获得数据 List<Person> list = new ArrayList<Person>();//PersonService.getPerson(); for(int i=0;i<999999;i++){ Person p = new Person(i, "xiaoming"+i, "湖北随机", "12123123", 30.3); list.add(p); } //每个xls文件的全路径 String fileName = request.getRealPath("/files") + "/" + zipFileName; //生成n多个Excel,并将多个xls文件名+n用于区分,返回每个生成的xls的文件全路径 List<String> fileNames = toExcel(list,10000,fileName,out); //将生成的n个xls压缩成一个zip文件,并删除xls文件 File zip = new File(fileName + ".zip");// 压缩文件 File srcfile[] = new File[fileNames.size()]; for (int i = 0, n = fileNames.size(); i < n; i++) { srcfile[i] = new File(fileNames.get(i));//根据路径生成File对象 } //压缩成zip FileZip.ZipFiles(srcfile, zip); //将zip写给客户端 FileInputStream inStream = new FileInputStream(zip); byte[] buf = new byte[4096]; int readLength; while (((readLength = inStream.read(buf)) != -1)) { out.write(buf, 0, readLength); } inStream.close(); //删除zip文件,免得占用服务器内存 zip.delete(); } catch (IOException e1) { e1.printStackTrace(); } finally { try { out.flush(); out.close(); } catch (IOException e) { e.printStackTrace(); } } } @SuppressWarnings({"rawtypes","unchecked","deprecation"})/** * * @param list 里面组装的实体类 * @param request * @param length 多少条分一个文件 * @param f 文件名称 * @param out * @throws IOException */public List<String> toExcel(List<Person> list,/* HttpServletRequest request,*/int length, String fileName, OutputStream out) throws IOException { List<String> fileNames = new ArrayList();// 用于存放生成的文件名称s // 生成excel(一个对象一行) for (int j = 0, n = list.size() / length + 1; j < n; j++) { Workbook book = new HSSFWorkbook(); //创建Excel的第一页并指定名称 Sheet sheet = book.createSheet("中奖记录"); //将每个xls+j用于区分 String tempFileName = fileName + "-" + j + ".xls"; //将名字纪录起来 fileNames.add(tempFileName); FileOutputStream o = null; try { o = new FileOutputStream(tempFileName); // sheet.addMergedRegion(new // CellRangeAddress(list.size()+1,0,list.size()+5,6)); Row row = sheet.createRow(0); row.createCell(0).setCellValue("ID"); row.createCell(1).setCellValue("NAME"); row.createCell(2).setCellValue("ADDRESS"); row.createCell(3).setCellValue("TEL"); row.createCell(4).setCellValue("Money"); int m = 1; for (int i = 1, min = (list.size() - j * length + 1) > (length + 1) ? (length + 1) : (list.size() - j * length + 1); i < min; i++) { m++; Person user = list.get(length * (j) + i - 1); Double dd = user.getMoney(); if (dd == null) { dd = 0.0; } row = sheet.createRow(i); row.createCell(0).setCellValue(user.getId()); row.createCell(1).setCellValue(user.getName()); row.createCell(2).setCellValue(user.getAddress()); row.createCell(3).setCellValue(user.getTel()); row.createCell(4).setCellValue(dd); } CellStyle cellStyle2 = book.createCellStyle(); cellStyle2.setAlignment(CellStyle.ALIGN_CENTER); row = sheet.createRow(m); // Cell cell0 = row.createCell(0); // cell0.setCellValue(“Total”); // cell0.setCellStyle(cellStyle2); // Cell cell4 = row.createCell(4); // cell4.setCellValue(d); // cell4.setCellStyle(cellStyle2); //没注意看 sheet.addMergedRegion(new CellRangeAddress(m, m, 0, 3)); } catch (Exception e) { e.printStackTrace(); } try { book.write(o); } catch (Exception ex) { ex.printStackTrace(); } finally { book.close(); o.flush(); o.close(); } } return fileNames; } }
阅读全文
1 0
- 比较好用的java导出大数据量Excel
- 比较好用的java导出大数据量Excel
- Java大数据量导出Excel的问题
- java大数据量的excel导入导出
- .大数据量导出Excel的方案 java 大excel文件
- 大数据量导出Excel的方案 java 大excel文件
- 大数据量导出Excel的方案 POI、JXL、FastExcel比较
- 大数据量导出Excel的方案 POI、JXL、FastExcel比较
- JAVA导出大数据量Excel文件的解决方案
- JAVA导出数据到excel中大数据量的解决方法
- JAVA导出数据到excel中大数据量的解决方法
- JAVA导出数据到excel中大数据量的解决方法
- java 导出Excel 大数据量(一)
- JAVA实现大数据量导出excel
- 大数据量导出Excel
- EXCEL大数据量导出的解决方案
- 大数据量导出Excel的方案
- 大数据量导出Excel的方案
- 解决Android Studio Gradle DSL method not found: 'android()'问题
- 2017全球软件架构技术大会特邀四十位技术领袖以及一线实战专家
- Java初学者的学习路线建议
- 3158: 千钧一发/3275: Number
- Linux系统教程
- 比较好用的java导出大数据量Excel
- 设计模式-原型模式
- echarts 地图类型热度图 visualMap 图列数值导致 地图发生严重的显示bug
- 读取cpp文件处理
- HashPasswordForStoringInConfigFile 已过时
- 质因数分解(唯一分解定理)
- 【FZU
- 每天一点积累(一二)---JVM之GC垃圾回收
- 设计模式-模板方法模式