为简化使用Excel COM写的类
from: http://www.cnblogs.com/waxdoll/archive/2005/10/28/264071.html
经常需要在开发中使用Excel COM,为简化使用写了这个类,感觉还是不太方便。
using System;
namespace ExcelHandle { /** <summary> /// ExcelHandle 的摘要说明。 /// </summary> public class ExcelHandle {
/** <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;
/** <summary> /// 构造函数 /// </summary> public ExcelHandle() { // // TODO: 在此处添加构造函数逻辑 //
this.dtBefore = System.DateTime.Now; CurExcel = new Excel.Application();
this.dtAfter = System.DateTime.Now;
this.timestamp = System.DateTime.Now.ToShortDateString().Replace("-", "") + System.DateTime.Now.ToShortTimeString().Replace(":", "") + System.DateTime.Now.Second.ToString() + System.DateTime.Now.Millisecond.ToString();
}
/** <summary> /// 构造函数 /// </summary> /// <param name="strFilePath">加载的Excel文件名</param> public ExcelHandle(string strFilePath) {
this.dtBefore = System.DateTime.Now;
CurExcel = new Excel.Application();
this.dtAfter = System.DateTime.Now;
CurBook = (Excel._Workbook)CurExcel.Workbooks.Open(strFilePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
this.timestamp = System.DateTime.Now.ToShortDateString().Replace("-", "") + System.DateTime.Now.ToShortTimeString().Replace(":", "") + System.DateTime.Now.Second.ToString() + System.DateTime.Now.Millisecond.ToString();
}
/** <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) { this.MessageWarning("在释放Excel内存空间时发生了一个错误:", ex); } finally { foreach(System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel")) if (pro.StartTime > this.dtBefore && pro.StartTime < this.dtAfter) pro.Kill(); } System.GC.SuppressFinalize(this); }
private string filepath; private string timestamp; private System.DateTime dtBefore; private System.DateTime dtAfter;
/** <summary> /// Excel文件名 /// </summary> public string FilePath { get { return this.filepath; } set { this.filepath = value; } }
/** <summary> /// 是否打开Excel界面 /// </summary> public bool Visible { set { CurExcel.Visible = value; } }
/** <summary> /// 以时间字符串作为保存文件的名称 /// </summary> public string TimeStamp { get { return this.timestamp; } set { this.timestamp = value; } }
/** <summary> /// 加载Excel文件 /// </summary> public void Load() { if (CurBook == null && this.filepath != null) CurBook = (Excel._Workbook)CurExcel.Workbooks.Open(this.filepath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue); }
/** <summary> /// 加载Excel文件 /// </summary> /// <param name="strFilePath">Excel文件名</param> public void Load(string strFilePath) { if (CurBook == null) CurBook = (Excel._Workbook)CurExcel.Workbooks.Open(strFilePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue); }
/** <summary> /// 新建工作表 /// </summary> /// <param name="strWorkSheetName">工作表名称</param> public void NewWorkSheet(string strWorkSheetName) { CurSheet = (Excel._Worksheet)CurBook.Sheets.Add(CurBook.Sheets[1], mValue, mValue, mValue); CurSheet.Name = strWorkSheetName; }
/** <summary> /// 在指定单元格插入指定的值 /// </summary> /// <param name="strCell">单元格,如“A4”</param> /// <param name="objValue">文本、数字等值</param> public void WriteCell(string strCell, object objValue) { CurSheet.get_Range(strCell, mValue).Value2 = objValue; }
/** <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> /// 合并单元格,并在合并后的单元格中插入指定的值 /// </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> /// 在连续单元格中插入一个DataTable中的值 /// </summary> /// <param name="strStartCell">开始的单元格</param> /// <param name="dtData">存储数据的DataTable</param> public void WriteTable(string strStartCell, System.Data.DataTable dtData) { object[,] arrData = new object[dtData.Rows.Count, dtData.Columns.Count];
for (int i = 0; i < dtData.Rows.Count; i ++) for (int j = 0; j < dtData.Columns.Count; j ++) arrData[i, j] = dtData.Rows[i][j];
CurSheet.get_Range(strStartCell, this.GetEndCell(strStartCell, dtData.Rows.Count - 1, dtData.Columns.Count - 1)).Value2 = arrData;
arrData = null; }
/** <summary> /// 在连续单元格中插入一个DataTable并作超级链接 /// </summary> /// <param name="strStartCell">起始单元格标识符</param> /// <param name="dtData">存储数据的DataTable</param> /// <param name="strLinkField">链接的地址字段</param> /// <param name="strTextField">链接的文本字段</param> public void WriteTableAndLink(string strStartCell, System.Data.DataTable dtData, string strLinkField, string strTextField) { object[,] arrData = new object[dtData.Rows.Count, dtData.Columns.Count - 1];
for (int i = 0; i < dtData.Rows.Count; i ++) { for (int j = 0; j < dtData.Columns.Count; j ++) { if (j > dtData.Columns.IndexOf(strLinkField)) arrData[i, j - 1] = dtData.Rows[i][j]; else if (j < dtData.Columns.IndexOf(strLinkField)) arrData[i, j] = dtData.Rows[i][j]; } }
CurSheet.get_Range(strStartCell, this.GetEndCell(strStartCell, dtData.Rows.Count - 1, dtData.Columns.Count - 2)).Value2 = arrData;
for (int i = 0; i < dtData.Rows.Count; i ++) this.AddHyperLink(this.NtoL(this.LtoN(this.GetCellLetter(strStartCell)) + dtData.Columns.IndexOf(strTextField)) + System.Convert.ToString(this.GetCellNumber(strStartCell) + i), dtData.Rows[i][strLinkField].ToString() + ".htm", "点击查看详细", dtData.Rows[i][strTextField].ToString());
arrData = null; }
/** <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="strStartCell">开始单元格标识符</param> /// <param name="strEndCell">结束单元格标识符</param> public void SetBold(string strStartCell, string strEndCell) { CurSheet.get_Range(strStartCell, strEndCell).Font.Bold = true; }
/** <summary> /// 设置单元格或连续区域的字体颜色 /// </summary> /// <param name="strCell">单元格标识符</param> /// <param name="clrColor">颜色</param> public void SetColor(string strCell, System.Drawing.Color clrColor) { CurSheet.get_Range(strCell, mValue).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor); }
/** <summary> /// 设置连续区域的字体颜色 /// </summary> /// <param name="strStartCell">开始单元格标识符</param> /// <param name="strEndCell">结束单元格标识符</param> /// <param name="clrColor">颜色</param> public void SetColor(string strStartCell, string strEndCell, System.Drawing.Color clrColor) { CurSheet.get_Range(strStartCell, strEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor); }
/** <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.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); 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.LightGray); CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); 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.LightGray); 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) { CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; }
/** <summary> /// 设置单元格或连续区域水平居左 /// </summary> /// <param name="strCell">单元格标识符</param> public void SetHAlignLeft(string strCell) { CurSheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; }
/** <summary> /// 设置连续区域水平居左 /// </summary> /// <param name="strStartCell">开始单元格标识符</param> /// <param name="strEndCell">结束单元格标识符</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 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; }
/** <summary> /// 设置单元格或连续区域水平居右 /// </summary> /// <param name="strCell">单元格标识符</param> public void SetHAlignRight(string strCell) { CurSheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; }
/** <summary> /// 设置连续区域水平居右 /// </summary> /// <param name="strStartCell">开始单元格标识符</param> /// <param name="strEndCell">结束单元格标识符</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="strStartCell">开始单元格标识符</param> /// <param name="strEndCell">结束单元格标识符</param> /// <param name="strNF">如"#,##0.00"的显示格式</param> public void SetNumberFormat(string strStartCell, string strEndCell, string strNF) { CurSheet.get_Range(strStartCell, strEndCell).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(); }
/** <summary> /// 设置连续区域的字体大小 /// </summary> /// <param name="strStartCell">开始单元格标识符</param> /// <param name="strEndCell">结束单元格标识符</param> /// <param name="intFontSize">字体大小</param> public void SetFontSize(string strStartCell, string strEndCell, int intFontSize) { CurSheet.get_Range(strStartCell, strEndCell).Font.Size = intFontSize.ToString(); }
/** <summary> /// 设置列宽 /// </summary> /// <param name="strColID">列标识,如A代表第一列</param> /// <param name="decWidth">宽度</param> public void SetColumnWidth(string strColID, double dblWidth) { ((Excel.Range)CurSheet.Columns.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, CurSheet.Columns, new object[]{(strColID + ":" + strColID).ToString()})).ColumnWidth = dblWidth; }
/** <summary> /// 为单元格添加超级链接 /// </summary> /// <param name="strCell">单元格标识符</param> /// <param name="strAddress">链接地址</param> /// <param name="strTip">屏幕提示</param> /// <param name="strText">链接文本</param> public void AddHyperLink(string strCell, string strAddress, string strTip, string strText) { CurSheet.Hyperlinks.Add(CurSheet.get_Range(strCell, mValue), strAddress, mValue, strTip, strText); }
/** <summary> /// 已知开始的单元格标识,求intR行、intColumn列后的单元格标识 /// </summary> /// <param name="strStartCell">开始单元格标识</param> /// <param name="intR">行数</param> /// <param name="intC">列数</param> /// <returns>单元格标识符结果</returns> public string GetEndCell(string strStartCell, int intR, int intC) {
System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>/d+)");
return this.NtoL(this.LtoN(regex.Match(strStartCell).Result("${vLetter}")) + intC) + System.Convert.ToString((System.Convert.ToInt32(regex.Match(strStartCell).Result("${vNumber}")) + intR));
}
/** <summary> /// 获取单元格标识符中的字母 /// </summary> /// <param name="strCell">单元格标识符</param> /// <returns>单元格标识符对应的字母</returns> public string GetCellLetter(string strCell) { System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>/d+)"); return regex.Match(strCell).Result("${vLetter}"); }
/** <summary> /// 获取单元格标识符中的数字 /// </summary> /// <param name="strCell">单元格标识符</param> public int GetCellNumber(string strCell) { System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>/d+)"); return System.Convert.ToInt32(regex.Match(strCell).Result("${vNumber}")); }
/** <summary> /// 另存为xls文件 /// </summary> /// <param name="strFilePath">文件路径</param> public void Save(string strFilePath) { CurBook.SaveCopyAs(strFilePath); }
/** <summary> /// 另存为html文件 /// </summary> /// <param name="strFilePath">文件路径</param> public void SaveHtml(string strFilePath) { CurBook.SaveAs(strFilePath, Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue); }
public void CreateHtmlFile() { }
//辅助函数 #region 辅助函数
/** <summary> /// 调用MessageBox显示警告信息 /// </summary> /// <param name="text">警告信息</param> private void MessageWarning(string text) { System.Windows.Forms.MessageBox.Show(text, "Excel操作组件", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning); }
/** <summary> /// 调用MessageBox显示警告信息 /// </summary> /// <param name="text">警告信息</param> /// <param name="ex">产生警告的异常</param> private void MessageWarning(string text, System.Exception ex) { System.Windows.Forms.MessageBox.Show(text + "/n/n错误信息:/n" + ex.Message + "/n堆栈跟踪:" + ex.StackTrace + "/n错误来源:" + ex.Source, "Excel操作组件", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning); }
/** <summary> /// 字母转换为数字,Excel列头,如A-1;AA-27 /// </summary> /// <param name="strLetter">字母</param> /// <returns>字母对应的数字</returns> private int LtoN(string strLetter) { int intRtn = 0;
string strLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
if (strLetter.Length == 2) intRtn += (strLetters.IndexOf(strLetter.Substring(0, 1)) + 1) * 26;
intRtn += strLetters.IndexOf(strLetter.Substring(strLetter.Length - 1, 1)) + 1;
return intRtn;
}
/** <summary> /// 数字转换为字母,Excel列头,如1-A;27-AA /// </summary> /// <param name="intNumber">数字</param> /// <returns>数字对应的字母</returns> private 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); strRtn += strLetters.Substring((intNumber % 26) - 1, 1);
return strRtn; }
#endregion 辅助函数
} }