生成新文档类源码

一、生成新文档类源码

using System;
using Excel;
using System.Windows.Forms;
using System.Text.RegularExpressions;

namespace DebtManage.Report
{
 /// <summary>
 /// ExcelHelp 的摘要说明。
 /// </summary>
 public class ExcelHelp
 {
  /// <summary>
  /// Excel
  /// </summary>
  public Excel.Application CurExcel = null;

  /// <summary>
  /// 工作簿
  /// </summary>
  public Excel._Workbook CurBook = null;

  /// <summary>
  /// 工作表
  /// </summary>
  public Excel._Worksheet CurSheet = null;

  private object mValue = System.Reflection.Missing.Value;

//  private string filepath;
//  private string timestamp;
  private System.DateTime dtBefore;
  private System.DateTime dtAfter;
  public ExcelHelp()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //

   dtBefore=System.DateTime.Now;

   CurExcel=new Excel.ApplicationClass();

   dtAfter=System.DateTime.Now;

   CurExcel.Visible=true;
//   CurExcel.UserName="DebtSystem";
//   MessageBox.Show(CurExcel.UILanguage.ToString());

   CurExcel.Workbooks.Add(true);
   CurSheet=CurExcel.Worksheets[1] as _Worksheet;
   CurBook=CurExcel.Workbooks[1];
  }
  public void SetAutoFilter(string strStartCelID,string strEndCelID)
  {
   CurSheet.get_Range(strStartCelID,strEndCelID).AutoFilter(1, mValue,
    Excel.XlAutoFilterOperator.xlAnd, mValue, true);
//   mValue = System.Reflection.Missing.Value;

//   (1,"A01",Excel.XlAutoFilterOperator.xlAnd,mValue,mValue);  
  }
  
