Java中实现简单的Excel导出

简单介绍一下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");
 }
}

浏览器输入链接：

http://localhost:8099/index/testExcelOutPut

即可自动下载测试数据组成的Excel：

来源：https://blog.csdn.net/hz_940611/article/details/80590488