出口数据集到EXCEL [英] EXPORT Dataset to EXCEL

问题描述

我用下面的code从数据库表中的字段导出到Excel中。我想要做的是能写一个SQL语句检索多个表中的字段并导出到Excel中。这code只允许我导出一个表。另外，我怎么能显示一个保存提示对话框？示例code将AP preciated - ！非常感谢

 保护无效export_Click（对象发件人，EventArgs的发送）
{        字符串SQL = NULL;
        字符串数据= NULL;
        字符串路径= save_as.Text;        INT I = 0;
        INT J = 0;        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        反对misValue = System.Reflection.Missing.Value;        xlApp =新Excel.ApplicationClass（）;
        xlWorkBook = xlApp.Workbooks.Add（misValue）;
        xlWorkSheet =（Excel.Worksheet）xlWorkBook.Worksheets.get_Item（1）;        //的connectionString =数据源=服务器;初始目录=数据库名称;用户ID =用户名;密码=密码;;
        SqlConnection的CNN =新的SqlConnection（GetConnectionString（））;
        cnn.Open（）;
        SQL =SELECT故事，CreationDate FROM故事;
        SqlDataAdapter的dscmd =新SqlDataAdapter的（SQL，美国有线电视新闻网）
        DataSet的DS =新的DataSet（）;
        dscmd.Fill（DS）;        对于（i = 0; I＆LT; = ds.Tables [0] .Rows.Count  -  1;我++）
        {
            为（J = 0; J＆LT; = ds.Tables [0] .Columns.Count  -  1; J ++）
            {
                数据= ds.Tables [0] .Rows [I] .ItemArray [J]的ToString（）;
                xlWorkSheet.Cells第[i + 1，J + 1] =数据;
            }
        }        xlWorkBook.SaveAs（路径+XLS，Excel.XlFileFormat.xlWorkbookNormal，misValue，misValue，misValue，misValue，Excel.XlSaveAsAccessMode.xlExclusive，misValue，misValue，misValue，misValue，misValue）;
        xlWorkBook.Close（真，misValue，misValue）;
        xlApp.Quit（）;        releaseObject（xlWorkSheet）;
        releaseObject（xlWorkBook）;
        releaseObject（xlApp）;        创建//MessageBox.Show(\"Excel文件，你可以找到文件c：\\\\ csharp.net-informations.xls）;
    }    私人无效releaseObject（obj对象）
    {
        尝试
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject（OBJ）;
            OBJ = NULL;
        }
        赶上（异常前）
        {
            OBJ = NULL;
            //MessageBox.Show(\"Exception发生，同时释放对象+ ex.ToString（））;
        }
        最后
        {
            所以GC.Collect（）;
        }
    }
 

解决方案

这是不是安全在ASP.NET环境中执行办公自动化。相反，你应该生成CSV，HTML表或XLSX文件并将其发送给客户端。对于XLSX可以使用的Office Open XML SDK略微简化的东西。

<一个href=\"http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&displaylang=en\" rel=\"nofollow\">http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&displaylang=en

I am using the following code to export fields from a database table into excel. What I want to do is be able to write a SQL statement to retrieve fields from multiple tables and export them into excel. This code only allows me to export one table. Also, how can I display a save prompt dialog? Sample code would be appreciated - many thanks!

protected void export_Click(object sender, EventArgs e)
{

        string sql = null;
        string data = null;
        string path = save_as.Text;

        int i = 0;
        int j = 0;

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;";
        SqlConnection cnn = new SqlConnection(GetConnectionString());
        cnn.Open();
        sql = "SELECT Story, CreationDate FROM Story";
        SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
        DataSet ds = new DataSet();
        dscmd.Fill(ds);

        for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
        {
            for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
            {
                data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                xlWorkSheet.Cells[i + 1, j + 1] = data;
            }
        }

        xlWorkBook.SaveAs(path+".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);

        //MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            //MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

解决方案

It is not safe to perform Office Automation in an ASP.NET environment. Instead you should generate a csv, html table, or xlsx file and send it to the client. For xlsx you can use the Office Open XML SDK to simplify things slightly.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&displaylang=en

这篇关于出口数据集到EXCEL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！