如何将数据从数据表导出到Excel中的多个工作表 [英] How to export data from datatable to multiple sheets in Excel

问题描述

我有一个表格，例如orgcode（ddl），startdate（textbox），enddate（文本框）和一个ExportToExcel按钮。



现在输入并点击ExportToExcel Button后，我需要读取XML中的所有数据（即Reports.XML）我的解决方案本身XML文件中的数据如下：

Hi,


I have a form whcih has few things like orgcode(ddl),startdate(textbox),enddate(textbox) and a ExportToExcel button.

Now after giving inputs and clicking on ExportToExcel Button i need to read all data from XML(namely Reports.XML) which is there in my solution itself. The data in the XML file is as follows.

<Reports>
  <Report>
    <ReportName>Daily</ReportName>
    <StoredProcName>sp_GetDatafromTB</StoredProcName>
  </Report>
  <Report>
    <ReportName>Weekly</ReportName>
    <StoredProcName>sp_GetExamDetails</StoredProcName>
  </Report>
  <Report>
    <ReportName>Monthly</ReportName>
    <StoredProcName>sp_GetTeacherDetails</StoredProcName>
  </Report>
  <Report>
    <ReportName>Yearly</ReportName>
    <StoredProcName>sp_GetYearDetails</StoredProcName>
  </Report>
  <Report>
    <ReportName>Decade</ReportName>
    <StoredProcName>sp_GetYearDetails</StoredProcName>
  </Report>
  <Report>
    <ReportName>Annual</ReportName>
    <StoredProcName>sp_GetDatafromTB</StoredProcName>
  </Report>
</Reports>

现在我可以通过解析来读取XML中的所有StoredProcName。之后我需要执行所有stroed程序并将数据提取到数据表中。





我能做到这一点。



现在我需要将这个结果导出到Excel中的多个（单个）工作表中，我很震惊。请帮我如何将数据从datatabl导出到xml中的多个工作表。





安试你。

Now i am able to read all the StoredProcName from XML by parsing it. After that i need to execute all the stroed procedures and fetch the data into a datatable.


I am able to do upto this.

Now i need to export this results into multiple(individual) sheets in Excel, i am struck here. Please help me how do i export data from datatabl to multiple sheets in xml.


Thnak you.

推荐答案

private void button1_Click(object sender, EventArgs e)
        {
            //connect with database
            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password=";
            OleDbCommand command = new OleDbCommand();
            command.CommandText = "select * from parts where Cost<1000 and ListPrice>500";
            DataSet dataSet = new System.Data.DataSet();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText, connection);
            dataAdapter.Fill(dataSet);
            DataTable dt = dataSet.Tables[0];
            this.dataGridView1.DataSource = dt;
            //export specific data to Excel
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            book.Worksheets[0].InsertDataTable(this.dataGridView1.DataSource as DataTable, true, 1, 1);
            book.SaveToFile("sample.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("sample.xlsx");
        }

这篇关于如何将数据从数据表导出到Excel中的多个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！