/// <summary> /// 確認excel檔案有正確的table header 且取得相關資訊 /// </summary> /// <returns>可能有的錯誤類型</returns> public ReadExcelToJsonStringError CheckAndReadTableHeader(NeedReadSite nrs, out List<string> allType) { allType = null; ReadExcelToJsonStringError ree = CheckTableStartAndCountTableColumn(); if (ree != ReadExcelToJsonStringError.NONE) { return ree; } ree = GetTableAllColumnType(out allType); if (ree != ReadExcelToJsonStringError.NONE) { return ree; } ree = GetTableIgnoreColumn(nrs); if (ree != ReadExcelToJsonStringError.NONE) { return ree; } DeleteIgnoreCol(ref allType); return ReadExcelToJsonStringError.NONE; }
/// <summary> /// 讀取excel檔案,不論是否有錯誤,回傳前都會關閉檔案 /// </summary> /// <param name="directoryPath">資料夾路徑</param> /// <param name="dataLoadTag">資料轉換的資訊</param> /// <param name="needReadSite">轉出資料是哪方(Server/Client)需要</param> /// <param name="jsonString">對應輸出的json字串</param> /// <param name="debugString">偵錯字串</param> /// <returns>可能有的錯誤訊息</returns> public ReadExcelToJsonStringError ReadExcelFile(string directoryPath, EnumClassValue dataConvertInfo, NeedReadSite needReadSite, out string jsonString, out string debugString) { if (string.IsNullOrEmpty(dataConvertInfo.FileName) || dataConvertInfo.ClassType == null) { ReadExcelFileEnd(null, out jsonString, out debugString); return ReadExcelToJsonStringError.ENUM_ATTRIBUTE_ERROR; } ReadExcelToJsonStringError readExcelError = _excelToTable.OpenExcelFile(directoryPath, dataConvertInfo.FileName); if (readExcelError != ReadExcelToJsonStringError.NONE) { ReadExcelFileEnd(null, out jsonString, out debugString); return readExcelError; } List<string> allType; readExcelError = _excelToTable.CheckAndReadTableHeader(needReadSite, out allType); if (readExcelError != ReadExcelToJsonStringError.NONE) { ReadExcelFileEnd(null, out jsonString, out debugString); return readExcelError; } #region 確認各欄位和要被寫入的物件欄位Type有對應 object checkObject = Activator.CreateInstance(dataConvertInfo.ClassType); List<string>.Enumerator tableTypeEnumerator = allType.GetEnumerator(); bool isConform = CheckObjectTypeCorrect(dataConvertInfo.ClassType, checkObject, ref tableTypeEnumerator); if (!isConform) { _debugMessage = string.Format("{0}{1} 轉換失敗:表格與資料結構({2})內容不符\n", _debugMessage, dataConvertInfo.FileName, dataConvertInfo.ClassType); ReadExcelFileEnd(null, out jsonString, out debugString); return ReadExcelToJsonStringError.TABLE_TYPE_IS_NOT_CONFORM; } #endregion #region 抓取資料 List<object> allData = new List<object>(); bool hasEOR = false; List<string> tableRowData = _excelToTable.GetNextRow(); while (tableRowData != null) // 還有資料 { if (_excelToTable.CheckEndOfTable(tableRowData)) { // 有結尾符號,正常結束 hasEOR = true; break; } if (CheckEmptyRow(tableRowData)) { ReadExcelFileEnd(null, out jsonString, out debugString); return ReadExcelToJsonStringError.HAS_EMPTY_ROW; } object obj = Activator.CreateInstance(dataConvertInfo.ClassType); List<string>.Enumerator rowDataEnumerator = tableRowData.GetEnumerator(); ReadExcelToJsonStringError error = GetObjectTypeDataFromExcel(dataConvertInfo.ClassType, ref obj, ref rowDataEnumerator); if (error != ReadExcelToJsonStringError.NONE) { ReadExcelFileEnd(null, out jsonString, out debugString); return error; } allData.Add(obj); // 取得下一行資料 tableRowData = _excelToTable.GetNextRow(); } if (!hasEOR) { ReadExcelFileEnd(null, out jsonString, out debugString); return ReadExcelToJsonStringError.CANT_FIND_END_OF_ROW_TOKEN; } #endregion ReadExcelFileEnd(allData, out jsonString, out debugString); return ReadExcelToJsonStringError.NONE; }
/// <summary> /// 取得excel中table內所有忽略的column index,結果存在ignoreColumnData /// </summary> /// <returns>可能有的錯誤訊息</returns> ReadExcelToJsonStringError GetTableIgnoreColumn(NeedReadSite nrs) { _dontNeedColumnIndexes.Clear(); List<string> ignoreColumnData = GetNextRow(); if (ignoreColumnData == null || ignoreColumnData.Count == 0) { return ReadExcelToJsonStringError.DONT_INSTRUCT_NEED_ROW; } // 沒指示不需讀入欄位 if (CheckEndOfTable(ignoreColumnData)) { return ReadExcelToJsonStringError.END_OF_ROW_TOKEN_TO_EARLY; } // 太早遇到END_OF_ROW if (ignoreColumnData.Count < _columnCount) { return ReadExcelToJsonStringError.INSTRUCT_IGNORE_COL_NOT_ENOUGH; } //column數量不正確 for (int col = 0; col < _columnCount; ++col) { if (string.IsNullOrEmpty(ignoreColumnData[col])) { return ReadExcelToJsonStringError.INSTRUCT_IGNORE_COL_NOT_ENOUGH; } // column數量不正確 if (!(ignoreColumnData[col].Equals(NEED_READ_SITE_IS_ALL) || (ignoreColumnData[col].Equals(NEED_READ_SITE_IS_SERVER) && nrs == NeedReadSite.SERVER) || (ignoreColumnData[col].Equals(NEED_READ_SITE_IS_CLIENT) && nrs == NeedReadSite.CLIENT))) { _dontNeedColumnIndexes.Add(col); } } return ReadExcelToJsonStringError.NONE; }