public Excel.Range RealUsedRange(Excel.Worksheet xlWorksheet)
{
long FirstRow;
long LastRow;
int FirstColumn;
int LastColumn;
try{
FirstRow = xlWorksheet.Cells.Find("*",xlWorksheet.Range("IV65536"),Excel.Enums.XlFindLookIn.xlValues,
Excel.Enums.XlLookAt.xlPart,Excel.Enums.XlSearchOrder.xlByRows,Excel.Enums.XlSearchDirection.xlNext).Row;
FirstColumn = xlWorksheet.Cells.Find("*", xlWorksheet.Range("IV65536"), Excel.Enums.XlFindLookIn.xlValues,
Excel.Enums.XlLookAt.xlPart,Excel.Enums.XlSearchOrder.xlByColumns,Excel.Enums.XlSearchDirection.xlNext).Column;
LastRow = xlWorksheet.Cells.Find("*", xlWorksheet.Range("A1"), Excel.Enums.XlFindLookIn.xlValues,
Excel.Enums.XlLookAt.xlPart,Excel.Enums.XlSearchOrder.xlByRows,Excel.Enums.XlSearchDirection.xlPrevious).Row;
LastColumn = xlWorksheet.Cells.Find("*", xlWorksheet.Range("A1"), Excel.Enums.XlFindLookIn.xlValues,
Excel.Enums.XlLookAt.xlPart,Excel.Enums.XlSearchOrder.xlByColumns,Excel.Enums.XlSearchDirection.xlPrevious).Column;
return xlWorksheet.Range(xlWorksheet.Cells[FirstRow, FirstColumn], xlWorksheet.Cells[LastRow, LastColumn]);
}catch(Exception e){
throw new Exception("Sheet is empty", e);
}
}
private static Excel.Range PutSampleData(Excel.Worksheet workSheet)
{
workSheet.Cells[2, 2].Value = "Datum";
workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[2, 3].Value = "Columns1";
workSheet.Cells[3, 3].Value = 25;
workSheet.Cells[4, 3].Value = 33;
workSheet.Cells[5, 3].Value = 30;
workSheet.Cells[6, 3].Value = 22;
workSheet.Cells[2, 4].Value = "Column2";
workSheet.Cells[3, 4].Value = 25;
workSheet.Cells[4, 4].Value = 33;
workSheet.Cells[5, 4].Value = 30;
workSheet.Cells[6, 4].Value = 22;
workSheet.Cells[2, 5].Value = "Column3";
workSheet.Cells[3, 5].Value = 25;
workSheet.Cells[4, 5].Value = 33;
workSheet.Cells[5, 5].Value = 30;
workSheet.Cells[6, 5].Value = 22;
return workSheet.Range("$B2:$E6");
}