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

    三、使用

  • 相关阅读:
    【UVa】And Then There Was One(dp)
    【vijos】1006 晴天小猪历险记之Hill(dijkstra)
    【UVa】Palindromic Subsequence(dp+字典序)
    【UVa】Wavio Sequence(dp)
    【UVa】Salesmen(dp)
    【UVa】Partitioning by Palindromes(dp)
    小结:特殊的技巧
    java 中 进程和线程的区别
    java中的 sleep() 和 wait() 有什么区别?
    Java 静态static 关键字作用
  • 原文地址:https://www.cnblogs.com/ld-swust/p/6257383.html
Copyright © 2020-2023  润新知