使用C＃从SQL Server到Excel 2007中导出表 [英] Export Table from SQL Server to Excel 2007 using C#

问题描述

我在SQL表如下所示：

  CREATE TABLE InjuryScenario 
（
 InjuryScenario_id INT身份（1,1），
 InjuryDay INT，
 InjuryMonth INT，
 InjuryYear INT，
 InjuryDesc VARCHAR（80），
 InjuryComments VARCHAR（50），
 AlmostInjury整型，
 InjuryInSchool VARCHAR（20），
 ProductInjury VARCHAR（20），
 Cause_id整型，
 CauseType_id整型，
 CauseChar_id整型，
 Place_id INT，
 PlaceType_id INT，
 InjuryDate_id INT，
用户名VARCHAR（50），
时间戳的datetime默认值（GETDATE（）），
约束pk_InjuryScenario_id主键（InjuryScenario_id） ，
约束fk_Cause_InjuryScenario外键（Cause_id）引用ON DELETE CASCADE原因（Cause_id），
约束fk_CauseType_InjuryScenario外键（CauseType_id）引用CauseType（CauseType_id）上删除不采取行动，
约束fk_CauseChar_InjuryScenario外键（CauseChar_id）引用CauseChar（CauseChar_id）上删除不采取行动，
约束fk_Place_InjuryScenario外键（Place_id）ON DELETE CASCADE将引用广场（Place_id），
约束fk_PlaceType_InjuryScenario外键（PlaceType_id）引用PlaceType（PlaceType_id）上删除无动作，
约束fk_InjuryDate_InjuryScenario外键的删除级联（InjuryDate_id）引用InjuryDate（InjuryDate_id），
约束fk_Users_InjuryScenario外键（用户名）引用ON DELETE CASCADE 
）$ b中的用户（用户名） $ b  

我填写此表的数据，我想显示在Excel 2007
哪有这个表我这样做是使用C＃？
谢谢！

解决方案

这是百分之百的为VS-2013高级工作C＃。只是复制
和粘贴这就是它的代码。其SQL Server数据库的工作你
可以将数据保存到 XLS，XLSX或CSV 并可以使用相同的csv
进行参数设置。你需要安装下面的包，使
它的工作。

使用使用Excel = Microsoft.Office System.Data.SqlClient的结果
。使用SQL = System.Data这

Interop.Excel结果

/// ***复制从这里// //

  SqlConnection的CNN; 
字符串的ConnectionString = NULL; 
字符串SQL = NULL; 
字符串数据= NULL; 
 INT I = 0; 
 INT J = 0; 
 
 
 
 //// ***准备Excel应用程序
 
 Excel.Application xlApp; 
 Excel.Workbook xlWorkBook; 
 Excel.Worksheet xlWorkSheet; 
对象misValue = System.Reflection.Missing.Value; 
 
 /// ***打开Excel应用程序
 
 xlApp =新Microsoft.Office.Interop.Excel.Application（）; 
 xlWorkBook = xlApp.Workbooks.Open（@Fullpath\Book1.csv）; 
 xlWorkSheet =（Excel.Worksheet）（xlWorkBook.ActiveSheet为Excel.Worksheet）; 
 
 
 //// ***它总是从CSV文件，这样我们就可以随时得到更新的数据
 xlWorkSheet.UsedRange.Select删除prvious结果（） ; 
 xlWorkSheet.UsedRange.Delete（Excel.XlDeleteShiftDirection.xlShiftUp）; 
 xlApp.DisplayAlerts = FALSE; 
 //xlWorkBook.Save（）; 
 
 ///// ***使用Windows身份验证
 
的ConnectionString =打开SQL数据库集成安全性= SSPI;初始目录=数据库名;数据源=服务器;; 
 CNN =新的SqlConnection（ConnectionString中）; 
 cnn.Open（）; 
 
 ** ////写您的SQL查询这里
 SQL =SELECT TOP 10 [名字]，[中间名]，[姓氏]，[邮件]，[AltEmail]，[ AuthorizedUserName]从表名; 
 
 /// ***准备从数据库中检索
 SQL.DataTable dtable =新SQL.DataTable）值（; 
 
 SqlDataAdapter的dscmd =新SqlDataAdapter的（SQL，美国有线电视新闻网） 
 SQL.DataSet DS =新SQL.DataSet（）; 
 dscmd.Fill（dtable）; 
 
 //// ***生成的列名在这里
 
的String [] = colNames新的字符串[dtable.Columns.Count] 
 
 INT山坳= 0; 
 
的foreach（在dtable.Columns SQL.DataColumn DC）
 colNames [COL ++] = dc.ColumnName; 
 
字符lastColumn =（char）的（65 + dtable.Columns.Count  -  1）; 
 
 xlWorkSheet.get_Range（A1，lastColumn +1）值2 = colNames。 
 xlWorkSheet.get_Range（A1，lastColumn +1）Font.Bold =真。 
 xlWorkSheet.get_Range（A1，lastColumn +1）VerticalAlignment 
 = Excel.XlVAlign.xlVAlignCenter。 
 
 ///// ***插入列和值到Excel文件
 
 
 
