Exemple #1
0
        public static bool RangeIsValid(WorkSheetRangeCoordinates wsrc)
        {
            //Excel limits = 1,048,576 rows by 16,384 columns
            if (wsrc == null)
            {
                return(false);
            }

            return((wsrc.TopLeft.X <= 16384) && (wsrc.BottomRight.X <= 16384) && (wsrc.TopLeft.Y <= 1048576) && (wsrc.BottomRight.Y <= 1048576));

            //long l = Math.Abs(Convert.ToInt64(wsrc.TopLeft.X - wsrc.BottomRight.X)) * Math.Abs(Convert.ToInt64(wsrc.TopLeft.Y - wsrc.BottomRight.Y));
        }
Exemple #2
0
        public static WorkSheetRangeCoordinates ParseExcelRange(string range)
        {
            const string letters = "_ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            //BMZ4:BNC14
            string regexPattern = @"([A-Z]{1,3})(\d{1,7}):([A-Z]{1,3})(\d{1,7})";
            WorkSheetRangeCoordinates rangeCoords = new WorkSheetRangeCoordinates();
            var matches  = Regex.Matches(range, regexPattern);
            int colIndex = 0;

            if (matches.Count > 0)
            {
                int    letterIndex;
                string part1  = matches[0].Groups[1].Value;
                char[] part1X = part1.ToCharArray();
                Array.Reverse(part1X);                 //make first car the LSC
                for (int i = 0; i < part1X.Length; i++)
                {
                    letterIndex = letters.IndexOf(part1X[i]);
                    colIndex   += letterIndex * Convert.ToInt32(Math.Pow(26, i));
                }
                rangeCoords.TopLeft.X = colIndex;
                rangeCoords.TopLeft.Y = int.Parse(matches[0].Groups[2].Value);

                colIndex = 0;

                string part2  = matches[0].Groups[3].Value;
                char[] part2X = part2.ToCharArray();
                Array.Reverse(part2X);                 //make first car the LSC
                for (int i = 0; i < part2X.Length; i++)
                {
                    letterIndex = letters.IndexOf(part2X[i]);
                    colIndex   += letterIndex * Convert.ToInt32(Math.Pow(26, i));
                }
                rangeCoords.BottomRight.X = colIndex;
                rangeCoords.BottomRight.Y = int.Parse(matches[0].Groups[4].Value);

                if (!RangeIsValid(rangeCoords))
                {
                    return(null);
                }


                return(rangeCoords);
            }
            else
            {
                return(null);
            }
        }
Exemple #3
0
        public static List <List <string> > ReadExcelRows(string excelFileName, out ResultCode resultCode,
                                                          out string resultDesc, int worksheet = 1, WorkSheetRangeCoordinates wsrc = null)
        {
            List <List <string> > tablerows = new List <List <string> >();
            Application           xlApp     = new Application();
            Workbook  xlWorkBook;
            Worksheet xlWorkSheet = null;

            try
            {
                xlWorkBook = xlApp.Workbooks.Open(excelFileName);
            }
            catch (Exception ex)
            {
                xlApp.Quit();
                ReleaseObject(xlApp);
                resultCode = ResultCode.ErrorOpeningFile;
                resultDesc = ex.Message;
                return(tablerows);
            }

            try
            {
                xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.Item[worksheet];
                Range range;
                if (wsrc == null)
                {
                    range = xlWorkSheet.UsedRange;                     //only look at area where there is data
                }
                else
                {
                    //Cell index is [Y,X]! wtf
                    Range c1 = xlWorkSheet.Cells[wsrc.TopLeft.Y, wsrc.TopLeft.X];
                    Range c2 = xlWorkSheet.Cells[wsrc.BottomRight.Y, wsrc.BottomRight.X];
                    //oRange = (Excel.Range)oSheet.get_Range(c1, c2);
                    range = xlWorkSheet.Range[c1, c2];
                }

                int rowIndex;

                for (rowIndex = 1; rowIndex <= range.Rows.Count; rowIndex++)
                {
                    List <string> cells = new List <string>();
                    for (int colIndex = 1; colIndex <= range.Columns.Count; colIndex++)
                    {
                        //cells.Add(String.Format("{0}", (range.Cells[rCnt, c] as Range).Value2));
                        cells.Add($"{GetRangeStr(range.Cells[rowIndex, colIndex] as Range)}");
                    }

                    tablerows.Add(cells);
                }
            }
            catch (Exception ex1)
            {
                resultCode = ResultCode.ErrorOpeningFile;
                resultDesc = ex1.Message;
                return(tablerows);
            }
            finally
            {
                xlWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
                xlApp.Quit();
                ReleaseObject(xlWorkSheet);
                ReleaseObject(xlWorkBook);
                ReleaseObject(xlApp);
            }
            resultCode = ResultCode.Success;
            resultDesc = "Success";
            return(tablerows);
        }