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>        ///