澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:Excel表格核对8神技,如何

2019-11-10 作者:办公软件   |   浏览(117)

问题:如何筛选两个电子表格中重复的数据?

基础篇

核对工作

1、Count函数:
计数,但只能对数字计数
eg:
=COUNT(F:F)

使用首先需要了解他的工作原理

回答:

EXCEL的四大功能

数据核对,作为Excel中的大工程,这让很多人最头痛的工作。无数的职场人士为它而加班,看到它都是一脸的无奈。

2、Countif:
Countif(range,criteria)
在什么地方,数什么东西
eg:
=COUNTIF(E:E,H8)

1.POI结构与常用类

现有两个成绩表,需判断两组数据是否一致。

数据存储 数据处理 数据分析 数据呈现

今天分享兰色老师关于Excel表格核对的八大神技,在这里整理出来,希望能够帮助你或者你身边的人。

3、Countif函数计算数值区间:
带条件的计数,
eg:计算及格的数目
=COUNTIF(A2:F2,">=60"),多了个双引号,

(1)创建Workbook和Sheet

先复制第一个表中的成绩区域,然后选中第二个表中的成绩区域,单击鼠标右键,选择,在-中选择“减”,粘贴结果不为0的单元格即为成绩不一致的科目。

界面认知

大神技一、两列数据找相同/不同

4、银行卡号的计数
eg:
=COUNTIF($A$8:$A$20,A8&"")
$A$8:$A$20:区域范围需要用绝对引用
A8&"
":Excel表格只能识别15位的数字,所以需要在身份证号码后面连接“*”

**(2)创建单元格
**

澳门新萄京官方网站 1

标题,功能区,功能区选项卡,工作簿,行标题,列标题,状态栏,工作表标签

神技1、同行数据对比

处理每行数据是否相同,通过对比来查找有无重复数据。

【实例1】在C、D两列中,查找哪些行数的数据不同。

同行数据对比

核对方法:

5、检查学生是否体检

(1)POI介绍

对文本进行核对可以利用条件格式。

澳门新萄京官方网站 2

选取要核对的两列,按【Ctrl】 键,填充颜色,标记成不同进行区分。

核对后

澳门新萄京官方网站 3

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

选择第二个表中的文本内容,单击,选择-,在“为符合此公式的值设置格式”中输入公式:=F2<>A2,设置填充颜色。

快捷键大全

神技2、相同数值不一定同行(单列无重复值)

工作中,经常碰到数据处在不同行数上,如何处理这种核对。

【实例2】下图,要求找出两列中相同的数值(单列中数据无重复)

核对方法:

学生体检

(2)POI结构说明

澳门新萄京官方网站 4

选项卡快捷键

选中核对的两列,在【条件格式】——【突出显示单元格规则】——【重复值】

核对

结果如下图所示:

核对结果

是否体检的函数:
=COUNTIF(G:G,A2),
结合前面的if函数,=IF(B2=1,"是","否")
=IF(COUNTIF(G:G,A2)=1,"是","否"),

包名称说明

当然,我们还有非常快速的方法,使用快捷键Ctrl 可以直接对文本和数据进行核对。

