poi导出excel工具类

package com.hfpmp.common.controller;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

@Controller
@RequestMapping("/autoEx")
public class AutoExportExcelUtilController {
    /**
     * @param @param filePath  Excel文件路径
     * @param @param handers   Excel列标题(数组)
     * @param @param downData  下拉框数据(数组)
     * @param @param downRows  下拉列的序号(数组,序号从0开始)
     * @return void
     * @throws
     * @Title: createExcelTemplate
     * @Description: 生成全新的Excel导入模板
     */
    private static void createExcelTemplate(String filePath, String[] handers,
                                            List<String[]> downData, String[] downRows) {
        HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
        //表头样式
//        HSSFCellStyle style = wb.createCellStyle();
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//        //字体样式
//        HSSFFont fontStyle = wb.createFont();
//        fontStyle.setFontName("微软雅黑");
//        fontStyle.setFontHeightInPoints((short) 12);
//        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//        style.setFont(fontStyle);

        //新建sheet
        HSSFSheet sheet1 = wb.createSheet("Sheet1");
        //生成sheet1内容
        Row rowFirst = sheet1.createRow(0);//第一个sheet的第一行为标题
        //写标题
        for (int i = 0; i < handers.length; i++) {
            Cell cell = rowFirst.createCell(i); //获取第一行的每个单元格
            sheet1.setColumnWidth(i, 4000); //设置每列的列宽
//            cell.setCellStyle(style); //加样式
            cell.setCellValue(handers[i]); //往单元格里写数据
        }
        for (int r = 0; r < downRows.length; r++) {
            String[] dlData = downData.get(r);//获取下拉对象
            int rownum = Integer.parseInt(downRows[r]);
            sheet1.addValidationData(setDataValidation(sheet1, dlData, 1, 50000, rownum, rownum)); //超过255个报错
        }

        try {

//            File f = new File(filePath); //写文件
//            //不存在则新增
//            if (!f.getParentFile().exists()) {
//                f.getParentFile().mkdirs();
//            }
//            if (!f.exists()) {
//                f.createNewFile();
//            }
//            FileOutputStream out = new FileOutputStream(f);
            FileOutputStream fileOut = new FileOutputStream("testte.xls");
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param @param filePath  Excel文件路径
     * @param @param handers   Excel列标题(数组)
     * @param @param downData  下拉框数据(数组)
     * @param @param downRows  下拉列的序号(数组,序号从0开始)
     * @return void
     * @throws
     * @Title: createExcelTemplate
     * @Description: 先读取现有的模板然后在模板中填充数据
     */
    private static void createExcel(String filePath, String[] handers,
                                    List<String[]> downData, String[] downRows) throws IOException, InvalidFormatException {
        File file = new File(filePath);
        FileInputStream in = new FileInputStream(file);
        //创建工作薄
        Workbook wb = WorkbookFactory.create(in);
        //获得sheet
        Sheet sheet1 = wb.getSheetAt(0);
        for (int r = 0; r < downRows.length; r++) {
            String[] dlData = downData.get(r);//获取下拉对象
            int rownum = Integer.parseInt(downRows[r]);
            sheet1.addValidationData(setDataValidation(sheet1, dlData, 1, 50, rownum, rownum)); //超过255个报错
        }
        try {
//            File f = new File(filePath); //写文件
//            //不存在则新增
//            if (!f.getParentFile().exists()) {
//                f.getParentFile().mkdirs();
//            }
//            if (!f.exists()) {
//                f.createNewFile();
//            }


            FileOutputStream fileOut = new FileOutputStream("tete.xls");
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param @param  uuid
     * @param @param  request
     * @param @param  response
     * @param @return
     * @return Data
     * @throws
     * @Title: getExcelTemplate
     * @Description: 生成Excel模板并导出
     */
    @RequestMapping("/getExcelTemplate")
    public void getExcelTemplate(HttpServletRequest request, HttpServletResponse response) {
        String fileName = "员工信息表.xls"; //模板名称
        String[] handers = {"姓名", "性别", "证件类型", "证件号码", "服务结束时间", "参保地", "民族"}; //列标题
        //下拉框数据
        List<String[]> downData = new ArrayList();
        String[] str1 = {"男", "女", "未知"};
        String[] str2 = {"北京", "上海", "广州", "深圳", "武汉", "长沙", "湘潭"};
        String[] str3 = {"01-汉族", "02-蒙古族", "03-回族", "04-藏族", "05-维吾尔族", "06-苗族", "07-彝族", "08-壮族", "09-布依族"};
        downData.add(str1);
        downData.add(str2);
        downData.add(str3);
        String[] downRows = {"1", "5", "7"}; //下拉的列序号数组(序号从0开始)
        try {
            downExcelTemplate(fileName, response, request);
        } catch (Exception e) {
            System.err.print("批量导入信息异常：" + e.getMessage());
        }
    }

    /**
     * @param @param filePath  文件路径
     * @return void
     * @throws
     * @Title: delFile
     * @Description: 删除文件
     */
    public static void delFile(String filePath) {
        java.io.File delFile = new java.io.File(filePath);
        delFile.delete();
    }

    /**
     * @param @param url 文件路径
     * @param @param fileName  文件名
     * @param @param response
     * @return void
     * @throws
     * @Title: getExcel
     * @Description: 下载指定路径的Excel文件
     */
    public static void downExcelTemplate(String fileName, HttpServletResponse response, HttpServletRequest request) {

        try {
            //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
            response.setContentType("multipart/form-data");
            //2.设置文件头：最后一个参数是设置下载文件名
            response.setHeader("Content-disposition", "attachment; filename=\""
                    + encodeChineseDownloadFileName(request, fileName + ".xls") + "\"");
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xls"); //中文文件名

            HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
            //表头样式
//        HSSFCellStyle style = wb.createCellStyle();
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//        //字体样式
//        HSSFFont fontStyle = wb.createFont();
//        fontStyle.setFontName("微软雅黑");
//        fontStyle.setFontHeightInPoints((short) 12);
//        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//        style.setFont(fontStyle);

            //新建sheet
            HSSFSheet sheet1 = wb.createSheet("new Sheet1");
            //通过文件路径获得File对象
            //3.通过response获取OutputStream对象(out)
            OutputStream out = new BufferedOutputStream(response.getOutputStream());
            wb.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            System.err.println("下载Excel模板异常" + e);
        }
    }

    /**
     * @param @param url 文件路径
     * @param @param fileName  文件名
     * @param @param response
     * @return void
     * @throws
     * @Title: getExcel
     * @Description: 下载指定路径的Excel文件
     */
    public static void getExcel(String url, String fileName, HttpServletResponse response, HttpServletRequest request) {

        try {
            //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
            response.setContentType("multipart/form-data");
            //2.设置文件头：最后一个参数是设置下载文件名
            response.setHeader("Content-disposition", "attachment; filename=\""
                    + encodeChineseDownloadFileName(request, fileName + ".xls") + "\"");
//            response.setHeader("Content-Disposition", "attachment;filename="
//                    + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xls"); //中文文件名

            //通过文件路径获得File对象
            File file = new File(url);

            FileInputStream in = new FileInputStream(file);
            //3.通过response获取OutputStream对象(out)
            OutputStream out = new BufferedOutputStream(response.getOutputStream());

            int b = 0;
            byte[] buffer = new byte[2048];
            while ((b = in.read(buffer)) != -1) {
                out.write(buffer, 0, b); //4.写到输出流(out)中
            }

            in.close();
            out.flush();
            out.close();

        } catch (IOException e) {
            System.err.println("下载Excel模板异常" + e);
        }
    }

    /**
     * @param @param  request
     * @param @param  pFileName
     * @param @return
     * @param @throws UnsupportedEncodingException
     * @return String
     * @throws
     * @Title: encodeChineseDownloadFileName
     * @Description: TODO(这里用一句话描述这个方法的作用)
     */
    private static String encodeChineseDownloadFileName(HttpServletRequest request, String pFileName)
            throws UnsupportedEncodingException {

        String filename = null;
        String agent = request.getHeader("USER-AGENT");
        //System.out.println("agent==========》"+agent);
        if (null != agent) {
            if (-1 != agent.indexOf("Firefox")) {//Firefox
                filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8")))) + "?=";
            } else if (-1 != agent.indexOf("Chrome")) {//Chrome
                filename = new String(pFileName.getBytes(), "ISO8859-1");
            } else {//IE7+
                filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
                filename = StringUtils.replace(filename, "+", "%20");//替换空格
            }
        } else {
            filename = pFileName;
        }

        return filename;
    }

    /**
     * @param @param  strFormula
     * @param @param  firstRow   起始行
     * @param @param  endRow     终止行
     * @param @param  firstCol   起始列
     * @param @param  endCol     终止列
     * @param @return
     * @return HSSFDataValidation
     * @throws
     * @Title: SetDataValidation
     * @Description: 下拉列表元素很多的情况 (255以上的下拉)
     */
    private static HSSFDataValidation SetDataValidation(String strFormula,
                                                        int firstRow, int endRow, int firstCol, int endCol) {
        // 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);

        dataValidation.createErrorBox("Error", "Error");
        dataValidation.createPromptBox("", null);

        return dataValidation;
    }

    /**
     * @param @param  sheet
     * @param @param  textList
     * @param @param  firstRow
     * @param @param  endRow
     * @param @param  firstCol
     * @param @param  endCol
     * @param @return
     * @return DataValidation
     * @throws
     * @Title: setDataValidation
     * @Description: 下拉列表元素不多的情况(255以内的下拉)
     */
    private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {

        DataValidationHelper helper = sheet.getDataValidationHelper();
        //加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
        //DVConstraint constraint = new DVConstraint();
        constraint.setExplicitListValues(textList);

        //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);

        //数据有效性对象
        DataValidation data_validation = helper.createValidation(constraint, regions);
        return data_validation;
    }

    /**
     * 添加数据有效性检查.
     *
     * @param sheet    要添加此检查的Sheet
     * @param firstRow 开始行
     * @param lastRow  结束行
     * @param firstCol 开始列
     * @param lastCol  结束列
     * @throws IllegalArgumentException 如果传入的行或者列小于0(< 0)或者结束行/列比开始行/列小
     */
    public static DataValidation setValidationData(Sheet sheet, String[] explicitListValues, int firstRow, int lastRow,
                                                   int firstCol, int lastCol) throws IllegalArgumentException {
        if (firstRow < 0 || lastRow < 0 || firstCol < 0 || lastCol < 0 || lastRow < firstRow || lastCol < firstCol) {
            throw new IllegalArgumentException("Wrong Row or Column index : " + firstRow + ":" + lastRow + ":" + firstCol + ":" + lastCol);
        }
        if (sheet instanceof XSSFSheet) {
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(explicitListValues);
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            return validation;
        } else if (sheet instanceof HSSFSheet) {
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(explicitListValues);
            DataValidation validation = new HSSFDataValidation(addressList, dvConstraint);
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            return validation;
        }
        return null;
    }

    public static void main(String[] args) {
        String fileName = "testte.xls"; //模板名称
        String[] handers = {"姓名", "性别", "证件类型", "证件号码", "服务结束时间", "参保地", "民族"}; //列标题
        //下拉框数据
        List<String[]> downData = new ArrayList();
        String[] str1 = {"男", "女", "未知"};
        String[] str2 = {"北京", "上海", "广州", "深圳", "武汉", "长沙", "湘潭"};
        String[] str3 = {"01-汉族", "02-蒙古族", "03-回族", "04-藏族", "05-维吾尔族", "06-苗族", "07-彝族", "08-壮族", "09-布依族"};
        downData.add(str1);
        downData.add(str2);
        downData.add(str3);
        String[] downRows = {"1", "5", "6"}; //下拉的列序号数组(序号从0开始)
        try {
            createExcel(fileName, handers, downData, downRows);
            createExcelTemplate(fileName, handers, downData, downRows);
//            downExcelTemplate(fileName, handers, downData, downRows);
        } catch (Exception e) {
            System.err.print("批量导入信息异常：" + e.getMessage());
        }
    }
}