public override void OnSetConnectionInfo(out List <TableDataPath> pathList, out TableDataPath mainDataPath, out bool needTxt)
{
pathList = new List <TableDataPath>();
foreach (var data in script.tablePathList)
{
string fileFullName = Application.dataPath + "/" + data.dataPath;
fileFullName = fileFullName.Replace("\\", "/");
TableDataPath newDataPath = new TableDataPath();
newDataPath.dataPath = fileFullName;
newDataPath.tableName = data.tableName;
pathList.Add(newDataPath);
}
string mainFileFullName = script.dataPath;
mainFileFullName = mainFileFullName.Replace("\\", "/");
mainDataPath = new TableDataPath();
mainDataPath.dataPath = mainFileFullName;
mainDataPath.tableName = script.tableName;
needTxt = script.generateTxt;
}
abstract public void OnSetConnectionInfo(out List <TableDataPath> pathList, out TableDataPath mainDataPath, out bool needTxt);
public static bool CombineExcel(TableDataPath resultDataPath, List <TableDataPath> dataPathList)
{
if (string.IsNullOrEmpty(resultDataPath.dataPath) || string.IsNullOrEmpty(resultDataPath.tableName))
{
Debug.LogError("目标表格路径或名字为空");
return(false);
}
if (dataPathList.Count <= 0)
{
return(true);
}
// 创建合并表
var fi = new FileInfo(resultDataPath.dataPath);
if (fi.Exists)
{
File.SetAttributes(resultDataPath.dataPath, FileAttributes.Normal);
//fi.Delete();
//fi = new FileInfo(resultDataPath.dataPath);
}
using (var package = new ExcelPackage(fi))
{
if (ContainSheet(package.Workbook, resultDataPath.tableName))
{
package.Workbook.Worksheets.Delete(resultDataPath.tableName);
}
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(resultDataPath.tableName);
Dictionary <string, List <object> > tableDict = new Dictionary <string, List <object> >();
Dictionary <string, int> tableColumnDict = new Dictionary <string, int>();
HashSet <string> pathSet = new HashSet <string>();
pathSet.Add(resultDataPath.dataPath + "." + resultDataPath.tableName);
// 从所有表中提取数据
for (int i = 0; i < dataPathList.Count; i++)
{
var data = dataPathList[i];
var cPath = data.dataPath + "." + data.tableName;
if (pathSet.Contains(cPath))
{
Debug.LogError("路径不能重复");
return(false);
}
pathSet.Add(cPath);
bool hasDefaultValue = true;
var fileInfo = new FileInfo(data.dataPath);
using (var pkg = new ExcelPackage(fileInfo))
{
if (!ContainSheet(pkg.Workbook, data.tableName))
{
Debug.LogError(data.dataPath + " 没有找到子表 " + data.tableName);
return(false);
}
ExcelWorksheet ws = pkg.Workbook.Worksheets[data.tableName];
int colNum = GetColumnsCount(ws);
int rowNum = GetRowsCount(ws);
if (colNum == 0 || rowNum < 2 || (i == 0 && rowNum == 2))
{
Debug.LogError("表格行数或列数错误, path:" + data.dataPath);
return(false);
}
if (i == 0 && ws.Cells[3, 1].Value.ToString() != "0")
{
hasDefaultValue = false;
}
// 从第一张表复制前两行
if (i == 0)
{
ws.Cells[1, 1, 2, colNum].Copy(worksheet.Cells[1, 1, 2, colNum]);
}
// 建立当前表第一行的索引
Dictionary <string, int> colDict = new Dictionary <string, int>();
for (int col = 1; col <= colNum; col++)
{
string key = ws.Cells[1, col].Value.ToString().Trim();
if (colDict.ContainsKey(key))
{
Debug.LogError("不允许重复的列 " + key + " path:" + data.dataPath);
return(false);
}
colDict.Add(key, col);
// 从第一张表第一行用于建立主索引
if (i == 0)
{
tableColumnDict.Add(key, col);
}
}
if (rowNum > 2)
{
// 迭代主索引，从当前表中获取数据
foreach (var v in tableColumnDict)
{
if (!tableDict.ContainsKey(v.Key))
{
tableDict.Add(v.Key, new List <object>());
}
int col;
bool hasKey = colDict.TryGetValue(v.Key, out col);
if (!hasKey && !hasDefaultValue)
{
Debug.LogError("该表缺少字段 " + v.Key + " 并且没有默认值, path:" + data.dataPath);
//return false;
}
for (int row = 3; row <= rowNum; row++)
{
// 使用默认值
var val = hasKey ? ws.Cells[row, col].Value : tableDict[v.Key][0];
tableDict[v.Key].Add(val);
if (!hasKey)
{
Debug.LogWarning("该表缺少字段 " + v.Key + " path:" + data.dataPath);
}
}
}
}
}
}
// 把数据复制到目标表中
int colCount = 0;
int rowCount = 0;
foreach (var v in tableDict)
{
int col = tableColumnDict[v.Key];
int totalRow = v.Value.Count + 2;
for (int row = 0; row < v.Value.Count; row++)
{
worksheet.Cells[row + 3, col].Value = v.Value[row];
}
colCount++;
if (rowCount != 0 && rowCount != totalRow)
{
Debug.LogError("合并表格错误，行数不同");
}
rowCount = totalRow;
}
// set style
var range = worksheet.Cells[1, 1, rowCount, colCount];
range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
package.Workbook.Worksheets.MoveToStart(resultDataPath.tableName);
worksheet.View.TabSelected = true;
worksheet.Calculate();
worksheet.Cells.AutoFitColumns(5, 100);
package.Save();
}
Debug.Log("Generate excel to : " + resultDataPath.dataPath);
return(true);
}
public bool LoadXlsx()
{
List <TableDataPath> tablePathList;
TableDataPath mainDataPath;
bool needTxt;
OnSetConnectionInfo(out tablePathList, out mainDataPath, out needTxt);
// 合并表格
if (!CombineExcel(mainDataPath, tablePathList))
{
Debug.LogError("合成表格错误");
return(false);
}
// 特殊情况：合并 modifier_node 表格
if (Path.GetFileNameWithoutExtension(mainDataPath.dataPath) == "aura")
{
string pathPrefix = Application.dataPath + "/";
TableDataPath data = new TableDataPath();
data.dataPath = pathPrefix + "../../server/bin/data/modifier_node.xlsx";
data.dataPath = data.dataPath.Replace("\\", "/");
data.tableName = "Sheet1";
TableDataPath data1 = new TableDataPath();
data1.dataPath = pathPrefix + "../../server/bin/data/modifier_node_old.xlsx";
data1.dataPath = data1.dataPath.Replace("\\", "/");
data1.tableName = "Sheet1";
TableDataPath data2 = new TableDataPath();
data2.dataPath = pathPrefix + "../../server/bin/data/modifier_node_new.xlsx";
data2.dataPath = data2.dataPath.Replace("\\", "/");
data2.tableName = "Sheet1";
TableDataPath data3 = new TableDataPath();
data3.dataPath = pathPrefix + "../../server/bin/data/modifier_node_x.xlsx";
data3.dataPath = data3.dataPath.Replace("\\", "/");
data3.tableName = "Sheet1";
TableDataPath data4 = new TableDataPath();
data4.dataPath = pathPrefix + "../../server/bin/data/modifier_node_wxw.xlsx";
data4.dataPath = data4.dataPath.Replace("\\", "/");
data4.tableName = "Sheet1";
TableDataPath data5 = new TableDataPath();
data5.dataPath = pathPrefix + "../../server/bin/data/modifier_node_gqc.xlsx";
data5.dataPath = data5.dataPath.Replace("\\", "/");
data5.tableName = "Sheet1";
TableDataPath data6 = new TableDataPath();
data6.dataPath = pathPrefix + "../../server/bin/data/modifier_node_zikai.xlsx";
data6.dataPath = data6.dataPath.Replace("\\", "/");
data6.tableName = "Sheet1";
TableDataPath data7 = new TableDataPath();
data7.dataPath = pathPrefix + "../../server/bin/data/modifier_node_thy.xlsx";
data7.dataPath = data7.dataPath.Replace("\\", "/");
data7.tableName = "Sheet1";
List <TableDataPath> pathList = new List <TableDataPath>()
{
data1, data2, data3, data4, data5, data6, data7
};
if (!(CombineExcel(data, pathList)))
{
Debug.LogError("合成 modifier_node 表错误");
return(false);
}
// 合并的表导出txt数据
if (needTxt && !SaveAs_Txt(data.dataPath, data.tableName))
{
Debug.LogError("modifier_node 导出txt错误");
return(false);
}
}
// 读取合并的表
FileStream stream = File.Open(mainDataPath.dataPath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
//DataTable dataTable = result.Tables[0];
DataTable dataTable = result.Tables[mainDataPath.tableName];
if (dataTable == null)
{
Debug.LogError("找不到子表, tableName: " + mainDataPath.tableName);
return(false);
}
if (!OnFetchData(dataTable))
{
Debug.LogError("读取表格有错");
return(false);
}
// 合并的表导出txt数据
if (needTxt && !SaveAs_Txt(mainDataPath.dataPath, mainDataPath.tableName))
{
Debug.LogError("表格导出txt错误");
return(false);
}
Debug.Log("done");
return(true);
}