ALT/F10提示快捷键,其他情况下点击Alt和以下功能按键快速切换功能区(如果出现快捷键冲突需要解决FF09

神技3、相同数值不一定同行(单列有重复)

工作中,每列中可能存在数值重复,这种情况下,只能使用这种方法来处理。

【实例3】下图中,对比的列中有重复值出现,无法使用条件格式做对比处理。

单列有重复值

核对方法:

将其中一列作为另一列的筛选条件区域。

在已体检名单中输入未体检的人名,自动将未体检显示为体检,

HSSF提供读写Microsoft Excel XLS格式档案的功能。(97-03)

选择第一个表中的数据,再选择第二个表格中的数据,按Ctrl ,核对第一表和第二个表中的第一列,再按Ctrl ,核对第一表和第二个表中的第二列,以此类推,每次剩下的单元格就是不一致的单元格。

名称

选中一列数值在【数据】——【高级】——【高级筛选】将条件区域选中另外要核对的列数上。

单列有重复

筛选结果如下图:

结果

6、利用开始下的条件格式新建规则:
1.选中学生名单中的所有成员(不包括表头),
开始——样式栏——条件格式——新建规则——使用公式确定要设置格式的单元格。填写适当的公式,设置满足该公式的单元格格式。
eg:
=COUNTIF(G:G,A2)=0 //计数为0的情况下

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

澳门新萄京官方网站 5

组合键

【注意】:在这里,两列的标题(库存个数)要相同。

7、将重复数据标记为红色背景
银行卡号

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

刚才分享的是核对的方法。

含义

神技4、不同工作表的两列核对

上述所展示都是在同一张工作表中进行核对操作的,在平日的工作中,我们经常遇到不同工作表中进行核对重复值。

【实例4】下图所示,要求核对在两个工作表中的A列数据。

两个工作表

核对方法:

使用函数来完成核对,Countif函数统计表1库存个数在表2中的个数,如果结果为0则不同,为1则表示相同。

澳门新萄京官方网站 6

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

这里罂粟姐姐再分享5种Excel数据除重的方法,大家看看哪种更方便。

文件

公式如下:=COUNTIF([工作簿1]表1!$A$2:$A$11,A2)

核对结果如下:

结果

银行卡号

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

1、删除重复项

F

大神技二、相同项目对比数据

编辑公式:
=countif($E$2:$E$14,E2&"*")>1

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

贴心的Excel自带“删除重复项”功能,缺点就是删除的太干净了,想看看到底哪些是重复的就不知道了。

file

神技1、单条件核对

同一张工作表中,要求核对两个表中的相同项目。

【实例1】如下图所示,核对商品名称和库存个数。

单条件核对

核对方法:

使用函数Vlookup搜索后相减,来完成核对。结果为0则相同,出现其他数值则为不同。

注意:
1.区域要设置为绝对引用,单元格要设置为相对引用
2.身份证号码后面要连接“*”

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

澳门新萄京官方网站 7

开始

公式如下:

8、在数据有效性中使用Countif函数:
问题1 将C列设置为禁止输入重复数据
选中C列,数据——数据工具栏——数据验证/数据有效性——允许“自定义”
公式:=countif(C:C,C1)<2

(3)POI常用类说明

2、条件格式-重复值

H

=VLOOKUP(D2,A:B,2,0)-E2

核对结果

问题2 将D2:I20设置为禁止输入重复数据
在名称框中,输入D2:I20,回车后就选中了相应的区域
公式:=countif($D$2:$I$20,D2)<2 //绝对引用

类名说明

选择数据,开始-条件格式-重复值,重复数据单元格被填充为其他颜色,筛选-按颜色筛选-筛选红色,删除筛选出来的重复数据(如果没有了重复数据会单元格会自动填充为白色),即保留了没有重复的数据。

home

神技2、多条件核对

如果出现两个项目同时进行核对,那上述则完成不了需要换另个函数SUMIFS。

【实例2】如下图,要求同时按产品和品牌对两表的库存个数进行核对。

多条件

核对方法:

使用sumifs函数多条件求和,然后进行核对。为0值则相同,为其他值则不同。

9、多个条件的countifs函数:

HSSFWorkbook           Excel的文档对象

澳门新萄京官方网站 8

设计

公式如下:

澳门新萄京官方网站 9

HSSFSheet                  Excel的表单

3、高级筛选

G

=SUMIFS(C:C,A:A,E2,B:B,F2)-G2

核对结果

Paste_Image.png

HSSFRow                    Excel的行

选择数据,数据-高级筛选-将筛选结果复制到其他位置-复制到条件区域C1-选择不重复的记录,新得到的数据即为不重复的数据。(最大的好处就是原始数据也保留了哦)

Design

大神技三、两个表格对比数据

=COUNTIFS(E:E,J5,D:D,I5)
不同条件间是“且”的关系

HSSFCell                     Excel的格子单元

澳门新萄京官方网站 10

页面布局

神技1、数值类型对比

【实例1】如下图所示的两个表格,产品顺序一致要求快速找出两个表格的数据差异。

数值类型对比

核对方法:

使用选择性粘贴减的方法核对。

选中其中某个表格,在另一个表格中,右键选择【选择性粘贴】——【运算减】,查看为0则无重复,有其他值出现则为重复。

核对结果如下:

结果

HSSFFont                     Excel字体

4、函数COUNTIF

P

神技2、 不限数值类型对比

【实例2】还是借用上图的表格,对比两个表格。

核对方法:

使用条件格式设置,在公式中设置两表不相等。

选中一个表格,在【开始】——【条件格式】——【新建规则】——【使用公式...】——【选中商品名称】——【格式】填充自己想要的颜色,凸显不同之处。

核对结果如下:

这种很直观的看出哪些数值不相同

结果

以上就是这八种神技,希望大家能够灵活运用,多多尝试。

HSSFDataFormat         格子单元的日期格式

在B2单元格输入函数公式=COUNTIF($A$1:A2,A2)。

page

花了三个多小时,实属不易,如果觉得对自己有帮助,就请点个赞吧!!!

HSSFHeader                 Excel文档Sheet的页眉

结果为1的代表第一次出现,结果为2的代表第二次出现,结果为3的代表第三次出现,以此类推。

引用

HSSFFooter                   Excel文档Sheet的页脚

删除结果不是1的数据,保留是1的数据,即完成了“除重”任务。

S

HSSFCellStyle               格子单元样式

澳门新萄京官方网站 11

reference

HSSFDateUtil                 日期

5、数据透视表

邮件

HSSFPrintSetup             打印

选择数据,插入-数据透视表-将电话号码拉入行,行标签数据即为不重复的电话号码。(因为相同的内容合并了)

M(word)

HSSFErrorConstants      错误信息表

澳门新萄京官方网站 12

mail

2.Excel的基本操作

回答:

公式

(1)创建Workbook和Sheet

重复值排查,一直都是Excel中的重头戏。

M

publicclassTest00

这里,我就分享几个重复值排查的技巧,希望能帮到大家!

formula

{


插入

publicstaticvoidmain(String[]args)throwsIOException

不同工作表中的重复数据

这里,先回答下题主的问题,即,排查两个工作表的重复数据!

  • 方法1:使用Countif函数排查重复

Countif函数用法:Countif( 计数区域, 条件 ),返回计数区域中满足条件的数量。

用这个函数,我们就可以判断不同工作表中的重复数量,如下所示:

公式: Countif(Sheet1!a:a, Sheet2!a2)

表示:Sheet2工作表中的a2单元格的内容,在Sheet1工作表中出现的次数。

若为0,表示Sheet1表中没有该内容;为1,表示出现1次;为4,则表示出现4次。

如此便可以筛选出你需要的内容。

澳门新萄京官方网站 13

  • 方法2:高级筛选也可以实现跨表查重

还是如上示例,以Sheet2工作表的区域作为列表区域,Sheet1中的区域作为条件区域,高级筛选后,即可获取两张工作表相同部分的数据。如下所示:

澳门新萄京官方网站 14

N

{

高级筛选,查找差异数据

为了动画演示方便,以下示例列于同一工作表中。

高级筛选是可以跨工作表实现差异对比的。

澳门新萄京官方网站 15

insert

StringfilePath="d:\users\lizw\桌面\POI\sample.xls";//文件路径

<Ctrl> 反斜杠,瞬间完成单表多列差异对比

同一张工作表的多列数据,查找差异或重复的最快方法就是<Ctrl> 反斜杠了。

这里还有一个小技巧,那就是区域不一定非要连续的。

若是不连续区域,则可以按住<Ctrl>然后挨个选取所有参与差异对比的区域。

澳门新萄京官方网站 16

审阅

HSSFWorkbookworkbook=newHSSFWorkbook();//创建Excel文件(Workbook)

定位行/列内容差异数据

以上几个技巧都是在列上逐行进行差异对比,而F5定位条件中,既可以按行内容,还可以按列内容差异分析,这个功能很强大。

澳门新萄京官方网站 17

R

HSSFSheetsheet=workbook.createSheet();//创建工作表(Sheet)

条件格式中使用公式即可实现重复排查

条件格式中,是可以使用公式的。其实,强大的公式,几乎万能的。

以下是逐行对比的公式,当然,我们稍加改动下,也可以使用Countif函数。

澳门新萄京官方网站 18

review

sheet=workbook.createSheet("Test");//创建工作表(Sheet)

多表重复/差异分析的技巧就分享到这,希望能帮到你!

视图

FileOutputStreamout=newFileOutputStream(filePath);

我是Excel技巧精选,别忘了,点赞、点赞、点赞!!!

回答:

如何筛选两张表中的重复值,这个事很多人都经常碰到的问题,今天就给大家分享一下。例如:我有一个班级的花名册,有本年级全部学生的期末成绩,想筛选这个班级全部学生的期末成绩。

将两个工作表放在一个窗口中,如图所示:sheet1是全部学生的,sheet2是某班学生花名。澳门新萄京官方网站 19

澳门新萄京官方网站 20

在sheet1相对应名字同一行的空白出输入=if(countif())

澳门新萄京官方网站 21

然后切换到sheet2,选中全部名字并回车

澳门新萄京官方网站 22

再切换到sheet1,这时这个函数变成了=if(countif(Sheet2!A1:A44))

澳门新萄京官方网站 23

注意:这一步时,要将字母(我们这里是A)以及数字(我们这里是1和44)前全加上符号$,=if(countif(Sheet2!$A$1:$A$44))

最后,将函数补充完=if(countif(Sheet2!$A$1:$A$44,A2),"S","F"),输入完成后,按回车,显示为S的就是这个班的学生,显示为F的就不是。再从这一行拉下填充,全部学生就可筛选完毕。

澳门新萄京官方网站 24

回答:

快速从EXCEL表里两列相似数据中找出异同

n

{!-- PGC_VIDEO:{"thumb_height": 360, "group_id": 6334869691325513985, "user_id": 0, "neardup_id": 3808910833308536857, "thumb_width": 640, "video_size": {"high": {"h": 480, "subjective_score": 1, "w": 854, "file_size": 5885858}, "ultra": {"h": 720, "subjective_score": 1, "w": 1280, "file_size": 9980291}, "normal": {"h": 360, "subjective_score": 1, "w": 640, "file_size": 4246960}}, "sp": "toutiao", "vposter": "", "vid": "d38df798a395482fb2cb87a852aba097", "item_id": 6334873770364240386, "vu": "d38df798a395482fb2cb87a852aba097", "md5": "df198ac7c10cc777023251d9c082a7b3", "duration": 196, "media_id": 50090367555, "thumb_url": "eea000554e939c43d55", "vname": "\u5feb\u901f\u4eceEXCEL\u8868\u91cc\u4e24\u5217\u76f8\u4f3c\u6570\u636e\u4e2d\u627e\u51fa\u5f02\u540c.mp4", "thumb_uri": "eea000554e939c43d55", "hash_id": 3808910833308536857} --}

回答:

选中需操作的单元格,在数据标签页里选择删除重复项,系统会自动选出重复数据删除,可以参考这个操作:

澳门新萄京官方网站 25想了解更多的excel操作可以点击下面这个链接:)

W

workbook.write(out);//保存Excel文件

view

out.close();//关闭文件流

数据

System.out.println("OK!");

A

}

data

}

