以下是同事寫(xiě)的一個(gè)jxl的工具類(lèi)
package test.jxl.util;
import java.io.IOException;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import jxl.Workbook;
import jxl.write.Blank;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class ConvinWriter {
/**
* 寫(xiě)入一行
* @param sheet
* @param array
* @param row
* @return the number of cols written
*/
public static int writeRow(WritableSheet sheet, Object[] array, int row) {
int colsWritten = 0;
int col = 0;
for (Object value : array) {
colsWritten += writeObject(sheet, value, col ++, row) ? 1 : 0;
}
return colsWritten;
}
/**
* 寫(xiě)入一行
* @param sheet
* @param array
* @param beginCol
* @param endCol
* @param row
* @return the number of cols written
*/
public static int writeRow(WritableSheet sheet, Object[] array, int beginCol, int endCol, int row) {
int colsWritten = 0;
int col = beginCol;
for (Object value : array) {
if (col == endCol) {
break;
}
colsWritten += writeObject(sheet, value, col ++, row) ? 1 : 0;
}
return colsWritten;
}
/**
* 寫(xiě)入一行
* @param sheet 表單
* @param list 數(shù)據(jù)
* @param row 所在行
* @return the number of cols written
*/
public static int writeRow(WritableSheet sheet, Collection<Object> list, int row) {
int colsWritten = 0;
int col = 0;
for (Object value : list) {
colsWritten += writeObject(sheet, value, col ++, row) ? 1 : 0;
}
return colsWritten;
}
/**
* 寫(xiě)入一行
* @param sheet
* @param list
* @param beginCol >=0, inclusive
* @param endCol >0, exclusive
* @param row
* @return the number of cols written
*/
public static int writeRow(WritableSheet sheet, Collection<Object> list, int beginCol, int endCol, int row) {
int colsWritten = 0;
int col = beginCol;
for (Object value : list) {
if (col == endCol) {
break;
}
colsWritten += writeObject(sheet, value, col ++, row) ? 1 : 0;
}
return colsWritten;
}
/**
* 向Sheet寫(xiě)入List的數(shù)據(jù)
* @param sheet
* @param keyArray
* @param list
* @param beginRow
* @return the number of rows written
*/
public static int writeMapListOfStringKey(WritableSheet sheet, String[] keyArray, Collection<Map<String, Object>> list, int beginRow){
int count = 0;
int rowCount = beginRow;
for (Map<String, Object> element : list) {
boolean bl = writeStringKeyMap(sheet, keyArray, element, rowCount++);
if(bl)count++;
}
return count;
}
/**
* 寫(xiě)入一個(gè)Map的數(shù)據(jù)到一行
* @param sheet
* @param keyArray
* @param map
* @param row
* @return
*/
public static boolean writeStringKeyMap(WritableSheet sheet, String[] keyArray, Map<String,Object> map, int row){
int col = 0;
for (String key : keyArray) {
key = key.trim();
Object value = map.get(key);
writeObject(sheet, value, col ++, row);
}
return false;
}
/**
* 寫(xiě)入一個(gè)對(duì)象
* @param sheet
* @param value
* @param col
* @param row
*/
public static boolean writeObject(WritableSheet sheet, Object value, int col, int row) {
if (value == null) {
return addCell(sheet, new Blank(col, row));
} else if (value instanceof Number) {
Number new_name = (Number) value;
return addCell(sheet, new jxl.write.Number(col, row, new_name.doubleValue()));
} else if (value instanceof Boolean) {
Boolean bool = (Boolean) value;
return addCell(sheet, new jxl.write.Boolean(col, row, bool.booleanValue()));
} else if (value instanceof java.util.Date) {
java.util.Date new_name = (java.util.Date) value;
return addCell(sheet, new DateTime(col, row, new_name));
} else {
return addCell(sheet, new Label(col, row, value.toString()));
}
}
private static boolean addCell(WritableSheet sheet, WritableCell cell) {
try {
sheet.addCell(cell);
} catch (RowsExceededException e) {
e.printStackTrace();
return false;
} catch (WriteException e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* 將一個(gè)List的內(nèi)容寫(xiě)到一個(gè)新的Sheet
* @param path 文件路徑
* @param titleArray 行題頭
* @param keyArray 行題頭 Key
* @param content
* @param sheetName
* @return boolean
*/
public static boolean writeInNewSheet (String path, String[] titleArray, String[] keyArray, Collection<Map<String, Object>> content, String sheetName) {
java.io.File file = new java.io.File(path);
return writeInNewSheet (file, titleArray, keyArray, content, sheetName);
}
/**
* create file
* @param file
* @return
*/
private static boolean createFile(java.io.File file) {
if (!file.exists()) {
try {
if (file.getParentFile().mkdirs()) {
if (file.createNewFile()) {
// do nothing
} else {
return false;
}
} else {
return false;
}
} catch (java.io.IOException e) {
e.printStackTrace();
return false;
}
}
return true;
}
/**
* get the workbook
* @param file
* @return
*/
public static Workbook getWorkBook(java.io.File file) {
jxl.Workbook exists = null;
if (createFile(file)) {
try {
exists = jxl.Workbook.getWorkbook(file);
} catch (jxl.read.biff.BiffException e) {
e.printStackTrace();
return null;
} catch (java.io.IOException e) {
e.printStackTrace();
return null;
}
} else {
return null;
}
return exists;
}
/**
* 創(chuàng)建workbook
* @param file
* @return writableworkbook, or null when failed
*/
public static WritableWorkbook createWorkbook (java.io.File file) {
jxl.Workbook exists = getWorkBook(file);
jxl.write.WritableWorkbook workbook = null;
try {
if (exists != null) {
workbook = jxl.Workbook.createWorkbook(file, exists);
} else {
workbook = jxl.Workbook.createWorkbook(file);
}
} catch (IOException e) {
e.printStackTrace();
return null;
}
return workbook;
}
/**
* 將一個(gè)List的內(nèi)容寫(xiě)到一個(gè)新的Sheet
* @param file 文件
* @param titleArray 行題頭
* @param keyArray 行題頭 Key
* @param content
* @param sheetName
*/
public static boolean writeInNewSheet (java.io.File file, String[] titleArray, String[] keyArray, Collection<Map<String, Object>> content, String sheetName) {
jxl.write.WritableWorkbook wookbook = createWorkbook(file);
int sheetNum = wookbook.getNumberOfSheets();
try {
WritableSheet sheet = wookbook.createSheet(sheetName, sheetNum);
ConvinWriter.writeRow(sheet, titleArray, 0);
ConvinWriter.writeMapListOfStringKey(sheet, keyArray, content, 1);
wookbook.write();
} catch (IOException e) {
e.printStackTrace();
return false;
} finally {
try {
wookbook.close();
} catch (WriteException e) {
e.printStackTrace();
return false;
} catch (java.io.IOException e) {
e.printStackTrace();
return false;
}
}
return true;
}
/**
* @param args
*/
public static void main(String[] args) {
List<Object> list = new java.util.ArrayList<Object>();
list.add(null);
list.add("String");
list.add(new java.util.Date());
list.add(12345);
list.add(false);
list.add(new ConvinWriter());
List <Map<String, Object>> mapList = new java.util.ArrayList<Map<String, Object>>();
String[] keyArray = new String [] {
"Blank", "String", "DateTime", "Number", "Boolean", "Object"
};
for (int i = 0; i < 3; i ++) {
Map<String, Object> map = new java.util.HashMap<String, Object>(keyArray.length);
if (i == 0)map.put("Blank", null);
map.put("String", "String " + i);
map.put("DateTime", new java.util.Date());
map.put("Number", i);
map.put("Boolean", i % 2 == 0);
map.put("Object", map);
mapList.add(map);
}
// FIXME
java.io.File file = new java.io.File("E:/tempJxlFile.xls");
jxl.Workbook exists = null;
if (file.exists()) {
try {
exists = jxl.Workbook.getWorkbook(file);
} catch (jxl.read.biff.BiffException e) {
e.printStackTrace();
} catch (java.io.IOException e) {
e.printStackTrace();
}
} else {
try {
file.createNewFile();
} catch (java.io.IOException e) {
e.printStackTrace();
}
}
jxl.write.WritableWorkbook wookbook = null;
try {
if (exists != null) {
wookbook = jxl.Workbook.createWorkbook(file, exists);
} else {
wookbook = jxl.Workbook.createWorkbook(file);
}
int sheetNum = wookbook.getNumberOfSheets();
WritableSheet sheet = wookbook.createSheet("testSheet" + (sheetNum + 1), sheetNum);
ConvinWriter.writeRow(sheet, keyArray, 0);
ConvinWriter.writeRow(sheet, list, 1);
ConvinWriter.writeMapListOfStringKey(sheet, keyArray, mapList, 2);
} catch (java.io.IOException e) {
e.printStackTrace();
} finally {
try {
wookbook.write();
wookbook.close();
} catch (WriteException e) {
e.printStackTrace();
} catch (java.io.IOException e) {
e.printStackTrace();
}
}
}
}