Visual Studio 2010 C#,winform,如何将dataGridview的数据导出到Excel表中?

高手们,请教下,Visual Studio 2010 C#,winform,如何将dataGridview的数据导出到Excel表中?
最新回答
﹛理葙﹜忄青λ

2024-09-13 00:01:11

//需要Using的命名
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;

------------------------------------------------------
////需要使用的变数。Workbook表示Excel档,Worksheet表示一个Excel档里面的sheet(一个Excel档可以有很多sheet),Range表示Excel里面单元格的范围。
Microsoft.Office.Interop.Excel.Application xlApp = null;
Workbook wb = null;
Worksheet ws = null;
Range aRange = null;
Range aRange2 = null;
object mObj_opt = System.Reflection.Missing.Value;
--------------------------------------------------------
private void WriteXls()
{
Console.WriteLine("WriteXls");
//启动Excel应用程式
xlApp = new Microsoft.Office.Interop.Excel.Application();

if (xlApp == null)
{
Console.WriteLine("Error! xlApp");
return;
}
//用Excel应用程式建立一个Excel物件,也就是Workbook。并取得Workbook中的第一个sheet。这就是我们要操作资料的地方。
wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
ws = (Worksheet)wb.Worksheets[1];
if (ws == null)
{
Console.WriteLine("Error! ws");
}
//要在Excel储存资料,有三种方式,以下分别介绍。利用Range物件,设定要储存资料的储存格范围。
// Select the Excel cells, in the range c1 to c7 in the worksheet.
Range aRange = ws.get_Range("C1", "C7");
if (aRange == null)
{
Console.WriteLine("Could not get a range. Check to be sure you have the correct versions of the office DLLs.");
}
// Fill the cells in the C1 to C7 range of the worksheet with the number 6.
Object[] args = new Object[1];
args[0] = 6;
aRange.Value2 = args;
//衍生自上面方法,但是在储存资料的时候,可以用InvokeMember呼叫aRange的资料成员(成员函式?)。
//aRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, aRange, args);
//利用Cells属性,取得单一储存格,并进行操作。
string[] number = { "A", "B", "C", "D", "E" };
foreach (string s in number)
{
Range aRange2 = (Range)ws.Cells["1", s];
Object[] args2 = new Object[1];
args2[0] = s;
aRange2.Value2 = args2;
}

//最後,呼叫SaveAs function储存这个Excel物件到硬碟。
wb.SaveAs(@"C:\test.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, mObj_opt, mObj_opt, mObj_opt, mObj_opt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, mObj_opt, mObj_opt, mObj_opt, mObj_opt, mObj_opt);

Console.WriteLine("save");
wb.Close(false, mObj_opt, mObj_opt);
xlApp.Workbooks.Close();
xlApp.Quit();
//删除 Windows工作管理员中的Excel.exe 进程,
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(aRange2);
System.Runtime.InteropServices.Marshal.ReleaseComObject(aRange3);
xlApp = null;
wb = null;
ws = null;
aRange2 = null;
aRange3 = null;
//呼叫垃圾回收
GC.Collect();
}
追问
谢谢,可是我的VS引用不了Microsoft.Office.Interop.Excel这个命名空间,您知道是什么原因吗?
ぜ龙游浅水

2024-09-13 00:02:08

要那么麻烦吗?
先写个通用的方法
// 定义导出Excel的函数
private void Export(string FileType, string FileName)
{
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
HtmlTextWriter htw = new HtmlTextWriter(sw);
Page page = new Page();
HtmlForm form = new HtmlForm();
GrvRuleDetail.EnableViewState = false;
page.EnableEventValidation = false;
page.DesignerInitialize();
page.Controls.Add(form);
form.Controls.Add(GrvRuleDetail);
page.RenderControl(htw);
Response.Clear();
Response.Buffer = true;
Response.ContentType = FileType;
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.Charset = "UTF-8";
Response.ContentEncoding = Encoding.Default;
Response.Write(sb.ToString());
Response.End();
}
然后使用:
//导出数据
protected void bthExport_Click(object sender, EventArgs e)
{
if (this.dropExport.SelectedValue.Equals("Word"))
{
Export("application/ms-word", "数据.doc");
}
else
{
Export("application/ms-excel", "数据.xls");
}
}
引用就
using System.IO;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
浅时光倾城

2024-09-13 00:03:14

using Microsoft.Office.Interop.Excel;

//将当前显示的数据导入EXCL表格中
public bool ExportDataGridview(DataGridView gridView)
{
if (gridView.Rows.Count == 0)
return false;
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
//生成字段名称
for (int i = 0; i < gridView.ColumnCount; i++)
{
excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
} //填充数据
for (int i = 0; i < gridView.RowCount - 1; i++)
{
for (int j = 0; j < gridView.ColumnCount; j++)
{
if (gridView[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" +
gridView[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] =
gridView[j, i].Value.ToString();
}
}
}
excel.Visible = true;
return true;
}
用的时候直接调用就好了
追问
谢谢,可是我的VS引用不了Microsoft.Office.Interop.Excel这个命名空间,您知道是什么原因吗?