visual read net manipular insertar guardar escribir desde datos vb.net excel

vb.net - read - vb net excel interop



¿Cómo guardo una cuadrícula de datos para sobresalir en vb.net? (9)

¿Tiene que ser un archivo XLS nativo? Su mejor opción probablemente sea solo exportar los datos a un archivo CSV, que es texto plano y razonablemente fácil de generar. Los CSV se abren en Excel de forma predeterminada para la mayoría de los usuarios, por lo que no sabrán la diferencia.

Sé que esto debería ser fácil, pero ¿cómo puedo exportar / guardar un DataGridView para Excel?


Aquí tenemos un código que usamos para hacerlo en muchas de nuestras aplicaciones. Tenemos un método especial para limpiar la columna "no exportable". Además, no exportamos cols sin encabezados, pero puede ajustar esa lógica a sus necesidades.

Editar: El formateador de código no adora vb.net; puede copiar / pegar en Visual Studio y estará bien.

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


Pruébelo, es un toque más simple que Brendans, pero no como "rico en funciones":

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


Podría usar cristal ya que está integrado en VS. Predefina un informe de cristal con las columnas apropiadas y luego puede usar cualquier fuente de datos que use para una cuadrícula de datos o una vista de cuadrícula.

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")


Advertiría de nuevo haciendo un ciclo doble para extraer los datos de cada celda de datos y escribirlos individualmente en una celda de Excel. En su lugar, use una matriz de objetos 2D y recorra su cuadrícula de datos guardando allí todos sus datos. Luego podrá establecer un rango de Excel igual a esa matriz de objetos 2D.

Esto será varios órdenes de magnitud más rápido que escribir Excel celda por celda. Algunos informes en los que he estado trabajando que solían tomar dos horas simplemente para exportar se han reducido a menos de un minuto.


Configuré el gridview y luego usé el objeto html text writer para escupirlo en un archivo .xls, así:

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



Uso esto todo el tiempo:

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); } } } }


Primera biblioteca COM Importar Objeto Microsoft Excel

Código de muestra:

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

Uso SaveFileDialog para crear Excel en la ubicación específica