JXL 读工具类:
package jason.excel.util;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
/**
* 不支持2007
* @author Jason
*
*/
public class JXLReadUtil {
private String filePath = null;
private File file = null;
private Workbook wk = null;
private Map hmSheet = new HashMap();
private JXLReadUtil(String filePath) {
this.filePath = filePath;
this.file = new File(filePath);
}
private JXLReadUtil(File file) {
this.file = file;
}
public static JXLReadUtil getInstance(String filePath) {
return new JXLReadUtil(filePath);
}
public static JXLReadUtil getInstance(File file) {
return new JXLReadUtil(file);
}
public Workbook getWorkbook() {
if (wk == null) {
try {
wk = Workbook.getWorkbook(this.file);
} catch (Exception e) {
e.printStackTrace();
}
}
return wk;
}
/**
*
* @param index
* start with 0
* @return
*/
public Sheet getSheet(int index) {
wk = getWorkbook();
if (hmSheet.containsKey(index)) {
return (Sheet) hmSheet.get(index);
} else {
Sheet sheet = wk.getSheet(index);
hmSheet.put(index, sheet);
return sheet;
}
}
/**
*
* @param sheet
* @param row
* start with 0
* @return
*/
public String[] getContentsViaRow(Sheet sheet, int row) {
Cell[] rowCells = sheet.getRow(row);
int len = rowCells.length;
String[] strCells = new String[len];
for (int i = 0; i < len; i++) {
strCells[i] = rowCells[i].getContents();
}
return strCells;
}
/**
*
* @param sheet
* @param col
* start with 0
* @return
*/
public String[] getContentsViaCol(Sheet sheet, int col) {
Cell[] cells = sheet.getColumn(col);
int len = cells.length;
String[] strCols = new String[len];
Cell c = null;
for (int i = 0; i < len; i++) {
c = cells[i];
strCols[i] = c.getContents();
}
return strCols;
}
public List<String[]> getFirstSheetRowsContents() {
Sheet sheet = this.getSheet(0);
int rows = sheet.getRows();
List<String[]> ls = new ArrayList<String[]>();
for(int i=0;i<rows;i++) {
ls.add(getContentsViaRow(sheet,i));
}
return ls;
}
public List<String[]> getFirstSheetColsContents() {
Sheet sheet = this.getSheet(0);
int cols = sheet.getColumns();
List<String[]> ls = new ArrayList<String[]>();
for(int i=0;i<cols;i++) {
ls.add(getContentsViaCol(sheet,i));
}
return ls;
}
public static void main(String[] args) throws Exception {
JXLReadUtil util = JXLReadUtil.getInstance("f:\\CUST01.xls");
List<String[]> ls = util.getFirstSheetRowsContents();
for(String[] ss : ls) {
for(String s : ss) {
System.out.println(s);
}
}
List<String[]> lss = util.getFirstSheetColsContents();
for(String[] ss : lss) {
for(String s : ss) {
System.out.println(s);
}
}
}
}
JXL写工具类,简单版:
package com.sys.plugin.doc;
import java.io.File;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.pro.entity.Customer;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class JXLWriteUtil<T> {
private String fullFilePath;
private WritableWorkbook wk = null;
private Map hmSheet = new HashMap();
private JXLWriteUtil(String fullFilePath) {
this.setFullFilePath(fullFilePath);
this.createWorkbook();
}
private JXLWriteUtil(String filePath, String fileName) {
this.setFullFilePath(filePath + File.separator + fileName);
this.createWorkbook();
}
public static JXLWriteUtil getInstance(String filePath, String fileName) {
return new JXLWriteUtil(filePath,fileName);
}
public static JXLWriteUtil getInstance(String fullFilePath) {
return new JXLWriteUtil(fullFilePath);
}
private WritableWorkbook createWorkbook() {
if(wk == null) {
try {
wk = Workbook.createWorkbook(new File(this.getFullFilePath()));
}catch(Exception e) {
e.printStackTrace();
}
}
return this.wk;
}
public WritableSheet getSheet(int index, String name) {
WritableSheet sheet = (WritableSheet)this.hmSheet.get(index);
if(sheet == null) {
sheet = this.createWorkbook().createSheet(name, index);
this.hmSheet.put(index, sheet);
}
return sheet;
}
public WritableSheet setFirstSheetTitles(String[] titles, String sheetName) {
WritableSheet sheet = this.getSheet(0, sheetName);
try {
int len = titles.length;
for(int i = 0;i<len;i++) {
sheet.addCell(new Label(i,0,titles[i]));
}
}catch(Exception e) {
e.printStackTrace();
}
return sheet;
}
public WritableSheet setFisrtSheetCells(List<T> list, String[] names) {
WritableSheet sheet = this.getSheet(0, null);
Class clazz = null;
Object obj = null;
Method method = null;
try {
int len = list.size();
for(int i=0;i<len;i++) {
int length = names.length;
for(int j=0;j<length;j++) {
clazz = list.get(i).getClass();
method = clazz.getDeclaredMethod(names[j]);
obj = method.invoke(list.get(i));
if(obj == null) {
sheet.addCell(new Label(j,i+1,""));
} else {
sheet.addCell(new Label(j,i+1,String.valueOf(obj)));
}
}
}
}catch(Exception e) {
e.printStackTrace();
}
return sheet;
}
public void generateExcel() {
try {
this.createWorkbook().write();
this.createWorkbook().close();
}catch(Exception e) {
e.printStackTrace();
}
}
public String getFullFilePath() {
return fullFilePath;
}
public void setFullFilePath(String fullFilePath) {
this.fullFilePath = fullFilePath;
}
public static void main(String[] args) throws Exception {
String filePath = "c:\\temp";
String fileName = "test.xls";
List<Customer> list = null;//get data obj from db
JXLWriteUtil<Customer> util = JXLWriteUtil.getInstance(filePath, fileName);
String[] titles = new String[]{"客户编号","姓名","手机","车牌号","身份证","生日","性别","年龄","邮箱地址","驾照到期日"};
util.setFirstSheetTitles(titles, "客户信息");
String[] names = new String[]{"getCustid","getName","getPhone","getCarid","getIdno","getBirthday","getGender","getAge","getEmail","getExpiredate"};
util.setFisrtSheetCells(list, names);
util.generateExcel();
}
}