public static void Write(ISheet sheet, string id, string columnName, string value, int dataStartRowNum,
int headerRowNum)
{
List <string> headerColumnNames = ExcelUtil.GetSheetHeaderNames(sheet, headerRowNum);
for (int curRowNum = dataStartRowNum; curRowNum <= sheet.LastRowNum; curRowNum++)
{
IRow curRow = sheet.GetRow(curRowNum);
ICell idCell = curRow.GetCell(0);
if (ExcelUtil.GetCellValue(idCell).Equals(id))
{
for (int curCellNum = 0; curCellNum < curRow.LastCellNum; curCellNum++)
{
ICell icell = curRow.GetCell(curCellNum);
if (icell == null)
{
icell = curRow.CreateCell(curCellNum);
}
if (headerColumnNames[curCellNum].Equals(columnName))
{
icell.SetCellValue(value);
return;
}
}
}
}
//否则新建一行输入
IRow eRow = sheet.CreateRow(sheet.LastRowNum + 1);
for (int curCellNum = 0; curCellNum < headerColumnNames.Count; curCellNum++)
{
ICell icell = eRow.CreateCell(curCellNum);
if (headerColumnNames[curCellNum].ToLower() == ExcelConst.Id_Text)
{
icell.SetCellValue(id);
}
else if (headerColumnNames[curCellNum] == columnName)
{
icell.SetCellValue(value);
}
}
}
/// <summary>
/// 检查表是否按要求填写
/// </summary>
/// <param name="sheet"></param>
/// <param name="dataStartRowNum"></param>
/// <returns></returns>
public static bool CheckSheetIsValid(ISheet sheet, string fileName, int dataStartRowNum)
{
var headerRow = ExcelConst.Header_Name_Row_Index;
if (sheet.LastRowNum < dataStartRowNum || sheet.GetRow(headerRow) == null)
{
Debug.LogErrorFormat("表格: [{0}]必须大于等于{1}行", fileName, dataStartRowNum);
return(false);
}
var headRow = sheet.GetRow(headerRow); //表头
var hasIdColumn = false;
for (var i = 0; i < headRow.LastCellNum; i++)
{
var cell = headRow.GetCell(i);
var cellValue = cell == null ? "" : ExcelUtil.GetCellValue(cell);
if (cellValue.ToLower().Equals(ExcelConst.Id_Text))
{
hasIdColumn = true;
break;
}
// if (string.IsNullOrEmpty(cellValue))
// {
// Debug.LogErrorFormat("表头字段名字不能为空，所在列：{0}/{1}", i + 1, fileName);
// return false;
// }
}
if (!hasIdColumn)
{
Debug.LogErrorFormat("表格: [{0}]第{1}行没有包含id字段", fileName, dataStartRowNum);
return(false);
}
return(true);
}
private static void WriteToAsset(ISheet sheet, string fileName, string outputPath)
{
if (!CheckSheetIsValid(sheet, fileName, ExcelConst.Start_Data_Row_Index))
{
return;
}
try
{
var rowEnumerator = sheet.GetRowEnumerator();
var sheetColLen = ExcelUtil.GetSheetColLen(sheet);
var headerNames = ExcelUtil.GetSheetHeaderNames(sheet, ExcelConst.Header_Name_Row_Index);
// if (headerNames.Count > sheetColLen)
// {
// Debug.LogErrorFormat("表格: [{0}] 名字那行的数据长度不对,names.Count:{1}, colLength:{2}", sheet.SheetName,
// headerNames.Count, sheetColLen);
// }
// else
{
var sheetTypes = ExcelUtil.GetSheetHeaderTypes(sheet, ExcelConst.Header_Name_Type_Row_Index);
// if (sheetTypes.Count > sheetColLen)
// {
// Debug.LogErrorFormat("表格: [{0}] 类型那行的数据长度不对", sheet.SheetName);
// }
// else
{
var excelDatabase = AssetDatabase.LoadAssetAtPath <ExcelDatabase>(outputPath);
if (excelDatabase == null)
{
excelDatabase = ScriptableObjectUtil.CreateAsset <ExcelDatabase>(outputPath);
}
var linkedDictionary = new LinkedDictionary <string, ExcelRow>();
excelDatabase.headerList.Clear();
var headers = excelDatabase.headerList;
int curIgnoreCount = 0;
for (var i = 0; i < sheetColLen; i++)
{
// LogCat.LogError(headerNames[i] + " " + sheetTypes[i]);
if (!ExcelUtil.IsColumnValid(sheet, i))
{
curIgnoreCount++;
continue;
}
else
{
headers.Add(new ExcelHeader
{
name = headerNames[i - curIgnoreCount],
type = sheetTypes[i - curIgnoreCount]
});
}
}
List <string> removeList = new List <string>(); //多语言表处理
//预先处理不符合的cell类型
while (rowEnumerator.MoveNext())
{
var row = rowEnumerator.Current as IRow;
if (row.RowNum >= ExcelConst.Start_Data_Row_Index)
{
if (row.LastCellNum == -1)
{
continue;
}
bool isHasLang = false; //多语言表处理
var excelValueList = new ExcelRow();
string id = "";
curIgnoreCount = 0;
for (var j = 0; j < sheetColLen; j++)
{
if (!ExcelUtil.IsColumnValid(sheet, j))
{
curIgnoreCount++;
continue;
}
var errorTips = string.Format("表格: [{0}] 第{1}行，第{2}列数据格式不对!", row.RowNum + 1,
sheet.SheetName, j + 1);
var cell = j < (int)row.LastCellNum ? row.GetCell(j) : null;
var sCellValue = cell == null ? "" : ExcelUtil.GetCellValue(cell);
var excelHeader = headers[j - curIgnoreCount];
var cellType = excelHeader.type;
var intValue = 0;
switch (cellType)
{
case ExcelDataType.INT:
if (!string.IsNullOrEmpty(sCellValue) &&
!int.TryParse(sCellValue, out intValue))
{
Debug.LogErrorFormat("表格: [{0}] 第{1}行，第{2}列数据格式不对!", row.RowNum + 1,
sheet.SheetName, j + 1);
}
break;
case ExcelDataType.FLOAT:
var floatValue = 0f;
if (!string.IsNullOrEmpty(sCellValue) &&
!float.TryParse(sCellValue, out floatValue))
{
Debug.LogWarning(errorTips);
}
break;
case ExcelDataType.VECTOR3:
var sVector3 = sCellValue.Split(',');
if (sVector3 == null || sVector3.Length != 3)
{
Debug.LogWarning(errorTips);
}
foreach (var v in sVector3)
{
var num5 = 0f;
if (!float.TryParse(v, out num5))
{
Debug.LogWarning(errorTips);
}
}
break;
case ExcelDataType.BOOLEAN:
var boolValue = false;
if (!string.IsNullOrEmpty(sCellValue) &&
!bool.TryParse(sCellValue, out boolValue))
{
Debug.LogWarning(errorTips);
}
break;
}
if (excelHeader.name.ToLower().Equals(ExcelConst.Id_Text))
{
id = sCellValue;
}
else
{
//多语言表处理
if (fileName.Contains("D 多语言表-Lang.xlsx"))
{
if (!sCellValue.IsNullOrWhiteSpace())
{
isHasLang = true;
}
}
}
var excelValue = new ExcelValue();
excelValue.value = sCellValue;
excelValueList.valueList.Add(excelValue);
}
//多语言表处理,对没有翻译的key不用写asset中，以减少数据
if (fileName.Contains("D 多语言表-Lang.xlsx"))
{
if (isHasLang == false)
{
removeList.Add(id);
}
}
if (excelValueList.valueList.Count > 0)
{
linkedDictionary[id] = excelValueList;
}
}
}
foreach (var toRemoveId in removeList)
{
linkedDictionary.Remove(toRemoveId);
}
excelDatabase.SetAssetData(linkedDictionary);
EditorUtility.SetDirty(excelDatabase);
AssetDatabase.SaveAssets();
AssetDatabase.Refresh();
Debug.LogFormat("WriteTable: {0} count:{1} to {2}", fileName, linkedDictionary.Count,
outputPath);
}
}
}
catch (Exception e)
{
Debug.LogException(e);
}
}