POI操作excel基础用法详解

最新推荐文章于 2024-06-21 14:13:56 发布

Munger6

最新推荐文章于 2024-06-21 14:13:56 发布

阅读量7.1k

点赞数 11

分类专栏：工具类文章标签： poi excel

原文链接：https://blog.csdn.net/u014644574/article/details/105695787

版权

工具类专栏收录该内容

14 篇文章 1 订阅

订阅专栏

官方文档地址：

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/

http://poi.apache.org/apidocs/org/apache/poi/ss/util/CellRangeAddress.html

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Row.html#createCell(int)

1.POI结构与常用类

(1)POI介绍

Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 .NET的开发人员则可以利用NPOI (POI for .NET) 来存取 Microsoft Office文档的功能。

(2)POI结构说明

包名称说明

HSSF 提供读写Microsoft Excel XLS格式档案的功能。

XSSF 提供读写Microsoft Excel OOXML XLSX格式档案的功能。

HWPF 提供读写Microsoft Word DOC格式档案的功能。

HSLF 提供读写Microsoft PowerPoint格式档案的功能。

HDGF 提供读Microsoft Visio格式档案的功能。

HPBF 提供读Microsoft Publisher格式档案的功能。

HSMF 提供读Microsoft Outlook格式档案的功能。

(3)POI常用类说明

HSSFWorkbook Excel的文档对象

HSSFSheet   Excel的表单
HSSFRow           Excel的行
HSSFCell   Excel的格子单元
HSSFFont           Excel字体

HSSFDataFormat 格子单元的日期格式

HSSFHeader Excel文档Sheet的页眉

HSSFFooter Excel文档Sheet的页脚

HSSFCellStyle 格子单元样式

HSSFDateUtil 日期

HSSFPrintSetup 打印

HSSFErrorConstants 错误信息表

1、创建Sheet

public static void main(String[] args) throws IOException{
	String filePath="d:\\users\\lizw\\桌面\\POI\\sample.xls";//文件路径
	HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件(Workbook)
	HSSFSheet sheet = workbook.createSheet();//创建工作表(Sheet)
	sheet = workbook.createSheet("Test");//创建工作表(Sheet)
	FileOutputStream out = new FileOutputStream(filePath);
	workbook.write(out);//保存Excel文件
	out.close();//关闭文件流
	System.out.println("OK!");
}

2、创建单元格

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(0);// 创建行,从0开始
HSSFCell cell = row.createCell(0);// 创建行的单元格,也是从0开始
cell.setCellValue("李志伟");// 设置单元格内容
row.createCell(1).setCellValue(false);// 设置单元格内容,重载
row.createCell(2).setCellValue(new Date());// 设置单元格内容,重载
row.createCell(3).setCellValue(12.345);// 设置单元格内容,重载

技术分享

3、创建文档摘要信息

workbook.createInformationProperties();//创建文档信息
DocumentSummaryInformation dsi= workbook.getDocumentSummaryInformation();//摘要信息
dsi.setCategory("类别:Excel文件");//类别
dsi.setManager("管理者:李志伟");//管理者
dsi.setCompany("公司:--");//公司
SummaryInformation si = workbook.getSummaryInformation();//摘要信息
si.setSubject("主题:--");//主题
si.setTitle("标题:测试文档");//标题
si.setAuthor("作者:李志伟");//作者
si.setComments("备注:POI测试文档");//备注

技术分享

4、创建批注

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFPatriarch patr = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = patr.createAnchor(0, 0, 0, 0, 5, 1, 8, 3);//创建批注位置：第1个单元格中x轴的偏移量、第1个单元格中y轴的偏移量、 第2个单元格中x轴的偏移量、 第2个单元格中y轴的偏移量、第1个单元格的列号、第1个单元格的行号、 第2个单元格的列号、第2个单元格的行号
HSSFComment comment = patr.createCellComment(anchor);//创建批注
comment.setString(new HSSFRichTextString("这是一个批注段落！"));//设置批注内容
comment.setAuthor("李志伟");//设置批注作者
comment.setVisible(true);//设置批注默认显示
HSSFCell cell = sheet.createRow(2).createCell(1);
cell.setCellValue("测试");
cell.setCellComment(comment);//把批注赋值给单元格

技术分享

5、创建页眉和页脚

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFHeader header =sheet.getHeader();//得到页眉
header.setLeft("页眉左边");
header.setRight("页眉右边");
header.setCenter("页眉中间");
HSSFFooter footer =sheet.getFooter();//得到页脚
footer.setLeft("页脚左边");
footer.setRight("页脚右边");
footer.setCenter("页脚中间");


