Pb with fill thanx to a Dataset ..

Apr 19, 2010 at 10:58 AM

Hi!   

 

First, I prefer tell u now that i’m French and so, my English isn’t very good … ( u have maybe noticed it yet :))

I’d like to thanks u for your incredible work on this API. The SDK is so complicated next to this one !

But I have a problem :)

 

Actually, i'm not able to fill a a excel file thanks to a dataset. I wonder if the problem could be my template file, because i don't very understand what to do in this one.

I have create a Table named "Table1" with the 6 columns i want to populate. But the code does nothing.. i have checked if it's the good name for this table and it is.

If you have a solution... Don't hesitate, thx!

 

My code:

   private void DataSet_Click(object sender, RoutedEventArgs e)
        {
            using (SpreadsheetDocumentWrapper package = SpreadsheetDocumentWrapper.Open("MyWorkbook.xlsx"))
            {
                DataSet1 ds = new DataSet1();

                TableWrapper table = package.WorkbookPart.GetTablePart("Table1").Table;
                table.Fill(ds,"View_Archives");
            }

        }

Coordinator
Apr 19, 2010 at 12:41 PM

Hi shawy129, and thanks for your question.

Your code sample uses approximately the right pattern, but I don't see where it obtains the data that will populate the the Excel table. When you create a new instance of a DataSet, it is empty, so it needs some additional code to fill it with data, usually from a database. Here's the kind of code pattern that you would usually use when filling a DataSet using an SQL query:

var connection = new SqlConnection("my_connection_string");
var command = new SqlCommand("SELECT * FROM View_Archives", connection);
var adapter = new SqlDataAdapter(command);
var dataSet = new DataSet();
adapter.Fill(dataSet);

Once the DataSet is populated, you can check that it contains the necessary data using the following assertion:

System.Diagnostics.Debug.Assert(dataSet.Tables["View_Archives"] != null);

If the DataSet contains the required data, the Fill() method will attempt to inject it into the template, but only if the required conditions are met:

  • The template must contain an empty Excel table whose name matches the argument passed to the GetTablePart() method - in your case "Table1".
  • The names of the Excel table columns must exactly match the names of the columns in the specified DataSet table. If you wish to use column names that are different, you must map them explicitly using the Caption property, as described here.

If you follow all of these guidelines, this procedure should work correctly. If you still encounter problems, please can you explain how your DataSet is obtaining its data and post a copy of your template workbook.

J'aurais pu répondre en français, mais ma maitrise de la langue n'est pas terrible non plus; alors ce serait peut-être pire pour toi! Bon courage et bonne chance.

Tim.

Apr 19, 2010 at 1:18 PM

Thx for your quick answer but unfortunately, I don't find the solution :(

Actually, I populate my dataset just by creating a new Item/Dataset and then put the table i want to work on, in this dataset. And so I think the dataset is well populated!

With your solution, (sql connection, adapter, …), I d’ont arrive to get back my results… strange !

Anyway, I think the problem is my template file…

How can i show you my template file?

Thx for all !

Coordinator
Apr 19, 2010 at 1:42 PM

If you contact me via the contact page at www.extrememl.com I will send you my email address so that you can forward me the template file for analysis.