一、项目背景:
Jxl是开源javaexcelAPI项目,项目主页http://jxl.sourceforge.net/。
官网介绍:
Java Excel APIis a mature, open source java API enabling developers to read, write, andmodifiy Excel spreadsheets dynamically. Now java developers can read Excelspreadsheets, modify them with a convenient and simple API, and write thechanges to any output stream (e.g. disk, HTTP, database, or any socket).
Anyoperating system which can run a Java virtual machine (i.e., not just Windows)can both process and deliver Excel spreadsheets. Because it is Java, the APIcan be invoked from within a servlet, thus giving access to Excel spreadsheetsover internet and intranet web applications.
Some Features
· Reads data fromExcel 95, 97, 2000, XP, 2003 workbooks
· Reads and writesformulas (Excel 97 and later only)
· Generatesspreadsheets in Excel 2000 format
· Supports font,number and date formatting
· Supports shading,bordering, and coloring of cells
· Modifies existingworksheets
· Isinternationalized, enabling processing in almost any locale, country, language,or character encoding (formulas are currently only supported in English,French, Spanish, and German, but more can be added if translated)
· Supports copyingof charts
· Supports insertion and copying of images into spreadsheets
· Supports logging with Jakarta CommonsLogging, log4j, JDK 1.4 Logger
二、项目应用
应用指南地址http://www.andykhan.com/jexcelapi/tutorial.html
1、应用包 jxl.jar
2、解析EXCEL
2.1基本元素
a) Workbook(可通过解析输入流或者文件对象获取)
api:workBook= Workbook.getWorkbook(InputStream/File);
b) sheet(行)
api:workBook.getSheet(index);
c) row(行)
api:sheet.getRow(index);
d) column(列)
api:sheet.column(index);
e) cell(单元格)
api:row.getCell(index),column.getCell(index),sheet.getCell(rowIndex,columnIndex)
jdoc地址
http://jexcelapi.sourceforge.net/resources/javadocs/current/docs/
2.2应用实例
1)
/**
* 获取excel对象
*
* @param is
* @return
* @throws BusinessException
*/
public static Workbook getWorkBook(InputStream is) throws BusinessException{
Workbook workBook = null;
try {
workBook = Workbook.getWorkbook(is);
} catch (BiffException e) {
throw new BusinessException("生成Excel工作薄出错!");
} catch (IOException e) {
throw new BusinessException("读取输入流出错!");
}
return workBook;
}
2)
/**
* 获取sheet内容,以行为map对象(key值为列值)组装的数组
*
* @param sheet
* @return
*/
public static List<Map<String, String>> getRowList(Sheetsheet,
int startRowIndex, int startColumnIndex) {
int rowCount = sheet.getRows();// 总行数
int columnCount = sheet.getColumns();// 总列数
List<Map<String, String>> result = newArrayList<Map<String, String>>();
for (int r = startRowIndex; r < rowCount; r++) {// 遍历
Map data = new HashMap<String, String>();
for (int c = startColumnIndex; c < columnCount; c++) {
data.put(String.valueOf(c), sheet.getCell(c,r).getContents().trim());
}
result.add(data);
}
return result;
}