使用C#将表从SQL Server导出到Excel 2007

Export Table from SQL Server to Excel 2007 using C#
2021-03-07
  •  译文(汉语)
  •  原文(英语)

我在sql中的表如下所示:

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
)

我用数据填充此表,并希望在Excel 2007中显示此表.如何使用C#进行显示?谢谢!

速聊2:
视觉工作室找不到这个:使用Excel = Microsoft.Office.Interop.Excel;
解决过程1
protected void insertBTN(object sender, EventArgs e)
{string conString = @"Data Source =XXXX; Initial Catalog=XXXX;     Persist Security Info=True;User ID=XXXX; Password=XXXX";SqlConnection sqlCon     = new SqlConnection(conString);
sqlCon.Open();

 SqlDataAdapter da = new SqlDataAdapter("SELECT * from InjuryScenario", sqlCon);
 System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
 da.Fill(dtMainSQLData);
 DataColumnCollection dcCollection = dtMainSQLData.Columns;
    // Export Data into EXCEL Sheet
 Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new                                            
 Microsoft.Office.Interop.Excel.ApplicationClass();
    ExcelApp.Application.Workbooks.Add(Type.Missing);
    // ExcelApp.Cells.CopyFromRecordset(objRS);
    for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
    {
        for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
        {
            if (i == 1)
            {
                ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
            }
            else
                ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
        }
    }
    ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\Users\\Mor Shivek\\Desktop\\test.xls");
    ExcelApp.ActiveWorkbook.Saved = true;
    ExcelApp.Quit();}
速聊1:
我成功!谢谢!您知道如何将几张表(而不只是一张表)导出到张数吗?(每个表在单独的表格中)我在上面复制我的代码
解决过程2

这对于VS-2013 Premium for C#是百分百的工作.只需复制并粘贴代码即可.它适用于SQL Server数据库.您可以将数据保存到xls,xlsx或csv中,并且可以使用相同的csv进行参数化.您需要安装以下软件包才能使其正常工作.

使用System.Data.SqlClient
使用Excel = Microsoft.Office.Interop.Excel
使用SQL = System.Data

/// ***从此处复制////

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

    }
解决过程3

我尝试了这段代码,它正在工作.

private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cnn;
            string connectionstring = null;
            string sql = null;
            string data = null;
            int i = 0;
            int j = 0;

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


            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            connectionstring ="Data Source=IN-WTS-SAM;Initial Catalog=MSNETDB;Integrated Security=True;Pooling=False";
            cnn = new SqlConnection(connectionstring);
            cnn.Open();
            sql = "SELECT * FROM Emp";
            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("informations.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 D:\\Sam-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();
            }

        }
    }

如果适合您,请尝试此操作.

速聊1:
你知道我该如何动态地做到这一点吗?如果我想在具有多台计算机的服务器上工作:ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\ Users \\ Mor Shivek \\ Desktop \\ test.xls"); @KrunalPatil
速聊2:
此代码对我有用,但我想将sql表导出为具有列名的excel.请帮忙!谢谢!
解决过程4

用列名将SQL表导出到Excel

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

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!

Talk1:
Talk2:
the visual studio can't find this: using Excel = Microsoft.Office.Interop.Excel;
Solutions1
protected void insertBTN(object sender, EventArgs e)
{string conString = @"Data Source =XXXX; Initial Catalog=XXXX;     Persist Security Info=True;User ID=XXXX; Password=XXXX";SqlConnection sqlCon     = new SqlConnection(conString);
sqlCon.Open();

 SqlDataAdapter da = new SqlDataAdapter("SELECT * from InjuryScenario", sqlCon);
 System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
 da.Fill(dtMainSQLData);
 DataColumnCollection dcCollection = dtMainSQLData.Columns;
    // Export Data into EXCEL Sheet
 Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new                                            
 Microsoft.Office.Interop.Excel.ApplicationClass();
    ExcelApp.Application.Workbooks.Add(Type.Missing);
    // ExcelApp.Cells.CopyFromRecordset(objRS);
    for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
    {
        for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
        {
            if (i == 1)
            {
                ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
            }
            else
                ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
        }
    }
    ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\Users\\Mor Shivek\\Desktop\\test.xls");
    ExcelApp.ActiveWorkbook.Saved = true;
    ExcelApp.Quit();}
Talk1:
i succeed! thank you! do you know how can i export few tables (not just one) to number of sheets? (each table in separate sheet) i copy my code above
Solutions2

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

    }
Solutions3

I tried this code it's working.

private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cnn;
            string connectionstring = null;
            string sql = null;
            string data = null;
            int i = 0;
            int j = 0;

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


            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            connectionstring ="Data Source=IN-WTS-SAM;Initial Catalog=MSNETDB;Integrated Security=True;Pooling=False";
            cnn = new SqlConnection(connectionstring);
            cnn.Open();
            sql = "SELECT * FROM Emp";
            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("informations.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 D:\\Sam-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();
            }

        }
    }

Try this if it works for you.

Talk1:
do you know how can i do this dynamically? if i want to work on server with several computers: ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\Users\\Mor Shivek\\Desktop\\test.xls"); @KrunalPatil
Talk2:
This codes work for me but I want to export sql table to excel with column name. Please help! Thank you!
Solutions4

Export sql table to excel with column name

for (i = 0; i <= ds.Tables[0].Columns.Count - 1; i++)
{
    data = ds.Tables[0].Columns[i].ColumnName.ToString();
    xlWorkSheet.Cells[1, i + 1] = data;
}
转载于:https://stackoverflow.com/questions/23028604/export-table-from-sql-server-to-excel-2007-using-c-sharp

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入