Thursday, June 3, 2010

Add Excel Data to List using Range.UsedRange

public ImportExcel(string fileName)
{
//To gert the work book from choosed excel file
Microsoft.Office.Interop.Excel.Workbook workBook = application.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
//To get Active Work Sheet
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Worksheet)workBook.ActiveSheet;
//To get used Excel Cell Range
Microsoft.Office.Interop.Excel.Range range = workSheet.UsedRange;
try
{
//Create instance for Excel data storage
lstExcelData = new List>();
//Iterate through value cells in excel
for (int j = (Int32)rowIndex; j <= workSheet.UsedRange.Rows.Count; j++)
{
//Create instance ExcelData list
lstRowData = new List();
if (((Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange.Cells[colIndex, j]).Value2 != null)
lstRowHeader.Add(((Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange.Cells[colIndex, j]).Value2);
if (((Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange.Cells[j, rowIndex]).Value2 != null)
lstColumnHeader.Add(((Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange.Cells[j, rowIndex]).Value2);
for (int i = (Int32)colIndex; i <= workSheet.UsedRange.Columns.Count; i++)
{
//Iterate through every column of a particular row
if (((Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange.Cells[j, i]).Value2 != null)
{

data = ((Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange.Cells[j, i]).Value2;
//Add each row collection list
lstRowData.Add(new ExcelData(rowIndex, i, data));
}
}
//Add Each Exceldata collection list
lstExcelData.Add(lstRowData);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//To quit application
application.Quit();
}
}

No comments:

Post a Comment