其他快捷键

(2)创建单元格

取消操作Esc

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

撤销Ctrl Z

HSSFRowrow=sheet.createRow(0);// 创建行,从0开始

恢复或重复Ctrl Y

HSSFCellcell=row.createCell(0);// 创建行的单元格,也是从0开始

区域填充Ctrl Enter

cell.setCellValue("李志伟");// 设置单元格内容

下一项目:tab上一个项目:shift tab

row.createCell(1).setCellValue(false);// 设置单元格内容,重载

工具类

row.createCell(2).setCellValue(newDate());// 设置单元格内容,重载

表格工具

row.createCell(3).setCellValue(12.345);// 设置单元格内容,重载

1.行列次序交换(shift 鼠标星形)

(3)创建文档摘要信息

2.快速到表格底部,顶部

workbook.createInformationProperties();//创建文档信息

3.冻结首行

澳门新萄京官方网站:Excel表格核对8神技,如何筛选两个电子表格中重复的数据。DocumentSummaryInformationdsi=workbook.getDocumentSummaryInformation();//摘要信息

4.填充饼(顺序,复制,特殊序列)

dsi.setCategory("类别:Excel文件");//类别

数据工具

dsi.setManager("管理者:李志伟");//管理者

1.值,格式,单元格匹配查找

dsi.setCompany("公司:--");//公司

2.高级查找和通配符(?*和逃逸符~)

SummaryInformationsi=workbook.getSummaryInformation();//摘要信息

3.定位(名称框定位,自定义名称框,定位条件,定位的批量操作)

si.setSubject("主题:--");//主题

4.批注的显示和隐藏,建立和删除,批注图形的形状更改

si.setTitle("标题:测试文档");//标题

5.排序:数值排序,颜色排序,自定义排序,排序插入

si.setAuthor("作者:李志伟");//作者

6.数据筛选:部门筛选,数值筛选,文本筛选

si.setComments("备注:POI测试文档");//备注

7.高级筛选:筛选不重复项,一行为且关系,二行为或关系,多行条件区域的演示,关于公式的筛选,只能写公式不能写标题。

(4)创建批注

8.分类汇总:分类汇总前先排序

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

9.使用分类汇总合并单元格,分类汇总得到空格,然后定位空格,合并,然后删除分类汇总,然后粘贴格式到你的目标列。

HSSFPatriarchpatr=sheet.createDrawingPatriarch();

10.数据有效性/数据验证,警告和提示

HSSFClientAnchoranchor=patr.createAnchor(0,0,0,0,5,1,8,3);//创建批注位置

11.通过数据有效性保护数据

HSSFCommentcomment=patr.createCellComment(anchor);//创建批注

12.通过数据有效性定义行列的输入法

comment.setString(newHSSFRichTextString("这是一个批注段落!"));//设置批注内容

13.数据透视表

comment.setAuthor("李志伟");//设置批注作者

公式

comment.setVisible(true);//设置批注默认显示

运算函数

HSSFCellcell=sheet.createRow(2).createCell(1);

1.运算函数: -*/^

cell.setCellValue("测试");

2.绝对引用,相对引用,混合引用:做一个九九乘法表

cell.setCellComment(comment);//把批注赋值给单元格

逻辑函数

创建批注位置HSSFPatriarch.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)方法参数说明:

If函数,多次if的使用,iserror函数

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

3.And函数

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

4.OR函数

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

取值函数

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

5.Countif函数:使用条件格式限定区域内唯一值,用条件格式标记区域内重复的为红色

col1第1个单元格的列号

