如何将datagridview数据导出到excel?
我的导出代码
此代码在excel中写入数据,但内容适合单元格。 他们分散了。
public static void export_to_excel(DataGridView dgv, string excel_file) { int cols; //open file StreamWriter wr = new StreamWriter(excel_file); cols = dgv.Columns.Count; for (int i = 0; i < cols; i++) { wr.Write(dgv.Columns[i].HeaderText.ToString().ToUpper() + "t"); } wr.WriteLine(); //write rows to excel file for (int i = 0; i < (dgv.Rows.Count - 1); i++) { for (int j = 0; j < cols; j++) { if (dgv.Rows[i].Cells[j].Value != null) wr.Write(dgv.Rows[i].Cells[j].Value + "t"); else { wr.Write("t"); } } wr.WriteLine(); } //close file wr.Close(); }
我在VB.net中已经这样做了,我用https://www.developerfusion.com/tools/convert/vb-to-csharp/将它转换为c#,所以你应该测试一下。
VB.net代码:
Imports System.Runtime.CompilerServices Imports Excel = Microsoft.Office.Interop.Excel Public Module ExcelMod _ Public Function ToExcel(ByVal grd As DataGridView, ByVal path As String, Optional ByRef exp As Exception = Nothing) As Boolean Dim res As Boolean = False exp = Nothing Dim xlApp As Excel.Application = Nothing Dim xlWorkBook As Excel.Workbook = Nothing Dim xlWorkSheet As Excel.Worksheet = Nothing Try Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture Dim misValue As Object = System.Reflection.Missing.Value Dim i As Integer Dim j As Integer xlApp = New Excel.ApplicationClass System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US") xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("sheet1") Dim lastCol As Integer = 0 Dim lastRow As Integer = 0 For j = 0 To grd.ColumnCount - 1 If grd.Columns(j).Visible Then xlWorkSheet.Columns(lastCol + 1).ColumnWidth = CInt(grd.Columns(j).Width / 10) xlWorkSheet.Cells(1, lastCol + 1) = grd.Columns(j).HeaderText lastCol += 1 End If Next lastRow = 0 For i = 0 To grd.RowCount - 1 lastCol = 0 For j = 0 To grd.ColumnCount - 1 If grd.Columns(j).Visible AndAlso grd.Rows(i).Visible Then If grd(j, i).FormattedValue <> Nothing Then _ xlWorkSheet.Cells(lastRow + 2, lastCol + 1) = grd(j, i).FormattedValue.ToString() lastCol += 1 End If Next If grd.Rows(i).Visible Then lastRow += 1 Next xlWorkSheet.SaveAs(path) xlWorkBook.Close() xlApp.Quit() System.Threading.Thread.CurrentThread.CurrentCulture = oldCI res = True Catch ex As Exception exp = ex Finally If xlApp IsNot Nothing Then releaseObject(xlApp) If xlWorkBook IsNot Nothing Then releaseObject(xlWorkBook) If xlWorkSheet IsNot Nothing Then releaseObject(xlWorkSheet) End Try Return res End Function Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub End Module
C#代码:
using Microsoft.VisualBasic; using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Diagnostics; using System.Runtime.CompilerServices; using Excel = Microsoft.Office.Interop.Excel; public static class ExcelMod { public static bool ToExcel(this DataGridView grd, string path, ref Exception exp = null) { bool res = false; exp = null; Excel.Application xlApp = null; Excel.Workbook xlWorkBook = null; Excel.Worksheet xlWorkSheet = null; try { System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; object misValue = System.Reflection.Missing.Value; int i = 0; int j = 0; xlApp = new Excel.ApplicationClass(); System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = xlWorkBook.Sheets("sheet1"); int lastCol = 0; int lastRow = 0; for (j = 0; j <= grd.ColumnCount - 1; j++) { if (grd.Columns(j).Visible) { xlWorkSheet.Columns(lastCol + 1).ColumnWidth = Convert.ToInt32(grd.Columns(j).Width / 10); xlWorkSheet.Cells(1, lastCol + 1) = grd.Columns(j).HeaderText; lastCol += 1; } } lastRow = 0; for (i = 0; i <= grd.RowCount - 1; i++) { lastCol = 0; for (j = 0; j <= grd.ColumnCount - 1; j++) { if (grd.Columns(j).Visible && grd.Rows(i).Visible) { if (grd(j, i).FormattedValue != null) xlWorkSheet.Cells(lastRow + 2, lastCol + 1) = grd(j, i).FormattedValue.ToString(); lastCol += 1; } } if (grd.Rows(i).Visible) lastRow += 1; } xlWorkSheet.SaveAs(path); xlWorkBook.Close(); xlApp.Quit(); System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; res = true; } catch (Exception ex) { exp = ex; } finally { if (xlApp != null) releaseObject(xlApp); if (xlWorkBook != null) releaseObject(xlWorkBook); if (xlWorkSheet != null) releaseObject(xlWorkSheet); } return res; } private static void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; } finally { GC.Collect(); } } }
分散的原因是你使用的是tab( t)分隔符,使用“,”分隔符。 仅对于导出值,应将文件导出为csv文件。 优点: – 不需要安装Excel。 – 无需COM互操作。 (顺便说一下,它使你的代码单线程,不能扩展到多个CPU /线程) – Excel可以直接读取csv文件。
出口代码:
上述就是C#学习教程:如何将datagridview数据导出到excel?分享的全部内容,如果对大家有所用处且需要了解更多关于C#学习教程,希望大家多多关注---计算机技术网(www.ctvol.com)!
public static void export_to_excelAsCsvFile(DataGridView dGV, string filename) { string separator = ","; StringBuilder stOutput = new StringBuilder(); // Export titles: StringBuilder sHeaders = new StringBuilder(); for (int j = 0; j < dGV.Columns.Count; j++) { sHeaders.Append(dGV.Columns[j].HeaderText); sHeaders.Append(separator); } stOutput.AppendLine(sHeaders.ToString()); // Export data. for (int i = 0; i < dGV.RowCount - 1; i++) { StringBuilder stLine = new StringBuilder(); for (int j = 0; j < dGV.ColumnCount; j++) { stLine.Append(Convert.ToString(dGV[j, i].Value)); stLine.Append(separator); } stOutput.AppendLine(stLine.ToString()); } File.WriteAllText(filename, stOutput.ToString()); }
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/cdevelopment/1002013.html