Java中实现简单的Excel导出

素颜马尾好姑娘i 2022-05-23 09:04 68阅读 0赞

简单介绍一下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：

![70][]

参考文章：https://www.jianshu.com/p/d6a037a7e9d9

[70]: /images/20220523/57f4ad108897498fadb50c6b042f1609.png