如何将datagrid导出到excel

本文介绍了如何将datagrid导出到excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 大家好 我能够在VB.net桌面应用程序中运行将DataGridView导出为ex​​cel的代码。现在，我想在使用DataGrid的WPF桌面应用程序中执行此操作。 我正在使用此代码：Hi allI was able to run code for exporting DataGridView to excel in VB.net desktop application. Now, I would like to do this in WPF desktop application with DataGrid.I'm using this code:Dim xlApp As Excel.Application = New Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), Excel.Worksheet) 'xlApp.Visible = True Dim headers = (From ch In GeneralReport.Columns _ Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _ Select header.Value).ToArray() Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString) Dim items() = (From r In GeneralReport.Rows _ Let row = DirectCast(r, DataGridViewRow) _ Where Not row.IsNewRow _ Select (From cell In row.Cells _ Let c = DirectCast(cell, DataGridViewCell) _ Select c.Value).ToArray()).ToArray() Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine For Each a In items Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString) table &= String.Join(vbTab, t) & Environment.NewLine Next table = table.TrimEnd(CChar(Environment.NewLine)) Clipboard.SetText(table) Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray Dim range As Excel.Range = xlWorkSheet.Range("A1:" & alphabet(headerText.Length - 1) & (items.Length + 1).ToString) Try xlWorkSheet.Columns.AutoFit() range.Select() 'range.Copy() xlWorkSheet.Paste() Catch ex As Exception MessageBox.Show("Exception " + ex.Message) Finally GC.Collect() End Try range.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone With range.Borders(Excel.XlBordersIndex.xlEdgeLeft) .LineStyle = Excel.XlLineStyle.xlContinuous .ColorIndex = 24 'black .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlThick End With With range.Borders(Excel.XlBordersIndex.xlEdgeTop) .LineStyle = Excel.XlLineStyle.xlContinuous .ColorIndex = 24 'black .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlMedium End With With range.Borders(Excel.XlBordersIndex.xlEdgeBottom) .LineStyle = Excel.XlLineStyle.xlContinuous .ColorIndex = 24 'black .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlMedium End With With range.Borders(Excel.XlBordersIndex.xlEdgeRight) .LineStyle = Excel.XlLineStyle.xlContinuous .ColorIndex = 24 'black .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlMedium End With With range.Borders(Excel.XlBordersIndex.xlInsideVertical) .LineStyle = Excel.XlLineStyle.xlContinuous .ColorIndex = 24 'black .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlThin End With With range.Borders(Excel.XlBordersIndex.xlInsideHorizontal) .LineStyle = Excel.XlLineStyle.xlContinuous .ColorIndex = 24 'black .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlThin End With 'With range.Interior ' .Pattern = Excel.XlPattern.xlPatternLinearGradient ' .Gradient.Degree = 60 ' .Gradient.ColorStops.Clear() ' With .Gradient.ColorStops.Add(0) ' .ThemeColor = Excel.XlThemeColor.xlThemeColorAccent1 ' .TintAndShade = 0 ' End With 'End With Dim arry As Object(,) arry = range.Value For r As Integer = 1 To arry.GetUpperBound(0) For c As Integer = 1 To arry.GetUpperBound(1) Dim myRange As Object = arry(r, c) Next c Next r Dim SourceRange As Excel.Range = DirectCast(xlWorkSheet.UsedRange, Excel.Range) FormatAsTable(SourceRange, "Table1", "TableStyleMedium2") 'range.Select() 'range.ListObjects("Table1").TableStyle = "TableStyleLight2" If Not Directory.Exists("C:\Timer Tool Reports\") Then Directory.CreateDirectory("C:\Timer Tool Reports\") End If Dim dat As String = dtReport.Value.ToString("dd-MM-yyyy") Try xlWorkBook.SaveAs("C:\Timer Tool Reports\" & pathN & "_" & dat & ".xlsx") 'save our workbook MsgBox("You can find the file C:\Timer Tool Reports\" & pathN & ".xlsx") Catch ex As Exception MessageBox.Show("Exception " + ex.Message) Finally GC.Collect() End Try 'releasing object references xlWorkBook = Nothing xlWorkBook = Nothing xlApp.Quit() xlApp = Nothing releaseObject(xlApp) releaseObject(xlWorkBook) releaseObject(xlWorkSheet) Clipboard.Clear() Dim proc As System.Diagnostics.Process For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL") proc.Kill() Next End IfPublic Sub FormatAsTable(SourceRange As Excel.Range, TableName As String, TableStyleName As String) SourceRange.Worksheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, SourceRange, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing).Name = TableName SourceRange.[Select]() SourceRange.Worksheet.ListObjects(TableName).TableStyle = TableStyleName End Sub 是否可以在WPF中使用此代码？当我试图实现它时，我收到错误：Is it possible to use this code in WPF? When I'm trying to implement it I get an error in:Dim headers = (From ch In GeneralReport.Columns _ Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _ Select header.Value).ToArray()Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)Dim items() = (From r In GeneralReport.Rows _ Let row = DirectCast(r, DataGridViewRow) _ Where Not row.IsNewRow _ Select (From cell In row.Cells _ Let c = DirectCast(cell, DataGridViewCell) _ Select c.Value).ToArray()).ToArray() 我不知道如何更换不受支持的会员。 感谢您的支持。I have no idea how to change unsupported members.Thank you for your support.推荐答案Imports System.Collections 和 IQuerable [ ^ ]and IQuerable[^]Imports System.Linq 有关名称空间的更多信息，请参阅：导入语句（.NET命名空间和类型） [ ^ ]。强烈建议使用添加引用对话框添加（或删除）引用 [ ^ ] - 如果你是初学者。 最后，我建议阅读这个 [ ^ ]之前你走得更远。按照浏览器窗口左侧的链接。 并且......如果你不知道它在做什么，请不要使用别人的代码。For further information about namespaces, please see: Imports Statement (.NET Namespace and Type)[^]. It is strongly recommended to Add (or Remove) References By Using the Add Reference Dialog Box[^] - if you're a beginner.Finally, i'd suggest to read this[^] before you step further. Follow the links on the left side of browser's window.And... please, do not use someone else's code if you don't know what it's doing. 这篇关于如何将datagrid导出到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！