java中实现导出excel文件（封装方法，极简导出）（附excelutil工具类）

导出excel规避导出文件失败与导出文件无法打开问题

1.首先在controller层需要获取 HttpServletResponse

@ApiOperation(value = "导出", notes = "导出")
    @RequestMapping(value = "/exportInfo", method = RequestMethod.POST)
    public void exportOperLogInfo(@RequestBody InfoReq infoReq , HttpServletResponse response) throws Exception{
    	//1.获取请求实体的参数数据存入HashMap()
    	Map<String, Object> map = new HashMap<String, Object>();
    	//此处代码省略
        //2.进行导出
        exportService.export(map,response);
    }

2.实现类里进行数据查询与导出操作

public void exportMenuButtonOperLog(Map<String, Object> paramMap, HttpServletResponse response){
		//获取数据存入List<Map>
		//数据来源省略
        List<Map> list= new ArrayList();
        //设置路径
        String filePath = "";
        //文件名
        String fileName = "info.xlsx";
        //设置表头
        String[] header = new String[]{"表头1", "表头2"};
        List<String[]> excelData = new ArrayList<>();
        //遍历list获取每个map中的数据，根据key对应不同的列
        for (Map map : list) {
            String[] data = new String[]{
                    (String) map.get("字段一"),
                    (String) map.get("字段二")
            };
            excelData.add(data);
        }
        ExcelUtil.toExport(filePath, fileName, header, excelData, response);
    }

3.使用ExcelUtil中的方法进行导出，以下是ExcelUtil类

