Java 读取Excel文件(xlsx/xls/csv)

1. 读取xlsx

private static FormulaEvaluator evaluator;
String static void readXlsx(String path) {
       try{
            // 读取的时候可以使用流，也可以直接使用文件名
            FileInputStream is =  new FileInputStream(path);
            Workbook xwb = new XSSFWorkbook(is);
            // 循环工作表sheet
            for(int numSheet = 0; numSheet < xwb.getNumberOfSheets(); numSheet++) {
                Sheet sheet = xwb.getSheetAt(numSheet);
                if(sheet == null) {
                    continue;
                }
               // 循环row，如果第一行是字段，则 numRow = 1
              for(int numRow = 0; numRow <= sheet.getLastRowNum(); numRow++) {
                  Row row = sheet.getRow(numRow);
                  if(row == null) {
                    continue;
                  }
                  // 循环cell
                 for(int numCell = 0; numCell < row.getLastCellNum(); numCell++) {
                    Cell cell = row.getCell(numCell);
                    if(cell == null) {
                        continue;
                        }
                    // 打印数据
                    System.out.println("xlsx表格中读取的数据" + getValue(cell));
                 }
              }
            }            
      }catch(Exception e) {
          e.printStackTrace();
      }
}
public static String getValue(Cell cell) {
      String val = null;
      switch(cell.getCellType()) {
            case FORMULA:  //公式类型
                 // 先计算表达式 
                 val = String.valueOf(evaluator.evaluate(cell).getNumericCellValue());
                 break;
            case BOOLEAN:  //布尔类型
                 val = String.valueOf(cell.getBooleanCellValue()); 
                 break; 
            case STRING:   // 字符串类型
                 val = cell.getStringCellValue().trim();
                 break;
            case NUMERIC:  // 数值类型
                 // 日期格式
                 if(DateUtil.isCellDateFormatted(cell)) {
                    val =   Date2Str(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");
                 }else {
                    // 四舍五入
                    val = new DecimalFormat("#.####").format(cell.getNumericCellValue())
                 }
                 break;
            default: //其它类型
                 break;
        }
        return val;                 
      }
}
public static String Date2Str(Date date, String format){
    // Date -> LocalDateTime -> String
    DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
    ZoneId zone = ZoneId.systemDefault();
    LocalDateTime localDateTime = LocalDateTime.ofInstant(date.toInstant(),zone);
    return df.format(localDateTime);
}

这里值得注意的是：

// 最后一行行标，比行数小1，行数使用getPhysicalNumberOfRows()获取
sheet.getLastRowNum();
// 获取列数，比最后一列列标大1，等价于getPhysicalNumberOfCells()，空的cell不计算
sheet.getRow(k).getLastCellNum();

2. 读取xls

String static void readXls(String path) {
       try{
            // 读取的时候可以使用流，也可以直接使用文件名
            FileInputStream is =  new FileInputStream(path);
            Workbook hwb = new HSSFWorkbook(is);
            // 循环工作表sheet
            for(int numSheet = 0; numSheet < hwb.getNumberOfSheets(); numSheet++) {
                Sheet sheet = hwb.getSheetAt(numSheet);
                if(sheet == null) {
                    continue;
                }
               // 循环row，如果第一行是字段，则 numRow = 1
              for(int numRow = 0; numRow <= sheet.getLastRowNum(); numRow++) {
                  Row row = sheet.getRow(numRow);
                  if(row == null) {
                    continue;
                  }
                  // 循环cell
                 for(int numCell = 0; numCell < row.getLastCellNum(); numCell++) {
                    Cell cell = row.getCell(numCell);
                    if(cell == null) {
                        continue;
                        }
                    // 打印数据
                    System.out.println("xls表格中读取的数据" + getValue(cell));
                 }
              }
            }            
      }catch(Exception e) {
          e.printStackTrace();
      }
}

里面的通用方法都在读取xlsx文件中已经定义，这里不再写了。

3. 读写csv

Java读取xls和xlsx都是通过poi实现的，csv比较特别，可以通过opencsv或javacsv读取。

3.1 读csv

public static List<String[]> readCSV(String filePath) throws Exception {
        CsvReader reader = null;
        List<String[]> dataList = new ArrayList<String[]>();
        try {
            //如果生产文件乱码，windows下用gbk，linux用UTF-8
            reader = new CsvReader(filePath, separator, Charset.forName("GBK"));

            // 读取表头
            reader.readHeaders();
            String[] headArray = reader.getHeaders();//获取标题
            System.out.println(headArray[0] + headArray[1] + headArray[2]);

            // 逐条读取记录，直至读完
            while (reader.readRecord()) {
                dataList.add(reader.getValues());
                // 读一整行
                System.out.println(reader.getRawRecord());
                // 读这行的第二列
                System.out.println(reader.get(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != reader) {
                reader.close();
            }
        }
        return dataList;
    }

3.2 写csv

public static boolean createCSV(List<String[]> dataList, String filePath) throws Exception {
        boolean isSuccess = false;
        CsvWriter writer = null;
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(filePath, true);
            //如果生产文件乱码，windows下用gbk，linux用UTF-8
            writer = new CsvWriter(out, separator, Charset.forName("GBK"));
            for (String[] strs : dataList) {
                writer.writeRecord(strs);
            }
            isSuccess = true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != writer) {
                writer.close();
            }
            if (null != out) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return isSuccess;
    }

参考文献

• https://www.cnblogs.com/zhxn/p/7016380.html
• https://blog.csdn.net/qq_21137441/article/details/79226171
• https://blog.csdn.net/xf_zhen/article/details/80967064
• https://blog.csdn.net/chenPengFate/article/details/82683960
• https://blog.csdn.net/u010004317/article/details/62841164
• https://blog.csdn.net/thebigdipperbdx/article/details/83010009