/*
* Edit by XJH at 2020-05-12
*/
using System.Collections.Generic;
using UnityEngine;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;/// <summary>/// HSSFWorkbook 用于 .xls/// XSSFWorkbook 用于 .xlsx/// </summary>publicclassNPOI2Excel{/// <summary>/// 加载Excel文件/// </summary>/// <param name="excelPath">文件路径</param>/// <returns></returns>publicstatic IWorkbook LoadExcel(string excelPath){
string suffixStr = Path.GetExtension(excelPath);switch(suffixStr){case".xls":returnReadXLS(excelPath);case".xlsx":returnReadXLSX(excelPath);default:break;}
Debug.LogError("File isn't .xls or .xlsx");returnnull;}/// <summary>/// 加载Excel文件/// 获取某行某列的值/// </summary>/// <param name="excelPath">文件路径</param>/// <param name="sheetIndex">单元下标</param>/// <param name="rowNum">行下标</param>/// <param name="cellNum">列下标</param>/// <returns>单元格字符串</returns>publicstatic string LoadExcel(string excelPath,int sheetIndex,int rowNum,int cellNum){returnLoadExcel(excelPath).GetSheetAt(sheetIndex).GetRow(rowNum).GetCell(cellNum).ToString();}/// <summary>/// 加载Excel文件/// 获取某行某列的值/// </summary>/// <param name="excelPath">文件路径</param>/// <param name="sheetName">单元名称</param>/// <param name="rowNum">行下标</param>/// <param name="cellNum">列下标</param>/// <returns>单元格字符串</returns>publicstatic string LoadExcel(string excelPath, string sheetName, int rowNum, int cellNum){returnLoadExcel(excelPath).GetSheet(sheetName).GetRow(rowNum).GetCell(cellNum).ToString();}/// <summary>/// 加载Excel文件/// 所有数据/// </summary>/// <param name="excelPath">文件路径</param>/// <param name="sheetIndex">单元下标</param>/// <returns>数据</returns>publicstatic List<string>[]LoadExcel(string excelPath, int sheetIndex){
ISheet sheet =LoadExcel(excelPath).GetSheetAt(sheetIndex);
List<string>[] fullData =newList<string>[sheet.LastRowNum - sheet.FirstRowNum];for(int i = sheet.FirstRowNum; i < sheet.LastRowNum; i++){
List<string> rowData =newList<string>();for(int k = sheet.GetRow(i).FirstCellNum; k < sheet.GetRow(i).LastCellNum; k++){//直接GetCell(k).StringCellValue会报错://InvalidOperationException: Cannot get a text value from a numeric cell//因为Cell可能为其他类型,//**************************************//方法一:将类型转换为string//if (sheet.GetRow(i).GetCell(k) != null)//{// sheet.GetRow(i).GetCell(k).SetCellType(CellType.String);//}//方法二:直接ToString()
rowData.Add(sheet.GetRow(i).GetCell(k).ToString());}
fullData[i]= rowData;}return fullData;}/// <summary>/// 加载Excel文件/// 所有数据/// </summary>/// <param name="excelPath">文件路径</param>/// <param name="sheetName">单元名称</param>/// <returns>数据</returns>publicstatic List<string>[]LoadExcel(string excelPath, string sheetName){
ISheet sheet =LoadExcel(excelPath).GetSheet(sheetName);
List<string>[] fullData =newList<string>[sheet.LastRowNum - sheet.FirstRowNum];for(int i = sheet.FirstRowNum; i < sheet.LastRowNum; i++){
List<string> rowData =newList<string>();for(int k = sheet.GetRow(i).FirstCellNum; k < sheet.GetRow(i).LastCellNum; k++){//直接GetCell(k).StringCellValue会报错://InvalidOperationException: Cannot get a text value from a numeric cell//因为Cell可能为其他类型,//**************************************//方法一:将类型转换为string//if (sheet.GetRow(i).GetCell(k) != null)//{// sheet.GetRow(i).GetCell(k).SetCellType(CellType.String);//}//方法二:直接ToString()
rowData.Add(sheet.GetRow(i).GetCell(k).ToString());}
fullData[i]= rowData;}return fullData;}/// <summary>/// 读取Excel文件(.xls)/// </summary>publicstatic HSSFWorkbook ReadXLS(string excelPath){//后缀判断if(!Path.GetExtension(@excelPath).Contains(".xls")){
Debug.LogError("This is not a .xls File");returnnull;}
FileStream fs = File.OpenRead(@excelPath);
HSSFWorkbook xlsData =newHSSFWorkbook(fs);
fs.Close();
fs.Dispose();return xlsData;}/// <summary>/// 读取Excel文件(.xlsx)/// </summary>publicstatic XSSFWorkbook ReadXLSX(string excelPath){//后缀判断if(!Path.GetExtension(@excelPath).Contains(".xlsx")){
Debug.LogError("This is not a .xlsx File");returnnull;}
FileStream fs = File.OpenRead(@excelPath);
XSSFWorkbook xlsxData =newXSSFWorkbook(fs);
fs.Close();
fs.Dispose();return xlsxData;}}
二、测试代码
挂在Unity场景中,Inspector上赋值excelPath,运行即可
/*
* Edit by XJH at 2020-05-12
*/
using System.Collections.Generic;
using UnityEngine;/// <summary>/// 读取Excel文件测试代码/// </summary>publicclassTestExcel: MonoBehaviour {[Header("Excel文件路径(带后缀)")]public string excelPath;// Use this for initializationvoidStart(){
List<string>[] excelData = NPOI2Excel.LoadExcel(excelPath,0);for(int i =0; i < excelData.Length; i++){for(int k =0; k < excelData[i].Count; k++){
Debug.Log(string.Format("第{0}行 第{1}列>{2}", i +1, k +1, excelData[i][k]));}}}}