java excel工具类

public class ExcelExportUtil {

private static final Logger log = LoggerFactory.getLogger(ExcelExportUtil.class);

/**
 * 导出
 *
 * @param title
 * @param headers
 * @param strArray
 * @param response
 * @throws UnsupportedEncodingException
 */
public static void exportExcel(String title, String[] headers, String[][] strArray, HttpServletResponse response) throws UnsupportedEncodingException {
    ExcelUtil excelUtil = new ExcelUtil();
    excelUtil.exportExcel(title, headers, strArray, response);
}

/**
 * @param workbook
 * @param response
 * @param fileName
 * @throws Exception
 */
public static void exportExcel(Workbook workbook, HttpServletResponse response, String fileName) throws Exception {
    OutputStream os = response.getOutputStream();
    response.reset();
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    response.setHeader("Access-Control-Allow-Origin", "*");
    response.setHeader("Access-Control-Allow-Methods", "POST,PUT,GET,DELETE");
    response.setHeader("Access-Control-Max-Age", "3600");
    response.setHeader("Access-Control-Allow-Headers", "*");
    response.setCharacterEncoding("utf-8");
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    workbook.write(os);
    os.flush();
    os.close();
}

/**
 * 导出文件
 *
 * @param response
 * @param file     完整文件路径，包括文件名称
 * @param fileName 文件名称 ，包括后缀
 * @throws Exception
 */
public static void exportExcel(HttpServletResponse response, String file, String fileName) throws Exception {
    //读取文件流
    FileReader fileReader = new FileReader(file);
    File file1 = fileReader.getFile();
    if (null == file1 || !file1.exists()) {
        throw new DoValidException("文件不存在");
    }
    BufferedInputStream inputStream = fileReader.getInputStream();
    Workbook sheets = ExcelExportUtil.chooseFormat(fileName, inputStream);
    exportExcel(sheets, response, fileName);
}

/**
 * 根据 list<t> 数据 导出excel
 *
 * @param response
 * @param data      实际数据
 * @param fileName  文件名称
 * @param sheetName 默认附表名称
 * @throws Exception
 */
public static <E> void exportExcel(HttpServletResponse response, List<E> data, Class<E> clazz, String fileName, String sheetName) throws Exception {
    EasyExcelUtil.exportExcel(response, data, clazz, fileName, sheetName);
}

/**
 * 根据 list<t> 数据 导出excel
 *
 * @param response
 * @param data     实际数据
 * @param fileName 文件名称
 * @throws Exception
 */
public static <E> void exportExcel(HttpServletResponse response, List<E> data, Class<E> clazz, String fileName) throws Exception {
    exportExcel(response, data, clazz, fileName, "sheet");
}

/**
 * 根据 list<t> 数据 导出excel
 * 先写excel，再读取，最后下载导出
 *
 * @param response
 * @param data      实际数据
 * @param fileName  文件名称
 * @param sheetName 默认附表名称
 * @throws Exception
 */
public static <E> void exportExcelByWriterAfter(HttpServletResponse response, List<E> data, Class<E> clazz, String filePath, String fileName, String sheetName) throws Exception {
    EasyExcelUtil.exportExcelByWriterAfter(response, data, clazz, filePath, fileName, sheetName);
}

/**
 * 根据 list<t> 数据 导出excel
 * 先写excel，再读取，最后下载导出
 *
 * @param response
 * @param data     实际数据
 * @param fileName 文件名称
 * @throws Exception
 */
public static <E> void exportExcelByWriterAfter(HttpServletResponse response, List<E> data, Class<E> clazz, String filePath, String fileName) throws Exception {
    exportExcelByWriterAfter(response, data, clazz, filePath, fileName, "sheet");
}

/**
 * 选择EXCEL版本（xls和xlsx）
 *
 * @param file 文件流（上传的文件流）
 * @return EXCEL版本
 * @throws Exception 向上抛出异常
 */
public static Workbook chooseExcelFormat(MultipartFile file) throws Exception {
    if (!FileUploaderManger.verify(file, FileTypeEnum.EXCEL)) {
        throw new DoValidException("不是正确的excel格式");
    }
    String fileName = file.getOriginalFilename();
    return chooseFormat(fileName, file.getInputStream());
}

/**
 * 读取静态文件夹下的模板Excel
 *
 * @param path 模板地址
 * @return EXCEL表格文件
 * @throws Exception 向上抛出异常
 */
public static Workbook readExcelExport(String path) throws Exception {
    Resource resource = new ClassPathResource(path);
    String fileName = resource.getFilename();
    InputStream is = resource.getInputStream();
    return ExcelExportUtil.chooseFormat(fileName, is);
}

/**
 * 选择EXCEL版本
 *
 * @param fileName 文件名
 * @param is       流
 * @return 结果
 * @throws Exception 向上抛出异常
 */
public static Workbook chooseFormat(String fileName, InputStream is) throws Exception {
    Workbook excelFile;
    byte[] buf = IOUtils.toByteArray(is);
    ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(buf);
    if (StringUtils.isEmpty(fileName)) {
        throw new DoValidException("文件名不存在");
    }
    String matches = "^.+\\.(?i)(xls)$";
    if (fileName.matches(matches)) {
        excelFile = new HSSFWorkbook(byteArrayInputStream);
    } else {
        excelFile = new XSSFWorkbook(byteArrayInputStream);
    }
    return excelFile;
}

/**
 * 以上一行为模板创建新的行
 *
 * @param sheet    表
 * @param rowNum   第几行
 * @param cellNums 创建列总数
 * @return 返回生成的行
 */
public static Row createRowAndCell(Sheet sheet, int rowNum, int cellNums) {
    Row row = sheet.getRow(rowNum);
    if (row == null) {
        row = sheet.createRow(rowNum);
        for (int j = 0; j <= cellNums; j++) {
            Cell upCell = sheet.getRow(rowNum - 1).getCell(j);
            Cell cell = row.createCell(j);
            cell.setCellStyle(upCell.getCellStyle());
        }
    }
    return row;
}

/**
 * 构造名称管理器和数据验证及公式
 *
 * @param workbook            目标工作簿
 * @param dropDownDataSource  以父级id为key,子级名称列表为value的集合
 * @param dataSourceSheetName 作为数据源的工作表名称
 * @param columnStep          起始列的列号（以下表0为初始列）
 * @param totalLevel          总共的层级数量
 * @throws IOException
 * @throws InvalidFormatException
 */
public static Workbook cascade(Workbook workbook, Map<String, List<String>> dropDownDataSource, final String dataSourceSheetName, final int columnStep, final int totalLevel) throws IOException, InvalidFormatException {

    Sheet dataSourceSheet = workbook.createSheet(dataSourceSheetName);
    workbook.setSheetHidden(workbook.getSheetIndex(dataSourceSheet), true);

    Row headerRow = dataSourceSheet.createRow(0);
    boolean firstTime = true;
    int columnIndex = 0;
    // 构造名称管理器数据源
    for (String key : dropDownDataSource.keySet()) {
        Cell cell = headerRow.createCell(columnIndex);
        cell.setCellValue(key);
        if (dropDownDataSource.get(key) == null || dropDownDataSource.get(key).size() == 0) {
            continue;
        }
        ArrayList<String> values = (ArrayList) dropDownDataSource.get(key);
        int dataRowIndex = 1;
        for (String value : values) {
            Row row = firstTime ? dataSourceSheet.createRow(dataRowIndex) : dataSourceSheet.getRow(dataRowIndex);
            if (row == null) {
                row = dataSourceSheet.createRow(dataRowIndex);
            }
            row.createCell(columnIndex).setCellValue(value);
            dataRowIndex++;
        }

        // 构造名称管理器
        String range = buildRange(columnIndex, 2, values.size());
        Name name = null;
        try {
            name = workbook.createName();
            name.setNameName(key);
        } catch (Exception e) {
            name = workbook.getName(key);
        }
        String formula = dataSourceSheetName + "!" + range;
        name.setRefersToFormula(formula);
        columnIndex++;
        firstTime = false;
    }


    Sheet assetSheet = workbook.getSheetAt(0);
    // 第一级设置DataValidation
    setValidationData(assetSheet, 1, 100, 0 + columnStep, 0 + columnStep, dataSourceSheet.getRow(0).getCell(0).toString());

    // 剩下的层级设置DataValidation
    for (int i = 1; i < totalLevel; i++) {
        char[] offset = new char[1];
        offset[0] = (char) ('A' + columnStep + i - 1);
        String formulaString = buildFormulaString(new String(offset), 1);
        CellRangeAddressList addressList = new CellRangeAddressList(1, 100, 0 + columnStep + i, 0 + columnStep + i);
        DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(formulaString);
        DataValidation validation = new HSSFDataValidation(addressList, dvConstraint);
        assetSheet.addValidationData(validation);
    }
    return workbook;
}

/**
 * 添加数据有效性检查.
 *
 * @param sheet         要添加此检查的Sheet
 * @param firstRow      开始行
 * @param lastRow       结束行
 * @param firstCol      开始列
 * @param lastCol       结束列
 * @param formulaString 有效性检查的name
 * @throws IllegalArgumentException 如果传入的行或者列小于0(< 0)或者结束行/列比开始行/列小
 */
public static void setValidationData(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, String formulaString) 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.createFormulaListConstraint(formulaString);
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        sheet.addValidationData(validation);
    } else if (sheet instanceof HSSFSheet) {
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(formulaString);
        DataValidation validation = new HSSFDataValidation(addressList, dvConstraint);
        sheet.addValidationData(validation);
    }
}

/**
 * 构造名称引用的数据源区域：
 */
private static String buildRange(int offset, int startRow, int rowCount) {
    char start = (char) ('A' + offset);
    return "$" + start + "$" + startRow + ":$" + start + "$" + (startRow + rowCount - 1);
}

/**
 * 构造indirect公式：
 */
private static String buildFormulaString(String offset, int rowNum) {
    return "INDIRECT($" + offset + (rowNum) + ")";
}

}