<summary> /// 設定連續地區水平置中 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> public void SetHAlignCenter(Excel._Worksheet CurSheet,object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } /// <summary> /// 設定連續地區水平居左 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> public void SetHAlignLeft(Excel._Worksheet CurSheet,object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } /// <summary> /// 設定連續地區水平居右 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> public void SetHAlignRight(Excel._Worksheet CurSheet,object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment=Excel.XlHAlign.xlHAlignRight; } /// <summary> /// 設定連續地區的顯示格式 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> /// <param name="strNF">如"#,##0.00"的顯示格式</param> public void SetNumberFormat(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, string strNF) { CurSheet.get_Range(objStartCell, objEndCell).NumberFormat = strNF; } /// <summary> /// 設定列寬 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="strColID">列標識,如A代表第一列</param> /// <param name="dblWidth">寬度</param> public void SetColumnWidth(Excel._Worksheet CurSheet,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="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> /// <param name="dblWidth">寬度</param> public void SetColumnWidth(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, double dblWidth) { CurSheet.get_Range(objStartCell,objEndCell).ColumnWidth=dblWidth; } /// <summary> /// 設定行高 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> /// <param name="dblHeight">行高</param> public void SetRowHeight(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, double dblHeight) { CurSheet.get_Range(objStartCell,objEndCell).RowHeight=dblHeight; } /// <summary> /// 為儲存格添加超級連結 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objCell">儲存格</param> /// <param name="strAddress">連結地址</param> /// <param name="strTip">工具提示</param> /// <param name="strText">連結文本</param> public void AddHyperLink(Excel._Worksheet CurSheet,object objCell, string strAddress, string strTip, string strText) { CurSheet.Hyperlinks.Add(CurSheet.get_Range(objCell, objCell),strAddress, mValue, strTip, strText); } /// <summary> /// 另存新檔xls檔案 /// </summary> /// <param name="CurBook">Workbook</param> /// <param name="strFilePath">檔案路徑</param> public void Save(Excel._Workbook CurBook,string strFilePath) { CurBook.SaveCopyAs(strFilePath); } /// <summary> /// 儲存檔案 /// </summary> /// <param name="CurBook">Workbook</param> /// <param name="strFilePath">檔案路徑</param> public void SaveAs(Excel._Workbook CurBook,string strFilePath) { CurBook.SaveAs(strFilePath, mValue, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlShared, mValue, mValue, mValue, mValue, mValue); } /// <summary> /// 另存新檔html檔案 /// </summary> /// <param name="CurBook">Workbook</param> /// <param name="strFilePath">檔案路徑</param> public void SaveHtml(Excel._Workbook CurBook,string strFilePath) { CurBook.SaveAs(strFilePath, Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue); } /// <summary> /// 釋放記憶體 /// </summary> public void Dispose(Excel._Worksheet CurSheet,Excel._Workbook CurBook,Excel._Application CurExcel) { 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) { HttpContext.Current.Response.Write( "在釋放Excel記憶體空間時發生了一個錯誤:"+ex); } finally { foreach(System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel")) //if (pro.StartTime < DateTime.Now) pro.Kill(); } System.GC.SuppressFinalize(this); } }
using System;using System.Web;using Excel = Microsoft.Office.Interop.Excel; /// <summary> /// ExcelOperate 的摘要說明。Excel操作函數 /// </summary> public class ExcelOperate { private object mValue = System.Reflection.Missing.Value; public ExcelOperate() { // // TODO: 在此處添加建構函式邏輯 // } /// <summary> /// 合併儲存格 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> public void Merge(Excel._Worksheet CurSheet,object objStartCell,object objEndCell) { CurSheet.get_Range(objStartCell,objEndCell).Merge(0); } /// <summary> /// 設定連續地區的字型大小 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="strStartCell">開始儲存格</param> /// <param name="strEndCell">結束儲存格</param> /// <param name="intFontSize">字型大小</param> public void SetFontSize(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, int intFontSize) { CurSheet.get_Range(objStartCell, objEndCell).Font.Size = intFontSize.ToString(); } /// <summary> /// 橫向列印 /// </summary> /// <param name="CurSheet"></param> public void xlLandscape(Excel._Worksheet CurSheet) { CurSheet.PageSetup.Orientation=Excel.XlPageOrientation.xlLandscape; } /// <summary> /// 縱向列印 /// </summary> /// <param name="CurSheet"></param> public void xlPortrait(Excel._Worksheet CurSheet) { CurSheet.PageSetup.Orientation=Excel.XlPageOrientation.xlPortrait; } /// <summary> /// 在指定儲存格插入指定的值 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="Cell">儲存格 如Cells[1,1]</param> /// <param name="objValue">文本、數字等值</param> public void WriteCell(Excel._Worksheet CurSheet,object objCell, object objValue) { CurSheet.get_Range(objCell, mValue).Value2 = objValue; } /// <summary> /// 在指定Range中插入指定的值 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="StartCell">開始儲存格</param> /// <param name="EndCell">結束儲存格</param> /// <param name="objValue">文本、數字等值</param> public void WriteRange(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, object objValue) { CurSheet.get_Range(objStartCell, objEndCell).Value2 = objValue; } /// <summary> /// 合併儲存格,並在合并後的儲存格中插入指定的值 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> /// <param name="objValue">文本、數字等值</param> public void WriteAfterMerge(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, object objValue) { CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue); CurSheet.get_Range(objStartCell, mValue).Value2 = objValue; } /// <summary> /// 為儲存格設定公式 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objCell">儲存格</param> /// <param name="strFormula">公式</param> public void SetFormula(Excel._Worksheet CurSheet,object objCell, string strFormula) { CurSheet.get_Range(objCell, mValue).Formula = strFormula; } /// <summary> /// 儲存格自動換行 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> public void AutoWrapText(Excel._Worksheet CurSheet,object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell,objEndCell).WrapText=true; } /// <summary> /// 設定整個連續地區的字型顏色 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> /// <param name="clrColor">顏色</param> public void SetColor(Excel._Worksheet CurSheet,object objStartCell, object objEndCell, System.Drawing.Color clrColor) { CurSheet.get_Range(objStartCell, objEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor); } /// <summary> /// 設定整個連續地區的儲存格背景色 /// </summary> /// <param name="CurSheet"></param> /// <param name="objStartCell"></param> /// <param name="objEndCell"></param> /// <param name="clrColor"></param> public void SetBgColor(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor) { CurSheet.get_Range(objStartCell, objEndCell).Interior.Color = System.Drawing.ColorTranslator.ToOle(clrColor); } /// <summary> /// 設定連續地區的字型名稱 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> /// <param name="fontname">字型名稱 隸書、仿宋_GB2312等</param> public void SetFontName(Excel._Worksheet CurSheet,object objStartCell, object objEndCell,string fontname) { CurSheet.get_Range(objStartCell, objEndCell).Font.Name=fontname; } /// <summary> /// 設定連續地區的字型為黑體 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> public void SetBold(Excel._Worksheet CurSheet,object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).Font.Bold = true; } /// <summary> /// 設定連續地區的邊框:上下左右都為黑色連續邊框 /// </summary> /// <param name="CurSheet">Worksheet</param> /// <param name="objStartCell">開始儲存格</param> /// <param name="objEndCell">結束儲存格</param> public void SetBorderAll(Excel._Worksheet CurSheet,object objStartCell, object objEndCell) { CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; }