Java操作Excel之JXL（填充EXCEL模板）转载

Java操作Excel之理解JXL--读取Excel 说到如何⽤JXL读取Excel⽂件，

现在来看看如何读取Excel模板然后把动态数据写⼊到模板以⽣成特定格式的Excel。

同样的思路，先来考虑下会涉及到那些对象，和上篇⽂章读取相⽐这⾥涉及到⼀个写⼊的步骤，所以JXL必然会提供⼀个对象来⽀持写⼊，这就是WritableWorkbook。那么如何得到到这个类的实例，查看APIDoc发现Workbook类提供了⼏个静态的createWorkbook⽅法返回WritableWorkbook实例，可以看到众多createWorkbook⽅法主要分为两类：⼀个参数和两个参数。简单分析可以得知前者仅仅是⽤来直接⽣成Excel⽂件，后者先读取模板再向模板写⼊数据然后⽣成Excel。（还有⼀个三参数⽅法加上⼀个⽤来设置workbook的参数）

现在按照上篇⽂章中所讲述的流程来看看读取模板并写⼊需要那些步骤。第⼀步：选择模板⽂件：

Workbook wb = Workbook.getWorkbook(new File(realpath));第⼆步：通过模板得到⼀个可写的Workbook：

WritableWorkbook wwb = Workbook.createWorkbook(targetFile, wb);第⼀个参数是⼀个输出流对象，⽐如可以

ByteArrayOutputStream targetFile = new ByteArrayOutputStream();这样定义这个输出流对象。第⼆个参数代表了要读取的模板。

