poi文本导出为数字

不念不忘少年蓝@ 2023-10-10 08:55 38阅读 0赞

# 1.需求描述 #

用户要求导出的金额格式为数字，不能导出为文本  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTM2MjUw_size_16_color_FFFFFF_t_70]

POI导出工具类

import com.platform.park.model.vo.carRecords.ExportOutOfYardVO;
    import io.swagger.annotations.ApiModelProperty;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.cglib.beans.BeanMap;
    import org.springframework.stereotype.Service;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.math.BigDecimal;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    
    @Service
    public class ExcelExpotUtils {
    
        public static final int SHEET_PAGE_NUM = 10000;
    
        public void expoerDataExcel(HttpServletResponse response,String fileName, Class c, List<BeanMap> beanMapList) throws IOException, NoSuchFieldException {
            XSSFWorkbook wb = new XSSFWorkbook();
            // 工作表对象
            XSSFSheet sheet = null;
            //行对象
            Row row = null;
            //列对象
            Cell cell = null;
            List<String> titleList= new ArrayList<>();
            List<String> fieldList = new ArrayList<>();
            Field[] fields = c.getFields();
            for (Field field:fields){
                titleList.add(field.getAnnotation(ApiModelProperty.class).value());
                fieldList.add(field.getName());
            }
            //总行号
            int rowNo = 0;
            //页行号
            int pageRowNo = 0;
            if (beanMapList==null||beanMapList.size()==0){
                sheet = wb.createSheet("工作簿"+(rowNo/SHEET_PAGE_NUM));
                //动态指定当前的工作表
                sheet = wb.getSheetAt(rowNo/SHEET_PAGE_NUM);
                //新建了工作表,重置工作表的行号为0
                pageRowNo = 0;
                // -----------定义表头-----------
                row = sheet.createRow(pageRowNo++);
                // 列数 titleKeyList.size()
                for(int i=0;i<titleList.size();i++){
                    Cell cellTem = row.createCell(i);
                    cellTem.setCellValue(titleList.get(i));
                }
            }else {
                for (int k = 0; k < beanMapList.size(); k++) {
                    Map<String, Object> srcMap = beanMapList.get(k);
                    //写入SHEET_PAGE_NUM条后切换到下个工作表
                    if (rowNo % SHEET_PAGE_NUM == 0) {
                        //        System.out.println("Current Sheet:" + rowNo/SHEET_PAGE_NUM);
                        //建立新的sheet对象
                        sheet = wb.createSheet("工作簿" + (rowNo / SHEET_PAGE_NUM));
                        //动态指定当前的工作表
                        sheet = wb.getSheetAt(rowNo / SHEET_PAGE_NUM);
                        //新建了工作表,重置工作表的行号为0
                        pageRowNo = 0;
                        // -----------定义表头-----------
                        row = sheet.createRow(pageRowNo++);
                        // 列数 titleKeyList.size()
                        for (int i = 0; i < titleList.size(); i++) {
                            Cell cellTem = row.createCell(i);
                            cellTem.setCellValue(titleList.get(i));
                        }
                        rowNo++;
                        // ---------------------------
                    }
                    rowNo++;
                    //新建行对象
                    row = sheet.createRow(pageRowNo++);
                    // 行，获取cell值
                    for (int j = 0; j < fieldList.size(); j++) {
                        cell = row.createCell(j);
                        Object object = srcMap.get(fieldList.get(j));
                        if (object != null) {
                            if (object instanceof BigDecimal){
                                cell.setCellValue(Double.parseDouble(object.toString()));
                                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                            }else {
                                cell.setCellValue(object.toString());
                            }
                        } else {
                            if (object instanceof BigDecimal){
                                cell.setCellValue(0);
                                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                            }else{
                                cell.setCellValue("");
                            }
    
                        }
                        //设置单元格类型
    
                    }
                }
            }
            setSizeColumn(sheet,fieldList.size()==0?1:fieldList.size());
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.addHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1") + ".xlsx");
            response.flushBuffer();
            OutputStream outputStream = response.getOutputStream();
            wb.write(response.getOutputStream());
            wb.close();
            outputStream.flush();
            outputStream.close();
        }
    
    
        // 自适应宽度(中文支持)
        private void setSizeColumn(XSSFSheet sheet, int size) {
            for (int columnNum = 0; columnNum < size; columnNum++) {
                int columnWidth = sheet.getColumnWidth(columnNum) / 256;
                System.out.println(sheet.getLastRowNum());
                for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    XSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
    
                    if (currentRow.getCell(columnNum) != null) {
                        XSSFCell 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);
            }
        }
    
    
        public static void main(String[] args) throws NoSuchFieldException {
            ExportOutOfYardVO vo = new ExportOutOfYardVO();
            vo.setCarNo("1");
            vo.setFeeStandardName("322");
            vo.setEnterTime(new Date());
            vo.setLeaveTime(new Date());
            vo.setMoney(new BigDecimal("2324"));
            System.out.println( ExportOutOfYardVO.class.getFields().length);
    //        for (int i=0; i< ExportOutOfYardVO.class.getFields().length;i++){
    //            String a = ExportOutOfYardVO.class.getFields()[i].getAnnotation(ApiModelProperty.class).value();
    //            String b = ExportOutOfYardVO.class.getFields()[i].getName();
    //            System.out.println(a);
    //            System.out.println(b);
    //        }
            Object o = new BigDecimal("0.01");
            System.out.println(String.valueOf(o));
            String a = String.valueOf(o);
            System.out.println(a ==null);
            System.out.println(a.equals("null"));
            if (o instanceof BigDecimal){
                System.out.println("1122---22");
                System.out.println(o);
            }
    
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTM2MjUw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190924112736107.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMTM2MjUw,size_16,color_FFFFFF,t_70