ExcelUtil 生成excel工具

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.util.List;
import java.util.Map;

public class ExcelUtil {

    /**
     * 生成Excel workbook工具类，后去workbook后，可以直接调用OutputStream out = response.getOutputStream();workbook.write(out);返回
     * @param headers
     * @param contents
     * @return
     */
    public static HSSFWorkbook buildExcel(Map<String, String> headers, List<Map<String, String>> contents) {
        HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个excel
        HSSFSheet sheet = workbook.createSheet("Sheet1");// 新建sheet页

        HSSFCellStyle cellStyle = workbook.createCellStyle(); // 新建单元格样式
        cellStyle.setFillForegroundColor((short) 13);// 设置背景色
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setWrapText(true);// 设置自动换行

        //设置标题
        HSSFRow titleRow = sheet.createRow(0);
        int[] i = {0};
        headers.forEach((key, val) -> titleRow.createCell(i[0]++).setCellValue(val));

        //设置内容
        int row = 1; int[] j = {0};
        for (Map<String, String> data : contents) {
            HSSFRow hssfRow = sheet.createRow(row++);
            j[0] = 0;
            headers.forEach((key, v) -> {
                String val = data.get(key);
                hssfRow.createCell(j[0]++).setCellValue(val);
            });
        }

        return workbook;
    }
}

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.Map.Entry;

import lombok.val;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import com.entity.SheetHolder;

