poi 3.9 导出设置指定单元格类型为数值

记录一下起因

导出Excel文件时,已经设置好了数据的格式,导出后发现为文本格式,且手动改变不了其单元格类型

我试过后总结的必要因素

1.导出实例为数值类型(包含对应的包装类,和BigDecimal)；
2. 模板事先设置好为常规；

部分代码样式

实现类-----多数均为自定义类型,参考即可

 public String outExcel(String pcrqA, String pcrqB, HttpServletResponse response) {
        try {
                String filename = "****.xls";
                Map<String, Object> map = new HashMap<String, Object>();
                List<SiWuHuiBao> list = siWuReportingMapper.outExcel(pcrqA,pcrqB);
                if(list.size()>0) {
                    AtomicInteger m = new AtomicInteger(1);
                    for (SiWuHuiBao siWuHuiBao : list) {
                        if (siWuHuiBao.getRIQI() != null) {
                            Calendar calender1 = Calendar.getInstance();
                            calender1.setTime(siWuHuiBao.getRIQI());
                            siWuHuiBao.setYUE(String.valueOf(calender1.get(Calendar.MONTH)+1));
                            siWuHuiBao.setRI(String.valueOf(calender1.get(Calendar.DATE)));
                        }
                    }
                    map.put("entitylist" + m , list);
                    m.getAndIncrement();
                }
                String[] strings = new String[1];
                //关键是这一步
                return ExcelTemplateUtil.excelTemplate(response, map, "template/****.xls", strings,filename);
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }

    }

package com.***.uitl;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.***.common.utils.StringUtils;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.HashMap;
import java.util.Map;

public class ExcelTemplateUtil {



    public static String excelTemplate(HttpServletResponse response, Map<String, Object> map, String templateUrl, String[] sheetName, String filename) throws IOException {
        Integer[] integers = new Integer[sheetName.length];
        for(int i =0;i<sheetName.length;i++){
            integers[i] = i;
        }
        TemplateExportParams params = new TemplateExportParams(
                templateUrl,integers);
        params.setSheetName(sheetName);
        OutputStream outputStream = new FileOutputStream(getAbsoluteFile(filename));
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        if(templateUrl.contains("AAA")){
            setCangDanHuRen(integers,workbook);
        }
        else if(templateUrl.contains("BBB")){
            setJunAn(integers,workbook);
        }
          else if(templateUrl.contains("***")){
            setSiWu(integers,workbook);
        }
        workbook.write(outputStream);
        outputStream.close();
        return filename;
    }


    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public static String getAbsoluteFile(String filename) {
        // 新建本地文件
        File dest = new File(   "/gzcw/uploadFiles/download/" + filename);
        if(!dest.getParentFile().exists()){ //判断文件父目录是否存在
            // 创建父目录
            dest.getParentFile().mkdirs();
        }
        String downloadPath = dest.getPath();
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists()) {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }

    private static void setCangDan(Integer[] integers,Workbook workbook){
        int[] ints = {0};
        for(int i =0;i<integers.length;i++){
            Map<Integer, int[]> integerHashMap = new HashMap<>(1);
            integerHashMap.put(0, ints);
            integerHashMap.put(4, ints);
            integerHashMap.put(5, ints);
            integerHashMap.put(6, ints);
            integerHashMap.put(7, ints);
            integerHashMap.put(8, ints);
            integerHashMap.put(9, ints);
            integerHashMap.put(10, ints);
            integerHashMap.put(11, ints);
            integerHashMap.put(12, ints);
            integerHashMap.put(13, ints);
            integerHashMap.put(14, ints);
            integerHashMap.put(15, ints);
            integerHashMap.put(16, ints);
            Sheet sheet = workbook.getSheetAt(i);
            PoiMergeCellUtil.mergeCells(sheet,integerHashMap,9,4);
        }
    }

    private static void setCangDanHuRen(Integer[] integers,Workbook workbook){
        int[] ints = {0};
        for(int i =0;i<integers.length;i++){
            Map<Integer, int[]> integerHashMap = new HashMap<>(1);
            integerHashMap.put(0, ints);
            integerHashMap.put(4, ints);
            integerHashMap.put(5, ints);
            integerHashMap.put(6, ints);
            integerHashMap.put(7, ints);
            integerHashMap.put(9, ints);
            integerHashMap.put(10, ints);
            integerHashMap.put(11, ints);
            integerHashMap.put(12, ints);
            integerHashMap.put(13, ints);
            integerHashMap.put(14, ints);
            integerHashMap.put(15, ints);
            integerHashMap.put(16, ints);
            Sheet sheet = workbook.getSheetAt(i);
            PoiMergeCellUtil.mergeCells(sheet,integerHashMap,5,0);
        }
    }

    private static void setJunAn(Integer[] integers,Workbook workbook){
        int[] ints = {1};
        int[] ints2 = {2};
        for(int i =0;i<integers.length;i++){
                if(i == 0){
                Map<Integer, int[]> integerHashMap = new HashMap<>(1);
                integerHashMap.put(0, ints2);
                integerHashMap.put(1, ints2);
                integerHashMap.put(2, ints2);
                integerHashMap.put(3, ints2);
                integerHashMap.put(4, ints2);
                integerHashMap.put(5, ints2);
                integerHashMap.put(6, ints2);
                integerHashMap.put(7, ints2);
                integerHashMap.put(8, ints2);
                integerHashMap.put(9, ints2);
                integerHashMap.put(10, ints2);
                integerHashMap.put(11, ints2);
                integerHashMap.put(12, ints2);
                integerHashMap.put(13, ints2);
                integerHashMap.put(14, ints2);
                integerHashMap.put(15, ints2);
                Sheet sheet = workbook.getSheetAt(i);
                PoiMergeCellUtil.mergeCells(sheet, integerHashMap, 5, 12);
            }
        }
    }

	//这个就是此设置单元格类型的 功能的核心代码
    private static void setSiWu(Integer[] integers,Workbook workbook){
        Sheet sheet = workbook.getSheetAt(0);//要设置的sheet页,我这里只有一个,故未用循环
        for(int i=0; i<=sheet.getLastRowNum();i++) {
            Row row2 =   sheet.getRow(i+1);
            int[] cellNum = {14,15,16,17,18,19,20,21,22,26,27,28,29,30,31,32,33};//要设置的列
            for(int cum : cellNum){
                if(row2 == null || row2.getCell(cum) == null )continue;
                String rc = row2.getCell(cum).getStringCellValue();
                if(StringUtils.isNumeric(rc)) {
                    row2.getCell(cum).setCellValue(Integer.valueOf(rc));
                    row2.getCell(cum).setCellType(Cell.CELL_TYPE_NUMERIC);
                }
            }
        }
    }

}

仅供参考的例子

我的核心逻辑
   		Sheet sheet = workbook.getSheetAt(0);//先拿到sheet
     	Row row2 =   sheet.getRow(i+1);//在拿到行数,实际数据上有多少行将1改为实际行数即可
      	if(row2 == null || row2.getCell(cum) == null )continue;//循环每一行的每一个单元格,判空,就跳过
      	//不为空则,可以为其设置类型
      	row2.getCell(cum).setCellValue(Integer.valueOf(rc));//先转为需要的类型
      	row2.getCell(cum).setCellType(Cell.CELL_TYPE_NUMERIC);//然后将单元格也设置成对应的格式
      	```