POI导出excel的三种方式
原文链接:https://www.cnblogs.com/zhaoblog/p/7661245.html
poi导出excel最常用的是第一种方式HSSFWorkbook，不过这种方式数据量大的话会产生内存溢出问题，SXSSFWorkbook是一种大数据量导出格式，csv是另一种excel导出的一种轻快的实现。
先介绍一下这三种格式的特点
1 HSSFWorkbook excel文件底层是txt实现，我们经常见到的excel都是这种实现的。
2 SXSSFWorkbook excel文件底层是xml实现，同样的数据量，大约是第一种的1/6-1/4之间
3 csv 这个比较新潮，数据量应该更小，可以百度。
一 HSSFWorkbook 实现
Map<String, Object> dataMap = service.AssignMiddleExcel(page); ObjectExcelView erv = new ObjectExcelView("逾期客户表"); //执行excel操作 mv = new ModelAndView(erv,dataMap); //dataMap 是个map ,包含两个键值对 一个是标题列 Map<String,Object> dataMap = new HashMap<String,Object>(); List<String> titles = new ArrayList<String>(); titles.add("借款人姓名"); //1 titles.add("身份证号"); //2 titles.add("借款人手机号"); dataMap.put("titles", titles); // 这是标题列 //vpd是个map vpd.put("var1", userList.get(i).getString("realName")); //1 vpd.put("var2", userList.get(i).getString("contIdCard")); //2 vpd.put("var3", userList.get(i).getString("telephone")); //3 List<PageData> varList = new ArrayList<PageData>(); //PageData就是个HashMap varList.add(vpd); dataMap.put("varList", varList); //这就是dataMap的由来 public class ObjectExcelView extends AbstractExcelView{ private String fileName; public ObjectExcelView(){ } public ObjectExcelView(String fileName){ this.fileName = fileName; } @Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // TODO Auto-generated method stub //String fileName = "逾期客户表-待催收"; HSSFSheet sheet; HSSFCell cell; response.setContentType("application/x-download");//下面三行是关键代码，处理乱码问题 response.setCharacterEncoding("utf-8"); response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("gbk"), "iso8859-1")+".xls"); sheet = workbook.createSheet("sheet1"); List<String> titles = (List<String>) model.get("titles"); int len = titles.size(); HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式 headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = workbook.createFont(); //标题字体 headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short)11); headerStyle.setFont(headerFont); short width = 20,height=25*20; sheet.setDefaultColumnWidth(width); for(int i=0; i<len; i++){ //设置标题 String title = titles.get(i); cell = getCell(sheet, 0, i); cell.setCellStyle(headerStyle); setText(cell,title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式 contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); List<PageData> varList = (List<PageData>) model.get("varList"); int varCount = varList.size(); for(int i=0; i<varCount; i++){ PageData vpd = varList.get(i); for(int j=0;j<len;j++){ String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : ""; cell = getCell(sheet, i+1, j); cell.setCellStyle(contentStyle); setText(cell,varstr); } } } }
二 SXSSFWorkbook
@RequestMapping("/excel111")
public Object exportExcel(Page page,HttpServletRequest request,HttpServletResponse response){
PageData pd = new PageData();
pd = this.getPageData();
try{
//检索条件===========
PageData pageData = service.setPdWithTrim(this.getPageData());
page.setPd(pageData);
//检索条件===========
Map<String, Object> dataMap = service.AssignMiddleExcel(page);
OutputStream outputStream = response.getOutputStream();
String sheetName = "逾期客户表";
List<String> list = (List<String>)dataMap.get("titles");
String[] headers = new String[list.size()];
for(int i = 0;i<list.size();i++){
headers[i]=list.get(i);
}
List<Object[]> dataList = new ArrayList<>();
List<PageData> varList = new ArrayList<PageData>();
varList = (List<PageData>)dataMap.get("varList");
varList.forEach(e ->{
List<Object> ObjectList = new ArrayList<>();
for(int i=0;i<headers.length;i++){
ObjectList.add(e.get("var"+(i+1))==null?null:e.getString("var"+(i+1)));
}
/* for (Object v : e.values()) {
ObjectList.add(v);
}*/
Object[] objarr = new Object[ObjectList.size()];
for(int i = 0;i<ObjectList.size();i++){
objarr[i] = ObjectList.get(i);
}
dataList.add(objarr);
});
ExportUtil exportUtil = new ExportUtil(sheetName,headers,dataList);
//SXSSFWorkbook 大批量数据导出
SXSSFWorkbook workBook = exportUtil.export();
// 如果文件名有中文，必须URL编码
String fileName1 = URLEncoder.encode(sheetName, "UTF-8");
// response.reset();
// response.setContentType("application/force-download");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName1+".xls");
workBook.write(outputStream);
workBook.close();
outputStream.flush();
outputStream.close();
//ObjectExcelView erv = new ObjectExcelView("逾期客户表"); //执行excel操作
//mv = new ModelAndView(erv,dataMap);
} catch(Exception e){
logger.error(e.toString(), e);
}
return new Object();
}
/** * Created by dell、 on 2017/10/12. */ public class ExportUtil { private SXSSFWorkbook wb = null;//大数据导出格式 private Sheet sheet = null; private String sheetName; private String[] headers;//表头字段 private List<Object[]> dataList = new ArrayList<Object[]>();//表内容数据 //构造 public ExportUtil(SXSSFWorkbook wb, Sheet sheet) { this.wb = wb; this.sheet = sheet; } //构造 public ExportUtil(String sheetName, String[] headers, List<Object[]> dataList) { this.dataList = dataList; this.headers = headers; this.sheetName = sheetName; } //不知道做什么 public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) { int toprowNum = region.getFirstRow(); for (int i = toprowNum; i <= region.getLastRow(); i++) { SXSSFRow row = (SXSSFRow) sheet.getRow(i); for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) { SXSSFCell cell = (SXSSFCell) row.getCell(j); cell.setCellStyle(cs); } } } // 设置表头的单元格样式 public CellStyle getHeadStyle() { // 创建单元格样式 CellStyle cellStyle = wb.createCellStyle(); // 设置单元格的背景颜色为淡蓝色 cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index); // 设置填充字体的样式 cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 设置单元格居中对齐 cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 设置单元格垂直居中对齐 cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 创建单元格内容显示不下时自动换行 cellStyle.setWrapText(false); // 设置单元格字体样式 XSSFFont font = (XSSFFont) wb.createFont(); // font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗 font.setFontName("宋体");// 设置字体的样式 font.setFontHeight(14);// 设置字体的大小 cellStyle.setFont(font);// 将字体填充到表格中去 // 设置单元格边框为细线条（上下左右） cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); return cellStyle; } // 设置表体的单元格样式 public CellStyle getBodyStyle() { // 创建单元格样式 CellStyle cellStyle = wb.createCellStyle(); // 设置单元格居中对齐 cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 设置单元格居中对齐 cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 创建单元格内容不显示自动换行 cellStyle.setWrapText(false); // 设置单元格字体样式 XSSFFont font = (XSSFFont) wb.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗 font.setFontName("宋体");// 设置字体 font.setFontHeight(12);// 设置字体的大小 cellStyle.setFont(font);// 将字体添加到表格中去 // 设置单元格边框为细线条 cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); return cellStyle; } //似乎没用到 public Cell setHeadStyle(String[] titles) { Cell cell = null; CellStyle headStyle = getHeadStyle(); Row headRow = sheet.createRow(0); // 构建表头 for (int i = 0; i < titles.length; i++) { cell = headRow.createCell(i); cell.setCellStyle(headStyle); cell.setCellValue(titles[i]); } return cell; } /* * 导出数据 */ public SXSSFWorkbook export() throws Exception { // String nsheetName = new String(sheetName.getBytes("UTF-8")); /* * response.setCharacterEncoding("utf-8"); * response.setContentType("application/x-msdownload"); * response.setHeader("Content-disposition", "attachment; filename=" + * sheetName + ".xlsx");// 组装附件名称和格式 */ Integer rowaccess = 1000;// 内存中缓存记录行数，以免内存溢出 SXSSFWorkbook workbook = new SXSSFWorkbook(rowaccess); try { Sheet sheet = workbook.createSheet(sheetName); // 产生表格标题行 Row titleRow = sheet.createRow(0); Cell cellTiltle = titleRow.createCell(0); CellStyle columnTopStyle = this.getColumnTopStyle(workbook);// 获取列头样式对象 sheet.setDefaultColumnWidth(100); sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (headers.length - 1))); cellTiltle.setCellStyle(columnTopStyle); cellTiltle.setCellValue(sheetName); Cell cell = null; CellStyle headStyle = this.getHeadStyle(workbook); // 定义所需列数 int columnNum = headers.length; Row headRow = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行) //表头 for (int n = 0; n < columnNum; n++) { Cell cellRowName = headRow.createCell(n); // 创建列头对应个数的单元格 cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型 HSSFRichTextString text = new HSSFRichTextString(headers[n]); cellRowName.setCellValue(headers[n]); // 设置列头单元格的值 cellRowName.setCellStyle(headStyle); // 设置列头单元格样式 sheet.setColumnWidth(n,256*20); } CellStyle bodyStyle = this.getBodyStyle(workbook); // 表体数据 for (int i = 0; i < dataList.size(); i++) { Object[] obj = dataList.get(i); Row row = sheet.createRow(i + 3);// 创建所需的行数 for (int j = 0; j < obj.length; j++) { cell = row.createCell(j); String str = String.valueOf(StringUtils.isEmpty(obj[j]) ? "" : obj[j]); cell.setCellValue(str); // 单元格的值 cell.setCellStyle(bodyStyle); // 单元格的样式 } } } catch (Exception e) { e.printStackTrace(); } return workbook; } // 设置表头的单元格样式 public CellStyle getHeadStyle(SXSSFWorkbook workbook) { // 创建单元格样式 CellStyle cellStyle = workbook.createCellStyle(); // 设置单元格的背景颜色为淡蓝色 cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index); // 设置填充字体的样式 cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 设置单元格居中对齐 cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 设置单元格垂直居中对齐 cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // cellStyle.setShrinkToFit(true); // 创建单元格内容显示不下时自动换行 //cellStyle.setWrapText(true); // 设置单元格字体样式 XSSFFont font = (XSSFFont) workbook.createFont(); // font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗 font.setFontName("宋体");// 设置字体的样式 font.setFontHeight(12);// 设置字体的大小 cellStyle.setFont(font);// 将字体填充到表格中去 // 设置单元格边框为细线条（上下左右） cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); return cellStyle; } // 设置表体的单元格样式 public CellStyle getBodyStyle(SXSSFWorkbook workbook) { // 创建单元格样式 CellStyle cellStyle = workbook.createCellStyle(); // 设置单元格居中对齐 cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 设置单元格居中对齐 cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 创建单元格内容不显示自动换行 //cellStyle.setWrapText(true); // 设置单元格字体样式 XSSFFont font = (XSSFFont) workbook.createFont(); // font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 这是字体加粗 font.setFontName("宋体");// 设置字体 font.setFontHeight(10);// 设置字体的大小 cellStyle.setFont(font);// 将字体添加到表格中去 // 设置单元格边框为细线条 cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); return cellStyle; } public Cell setHeadStyle(String[] titles, SXSSFWorkbook workbook) { Cell cell = null; CellStyle headStyle = getHeadStyle(workbook); Row headRow = sheet.createRow(0); // 构建表头 for (int i = 0; i < titles.length; i++) { cell = headRow.createCell(i); cell.setCellStyle(headStyle); cell.setCellValue(titles[i]); } return cell; } /* * 列头单元格样式 */ public CellStyle getColumnTopStyle(SXSSFWorkbook workbook) { // 设置字体 Font font = workbook.createFont(); // 设置字体大小 font.setFontHeightInPoints((short) 18); // 字体加粗 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 设置字体名字 font.setFontName("Courier New"); // 设置样式; CellStyle style = workbook.createCellStyle(); // 设置底边框; style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置底边框颜色; style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置左边框; style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置左边框颜色; style.setLeftBorderColor(HSSFColor.BLACK.index); // 设置右边框; style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框颜色; style.setRightBorderColor(HSSFColor.BLACK.index); // 设置顶边框; style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置顶边框颜色; style.setTopBorderColor(HSSFColor.BLACK.index); // 在样式用应用设置的字体; style.setFont(font); // 设置自动换行; style.setWrapText(false); // 设置水平对齐的样式为居中对齐; style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } }
三 CSV/* * 导出委外催收中EXCEL 大数据量csv导出 * @return */ @RequestMapping("/excel") public Object exportExcel111(Page page, HttpServletResponse response ) { PageData pd = new PageData(); pd = this.getPageData(); try { //检索条件=========== PageData pageData = service.setPdWithTrim(this.getPageData()); page.setPd(pageData); //检索条件=========== Map<String, Object> dataMap = service.AssignMiddleExcel(page); String sheetName = "逾期客户表"; List<Object> headlist = (List<Object>)dataMap.get("titles"); List<List<Object>> dataList = new ArrayList<>(); List<PageData> varList = new ArrayList<PageData>(); varList = (List<PageData>)dataMap.get("varList"); varList.forEach(e ->{ List<Object> ObjectList = new ArrayList<>(); for(int i=0;i<headlist.size();i++){ ObjectList.add(e.get("var"+(i+1))==null?null:e.getString("var"+(i+1))); } dataList.add(ObjectList); }); File csvFile =CSVUtils.createCSVFile(headlist,dataList,sheetName); BufferedInputStream bis = null; BufferedOutputStream bos = null; response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(csvFile.getName(), "UTF-8")); response.setHeader("Content-Length", String.valueOf(csvFile.length())); bis = new BufferedInputStream(new FileInputStream(csvFile)); bos = new BufferedOutputStream(response.getOutputStream()); byte[] buff = new byte[2048]; while (true) { int bytesRead; if (-1 == (bytesRead = bis.read(buff, 0, buff.length))) break; bos.write(buff, 0, bytesRead); } bis.close(); bos.close(); csvFile.delete(); }catch (Exception e){ e.printStackTrace(); } return new Object(); }
package com.fh.controller.assign_out; import javax.servlet.http.HttpServletRequest; import java.io.*; import java.util.*; /** * Created by dell、 on 2017/10/12. */ public class CSVUtils { /** * CSV文件生成方法 * @param head * @param dataList * @param filename * @return */ public static File createCSVFile(List<Object> head, List<List<Object>> dataList, String filename) { File csvFile = null; BufferedWriter csvWtriter = null; try { csvFile = new File( "temp123/"+ File.separator + filename + ".csv"); File parent = csvFile.getParentFile(); if (parent != null && !parent.exists()) { parent.mkdirs(); } csvFile.createNewFile(); // GB2312使正确读取分隔符"," csvWtriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream( csvFile), "GB2312"), 1024); // 写入文件头部 writeRow(head, csvWtriter); // 写入文件内容 for (List<Object> row : dataList) { writeRow(row, csvWtriter); } csvWtriter.flush(); } catch (Exception e) { e.printStackTrace(); } finally { try { csvWtriter.close(); } catch (IOException e) { e.printStackTrace(); } } return csvFile; } /** * 写一行数据方法 * @param row * @param csvWriter * @throws IOException */ private static void writeRow(List<Object> row, BufferedWriter csvWriter) throws IOException { // 写入文件头部 for (Object data : row) { StringBuffer sb = new StringBuffer(); String rowStr = sb.append("\"").append(data).append("\",").toString(); csvWriter.write(rowStr); } csvWriter.newLine(); } public static File createCSVFile(HttpServletRequest request){ List< Map<String,Object>> list = new ArrayList<>(); for(int i =0;i<10;i++){ Map<String,Object> mao = new HashMap<>(); mao.put("cutomerName","zhangsan"+i); mao.put("cutomerNam1","zhangsan"+i); mao.put("cutomerNam2","zhangsan"+i); mao.put("cutomerNam3","zhangsan"+i); mao.put("cutomerNam4","zhangsan"+i); mao.put("cutomerNam5","zhangsan"+i); mao.put("cutomerNam6","zhangsan"+i); mao.put("cutomerNam7","zhangsan"+i); mao.put("cutomerNam8","zhangsan"+i); mao.put("cutomerNam9", "2017-10-17 22:33:33 12.00"); mao.put("cutomerNam10",555555556); /* mao.put("cutomerNam11","zhangsan"+i); mao.put("cutomerNam12","zhangsan"+i); mao.put("cutomerNam13","zhangsan"+i); mao.put("cutomerNam14","zhangsan"+i); mao.put("cutomerNam15","zhangsan"+i); mao.put("cutomerNam16","zhangsan"+i); mao.put("cutomerNam17","zhangsan"+i); mao.put("cutomerNam18","zhangsan"+i); mao.put("cutomerNam19","zhangsan"+i); mao.put("cutomerNam20","zhangsan"+i); mao.put("cutomerNam21","zhangsan"+i); mao.put("cutomerNam22","zhangsan"+i); mao.put("cutomerNam23","zhangsan"+i); mao.put("cutomerNam24","zhangsan"+i); mao.put("cutomerNam25","zhangsan"+i); mao.put("cutomerNam26","zhangsan"+i); mao.put("cutomerNam27","zhangsan"+i); mao.put("cutomerNam28","zhangsan"+i); mao.put("cutomerNam29","zhangsan"+i); mao.put("cutomerNam30","zhangsan"+i); mao.put("cutomerNam31","zhangsan"+i); mao.put("cutomerNam32","zhangsan"+i); mao.put("cutomerNam33","zhangsan"+i); mao.put("cutomerNam34","zhangsan"+i); mao.put("cutomerNam35","zhangsan"+i); mao.put("cutomerNam36","zhangsan"+i); mao.put("cutomerNam37","zhangsan"+i); mao.put("cutomerNam38","zhangsan"+i); mao.put("cutomerNam39","zhangsan"+i); mao.put("cutomerNam40","zhangsan"+i); mao.put("cutomerNam41","zhangsan"+i); mao.put("cutomerNam42","zhangsan"+i); mao.put("cutomerNam43","zhangsan"+i); mao.put("cutomerNam44","zhangsan"+i); mao.put("cutomerNam45","zhangsan"+i);*/ list.add(mao); } // 设置表格头 Object[] head = {"客户姓名", "证件类型", "证件号码", "银行账号", "理财账号", "客户类型", "风险等级", "归属状况", "归属机构", "客户经理", "营销比例(%)" }; List<Object> headList = Arrays.asList(head); // 设置数据 List<List<Object>> dataList = new ArrayList<List<Object>>(); List<Object> rowList = null; for (int i = 0; i < list.size(); i++) { rowList = new ArrayList<Object>(); Map<String,Object> maovo = list.get(i); rowList.add(maovo.get("cutomerName")); rowList.add(maovo.get("cutomerNam1")); rowList.add(maovo.get("cutomerNam2")); rowList.add(maovo.get("cutomerNam3")); rowList.add(maovo.get("cutomerNam4")); rowList.add(maovo.get("cutomerNam5")); rowList.add(maovo.get("cutomerNam6")); rowList.add(maovo.get("cutomerNam7")); rowList.add(maovo.get("cutomerNam8")); rowList.add(maovo.get("cutomerNam9")); rowList.add(maovo.get("cutomerNam10")); dataList.add(rowList); } // 导出文件路径 // String downloadFilePath = request.getContextPath(); // 导出文件名称 String fileName = "客户列表_"; // 导出CSV文件 File csvFile = CSVUtils.createCSVFile(headList, dataList, fileName); return csvFile; } }
