如何将数据网格保存为vb.net中的excel?
答
它需要是本机XLS文件吗?您最好的选择可能只是将数据导出为CSV文件,这是一种纯文本且相当容易生成的文件。对于大多数用户,CSV默认情况下会在Excel中打开,因此他们不会知道区别。
答
这里有一些代码用于在我们的许多应用程序中执行此操作。我们有一个特殊的方法来清理“不可导出”列。此外,我们不会在没有标题的情况下导出列,但您可以根据需要调整该逻辑。
编辑:代码格式化程序不喜欢vb.net - 你可以复制/粘贴到visual studio,它会没事的。
Public Overloads Shared Function BuildExcel(ByVal gView As System.Web.UI.WebControls.GridView) As String
PrepareGridViewForExport(gView)
Dim excelDoc As New StringBuilder
Dim startExcelXML As String = " " + _
" " + _
" " + _
" " + _
" " + _
" " + _
" " + _
" " + _
" "
Dim endExcelXML As String = ""
Dim rowCount As Int64 = 0
Dim sheetCount As Int16 = 1
excelDoc.Append(startExcelXML)
excelDoc.Append("")
excelDoc.Append("")
' write out column headers
excelDoc.Append("")
For x As Int32 = 0 To gView.Columns.Count - 1
'Only write out columns that have column headers.
If Not gView.Columns(x).HeaderText = String.Empty Then
excelDoc.Append("")
excelDoc.Append(gView.Columns(x).HeaderText.ToString)
excelDoc.Append("")
End If
Next
excelDoc.Append("")
For r As Int32 = 0 To gView.Rows.Count - 1
rowCount += rowCount
If rowCount = 64000 Then
rowCount = 0
sheetCount += sheetCount
excelDoc.Append("")
excelDoc.Append(" ")
excelDoc.Append("")
excelDoc.Append("")
End If
excelDoc.Append("")
For c As Int32 = 0 To gView.Rows(r).Cells.Count - 1
'Don't write out a column without a column header.
If Not gView.Columns(c).HeaderText = String.Empty Then
Dim XMLstring As String = gView.Rows(r).Cells(c).Text
XMLstring = XMLstring.Trim()
XMLstring = XMLstring.Replace("&", "&")
XMLstring = XMLstring.Replace(">", ">")
XMLstring = XMLstring.Replace("" + "")
excelDoc.Append(XMLstring)
excelDoc.Append("")
End If
Next
excelDoc.Append("")
Next
excelDoc.Append("")
excelDoc.Append(" ")
excelDoc.Append(endExcelXML)
Return excelDoc.ToString
End Function
Shared Sub PrepareGridViewForExport(ByVal gview As System.Web.UI.Control)
' Cleans up grid for exporting. Takes links and visual elements and turns them into text.
Dim lb As New System.Web.UI.WebControls.LinkButton
Dim l As New System.Web.UI.WebControls.Literal
Dim name As String = String.Empty
For i As Int32 = 0 To gview.Controls.Count - 1
If TypeOf gview.Controls(i) Is System.Web.UI.WebControls.LinkButton Then
l.Text = CType(gview.Controls(i), System.Web.UI.WebControls.LinkButton).Text
gview.Controls.Remove(gview.Controls(i))
gview.Controls.AddAt(i, l)
ElseIf TypeOf gview.Controls(i) Is System.Web.UI.WebControls.DropDownList Then
l.Text = CType(gview.Controls(i), System.Web.UI.WebControls.DropDownList).SelectedItem.Text
gview.Controls.Remove(gview.Controls(i))
gview.Controls.AddAt(i, l)
ElseIf TypeOf gview.Controls(i) Is System.Web.UI.WebControls.CheckBox Then
l.Text = CType(gview.Controls(i), System.Web.UI.WebControls.CheckBox).Checked.ToString
gview.Controls.Remove(gview.Controls(i))
gview.Controls.AddAt(i, l)
End If
If gview.Controls(i).HasControls() Then
PrepareGridViewForExport(gview.Controls(i))
End If
Next
End Sub
答
尝试了这一点,这是一个触摸比Brendans简单,但不能作为“功能丰富”:
Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'Export to excel
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter
Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)
Me.ClearControls(gvSearchTerms)
gvSearchTerms.RenderControl(oHtmlTextWriter)
Response.Write(oStringWriter.ToString)
Response.End()
End Sub
Private Sub ClearControls(ByVal control As Control)
Dim i As Integer = (control.Controls.Count - 1)
Do While (i >= 0)
ClearControls(control.Controls(i))
i = (i - 1)
Loop
If Not (TypeOf control Is TableCell) Then
If (Not (control.GetType.GetProperty("SelectedItem")) Is Nothing) Then
Dim literal As LiteralControl = New LiteralControl
control.Parent.Controls.Add(literal)
Try
literal.Text = CType(control.GetType.GetProperty("SelectedItem").GetValue(control, Nothing), String)
Catch ex As System.Exception
End Try
control.Parent.Controls.Remove(control)
ElseIf (Not (control.GetType.GetProperty("Text")) Is Nothing) Then
Dim literal As LiteralControl = New LiteralControl
control.Parent.Controls.Add(literal)
literal.Text = CType(control.GetType.GetProperty("Text").GetValue(control, Nothing), String)
control.Parent.Controls.Remove(control)
End If
End If
Return
End Sub
Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
Return
End Sub
答
你可以使用水晶,因为它被内置到VS.使用适当的列预定义水晶报表,然后您可以使用您用于数据网格或网格视图的任何数据源。
Dim report_source As CrystalDecisions.Web.CrystalReportSource
report_source.ReportDocument.SetDataSource(dt) 'DT IS A DATATABLE
report_source.Report.FileName = "test.rpt"
report_source.ReportDocument.Refresh()
report_source.ReportDocument.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.Excel, "c:\test.xls")
答
我会再次警告做一个double for循环来取出每个datacell的数据,并单独写出一个excel单元格。相反,使用2D对象数组,并循环访问您的数据网格,将所有数据保存在那里。然后,您可以设置一个等于该2D对象数组的Excel范围。
这将比写入excel逐个单元更快几个数量级。有些报告说,我一直在研究这个过去需要两个小时才能完成出口的报告已经减少到不到一分钟。
答
我设置了GridView和再使用的HTML文本编写对象吐出来的.xls文件,像这样:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'get the select command of the gridview
sqlGridview.SelectCommand = Session("strSql")
gvCompaniesExport.DataBind()
lblTemp.Text = Session("strSql")
'do the export
doExport()
'close the window
Dim closeScript As String = "<script language='javascript'> window.close() </scri"
closeScript = closeScript & "pt>"
'split the ending script tag across a concatenate to keep it from causing problems
'this will write it to the asp.net page and fire it off, closing the window
Page.RegisterStartupScript("closeScript", closeScript)
End Sub
Public Sub doExport()
Response.AddHeader("content-disposition", "attachment;filename=IndianaCompanies.xls")
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim objStrWriter As New System.IO.StringWriter
Dim objHtmlTextWriter As New System.Web.UI.HtmlTextWriter(objStrWriter)
'Get the gridview HTML from the control
gvCompaniesExport.RenderControl(objHtmlTextWriter)
'writes the dg info
Response.Write(objStrWriter.ToString())
Response.End()
End Sub
答
我用这一切的时候:
public static class GridViewExtensions
{
public static void ExportToExcel(this GridView gridView, string fileName, IEnumerable<string> excludeColumnNames)
{
//Prepare Response
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition",
string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a table to contain the grid
Table table = new Table();
// include the gridline settings
table.GridLines = gridView.GridLines;
// add the header row to the table
if (gridView.HeaderRow != null)
{
PrepareControlForExport(gridView.HeaderRow);
table.Rows.Add(gridView.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gridView.Rows)
{
PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (gridView.FooterRow != null)
{
PrepareControlForExport(gridView.FooterRow);
table.Rows.Add(gridView.FooterRow);
}
// Remove unwanted columns (header text listed in removeColumnList arraylist)
foreach (DataControlField column in gridView.Columns)
{
if (excludeColumnNames != null && excludeColumnNames.Contains(column.HeaderText))
{
column.Visible = false;
}
}
// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
/// <summary>
/// Replace any of the contained controls with literals
/// </summary>
/// <param name="control"></param>
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}
if (current.HasControls())
{
PrepareControlForExport(current);
}
}
}
}
答
首次进口COM库Microsoft Excel对象
示例代码:
Public Sub exportOfficePCandWorkstation(ByRef mainForm As Form1, ByVal Location As String, ByVal WorksheetName As String)
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim Header(23) As String
Dim HeaderCell(23) As String
Header = {"No.", "PC Name", "User", "E-mail", "Department/Location", "CPU Model", "CPU Processor", "CPU Speed", "CPU HDD#1", "CPU HDD#2", "CPU Memory", "CPU OS", "CPU Asset Tag", "CPU MAC Address", "Monitor 1 Model", "Monitor Serial Number", "Monitor2 Model", "Monitor2 Serial Number", "Office", "Wi-LAN", "KVM Switch", "Attachment", "Remarks", "Date and Time"}
HeaderCell = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"}
xlWorkBook = xlApp.Workbooks.Add
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
xlWorkSheet.Name = WorksheetName
xlApp.Visible = True
xlWorkSheet.Application.ActiveWindow.SplitRow = 1
xlWorkSheet.Application.ActiveWindow.SplitColumn = 3
xlWorkSheet.Application.ActiveWindow.FreezePanes = True
With xlWorkSheet
For count As Integer = 0 To 23
.Range(HeaderCell(count) & 1).Value = Header(count)
Next
With .Range("A1:X1")
.Interior.Color = 1
With .Font
.Size = 16
.ColorIndex = 2
.Name = "Times New Roman"
End With
End With
For i = 0 To mainForm.DataGridView1.RowCount - 1
For j = 0 To mainForm.DataGridView1.ColumnCount - 1
If mainForm.DataGridView1(j, i).Value.ToString = "System.Byte[]" Then
xlWorkSheet.Cells(i + 2, j + 2) = "Attached"
Else
xlWorkSheet.Cells(i + 2, j + 2) = mainForm.DataGridView1(j, i).Value.ToString()
End If
Next
.Range("A" & i + 2).Value = (i + 1).ToString
Next
With .Range("A:Z")
.EntireColumn.AutoFit()
End With
With .Range("B2:X" & mainForm.DataGridView1.RowCount + 1)
.HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify
End With
With .Range("A1:A" & mainForm.DataGridView1.RowCount + 1)
.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
'-----------------------------------Insert Border Lines--------------------------------------
With .Range("A1:X" & mainForm.DataGridView1.RowCount + 1)
With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
End With
End With
xlWorkSheet.SaveAs(Location)
xlWorkBook.Close()
xlApp.Quit()
MsgBox("Export Record successful", MsgBoxStyle.Information, "Export to Excel")
End Sub
我使用SaveFileDialog中的具体位置,以创建Excel
我认为你的代码被消毒了或者什么的,因为它没有意义。 – 2008-09-24 12:34:44