侧边栏壁纸
博主头像
黑色閃光w博主等级

行动起来，活在当下

  • 累计撰写 28 篇文章
  • 累计创建 14 个标签
  • 累计收到 152 条评论

目 录CONTENT

文章目录
Java

使用POI库来操作Excel文件

黑色閃光w
黑色閃光w
2024-04-11 / 1 评论 / 1 点赞 / 193 阅读 / 18675 字 / 正在检测是否收录...
温馨提示：
本文最后更新于 2024-04-12，若内容或图片失效，请留言反馈。部分素材来自网络，若不小心影响到您的利益，请联系我们删除。

前言

在工作中总是会列表信息导出至Excel文件的需求,

此时我们就需要使用Java来生成Excel并且构建表的结构然后填充数据.

那么我们该如何做呢?

正文

引入pom依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

首先解释以下这三个依赖都是做什么的?

  • Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

  • poi-ooxml是poi包中用于操作Excel文件的库

  • poi-ooxml-schemas是poi-ooxml包的依赖

所以不难看出这三个依赖缺一不可

代码实现

首先poi提供三种写入Excel文件的类,这三个类各有所长,至于具体使用哪个类来完成, 就取决于我们的需求.

HSSFWorkbook、XSSFWorkbook和SXSSFWorkbook的不同之处

HSSFWorkbook是专门用来对.xls文件进行读写操作的实现类. .xls文件是03版本Excel的规范, 它不支持超过65535行的大型表格.

XSSFWorkbook是专门用来对.xlsx文件进行读写操作的实现类. .xlsx文件是07版本的Excel的规范, 它可以向下兼容.xls格式,相较于.xls格式更加先进安全,也支持超大型表格.

那么SXSSFWorkbook是什么呢?

XSSFWorkbook类在读取和写入的时候会将表格(读取时)或数据(写入时)全部统统读到内存中, 然后再进行操作, 当表格或者数据过大时, 就会有一个致命的风险--堆栈溢出.

此时我们就需要一个新的类来进行大型表格或数据的写入或导出SXSSFWorkbook便横空出世.

SXSSFWorkbook提供了一个类似于Flink的处理大型数据的逻辑--窗口.

它会将数据切片, 一点一点的进行处理, 每处理一片数据就会生成一个临时文件, 最后在将所有的临时文件合成一个最终的结果文件. 这就用硬盘的存储空间代替了捉襟见肘的内存空间.

创建工作簿

创建一个简单的工作簿,并且新建一个Sheet页,在第一个格子中填写一句话.

public static void main(String[] args){
	//创建工作簿
	Workbook wb = new HSSFWorkbook();
	//创建Sheet
	Sheet sheet = wb.createSheet("TestSheet");
	//创建行
    Row row = sheet.createRow(0);
    //创建单元格
    Cell cell = row.createCell(0);
	//填充数据
    cell.setCellValue("我是一个单元格!");
}

此时一个最最最简单的工作簿就写好了,这时我们创建一个文件输出流然后使用Workbook类的write()方法写入输出流, 就可以拿到我们写好的Excel文件了

使用IO流记得使用try-catch方法包裹, 并且在完成操作之后关闭IO流以释放资源.

public static void main(String[] args){
	Workbook wb;
    FileOutputStream out = null;
	try {
        //读写xls格式
        //创建工作簿
        wb = new HSSFWorkbook();
        //创建Sheet
        Sheet sheet = wb.createSheet("TestSheet");
		//创建行
    	Row row = sheet.createRow(0);
    	//创建单元格
    	Cell cell = row.createCell(0);
		//填充数据
    	cell.setCellValue("我是一个单元格!");
		out = new FileOutputStream("C:\\excelText\\firstExcel.xls")
		wb.write(out);
	} catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (out != null) {
			//关闭文件输出流
            out.close();
        }
    }
}

此时打开生成好的Excel文件, 我们发现字的太长超出单元格的大小, 溢出格子了.

这时我们需要设置单元格大小以适应内容长度

设置单元格宽度

