exportacion de multiples datagridview a excel
Publicado por pink_pink (10 intervenciones) el 10/12/2009 18:02:57
hola encontre este super codigo k me ayuda a exportar mi datagridview a excel pero solo es 1 y yo necesito exportar 4.
Private Declare Function ShellEx Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hWnd As Integer, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Integer) As Integer
Private Sub exportExcel(ByVal grdView As DataGridView, ByVal fileName As String, _
ByVal fileExtension As String, ByVal filePath As String)
' Choose the path, name, and extension for the Excel file
Dim myFile As String = filePath & "\" & fileName & fileExtension
' Open the file and write the headers
Dim fs As New IO.StreamWriter(myFile, False)
fs.WriteLine("<?xml version=""1.0""?>")
fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:off…
' Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>")
' Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" <ss:Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" " & _
"ss:WrapText=""1""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C0C0"" ss:Pattern=""Solid""/>")
fs.WriteLine(" </ss:Style>")
' Style for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""Center"" ss:WrapText=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")
' Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")
fs.WriteLine(" <ss:Table>")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" <ss:Column ss:Width=""{0}""/>", _
grdView.Columns.Item(i).Width))
Next
fs.WriteLine(" <ss:Row>")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" <ss:Cell ss:StyleID=""1"">" & _
"<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cel… _
grdView.Columns.Item(i).HeaderText))
Next
fs.WriteLine(" </ss:Row>")
' Check for an empty row at the end due to Adding allowed on the DataGridView
Dim subtractBy As Integer, cellText As String
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
' Write contents for each cell
For i As Integer = 0 To grdView.RowCount - subtractBy
fs.WriteLine(String.Format(" <ss:Row ss:Height=""{0}"">", _
grdView.Rows(i).Height))
For intCol As Integer = 0 To grdView.Columns.Count - 1
cellText = grdView.Item(intCol, i).Value
' Check for null cell and change it to empty to avoid error
If cellText = vbNullString Then cellText = ""
fs.WriteLine(String.Format(" <ss:Cell ss:StyleID=""2"">" & _
"<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cel… _
cellText.ToString))
Next
fs.WriteLine(" </ss:Row>")
Next
' Close up the document
fs.WriteLine(" </ss:Table>")
fs.WriteLine("</ss:Worksheet>")
fs.WriteLine("</ss:Workbook>")
fs.Close()
' Open the file in Microsoft Excel
' 10 = SW_SHOWDEFAULT
ShellEx(Me.Handle, "Open", myFile, "", "", 10)
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
' Call the export sub
SI PONGO LA SIGUIENTE SINTAXIS TODO SALE BIEN CON EL DGCRIAS LO EXPORTA PERFECTAMENTE -------->
exportExcel(dgcrias, "exportedData", ".xls", _
My.Computer.FileSystem.SpecialDirectorie…
PERO SI PONGO ESTOS 3 ME MARCA ERROR EN ESTA LÍNEA
Dim fs As New IO.StreamWriter(myFile, False)
ERROR: El proceso no puede obtener acceso al archivo 'C:\Documents and Settings\Administrador\Escritorio\exportedData.xls' porque está siendo utilizado en otro proceso.
A LA HORA DE EXPORTAR Y SOLO ME EXPORTA DGCRIAS, OJALA PUEDAN AYUDARME ----->
exportExcel(dgbaños, "exportedData", ".xls", _
My.Computer.FileSystem.SpecialDirectorie…
exportExcel(dgdesp, "exportedData", ".xls", _
My.Computer.FileSystem.SpecialDirectorie…
exportExcel(dgexa, "exportedData", ".xls", _
My.Computer.FileSystem.SpecialDirectorie…
End Sub
GRACIAS
Private Declare Function ShellEx Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hWnd As Integer, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Integer) As Integer
Private Sub exportExcel(ByVal grdView As DataGridView, ByVal fileName As String, _
ByVal fileExtension As String, ByVal filePath As String)
' Choose the path, name, and extension for the Excel file
Dim myFile As String = filePath & "\" & fileName & fileExtension
' Open the file and write the headers
Dim fs As New IO.StreamWriter(myFile, False)
fs.WriteLine("<?xml version=""1.0""?>")
fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:off…
' Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>")
' Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" <ss:Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" " & _
"ss:WrapText=""1""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C0C0"" ss:Pattern=""Solid""/>")
fs.WriteLine(" </ss:Style>")
' Style for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""Center"" ss:WrapText=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")
' Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")
fs.WriteLine(" <ss:Table>")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" <ss:Column ss:Width=""{0}""/>", _
grdView.Columns.Item(i).Width))
Next
fs.WriteLine(" <ss:Row>")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" <ss:Cell ss:StyleID=""1"">" & _
"<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cel… _
grdView.Columns.Item(i).HeaderText))
Next
fs.WriteLine(" </ss:Row>")
' Check for an empty row at the end due to Adding allowed on the DataGridView
Dim subtractBy As Integer, cellText As String
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
' Write contents for each cell
For i As Integer = 0 To grdView.RowCount - subtractBy
fs.WriteLine(String.Format(" <ss:Row ss:Height=""{0}"">", _
grdView.Rows(i).Height))
For intCol As Integer = 0 To grdView.Columns.Count - 1
cellText = grdView.Item(intCol, i).Value
' Check for null cell and change it to empty to avoid error
If cellText = vbNullString Then cellText = ""
fs.WriteLine(String.Format(" <ss:Cell ss:StyleID=""2"">" & _
"<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cel… _
cellText.ToString))
Next
fs.WriteLine(" </ss:Row>")
Next
' Close up the document
fs.WriteLine(" </ss:Table>")
fs.WriteLine("</ss:Worksheet>")
fs.WriteLine("</ss:Workbook>")
fs.Close()
' Open the file in Microsoft Excel
' 10 = SW_SHOWDEFAULT
ShellEx(Me.Handle, "Open", myFile, "", "", 10)
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
' Call the export sub
SI PONGO LA SIGUIENTE SINTAXIS TODO SALE BIEN CON EL DGCRIAS LO EXPORTA PERFECTAMENTE -------->
exportExcel(dgcrias, "exportedData", ".xls", _
My.Computer.FileSystem.SpecialDirectorie…
PERO SI PONGO ESTOS 3 ME MARCA ERROR EN ESTA LÍNEA
Dim fs As New IO.StreamWriter(myFile, False)
ERROR: El proceso no puede obtener acceso al archivo 'C:\Documents and Settings\Administrador\Escritorio\exportedData.xls' porque está siendo utilizado en otro proceso.
A LA HORA DE EXPORTAR Y SOLO ME EXPORTA DGCRIAS, OJALA PUEDAN AYUDARME ----->
exportExcel(dgbaños, "exportedData", ".xls", _
My.Computer.FileSystem.SpecialDirectorie…
exportExcel(dgdesp, "exportedData", ".xls", _
My.Computer.FileSystem.SpecialDirectorie…
exportExcel(dgexa, "exportedData", ".xls", _
My.Computer.FileSystem.SpecialDirectorie…
End Sub
GRACIAS
Valora esta pregunta


0