原文：

 

项目中要求读取excel文件内容，并将其转化为xml格式。常见读取excel文档一般使用POI和JExcelAPI这两个工具。这里我们介绍使用POI实现读取excel文档。

（注：HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls

        XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx

Workbook wb = null;//当excel是2003时if(isExcel2003){       wb = new HSSFWorkbook(is); }else{
   //当excel是2007时       wb = new XSSFWorkbook(is); }//读取Excel里面客户的信息//得到第一个shell  Sheet sheet=wb.getSheetAt(0);

）

例1：

/* * 使用POI读取EXCEL文件 */import java.io.File;import java.io.FileInputStream;import java.util.ArrayList;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;/** * * @author Hanbin */public class ReadExcel {    /**     * @param args the command line arguments     */    public static void main(String[] args)throws Exception {        read("d:\\demo.xls");    }    public static ArrayList read(String fileName){        ArrayList list = new ArrayList();        String sql = "";        try{            File f = new File(fileName);            FileInputStream fis = new FileInputStream(f);            HSSFWorkbook wbs = new HSSFWorkbook(fis);            HSSFSheet childSheet = wbs.getSheetAt(0);            System.out.println("行数:" + childSheet.getLastRowNum());            for(int i = 4;i

 

例2：

package edu.sjtu.erplab.poi;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.text.SimpleDateFormat;import java.util.Date;import java.util.HashMap;import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;/** * 操作Excel表格的功能类 */public class ExcelReader {    private POIFSFileSystem fs;    private HSSFWorkbook wb;    private HSSFSheet sheet;    private HSSFRow row;    /**     * 读取Excel表格表头的内容     * @param InputStream     * @return String 表头内容的数组     */    public String[] readExcelTitle(InputStream is) {        try {            fs = new POIFSFileSystem(is);            wb = new HSSFWorkbook(fs);        } catch (IOException e) {            e.printStackTrace();        }        sheet = wb.getSheetAt(0);        row = sheet.getRow(0);        // 标题总列数        int colNum = row.getPhysicalNumberOfCells();        System.out.println("colNum:" + colNum);        String[] title = new String[colNum];        for (int i = 0; i < colNum; i++) {            //title[i] = getStringCellValue(row.getCell((short) i));            title[i] = getCellFormatValue(row.getCell((short) i));        }        return title;    }    /**     * 读取Excel数据内容     * @param InputStream     * @return Map 包含单元格数据内容的Map对象     */    public Map
     
       readExcelContent(InputStream is) {        Map
      
        content = new HashMap
       
        ();        String str = "";        try {            fs = new POIFSFileSystem(is);            wb = new HSSFWorkbook(fs);        } catch (IOException e) {            e.printStackTrace();        }        sheet = wb.getSheetAt(0);        // 得到总行数        int rowNum = sheet.getLastRowNum();        row = sheet.getRow(0);        int colNum = row.getPhysicalNumberOfCells();        // 正文内容应该从第二行开始,第一行为表头的标题        for (int i = 1; i <= rowNum; i++) {            row = sheet.getRow(i);            int j = 0;            while (j < colNum) {                // 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据                // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean                // str += getStringCellValue(row.getCell((short) j)).trim() +                // "-";                str += getCellFormatValue(row.getCell((short) j)).trim() + "    ";                j++;            }            content.put(i, str);            str = "";        }        return content;    }    /**     * 获取单元格数据内容为字符串类型的数据     *     * @param cell Excel单元格     * @return String 单元格数据内容     */    private String getStringCellValue(HSSFCell cell) {        String strCell = "";        switch (cell.getCellType()) {        case HSSFCell.CELL_TYPE_STRING:            strCell = cell.getStringCellValue();            break;        case HSSFCell.CELL_TYPE_NUMERIC:            strCell = String.valueOf(cell.getNumericCellValue());            break;        case HSSFCell.CELL_TYPE_BOOLEAN:            strCell = String.valueOf(cell.getBooleanCellValue());            break;        case HSSFCell.CELL_TYPE_BLANK:            strCell = "";            break;        default:            strCell = "";            break;        }        if (strCell.equals("") || strCell == null) {            return "";        }        if (cell == null) {            return "";        }        return strCell;    }    /**     * 获取单元格数据内容为日期类型的数据     *     * @param cell     *            Excel单元格     * @return String 单元格数据内容     */    private String getDateCellValue(HSSFCell cell) {        String result = "";        try {            int cellType = cell.getCellType();            if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {                Date date = cell.getDateCellValue();                result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)                        + "-" + date.getDate();            } else if (cellType == HSSFCell.CELL_TYPE_STRING) {                String date = getStringCellValue(cell);                result = date.replaceAll("[年月]", "-").replace("日", "").trim();            } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {                result = "";            }        } catch (Exception e) {            System.out.println("日期格式不正确!");            e.printStackTrace();        }        return result;    }    /**     * 根据HSSFCell类型设置数据     * @param cell     * @return     */    private String getCellFormatValue(HSSFCell cell) {        String cellvalue = "";        if (cell != null) {            // 判断当前Cell的Type            switch (cell.getCellType()) {            // 如果当前Cell的Type为NUMERIC            case HSSFCell.CELL_TYPE_NUMERIC:            case HSSFCell.CELL_TYPE_FORMULA: {                // 判断当前的cell是否为Date                if (HSSFDateUtil.isCellDateFormatted(cell)) {                    // 如果是Date类型则，转化为Data格式                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00                    //cellvalue = cell.getDateCellValue().toLocaleString();                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12                    Date date = cell.getDateCellValue();                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");                    cellvalue = sdf.format(date);                }                // 如果是纯数字                else {                    // 取得当前Cell的数值                    cellvalue = String.valueOf(cell.getNumericCellValue());                }                break;            }            // 如果当前Cell的Type为STRIN            case HSSFCell.CELL_TYPE_STRING:                // 取得当前的Cell字符串                cellvalue = cell.getRichStringCellValue().getString();                break;            // 默认的Cell值            default:                cellvalue = " ";            }        } else {            cellvalue = "";        }        return cellvalue;    }    public static void main(String[] args) {        try {            // 对读取Excel表格标题测试            InputStream is = new FileInputStream("d:\\test2.xls");            ExcelReader excelReader = new ExcelReader();            String[] title = excelReader.readExcelTitle(is);            System.out.println("获得Excel表格的标题:");            for (String s : title) {                System.out.print(s + " ");            }            // 对读取Excel表格内容测试            InputStream is2 = new FileInputStream("d:\\test2.xls");            Map
        
          map = excelReader.readExcelContent(is2);            System.out.println("获得Excel表格的内容:");            for (int i = 1; i <= map.size(); i++) {                System.out.println(map.get(i));            }        } catch (FileNotFoundException e) {            System.out.println("未找到指定路径的文件!");            e.printStackTrace();        }    }}