Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • Last Modified:

OOXML PasteDataTable too slow

I am trying to find a way to place a DataTable set into an Excel sheet using OOXML.

I have tried using the PasteDataTable function, but this takes 3 minutes to paste 11,000 records. I know there must be a quicker way to do this - but uptil now I have not been able to find it. A lmgtfy answer will not suffice ;)

There must be a quicker way to do this.

I am using Office 2010 (Excel 2010) with OOXML, C#, Visual Studios 2010. The code will be in a webpart used in SharePoint 2010 to generate an Excel file based on information from an SQL server 2010 database.

The code calls a stored procedure which returns a DataSet. This DataSet contains one DataTable. I pass this DataTable to the PasteDataTable function of OOXML. It pastes it properly (not looking at formatting right now - will pick this up later) but it takes way too long.

Currently the excel file has a Data Connection which grabs the information from the database. As this excel sheet needs to be opened by many different users on many different computers I would rather not use this Excel data connection. As they get the Excel file from SharePoint I decided to create a webpart which will "Generate" the data into a new Excel sheet - and thus no longer needing the Data Connection.

Anyone have any ideas how I can make a function like PasteDataTable work just as fast as the original Data Connection in Excel (less than a second compared to 3 minutes)?
0
Wiesje
Asked:
Wiesje
  • 6
  • 2
  • 2
  • +1
1 Solution
 
AndyAinscowCommented:
If the excel sheet is hidden (= no redrawing during the paste) that might be the solution for you.
0
 
WiesjeAuthor Commented:
Hi Andy,

The excel sheet is not open in the conventional sense of the word. OOXML reads in the XML of the excel sheet and changes that. So even if the sheet was hidden or not it would not make a difference.

I have however tested this earlier as I was trying to find ways to speed it up, it did, however, not help one bit.

Any other suggestions?

Wiesje
0
 
AndyAinscowCommented:
>>Any other suggestions?

no, at least no further ideas at present.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
aikimarkCommented:
If this were a traditional Excel automation, I'd suggest using the CopyFromRecordset method of a range object or populate a 2D array and to a direct assignment of the values to a 2D range.value property.

I wrote about this here:
http:A_2253.html

However, I don't know if you have any of those options with OOXML
0
 
WiesjeAuthor Commented:
Fantastic article! Really insightful and it would definitely help me in this if I was trying to put the Data into the Excel sheet using VBA / Excel.

I was putting the data into the Excel sheet using a Data Connection (which is incredibly fast) - but as I do not want the Excel sheet to know that data exists and only "receive" it from the WebPart / C# code - it will not help me.

Well it could - if there was a C# version of this, that would do exactly the same.

I will try and see if I can do something similar in C#.

Is there anyone out there that has done something like that in C#?
(I'm trying to reduce the amount of VBA code in the excel sheet - because of the security risk of having a database connection in the excel sheet to name but one reason).
0
 
aikimarkCommented:
You can instantiate an Excel Automation object and do the data push from other applications, including .Net applications.  You could even do this from VB Script (not compiled), and Delphi (non Microsoft development product).  One of my colleagues was having a problem pushing data from his Access application into Excel.  I steered him to my article and suggested that he use CopyFromRecordset.  He was ecstatic -- the process is much faster than what he was using, can copying to any destination cell (not just A1), and isn't limited to 64k rows. Which reminds me...he still owes me dinner for that tip.

However, I don't know if VSTO/OOXML provides the same view of Excel objects (workbook, worksheet, cells, ranges, etc.) If it does, then you should first look at the CopyFromRecordset method, since you already have a recordset like object.
0
 
WiesjeAuthor Commented:
I hope you get your dinner :)

I will look into it on monday and let you know how it goes!
0
 
WiesjeAuthor Commented:
I have had a play with it in C# but cant quite get the code to do what it does in VBA.

Does anyone else have any ideas how to quickly paste a lot of data into an xlsm sheet, using C#?
0
 
myfriendhenryCommented:
This is what I would do, forget xml, put it out to a csv, direct them to that new local file.  I'll assume the vb to c# translation is ok with you.

' Save the input DataTable to a CSV file. By default the values are Tab
' delimited, but you can use the second overload version to use any other
' string you want.
'
' Example:
'    Dim ds As New DataSet
'    SqlDataAdapter1.Fill(ds, "Users")
'    DataTable2CSV(ds.Tables("Users"), "D:\Users.txt")

Sub DataTable2CSV(ByVal table As DataTable, ByVal filename As String)
    DataTable2CSV(table, filename, vbTab)
End Sub
Sub DataTable2CSV(ByVal table As DataTable, ByVal filename As String, _
    ByVal sepChar As String)
    Dim writer As System.IO.StreamWriter
    Try
        writer = New System.IO.StreamWriter(filename)

        ' first write a line with the columns name
        Dim sep As String = ""
        Dim builder As New System.Text.StringBuilder
        For Each col As DataColumn In table.Columns
            builder.Append(sep).Append(col.ColumnName)
            sep = sepChar
        Next
        writer.WriteLine(builder.ToString())

        ' then write all the rows
        For Each row As DataRow In table.Rows
            sep = ""
            builder = New System.Text.StringBuilder

            For Each col As DataColumn In table.Columns
                builder.Append(sep).Append(row(col.ColumnName))
                sep = sepChar
            Next
            writer.WriteLine(builder.ToString())
        Next
    Finally
        If Not writer Is Nothing Then writer.Close()
    End Try
End Sub
0
 
WiesjeAuthor Commented:
I would love to be able to send them to a CSV file - but the excel file needs a lot of specific formatting (locking, formulas, title etc etc).

Currently I have set Excel back to reading the data in via the Connection options in Excel. It does mean that Excel needs a data connection with the database (not very good for security!). The main issue is - when I read only 10,000 lines in using OOXML it takes 2,5 minutes... that is just not acceptable. When it becomes 30,000 lines - it's about 8 minutes. But when these 30,000 lines are read into it using the OLEDB database connection in Excel it only takes 2 seconds.

My thought on this matter is, and perhaps the question should now be: How can I create such a connection in the Excel sheet using C# (the webpart code in SharePoint), so that the excel document does not have a persistent database connection which the end-user can see?

Actually, thinking while I type, perhaps I can first write it to a CSV file - and then use VBA code to read it into the proper place in the Excel document - which means the excel document does not need a database OLEDB connection. That would work!

If anyone has a better solution - I'd love to hear - otherwise I will accept the above solution as half the solution for the current need.
0
 
myfriendhenryCommented:
How about create your fancy excel file with a named location, say "CSV DATA"
Macro in the fancy template file will fire upon start-up that will import the csv data into the named location.  Re-reading your last comment, you may have just said that!  lol
0
 
WiesjeAuthor Commented:
LOL I did actually just say that - but it is a brilliant idea :)

And after having thought about it some more - that is how I'll do it. It is quick, easy and no security risk!

Thanks for giving me the idea :)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now