有没有一种方法可以直接使用asp.net和vb.net从sql server 2008导出整个表中的所有数据，而不使用datagridview到EXCEL文件？
6个回答
13
基本上，您只需要循环遍历DataTable的列和行，以将它们输出到响应中。这个链接展示了如何操作：This link。
在C＃中：
DataTable dt = GetData();
string attachment = "attachment; filename=Employee.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();
在VB.NET中
Dim dt As DataTable = GetData()
Dim attachment As String = "attachment; filename=Employee.xls"
Response.ClearContent()
Response.AddHeader("content-disposition", attachment)
Response.ContentType = "application/vnd.ms-excel"
Dim tab As String = ""
For Each dc As DataColumn In dt.Columns
Response.Write(tab + dc.ColumnName)
tab = vbTab
Next
Response.Write(vbLf)
Dim i As Integer
For Each dr As DataRow In dt.Rows
tab = ""
For i = 0 To dt.Columns.Count - 1
Response.Write(tab & dr(i).ToString())
tab = vbTab
Next
Response.Write(vbLf)
Next
Response.End()
- Town
1
将您页面的contenttype设置为 "ContentType="application/vnd.ms-excel""
然后将所有列标题写入到 "th" 中，将所有数据写入到 "tr" 中，并使用 "td" 进行标记。
var exceltable = new StringBuilder();
exceltable.Append("<HTML><BODY><TABLE Border=0>");
exceltable.AppendFormat("<TR>");
exceltable.AppendFormat(string.Concat("<TD>Merchantname</TD>"));
exceltable.AppendFormat(string.Concat("<TD>Pendingstatus</TD>"));
exceltable.AppendFormat(string.Concat("<TD>Date</TD>"));
exceltable.AppendFormat(string.Concat("<TD>Ordervalue</TD>"));
exceltable.AppendFormat(string.Concat("<TD>Customer commision</TD>"));
exceltable.AppendFormat(string.Concat("<TD>Affiliate commision</TD>"));
exceltable.AppendFormat(string.Concat("<TD>Customerid</TD>"));
exceltable.AppendFormat(string.Concat("<TD>Paid</TD>"));
exceltable.AppendFormat(string.Concat("<TD>Paid date</TD>"));
exceltable.AppendFormat("</TR>");
foreach (DataRow row in dt.Rows)
{
exceltable.AppendFormat("<TR>");
exceltable.AppendFormat(string.Concat("<TD>", row["NAME"].ToString(), "</TD>"));
exceltable.AppendFormat(string.Concat("<TD>", row["pendingstatus"].ToString(), "</TD>"));
exceltable.AppendFormat(string.Concat("<TD>", row["datetimeclickout"].ToString(), "</TD>"));
exceltable.AppendFormat(string.Concat("<TD>", row["ordervalue"].ToString(), "</TD>"));
exceltable.AppendFormat(string.Concat("<TD>", row["customercommision"].ToString(), "</TD>"));
exceltable.AppendFormat(string.Concat("<TD>", row["affiliatecommision"].ToString(), "</TD>"));
exceltable.AppendFormat(string.Concat("<TD>", row["user_id"].ToString(), "</TD>"));
exceltable.AppendFormat(string.Concat("<TD>", row["paid"].ToString(), "</TD>"));
exceltable.AppendFormat(string.Concat("<TD>", row["paiddate"].ToString(), "</TD>"));
exceltable.AppendFormat("</TR>");
}
exceltable.Append("</TABLE></BODY></HTML>");
Response.Write(exceltable.ToString());
页面加载时会要求您保存文件。将其保存在桌面上并使用Excel打开。
- Ivo
1
如何编写代码以在PDF中显示表格记录而不使用数据网格？这是我正在使用的代码。 私有子命令1_Click（ByVal发送者作为系统。对象，ByVal e As System.EventArgs）Handles Command1.Click Dim startTime As Date
Command1.Enabled = False
startTime = Now()
lblEnd.Text = ""
Dim clPDF As New clsPDFCreator
Dim strFile As String
Dim i As Integer
' output NAME
strFile = App_Path & "\Demo.pdf"
With clPDF
.Title = "Pay Day Report" ' TITLE
.ScaleMode = clsPDFCreator.pdfScaleMode.pdfCentimeter
.PaperSize = clsPDFCreator.pdfPaperSize.pdfA4 ' PAGE FORMAT
.Margin = 0 ' Margin
.Orientation = clsPDFCreator.pdfPageOrientation.pdfPortrait ' ORIENTATION
.EncodeASCII85 = chkASCII85.Checked
.InitPDFFile(strFile)
' DEFINING FONT
.LoadFont("Fnt1", "Times New Roman")
.LoadFont("Fnt2", "Arial", clsPDFCreator.pdfFontStyle.pdfItalic)
.LoadFont("Fnt3", "Courier New")
.LoadFontStandard("Fnt4", "Courier New", clsPDFCreator.pdfFontStyle.pdfBoldItalic)
.LoadImgFromBMPFile("Img1", App_Path & "\img\20x20x24.bmp")
.LoadImgFromBMPFile("Img2", App_Path & "\img\200x200x24.bmp")
For i = 0 To 5
' open a page
.BeginPage()
.DrawText(19, 1.5, "page " & Trim(CStr(.Pages)), "Fnt1", 12, clsPDFCreator.pdfTextAlign.pdfAlignRight)
.DrawObject("Footers")
.DrawText(10.5, 27, "Unifrieght Sage", "Fnt1", 18, clsPDFCreator.pdfTextAlign.pdfCenter)
.SetTextHorizontalScaling(70)
.DrawText(20, 25, "Regnumber", "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfAlignRight)
.DrawText(1, 25, "Name", "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfAlignLeft)
.DrawText(10.5, 25, "Surname", "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfCenter)
.SetTextHorizontalScaling(100)
Dim Name1 As String
Dim Surname As String
Dim Regnumber As String
Dim dt As DataTable
Dim tab As String = ""
Dim a As Integer
Dim cmd As OdbcCommand = New OdbcCommand("Select *from tblMain ", cn)
cmd.CommandType = CommandType.Text
Dim DR As OdbcDataReader = cmd.ExecuteReader
For Each dc As DataColumn In dt.Columns
'.DrawText(tab + dc.ColumnName)
tab = vbTab
Next
While DR.Read
Name1 = DR("name")
Surname = DR("surname")
Regnumber = ("regnumber")
Dim i As Integer
For Each drk As DataRow In dt.Rows
tab = ""
For i = 0 To dt.Columns.Count - 1
.SetTextHorizontalScaling(70)
.DrawText(20, 23 - a, Regnumber, "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfAlignRight)
.DrawText(1, 23 - a, Name1, "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfAlignLeft)
.DrawText(10.5, 23 - a, Surname, "Fnt2", 14, clsPDFCreator.pdfTextAlign.pdfCenter)
.SetTextHorizontalScaling(100)
tab = vbTab
Next
Next
.SetCharSpacing(3)
End While
.EndPage()
' this is for the footers
.StartObject("Footers", clsPDFCreator.pdfObjectType.pdfAllPages)
.DrawText(10, 1.5, "Designed by Renegate", "Fnt3", 8, clsPDFCreator.pdfTextAlign.pdfCenter)
.DrawText(20, 1.5, " of " & Trim(CStr(.Pages)), "Fnt1", 12, clsPDFCreator.pdfTextAlign.pdfAlignRight)
.EndObject()
Next
' closing the document
.ClosePDFFile()
End With
Dim Elapsed As TimeSpan = Now().Subtract(startTime)
lblEnd.Text = Elapsed.ToString()
Command1.Enabled = True
Call Shell("rundll32.exe url.dll,FileProtocolHandler " & (strFile), vbMaximizedFocus)
End Sub
- gwanztreva
0
过去我处理这个问题的方式是从DataTable创建一个CSV文件。下面是一个使用扩展方法到DataTable类的好例子：
http://blog.runxc.com/post/2009/06/24/Exporting-a-DataTable-to-Excel-(DataTable-to-CSV).aspx
在将该扩展方法添加到您的项目后，您可以像这样将CSV输出到响应流中：
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = new System.Text.UTF8Encoding();
Response.AddHeader("content-disposition", "attachment; filename=report.xls");
Response.Write(myDataTable.toCSV());
Response.End();
- Ken Pespisa
0
对我来说，这是正确的答案
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ad As New results()
Dim dt As results.ResultsDataTable
dt = ad.Read()
Dim attachment As String = "attachment; filename=USurvey.xls"
Response.ClearContent()
Response.AddHeader("content-disposition", attachment)
Response.ContentType = "application/vnd.ms-excel"
Dim tab As String = ""
For Each dc As DataColumn In dt.Columns
Response.Write(tab + dc.ColumnName)
tab = vbTab
Next
Response.Write(vbLf)
Dim i As Integer
For Each dr As DataRow In dt.Rows
tab = ""
For i = 0 To dt.Columns.Count - 1
Response.Write(tab & dr(i).ToString())
tab = vbTab
Next
Response.Write(vbLf)
Next
Response.[End]()
'export to excel
End Sub
谢谢大家！！！
- HAJJAJ
0
你可以尝试使用Office Interop，它可以创建和操作Office格式。
但要注意，它有严重的性能问题，微软官方建议不要在生产服务器上使用它，而是在客户端机器上使用（虽然对于Office 2007及以上版本可能已经改变）。
如果你需要替代方案，例如创建PDF文档，有很多插件可供选择。
另外，请参考这个问题。
- J. Ed
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接