1.先去导入dll库：Microsoft.Office.Interop.Excel.dl
2.然后再去写代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
public class ExprotToExcel
{
public void DataToExcel(DataGridView dgv,ToolStripProgressBar tempProgressBar,ToolStripStatusLabel toolstrip)
{
if (dgv.Rows.Count == 0)
{
MessageBox.Show("无数据"); return;
}
MessageBox.Show("开始生成要导出的数据", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = false;
for (int i = 0; i < dgv.ColumnCount; i++)
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
tempProgressBar.Visible = true;
tempProgressBar.Minimum = 1;
tempProgressBar.Maximum = dgv.RowCount;
tempProgressBar.Step = 1;
toolstrip.Visible = true;
for (int i = 0; i < dgv.RowCount; i++)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
}
}
toolstrip.Text = "|| 状态：正在生成第 "+i+"/"+dgv.RowCount+" 个";
tempProgressBar.Value = i + 1;
}
toolstrip.Text = "|| 状态：生成成功！";
MessageBox.Show("生成成功，请保存。","生成提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
excel.Visible = true;
}
}
3.打开后，效果如下：
using System.Data;
using System.Windows.Forms;
namespace Hotel.DAL
{
public static class HelperOffice
{
public const string CHECKNAME = "IsCheckedToGrid";
/// <summary>
/// 导出EXCEL
/// </summary>
/// <param name="tb">需要导出的数据表</param>
/// <param name="DataTable_ColumnsName">当使用表为数据源时,为表起的自定义中文标题组</param>
public static void OutExcel(this DataTable tb, string[] DataTable_ColumnsName)
{
OutExcel(null, tb, DataTable_ColumnsName);
}
/// <summary>
/// 导出EXCEL
/// </summary>
/// <param name="dgv1">需要导出的DataGridView,请注意,分页的grid只能导出当前内容6087c8 </param>
public static void OutExcel(this DataGridView dgv1)
{
OutExcel(dgv1, null, null);
}
/// <summary>
/// 导出EXCEL
/// </summary>
/// <param name="dgv1">需要导出的DataGridView,请注意,分页的grid只能导出当前内容</param>
/// <param name="tb">需要导出的数据表,在第一个参数DataGridView为null时,此数据源有效</param>
/// <param name="DataTable_ColumnsName">当使用表为数据源时,为表起的自定义中文标题组</param>
public static void OutExcel(DataGridView dgv1, DataTable tb, string[] DataTable_ColumnsName)
{
DataGridView dgv;
if (null != dgv1)
dgv = dgv1;
else
{
dgv = new DataGridView();
foreach (string strTitle in DataTable_ColumnsName)
{
dgv.Columns.Add(strTitle, strTitle);
}
for (int i = 0; i < tb.Rows.Count; i++)
{
dgv.Rows.Add();
for (int ic = 0; ic < DataTable_ColumnsName.Length; ic++)
{
dgv.Rows[i].Cells[ic].Value = tb.Rows[i].ItemArray[ic] + "";
}
}
}
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);
if (0 < dgv.Rows.Count)
{
int iinx, iiins; iinx = iiins = 0;
while (iinx < dgv.Columns.Count)
{
if (dgv.Columns[iinx].Visible&&dgv.Columns[iinx].Name!=CHECKNAME)
{
excel.Cells[1, iiins + 1] = dgv.Columns[iinx].HeaderText;
iiins++;
}
iinx++;
}
int rowIndex, columnsIndex, cloumnsstart;
cloumnsstart = rowIndex = columnsIndex = 0;
while (rowIndex < dgv.Rows.Count)
{
cloumnsstart = columnsIndex = 0;
while (columnsIndex < dgv.Columns.Count)
{
if (dgv.Columns[columnsIndex].Visible && dgv.Columns[columnsIndex].Name != CHECKNAME)
{
excel.Cells[rowIndex + 2, cloumnsstart + 1] = dgv.Rows[rowIndex].Cells[columnsIndex].Value + "";
cloumnsstart++;
}
columnsIndex++;
}
rowIndex++;
//for (int i1 = 0; i1 < dgv.Columns.Count; i1++)
// excel.Cells[1, i1 + 1] = dgv.Columns[i1].HeaderText;
//for (int i = 0; i < dgv.Rows.Count; i++)
// for (int j = 0; j < dgv.Columns.Count; j++)
// excel.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value + "";
}
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
Microsoft.Office.Interop.Excel.Range range1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Columns;
range1.EntireColumn.AutoFit();
excel.Rows.RowHeight = 21;
Application.DoEvents();
excel.Visible = true;
excel.DisplayAlerts = true;
}
}
}
}