import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class ExcelUtil {

    /**
     * 创建样式库
     * @param wb 工作簿
     * @return 样式库Map
     */
    public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
        Map<String, XSSFCellStyle> styles = new HashMap<>(16);
        XSSFDataFormat fmt = wb.createDataFormat();

        //创建字体对象
        Font font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short)10);
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());

        //设置字体
        XSSFFont fontRed = wb.createFont();
        fontRed.setFontName("微软雅黑");
        fontRed.setFontHeightInPoints((short)10);
        fontRed.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());

        XSSFFont fontGreen = wb.createFont();
        fontGreen.setFontName("微软雅黑");
        fontGreen.setFontHeightInPoints((short)10);
        fontGreen.setColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());


        XSSFCellStyle style1 = wb.createCellStyle();
        style1.setAlignment(HorizontalAlignment.RIGHT);
        style1.setDataFormat(fmt.getFormat("0.00%"));
        style1.setFont(font);
        styles.put("percent", style1);

        XSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setDataFormat(fmt.getFormat("0.0X"));
        style2.setFont(font);
        styles.put("coeff", style2);

        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(HorizontalAlignment.RIGHT);
        style3.setDataFormat(fmt.getFormat("$#,##0.00"));
        style3.setFont(font);
        styles.put("currency", style3);

        XSSFCellStyle style4 = wb.createCellStyle();
        style4.setAlignment(HorizontalAlignment.RIGHT);
        style4.setDataFormat(fmt.getFormat("mmm dd"));
        style4.setFont(font);
        styles.put("date", style4);

        XSSFCellStyle style5 = wb.createCellStyle();
        XSSFFont headerFont = wb.createFont();
        style5.setFont(headerFont);
        style5.setAlignment(HorizontalAlignment.CENTER);
        style5.setFont(font);
        styles.put("header", style5);

        XSSFCellStyle style6 = wb.createCellStyle();
        style6.setAlignment(HorizontalAlignment.CENTER);
        style6.setFont(font);
        styles.put("center", style6);

        XSSFCellStyle style7 = wb.createCellStyle();
        style7.setAlignment(HorizontalAlignment.RIGHT);
        style7.setDataFormat(fmt.getFormat("#,##0"));
        style7.setFont(font);
        styles.put("long", style7);

        XSSFCellStyle styleRed = wb.createCellStyle();
        styleRed.setFont(fontRed);
        styles.put("red", styleRed);

        XSSFCellStyle styleGreen = wb.createCellStyle();
        styleGreen.setFont(fontGreen);
        styles.put("green",styleGreen);

        XSSFCellStyle defaultStyle = wb.createCellStyle();
        defaultStyle.setAlignment(HorizontalAlignment.CENTER);
        defaultStyle.setFont(font);
        //前景填充色
        styles.put("defaultStyle", defaultStyle);


        return styles;
    }
    /**
     * 设置单元格的值 - 替换上面方法
     * @param cell   单元格
     * @param object 属性所对应的值
     * @param type   该值的类型
     */
    public static void setCellData(Cell cell, Map<String, XSSFCellStyle> styleMap, Object object, String type) {
        try {
            if(object == null){
                return;
            }
            if ("currency".equals(type)) {
                double currency = Double.parseDouble(object.toString());
                cell.setCellValue(DigestUtils.formatDouble(currency));
                XSSFCellStyle style = styleMap.get("currency");
                cell.setCellStyle(style);
            } else if ("percent".equals(type)) {
                double dVal = Double.parseDouble(object.toString());
                cell.setCellValue(DigestUtils.formatDouble(dVal, 4));
                cell.setCellType(CellType.NUMERIC);
                XSSFCellStyle style = styleMap.get("percent");
                cell.setCellStyle(style);
            } else if ("long".equals(type)) {
                long v = Long.parseLong(object.toString());
                cell.setCellValue(v);
                cell.setCellType(CellType.NUMERIC);
                XSSFCellStyle style = styleMap.get("long");
                cell.setCellStyle(style);
            } else if ("number".equals(type)) {
                cell.setCellValue(new Double(object.toString()));
                cell.setCellType(CellType.NUMERIC);
            }  else if("red".equals(type)){
                cell.setCellValue(String.valueOf(object));
                cell.setCellType(CellType.STRING);
                XSSFCellStyle style = styleMap.get("red");
                cell.setCellStyle(style);
            } else if("green".equals(type)){
                cell.setCellValue(String.valueOf(object));
                cell.setCellType(CellType.STRING);
                XSSFCellStyle style = styleMap.get("green");
                cell.setCellStyle(style);
            }  else {
                cell.setCellValue(String.valueOf(object));
                cell.setCellType(CellType.STRING);
                XSSFCellStyle style = styleMap.get("defaultStyle");
                cell.setCellStyle(style);
            }
        }catch (Exception ex){
            log.info("赋值单元格出错",ex);
        }
    }

    /**
     * 根据表头Map来创建表格头部信息
     *
     * @param sheet       表格
     * @param styleHeader 表格头部样式
     * @param headMap     表格头部内容
     */
    public static void createSheetHead(XSSFSheet sheet, XSSFCellStyle styleHeader, HashMap<String, String> headMap) {
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell;
        //在EXCEL的第一行建立各列的标题
        int cellIndex = 0;
        for (Map.Entry<String, String> entry : headMap.entrySet()) {
            cell = row.createCell(cellIndex);
            cell.setCellValue(entry.getValue());
            cell.setCellStyle(styleHeader);
            cellIndex++;
        }
    }



    /**
     * 生成Excel workbook工具类，后去workbook后，可以直接调用OutputStream out =
     * response.getOutputStream();workbook.write(out);返回
     * 
     * @param headers
     * @param contents
     * @return
     */
    public static HSSFWorkbook buildExcel(Map<String, String> headers, List<Map<String, String>> contents) {
        HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个excel
        HSSFSheet sheet = workbook.createSheet("Sheet1");// 新建sheet页

        HSSFCellStyle cellStyle = workbook.createCellStyle(); // 新建单元格样式
        cellStyle.setFillForegroundColor((short) 13);// 设置背景色
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setWrapText(true);// 设置自动换行

        // 设置标题
        HSSFRow titleRow = sheet.createRow(0);
        int[] i = { 0 };
        headers.forEach((key, val) -> titleRow.createCell(i[0]++).setCellValue(val));

        // 设置内容
        int row = 1;
        int[] j = { 0 };
        for (Map<String, String> data : contents) {
            HSSFRow hssfRow = sheet.createRow(row++);
            j[0] = 0;
            headers.forEach((key, v) -> {
                String val = data.get(key);
                hssfRow.createCell(j[0]++).setCellValue(val);
            });
        }

        return workbook;
    }

    /**
     * 同表头，多个表格
     * @param headers
     * @param contents
     * @return
     */
    public static HSSFWorkbook buildExcel(Map<String, String> headers, Map<Object,List<Map<String, String>>> contents) {
        HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个excel
        HSSFSheet sheet = workbook.createSheet("Sheet1");// 新建sheet页

        HSSFCellStyle cellStyle = workbook.createCellStyle(); // 新建单元格样式
        cellStyle.setFillForegroundColor((short) 13);// 设置背景色
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setWrapText(true);// 设置自动换行

        int row = 0;
        for(Object key:contents.keySet()){
            // 设置标题
            HSSFRow titleRow = sheet.createRow(row);
            int[] i = { 0 };
            headers.forEach((k, val) -> titleRow.createCell(i[0]++).setCellValue(val));

            row = row+1;
            List<Map<String, String>> oneContent = contents.get(key);
            // 设置内容
            int[] j = { 0 };
            for (Map<String, String> data : oneContent) {
                HSSFRow hssfRow = sheet.createRow(row++);
                j[0] = 0;
                headers.forEach((k, v) -> {
                    String val = data.get(k);
                    hssfRow.createCell(j[0]++).setCellValue(val);
                });
            }
            //设置完一块个表格后，分隔行
            int r = row++;
            sheet.addMergedRegion(new CellRangeAddress(r, r, 0, oneContent.get(0).size()));
        }

        return workbook;
    }
    /**
     * 读取excel
     * 
     * @param filePath
     * @return
     */
    public static Workbook readExcel(String filePath) {
        Workbook wb = null;
        if (filePath == null) {
            return null;
        }
        String subfix = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(subfix)) {
                return wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(subfix)) {
                return wb = new XSSFWorkbook(is);
            } else {
                return wb = null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return wb;
    }

    /**
     * 读取表格字段
     * 
     * @param wb
     * @return
     */
    public static List<String> readColumnKeys(Workbook wb) {
        List<String> list = new ArrayList<String>();
        if (wb == null) {
            return list;
        }
        // 获取第一个sheet
        Sheet sheet = wb.getSheetAt(0);
        if (sheet == null) {
            return list;
        }
        // 获取最大行数
        int rowSize = sheet.getPhysicalNumberOfRows();
        if (rowSize < 1) {
            return list;
        }
        // 获取第一行
        Row row = sheet.getRow(0);
        // 获取最大列数
        int columnSize = row.getPhysicalNumberOfCells();
        for (int i = 0; i < columnSize; i++) {
            list.add(row.getCell(i).toString());
        }
        return list;
    }

    /**
     * 读取总行数
     * 
     * @param wb
     * @return
     */
    public static int readRowSize(Workbook wb) {
        if (wb == null) {
            return 0;
        }
        // 获取第一个sheet
        Sheet sheet = wb.getSheetAt(0);
        if (sheet == null) {
            return 0;
        }
        // 获取最大行数
        return sheet.getPhysicalNumberOfRows();
    }

    /**
     * 读取单元格值
     * 
     * @param cell
     * @return
     */
    public static String readCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        cell.setCellType(CellType.STRING);
        return cell.toString();
    }

    /**
     * 生成Excel workbook工具类，后去workbook后，可以直接调用OutputStream out =
     * response.getOutputStream();workbook.write(out);返回
     * 
     * @param headers
     * @param contents
     * @return
     */
    public static HSSFWorkbook buildExcelWithMerge(Map<String, String> headers, List<Map<String, String>> contents,
            Set<String> mergeColumns) {
        HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个excel
        HSSFSheet sheet = workbook.createSheet("Sheet1");// 新建sheet页

        HSSFCellStyle cellStyle = workbook.createCellStyle(); // 新建单元格样式
        cellStyle.setFillForegroundColor((short) 13);// 设置背景色
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setWrapText(true);// 设置自动换行

        // 设置标题
        HSSFRow titleRow = sheet.createRow(0);
        int[] i = { 0 };
        headers.forEach((key, val) -> titleRow.createCell(i[0]++).setCellValue(val));

        boolean enableMerge = CollectionUtils.isEmpty(mergeColumns) == false;
        // 设置内容
        int row = 1;
        int[] j = { 0 };

        Map<String, MergeHolder> mergeRowMap = new HashMap<String, MergeHolder>();
        for (Map<String, String> data : contents) {
            if (enableMerge) {
                String key = getRepeatColumnMapKey(data, mergeColumns);
                MergeHolder holder = mergeRowMap.get(key);
                if (holder == null) {
                    holder = new MergeHolder();
                    holder.setBeginRow(row);
                } else {
                    holder.setEndRow(row);
                }
                mergeRowMap.put(key, holder);
            }
            HSSFRow hssfRow = sheet.createRow(row++);
            j[0] = 0;
            headers.forEach((key, v) -> {
                String val = data.get(key);
                hssfRow.createCell(j[0]++).setCellValue(val);
            });
        }

        // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
        // 行和列都是从0开始计数，且起始结束都会合并
        if (enableMerge) {
            List<Integer> columnNums = new ArrayList<Integer>();
            int columnAt = 0;
            for (Entry<String, String> header : headers.entrySet()) {
                if (mergeColumns.contains(header.getKey())) {
                    columnNums.add(columnAt);
                }
                columnAt++;
            }
            for (Entry<String, MergeHolder> item : mergeRowMap.entrySet()) {
                MergeHolder holder = item.getValue();
                if (holder == null || holder.getBeginRow() < 0 || holder.getEndRow() < 0) {
                    continue;
                }
                for (Integer colAt : columnNums) {
                    log.info("merge, beginRow:{}, endRow:{}, beginCol:{}, endCol:{}", holder.getBeginRow(),
                            holder.getEndRow(), colAt, colAt);
                    sheet.addMergedRegion(new CellRangeAddress(holder.getBeginRow(), holder.getEndRow(), colAt, colAt));
                }
            }
        }

        return workbook;
    }

    /**
     * 复杂表格，例如叠拼的表格
     * 采用 传入加工过的workbook 继续附加其他表格
     * @param headers
     * @param contents
     * @return
     */
    public static HSSFWorkbook buildDiepinExcel(HSSFWorkbook workbook, int starRowIndex, List<LinkedHashMap<String, Object>> headers, List<LinkedHashMap<String, String>> contents) {
        HSSFSheet sheet = workbook.getSheet("Sheet1");
        if(sheet==null){
            sheet = workbook.createSheet("Sheet1");// 新建sheet页
        }

        HSSFCellStyle cellStyle = workbook.createCellStyle(); // 新建单元格样式
        cellStyle.setFillForegroundColor((short) 13);// 设置背景色
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setWrapText(true);// 设置自动换行

        // 设置标题
        if(headers!=null){
            for(Map<String, Object> m:headers){//每一行head
                int[] i = { 0 };
                HSSFRow titleRow = sheet.createRow(starRowIndex);
                Object col = m.get("col");
                if(col ==null){
                    m.forEach((key, val) -> titleRow.createCell(i[0]++).setCellValue(val.toString()));
                }else{
                    for(String k:m.keySet()){
                        if(!k.equals("col")){
                            titleRow.createCell(i[0]++).setCellValue(m.get(k).toString());
                            sheet.addMergedRegion(new CellRangeAddress(starRowIndex,starRowIndex, i[0]-1, i[0]+(Integer)col));
                        }
                    }

                }
                starRowIndex++;
            }
        }

        // 设置内容
        int row = starRowIndex;
        int[] j = { 0 };

        for (Map<String, String> data : contents) {
            HSSFRow hssfRow = sheet.createRow(row++);
            j[0] = 0;
            data.forEach((key, v) -> {
                hssfRow.createCell(j[0]++).setCellValue(v);
            });
        }

        return workbook;
    }


    private static String getRepeatColumnMapKey(Map<String, String> data, Set<String> mergeColumns) {
        StringBuilder sb = new StringBuilder();
        for (String col : mergeColumns) {
            sb.append(data.get(col));
        }
        return sb.toString();
    }

    @Getter
    @Setter
    public static class MergeHolder {
        private int beginRow;

        private int endRow;

        public MergeHolder(int beginRow, int endRow) {
            this.beginRow = beginRow;
            this.endRow = endRow;
        }

        public MergeHolder() {
            this(-1, -1);
        }
    }

    /**
     * 1次导出多个sheet页
     * 
     * @param list
     * @return
     */
    public static HSSFWorkbook buildExcel(List<SheetHolder> list) {
        HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个excel
        
        // 多个sheet页
        for (SheetHolder holder : list) {
            HSSFSheet sheet = workbook.createSheet(holder.getSheetName());// 新建sheet页
            
            HSSFCellStyle cellStyle = workbook.createCellStyle(); // 新建单元格样式
            cellStyle.setFillForegroundColor((short) 13);// 设置背景色
            cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
            cellStyle.setWrapText(true);// 设置自动换行
            
            // 设置标题
            HSSFRow titleRow = sheet.createRow(0);
            int[] i = { 0 };
            Map<String, String> headers = holder.getHeaders();
            headers.forEach((key, val) -> titleRow.createCell(i[0]++).setCellValue(val));
            
            // 百分比格式
            HSSFCellStyle percentStyle = workbook.createCellStyle();
            percentStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
            
            // 设置内容
            int row = 1;
            int[] j = { 0 };
            List<Map<String, String>> contents = holder.getContents();
            boolean hasPercentColumn = holder.getPercentColumnBegin() != null;
            int percentBegin = hasPercentColumn ? holder.getPercentColumnBegin() : Integer.MAX_VALUE;
            
            boolean hasNumberColumn = holder.getNumberColumnBegin() != null;
            int numberBegin = hasNumberColumn ? holder.getNumberColumnBegin() : Integer.MAX_VALUE;
            for (Map<String, String> data : contents) {
                HSSFRow hssfRow = sheet.createRow(row++);
                j[0] = 0;
                headers.forEach((key, v) -> {
                    String val = data.get(key);
                    HSSFCell cell = null;
                    if (hasPercentColumn && j[0] >= percentBegin) {
//                         log.info("percent key:{}, val:{}", key, val);
                        cell = hssfRow.createCell(j[0]);
                        cell.setCellStyle(percentStyle);
                        if (val.endsWith("%")) {
                            cell.setCellValue(Double.valueOf(val.substring(0, val.length() - 1)) / 100);
                        } else {
                            cell.setCellValue(Double.valueOf(val));
                        }
                    } else if (hasNumberColumn && j[0] >= numberBegin) {
//                         log.info("number key:{}, val:{}", key, val);
                        if (val.contains(",")) {
                            val = val.replaceAll(",", "");
                        }
                        cell = hssfRow.createCell(j[0], CellType.NUMERIC);
                        cell.setCellValue(Integer.valueOf(val));
                    } else {
                        cell = hssfRow.createCell(j[0]);
                        cell.setCellValue(val);
                    }
                    j[0]++;
                    
//                    hssfRow.createCell(j[0]++).setCellValue(val);
                });
            }
        }
        
        return workbook;
    }
    
    /**
     * 1次导出多个sheet页，单元格可以使用格式
     * 
     * @param list
     * @return
     */
    public static HSSFWorkbook buildExcelUseStype(List<SheetHolder> list) {
        HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个excel
        
        // 多个sheet页
        for (SheetHolder holder : list) {
            HSSFSheet sheet = workbook.createSheet(holder.getSheetName());// 新建sheet页
            
            HSSFCellStyle cellStyle = workbook.createCellStyle(); // 新建单元格样式
            cellStyle.setFillForegroundColor((short) 13);// 设置背景色
            cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
            cellStyle.setWrapText(true);// 设置自动换行
            
            // 设置标题
            HSSFRow titleRow = sheet.createRow(0);
            int[] i = { 0 };
            Map<String, String> headers = holder.getHeaders();
            headers.forEach((key, val) -> titleRow.createCell(i[0]++).setCellValue(val));
            
            // 百分比格式
            HSSFCellStyle percentStyle = workbook.createCellStyle();
            percentStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
            
            // 设置内容
            int row = 1;
            int[] j = { 0 };
            List<Map<String, String>> contents = holder.getContents();
            boolean hasPercentColumn = holder.getPercentColumns() != null;
            
            boolean hasNumberColumn = holder.getNumberColumns() != null;
            for (Map<String, String> data : contents) {
                HSSFRow hssfRow = sheet.createRow(row++);
                j[0] = 0;
                headers.forEach((key, v) -> {
                    String val = data.get(key);
                    HSSFCell cell = null;
                    if (hasPercentColumn && holder.getPercentColumns().contains(j[0])) {
//                         log.info("percent key:{}, val:{}", key, val);
                        cell = hssfRow.createCell(j[0]);
                        cell.setCellStyle(percentStyle);
                        if (val.endsWith("%")) {
                            cell.setCellValue(Double.valueOf(val.substring(0, val.length() - 1)) / 100);
                        } else {
                            cell.setCellValue(Double.valueOf(val));
                        }
                    } else if (hasNumberColumn && holder.getNumberColumns().contains(j[0])) {
                         log.info("number key:{}, val:{}", key, val);
                        if (val.contains(",")) {
                            val = val.replaceAll(",", "");
                        }
                        cell = hssfRow.createCell(j[0], CellType.NUMERIC);
                        cell.setCellValue(Integer.valueOf(val));
                    } else {
                        cell = hssfRow.createCell(j[0]);
                        cell.setCellValue(val);
                    }
                    j[0]++;
                    
//                    hssfRow.createCell(j[0]++).setCellValue(val);
                });
            }
        }
        
        return workbook;
    }
}

@Getter
@Setter
public class SheetHolder {

    private String sheetName;

    private Map<String, String> headers;
    private List<Map<String, String>> contents;
    
    /**
     * 指定具体几列开始是百分比，列号从0开始，null则没有
     */
    private Set<Integer> percentColumns;
    
    /**
     * 指定具体几列开始是数字，列号从0开始，null则没有
     */
    private Set<Integer> numberColumns;
    
    /**
     * 从第几列开始是百分比，从0开始，null则没有
     */
    private Integer percentColumnBegin;
    
    /**
     * 从第几列开始是数字，从0开始，null则没有
     */
    private Integer numberColumnBegin;
}

 

关注公众号获取更多内容，有问题也可在公众号提问哦：

 

强哥叨逼叨

叨逼叨编程、互联网的见解和新鲜事