1、工具类:
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();
}
}
}
2、 测试代码:
public class JxlTest {
/**
* @param args
*/
public static void main(String[] args) {
String filename = "D:/newExcel.xls";
String filename2 = "D:/newExcel2.xls";
JxlUtil.createExcel(filename);
JxlUtil.writeExcel(filename);
JxlUtil.createExcel(filename2);
JxlUtil.writeExcelWithFormat(filename2);
JxlUtil.readExcel(filename2);
}
}
输出结果:
通讯录
姓名 电话 地址
小祝 1314***0974 武汉武昌
ABCD