本文共 17735 字，大约阅读时间需要 59 分钟。
一．首先，项目中引入 excel.dll
二．代码： １. datagridview to excelusing System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using System.ComponentModel;namespace DocumentSearch{ class ExcelHelper { //实现思路：锁定 DataGridView（以防止导出过程中用户重新对datagridview排序等等）->逐行获取DataGridView的cells的值（cell用 tab隔开，行用 NewLine -->将cells值串装入 dictionary(多个DataGridView对应) ）,中途可以用进度条显示进度或取消--> 复制到剪贴板Clipboard.SetDataObject(dictionary)-->粘贴到excel worksheet(复制＼粘贴方法要比逐行循环插入速度要快很多)． // //get the datagridview data to text content. use "tab" split the field value ,"newline" to split the datarow // // // // DataGridToExcelContent(DataGridView[] dgvArr, BackgroundWorker bgWorker) { Dictionary rtnDic = new Dictionary (); int rowTol=0,rowRead=0,percent=0; foreach (DataGridView dgv in dgvArr) { dgv.Invoke(new Action(() => { dgv.Enabled = false; rowTol = rowTol + dgv.Rows.Count; })); } foreach (DataGridView dgv in dgvArr) { if (dgv.Rows.Count < 1) return rtnDic; string dgvContent = ""; //Title of column name //Add column title for (int i = 0; i <= dgv.Columns.Count - 1; i++) { dgvContent = dgvContent + (i == 0 ? "" : "\t") + dgv.Columns[i].HeaderText; } dgvContent = dgvContent + Environment.NewLine; //Content for (int r = 0; r <= dgv.Rows.Count - 1; r++) { if (bgWorker != null && (bgWorker as BackgroundWorker).CancellationPending) { //如果有取消則退出導出 dgv.Invoke(new Action(() => { foreach (DataGridView dgvInvoke in dgvArr) { dgvInvoke.Enabled = true; } })); return rtnDic; } for (int c = 0; c <= dgv.Columns.Count - 1; c++) { dgvContent = dgvContent + (c == 0 ? "" : "\t") + Convert.ToString(dgv.Rows[r].Cells[c].Value); } rowRead = rowRead + 1; percent = (100 * rowRead) / rowTol; if (bgWorker != null && percent <= 100) { (bgWorker as BackgroundWorker).ReportProgress(percent); } dgvContent = dgvContent + Environment .NewLine ; } rtnDic.Add(dgv.Name, dgvContent); dgv.Invoke(new Action(() => { dgv.Enabled = true; })); } return rtnDic; } /// /// export the Dictionary text to excel public void ExportExcel(Dictionary dic) { if (dic == null && dic.Count == 0) return; try { var xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) return; //Dim CurrentCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture() System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); var workbooks = xlApp.Workbooks; // Microsoft.Office.Interop.Excel.Workbooks var workbook = workbooks.Add();// Microsoft.Office.Interop.Excel.Workbook foreach (var d in dic) { var worksheet = workbook.Worksheets.Add();// Microsoft.Office.Interop.Excel.Worksheet worksheet.Name = d.Key; if (worksheet == null) continue; Clipboard.SetDataObject(d.Value); worksheet.Paste(); worksheet.Cells.Font.Size = 10; worksheet.Cells.Borders.LineStyle = 1; worksheet.Columns.AutoFit(); //列寬自適應 } xlApp.Visible = true; } catch (Exception ex) { throw ex; } } }} VB:
'''''' get the datagridview data to text content. use "tab" split the field value ,"newline" to split the datarow ''' ''' ''' '''Public Function DataGridToExcelContent(ByVal dgvArr As DataGridView(), ByVal bgWorker As BackgroundWorker) As Dictionary(Of String, String) Dim rtnDic = New Dictionary(Of String, String) Dim rowTol As Integer 'get the data's rows count Dim rowRead As Integer Dim percent As Integer For Each dgv In dgvArr dgv.Invoke(New Action(Sub() dgv.Enabled = False End Sub)) rowTol = rowTol + dgv.Rows.Count Next For Each dgv In dgvArr If dgv.Rows.Count < 1 Then Return rtnDic End If Dim dgvContent = "" 'Title of column name 'Add column title For i = 0 To dgv.Columns.OfType(Of DataGridViewColumn).Count - 1 dgvContent = dgvContent + IIf(i = 0, "", vbTab) + dgv.Columns(i).HeaderText Next dgvContent = dgvContent + vbNewLine 'Content For r = 0 To dgv.Rows.OfType(Of DataGridViewRow).Count - 1 If bgWorker IsNot Nothing And CType(bgWorker, BackgroundWorker).CancellationPending Then '//如果有取消則退出導出 dgv.Invoke(New Action(Sub() For Each dgvInvoke In dgvArr dgvInvoke.Enabled = True Next End Sub)) Return rtnDic End If For c = 0 To dgv.Columns.OfType(Of DataGridViewColumn).Count - 1 dgvContent = dgvContent + IIf(c = 0, "", vbTab) + dgv.Rows(r).Cells(c).Value.ToString() Next rowRead = rowRead + 1 percent = (100 * rowRead) / rowTol If bgWorker IsNot Nothing And percent <= 100 Then CType(bgWorker, BackgroundWorker).ReportProgress(percent) End If dgvContent = dgvContent + vbNewLine Next rtnDic.Add(dgv.Name, dgvContent) dgv.Invoke(New Action(Sub() dgv.Enabled = True End Sub)) Next Return rtnDic End Function '/// '/// export the datagridview to excel '/// '/// the datagridviews will be exported to excel '/// set the odd And event rows have different color. '/// the name of BackgroundWorker. yes '/// Public Sub ExportExcel(ByVal dic As Dictionary(Of String, String)) If dic Is Nothing Or dic.Count = 0 Then Return End If Try Dim xlApp = New Microsoft.Office.Interop.Excel.Application() If (xlApp Is Nothing) Then Return End If Dim CurrentCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture() System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US") Dim workbooks As Microsoft.Office.Interop.Excel.Workbooks = xlApp.Workbooks Dim workbook As Microsoft.Office.Interop.Excel.Workbook = workbooks.Add() For Each d In dic Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets.Add() worksheet.Name = d.Key If worksheet Is Nothing Then Continue For End If Clipboard.SetDataObject(d.Value) worksheet.Paste() worksheet.Cells.Font.Size = 10 worksheet.Cells.Borders.LineStyle = 1 worksheet.Columns.AutoFit() ' //列寬自適應 Next xlApp.Visible = True Catch e As Exception Throw e End Try End Sub
C#:
//------------------------------------------------- class ExportToExcel{ /// /// export the dataset to excel /// /// the dataset will be exported to excel /// set the odd and event rows have different color. /// the name of BackgroundWorker. yes /// sheets 's name /// public void ExportExcel(DataSet ds, bool oddEvenColor, object bgWorker,string[] tableName) { if (ds == null || ds.Tables .Count == 0) return; try { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { return; } System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(); int rowRead = 0; int rowCount = 0; //get the table's rows count foreach (DataTable dt in ds.Tables) { rowCount += dt.Rows.Count; } for (int tbi = 0; tbi < ds.Tables.Count; tbi++) { Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet) workbook.Worksheets.Add(); worksheet.Name = tableName[tbi].ToString(); if (worksheet == null)//工作薄中没有工作表 { return; } Microsoft.Office.Interop.Excel.Range range; int percent = 0; //title column name for (int i = 0; i < ds.Tables[tbi].Columns.Count; i++) { worksheet.Cells[1, i + 1] = ds.Tables[tbi].Columns[i].ColumnName; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; range.Interior.ColorIndex = 31; range.Font.Size = 12; range.Font.Bold = true; range.Borders.LineStyle = 1; range.Font.ColorIndex = 2;//white } //content for (int r = 0; r < ds.Tables[tbi].Rows.Count; r++) { if (bgWorker != null && (bgWorker as BackgroundWorker).CancellationPending) //if (frmMain.form1.bgWorker.CancellationPending)//如果有取消則退出導出 break; for (int i = 0; i < ds.Tables[tbi].Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = ds.Tables[tbi].Rows[r][i].ToString(); } rowRead++; percent = ((int)(100 * rowRead)) / rowCount; if (bgWorker != null && percent <= 100) { (bgWorker as BackgroundWorker).ReportProgress(percent); //frmMain.form1.bgWorker.ReportProgress(percent); } } range = worksheet.Range(worksheet.Cells［2, 1］, worksheet.Cells［ds.Tables［tbi］.Rows.Count + 1, ds.Tables［tbi］.Columns.Count］) range.Borders.LineStyle = 1; if (oddEvenColor) { if (r % 2 == 1) range.Interior.ColorIndex = 34;//隔行換底色 } range.Font.Size = 9; worksheet.Columns.AutoFit(); //列寬自適應 } xlApp.Visible = true; } catch (Exception e) { throw (e); } } VB:
'///'/// export the dataset to excel '/// '/// the dataset will be exported to excel '/// set the odd And event rows have different color. '/// the name of BackgroundWorker. yes '/// Public Sub ExportExcel(ByVal ds As DataSet, ByVal oddEvenColor As Boolean, ByVal bgWorker As Object) If ds Is Nothing Or ds.Tables.Count = 0 Then Return End If Try Dim xlApp = New Microsoft.Office.Interop.Excel.Application() If (xlApp Is Nothing) Then Return End If Dim CurrentCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture() System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US") Dim workbooks As Microsoft.Office.Interop.Excel.Workbooks = xlApp.Workbooks Dim workbook As Microsoft.Office.Interop.Excel.Workbook = workbooks.Add() Dim rowRead As Integer = 0 Dim cbRowRead As ConcurrentBag(Of Integer) = New ConcurrentBag(Of Integer) cbRowRead.Add(0) Dim rowCount As Integer = 0 'get the table's rows count rowCount = ds.Tables.OfType(Of DataTable).AsEnumerable().Sum(Function(o) o.Rows.Count) Dim listT As List(Of Task) = New List(Of Task)() For Each tb In ds.Tables.OfType(Of DataTable) listT.Add(Task.Run(Sub() Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets.Add() worksheet.Name = tb.ToString() If worksheet Is Nothing Then Return 'Continue For End If Dim Range As Microsoft.Office.Interop.Excel.Range Dim percent As Integer 'Title of column name For i = 0 To tb.Columns.Count - 1 worksheet.Cells(1, i + 1) = tb.Columns(i).ColumnName Range = worksheet.Cells(1, i + 1) Range.Interior.ColorIndex = 31 Range.Font.Size = 12 Range.Font.Bold = True Range.Borders.LineStyle = 1 Range.Font.ColorIndex = 2 Next 'Content For r As Integer = 0 To tb.Rows.Count - 1 If bgWorker IsNot Nothing And CType(bgWorker, BackgroundWorker).CancellationPending Then '//如果有取消則退出導出 Return End If For i = 0 To tb.Columns.Count - 1 worksheet.Cells(r + 2, i + 1) = tb.Rows(r)(i).ToString() Next Dim lastItemInCb As Integer cbRowRead.TryTake(lastItemInCb) lastItemInCb = lastItemInCb + 1 cbRowRead.Add(lastItemInCb) ' rowRead = rowRead + 1 percent = (100 * lastItemInCb) / rowCount 'percent = (100 * rowRead) / rowCount If bgWorker IsNot Nothing And percent <= 100 Then CType(bgWorker, BackgroundWorker).ReportProgress(percent) End If Next Range = worksheet.Range(worksheet.Cells(2, 1), worksheet.Cells(tb.Rows.Count + 1, tb.Columns.Count)) Range.Borders.LineStyle = 1 Range.Font.Size = 9 worksheet.Columns.AutoFit() ' //列寬自適應 End Sub)) Next Task.WaitAll(listT.ToArray()) xlApp.Visible = True Catch e As Exception Throw e End Try End Sub
转载地址：http://ssmdi.baihongyu.com/