6.Countifs函数(注意:标准引用超过15位就不识别了,如果超过15位则需要x&”*”

row1第1个单元格的行号

7.Sumif函数和sumifs函数

col2第2个单元格的列号

8.Vlookup函数:当关键词只有部分的时候怎么办?“*”&“*”,个税计算公式:=IF(F7>3500,VLOOKUP(F7,$A$6:$D$12,3,1)*F7-VLOOKUP(VLOOKUP(F7,$A$6:$D$12,3,1),$C$6:$D$12,2,0),0)

row2第2个单元格的行号

9.Vlookup模糊匹配只会寻找小于等于自己的最大值。

(5)创建页眉和页脚

10.Match数数函数和index取值函数,column函数

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

日期函数

HSSFHeaderheader=sheet.getHeader();//得到页眉

1.Year,month,day,date

header.setLeft("页眉左边");

2.Datedif函数,公式实例:=DATEDIF(B5,C5,"y"),参数,y,m,d,ym,md,yd抛去前面参数,算后面参数

header.setRight("页眉右边");

3.Weeknum周数参数,weekday周几函数

header.setCenter("页眉中间");

条件格式

HSSFFooterfooter=sheet.getFooter();//得到页脚

1.数据透视表中条件格式和切片器的使用

footer.setLeft("页脚左边");

2.管理条件格式

footer.setRight("页脚右边");

3.条件格式优先级,后来的条件格式覆盖前面的格式

footer.setCenter("页脚中间");

文本函数

也可以使用Office自带的标签定义,你可以通过HSSFHeader或HSSFFooter访问到它们,都是静态属性,列表如下:

1.截取函数:Left函数,right函数,mid函数

HSSFHeader.tab&A表名

2.Find函数:需找一个字符在文本中的第几位

HSSFHeader.file&F文件名

3.Len函数:字符长度显示函数

HSSFHeader.startBold&B粗体开始

4.Lenb函数:字节长度显示函数

HSSFHeader.endBold&B粗体结束

数学函数

HSSFHeader.startUnderline&U下划线开始

1.Round函数:四舍五入函数

HSSFHeader.endUnderline&U下划线结束

2.Roundup函数,进位函数,无论小数点后面有什么数字就进一位。

HSSFHeader.startDoubleUnderline&E双下划线开始

3.Rounddown函数,缩位函数,无论小数点后面是什么,直接社区

HSSFHeader.endDoubleUnderline&E双下划线结束

4.Int函数,直接取整函数

HSSFHeader.time&T时间

5.Mod函数,求余数函数,可以用于奇数,偶数判定。

HSSFHeader.date&D日期

6.Row函数和colum函数

HSSFHeader.numPages&N总页面数

7.Match函数,找单元格值在一列中的第几个

HSSFHeader.page&P当前页号

8.转置数组

3.Excel的单元格操作

统计函数

(1)设置格式

1.数组函数:一组数和另一组数的加减乘除,最后是按:ctrl shift enter在外面加大括号,实例{=SUM((A2:A22=I15)*(B2:B22=J15)*E2:E22)},数组又名矩阵。

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

2.Sumproduct函数,数组加法函数,不会在最外面加大括号。

HSSFRowrow=sheet.createRow(0);

3.Lookup函数,lookup本身就是数组函数,且本身是模糊查找,需要排除错误值留下正确值来确定使用。实例函数:(第一个实例的一可以,是零可以,是其他正数也可以)=LOOKUP(1,0/(($A$2:$A$13=I6)*($B$2:$B$13=J6)),$D$2:$D$13)

//设置日期格式--使用Excel内嵌的格式

4.个税计算max函数版:=IF(F7>3500,MAX((F7-3500)*($C$6:$C$12)-$D$6:$D$12),0)

HSSFCellcell=row.createCell(0);

要点:注意逻辑,条件,语法和最后的数组函数括号。

cell.setCellValue(newDate());

Indirect函数

HSSFCellStylestyle=workbook.createCellStyle();

1.Indirect函数:间接引用单元格的值

style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

2.单元格直接引用函数:“e”&4,列号码加双引号然后连上数字。

cell.setCellStyle(style);

3.表格引用该函数:实例:='1月'!G2

//设置保留2位小数--使用Excel内嵌的格式

4.跨表引用函数实例:=VLOOKUP("张三",INDIRECT(A4&"!a:g"),7,0),实例2:

cell=row.createCell(1);

=VLOOKUP(B$2,INDIRECT($A3&"!a:g"),7,0)

cell.setCellValue(12.3456789);

5.注意事项:所有文本事项需要加双引号标定!

style=workbook.createCellStyle();

6.注意事项2:当直接引用工作表出错的时候,工作表可以用单引号括起来。

style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

Offset

cell.setCellStyle(style);

文本函数

//设置货币格式--使用自定义的格式

by april2summer冰墨洁

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静态方法更加直接一些。

(2)合并单元格

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFRowrow=sheet.createRow(0);

//合并列

HSSFCellcell=row.createCell(0);

cell.setCellValue("合并列");

CellRangeAddressregion=newCellRangeAddress(0,0,0,5);

sheet.addMergedRegion(region);

//合并行

cell=row.createCell(6);

cell.setCellValue("合并行");

region=newCellRangeAddress(0,5,6,6);

sheet.addMergedRegion(region);

CellRangeAddress对象其实就是表示一个区域,其构造方法如下:CellRangeAddress(firstRow, lastRow, firstCol, lastCol),参数的说明:

firstRow区域中第一个单元格的行号

lastRow区域中最后一个单元格的行号

firstCol区域中第一个单元格的列号

lastCol区域中最后一个单元格的列号

提示:即使你没有用CreateRow和CreateCell创建过行或单元格,也完全可以直接创建区域然后把这一区域合并,Excel的区域合并信息是单独存储的,和RowRecord、ColumnInfoRecord不存在直接关系。

(3)单元格对齐

HSSFCellcell=row.createCell(0);

cell.setCellValue("单元格对齐");

HSSFCellStylestyle=workbook.createCellStyle();

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

style.setWrapText(true);//自动换行

style.setIndention((short)5);//缩进

style.setRotation((short)60);//文本旋转,这里的取值是从-90到90,而不是0-180度。

cell.setCellStyle(style);

水平对齐相关参数

如果是左侧对齐就是HSSFCellStyle.ALIGN_FILL;

如果是居中对齐就是HSSFCellStyle.ALIGN_CENTER;

如果是右侧对齐就是HSSFCellStyle.ALIGN_RIGHT;

如果是跨列举中就是HSSFCellStyle.ALIGN_CENTER_SELECTION;

如果是两端对齐就是HSSFCellStyle.ALIGN_JUSTIFY;

如果是填充就是HSSFCellStyle.ALIGN_FILL;

垂直对齐相关参数

如果是靠上就是HSSFCellStyle.VERTICAL_TOP;

如果是居中就是HSSFCellStyle.VERTICAL_CENTER;

如果是靠下就是HSSFCellStyle.VERTICAL_BOTTOM;

如果是两端对齐就是HSSFCellStyle.VERTICAL_JUSTIFY;

(4)使用边框

边框和其他单元格设置一样也是调用CellStyle接口,CellStyle有2种和边框相关的属性,分别是:

边框相关属性

说明

范例

Border  方向

边框类型

BorderLeft, BorderRight 等

方向 BorderColor

边框颜色

TopBorderColor,BottomBorderColor 等

HSSFCellcell=row.createCell(1);

cell.setCellValue("设置边框");

HSSFCellStylestyle=workbook.createCellStyle();

style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);//上边框

style.setBorderBottom(HSSFCellStyle.BORDER_THICK);//下边框

style.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左边框

style.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右边框

style.setTopBorderColor(HSSFColor.RED.index);//上边框颜色

