生成新文档种源码

一、生成新文档类源码
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;
??? }

}