  /// <summary>
  /// 合并单元格，并在合并后的单元格中插入指定的值
  /// </summary>
  /// <param name="strStartCell"></param>
  /// <param name="strEndCell"></param>
  /// <param name="objValue"></param>
  public void WriteAfterMerge(string strStartCell, string strEndCell, object objValue)
  {
   CurSheet.get_Range(strStartCell, strEndCell).Merge(mValue);
   CurSheet.get_Range(strStartCell, mValue).Value2 = objValue;
  }
  /// <summary>
  /// 为单元格设置公式
  /// </summary>
  /// <param name="strCell">单元格标识符</param>
  /// <param name="strFormula">公式</param>
  public void SetFormula(string strCell, string strFormula)
  {
   CurSheet.get_Range(strCell, mValue).Formula = strFormula;
  }
  /// <summary>
  /// 设置单元格或连续区域的字体为黑体
  /// </summary>
  /// <param name="strCell">单元格标识符</param>
  public void SetBold(string strCell)
  {
   CurSheet.get_Range(strCell, mValue).Font.Bold = true;
  }
  /// <summary>
  /// 设置单元格或连续区域的字体为黑体
  /// </summary>
  /// <param name="strCell">单元格标识符</param>
  public void SetBold(string strStartCell,string strEndCell)
  {
   CurSheet.get_Range(strStartCell, strEndCell).Font.Bold = true;
  }
  /// <summary>
  /// 设置单元格或连续区域的边框：上下左右都为黑色连续边框
  /// </summary>
  /// <param name="strCell">单元格标识符</param>
  public void SetBorderAll(string strCell)
  {
   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight=Excel.XlBorderWeight.xlThick;

   //    Range("C5:H11").Select
   //  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
   //  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
   //  With Selection.Borders(xlEdgeLeft)
   //  .LineStyle = xlContinuous
   //  .Weight = xlMedium
   //   .ColorIndex = xlAutomatic
   //  End With

   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight=Excel.XlBorderWeight.xlThick;

   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;

   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight=Excel.XlBorderWeight.xlThick;

   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;

   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;

  }/** <summary>
  /// 设置连续区域的边框：上下左右都为黑色连续边框
  /// </summary>
  /// <param name="strStartCell">开始单元格标识符</param>
  /// <param name="strEndCell">结束单元格标识符</param>
  public void SetBorderAll(string strStartCell, string strEndCell,Excel.XlBorderWeight xlEdgeWeight,Excel.XlBorderWeight xlInsideWeight)
  {
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight=xlEdgeWeight;

   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight=xlEdgeWeight;

   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight=xlEdgeWeight;

   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight=xlEdgeWeight;

   string iStart="",iEnd="";
   Regex r=new Regex(@"(\d+)",RegexOptions.IgnoreCase);
   System.Text.RegularExpressions.Match m=r.Match(strStartCell);
   if (m.Success)
   {
    iStart=m.Groups[1].ToString();
   }
   m=r.Match(strEndCell);
   if (m.Success)
   {
    iEnd=m.Groups[1].ToString();
   }
   if (iStart==iEnd)
    return;

   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight=xlInsideWeight;

   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
   CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Weight=xlInsideWeight;
  }
  /// <summary>
  /// 设置单元格或连续区域水平居左
  /// </summary>
  /// <param name="strCell">单元格标识符</param>
  public void SetHAlignLeft(string strCell)
  {
   SetHAlignLeft(strCell,strCell);
  } 
  /// <summary>
  /// 设置单元格或连续区域水平居左
  /// </summary>
  /// <param name="strCell">单元格标识符</param>
  public void SetHAlignLeft(string strStartCell,string strEndCell)
  {
   CurSheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
  } 
  /// <summary>
  /// 设置单元格或连续区域水平居左
  /// </summary>
  /// <param name="strCell">单元格标识符</param>
  public void SetHAlignRight(string strCell)
  {
   SetHAlignRight(strCell,strCell);
  }
  /// <summary>
  /// 设置单元格或连续区域水平居左
  /// </summary>
  /// <param name="strCell">单元格标识符</param>
  public void SetHAlignRight(string strStartCell,string strEndCell)
  {
   CurSheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
  }
  /// <summary>
  /// 设置单元格或连续区域的显示格式
  /// </summary>
  /// <param name="strCell">单元格标识符</param>
  /// <param name="strNF">如"#,##0.00"的显示格式</param>
  public void SetNumberFormat(string strCell, string strNF)
  {
   CurSheet.get_Range(strCell, mValue).NumberFormat = strNF;
  }
  /// <summary>
  /// 设置单元格或连续区域的字体大小
  /// </summary>
  /// <param name="strCell">单元格或连续区域标识符</param>
  /// <param name="intFontSize"></param>
  public void SetFontSize(string strCell, int intFontSize)
  {
   CurSheet.get_Range(strCell, mValue).Font.Size = intFontSize.ToString();
  }
  public void SetFontSize(string strStartCol,string strEndCol, int intFontSize)
  {
   CurSheet.get_Range(strStartCol, strEndCol).Font.Size = intFontSize.ToString();
  }
  public void SetFontColor(string strStartCol,string strEndCol, System.Drawing.Color color)
  {
   CurSheet.get_Range(strStartCol, strEndCol).Font.Color=System.Drawing.ColorTranslator.ToOle(color);
  }
  /// <summary>
  /// 设置列宽
  /// </summary>
  /// <param name="strColID">列标识，如A代表第一列</param>
  /// <param name="decWidth">宽度</param>
  public void SetColumnWidth(string strStartColID,string strEndColID, double dblWidth)
  {
   ((Excel.Range)CurSheet.Columns.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, CurSheet.Columns, new object[]{(strStartColID + ":" + strEndColID).ToString()})).ColumnWidth = dblWidth;
  }
  public void SetRowHeiht(string strStartRowID,string strEndRowID, double dblWidth)
  {  
   ((Excel.Range)CurSheet.Rows.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, CurSheet.Rows, new object[]{(strStartRowID + ":" + strEndRowID).ToString()})).RowHeight = dblWidth;
  }
  /// <summary>
  /// 设置单元格或连续区域水平居左
  /// </summary>
  /// <param name="strCell">单元格标识符</param>
  public void SetHAlignCenter(string strCell)
  {
   CurSheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
  }

  /// <summary>
  /// 设置连续区域水平居中
  /// </summary>
  /// <param name="strStartCell">开始单元格标识符</param>
  /// <param name="strEndCell">结束单元格标识符</param>
  public void SetHAlignCenter(string strStartCell, string strEndCell)
  {
   CurSheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
  }
  public string NtoL(int intNumber)
  {
   if (intNumber > 702)
    return String.Empty;

   if (intNumber == 702)
    return "ZZ";

   string strRtn = String.Empty;

   string strLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

   if (intNumber > 26)
    strRtn = strLetters.Substring(intNumber / 26 - 1, 1);

   int mv=intNumber % 26;
   if (mv==0)
    strRtn += "Z";
   else
    strRtn += strLetters.Substring(mv - 1, 1);

   return strRtn;
  }
  /// <summary>
  /// 在指定Range中插入指定的值
  /// </summary>
  /// <param name="strStartCell">Range的开始单元格</param>
  /// <param name="strEndCell">Range的结束单元格</param>
  /// <param name="objValue">文本、数字等值</param>
  public void WriteRange(string strStartCell, string strEndCell, object objValue)
  {
   CurSheet.get_Range(strStartCell, strEndCell).Value2 = objValue;
  }
  /// <summary>
  /// 在指定Range中插入指定的值
  /// </summary>
  /// <param name="strStartCell">Range的开始单元格</param>
  /// <param name="strEndCell">Range的结束单元格</param>
  /// <param name="objValue">文本、数字等值</param>
  public void WriteRange(string strCell, object objValue)
  {
   CurSheet.get_Range(strCell, mValue).Value2 = objValue;
  }
  
  public object GetRangeValue(string strStartCell, string strEndCell)
  {
   return CurSheet.get_Range(strStartCell, strEndCell).Value2;
  }
  public object GetRangeValue(string strStartCell)
  {
   return CurSheet.get_Range(strStartCell, strStartCell).Value2;
  }
  /// <summary>
  /// 释放内存空间
  /// </summary>
  public void Dispose()
  {
   try
   {
    System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
    CurSheet = null;

    CurBook.Close(false, mValue, mValue);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
    CurBook = null;

    CurExcel.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
    CurExcel = null;

    GC.Collect();
    GC.WaitForPendingFinalizers();

   }
   catch(System.Exception ex)
   {
//    MessageBox.Show("在释放Excel内存空间时发生了一个错误："+ex.Message);
   }
   finally
   {
    foreach(System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
     if (pro.StartTime > this.dtBefore && pro.StartTime < this.dtAfter)
     {
      try
      {
       pro.Kill();       
      }
      catch{}
     }
   }
   System.GC.SuppressFinalize(this);
  }
 }
}


二、操作已有文档类源码

using System;
using Excel;
using System.Collections;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Text.RegularExpressions;

public class ExcelHelper
{
    private Excel._Application excelApp;
    private string fileName = string.Empty;
    private Excel.WorkbookClass wbclass;
    public ExcelHelper(string _filename)
    {
        excelApp = new Excel.Application();
        object objOpt = System.Reflection.Missing.Value;
        wbclass = (Excel.WorkbookClass)excelApp.Workbooks.Open(_filename, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
    }
    /**/
    /// <summary>
    /// 所有sheet的名称列表
    /// </summary>
    /// <returns></returns>
    public List<string> GetSheetNames()
    {
        List<string> list = new List<string>();
        Excel.Sheets sheets = wbclass.Worksheets;
        string sheetNams = string.Empty;
        foreach (Excel.Worksheet sheet in sheets)
        {
            list.Add(sheet.Name);
        }
        return list;
    }
    public Excel.Worksheet GetWorksheetByName(string name)
    {
        Excel.Worksheet sheet = null;
        Excel.Sheets sheets = wbclass.Worksheets;
        foreach (Excel.Worksheet s in sheets)
        {
            if (s.Name == name)
            {
                sheet = s;
                break;
            }
        }
        return sheet;
    }
    /**/
    /// <summary>
    ///
    /// </summary>
    /// <param name="sheetName">sheet名称</param>
    /// <returns></returns>
    public Array GetContent(string sheetName, string strStartCell, string strEndCell)
    {
        Excel.Worksheet sheet = GetWorksheetByName(sheetName);
        //获取A1 到AM24范围的单元格
        Excel.Range rang = sheet.get_Range(strStartCell,strEndCell);
        //读一个单元格内容
        //sheet.get_Range("A1", Type.Missing);
        //不为空的区域,列,行数目
        //   int l = sheet.UsedRange.Columns.Count;
        // int w = sheet.UsedRange.Rows.Count;
        //  object[,] dell = sheet.UsedRange.get_Value(Missing.Value) as object[,];
        System.Array values = (Array)rang.Cells.Value2;
        return values;
    }

