excel读取

 

一、jar包

二、工具类

package excel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class XLSOprUtil {
    private static final Logger logger = LoggerFactory.getLogger(XLSOprUtil.class);

    public static void main(String[] args) throws Exception {
        final String filename = "userInfo.xlsx";
        new XLSOprUtil(filename).readExcel(0);
    }

    private InputStream inp;
    private String filePath;

    public XLSOprUtil(String filePath) throws FileNotFoundException {
        this.inp = new FileInputStream(filePath);
        this.filePath = filePath;
    }

    /**
     * 读取xls文件内容
     * 
     * @param isCloseStream
     *            true:读取内容完成后，关闭流；false：不管比
     * @return
     */
    public List<RowContent> readExcel(int sheetIndex, boolean isCloseStream) {
        try {
            long s1 = System.nanoTime();
            Workbook wb = getXLSWorkBook(this.filePath, this.inp);

            if (wb == null)
                return null;
            Sheet sheet = getXLSSheet(wb, sheetIndex);

            List<RowContent> rowsContent = readSheetContent(sheet);

            long s2 = System.nanoTime();
            logger.debug("readSheetContent coast times:" + (s2 - s1) / 1000000);

            return rowsContent;
        } catch(Exception e)
        {
            logger.error("readExcel 异常", e);
            return null;
        } finally
        {
            if(isCloseStream)
            {
                destory();
            }
        }
    }

    /**
     * 重载，默认读取完成后，关闭流
     *
     * @return
     */
    public List<RowContent> readExcel(int sheetIndex) {
        return readExcel(sheetIndex, true);
    }

    /**
     *
     * @param sheet
     * @return
     */
    public List<RowContent> readSheetContent(Sheet sheet) {
        if (sheet == null) {
            return null;
        }

        List<RowContent> rowsContent = new ArrayList<XLSOprUtil.RowContent>();

        int itemNum = sheet.getLastRowNum();
        logger.info("Sheet名称：{}, 行数：{}", sheet.getSheetName(), itemNum);

        for (Row row : sheet) {
            int cellNum = row.getLastCellNum();
            String[] cells = new String[cellNum];
            int index = 0;
            boolean isAllEmpty = true; // 判断某一行内容是否为空
            for (Cell cell : row) {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    // System.out.print("数字型单元格内容：" + cell.getNumericCellValue()
                    // + "  ");
                    cells[index++] = String.valueOf(cell.getNumericCellValue());
                    isAllEmpty = false;
                    break;
                case Cell.CELL_TYPE_STRING:
                    // System.out.print("字符串型单元格内容：" + cell.getStringCellValue()
                    // + "  ");
                    cells[index++] = String.valueOf(cell.getStringCellValue());
                    isAllEmpty = false;
                    break;
                default:
                    index++;
                    break;
                }
            }

            if (!isAllEmpty) {
                RowContent rowContent = new RowContent();
                rowContent.setRowCells(cells);
                rowsContent.add(rowContent);
            }
        }

        return rowsContent;
    }

    /**
     *
     */
    public void destory() {
        if (inp != null) {
            try {
                inp.close();
                logger.debug("--- 关闭读取的文件流 成功 ----");
            } catch (Exception e) {
                logger.error(e.toString());
            }
        }
    }

    /**
     * 适配不同的excel文档版本
     *
     * @param filename
     * @param inp
     * @return
     * @throws IOException
     */
    public Workbook getXLSWorkBook(String filename, InputStream inp)
            throws IOException {
        if (filename.matches("^(.*.xls)$")) {
            return new HSSFWorkbook(inp);
        } else {
            return new XSSFWorkbook(inp);
        }
    }

    /**
     * 获取指定的工作表
     * 
     * @param wb
     * @param number
     * @return
     */
    public Sheet getXLSSheet(Workbook wb, int number) {
        if (wb != null) {
            return wb.getSheetAt(number);
        }
        return null;
    }

    @SuppressWarnings("serial")
    public class RowContent implements Serializable{
        private String[] rowCells;

        /**
         * @return the rowCells
         */
        public String[] getRowCells() {
            return rowCells;
        }

        /**
         * @param rowCells
         *            the rowCells to set
         */
        public void setRowCells(String[] rowCells) {
            this.rowCells = rowCells;
        }
    }
}

 

三、使用