请教下,C#操作excel 本帖最后由 vachul 于 2011-08-13 11:14:32 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我做了一个你看看吧。第一步:添加引用,项目-->菜单-->添加引用--->COM-->Microsoft Excel 11.0 Object Library-->确定第二步:在源文件中添加Excel的引用,加 using Microsoft.Office.Interop.Excel;using System;using System.Configuration;using System.Data;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;using System.IO;using System.Text;using Microsoft.Office.Interop.Excel;public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { string str = "abcd"; ExportToExcel(@"d:/abcd.xls",str); } public static void ExportToExcel(string FileSavePath,string str) { int sheetCount = 10; Application excel = null; _Workbook xBk = null; _Worksheet xSt = null; try { excel = new ApplicationClass(); xBk = excel.Workbooks.Add(true); //对全部Sheet进行操作 for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { //创建一个Sheet if (null == xSt) { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } else { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); } //设置SheetName xSt.Name = "Excel"; if (sheetCount > 1) { xSt.Name += ((int)(sheetIndex + 1)).ToString(); } xSt.Cells[sheetIndex + 1, sheetIndex + 1] = str; } //删除Sheet1 excel.DisplayAlerts = false; //注意一定要加上这句 ((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete(); excel.DisplayAlerts = true;//注意一定要加上这句 object objOpt = System.Reflection.Missing.Value; excel.Visible = false; xBk.SaveCopyAs(FileSavePath); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); } catch (Exception e) { xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); } }} 不错,就是我想要的,不过,有几句:excel.DisplayAlerts = false; //注意一定要加上这句 ((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete(); excel.DisplayAlerts = true;//注意一定要加上这句是什么意思啊? 这句: excel = new ApplicationClass();错误 1 类型“Microsoft.Office.Interop.Excel.ApplicationClass”未定义构造函数 d:\我的文档\visual studio 2010\Projects\doexcel\doexcel\Form1.cs 36 25 doexcel 刚才发的不是winform的,现在给你一个winform的。好像是刚才添加的引用有点问题,才导致的错误,对不住了,添加以下吧。第一步:添加引用,项目-->菜单-->添加引用--->COM-->Microsoft Office 11.0 Object Library-->确定第二步:添加引用,项目-->菜单-->添加引用--->.NET-->Microsoft Office Interop Excel(选择11.0.0.0版本)-->确定第三步:在源文件中添加Excel的引用,using Excel = Microsoft.Office.Interop.Excel; private void button1_Click(object sender, EventArgs e) { string str = "abcd"; ExportToExcel(@"d:/abcd.xls", str); } public static void ExportToExcel(string FileSavePath, string str) { int sheetCount = 10; Excel.Application excel = null; Excel._Workbook xBk = null; Excel._Worksheet xSt = null; try { excel = new Excel.ApplicationClass(); xBk = excel.Workbooks.Add(true); //对全部Sheet进行操作 for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { //创建一个Sheet if (null == xSt) { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } else { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); } //设置SheetName xSt.Name = "Excel"; if (sheetCount > 1) { xSt.Name += ((int)(sheetIndex + 1)).ToString(); } xSt.Cells[sheetIndex + 1, sheetIndex + 1] = str; } //删除Sheet1 excel.DisplayAlerts = false; //注意一定要加上这句 ((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete(); excel.DisplayAlerts = true;//注意一定要加上这句 object objOpt = System.Reflection.Missing.Value; excel.Visible = false; xBk.SaveCopyAs(FileSavePath); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); } catch (Exception e) { xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); } } //删除Sheet1 excel.DisplayAlerts = false; //注意一定要加上这句((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();excel.DisplayAlerts = true;//注意一定要加上这句要是把这几句给注释掉的话,生成的excel文件中会有11个Sheet。你自己试一试就知道了 求从数据库中把输入的数据用Excel的形式导出的C#代码,谢谢! 关于DataGridView的美化问题 vs2010打包生成程序 显示窗口问题 ListView的3个问题 动态gridview怎么手动调整每列的宽度 关键路径和最短时间 请问如何通过客户端脚本判断是否触发某事件 请教:想写了一个CS的程序,想把它装在两台电脑上,然后把数据库装在另一台电脑上。 webservice调用引用时报错 哪有DevPartner下载 求助如何解决NULLReferenceException was unhandled C# 内存映射
第一步:添加引用,项目-->菜单-->添加引用--->COM-->Microsoft Excel 11.0 Object Library-->确定
第二步:在源文件中添加Excel的引用,加 using Microsoft.Office.Interop.Excel;
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Text;
using Microsoft.Office.Interop.Excel;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{ }
protected void Button1_Click(object sender, EventArgs e)
{
string str = "abcd";
ExportToExcel(@"d:/abcd.xls",str);
}
public static void ExportToExcel(string FileSavePath,string str)
{
int sheetCount = 10;
Application excel = null;
_Workbook xBk = null;
_Worksheet xSt = null;
try
{
excel = new ApplicationClass();
xBk = excel.Workbooks.Add(true); //对全部Sheet进行操作
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
//创建一个Sheet
if (null == xSt)
{
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
}
//设置SheetName
xSt.Name = "Excel";
if (sheetCount > 1)
{
xSt.Name += ((int)(sheetIndex + 1)).ToString();
} xSt.Cells[sheetIndex + 1, sheetIndex + 1] = str;
}
//删除Sheet1
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true;//注意一定要加上这句 object objOpt = System.Reflection.Missing.Value;
excel.Visible = false; xBk.SaveCopyAs(FileSavePath);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect(); }
catch (Exception e)
{
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
}
}
}
不错,就是我想要的,不过,有几句:
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true;//注意一定要加上这句是什么意思啊?
好像是刚才添加的引用有点问题,才导致的错误,对不住了,添加以下吧。
第一步:添加引用,项目-->菜单-->添加引用--->COM-->Microsoft Office 11.0 Object Library-->确定
第二步:添加引用,项目-->菜单-->添加引用--->.NET-->Microsoft Office Interop Excel(选择11.0.0.0版本)-->确定
第三步:在源文件中添加Excel的引用,using Excel = Microsoft.Office.Interop.Excel;
private void button1_Click(object sender, EventArgs e)
{
string str = "abcd";
ExportToExcel(@"d:/abcd.xls", str);
}
public static void ExportToExcel(string FileSavePath, string str)
{
int sheetCount = 10;
Excel.Application excel = null;
Excel._Workbook xBk = null;
Excel._Worksheet xSt = null;
try
{
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true); //对全部Sheet进行操作
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
//创建一个Sheet
if (null == xSt)
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
}
//设置SheetName
xSt.Name = "Excel";
if (sheetCount > 1)
{
xSt.Name += ((int)(sheetIndex + 1)).ToString();
} xSt.Cells[sheetIndex + 1, sheetIndex + 1] = str;
}
//删除Sheet1
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true;//注意一定要加上这句 object objOpt = System.Reflection.Missing.Value;
excel.Visible = false; xBk.SaveCopyAs(FileSavePath);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect(); }
catch (Exception e)
{
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
}
}
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true;//注意一定要加上这句要是把这几句给注释掉的话,生成的excel文件中会有11个Sheet。你自己试一试就知道了