    public Array GetUsedRangeContent(string sheetName)
    {
        Excel.Worksheet sheet = GetWorksheetByName(sheetName);

        int ulc = sheet.UsedRange.Columns.Count;
        int uwc = sheet.UsedRange.Rows.Count;

        string strStartCell = "A1", strEndCell = NtoL(ulc) + uwc.ToString();

        Excel.Range rang = sheet.get_Range(strStartCell, strEndCell);
        System.Array values = (Array)rang.Cells.Value2;
        return values;
    }

    public Array GetContent(string sheetName, int iStartRow,int iStartCol,int iEndRow,int iEndCol)
    {
        string strStartCell = NtoL(iStartRow) + iStartCol.ToString(), strEndCell = NtoL(iEndRow) + iEndCol.ToString();
        return GetContent(sheetName, strStartCell, strEndCell);
    }

    public void Close()
    {
        excelApp.Quit();
        excelApp = null;
    }

    public string NtoL(int intNumber)
    {
        if (intNumber > 702)
            return String.Empty;

        if (intNumber == 702)
            return "ZZ";

        string strRtn = String.Empty;

        string strLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

        if (intNumber > 26)
            strRtn = strLetters.Substring(intNumber / 26 - 1, 1);

        int mv = intNumber % 26;
        if (mv == 0)
            strRtn += "Z";
        else
            strRtn += strLetters.Substring(mv - 1, 1);

        return strRtn;
    }

}