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)); }
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); } }
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); }