//或者使用Office自带标签定义
HSSFHeader.tab                  &A	表名
HSSFHeader.file                 &F	文件名
HSSFHeader.startBold            &B	粗体开始
HSSFHeader.endBold              &B	粗体结束
HSSFHeader.startUnderline       &U	下划线开始
HSSFHeader.endUnderline         &U	下划线结束
HSSFHeader.startDoubleUnderline &E	双下划线开始
HSSFHeader.endDoubleUnderline   &E	双下划线结束
HSSFHeader.time                 &T	时间
HSSFHeader.date                 &D	日期
HSSFHeader.numPages             &N	总页面数
HSSFHeader.page                 &P	当前页号

技术分享

3、单元格设置

①设置单元格

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





//介紹
HSSFDataFormat.getFormat和HSSFDataFormat.getBuiltinFormat的区别：当使用Excel内嵌的（或者
说预定义）的格式时，直接用HSSFDataFormat.getBuiltinFormat静态方法即可。当使用自己定义的
格式时，必须先调用HSSFWorkbook.createDataFormat()，因为这时在底层会先找有没有匹配的内嵌
FormatRecord，如果没有就会新建一个FormatRecord，所以必须先调用这个方法，然后你就可以用获
得的HSSFDataFormat实例的getFormat方法了，当然相对而言这种方式比较麻烦，所以内嵌格式还是
用HSSFDataFormat.getBuiltinFormat静态方法更加直接一些。

技术分享

②合并单元格

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row=sheet.createRow(0);
//合并列
HSSFCell cell=row.createCell(0);
cell.setCellValue("合并列");
CellRangeAddress region=new CellRangeAddress(0, 0, 0, 5); //第一个单元格行号， 最后一个单元格行号，   第一个单元格列号、  最后一个单元格列号
sheet.addMergedRegion(region);
//合并行
cell=row.createCell(6);
cell.setCellValue("合并行");
region=new CellRangeAddress(0, 5, 6, 6);
sheet.addMergedRegion(region);

技术分享

③单元格对齐

HSSFCell cell=row.createCell(0);
cell.setCellValue("单元格对齐");
HSSFCellStyle style=workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
如果是左侧对齐就是   HSSFCellStyle.ALIGN_FILL;
如果是居中对齐就是   HSSFCellStyle.ALIGN_CENTER;
如果是右侧对齐就是   HSSFCellStyle.ALIGN_RIGHT;
如果是跨列举中就是   HSSFCellStyle.ALIGN_CENTER_SELECTION;
如果是两端对齐就是   HSSFCellStyle.ALIGN_JUSTIFY;
如果是填充就是       HSSFCellStyle.ALIGN_FILL;    
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
如果是靠上就是     HSSFCellStyle.VERTICAL_TOP;
如果是居中就是     HSSFCellStyle.VERTICAL_CENTER;
如果是靠下就是     HSSFCellStyle.VERTICAL_BOTTOM;
如果是两端对齐就是 HSSFCellStyle.VERTICAL_JUSTIFY;
style.setWrapText(true);//自动换行
style.setIndention((short)5);//缩进
style.setRotation((short)60);//文本旋转，这里的取值是从-90到90，而不是0-180度。
cell.setCellStyle(style);

技术分享

④使用边框

HSSFCell cell=row.createCell(1);
cell.setCellValue("设置边框");
HSSFCellStyle style=workbook.createCellStyle();
//边框类型：  Border + 方向
style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);//上边框
style.setBorderBottom(HSSFCellStyle.BORDER_THICK);//下边框
style.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左边框
style.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右边框

//边框颜色：  方向 + BorderColor
style.setTopBorderColor(HSSFColor.RED.index);//上边框颜色
style.setBottomBorderColor(HSSFColor.BLUE.index);//下边框颜色
style.setLeftBorderColor(HSSFColor.GREEN.index);//左边框颜色
style.setRightBorderColor(HSSFColor.PINK.index);//右边框颜色
cell.setCellStyle(style);

技术分享

⑤设置字体

HSSFCell cell = row.createCell(1);
cell.setCellValue("设置字体");
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontName("华文行楷");//设置字体名称
font.setFontHeightInPoints((short)28);//设置字号
font.setColor(HSSFColor.RED.index);//设置字体颜色
font.setUnderline(FontFormatting.U_SINGLE);//设置下划线
     单下划线 FontFormatting.U_SINGLE
     双下划线 FontFormatting.U_DOUBLE
     会计用单下划线 FontFormatting.U_SINGLE_ACCOUNTING
     会计用双下划线 FontFormatting.U_DOUBLE_ACCOUNTING
	 无下划线 FontFormatting.U_NONE
