java 利用jxl读取，生成excel 工具类集合大全
By：Roy.LiuLast updated：2013-08-05
在以前的一篇文章中，曾经提到如何用 jxl 来操作excel ，但讲得比较简单，不够详细，一般这种操作，都会被封装成一个工具类，供给其他地方调用。现在将整个工具类的代码贴上来，以供以后参考。
import java.io.*;
import jxl.*;
import jxl.write.*;
import jxl.format.*;
import java.util.*;
import com.tjhq.cee2.imp.tool.debug;
public class Excel {
private jxl.write.WritableWorkbook WorkBuk=null ;
private jxl.write.WritableSheet ws=null;
private FileOutputStream fos = null;
private InputStream is = null;
private jxl.Workbook rwb = null;
private jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL,10, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
private jxl.write.WritableCellFormat wcfFC= null;
/**
* 在指定路径下新建一个Excel文件。
* @parma String FileName 文件名称+文件路径（文件绝对路径 如：D:\\sheet1.xls）
* @return boolean
*/
public boolean createExcel(String FileName) throws Exception {
boolean Rtn = false;
try {
fos = new FileOutputStream(FileName);
WorkBuk = Workbook.createWorkbook(fos);
Rtn = true;
}
catch (Exception e) {
debug.printDug("Excel.createExcel is error,this is errormessage="+e.getMessage());
Rtn = false;
}
finally {
try {
}
catch (Exception e) {
}
}
return Rtn;
}
/**
* 设置当前sheet工作薄的名称及sheet号
* @parma int SheetIdx(sheet号) String SheetName(工作薄的名称)
* @return boolean
*/
public boolean setCurSheet(int SheetIdx, String SheetName) {
boolean Rtn = false;
try {
ws = WorkBuk.createSheet(SheetName, SheetIdx);
Rtn = true;
}
catch (Exception e) {
debug.printDug("Excel.setCurSheet is error,this is errormessage="+e.getMessage());
Rtn = false;
}
finally {
try {
}
catch (Exception e) {
}
}
return Rtn;
}
/**
* 向cell单元格中写入String型数据并可以设置单元格背景
* @parma int col(列号) int row(行号) Txt 内容
* @return boolean
*/
public boolean setCellText(int col, int row, String Txt, String bgcolor) {
boolean Rtn = false;
try {
wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10,
WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
setCellBgcolor(bgcolor);
jxl.write.Label labelC = new jxl.write.Label(col, row, Txt, wcfFC);
ws.addCell(labelC);
Rtn = true;
}
catch (Exception e) {
debug.printDug("Excel.setCellText is error,this is errormessage="+e.getMessage());
Rtn = false;
}
finally {
try {
}
catch (Exception e) {
}
}
return Rtn;
}
/**
* 向cell单元格中写入Date型数据并可以设置单元格背景
* @parma int col(列号) int row(行号) Txt 内容
* @return boolean
*/
public boolean setCellDate(int col, int row, Date Txt, String bgcolor) {
boolean Rtn = false;
try {
wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10,
WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
setCellBgcolor(bgcolor);
jxl.write.DateTime labelC = new jxl.write.DateTime(col, row, Txt, wcfFC);
ws.addCell(labelC);
Rtn = true;
}
catch (Exception e) {
debug.printDug("Excel.setCellDate is error,this is errormessage="+e.getMessage());
Rtn = false;
}
finally {
try {
}
catch (Exception e) {
}
}
return Rtn;
}
/**
* 向cell单元格中写入数字：double型数字并可以设置单元格背景
* @parma int col(列号) int row(行号) Txt 内容
* @return boolean
*/
public boolean setCellNumber(int col,int row,double Txt,String bgcolor)
{
boolean Rtn = false;
try {
setCellBgcolor(bgcolor);
jxl.write.Number labelC = new jxl.write.Number(col, row, Txt, wcfFC);
ws.addCell(labelC);
Rtn = true;
}
catch (Exception e) {
debug.printDug("Excel.setCellNumber is error,this is errormessage="+e.getMessage());
Rtn = false;
}
finally {
try {
}
catch (Exception e) {
}
}
return Rtn;
}
/**
* 向cell单元格中写入String型数据并可以设置字体样式
* @parma int col(列号) int row(行号) Txt 内容 int format 字体样式编号 int fontSize 字体大小
* @return boolean
*/
public boolean setCellText(int col,int row,String Txt,int format,int fontSize)
{
boolean Rtn=false;
try
{
setCellFont(format,fontSize);
wcfFC=new jxl.write.WritableCellFormat(wfc);
jxl.write.Label labelC = new jxl.write.Label(col, row, Txt,wcfFC);
ws.addCell(labelC);
Rtn=true;
}
catch(Exception e)
{
debug.printDug("Excel.setCellText is error,this is errormessage="+e.getMessage());
Rtn=false;
}
finally
{
try
{
}
catch(Exception e)
{
}
}
return Rtn;
}
/**
* 向cell单元格中写入Date型数据并可以设置字体样式
* @parma int col(列号) int row(行号) Txt 内容 int format 字体样式编号 int fontSize 字体大小
* @return boolean
*/
public boolean setCellDate(int col,int row,Date Txt,int format,int fontSize)
{
boolean Rtn=false;
try
{
setCellFont(format,fontSize);
wcfFC=new jxl.write.WritableCellFormat(wfc);
jxl.write.DateTime labelC = new jxl.write.DateTime (col, row, Txt,wcfFC);
ws.addCell(labelC);
Rtn=true;
}
catch(Exception e)
{
debug.printDug("Excel.setCellDate is error,this is errormessage="+e.getMessage());
Rtn=false;
}
finally
{
try
{
}
catch(Exception e)
{
}
}
return Rtn;
}
/**
* 向cell单元格中写入数字：double型数字并可以设置字体样式
* @parma int col(列号) int row(行号) Txt 内容 int format 字体样式编号 int fontSize 字体大小
* @return boolean
*/
public boolean setCellNumber(int col,int row,double Txt,int format,int fontSize)
{
boolean Rtn=false;
try
{
setCellFont(format,fontSize);
wcfFC=new jxl.write.WritableCellFormat(wfc);
jxl.write.Number labelC = new jxl.write.Number (col, row, Txt,wcfFC);
ws.addCell(labelC);
Rtn=true;
}
catch(Exception e)
{
debug.printDug("Excel.setCellNumber is error,this is errormessage="+e.getMessage());
Rtn=false;
}
finally
{
try
{
}
catch(Exception e)
{
}
}
return Rtn;
}
/**
* 向cell单元格中写入String型数据
* @parma int col(列号) int row(行号) Txt 内容
* @return boolean
*/
public boolean setCellText(int col,int row,String Txt)
{
boolean Rtn=false;
try
{
jxl.write.Label labelC = new jxl.write.Label(col, row, Txt);
ws.addCell(labelC);
Rtn=true;
}
catch(Exception e)
{
debug.printDug("Excel.setCellText is error,this is errormessage="+e.getMessage());
Rtn=false;
}
finally
{
try
{
}
catch(Exception e)
{
}
}
return Rtn;
}
/**
* 向cell单元格中写入Date型数据
* @parma int col(列号) int row(行号) Txt 内容
* @return boolean
*/
public boolean setCellDate(int col,int row,Date Txt)
{
boolean Rtn=false;
try
{
//String dd=DateUtils.getInstance().format(Txt,"yyyy-mm-dd");
//System.out.println(dd);
jxl.write.DateTime labelC = new jxl.write.DateTime (col, row, Txt);
ws.addCell(labelC);
Rtn=true;
}
catch(Exception e)
{
debug.printDug("Excel.setCellDate is error,this is errormessage="+e.getMessage());
Rtn=false;
}
finally
{
try
{
}
catch(Exception e)
{
}
}
return Rtn;
}
/**
* 向cell单元格中写入数字：double型数字。
* @parma int col(列号) int row(行号) Txt 内容
* @return boolean
*/
public boolean setCellNumber(int col,int row,double Txt)
{
boolean Rtn=false;
try
{
jxl.write.Number labelC = new jxl.write.Number (col, row, Txt);
ws.addCell(labelC);
Rtn=true;
}
catch(Exception e)
{
debug.printDug("Excel.setCellNumber is error,this is errormessage="+e.getMessage());
Rtn=false;
}
finally
{
try
{
}
catch(Exception e)
{
}
}
return Rtn;
}
/**
* 向cell单元格中写入数字：float型数字。
* @parma int col(列号) int row(行号) Txt 内容
* @return boolean
*/
public boolean setCellNumber(int col,int row,float Txt)
{
boolean Rtn=false;
try
{
jxl.write.Number labelC = new jxl.write.Number (col, row, Txt);
ws.addCell(labelC);
Rtn=true;
}
catch(Exception e)
{
debug.printDug("Excel.setCellNumber is error,this is errormessage="+e.getMessage());
Rtn=false;
}
finally
{
try
{
}
catch(Exception e)
{
}
}
return Rtn;
}
/**
* 将写入的cell的内容保存到Excel文件中.
* @parma
* @return boolean
*/
public boolean saveExcel()
{
boolean Rtn=false;
try
{
//写入Exel工作表
WorkBuk.write();
//关闭Excel工作薄对象
WorkBuk.close();
Rtn=true;
}
catch(Exception e)
{
debug.printDug("Excel.saveExcel is error,this is errormessage="+e.getMessage());
Rtn=false;
}
finally
{
try
{
}
catch(Exception e)
{
}
}
return Rtn;
}
/**
* 打开Excel.
* @parma fileName Excel文件名+文件路径（绝对路径）
* @return boolean
*/
public boolean openExcel(String fileName)
{
boolean Rtn=false;
try
{
is = new FileInputStream(fileName);
rwb = Workbook.getWorkbook(is);
Rtn=true;
}
catch(Exception e)
{
debug.printDug("Excel.openExcel is error,this is errormessage="+e.getMessage());
Rtn=false;
}
finally
{
try
{
}
catch(Exception e)
{
}
}
return Rtn;
}
/**
* 关闭打开的Excel.
* @
* @return boolean
*/
public boolean closeExcel()
{
boolean Rtn=false;
try
{
rwb.close();
Rtn=true;
}
catch(Exception e)
{
debug.printDug("Excel.closeExcel is error,this is errormessage="+e.getMessage());
Rtn=false;
}
finally
{
try
{
rwb.close();
}
catch(Exception e)
{
Rtn=false;
}
}
return Rtn;
}
/**
* 得到工作薄的名称
* @parma sheetIndex 工作薄号
* @return String
*/
public String getSheetName(int sheetIndex) {
String sheetNam = "";
try {
jxl.Sheet rs = rwb.getSheet(sheetIndex);
sheetNam = rs.getName();
}
catch (Exception e) {
debug.printDug("Excel.getSheetName is error,this is errormessage="+e.getMessage());
sheetNam = "";
}
finally {
try {
}
catch (Exception e) {
sheetNam = "";
}
}
return sheetNam;
}
/**
* 得到当前工作薄的总列数
* @parma sheetIndex 工作薄号
* @return int
*/
public int getColCount(int sheetIndex) {
int colCnt = 0;
try {
jxl.Sheet rs = rwb.getSheet(sheetIndex);
colCnt = rs.getColumns();
}
catch (Exception e) {
debug.printDug("Excel.getColCount is error,this is errormessage="+e.getMessage());
colCnt = 0;
}
finally {
try {
}
catch (Exception e) {
colCnt = 0;
}
}
return colCnt ;
}
/**
* 得到当前工作薄的总行数
* @parma sheetIndex 工作薄号
* @return int
*/
public int getRowCount(int sheetIndex) {
int colCnt = 0;
try {
jxl.Sheet rs = rwb.getSheet(sheetIndex);
colCnt = rs.getRows();
}
catch (Exception e) {
debug.printDug("Excel.getRowCount is error,this is errormessage="+e.getMessage());
colCnt = 0;
}
finally {
try {
}
catch (Exception e) {
colCnt = 0;
}
}
return colCnt ;
}
/**
* 获取某一列的所有单元格
* @parma col 列数
* @return String[]
*/
public String[] getColArray(int col) {
Sheet rs = rwb.getSheet(0);
Cell[] getArray = rs.getColumn(col);
String Str[] = new String[getArray.length];
try {
for(int i=0;i
调用如下:
int sheetIndex = 0;
String fileName = "D:\\test.xls";
String SheetName="Excel测试";
boolean result=false;
//在指定路径下新建一个Excel文件。（创建一个Excel文件的第一步，必须。）
result = ex.createExcel(fileName);
//设置当前sheet工作薄的名称及sheet号（创建一个Excel文件的第二步，必须。）
result = ex.setCurSheet(sheetIndex,SheetName);
//单元格赋值（创建一个Excel文件的第三步）
if(result)
{
//向单元加文本型内容
result=ex.setCellText(0,0,"语文");
//向单元加内容并且设置单元格背景颜色(颜色值有限种如：red,green,blue,yellow,brown,dark_blue)
result=ex.setCellText(1,0,"数学","green");
result=ex.setCellText(2,0,"英语","gray");
result=ex.setCellText(3,0,"化学","blue");
result=ex.setCellText(0,1,"90");
result=ex.setCellText(1,1,"93");
result=ex.setCellText(2,1,"97");
result=ex.setCellText(3,1,"80");
//向单元加数字内容并且设置字体样式。setCellText（int col,int row,String txt,int format,int fontSize）在文档中有说明;
result=ex.setCellNumber(0,2,68,3,10);
result=ex.setCellText(1,2,"88");
//向单元加数字内容
result=ex.setCellNumber(2,2,93.23);
//向单元加文本型内容并且设置字体样式。setCellText（int col,int row,String txt,int format,int fontSize）在文档中有说明;
result=ex.setCellText(3,2,"设置字体样式",4,11);
result=ex.setCellText(0,3,"99");
result=ex.setCellText(1,3,"100");
result=ex.setCellText(2,3,"60");
Date xx = new Date("1982/12/24");
result=ex.setCellDate(3,3,xx);
result=ex.saveExcel();
ex.closeExcel();
}
if(result)
{
out.println("创建Excel文件"+fileName+" 成功！");
}
From：一号门
COMMENTS