JXL示例

package com.coderdream.jxl;import java.io.File;import java.io.IOException;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import jxl.format.Alignment;import jxl.format.Border;import jxl.format.BorderLineStyle;import jxl.format.Colour;import jxl.format.VerticalAlignment;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;public class JxlUtil { /** * @param fileName * @throws IOException */ public static void createExcel(String filename) { File f = new File(filename); WritableWorkbook wwb = null; // 创建Excel工作表 WritableSheet ws = null; try { f.createNewFile(); wwb = Workbook.createWorkbook(f); ws = wwb.createSheet("Sheet1", 0);// 创建sheet ws.addCell(new Label(0, 8, "ABCD")); // 输出流 wwb.write(); // 关闭流 wwb.close(); } catch (WriteException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } /** * @param fileName * @throws IOException */ public static void writeExcel(String filename) { writeExcel(new File(filename)); } /** * @param fileName * @throws IOException */ public static void writeExcel(File file) { WritableWorkbook wwb = null; // 创建Excel工作表 WritableSheet sheet1 = null; Workbook wb = null; try { // Excel获得文件 wb = Workbook.getWorkbook(file); // 打开一个文件的副本，并且指定数据写回到原文件 wwb = Workbook.createWorkbook(file, wb); // 读取第一张工作表 sheet1 = wwb.getSheet(0); Label l = new Label(0, 0, "姓名");// 第1行 sheet1.addCell(l); l = new Label(1, 0, "电话"); sheet1.addCell(l); l = new Label(2, 0, "地址"); sheet1.addCell(l); l = new Label(0, 1, "小祝");// 第2行 sheet1.addCell(l); l = new Label(1, 1, "1314***0974"); sheet1.addCell(l); l = new Label(2, 1, "武汉武昌"); sheet1.addCell(l); // 添加一个工作表 WritableSheet sheet2 = wwb.createSheet("第二页", 1); sheet2.addCell(new Label(0, 0, "第二页的测试数据")); // 输出流 wwb.write(); // 关闭流 wwb.close(); } catch (WriteException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (BiffException e) { e.printStackTrace(); } } /** * @param fileName * @throws IOException */ public static void writeExcelWithFormat(String filename) { writeExcelWithFormat(new File(filename)); } /** * 增加带格式的内容 * * @param fileName * @throws IOException */ public static void writeExcelWithFormat(File file) { WritableWorkbook wwb = null; // 创建Excel工作表 WritableSheet sheet1 = null; Workbook wb = null; try { // Excel获得文件 wb = Workbook.getWorkbook(file); // 打开一个文件的副本，并且指定数据写回到原文件 // 原文件中某个Cell(单元格)的内容如果没有被修改，则会保留。 wwb = Workbook.createWorkbook(file, wb); // 读取第一张工作表 sheet1 = wwb.getSheet(0); // 合并单元格(左列，左行，右列，右行)从第1行第1列到第1行第3列 sheet1.mergeCells(0, 0, 2, 0); Label header = new Label(0, 0, "通讯录", getHeader()); sheet1.addCell(header);// 写入头 Label l = new Label(0, 1, "姓名", getTitle());// 第1行 sheet1.addCell(l); l = new Label(1, 1, "电话", getTitle()); sheet1.addCell(l); l = new Label(2, 1, "地址", getTitle()); sheet1.addCell(l); l = new Label(0, 2, "小祝", getNormolCell());// 第2行 sheet1.addCell(l); l = new Label(1, 2, "1314***0974", getNormolCell()); sheet1.addCell(l); l = new Label(2, 2, "武汉武昌", getNormolCell()); sheet1.addCell(l); sheet1.setColumnView(0, 20);// 设置列宽 sheet1.setColumnView(1, 20); sheet1.setColumnView(2, 40); sheet1.setRowView(0, 800);// 设置行高 sheet1.setRowView(1, 500); sheet1.setRowView(2, 500); // 添加一个工作表 WritableSheet sheet2 = wwb.createSheet("第二页", 1); sheet2.addCell(new Label(0, 0, "第二页的测试数据")); // 输出流 wwb.write(); // 关闭流 wwb.close(); } catch (WriteException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (BiffException e) { e.printStackTrace(); } } /** * 设置头的样式 * * @return */ public static WritableCellFormat getHeader() { WritableFont font = new WritableFont(WritableFont.TIMES, 24, WritableFont.BOLD);// 定义字体 WritableCellFormat format = null; try { font.setColour(Colour.BLUE);// 蓝色字体 format = new WritableCellFormat(font); format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中 format.setVerticalAlignment(VerticalAlignment.CENTRE);// 上下居中 format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框 format.setBackground(Colour.YELLOW);// 黄色背景 } catch (WriteException e1) { e1.printStackTrace(); } return format; } /** * 设置标题样式 * * @return */ public static WritableCellFormat getTitle() { WritableFont font = new WritableFont(WritableFont.TIMES, 14); WritableCellFormat format = null; try { font.setColour(Colour.BLUE);// 蓝色字体 format = new WritableCellFormat(font); format.setAlignment(Alignment.CENTRE); format.setVerticalAlignment(VerticalAlignment.CENTRE); format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK); } catch (WriteException e) { 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) { e.printStackTrace(); } return format; } public static void readExcel(String filename) { readExcel(new File(filename)); } public static void readExcel(File filename) { Workbook wb = null; try { wb = Workbook.getWorkbook(filename); Sheet s = wb.getSheet(0);// 第1个sheet Cell 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(); } } catch (IOException e) { e.printStackTrace(); } catch (BiffException e) { e.printStackTrace(); } }}