/// <summary> /// Get the list of Columns for sheet /// </summary> /// <param name="fileName"></param> /// <param name="aReadOnly"></param> /// <returns></returns> public static void SetColumnInfos(string fileName, SheetInfo sheetInfo) { if (sheetInfo.Columns != null && sheetInfo.Columns.Count > 0) { return; } if (!File.Exists(fileName)) { return; } IList <SheetInfo> m_SheetInfos = new List <SheetInfo>(); ExcelReaderWriter m_ExcelReaderWriter = new ExcelReaderWriter(fileName, 1); try { m_ExcelReaderWriter.OpenWorkbook(true); foreach (Excel.Worksheet m_WorkSheet in m_ExcelReaderWriter.ActiveWorkbook.Sheets) { if (m_WorkSheet.Name == sheetInfo.Name) { #region Set Column Infos object[,] m_Values = (object[, ])m_WorkSheet.UsedRange.Value2; if (m_Values != null) { int m_UseRangeRowLength = (int)m_Values.GetLongLength(0); int m_UseRangeColumnLength = (int)m_Values.GetLongLength(1); //Recheck // - BeginHeaderColumnIndex if (sheetInfo.BeginHeaderColumnIndex == 0) { sheetInfo.BeginHeaderColumnIndex = 1; } else if (sheetInfo.BeginHeaderColumnIndex > m_UseRangeColumnLength) { sheetInfo.BeginHeaderColumnIndex = m_UseRangeColumnLength; } // - EndHeaderColumnIndex if (sheetInfo.EndHeaderColumnIndex == 0) { sheetInfo.EndHeaderColumnIndex = m_UseRangeColumnLength; } else if (sheetInfo.EndHeaderColumnIndex < sheetInfo.BeginHeaderColumnIndex) { sheetInfo.EndHeaderColumnIndex = sheetInfo.BeginHeaderColumnIndex; } else if (sheetInfo.EndHeaderColumnIndex > m_UseRangeColumnLength) { sheetInfo.EndHeaderColumnIndex = m_UseRangeColumnLength; } // - BeginRowIndex if (sheetInfo.BeginRowIndex == 0) { sheetInfo.BeginRowIndex = 1; } else if (sheetInfo.BeginRowIndex > m_UseRangeRowLength) { sheetInfo.BeginRowIndex = m_UseRangeRowLength; } // - EndRowIndex if (sheetInfo.EndRowIndex == 0) { sheetInfo.EndRowIndex = m_UseRangeRowLength; } else if (sheetInfo.EndRowIndex > m_UseRangeRowLength) { sheetInfo.EndRowIndex = m_UseRangeRowLength; } else if (sheetInfo.EndRowIndex < sheetInfo.BeginRowIndex) { sheetInfo.EndRowIndex = sheetInfo.BeginRowIndex; } // - HeaderRowIndex if (sheetInfo.HeaderRowIndex == 0) { sheetInfo.HeaderRowIndex = 1; } else if (sheetInfo.HeaderRowIndex > sheetInfo.EndRowIndex) { sheetInfo.HeaderRowIndex = sheetInfo.EndRowIndex; } for (int c = sheetInfo.BeginHeaderColumnIndex; c <= sheetInfo.EndHeaderColumnIndex; c++) { ColumnInfo m_ColumnInfo = new ColumnInfo() { Index = c, Header = ConvertedColumnData.Instance.Data[c], Name = ConvertedColumnData.Instance.Data[c] }; sheetInfo.Columns.Add(m_ColumnInfo); } } #endregion return; } } } catch (Exception ex) { Debug.WriteLine(ex.Message); } finally { m_ExcelReaderWriter.CloseWorkbook(); } }
/// <summary> /// Get the list of Sheets /// </summary> /// <param name="fileName"></param> /// <param name="aReadOnly"></param> /// <returns></returns> public static IList <SheetInfo> GetSheetInfos(string fileName, bool aReadOnly) { if (!File.Exists(fileName)) { return(null); } IList <SheetInfo> m_SheetInfos = new List <SheetInfo>(); ExcelReaderWriter m_ExcelReaderWriter = new ExcelReaderWriter(fileName, 1); try { m_ExcelReaderWriter.OpenWorkbook(true); int i = 1; foreach (Excel.Worksheet m_WorkSheet in m_ExcelReaderWriter.ActiveWorkbook.Sheets) { var m_SheetInfo = new SheetInfo() { Name = m_WorkSheet.Name, Index = i }; //#region Set Column Infos //object[,] m_Values = (object[,])m_ExcelReaderWriter.ActiveWorksheet.UsedRange.Value2; //if (m_Values != null) //{ // int m_UseRangeRowLength = (int)m_Values.GetLongLength(0); // int m_UseRangeColumnLength = (int)m_Values.GetLongLength(1); // //Recheck // // - BeginHeaderColumnIndex // if (m_SheetInfo.BeginHeaderColumnIndex == 0) // m_SheetInfo.BeginHeaderColumnIndex = 1; // else if (m_SheetInfo.BeginHeaderColumnIndex > m_UseRangeColumnLength) // m_SheetInfo.BeginHeaderColumnIndex = m_UseRangeColumnLength; // // - EndHeaderColumnIndex // if (m_SheetInfo.EndHeaderColumnIndex == 0) // m_SheetInfo.EndHeaderColumnIndex = m_UseRangeColumnLength; // else if (m_SheetInfo.EndHeaderColumnIndex < m_SheetInfo.BeginHeaderColumnIndex) // m_SheetInfo.EndHeaderColumnIndex = m_SheetInfo.BeginHeaderColumnIndex; // else if (m_SheetInfo.EndHeaderColumnIndex > m_UseRangeColumnLength) // m_SheetInfo.EndHeaderColumnIndex = m_UseRangeColumnLength; // // - BeginRowIndex // if (m_SheetInfo.BeginRowIndex == 0) // m_SheetInfo.BeginRowIndex = 1; // else if (m_SheetInfo.BeginRowIndex > m_UseRangeRowLength) // m_SheetInfo.BeginRowIndex = m_UseRangeRowLength; // // - EndRowIndex // if (m_SheetInfo.EndRowIndex == 0) // m_SheetInfo.EndRowIndex = m_UseRangeRowLength; // else if (m_SheetInfo.EndRowIndex > m_UseRangeRowLength) // m_SheetInfo.EndRowIndex = m_UseRangeRowLength; // else if (m_SheetInfo.EndRowIndex < m_SheetInfo.BeginRowIndex) // m_SheetInfo.EndRowIndex = m_SheetInfo.BeginRowIndex; // // - HeaderRowIndex // if (m_SheetInfo.HeaderRowIndex == 0) // m_SheetInfo.HeaderRowIndex = 1; // else if (m_SheetInfo.HeaderRowIndex > m_SheetInfo.EndRowIndex) // m_SheetInfo.HeaderRowIndex = m_SheetInfo.EndRowIndex; // for (int c = m_SheetInfo.BeginHeaderColumnIndex; c <= m_SheetInfo.EndHeaderColumnIndex; c++) // { // ColumnInfo m_ColumnInfo = new ColumnInfo() // { // Index = c, // Header = ConvertedColumnData.Instance.Data[c], // Name = ConvertedColumnData.Instance.Data[c] // }; // m_SheetInfo.Columns.Add(m_ColumnInfo); // } //} //#endregion m_SheetInfos.Add(m_SheetInfo); i++; } } catch (Exception ex) { Debug.WriteLine(ex.Message); m_SheetInfos.Clear(); } finally { m_ExcelReaderWriter.CloseWorkbook(); } return(m_SheetInfos); }