font.setTypeOffset(FontFormatting.SS_SUPER);//设置上标下标
     上标 FontFormatting.SS_SUPER
     下标 FontFormatting.SS_SUB
     普通，默认值 FontFormatting.SS_NONE
font.setStrikeout(true);//设置删除线
style.setFont(font);
cell.setCellStyle(style);

技术分享

⑥背景和纹理

HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREEN.index);//设置图案颜色
style.setFillBackgroundColor(HSSFColor.RED.index);//设置图案背景色
style.setFillPattern(HSSFCellStyle.SQUARES);//设置图案样式
cell.setCellStyle(style);

技术分享

⑦设置宽度和高度

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(1);
HSSFCell cell = row.createCell(1);
cell.setCellValue("123456789012345678901234567890");
sheet.setColumnWidth(1, 31 * 256);//设置第一列的宽度是31个字符宽度
row.setHeightInPoints(50);//设置行的高度是50个点

技术分享

⑧判断单元格是否为日期

HSSFCell cell = row.createCell(1);
cell.setCellValue(new Date());//设置日期数据
System.out.println(DateUtil.isCellDateFormatted(cell));//输出：false
HSSFCellStyle style =workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
cell.setCellStyle(style);//设置日期样式
System.out.println(DateUtil.isCellDateFormatted(cell));//输出：true

4、使用excel公式

①基本计算

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellFormula("2+3*4");//设置公式
cell = row.createCell(1);
cell.setCellValue(10);
cell = row.createCell(2);
cell.setCellFormula("A1*B1");//设置公式

技术分享

②SUM函数

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(1);
row.createCell(1).setCellValue(2);
row.createCell(2).setCellValue(3);
row.createCell(3).setCellValue(4);
row.createCell(4).setCellValue(5);
row = sheet.createRow(1);
row.createCell(0).setCellFormula("sum(A1,C1)");//等价于"A1+C1"
row.createCell(1).setCellFormula("sum(B1:D1)");//等价于"B1+C1+D1"

技术分享

③日期函数

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFCellStyle style=workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd"));
HSSFRow row = sheet.createRow(0);
Calendar date=Calendar.getInstance();//日历对象
HSSFCell cell=row.createCell(0);
date.set(2011,2, 7);
cell.setCellValue(date.getTime());
cell.setCellStyle(style);//第一个单元格开始时间设置完成
cell=row.createCell(1);
date.set(2014,4, 25);
cell.setCellValue(date.getTime());
cell.setCellStyle(style);//第一个单元格结束时间设置完成
cell=row.createCell(3);
cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"y\"),\"年\")");
cell=row.createCell(4);
cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"m\"),\"月\")");
cell=row.createCell(5);
cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"d\"),\"日\")");

技术分享

④字符串相关函数

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue("abcdefg");
row.createCell(1).setCellValue("aa bb cc dd ee fF GG");
row.createCell(3).setCellFormula("UPPER(A1)");      //将文本转换为大写形式
row.createCell(4).setCellFormula("PROPER(B1)");     //将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。
//更多参考： http://tonyqus.sinaapp.com/archives/289

技术分享

⑤IF函数

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(12);
row.createCell(1).setCellValue(23);
row.createCell(3).setCellFormula("IF(A1>B1,\"A1大于B1\",\"A1小于等于B1\")");//true显示：A1大于B1

技术分享

⑥ CountIf 和SumIf函数

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(57);
row.createCell(1).setCellValue(89);
row.createCell(2).setCellValue(56);
row.createCell(3).setCellValue(67);
row.createCell(4).setCellValue(60);
row.createCell(5).setCellValue(73);
row.createCell(7).setCellFormula("COUNTIF(A1:F1,\">=60\")");   //A1-F1 >= 60个数为4
row.createCell(8).setCellFormula("SUMIF(A1:F1,\">=60\",A1:F1)");  //A1-F1 >= 60之和为289

技术分享