style.setBottomBorderColor(HSSFColor.BLUE.index);//下边框颜色

style.setLeftBorderColor(HSSFColor.GREEN.index);//左边框颜色

style.setRightBorderColor(HSSFColor.PINK.index);//右边框颜色

cell.setCellStyle(style);

其中边框类型分为以下几种:

澳门新萄京官方网站,边框范例图

对应的静态值

澳门新萄京官方网站 26

HSSFCellStyle.BORDER_DOTTED

澳门新萄京官方网站 27

HSSFCellStyle.BORDER_HAIR

澳门新萄京官方网站 28

HSSFCellStyle.BORDER_DASH_DOT_DOT

澳门新萄京官方网站 29

HSSFCellStyle.BORDER_DASH_DOT

澳门新萄京官方网站 30

HSSFCellStyle.BORDER_DASHED

澳门新萄京官方网站 31

HSSFCellStyle.BORDER_THIN

澳门新萄京官方网站 32

HSSFCellStyle.BORDER_MEDIUM_DASH_DOT_DOT

澳门新萄京官方网站 33

HSSFCellStyle.BORDER_SLANTED_DASH_DOT

澳门新萄京官方网站 34

HSSFCellStyle.BORDER_MEDIUM_DASH_DOT

澳门新萄京官方网站 35

HSSFCellStyle.BORDER_MEDIUM_DASHED

澳门新萄京官方网站 36

HSSFCellStyle.BORDER_MEDIUM

澳门新萄京官方网站 37

HSSFCellStyle.BORDER_THICK

澳门新萄京官方网站 38

HSSFCellStyle.BORDER_DOUBLE

(5)设置字体

HSSFCellcell=row.createCell(1);

cell.setCellValue("设置字体");

HSSFCellStylestyle=workbook.createCellStyle();

HSSFFontfont=workbook.createFont();

font.setFontName("华文行楷");//设置字体名称

font.setFontHeightInPoints((short)28);//设置字号

font.setColor(HSSFColor.RED.index);//设置字体颜色

font.setUnderline(FontFormatting.U_SINGLE);//设置下划线

font.setTypeOffset(FontFormatting.SS_SUPER);//设置上标下标

font.setStrikeout(true);//设置删除线

style.setFont(font);

cell.setCellStyle(style);

下划线选项值:

单下划线 FontFormatting.U_SINGLE

双下划线 FontFormatting.U_DOUBLE

会计用单下划线 FontFormatting.U_SINGLE_ACCOUNTING

会计用双下划线 FontFormatting.U_DOUBLE_ACCOUNTING

无下划线 FontFormatting.U_NONE

上标下标选项值:

上标 FontFormatting.SS_SUPER

下标 FontFormatting.SS_SUB

普通,默认值 FontFormatting.SS_NONE

(6)背景和纹理

HSSFCellStylestyle=workbook.createCellStyle();

style.setFillForegroundColor(HSSFColor.GREEN.index);//设置图案颜色

style.setFillBackgroundColor(HSSFColor.RED.index);//设置图案背景色

style.setFillPattern(HSSFCellStyle.SQUARES);//设置图案样式

cell.setCellStyle(style);

图案样式及其对应的值:

图案样式

常量

澳门新萄京官方网站 39

HSSFCellStyle.NO_FILL

澳门新萄京官方网站 40

HSSFCellStyle.ALT_BARS

澳门新萄京官方网站 41

HSSFCellStyle.FINE_DOTS

澳门新萄京官方网站 42

HSSFCellStyle.SPARSE_DOTS

澳门新萄京官方网站 43

HSSFCellStyle.LESS_DOTS

澳门新萄京官方网站 44

HSSFCellStyle.LEAST_DOTS

澳门新萄京官方网站 45

HSSFCellStyle.BRICKS

澳门新萄京官方网站 46

HSSFCellStyle.BIG_SPOTS

澳门新萄京官方网站 47

HSSFCellStyle.THICK_FORWARD_DIAG

澳门新萄京官方网站 48

HSSFCellStyle.THICK_BACKWARD_DIAG

澳门新萄京官方网站 49

HSSFCellStyle.THICK_VERT_BANDS

澳门新萄京官方网站 50

HSSFCellStyle.THICK_HORZ_BANDS

澳门新萄京官方网站 51

HSSFCellStyle.THIN_HORZ_BANDS

澳门新萄京官方网站 52

HSSFCellStyle.THIN_VERT_BANDS

澳门新萄京官方网站 53

HSSFCellStyle.THIN_BACKWARD_DIAG

澳门新萄京官方网站 54

HSSFCellStyle.THIN_FORWARD_DIAG

澳门新萄京官方网站 55

HSSFCellStyle.SQUARES

澳门新萄京官方网站 56

HSSFCellStyle.DIAMONDS

(7)设置宽度和高度

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFRowrow=sheet.createRow(1);

HSSFCellcell=row.createCell(1);

cell.setCellValue("123456789012345678901234567890");

sheet.setColumnWidth(1,31*256);//设置第一列的宽度是31个字符宽度

row.setHeightInPoints(50);//设置行的高度是50个点

这里你会发现一个有趣的现象,setColumnWidth的第二个参数要乘以256,这是怎么回事呢?其实,这个参数的单位是1/256个字符宽度,也就是说,这里是把B列的宽度设置为了31个字符。

设置行高使用HSSFRow对象的setHeight和setHeightInPoints方法,这两个方法的区别在于setHeightInPoints的单位是点,而setHeight的单位是1/20个点,所以setHeight的值永远是setHeightInPoints的20倍。

你也可以使用HSSFSheet.setDefaultColumnWidth、HSSFSheet.setDefaultRowHeight和HSSFSheet.setDefaultRowHeightInPoints方法设置默认的列宽或行高。

(8)判断单元格是否为日期

判断单元格是否为日期类型,使用DateUtil.isCellDateFormatted(cell)方法,例如:

HSSFCellcell=row.createCell(1);

cell.setCellValue(newDate());//设置日期数据

System.out.println(DateUtil.isCellDateFormatted(cell));//输出:false

HSSFCellStylestyle=workbook.createCellStyle();

style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

cell.setCellStyle(style);//设置日期样式

System.out.println(DateUtil.isCellDateFormatted(cell));//输出:true

4.使用Excel公式

(1)基本计算

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFRowrow=sheet.createRow(0);

HSSFCellcell=row.createCell(0);

cell.setCellFormula("2 3*4");//设置公式

cell=row.createCell(1);

cell.setCellValue(10);

cell=row.createCell(2);

cell.setCellFormula("A1*B1");//设置公式

(2)SUM函数

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFRowrow=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"

(3)日期函数

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFCellStylestyle=workbook.createCellStyle();

style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd"));

