Unity 读取 Excel 表格 配置 游戏 参数 可打包成exe

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

 

Unity 版本：5.4.1f1，可以正常打包成exe

工程地址：链接：http://pan.baidu.com/s/1mhGhN8s 密码：orsm

这里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;
    }
}