como puedo exportar informacion de un datagridview realizado en c#.net a excel,ya eh estado copiando y pegando codigo pero no me sale......!!
alguien me puede decir como hacerlo??? es en c#.net 2005
saludos.......
| ||||
Respuesta: exportar a excel hay 2 formas para el caso de winforms usando el motor de ado.net y haciendo select, insert o cualquier comando sql a una hora de excel en vez de tabla "c# winforms excel ado.net" y la otra es usnado Interop y las librerias propias de Excel (limitante en muchos casos por la seguridad de los ensamblados y que no todos los usuarios tienen la misma version de office) pero tienen que darle una vuelta a PIA si optan por la 2da opcion |
| |||
Respuesta: exportar a excel Hola, estuve investigando y supongo que a la primera forma que dices (con select e insert) es como sigue. Solo cambie un par de cosas que me daban error: 1 – cambie los &= por += (no se por que pero aveces me daban error y aveces no) 2 – cambie: Dim da As New OleDbDataAdapter("Select * From [" + SheetName + "$]", conn) Por Dim da As New OleDbDataAdapter("Select * From [" + SheetName + "]", conn) 3 – cambie: da.InsertCommand = New OleDbCommand("INSERT INTO [" + SheetName + "$] " + Inse…… por da.InsertCommand = New OleDbCommand("INSERT INTO [" + SheetName + "] " + Inse…… Bueno aquí esta el resultado y si me funciono así que aquí esta mi parte y espero les sirva (el código original no recuerdo de donde lo saque, si lo encuentro de nuevo les digo, de todas formas es fácil de encontrar fue de los primeros resultados de google)
Código:
Imports System.Data.OleDb Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim ds As DataSet = New DataSet("jose") ds.Tables.Add("t1") ds.Tables("t1").Columns.Add("c1") ds.Tables("t1").Columns.Add("c2") ds.Tables("t1").Columns.Add("c3") Dim r As DataRow r = ds.Tables("t1").NewRow r.Item(0) = "aa" r.Item(1) = "bb" r.Item(2) = "cc" ds.Tables("t1").Rows.Add(r) r = ds.Tables("t1").NewRow r.Item(0) = "11" r.Item(1) = "22" r.Item(2) = "33" ds.Tables("t1").Rows.Add(r) ExportExcel("C:\a.xls", ds) End Sub Sub ExportExcel(ByVal DestinationXLSFile As String, ByVal SourceDS As DataSet) 'reference site http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934 'Delete the old file If Dir(DestinationXLSFile) <> "" Then Kill(DestinationXLSFile) 'Create the new File Dim Conn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DestinationXLSFile + ";Extended Properties=""Excel 8.0;HDR=YES""" Dim conn As New OleDbConnection conn.ConnectionString = Conn1 conn.Open() Dim cmd1 As New OleDbCommand cmd1.Connection = conn 'Create The Sheets Dim SheetName As String Dim CreateTablestring As String = "" Dim InsertString1 As String = "" Dim InsertString2 As String = "" 'Dim UpdateString As String = "" Dim ExcelDS As New DataSet Dim DataType As String Dim ColName As String = "" Dim dr, dr2 As DataRow For i As Integer = 0 To SourceDS.Tables.Count - 1 SheetName = SourceDS.Tables(i).TableName CreateTablestring = " (" InsertString1 = "(" InsertString2 = "(" 'UpdateString = "" For C As Integer = 0 To SourceDS.Tables(i).Columns.Count - 1 ColName = SourceDS.Tables(i).Columns(C).ColumnName 'Createtablestring DataType = SourceDS.Tables(i).Columns(C).DataType.ToString Select Case DataType Case "System.String" CreateTablestring += ColName + " char(255)" Case "System.Int32" CreateTablestring += ColName + " int" Case Else 'todo CreateTablestring += ColName + " char(255)" End Select InsertString1 += ColName InsertString2 += "?" 'UpdateString += ColName + " = ?" If C <> SourceDS.Tables(i).Columns.Count - 1 Then CreateTablestring += ", " InsertString1 += ", " InsertString2 += ", " 'UpdateString += ", " Else CreateTablestring += ")" InsertString1 += ")" InsertString2 += ")" End If Next cmd1.CommandText = "CREATE TABLE " + SheetName + CreateTablestring cmd1.ExecuteNonQuery() Dim da As New OleDbDataAdapter("Select * From [" + SheetName + "]", conn) da.Fill(ExcelDS, SheetName) 'Only need the INSERT command as deleted records will be ignored da.InsertCommand = New OleDbCommand("INSERT INTO [" + SheetName + "] " + InsertString1 + " VALUES " + InsertString2, conn) 'da.UpdateCommand = New OleDbCommand("UPDATE [" + SheetName + "$] SET " + UpdateString, conn) For C As Integer = 0 To SourceDS.Tables(i).Columns.Count - 1 ColName = SourceDS.Tables(i).Columns(C).ColumnName DataType = SourceDS.Tables(i).Columns(C).DataType.ToString Select Case DataType Case "System.String" da.InsertCommand.Parameters.Add("@" + ColName, OleDbType.VarChar, 255, ColName) Case "System.Int32" da.InsertCommand.Parameters.Add("@" + ColName, OleDbType.Integer, 7, ColName) Case Else 'todo da.InsertCommand.Parameters.Add("@" + ColName, OleDbType.VarChar, 255, ColName) End Select 'da.UpdateCommand.Parameters.Add("@" + ColName, OleDbType.VarChar, 255, ColName) Next For Each dr In SourceDS.Tables(i).Rows 'todo why does this not work 'ExcelDS.Tables(i).ImportRow(dr) 'Replace with this dr2 = ExcelDS.Tables(i).NewRow For it As Integer = 0 To ExcelDS.Tables(i).Columns.Count - 1 dr2.Item(it) = dr.Item(it) Next ExcelDS.Tables(i).Rows.Add(dr2) Next da.Update(ExcelDS, SheetName) Next conn.Close() End Sub End Class |
| ||||
![]() esta es la funcion que yo utilizo en c#.net 2005
Código:
using Excel = Microsoft.Office.Interop.Excel; public partial class Form1 : Form { private Excel._Application ApExcel; private Excel.Workbook Libro; private Excel.Worksheet Hoja1; private object opc = Type.Missing; private void button2_Click(object sender, EventArgs e) { ApExcel = new Excel.Application(); ApExcel.Visible = true; Libro = ApExcel.Workbooks.Add(opc); Hoja1 = (Excel.Worksheet)Libro.Sheets[1]; Excel.Range rango; rango = (Excel.Range)Hoja1.Columns["A", opc]; Hoja1.Cells[1, 1] = "HORA"; rango = (Excel.Range)Hoja1.Columns["B", opc]; Hoja1.Cells[1, 2] = "TIPO_ACCESO"; rango = (Excel.Range)Hoja1.Columns["C", opc]; Hoja1.Cells[1, 3] = "RUTA"; rango = (Excel.Range)Hoja1.Columns["D", opc]; Hoja1.Cells[1, 4] = "UNIDAD"; rango = (Excel.Range)Hoja1.Columns["E", opc]; Hoja1.Cells[1, 5] = "OPERADOR"; //Excel.ApplicationClass Libro1 = new Excel.ApplicationClass(); //ApExcel.Application.Workbooks.Add(Type.Missing); for (int i = 0; i < dataGridView1.Rows.Count; i++) { DataGridViewRow row = dataGridView1.Rows[i]; for (int j = 0; j < row.Cells.Count; j++) { try { ApExcel.Cells[i + 2, j + 1] = row.Cells[j].Value.ToString(); } catch (Exception eu) { } finally { } } } saludos............................ |