⑦Lookup函数

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(0);
row.createCell(1).setCellValue(59);
row.createCell(2).setCellValue("不及格");
row = sheet.createRow(1);
row.createCell(0).setCellValue(60);
row.createCell(1).setCellValue(69);
row.createCell(2).setCellValue("及格");
row = sheet.createRow(2);
row.createCell(0).setCellValue(70);
row.createCell(1).setCellValue(79);
row.createCell(2).setCellValue("良好");
row = sheet.createRow(3);
row.createCell(0).setCellValue(80);
row.createCell(1).setCellValue(100);
row.createCell(2).setCellValue("优秀");
row = sheet.createRow(4);
row.createCell(0).setCellValue(75);
row.createCell(1).setCellFormula("LOOKUP(A5,$A$1:$A$4,$C$1:$C$4)");  //查找内容、比较对象区域、查找结果区域，匹配上则返回对应数据结果；  
//75模糊匹配上比75小的最大值70匹配回来
row.createCell(2).setCellFormula("VLOOKUP(A5,$A$1:$C$4,3,true)");    //需要查找的内容、比较对象区域、匹配对应的列序号、是否模糊匹配；    精准匹配找不到会报N/A错误

//  HLOOKUP和 VLOOKUP的区别
//  VLOOKUP: 在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值
//  HLOOKUP: 用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。

技术分享

⑧随机数函数

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellFormula("RAND()");//取0-1之间的随机数
row.createCell(1).setCellFormula("int(RAND()*100)");//取0-100之间的随机整数，小数点后会被舍弃
row.createCell(2).setCellFormula("rand()*10+10");//取10-20之间的随机实数
row.createCell(3).setCellFormula("CHAR(INT(RAND()*26)+97)");//随机小写字母（ASCII转换为字母）
row.createCell(4).setCellFormula("CHAR(INT(RAND()*26)+65)");//随机大写字母
//随机大小写字母
row.createCell(5).setCellFormula("CHAR(INT(RAND()*26)+if(INT(RAND()*2)=0,97,65))");

技术分享

⑨获得公式的返回值

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue(7);//A1
row.createCell(1).setCellValue(8);//B1
HSSFCell cell=row.createCell(2);
cell.setCellFormula("A1*B1+14");
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
cell = e.evaluateInCell(cell);//若Excel文件不是POI创建的，则不必调用此方法
System.out.println("公式计算结果："+cell.getNumericCellValue());

技术分享

5、使用图形

①画线

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
 // 创建一个Patriarch（注意，一个sheet中通常只创建一个Patriarch对象）；
HSSFPatriarch patriarch=sheet.createDrawingPatriarch();   
//创建一个Anchor，以确定图形的位置；起始单元X偏移量、起始单元y偏移量、终止单元x偏移量、终止单元y偏移量；  起始单元格列序号、起始单元格行序号、终止单元格列序号、终止单元格行序号
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)1, 0,(short)4, 4);
// 调用Patriarch创建图形；
HSSFSimpleShape line = patriarch.createSimpleShape(anchor);
//设置图形类型(直线，矩形，圆形等)及样式（颜色，粗细等）。
line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);//设置图形类型
line.setLineStyle(HSSFShape.LINESTYLE_SOLID);//设置图形样式
line.setLineWidth(6350);//在POI中线的宽度12700表示1pt,所以这里是0.5pt粗的线条。

技术分享

②画矩形

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(255,122, 255, 122, (short)1, 0,(short)4, 3);
HSSFSimpleShape rec = patriarch.createSimpleShape(anchor);
rec.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
rec.setLineStyle(HSSFShape.LINESTYLE_DASHGEL);//设置边框样式
rec.setFillColor(255, 0, 0);//设置填充色
rec.setLineWidth(25400);//设置边框宽度
rec.setLineStyleColor(0, 0, 255);//设置边框颜色

技术分享

③画圆形

rec.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);//设置图片类型

技术分享

④插入图片

HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
FileInputStream stream=new FileInputStream("d:\\POI\\Apache.gif");
byte[] bytes=new byte[(int)stream.getChannel().size()];
stream.read(bytes);//读取图片到二进制数组
int pictureIdx = workbook.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)0, 0, (short)5, 5);
HSSFPicture pict = patriarch.createPicture(anchor, pictureIdx);
//pict.resize();//自动调节图片大小,图片位置信息可能丢失

技术分享

6、excel表操作

①设置默认工作表

HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
workbook.createSheet("Test0");// 创建工作表(Sheet)
workbook.createSheet("Test1");// 创建工作表(Sheet)
workbook.createSheet("Test2");// 创建工作表(Sheet)
workbook.createSheet("Test3");// 创建工作表(Sheet)
workbook.setActiveSheet(2);//设置默认工作表

②重命名工作表

HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
workbook.createSheet("Test0");// 创建工作表(Sheet)
workbook.createSheet("Test1");// 创建工作表(Sheet)
workbook.createSheet("Test2");// 创建工作表(Sheet)
workbook.createSheet("Test3");// 创建工作表(Sheet)
workbook.setSheetName(2, "1234");//重命名工作表

③调整显示比例

HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
HSSFSheet sheet1= workbook.createSheet("Test0");// 创建工作表(Sheet)
HSSFSheet sheet2=workbook.createSheet("Test1");// 创建工作表(Sheet)
HSSFSheet sheet3=workbook.createSheet("Test2");// 创建工作表(Sheet)
sheet1.setZoom(1,2);//50%显示比例
sheet2.setZoom(2,1);//200%显示比例
sheet3.setZoom(1,10);//10%显示比例

④显示/隐藏网格线

HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
HSSFSheet sheet1= workbook.createSheet("Test0");// 创建工作表(Sheet)
HSSFSheet sheet2=workbook.createSheet("Test1");// 创建工作表(Sheet)
sheet1.setDisplayGridlines(false);//隐藏Excel网格线,默认值为true
sheet2.setGridsPrinted(true);//打印时显示网格线,默认值为false

技术分享

⑤遍历Sheet

String filePath = "d:\\users\\lizw\\桌面\\POI\\sample.xls";
FileInputStream stream = new FileInputStream(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(stream);//读取现有的Excel
HSSFSheet sheet= workbook.getSheet("Test0");//得到指定名称的Sheet
for (Row row : sheet)
{
    for (Cell cell : row)
    {
        System.out.print(cell + "\t");
    }
    System.out.println();
}

技术分享

7、Excel行列操作

①组合行/列

组合就是能够将行/列进行展开和合拢操作；

HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
sheet.groupRow(1, 3);//组合行
sheet.groupRow(2, 4);//组合行
sheet.groupColumn(2, 7);//组合列
//sheet.ungroupColumn(1, 3);//取消列组合

技术分享

②锁定列（冻结部分列，不能滚动，方便查看）

HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
sheet.createFreezePane(2, 3, 15, 25);//冻结行列（要冻结列数、要冻结行数、右边可见的首列序号从1开始、下边可见的首行序号从1开始）

技术分享

③上下移动行

FileInputStream stream = new FileInputStream(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(stream);
HSSFSheet sheet = workbook.getSheet("Test0");
sheet.shiftRows(2, 4, 2);//把第3行到第4行向下移动两行（需要移动开始行，结束行，移动位置）

④生成下拉式菜单

CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
DVConstraint constraint = DVConstraint.createExplicitListConstraint(new String[] { "C++", "Java", "C#" });
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidate);

技术分享

⑤超链接

HSSFSheet sheet = workbook.createSheet("Test0");
CreationHelper createHelper = workbook.getCreationHelper();
// 关联到网站
Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
link.setAddress("http://poi.apache.org/");
sheet.createRow(0).createCell(0).setHyperlink(link);
// 关联到当前目录的文件
link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
link.setAddress("sample.xls");
sheet.createRow(0).createCell(1).setHyperlink(link);
// e-mail 关联
link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
sheet.createRow(0).createCell(2).setHyperlink(link);
//关联到工作簿中的位置 
link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
link.setAddress("‘Test0‘!C3");//Sheet名为Test0的C3位置
sheet.createRow(0).createCell(3).setHyperlink(link);

技术分享

八、excel生成图表信息

https://blog.csdn.net/u014644574/article/details/105695787

①饼图

    /**
	 * 创建饼图（xlsx格式excel）
	 * @param sheetAt 工作表
	 */
	private void createPie(XSSFSheet sheetAt) {
		// 创建一个画布
		Drawing<?> drawing = sheetAt.createDrawingPatriarch();
		//设置画布在excel工作表的位置
		ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 2, 8, 15);
		// 创建一个chart对象
		Chart chart = drawing.createChart(anchor);
		CTChart ctChart = ((XSSFChart) chart).getCTChart();
		CTPlotArea ctPlotArea = ctChart.getPlotArea();
		// 创建圆环图
		CTPieChart ctPieChart = ctPlotArea.addNewPieChart();
		CTBoolean ctBoolean = ctPieChart.addNewVaryColors();
		// 允许自定义颜色
		ctBoolean.setVal(true);
		// 设置图表标题
		CTTitle title = ctChart.addNewTitle();
		//选择图表标题所在位置，此时titleRange不是单元格内容，而是 --》 '工作表名'!$A$2
		String titleRange = new CellRangeAddress(0, 0, 0, 0).formatAsString(sheetAt.getSheetName(), true);
		title.addNewTx().addNewStrRef().setF(titleRange);
		// 创建序列,并且设置选中区域
		CTPieSer ctPieSer = ctPieChart.addNewSer();
		// 设置横坐标区
		CTAxDataSource cttAxDataSource = ctPieSer.addNewCat();
		CTStrRef ctStrRef = cttAxDataSource.addNewStrRef();
		String axisDataRange = new CellRangeAddress(1, 4, 0, 0).formatAsString(sheetAt.getSheetName(), true);
		ctStrRef.setF(axisDataRange);
		// 数据区域
		CTNumDataSource ctNumDataSource = ctPieSer.addNewVal();
		CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
		// 选第1-6行,第1-3列作为数据区域 //1 2 3
		String numDataRange = new CellRangeAddress(1, 4, 1, 1).formatAsString(sheetAt.getSheetName(),
				true);
		ctNumRef.setF(numDataRange);
		// 设置标签格式
		CTDLbls newDLbls = ctPieSer.addNewDLbls();
//		newDLbls.setShowLegendKey(ctBoolean);
		newDLbls.setShowVal(ctBoolean);
//		newDLbls.setShowCatName(ctBoolean);//显示横坐标（图注）
		newDLbls.setShowPercent(ctBoolean);// 显示百分比
		newDLbls.setShowBubbleSize(ctBoolean);// 显示纵坐标（数量）
		newDLbls.setShowLeaderLines(ctBoolean);// 显示线
		// legend图注
		CTLegend ctLegend = ctChart.addNewLegend();
		ctLegend.addNewLegendPos().setVal(STLegendPos.B);//将图注放在下面（Bottom)
		ctLegend.addNewOverlay().setVal(false);// 显示图注但不与图表重叠
	}







