Java中实现简单的Excel导出

2019-11-26 09:20:08

字体：大中小

来源：转载

供稿：网友

简单介绍一下Java中的Excel文件导出功能（基于HttpServletResponse实现下载）

首先，引入需要依赖的jar包：

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version></dependency><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version></dependency>

编写一个工具类：

package exceloutput;import org.apache.commons.lang3.StringUtils;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.UnsupportedEncodingException;import java.util.List;import java.util.UUID;/** * @author haozz * @date 2018/6/6 9:57 * @description excel导出抽象工具类 **/public abstract class ExportAbstractUtil {  public void write(HttpServletResponse response, Workbook workbook){    String fileName = UUID.randomUUID().toString()+".xls";    pwrite(response,workbook,fileName);  }  public void write(HttpServletResponse response,Workbook workbook,String fileName){    if(StringUtils.isEmpty(fileName)){      fileName = UUID.randomUUID().toString()+".xls";    }    pwrite(response,workbook,fileName);  }  public void write(HttpServletResponse response, List<List<String>> lists,String fileName){    if(StringUtils.isEmpty(fileName)){      fileName = UUID.randomUUID().toString()+".xls";    }    SXSSFWorkbook workbook = new SXSSFWorkbook(lists.size());    SXSSFSheet sheet = workbook.createSheet(fileName.substring(0,fileName.indexOf(".xls")));    Integer rowIndex = 0;    Row row = null;    Cell cell = null;    for(List<String> rowData: lists ){      Integer columnIndex = 0;      row = sheet.createRow(rowIndex++);      for(String columnVal:rowData){        cell = row.createCell(columnIndex++);        cell.setCellValue(columnVal);      }    }    pwrite(response,workbook,fileName);  }  private void pwrite(HttpServletResponse response,Workbook workbook,String fileName){    response.setCharacterEncoding("UTF-8");    response.setContentType("application/vnd.ms-excel;charset=UTF-8");    try {      response.addHeader("Content-Disposition", "attachment; filename="+new String(fileName.getBytes("UTF-8"),"ISO8859-1"));    } catch (UnsupportedEncodingException e) {      e.printStackTrace();      fileName= UUID.randomUUID().toString()+".xls";      response.addHeader("Content-Disposition", "attachment; filename="+fileName);    }    try {      workbook.write(response.getOutputStream());    } catch (IOException e) {      e.printStackTrace();    }  }}

有了这个工具类就可以实现Excel导出了，代码不难，这里就不多解释了。

在SpringBoot项目中编写一个导出Excel的Controller，并继承上面的ExportAbstractUtil，给出一个接口用作测试：

package com.csdn.myboot.controller;import com.csdn.myboot.utils.ExportAbstractUtil;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.ResponseBody;import javax.servlet.http.HttpServletResponse;import java.util.ArrayList;import java.util.Arrays;import java.util.List;/** * @author haozz * @date 2018/6/6 10:14 * @description **/@Controller@RequestMapping(value = "/index")public class HelloCtrl extends ExportAbstractUtil{  @RequestMapping(value = "/testExcelOutPut")  @ResponseBody  public void testExcelOutPut(HttpServletResponse response){    //拼接数据start    List<List<String>> lists = new ArrayList<List<String>>();    String rows[] = {"year","month","day"};    List<String> rowsTitle = Arrays.asList(rows);    lists.add(rowsTitle);    for(int i = 0; i<=9;i++){      String [] rowss = {"1","2","3"};      List<String> rowssList = Arrays.asList(rowss);      lists.add(rowssList);    }    //拼接数据end    write(response,lists,"导出Excel.xls");  }}

浏览器输入链接：