欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

利用jxl操作Excel

程序员文章站 2024-02-24 13:31:46
...
我这里只是几个简单的操作。更多操作请看它自身带的API及例子。

需要导入jxl的jar包,在哪儿下我就不说了,自己去摆渡一下。
[quote]
jexcelapi_HOME/jxl.jar
[/quote]

package com.yx.zzg.jxl;

import java.awt.Color;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import jxl.Cell;
import jxl.CellType;
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.DateFormat;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class JxlUtil {

public static void createExcel(String path, String sheetName,
String[] dataTitle) {
WritableWorkbook wwk = null;
OutputStream os = null;
try {
os = new BufferedOutputStream(new FileOutputStream(path));
wwk = Workbook.createWorkbook(os);
WritableSheet sheet = wwk.createSheet(sheetName, 0);
initSheet(sheet);
Label label;
for (int i = 0; i < dataTitle.length; i++) {
label = new Label(i, 0, dataTitle[i], setTitleCellFormat());
sheet.addCell(label);
}
insertRowData(sheet, 1, new String[] { "000000001", "张三", "90",
"75", "85", "240" },
setDataCellFormat(CellType.STRING_FORMULA));
wwk.write();
wwk.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} finally {
try {
if (os != null) {
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}

/**
* 初始化表格属性
*
* @param sheet
*/
public static void initSheet(WritableSheet sheet) {
// 设置列的默认宽度
sheet.getSettings().setDefaultColumnWidth(20);
// 设置第一列的宽度
sheet.setColumnView(0, 20);
}

/**
* @param args
*/
public static void main(String[] args) {
String[] title = { "学号", "姓名", "语文", "数学", "英语", "总分" };
String fromPath = "C:\\Users\\toshiba\\work\\dd.xls";
createExcel(fromPath, "sheet1", title);
readExcel(fromPath);
}

public static void readExcel(String fromPath) {
File fromFile = new File(fromPath);
Workbook wb = null;
try {
wb = Workbook.getWorkbook(fromFile);
Sheet sheet = wb.getSheet(0);
for (int i = 0; i < sheet.getRows(); i++) {
for (int j = 0; j < sheet.getColumns(); j++) {
Cell c = sheet.getCell(j, i);
System.out.print(c.getContents() + " ");
if ((j + 1) == sheet.getColumns()) {
System.out.println();
}
}
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
wb.close();
}
}

/**
* 设置数据的格式并返回
*
* @param type
* @return
*/
public static WritableCellFormat setDataCellFormat(CellType type) {
WritableCellFormat wcf = null;
if (type == CellType.NUMBER || type == CellType.NUMBER_FORMULA) {
NumberFormat nf = new NumberFormat("#.00");
wcf = new WritableCellFormat(nf);
} else if (type == CellType.DATE || type == CellType.DATE_FORMULA) {
DateFormat dd = new DateFormat("yyyy-MM-dd hh:mm:ss");
wcf = new WritableCellFormat(dd);
} else {
WritableFont wf = new WritableFont(WritableFont.TIMES, 10,
WritableFont.NO_BOLD, false);
wcf = new WritableCellFormat(wf);
}
try {
// 对齐方式
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
// 边框
wcf.setBorder(Border.LEFT, BorderLineStyle.THIN);
wcf.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
wcf.setBorder(Border.RIGHT, BorderLineStyle.THIN);
// 背景色
wcf.setBackground(Colour.WHITE);
// 自动换行
wcf.setWrap(true);
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return wcf;
}

/**
* 设置数据表头格式并返回
*
* @return
*/
public static WritableCellFormat setTitleCellFormat() {
WritableCellFormat wcf = null;
WritableFont wf = new WritableFont(WritableFont.TIMES, 12,
WritableFont.NO_BOLD, false);
try {
wf.setColour(Colour.RED);
wcf = new WritableCellFormat(wf);
// 设置对齐格式
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
// 设置边框
wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
// 设置背景色
wcf.setBackground(Colour.GREY_25_PERCENT);
} catch (WriteException e) {
e.printStackTrace();
}
return wcf;
}

/**
* 插入一条数据
*
* @param sheet
* @param row
* @param dataArr
* @param format
*/
public static void insertRowData(WritableSheet sheet, Integer row,
String[] dataArr, WritableCellFormat format) {
try {
Label label;
for (int i = 0; i < dataArr.length; i++) {
label = new Label(i, row, dataArr[i], format);
sheet.addCell(label);
}
} catch (Exception e) {
e.printStackTrace();
}
}

}