public class ExcelUtil {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    public static void buildExcel(SXSSFWorkbook workbook, String[] titles, String sheetName, int sheetNum, List<Map<String, Object>> list,File csvFile) throws Exception {
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(sheetName);
        Row sheetRow = sheet.createRow(0);
        if (titles != null) {
            for (int i = 0; i < titles.length; i++) {
                sheetRow.createCell(i).setCellValue(titles[i]);
            }
        }
        if (list != null && list.size() > 0) {
            logger.info("===buildExcel====22222222222222========="+list.size());
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> map = list.get(i);
                Row listRow = sheet.createRow(i + 1);
                int num = 0;
                for (String key : map.keySet()) {
                    listRow.createCell(num++).setCellValue(String.valueOf(map.get(key)));
                }
            }
            list.clear();
        }
        logger.info("===buildExcel====33333333333333333=========");
        writeToFile(workbook, csvFile);

    }

    public static void createExcel(SXSSFWorkbook workbook, String filePath, String fileName) {
        try {
            FileOutputStream fout = new FileOutputStream(filePath + fileName);
            workbook.write(fout);
            fout.flush();
            fout.close();
            workbook.dispose();
            Process pro = Runtime.getRuntime().exec("chmod 777 " + filePath + fileName);
            pro.waitFor();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        
    }

    /**
     * 读取本地文件
     *
     * @param workbook
     * @param fileName
     * @return
     * @throws Exception
     */
    public static ResponseEntity<byte[]> getResponseEntity(SXSSFWorkbook workbook, String fileName) throws Exception {
        fileName = fileName + ".xlsx";
        String filePath = "/slview/iot-ssp-server/iot-server/sspfile/tempfile/";
        File dir = new File(filePath);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        File file = new File(filePath + fileName);
        if (!file.exists()) {
            file.createNewFile();
        }
        ExcelUtil.createExcel(workbook, filePath, fileName);
        Thread.sleep(1000);
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.setContentDispositionFormData("attachment", fileName);
        ResponseEntity<byte[]> re = new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.CREATED);
        if (file.exists()) {
            // file.delete();
        }
        return re;
    }

    

    /**
     * excel
     *
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public static List getListByExcel(InputStream in, String fileName) throws Exception {

        List list = new ArrayList<>();

        // 创建excel工作簿
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }

        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        for (int i = 0; i < work.getNumberOfSheets(); i++) {

            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

//            if(!sheet.getSheetName().equals("结果表")){
//                continue;
//            }

            // 滤过第一行标题
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || row.getFirstCellNum() == j) {
                    continue;
                }


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

                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(getCellValue(cell));

                }
                list.add(li);
            }
        }
        work.close();
        return list;
    }


    public static Map<String, List> getSheetListByExcel(InputStream in, String fileName) throws Exception {
        Map<String, List> result = new LinkedHashMap<>();


        // 创建excel工作簿
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }

        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        for (int i = 0; i < work.getNumberOfSheets(); i++) {


            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            List list = new ArrayList<>();
            String sheetName = work.getSheetName(i);
//            if(!sheet.getSheetName().equals("结果表")){
//                continue;
//            }

            // 滤过第一行标题
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null || row.getFirstCellNum() == j) {
                    continue;
                }


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

                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(getCellValue(cell));

                }
                list.add(li);
            }
            result.put(sheetName, list);
        }
        work.close();
        return result;
    }

    public static String getCellValue(Cell cell) {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }

        CellType cellType = cell.getCellTypeEnum();
        // 把数字当成String来读，避免出现1读成1.0的情况
//        if (cellType == CellType.NUMERIC) {
//            cell.setCellType(CellType.STRING);
//        }

        // 判断数据的类型
        switch (cellType) {
            case NUMERIC: // 数字、日期
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    cellValue = fmt.format(cell.getDateCellValue()); // 日期型
                } else {
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getStringCellValue());
//                cellValue = String.valueOf(cell.getNumericCellValue()); // 数字
                    if (cellValue.contains("E")) {
                        cellValue = String.valueOf(new Double(cell.getNumericCellValue()).longValue()); // 数字
                    }
                }
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: // 空值
                cellValue = cell.getStringCellValue();
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * 判断文件格式
     *
     * @param in
     * @param fileName
     * @return
     */
    private static Workbook getWorkbook(InputStream in, String fileName) throws Exception {


        Workbook book = null;
        String filetype = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(filetype)) {
            book = new HSSFWorkbook(in);
        } else if (".xlsx".equals(filetype)) {
            book = new XSSFWorkbook(in);
        } else {
            throw new Exception("请上传excel文件！");
        }


        return book;
    }

    /**
     * 导出excel，文件名和sheet页名称相同使用该方法
     *
     * @param fileName
     * @param titles
     * @param dataList
     * @return
     */
    public static void toExport(String filePath, String fileName, final String[] titles,
                                final List<String[]> dataList, HttpServletResponse response) {
        try {
            toExport(filePath, fileName, null, titles, null, dataList, response);
        } catch (Exception e) {
            logger.error("导出Excel文件失败！", e);
            throw new RuntimeException(e);
        }
    }


    /**
     * 创建一个excel文件
     *
     * @return
     */
    private static File createFile(String filePath, String fileName) {
        if (StringUtils.isBlank(fileName)) {
            synchronized (ExcelUtil.class) {
                fileName = new Long(System.currentTimeMillis()).toString();
            }
        }
        File excelFile = new File(filePath + fileName);
        if (excelFile != null && !excelFile.exists()) {
            try {
                File fileParent = excelFile.getParentFile();
                if (!fileParent.exists()) {
                    fileParent.mkdirs();
                }
                excelFile.createNewFile();
            } catch (IOException e) {
                logger.error("创建Excel文件失败！", e);
            }
        }
        return excelFile;
    }

    /**
     * 新建sheet页
     *
     * @param workbook
     * @param sheetName
     * @return
     */
    private static Sheet createSheet(Workbook workbook, String sheetName) {
        Sheet sheet = workbook.createSheet(sheetName);
        return sheet;
    }

    /**
     * 设置标题
     *
     * @param workbook
     * @param sheet
     * @param titles
     */
    private static void setTitleCells(Workbook workbook, Sheet sheet, String[] titles, int[] titlesWidth) {
        if (workbook != null) {
            if (titlesWidth == null || titlesWidth.length != titles.length) {
                titlesWidth = new int[titles.length];
                for (int i = 0; i < titles.length; i++) {
                    titlesWidth[i] = 20;
                }
            }

            Row header = sheet.createRow(0);// 第一行为标题行，从0开始
//			CellStyle style = getTitleCellStyle(workbook);
            for (int i = 0; i < titles.length; i++) {
                sheet.setColumnWidth(i, titlesWidth[i] * 256);// in units of 1/256th of a character width),默认为20个字符
                Cell cell = header.createCell(i);
//				cell.setCellStyle(style);
                cell.setCellValue(titles[i]);
            }
        }
    }

    /**
     * 设置标题
     *
     * @param workbook
     * @param sheet
     */
    private static void setAITitleCells(XSSFWorkbook workbook, Sheet sheet) {
        if (workbook != null) {
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);

            XSSFRow row0 = (XSSFRow) sheet.createRow(0);
            row0.createCell(0).setCellValue("稽核账期");
            row0.createCell(1).setCellValue("省份");
            XSSFCell cell = row0.createCell(2);
            cell.setCellStyle(cellStyle);
            cell.setCellValue("稽核场景");

            XSSFRow row1 = (XSSFRow) sheet.createRow(1);
            row1.createCell(2).setCellValue("错误类型");
            XSSFCell cell1 = row1.createCell(3);
            cell1.setCellStyle(cellStyle);
            cell1.setCellValue("关键局数据配置");
            XSSFCell cell2 = row1.createCell(6);
            cell2.setCellStyle(cellStyle);
            cell2.setCellValue("H码号段配置");
            XSSFCell cell3 = row1.createCell(9);
            cell3.setCellStyle(cellStyle);
            cell3.setCellValue("定向流量配置");
            XSSFCell cell4 = row1.createCell(12);
            cell4.setCellStyle(cellStyle);
            cell4.setCellValue("数通设备");

            XSSFRow row2 = (XSSFRow) sheet.createRow(2);
            row2.createCell(3).setCellValue("错误行数");
            row2.createCell(4).setCellValue("确认行数");
            row2.createCell(5).setCellValue("确认率");
            row2.createCell(6).setCellValue("错误行数");
            row2.createCell(7).setCellValue("确认行数");
            row2.createCell(8).setCellValue("确认率");
            row2.createCell(9).setCellValue("错误行数");
            row2.createCell(10).setCellValue("确认行数");
            row2.createCell(11).setCellValue("确认率");
            row2.createCell(12).setCellValue("错误行数");
            row2.createCell(13).setCellValue("确认行数");
            row2.createCell(14).setCellValue("确认率");

            sheet.addMergedRegion(new CellRangeAddress(0,2,0,0));
            sheet.addMergedRegion(new CellRangeAddress(0,2,1,1));
            sheet.addMergedRegion(new CellRangeAddress(0,0,2,14));
            sheet.addMergedRegion(new CellRangeAddress(1,2,2,2));
            sheet.addMergedRegion(new CellRangeAddress(1,1,3,5));
            sheet.addMergedRegion(new CellRangeAddress(1,1,6,8));
            sheet.addMergedRegion(new CellRangeAddress(1,1,9,11));
            sheet.addMergedRegion(new CellRangeAddress(1,1,12,14));
        }
    }

    /**
     * 设置数据信息
     *
     * @param workbook
     * @param sheet
     * @param dataList
     */
    private static void setDataCells(Workbook workbook, Sheet sheet, List<String[]> dataList) {
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + 1);// 第二行开始
            String[] data = dataList.get(i);
            for (int j = 0; j < data.length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(data[j]);
            }
        }
    }


    private static String divide(String num, String row) {
        String orderRate = "";
        if (StringUtils.isEmpty(num) || "0".equals(num)) {
            return orderRate;
        } else {
            row = StringUtils.isEmpty(row) ? "0" : row;
            Integer numint = Integer.parseInt(num);
            Integer rowint = Integer.parseInt(row);
            DecimalFormat df = new DecimalFormat();
            df.setMaximumFractionDigits(2); //设置数值的小数部分允许的最大位数。
            df.setMinimumFractionDigits(2); //设置数值的小数部分允许的最小位数。
            orderRate = df.format(rowint * 100.00 / numint) + "%";
        }
        return orderRate;
    }

    /**
     * 写入数据到本地excel文件中
     *
     * @param workbook
     * @return
     */
    public static void writeToFile(Workbook workbook, File excelFile) {
        FileOutputStream fileOut = null;
        logger.info("===buildExcel====6666666666666=========");
        try {
            fileOut = new FileOutputStream(excelFile);
            workbook.write(fileOut);
            logger.info("===buildExcel====999999999999999999=========");
        } catch (FileNotFoundException e) {
            logger.error("Excel文件不存在！");
        } catch (IOException e) {
            logger.error("数据写入到Excel文件失败！");
        } finally {
            if (fileOut != null) {
                try {
                    fileOut.flush();
                    fileOut.close();
                } catch (IOException e) {
                    logger.error("关闭文件输出流失败！");
                }
            }
        }
    }

    /**
     * 导出Excel, fileName为空根据当前系统时间自动生成一个文件名，
     * sheetName为空，如果fileName不为空，则根据fileName生成sheet页名称，否则生成默认的sheet页名称
     *
     * @param fileName  excel文件名
     * @param sheetName excel sheet页名称
     * @param titles    sheet页列表名称
     * @param dataList  sheet页列表数据，对应title
     * @return 返回excel下载路径。相对于前台根目录
     */
    public static void toExport(String filePath, String fileName, String sheetName, final String[] titles,
                                final int[] titlesWidth, final List<String[]> dataList, HttpServletResponse response) {
        FileInputStream myStream = null;
        try {
//            Workbook workbook = new HSSFWorkbook(); // or new XSSFWorkbook();
            Workbook workbook = null;
            String filetype = fileName.substring(fileName.lastIndexOf("."));
            if (".xls".equals(filetype)) {
                workbook = new HSSFWorkbook();
            } else if (".xlsx".equals(filetype)) {
                workbook = new XSSFWorkbook();
            } else {
                workbook = new HSSFWorkbook();
            }

            if (fileName != null && sheetName == null) {
                sheetName = fileName;
            }
            Sheet sheet = createSheet(workbook, sheetName);
            // 标题
            setTitleCells(workbook, sheet, titles, titlesWidth);

            setDataCells(workbook, sheet, dataList);

            File excelFile = createFile(filePath, fileName);

            if (excelFile != null) {
                writeToFile(workbook, excelFile);
            }
            response.setContentType("application/vnd.ms-excel");
            response.setContentLength((int) excelFile.length());
            String headerKey = "Content-Disposition";
            String headerValue = String.format("attachment; filename=\"%s\"", "" + fileName);
            response.setHeader(headerKey, headerValue);
            myStream = new FileInputStream(excelFile);
            IOUtils.copy(myStream, response.getOutputStream());
            response.flushBuffer();
        } catch (Exception e) {
            logger.error("导出Excel文件失败！", e);
            throw new RuntimeException(e);
        } finally {
            if (myStream != null) {
                try {
                    myStream.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}