'写文件
'传入参数:sPath-输出路径,sfileName-输出文件名,odata-输出数据
Public Sub WriteToFile(ByVal sPath As String, ByVal sfileName As String, ByVal odata As DataTable)
Dim itemvalue As String = String.Empty
Dim enterSymbol As String = Chr(10).ToString() '换行符
Dim separSymbol As String = "," '每个字段间的分割符 Try
Dim sfileFullName As String = sfileName
Response.Buffer = True
Response.AddHeader("Content-Disposition", "attachment;filename=" & sfileFullName & ";")
'Response.Charset = "bg2312"
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312")
Response.ContentType = "Text/csv" '通过修改文件类型可以让用户下载为csv类型的文件
'Response.ContentType = "application/octet-stream"
'写文件
For Each row As DataRow In odata.Rows
'获取文件名 and 生成文件
For itemcount As Integer = 0 To row.ItemArray.Length - 1
itemvalue = row.Item(itemcount).ToString() If itemcount <> row.ItemArray.Length - 1 Then
itemvalue += separSymbol
End If
Response.Write(itemvalue)
Next
Response.Write(enterSymbol)
Next
Response.End() Catch ex As Exception
Throw ex
Finally
End Try
End Sub
'传入参数:sPath-输出路径,sfileName-输出文件名,odata-输出数据
Public Sub WriteToFile(ByVal sPath As String, ByVal sfileName As String, ByVal odata As DataTable)
Dim itemvalue As String = String.Empty
Dim enterSymbol As String = Chr(10).ToString() '换行符
Dim separSymbol As String = "," '每个字段间的分割符 Try
Dim sfileFullName As String = sfileName
Response.Buffer = True
Response.AddHeader("Content-Disposition", "attachment;filename=" & sfileFullName & ";")
'Response.Charset = "bg2312"
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312")
Response.ContentType = "Text/csv" '通过修改文件类型可以让用户下载为csv类型的文件
'Response.ContentType = "application/octet-stream"
'写文件
For Each row As DataRow In odata.Rows
'获取文件名 and 生成文件
For itemcount As Integer = 0 To row.ItemArray.Length - 1
itemvalue = row.Item(itemcount).ToString() If itemcount <> row.ItemArray.Length - 1 Then
itemvalue += separSymbol
End If
Response.Write(itemvalue)
Next
Response.Write(enterSymbol)
Next
Response.End() Catch ex As Exception
Throw ex
Finally
End Try
End Sub
2、windows的换行符是“\r\n”,对应于vb是Chr(10)&Chr(13)。
3、csv格式中,如果字段的值中间有逗号,那么这个字段整个要被引号包括,如果既含有逗号也含有引号,呵呵,到网上查查或者是使用excel做个试验看看。
4、你的Response在write前,是不是应该把总的长度写上啊?!
/// 将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";
else
column.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;
else
return 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();
StringBuilder sb = new StringBuilder(); foreach (DataRow dr in ds.Tables[0].Rows)
{
Content = dr["Site"].ToString() + "," + dr["Title"].ToString() + "," + dr["Company"].ToString() + "," + dr["Location"].ToString() + "," + dr["PublishTime"].ToString() + "," + dr["Trade"].ToString();
sb.AppendLine(Content);
} Response.Clear();
Response.Buffer = false;
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/octet-stream";
Response.AppendHeader("content-disposition", "attachment;filename=" + FileName);
Response.Write(sb.ToString());
Response.Flush();
Response.End();