//设置标题颜色，字体大小
CTTitle ctTitle = ctChart.addNewTitle();
ctTitle.addNewOverlay().setVal(false);// true时与饼图重叠
ctTitle.addNewTx().addNewRich().addNewBodyPr();
CTTextBody rich = ctTitle.getTx().getRich();
rich.addNewLstStyle();
CTRegularTextRun newR = rich.addNewP().addNewR();
newR.setT(sheetName);
newR.addNewRPr().setB(false);
XmlBoolean xmlBoolean = XmlBoolean.Factory.newInstance();
xmlBoolean.setStringValue("0");
newR.getRPr().xsetB(xmlBoolean);//是否加粗
//newR.getRPr().setLang("zh-CN");
//newR.getRPr().setAltLang("en-US");
newR.getRPr().setSz(1400);//字体大小




//设置饼图每个块颜色
// 创建序列,并且设置选中区域
CTPieSer ctPieSer = ctPieChart.addNewSer();
// 设置区域颜色
for(int i=0;i<pies.size();i++) {
	CTDPt dPt = ctPieSer.addNewDPt();
	STHexBinary3 hex=STHexBinary3.Factory.newInstance();
	if(i==0) {
		hex.setStringValue("5B9BD5");
	}else {
		hex.setStringValue(pies.get(i).getColor());//color为颜色的十六进制去除#
	}
	dPt.addNewIdx().setVal(i);
	dPt.addNewSpPr().addNewSolidFill().addNewSrgbClr().xsetVal(hex);
}



//折线颜色
CTLineSer ctLineSer = ctLineChart.addNewSer();
STHexBinary3 hex = STHexBinary3.Factory.newInstance();
if (i == 0) {//我这里是画了三条折线所以设置了三种颜色
	hex.setStringValue("92D050");
} else if (i == 1) {
	hex.setStringValue("FFFF00");
} else if (i == 2) {
	hex.setStringValue("FF0000");
}
ctLineSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().xsetVal(hex);






//纵坐标
// val axis
CTValAx ctValAx = ctPlotArea.addNewValAx();
ctValAx.addNewAxId().setVal(123457); // id of the val axis
ctScaling = ctValAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);// 设置坐标轴从小到大从下往上排列
ctScaling.addNewMin().setVal(minDiameter);// 设置纵坐标最小值
ctScaling.addNewMax().setVal(maxDiameter);// 设置纵坐标最大值
ctValAx.addNewAxPos().setVal(STAxPos.L);
ctValAx.addNewCrossAx().setVal(123456); // id of the cat axis
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
ctValAx.addNewMajorGridlines().addNewSpPr();// 设置网格
//ctValAx.addNewMajorUnit().setVal(0.01);//设置主要刻度线之间距离为0.01
ctValAx.addNewMajorTickMark().setVal(STTickMark.NONE);// 设置主要刻度线类型无；（Major：主要刻度线 Minor：次要刻度线）
//设置纵坐标标题
//ctValAx.addNewTitle().addNewTx().addNewStrRef()
//.setF(new CellRangeAddress(1, 1, 6, 6).formatAsString(sheetAt.getSheetName(), true));
		ctValAx.addNewSpPr().addNewLn().addNewNoFill();

