Add Excel Range to Lists without looping in C#

Last Reply on Jul 12, 2016 08:56 AM By Aporvi

Posted on Jul 12, 2016 05:52 AM
xlRangeDB = xlWorkSheetDB.get_Range("A2", "BA" + lRow.ToString());
                object[,] valueArray = xlRangeDB.Value2;

                List<string> inputData = new List<string>();
                inputData.AddRange(valueArray);
I would like to convert a Excel Range (xlRangeDB) to List. I couldn't find a direct way so I first convert excel range to Array and then try to add into List. But it doesn't work either.
Posted on Jul 12, 2016 05:52 AM

Please share complete code


Posted on Jul 12, 2016 06:06 AM

 

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBookDB;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheetDB;
Microsoft.Office.Interop.Excel.Range xlRangeDB;
int lRow = 0;

//Open Excel Workbook to read data

 xlWorkBookDB = xlApp.Workbooks.Open(TestWorkbook1,
                    false, true, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);

//Name Excel Sheet from where data needs to be taken

Worksheet xlWorkSheetDB = xlWorkBookDB.Worksheets["DB"];

// Check if there is any data available in the worksheet
int dataCount = (int)xlApp.WorksheetFunction.CountA(xlWorkSheetDB.Cells);

                    if (dataCount > 0)
                    {
                        lRow = xlWorkSheetDB.Cells.Find("*", xlWorkSheetDB.get_Range("A1"), Type.Missing, Type.Missing, XlSearchOrder.xlByRows, XlSearchDirection.xlPrevious).Row;
                    }

//Name a range to get data from
xlRangeDB = xlWorkSheetDB.get_Range("A2", "BA" + lRow.ToString());

//read above range into an array
object[,] valueArray = xlRangeDB.Value2;

//Try to add that array data into List
List<string> inputData = new List<string>();
inputData.AddRange(valueArray);

//If everything goes well close excel workbook
xlWorkBookDB.Close(false, TestWorkbook1, null);

 


Posted on Jul 12, 2016 08:50 AM Modified on on Jul 12, 2016 08:50 AM

Insted of using Microsoft.Office.Interop.Excel you can easily read the excel in oledb or openXml.

 

 


Posted on Jul 12, 2016 08:56 AM

Hi Dharmendr,

I have (.xlsb) excel files therefore OLEDB does not work properly.

OpenXml go through each rows. Which is not optimal if you have 200 workbooks and each workbook contains 70,000 rows and 70 columns of data.

However I found a way:

var inputData = valueArray.Cast<object>().Select(Convert.ToString).ToList();