POI通用導(dǎo)出Excel(.xls,.xlsx)的方法

更新時(shí)間：2019年05月07日 15:26:59 作者：後雪寒

這篇文章主要介紹了POI通用導(dǎo)出Excel(.xls,.xlsx)的方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

POI操作EXCEL對(duì)象
HSSF：操作Excel 97(.xls)格式
XSSF：操作Excel 2007 OOXML (.xlsx)格式，操作EXCEL內(nèi)存占用高于HSSF
SXSSF:從POI3.8 beta3開始支持，基于XSSF，低內(nèi)存占用。

使用POI的HSSF對(duì)象，生成Excel 97(.xls)格式，生成的EXCEL不經(jīng)過(guò)壓縮直接導(dǎo)出。
線上問(wèn)題：負(fù)載服務(wù)器轉(zhuǎn)發(fā)請(qǐng)求到應(yīng)用服務(wù)器阻塞,以及內(nèi)存溢出。
如果系統(tǒng)存在大數(shù)據(jù)量報(bào)表導(dǎo)出，則考慮使用POI的SXSSF進(jìn)行EXCEL操作。

HSSF生成的Excel 97(.xls)格式本身就有每個(gè)sheet頁(yè)不能超過(guò)65536條的限制。
XSSF生成Excel 2007 OOXML (.xlsx)格式，條數(shù)增加了，但是導(dǎo)出過(guò)程中，內(nèi)存占用率卻高于HSSF.
SXSSF是自3.8-beta3版本后，基于XSSF提供的低內(nèi)存占用的操作EXCEL對(duì)象。其原理是可以設(shè)置或者手動(dòng)將內(nèi)存中的EXCEL行寫到硬盤中，這樣內(nèi)存中只保存了少量的EXCEL行進(jìn)行操作。

EXCEL的壓縮率特別高，能達(dá)到80%，12M的文件壓縮后才2M左右。如果未經(jīng)過(guò)壓縮、不僅會(huì)占用用戶帶寬，且會(huì)導(dǎo)致負(fù)載服務(wù)器（apache）和應(yīng)用服務(wù)器之間，長(zhǎng)時(shí)間占用連接(二進(jìn)制流轉(zhuǎn)發(fā))，導(dǎo)致負(fù)載服務(wù)器請(qǐng)求阻塞，不能提供服務(wù)。

一定要注意文件流的關(guān)閉

防止前臺(tái)（頁(yè)面）連續(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ù)超過(guò)了，則在第二頁(yè)顯示

        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ù)超過(guò)了，則在第二頁(yè)顯示

        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ù)，可以打開下載頁(yè)面
      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 = "測(cè)試";
    /*
    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.前端頁(yè)面的寫法（不要用異步方式請(qǐng)求，如$.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.本地測(cè)試

將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)的方式，正好與屬性列對(duì)應(yīng)，這樣靈活性高，屬性列不必與java對(duì)象的字段匹配。而若使用java類，則要應(yīng)用反射，拼湊get方法，這樣更復(fù)雜點(diǎn)。

以上所述是小編給大家介紹的POI通用導(dǎo)出Excel(.xls,.xlsx)的方法詳解整合，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

您可能感興趣的文章: