Hutool excel 设置单元格格式为文本

曲美丽

已于 2022-08-16 09:11:12 修改

阅读量4.7k

点赞数 2

分类专栏： hutool 文章标签： java 前端 html

于 2022-06-15 17:01:11 首次发布

hutool 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

导出excel

hutool-excel-并设置单元格格式为文本

public static void exportExcel(HttpServletResponse response, List<?> list, Map<String, String> headerAlias, String reportName) {

        if ((reportName == null) && (list == null) && (headerAlias == null)) {
            throw new RuntimeException("输入参数不对");
        }
        //通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        //设置sheet的名称
        writer.renameSheet(reportName);
        //自定义标题别名
        headerAlias.forEach(
                (k, v) -> {
                    writer.addHeaderAlias(k, v);
                }
        );
        //一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);

        //获取整个Excel的样式，设置单元格格式为文本
        StyleSet styleSet = writer.getStyleSet();
        CellStyle cellStyle = styleSet.getCellStyleForNumber();
        DataFormat format = writer.getWorkbook().createDataFormat();
        cellStyle.setDataFormat(format.getFormat("@"));
        writer.setStyleSet(styleSet);

        //设置所有列为自动宽度，不考虑合并单元格 此方法必须在指定列数据完全写出后调用才有效。
        //writer.autoSizeColumnAll();
        setSizeColumn(writer.getSheet(), 17);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(reportName.getBytes("gb2312"), "ISO8859-1") + ".xls");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        ServletOutputStream out = null;
        getServletOutputStream(response, writer, out);
    }

private static void getServletOutputStream(HttpServletResponse response, ExcelWriter writer, ServletOutputStream out) {
        try {
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭writer，释放内存
            writer.close();
            //此处记得关闭输出Servlet流
            IoUtil.close(out);
        }
    }

以下为业务代码：

@ApiOperation(value = "导出Excel")
    @GetMapping("/export2")
    public void exportExcel(HttpServletResponse response, @RequestParam String outputbillid){
        projectDeliveryDetailCmdService.exportExcel(response, outputbillid);
    }

/**
     * 获取数据，生成excel
     * @param outputBillId 出库主单ID
     * @author quxingtao
     * @Date 2022-06-30 15:28:05
     * @Version 1.0
     * */
    public void exportExcel(HttpServletResponse response, String outputBillId){
		//sheet页的名称
        String reportName = "子库转移明细";
        
        //列名
        String[] colName = {"X1-序号","ISMODELPRODUCTSTR-配置类型", "MATERIALCODE-物料编码", "MATERIALNAME-物料名称", "PROVIDERPRODUCTNAME-供应商产品名称", "MEASURENAME-计量单位", "APPLYAMOUNT-申请数量", "TOTALMOUNT-总数量", "PRICE-单价", "MONEY-金额",
                "BATCH-批次", "WAREHOUSELOCATIONFULLNAME-货位", "BOXNUMBER-箱件号", "PROVIDERNAME-供应商", "PROJECTCODE-项目编号", "PROJECTNAME-项目名称", "IMPORTPROJECTCODE-目标项目编号", "IMPORTPROJECTNAME-目标项目名称", "ENTITYDESC-备注"};
		
		//这四个为数字
        String[] numCol = {"APPLYAMOUNT", "TOTALMOUNT", "PRICE", "MONEY"};
		//查询数据库，获取实体数据
        List<Map<String, Object>> list = this.findExcelList(outputBillId);
        
        int num = 1;
        for (Map<String, Object> map:list){
            //数字为null则设置为0
            for (int i = 0; i < numCol.length; i++){
                if (map.get(numCol[i]) == null){
                    map.put(numCol[i],"0");
                }
            }
            //手写的excel中的序号
            map.put("X1",num+"");
            num++;
			//业务需求，库中字段进行翻译
            if (map.get("ISMODELPRODUCTSTR") != null) {
                String ismodelproductstr = map.get("ISMODELPRODUCTSTR").toString();
                if ("1".equals(ismodelproductstr)) {
                    map.put("ISMODELPRODUCTSTR","动态BOM");
                } else if ("2".equals(ismodelproductstr)) {
                    map.put("ISMODELPRODUCTSTR","静态BOM");
                } else if ("3".equals(ismodelproductstr)) {
                    map.put("ISMODELPRODUCTSTR","虚拟BOM");
                } else if ("0".equals(ismodelproductstr)) {
                    map.put("ISMODELPRODUCTSTR","单一产品");
                } else if ("4".equals(ismodelproductstr)) {
                    map.put("ISMODELPRODUCTSTR","配置清单");
                } else if ("5".equals(ismodelproductstr)) {
                    map.put("ISMODELPRODUCTSTR","二级辅材");
                }
            } else {
                map.put("ISMODELPRODUCTSTR","单一产品");
            }
        }
        //设置列名
        Map<String, String> head = new LinkedHashMap<>();
        for (int i =0; i < colName.length ;i++){
            head.put(colName[i].split("-")[0], colName[i].split("-")[1]);
        }
        ExcelUtils.exportExcel(response, list, head, reportName);
    }

//查询数据库，利用jpa。生成list<Map<string,string>>形式
/**
     * 获取数据，生成excel
     * @param outputBillId 出库主单ID
     * @author quxingtao
     * @Date 2022-06-30 15:28:05
     * @Version 1.0
     * */
    public List<Map<String, Object>> findExcelList(String outputBillId){
        StringBuffer sql = new ExportExcelViewSql().LOCATION_SHIFT_EXCEL_VIEW;
        sql.append(" where nvl(o.parent_output_detail_id,0) = 0 and o.output_bill_id = ").append(outputBillId);
        sql.append(" order by o.id ");
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.getResultList();
    }

在这里插入图片描述

原生poi写法

public ReturnResult<String> exportExcel(HttpServletResponse response, String masterId) throws IOException {
        ServletOutputStream out = response.getOutputStream();
        //获取模板
        ReturnResult<String> returnResult = plisFeignClient.getFileId("downLoad", "SHIP_DELIVERNOTICE");
        if (returnResult.getCode() != ReturnResultCode.SUCCESS || returnResult.getData() == null || returnResult.getData() == "") {
            throw new StockParseException("获取附件id失败:" + returnResult.getMessage());
        }
        String fileId = returnResult.getData();
        Response responseInventory = plisFeignClient.download(fileId, "bpc-web");
        if (responseInventory.status() == 404) {
            throw new StockParseException("调用下载文件接口失败:404");
        }
        if (responseInventory == null) {
            throw new StockParseException("模板下载失败");
        }
        //获取模板信息
        Map<String, Collection<String>> headers = responseInventory.headers();
        Collection<String> collection = headers.get("content-disposition");
        if (collection == null) {
            throw new StockParseException("模板下载失败");
        }
        String coll = collection.toArray()[0].toString();
        Response.Body body = responseInventory.body();
        //获取文件名
        String filleNameDec = coll.split(";")[1].split("=")[1];
        InputStream inputStream = null;
        String fileName = "";
        try {
            fileName = URLDecoder.decode(filleNameDec, "UTF-8");
            inputStream = body.asInputStream();
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (inputStream == null) {
            throw new StockParseException("获取模板失败:空文件");
        }

        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls")) {
                //2003
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith("xlsx")) {
                //2007 及2007以上
                workbook = new XSSFWorkbook(inputStream);
            }
            workbook = parseExcel(workbook, masterId);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            String[] fileNames = filleNameDec.split("\\.");
            response.setHeader("Content-Disposition", "attachment;fileName=" + new String(fileNames[0].getBytes("gb2312"), "ISO8859-1") + ".xls");
            workbook.write(out);
            out.flush();
            //response.getOutputStream().flush();
        } catch (Exception e) {
            return ReturnResult.error(e.getMessage());
        } finally {
            inputStream.close();
            workbook.close();
            out.close();
        }
        return ReturnResult.success("success");
    }

private Workbook parseExcel(Workbook workbook, String masterId){
        Sheet sheet = workbook.getSheet("签收装箱清单");
        List<PackInfoDetail> packDetailList = findPackDetailByPage4Excel(masterId);

        int rows = 2;
        for (int i = 0; i < packDetailList.size(); i++) {
            PackInfoDetail scmPackDetail = packDetailList.get(i);
            rows++;
            //在模板的第三行开始创建数据，
            Row row = sheet.createRow(rows);
            for (int j = 0; j < 40; j++) {
                row.createCell(j);
            }

            sheet.getRow(rows).getCell(0).setCellValue(String.valueOf(i + 1));
            sheet.getRow(rows).getCell(1).setCellValue(scmPackDetail.getId());
            sheet.getRow(rows).getCell(2).setCellValue(scmPackDetail.getOrderDetailId() == null ? "" : scmPackDetail.getOrderDetailId());
            sheet.getRow(rows).getCell(3).setCellValue(scmPackDetail.getIsModelProduct() == null ? "" : getModelProductName(scmPackDetail.getIsModelProduct()));
            sheet.getRow(rows).getCell(4).setCellValue(scmPackDetail.getBomMark() == null ? "" : scmPackDetail.getBomMark());

            String bom2Name = "";
            if ("5".equals(scmPackDetail.getIsModelProduct())) {
                bom2Name = scmPackDetail.getBom2Name() == null ? "" : scmPackDetail.getBom2Name();
            }
            sheet.getRow(rows).getCell(5).setCellValue(bom2Name);

            PackInfo packInfo = null;
            if (scmPackDetail.getPackInfoId() != null) {
                Optional<PackInfo> byId = packInfoDao.findById(scmPackDetail.getPackInfoId());
                packInfo = byId.orElse(null);
            }
            sheet.getRow(rows).getCell(6).setCellValue(packInfo == null ? "" : packInfo.getBoxNumber());
            sheet.getRow(rows).getCell(7).setCellValue(packInfo == null ? "" : packInfo.getProductName());

            sheet.getRow(rows).getCell(8).setCellValue(scmPackDetail.getProjectId() == null ? "" : scmPackDetail.getProjectCode());
            sheet.getRow(rows).getCell(9).setCellValue(scmPackDetail.getProjectId() == null ? "" : scmPackDetail.getProjectCode());
            sheet.getRow(rows).getCell(10).setCellValue(scmPackDetail.getSiteCode() == null ? "" : scmPackDetail.getSiteCode());
            sheet.getRow(rows).getCell(11).setCellValue(scmPackDetail.getSiteName() == null ? "" : scmPackDetail.getSiteName());
            sheet.getRow(rows).getCell(12).setCellValue(scmPackDetail.getBuyProductName());

            String productMaterialId = scmPackDetail.getProductMaterialId();
            String materialCode = "";
            String materialName = "";
            if(StringUtils.isNotEmpty(productMaterialId)) {
                ReturnResult<Material> materialById = materialFeignClient.getMaterialById(productMaterialId);
                if (materialById.getCode()==ReturnCodeEnum.SUCCESS.getCode()){
                    materialCode = materialById.getData().getEntityCode();
                    materialName = materialById.getData().getEntityName();
                }
            }
            sheet.getRow(rows).getCell(13).setCellValue(materialCode);
            sheet.getRow(rows).getCell(14).setCellValue(materialName);

            sheet.getRow(rows).getCell(15).setCellValue(scmPackDetail.getMaterialPropertyId() == null ? "" : scmPackDetail.getMaterialPropertyId());

            String strIsMain = "";
            if (StringUtils.isNotEmpty(scmPackDetail.getIsMainProduct()) && "1".equals(scmPackDetail.getIsMainProduct())) {
                strIsMain = "是";
            } else if (StringUtils.isNotEmpty(scmPackDetail.getIsMainProduct()) && "2".equals(scmPackDetail.getIsMainProduct())) {
                strIsMain = "BOM产品";
            } else {
                strIsMain = "否";
            }
            sheet.getRow(rows).getCell(16).setCellValue(strIsMain);

            sheet.getRow(rows).getCell(17).setCellValue(scmPackDetail.getMeasureName());

            Double amountBom = scmPackDetail.getCountRelation() == null ? new Double(0.0D) : scmPackDetail.getCountRelation().doubleValue();
            sheet.getRow(rows).getCell(18).setCellValue(amountBom);

            Double amount = scmPackDetail.getAmount() == null ? new Double(0.0D) : Double.valueOf(scmPackDetail.getAmount());
            sheet.getRow(rows).getCell(19).setCellValue(amount);

            Double price = scmPackDetail.getPrice() == null ? new Double(0.0D) : Double.valueOf(scmPackDetail.getPrice());
            sheet.getRow(rows).getCell(20).setCellValue(price);

            Double money = scmPackDetail.getMoney() == null ? new Double(0.0D) : Double.valueOf(scmPackDetail.getMoney());
            sheet.getRow(rows).getCell(21).setCellValue(money);

            sheet.getRow(rows).getCell(22).setCellValue(scmPackDetail.getBuyApplyCode() == null ? "" : scmPackDetail.getBuyApplyCode());
            sheet.getRow(rows).getCell(23).setCellValue(scmPackDetail.getProviderId() == null ? "" : fingProviderById(scmPackDetail.getProviderId()));

            if (scmPackDetail.getProductionDate() == null) {
                sheet.getRow(rows).getCell(24).setCellValue("");
            } else {
                sheet.getRow(rows).getCell(24).setCellValue(scmPackDetail.getProductionDate().toString());
            }

            sheet.getRow(rows).getCell(25).setCellValue(scmPackDetail.getProviderProductCode());
            sheet.getRow(rows).getCell(26).setCellValue(scmPackDetail.getProviderProductName());
            sheet.getRow(rows).getCell(27).setCellValue(scmPackDetail.getDescription());

            if(packInfo !=null && packInfo.getLength() != null){
                sheet.getRow(rows).getCell(28).setCellValue(Double.valueOf(packInfo.getLength()));
            } else {
                sheet.getRow(rows).getCell(28).setCellValue("");
            }

            if(packInfo !=null && packInfo.getWidth() != null){
                sheet.getRow(rows).getCell(29).setCellValue(Double.valueOf(packInfo.getWidth()));
            } else {
                sheet.getRow(rows).getCell(29).setCellValue("");
            }

            if(packInfo !=null && packInfo.getHeight() != null){
                sheet.getRow(rows).getCell(30).setCellValue(Double.valueOf(packInfo.getHeight()));
            } else {
                sheet.getRow(rows).getCell(30).setCellValue("");
            }

            if(packInfo !=null && packInfo.getVolume() != null){
                sheet.getRow(rows).getCell(31).setCellValue(Double.valueOf(packInfo.getVolume()));
            } else {
                sheet.getRow(rows).getCell(31).setCellValue("");
            }

            if(packInfo !=null && packInfo.getGrossWeight() != null){
                sheet.getRow(rows).getCell(32).setCellValue(Double.valueOf(packInfo.getGrossWeight()));
            } else {
                sheet.getRow(rows).getCell(32).setCellValue("");
            }

            sheet.getRow(rows).getCell(33).setCellValue(packInfo == null ? "" : packInfo.getProductMeasureName());

            if(packInfo != null && packInfo.getProductAmount() != null){
                sheet.getRow(rows).getCell(34).setCellValue(Double.valueOf(packInfo.getProductAmount().toString()));
            } else {
                sheet.getRow(rows).getCell(34).setCellValue("");
            }

            //DeliverNoticeServiceImpl 879
            if(packInfo != null && packInfo.getBoxAmount() != null){
                sheet.getRow(rows).getCell(35).setCellValue(Double.valueOf(packInfo.getBoxAmount()));
            } else {
                sheet.getRow(rows).getCell(34).setCellValue("");
            }

            if(packInfo != null && packInfo.getProductTotalAmount() != null){
                sheet.getRow(rows).getCell(36).setCellValue(Double.valueOf(packInfo.getProductTotalAmount()));
            } else {
                sheet.getRow(rows).getCell(36).setCellValue("");
            }

            if(packInfo != null && packInfo.getProductTotalWeight() != null){
                sheet.getRow(rows).getCell(37).setCellValue(Double.valueOf(packInfo.getProductTotalWeight()));
            } else {
                sheet.getRow(rows).getCell(37).setCellValue("");
            }

            if(packInfo != null && packInfo.getProductTotalVolumn() != null){
                sheet.getRow(rows).getCell(38).setCellValue(Double.valueOf(packInfo.getProductTotalVolumn()));
            } else {
                sheet.getRow(rows).getCell(38).setCellValue("");
            }

            sheet.getRow(rows).getCell(39).setCellValue(packInfo == null ? "" : packInfo.getDescription());
            //sheet.setRowView(rows, 350);
        }

        return workbook;
    }

导入excel

@ApiOperation(value = "期初导入")
    @PostMapping("/returnImportExcels/{inputBillId}")
    public ReturnResult<String> returnImportExcels(@RequestParam("file") MultipartFile file, @PathVariable String inputBillId){
        return inputBillExcelImportService.returnImportExcels(file, inputBillId);
    }

public ReturnResult<String> returnImportExcels(MultipartFile file, String inputBillId){
        Workbook workbook = ParseExcelUtil.getWorkBook(file);

        //判断入库主单是否存在
        Optional<InputBill> byId = inputStorageDao.findById(inputBillId);
        InputBill inputBill = byId.orElseThrow(() -> new StockParseException("未找到该主单"));
        //保存数据字典基础信息
        return ReturnResult.success(parseExcel(workbook, inputBill));
    }

public String parseExcel(Workbook workbook, InputBill inputBill) {
        boolean isERP = true;
        List<InputBillDetail> resultDataList = new LinkedList();
        // 解析sheet
        Sheet sheet = workbook.getSheetAt(0);
        // 获取第一行数据
        int firstRowNum = sheet.getFirstRowNum();
        Row firstRow = sheet.getRow(firstRowNum);
        if (null == firstRow) {
            log.error("解析Excel失败，在第一行没有读取到任何数据！");
            throw new StockParseException("模板异常");
        }

        // 解析每一行的数据，构造数据对象 从excel第三行开始拿数据
        int rowStart = firstRowNum + 3;
        int rowEnd = sheet.getLastRowNum();//获取的最后一行为什么是20

        for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {
            Row row = sheet.getRow(rowNum);

            if (null == row) {
                continue;
            }

            if (ParseExcelUtil.isRowEmpty(row)) {
                continue;
            }
            InputBillDetail inputBillDetail = convertRowToData(row, inputBill);
            if (null == inputBillDetail) {
                log.error("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                continue;
            }else if (isERP == true){
                resultDataList.add(inputBillDetail);
            }
        }
        inputStorageDao.save(inputBill);
        Iterator<InputBillDetail> iterator = resultDataList.iterator();
        while (iterator.hasNext()){
            InputBillDetail next = iterator.next();
            inputStorageDetailDao.save(next);
        }
        return "success";
    }

private InputBillDetail convertRowToData(Row row, InputBill inputBill) {
        InputBillDetail inputBillDetail = new InputBillDetail();
        //SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        int rowNum = row.getRowNum() + 1;

        Cell cell;
        //每行有17列
        for (int i = 1; i <= 17; i++) {
            String cellValue = ParseExcelUtil.getCellValue(row.getCell(i));
            if (StrUtil.containsBlank(cellValue)) {
                throw new StockParseException("第 " + rowNum + "行第" + (i + 1) + "列数据不合法(不能包含空格)");
            }
        }

        //第0列是序号 从1开始
        int cellNum = 1;

        //库存组织编码 --B
        cell = row.getCell(cellNum++);
        String warehouseNum = ParseExcelUtil.getCellValue(cell);
        //仓库
        String inputWarehouseId = inputBill.getInputWarehouseId();
        Optional<Warehouse> byId = warehouseDao.findById(inputWarehouseId);
        Warehouse warehouse = byId.orElseThrow(() -> new StockParseException("未查询到仓库"));

        //是否主配置 --G
        cell = row.getCell(cellNum++);
        String isMainProduct = ParseExcelUtil.getCellValue(cell);
        if (!"是".equals(isMainProduct) && !"否".equals(isMainProduct)) {
            throw new StockParseException("第 " + rowNum + "行的“是否主配置”只能填写：“是”或“否” ！");
        }
        inputBillDetail.setIsMainProduct("是".equals(isMainProduct) ? "1" : "0");

        //金额 --K 
        cell = row.getCell(cellNum++);
        System.out.println("==================cell2："+cell.toString()+","+cell.getCellTypeEnum());
        //获取小数自动为四舍五入，设置单元格格式为文本
        cell.setCellType(CellType.STRING);
        String money = ParseExcelUtil.getCellValue(cell);
        System.out.println("==========金额：" + money);
        inputBillDetail.setMoney(money);
        return inputBillDetail;
    }

1.字体样式常用方法

   /**
     * 方法描述: 设置基础字体样式字体 这里保留最基础的样式使用
     *
     * @param workbook 工作簿
     * @param bold     是否粗体
     * @param fontName 字体名称
     * @param fontSize 字体大小
     * @return org.apache.poi.ss.usermodel.Font
     * @author wqf
     * @date 2021/5/19 15:58
     */
    public static Font setBaseFont(Workbook workbook, boolean bold, boolean italic, String fontName, int fontSize) {
        Font font = workbook.createFont();
        //设置字体名称 宋体 / 微软雅黑 /等
        font.setFontName(fontName);
        //设置是否斜体
        font.setItalic(italic);
        //设置字体高度
        //font.setFontHeight((short) fontHeight);
        //设置字体大小 以磅为单位
        font.setFontHeightInPoints((short) fontSize);
        //设置是否加粗
        font.setBold(bold);
        //默认字体颜色
        // font.setColor(Font.COLOR_NORMAL);
        //红色
        //font.setColor(Font.COLOR_RED);
        //设置下划线样式
        //font.setUnderline(Font.ANSI_CHARSET);
        //设定文字删除线
        //font.setStrikeout(true);
        return font;
    }
全局样式设置

 private static StyleSet GlobalStyleSet(ExcelWriter writer, Workbook workbook,Font font) {
        //全局样式设置
        StyleSet styleSet = writer.getStyleSet();
        CellStyle cellStyle = styleSet.getCellStyle();
        //设置全局文本居中
        styleSet.setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
        //设置全局字体样式
        styleSet.setFont(font);
        //设置背景颜色 第二个参数表示是否将样式应用到头部
        styleSet.setBackgroundColor(IndexedColors.WHITE, true);
        //设置自动换行 当文本长于单元格宽度是否换行
        styleSet.setWrapText();
        // 设置全局边框样式
        styleSet.setBorder(BorderStyle.THIN, IndexedColors.BLACK);
        return styleSet;
    }
头部标题样式

//设置全局样式
StyleSet styleSet = GlobalStyleSet(writer, workbook);
//设置头部标题样式
CellStyle headCellStyle = styleSet.getHeadCellStyle();
//水平居中
headCellStyle.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置字体样式
headCellStyle.setFont(setBaseFont(workbook, true, false, "宋体", 12));
writer.setStyleSet(styleSet);
数字保留小数
例：保留两位小数

CellStyle cellStyleForNumber = styleSet.getCellStyleForNumber();
cellStyleForNumber.setDataFormat((short)2);
5.时间格式化
例如格式为：YYYY/MM/dd 格式


CellStyle cellStyleForDate = styleSet.getCellStyleForDate();
//14 代表的时间格式是 yyyy/MM/dd
cellStyleForDate.setDataFormat((short)14);
时间格式占时只看到这一种格式常见些，像yyyy-MM-dd 格式都没找到，就只有在写入数据写先处理下时间格式了。

行（Row）样式

//获取输出构造器 设置工作簿名称
ExcelWriter writer = ExcelUtil.getWriterWithSheet(sheetName);
Workbook workbook = writer.getWorkbook();
Sheet sheet = writer.getSheet();
Row row = sheet.getRow(rowIndex);
 if(sheet.getRow(rowIndex )==null){
      //rowIndex 表示的是第几行，例：创建第二行，rowIndex=1
      sheet.createRow(rowIndex );
  }
//创建样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle .setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle .setAlignment(HorizontalAlignment.LEFT);
cellStyle .setFont(setBaseFont(workbook, true, false, "宋体", 12));
cellStyle .setBorderBottom(BorderStyle.THIN);
cellStyle .setBorderLeft(BorderStyle.THIN);
cellStyle .setBorderRight(BorderStyle.THIN);
cellStyle .setBorderTop(BorderStyle.THIN);
//应用样式到某一行（
row .setRowStyle(cellStyle );
//应用样式到某一行 （或者这样写） rowIndex  表示的是第几行
//writer.setRowStyle(rowIndex ,cellStyle );
单元格（Cell）样式

Row row = sheet.getRow(rowIndex);
if(sheet.getRow(rowIndex )==null){
      //rowIndex 表示的是第几行，例：创建第二行，rowIndex=1
      sheet.createRow(rowIndex );
  }
//创建本行的第几个单元格  cellIndex=0 表示第一个
if(row.get(cellIndex)==null){
    row .createCell(cellIndex)；
}

//创建样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle .setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle .setAlignment(HorizontalAlignment.LEFT);
cellStyle .setFont(setBaseFont(workbook, true, false, "宋体", 12));
cellStyle .setBorderBottom(BorderStyle.THIN);
cellStyle .setBorderLeft(BorderStyle.THIN);
cellStyle .setBorderRight(BorderStyle.THIN);
cellStyle .setBorderTop(BorderStyle.THIN);
//应用样式到某一行（
row .setRowStyle(cellStyle );
//应用样式到某一行 （或者这样写） rowIndex  表示的是第几行
//writer.setRowStyle(rowIndex ,cellStyle );
合并单元格
//处理标题行 合并某行的单元格，并写入对象到单元格,如果写到单元格中的内容非null，行号自动+1，否则当前行号不变

//主要有两种方式
1. writer.merge(cellIndex, content, true);  
表示当前行 合并从第一个单元到cellIndex+1个单元，并填充内容content，第三个参数表示是否将头部标题样式应用到这里。
或者
2.writer.merge(startRowIndex,endRowIndex, startCellIndex, endCellIndex, content, false);
表示和并第startRowIndex+1行到endRowIndex+1行 ，并合并从第endCellIndex+1个单元到endCellIndex+1个单元格，并填充content内容，最后一个字段表示是否将头部标题样式应用到这里。
列表别名

//LinkedHashMap 中的数据是根据put先后顺序来的，HashMap数据时无序的。
//使用方法 writer.setHeaderAlias(headerAlias); 时如果使用HashMap 可能展示的数
//据顺序会错乱
Map<String, String> headerAlias = new LinkedHashMap<>();
headerAlias.put(字段名1, 列名1);
headerAlias.put(字段名2, 列名2);
headerAlias.put(字段名3, 列名3);
headerAlias.put(字段名4, 列名4);
headerAlias.put(字段名5, 列名5);
writer.setHeaderAlias(headerAlias);
//或者一项一项设置列的别名 列别名顺序会跟代码中addHeaderAlias顺序一致
writer.addHeaderAlias(字段名1,列名1);
writer.addHeaderAlias(字段名2,列名2);
列宽问题
8.1 自动列宽 百度查到一个方法有用 ，但实际好像还是会有点问题，大家可以先试试
博客链接 ：https://blog.csdn.net/kongbai953/article/details/110382544

/**
     * 自适应宽度(中文支持)
     * @param sheet
     * @param size 因为for循环从0开始，size值为 列数-1
     */
    public static void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum <= size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }
