JAVA导出Excel报表之jxl插件及实例源码

package com.boco.jxl; import java.io.File; import java.util.ArrayList; import java.util.List; import jxl.LabelCell; import jxl.Workbook; import jxl.format.UnderlineStyle; import jxl.write.Border; import jxl.write.Label; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; /** * 生成特定样式Excel实例 * * @author 卢秦 * @version * @time 2012-6-20 上午11:11:29 * */ public class CreateXLS { /** * @param args */ public static void main(String[] args) { String excelName = "table.xls"; try { File excelFile = new File(excelName); // 打开文件 WritableWorkbook book = Workbook.createWorkbook(excelFile); // 生成名为“第一页”的工作表，参数0表示这是第一页 WritableSheet sheet = book.createSheet("第一页 ", 0); // 合并单元格 CreateXLS.mergeCells(sheet); // 设置表格内容和样式 CreateXLS.addCells(sheet); // 写入数据并关闭文件 book.write(); book.close(); System.out.println("Excel创建成功"); } catch (Exception e) { System.out.println(e); } } /** * 合并表格 * * @param sheet * @return */ public static boolean mergeCells(WritableSheet sheet) { boolean flag = false; try { // 第一行 sheet.mergeCells(0, 0, 0, 1);// 0,0 sheet.mergeCells(1, 0, 1, 1);// 1,0 sheet.mergeCells(2, 0, 4, 0);// 2,0 sheet.mergeCells(5, 0, 8, 0);// 5,0 // 第三行 sheet.mergeCells(0, 2, 0, 5);// 0,2 sheet.mergeCells(1, 2, 0, 5);// 1,2 sheet.mergeCells(2, 2, 2, 3);// 2,2 sheet.mergeCells(3, 2, 3, 3);// 3,2 sheet.mergeCells(4, 2, 4, 3);// 4,2 // 第五行 sheet.mergeCells(2, 4, 2, 5);// 2,4 sheet.mergeCells(3, 4, 3, 5);// 3,4 sheet.mergeCells(4, 4, 4, 5);// 4,4 // 第七行 sheet.mergeCells(0, 6, 0, 9);// 0,6 sheet.mergeCells(1, 6, 0, 9);// 1,6 sheet.mergeCells(2, 6, 2, 7);// 2,6 sheet.mergeCells(3, 6, 3, 7);// 3,6 sheet.mergeCells(4, 6, 4, 7);// 4,6 // 第九行 sheet.mergeCells(2, 8, 2, 9);// 2,8 sheet.mergeCells(3, 8, 3, 9);// 3,8 sheet.mergeCells(4, 8, 4, 9);// 4,8 flag = true; } catch (RowsExceededException e) { e.printStackTrace(); } catch (WriteException e) { e.printStackTrace(); } return flag; } /** * 设置表格内容并设置样式 * * @param sheet */ public static void addCells(WritableSheet sheet) { CellFormatFactory cff = new CellFormatFactory(); try { Label label; jxl.write.Number number; // 第一行 label = new Label(0, 0, "Head1", cff.getWCF2()); sheet.addCell(label); label = new Label(1, 0, "Head2", cff.getWCF2()); sheet.addCell(label); label = new Label(2, 0, "Head3", cff.getWCF2()); sheet.addCell(label); label = new Label(5, 0, "Head4", cff.getWCF2()); sheet.addCell(label); // 第二行 label = new Label(2, 1, "Head31", cff.getWCF2()); sheet.addCell(label); label = new Label(3, 1, "Head32", cff.getWCF2()); sheet.addCell(label); label = new Label(4, 1, "Head33", cff.getWCF2()); sheet.addCell(label); label = new Label(5, 1, "Head41", cff.getWCF2()); sheet.addCell(label); label = new Label(6, 1, "Head42", cff.getWCF2()); sheet.addCell(label); label = new Label(7, 1, "Head43", cff.getWCF2()); sheet.addCell(label); label = new Label(8, 1, "Head44", cff.getWCF2()); sheet.addCell(label); // 第一列 label = new Label(0, 2, "D1", cff.getDefaultWCF()); sheet.addCell(label); label = new Label(0, 6, "D2", cff.getDefaultWCF()); sheet.addCell(label); // 第二列 label = new Label(1, 2, "A1", cff.getDefaultWCF()); sheet.addCell(label); label = new Label(1, 6, "A2", cff.getDefaultWCF()); sheet.addCell(label); // 第三列 label = new Label(2, 2, "B1", cff.getDefaultWCF()); sheet.addCell(label); label = new Label(2, 4, "B4", cff.getDefaultWCF()); sheet.addCell(label); label = new Label(2, 6, "C1", cff.getDefaultWCF()); sheet.addCell(label); label = new Label(2, 8, "C4", cff.getDefaultWCF()); sheet.addCell(label); // 第四列 label = new Label(3, 2, "B2", cff.getDefaultWCF()); sheet.addCell(label); label = new Label(3, 4, "B5", cff.getDefaultWCF()); sheet.addCell(label); label = new Label(3, 6, "C2", cff.getDefaultWCF()); sheet.addCell(label); label = new Label(3, 8, "C5", cff.getDefaultWCF()); sheet.addCell(label); // 第五列 label = new Label(4, 2, "B3", cff.getDefaultWCF()); sheet.addCell(label); label = new Label(4, 4, "B6", cff.getDefaultWCF()); sheet.addCell(label); label = new Label(4, 6, "C3", cff.getDefaultWCF()); sheet.addCell(label); label = new Label(4, 8, "C6", cff.getDefaultWCF()); sheet.addCell(label); // 第六列 for (int i = 2; i <= 9; i++) { number = new jxl.write.Number(5, i, 9, cff.getDefaultWCF()); sheet.addCell(number); } // 第七列 number = new jxl.write.Number(6, 2, 0.52, cff.getWCF1()); sheet.addCell(number); number = new jxl.write.Number(6, 3, 0.6, cff.getWCF1()); sheet.addCell(number); number = new jxl.write.Number(6, 4, 0.31, cff.getWCF1()); sheet.addCell(number); number = new jxl.write.Number(6, 5, 0.77, cff.getWCF1()); sheet.addCell(number); number = new jxl.write.Number(6, 6, 0.98, cff.getWCF1()); sheet.addCell(number); number = new jxl.write.Number(6, 7, 0.3, cff.getWCF1()); sheet.addCell(number); number = new jxl.write.Number(6, 8, 0.8, cff.getWCF1()); sheet.addCell(number); number = new jxl.write.Number(6, 9, 0.55, cff.getWCF1()); sheet.addCell(number); // 第八列 label = new Label(7, 2, "100%", cff.getWCF1()); sheet.addCell(label); label = new Label(7, 3, "76%", cff.getWCF1()); sheet.addCell(label); label = new Label(7, 4, "38%", cff.getWCF1()); sheet.addCell(label); label = new Label(7, 5, "99%", cff.getWCF1()); sheet.addCell(label); label = new Label(7, 6, "7%", cff.getWCF1()); sheet.addCell(label); label = new Label(7, 7, "29%", cff.getWCF1()); sheet.addCell(label); label = new Label(7, 8, "3%", cff.getWCF1()); sheet.addCell(label); label = new Label(7, 9, "52%", cff.getWCF1()); sheet.addCell(label); // 第九列 number = new jxl.write.Number(8, 2, 5, cff.getWCF3(jxl.format.Colour.RED)); sheet.addCell(number); number = new jxl.write.Number(8, 3, 4, cff.getWCF3(jxl.format.Colour.YELLOW)); sheet.addCell(number); number = new jxl.write.Number(8, 4, 2, cff.getWCF3(jxl.format.Colour.BLUE)); sheet.addCell(number); number = new jxl.write.Number(8, 5, 5, cff.getWCF3(jxl.format.Colour.RED)); sheet.addCell(number); number = new jxl.write.Number(8, 6, 1, cff.getWCF3(jxl.format.Colour.GREEN)); sheet.addCell(number); number = new jxl.write.Number(8, 7, 2, cff.getWCF3(jxl.format.Colour.BLUE)); sheet.addCell(number); number = new jxl.write.Number(8, 8, 1, cff.getWCF3(jxl.format.Colour.GREEN)); sheet.addCell(number); number = new jxl.write.Number(8, 9, 3, cff.getWCF3(jxl.format.Colour.YELLOW)); sheet.addCell(number); } catch (RowsExceededException e) { e.printStackTrace(); } catch (WriteException e) { e.printStackTrace(); } } /** * 通过集合添加单元格 * * @param sheet * @param list */ public static void addCellsByList(WritableSheet sheet, List<Label> list) { for (Label label : list) { try { sheet.addCell(label); } catch (RowsExceededException e) { e.printStackTrace(); } catch (WriteException e) { e.printStackTrace(); } } } }