讀取簡單excel通用工具類

讀取excel通用工具類

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 讀取Excel資料
 * @author zql
 * @createTime 2020-11-30 22:54:21
 * @version 1.1
 * @modifyLog 1.1 優化代碼
 *
 */
public class ReadExcel {

	/**
	 * 讀取Excel
	 * @author zql
	 * @createTime 2020-11-30 22:54:34
	 *
	 * @param filepath 檔案路徑
	 * @param filename 檔案名，包括擴充名
	 * @param startrow 開始行号，索引從0開始
	 * @param startcol 開始列号，索引從0開始
	 * @param sheetnum 工作簿，索引從0開始
	 * @return
	 */
	public List<Map<String, String>> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
		List<Map<String, String>> varList = new ArrayList<Map<String, String>>();
		if (filename.endsWith(".xls")) {
			varList = this.readExcel2003(filepath, filename, startrow, startcol, sheetnum);
		} else if (filename.endsWith(".xlsx")) {
			varList = this.readExcel2007(filepath, filename, startrow, startcol, sheetnum);
		} else {
			System.out.println("Only excel files with XLS or XLSX suffixes are allowed to be read!");
			return null;
		}
		return varList;
	}
	
	/**
	 * 讀取2003Excel
	 * @author zql
	 * @createTime 2020-11-30 22:57:35
	 *
	 * @param filepath 檔案路徑
	 * @param filename 檔案名，包括擴充名
	 * @param startrow 開始行号，索引從0開始
	 * @param startcol 開始列号，索引從0開始
	 * @param sheetnum 工作簿，索引從0開始
	 * @return
	 */
	public List<Map<String,String>> readExcel2003(String filepath, String filename, int startrow, int startcol, int sheetnum) {
		List<Map<String, String>> varList = new ArrayList<Map<String, String>>();
		try {
			File target = new File(filepath, filename);
			FileInputStream fis = new FileInputStream(target);
			HSSFWorkbook wb = new HSSFWorkbook(fis);
			fis.close();
			// sheet 從0開始
			HSSFSheet sheet = wb.getSheetAt(sheetnum);
			// 取得最後一行的行号
			int rowNum = sheet.getLastRowNum() + 1;

			HSSFRow rowTitle = sheet.getRow(0);
			// 标題行的最後一個單元格位置
			int cellTitleNum = rowTitle.getLastCellNum();
			String[] title = new String[cellTitleNum];
			for (int i = startcol; i < cellTitleNum; i++) {
				HSSFCell cell = rowTitle.getCell(Short.parseShort(String.valueOf(i)));
				if (Objects.nonNull(cell)) {
					cell.setCellType(CellType.STRING);
					title[i] = cell.getStringCellValue();
				} else {
					title[i] = "";
				}
			}

			// 行循環開始
			for (int i = startrow + 1; i < rowNum; i++) {
				Map<String, String> varpd = new HashMap<String, String>();
				// 行
				HSSFRow row = sheet.getRow(i);
				// 列循環開始
				for (int j = startcol; j < cellTitleNum; j++) {
					HSSFCell cell = row.getCell(Short.parseShort(String.valueOf(j)));
					String cellValue = "";
					if (Objects.nonNull(cell)) {
						// 把類型先設定為字元串類型
						cell.setCellType(CellType.STRING);
						cellValue = cell.getStringCellValue();
					}
					varpd.put(title[j], cellValue);
				}
				varList.add(varpd);
			}
			wb.close();
		} catch (Exception e) {
			System.out.println(e);
		}
		return varList;
	}
	
	/**
	 * 讀取2007Excel
	 * @author zql
	 * @createTime 2020-11-30 23:23:20
	 *
	 * @param filepath 檔案路徑
	 * @param filename 檔案名，包括擴充名
	 * @param startrow 開始行号，索引從0開始
	 * @param startcol 開始列号，索引從0開始
	 * @param sheetnum 工作簿，索引從0開始
	 * @return
	 */
	public List<Map<String,String>> readExcel2007(String filepath, String filename, int startrow, int startcol, int sheetnum) {
		List<Map<String, String>> varList = new ArrayList<Map<String, String>>();
		try {
			File target = new File(filepath, filename);
			InputStream ins = new FileInputStream(target);
			XSSFWorkbook wb = new XSSFWorkbook(ins);
			ins.close();
			// 得到Excel工作表對象
			XSSFSheet sheet = wb.getSheetAt(sheetnum);
			// 取得最後一行的行号
			int rowNum = sheet.getLastRowNum() + 1;

			XSSFRow rowTitle = sheet.getRow(0);
			int cellTitleNum = rowTitle.getLastCellNum();
			String[] title = new String[cellTitleNum];
			for (int i = startcol; i < cellTitleNum; i++) {
				XSSFCell cell = rowTitle.getCell(Short.parseShort(String.valueOf(i)));
				if (Objects.nonNull(cell)) {
					// 把類型先設定為字元串類型
					cell.setCellType(CellType.STRING);
					title[i] = cell.getStringCellValue();
				} else {
					title[i] = "";
				}
			}

			// 行循環開始
			for (int i = startrow + 1; i < rowNum; i++) {
				Map<String, String> varpd = new HashMap<String, String>();
				// 得到Excel工作表的行
				XSSFRow row = sheet.getRow(i);
				// 列循環開始
				for (int j = startcol; j < cellTitleNum; j++) {
					// 得到Excel工作表指定行的單元格
					XSSFCell cell = row.getCell(j);
					String cellValue = "";
					if (Objects.nonNull(cell)) {
						// 把類型先設定為字元串類型
						cell.setCellType(CellType.STRING);
						cellValue = cell.getStringCellValue();
					}
					varpd.put(title[j], cellValue);
				}
				varList.add(varpd);
			}
			wb.close();
		} catch (Exception e) {
			System.out.println(e);
		}
		return varList;
	}
	
}



           

讀取excel通用工具示例測試類

import org.junit.Test;

import java.util.List;
import java.util.Map;
import java.util.Objects;


/**
 * 讀取Excel資料測試類
 *
 * @author zql
 * @version 1.0
 * @createTime 2021-01-03 0:04:00
 * @modifyLog
 */
public class ReadExcelTest {

    private ReadExcel r = new ReadExcel();

    @Test
    public void readExcel2003() {
        List<Map<String, String>> list = r.readExcel("E:\\excel", "測試表格.xls", 0, 0, 0);
        if (Objects.nonNull(list)) {
            for (int i = 0; i < list.size(); i++) {
                Map<String, String> m = list.get(i);
                m.forEach((key, value) -> {
                    System.out.println(key + ":" + value);
                });
                System.out.println();
            }
        }
    }

    @Test
    public void readExcel2007() {
        List<Map<String, String>> lists = r.readExcel("E:\\excel", "測試表格.xlsx", 0, 0, 0);
        if (Objects.nonNull(lists)) {
            for (int i = 0; i < lists.size(); i++) {
                Map<String, String> m = lists.get(i);
                m.forEach((key, value) -> {
                    System.out.println(key + ":" + value);
                });
                System.out.println();
            }
        }
    }
}
           

普通項目需要引入的包

poi-4.0.1.jar

poi-ooxml-4.0.1.jar

poi-ooxml-schemas-4.0.1.jar

commons-codec-1.11.jar

commons-collections4-4.3.jar

commons-math3-3.6.1.jar

xmlbeans-3.0.2.jar

commons-compress-1.18.jar

curvesapi-1.06.jar

maven項目依賴

<!-- poi -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.0.1</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.0.1</version>
</dependency>