HSSFRowrow=sheet.createRow(0);

Calendardate=Calendar.getInstance();//日历对象

HSSFCellcell=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"),"日")");

以上代码中的公式说明:

DATEDIF(A1,B1,"y") :取得A1 单元格的日期与B1 单元格的日期的时间间隔。( “y ”: 表示以年为单位, ”m ”表示以月为单位; ”d ”表示以天为单位) 。

CONCATENATE(str1,str2, …) :连接字符串。

更多Excel 的日期函数可参考:http://tonyqus.sinaapp.com/archives/286

(4)字符串相关函数

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFRowrow=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)");

以上代码中的公式说明:

UPPER(String) :将文本转换成大写形式。

PROPER(String) :将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。

更多Excel 的字符串函数可参考:http://tonyqus.sinaapp.com/archives/289

(5)IF函数

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFRowrow=sheet.createRow(0);

row.createCell(0).setCellValue(12);

row.createCell(1).setCellValue(23);

row.createCell(3).setCellFormula("IF(A1>B1,"A1大于B1","A1小于等于B1")");

以上代码中的公式说明:

IF(logical_test,value_if_true,value_if_false)用来用作逻辑判断。其中Logical_test表示计算结果为 TRUE 或 FALSE 的任意值或表达式 ; value_if_true表示当表达式Logical_test的值为TRUE时的返回值;value_if_false表示当表达式Logical_test的值为FALSE时的返回值。

(6)CountIf和SumIf函数

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFRowrow=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")");

row.createCell(8).setCellFormula("SUMIF(A1:F1,">=60",A1:F1)");

以上代码中的公式说明:

COUNTIF(range,criteria):满足某条件的计数的函数。参数range:需要进行读数的计数;参数criteria:条件表达式,只有当满足此条件时才进行计数。

SumIF(criteria_range, criteria,sum_range):用于统计某区域内满足某条件的值的求和。参数criteria_range:条件测试区域,第二个参数Criteria中的条件将与此区域中的值进行比较;参数criteria:条件测试值,满足条件的对应的sum_range项将进行求和计算;参数sum_range:汇总数据所在区域,求和时会排除掉不满足Criteria条件的对应的项。

(7)Lookup函数

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFRowrow=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)");

row.createCell(2).setCellFormula("VLOOKUP(A5,$A$1:$C$4,3,true)");

以上代码中的公式说明:

LOOKUP(lookup_value,lookup_vector,result_vector) ,第一个参数:需要查找的内容,本例中指向A5 单元格,也就是75 ;第二个参数:比较对象区域,本例中的成绩需要与$A$1:$A$4 中的各单元格中的值进行比较;第三个参数:查找结果区域,如果匹配到会将此区域中对应的数据返回。如本例中返回$C$1:$C$4 中对应的值。

可能有人会问,字典中没有75 对应的成绩啊,那么Excel 中怎么匹配的呢?答案是模糊匹配,并且LOOKUP 函数只支持模糊匹配。Excel 会在$A$1:$A$4 中找小于75 的最大值,也就是A3 对应的70 ,然后将对应的$C$1:$C$4 区域中的C3 中的值返回,这就是最终结果“良好”的由来。

VLOOKUP(lookup_value,lookup_area,result_col,is_fuzzy ) ,第一个参数:需要查找的内容,这里是A5 单元格;第二个参数:需要比较的表,这里是$A$1:$C$4 ,注意VLOOKUP 匹配时只与表中的第一列进行匹配。第三个参数:匹配结果对应的列序号。这里要对应的是成绩列,所以为3 。第四个参数:指明是否模糊匹配。例子中的TRUE 表示模糊匹配,与上例中一样。匹配到的是第三行。如果将此参数改为FALSE ,因为在表中的第1 列中找不到75 ,所以会报“#N/A ”的计算错误。

另外,还有与VLOKUP 类似的HLOOKUP 。不同的是VLOOKUP 用于在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。而HLOOKUP 用于在表格或数值数组的首行查找指定的数值,并由此返回表格或数组当前列中指定行处的数值。读者可以自已去尝试。

(8)随机数函数

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFRowrow=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)");//随机小写字母

row.createCell(4).setCellFormula("CHAR(INT(RAND()*26) 65)");//随机大写字母

//随机大小写字母

row.createCell(5).setCellFormula("CHAR(INT(RAND()*26) if(INT(RAND()*2)=0,97,65))");

以上代码中的公式说明:

上面几例中除了用到RAND函数以外,还用到了CHAR函数用来将ASCII码换为字母,INT函数用来取整。值得注意的是INT函数不会四舍五入,无论小数点后是多少都会被舍去。

(9)获得公式的返回值

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFRowrow=sheet.createRow(0);

row.createCell(0).setCellValue(7);//A1

row.createCell(1).setCellValue(8);//B1

HSSFCellcell=row.createCell(2);

cell.setCellFormula("A1*B1 14");

HSSFFormulaEvaluatore=newHSSFFormulaEvaluator(workbook);

cell=e.evaluateInCell(cell);//若Excel文件不是POI创建的,则不必调用此方法

System.out.println("公式计算结果:" cell.getNumericCellValue());

5.使用图形

(1)画线

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFPatriarchpatriarch=sheet.createDrawingPatriarch();

HSSFClientAnchoranchor=newHSSFClientAnchor(0,0,0,0,(short)1,0,(short)4,4);

HSSFSimpleShapeline=patriarch.createSimpleShape(anchor);

line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);//设置图形类型

line.setLineStyle(HSSFShape.LINESTYLE_SOLID);//设置图形样式

line.setLineWidth(6350);//在POI中线的宽度12700表示1pt,所以这里是0.5pt粗的线条。

通常,利用POI画图主要有以下几个步骤:

  1. 创建一个Patriarch(注意,一个sheet中通常只创建一个Patriarch对象);

  2. 创建一个Anchor,以确定图形的位置;

  3. 调用Patriarch创建图形;

  4. 设置图形类型(直线,矩形,圆形等)及样式(颜色,粗细等)。

关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数,有必要在这里说明一下:

dx1:起始单元格的x偏移量,如例子中的0表示直线起始位置距B1单元格左侧的距离;

dy1:起始单元格的y偏移量,如例子中的0表示直线起始位置距B1单元格上侧的距离;

dx2:终止单元格的x偏移量,如例子中的0表示直线起始位置距E5单元格左侧的距离;

dy2:终止单元格的y偏移量,如例子中的0表示直线起始位置距E5单元格上侧的距离;

col1:起始单元格列序号,从0开始计算;

row1:起始单元格行序号,从0开始计算,如例子中col1=1,row1=0就表示起始单元格为B1;

col2:终止单元格列序号,从0开始计算;