8.2 手动列宽设置


//表示 第一列的列宽是15
 writer.setColumnWidth(0, 15);
另外常用方法
//跳过当前行 即当前行不写内容


writer.passCurrentRow();
//定位到最后一行，常用于在末尾追加数据


writer.setCurrentRowToEnd();
10 . 下载excel代码 在数据都填充完成后，调用该方法即可


 /**
     * 方法描述: 下载excel文件
     *
     * @param response 响应
     * @param fileName 文件名称
     * @param writer   writer
     * @return void
     * @author wqf
     * @date 2021/5/24 16:20
     */
    private static void downloadExcel(HttpServletResponse response, String fileName, ExcelWriter writer) {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        ServletOutputStream out = null;
        try {
            // 设置请求头属性
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), StandardCharsets.ISO_8859_1));
            out = response.getOutputStream();
            // 写出到文件
            writer.flush(out, true);
            // 关闭writer，释放内存
            writer.close();
            // 此处记得关闭输出Servlet流
            IoUtil.close(out);
        } catch (IOException e) {
            log.error(e.getMessage());
            e.printStackTrace();
        }
    }
excel 添加下拉框

CellRangeAddressList addressList = new CellRangeAddressList(2, 2, 5, 5);
DataValidationHelper helper = sheet.getDataValidationHelper();
 // 设置下拉框数据