第三步：选择模板中名称为StateResult的Sheet：WritableSheet wws = wwb.getSheet(\"StateResult\");如果需要也可以创建Sheet

WritableSheet wws = wwb.createSheet(\"Sheet名称\

第四步：选择单元格，写⼊动态值，根据单元格的不同类型转换成相应类型的单元格：Label A1 = (Label)wws.getWritableCell(0,0);A1.setString(\"单元格内容\")或

Number A2 = (Number)wws.getWritableCell(0,1);//Number是jxl.write.NumberA2.setValue(3.3);

也可以创建新的单元格并且加⼊到Sheet中Label C1 = new Label(2,0,\"单元格内容\");wws.addCell(C1);或

Number C1 = new Number(2,0,3.3);wws.addCell(C1);

在⽣成Excel报表的时候还会遇到⼀种需求就是数据的格式问题，我们可能希望数值型单元格以某种格式显⽰，⽽字符串型单元格以另⼀种格式显⽰。这些可以通过WritableFont、NumberFormat、WritableCellFormat等实现,下例给单元格A1、A2添加了不同的格式。java 代码

WritableFont font= new WritableFont(WritableFont.createFont(\"宋体\"),10,WritableFont.NO_BOLD); NumberFormat format = new NumberFormat(\"###,##0.00\"); //NumberFormat是jxl.write.NumberFormat WritableCellFormat cellFormat1 = new WritableCellFormat(font,format); WritableCellFormat cellFormat2 = new WritableCellFormat(font);

cellFormat1.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); //Border是jxl.format.Border cellFormat2.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); //Border是jxl.format.Border A2.setCellFormat(cellFormat1); A1.setCellFormat(cellFormat2);

还有不要忘记关闭WritableWorkbook和Workbook以释放资源：wwb.close();wb.close();

最后就可以你需要的⽅式从输出流targetFile中取得Excel，⽐如直接⽣成⽂件存本地，输出到客户端浏览器等。如果还有其他需求，按照这种思路，再参照APIDoc相信可以很容易的解决。⾄此，Java操作Excel之理解JXL就写完了。

***********************************************************************************************************************************

在这⾥说说两种表单格式，第⼀种只需把数据填充就⾏了,第⼆种是循环输出数据。第⼀种表单如下：

//选择模板⽂件：

String realpath = \"D:/download/test.xls\"; try{

Workbook wb = Workbook.getWorkbook(new File(realpath));

//第⼆步：通过模板得到⼀个可写的Workbook：第⼀个参数是⼀个输出流对象,第⼆个参数代表了要读取的模板 File targetFile = new File(\"D:/download/test1.xls\");

WritableWorkbook wwb = Workbook.createWorkbook(targetFile, wb); //第三步：选择模板中名称为StateResult的Sheet： WritableSheet wws = wwb.getSheet(4);// 如果需要也可以创建Sheet

// WritableSheet wws = wwb.createSheet(\"Sheet名称\

//第四步：选择单元格，写⼊动态值，根据单元格的不同类型转换成相应类型的单元格： //(列，⾏)

Label A1 = (Label)wws.getWritableCell(0,0);

A1.setString(\" 2009年 7 ⽉****出单中⼼####公司结算表\");

WritableFont font= new WritableFont(WritableFont.createFont(\"宋体\"),18,WritableFont.BOLD); WritableCellFormat cellFormat1 = new WritableCellFormat(font);

cellFormat1.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); //Border是jxl.format.Border cellFormat1.setBackground(Colour.BLUE);//设置单元格背景颜⾊为天蓝⾊

cellFormat1.setAlignment(jxl.format.Alignment.CENTRE);//设置⽂本对其⽅式，左对齐还是右对齐 A1.setCellFormat(cellFormat1);

Label A2 = (Label)wws.getWritableCell(0,1);

A2.setString(\"结算⽇期：2009年 7⽉7⽇ ⾄ 2009年 7⽉31⽇\");

WritableFont font2= new WritableFont(WritableFont.createFont(\"宋体\"),12,WritableFont.BOLD); WritableCellFormat cellFormat2 = new WritableCellFormat(font2);

cellFormat2.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); //Border是jxl.format.Border cellFormat2.setBackground(Colour.BLUE);//设置单元格背景颜⾊为天蓝⾊

cellFormat2.setAlignment(jxl.format.Alignment.RIGHT);//设置⽂本对其⽅式，左对齐还是右对齐 A2.setCellFormat(cellFormat2);

/********************************public*****************************/

jxl.write.NumberFormat format = new jxl.write.NumberFormat(\"#\");

jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(format);

WritableFont fonte= new WritableFont(WritableFont.createFont(\"宋体\"),12,WritableFont.NO_BOLD); WritableCellFormat cellFormat= new WritableCellFormat(fonte,format);

cellFormat.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); //Border是jxl.format.Border cellFormat.setAlignment(jxl.format.Alignment.CENTRE);//设置⽂本对其⽅式，左对齐还是右对齐 /********************************public*****************************/

/********************************保单⽣产明细*****************************/ //份数

jxl.write.Number C4 = new jxl.write.Number(2,3,bean.getPieces(),wcf); wws.addCell(C4);

C4.setCellFormat(cellFormat);

//保单总印量

jxl.write.Number C5 = new jxl.write.Number(2,4,bean.getAllprintnum(),wcf); wws.addCell(C5);

C5.setCellFormat(cellFormat); //.................以此类推

wwb.write(); wwb.close(); wb.close();

}catch(Exception e){

e.printStackTrace(); } }

第⼆种表单如下：

循环⼀个⽉内 每天的记录，前⾯是写死的填充数据，现在是循环输出，也很简单，写个FOR循环就OK，关闭WritableWorkbook和Workbook释放资源 要放在FOR循环之外，⽅法如下：

/**

* ⽉报表导出EXCEL*

* 循环输出的地⽅要采⽤新建单元格并且加⼊到Sheet的⽅式来实现**/

public void reportMonthJXLWriteExcel(List list, List list2) throws Exception { // 选择模板⽂件：

String realpath = \"D:/download/test.xls\"; try {

Workbook wb = Workbook.getWorkbook(new File(realpath));

// 第⼆步：通过模板得到⼀个可写的Workbook：第⼀个参数是⼀个输出流对象,第⼆个参数代表了要读取的模板 File targetFile = new File(\"D:/download/test1.xls\");

WritableWorkbook wwb = Workbook.createWorkbook(targetFile, wb); // 第三步：选择模板中名称为StateResult的Sheet： WritableSheet wws = wwb.getSheet(3);

// 第四步：选择单元格，写⼊动态值，根据单元格的不同类型转换成相应类型的单元格： // (列，⾏)

Label A1 = (Label) wws.getWritableCell(0, 0);

A1.setString(\"2007年07⽉16⽇湖南出单中⼼长沙分公司⽣产⽉明细表\"); WritableFont font = new WritableFont(WritableFont.createFont(\"宋体\"), 18, WritableFont.BOLD);

WritableCellFormat cellFormat1 = new WritableCellFormat(font);

cellFormat1.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); // Border是jxl.format.Border cellFormat1.setBackground(Colour.BLUE);// 设置单元格背景颜⾊为天蓝⾊

cellFormat1.setAlignment(jxl.format.Alignment.CENTRE);// 设置⽂本对其⽅式，左对齐还是右对齐 A1.setCellFormat(cellFormat1);

/** ******************************public**************************** */

jxl.write.NumberFormat format = new jxl.write.NumberFormat(\"#\"); jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat( format);

WritableFont fonte = new WritableFont(

WritableFont.createFont(\"宋体\"), 10, WritableFont.NO_BOLD); WritableCellFormat cellFormat = new WritableCellFormat(fonte, format);

cellFormat.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); // Border是jxl.format.Border cellFormat.setAlignment(jxl.format.Alignment.CENTRE);// 设置⽂本对其⽅式，左对齐还是右对齐 for (int c = 0; c < 18; c++) {

wws.setColumnView(c, 8);// 给每列设置宽度 }

/** ******************************public**************************** */ int i = 0, j = 0;// i=描述单元格，j=获取JAVA BEAN对象 for (i = 3, j = 0; i < list.size() + 3 && j < list.size(); i++, j++) { ReportDayBean bean = (ReportDayBean) list.get(j);

Label A4 = new Label(0, i, bean.getBusitime()); wws.addCell(A4);

WritableFont font2 = new WritableFont(WritableFont .createFont(\"宋体\"), 10, WritableFont.NO_BOLD);

WritableCellFormat cellFormat2 = new WritableCellFormat(font2); cellFormat2.setBorder(Border.ALL,

jxl.format.BorderLineStyle.THIN); // Border是jxl.format.Border

cellFormat2.setAlignment(jxl.format.Alignment.CENTRE);// 设置⽂本对其⽅式，左对齐还是右对齐 A4.setCellFormat(cellFormat2);

// 份数

jxl.write.Number B4 = new jxl.write.Number(1, i, bean .getPieces(), wcf); wws.addCell(B4);

B4.setCellFormat(cellFormat);

// 总印量

jxl.write.Number C4 = new jxl.write.Number(2, i, bean .getAllprintnum(), wcf); wws.addCell(C4);

C4.setCellFormat(cellFormat);

// 超印量

jxl.write.Number D4 = new jxl.write.Number(3, i, bean .getOverprintnum(), wcf); wws.addCell(D4);

D4.setCellFormat(cellFormat);

// 清单印量

jxl.write.Number E4 = new jxl.write.Number(4, i, bean .getListprintnum(), wcf); wws.addCell(E4);

E4.setCellFormat(cellFormat); // A4纸

jxl.write.Number F4 = new jxl.write.Number(5, i, bean .getPaper(), wcf); wws.addCell(F4);

F4.setCellFormat(cellFormat);

// 份数

jxl.write.Number G4 = new jxl.write.Number(6, i, bean .getZ_pieces(), wcf); wws.addCell(G4);

G4.setCellFormat(cellFormat); // 印量

jxl.write.Number H4 = new jxl.write.Number(7, i, bean .getZ_printnum(), wcf); wws.addCell(H4);

H4.setCellFormat(cellFormat); // ⽤纸

jxl.write.Number I4 = new jxl.write.Number(8, i, bean .getZ_paper(), wcf); wws.addCell(I4);

I4.setCellFormat(cellFormat); // 本埠信函

jxl.write.Number J4 = new jxl.write.Number(9, i, bean .getLocal(), wcf); wws.addCell(J4);

J4.setCellFormat(cellFormat); // 外埠信函

jxl.write.Number K4 = new jxl.write.Number(10, i, bean .getForeign(), wcf); wws.addCell(K4);

K4.setCellFormat(cellFormat); // 印量

jxl.write.Number L4 = new jxl.write.Number(11, i, bean .getPrintnum(), wcf); wws.addCell(L4);

L4.setCellFormat(cellFormat); // 预印⽤纸

jxl.write.Number M4 = new jxl.write.Number(12, i, bean .getPrepaper(), wcf); wws.addCell(M4);

M4.setCellFormat(cellFormat);

// 空⽩⽤纸

jxl.write.Number N4 = new jxl.write.Number(13, i, bean .getBlankpaper(), wcf); wws.addCell(N4);

N4.setCellFormat(cellFormat); // 信封

jxl.write.Number O4 = new jxl.write.Number(14, i, bean .getEnvelope(), wcf); wws.addCell(O4);

O4.setCellFormat(cellFormat); // 装订份数

jxl.write.Number P4 = new jxl.write.Number(15, i, bean .getQ_pieces(), wcf); wws.addCell(P4);

P4.setCellFormat(cellFormat); // 印量

jxl.write.Number Q4 = new jxl.write.Number(16, i, bean .getQ_printnum(), wcf); wws.addCell(Q4);

Q4.setCellFormat(cellFormat); // A4纸

jxl.write.Number R4 = new jxl.write.Number(17, i, bean .getQ_paper(), wcf); wws.addCell(R4);

R4.setCellFormat(cellFormat); }

System.out.println(\"iiiiiiiiiiii=\" + i);

ReportDayBean bean = (ReportDayBean) list2.get(0);

Label A4 = new Label(0, i, \"⼩计\"); wws.addCell(A4);

WritableFont font2 = new WritableFont(

WritableFont.createFont(\"宋体\"), 12, WritableFont.NO_BOLD); WritableCellFormat cellFormat2 = new WritableCellFormat(font2);

cellFormat2.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); // Border是jxl.format.Border cellFormat2.setAlignment(jxl.format.Alignment.CENTRE);// 设置⽂本对其⽅式，左对齐还是右对齐 A4.setCellFormat(cellFormat2);

// 份数

jxl.write.Number B4 = new jxl.write.Number(1, i, bean.getPieces(), wcf);

wws.addCell(B4);

B4.setCellFormat(cellFormat);

// 总印量

jxl.write.Number C4 = new jxl.write.Number(2, i, bean .getAllprintnum(), wcf); wws.addCell(C4);

C4.setCellFormat(cellFormat);

// 超印量

jxl.write.Number D4 = new jxl.write.Number(3, i, bean .getOverprintnum(), wcf); wws.addCell(D4);

D4.setCellFormat(cellFormat);

// 清单印量

jxl.write.Number E4 = new jxl.write.Number(4, i, bean .getListprintnum(), wcf); wws.addCell(E4);

E4.setCellFormat(cellFormat); // A4纸

jxl.write.Number F4 = new jxl.write.Number(5, i, bean.getPaper(), wcf);

wws.addCell(F4);

F4.setCellFormat(cellFormat);

// 份数

jxl.write.Number G4 = new jxl.write.Number(6, i, bean.getZ_pieces(), wcf); wws.addCell(G4);

G4.setCellFormat(cellFormat); // 印量

jxl.write.Number H4 = new jxl.write.Number(7, i, bean .getZ_printnum(), wcf); wws.addCell(H4);

H4.setCellFormat(cellFormat); // ⽤纸

jxl.write.Number I4 = new jxl.write.Number(8, i, bean.getZ_paper(), wcf);

wws.addCell(I4);

I4.setCellFormat(cellFormat); // 本埠信函

jxl.write.Number J4 = new jxl.write.Number(9, i, bean.getLocal(), wcf);

wws.addCell(J4);

J4.setCellFormat(cellFormat); // 外埠信函

jxl.write.Number K4 = new jxl.write.Number(10, i, bean.getForeign(), wcf); wws.addCell(K4);

K4.setCellFormat(cellFormat); // 印量

jxl.write.Number L4 = new jxl.write.Number(11, i, bean .getPrintnum(), wcf); wws.addCell(L4);

L4.setCellFormat(cellFormat); // 预印⽤纸

jxl.write.Number M4 = new jxl.write.Number(12, i, bean .getPrepaper(), wcf); wws.addCell(M4);

M4.setCellFormat(cellFormat); // 空⽩⽤纸

jxl.write.Number N4 = new jxl.write.Number(13, i, bean .getBlankpaper(), wcf); wws.addCell(N4);

N4.setCellFormat(cellFormat); // 信封

jxl.write.Number O4 = new jxl.write.Number(14, i, bean .getEnvelope(), wcf); wws.addCell(O4);

O4.setCellFormat(cellFormat); // 装订份数

jxl.write.Number P4 = new jxl.write.Number(15, i, bean .getQ_pieces(), wcf); wws.addCell(P4);

P4.setCellFormat(cellFormat); // 印量

jxl.write.Number Q4 = new jxl.write.Number(16, i, bean .getQ_printnum(), wcf); wws.addCell(Q4);

Q4.setCellFormat(cellFormat); // A4纸

jxl.write.Number R4 = new jxl.write.Number(17, i, bean.getQ_paper(), wcf); wws.addCell(R4);

R4.setCellFormat(cellFormat);

int k = i + 2;// 这⾥是空⼀⾏再写⼊制表⼈，审核⼈...... wws.mergeCells(4, k, 17, k); Label E14 = new Label( 4, k,

\"制表⼈： 　 审核⼈： 机构负责⼈： 　　 机构财务部负责⼈： \");

wws.addCell(E14);

WritableCellFormat cellFormatinfo = new WritableCellFormat(font2); cellFormatinfo.setBorder(Border.ALL,

jxl.format.BorderLineStyle.NONE); // Border是jxl.format.Border

cellFormatinfo.setAlignment(jxl.format.Alignment.LEFT);// 设置⽂本对其⽅式，左对齐还是右对齐 E14.setCellFormat(cellFormatinfo); wwb.write(); wwb.close(); wb.close();

} catch (Exception e) { }}