public static ColumnInfo[] GetColumnInfoUsed(string sFileName, string sSheetName)
 {
     if (!string.IsNullOrEmpty(sSheetName) && (!string.IsNullOrEmpty(sFileName)))
     {
         using (var excel = new ExcelReaderWriter(sFileName, sSheetName))
         {
             excel.OpenWorkbook(true);
             int nColumn   = excel.ActiveWorksheet.UsedRange.Columns.Count;
             int?nColumnTo = GetLastCoumnIndex(excel.ActiveWorksheet.UsedRange);
             if (!nColumnTo.HasValue)
             {
                 excel.CloseWorkbook();
                 return(null);
             }
             int nColumnFrom        = nColumnTo.Value - nColumn + 1;
             List <ColumnInfo> list = new List <ColumnInfo>();
             for (int i = nColumnFrom; i <= nColumnTo; i++)
             {
                 list.Add(new ColumnInfo()
                 {
                     Index = i,
                     Name  = GetNameFromColumnIndex(i)
                 });
             }
             excel.CloseWorkbook();
             return(list.ToArray());
         }
     }
     return(null);
 }
        /// <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);
        }