ExcelTechnology
Export Data in DataSet to Excel
We often use Excel during working for it is convenient for saving and displaying data. Also, Excel is very powerful to calculate data. Actually, in order to store a large amount of data, every company has a small database. When we want to use the data, we should get it from database. Well, how to export data to Excel? This article focuses on converting data in DataSet to Excel.
If we want to export data in DataSet to Excel, it is essential to add Office COM references for we need some methods and properties when exporting Excel objects. For this method, we need to generate XML file firstly and then import to database.
public void DataSetToExcel(DataSet ds,string FileName)
{
try
{
//Define Web Page
//System.Web.UI.Page mypage=new System.Web.UI.Page();
HttpResponse resp;
resp=HttpContext.Current.Response;
resp.ContentEncoding=System.Text.Encoding.GetEncoding(“GB23123);
resp.AppendHeader(“Content-disposition”,”attachment;filename=”+FileName+”.xls”);
resp.ContentType=”application/ms-excel”;
//Define Variable
string colHeaders=null;
string Is_item=null;
//Display Format Definition////////////////
//Define File Stream Operation
//FileStream fs=new FileStream(FileName,FileMode.Create,FileAccess.Write);
//StreamWriter sw = new StreamWriter (fs, System. Text. Encoding. GetEncoding (“GB23123));
StringWriter sfw=new StringWriter();
//Define Table Object and Row Object, and Initialize Value with DataSet
System.Data.DataTable dt=ds.Tables[0];
DataRow[] myRow=dt.Select();
int i=0;
int cl=dt.Columns.Count;
//Get Column Title of Data Table, and Segment Title with t. Add Enter Symbol after the Last Column Title
for(i=0;i
{
//if(i==(cl-1)) //Last column, add n
// colHeaders+=dt.Columns[i].Caption.ToString();
//else
colHeaders+=dt.Columns[i].Caption.ToString()+”t”;
}
sfw.WriteLine(colHeaders);
//sw.WriteLine(colHeaders);
//Deal with Data Row by Row
foreach(DataRow row in myRow)
{
//Type Data
for(i=0;i
{
//if(i==(cl-1))
// Is_item+=row[i].ToString()+”n”;
//else
Is_item+=row[i].ToString()+”t”;
}
sfw.WriteLine(Is_item);
//sw.WriteLine(Is_item);
Is_item=null;
}
resp.Write(sfw);
//resp.Clear();
resp.End();
}
catch(Exception e)
{
throw e;
}
}
Sometimes, we need to deal with lots of data exportation to Excel or Word. In order to save time and energy, we may use the third party Add-ins. In fact, some Add-in has high quality and can export a large amount of data quickly. As I know, Spire.DataExport specializes in exporting data to various format files. And it published the free component for exporting data recently. The free version remains the basic function to export data of official one. The difference is that the free version can’t satisfy customers with some additional requirements, for example, special data format setting.
