将数据集对象插入Excel工作表 [英] Inserting dataset object into an excel sheet

问题描述

你能帮助我开发代码以"导出"吗？来自lightswich数据库客户端中的数据集的数据然后"插入"数据。将数据集转换为工作表excel的现有行和列。以下是我目前收集的信息...

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

这
链接介绍了如何使用oledb从excel文件中获取数据并将其导入文本文件。

     - 我需要从lightswitch数据集转到excel表

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;
            }

以下
链接描述了将数据库数据集导出到新的Excel文档中。

     - 我需要插入不将数据集导出到Excel工作表中，并且不知道如何修改代码来执行此操作。

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.

推荐答案

您可以通过多种方式编写/导出/插入数据。根据您的情况，您需要选择正确的解决方案。如果你想附加或导出到具有一组固定列的简单excel文件，你可以使用Jet OLEDB Provider和achive。

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.

第一步 - 使用下面的代码从LightSwitch读取数据/ SQL Express数据库到DataTable / DataSet。

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-将DataTable附加到现有excel文件。

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 '）;"，连接）;
commande.ExecuteNonQuery（）;
connection.Close（）;
connection.Dispose（）;
}

(F1,F2,F3) VALUES ('A3','B3','C3');", connection); commande.ExecuteNonQuery(); connection.Close(); connection.Dispose(); }

只是很想知道，为什么你需要一个自定义的Exceel导出机制，其中LisghtSwitch本身为你提供了一个随时可用的导出功能性。

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

这篇关于将数据集对象插入Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！