为（i = 0; I< = dtable.Rows.Count  -  1;我++）
 {
为（J = 0; J< = dtable.Columns.Count-1; J ++）
 {
数据= dtable.Rows [I] .ItemArray [J]的ToString（）; 
 xlWorkSheet.Cells第[i + 2，J + 1] =数据; 
 
} 
} 
 
 
 /// **保存，恕不另行通知csv文件。 
 xlApp.DisplayAlerts = FALSE; 
 xlWorkBook.Save（）; 
 
 
 xlWorkBook.Close（真，misValue，misValue）; 
 xlApp.Quit（）; 
 
 releaseObject（xlWorkSheet）; 
 releaseObject（xlWorkBook）; 
 releaseObject（xlApp）; 
 
} 
 
 
 
私人无效releaseObject（obj对象）
 {
试
 {
 System.Runtime.InteropServices.Marshal.ReleaseComObject（OBJ）; 
的obj = NULL; 
} 
赶上（异常前）
 {
的obj = NULL; 
 MessageBox.Show（出现异常，同时释放对象+ ex.ToString（））; 
} 
终于
 {
 GC.Collect的（）; 
} 
 
} 
  

my table in sql looks like this:

CREATE TABLE InjuryScenario
(
InjuryScenario_id int identity(1,1),
InjuryDay int,
InjuryMonth int,
InjuryYear int,
InjuryDesc varchar(80),
InjuryComments varchar(50),
AlmostInjury int,
InjuryInSchool varchar(20),
ProductInjury varchar(20),
Cause_id int,
CauseType_id int,
CauseChar_id int,
Place_id int,
PlaceType_id int,
InjuryDate_id int,
Username varchar(50),
TimeStamp datetime default (getdate()),
constraint pk_InjuryScenario_id primary key (InjuryScenario_id),
constraint fk_Cause_InjuryScenario foreign key(Cause_id) references Cause(Cause_id) on delete cascade,
constraint fk_CauseType_InjuryScenario foreign key(CauseType_id) references CauseType(CauseType_id) on delete no action,
constraint fk_CauseChar_InjuryScenario foreign key(CauseChar_id) references CauseChar(CauseChar_id) on delete no action,
constraint fk_Place_InjuryScenario foreign key(Place_id) references Place(Place_id) on delete cascade,
constraint fk_PlaceType_InjuryScenario foreign key(PlaceType_id) references PlaceType(PlaceType_id) on delete no action,
constraint fk_InjuryDate_InjuryScenario foreign key(InjuryDate_id) references InjuryDate(InjuryDate_id) on delete cascade, 
constraint fk_Users_InjuryScenario foreign key(Username) references Users(Username) on delete cascade
)

I fill this table with data and i want to show this table in Excel 2007. how can i do it using c#? Thank you!

解决方案

This is hundred percent working for VS-2013 Premium for C#. just copy and paste the code That's it. Its working for SQL Server database You can save the data into xls, xlsx, or csv and can use that same csv for parameterization. you need to install following packages to make it work.

using System.Data.SqlClient
using Excel=Microsoft.Office.Interop.Excel
using SQL = System.Data

///***Copy from here////

        SqlConnection cnn;
        string connectionstring = null;
        string sql = null;
        string data = null;
        int i = 0;
        int j = 0;



        ////*** Preparing excel Application

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

        ///*** Opening Excel application

        xlApp = new Microsoft.Office.Interop.Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open(@"Fullpath\Book1.csv");
        xlWorkSheet = (Excel.Worksheet)(xlWorkBook.ActiveSheet as Excel.Worksheet);


        ////*** It will always remove the prvious result from the CSV file so that we can get always the updated data
        xlWorkSheet.UsedRange.Select();
        xlWorkSheet.UsedRange.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
        xlApp.DisplayAlerts = false;
        //xlWorkBook.Save();

        /////***Opening SQL Database using Windows Authentication

        connectionstring = "Integrated Security = SSPI;Initial Catalog=DatabaseName; Data Source=ServerName;";
        cnn = new SqlConnection(connectionstring);
        cnn.Open();

        ////** Write your Sql Query here
        sql = "SELECT  TOP 10 [FirstName],[MiddleName],[LastName],[Email],[AltEmail],[AuthorizedUserName] From Tablename";

        ///*** Preparing to retrieve value from the database
        SQL.DataTable dtable = new SQL.DataTable();

        SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
        SQL.DataSet ds = new SQL.DataSet();
        dscmd.Fill(dtable);

        ////*** Generating the column Names here

        string[] colNames = new string[dtable.Columns.Count];

        int col = 0;

        foreach (SQL.DataColumn dc in dtable.Columns)
            colNames[col++] = dc.ColumnName;

        char lastColumn = (char)(65 + dtable.Columns.Count - 1);

        xlWorkSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
        xlWorkSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
        xlWorkSheet.get_Range("A1", lastColumn + "1").VerticalAlignment
                    = Excel.XlVAlign.xlVAlignCenter;

        /////*** Inserting the Column and Values into Excel file



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

                }
            }


        ///**Saving the csv file without notification.
            xlApp.DisplayAlerts = false;
            xlWorkBook.Save();


        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

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

}



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

    }

这篇关于使用C＃从SQL Server到Excel 2007中导出表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！