easyExcel读写操作

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentFontStyle;
import com.alibaba.excel.metadata.property.ColumnWidthProperty;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import dcocd.custom.slsyxt.entity.ExcelGoods;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


public class ExcelUtil {
    /** 根据指定表头生成表格
     * @param dataList  表格数据
     * @param fileName  文件名
     * @param sheetName 表名
     * @param headList  表头
     * @param response  响应
     * @throws IOException
     */
    public static void DownloadExcel(List<Map> dataList, String fileName, String sheetName, List<String> headList, HttpServletResponse response) throws IOException {
        //组装数据
        ArrayList<List> lists = new ArrayList<>();
        for (int i = 0; i < dataList.size(); i++) {
            Map map = dataList.get(i);
            ArrayList<String> tempList = new ArrayList<>();
            tempList.add(String.valueOf(i + 1));//添加序号

            map.forEach((key, value) -> {
                tempList.add(String.valueOf(value));
            });
            lists.add(tempList);
        }


        //设置返回头
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName +DateUtil.getTimeByNow()+ ".xlsx");

        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.index);

        // 字体大小
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short)12);
        headWriteCellStyle.setWriteFont(contentWriteFont);



        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//        contentWriteCellStyle.setShrinkToFit(true); //自动调整字体大小适应表格宽度

        //设置单元格策略
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        //设置列宽策略
        LongestMatchColumnWidthStyleStrategy longest = new LongestMatchColumnWidthStyleStrategy();
        //根据指定表头写出文件
            EasyExcel.write(response.getOutputStream()).registerWriteHandler(horizontalCellStyleStrategy).head(head(headList)).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(longest).doWrite(lists);
    }


    /** 根据指定模型表头生成表格
     * @param dataList  表格数据
     * @param fileName  文件名
     * @param sheetName 表名
     * @param clazz  表头模型文件  参考 ExcelGoods模型
     * @param response  响应
     * @throws IOException
     */
    public static void DownloadExcel(List<ExcelGoods> dataList, String fileName, String sheetName, Class clazz, HttpServletResponse response) throws IOException {



        //设置返回头
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + DateUtil.getTimeByNow()+".xlsx");

        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置策略
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        //根据指定模型写出文件
        EasyExcel.write(response.getOutputStream(),clazz).registerWriteHandler(horizontalCellStyleStrategy).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).doWrite(dataList);


    }


    /**
     * 表格表头标题
     *
     * @param headList
     * @return
     */
    public static List<List<String>> head(List<String> headList) {
        List<List<String>> list = new ArrayList<>();
        headList.forEach(str -> {
            List<String> head = new ArrayList<>();
            head.add(str);
            list.add(head);
        });
        return list;
    }

  /**
     * 读取上传表格
     *
     * @param file
     * @throws IOException
     */
    @Override
    public String uploadGoodsExcel(MultipartFile file) throws IOException {
        //获取文字信息
        ArrayList<Map<String, Object>> list = new ArrayList<>();
        EasyExcel.read(file.getInputStream(), ExcelGoodsTemplate.class, new PageReadListener<ExcelGoodsTemplate>(dataList -> {
            for (ExcelGoodsTemplate demoData : dataList) {
                String s = JSON.toJSONString(demoData);
                String jsons = JSON.parseObject(s).toJSONString();
                LinkedHashMap<String, Object> map = JSON.parseObject(jsons, new TypeReference<LinkedHashMap<String, Object>>() {
                });
                if (map.get("id") == null) {
                    return;
                }
                list.add(map);
            }
        })).sheet().doRead();

        ArrayList<Object> reList = new ArrayList<>();
        Integer success = 0;
        Integer error = 0;
        //遍历存储
        for (int i = 0; i < list.size(); i++) {
            try {
                int save = save(list.get(i));
                if (save == 0) {
                    throw new NullPointerException();
                }
                success = success + save;
            } catch (Exception e) {
                error++;
                reList.add(i + 2);
            }
        }
        String str = "本次操作成功" + success + "行数据" + ",失败" + error + "行数据!";
        if (error > 0) {
            str = str + "第" + reList + "行数据错误,请检查!";
        }
        return str;
    }

 /**
     * 下载模板
     *
     * @param response
     */
    @Override
    public void downloadExcelTemplate(HttpServletResponse response) throws IOException, BiffException {
        OutputStream outputStream = response.getOutputStream();
        //设置返回头
        String fileName = URLEncoder.encode("商品模板", "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + DateUtil.getTimeByNow() + ".xlsx");
        //读取模板文件
        File templateFile = new File(filePath + "xlsx\\商品模板.xlsx");

        ExcelWriter excelWriter = null;
        //头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//        contentWriteCellStyle.setShrinkToFit(true); //自动调整字体大小适应表格宽度

        //设置单元格策略
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        //设置列宽策略
        LongestMatchColumnWidthStyleStrategy longest = new LongestMatchColumnWidthStyleStrategy();

        excelWriter = EasyExcel.write(outputStream).registerWriteHandler(horizontalCellStyleStrategy).withTemplate(templateFile).autoCloseStream(false).registerWriteHandler(longest).build();


        //写入供应商
        List<ExcelSelect> supplierList = tempList(publicService.find("supplier", new ArrayList<>()), "name", CommonConstants.TABLE_ID);

        //写入商品类别
        List<ExcelSelect> typeList = tempList(publicService.find("type", new ArrayList<>()), "name", CommonConstants.TABLE_ID);
        //写入计量单位
        List<ExcelSelect> unitList = tempList(publicService.find("SYS_DICTIONARY_DETAIL", new ArrayList<>()), "label", CommonConstants.TABLE_ID);

        //写入品牌
        List<ExcelSelect> brandManagementList = tempList(publicService.find("brandManagement", new ArrayList<>()), "name", CommonConstants.TABLE_ID);

        //写入属性
        List<ExcelSelect> commodityAttributeList = tempList(publicService.find("commodityAttribute", new ArrayList<>()), "name", CommonConstants.TABLE_ID);


        excelWriter.write(supplierList, EasyExcel.writerSheet("供应商").build()).
                write(typeList, EasyExcel.writerSheet("商品类别").build()).
                write(unitList, EasyExcel.writerSheet("计量单位").build()).
                write(brandManagementList, EasyExcel.writerSheet("品牌").build()).
                write(commodityAttributeList, EasyExcel.writerSheet("属性").build()).finish();


    }