export sql server or other database query in to excel or word by using c# programming

• Download clsExcel.zip - 1.2 KB

Introduction

hi. i need a procedure for export a large data reported from sql server in excel document. by using my method we can do it. this method is vary high speed.

Background

this code is use full for database query (large data). i assume that that data is text base and do not contain any image and binary data. if other, use must have some modification in this code.

Using the code

my idea is based on office 2003 feature for supporting a HTML data. you can make a HTML data and open with every office application. for generate excel document use can use a TABLE tag. each table cell is excel cell's too.

A brief description of how to use the article or code. The class names, the methods and properties, any tricks or tips.

base of HTML generation is following code.

while (dr.Read())
    {
       sbrHTML.Append("<TR>");
       for (int i = 0; i < dr.FieldCount; i++)
       {
          sbrHTML.Append("<TD>" + good_value(dr.GetValue(i).ToString()) + "</TD>");
       }
       sbrHTML.Append("</TR>");
    } 

after generation save a document by .xls .

the excel document was made (is it a simple?).

now in your application use following code...

 clsExcel clsexcel = new clsExcel("sql text", "report header");
 clsexcel.save();

note that. if your language is right to left. you must use this code before you HTML

اگر متن فارسي داريد بايد از اين كد استفاده نماييد

sbrHTML.Append("<meta http-equiv=\"Content-Type\" 
        content=\"text/html;charset=UTF-8\">
    <style>.text { mso-number-format:\\@; } </style><TABLE 
        class=\"text\" Border=1 ID=\"Table1\">");
 

if you can't run this code. mail to me.

thanks.