none
Inserting dataset object into an excel sheet RRS feed

  • Question

  • Can you help me develop code to "export" data from a dataset in a lightswich database client and then "insert" that dataset into existing rows and columns of an sheet excel. The following is the information I have gathered so far...

    This link describes how to use oledb to get data from an excel file and import it into a text file.
         -I need to go from a lightswitch dataset to an excel sheet

    This link describes how to insert cells into a new sheet.
         - I would insert the dataset into an existing sheet using the following code change.

    WorksheetPart worksheetPart = FindWorkSheet(spreadSheet.WorkbookPart, "Sheet2");
    
    private static WorksheetPart FindWorkSheet(WorkbookPart workbookPart, string condition)
                {
                    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == condition);
    
                    if (sheet == null) throw new Exception(string.Format("Could not find sheet with name {0}", condition));
                    else return workbookPart.GetPartById(sheet.Id) as WorksheetPart;
                }

    The following link describes exporting a database dataset into a new excel document.
         -I need to insert not export the dataset into an excel sheet and do not know how to modify the code to do so.

    Tuesday, April 1, 2014 2:50 AM

All replies

  • There are many ways you can write/export/insert your data. Based on your situation you need to choose the right solution. If you wanted to append or export to simple excel file which has set of fixed columns you can use Jet OLEDB Provider and achive that.

    Step One- use below code to read data from your LightSwitch/SQL Express Database to a DataTable/DataSet.

    string connectionString = "server=localhost\\SQLExpress;database=<DB Name>;integrated Security=SSPI;";
        DataTable table = new DataTable("allPrograms");
        using(SqlConnection conn = new SqlConnection(connectionString))
        {
            string command = "SELECT * FROM Programs";
            using(SqlCommand cmd = new SqlCommand(command, conn))
            {
                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                conn.Open(); 
                adapt.Fill(table);
                conn.Close();
            }
        }

    Step-Two- Append DataTable sets to Existing excel file.

    using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"C:\1.xls" + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;READONLY=FALSE\""))
            {
                connection.Open();
                OleDbCommand commande = new OleDbCommand(
                  "INSERT INTO [Sheet1$](F1,F2,F3) VALUES ('A3','B3','C3');", connection);
                commande.ExecuteNonQuery();
                connection.Close();
                connection.Dispose();
            }

    Just curious to know, Why you need a custom Exceel export mechanism where LisghtSwitch itself provide you a ready to use Export functionalities.


    Lingaraj Mishra

    Tuesday, April 1, 2014 5:55 AM
  • Just curious to know, Why you need a custom Exceel export mechanism where LisghtSwitch itself provide you a ready to use Export functionalities.


    Lingaraj Mishra

    I need to export the data into an excel workbook that contains functions to use the data. I tried writing the =cell (contents, [Book1.xlsx]Export Medication Administratio'!$A$2) as I did previously so it would import the data into the excel template file using the export sheets generated by lightswitch. This is no longer working as it now asks for a full path name to the file not just if the files are both open. Additionally it is hard on the user to expect them to know that only one file named book 1 is open or require them and require them to save it to a specified folder to update the links. I am trying instead to add the single worksheet to the template file and set up the =cell functions to access that sheet. Ideally I would have them "save as" a new file name or write the code so that the sheet data is always written over on an existing sheet so that the same template file can be used over and over again to spit out report sheets.
    Tuesday, April 8, 2014 11:56 PM
  • Hello reigh7,

    Do you have a try with way provided by Lingaraj? The OleDb way is the usual way used to export or import between database and excel.

    Or do you get it with your way?

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 11, 2014 10:12 AM
    Moderator
  • Do you have a try with way provided by Lingaraj? The OleDb way is the usual way used to export or import between database and excel.

    I have not yet solved it. I am still trying to combine Lingaraj's code provided above with the open XML office code from this link so that I am inserting many cells at once as a dataset to an existing files sheet.

    Secondly what Using statments, and /or references must I add to use Lingaraj's code. right now I have the following in the LightSwitchApplication.cs

    using System;
    using System.Linq;
    using System.IO;
    using System.IO.IsolatedStorage;
    using System.Collections.Generic;
    using Microsoft.LightSwitch;
    using Microsoft.LightSwitch.Framework.Client;
    using Microsoft.LightSwitch.Presentation;
    using Microsoft.LightSwitch.Presentation.Extensions;

    Monday, April 14, 2014 3:26 AM
  • >>Secondly what Using statments, and /or references must I add to use Lingaraj's code. right now I have the following in the LightSwitchApplication.cs

    It uses the using System.Data and using System.Data.OleDb. For details, check this article:

    http://msdn.microsoft.com/en-us/library/aa288452(v=vs.71).aspx


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 16, 2014 5:25 AM
    Moderator
  • Thanks I will gave that a try but "using System.Data.OleDb;" OleDB does not exist so I must need to add an assembly reference.

    And thank you for linking me to the ole DB tutorials.

    • Edited by reigh7 Thursday, April 17, 2014 2:49 AM
    Thursday, April 17, 2014 2:37 AM