②折线图

<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>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-collections4</artifactId>
    <version>4.4</version>
</dependency>
<dependency>
    <groupId>commons-codec</groupId>
    <artifactId>commons-codec</artifactId>
    <version>1.13</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-compress</artifactId>
    <version>1.19</version>
</dependency>
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>3.1.0</version>
</dependency>

package test;
 
import java.io.FileOutputStream;
import java.io.IOException;
 
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.xddf.usermodel.PresetLineDash;
import org.apache.poi.xddf.usermodel.XDDFLineProperties;
import org.apache.poi.xddf.usermodel.XDDFPresetLineDash;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.MarkerStyle;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFLineChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public class ApachePoiLineChart4 {
 
	public static void main(String[] args) throws IOException {
		XSSFWorkbook wb = new XSSFWorkbook();
		String sheetName = "Sheet1";
		FileOutputStream fileOut = null;
		try {
			XSSFSheet sheet = wb.createSheet(sheetName);
			//第一行，国家名称
			Row row = sheet.createRow(0);
			Cell cell = row.createCell(0);
			cell.setCellValue("俄罗斯");
			cell = row.createCell(1);
			cell.setCellValue("加拿大");
			cell = row.createCell(2);
			cell.setCellValue("美国");
			cell = row.createCell(3);
			cell.setCellValue("中国");
			cell = row.createCell(4);
			cell.setCellValue("巴西");
			cell = row.createCell(5);
			cell.setCellValue("澳大利亚");
			cell = row.createCell(6);
			cell.setCellValue("印度");
			// 第二行，乡村地区
			row = sheet.createRow(1);
			cell = row.createCell(0);
			cell.setCellValue(17098242);
			cell = row.createCell(1);
			cell.setCellValue(9984670);
			cell = row.createCell(2);
			cell.setCellValue(9826675);
			cell = row.createCell(3);
			cell.setCellValue(9596961);
			cell = row.createCell(4);
			cell.setCellValue(8514877);
			cell = row.createCell(5);
			cell.setCellValue(7741220);
			cell = row.createCell(6);
			cell.setCellValue(3287263);
			// 第三行，农村人口
			row = sheet.createRow(2);
			cell = row.createCell(0);
			cell.setCellValue(14590041);
			cell = row.createCell(1);
			cell.setCellValue(35151728);
			cell = row.createCell(2);
			cell.setCellValue(32993302);
			cell = row.createCell(3);
			cell.setCellValue(14362887);
			cell = row.createCell(4);
			cell.setCellValue(21172141);
			cell = row.createCell(5);
			cell.setCellValue(25335727);
			cell = row.createCell(6);
			cell.setCellValue(13724923);
			// 第四行，面积平局
			row = sheet.createRow(3);
			cell = row.createCell(0);
			cell.setCellValue(9435701.143);
			cell = row.createCell(1);
			cell.setCellValue(9435701.143);
			cell = row.createCell(2);
			cell.setCellValue(9435701.143);
			cell = row.createCell(3);
			cell.setCellValue(9435701.143);
			cell = row.createCell(4);
			cell.setCellValue(9435701.143);
			cell = row.createCell(5);
			cell.setCellValue(9435701.143);
			cell = row.createCell(6);
			cell.setCellValue(9435701.143);
			// 第四行，人口平局
			row = sheet.createRow(4);
			cell = row.createCell(0);
			cell.setCellValue(22475821.29);
			cell = row.createCell(1);
			cell.setCellValue(22475821.29);
			cell = row.createCell(2);
			cell.setCellValue(22475821.29);
			cell = row.createCell(3);
			cell.setCellValue(22475821.29);
			cell = row.createCell(4);
			cell.setCellValue(22475821.29);
			cell = row.createCell(5);
			cell.setCellValue(22475821.29);
			cell = row.createCell(6);
			cell.setCellValue(22475821.29);
 
			//创建一个画布
			XSSFDrawing drawing = sheet.createDrawingPatriarch();
			//前四个默认0，[0,5]：从0列5行开始;[7,26]:宽度7个单元格，26向下扩展到26行
			//默认宽度(14-8)*12
			XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 7, 26);
			//创建一个chart对象
			XSSFChart chart = drawing.createChart(anchor);
			//标题
			chart.setTitleText("地区排名前七的国家");
			//标题覆盖
			chart.setTitleOverlay(false);
			
			//图例位置
			XDDFChartLegend legend = chart.getOrAddLegend();
			legend.setPosition(LegendPosition.TOP);
			
			//分类轴标(X轴),标题位置
			XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
			bottomAxis.setTitle("国家");
			//值(Y轴)轴,标题位置
			XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
			leftAxis.setTitle("面积和人口");
 
			//CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
			//分类轴标(X轴)数据，单元格范围位置[0, 0]到[0, 6]
			XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));
