Add Excel Range to Lists without looping

Question

 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 and 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. Actually I have more than 200 excel files and each file contain 70000 rows and 70 columns. I need to collect data from these files. Looping in this situation would take enormous time. Any advice. Thanks

I will slice and dice the data and put back to different reporting excel files based on their criteria. — AAP, Jul 12 '16 at 14:16
You'll definitely want to look into streaming if it is possible, you could end up with really high memory usage storing all 70000 text rows in memory. But that would be all your judgement. — Callum Linington, Jul 12 '16 at 14:20
I mean, when you read an item from the excel spreadsheet, process it, store it back to excel spreadsheet. On a per item basis, if possible. That way you aren't attempting to fill up your memory — Callum Linington, Jul 12 '16 at 14:30
If you can, add more detail on what exactly you are doing with the data and if you are filtering or aggregating it. Adding the values of 200+ .xlsb files to the same List seems unreasonable. — Slai, Jul 12 '16 at 16:44

score 1 · Accepted Answer · edited May 23 '17 at 12:22

1

Converting them to List will be slower than just using the object[,], but if you really need a List:

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

or

xlRangeDB.Copy(); // copies to the clipboard as TSV - rows separated by "\r\n" and cells separated by '\t'
var inputData = System.Windows.Forms.Clipboard.GetText().Split('\n', '\r', '\t').ToList();

Interop Excel is usually the slowest way to read Excel files! You can try with OleDb DataReader or Excel Data Reader instead Optimal way to Read an Excel file (.xls/.xlsx)

Update

inputData.AddRange(valueArray.Cast<object>().Select(Convert.ToString));

but you will probably run out of memory at some point, so you should save the values to a file or database instead.

edited May 23 '17 at 12:22

Community

1
1

answered Jul 12 '16 at 13:36

Slai

22,144
5
45
53

Many thanks, Actually I don't know the array size which can hold 200 excel file data. Resizing and add data to array 200 times using Object[,] would work I have no idea. All Excel files are (.xlsb) format. Please advice. – AAP Jul 12 '16 at 14:14
This works perfect but how do I hold data and add more to it by looping over many workbooks. – AAP Jul 12 '16 at 15:09

Add Excel Range to Lists without looping

1 Answers1