java导出工具,java惯用组件之导出excel工具-ExportExcelUtils

java常用组件之导出excel工具--ExportExcelUtils

/*

* Copyright (c) 2009 Skyon Technology Ltd.

* All rights reserved.

*

* project: ProjectTest

* create: 2009-11-6 上午09:46:13

* cvs: $Id: ExportExcel.java,v 1.10 2010/08/30 02:24:09 dev_cvs Exp $

*/

package cn.ccb.jstsccf.common.utils;

import java.io.BufferedOutputStream;

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.math.BigDecimal;

import org.apache.commons.lang.StringUtils;

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.HSSFRichTextString;

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.CellRangeAddress;

import org.apache.poi.hssf.util.Region;

/**

* 导出execl.

*

* @author ghl

* @version $Revision: 1.10 $

*/

public class ExportExcel {

private HSSFWorkbook wb = null;

private HSSFSheet sheet = null;

private HSSFRow dataRow = null;

/**

* @param wb

* @param sheet

*/

public ExportExcel(String sheetName) {

super();

this.wb = new HSSFWorkbook();

this.sheet = wb.createSheet(sheetName);

}

/**

* @return the sheet

*/

public HSSFSheet getSheet() {

return sheet;

}

/**

* @param sheet

* the sheet to set

*/

public void setSheet(HSSFSheet sheet) {

this.sheet = sheet;

}

/**

* @return the wb

*/

public HSSFWorkbook getWb() {

return wb;

}

/**

* @param wb

* the wb to set

*/

public void setWb(HSSFWorkbook wb) {

this.wb = wb;

}

/**

* @return dataRow

*/

public HSSFRow getDataRow() {

return dataRow;

}

/**

* @param dataRow

* 要设置的 dataRow

*/

public void setDataRow(HSSFRow dataRow) {

this.dataRow = dataRow;

}

/**

* 设置列宽，其中widths中的第二维中第一位表示列行号，第二位表示列宽

*

* @param widths

*/

public void setColumnWith(int[][] widths) {

if (widths == null) {

return;

}

for (int i = 0; i < widths.length; i++) {

int[] column = widths[i];

if (column == null || column.length != 2) {

continue;

}

// 设置列宽

sheet.setColumnWidth(column[0], column[1]);

}

}

/**

* 设置列宽

*

* @param widths

*/

public void setColumnWith(int[] widths) {

if (widths == null) {

return;

}

for (int i = 0; i < widths.length; i++) {

// 设置列宽

sheet.setColumnWidth(i, widths[i]);

}

}

/**

* 设置默认的列宽

*

* @param defaultColumnWith

*/

public void setColumnWith(int defaultColumnWith) {

sheet.setDefaultColumnWidth(defaultColumnWith);

}

/**

* 设置默认的行高

*

* @param defaultRowHight

*/

public void setDefaultRowHight(int defaultRowHight) {

sheet.setDefaultRowHeight((short) defaultRowHight);

}

/**

* 设置默认的行高

*

* @param defaultRowHight

*/

public void setDefaultRowHeightInPoints(float defaultRowHight) {

sheet.setDefaultRowHeightInPoints(defaultRowHight);

}

/**

* 合并单元格

*

* @param firstRow

* @param lastRow

* @param firstCol

* @param lastCol

*/

public void mergedRegion(int firstRow, int lastRow, int firstCol,

int lastCol) {

// 指定合并区域

Region region = new Region((short) firstRow, (short) firstCol,

(short) lastRow, (short) lastCol);

// 设置合并区域的边框

setRegionStyle(region);

sheet.addMergedRegion(region);

// sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow,

// firstCol, lastCol));

}

/**

* 创建行，设置行高

*

* @param rowIndex

* @param rowHight

* 行高，若行高大于0才设置，否则使用默认的

*/

public void createRow(int rowIndex, int rowHight) {

this.dataRow = sheet.createRow(rowIndex);

if (rowHight > 0) {

dataRow.setHeight((short) rowHight);

}

}

/**

* 创建行,默认行高

*

* @param rowIndex

*/

public void createRow(int rowIndex) {

this.dataRow = sheet.createRow(rowIndex);

}

/**

* 设置单元格边框为实线 HSSFCellStyle.BORDER_THIN

*

* @return cellStyle 带边框的单元格样式

*/

public HSSFCellStyle getCellBorderThin() {

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

return cellStyle;

}

/**

* 设置单元格边框为虚线 HSSFCellStyle.BORDER_THIN

*

* @return cellStyle 带边框的单元格样式

*/

public HSSFCellStyle getCellBorderDotted() {

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_DOTTED);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_DOTTED);

