EasyPoiUtil导出工具

package com.zjson.alibaba.commons.tools.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.NoSuchElementException;

public class EasyPoiUtil {


    /**
     * 获取导出数据的book
     */
    public static Workbook getExcelDataWorkbook(List<?> list, Class<?> pojoClass){
        ExportParams exportParams = new ExportParams(null, "sheet1",ExcelType.HSSF);
        //设置导出格式
        exportParams.setStyle(ExcelStyleUtil.class);
        //把数据添加到excel表格中
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        return workbook;
    }



    /**
     * excel 导出
     *
     * @param workbook         数据列表
     * @param fileName     导出时的excel名称
     * @param response
     */
    public static void defaultExport(String fileName,Workbook workbook,HttpServletResponse response) throws IOException {
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * excel下载
     *
     * @param fileName 下载时的文件名称
     * @param response
     * @param workbook excel数据
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel数据导入
     */
    public static List<T> importDataFromExcel(MultipartFile file, Class<T> classObj) throws IOException {
        List<T> importData = importExcel(file.getInputStream(), 1, classObj);
        return importData;
    }


    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param sheetNum sheet页
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream,int sheetNum, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setSheetNum(sheetNum);
        params.setNeedSave(false);
        params.setHeadRows(1);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }


    /**
     * 添加批注
     * @param workbook
     * @param excelCommentList
     */
    public static void addSheetComments(Workbook workbook,List<ExcelComment> excelCommentList){
        Sheet sheet = workbook.getSheetAt(0);
        for (ExcelComment excelComment : excelCommentList) {
            Row row = sheet.getRow(excelComment.getRow());
            Cell cell = row.getCell(excelComment.getCell());
            // 创建绘图对象
            Drawing p = sheet.createDrawingPatriarch();
            // 获取批注对象 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
            Comment comment = p.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            // 输入批注信息
            comment.setString(new HSSFRichTextString(excelComment.getComment()));
            // 将批注添加到单元格对象中
            cell.setCellComment(comment);
        }
    }



    /**
     * 为表格设置数据验证
     * firstRow 开始行号(下标0开始)
     * lastRow  结束行号，最大65535
     * firstCol 区域中第一个单元格的列号 (下标0开始)
     * lastCol 区域中最后一个单元格的列号
     * dataArray 下拉内容
     * sheetHidden 影藏的sheet编号（例如1,2,3），多个下拉数据不能使用同一个
     * */
    public static void addSheetValidation(Workbook workbook, int firstRow, int lastRow, int firstCol, int lastCol, String[] dataArray, int sheetHidden){
        String hiddenName = "hidden_" + (int)((Math.random()*9+1)*100);
        Sheet sheet = workbook.getSheetAt(0);
        Sheet hidden = workbook.createSheet(hiddenName);
        Cell cell = null;
        for (int i = 0, length = dataArray.length; i < length; i++)
        {
            String name = dataArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }
        Name namedCell = workbook.createName();
        namedCell.setNameName(hiddenName);
        namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + dataArray.length);
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenName);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
        // 将sheet设置为隐藏
        //workbook.setSheetHidden(sheetHidden, true);
        sheet.addValidationData(validation);
    }

    /**
     *  文件流读取头字段
     * @param inp 文件流
     * @return 头字段
     */
    public static List<String> getHeaderFields(InputStream inp) {
        try {
            return  readHeader(0, 0, WorkbookFactory.create(inp));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return Collections.emptyList();
    }
    /**
     *  文件地址读取头字段
     * @param filePath 文件地址
     * @return 头字段
     */
    public static List<String> getHeaderFields(String filePath) {
        try {
            return readHeader(0, 0, WorkbookFactory.create(new File(filePath)));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return Collections.emptyList();
    }

    /**
     *  读取投文件
     * @param sheetNum sheetNum
     * @param rowNum rowNum
     * @param workbook workbook
     */
    public static List<String> readHeader(int sheetNum,int rowNum , Workbook workbook) {
        // 默认读取第一个sheet
        Sheet sheet = workbook.getSheetAt(sheetNum);
        // 默认读取第一行
        Row titleRow = sheet.getRow(rowNum);
        //有多少列
        int cellNum = titleRow.getLastCellNum();
        List<String> headerList = new ArrayList<>(cellNum);
        for (int i = 0; i < cellNum; i++) {
            //根据索引获取对应的列
            Cell cell = titleRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            //设置列的类型是字符串
            headerList.add(cell.getStringCellValue());
        }
        return headerList;
    }

    public static void main(String[] args) throws IOException {
        String filePath = "C:\\Users\\Administrator\\Desktop\\xc.xlsx";
        List<String> headerList = getHeaderFields(filePath);
        System.out.println(headerList);
    }
}