Excel | POI |
Sheet1表 | 0表 |
Sheet2表 | 1表 |
Sheet3表 | 2表 |
依次类推 | |
Excel | POI |
1行 | 0行 |
2行 | 1行 |
3行 | 2行 |
依次类推 | |
Excel | POI |
A列 | 0列 |
B列 | 1列 |
C列 | 2列 |
依次类推 | |
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();
}
}
}
|
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();
}
}
}
|
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();
}
}
}
|
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();
}
}
}
|
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();
}
}
}
|
边框相关属性 | 说明 | 范例 |
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();
}
}
}
|
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();
}
}
}
|
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();
}
}
}
|
-
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();
}
}
}
|
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();
}
}
}
|
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();
}
}
}
|
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();
}
}
}
|
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();
}
}
}
|