row2:终止单元格行序号,从0开始计算,如例子中col2=4,row2=4就表示起始单元格为E5;

最后,关于LineStyle属性,有如下一些可选值,对应的效果分别如图所示:

(2)画矩形

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFPatriarchpatriarch=sheet.createDrawingPatriarch();

HSSFClientAnchoranchor=newHSSFClientAnchor(255,122,255,122,(short)1,0,(short)4,3);

HSSFSimpleShaperec=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);//设置边框颜色

(3)画圆形

更改上例的代码如下:

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

(4)画Grid

在POI中,本身没有画Grid(网格)的方法。但我们知道Grid其实就是由横线和竖线构成的,所在我们可以通过画线的方式来模拟画Grid。代码如下:

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

HSSFRowrow=sheet.createRow(2);

row.createCell(1);

row.setHeightInPoints(240);

sheet.setColumnWidth(2,9000);

intlinesCount=20;

HSSFPatriarchpatriarch=sheet.createDrawingPatriarch();

//因为HSSFClientAnchor中dx只能在0-1023之间,dy只能在0-255之间,这里采用比例的方式

doublexRatio=1023.0/(linesCount*10);

doubleyRatio=255.0/(linesCount*10);

// 画竖线

intx1=0;

inty1=0;

intx2=0;

inty2=200;

for(inti=0;i

{

HSSFClientAnchora2=newHSSFClientAnchor();

a2.setAnchor((short)2,2,(int)(x1*xRatio),

(int)(y1*yRatio),(short)2,2,(int)(x2*xRatio),

(int)(y2*yRatio));

HSSFSimpleShapeshape2=patriarch.createSimpleShape(a2);

shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);

x1 =10;

x2 =10;

}

// 画横线

x1=0;

y1=0;

x2=200;

y2=0;

for(inti=0;i

{

HSSFClientAnchora2=newHSSFClientAnchor();

a2.setAnchor((short)2,2,(int)(x1*xRatio),

(int)(y1*yRatio),(short)2,2,(int)(x2*xRatio),

(int)(y2*yRatio));

HSSFSimpleShapeshape2=patriarch.createSimpleShape(a2);

shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);

y1 =10;

y2 =10;

}

(5)插入图片

HSSFSheetsheet=workbook.createSheet("Test");// 创建工作表(Sheet)

FileInputStreamstream=newFileInputStream("d:\POI\Apache.gif");

byte[]bytes=newbyte[(int)stream.getChannel().size()];

stream.read(bytes);//读取图片到二进制数组

intpictureIdx=workbook.addPicture(bytes,HSSFWorkbook.PICTURE_TYPE_JPEG);

HSSFPatriarchpatriarch=sheet.createDrawingPatriarch();

HSSFClientAnchoranchor=newHSSFClientAnchor(0,0,0,0,(short)0,0,(short)5,5);

HSSFPicturepict=patriarch.createPicture(anchor,pictureIdx);

//pict.resize();//自动调节图片大小,图片位置信息可能丢失

(6)从Excel文件提取图片

InputStreaminp=newFileInputStream(filePath);

HSSFWorkbookworkbook=newHSSFWorkbook(inp);//读取现有的Excel文件

Listpictures=workbook.getAllPictures();