//			XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(new String[] {"俄罗斯","加拿大","美国","中国","巴西","澳大利亚","印度"});
			//数据1，单元格范围位置[1, 0]到[1, 6]
			XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
//			XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(new Integer[] {17098242,9984670,9826675,9596961,8514877,7741220,3287263});
 
			//数据1，单元格范围位置[2, 0]到[2, 6]
			XDDFNumericalDataSource<Double> population = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, 6));
 
			//LINE：折线图，
			XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
 
			//图表加载数据，折线1
			XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(countries, area);
			//折线图例标题
			series1.setTitle("面积", null);
			//直线
			series1.setSmooth(false);
			//设置标记大小
			series1.setMarkerSize((short) 6);
			//设置标记样式，星星
			series1.setMarkerStyle(MarkerStyle.STAR);
 
			//图表加载数据，折线2
			XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(countries, population);
			//折线图例标题
			series2.setTitle("人口", null);
			//曲线
			series2.setSmooth(true);
			//设置标记大小
			series2.setMarkerSize((short) 6);
			//设置标记样式，正方形
			series2.setMarkerStyle(MarkerStyle.SQUARE);
 
			//图表加载数据，平均线3
			//数据1，单元格范围位置[2, 0]到[2, 6]
			XDDFNumericalDataSource<Double> population3 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(3, 3, 0, 6));
			XDDFLineChartData.Series series3 = (XDDFLineChartData.Series) data.addSeries(countries, population3);
			//折线图例标题
			series3.setTitle("面积平均", null);
			//直线
			series3.setSmooth(false);
			//设置标记大小
			//			series3.setMarkerSize((short) 3);
			//设置标记样式，正方形
			series3.setMarkerStyle(MarkerStyle.NONE);
			//折线图LineChart
			//	        XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.CHARTREUSE));
			XDDFLineProperties line = new XDDFLineProperties();
			//	        line.setFillProperties(fill);
			//	        line.setLineCap(LineCap.ROUND);
			line.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));//虚线
			//	        XDDFShapeProperties shapeProperties = new XDDFShapeProperties();
			//	        shapeProperties.setLineProperties(line);
			//	        series3.setShapeProperties(shapeProperties);	        
			series3.setLineProperties(line);
 
			//图表加载数据，平均线3
			//数据1，单元格范围位置[2, 0]到[2, 6]
			XDDFNumericalDataSource<Double> population4 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(4, 4, 0, 6));
			XDDFLineChartData.Series series4 = (XDDFLineChartData.Series) data.addSeries(countries, population4);
			//折线图例标题
			series4.setTitle("人口平均", null);
			//直线
			series4.setSmooth(false);
			//设置标记大小
			//			series4.setMarkerSize((short) 3);
			//设置标记样式，正方形
			series4.setMarkerStyle(MarkerStyle.NONE);
			XDDFLineProperties line4 = new XDDFLineProperties();
			line4.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));//虚线
			series4.setLineProperties(line);
 
			//绘制
			chart.plot(data);
 
			// 将输出写入excel文件
			String filename = "排行榜前七的国家.xlsx";
			fileOut = new FileOutputStream(filename);
			wb.write(fileOut);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			wb.close();
			if (fileOut != null) {
				fileOut.close();
			}
		}
 
	}
 
}

Munger6

关注

11
点赞
踩
61

收藏

觉得还不错? 一键收藏
1
评论
POI操作excel基础用法详解

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/http://poi.apache.org/apidocs/org/apache/poi/ss/util/CellRangeAddress.htmlhttp://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.htmlhttp://poi.apache.org/apidocs/org/apache/poi
复制链接

扫一扫

专栏目录