生成Excel文件

来源：网络

创建可写入的Excel工作薄

WritableWorkbook
wwb = Workbook.createWorkbook(new File(targetfile));
将WritableWorkbook直接写入到输出流
OutputStream os = new FileOutputStream(targetfile);
WritableWorkbook wwb = Workbook.createWorkbook(os);
创建工作表
WritableSheet ws = wwb.createSheet("通讯录", 0);//创建sheet
创建单元格
添加文本类单元格
Label labelC = new Label(0, 0, "This is a Label cell");
添加带有字型Formatting的对象
WritableFont wf = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
WritableCellFormat wcfF = new WritableCellFormat(wf);
labelCF = new Label(1, 0, "This is a Label Cell", wcfF);
ws.addCell(labelCF);
添加带有字体颜色Formatting的对象
WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
WritableCellFormat wcfFC = new WritableCellFormat(wfc);
Label labelCFC = new Label(1, 0, "This is a Label Cell", wcfFC);
ws.addCell(labelCF);
添加Number对象
Number labelN = new jxl.write.Number(0, 1, 3.1415926);
ws.addCell(labelN);
添加带有formatting的Number对象
NumberFormat nf = new NumberFormat("#.##");
WritableCellFormat wcfN = new WritableCellFormat(nf);
Number labelNF = new Number(1, 1, 3.1415926, wcfN);
ws.addCell(labelNF);
添加Boolean对象
Boolean labelB = new jxl.write.Boolean(0, 2, false);
ws.addCell(labelB);
添加DateTime对象
DateTime labelDT = new DateTime(0, 3, new java.util.Date());
ws.addCell(labelDT);
添加带有formatting的DateFormat对象
DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");
WritableCellFormat wcfDF = new WritableCellFormat(df);
DateTime labelDTF = new DateTime(1, 3, new Date(), wcfDF);
ws.addCell(labelDTF);
添加公式单元格
Fornual formual = new Formual(0,11,”Sum(A1:A9)”);
wrb.addCell(formual);
添加图像
WritableImage wrimage=new WritableImage(1,5,10,10,new File(imageFilepath));
wrb.addImage(wrimage);
注意，API中注明只支持png文件。
合并单元格
通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的。
表示将从第x+1列，y+1行到m+1列，n+1行合并 (四个点定义了两个坐标，左上角和右下角)结果是合并了m-x+1行，n-y+1列，两者乘积就是合并的单元格数量。
sheet.mergeCells(0, 6, 3, 8);
label = new Label(0, 6, "合并了12个单元格");
sheet.addCell(label);


添加单元格样式
主要是改变单元格背景、字体、颜色等等。
WritableCellFormat wc = new WritableCellFormat();
wc.setAlignment(Alignment.CENTRE); // 设置居中
wc.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
wc.setBackground(jxl.format.Colour.RED); // 设置单元格的背景颜色
label = new Label(1, 5, "字体", wc);
sheet.addCell(label);
设置单元格字体
WritableFont wfont =
new WritableFont(WritableFont.createFont("楷书"), 20);
WritableCellFormat font = new WritableCellFormat(wfont);
label = new Label(2, 6, "楷书", font);
sheet.addCell(label);
写入到文件
wwb.write();// 写入数据

wwb.close();// 关闭文件

案例：

   

