C# dataGridView导出EXcel

最新推荐文章于 2024-11-27 10:46:56 发布

原创最新推荐文章于 2024-11-27 10:46:56 发布 · 3.8k 阅读

5 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#c# #excel #datagridview

C# 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一种使用C#将dataGridView中的数据导出为Excel文件的方法。首先，通过创建ExportToExcel类并调用OutputAsExcelFile方法实现数据转换，然后将dataGridView的可见列转化为dataTable。接着，利用Microsoft.Office.Interop.Excel库创建Excel应用，填充数据到工作表，并调整样式和列宽。最后，保存文件并释放资源。

1、调用

/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonExport_Click(object sender, EventArgs e)
{
ExportToExcel d = new ExportToExcel();

d.OutputAsExcelFile(dataGridView1);
}

2、引用

3。创建类

using System;

using System.Collections;

using System.Text;

using System.Data;

using System.Threading;

using System.Windows.Forms;

using System.Collections.Generic;

using Microsoft.Office.Core;

using Excel = Microsoft.Office.Interop.Excel;

namespace 铸造数据分析与发送系统
{

public class ExportToExcel

{

public Excel.Application m_xlApp = null;

public void OutputAsExcelFile(DataGridView dataGridView)

{

if (dataGridView.Rows.Count <= 0)

{

MessageBox.Show("无数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return;

}

string filePath = "";

SaveFileDialog s = new SaveFileDialog();

s.Title = "保存Excel文件";

s.Filter = "Excel文件(*.xls)|*.xls";

s.FilterIndex = 1;

if (s.ShowDialog() == DialogResult.OK)

filePath = s.FileName;

else

return;

//第一步：将dataGridView转化为dataTable,这样可以过滤掉dataGridView中的隐藏列

DataTable tmpDataTable = new DataTable("tmpDataTable");

DataTable modelTable = new DataTable("ModelTable");

for (int column = 0; column < dataGridView.Columns.Count; column++)

{

if (dataGridView.Columns[column].Visible == true)

{

DataColumn tempColumn = new DataColumn(dataGridView.Columns[column].HeaderText, typeof(string));

tmpDataTable.Columns.Add(tempColumn);

DataColumn modelColumn = new DataColumn(dataGridView.Columns[column].Name, typeof(string));

modelTable.Columns.Add(modelColumn);

}

for (int row = 0; row < dataGridView.Rows.Count; row++)

{

if (dataGridView.Rows[row].Visible == false)

continue;

DataRow tempRow = tmpDataTable.NewRow();

for (int i = 0; i < tmpDataTable.Columns.Count; i++)

tempRow[i] = dataGridView.Rows[row].Cells[modelTable.Columns[i].ColumnName].Value;

tmpDataTable.Rows.Add(tempRow);

}

if (tmpDataTable == null)

{

return;

}

//第二步：导出dataTable到Excel

long rowNum = tmpDataTable.Rows.Count;//行数

int columnNum = tmpDataTable.Columns.Count;//列数

Excel.Application m_xlApp = new Excel.Application();

m_xlApp.DisplayAlerts = false;//不显示更改提示

m_xlApp.Visible = false;

Excel.Workbooks workbooks = m_xlApp.Workbooks;

Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

try

{

string[,] datas = new string[rowNum + 1, columnNum];

for (int i = 0; i < columnNum; i++) //写入字段

datas[0, i] = tmpDataTable.Columns[i].Caption;

//Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);

Excel.Range range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];

range.Interior.ColorIndex = 15;//15代表灰色

range.Font.Bold = true;

range.Font.Size = 10;

int r = 0;

for (r = 0; r < rowNum; r++)

{

for (int i = 0; i < columnNum; i++)

{

object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];

datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式

}

System.Windows.Forms.Application.DoEvents();

//添加进度条

}

//Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);

Excel.Range fchR = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];

fchR.Value2 = datas;

worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

//worksheet.Name = "dd";

//m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;

m_xlApp.Visible = false;

// = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);

range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];

//range.Interior.ColorIndex = 15;//15代表灰色

range.Font.Size = 9;

range.RowHeight = 14.25;

range.Borders.LineStyle = 1;

range.HorizontalAlignment = 1;

workbook.Saved = true;

workbook.SaveCopyAs(filePath);

}

catch (Exception ex)

{

MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);

}

finally

{

EndReport();

}

m_xlApp.Workbooks.Close();

m_xlApp.Workbooks.Application.Quit();

m_xlApp.Application.Quit();

m_xlApp.Quit();

MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

private void EndReport()

{

object missing = System.Reflection.Missing.Value;

try

{

//m_xlApp.Workbooks.Close();

//m_xlApp.Workbooks.Application.Quit();

//m_xlApp.Application.Quit();

//m_xlApp.Quit();

}

catch { }

finally

{

try

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);

m_xlApp = null;

}

catch { }

try

{

//清理垃圾进程

this.killProcessThread();

}

catch { }

GC.Collect();

}

private void killProcessThread()

{

ArrayList myProcess = new ArrayList();

for (int i = 0; i < myProcess.Count; i++)

{

try

{

System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();

}

catch { }

}

}
}