用C#生成Excel文件的方法和Excel.dll组件生成的方法 zt

生成Excel.dll组件

【注：在VS.Net中运行是要添加Excel.dll组件的，Excel组件VS.Net本身是没有的，下面是生成Excel.dll的方法。】
1.要保证机器本身要安装OFFICE. 
2.把[C:/Program Files/Microsoft Office/Office:默认安装路径]下的EXCEL9.OLB文件拷贝到[C:/Visual Studio.Net/SDK/v1.1/Bin:VS.Net安装路径]路径下。
3.打开Visual Studio .Net2003命令提示，运行TlbImp Excel9.olb Excel.dll ，就会在[C:/Visual Studio.Net/SDK/v1.1/Bin]下生成Excel.dll组件。
4.在项目中添加Excel.dll引用就OK了。 

 

一个示例：
class AppTest
 {
  private Excel.ApplicationClass _x;
  public static void Main0()
  {
   AppTest a = new AppTest();
   a._x = new Excel.ApplicationClass();
   a._x.UserControl = false;
   for (int i = 0 ;i < 4; i++)
   {
    
    a.SaveToXls("D://test//" + i + ".xls");  // 本例是在D盘下建立的test文件夹
   }
   a._x.Quit();
   System.Runtime.InteropServices.Marshal.ReleaseComObject((object) a._x);
   System.GC.Collect();
  }
  private void SaveToXls(string filename)
  {
   Excel.WorkbookClass wb = (Excel.WorkbookClass) this._x.Workbooks.Add(System.Reflection.Missing.Value);
   for(int i = 1;i <= 4; i++)
   {
    this._x.Cells[i,1]=i.ToString();
    this._x.Cells[i,2]="’bbb2";
    this._x.Cells[i,3]="’ccc3";
    this._x.Cells[i,4]="’aaa4";
   }
   
   wb.Saved = true;
   this._x.ActiveWorkbook.SaveCopyAs(filename);
  }
 }

winForm中导出Execl的方法：

1、方法1：

   SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
   SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn);
   DataSet ds=new DataSet();
   da.Fill(ds,"table1");
   DataTable dt=ds.Tables["table1"];
   string name=System.Configuration.ConfigurationSettings.AppSettings["downloadurl"].ToString()+DateTime.Today.ToString("yyyyMMdd")+new Random(DateTime.Now.Millisecond).Next(10000).ToString()+".csv";//存放到web.config中downloadurl指定的路径，文件格式为当前日期+4位随机数
   FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write);
   StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));
   sw.WriteLine("自动编号,姓名,年龄");
   foreach(DataRow dr in dt.Rows)
   {
    sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]);
   }
   sw.Close();
   Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name));
   Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载
   Response.WriteFile(name); // 把文件流发送到客户端
   Response.End();

 

public void Out2Excel(string sTableName,string url)
{
Excel.Application oExcel=new Excel.Application();
Workbooks oBooks;
Workbook oBook;
Sheets oSheets;
Worksheet oSheet;
Range oCells;
string sFile="",sTemplate="";
//
System.Data.DataTable dt=TableOut(sTableName).Tables[0];

sFile=url+"myExcel.xls";
sTemplate=url+"MyTemplate.xls";
//
oExcel.Visible=false;
oExcel.DisplayAlerts=false;
//定义一个新的工作簿
oBooks=oExcel.Workbooks;
oBooks.Open(sTemplate,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing, Type.Missing);
oBook=oBooks.get_Item(1);
oSheets=oBook.Worksheets;
oSheet=(Worksheet)oSheets.get_Item(1);
//命名该sheet
oSheet.Name="Sheet1";

oCells=oSheet.Cells;
//调用dumpdata过程，将数据导入到Excel中去
DumpData(dt,oCells);
//保存
oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
oBook.Close(false, Type.Missing,Type.Missing);
//退出Excel，并且释放调用的COM资源
oExcel.Quit();

GC.Collect();
KillProcess("Excel");
}

private void KillProcess(string processName)
{
System.Diagnostics.Process myproc= new System.Diagnostics.Process();
//得到所有打开的进程
try
{
foreach (Process thisproc in Process.GetProcessesByName(processName))
{
if(!thisproc.CloseMainWindow())
{
thisproc.Kill();
}
}
}
catch(Exception Exc)
{
throw new Exception("",Exc);
}
}

2、方法2：


 

protected void ExportExcel()
  {
   gridbind();
   if(ds1==null) return;
 
   string saveFileName="";
//   bool fileSaved=false;
   SaveFileDialog saveDialog=new SaveFileDialog();
   saveDialog.DefaultExt ="xls";
   saveDialog.Filter="Excel文件|*.xls";
   saveDialog.FileName ="Sheet1";
   saveDialog.ShowDialog();
   saveFileName=saveDialog.FileName;
   if(saveFileName.IndexOf(":")<0) return; //被点了取消
//   excelapp.Workbooks.Open   (App.path & 工程进度表.xls)
  
   Excel.Application xlApp=new Excel.Application();
   object missing=System.Reflection.Missing.Value;
 

   if(xlApp==null)
   {
    MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
    return;
   }
   Excel.Workbooks workbooks=xlApp.Workbooks;
   Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
   Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
   Excel.Range range;