Beispiel #1
0
        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);
                }
            }
        }
Beispiel #2
0
        /// <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);
        }
Beispiel #3
0
        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);
            }
        }