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