将数据库记录转换为Excel文件（非常简单的代码）

• 下载演示项目 - 435 Kb

引言

这个项目将非常容易了解如何创建一个新的 Excel 文件以及如何将数据插入到 Excel 单元格中。为此，我们需要添加对 Microsoft Excel.dll 的引用。要添加引用，右键单击解决方案资源管理器中的“引用”->选择“添加引用”->选择“COM”选项卡->选择 microsoft excel.dll。还有一件事很重要，通常我们会收到一个**异常错误，提示“旧格式或无效的类型库”**。这段代码也会解决这个问题。我在这里附上了包含注释的应用程序的完整代码。

 

以下代码将出现在 Form1 的代码视图中。该窗体将包含一个命令按钮。在单击此按钮的事件期间，它将从表中检索数据，并将其插入到 Excel 单元格中。

DatabaseToExcel.DB objDB;       // Declaring variabe of type db class 
Excel.Application application;  // Declaring variable in type of Excel 
                                // Application 
Excel.Workbook book;//Declaring variable in type of Excel Workbook 
Excel.Worksheet sheet;//Declaring variable in type of Excel WorkSheet 
string query = "select * from TestExcel"; 
string ConnectionString = "server=FARQUI;database=sample;uid=sa;pwd=;"; 

private void Form1_Load(object sender, System.EventArgs e) 
{ 
    //Creating instance for Excel Application Class. 
    //This will create new excel application. 
    application = new Excel.ApplicationClass(); 

    //This is to convert your sysdem data into Global language. 
    //It is necessary when your system data in some other Language. 
    System.Globalization.CultureInfo oldCI = 
                     System.Threading.Thread.CurrentThread.CurrentCulture ; 
    System.Threading.Thread.CurrentThread.CurrentCulture = 
                      new System.Globalization.CultureInfo("en-US"); 

    //This is to add new excell work book into your new application. 
    book = application.Workbooks.Add(Type.Missing); 

    //To make visible the excel application while execution. 
    application.Visible = true; 
} 

private void cmd_create_Click(object sender, System.EventArgs e) 
{ 
    try 
    { 
        DataTable dt = new DataTable(); 
        objDB = new DatabaseToExcel.DB(ConnectionString); 
        dt = objDB.runQuery(query); 
        ctl_progress.Visible = true; 
        ctl_progress.Minimum = 1; 
        ctl_progress.Maximum = dt.Rows.Count + 1; 
        ctl_progress.Value = 1; 
        //This is to access the first work sheet of your application 
        sheet = (Excel.Worksheet)book.Worksheets[1]; 
        sheet.Name = "SampleExcel"; 
        for(int i = 1 ; i <= dt.Rows.Count ; i++) 
        { 
            for(int j = 1 ; j <= dt.Columns.Count ; j++ ) 
            { 
                // This is to add the Data which retrieved from 
                // the database into your Excel Sheet. 
                ((Excel.Range)sheet.Cells[i,j]).Value2
                                   = dt.Rows[i-1].ItemArray[j-1].ToString(); 
                //dt.Rows[i-1].ItemArray[j-1].ToString()--> This will retrieve 
                // data from your datatable's 
                //(i-1)th rows (j-1)st column 
            } 
            ctl_progress.Value += 1; 
        } 
        MessageBox.Show("Your Process Completed Successfully"); 
        ctl_progress.Visible = false; 
    } 
    catch(Exception e1) 
    { 
        MessageBox.Show(e1.Message); 
    } 
}

以下代码是我的 DB.cs 类。它用于处理数据库功能。

using System; 
using System.Data; 
using System.Data.SqlClient; 
namespace DatabaseToExcel 
{ 
    public class DB 
    { 
        string ConnectionString; 
        SqlConnection con; 
        public DB(string s) 
        { 
            ConnectionString = s; 
            con = new SqlConnection(ConnectionString); 
        } 
        public DataTable runQuery(string query) 
        { 
            DataTable dt = new DataTable(); 
            SqlDataAdapter da = new SqlDataAdapter(query,con); 
            da.Fill(dt); 
            return dt; 
        } 
    } 
}