String[] str = new String[]{"男", "女","阴阳人"};
DataValidationConstraint constraint = helper.createExplicitListConstraint(str);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
writer.addValidationData(dataValidation);
12.背景色填充


//示例：将单元格背景填充为黄色
short index = IndexedColors.YELLOW.index;
cellStyle.setFillForegroundColor(index);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
总结 ： 整合了一个工具类，仅作参考


/**
 * @Author: wqf
 * @Date: 2021/05/28
 * @Description: hutool 工具导出excel(非填充模板，手工画)
 */
@Slf4j
public class HutoolExcelUtil {
    /**
     * YYYY/MM/dd 时间格式
     */
    private static final short LOCAL_DATE_FORMAT_SLASH = 14;

    /**
     * 方法描述: 创建excel
     *
     * @param isXlsx excel文件类型 true-xlsx/false-xls
     * @return cn.hutool.poi.excel.ExcelWriter
     * @author wqf
     * @date 2021/6/1 9:47
     */
    public static ExcelWriter createExcel(boolean isXlsx) {
        return ExcelUtil.getWriter(isXlsx);
    }

    /**
     * 方法描述: 全局基础样式设置
     * 默认 全局水平居中+垂直居中
     * 默认 自动换行
     * 默认单元格边框颜色为黑色，细线条
     * 默认背景颜色为白色
     *
     * @param writer writer
     * @param font   字体样式
     * @return cn.hutool.poi.excel.StyleSet
     * @author wqf
     * @date 2021/5/28 10:43
     */
    public static StyleSet setBaseGlobalStyle(ExcelWriter writer, Font font) {
        //全局样式设置
        StyleSet styleSet = writer.getStyleSet();
        //设置全局文本居中
        styleSet.setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
        //设置全局字体样式
        styleSet.setFont(font, true);
        //设置背景颜色 第二个参数表示是否将样式应用到头部
        styleSet.setBackgroundColor(IndexedColors.WHITE, true);
        //设置自动换行 当文本长于单元格宽度是否换行
        //styleSet.setWrapText();
        // 设置全局边框样式
        styleSet.setBorder(BorderStyle.THIN, IndexedColors.BLACK);
        return styleSet;
    }

