在讲导入Excel功能之前,先拓展一下Excel与POI对应关系:
表关系
Excel | POI |
Sheet1表 | 0表 |
Sheet2表 | 1表 |
Sheet3表 | 2表 |
依次类推 | |
行关系
Excel | POI |
1行 | 0行 |
2行 | 1行 |
3行 | 2行 |
依次类推 |
列关系
Excel | POI |
A列 | 0列 |
B列 | 1列 |
C列 | 2列 |
依次类推 |
Apache POI 是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写,意为“可怜的模糊实现”。
Apache POI 是创建和维护操作各种符合Office Open XML(OOXML)标准和微软的OLE 2复合文档格式(OLE2)的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案(适用于Excel97-2008)。
主要组件:
Excel (SS=HSSF+XSSF)
Word (HWPF+XWPF)
PowerPoint (HSLF+XSLF)
OpenXML4J (OOXML)
OLE2 Filesystem (POIFS)
OLE2 Document Props (HPSF)
Outlook (HSMF)
Visio (HDGF)
Publisher (HPBF)
Excel表格有两种格式,一种是xls格式的,另一种是xlsx格式的,相对应的java poi针对操作这两种格式的表格设计了两种不同的组件,目前很多系统中仍然存在HSSF处理xls的代码,此篇文章就是让读者了解HSSF大部分用法,我们会用HSSF制作一个精美的xls格式课程表。
实例一
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
import
java.io.FileOutputStream;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
createBlankExcel {
public
static
void
main(String[] args) {
try
{
HSSFWorkbook workbook=
new
HSSFWorkbook();
FileOutputStream out=
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(out);
out.close();
}
catch
(Exception e) {
e.printStackTrace();
}
}
}
|
空白的xls表格。
实例二 单元格赋值
让我们来创建表格并且为表格赋值,关于创建表格,你只要记住HSSFWorkbook 创建HSSFSheet,HSSFSheet创建HSSFRow,HSSFRow创建HSSFCell。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
import
java.io.FileOutputStream;
import
java.util.Random;
import
org.apache.poi.hssf.usermodel.HSSFCell;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
createSimpleExcel {
public
static
void
main(String[] args) {
try
{
HSSFWorkbook workbook=
new
HSSFWorkbook();
/**
* createSheet存在有参和无参两种形式,主要设置sheet名字
*/
HSSFSheet sheet=workbook.createSheet(
"课程表"
);
HSSFRow row=sheet.createRow(
0
);
//创建第一行
/**
* 我们知道课程表第一行是代表周一到周五,下面我们用两种方式创建Cell
* 一种用变量,另一种未用变量,用变量的好处后面可以体会到。
*/
HSSFCell cell=row.createCell(
0
);
cell.setCellValue(
"星期一"
);
row.createCell(
1
).setCellValue(
"星期二"
);
row.createCell(
2
).setCellValue(
"星期三"
);
row.createCell(
3
).setCellValue(
"星期四"
);
row.createCell(
4
).setCellValue(
"星期五"
);
/**
* 上面我们只是设置了首行,后面课程我们用同样的方法设置,
* 这里面我们用循环方法设置课程
*/
Random random=
new
Random();
String[] course={
"语文"
,
"数学"
,
"英语"
,
"物理"
,
"化学"
,
"政治"
,
"历史"
,
"音乐"
,
"美术"
,
"体育"
};
//循环产生7行row
for
(
int
j=
1
;j<=
7
;j++){
//每个row的1-5个cell设置值,我用随机取数组来写值。
HSSFRow row_j=sheet.createRow(j);
for
(
int
k=
0
;k<=
4
;k++){
int
i=random.nextInt(
10
);
row_j.createCell(k).setCellValue(course[i]);
}
}
FileOutputStream out=
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(out);
out.close();
}
catch
(Exception e) {
e.printStackTrace();
}
}
}
|
如图:
上面代码主要用到了HSSFCell的setCellValue方法,该方法目前有6种不同参数的调用,下面我们来解析一下:
setCellValue(boolean) | 设置boolean型,即true|false |
setCellValue(Canlendar) | 设置Canlendar,Java表示时间的一种类 |
setCellValue(Date) | Date 也是一种时间类型 |
setCellValue(double) | 双浮点类型数字 |
setCellValue(RichTextString) | POI中HSSFRichTextString接口的实现类,可以使用不同格式格式化一个单元格的内容。 |
setCellValue(String) | 设置字符串 |
后面我们会继续用到以上不同参数的方法,现在只要了解就好。
实例三 合并单元格
我们继续完善课程表,我们知道每个课程表都有一个名称在第一列,同时存在合并的上午、下午、午休。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
import
java.io.FileInputStream;
import
java.io.FileOutputStream;
import
org.apache.poi.hssf.usermodel.HSSFCell;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
import
org.apache.poi.ss.util.CellRangeAddress;
public
class
createMoveExcel {
public
static
void
main(String[] args) {
try
{
FileInputStream is =
new
FileInputStream(
"D:/课程表.xls"
);
HSSFWorkbook workbook =
new
HSSFWorkbook(is);
HSSFSheet sheet = workbook.getSheet(
"课程表"
);
/**
* 首先我需要将课程表格第一行空出来,所以要对下面的
* 行进行下移操作。
*/
sheet.shiftRows(
0
,sheet.getLastRowNum(),
1
);
/**
* 插入午休 行,这里面为什么用6而不是5
* 我们知道上午一般是4节课,加上星期行,
* 按理应该从第五行下移一行,不要忘了前面
* 我们下移了一行,所以应该从第6行执行,对Excel操作如果
* 前面存在操作,我们应该考虑进来。
*/
sheet.shiftRows(
6
,sheet.getLastRowNum(),
1
);
/**
* 开始我认为移动会自己创建行和列,因此我直接
* 用方法想获取row以及cell,这时候报空指针,查API了解
* shiftRows可以把某区域的行移动,但是移动后剩下的区域却为空
* 因此我们需要创建
*/
/*HSSFRow row=sheet.getRow(0);
HSSFCell cell=row.getCell(0);
cell.setCellValue("课程表");*/
HSSFRow row=sheet.createRow(0);
HSSFCell cell=row.createCell(0);
cell.setCellValue("课程表");
HSSFRow srow=sheet.createRow(6);
HSSFCell scell=srow.createCell(0);
scell.setCellValue("午休");
/**
* 合并单元格功能,对新增的第一行进行合并
*/
CellRangeAddress address=new CellRangeAddress(0,0,0,4);
sheet.addMergedRegion(address);
/**
* 对新增的第六行进行合并
*/
CellRangeAddress secondaddress=new CellRangeAddress(6,6,0,4);
sheet.addMergedRegion(secondaddress);
/**
* 对表格的修改以及其他操作需要在workbook.write之后才生效的
*/
FileOutputStream os =
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(os);
is.close();
os.close();
}
catch
(Exception e) {
e.printStackTrace();
}
}
}
|
上面代码思路是先将所有列运用HSSFSheet.shiftRows下移一行,
HSSFSheet.shiftRows(startRow, endRow, n)参数说明
startRow:需要移动的起始行;
endRow:需要移动的结束行;
n:移动的位置,正数表示向下移动,负数表示向上移动;
然后需要我们自己创建行和列,shiftRows可以把某区域的行移动,但是移动后剩下的区域却为空
创建新的行和列并且设置单元格以后,我们开始进行合并单元格。
CellRangeAddress address=new CellRangeAddress(0,0,0,5);
sheet.addMergedRegion(address);
CellRangeAddress对象其实就是表示一个区域,其构造方法如下:CellRangeAddress(firstRow, lastRow, firstCol, lastCol),参数的说明:
firstRow 区域中第一个单元格的行号
lastRow 区域中最后一个单元格的行号
firstCol 区域中第一个单元格的列号
lastCol 区域中最后一个单元格的列号
最后,我们要调用
FileOutputStream os = new FileOutputStream("D:/课程表.xls"); workbook.write(os);
任何对表的修改或者其他操作,都在 workbook.write(os); 后生效的。
如图
实例四 合并单元格
实例三我没有按照开始的想法插入合并的上午和下午单元格,因为HSSF没有提供移动列的方法,所以我们需要在实例二中开始生成课程表的时候就考虑加进去,现在我们对实例二进行修改,同时将实例三的代码加入实例二中。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
import
java.io.FileOutputStream;
import
java.util.Random;
import
org.apache.poi.hssf.usermodel.HSSFCell;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
import
org.apache.poi.ss.util.CellRangeAddress;
public
class
createSimpleExcel {
public
static
void
main(String[] args) {
try
{
HSSFWorkbook workbook=
new
HSSFWorkbook();
/**
* createSheet存在有参和无参两种形式,主要设置sheet名字
*/
HSSFSheet sheet=workbook.createSheet(
"课程表"
);
HSSFRow firstrow=sheet.createRow(
0
);
//创建第一行
HSSFCell cell=firstrow.createCell(
0
);
cell.setCellValue(
"课程表"
);
CellRangeAddress address=
new
CellRangeAddress(
0
,
0
,
0
,
5
);
sheet.addMergedRegion(address);
/**
* 我们知道课程表第一行是代表周一到周五,下面我们用两种方式创建Cell,
* 一种用变量,另一种未用变量,用变量的好处后面可以体会到。
*/
HSSFRow sencodrow=sheet.createRow(
1
);
//创建第二行
//这里面我们第一列不用是因为第三行存在合并的上午单元格,自己体会下
sencodrow.createCell(
1
).setCellValue(
"星期一"
);
sencodrow.createCell(
2
).setCellValue(
"星期二"
);
sencodrow.createCell(
3
).setCellValue(
"星期三"
);
sencodrow.createCell(
4
).setCellValue(
"星期四"
);
sencodrow.createCell(
5
).setCellValue(
"星期五"
);
/**
* 上面我们只是设置了首行,后面课程我们用同样的方法设置,
* 这里面我们用循环方法设置课程
*/
Random random=
new
Random();
String[] course={
"语文"
,
"数学"
,
"英语"
,
"物理"
,
"化学"
,
"政治"
,
"历史"
,
"音乐"
,
"美术"
,
"体育"
};
//循环产生7行row
for
(
int
j=
2
;j<=
9
;j++){
//每个row的1-5个cell设置值,我用随机取数组来写值。
HSSFRow row_j=sheet.createRow(j);
//第六行是午休
if
(j==
6
){
row_j.createCell(
0
).setCellValue(
"午休"
);
CellRangeAddress secondaddress=
new
CellRangeAddress(
6
,
6
,
0
,
5
);
sheet.addMergedRegion(secondaddress);
continue
;
}
//每行开始都要空出一列来让我们能增加上午下午单元格
for
(
int
k=
1
;k<=
5
;k++){
int
i=random.nextInt(
10
);
row_j.createCell(k).setCellValue(course[i]);
}
}
sheet.getRow(
2
).createCell(
0
).setCellValue(
"上午"
);
sheet.getRow(
7
).createCell(
0
).setCellValue(
"下午"
);
CellRangeAddress thridaddress=
new
CellRangeAddress(
2
,
5
,
0
,
0
);
sheet.addMergedRegion(thridaddress);
CellRangeAddress fourthaddress=
new
CellRangeAddress(
7
,
9
,
0
,
0
);
sheet.addMergedRegion(fourthaddress);
FileOutputStream out=
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(out);
out.close();
}
catch
(Exception e) {
e.printStackTrace();
}
}
}
|
如图,已经初步可以看出来效果了。
实例五 单元格对齐
我们对字进行居中处理,HSSFCellStyle专门是创建修饰单元格的对象
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
import
org.apache.poi.hssf.usermodel.HSSFCellStyle;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
createStyleExcel {
public
static
void
main(String[] args) {
try
{
FileInputStream is =
new
FileInputStream(
"D:/课程表.xls"
);
HSSFWorkbook workbook =
new
HSSFWorkbook(is);
HSSFSheet sheet=workbook.getSheet(
"课程表"
);
HSSFRow firstrow=sheet.getRow(
0
);
//获取课程表行
HSSFRow sencodrow=sheet.getRow(
2
);
//获取上午行
HSSFRow sixthrow=sheet.getRow(
6
);
//获取午休行
HSSFRow ninthrow=sheet.getRow(
7
);
//获取下午行
HSSFCellStyle style=workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//垂直居中
firstrow.getCell(
0
).setCellStyle(style);
sencodrow.getCell(
0
).setCellStyle(style);
sixthrow.getCell(
0
).setCellStyle(style);
ninthrow.getCell(
0
).setCellStyle(style);
FileOutputStream out=
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(out);
out.close();
is.close();
}
catch
(Exception e) {
e.printStackTrace();
}
}
}
|
如图
上面代码我们用到了HSSFCellStyle的常量,以下是水平居中和垂直居中常量
水平对齐相关参数
如果是左侧对齐就是 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;
调整单元文字位置的方法还有以下几个:
setWrapText:自动换行,参数为boolean
setIndention;缩进
setRotation;文本旋转,这里的取值是从-90到90
实例六 使用边框
上面的课程表很明显的缺少边框,我们现在来给他加上。注意设置边框以及边框颜色都是针对单元格的,我们可以对row设置边框,但会将那一行全都被设置,因此我们需要再更细的单元格上操作。
边框和其他单元格设置一样也是调用CellStyle接口,CellStyle有2种和边框相关的属性,分别是:
边框相关属性 | 说明 | 范例 |
Border+ 方向 | 边框类型 | BorderLeft, BorderRight 等 |
方向 +BorderColor | 边框颜色 | TopBorderColor,BottomBorderColor 等 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
import
java.io.FileInputStream;
import
java.io.FileOutputStream;
import
org.apache.poi.hssf.usermodel.HSSFCellStyle;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
import
org.apache.poi.hssf.util.HSSFColor;
public
class
createBoderExcel {
public
static
void
main(String[] args) {
try
{
FileInputStream is =
new
FileInputStream(
"D:/课程表.xls"
);
HSSFWorkbook workbook =
new
HSSFWorkbook(is);
HSSFSheet sheet=workbook.getSheet(
"课程表"
);
HSSFCellStyle firststyle=workbook.createCellStyle();
//第一种样式针对第一个单元格的,不存在右边线
firststyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
firststyle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
firststyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
firststyle.setTopBorderColor(HSSFColor.PINK.index);
firststyle.setLeftBorderColor(HSSFColor.PINK.index);
firststyle.setBottomBorderColor(HSSFColor.BLUE.index);
HSSFCellStyle secondstyle=workbook.createCellStyle();
//第二种样式针对中间单元格的,不存在左右边线
secondstyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
secondstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
secondstyle.setTopBorderColor(HSSFColor.PINK.index);
secondstyle.setBottomBorderColor(HSSFColor.BLUE.index);
HSSFCellStyle thirdstyle=workbook.createCellStyle();
//第三种样式针对最后单元格的,不存在左边线
thirdstyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
thirdstyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
thirdstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
thirdstyle.setTopBorderColor(HSSFColor.PINK.index);
thirdstyle.setRightBorderColor(HSSFColor.PINK.index);
thirdstyle.setBottomBorderColor(HSSFColor.BLUE.index);
HSSFRow firstrow=sheet.getRow(
0
);
for
(
int
i=
0
;i<firstrow.getLastCellNum();i++){
if
(i==
0
){
firststyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
firstrow.getCell(i).setCellStyle(firststyle);
}
else
if
(i==firstrow.getLastCellNum()-
1
){
firstrow.createCell(i);
//注意前面实例针对第一行只创建了第一列,因此在这里我们需要创建列,不然不会设置边框
firstrow.getCell(i).setCellStyle(thirdstyle);
}
else
{
firstrow.createCell(i);
firstrow.getCell(i).setCellStyle(secondstyle);
}
}
//发现对合并的单元格设置边框,居中效果居然没了,因此我们在这里补充
FileOutputStream out=
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(out);
out.close();
is.close();
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
如图
关于常量代表的边框对应如下:
实例七 设置字体
注意因为对于单元格设置style,只要调用setCellStyle,其他代码设置的样式都会被清除,只会存在本次设置样式中保存的样式!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
import
java.io.FileInputStream;
import
java.io.FileOutputStream;
import
org.apache.poi.hssf.record.cf.FontFormatting;
import
org.apache.poi.hssf.usermodel.HSSFCellStyle;
import
org.apache.poi.hssf.usermodel.HSSFFont;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
import
org.apache.poi.hssf.util.HSSFColor;
public
class
createFontStyle {
public
static
void
main(String[] args) {
try
{
FileInputStream is =
new
FileInputStream(
"D:/课程表.xls"
);
HSSFWorkbook workbook =
new
HSSFWorkbook(is);
HSSFSheet sheet=workbook.getSheet(
"课程表"
);
HSSFCellStyle style=workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontName(
"微软雅黑"
);
//设置字体名称
font.setFontHeightInPoints((
short
)
10
);
//设置字号
font.setColor(HSSFColor.RED.index);
//设置字体颜色
font.setUnderline(FontFormatting.U_SINGLE);
//设置下划线
font.setTypeOffset(FontFormatting.SS_SUPER);
//设置上标下标
font.setStrikeout(
true
);
//设置删除线
style.setFont(font);
sheet.getRow(
0
).getCell(
0
).setCellStyle(style);
FileOutputStream out=
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(out);
out.close();
is.close();
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
如图:
实例八 设置背景和纹理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
import
java.io.FileInputStream;
import
java.io.FileOutputStream;
import
org.apache.poi.hssf.usermodel.HSSFCellStyle;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
import
org.apache.poi.hssf.util.HSSFColor;
public
class
createBackGroundExcel {
public
static
void
main(String[] args) {
try
{
FileInputStream is =
new
FileInputStream(
"D:/课程表.xls"
);
HSSFWorkbook workbook =
new
HSSFWorkbook(is);
HSSFSheet sheet=workbook.getSheet(
"课程表"
);
HSSFCellStyle style=workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREEN.index);
//设置图案颜色
style.setFillBackgroundColor(HSSFColor.RED.index);
//设置图案背景色
style.setFillPattern(HSSFCellStyle. THICK_VERT_BANDS);
//设置图案样式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
sheet.getRow(
2
).getCell(
0
).setCellStyle(style);
FileOutputStream out=
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(out);
out.close();
is.close();
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
图片样式与对象常量对应:
如图:
实例九 设置宽度和长度
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
import
java.io.FileInputStream;
import
java.io.FileOutputStream;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
createWidthAndHeight {
public
static
void
main(String[] args) {
try
{
FileInputStream is =
new
FileInputStream(
"D:/课程表.xls"
);
HSSFWorkbook workbook =
new
HSSFWorkbook(is);
HSSFSheet sheet=workbook.getSheet(
"课程表"
);
sheet.setColumnWidth(
1
,
20
*
256
);
sheet.getRow(
0
).setHeightInPoints(
50
);
FileOutputStream out=
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(out);
out.close();
is.close();
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
如图:
setColumnWidth的第二个参数要乘以256,这是怎么回事呢?其实,这个参数的单位是1/256个字符宽度,也就是说,这里是把B列的宽度设置为了31个字符。
设置行高使用HSSFRow对象的setHeight和setHeightInPoints方法,这两个方法的区别在于setHeightInPoints的单位是点,而setHeight的单位是1/20个点,所以setHeight的值永远是setHeightInPoints的20倍。
你也可以使用HSSFSheet.setDefaultColumnWidth、HSSFSheet.setDefaultRowHeight和HSSFSheet.setDefaultRowHeightInPoints方法设置默认的列宽或行高。
以上的是对HSSF基本用法的示范,下面我们来进行更进一步的补充说明:
实例十 创建文档摘要信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
import
java.io.FileInputStream;
import
java.io.FileOutputStream;
import
org.apache.poi.hpsf.DocumentSummaryInformation;
import
org.apache.poi.hpsf.SummaryInformation;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
createSummaryInformation {
public
static
void
main(String[] args) {
try
{
FileInputStream is =
new
FileInputStream(
"D:/课程表.xls"
);
HSSFWorkbook workbook =
new
HSSFWorkbook(is);
workbook.createInformationProperties();
//创建文档信息
DocumentSummaryInformation dsi= workbook.getDocumentSummaryInformation();
//摘要信息
dsi.setCategory(
"类别:Excel文件"
);
//类别
dsi.setManager(
"管理者:Liki"
);
//管理者
dsi.setCompany(
"公司:无"
);
//公司
SummaryInformation si = workbook.getSummaryInformation();
//摘要信息
si.setSubject(
"主题:课程表"
);
//主题
si.setTitle(
"标题:初中课程表"
);
//标题
si.setAuthor(
"作者:zyn"
);
//作者
si.setComments(
"备注:课程表展示"
);
//备注
FileOutputStream out=
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(out);
out.close();
is.close();
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
如图:
实例十一 设置批注
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
import
java.io.FileInputStream;
import
java.io.FileOutputStream;
import
org.apache.poi.hssf.usermodel.HSSFCell;
import
org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import
org.apache.poi.hssf.usermodel.HSSFComment;
import
org.apache.poi.hssf.usermodel.HSSFPatriarch;
import
org.apache.poi.hssf.usermodel.HSSFRichTextString;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
createPatriarch {
public
static
void
main(String[] args) {
try
{
FileInputStream is =
new
FileInputStream(
"D:/课程表.xls"
);
HSSFWorkbook workbook =
new
HSSFWorkbook(is);
HSSFSheet sheet=workbook.getSheet(
"课程表"
);
HSSFPatriarch patr = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = patr.createAnchor(
0
,
0
,
0
,
0
,
9
,
0
,
11
,
6
);
//创建批注位置
HSSFComment comment = patr.createCellComment(anchor);
//创建批注
comment.setString(
new
HSSFRichTextString(
"这是一个批注段落!"
));
//设置批注内容
comment.setAuthor(
"ZYN"
);
//设置批注作者
comment.setVisible(
true
);
//设置批注默认显示
HSSFCell cell = sheet.getRow(
0
).getCell(
0
);
cell.setCellComment(comment);
FileOutputStream out=
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(out);
out.close();
is.close();
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
如图:
HSSFPatriarch.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)方法参数说明:
dx1
- the x coordinate in EMU within the first cell.dy1
- the y coordinate in EMU within the first cell.dx2
- the x coordinate in EMU within the second cell.dy2
- the y coordinate in EMU within the second cell.col1
- the column (0 based) of the first cell.row1
- the row (0 based) of the first cell.col2
- the column (0 based) of the second cell.row2
- the row (0 based) of the second cell.
实例十二 设置页眉和页脚
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
import
java.io.FileInputStream;
import
java.io.FileOutputStream;
import
org.apache.poi.hssf.usermodel.HSSFFooter;
import
org.apache.poi.hssf.usermodel.HSSFHeader;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
createHeadAndFooter {
public
static
void
main(String[] args) {
try
{
FileInputStream is =
new
FileInputStream(
"D:/课程表.xls"
);
HSSFWorkbook workbook =
new
HSSFWorkbook(is);
HSSFSheet sheet=workbook.getSheet(
"课程表"
);
HSSFHeader header =sheet.getHeader();
//得到页眉
header.setLeft(
"页眉左边"
);
header.setRight(
"页眉右边"
);
header.setCenter(
"页眉中间"
);
HSSFFooter footer =sheet.getFooter();
//得到页脚
footer.setLeft(
"页脚左边"
);
footer.setRight(
"页脚右边"
);
footer.setCenter(
"页脚中间"
);
FileOutputStream out=
new
FileOutputStream(
"D:/课程表.xls"
);
workbook.write(out);
out.close();
is.close();
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
也可以使用Office自带的标签定义,你可以通过HSSFHeader或HSSFFooter访问到它们,都是静态属性,列表如下:
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 当前页号
实例十三 单元格格式操作
我们经常需要将单元格的数据进行格式化,比如时间、货币,HSSF提供格式化这些数据有两种实现方式,一种是内嵌格式,另一种是自定义格式。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
import
java.io.FileOutputStream;
import
java.util.Date;
import
org.apache.poi.hssf.usermodel.HSSFCell;
import
org.apache.poi.hssf.usermodel.HSSFCellStyle;
import
org.apache.poi.hssf.usermodel.HSSFDataFormat;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
createDataFormat {
public
static
void
main(String[] args) {
try
{
HSSFWorkbook workbook =
new
HSSFWorkbook();
HSSFSheet sheet=workbook.createSheet(
"格式转换"
);
HSSFRow row0=sheet.createRow(
0
);
/**
* 时间格式转换
* 我们用第一排第一个、第二个、第三个单元格都设置当前时间
* 然后第一个单元格不进行任何操作,第二个单元格用内嵌格式,第三个单元格用自定义
*/
Date date=
new
Date();
HSSFCell row1_cell1=row0.createCell(
0
);
HSSFCell row1_cell2=row0.createCell(
1
);
HSSFCell row1_cell3=row0.createCell(
2
);
row1_cell1.setCellValue(date);
row1_cell2.setCellValue(date);
row1_cell3.setCellValue(date);
HSSFCellStyle style1=workbook.createCellStyle();
style1.setDataFormat(HSSFDataFormat.getBuiltinFormat(
"m/d/yy h:mm"
));
HSSFCellStyle style2=workbook.createCellStyle();
style2.setDataFormat(workbook.createDataFormat().getFormat(
"yyyy-mm-dd hh:m:ss"
));
row1_cell2.setCellStyle(style1);
row1_cell3.setCellStyle(style2);
/**
* 第二排我们进行小数处理
* 第一个不进行任何处理,第二个我们用内嵌格式保留两位,第三个我们用自定义
*/
HSSFRow row1=sheet.createRow(
1
);
double
db=
3.1415926
;
HSSFCell row2_cell1=row1.createCell(
0
);
HSSFCell row2_cell2=row1.createCell(
1
);
HSSFCell row2_cell3=row1.createCell(
2
);
row2_cell1.setCellValue(db);
row2_cell2.setCellValue(db);
row2_cell3.setCellValue(db);
HSSFCellStyle style3=workbook.createCellStyle();
style3.setDataFormat(HSSFDataFormat.getBuiltinFormat(
"0.00"
));
HSSFCellStyle style4=workbook.createCellStyle();
style4.setDataFormat(workbook.createDataFormat().getFormat(
"0.00"
));
row2_cell2.setCellStyle(style3);
row2_cell3.setCellStyle(style4);
/**
* 下面是进行货币的三种形式
*/
HSSFRow row2=sheet.createRow(
2
);
double
money=
12345.6789
;
HSSFCell row3_cell1=row2.createCell(
0
);
HSSFCell row3_cell2=row2.createCell(
1
);
HSSFCell row3_cell3=row2.createCell(
2
);
row3_cell1.setCellValue(money);
row3_cell2.setCellValue(money);
row3_cell3.setCellValue(money);
HSSFCellStyle style5=workbook.createCellStyle();
style5.setDataFormat(HSSFDataFormat.getBuiltinFormat(
"¥#,##0.00"
));
HSSFCellStyle style6=workbook.createCellStyle();
style6.setDataFormat(workbook.createDataFormat().getFormat(
"¥#,##0.00"
));
row3_cell2.setCellStyle(style3);
row3_cell3.setCellStyle(style4);
FileOutputStream out=
new
FileOutputStream(
"D:/格式转换.xls"
);
workbook.write(out);
out.close();
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
如图:
HSSFDataFormat.getFormat和HSSFDataFormat.getBuiltinFormat的区别: 当使用Excel内嵌的(或者说预定义)的格式时,直接用HSSFDataFormat.getBuiltinFormat静态方法即可。当使用自己定义的格式时,必须先调用HSSFWorkbook.createDataFormat(),因为这时在底层会先找有没有匹配的内嵌FormatRecord,如果没有就会新建一个FormatRecord,所以必须先调用这个方法,然后你就可以用获得的HSSFDataFormat实例的getFormat方法了,当然相对而言这种方式比较麻烦,所以内嵌格式还是用HSSFDataFormat.getBuiltinFormat静态方法更加直接一些。
拓展:
判断单元格是否为日期类型,使用DateUtil.isCellDateFormatted(cell)方法。
实例十四 公式计算
很多人做过统计报表,都知道有时候我们需要用一列去统计其它列的数据,这时候我们经常会在这一列设置一个函数,下面我们用代码来实现这个功能,因为函数比较多,所以我会用较多的小例子分开说明以免代码过多混淆。
⑴基本计算
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
import
java.io.FileOutputStream;
import
org.apache.poi.hssf.usermodel.HSSFCell;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
createFormula {
public
static
void
main(String[] args) {
try
{
HSSFWorkbook workbook =
new
HSSFWorkbook();
HSSFSheet sheet=workbook.createSheet(
"基本计算"
);
HSSFRow row=sheet.createRow(
0
);
HSSFCell cell0=row.createCell(
0
);
cell0.setCellFormula(
"5*5+2"
);
//可直接赋予一个简单的计算公式
cell0=row.createCell(
1
);
cell0.setCellValue(
20
);
cell0=row.createCell(
2
);
cell0.setCellFormula(
"A1+B1"
);
cell0=row.createCell(
3
);
cell0.setCellFormula(
"A1-B1"
);
cell0=row.createCell(
4
);
cell0.setCellFormula(
"A1*B1"
);
cell0=row.createCell(
5
);
cell0.setCellFormula(
"A1/B1"
);
FileOutputStream out=
new
FileOutputStream(
"D:/基本计算.xls"
);
workbook.write(out);
out.close();
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
⑵SUM函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
import
java.io.FileOutputStream;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
createFormula {
public
static
void
main(String[] args) {
try
{
HSSFWorkbook workbook =
new
HSSFWorkbook();
HSSFSheet sheet=workbook.createSheet(
"基本计算"
);
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.createCell(
5
).setCellValue(
6
);
row.createCell(
6
).setCellValue(
7
);
//第七/八列进行计算,两种都等价A1+B1+C1+D1+E1+F1+G1
row.createCell(
7
).setCellFormula(
"sum(A1,B1,C1,D1,E1,F1,G1)"
);
row.createCell(
8
).setCellFormula(
"sum(A1:G1)"
);
FileOutputStream out=
new
FileOutputStream(
"D:/基本计算.xls"
);
workbook.write(out);
out.close();
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
结果:
⑶ABS绝对值、INT取整函数、ROUND四舍五入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
import
java.io.FileOutputStream;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
public
class
createABSDemo {
public
static
void
main(String[] args) {
try
{
HSSFWorkbook workbook =
new
HSSFWorkbook();
HSSFSheet sheet=workbook.createSheet(
"基本计算"
);
HSSFRow row0=sheet.createRow(
0
);
row0.createCell(
0
).setCellValue(-
1234
);
row0.createCell(
1
).setCellValue(
5678
);
HSSFRow row1=sheet.createRow(
1
);
row1.createCell(
0
).setCellValue(
23.456
);
row1.createCell(
1
).setCellValue(-
54.562
);
HSSFRow row2=sheet.createRow(
2
);
row2.createCell(
0
).setCellValue(
8.49314
);
row2.createCell(
1
).setCellValue(
12.927
);
/**
* 取绝对值
*/
row0.createCell(
2
).setCellFormula(
"ABS(A1)"
);
row0.createCell(
3
).setCellFormula(
"ABS(B1)"
);
/**
* 取整
*/
row1.createCell(
2
).setCellFormula(
"INT(A2)"
);
row1.createCell(
3
).setCellFormula(
"INT(B2)"
);
/**
* 四舍五入
*/
row2.createCell(
2
).setCellFormula(
"ROUND(A3,1)"
);
row2.createCell(
3
).setCellFormula(
"ROUND(B3,1)"
);
FileOutputStream out=
new
FileOutputStream(
"D:/基本计算.xls"
);
workbook.write(out);
out.close();
}
catch
(Exception e){
e.printStackTrace();
}
}
}
|
对于公式我们这里就不一一列举了,Excel所有公式函数都可以通过设置cellFormula来设置。
HSSF针对xls文件操作还有很多其他方面的,但是从开发方面来说,上面已经满足我们日常导入导出报表开发功能,后面我会详细讲解web关于HSSF读取以及解析xls报表功能。