源文参见http://www.cnblogs.com/waxdoll/archive/2005/10/28/264071.html
修改了部份功能及bug
一、生成新文档类源码
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;
}
}