for(inti=0;i

{

HSSFPictureDatapic=pictures.get(i);

Stringext=pic.suggestFileExtension();

if(ext.equals("png"))//判断文件格式

{

FileOutputStreampng=newFileOutputStream("d:\POI\Apache.png");

png.write(pic.getData());

png.close();//保存图片

}

}

6.Excel表操作

(1)设置默认工作表

HSSFWorkbookworkbook=newHSSFWorkbook();// 创建Excel文件(Workbook)

workbook.createSheet("Test0");// 创建工作表(Sheet)

workbook.createSheet("Test1");// 创建工作表(Sheet)

workbook.createSheet("Test2");// 创建工作表(Sheet)

workbook.createSheet("Test3");// 创建工作表(Sheet)

workbook.setActiveSheet(2);//设置默认工作表

(2)重命名工作表

HSSFWorkbookworkbook=newHSSFWorkbook();// 创建Excel文件(Workbook)

workbook.createSheet("Test0");// 创建工作表(Sheet)

workbook.createSheet("Test1");// 创建工作表(Sheet)

workbook.createSheet("Test2");// 创建工作表(Sheet)

workbook.createSheet("Test3");// 创建工作表(Sheet)

workbook.setSheetName(2,"1234");//重命名工作表

(3)调整表单显示比例

HSSFWorkbookworkbook=newHSSFWorkbook();// 创建Excel文件(Workbook)

HSSFSheetsheet1=workbook.createSheet("Test0");// 创建工作表(Sheet)

HSSFSheetsheet2=workbook.createSheet("Test1");// 创建工作表(Sheet)

HSSFSheetsheet3=workbook.createSheet("Test2");// 创建工作表(Sheet)

sheet1.setZoom(1,2);//50%显示比例

sheet2.setZoom(2,1);//200%显示比例

sheet3.setZoom(1,10);//10%显示比例

(4)显示/隐藏网格线

HSSFWorkbookworkbook=newHSSFWorkbook();// 创建Excel文件(Workbook)

HSSFSheetsheet1=workbook.createSheet("Test0");// 创建工作表(Sheet)

HSSFSheetsheet2=workbook.createSheet("Test1");// 创建工作表(Sheet)

sheet1.setDisplayGridlines(false);//隐藏Excel网格线,默认值为true

sheet2.setGridsPrinted(true);//打印时显示网格线,默认值为false

(5)遍历Sheet

StringfilePath="d:\users\lizw\桌面\POI\sample.xls";

FileInputStreamstream=newFileInputStream(filePath);

HSSFWorkbookworkbook=newHSSFWorkbook(stream);//读取现有的Excel

HSSFSheetsheet=workbook.getSheet("Test0");//得到指定名称的Sheet

for(Rowrow:sheet)

{

for(Cellcell:row)

{

System.out.print(cell "t");

}

System.out.println();

}

7.Excel行列操作

(1)组合行、列

HSSFSheetsheet=workbook.createSheet("Test0");// 创建工作表(Sheet)

sheet.groupRow(1,3);//组合行

sheet.groupRow(2,4);//组合行

sheet.groupColumn(2,7);//组合列

这里简单的介绍一下什么叫做组合:组合分为行组合和列组合,所谓行组合,就是让n行组合成一个集合,能够进行展开和合拢操作。

使用POI也可以取消组合,例如:sheet.ungroupColumn(1, 3);//取消列组合

(2)锁定列

在Excel中,有时可能会出现列数太多或是行数太多的情况,这时可以通过锁定列来冻结部分列,不随滚动条滑动,方便查看。

HSSFSheetsheet=workbook.createSheet("Test0");// 创建工作表(Sheet)

sheet.createFreezePane(2,3,15,25);//冻结行列

下面对CreateFreezePane的参数作一下说明:

第一个参数表示要冻结的列数;

第二个参数表示要冻结的行数,这里只冻结列所以为0;

第三个参数表示右边区域可见的首列序号,从1开始计算;

第四个参数表示下边区域可见的首行序号,也是从1开始计算,这里是冻结列,所以为0;

(3)上下移动行

FileInputStreamstream=newFileInputStream(filePath);

HSSFWorkbookworkbook=newHSSFWorkbook(stream);

HSSFSheetsheet=workbook.getSheet("Test0");

sheet.shiftRows(2,4,2);//把第3行到第4行向下移动两行

HSSFSheet.shiftRows(startRow, endRow, n)参数说明

startRow:需要移动的起始行;

endRow:需要移动的结束行;

n:移动的位置,正数表示向下移动,负数表示向上移动;

8.Excel的其他功能

(1)设置密码

HSSFSheetsheet=workbook.createSheet("Test0");// 创建工作表(Sheet)

HSSFRowrow=sheet.createRow(1);

HSSFCellcell=row.createCell(1);

cell.setCellValue("已锁定");

HSSFCellStylelocked=workbook.createCellStyle();

locked.setLocked(true);//设置锁定

cell.setCellStyle(locked);

cell=row.createCell(2);

cell.setCellValue("未锁定");

HSSFCellStyleunlocked=workbook.createCellStyle();

unlocked.setLocked(false);//设置不锁定

cell.setCellStyle(unlocked);

sheet.protectSheet("password");//设置保护密码

(2)数据有效性

HSSFSheetsheet=workbook.createSheet("Test0");// 创建工作表(Sheet)

HSSFRowrow=sheet.createRow(0);

HSSFCellcell=row.createCell(0);

cell.setCellValue("日期列");

CellRangeAddressListregions=newCellRangeAddressList(1,65535,0,0);//选定一个区域

DVConstraintconstraint=DVConstraint.createDateConstraint(DVConstraint . OperatorType . BETWEEN , "1993-01-01" ,"2014-12-31" , "yyyy-MM-dd" );

HSSFDataValidationdataValidate=newHSSFDataValidation(regions,constraint);

dataValidate.createErrorBox("错误","你必须输入一个时间!");

sheet.addValidationData(dataValidate);

CellRangeAddressList类表示一个区域,构造函数中的四个参数分别表示起始行序号,终止行序号,起始列序号,终止列序号。65535是一个Sheet的最大行数。另外,CreateDateConstraint的第一个参数除了设置成DVConstraint.OperatorType.BETWEEN外,还可以设置成如下一些值,大家可以自己一个个去试看看效果:

验证的数据类型也有几种选择,如下:

(3)生成下拉式菜单

CellRangeAddressListregions=newCellRangeAddressList(0,65535,0,0);

DVConstraintconstraint=DVConstraint.createExplicitListConstraint(newString[]{"C ","Java","C#"});

HSSFDataValidationdataValidate=newHSSFDataValidation(regions,constraint);

sheet.addValidationData(dataValidate);

(4)打印基本设置

HSSFSheetsheet=workbook.createSheet("Test0");// 创建工作表(Sheet)

HSSFPrintSetupprint=sheet.getPrintSetup();//得到打印对象

print.setLandscape(false);//true,则表示页面方向为横向;否则为纵向

print.setScale((short)80);//缩放比例80%(设置为0-100之间的值)

print.setFitWidth((short)2);//设置页宽

print.setFitHeight((short)4);//设置页高

print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);//纸张设置

print.setUsePage(true);//设置打印起始页码不使用"自动"

print.setPageStart((short)6);//设置打印起始页码

sheet.setPrintGridlines(true);//设置打印网格线

print.setNoColor(true);//值为true时,表示单色打印

print.setDraft(true);//值为true时,表示用草稿品质打印

print.setLeftToRight(true);//true表示“先行后列”;false表示“先列后行”

print.setNotes(true);//设置打印批注

sheet.setAutobreaks(false);//Sheet页自适应页面大小

更详细的打印设置请参考:http://tonyqus.sinaapp.com/archives/271

(5)超链接

HSSFSheetsheet=workbook.createSheet("Test0");

CreationHelpercreateHelper=workbook.getCreationHelper();

// 关联到网站

Hyperlinklink=createHelper.createHyperlink(Hyperlink.LINK_URL);

link.setAddress("");

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);

9.POI对Word的基本操作

(1)POI操作Word简介

POI读写Excel功能强大、操作简单。但是POI操作时,一般只用它读取word文档,POI只能能够创建简单的word文档,相对而言POI操作时的功能太少。

(2)POI创建Word文档的简单示例

XWPFDocumentdoc=newXWPFDocument();// 创建Word文件

XWPFParagraphp=doc.createParagraph();// 新建一个段落

p.setAlignment(ParagraphAlignment.CENTER);// 设置段落的对齐方式

p.setBorderBottom(Borders.DOUBLE);//设置下边框

p.setBorderTop(Borders.DOUBLE);//设置上边框

p.setBorderRight(Borders.DOUBLE);//设置右边框

p.setBorderLeft(Borders.DOUBLE);//设置左边框

XWPFRunr=p.createRun();//创建段落文本

r.setText("POI创建的Word段落文本");

r.setBold(true);//设置为粗体

r.setColor("FF0000");//设置颜色

p=doc.createParagraph();// 新建一个段落

r=p.createRun();

r.setText("POI读写Excel功能强大、操作简单。");

XWPFTabletable=doc.createTable(3,3);//创建一个表格

table.getRow(0).getCell(0).setText("表格1");

table.getRow(1).getCell(1).setText("表格2");

table.getRow(2).getCell(2).setText("表格3");

FileOutputStreamout=newFileOutputStream("d:\POI\sample.doc");

doc.write(out);

out.close();

(3)POI读取Word文档里的文字

FileInputStreamstream=newFileInputStream("d:\POI\sample.doc");

XWPFDocumentdoc=newXWPFDocument(stream);// 创建Word文件

for(XWPFParagraphp:doc.getParagraphs())//遍历段落

{

System.out.print(p.getParagraphText());

}

for(XWPFTabletable:doc.getTables())//遍历表格

{

for(XWPFTableRowrow:table.getRows())

{

for(XWPFTableCellcell:row.getTableCells())

{

System.out.print(cell.getText());

}

}

}

本文由澳门新萄京官方网站发布于办公软件,转载请注明出处:澳门新萄京官方网站:Excel表格核对8神技,如何

关键词: