JXL应用-解析excel工具包

一、项目背景：

   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;
    }