My<cod/>.net
Как сохранить datagrid в excel в vb.net?
Просмотров: 460
Ответов: 9
Я знаю, что это должно быть легко, но как мне экспортировать/сохранить DataGridView в excel?
Вы можете использовать эту библиотеку для более детального форматирования
http://www.carlosag.net/Tools/ExcelXmlWriter/
На этой странице есть примеры.
Я использую это все время:
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);
}
}
}
}
Я бы еще раз предупредил, что делаю двойной цикл for, чтобы вытащить данные каждой ячейки datacell и записать их отдельно в ячейку excel. Вместо этого используйте массив объектов 2D и циклически проходите через свой datagrid, сохраняя там все свои данные. Затем вы сможете установить диапазон excel, равный этому массиву объектов 2D.
Это будет на несколько порядков быстрее, чем писать excel ячейка за ячейкой. Некоторые отчеты, над которыми я работал раньше, занимали два часа просто для экспорта, были сокращены до менее чем минуты.
Я настроил gridview, а затем использовал объект html text writer, чтобы выплюнуть его в файл .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
Попробуйте это, это немного проще, чем Бренданы, но не так, как 'feature rich':
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. Предварительно определите отчет crystal с соответствующими столбцами, а затем вы можете использовать любой источник данных, который вы бы использовали для datagrid или gridview.
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")
Первый импорт 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 в определенном месте
Вот некоторый код, который мы используем для этого во многих наших приложениях. У нас есть специальный метод очистки столбца "not exportable". Кроме того, мы не экспортируем cols без заголовков, но вы можете настроить эту логику в соответствии с вашими потребностями.
Edit: форматер кода не любит 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