LazyDreamy

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:office:spreadsheet"">")

' 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:Cell>", _
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:Cell>", _
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 exportExcel2(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("<html>")
fs.WriteLine("<head></head>")
fs.WriteLine("<body>")

' Write the worksheet contents
fs.WriteLine(" <table>")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format("    <cols width=""{0}""/>", _
grdView.Columns.Item(i).Width))
Next
fs.WriteLine("    <tr>")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format("    <td>" & _
"{0}</td>", _
grdView.Columns.Item(i).HeaderText))
Next
fs.WriteLine("    </tr>")

' 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("    <tr 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("    <td>" & _
"{0}</td>", _
cellText.ToString))
Next
fs.WriteLine("    </tr>")
Next

' Close up the document
fs.WriteLine(" </table>")
fs.WriteLine("</body>")
fs.WriteLine("</html>")
fs.Close()

' Open the file in Microsoft Excel
' 10 = SW_SHOWDEFAULT
ShellEx(Me.Handle, "Open", myFile, "", "", 10)
End Sub

Excel 로 내보내는 방법 중 가장 간단한 방법은 COM(Microsoft.Office.Interop.Excel.Application)을 이용하는 방법이다. 하지만 임의로 제어해야 할 필요가 있다면. HTML 이나 XML 을 사용한 도큐멘트를 제작해도 Excel 은 인식이 가능하다.

위의 방법은 파일로 출력하는 방식. 다만 Excel 2007 기준에서 에러메시지를 하나 뱉는 경향이 있었다. (뭐.. 궁할때 쓰자;;)

태그 DataGrid, VB, VB.NET

Private Function GetSelected(ByVal dataGridView As DataGridView) As List(Of DataRow)
Dim list As New List(Of DataRow)()
'If the dataGridView selected rows are null
If dataGridView.SelectedRows Is Nothing OrElse dataGridView.SelectedRows.Count = 0 Then
'Return list
Return list
End If
For Each dataGridViewRow As DataGridViewRow In dataGridView.SelectedRows
'Declare a null DataRow
Dim dataRow As DataRow = Nothing
Try
'Declare a DataRowView(DataRowView)
Dim dataRowView As DataRowView = DirectCast(dataGridViewRow.DataBoundItem, DataRowView)
dataRow = dataRowView.Row
Catch ex As Exception
'Catch the exception
'Write the error message
System.Diagnostics.Debug.WriteLine("Error: " + ex.Message)
End Try
'If the row is null
If dataRow Is Nothing Then
'Continue
Continue For
End If
'Add a row(DataRow) to list
list.Add(dataRow)
Next
'Return list
Return list
End Function