return cellStyle;

}

/**

* 设置合并单元格的边框样式

*

* @param region

* 合并的单元格对象

*/

public void setRegionStyle(Region region) {

for (int i = region.getRowFrom(); i <= region.getRowTo(); i++) {

HSSFRow row = sheet.getRow(i);

if (null == row) {

row = sheet.createRow(i);

}

for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {

HSSFCell cell = row.getCell(j);

if (null == cell) {

cell = row.createCell(j);

}

cell.setCellStyle(getCellBorderThin());

}

}

}

/**

* 创建通用EXCEL头部

*

* @param headString

* 头部显示的字符

* @param colSum

* 该报表的列数

* @param headString

* 报报的标题

* @param rowIndex

* 行索引

* @param cellStyle

* 单元格样式

* @param rowHight

* 行高，若行高大于0才设置，否则使用默认的

*/

public void createNormalHead(String headString, int colSum, int rowIndex,

HSSFCellStyle cellStyle, int rowHight) {

HSSFRow row = sheet.createRow(rowIndex);

// 设置第一行

HSSFCell cell = row.createCell(0);

if (rowHight > 0) {

row.setHeight((short) rowHight);

}

// 定义单元格为字符串类型

cell.setCellType(HSSFCell.ENCODING_UTF_16);

cellStyle.setFont(getTitleFont());

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐

cell.setCellValue(new HSSFRichTextString(headString));

// 指定合并区域

mergedRegion(rowIndex, rowIndex, 0, colSum);

if (cellStyle != null) {

cell.setCellStyle(cellStyle);

}

}

/**

* 创建通用报表统计条件

*

* @param params

* 统计条件

* @param colSum

* 需要合并到的列索引

* @param rowIndex

* 创建的行下标

* @param cellStyle

* 单元格样式

* @param rowHight

* 行高，若行高大于0才设置，否则使用默认的

*/

public void createNormalTwoRow(String params, int colSum, int rowIndex,

HSSFCellStyle cellStyle, int rowHight) {

HSSFRow row = sheet.createRow(rowIndex);

if (rowHight > 0) {

row.setHeight((short) rowHight);

}

HSSFCell cell = row.createCell(0);

cell.setCellType(HSSFCell.ENCODING_UTF_16);

cellStyle.setFont(getTitleFont());

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐

if (StringUtils.isNotBlank(params)) {

cell.setCellValue(new HSSFRichTextString(params));

}

// 指定合并区域

mergedRegion(rowIndex, rowIndex, 0, colSum);

if (cellStyle != null) {

cell.setCellStyle(cellStyle);

}

}

/**

* 创建特殊行，每维中第一位为起始列下标(从0开始)，第二位为终止下标，第三位为值

* 如：{{"1","2","移交日期"}},是把第一列和第二列合并，填充的值为移交日期

*

* @param values

* 特殊值

* @param rowIndex

* 创建的行下标

* @param cellStyle

* 单元格样式

* @param rowHight

* 行高，若行高大于0才设置，否则使用默认的

*/

public void createRow(String[][] values, int rowIndex,

HSSFCellStyle cellStyle, int rowHight) {

HSSFRow row = sheet.createRow(rowIndex);

if (rowHight > 0) {

row.setHeight((short) rowHight);

}

if (values == null) {

return;

}

for (int i = 0; i < values.length; i++) {

String[] value = values[i];

if (value == null || value.length != 3) {

continue;

}

HSSFCell cell = row.createCell(Integer.parseInt(value[0]));

cell.setCellValue(new HSSFRichTextString(value[2]));

// 指定合并区域

mergedRegion(rowIndex, rowIndex, Integer.parseInt(value[0]),

Integer.parseInt(value[1]));

if (cellStyle != null) {

cell.setCellStyle(cellStyle);

}

}

}

/**

* 创建通用报表第二行

*

* @param condition

* @param params

* 统计条件数组

* @param colSum

* 需要合并到的列索引

* @param cellStyle

* 单元格样式

* @param rowHight

* 行高，若行高大于0才设置，否则使用默认的

*/

