java读取excel内容

使用JAVA代码读取excel内容

前段时间，由于公司需要进行excel的导入导出，系统复习了一下POI的使用，详细内容如下：

代码如下：

public class POITest {


    private static Workbook wb;
    private static Sheet sheet;


    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";


    /**
     * @return
     * @Description //读取表头
     * @Param
     **/

    public static Map<Integer, String> readExcelTitle(InputStream inputStream, String suffix) {
        getWorkbook(inputStream,suffix);
        sheet = wb.getSheetAt(0);//获取文件中的第一个表格（工作簿）
        Row row = sheet.getRow(0);//获取第一行 也就是表头

        //标题总列数
        int colNum = row.getPhysicalNumberOfCells();

        Map<Integer,String> map = new HashMap<>();
        for (int i = 0; i <colNum ; i++) {
            map.put(i, row.getCell(i).getStringCellValue());

        }
        return map;

    }

    /**
     * @return
     * @Description 读取excel内容
     * @Param
     **/
    public static Map<Integer, Map<Integer, String>> readExcelContent(InputStream inputStream,String suffix) {

        Map<Integer, Map<Integer, String>> content = new HashMap<>();
        getWorkbook(inputStream,suffix );
        sheet = wb.getSheetAt(0);
        int rowNum = sheet.getLastRowNum();//获取总行数

        //正文内容从第二行开始，第一行为表头的标题
        for (int i = 1; i <rowNum ; i++) {
            Row row = sheet.getRow(i);//读取第i行的内容
            int j = 0;
            Map<Integer, String> cellValue = new HashMap<>();
            while (j<rowNum) {
                String obj = getCellFormatValue(row.getCell(j));//数据类型转换方法
                cellValue.put(j,obj);
                j++;
            }
            content.put(i,cellValue );

        }
            return content;
    }

    private static String getCellFormatValue(Cell cell) {


        String cellValue = "";
        if (cell!=null) {

            //判断当前cell的类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                case Cell.CELL_TYPE_FORMULA:{

                    //判断当前cell是否为date
                    if (DateUtil.isCellDateFormatted(cell)) {
                        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("YYYY-MM-dd HH:mm:ss");
                        Instant instant = cell.getDateCellValue().toInstant();
                        ZoneId zoneId = ZoneId.systemDefault();
                        LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, zoneId);
                        cellValue = dateTimeFormatter.format(localDateTime);

                    }else {
                        //如果是纯数字 取当前cell的数值
                        cellValue = String.valueOf(cell);
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:

                    //取得当前cell的字符串
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                default:
                    //默认值
                    cellValue = "";
            }

        }

        return cellValue;
    }


    private static void getWorkbook(InputStream inputStream, String suffix) {


        try {
            //2003
            if(EXCEL_XLS.equals(suffix)){
                 wb = new HSSFWorkbook(inputStream);
                 //2007、2010
            } else if (EXCEL_XLSX.equals(suffix)) {
                wb = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                inputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {


        try {
            InputStream inputStream = new FileInputStream("F:/abc.xlsx");
            Map<Integer, Map<Integer,String>> xlsx = readExcelContent(inputStream, "xlsx");
            System.out.println("xlsx = " + xlsx);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }


    }
}