C#操作Excel文档

参考了网上查找的一些资料,整理了一下工作所需的C#Excel文件操作,仅针对数据操作,而不针对Excel表样式操作
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace ClassLibraryCollection
{
    public class ExcelOperateClass
    {
        //创建Application
        Excel.Application excelApp = new Excel.Application();
        Excel.Worksheet ActiveSheet = new Worksheet();
        Excel.Workbook xlsWorkBook = null;
        public ExcelOperateClass()
        {
            //设置程序运行语言
            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            //设置是否显示警告窗体
            excelApp.DisplayAlerts = false;
            //设置是否显示Excel
            excelApp.Visible = false;
            //禁止刷新屏幕
            excelApp.ScreenUpdating = false;
        }

        //根据路径打开,返回workbook
        public Excel.Workbook OpenExcelBybook(string OpenPath)
        {
            xlsWorkBook = excelApp.Workbooks.Open(OpenPath, System.Type.Missing, System.Type.Missing, System.Type.Missing,
            System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
            System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
            return xlsWorkBook;
        }

        //打开Excel,并返回第一个sheet
        public Excel.Worksheet OpenExcelBysheet(string OpenPath)
        {
            xlsWorkBook = excelApp.Workbooks.Open(OpenPath, System.Type.Missing, System.Type.Missing, System.Type.Missing,
            System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
            System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
            ActiveSheet = (Worksheet)xlsWorkBook.Sheets.get_Item(1);
            return ActiveSheet;
        }

        //打开Excel,并返回指定的sheet

        public Excel.Worksheet OpenExcelBysheet(string OpenPath, int CNum)
        {
            xlsWorkBook = excelApp.Workbooks.Open(OpenPath, System.Type.Missing, System.Type.Missing, System.Type.Missing,
            System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
            System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
            ActiveSheet = (Worksheet)xlsWorkBook.Sheets.get_Item(CNum);
            return ActiveSheet;
        }

        //在第一列的左边插入一列
        public void InsertLeftCol()
        {
            Excel.Range xlsColumns = (Excel.Range)ActiveSheet.Columns[1, System.Type.Missing];
            // xlsColumns.Insert(XlInsertShiftDirection.xlShiftToRight, Type.Missing);
            //xlsSheetTemplateMajor_Meisai.Cells.get_Range(xlsSheetTemplateMajor_Meisai.Cells[1, 1], xlsSheetTemplateMajor_Meisai.Cells[65535, 1]).Insert(Type.Missing, Type.Missing);
            Excel.Range rng;
            rng = ActiveSheet.get_Range("A:A", "A:A");
            rng.Insert(Excel.XlDirection.xlToRight, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            rng = (Microsoft.Office.Interop.Excel.Range)ActiveSheet.Columns[12, Type.Missing];
            rng.Insert(XlInsertShiftDirection.xlShiftToRight, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
        }

        //删除行,输入参数为第几行
        public void DeletRow(int x)
        {
            Range deleteRng = (Range)ActiveSheet.Rows[x, System.Type.Missing];
            deleteRng.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
        }

        //删除一列数据,输入参数为第几列

        public void DeletCol(int y)
        {
            //((Microsoft.Office.Interop.Excel.Range)ActiveSheet.Cells[1, 11]).Select();
            //((Microsoft.Office.Interop.Excel.Range)ActiveSheet.Cells[1, 11]).EntireColumn.Delete(0);
            ((Excel.Range)ActiveSheet.Cells[1, y]).EntireColumn.Delete(0);
        }

        //设置Form属性
        // 1、设置文本格式,保留数据中的前置0,比如 00069
        //传入参数RanStar为开始单元格,RanEnd为结束单元格
        public void SetRangeToText(string RanStar, string RanEnd)
        {
            Microsoft.Office.Interop.Excel.Range range1 = ActiveSheet.get_Range(RanStar, RanEnd);
            range1.NumberFormat = "@";//文本格式
        }

        //替换 //设定的区域范围只能是某一行或者某一列
        public void ReplaceData(string RangeStar, string RangeEnd, string NeedRepla, string Repla)
        {
            Range Drng = ActiveSheet.get_Range(RangeStar, RangeEnd);
            Drng.Replace(NeedRepla, Repla, XlLookAt.xlPart, XlSearchOrder.xlByColumns, System.Type.Missing,
                System.Type.Missing, System.Type.Missing, System.Type.Missing);
            Drng.TextToColumns(Drng, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierSingleQuote,
                System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
                System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
                System.Type.Missing);
        }

        //查找,输入查找起点和终点和需查找的字符串,返回查找到字符串位置的Range
        public Excel.Range FindData(string RanStae, string RanEnd, string FindStr)
        {
            Range rng = ActiveSheet.get_Range(RanStae, RanEnd).Find(FindStr, Type.Missing,
                        Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlWhole,
                        Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
                        Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);
            return rng;
        }

        //另存为
        public void SaveAs(string SavePath)
        {
            xlsWorkBook.SaveAs(SavePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        }

        //关闭打开的Excel方法
        public void CloseExcel()
        {
            xlsWorkBook.Close(false, Type.Missing, Type.Missing);
            xlsWorkBook = null;
            excelApp.Quit();
            GC.Collect();
            KeyMyExcelProcess.Kill(excelApp);
        }

        /// <summary>
        /// 关闭Excel进程
        /// </summary>
        public class KeyMyExcelProcess
        {
            [DllImport("User32.dll", CharSet = CharSet.Auto)]
            public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
            public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
            {
                try
                {
                    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
                }
                catch (System.Exception ex)
                {
                    throw ex;
                }
            }
        }

        //原路径保存
        public void Save()
        {
            xlsWorkBook.Save();
        }
    }
}
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值