C# datagridview导出Excel_c# datagridview 导出excel 类库-优快云博客

本文链接：https://blog.youkuaiyun.com/chunli200902520208/article/details/39453403

首先我们需要准备好导出所用的组件，而且他们版本应该是相同的

Microsoft.Office.Interop.Excel.dll
与Office.dll必须是相同的版本，引入两个DLL,Microsoft.Office.Interop.Excel.dll和office.dll,在加上项目的时候，
会报错“类型“Microsoft.Office.Interop.Excel.ApplicationClass” 未定义构造函数无法嵌入互操作类型“Microsoft.Office.Interop.Excel.ApplicationClass”
。请改用适用的接口。”，这时只需要将将引用的DLL：Microsoft.Office.Interop.Excel;的嵌入互操作类型改为false,就可以了

这两个组件在附件中都能找到，

使用下面的代码即可导出：

public static int ExportExcel(string strCaption, DataGridView myDGV, SaveFileDialog saveFileDialog)
{
int result = 9999;
saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel(97-2003)(*.xls)|*.xls|Excel(*.xlsx)|(*.xlsx)";
// saveFileDialog.RestoreDirectory = true;
saveFileDialog.FileName = "教师信息表" + DateTime.Now.ToString("yyMMddHHmmss");
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
string fileName = saveFileDialog.FileName;
if (File.Exists(fileName))
{
File.Delete(fileName);
}
//列索引，行索引，列总数，行总数
int ColIndex = 0;
int RowIndex = 0;
int Col_Count = myDGV.Columns.Count;
int Row_Count = myDGV.Rows.Count;
if (myDGV.Rows.Count == 0)
{
result = 1;//表示没有数据
}
//创建Excel对象
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (xlApp == null)
{
result = 2;//代表Excel无法启动
}
try
{
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
xlSheet.Cells.VerticalAlignment = Constants.xlCenter;
xlSheet.Cells.HorizontalAlignment = Constants.xlCenter;
//设置标题，标题所占单元格数同DataGridView列数相同起始行，起始列结束行结束列
Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, Col_Count]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = strCaption;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
//创建数据缓存
object[,] objData = new object[Row_Count +1,Col_Count];
//获取列标题
foreach (DataGridViewColumn col in myDGV.Columns)
{
objData[RowIndex,ColIndex++] = col.HeaderText;
}
//获取数据,少一行
for (RowIndex = 1; RowIndex <= Row_Count; RowIndex++)
{
for (ColIndex = 0; ColIndex < Col_Count; ColIndex++)
{
if (myDGV[ColIndex, RowIndex - 1].ValueType == typeof(string) || myDGV[ColIndex, RowIndex - 1].ValueType == typeof(DateTime))
{
objData[RowIndex, ColIndex] = " " + myDGV[ColIndex, RowIndex-1].Value;

}
else if (myDGV[ColIndex, RowIndex - 1].ValueType.Name == "Decimal")
{
objData[RowIndex, ColIndex] = Convert.ToDecimal(myDGV[ColIndex, RowIndex-1].Value).ToString("0.00");
}
else
{
objData[RowIndex, ColIndex] = myDGV[ColIndex, RowIndex-1].Value;
}
}
System.Windows.Forms.Application.DoEvents();
}
//写入Excel
range.Borders.LineStyle = 1;//开始行，开始列结束行结束列
range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[Row_Count + 2, Col_Count]);
range.NumberFormatLocal = "@";
range.Value2 = objData;
range.Columns.AutoFit();
xlBook.Saved = true;
xlBook.SaveCopyAs(saveFileDialog.FileName);
xlApp.DisplayAlerts = false;
}
catch(Exception ex)
{
result = 9999;//异常错误
}
finally
{
xlApp.Application.Workbooks.Close();
xlApp.Quit();
GC.Collect();//强制回收
}
return 0;
}
return result;
}
这是很经典的代码，如果想在底部添加统计列，则如下：

using (SqlConnection conn = new SqlConnection(sqlConn))
{
SqlDataAdapter da = new SqlDataAdapter("select * from tb_teacher", sqlConn);
DataSet ds = new DataSet();

da.Fill(ds, "tb_teacher");
System.Data.DataTable dt = ds.Tables["tb_teacher"];
int sum1 = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
sum1 += Convert.ToInt32(dt.Rows[i][4].ToString());
}
DataRow dr = dt.NewRow();
dr[1] = "合计";
dr[4] = sum1.ToString();
dt.Rows.Add(dr);
// dgResult.DataSource = ds.Tables[0];
dgResult.DataSource = dt.DefaultView;

}