Excel操作样例

Excel直接操作

Excel操作类

ExcelOper.cs文件内容：

using System;
using Microsoft.Office.Interop.Excel;
using System.Collections;
using System.Data.OleDb;
using System.Data;
using System.Reflection;
using System.Collections.Generic;

namespace ExcelOper
{
    class ExcelOper
    {
        Application app;
        Workbooks wbks;
        _Workbook currentWb;
        Worksheet currentWS;
        List<String> sheetsName = new List<string>();
        string pathPrefix = @"F:\ExcelOper\ExcelOper\bin\Debug\";
        string filePath;
        public List<string> sheetFilter = new List<string>();
        public ExcelOper(string path)
        {
            //Console.WriteLine(System.Environment.CurrentDirectory);
            filePath = pathPrefix +path;
        }
        private ArrayList GetExcelSheetName(OleDbConnection Conn)
        {
            ArrayList result = new ArrayList();
            System.Data.DataTable sheetNames = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            foreach (DataRow dr in sheetNames.Rows)
            {
                result.Add(dr[2]);
            }
            return result;
        }
        public void Create()//创建一个Microsoft.Office.Interop.Excel对象
        {
            app = new Application();
            wbks = app.Workbooks;
            currentWb = wbks.Add(true);
        }
        public void Open2()
        {
            object missing = Missing.Value;
            app = new Application();
            app.AlertBeforeOverwriting = false;
            wbks = app.Workbooks;
            wbks.Open(filePath,missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            sheetsName.Add("HeadInit");
            for (int i = 0; i < wbks[1].Worksheets.Count; i++)
            {
                currentWS = (Worksheet)wbks[1].Worksheets[i + 1];
                //Console.WriteLine(currentWS.Name);
                sheetsName.Add(currentWS.Name);
            }
        }
        public void Open()//打开一个Microsoft.Office.Interop.Excel文件
        {
            app = new Application();
            app.AlertBeforeOverwriting = false;
            //app.DisplayAlerts = false;
            wbks = app.Workbooks;
            currentWb = wbks.Add(filePath);
            sheetsName.Add("HeadInit");

            for (int i = 0; i < currentWb.Sheets.Count; i++)
            {
                //Console.WriteLine(currentWb.Worksheets[i + 1].Name);
                sheetsName.Add(currentWb.Worksheets[i + 1].Name);
            }
        }
        public void GetColAndRow()
        {
            int row = 10;
            int col = 10; 
            currentWS = currentWb.Worksheets[3];
            for (int i = 0; i < row; i++)
            {
                for (int j = 0; j < col; j++)
                {
                    Console.Write(currentWS.Cells[i+1,j+1].Text+" ");
                }
                Console.WriteLine();
            }
        }
        public void DelRowByFilter(int sheetNum,int row,int col)
        {
            Console.WriteLine(sheetsName[sheetNum] + ":");
            currentWS = currentWb.Worksheets[sheetNum];
            string currentCellText = currentWS.Cells[row, col].Text;
            while (!currentCellText.Equals("合计") && !string.IsNullOrEmpty(currentCellText))
            {
                //Console.WriteLine(currentCellText);
                if (!IfSheetFilterContain(currentCellText))
                {
                    DelSheetRow(sheetNum,row);
                    Console.WriteLine(row);
                }
                
                currentCellText = currentWS.Cells[++row, col].Text;
            }
        }
        public void DelRowByFilterOnce(int sheetNum, int row, int col)
        {
            Console.WriteLine(sheetsName[sheetNum] + ":");
            currentWS = currentWb.Worksheets[sheetNum];
            string currentCellText = currentWS.Cells[row, col].Text;
            while (!currentCellText.Equals("合计") && !string.IsNullOrEmpty(currentCellText))
            {
                //Console.WriteLine(currentCellText);
                if (!IfSheetFilterContain(currentCellText))
                {
                    DelSheetRow(sheetNum, row);
                    Console.WriteLine(row);
                    currentCellText = currentWS.Cells[row, col].Text;
                }else
                {
                    currentCellText = currentWS.Cells[++row, col].Text;
                }

            }
        }
        public bool IfSheetFilterContain(string target)
        {
            for (int i = 0; i < sheetFilter.Count; i++)
            {
                if (sheetFilter[i].Equals(target))
                {
                    return true;
                }
            }
            return false;
        }
        public void DelSheetRow(int sheetNum,int row)
        {
            //Console.WriteLine(sheetsName[sheetNum] + ":");
            currentWS = currentWb.Worksheets[sheetNum];
            ((Range)currentWS.Rows[row, Missing.Value]).Delete(XlDeleteShiftDirection.xlShiftUp);
        }
        public void Save()
        {
            currentWb.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value);
            //currentWb.SaveAs(filePath, XlSaveAction.xlSaveChanges, false, false, XlSaveAsAccessMode.xlNoChange);
            //currentWb.Save();
        }
        public void GetFilterList()
        {
            for (int i = 3; i < 11; i++)
            {
                GetSheetOneCol(i,3, 2,7);
                
            }
            for (int i = 11; i <= 12; i++)
            {
                
                GetSheetOneCol(i,3, 4,11);
            }
        }
        /// <summary>
        /// 得到某个sheet的某一列
        /// </summary>
        /// <param name="sheetNum">sheet序号</param>
        /// <param name="row">开始行</param>
        /// <param name="col">得到第几列</param>
        public void GetSheetOneCol(int sheetNum,int row,int col,int filterCol)
        {
            Console.WriteLine(sheetsName[sheetNum] + ":");
            currentWS = currentWb.Worksheets[sheetNum];
            string currentCellText = currentWS.Cells[row, col].Text;
            string keyword = "动漫";
            while (!currentCellText.Equals("合计") && !string.IsNullOrEmpty(currentCellText))
            {
                if (currentWS.Cells[row, filterCol].Text.Contains(keyword) || currentWS.Cells[row,filterCol+1].Text.Contains(keyword)) {
                    //Console.WriteLine(currentCellText);
                    sheetFilter.Add(currentCellText);
                }
                currentCellText = currentWS.Cells[++row, col].Text;
            }
        }
        public Worksheet GetSheet(string SheetName)
        {
           return (Worksheet)currentWb.Worksheets[SheetName];
        }
        public Worksheet AddSheet(string SheetName)
        //添加一个工作表
        {
            Worksheet s = (Worksheet)currentWb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            s.Name = SheetName;
            return s;
        }
        public void Close()
        {
            //wb.Save();
            //currentWb.Close(Type.Missing, Type.Missing, Type.Missing);
            wbks.Close();
            app.Quit();
            wbks = null;
            app = null;
            GC.Collect();
        }

    }
}

主程序运行

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelOper
{
    class Program
    {
        static void Main(string[] args)
        {
            ExcelOper standard = new ExcelOper("standard.xls");
            standard.Open();
            standard.GetFilterList();
            standard.Close();

            ExcelOper temp = new ExcelOper("2016b.xlsx");
            temp.Open();
            temp.sheetFilter = standard.sheetFilter;
            //temp.DelRowByFilter(standard.sheetFilter, 2, 4, 1);
            temp.DelRowByFilterOnce(1, 1, 1);
            //temp.DelSheetRow(1, 6);
            //temp.Save();
            temp.Close();
        }
    }
}