java应用jxl API读写excel:
package tf.excel;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import common.Logger;
public class JxlTest {
private static Logger log = Logger.getLogger(JxlTest.class);
private static SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
private File file;
public JxlTest(String fileName) {
this.file = new File(fileName);
}
public JxlTest(File file) {
this.file = file;
}
public static void main(String[] args) {
JxlTest jxl = new JxlTest("users.xls");
jxl.writeExcel();
List<User> list = jxl.readExcel();
for (User u : list) {
log.info(u.yhm + "--" + u.xm + "--" + u.age + "--"
+ df.format(u.sr));
}
}
public void writeExcel() {
WritableWorkbook ww;
try {
ww = Workbook.createWorkbook(file);
WritableSheet ws = ww.createSheet("用户信息", 0);
Operate.addLabelToSheet(ws, 0, 0, 3, 0, "用户信息", Style
.getHeaderStyle());
Operate.addLabelToSheet(ws, 0, 1, "用户名", Style.getTitleStyle());
Operate.addLabelToSheet(ws, 1, 1, "姓名", Style.getTitleStyle());
Operate.addLabelToSheet(ws, 2, 1, "年龄", Style.getTitleStyle());
Operate.addLabelToSheet(ws, 3, 1, "生日", Style.getTitleStyle());
Operate.addLabelToSheet(ws, 0, 2, "tt", Style.getTitleStyle());
Operate.addLabelToSheet(ws, 1, 2, "糖糖", Style.getTitleStyle());
Operate.addLabelToSheet(ws, 2, 2, 25, Style.getTitleStyle());
Operate.addLabelToSheet(ws, 3, 2, df.parse("1984-08-12"), Style
.getDateStyle());
Operate.addLabelToSheet(ws, 0, 3, "ff", Style.getTitleStyle());
Operate.addLabelToSheet(ws, 1, 3, "飞飞", Style.getTitleStyle());
Operate.addLabelToSheet(ws, 2, 3, 24, Style.getTitleStyle());
Operate.addLabelToSheet(ws, 3, 3, df.parse("1985-11-25"), Style
.getDateStyle());
for (int i = 0; i < 4; i++) {
ws.setColumnView(i, 16);
}
ws.setRowView(0, 20);
ww.write();
ww.close();
log.info("写入excel成功!");
} catch (Exception e) {
log.info("写入excel失败!");
e.printStackTrace();
}
}
public List<User> readExcel() {
List<User> list = new ArrayList<User>();
Workbook wb;
try {
wb = Workbook.getWorkbook(file);
Sheet sheet = wb.getSheet(0);
int row = sheet.getRows();
int col = sheet.getColumns();
for (int i = 2; i < row; i++) {
User user = new User();
for (int j = 0; j < col; j++) {
Cell cell = sheet.getCell(j, i);
if (cell.getType() == CellType.LABEL) {
LabelCell lc = (LabelCell) cell;
switch (j) {
case 0:
user.setYhm(lc.getContents());
case 1:
user.setXm(lc.getContents());
}
}
if (cell.getType() == CellType.NUMBER) {
NumberCell nc = (NumberCell) cell;
user.setAge((int) nc.getValue());
}
if (cell.getType() == CellType.DATE) {
DateCell dc = (DateCell) cell;
user.setSr(dc.getDate());
}
}
list.add(user);
}
log.info("读取excel成功!");
} catch (Exception e) {
log.info("读取excel失敗!");
e.printStackTrace();
}
return list;
}
static class Style {
/**
* 页头样式
*/
public static WritableCellFormat getHeaderStyle() throws Exception {
WritableFont font = new WritableFont(WritableFont.TIMES, 14); // 设置14号字体
font.setColour(Colour.BLUE); // 设置字体颜色
font.setBoldStyle(WritableFont.BOLD); // 设置粗体字
WritableCellFormat format = new WritableCellFormat(font); // 设置单元格样式
format.setBackground(Colour.LIGHT_GREEN); // 设置背景颜色
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);//设置边界样式
format.setAlignment(Alignment.CENTRE); // 设置水平位置
format.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置竖直位置
return format;
}
/**
* 标题样式
*/
public static WritableCellFormat getTitleStyle() throws Exception {
WritableFont font = new WritableFont(WritableFont.TIMES, 14);
font.setColour(Colour.GREEN);
WritableCellFormat format = new WritableCellFormat(font);
format.setBackground(Colour.VERY_LIGHT_YELLOW);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE);
return format;
}
/**
* 内容样式
*/
public static WritableCellFormat getContentStyle() throws Exception {
WritableFont font = new WritableFont(WritableFont.TIMES, 14);
font.setColour(Colour.BLUE);
WritableCellFormat format = new WritableCellFormat(font);
format.setBackground(Colour.VERY_LIGHT_YELLOW);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE);
return format;
}
/**
* 普通样式
*/
public static WritableCellFormat getNormalStyle() throws Exception {
WritableFont font = new WritableFont(WritableFont.TIMES, 14);
font.setColour(Colour.BLUE);
WritableCellFormat format = new WritableCellFormat(
DateFormats.FORMAT12);
format.setBackground(Colour.VERY_LIGHT_YELLOW);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE);
return format;
}
/**
* 日期样式
*/
public static WritableCellFormat getDateStyle() throws Exception {
WritableFont font = new WritableFont(WritableFont.TIMES, 14);
font.setColour(Colour.BLUE);
WritableCellFormat format = new WritableCellFormat(
DateFormats.FORMAT1);
format.setBackground(Colour.VERY_LIGHT_YELLOW);
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE);
return format;
}
}
static class Operate {
/**
* 写入文本
*/
public static void addLabelToSheet(WritableSheet ws, int col, int row,
String cont, CellFormat format) throws Exception {
Label label = new Label(col, row, cont, format);
ws.addCell(label);
}
/**
* 写入文本(合并后的单元格)
*/
public static void addLabelToSheet(WritableSheet ws, int firstCol,
int firstRow, int lastCol, int lastRow, String cont,
CellFormat format) throws Exception {
ws.mergeCells(firstCol, firstRow, lastCol, lastRow);
addLabelToSheet(ws, firstCol, firstRow, cont, format);
}
/**
* 写入数字
*/
public static void addLabelToSheet(WritableSheet ws, int col, int row,
double num, CellFormat format) throws Exception {
jxl.write.Number number = new jxl.write.Number(col, row, num,
format);
ws.addCell(number);
}
/**
* 写入日期
*/
public static void addLabelToSheet(WritableSheet ws, int col, int row,
Date date, CellFormat format) throws Exception {
DateTime dt = new DateTime(col, row, date, format, DateTime.GMT);
ws.addCell(dt);
}
}
static class User {
private String yhm;
private String xm;
private int age;
private Date sr;
public String getYhm() {
return yhm;
}
public void setYhm(String yhm) {
this.yhm = yhm;
}
public String getXm() {
return xm;
}
public void setXm(String xm) {
this.xm = xm;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getSr() {
return sr;
}
public void setSr(Date sr) {
this.sr = sr;
}
}
}