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

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

这是100%适用于C#的VS-2013高级版。只需复制并粘贴代码就这样。它适用于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();
        }
    }

我试过这个代码，它正在工作。

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

如果它对你有用，试试这个。

使用列名将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;
}