public void createNormalTwoRow(String[] condition, String[] params,

int colSum, HSSFCellStyle cellStyle, int rowHight) {

HSSFRow row = sheet.createRow(1);

if (rowHight > 0) {

row.setHeight((short) rowHight);

}

HSSFCell cell = row.createCell(0);

cell.setCellType(HSSFCell.ENCODING_UTF_16);

if (params == null || params.length == 0) {

} else if (params.length == 1) {

cell.setCellValue(new HSSFRichTextString(condition[0] + params[0]));

} else {

cell.setCellValue(new HSSFRichTextString(condition[0] + params[0]

+ " " + condition[1] + params[1]));

}

// 指定合并区域

mergedRegion(1, 1, 0, colSum);

if (cellStyle != null) {

cell.setCellStyle(cellStyle);

}

}

/**

* 获取默认的样式

*

* @return

*/

public HSSFCellStyle getDefaultCellStyle() {

HSSFCellStyle cellStyle = wb.createCellStyle();

return cellStyle;

}

/**

* 获取居中的样式

*

* @return

*/

public HSSFCellStyle getCenterCellStyle() {

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐

cellStyle.setWrapText(true);// 指定单元格自动换行

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

/*

* cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单无格的边框为粗体

* cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．

* cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

* cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);

* cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

* cellStyle.setRightBorderColor(HSSFColor.BLACK.index);

* cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

* cellStyle.setTopBorderColor(HSSFColor.BLACK.index); // 设置单元格背景色

* cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

* cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

*/

return cellStyle;

}

/**

* 获取默认的字体

*

* @return

*/

public HSSFFont getDefaultFont() {

HSSFFont font = wb.createFont();

return font;

}

/**

* 获取宋体黑体的字体

*

* @return

*/

public HSSFFont getTitleFont() {

HSSFFont font = wb.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

font.setFontName("宋体");

font.setFontHeightInPoints((short) 12);

return font;

}

/**

* 设置报表标题

*

* @param columHeader

* 标题字符串数组

* @param rowIndex

* 行索引

* @param mergedRegion

* 合并的区域

* @param cellStyle

* 单元格样式

* @param rowHight

* 行高，若行高大于0才设置，否则使用默认的

*/

public void createColumHeader(String[] columHeader, int rowIndex,

int[][] mergedRegion, HSSFCellStyle cellStyle, int rowHight) {

// 设置列头

HSSFRow row = sheet.createRow(rowIndex);

// 指定行高

if (rowHight > 0) {

row.setHeight((short) rowHight);

}

HSSFCell cell = null;

for (int i = 0; i < columHeader.length; i++) {

cell = row.createCell(i);

cell.setCellType(HSSFCell.ENCODING_UTF_16);

if (cellStyle != null) {

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

cellStyle.setFont(getTitleFont());

cellStyle.setWrapText(true);// 指定单元格自动换行

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐

cell.setCellStyle(cellStyle);

} else {

cell.setCellStyle(getCellBorderThin());

}

cell.setCellValue(new HSSFRichTextString(columHeader[i]));

}

// 合并区域

if (mergedRegion != null) {

for (int i = 0; i < mergedRegion.length; i++) {

int[] js = mergedRegion[i];

if (js == null || js.length != 2) {

continue;

}

// 指定合并区域

mergedRegion(rowIndex, rowIndex, js[0], js[1]);

}

}

}

/**

* 创建内容单元格

*

* @param col

* short型的列索引

* @param val

* 列值

*/

public void cteateCell(int col, String val, HSSFCellStyle cellStyle) {

HSSFCell cell = dataRow.createCell(col);

cell.setCellType(HSSFCell.ENCODING_UTF_16);

cell.setCellValue(new HSSFRichTextString(val));

if (cellStyle != null) {

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

cell.setCellStyle(cellStyle);

} else {

cell.setCellStyle(getCellBorderThin());

}

}

/**

* 创建内容单元格，使用的是默认的样式

*

* @param col

* short型的列索引

* @param val

* 列值

*/

public void cteateCell(int col, String val) {

HSSFCell cell = dataRow.createCell(col);

cell.setCellType(HSSFCell.ENCODING_UTF_16);

cell.setCellValue(new HSSFRichTextString(val));

cell.setCellStyle(getCenterCellStyle());

}

/**

* 创建内容单元格

*

* @param col

* short型的列索引

* @param val

* 列值

*/

public void cteateCell(int col, long val, HSSFCellStyle cellStyle) {

HSSFCell cell = dataRow.createCell(col);

cell.setCellType(HSSFCell.ENCODING_UTF_16);

cell.setCellValue(val);

if (cellStyle != null) {

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

cell.setCellStyle(cellStyle);

} else {

cell.setCellStyle(getCellBorderThin());

}

}

/**

* 创建内容单元格，使用的是默认的样式

*

* @param col

* short型的列索引

* @param val

* 列值

*/

