com.gitee.beiding.template_excel.FastSheet Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of template-excel Show documentation
使用模板快速提取excel文件中的数据为数据实体,或者使用模板将数据实体渲染成excel
There is a newer version: 3.18.1-RELEASE
package com.gitee.beiding.template_excel;


import org.apache.poi.ss.util.CellRangeAddress;
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 java.util.*;

public class FastSheet {


    public static FastSheet create(String name) {
        FastSheet fastSheet = new FastSheet(name);
        return fastSheet;
    }

    private String name;

    private FastSheet(String name) {
        this.name = name;
    }

    private ColNumberMatchingMode mode = ColNumberMatchingMode.EQUALS;

    public FastSheet colEquals() {
        mode = ColNumberMatchingMode.EQUALS;
        return this;
    }

    public FastSheet colIntersection() {
        mode = ColNumberMatchingMode.INTERSECTION;
        return this;
    }


    public FastSheet colTemplateFull() {
        mode = ColNumberMatchingMode.TEMPLATE_FULL;
        return this;
    }


    private List rows = new ArrayList<>();

    public class Row {
        Map cellMap = new HashMap<>();

        public XSSFSheet toXSSFSheet() {
            return sheet().toXSSFSheet();
        }

        public class Cell {
            Object v;
            int cell;
            int row;
            int col;

            public XSSFSheet toXSSFSheet() {
                return sheet().toXSSFSheet();
            }

            public FastSheet sheet() {
                return FastSheet.this;
            }
        }


        public Row newRow() {
            return sheet().newRow();
        }

        public FastSheet sheet() {
            return FastSheet.this;
        }

        public Row cell(String cell, Object v) {
            return cell(cell, v, 1, 1);
        }

        public Row cell(String cell, Object v, int row, int col) {
            cell = cell.toUpperCase();
            char[] chars = cell.toCharArray();
            int sum = 0;
            for (int i = 0; i < chars.length; i++) {
                if (chars[i] < 'A' || chars[i] > 'Z') {
                    throw new RuntimeException("无效标记:" + chars[i]);
                }
                sum += (chars[i] - 'A') * Math.pow(26, i);
            }
            return cell(sum, v, row, col);
        }

        public Row cell(int cell, Object v) {
            return cell(cell, v);
        }

        public Row cell(int cell, Object v, int row, int col) {

            Cell ce = new Cell();
            ce.cell = cell;
            ce.v = v;
            if (cellMap.containsKey(cell)) {
                throw new RuntimeException("无法添加重复的单元格:" + cell);
            }
            ce.row = row;
            ce.col = col;
            cellMap.put(cell, ce);
            return this;
        }


    }

    public Row newRow() {
        Row row = new Row();
        rows.add(row);
        return row;
    }

    public XSSFSheet toXSSFSheet() {
        XSSFWorkbook sheets = new XSSFWorkbook();

        //创建一个模板页
        XSSFSheet template = sheets.createSheet(name);

        for (int i = 0; i < this.rows.size(); i++) {
            XSSFRow row = template.createRow(i);
            FastSheet.Row theRow = this.rows.get(i);
            for (Integer c : theRow.cellMap.keySet()) {
                XSSFCell cell = row.createCell(c);
                FastSheet.Row.Cell cell1 = theRow.cellMap.get(c);

                if (cell1.row * cell1.col > 1) {

                    //添加单元格合并
                    template.addMergedRegion(new CellRangeAddress(i, i + cell1.row - 1, c, c + cell1.col - 1));
                }

                Object v = cell1.v;

                if (v == null) {
                    continue;
                }

                if (v instanceof String) {
                    cell.setCellValue((String) v);
                } else if (v instanceof Double) {
                    cell.setCellValue((Double) v);
                } else if (v instanceof Boolean) {
                    cell.setCellValue((Boolean) v);
                } else if (v instanceof Date) {
                    cell.setCellValue((Date) v);
                } else if (v instanceof Calendar) {
                    cell.setCellValue((Calendar) v);
                } else {
                    cell.setCellValue(v.toString());
                }
            }

        }

        return template;
    }

}