已经有了几篇完备的SQL Server查询的结果集导出为Excel的技术文章:http://www.cnblogs.com/meyer/archive/2004/10/18/6977.htmlhttp://dev.csdn.net/develop/article/18/18623.shtm下面是导出为CSV格式的代码:DataSet导出CSV格式(ASP.NET,C#)本文引用下面的 Microsoft .NET 框架类库命名空间:System.Data;System.Web.UI.WebControls;概要本文解决将DataSet导出到CSV格式问题、将DataGrid中的数据导出到CSV格式问题导出DataSet所有的列到CSV格式遍历DataSet的Tables遍历Table的Rows遍历Rows的Columns代码/// <summary>/// 将DataSet导出成CSV格式/// </summary>/// <param name="ds">DataSet</param>/// <returns>CSV字符串数据</returns>public static string ExportCSV(DataSet ds){string data = "";//data = ds.DataSetName + "\n";foreach(DataTable tb in ds.Tables){data += tb.TableName + "\n";//写出列名foreach (DataColumn column in tb.Columns){data += column.ColumnName + ",";}data += "\n";//写出数据foreach (DataRow row in tb.Rows){foreach (DataColumn column in tb.Columns){data += row[column].ToString() + ",";}data += "\n";}data += "\n";}return data;}DataSet与DataGrid问题一般情况下我们用DataGrid显示DataSet时,只显示我们需要的列,并不显示DataSet所有的列,所以导出成CSV格式的时候可能只需要导出DataGrid中的列。本文解决方法:将DataSet中需要导出的列做上标记,在导出时只将有标记的列导出。代码/// <summary>/// 标记DataColumn为接受导出的/// </summary>/// <param name="column">DataColumn</param>public static void SetExport(DataColumn column){if (column != null){if (column.ExtendedProperties["IsExport"] == null)column.ExtendedProperties.Add("IsExport", "true";elsecolumn.ExtendedProperties["IsExport"] = "true";}}/// <summary>/// 标记DataTable中的一些列为接受导出的/// </summary>/// <param name="tb">DataTable</param>/// <param name="columns">列</param>public static void SetExport(DataTable tb,params string[] columns){foreach(string column in columns){SetExport(tb.Columns[column]);}}/// <summary>/// 标记DataTable中的一些列为接受导出的/// </summary>/// <param name="tb">DataTable</param>/// <param name="columns">DataGrid的列</param>public static void SetExport(DataTable tb,DataGridColumnCollection columns){foreach(DataGridColumn column in columns){if (column.GetType().Name == "BoundColumn" || column.GetType().Name == "HyperLinkColumn" || column.GetType().Name == "TemplateColumn")SetExport(tb.Columns[column.HeaderText]);}}/// <summary>/// 判断DataColumn是否为接受导出的/// </summary>/// <param name="column">DataColumn</param>/// <returns>bool型的结果</returns>public static bool IsExport(DataColumn column){if (column.ExtendedProperties["IsExport"] != null && column.ExtendedProperties["IsExport"].ToString().Trim().ToLower() == "true")return true;elsereturn false;}使用示例:Export.SetExport(ds.供应商列表视图,dg.Columns);string data = Export.ExportCSV(ds);string temp = string.Format("attachment;filename={0}","ExportData.csv";Response.ClearHeaders();Response.AppendHeader("Content-disposition", temp);Response.Write(data);Response.End();
本文转自斯克迪亚博客园博客,原文链接:http://www.cnblogs.com/sgsoft/archive/2004/11/24/68031.html,如需转载请自行联系原作者