浏览器下载Excel报表

package com.example.store.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * 导入导出Excel工具类
 * @Auther: yueyun.pan
 * @Date: 2019/1/17 09:59
 * @Description:
 */
public class ExcelUtils {

    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public static XSSFWorkbook getWorkbook(InputStream inStr, String fileName) throws Exception{
        XSSFWorkbook wb = null;
//        Workbook wb = new XSSFWorkbook();
        String fileType = fileName.substring(fileName.lastIndexOf("."));
//        String fileType = fileName;
//        if(excel2003L.equals(fileType)){
//            wb = new HSSFWorkbook(inStr);  //2003-
//        }else
          if(excel2007U.equals(fileType)){
            wb = new XSSFWorkbook(inStr);  //2007+
        }else{
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     * @param cell 单元格
     * @return
     */
    public static Object getCellValue(Cell cell){
        Object value = null;
        DecimalFormat df = new DecimalFormat("0.00");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字

        switch (cell.getCellType().getCode()) {
            case 1://字符型
                value = cell.getRichStringCellValue().getString();
                break;
            case 0://数字型
                if("General".equals(cell.getCellStyle().getDataFormatString())){
                    value = df.format(cell.getNumericCellValue());
                }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
                    value = sdf.format(cell.getDateCellValue());
                }else{
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case 4://布尔型
                value = cell.getBooleanCellValue();
                break;
            case 3://为空
                value = "";
                break;
            default:
                break;
        }
        return value;
    }

    /**
     * 描述：单个sheet（以第一个为准），获取IO流中的数据，组装成List<List<Object>>对象
     * @param in 文件流
     * @param fileName 文件名
     * @return
     * @throws IOException
     */
    public static List<List<Object>> getListByExcel(InputStream in,String fileName) throws Exception{

        List<List<Object>> list = new ArrayList<>();

        //创建Excel工作薄
        Workbook work = ExcelUtils.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        //获取Excel中第一个sheet
        sheet = work.getSheetAt(0);
        if(sheet==null){
            return null;
        }

        //遍历当前sheet中的所有行
        for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if(row==null){continue;}

            //遍历所有的列
            List<Object> li = new ArrayList<>();
            for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                cell = row.getCell(y);
                if(cell==null){
                    li.add("");
                }else{
                    li.add(ExcelUtils.getCellValue(cell));
                }
            }
            list.add(li);
        }
        in.close();
        return list;
    }

    /**
     * 描述：单个sheet（以第一个为准），获取IO流中的数据，组装成List<List<Object>>对象
     * @param in 文件流
     * @param fileName 文件名
     * @param colNum 列数
     * @return
     * @throws IOException
     */
    public static List<List<Object>> getListByExcel(InputStream in,String fileName,int colNum) throws Exception{

        List<List<Object>> list = new ArrayList<>();

        //创建Excel工作薄
        Workbook work = ExcelUtils.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        //获取Excel中第一个sheet
        sheet = work.getSheetAt(0);
        if(sheet==null){
            return null;
        }

        //遍历当前sheet中的所有行
        for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if(row==null){continue;}

            //遍历所有的列
            List<Object> li = new ArrayList<>();
            for (int y = row.getFirstCellNum(); y < colNum; y++) {
                cell = row.getCell(y);
                if(cell==null){
                    li.add("");
                }else{
                    li.add(ExcelUtils.getCellValue(cell));
                }
            }
            list.add(li);
        }
        in.close();
        return list;
    }

    /**
     * 描述：获取IO流中的数据，组装成Map<String, List<List<Object>>>对象
     * key 格式：sheet名称
     * @param in 文件流
     * @param fileName 文件名
     * @return
     * @throws IOException
     */
    public static HashMap<String,List<List<Object>>> getAllSheetData(InputStream in, String fileName) throws Exception{
        HashMap<String,List<List<Object>>> sheetMap = new HashMap<>();

        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = ExcelUtils.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            list = new ArrayList<>();
            String sheetName = work.getSheetName(i);
            sheet = work.getSheetAt(i);
            if(sheet==null){
                return null;
            }

            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null){continue;}

                //遍历所有的列
                List<Object> li = new ArrayList<>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    if(cell==null) {
                        li.add("");
                    }else {
                        li.add(ExcelUtils.getCellValue(cell));
                    }
                }
                if( !li.isEmpty()) { //空行不需要追加
                    list.add(li);
                }
            }
            sheetMap.put(sheetName, list);
        }
        in.close();
        return sheetMap;
    }



    /**
     * 描述：获取IO流中第一个sheet的数据，组装成List<List<Object>>对象
     * key 格式：sheet名称
     * @param in 文件流
     * @param fileName 文件名
     * @return
     * @throws IOException
     */
    public static List<List<String>> getFirstSheetData(InputStream in, String fileName) throws Exception{
        //创建Excel工作薄
        Workbook work = ExcelUtils.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("Excel工作薄为空！");
        }

        //遍历Excel中所有的sheet
        List<List<String>> list = new ArrayList<>();
        Sheet sheet = work.getSheetAt(0);
        if(sheet==null){
            return null;
        }
        Row row;
        Cell cell;
        String cellValue;
        Row firstRow= sheet.getRow(sheet.getFirstRowNum());
        int firstCellNo = firstRow.getFirstCellNum();
        int lastCellNo = firstRow.getLastCellNum();
        //遍历当前sheet中的所有行
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if(row==null){continue;}

            //遍历所有的列
            List<String> li = new ArrayList<>();
            for (int j = firstCellNo; j < lastCellNo; j++) {
                cell = row.getCell(j);
                cellValue = null;
                if(cell!=null) {
                    Object obj = ExcelUtils.getCellValue(cell);
                    if(obj!=null){
                        cellValue = String.valueOf(obj);
                    }
                }
                li.add(cellValue);
            }
            list.add(li);
        }
        in.close();
        return list;
    }
}

@ApiOperation("下载StoreMatrix示例文件")
    @ApiResponses({
            @ApiResponse(code = 200, response = File.class, message = "")
    })
    @RequestMapping(path = "/importStoreMatrixExample", produces="application/json", method= RequestMethod.GET)
    public void importStoreMatrixExample(HttpServletResponse response) throws Exception {
        org.springframework.core.io.Resource resource = new ClassPathResource("template/StoreMatrixExample.xlsx");
        InputStream inStr = resource.getInputStream();
        String fileName = "StoreMatrixExample.xlsx";
        XSSFWorkbook workbook = ExcelUtils.getWorkbook(inStr, fileName);

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            workbook.write(os);
        } catch (IOException e) {
            throw new Exception("报表导出失败！", e);
        }
        byte[] content = os.toByteArray();
        if (inStr != null) {
            inStr.close();
        }
        String encodedFileName = URLEncoder.encode(fileName + ".xlsx", "utf-8").replaceAll("\\+", "%20");
        // 清除buffer缓存
        response.reset();
        //设置导出Excel的名称
        response.setHeader("Content-disposition", "attachment;filename="+encodedFileName+"");
        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");

        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        //刷新缓冲
        response.flushBuffer();
        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        workbook.write(response.getOutputStream());
    }