先写一个计算字符串长度的方法, 为了更好的适配中文字符, 我们使用正则表达式

    /**
     * 计算中文字符的数量
     * @param str 需要计算的字符串
     * @return 中文字符的数量
     */
    public static int calculateChineseNumber(String str) {
        int count = 0;
        //正则表达式，匹配中文字符
        String regEx = "[\\u4e00-\\u9fa5]";
        //编译正则表达式
        Pattern pattern = Pattern.compile(regEx);
        //匹配字符串
        Matcher matcher = pattern.matcher(str);
        //循环匹配
        while (matcher.find()) {
            //循环匹配到的字符
            for (int i = 0; i <= matcher.groupCount(); i++) {
                //数量加1
                count = count + 1;
            }
        }
        //返回数量
        return count;
    }
			//获取字符串长度
            int stringLength = calculateChineseNumber(text);
            //计算像素长度
            int strLen = (text.length() - stringLength) * 256 + (stringLength + 1) * 512;
            //设置单元格宽度
            sheet.setColumnWidth(0, strLen);

这里发现我们多进行了一部分换算,这时为什么呢?

具体来说，(text.length() - stringLength) * 256 表示非中文字符的像素长度，(stringLength + 1) * 512 表示中文字符的像素长度。+1 是因为在计算中文字符的像素长度时，需要加上一个额外的字节，以便在Excel中正确显示中文字符。

然后使用setColumnWidth( )方法应用到对应的单元格中, 两个参数, 第一个为index(int), Sheet页中的第几列,从0开始. 第二个为多宽Width(int)

设置好宽度之后, 老板又对单元格中内容的样式提出了需求...

设置字体样式

在创建工作表时, 我们可以创建多个样式类型, 里面可以定义包括但不限于字体颜色,字体,字体大小,加粗,斜体等

//创建普通单元格样式
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();
//设置字体颜色
font.setColor(Font.COLOR_RED);
//是否加粗
font.setBold(true);
//设置字体大小
font.setFontHeightInPoints((short) 24);
//设置斜体
font.setItalic(true);
//设置字体
font.setFontName("华文彩云");
//设置删除线
font.setStrikeout(true);
//装载
cellStyle.setFont(font);

...

Font类中有很多的方法, 篇幅优先这里就不一一列举了, 需要时可以直接查看源码, 方法名顾名思义即可..

此时, 老板又说, 我们要把内容改成单元格, 点一下就可以跳转老板娘的QQ空间公司官网!

设置单元格超链接

//创建超链接对象
CreationHelper ch = wb.getCreationHelper();
//声明超链接类型
Hyperlink hyperlink = ch.createHyperlink(HyperlinkType.URL);
//设置链接目标
hyperlink.setAddress("http://www.yangnanblog.top");
//创建一个超链接单元格,并装载超链接
Cell linkCell = row.createCell(1);
linkCell.setCellValue("我是超链接,点我!");
linkCell.setHyperlink(hyperlink);

使用Hyperlink类来声明超链接, 接收的参数是HyperlinkType的枚举类, 在这个枚举类中拥有下面的集中类型:

类型

作用

备注

URL

网络链接, 允许链接可访问的互联网地址

必须是http(s)协议的绝对路径

DOCUMENT

文档, 允许在当前工作簿中的任何单元格

包括其他Sheet的单元格

链接格式需要遵守Excel的规范编写

例如: #Sheet!A1

EMAIL

Email地址

链接格式例如:mailto:1234@gmail.com

FILE

文件路径

这个没有特别的格式要求, 只要求绝对路径并且路径可到达

老板, 完成了....老板说不行我还需要冻结首行...行吧...

冻结单元格

如果想要冻结单元格, 我们可以使用这个方法createFreezePane( ), 这个方法是Sheet类下的.

它接收两个参数,一个是从第几行开始, 另一个冻结几行

//冻结
sheet.createFreezePane(0, 10);

以上的示例代表着 首行冻结, 冻结十行, 这样子...

冻结首行之后, 我们想到那如果需要合并单元格之后在冻结呢?

合并单元格

我们现在有了合并单元格的需求

//创建一个需要合并单元格范围的对象
CellRangeAddress region = new CellRangeAddress(1, 1, 1, 4);

构造方法接收四个参数

参数

含义

firstRow

从哪一行开始

lastRow

从哪一行结束

firstCol

从哪一列开始

lastCol

从哪一列结束

纵纵横横就圈出了需要合并的单元格范围, 然而这只是范围, 还没有装载