import java.io.FileOutputStream;import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import jxl.*;import jxl.format.Alignment;import jxl.format.Border;import jxl.format.BorderLineStyle;import jxl.format.CellFormat;import jxl.write.Boolean;import jxl.write.Label;import jxl.write.Number;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;public class JXLExample {/** * * @author smart * */public static void main(String[] args) {// 准备设置excel工作表的标题String[] title = { "编号", "产品名称", "产品价格", "产品数量", "生产日期", "产地", "是否出口" };try {// 获得开始时间long start = System.currentTimeMillis();// 输出的excel的路径String filePath = "c:\\test.xls";// 创建Excel工作薄WritableWorkbook wwb;// 新建立一个jxl文件,即在C盘下生成test.xlsOutputStream os = new FileOutputStream(filePath);wwb = Workbook.createWorkbook(os);// 添加第一个工作表并设置第一个Sheet的名字WritableSheet sheet = wwb.createSheet("产品清单", 0);Label label;for (int i = 0; i < title.length; i++) {label = new Label(i, 0, title[i]);// 将定义好的单元格添加到工作表中sheet.addCell(label);}/* * 保存数字到单元格，需要使用jxl.write.Number 必须使用其完整路径，否则会出现错误 */// 填充产品编号jxl.write.Number number = new jxl.write.Number(0, 1, 20071001);sheet.addCell(number);// 填充产品名称label = new Label(1, 1, "金鸽瓜子");sheet.addCell(label);/* * 定义对于显示金额的公共格式 jxl会自动实现四舍五入 例如 2.456会被格式化为2.46,2.454会被格式化为2.45 */jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(nf);// 填充产品价格jxl.write.Number nb = new jxl.write.Number(2, 1, 2.45, wcf);sheet.addCell(nb);// 填充产品数量jxl.write.Number numb = new jxl.write.Number(3, 1, 200);sheet.addCell(numb);/* * 定义显示日期的公共格式 如:yyyy-MM-dd hh:mm */SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");String newdate = sdf.format(new Date());// 填充出产日期label = new Label(4, 1, newdate);sheet.addCell(label);// 填充产地label = new Label(5, 1, "陕西西安");sheet.addCell(label);/* * 显示布尔值 */jxl.write.Boolean bool = new jxl.write.Boolean(6, 1, true);sheet.addCell(bool);/* * 合并单元格 通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的 * 表示将从第x+1列，y+1行到m+1列，n+1行合并 */sheet.mergeCells(0, 3, 2, 3);label = new Label(0, 3, "合并了三个单元格");sheet.addCell(label);/* *  * 定义公共字体格式 通过获取一个字体的样式来作为模板 首先通过web.getSheet(0)获得第一个sheet * 然后取得第一个sheet的第二列，第一行也就是"产品名称"的字体 */CellFormat cf = wwb.getSheet(0).getCell(1, 0).getCellFormat();WritableCellFormat wc = new WritableCellFormat();// 设置居中wc.setAlignment(Alignment.CENTRE);// 设置边框线wc.setBorder(Border.ALL, BorderLineStyle.THIN);// 设置单元格的背景颜色wc.setBackground(jxl.format.Colour.RED);label = new Label(1, 5, "字体", wc);sheet.addCell(label);// 设置字体WritableFont wfont = new WritableFont(WritableFont.createFont("隶书"), 20);WritableCellFormat font = new WritableCellFormat(wfont);label = new Label(2, 6, "隶书", font);sheet.addCell(label);// 写入数据wwb.write();// 关闭文件wwb.close();long end = System.currentTimeMillis();System.out.println("----完成该操作共用的时间是:" + (end - start) / 1000);} catch (Exception e) {System.out.println("---出现异常---");e.printStackTrace();}}}


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;public class JexcelSample {/** * 写excel文件 * */public void writeExc(File filename) {WritableWorkbook wwb = null;try {wwb = Workbook.createWorkbook(filename);} catch (Exception e) {e.printStackTrace();}// 创建Excel工作表WritableSheet ws = wwb.createSheet("通讯录", 0);// 创建sheettry {ws.mergeCells(0, 0, 2, 1);// 合并单元格(左列，左行，右列，右行)从第1行第1列到第2行第3列Label header = new Label(0, 0, "通讯录(191026班)", getHeader());ws.addCell(header);// 写入头Label l = 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 (RowsExceededException e1) {e1.printStackTrace();} catch (WriteException e1) {e1.printStackTrace();}// 输出流try {wwb.write();} catch (IOException ex) {// TODO 自动生成 catch 块ex.printStackTrace();}// 关闭流try {wwb.close();} catch (WriteException ex) {// TODO 自动生成 catch 块ex.printStackTrace();} catch (IOException ex) {// TODO 自动生成 catch 块ex.printStackTrace();}// outStream.close();System.out.println("写入成功！\n");}/** * 读取excel文件 *  * @param filename * @throws BiffException * @throws IOException */public void readExc(File filename) throws BiffException, IOException {Workbook wb = Workbook.getWorkbook(filename);Sheet s = wb.getSheet(0);// 第1个sheetCell c = 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 WritableCellFormat getHeader() {WritableFont font = new WritableFont(WritableFont.TIMES, 24,WritableFont.BOLD);// 定义字体try {font.setColour(Colour.BLUE);// 蓝色字体} catch (WriteException e1) {// TODO 自动生成 catch 块e1.printStackTrace();}WritableCellFormat format = 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 (WriteException e) {// TODO 自动生成 catch 块e.printStackTrace();}return format;}/** * 设置标题样式 *  * @return */public static WritableCellFormat getTitle() {WritableFont font = new WritableFont(WritableFont.TIMES, 14);try {font.setColour(Colour.BLUE);// 蓝色字体} catch (WriteException e1) {// TODO 自动生成 catch 块e1.printStackTrace();}WritableCellFormat format = 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 (WriteException e) {// TODO 自动生成 catch 块e.printStackTrace();}return format;}/** * 设置其他单元格样式 *  * @return */public static WritableCellFormat getNormolCell() {// 12号字体,上下左右居中,带黑色边框WritableFont font = new WritableFont(WritableFont.TIMES, 12);WritableCellFormat format = 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 (WriteException e) {// TODO 自动生成 catch 块e.printStackTrace();}return format;}public static void main(String[] args) throws IOException, BiffException {JexcelSample js = new JexcelSample();File f = new File("D:\\address.xls");f.createNewFile();js.writeExc(f);js.readExc(f);}}


jxl常用api       Workbook类提供的方法:int getNumberOfSheets() 获取工作表的总个数Sheet[] getSheets() 获取数组型的工作表Sheet getSheet(String name);//得到此对应名称的工作表       Sheet接口提供的方法:String getName() 获取工作表的名称int getColumns() 获取Sheet表中所包含的总列数:Cell[] getColumn(int column) 获取某一列的所有单元格，返回的是单元格对象数组int getRows() 获取Sheet表中所包含的总行数Cell[] getRow(int row) 获取某一行的所有单元格，返回的是单元格对象数组Cell getCell(int column, int row)获取指定单元格的对象引用，需要注意的是它的两个参数，第一个是列数，第二个是行数，这与通常的行、列组合有些不同WritableSheet.setRowView(int i,int height); 指定第i+1行的高度WritableSheet.setColumnView(int i,int width); 指定第i+1列的宽度