c# 操作Excel

基本操作類

 public class ExcelUtil    {               public void ReleaseCOM(object pObj)//釋放資源        {            try            {                System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);            }            catch            {                throw new Exception("釋放資源時發生錯誤！");            }            finally            {                pObj = null;            }        }        public ExcelUtil(string filePath)        {            this.filePath = filePath;            app = new Microsoft.Office.Interop.Excel.application();//打開一個Excel應用            if (app == null)            {                return;            }            wbs = app.Workbooks;            wb = wbs.Add(filePath);            shs = wb.Sheets;            int sheetNumber = shs.Count;            for (int i = 1; i <= sheetNumber; i++)            {                _Worksheet sh = (_Worksheet)shs.get_Item(i);                this.ShList.Add(sh);            }        }        /// <summary>        /// 保存文件        /// </summary>        public ExcelUtil(string filePath, int addSheet)        {            this.filePath = filePath;            app = new Microsoft.Office.Interop.Excel.Application();//打開一個Excel應用            if (app == null)            {                return;            }            wbs = app.Workbooks;            wb = wbs.Add(filePath);            shs = wb.Sheets;            int sheetNumber = shs.Count;            int addSheetCount = addSheet - sheetNumber;            if (addSheetCount > 0)            {                for (int i = 0; i < addSheetCount; i++)                {                    var sheet = (_Worksheet)shs.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);                }            }            int currentSheetNumber = shs.Count;            for (int i = 1; i <= currentSheetNumber; i++)            {                _Worksheet sh = (_Worksheet)shs.get_Item(i);                this.ShList.Add(sh);            }        }        /// <summary>        /// 保存文件        /// </summary>        public void save()        {            if (filePath != null)            {                FileInfo old = new FileInfo(filePath);                if (old.Exists)                {                    File.Delete(filePath);                }                wb.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsaccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);                foreach (_Worksheet inst in shList)                {                    ReleaseCOM(inst);                }                ReleaseCOM(shs);                ReleaseCOM(wb);                ReleaseCOM(wbs);                app.Quit();                ReleaseCOM(app);            }        }        /// <summary>        /// 殺掉進程的方式保存excel。        /// 用來解決正常保存時出現的公式異常問題。        /// </summary>        public void saveByKillPRocess()        {            if (filePath != null)            {                try                {                    FileInfo old = new FileInfo(filePath);                    if (old.Exists)                    {                        File.Delete(filePath);                    }                    wb.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);                    foreach (_Worksheet inst in shList)                    {                        ReleaseCOM(inst);                    }                    ReleaseCOM(shs);                    ReleaseCOM(wb);                    ReleaseCOM(wbs);                    KillExcel(app);                }                catch (System.Exception e)                {                    logger.Debug(e.Message);                }            }        }        /// <summary>        /// 合并Excel單元格        /// </summary>        /// <param name="ws">sheet頁</param>        /// <param name="str">要合并單元格的左上角的單元格列號A</param>        /// <param name="i">要合并的單元格的左上角的單元格行號2</param>        /// <param name="str3">要合并單元格的右下角的單元格列號B</param>        /// <param name="i">要合并的單元格的右下角的單元格行號2</param>        /// <param name="j">表格最后一行的行號</param>        /// <returns>單元格</returns>        public static Range CombineExcel(_Worksheet ws, int colLeft, int rowUp, int colRight, int rowDown)        {            try            {                string str1 = GetColStrFromInt(colLeft) + rowUp;                string str2 = GetColStrFromInt(colRight) + rowDown;                Range range = ws.get_Range(str1, str2);                range.Merge(0);                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;                range.Font.Underline = false;                range.WrapText = true;                //  range.EntireColumn.AutoFit();                range.Borders.LineStyle = 1;                return range;            }            catch (Exception ex)            {                logger.Error(ex.Message);                return null;            }        }        /// <summary>        /// 傳入列號得到對應的列名稱，從1開始，1代表第A列        /// </summary>        /// <param name="col">列號</param>        /// <returns></returns>        public static string GetColStrFromInt(int col)        {            col = col + 'A' - 1;            string colStr = "";            if (col > (int)'Z')            {                colStr = Convert.ToChar((col - 90 - 1) / 26 + 'A').ToString() +                    Convert.ToChar((col - 90 - 1) % 26 + 'A').ToString();                return colStr;            }            else                return Convert.ToChar(col).ToString();        }        [DllImport("User32.dll", CharSet = CharSet.Auto)]        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);        private void KillExcel(Microsoft.Office.Interop.Excel.Application excel)        {            IntPtr t = new IntPtr(excel.Hwnd); //得到這個句柄，具體作用是得到這塊內存入口            int k = 0;            GetWindowThreadProcessId(t, out k); //得到本進程唯一標志k            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到對進程k的引用            p.Kill(); //關閉進程k        }        private Microsoft.Office.Interop.Excel.Application app;        private Workbooks wbs;        private _Workbook wb;        private Sheets shs;        private List<_Worksheet> shList = new List<_Worksheet>();        public List<_Worksheet> ShList        {            get            {                return shList;            }            set            {                shList = value;            }        }        private string filePath;        protected readonly static log4net.ILog logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);    }

得到一個合并range插入數據

 private Range GetCurrentRange(_Worksheet ws, int colLeft, int rowUp, int colRight, int rowDown, string result)        {            Range currentRange = null;            currentRange = ExcelUtil.CombineExcel(ws, colLeft, rowUp, colRight, rowDown);            currentRange.Value2 = result;插入結果            currentRange.Borders.LineStyle = 0;//邊框線currentRange.Font.ColorIndex = 3;//插入顏色currentRange.Font.Bold = true;//加粗            currentRange.ColumnWidth = 100;//控制列寬 currentRange.HorizontalAlignment = XlHAlign.xlHAlignLeft;//文字的位置            currentRange.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//下邊框加粗   var range = ws.Cells;//選中整個文檔            range.Interior.ColorIndex = 2;//填充背景顏色return currentRange;        }

創建超鏈接

/// <summary>        /// Inserts the catalogue.目錄超鏈接        /// </summary>        /// <param name="ws">The ws.</param>        ///