<div id="article_content" class="article_content clearfix">
<link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/ck_htmledit_views-b5506197d8.css">
<div id="content_views" class="htmledit_views">
<p>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</p>
输出表格
poi输出excel最基本是输出table表格，下面是输出区域、总销售额(万元)、总利润(万元)简单的表格,
创建HSSFWorkbook 对象，用于将excel输出到输出流中
-
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宽度并不是像素需要转换
-
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
-
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);
-
-
}
-
-
}
单元格文本设置字体样式
单元格文本可设置字体大小、颜色、斜体、粗体、下划线等。
-
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开始)、 合并单元格最后列四个参数值
-
CellRangeAddress region =
new CellRangeAddress(
0,
// first row
-
0,
// last row
-
0,
// first column
-
2
// last column
-
);
-
sheet.addMergedRegion(region);
单元格中加入图片
单元格中不仅是文本、数值、也可以加入图片，需要指定图片占用单元格开始行数、开始列数、末尾行数、末尾列数。
支持png、jpeg、emf等
-
//加入图片
-
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()会使图片依据图片实际大小进行扩展。
-
//加入图片
-
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是一个转换工具类
-
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源码
-
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"));
-
}
-
}
扫一扫
1558
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
