2D arrays and range

Jan 12, 2013 at 6:10 PM
Edited Jan 12, 2013 at 10:00 PM

Hi,

am having a huge dataset with 96000 rows and 65 cols.  currently am using excel automation to form an excel sheet and download it via ASP.net application.  this is working fine.  Am using below code to udpate the data to excel sheet

oRange = oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(intRecordCount + 1, intColCount))               

oRange.Value = strDataArray -- this is my 2d array

am forming 2d array this because, am interacting with excel only once and hence it is faster.

am facing some deployment issues, because this requires excel to be installed in deplymnet server, which we don't want to do.  so am looking for alternatives.

I have few quesetions

1. Can i use closedXML to solve my problem?  do we need to install excel to use closedXML?

2. is closedXML efficient for huge dataset?

3. can i use 2D arrays and range in closedXML to update the cell contents in one single interaction?

 

kindly help.

Thanks in advance.

Jan 13, 2013 at 8:32 PM

I converted from Excel Interop to ClosedXML for exactly the same reason as you and find it perfect for everything I need to do except pivot tables.

It can definitely do what you want need it for. Check out this piece in the documentation. It shows you how easy it is.

http://closedxml.codeplex.com/wikipage?title=Inserting%20Data&referringTitle=Documentation

Also check out this discussion http://closedxml.codeplex.com/discussions/251095

There are a few limitations to closedXML that if you need those features you would probably need a paid program like ASPOSE. ClosedXML cannot do pivot tables; nor can it process or create Excel2003 (.xls) files (it only processes and creates .xlsx files); some other things like inserting images are not handled in ClosedXML but you can use the OpenXML library that is required as part of ClosedXML to do that.

Jan 13, 2013 at 10:05 PM

Hi,

Thanks for the reply.  i tried the code in http://closedxml.codeplex.com/wikipage?title=Inserting%20Data&referringTitle=Documentation.  i does not make any difference when i use a string array like this  Dim strDataArray(1, 1) As String.  all the values, both rows and cols are appearing in same column in subsequent rows.

also i tried the below vb.net code... it is throwing a

Parameter count mismatch.

 Dim listOfLists As New List(Of List(Of String))       

listOfLists.Add(New List(Of String)(New String() {"a", "b", "c"}))       

listOfLists.Add(New List(Of String)(New String() {"d", "e", "f"}))       

listOfLists.Add(New List(Of String)(New String() {"g", "h", "i"}))

ws.Cell(1, 3).Value = "From Arrays"       

ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles")       

Dim rangeWithArrays = ws.Cell(2, 3).InsertData(listOfLists) 'Getting error here.

 

Also am not sure if i can use List.  will it store 96000 rows?  i'm guessing it will throw out of memory exception much like a dataset. is there anyway if i am make it work using a 2d string array instead of list?

Coordinator
Jan 13, 2013 at 11:38 PM

The thing is that setting cell with a list of list doesn't make much sense if you think about the behavior of a normal list. ws.Cell("A1").Value = { 1,2,3 } will set:

1 -> A1, 2 -> A2, 3 -> A3

On the other hand ws.Cell("A1").Value = { {1,2}, {3,4} } will have a problem:

1->A1, 2->A2

So far so good but then:

3->A2, 4->A3

You'll just have to loop through the lists and put the values where you need them to be.

Jan 13, 2013 at 11:43 PM

so the example mentioned in the example @ the below url http://closedxml.codeplex.com/wikipage?title=Inserting%20Data&referringTitle=Documentation. 

is wrong?

Coordinator
Jan 13, 2013 at 11:58 PM

Now I have to figure out what I was thinking 3 years ago =p

I'll get back to you tomorrow.

Jan 14, 2013 at 1:22 PM
Edited Jan 14, 2013 at 1:23 PM

Manuel, I see your quandary.

When you have a simple list in Excel you probably want the elements to be inserted, starting from the specified cell and going down the column.

However, when I think of a list of lists, I think of it as analogous to a table, or a list of objects where the first set starts in the same column and goes across columns in the same row. Then the next set starts in the next row of the same column and goes across the columns in the same row. This is just as you described the behavior in the documentation example.

So, ws.Cell("A1").Value = { {1,2}, {3,4} } works as follows:

1->A1, 2->B1

3->A2, 4->B2

To repeat myself, that's the way you envisioned it in the documentation, and it seems the most intuitive to me, and it also seems to be what cooldude_18 is looking to do. So that makes 3 of us. 

Note to cooldude_18: You say you are starting with a DataSet. ClosedXML can create a worksheet directly from a DataSet with a single line of code. See this page in the documentation, http://closedxml.codeplex.com/wikipage?title=Adding%20DataSet&referringTitle=Documentation

Jan 14, 2013 at 1:40 PM

Hi,

I don't want to start with dataset as dataset gives out of memory exception to store a 96000x65 table.  thats the reason why i am using a datareader and forming a 2D string array.  Excel automation allows to write the 2D string array directly to excel using a single statement.  That's what i am expecting in closedxml as well.