POI通用導(dǎo)出Excel(.xls,.xlsx)的方法
POI操作EXCEL對象
HSSF:操作Excel 97(.xls)格式
XSSF:操作Excel 2007 OOXML (.xlsx)格式,操作EXCEL內(nèi)存占用高于HSSF
SXSSF:從POI3.8 beta3開始支持,基于XSSF,低內(nèi)存占用。
使用POI的HSSF對象,生成Excel 97(.xls)格式,生成的EXCEL不經(jīng)過壓縮直接導(dǎo)出。
線上問題:負(fù)載服務(wù)器轉(zhuǎn)發(fā)請求到應(yīng)用服務(wù)器阻塞,以及內(nèi)存溢出 。
如果系統(tǒng)存在大數(shù)據(jù)量報(bào)表導(dǎo)出,則考慮使用POI的SXSSF進(jìn)行EXCEL操作。
HSSF生成的Excel 97(.xls)格式本身就有每個(gè)sheet頁不能超過65536條的限制。
XSSF生成Excel 2007 OOXML (.xlsx)格式,條數(shù)增加了,但是導(dǎo)出過程中,內(nèi)存占用率卻高于HSSF.
SXSSF是自3.8-beta3版本后,基于XSSF提供的低內(nèi)存占用的操作EXCEL對象。其原理是可以設(shè)置或者手動(dòng)將內(nèi)存中的EXCEL行寫到硬盤中,這樣內(nèi)存中只保存了少量的EXCEL行進(jìn)行操作。
EXCEL的壓縮率特別高,能達(dá)到80%,12M的文件壓縮后才2M左右。 如果未經(jīng)過壓縮、不僅會(huì)占用用戶帶寬,且會(huì)導(dǎo)致負(fù)載服務(wù)器(apache)和應(yīng)用服務(wù)器之間,長時(shí)間占用連接(二進(jìn)制流轉(zhuǎn)發(fā)),導(dǎo)致負(fù)載服務(wù)器請求阻塞,不能提供服務(wù)。
一定要注意文件流的關(guān)閉
防止前臺(tái)(頁面)連續(xù)觸發(fā)導(dǎo)出EXCEL
1.通用核心導(dǎo)出工具類 ExcelUtil.java
package sy.util; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import org.apache.poi.hpsf.SummaryInformation; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFComment; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.formula.functions.T; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; public class ExcelUtil{ public static String NO_DEFINE = "no_define";//未定義的字段 public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默認(rèn)日期格式 public static int DEFAULT_COLOUMN_WIDTH = 17; /** * 導(dǎo)出Excel 97(.xls)格式 ,少量數(shù)據(jù) * @param title 標(biāo)題行 * @param headMap 屬性-列名 * @param jsonArray 數(shù)據(jù)集 * @param datePattern 日期格式,null則用默認(rèn)日期格式 * @param colWidth 列寬 默認(rèn) 至少17個(gè)字節(jié) * @param out 輸出流 */ public static void exportExcel(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) { if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN; // 聲明一個(gè)工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); workbook.createInformationProperties(); workbook.getDocumentSummaryInformation().setCompany("*****公司"); SummaryInformation si = workbook.getSummaryInformation(); si.setAuthor("JACK"); //填加xls文件作者信息 si.setApplicationName("導(dǎo)出程序"); //填加xls文件創(chuàng)建程序信息 si.setLastAuthor("最后保存者信息"); //填加xls文件最后保存者信息 si.setComments("JACK is a programmer!"); //填加xls文件作者信息 si.setTitle("POI導(dǎo)出Excel"); //填加xls文件標(biāo)題信息 si.setSubject("POI導(dǎo)出Excel");//填加文件主題信息 si.setCreateDateTime(new Date()); //表頭樣式 HSSFCellStyle titleStyle = workbook.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 20); titleFont.setBoldweight((short) 700); titleStyle.setFont(titleFont); // 列頭樣式 HSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont headerFont = workbook.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(headerFont); // 單元格樣式 HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont cellFont = workbook.createFont(); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); cellStyle.setFont(cellFont); // 生成一個(gè)(帶標(biāo)題)表格 HSSFSheet sheet = workbook.createSheet(); // 聲明一個(gè)畫圖的頂級(jí)管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定義注釋的大小和位置,詳見文檔 HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 設(shè)置注釋內(nèi)容 comment.setString(new HSSFRichTextString("可以在POI中添加注釋!")); // 設(shè)置注釋作者,當(dāng)鼠標(biāo)移動(dòng)到單元格上是可以在狀態(tài)欄中看到該內(nèi)容. comment.setAuthor("JACK"); //設(shè)置列寬 int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字節(jié)數(shù) int[] arrColWidth = new int[headMap.size()]; // 產(chǎn)生表格標(biāo)題行,以及設(shè)置列寬 String[] properties = new String[headMap.size()]; String[] headers = new String[headMap.size()]; int ii = 0; for (Iterator<String> iter = headMap.keySet().iterator(); iter .hasNext();) { String fieldName = iter.next(); properties[ii] = fieldName; headers[ii] = fieldName; int bytes = fieldName.getBytes().length; arrColWidth[ii] = bytes < minBytes ? minBytes : bytes; sheet.setColumnWidth(ii,arrColWidth[ii]*256); ii++; } // 遍歷集合數(shù)據(jù),產(chǎn)生數(shù)據(jù)行 int rowIndex = 0; for (Object obj : jsonArray) { if(rowIndex == 65535 || rowIndex == 0){ if ( rowIndex != 0 ) sheet = workbook.createSheet();//如果數(shù)據(jù)超過了,則在第二頁顯示 HSSFRow titleRow = sheet.createRow(0);//表頭 rowIndex=0 titleRow.createCell(0).setCellValue(title); titleRow.getCell(0).setCellStyle(titleStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1)); HSSFRow headerRow = sheet.createRow(1); //列頭 rowIndex =1 for(int i=0;i<headers.length;i++) { headerRow.createCell(i).setCellValue(headers[i]); headerRow.getCell(i).setCellStyle(headerStyle); } rowIndex = 2;//數(shù)據(jù)內(nèi)容從 rowIndex=2開始 } JSONObject jo = (JSONObject) JSONObject.toJSON(obj); HSSFRow dataRow = sheet.createRow(rowIndex); for (int i = 0; i < properties.length; i++) { HSSFCell newCell = dataRow.createCell(i); Object o = jo.get(properties[i]); String cellValue = ""; if(o==null) cellValue = ""; else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o); else cellValue = o.toString(); newCell.setCellValue(cellValue); newCell.setCellStyle(cellStyle); } rowIndex++; } // 自動(dòng)調(diào)整寬度 /*for (int i = 0; i < headers.length; i++) { sheet.autoSizeColumn(i); }*/ try { workbook.write(out); workbook.close(); } catch (IOException e) { e.printStackTrace(); } } /** * 導(dǎo)出Excel 2007 OOXML (.xlsx)格式 * @param title 標(biāo)題行 * @param headMap 屬性-列頭 * @param jsonArray 數(shù)據(jù)集 * @param datePattern 日期格式,傳null值則默認(rèn) 年月日 * @param colWidth 列寬 默認(rèn) 至少17個(gè)字節(jié) * @param out 輸出流 */ public static void exportExcelX(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) { if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN; // 聲明一個(gè)工作薄 SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//緩存 workbook.setCompressTempFiles(true); //表頭樣式 CellStyle titleStyle = workbook.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 20); titleFont.setBoldweight((short) 700); titleStyle.setFont(titleFont); // 列頭樣式 CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font headerFont = workbook.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(headerFont); // 單元格樣式 CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); Font cellFont = workbook.createFont(); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); cellStyle.setFont(cellFont); // 生成一個(gè)(帶標(biāo)題)表格 SXSSFSheet sheet = workbook.createSheet(); //設(shè)置列寬 int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字節(jié)數(shù) int[] arrColWidth = new int[headMap.size()]; // 產(chǎn)生表格標(biāo)題行,以及設(shè)置列寬 String[] properties = new String[headMap.size()]; String[] headers = new String[headMap.size()]; int ii = 0; for (Iterator<String> iter = headMap.keySet().iterator(); iter .hasNext();) { String fieldName = iter.next(); properties[ii] = fieldName; headers[ii] = headMap.get(fieldName); int bytes = fieldName.getBytes().length; arrColWidth[ii] = bytes < minBytes ? minBytes : bytes; sheet.setColumnWidth(ii,arrColWidth[ii]*256); ii++; } // 遍歷集合數(shù)據(jù),產(chǎn)生數(shù)據(jù)行 int rowIndex = 0; for (Object obj : jsonArray) { if(rowIndex == 65535 || rowIndex == 0){ if ( rowIndex != 0 ) sheet = workbook.createSheet();//如果數(shù)據(jù)超過了,則在第二頁顯示 SXSSFRow titleRow = sheet.createRow(0);//表頭 rowIndex=0 titleRow.createCell(0).setCellValue(title); titleRow.getCell(0).setCellStyle(titleStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1)); SXSSFRow headerRow = sheet.createRow(1); //列頭 rowIndex =1 for(int i=0;i<headers.length;i++) { headerRow.createCell(i).setCellValue(headers[i]); headerRow.getCell(i).setCellStyle(headerStyle); } rowIndex = 2;//數(shù)據(jù)內(nèi)容從 rowIndex=2開始 } JSONObject jo = (JSONObject) JSONObject.toJSON(obj); SXSSFRow dataRow = sheet.createRow(rowIndex); for (int i = 0; i < properties.length; i++) { SXSSFCell newCell = dataRow.createCell(i); Object o = jo.get(properties[i]); String cellValue = ""; if(o==null) cellValue = ""; else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o); else if(o instanceof Float || o instanceof Double) cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString(); else cellValue = o.toString(); newCell.setCellValue(cellValue); newCell.setCellStyle(cellStyle); } rowIndex++; } // 自動(dòng)調(diào)整寬度 /*for (int i = 0; i < headers.length; i++) { sheet.autoSizeColumn(i); }*/ try { workbook.write(out); workbook.close(); workbook.dispose(); } catch (IOException e) { e.printStackTrace(); } } //Web 導(dǎo)出excel public static void downloadExcelFile(String title,Map<String,String> headMap,JSONArray ja,HttpServletResponse response){ try { ByteArrayOutputStream os = new ByteArrayOutputStream(); ExcelUtil.exportExcelX(title,headMap,ja,null,0,os); byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 設(shè)置response參數(shù),可以打開下載頁面 response.reset(); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename="+ new String((title + ".xlsx").getBytes(), "iso-8859-1")); response.setContentLength(content.length); ServletOutputStream outputStream = response.getOutputStream(); BufferedInputStream bis = new BufferedInputStream(is); BufferedOutputStream bos = new BufferedOutputStream(outputStream); byte[] buff = new byte[8192]; int bytesRead; while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } bis.close(); bos.close(); outputStream.flush(); outputStream.close(); }catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) throws IOException { int count = 100000; JSONArray ja = new JSONArray(); for(int i=0;i<100000;i++){ Student s = new Student(); s.setName("POI"+i); s.setAge(i); s.setBirthday(new Date()); s.setHeight(i); s.setWeight(i); s.setSex(i/2==0?false:true); ja.add(s); } Map<String,String> headMap = new LinkedHashMap<String,String>(); headMap.put("name","姓名"); headMap.put("age","年齡"); headMap.put("birthday","生日"); headMap.put("height","身高"); headMap.put("weight","體重"); headMap.put("sex","性別"); String title = "測試"; /* OutputStream outXls = new FileOutputStream("E://a.xls"); System.out.println("正在導(dǎo)出xls...."); Date d = new Date(); ExcelUtil.exportExcel(title,headMap,ja,null,outXls); System.out.println("共"+count+"條數(shù)據(jù),執(zhí)行"+(new Date().getTime()-d.getTime())+"ms"); outXls.close();*/ // OutputStream outXlsx = new FileOutputStream("E://b.xlsx"); System.out.println("正在導(dǎo)出xlsx...."); Date d2 = new Date(); ExcelUtil.exportExcelX(title,headMap,ja,null,0,outXlsx); System.out.println("共"+count+"條數(shù)據(jù),執(zhí)行"+(new Date().getTime()-d2.getTime())+"ms"); outXlsx.close(); } } class Student { private String name; private int age; private Date birthday; private float height; private double weight; private boolean sex; public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public float getHeight() { return height; } public void setHeight(float height) { this.height = height; } public double getWeight() { return weight; } public void setWeight(double weight) { this.weight = weight; } public boolean isSex() { return sex; } public void setSex(boolean sex) { this.sex = sex; } public void setAge(Integer age) { this.age = age; } }
2. 控制器Controller 的寫法
//導(dǎo)出配件列表 @RequestMapping(value = "partExport") @ResponseBody public void partExportHttpServletResponse response){ JSONArray ja = ptmpOrderService.selectStatExport();//獲取業(yè)務(wù)數(shù)據(jù)集 Map<String,String> headMap = ptmpOrderService.getPartStatHeadMap();//獲取屬性-列頭 String title = "配件統(tǒng)計(jì)表"; ExcelUtil.downloadExcelFile(title,headMap,ja,response); }
3.前端頁面的寫法(不要用異步方式請求,如$.post)
//可以點(diǎn)擊一個(gè)按鈕事件觸發(fā)下面的代碼進(jìn)行導(dǎo)出 window.open("partExport","_blank"); //或者可以提交表單 $('#form').attr('action','partExport'); $('#form').attr('target','_blank'); $('#form').submit();
4.POI依賴的jar包(maven pom)
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency>
5.本地測試
將10w條數(shù)據(jù)導(dǎo)出到本地硬盤中,HSSF方式用時(shí)14s左右,SXSSF方式用時(shí)24s左右,盡管如此,但建議使用SXSSF導(dǎo)出.xlsx的excel.
之所以使用JSONArray作為數(shù)據(jù)集,而沒有采用java的集合類,是因?yàn)镴SONObject 在獲取數(shù)據(jù)的時(shí)候是采用 get(key)的方式,正好與屬性列對應(yīng),這樣靈活性高,屬性列不必與java對象的字段匹配。而若使用java類,則要應(yīng)用反射,拼湊get方法,這樣更復(fù)雜點(diǎn)。
以上所述是小編給大家介紹的POI通用導(dǎo)出Excel(.xls,.xlsx)的方法詳解整合,希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持!
相關(guān)文章
java 輸入3個(gè)數(shù)a,b,c,按大小順序輸出的實(shí)例講解
今天小編就為大家分享一篇java 輸入3個(gè)數(shù)a,b,c,按大小順序輸出的實(shí)例講解,具有很好的參考價(jià)值,希望對大家有所幫助。一起跟隨小編過來看看吧2018-07-07SpringBoot使用AOP實(shí)現(xiàn)防重復(fù)提交功能
這篇文章主要為大家詳細(xì)介紹了SpringBoot如何使用AOP實(shí)現(xiàn)防重復(fù)提交功能,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下2024-03-03