EPPlusExcelHelper

Epplus方法

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

namespace Deleterow
{
    public class EPPlusExcelHelper : IDisposable
    {
        public ExcelPackage ExcelPackage { get; private set; }
        private Stream fs;

        public EPPlusExcelHelper(string filePath)
        {
            if (File.Exists(filePath))
            {
                var file = new FileInfo(filePath);
                ExcelPackage = new ExcelPackage(file);
            }
            else
            {
                fs = File.Create(filePath);
                ExcelPackage = new ExcelPackage(fs);

            }
        }
        /// <summary>
        /// 仅在新文件保留想要的sheet
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="filehead">路径</param>
        /// <param name="fileend">文件后缀</param>
        /// <param name="deleteStrength">需要强制删掉的sheet</param>
        /// <param name="excelPackage"></param>
        /// <param name="excelWorkbook"></param>
        public void DeleteSheet(string tableName, string filehead, string fileend, string deleteStrength, ExcelPackage excelPackage, ExcelWorkbook excelWorkbook)
        {
            foreach (var worksheetItem in excelWorkbook.Worksheets)
            {
                //比较sheet名，不是则删除
              //比较sheet名，不是则删除
                    if (worksheetItem.Name != tableName)
                    {
                        excelPackage.Workbook.Worksheets.Delete(worksheetItem);
                    }
                      //var sheet = excelPackage.Workbook.Worksheets.FirstOrDefault(i => i.Name == deleteStrength);
                   // excelPackage.Workbook.Worksheets.Delete(sheet);
                
                string newpath = filehead + tableName + fileend;
                FileInfo newfile = new FileInfo(newpath);
                excelPackage.SaveAs(newfile);
            }
        }
        public void DeleteSheet(string tableName, string filepath, string deleteStrength, ExcelPackage excelPackage, ExcelWorkbook excelWorkbook)
        {
            //foreach (var worksheetItem in excelWorkbook.Worksheets)
            //{
            //    //比较sheet名，不是则删除
            //    if (worksheetItem.Name != tableName)
            //    {
            //        excelPackage.Workbook.Worksheets.Delete(worksheetItem);
            //    }



            //}
            foreach (var worksheetItem in excelWorkbook.Worksheets)
            {
                for (int i = 0; i < excelWorkbook.Worksheets.Count; i++)
                { //比较sheet名，不是则删除
                    if (worksheetItem.Name != excelWorkbook.Worksheets[i].Name)
                    {
                        excelPackage.Workbook.Worksheets.Delete(worksheetItem);
                    }
                    //    var sheet = excelPackage.Workbook.Worksheets.FirstOrDefault(i => i.Name == deleteStrength);
                    //    excelPackage.Workbook.Worksheets.Delete(sheet);
                }

                FileInfo newfile = new FileInfo(filepath);
                excelPackage.SaveAs(newfile);
            }
        }
        /// <summary>
        /// 获得表名并且加到新表中
        /// </summary>
        /// <param name="excelPackage"></param>
        /// <param name="excelWorkbook"></param>
        public void GetSheetName(ExcelPackage excelPackage, ExcelWorkbook excelWorkbook)
        {
            foreach (var worksheetItem in excelWorkbook.Worksheets)
            {
                string n = worksheetItem.Name;

                excelPackage.Workbook.Worksheets.Add(n);
                ExcelWorksheet saveExcelSheet = excelPackage.Workbook.Worksheets[n];
            }
        }
     /// <summary>
     /// 将获得的sheet名字放到新的文件中并保存
     /// </summary>
     /// <param name="filehead">路径</param>
     /// <param name="fileend">文件后缀</param>
     /// <param name="excelPackage"></param>
     /// <param name="excelWorkbook"></param>
        public void LetNameInSheet(string filehead, string fileend, ExcelPackage excelPackage, ExcelWorkbook excelWorkbook)
        {
            foreach (var worksheetItem in excelWorkbook.Worksheets)
            {

                string filename = worksheetItem.Name;
                string newpath = filehead + filename + fileend;
                if (File.Exists(newpath) == true)
                {
                    File.Delete(newpath);
                    Console.WriteLine("File Deleted Successfully");
                    Console.ReadKey();
                }

                FileInfo newfile = new FileInfo(newpath);
                excelPackage.SaveAs(newfile);

            }
        }
            public void Dispose()
        {
            ExcelPackage.Dispose();
            if (fs != null)
            {
                fs.Dispose();
                fs.Close();
            }
        }
    }
}

主程序示例

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

namespace Deleterow
{
    class Program
    {
        static void Main(string[] args)
        {
            var tableName = "A";
            var valueToSearch = tableName + "K";
            var valueToReplace = "";

           
            var filehead = @"D:\武天设计资料\11 互提资料单\测量\测量提\互通服务区中桩断面6.27\华严服务区中桩断面数据\";
            var fileend = @"断面.csv";
            var filepath = filehead+tableName+ fileend;

           var deleteStrength = "C";
          FileInfo file = new FileInfo(filepath);

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//获取许可


            using (ExcelPackage excelPackage = new ExcelPackage(file))
            {
                ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets[tableName];


                //var query = from cell in excelWorksheet.Cells["A:XFD"]
                //            where cell.Value?.ToString().Contains(valueToSearch) == true
                //            select cell;

                //foreach (var cell in query)
                //{
                //    cell.Value = cell.Value.ToString().Replace(valueToSearch, valueToReplace);
                //    // cell.Value = cell.Value.ToString().Replace(valueToSearch1, valueToReplace);
                //}
                //excelPackage.Save(); 

                ExcelWorkbook excelWorkbook = excelPackage.Workbook;
                EPPlusExcelHelper plusExcelHelper = new EPPlusExcelHelper(filepath);
              //plusExcelHelper.LetNameInSheet(filehead, fileend, excelPackage, excelWorkbook);
                
             plusExcelHelper.DeleteSheet(tableName, filehead, fileend,deleteStrength, excelPackage, excelWorkbook);
                //plusExcelHelper.GetSheetName(excelPackage, excelWorkbook);
               
            }

        }

    }
        }