文档章节

POI Excel 工具类

Geeyu
 Geeyu
发布于 2017/09/07 20:57
字数 929
阅读 26
收藏 0

码上生花,ECharts 作品展示赛正式启动!>>>

简述

  1. 生成为.xlsx后缀
  2. 基本格式为 标题 + 字段 + 内容 + 备注
  3. 可以指定创建下拉框
  4. 流式风格API,易于理解使用
  5. 样式可扩展

最终效果

最终效果

测试类

import net.sf.json.JSONArray;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

/**
 * Created by gy on 2017/9/7.
 */
public class ExcelUtils2007Test {


    public static void main(String[] args) throws IOException {


        ExcelUtils2007 builder = new ExcelUtils2007();

        JSONArray content = JSONArray.fromObject("[[\"a\",\"b\",\"c\"],[\"aa\",\"bb\",\"cc\"],[\"aaa\",\"bbb\",\"ccc\"]]");
        JSONArray data = JSONArray.fromObject("[{\"text\":\"高\",\"value\":\"H\"},{\"text\":\"中\",\"value\":\"M\"},{\"text\":\"低\",\"value\":\"L\"}]");

        builder.createSheet("sheetName")
                .setTitle("title")
                .setStyle(ExcelUtils2007.STYLE_TITLE)
                .setField(new String[]{"A", "B", "C"})
                .setStyle(ExcelUtils2007.STYLE_FIELD)
                .setContent(content)
                .setStyle(ExcelUtils2007.STYLE_CONTENT)
                .setRemark("remark")
                .setStyle(ExcelUtils2007.REMARK_CONTENT)
                .build();

        // 设置下拉框
        builder.setDropDownBox(data, 2, 2, 4, 4);

        XSSFWorkbook workbook = builder.getWorkbook();

        OutputStream outputStream = new FileOutputStream("E:\\2.xlsx");
        workbook.write(outputStream);
        outputStream.flush();


    }

}

工具类

import net.sf.json.JSONArray;
import org.apache.commons.collections.map.LinkedMap;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.network.framework.exception.BusinessException;

import java.util.ArrayList;
import java.util.UUID;

/**
 * Created by gy on 2017/9/7.
 */
public class ExcelUtils2007 {

    private XSSFWorkbook workbook = new XSSFWorkbook();
    private XSSFSheet sheet;
    private LinkedMap cellStyleMap = new LinkedMap();
    private ArrayList<CellRangeAddress> mergeRegionList = new ArrayList();

    private static int TITLE_ROW_NUM = 0;
    private static int FIELD_ROW_NUM = 1;
    private static int CONTENT_START_ROW_NUM = 2;

    public static String STYLE_TITLE = StyleEnum.STYLE_TITLE.toString();
    public static String STYLE_FIELD = StyleEnum.STYLE_FIELD.toString();
    public static String STYLE_CONTENT = StyleEnum.STYLE_CONTENT.toString();
    public static String REMARK_CONTENT = StyleEnum.REMARK_CONTENT.toString();

    /*************************************** main start *********************************************************/
    public ExcelUtils2007 createSheet(String sheetName) {
        if (this.workbook == null)
            throw new BusinessException("请指定工作簿!");
        this.sheet = createSheet(workbook, sheetName);
        this.sheet.setDefaultColumnWidth(15);
        return this;
    }
    public static XSSFSheet createSheet(XSSFWorkbook workbook, String sheetName) {
        return workbook.createSheet(sheetName);
    }

    public ExcelUtils2007 setTitle(String title) {
        CellRangeAddress address = setTitle(this.sheet, title);
        cellStyleMap.put(address, null);
        return this;
    }
    public static CellRangeAddress setTitle(XSSFSheet sheet, String title) {

        Row row = sheet.createRow(TITLE_ROW_NUM);
        final Cell cell = row.createCell(0);
        cell.setCellValue(title);

        CellRangeAddress address = new CellRangeAddress(TITLE_ROW_NUM, TITLE_ROW_NUM , 0, 0);

        return address;

    }

    public ExcelUtils2007 setField(String[] fields) {
        CellRangeAddress cells = setField(this.sheet, fields);
        cellStyleMap.put(cells, null);
        mergeRegionList.add(new CellRangeAddress(0, 0, 0, fields.length));
        return this;
    }
    public static CellRangeAddress setField(XSSFSheet sheet, String[] fields) {

        Row row = sheet.createRow(FIELD_ROW_NUM);

        // 第一个字段为序号
        row.createCell(0).setCellValue("序号");

        int i = 1;
        for (String field : fields)
            row.createCell(i++).setCellValue(field);

        return new CellRangeAddress(FIELD_ROW_NUM, FIELD_ROW_NUM, 0, fields.length);
    }

    public ExcelUtils2007 setContent(JSONArray content) {
        CellRangeAddress address = setContent(this.sheet, content);
        if (address != null)
            cellStyleMap.put(address, null);
        return this;
    }
    public static CellRangeAddress setContent(XSSFSheet sheet, JSONArray content) {
        if (content == null || content.size() == 0)
            return null;

        for (int i = 0; i < content.size(); i++) {
            XSSFRow row = sheet.createRow(CONTENT_START_ROW_NUM + i);
            JSONArray bees = content.optJSONArray(i);

            Cell firstCol = row.createCell(0);// 序号
            firstCol.setCellValue(i + 1);
            for (int j = 0; j < bees.size(); j++)
                row.createCell(j + 1).setCellValue(bees.optString(j));
        }

        return new CellRangeAddress(CONTENT_START_ROW_NUM,
                CONTENT_START_ROW_NUM + content.size() - 1,
                0,
                content.optJSONArray(0).size());
    }

    public ExcelUtils2007 setRemark(String remark) {
        CellRangeAddress address = setRemark(this.sheet, remark);
        cellStyleMap.put(address, null);

        int fieldNum = sheet.getRow(1).getPhysicalNumberOfCells();
        CellRangeAddress address1 = address.copy();
        address1.setLastColumn(fieldNum - 1);
        mergeRegionList.add(address1);
        return this;
    }

    public static CellRangeAddress setRemark(XSSFSheet sheet, String remark) {

        int firstRow = sheet.getLastRowNum() + 1;
        int lastRow = sheet.getLastRowNum() + 1;
        int firstCol = 0;

        sheet.createRow(firstRow).createCell(firstCol).setCellValue(remark);

        return new CellRangeAddress(firstRow, lastRow, firstCol, firstCol);
    }

    public ExcelUtils2007 setStyle(String styleName) {

        StyleEnum styleEnum = StyleEnum.valueOf(styleName);
        String fontName = styleEnum.getFontName();
        short fontSize = styleEnum.getFontSize();
        short fillColor = styleEnum.getFillColor();
        short align = styleEnum.getAlign();

        XSSFCellStyle style = this.workbook.createCellStyle();

        // 字体
        XSSFFont font = workbook.createFont();
        font.setFontHeight(fontSize);
        font.setFontName(fontName);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setColor(HSSFFont.COLOR_NORMAL);
        style.setFont(font);

        // 填充色
        style.setFillForegroundColor(fillColor);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // 边框
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);

        // 对齐方式
        style.setAlignment(align);


        cellStyleMap.put(cellStyleMap.lastKey(), style);
        return this;
    }

    /**
     * @param list [{text:'', value:''}]
     */
    public ExcelUtils2007 setDropDownBox(JSONArray list, int firstRow, int lastRow, int firstCol, int lastCol) {
        setDropDownBox(this.workbook, this.sheet, list, firstRow, lastRow, firstCol, lastCol);
        return this;
    }

    public static void setDropDownBox(XSSFWorkbook workbook, XSSFSheet sheet, JSONArray list, int firstRow, int lastRow, int firstCol, int lastCol) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

        // 创建一个隐藏域
        int num = workbook.getNumberOfSheets();
        String hiddenSheetStr = "hiddenSheet" + num;
        XSSFSheet sheet_hidden = workbook.createSheet(hiddenSheetStr);
        workbook.setSheetHidden(num, true);
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet_hidden.createRow(i);
            String text = list.optJSONObject(i).optString("text");
            String value = list.optJSONObject(i).optString("value");
            row.createCell(0).setCellValue(text);
            row.createCell(1).setCellValue(value);
        }

        // 然后用公式去引用
        String formula = hiddenSheetStr + "!$A$1:$A$" + list.size();

        // 作用区域
        CellRangeAddressList region = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);

        XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formula);
        XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(constraint, region);
        validation.setShowErrorBox(true);

        sheet.addValidationData(validation);
    }

    private void format() {
        for (int i = 0; i < cellStyleMap.size(); i++) {
            CellRangeAddress address = (CellRangeAddress) cellStyleMap.get(i);
            XSSFCellStyle style = (XSSFCellStyle) cellStyleMap.getValue(i);

            int firstRow = address.getFirstRow();
            int lastRow = address.getLastRow();
            int firstColumn = address.getFirstColumn();
            int lastColumn = address.getLastColumn();

            for (int x = firstRow; x <= lastRow; x++) {
                Row row = this.sheet.getRow(x);
                for (int y = firstColumn; y <= lastColumn; y++)
                    row.getCell(y).setCellStyle(style);
            }
        }
    }

    private void merge() {
        for (CellRangeAddress address : mergeRegionList)
            this.sheet.addMergedRegion(address);
    }

    public void build() {
        merge();
        format();
    }

    public XSSFWorkbook getWorkbook() {
        return this.workbook;
    }
    /*************************************** main end *********************************************************/

    /*************************************** enum start *********************************************************/
    private enum StyleEnum {
        STYLE_TITLE("黑体", (short) (16*20), HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.ALIGN_CENTER),
        STYLE_FIELD("宋体", (short) (12*20), HSSFColor.WHITE.index, HSSFCellStyle.ALIGN_CENTER),
        STYLE_CONTENT("宋体", (short) (12*20), HSSFColor.WHITE.index, HSSFCellStyle.ALIGN_CENTER),
        REMARK_CONTENT("宋体", (short) (12*20), HSSFColor.LIGHT_YELLOW.index, HSSFCellStyle.ALIGN_CENTER);

        private String fontName;
        private short fontSize;
        private short fillColor;
        private short align;

        StyleEnum(String fontName, short fontSize, short fillColor, short align) {
            this.fontName = fontName;
            this.fontSize = fontSize;
            this.fillColor = fillColor;
            this.align = align;
        }

        public String getFontName() {
            return fontName;
        }

        public short getFontSize() {
            return fontSize;
        }

        public short getFillColor() {
            return fillColor;
        }

        public short getAlign() {
            return align;
        }
    }
    /*************************************** enum end *********************************************************/

}

