asp.net中操作Excel全集[1]

 

         首先在 Web.Config 中添加模拟帐号

 

<identity impersonate="true" userName="administrator"password="password" />

 

         其次 要添加 EXCEL com 组件。如果是EXCEL2003,则添加 EXcel library 11.0 版本。我把用到的引用头文件列举如下：

 

        private Excel.Application objApp;

       private Excel.Sheets objSheets;

       private Excel._Worksheet objSheet;

       private Excel.Range range;

      objApp = new Excel.Application();

      objSheet = (Excel._Worksheet)objSheets.get_Item(1);

 

          我接着把大家编程中用到的函数列举如下：

 

using System;

using System.IO;

using System.Text;

using System.Data;

using System.Reflection;

using System.Diagnostics;

using System.Collections;

 

 

namespace TEST.EXCEL

 

{

 /// <summary>

 /// 说    明：Excel输出打印模块

 ///     暂时不提供操作Excel对象样式方法，样式可以在Excel模板中设置好

 /// 作    者：

 /// 创建日期：

 /// 修　　改：Super

 /// </summary>

 public class ExcelHelper

 {

  #region 成员变量

  private string templetFile = null;

  private string outputFile = null;

  private object missing = Missing.Value;

  private DateTime beforeTime;   //Excel启动之前时间

  private DateTime afterTime;    //Excel启动之后时间

  Excel.Application app;

  Excel.Workbook workBook;

  Excel.Worksheet workSheet;

  Excel.Range range;

  Excel.Range range1;

  Excel.Range range2;

  Excel.TextBox textBox;

  private int sheetCount = 1;   //WorkSheet数量

  private string sheetPrefixName = "页";

  #endregion

 

  #region 公共属性

  /// <summary>

  /// WorkSheet前缀名，比如：前缀名为“页”，那么WorkSheet名称依次为“页-1，页-2...”

  /// </summary>

  public string SheetPrefixName

  {

   set { this.sheetPrefixName = value; }

  }

 

  /// <summary>

  /// WorkSheet数量

  /// </summary>

  public int WorkSheetCount

  {

   get { return workBook.Sheets.Count; }

  }

 

  /// <summary>

  /// Excel模板文件路径

  /// </summary>

  public string TempletFilePath

  {

   set { this.templetFile = value; }

  }

 

  /// <summary>

  /// 输出Excel文件路径

  /// </summary>

  public string OutputFilePath

  {

   set { this.outputFile = value; }

  }

  #endregion

 

  #region 公共方法

 

  #region ExcelHelper

  /// <summary>

  /// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径

  /// </summary>

  /// <param name="templetFilePath">Excel模板文件路径</param>

  /// <param name="outputFilePath">输出Excel文件路径</param>

  public ExcelHelper(string templetFilePath,string outputFilePath)

  {

   if(templetFilePath == null)

    throw new Exception("Excel模板文件路径不能为空！");

 

   if(outputFilePath == null)

    throw new Exception("输出Excel文件路径不能为空！");

 

   if(!File.Exists(templetFilePath))

    throw new Exception("指定路径的Excel模板文件不存在！");

 

   this.templetFile = templetFilePath;

   this.outputFile = outputFilePath;

 

   //创建一个Application对象并使其可见

   beforeTime = DateTime.Now;

   app = new Excel.ApplicationClass();

   app.Visible = true;

   afterTime = DateTime.Now;

 

   //打开模板文件，得到WorkBook对象

   workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,

    missing,missing,missing,missing,missing,missing,missing);

 

   //得到WorkSheet对象

   workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

 

  }

 

  /// <summary>

  /// 构造函数，打开一个已有的工作簿

  /// </summary>

  /// <param name="fileName">Excel文件名</param>

  public ExcelHelper(string fileName)

  {

   if(!File.Exists(fileName))

    throw new Exception("指定路径的Excel文件不存在！");

 

   //创建一个Application对象并使其可见

   beforeTime = DateTime.Now;

   app = new Excel.ApplicationClass();

   app.Visible = true;

   afterTime = DateTime.Now;

 

   //打开一个WorkBook

   workBook = app.Workbooks.Open(fileName,

    Type.Missing, Type.Missing, Type.Missing, Type.Missing,

    Type.Missing, Type.Missing, Type.Missing, Type.Missing,

    Type.Missing, Type.Missing, Type.Missing, Type.Missing);

 

   //得到WorkSheet对象

   workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

 

  }

 

  /// <summary>

  /// 构造函数，新建一个工作簿

  /// </summary>

  public ExcelHelper()

  {

   //创建一个Application对象并使其可见

   beforeTime = DateTime.Now;

   app = new Excel.ApplicationClass();

   app.Visible = true;

   afterTime = DateTime.Now;

 

   //新建一个WorkBook

   workBook = app.Workbooks.Add(Type.Missing);

 

   //得到WorkSheet对象

   workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

 

  }

  #endregion