java excel导出控件_Java使用poi组件导出excel报表

1，需要的jar包：poi-3.8-20120326.jar,poi-ooxml-3.8-20120326.jar,poi-scratchpad-3.8-20120326.jar

2.输出表格：

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

3.设置表格行高、列宽

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

4.设置excel单元格样式

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

}

}

5.合并单元格

sheet中可以类似html合并单元格,指定开始行(从0开始计算)、合并单元格最后行、开始列(从0开始)、

合并单元格最后列四个参数值

poi导出excel

CellRangeAddress region = new

CellRangeAddress(0, // first row

0, // last row

0, // first column

2 // last column

);

sheet.addMergedRegion(region);

6.单元格中加入图片

单元格中不仅是文本、数值、也可以加入图片，需要指定图片占用单元格开始行数、开始列数、末尾行数、末尾列数。

支持png、jpeg、emf等

poi导出excel

//加入图片

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

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

8.单元格中画斜线：excel单元格中画斜线另一篇有详细介绍：poi excel斜线表头

9.长度转换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 };

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;

}

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;

}

}

10.完整列子

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

}

}