public void cteateCell(int col, long val) {

HSSFCell cell = dataRow.createCell(col);

cell.setCellType(HSSFCell.ENCODING_UTF_16);

cell.setCellValue(val);

cell.setCellStyle(getCenterCellStyle());

}

/**

* 创建内容单元格

*

* @param col

* short型的列索引

* @param val

* 列值

*/

public void cteateCell(int col, BigDecimal val, HSSFCellStyle cellStyle) {

HSSFCell cell = dataRow.createCell(col);

cell.setCellType(HSSFCell.ENCODING_UTF_16);

if (val != null) {

cell.setCellValue(val.doubleValue());

}

if (cellStyle != null) {

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

cell.setCellStyle(cellStyle);

} else {

cell.setCellStyle(getCellBorderThin());

}

}

/**

* 创建内容单元格，使用的是默认的样式

*

* @param col

* short型的列索引

* @param val

* 列值

*/

public void cteateCell(int col, BigDecimal val) {

HSSFCell cell = dataRow.createCell(col);

cell.setCellType(HSSFCell.ENCODING_UTF_16);

if (val != null) {

cell.setCellValue(val.doubleValue());

}

cell.setCellStyle(getCenterCellStyle());

}

/**

* 创建合计行

*

* @param colSum

* 需要合并到的列索引

* @param cellValue

* @param cellStyle

* 单元格样式

* @param rowHight

* 行高，若行高大于0才设置，否则使用默认的

*/

public void createLastSumRow(int colSum, Object[] cellValue,

HSSFCellStyle cellStyle, int rowHight) {

HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));

HSSFCell sumCell = lastRow.createCell(0);

if (rowHight > 0) {

lastRow.setHeight((short) rowHight);

}

sumCell.setCellValue(new HSSFRichTextString("合计"));

if (cellStyle != null) {

sumCell.setCellStyle(cellStyle);

} else {

sumCell.setCellStyle(getCellBorderThin());

}

mergedRegion(sheet.getLastRowNum(), sheet.getLastRowNum(), (short) 0,

(short) colSum);// 指定合并区域

int m = colSum + 1;

for (int i = m; i < (cellValue.length + m); i++) {

sumCell = lastRow.createCell(i);

if (cellStyle != null) {

sumCell.setCellStyle(cellStyle);

} else {

sumCell.setCellStyle(getCellBorderThin());

}

Object cell = cellValue[i - m];

if (cell instanceof String) {

sumCell.setCellValue(new HSSFRichTextString(((String) cell)));

} else if (cell instanceof Long) {

sumCell.setCellValue(((Long) cell).longValue());

} else if (cell instanceof BigDecimal) {

sumCell.setCellValue(((BigDecimal) cell).doubleValue());

}

}

}

/**

* 创建合计行

*

* @param colSum

* 需要合并到的列索引

* @param cellValue

* @param cellStyle

* 单元格样式

* @param rowHight

* 行高，若行高大于0才设置，否则使用默认的

*/

public void createLastSumRow(Object[] cellValue, HSSFCellStyle cellStyle,

int rowHight) {

HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));

if (rowHight > 0) {

lastRow.setHeight((short) rowHight);

}

HSSFCell sumCell = null;

for (int j = 0; j < cellValue.length; j++) {

sumCell = lastRow.createCell(j);

if (cellStyle != null) {

sumCell.setCellStyle(cellStyle);

} else {

sumCell.setCellStyle(getCellBorderThin());

}

Object cell = cellValue[j];

if (cell instanceof String) {

sumCell.setCellValue(new HSSFRichTextString(((String) cell)));

} else if (cell instanceof Long) {

sumCell.setCellValue(((Long) cell).longValue());

} else if (cell instanceof BigDecimal) {

sumCell.setCellValue(((BigDecimal) cell).doubleValue());

}

}

}

/**

* 输入EXCEL文件

*

* @param os

* 输出流

* @throws IOException

*/

public void write(OutputStream os) throws IOException {

wb.write(os);

}

/**

* 输入EXCEL文件

*

* @param os

* 输出流

* @throws IOException

*/

public void write(String path) throws IOException {

File f = new File(path);

File parentFile = f.getParentFile();

if (!parentFile.exists()) {

parentFile.mkdirs();

}

if (!f.exists()) {

f.createNewFile();

}

FileOutputStream fos = new FileOutputStream(path);

BufferedOutputStream bos = new BufferedOutputStream(fos);

wb.write(bos);

if (fos != null) {

fos.flush();

fos.close();

}

if (bos != null) {

bos.flush();

bos.close();

}

}

}