© 著作权归作者所有

上一篇: Git 笔记
Geeyu
粉丝 3
博文 57
码字总数 30351
作品 0
昌平
私信 提问
加载中
请先登录后再评论。
Java中excel与对象的互相转换的通用工具类编写与使用(基于apache-poi-ooxml)

通用excel与对象相互转换的工具类 前言:最近开发需要一个Excel批量导入或者导出的功能,之前用过poi-ooxml开发过一个导入的工具类,正好蹭着这次机会,把工具类的功能进行完善。 使用说明:...

宇的季节
2018/02/24
0
0
ApachePOI,通过java代码生成Excel报表

ApachePOI 介绍 需求说明 在企业级应用开发中,Excel报表是一种最常见的报表需求。Excel报表开发一般分为两种形式: 1、为了方便操作,基于Excel的报表批量上传数据 2、通过java代码生成Exc...

osc_d8j4rcut
2019/09/18
32
0
Excel导入导出工具——POI XSSF的使用

工具简介 POI是Apache提供的一款用于处理Microsoft Office的插件,它可以读写Excel、Word、PowerPoint、Visio等格式的文件。 其中XSSF是poi对Excel2007(.xlsx)文件操作的Java实现,例如,我们...

osc_8k1i36qg
2019/05/21
14
0
Java POI 导出EXCEL经典实现 Java导出Excel弹出下载框

在web开发中,有一个经典的功能,就是数据的导入导出。特别是数据的导出,在生产管理或者财务系统中用的非常普遍,因为这些系统经常要做一些报表打印的工作。而数据导出的格式一般是EXCEL或者...

文文1
2015/12/02
1.7K
0
springboot-poi ---封装注解式导入导出

此demo 是基于poi封装对象式注解导入导出,项目框架为springboot项目! 简单的说明一下此demo涉及到的知识点,希望能给初学者带来方便! poi-excel 基本操作(工具) 自定义注解的使用 全局异...

osc_tn1q1y1r
2019/09/26
13
0

没有更多内容

加载失败,请刷新页面

加载更多

自媒体文章原创度检测,可以试一下蚁小二工具

自媒体文章原创度检测,可以试一下蚁小二工具,现在的自媒体人对文章原创度检测这一块还是挺看重的,因为文章的原创度不仅仅和推荐量有关还和质量分值有关,这个时候就可以用到蚁小二工具。 ...

一键分发工具-蚁小二
19分钟前
15
0
Cannot import Sklearn from sklearn.externals.joblib

使用: from sklearn.externals import joblib 报错:Cannot import Sklearn from sklearn.externals.joblib 解决方法: python -m pip install sklearn --upgrade python -m pip install jobli......

hc321
19分钟前
18
0
编程语言那么多,为何建议学习python呢?

  随着互联网技术的发展,大家对于编程的认识越来越系统化了,现在学习编程的年龄越来越小,而编程也成为了我们学习的重要知识。   当然,学习编程知识不分早晚的,也有很多26岁左右才转...

osc_ix000whh
20分钟前
7
0
kubectl的使用

https://kubernetes.io/docs/reference/kubectl/overview/

osc_n1fa9m3z
21分钟前
20
0
2020上海锁博会

上海锁博会将于2020年12月22日至24日在上海新国际博览中心举办“第六届上海国际锁博会”,上海锁博会本来就致力于展现中国作为亚洲最活跃、最具发展潜力的锁具市场之一的独特魅力。 锁博会坚...

osc_uvparld2
23分钟前
14
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部