1.引入依赖包
<!--xls文件操作--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><!--xlsx文件操作--><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><!--word、ppt、viso、outlook文件操作--><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>3.17</version></dependency><!--csv文件操作--><dependency><groupId>net.sourceforge.javacsv</groupId><artifactId>javacsv</artifactId><version>2.0</version></dependency>
2. 完整代码
package com.aliyun.db.web;import com.csvreader.CsvReader;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.*;import java.io.*;import java.nio.charset.Charset;import java.util.ArrayList;import java.util.List;public class ExcelUtil {public static List<List<String>> readExcel(String excelFilePath) {List<List<String>> result = new ArrayList<>();if (excelFilePath.endsWith(".xlsx") || excelFilePath.endsWith(".xls")) {result = handleData(excelFilePath);}return result;}// 读取后缀是xlsx的excel表格public static List<List<String>> handleData(String excelFilePath) {List<List<String>> result = new ArrayList<>();try (InputStream inputStream = new FileInputStream(excelFilePath);Workbook xssfWorkbook = WorkbookFactory.create(inputStream)) {//循环每一页，并处理当前的循环页for (Sheet sheet : xssfWorkbook) {if (sheet == null) {continue;}for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {//Row表示每一行的数据Row row = sheet.getRow(rowNum);if (null != row) {int minColIx = row.getFirstCellNum();int maxColIx = row.getLastCellNum();List<String> rowList = new ArrayList<>();//遍历该行，并获取每一个cell的数据for (int colIx = minColIx; colIx < maxColIx; colIx++) {Cell cell = row.getCell(colIx);if (cell == null) {continue;}rowList.add(cell.toString());}result.add(rowList);}}}} catch (IOException | InvalidFormatException e) {System.out.println(e);}return result;}// BufferedReader 读取csv文件public static ArrayList<String> readCsvByBufferedReader(String filePath) {File csv = new File(filePath);csv.setReadable(true);csv.setWritable(true);InputStreamReader isr = null;BufferedReader br = null;try {isr = new InputStreamReader(new FileInputStream(csv), "UTF-8");br = new BufferedReader(isr);} catch (Exception e) {e.printStackTrace();}String line = "";ArrayList<String> records = new ArrayList<>();try {while ((line = br.readLine()) != null) {records.add(line);}System.out.println("csv表格读取行数：" + records.size());} catch (IOException e) {e.printStackTrace();}return records;}// CsvReader方式读取csv文件public static ArrayList<String> readCsvByCsvReader(String filePath) {ArrayList<String> strList = null;try {ArrayList<String[]> arrList = new ArrayList<String[]>();strList = new ArrayList<String>();CsvReader reader = new CsvReader(filePath, ',', Charset.forName("UTF-8"));while (reader.readRecord()) {System.out.println(Arrays.asList(reader.getValues()));arrList.add(reader.getValues()); // 按行读取，并把每一行的数据添加到list集合}reader.close();// System.out.println("读取的行数：" + arrList.size());for (int row = 0; row < arrList.size(); row++) {// 组装String字符串String ele = arrList.get(row)[0] + "," + arrList.get(row)[1] + "," + arrList.get(row)[2];strList.add(ele);}} catch (Exception e) {e.printStackTrace();}return strList;}public static void main(String[] args) {String path1 = "src/main/java/com/aliyun/db/web/InstanceInfo1.xls";List<List<String>> list1 = readExcel(path1);System.out.println(list1);String path2 = "src/main/java/com/aliyun/db/web/InstanceInfo2.xlsx";List<List<String>> list2 = readExcel(path2);System.out.println(list2);String path3 = "src/main/java/com/aliyun/db/web/InstanceInfo3.csv";List<String> list3 = readCsvByBufferedReader(path3);System.out.println(list3);List<String> list4 = readCsvByCsvReader(path3);System.out.println(list4);}}
