jxl 作的报表通用方法
jxl 做的报表通用方法
ExcelUtilToolTest.java
package com.sobey.pms.util.excel;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import junit.framework.TestCase;
/**
* <p>Title: TestExcelUtilTool.java</p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2009 Sobey,Inc</p>
* <p>Company: Sobey.Inc</p>
* @author yaobo 2010-8-9
* @version 1.0
*/
public class ExcelUtilToolTest extends TestCase{
public void testCreateExcel(){
List datas = new ArrayList();
for (int i = 0; i < 10; i++){
Data data = new Data("名字" + i, "地址" + i, "公司" + i);
datas.add(data);
}
ExcelExportInfo info=null;
String footer[][]=new String[1][2];
footer[0][0]=""; //footer[0][0] 是文本内容,footer[0][1]为文本占的列数
footer[0][1]="3";
info = new ExcelExportInfo();
info.setColumnHeader("名字, 地址, 公司");
info.setDatas(datas); //list (是po\vo)
info.setFeilds("name,address,company"); // list 集合中对象的feilds
info.setTitle("人员信息"); //设置title,如果为空则excel无标题
info.setFooter(footer); // footer
info.setWidth(new int[]{50, 50, 50}); // int数组为int类型, 为null 取默认宽度20,
ExcelUtilTool util=new ExcelUtilTool();
ByteArrayOutputStream tt = util.writeExcelStream(info);
try{
FileOutputStream pos = new FileOutputStream("c:/test.xls");
pos.write(tt.toByteArray());
tt.close();
pos.close();
}catch (Exception ex){
assert(false);
ex.printStackTrace();
}
assert(true);
}
}
class Data{
String name;
String address;
String company;
public Data(String name, String address, String company) {
super();
this.name = name;
this.address = address;
this.company = company;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getCompany() {
return company;
}
public void setCompany(String company) {
this.company = company;
}
}
ExcelUtilTool.java
package com.sobey.pms.util.excel;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.apache.log4j.Logger;
import com.sobey.esda.utils.ReflectionUtils;
/**
*
* 通用Excel处理类
*
*
*/
public class ExcelUtilTool {
/**
* Logger for this class
*/
private static final Logger logger = Logger.getLogger(ExcelUtilTool.class);
/**
* 得到 Excel 报表数据的字节
*
* @param reportTitle
* 报表的标题
*
* @param dataInArr
* String[][] 输入的待显示数据数组
* @param param_width
* int[] 每列列宽
*
* @param columnHeader
* 列头标题
*
* @param columnFooter
* 脚眉 [0]=数据,[1]=表示占有宽度 为 1,2,3,..
*
* @param report_format
* String[] 报表显示 格式数组{"","",""}
* @throws Exception
*/
public byte[] writeExcelReport(String ReportTitle[][],
String columnHeader[], String dataInArr[][],
String columnFooter[][], int param_width[]) throws Exception {
ByteArrayOutputStream bufferOb = null; // 直接输出到流中
bufferOb = writeExcelStream(ReportTitle, columnHeader, dataInArr,
columnFooter, param_width);
if (bufferOb != null)
return bufferOb.toByteArray();
else
return null;
}
/**
*
* @param ops
* @return
*/
private WritableWorkbook createWorkBook(OutputStream ops) {
try {
return Workbook.createWorkbook(ops);
} catch (IOException e) {
e.printStackTrace();
return null;
}
}
/**
*
* @param workbook
* @param sheetName
* @param index
* @return
*/
private WritableSheet createSheet(WritableWorkbook workbook,
String sheetName, int index) {
if (workbook == null)
return null;
return workbook.createSheet(sheetName, index);
}
/**
*
* @param ws
* @param reportTitle
* @throws WriteException
* @throws RowsExceededException
*/
private void createExcelTitle(WritableSheet ws, String reportTitle[][],
int len) throws RowsExceededException, WriteException {
if (ws == null || reportTitle == null)
return;
WritableFont boldBigWTF = new WritableFont(WritableFont.ARIAL, 30,
WritableFont.BOLD);
WritableFont boldNormalWTF = new WritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);
// 填充报表标题,一维是字串，二维是格式(对齐方式):ReportTitle[x][2],对齐方式：left,center,right
for (int i = 0; i < reportTitle.length; i++) {
ws.mergeCells(0, i, (len - 1), i); // 合并标题单元格
WritableCellFormat reportheader = null;
if (i == 0) {
reportheader = new WritableCellFormat(boldBigWTF);
} else {
reportheader = new WritableCellFormat(boldNormalWTF);
}
reportheader.setBorder(Border.NONE, BorderLineStyle.NONE);
if (reportTitle[i][1].equalsIgnoreCase("left")) {
reportheader.setAlignment(Alignment.LEFT);
} else if (reportTitle[i][1].equalsIgnoreCase("center")) {
reportheader.setAlignment(Alignment.CENTRE);
} else if (reportTitle[i][1].equalsIgnoreCase("right")) {
reportheader.setAlignment(Alignment.RIGHT);
}
Label lable = new Label(0, i, reportTitle[i][0], reportheader);
ws.addCell(lable);
lable = null;
}
}
/**
*
* @param ws
* @param columnHeader
* @param list_start
* @throws WriteException
*/
private void createColumnHeader(WritableSheet ws, String columnHeader[],
int list_start) throws WriteException {
if (ws == null || columnHeader == null)
return;
WritableFont wfc = new WritableFont(WritableFont.ARIAL); // 设置字体对象
WritableCellFormat cellheader = new WritableCellFormat(wfc);
cellheader.setBackground(Colour.GRAY_25);
cellheader.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
// 填充columnHeader字段及样式
for (int i = 0; i < columnHeader.length; i++) {
Label lable = new Label(i, list_start, columnHeader[i], cellheader);
ws.addCell(lable);
lable = null;
}
}
/**
*
* @param ws
* @param data
* @param list_start
* @throws WriteException
*/
private void fillInData(WritableSheet ws, String data[][], int list_start)
throws WriteException {
if (ws == null || data == null)
return;
int dataInArr_row = data.length;
int dataInArr_col = data[0].length;
WritableFont wfc = new WritableFont(WritableFont.ARIAL); // 设置字体对象
WritableCellFormat wcfFC = new WritableCellFormat(wfc);
for (int i = 0; i < dataInArr_row; i++) {
for (int j = 0; j < dataInArr_col; j++) {
Label lable = new jxl.write.Label(j, (i + list_start + 1),
data[i][j], wcfFC);
ws.addCell(lable);
lable = null;
}
}
}
/**
*
* @param ws
* @param columnFooter
* @param rowStart
* @throws WriteException
* @throws RowsExceededException
*/
private void createFooter(WritableSheet ws, String columnFooter[][],
int rowStart) throws RowsExceededException, WriteException {
if (ws == null || columnFooter == null)
return;
// 填充columnFooter字段及统计,一维是字串，二维是格式(跨度):columnFooter[x][2]
WritableFont wfc = new WritableFont(WritableFont.ARIAL); // 设置字体对象
WritableCellFormat wcfFC = new WritableCellFormat(wfc);
if (columnFooter != null) {
int columnFooterWidth = columnFooter.length;
int tmpInt = 0, summergeNum = 0;
for (int i = 0; i < columnFooterWidth; i++) {
tmpInt = Integer.parseInt(columnFooter[i][1]);
summergeNum += tmpInt;
if (tmpInt > 1) {
ws.mergeCells((summergeNum - tmpInt), (rowStart + 1),
(summergeNum - 1), (rowStart + 1)); // 合并单元格
}
Label lable = new Label((summergeNum - tmpInt), (rowStart + 1),
columnFooter[i][0], wcfFC);
ws.addCell(lable);
lable = null;
}
}
}
/**
*
* @param info
* @return
* @throws Exception
*/
public ByteArrayOutputStream writeExcelStream(ExcelExportInfo info) {
if (logger.isDebugEnabled()) {
logger.debug("writeExcelStream(ExcelExportInfo) - start");
}
if (info == null || info.getDatas() == null
|| info.getColumnHeader() == null || info.getFeilds() == null) {
if (logger.isDebugEnabled()) {
logger.debug("info is null");
logger.debug("writeExcelStream(ExcelExportInfo) - end");
}
return null;
}
String title[][] = null;
String datas[][] = null;
String feilds[] = info.getFeilds();
List list = info.getDatas();
int rows = list.size();
int columns = info.getFeilds().length;
if (logger.isDebugEnabled()) {
logger.debug(" rows : " + rows + " columns:" + columns);
}
if (rows == 0 || columns == 0)
return null;
if (info.getTitle() != null) {
title = new String[1][2];
title[0][0] = info.getTitle();
title[0][1] = "center";
if (title[0][0] == null)
title[0][0] = "Excel报表打印";
}
datas = new String[rows][];
for (int i = 0; i < rows; i++) {
Object obj = list.get(i);
if (obj == null)
continue;
datas[i] = new String[columns];
for (int j = 0; j < columns; j++) {
datas[i][j] = getParameter(obj, feilds[j]);
}
}
ByteArrayOutputStream returnByteArrayOutputStream = null;
try {
returnByteArrayOutputStream = this.writeExcelStream(title, info
.getColumnHeader(), datas, info.getFooter(), info
.getWidth());
} catch (Exception e) {
e.printStackTrace();
}
if (logger.isDebugEnabled()) {
logger.debug("writeExcelStream(ExcelExportInfo) - end");
}
return returnByteArrayOutputStream;
}
/**
*
* @param obj
* @param attr
* @return
*/
private String getParameter(Object obj, String attr) {
String value = null;
Object valuObj = ReflectionUtils.invokeGetterMethod(obj, attr);
value = (valuObj == null) ? null : valuObj.toString();
return value;
}
/**
*
* 得到excel报表数据的字节流
*
* @param ReportTitle
* @param columnHeader
* @param dataInArr
* @param columnFooter
* @param param_width
* @throws Exception
*/
public ByteArrayOutputStream writeExcelStream(String reportTitle[][],
String columnHeader[], String datas[][], String columnFooter[][],
int paramWidth[]) throws Exception {
ByteArrayOutputStream bufferOb = new ByteArrayOutputStream(); // 直接输出到字节流中(内存中)
int i = 0;
int list_start = 3; // 列表数据从第4行开始写(默认)
int default_column_width = 20; // 表格默认宽度
int dataInArr_row = (datas == null) ? 0 : datas.length; // 得到数组行数
int dataInArr_col = (datas == null) ? 0 : datas[0].length; // 得到数组列数
if (dataInArr_col == 0 && paramWidth != null) {
dataInArr_col = paramWidth.length;
} else if (dataInArr_col == 0)
dataInArr_col = 1;
if (paramWidth == null) {
paramWidth = new int[dataInArr_col];
for (i = 0; i < dataInArr_col; i++) {
paramWidth[i] = default_column_width;
}
}
WritableWorkbook wwb = createWorkBook(bufferOb); // 建立excel工作空间
WritableSheet ws = createSheet(wwb, "报表显示(打印)", 0); // 建立报表表单
// 设置表格宽度
for (i = 0; i < dataInArr_col; i++) {
ws.setColumnView(i, paramWidth[i]);
}
// 填充报表标题,一维是字串，二维是格式(对齐方式):ReportTitle[x][2],对齐方式：left,center,right
int reportTitleLen = reportTitle == null ? 0 : reportTitle.length;
list_start = reportTitleLen;
this.createExcelTitle(ws, reportTitle, dataInArr_col);
this.createColumnHeader(ws, columnHeader, list_start); // list_start从多少行开始写数据
this.fillInData(ws, datas, list_start);
this.createFooter(ws, columnFooter, dataInArr_row + list_start);
wwb.write();
wwb.close();
reportTitle = null;
columnHeader = null;
datas = null;
columnFooter = null;
paramWidth = null;
return bufferOb;
}
/**
*
* @param info
* @return
* @throws Exception
*/
public void writeExcel(OutputStream stream, ExcelExportInfo info) {
if (logger.isDebugEnabled()) {
logger.debug("writeExcelStream(ExcelExportInfo) - start");
}
if (info == null || info.getDatas() == null
|| info.getColumnHeader() == null || info.getFeilds() == null) {
if (logger.isDebugEnabled()) {
logger.debug("info is null");
logger.debug("writeExcelStream(ExcelExportInfo) - end");
}
return;
}
String title[][] = null;
String datas[][] = null;
String feilds[] = info.getFeilds();
List list = info.getDatas();
int rows = list.size();
int columns = info.getFeilds().length;
if (logger.isDebugEnabled()) {
logger.debug(" rows : " + rows + " columns:" + columns);
}
if (rows == 0 || columns == 0)
return;
if (info.getTitle() != null) {
title = new String[1][2];
title[0][0] = info.getTitle();
title[0][1] = "center";
if (title[0][0] == null)
title[0][0] = "Excel报表打印";
}
datas = new String[rows][];
for (int i = 0; i < rows; i++) {
Object obj = list.get(i);
if (obj == null)
continue;
datas[i] = new String[columns];
for (int j = 0; j < columns; j++) {
datas[i][j] = getParameter(obj, feilds[j]);
}
}
try {
this.writeExcel(stream, title, info.getColumnHeader(), datas, info
.getFooter(), info.getWidth());
} catch (Exception e) {
e.printStackTrace();
}
if (logger.isDebugEnabled()) {
logger.debug("writeExcelStream(ExcelExportInfo) - end");
}
}
/**
*
* 得到excel报表数据的字节流
*
* @param ReportTitle
* @param columnHeader
* @param dataInArr
* @param columnFooter
* @param param_width
* @throws Exception
*/
public void writeExcel(OutputStream stream, String reportTitle[][],
String columnHeader[], String datas[][], String columnFooter[][],
int paramWidth[]) throws Exception {
int i = 0;
int list_start = 3; // 列表数据从第4行开始写(默认)
int default_column_width = 20; // 表格默认宽度
int dataInArr_row = (datas == null) ? 0 : datas.length; // 得到数组行数
int dataInArr_col = (datas == null) ? 0 : datas[0].length; // 得到数组列数
if (dataInArr_col == 0 && paramWidth != null) {
dataInArr_col = paramWidth.length;
} else if (dataInArr_col == 0)
dataInArr_col = 1;
if (paramWidth == null) {
paramWidth = new int[dataInArr_col];
for (i = 0; i < dataInArr_col; i++) {
paramWidth[i] = default_column_width;
}
}
WritableWorkbook wwb = createWorkBook(stream); // 建立excel工作空间
WritableSheet ws = createSheet(wwb, "报表显示(打印)", 0); // 建立报表表单
// 设置表格宽度
for (i = 0; i < dataInArr_col; i++) {
ws.setColumnView(i, paramWidth[i]);
}
// 填充报表标题,一维是字串，二维是格式(对齐方式):ReportTitle[x][2],对齐方式：left,center,right
int reportTitleLen = reportTitle == null ? 0 : reportTitle.length;
list_start = reportTitleLen;
this.createExcelTitle(ws, reportTitle, dataInArr_col);
this.createColumnHeader(ws, columnHeader, list_start); // list_start从多少行开始写数据
this.fillInData(ws, datas, list_start);
this.createFooter(ws, columnFooter, dataInArr_row + list_start);
wwb.write();
wwb.close();
reportTitle = null;
columnHeader = null;
datas = null;
columnFooter = null;
paramWidth = null;
}
/**
* 读excel表格中文字
*
* @param fileName
* @param x
* @param y
* @throws Exception
*/
public String readExcelCell(String fileName, int x, int y) throws Exception {
File f = new File(fileName);
InputStream os = new FileInputStream(f);
jxl.Workbook book = Workbook.getWorkbook(os);
jxl.Sheet sheet = book.getSheet(0);
Cell cell1 = sheet.getCell(x, y);
String result = cell1.getContents();
book.close();
return result;
}
/**
* 读excel表格中文字
*
* @param book
* @param x
* @param y
* @throws Exception
*/
public String readExcelCell(Workbook book, int x, int y) throws Exception {
jxl.Sheet sheet = book.getSheet(0);
Cell cell1 = sheet.getCell(x, y);
String result = cell1.getContents();
return result;
}
/**
* 把excel数据读入到数组中
*
* @param fileName
* @param sheetNo
* 工作表顺序号,默认为0
* @param x_start
* 左面开始的列数
* @param y_start
* 头开始的行数
* @param width
* 如果宽度为0(默认),就读出所有列
* @param height
* 如果高度为0(默认),就读出所有行
* @throws Exception
*/
public String[][] readExcel(String fileName, int sheetNo, int x_start,
int y_start, int columns, int rows) throws Exception {
// 数据校正
if (sheetNo < 0)
sheetNo = 0;
if (x_start < 0)
x_start = 0;
if (y_start < 0)
y_start = 0;
Workbook book = null;
Sheet sheet = null; // 获得第一个工作表对象
int tmpInt = 0;
book = this.getWorkBook(fileName);
if (book == null)
return null;
sheet = book.getSheet(sheetNo);
tmpInt = sheet.getColumns();
if (columns == 0 || columns > tmpInt)
columns = tmpInt;
tmpInt = sheet.getRows();
if (rows == 0 || rows > tmpInt)
rows = tmpInt;
String[][] reArr = new String[rows][columns];
for (int y = 0; y < rows; y++) {
for (int x = 0; x < columns; x++) {
int cel_x = x_start + x;
int cel_y = y_start + y;
reArr[y][x] = sheet.getCell(cel_x, cel_y).getContents(); // cel_x,列
// cel_y
// 行
}
}
book.close();
return reArr;
}
/**
* 把excel数据读入到数组中
*
* @param inputstream
* @param sheetNo
* 工作表顺序号,默认为0
* @param x_start
* 左面开始的列数
* @param y_start
* 头开始的行数
* @param width
* 如果宽度为0(默认),就读出所有列
* @param height
* 如果高度为0(默认),就读出所有行
* @throws Exception
*/
public String[][] readExcel(InputStream ins, int sheetNo, int x_start,
int y_start, int width, int height) throws Exception {
// 数据校正
if (sheetNo < 0)
sheetNo = 0;
if (x_start < 0)
x_start = 0;
if (y_start < 0)
y_start = 0;
Workbook book = null;
Sheet sheet = null; // 获得第一个工作表对象
int tmpInt = 0;
book = this.getWorkBook(ins);
if (book == null)
return null;
sheet = book.getSheet(sheetNo);
tmpInt = sheet.getColumns();
if (width == 0 || width > tmpInt)
width = tmpInt;
tmpInt = sheet.getRows();
if (height == 0 || height > tmpInt)
height = tmpInt;
String[][] reArr = new String[width][height];
int x = 0, y = 0;
for (x = x_start; x < (x_start + width); x++) {
for (y = y_start; y < (y_start + height); y++) {
reArr[x][y] = sheet.getCell(x, y).getContents();
}
}
book.close();
return reArr;
}
/**
*
* @param ins
* @return
*/
public Workbook getWorkBook(InputStream ins) {
Workbook book = null;
try {
book = Workbook.getWorkbook(ins);
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return book;
}
/**
*
* @param ins
* @return
*/
public Workbook getWorkBook(String fileName) {
File file = null;
InputStream is = null;
if (fileName == null)
return null;
file = new File(fileName);
if (!file.exists())
return null;
try {
is = new FileInputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
return this.getWorkBook(is);
}
/**
* 写数组数据到excel
*
* @param fileName
* 写入的excel文件明
* @param sheetNo
* 工作表顺序号,默认为0
* @param sheetName
* 工作表名
* @param x_start
* 左面开始的列数
* @param y_start
* 头开始的行数
* @param columnW
* 列宽
* @param inputArr
* 输入的数组
* @param maxPerPage
* 每页最大记录数(最大记录数为10000)
* @throws Exception
*/
public void writeExcel(String fileName, int sheetNo, String sheetName,
int x_start, int y_start, int columnW, String[][] inputArr,
int maxPerPage) throws Exception {
// 数据校正
if (sheetNo < 0)
sheetNo = 0;
if (sheetName == null)
sheetName = "数据列表";
if (x_start < 0)
x_start = 0;
if (y_start < 0)
y_start = 0;
if (columnW < 0)
columnW = 10;
if (maxPerPage < 0 || maxPerPage > 10000) {
maxPerPage = 10000;
System.out.println("每页最大记录数超出最大数10000,改为10000输出");
}
// 数据写入excel
int dataStart = 0; // 数据行开始
int dataEnd = 0; // 数据行结束
if (inputArr != null && inputArr.length > 0) {
int inputArrLength = inputArr[0].length; // 数据行数
int loop = 0;
if (inputArrLength % maxPerPage == 0) {
loop = inputArrLength / maxPerPage;
} else {
loop = inputArrLength / maxPerPage + 1;
}
int baseYstart = y_start;
String fileExtName = fileName.substring(fileName.lastIndexOf("."));
String filePathName = fileName.replaceAll(fileExtName, "");
for (int i = 0; i < loop; i++) {
dataStart = i * maxPerPage;
y_start = baseYstart + dataStart;
dataEnd = dataStart + maxPerPage;
if (dataEnd >= inputArrLength) {
dataEnd = inputArrLength;
}
if (i > 0) {
fileName = filePathName + " (" + i + ")" + fileExtName;
}
writeExcel(fileName, sheetNo, sheetName, x_start, baseYstart,
columnW, inputArr, dataStart, dataEnd);
}
}
inputArr = null;
}
/**
* 写数组数据到excel
*
* @param fileName
* @param sheetNo
* @param sheetName
* @param x_start
* @param y_base
* @param columnW
* @param inputArr
* @param dataStart
* @param dataEnd
* @throws Exception
*/
private void writeExcel(String fileName, int sheetNo, String sheetName,
int x_start, int y_base, int columnW, String[][] inputArr,
int dataStart, int dataEnd) throws Exception {
File os = new File(fileName);
if (os.exists()) {
if (!os.delete()) {
throw new Exception("文件已经存在,并且不能删除");
}
}
WritableWorkbook book = null;
WritableSheet ws = null;
if (os.createNewFile()) {
book = Workbook.createWorkbook(os);
ws = book.createSheet(sheetName, sheetNo);
} else {
throw new Exception("文件创建失败");
}
if (inputArr != null && inputArr.length > 0) {
int inputArrWidth = inputArr.length;
int x = 0, y = 0;
for (x = 0; x < inputArrWidth; x++) {
ws.setColumnView((x + x_start), columnW); // 设置列宽
for (y = dataStart; y < dataEnd; y++) {
Label lb = new Label((x + x_start),
(y - dataStart + y_base), inputArr[x][y]);
ws.addCell(lb);
}
}
}
book.write();
book.close();
inputArr = null;
}
/**
* 写数组数据到excel文件中
*
* @param sheetNo
* @param sheetName
* @param x_start
* @param y_base
* @param columnW
* @param inputArr
* @throws Exception
*/
public void writeExcel(String fileName, int sheetNo, String sheetName,
int x_start, int y_base, int columnW, String[][] inputArr)
throws Exception {
File os = new File(fileName);
if (os.exists()) {
if (!os.delete()) {
throw new Exception("文件已经存在,并且不能删除");
}
}
WritableWorkbook book = null;
WritableSheet ws = null;
if (os.createNewFile()) {
book = Workbook.createWorkbook(os);
ws = book.createSheet(sheetName, sheetNo);
} else {
throw new Exception("文件创建失败");
}
if (inputArr != null && inputArr.length > 0) {
int inputArrWidth = inputArr.length;
int inputArrHeight = inputArr[0].length;
int x = 0, y = 0;
for (x = 0; x < inputArrWidth; x++) {
ws.setColumnView((x + x_start), columnW); // 设置列宽
for (y = 0; y < inputArrHeight; y++) {
Label lb = new Label((x + x_start), (y + y_base),
inputArr[x][y]);
ws.addCell(lb);
}
}
}
book.write();
book.close();
inputArr = null;
}
/**
* 写数组数据到excel流中
*
* @param sheetNo
* @param sheetName
* @param x_start
* @param y_base
* @param columnW
* @param inputArr
* @throws Exception
*/
public ByteArrayOutputStream writeExcelStream(int sheetNo,
String sheetName, int x_start, int y_base, int columnW,
String[][] inputArr) throws Exception {
ByteArrayOutputStream os = new ByteArrayOutputStream();
WritableWorkbook book = null;
WritableSheet ws = null;
book = Workbook.createWorkbook(os);
ws = book.createSheet(sheetName, sheetNo);
if (inputArr != null && inputArr.length > 0) {
int inputArrWidth = inputArr.length;
int inputArrHeight = inputArr[0].length;
int x = 0, y = 0;
for (x = 0; x < inputArrWidth; x++) {
ws.setColumnView((x + x_start), columnW); // 设置列宽
for (y = 0; y < inputArrHeight; y++) {
Label lb = new Label((x + x_start), (y + y_base),
inputArr[x][y]);
ws.addCell(lb);
}
}
}
book.write();
book.close();
inputArr = null;
return os;
}
public byte[] writeExcel(int sheetNo, String sheetName, int x_start,
int y_base, int columnW, String[][] inputArr) throws Exception {
return writeExcelStream(sheetNo, sheetName, x_start, y_base, columnW,
inputArr).toByteArray();
}
}
ExcelExportInfo.java
package com.sobey.pms.util.excel;
import java.util.List;
public class ExcelExportInfo {
private List datas;
private String columnHeader[];
private String feilds[];
private String footer[][];
private String title;
private int width[];
private String fileName = "ExcelExport.xls";
public String[] getColumnHeader() {
return columnHeader;
}
public void setColumnHeader(String[] columnHeader) {
this.columnHeader = columnHeader;
}
public void setColumnHeader(String columnHeaders) {
if (columnHeaders != null)
this.columnHeader = columnHeaders.split(",");
else
this.columnHeader = null;
}
public List getDatas() {
return datas;
}
public void setDatas(List datas) {
this.datas = datas;
}
public String[] getFeilds() {
return feilds;
}
public void setFeilds(String[] feilds) {
this.feilds = feilds;
}
public void setFeilds(String feilds) {
if (feilds != null)
this.feilds = feilds.split(",");
else
this.feilds = null;
}
public String[][] getFooter() {
return footer;
}
public void setFooter(String[][] footer) {
this.footer = footer;
}
public int[] getWidth() {
return width;
}
public void setWidth(int[] width) {
this.width = width;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
}