OOXML PasteDataTable too slow

If the excel sheet is hidden (= no redrawing during the paste) that might be the solution for you.

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

>>Any other suggestions?

no, at least no further ideas at present.

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

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).

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.

I hope you get your dinner :)

I will look into it on monday and let you know how it goes!

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#?

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

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.

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 :)