Unity 读取 Excel 表格 配置 游戏 参数

在做游戏时，经常会遇到需要读取Excel表格，用来配置游戏数据，供策化调试程序用

这里Down了三种方法，都采用第三方库，不需要安装Office，关键free
第一种：NPOI

读取代码：

/// <summary>    /// 将excel中的数据导入到DataTable中/// </summary>/// <param name="fileName">excel文件的名称</param>/// <param name="sheetName">excel工作薄sheet的名称</param>/// <param name="isFirstRowColumn">第一行是否是属性</param>/// <returns>返回的DataTable</returns>public DataTable ReadExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn){    if (string.IsNullOrEmpty(fileName) || string.IsNullOrEmpty(sheetName)) return null;    DataTable dataTable = null;    DataColumn column = null;    DataRow dataRow = null;    IWorkbook workBook = null;    IRow row = null;    ICell cell = null;    int startRow = 0;    try    {        using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))        {            if (fileName.ToLower().EndsWith(".xlsx")) //2007版本以上                workBook = new XSSFWorkbook(fs);            else if (fileName.ToLower().EndsWith(".xls")) //2003版本                workBook = new HSSFWorkbook(fs);             if (workBook != null)            {                ISheet sheet = workBook.GetSheet(sheetName); //读取第一个sheet，当然也可以循环读取每个sheet                  dataTable = new DataTable();                if (sheet != null)                {                    int rowCount = sheet.LastRowNum; //总行数                      if (rowCount > 0)                    {                        IRow firstRow = sheet.GetRow(0); //第一行                          int cellCount = firstRow.LastCellNum; //列数                           //构建datatable的列                          if (isFirstRowColumn)                        {                            startRow = 1; //如果第一行是列名，则从第二行开始读取                              for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                            {                                cell = firstRow.GetCell(i);                                if (cell != null)                                {                                    if (cell.StringCellValue != null)                                    {                                        column = new DataColumn(cell.StringCellValue);                                        dataTable.Columns.Add(column);                                    }                                }                            }                        }                        else                        {                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                            {                                column = new DataColumn("column" + (i + 1));                                dataTable.Columns.Add(column);                            }                        }                         //填充行                          for (int i = startRow; i <= rowCount; ++i)                        {                            row = sheet.GetRow(i);                            if (row == null) continue;                             dataRow = dataTable.NewRow();                            for (int j = row.FirstCellNum; j < cellCount; ++j)                            {                                cell = row.GetCell(j);                                if (cell == null)                                {                                    dataRow[j] = "";                                }                                else                                {                                    //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)                                      switch (cell.CellType)                                    {                                        case CellType.Blank:                                            dataRow[j] = "";                                            break;                                        case CellType.Numeric:                                            short format = cell.CellStyle.DataFormat;                                            //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理                                              if (format == 14 || format == 31 || format == 57 || format == 58)                                                dataRow[j] = cell.DateCellValue;                                            else                                                dataRow[j] = cell.NumericCellValue;                                            break;                                        case CellType.String:                                            dataRow[j] = cell.StringCellValue;                                            break;                                    }                                }                            }                            dataTable.Rows.Add(dataRow);                        }                    }                }            }            fs.Close();            fs.Dispose();        }        return dataTable;    }    catch (Exception ex)    {        LogHelper.WriteLog(ex);        return null;    }}

第二种：Excel.dll 

读取代码：

/// <summary>    /// 将excel中的数据导入到DataTable中/// </summary>/// <param name="fileName">excel文件的名称</param>/// <param name="sheetName">excel工作薄sheet的名称</param>/// <returns>返回的DataTable</returns>public DataTable ReadExcelToDataTable(string fileName, string sheetName){    if (string.IsNullOrEmpty(fileName) || string.IsNullOrEmpty(sheetName)) return null;    IExcelDataReader excelReader = null;     try    {        using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))        {            if (fileName.ToLower().EndsWith(".xlsx"))                excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);            else if (fileName.ToLower().EndsWith(".xls"))                excelReader = ExcelReaderFactory.CreateBinaryReader(fs);             if (excelReader == null) return null;            DataSet result = excelReader.AsDataSet();             fs.Close();            fs.Dispose();            excelReader.Close();             return result.Tables[sheetName];        }    }    catch (Exception ex)    {        LogHelper.WriteLog(ex);        return null;    }}

第三种：EPPlus 推荐此方法，具体请自己用Excel测试

调用代码：

/// <summary>    /// 将excel中的数据导入到DataTable中/// </summary>/// <param name="fileName">excel文件的名称</param>/// <param name="sheetName">excel工作薄sheet的名称</param>/// <param name="isFirstRowColumn">第一行是否是属性</param>/// <returns>返回的DataTable</returns>public DataTable ReadExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn){    if (string.IsNullOrEmpty(fileName) || string.IsNullOrEmpty(sheetName)) return null;    try    {        using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))        {            var package = new ExcelPackage(fs);            DataTable data = new DataTable();             ExcelWorkbook workBook = package.Workbook;            if (workBook != null)            {                if (workBook.Worksheets.Count > 0)                 {                    ExcelWorksheet currentWorksheet = workBook.Worksheets[sheetName];                     int lastRow = currentWorksheet.Dimension.End.Row;                    int lastColumn = currentWorksheet.Dimension.End.Column;                     int columnCount = 1;                    while (columnCount <= lastColumn)                    {                        data.Columns.Add(Convert.ToString(currentWorksheet.Cells[1, columnCount].Value));                        columnCount++;                    }                     int rowCount = 0;                    if (isFirstRowColumn) rowCount = currentWorksheet.Dimension.Start.Row + 1;                    else rowCount = currentWorksheet.Dimension.Start.Row;                     while (rowCount <= lastRow)                    {                        columnCount = 1;                        DataRow newRow = data.NewRow();                        while (columnCount <= lastColumn)                        {                            newRow[data.Columns[columnCount - 1]] =                                Convert.ToString(currentWorksheet.Cells[rowCount, columnCount].Value);                            columnCount++;                        }                        rowCount++;                        data.Rows.Add(newRow);                    }                }                fs.Close();                fs.Dispose();            }            return data;        }    }    catch (Exception ex)    {        LogHelper.WriteLog(ex);        return null;    }}