Java POI导出excel实现
Java使用poi组件导出excel报表,能导出excel报表的还可以使用jxl组件,但jxl想对于poi功能有限,jxl应该不能载excel插入浮动层图片,poi能很好的实现输出excel各种功能,介绍poi导出excel功能实现案例,算比较常用的功能实现以及导出excel需要注意的地方,采用的是poi-3.8-20120326.jar,poi-ooxml-3.8-20120326.jar,poi-scratchpad-3.8-20120326.jar
输出表格
poi输出excel最基本是输出table表格,下面是输出区域、总销售额(万元)、总利润(万元)简单的表格,
创建HSSFWorkbook 对象,用于将excel输出到输出流中
1
2
3
4
5
6
7
8
9
10
11
12
13
|
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet( "table" ); //创建table工作薄
Object[][] datas = {{ "区域" , "总销售额(万元)" , "总利润(万元)简单的表格" }, { "江苏省" , 9045 , 2256 }, { "广东省" , 3000 , 690 }};
HSSFRow row;
HSSFCell cell;
for ( int i = 0 ; i < datas.length; i++) {
row = sheet.createRow(i); //创建表格行
for ( int j = 0 ; j < datas[i].length; j++) {
cell = row.createCell(j); //根据表格行创建单元格
cell.setCellValue(String.valueOf(datas[i][j]));
}
}
wb.write( new FileOutputStream( "/Users/mike/table.xls" ));
|
设置表格行高、列宽
有时表格文本比较多,需要设置表格的列宽度,在设置表格的行高与列宽时一定在创建全部的HSSFRow与HSSFCell之后,
即整个表格创建完成之后去设置,因为在单元格合并的时候,合并之前设置的宽度单元格会比设置的宽度更宽。 sheet.setColumnWidth 设置列宽值需要转换为excel的宽度值,使用工具类:MSExcelUtil,excel宽度并不是像素需要转换
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet( "table" ); //创建table工作薄
Object[][] datas = {{ "区域" , "总销售额(万元)" , "总利润(万元)简单的表格" }, { "江苏省" , 9045 , 2256 }, { "广东省" , 3000 , 690 }};
HSSFRow row;
HSSFCell cell;
for ( int i = 0 ; i < datas.length; i++) {
row = sheet.createRow(i); //创建表格行
for ( int j = 0 ; j < datas[i].length; j++) {
cell = row.createCell(j); //根据表格行创建单元格
cell.setCellValue(String.valueOf(datas[i][j]));
}
}
//创建表格之后设置行高与列宽
for ( int i = 0 ; i < datas.length; i++) {
row = sheet.getRow(i);
row.setHeightInPoints( 30 ); //设置行高
}
for ( int j = 0 ; j < datas[ 0 ].length; j++) {
sheet.setColumnWidth(j, MSExcelUtil.pixel2WidthUnits( 160 )); //设置列宽
}
wb.write( new FileOutputStream( "/Users/mike/table1.xls" ));
|
设置excel单元格样式
单元格可以设置居左、居中、居右、上下居中、设置边框、设置边框颜色、设置单元格背景颜色等, excel设置单元格有一个HSSFCellStyle类可以设置样式,单元格颜色比较麻烦,excel颜色对应一个下标值,我们可以使用自定义颜色,但下标值从11开始,前1-10被poi已经使用,通过palette.setColorAtIndex方法将颜色与下标值对应,下面cellStyle.setFillForegroundColor(bgIndex)设置背景颜色时set 下标值并不是颜色Color,一个下标值如11不能被重复设置颜色,否则excel单元格显示的都是黑色,如下 背景颜色使用下标值bgIndex=11,边框颜色使用下标值bdIndex=12
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
|
short colorIndex = 10 ;
HSSFPalette palette = wb.getCustomPalette(); //自定义颜色
Color rgb = Color.GREEN;
short bgIndex = colorIndex ++; //背景颜色下标值
palette.setColorAtIndex(bgIndex, ( byte ) rgb.getRed(), ( byte ) rgb.getGreen(), ( byte ) rgb.getBlue());
short bdIndex = colorIndex ++; //边框颜色下标值
rgb = Color.BLACK;
palette.setColorAtIndex(bdIndex, ( byte ) rgb.getRed(), ( byte ) rgb.getGreen(), ( byte ) rgb.getBlue());
for ( int i = 0 ; i < datas.length; i++) {
row = sheet.createRow(i); //创建表格行
for ( int j = 0 ; j < datas[i].length; j++) {
cell = row.createCell(j); //根据表格行创建单元格
cell.setCellValue(String.valueOf(datas[i][j]));
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//bdIndex 边框颜色下标值
cellStyle.setBottomBorderColor(bdIndex);
cellStyle.setLeftBorderColor(bdIndex);
cellStyle.setRightBorderColor(bdIndex);
cellStyle.setTopBorderColor(bdIndex);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cell.setCellStyle(cellStyle);
}
}
|
单元格文本设置字体样式
单元格文本可设置字体大小、颜色、斜体、粗体、下划线等。
1
2
3
4
5
6
7
8
|
HSSFCellStyle cellStyle = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setItalic( true );
font.setUnderline(HSSFFont.U_SINGLE);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints(( short ) 14 );
cellStyle.setFont(font);
|
合并单元格
sheet中可以类似html合并单元格,指定开始行(从0开始计算)、合并单元格最后行、开始列(从0开始)、 合并单元格最后列四个参数值
1
2
3
4
5
6
|
CellRangeAddress region = new CellRangeAddress( 0 , // first row
0 , // last row
0 , // first column
2 // last column
);
sheet.addMergedRegion(region);
|
单元格中加入图片
单元格中不仅是文本、数值、也可以加入图片,需要指定图片占用单元格开始行数、开始列数、末尾行数、末尾列数。
支持png、jpeg、emf等
1
2
3
4
5
6
7
8
9
10
11
|
//加入图片
byte [] bt = FileUtils.readFileToByteArray( new File( "/Users/mike/pie.png" ));
int pictureIdx = wb.addPicture(bt, Workbook.PICTURE_TYPE_PNG);
CreationHelper helper = wb.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1( 0 ); //图片开始列数
anchor.setRow1( 4 ); //图片开始行数
anchor.setCol2( 3 ); //图片结束列数
anchor.setRow2( 25 ); //图片结束行数
drawing.createPicture(anchor, pictureIdx);
|
excel中插入浮动层图片类似html中div
excel中插入图片, poi导出excel似乎没有按绝对位置X、Y这样插入图片,可以行高和列宽计算X、Y值的大概的位置在哪个单元格中,
然后类似(6)中插入图片,只指定图片开始行数、开始列数,picture.resize()会使图片依据图片实际大小进行扩展。
1
2
3
4
5
6
7
8
9
|
//加入图片
int pictureIdx = wb.addPicture(bt, Workbook.PICTURE_TYPE_PNG);
CreationHelper helper = wb.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1( 0 ); //图片开始列数
anchor.setRow1( 4 ); //图片开始行数
Picture picture = drawing.createPicture(anchor, pictureIdx);
picture.resize();
|
长度转换MSExcelUtil
excel中单元格宽度和高度并不是像素值、ppt值,所以需要转换,MSExcelUtil是一个转换工具类
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
|
public class MSExcelUtil {
public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256 ;
public static final int UNIT_OFFSET_LENGTH = 7 ;
public static final int [] UNIT_OFFSET_MAP = new int [] { 0 , 36 , 73 , 109 , 146 , 182 , 219 };
/**
* pixel units to excel width units(units of 1/256th of a character width)
*
* @param pxs
* @return
*/
public static short pixel2WidthUnits( int pxs) {
short widthUnits = ( short ) (EXCEL_COLUMN_WIDTH_FACTOR * (pxs / UNIT_OFFSET_LENGTH));
widthUnits += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];
return widthUnits;
}
/**
* excel width units(units of 1/256th of a character width) to pixel units
*
* @param widthUnits
* @return
*/
public static int widthUnits2Pixel( int widthUnits) {
int pixels = (widthUnits / EXCEL_COLUMN_WIDTH_FACTOR) * UNIT_OFFSET_LENGTH;
int offsetWidthUnits = widthUnits % EXCEL_COLUMN_WIDTH_FACTOR;
pixels += Math.round(offsetWidthUnits
/ (( float ) EXCEL_COLUMN_WIDTH_FACTOR / UNIT_OFFSET_LENGTH));
return pixels;
}
}
|
完整例子
poi导出excel源码
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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
|
import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
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.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
public final class TestExportExcel {
public static void main(String[] args) throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet( "table" ); //创建table工作薄
Object[][] datas = {{ "区域产品销售额" , "" , "" },{ "区域" , "总销售额(万元)" , "总利润(万元)简单的表格" }, { "江苏省" , 9045 , 2256 }, { "广东省" , 3000 , 690 }};
HSSFRow row;
HSSFCell cell;
short colorIndex = 10 ;
HSSFPalette palette = wb.getCustomPalette();
Color rgb = Color.GREEN;
short bgIndex = colorIndex ++;
palette.setColorAtIndex(bgIndex, ( byte ) rgb.getRed(), ( byte ) rgb.getGreen(), ( byte ) rgb.getBlue());
short bdIndex = colorIndex ++;
rgb = Color.BLACK;
palette.setColorAtIndex(bdIndex, ( byte ) rgb.getRed(), ( byte ) rgb.getGreen(), ( byte ) rgb.getBlue());
for ( int i = 0 ; i < datas.length; i++) {
row = sheet.createRow(i); //创建表格行
for ( int j = 0 ; j < datas[i].length; j++) {
cell = row.createCell(j); //根据表格行创建单元格
cell.setCellValue(String.valueOf(datas[i][j]));
HSSFCellStyle cellStyle = wb.createCellStyle();
if (i == 0 || i == 1 ) {
cellStyle.setFillForegroundColor(bgIndex); //bgIndex 背景颜色下标值
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
}
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//bdIndex 边框颜色下标值
cellStyle.setBottomBorderColor(bdIndex);
cellStyle.setLeftBorderColor(bdIndex);
cellStyle.setRightBorderColor(bdIndex);
cellStyle.setTopBorderColor(bdIndex);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
if (i == datas.length - 1 && j == datas[ 0 ].length - 1 ) {
HSSFFont font = wb.createFont();
font.setItalic( true );
font.setUnderline(HSSFFont.U_SINGLE);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints(( short ) 14 );
cellStyle.setFont(font);
}
cell.setCellStyle(cellStyle);
}
}
//加入图片
byte [] bt = FileUtils.readFileToByteArray( new File( "/Users/mike/pie.png" ));
int pictureIdx = wb.addPicture(bt, Workbook.PICTURE_TYPE_PNG);
CreationHelper helper = wb.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setDx1(MSExcelUtil.pixel2WidthUnits( 60 ));
anchor.setDy1(MSExcelUtil.pixel2WidthUnits( 60 ));
anchor.setCol1( 0 );
anchor.setRow1( 4 );
anchor.setCol2( 3 );
anchor.setRow2( 25 );
drawing.createPicture(anchor, pictureIdx);
//合并单元格
CellRangeAddress region = new CellRangeAddress( 0 , // first row
0 , // last row
0 , // first column
2 // last column
);
sheet.addMergedRegion(region);
//创建表格之后设置行高与列宽
for ( int i = 0 ; i < datas.length; i++) {
row = sheet.getRow(i);
row.setHeightInPoints( 30 );
}
for ( int j = 0 ; j < datas[ 0 ].length; j++) {
sheet.setColumnWidth(j, MSExcelUtil.pixel2WidthUnits( 160 ));
}
wb.write( new FileOutputStream( "/Users/mike/table6.xls" ));
}
}
|