使用Sheet类下的addMergedRegion()方法来装载合并范围对象

//装载合并范围
sheet.addMergedRegion(region);

此时就完成了单元格合并

效果如下:

截图.PNG创建文档摘要

wb.createInformationProperties();
DocumentSummaryInformation dsi = wb.getDocumentSummaryInformation();
dsi.setCategory("Excel文件");//类别
dsi.setManager("黑色閃光w");//管理者
dsi.setCompany("魏鹏文化有限公司");//公司
SummaryInformation si = wb.getSummaryInformation();
si.setAuthor("黑色閃光w");//作者
si.setComments("无");//备注
si.setTitle("POIDemo");//标题
si.setSubject("POI操作Excel");//主题
si.setKeywords("Java2Excel");//标记
si.setCreateDateTime(new Date());//创建时间

效果就像下面这样, 信息不是存在表格中, 而是存在文件属性的详细信息里.

创建批注

Drawing<?> patr = sheet.createDrawingPatriarch();
ClientAnchor anchor = patr.createAnchor(0, 0, 0, 0, 7, 1, 9, 3);//创建批注位置
Comment comment = patr.createCellComment(anchor);//创建批注
comment.setString(new HSSFRichTextString("这是一个批注段落！"));//设置批注内容
comment.setAuthor("黑色閃光");//设置批注作者
comment.setVisible(true);//设置批注默认显示
Cell cell3 = sheet.createRow(3).createCell(1);
cell3.setCellValue("被批注的单元格");
cell3.setCellComment(comment);//把批注赋值给单元格

创建注释的主要方法createAnchor() 一共有八个参数

参数

含义

dx1

第1个单元格中x轴的偏移量

dy1

第1个单元格中y轴的偏移量

dx2

第2个单元格中x轴的偏移量

dy2

第2个单元格中y轴的偏移量

col1

第1个单元格的列号

row1

第1个单元格的行号

col1

第2个单元格的列号

col2

第2个单元格的行号

创建页眉和页脚

//创建页眉和页脚
Header header = sheet.getHeader();//得到页眉
header.setLeft("页眉左边");
header.setRight("页眉右边");
header.setCenter("页眉中间");
Footer footer = sheet.getFooter();//得到页脚
footer.setLeft("页脚左边");
footer.setRight("页脚右边");
footer.setCenter("页脚中间");

创建好页眉和页脚为啥我的表格中看不见捏? 我也不知道,但是它确实存在...

设置单元格格式

//设置单元格格式
Row row1 = sheet.createRow(2);
//设置日期格式--使用Excel内嵌的格式
Cell cell1 = row1.createCell(0);
cell1.setCellValue(new Date());
HSSFCellStyle style = wb.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m-d-yy h:mm"));
cell1.setCellStyle(style);
//设置保留2位小数--使用Excel内嵌的格式
cell1 = row1.createCell(1);
cell1.setCellValue(12.3456789);
style = wb.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
cell1.setCellStyle(style);
//设置货币格式--使用自定义的格式
cell1 = row1.createCell(2);
cell1.setCellValue(12345.6789);
style = wb.createCellStyle();
style.setDataFormat(wb.createDataFormat().getFormat("￥#,##0"));
cell1.setCellStyle(style);
//设置百分比格式--使用自定义的格式
cell1 = row1.createCell(3);
cell1.setCellValue(0.123456789);
style = wb.createCellStyle();
style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));
cell1.setCellStyle(style);
//设置中文大写格式--使用自定义的格式
cell1 = row1.createCell(4);
cell1.setCellValue(12345);
style = wb.createCellStyle();
style.setDataFormat(wb.createDataFormat().getFormat("[DbNum2][$-804]0"));
cell1.setCellStyle(style);
//设置科学计数法格式--使用自定义的格式
cell1 = row1.createCell(5);
cell1.setCellValue(12345);
style = wb.createCellStyle();
style.setDataFormat(wb.createDataFormat().getFormat("0.00E+00"));
cell1.setCellStyle(style);

这一部分呢, 没什么好说的, 就是单纯的设置单元格格式而已..

设置单元格内容的对其方式

