CSV导出显示CSV文件的HTML标签，只显示一个记录 [英] csv export showing HTML tags in the CSV file and only shows one record
问题描述
我将不胜感激，如果你能帮助我走出以下code ..我可以读取本地.xls文件，并显示在使用jQuery JTable中的浏览器，我也可以将数据导出为.csv文件，但由于某种原因，它显示在下载的文件的HTML标签，我相信这是由于使用
I would be very grateful if you could help me out with the following code.. I can read a local .xls file and show it on the browser using jQuery jTable, I can also export the data to .csv file but for some reason it is showing HTML tags in the downloaded file, which I believe is due to using
HtmlTextWriter tw = new HtmlTextWriter(sw);
gridvw.RenderControl(tw);
此外，它只能说明下载该.csv文件时的一个记录。我试图用的TextWriter ，但不会显示任何东西。
Also, it only shows one record when downloaded the .CSV file.. I tried using TextWriter but doesn't show anything.
public ActionResult ExportToCsv()
{
string Path = @"C:\\5Newwithdate.xls";
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" + Path + "';Extended Properties=" + (char)34 + "Excel 8.0;IMEX=1;" + (char)34 + "");
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);
con.Close();
System.Data.DataTable data = new System.Data.DataTable();
da.Fill(data);
SQLDBBillingProvider sql = new SQLDBBillingProvider();
List<TopPlayed> daa = new List<TopPlayed>();
foreach (DataRow p in data.Rows)
{
TopPlayed top = new TopPlayed()
{
TrackID = p.Field<double>("ID").ToString(),
TrackName = p.Field<string>("Track Name"),
ArtistName = p.Field<string>("Artist Name"),
Times = p.Field<double>("NoOfPlays").ToString()
};
System.Web.UI.WebControls.GridView gridvw = new System.Web.UI.WebControls.GridView();
gridvw.DataSource = top.ArtistName.ToList().Take(7);
gridvw.DataBind();
HttpContext.Response.ClearContent();
HttpContext.Response.AddHeader("content-disposition", "attachment; filename=TopTracks.csv");
HttpContext.Response.AddHeader("Expires", "0");
var sw = new StreamWriter(new MemoryStream());
// Write the data here..
HtmlTextWriter tw = new HtmlTextWriter(sw);
gridvw.RenderControl(tw);
// Flush the stream and reset the file cursor to the start
sw.Flush();
sw.BaseStream.Seek(0, SeekOrigin.Begin);
// return the stream with Mime type
return new FileStreamResult(sw.BaseStream, "text/csv");
}
return View();
}
推荐答案
这么多问题，这么短的时间：
So many issues , so little time:
您不希望在所有写出来的网格。这是为您的Web视图，但CSV可以直接从数据生成。
You do not want to write out a grid at all. That was for your web view, but CSV can be generated directly from the data.
您想是这样的：
public ActionResult ExportToCsv()
{
string Path = @"C:\\5Newwithdate.xls";
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" + Path + "';Extended Properties=" + (char)34 + "Excel 8.0;IMEX=1;" + (char)34 + "");
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);
con.Close();
System.Data.DataTable data = new System.Data.DataTable();
da.Fill(data);
SQLDBBillingProvider sql = new SQLDBBillingProvider();
List<TopPlayed> daa = new List<TopPlayed>();
// Create a memory stream and a TextWriter that uses it for its output
var sw = new StreamWriter(new MemoryStream());
TextWriter tw = new TextWriter(sw);
// Write the header row
tw.WriteLine("\"ID\", \"Track\", \"Artist\", \"Plays\"");
// Write the data here..
foreach (DataRow p in data.Rows)
{
TopPlayed top = new TopPlayed()
{
TrackID = p.Field<double>("ID").ToString(),
TrackName = p.Field<string>("Track Name"),
ArtistName = p.Field<string>("Artist Name"),
Times = p.Field<double>("NoOfPlays").ToString()
};
// Write a single CSV line
tw.WriteLine(string.Format("\"{0}\", \"{1}\", \"{2}\", \"{3}\"", top.TrackID, top.TrackName, top.ArtistName, top.Times);
}
// Now return the stream to the client/browser
HttpContext.Response.ClearContent();
HttpContext.Response.AddHeader("content-disposition", "attachment; filename=TopTracks.csv");
HttpContext.Response.AddHeader("Expires", "0");
gridvw.RenderControl(tw);
// Flush the stream and reset the file cursor to the start
sw.Flush();
sw.BaseStream.Seek(0, SeekOrigin.Begin);
// return the stream with Mime type
return new FileStreamResult(sw.BaseStream, "text/csv");
}
您实际上并不需要TopPlayed对象，但我不想过多一次更改：）
You do not actually need the TopPlayed object, but I did not want to change too much at once :)
这可能成为：
// Write the data here..
foreach (DataRow p in data.Rows)
{
// Write a single CSV line direct from the database record
tw.WriteLine(string.Format("\"{0}\", \"{1}\", \"{2}\", \"{3}\"", p.Field<double>("ID"), p.Field<string>("Track Name"), p.Field<string>("Artist Name"), p.Field<double>("NoOfPlays"));
}
请注意，您不需要（char）的34 您的连接的字符串中。这再presents一个双引号。只是逃避任何双引号与2双引号（在@风格的字符串）或\\（在一个正常的字符串）。
Please note you do not need (char)34 in your connection string. That represents a double-quote. Just escape any double-quote with 2 double quotes "" (in an @-style string) or \" (in a normal string).
例如
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Path + @"';Extended Properties=""Excel 8.0;IMEX=1;""");
又一版本
此外，如约翰·桑德斯已经决定要指出的用下来票的，你应该总是包装实现的IDisposable 的对象使用语句，以确保自动时，外出的范围，它们是正确/关闭。如果你的 SQLDBBillingProvider 工具的IDisposable 它也应该有一个使用
Yet another version:
Also, as John Saunders has decided to point out with a down-vote, you should always wrap objects that implement IDisposable in a using statement to ensure they are closed correctly/automatically when they go out of scope. If your SQLDBBillingProvider implements IDisposable it should also have a using.
我也注意到我不需要额外的的TextWriter 的 ISA 的的StreamWriter （即它继承了的TextWriter 直接）。
I also noticed I do not need the extra TextWriter isa StreamWriter (i.e. it inherits TextWriter directly).
请注意，我不能编译这个code，因为我没有缺失的部分，所以有可能是奇错字：
Please note I cannot compile this code as I do not have the missing parts, so there may be the odd typo:
public ActionResult ExportToCsv()
{
string Path = @"C:\\5Newwithdate.xls";
using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" + Path + "';Extended Properties=" + (char)34 + "Excel 8.0;IMEX=1;" + (char)34 + ""))
{
using (OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con))
{
con.Close();
System.Data.DataTable data = new System.Data.DataTable();
da.Fill(data);
SQLDBBillingProvider sql = new SQLDBBillingProvider();
List<TopPlayed> daa = new List<TopPlayed>();
// Create a memory stream and a TextWriter that uses it for its output
using (var sw = new StreamWriter(new MemoryStream()))
{
// Write the header row
sw.WriteLine("\"ID\", \"Track\", \"Artist\", \"Plays\"");
// Write the data here..
foreach (DataRow p in data.Rows)
{
TopPlayed top = new TopPlayed()
{
TrackID = p.Field<double>("ID").ToString(),
TrackName = p.Field<string>("Track Name"),
ArtistName = p.Field<string>("Artist Name"),
Times = p.Field<double>("NoOfPlays").ToString()
};
// Write a single CSV line
sw.WriteLine(string.Format("\"{0}\", \"{1}\", \"{2}\", \"{3}\"", top.TrackID, top.TrackName, top.ArtistName, top.Times);
}
// Now return the stream to the client/browser
HttpContext.Response.ClearContent();
HttpContext.Response.AddHeader("content-disposition", "attachment; filename=TopTracks.csv");
HttpContext.Response.AddHeader("Expires", "0");
// Flush the stream and reset the file cursor to the start
sw.Flush();
sw.BaseStream.Seek(0, SeekOrigin.Begin);
// return the stream with Mime type
return new FileStreamResult(sw.BaseStream, "text/csv");
}
}
}
}
这篇关于CSV导出显示CSV文件的HTML标签，只显示一个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