    /**
     * 方法描述: 设置标题的基础样式
     *
     * @param styleSet            StyleSet
     * @param font                字体样式
     * @param horizontalAlignment 水平排列方式
     * @param verticalAlignment   垂直排列方式
     * @return org.apache.poi.ss.usermodel.CellStyle
     * @author wqf
     * @date 2021/5/28 10:16
     */
    public static CellStyle createHeadCellStyle(StyleSet styleSet, Font font,
                                                HorizontalAlignment horizontalAlignment,
                                                VerticalAlignment verticalAlignment) {
        CellStyle headCellStyle = styleSet.getHeadCellStyle();
        headCellStyle.setAlignment(horizontalAlignment);
        headCellStyle.setVerticalAlignment(verticalAlignment);
        headCellStyle.setFont(font);
        return headCellStyle;
    }

    /**
     * 方法描述: 设置基础字体样式字体 这里保留最基础的样式使用
     *
     * @param bold     是否粗体
     * @param fontName 字体名称
     * @param fontSize 字体大小
     * @return org.apache.poi.ss.usermodel.Font
     * @author wqf
     * @date 2021/5/19 15:58
     */
    public static Font createFont(ExcelWriter writer, boolean bold, boolean italic, String fontName, int fontSize) {
        Font font = writer.getWorkbook().createFont();
        //设置字体名称 宋体 / 微软雅黑 /等
        font.setFontName(fontName);
        //设置是否斜体
        font.setItalic(italic);
        //设置字体大小 以磅为单位
        font.setFontHeightInPoints((short) fontSize);
        //设置是否加粗
        font.setBold(bold);
        return font;
    }

    /**
     * 方法描述: 设置行或单元格基本样式
     *
     * @param writer              writer
     * @param font                字体样式
     * @param verticalAlignment   垂直居中
     * @param horizontalAlignment 水平居中
     * @return void
     * @author wqf
     * @date 2021/5/28 10:28
     */
    public static CellStyle createCellStyle(ExcelWriter writer, Font font, boolean wrapText,
                                            VerticalAlignment verticalAlignment,
                                            HorizontalAlignment horizontalAlignment) {
        CellStyle cellStyle = writer.getWorkbook().createCellStyle();
        cellStyle.setVerticalAlignment(verticalAlignment);
        cellStyle.setAlignment(horizontalAlignment);
        cellStyle.setWrapText(wrapText);
        cellStyle.setFont(font);
        return cellStyle;