//设置单元格对其方式
Row row3 = sheet.createRow(5);
row3.setHeight((short) (6 * 256));
Cell cell4 = row3.createCell(0);
cell4.setCellValue("单元格对齐");
CellStyle style1=wb.createCellStyle();
style1.setAlignment(HorizontalAlignment.CENTER);//水平居中
style1.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
style1.setWrapText(true);//自动换行
style1.setIndention((short)5);//缩进
style1.setRotation((short)60);//文本旋转，这里的取值是从-90到90，而不是0-180度。
cell4.setCellStyle(style1);

效果就像是这样

设置单元格公式

//设置单元格公式
HSSFSheet sheet2 = wb.createSheet("FormulaTest");// 创建工作表(Sheet)
Row row2_1 = sheet2.createRow(0);
row2_1.createCell(0).setCellValue(1);
row2_1.createCell(1).setCellValue(2);
row2_1.createCell(2).setCellValue(3);
row2_1.createCell(3).setCellValue(4);
row2_1.createCell(4).setCellValue(5);
Row row2_2 = sheet2.createRow(1);
row2_2.createCell(0).setCellFormula("sum(A1,C1)");//等价于"A1+C1"
row2_2.createCell(1).setCellFormula("sum(B1:D1)");//等价于"B1+C1+D1"

设置单元格保护

//设置单元格密码
Row row4 = sheet.createRow(8);
Cell cell5 = row4.createCell(0);
cell5.setCellValue("密码保护");
HSSFCellStyle lock = wb.createCellStyle();
lock.setLocked(true);//锁定
sheet.protectSheet("123123");//设置保护密码

可以锁定部分的单元格

但是保护密码是保护整个Sheet页

生成下拉式菜单

//生成下拉式菜单
HSSFSheet sheet3 = wb.createSheet("ExplicitListTest");
HSSFRow row5 = sheet3.createRow(0);
row5.createCell(0).setCellValue("下拉框");
CellRangeAddressList regions = new CellRangeAddressList(1, 65535,0, 0);
DVConstraint constraint =DVConstraint.createExplicitListConstraint(new String[] { "魏鹏","鹏魏", "委鬼月月鸟" });
HSSFDataValidation dataValidate = new HSSFDataValidation(regions,constraint);
sheet3.addValidationData(dataValidate);

下拉式菜单可以有效的解决用户在Excel表中乱填的bug

解决用户在酒吧点蛋炒饭的问题

校验数据有效性

//校验数据有效性
row5.createCell(1).setCellValue("日期列");
CellRangeAddressList regions1 = new CellRangeAddressList(1, 65535,1, 1);//选定一个区域
DVConstraint constraint1 = DVConstraint.createDateConstraint(DVConstraint . OperatorType.BETWEEN , "1993-01-01" ,"2014-12-31" , "yyyy-MM-dd" );
HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions1,constraint1);
dataValidate.createErrorBox("错误", "你必须输入一个时间！");
sheet3.addValidationData(dataValidate1);

看, 在酒吧中点蛋炒饭不要饭的问题也解决了..

SXSSFWorkbook的特殊操作

因为它会将任务切片, 所以会有一些特别的操作值得注意

try {
    //创建工作簿
    wb = new SXSSFWorkbook();
	wb.setCompressTempFiles(false); //是否压缩临时文件，否则写入速度更快，但更占磁盘，但程序最后是会将临时文件删掉的
 	wb.write(out);
} catch (IOException e) {
    e.printStackTrace();
} finally {
    if (wb != null) {
		wb.dispose();// 删除临时文件，很重要，否则磁盘可能会被写满
    }
    if (out != null) {
		out.close();
    }
}

结语

目前一个对Excel表格的基础操作就到此结束了.

什么? 为什么不讲XSSFWorkbook?

实际上在用法和操作上着三个实现类大差不差, 只有SXSSFWorkbook类会有一些对临时文件的配置.

参考

Apache POI 4.1.x JavaDoc --> https://poi.apache.org/apidocs/4.1/

知乎 JAVA-POI https://zhuanlan.zhihu.com/p/357790289

1
Spring Java
  1. qrcode alipay
  2. qrcode weixin
版权归属： 黑色閃光w
本文链接： http://www.yangnanblog.top:8091/archives/shi-yong-poiku-lai-cao-zuo-excelwen-jian
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权 >

评论区