jxl 操作 excel

有时候我们会需要做excel的报表,下面这个例子详细的给出了读写及定义样式的方法。

package com.test;

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
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;

/***/ /**
*
*CopyRight(C)www.blogjava.net/ilovezmhAllrightsreserved.<p>
*
*WuHanInpointInformationTechnologyDevelopment,Inc.<p>
*
*Authorzhu<p>
*
* @version 1.02007-2-6
*
*<p>Baseon:JDK1.5<p>
*
*/

public class JexcelSample ... {

/***/ /**
*写excel文件
*
*/
public void writeExc(Filefilename) ... {
WritableWorkbookwwb = null ;
try
... {
wwb = Workbook.createWorkbook(filename);
}
catch (Exceptione) ... {
e.printStackTrace();
}

// 创建Excel工作表
WritableSheetws = wwb.createSheet( " 通讯录 " , 0 ); // 创建sheet
try ... {
ws.mergeCells( 0 , 0 , 2 , 1 ); // 合并单元格(左列，左行，右列，右行)从第1行第1列到第2行第3列
Labelheader = new Label( 0 , 0 , " 通讯录(191026班) " ,getHeader());
ws.addCell(header); // 写入头
Labell = new Label( 0 , 2 , " 姓名 " ,getTitle()); // 第3行
ws.addCell(l);
l = new Label( 1 , 2 , " 电话 " ,getTitle());
ws.addCell(l);
l = new Label( 2 , 2 , " 地址 " ,getTitle());
ws.addCell(l);
l = new Label( 0 , 3 , " 小祝 " ,getNormolCell()); // 第4行
ws.addCell(l);
l = new Label( 1 , 3 , " 1314***0974 " ,getNormolCell());
ws.addCell(l);
l = new Label( 2 , 3 , " 武汉武昌 " ,getNormolCell());
ws.addCell(l);
l = new Label( 0 , 4 , " 小施 " ,getNormolCell()); // 第5行
ws.addCell(l);
l = new Label( 1 , 4 , " 1347***5057 " ,getNormolCell());
ws.addCell(l);
l = new Label( 2 , 4 , " 武汉武昌 " ,getNormolCell());
ws.addCell(l);
ws.setColumnView( 0 , 20 ); // 设置列宽
ws.setColumnView( 1 , 20 );
ws.setColumnView( 2 , 40 );
ws.setRowView( 0 , 400 ); // 设置行高
ws.setRowView( 1 , 400 );
ws.setRowView( 2 , 500 );
ws.setRowView( 3 , 500 );
ws.setRowView( 4 , 500 );
} catch (RowsExceededExceptione1) ... {
e1.printStackTrace();
} catch (WriteExceptione1) ... {
e1.printStackTrace();
}

// 输出流
try ... {
wwb.write();
} catch (IOExceptionex) ... {
// TODO自动生成catch块
ex.printStackTrace();
}
// 关闭流
try ... {
wwb.close();
} catch (WriteExceptionex) ... {
// TODO自动生成catch块
ex.printStackTrace();
} catch (IOExceptionex) ... {
// TODO自动生成catch块
ex.printStackTrace();
}
// outStream.close();
System.out.println( " 写入成功！ " );
}

public void readExc(Filefilename) throws BiffException,IOException ... {

Workbookwb = Workbook.getWorkbook(filename);
Sheets = wb.getSheet( 0 ); // 第1个sheet
Cellc = null ;
int row = s.getRows(); // 总行数
int col = s.getColumns(); // 总列数
for ( int i = 0 ;i < row;i ++ ) ... {
for ( int j = 0 ;j < col;j ++ ) ... {
c = s.getCell(j,i);
System.out.print(c.getContents() + " " );
}
System.out.println();
}
}

/***/ /**
*设置头的样式
* @return
*/
public static WritableCellFormatgetHeader() ... {
WritableFontfont = new WritableFont(WritableFont.TIMES, 24 ,WritableFont.BOLD); // 定义字体
try ... {
font.setColour(Colour.BLUE); // 蓝色字体
} catch (WriteExceptione1) ... {
// TODO自动生成catch块
e1.printStackTrace();
}
WritableCellFormatformat = new WritableCellFormat(font);
try ... {
format.setAlignment(jxl.format.Alignment.CENTRE); // 左右居中
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 上下居中
format.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.BLACK); // 黑色边框
format.setBackground(Colour.YELLOW); // 黄色背景
} catch (WriteExceptione) ... {
// TODO自动生成catch块
e.printStackTrace();
}
return format;
}

/***/ /**
*设置标题样式
* @return
*/
public static WritableCellFormatgetTitle() ... {
WritableFontfont = new WritableFont(WritableFont.TIMES, 14 );
try ... {
font.setColour(Colour.BLUE); // 蓝色字体
} catch (WriteExceptione1) ... {
// TODO自动生成catch块
e1.printStackTrace();
}
WritableCellFormatformat = new WritableCellFormat(font);

try ... {
format.setAlignment(jxl.format.Alignment.CENTRE);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
format.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.BLACK);
} catch (WriteExceptione) ... {
// TODO自动生成catch块
e.printStackTrace();
}
return format;
}

/***/ /**
*设置其他单元格样式
* @return
*/
public static WritableCellFormatgetNormolCell() ... { // 12号字体,上下左右居中,带黑色边框
WritableFontfont = new WritableFont(WritableFont.TIMES, 12 );
WritableCellFormatformat = new WritableCellFormat(font);
try ... {
format.setAlignment(jxl.format.Alignment.CENTRE);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
format.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.BLACK);
} catch (WriteExceptione) ... {
// TODO自动生成catch块
e.printStackTrace();
}
return format;
}

public static void main(String[]args) throws IOException,BiffException ... {
JexcelSamplejs = new JexcelSample();
Filef = new File( " D:\address.xls " );
f.createNewFile();
js.writeExc(f);
js.readExc(f);
}

}

生成的excel表格如下：