【C#】常用代码 EPPLUS

最新免费的版本是v4.5.3.3, VS NuGet安装最新的v5+的版本不是免费的

1. 文件新建写入和文件打开无法保存的处理

   void NewListFile(string fileName)
   {
       try
       {
           ExcelPackage package = new ExcelPackage(new FileInfo(fileName));
           ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheettest1");//创建worksheet

           // 文件头
           worksheet.InsertRow(1/*开始插入的位置*/, 1/*行数*/);
           worksheet.InsertColumn(1/*开始插入的位置*/, 3/*列数*/);

           worksheet.Column(1).Width = 20;
           worksheet.Cells[1, 1].Value = "sn";

           worksheet.Column(2).Width = 20;
           worksheet.Cells[1, 2].Value = "iccid";

           worksheet.Column(3).Width = 20;
           worksheet.Cells[1, 3].Value = "name";

           try
           {
               package.Save();//保存excel
           }
           catch (System.InvalidOperationException)
           {
               MessageBox.Show("文件已被打开,请关闭后点击[确定]");
               bool fileIsNotClosed = true;
               while (fileIsNotClosed)
               {
                   try
                   {
                       package.Save();//保存excel
                   }
                   catch (System.InvalidOperationException)
                   {
                       MessageBox.Show("文件已被打开,请关闭后点击[确定]");
                       continue;
                   }
                   fileIsNotClosed = false;
               }
           }
       }
       catch (Exception e)
       {
           MessageBox.Show($"NewListFile: {e.ToString()}");
       }
   }

2. 继续写入excel文件

  void ListFileAddOneItem(string fileName, string item)
  {

      try
      {
          ExcelPackage package = new ExcelPackage(new FileInfo(fileName));
          ExcelWorksheet worksheet = package.Workbook.Worksheets[1];

          int row = worksheet.Dimension.End.Row;

          worksheet.InsertRow(row+1, 1);

          worksheet.Cells[row+1, 1].Value = item;

          try
          {
              package.Save();//保存excel
          }
          catch (System.InvalidOperationException)
          {
              MessageBox.Show("文件已被打开,请关闭后点击[确定]");
              bool fileIsNotClosed = true;
              while (fileIsNotClosed)
              {
                  try
                  {
                      package.Save();//保存excel
                  }
                  catch (System.InvalidOperationException)
                  {
                      MessageBox.Show("文件已被打开,请关闭后点击[确定]");
                      continue;
                  }
                  fileIsNotClosed = false;
              }
          }
      }
      catch (Exception e)
      {
          MessageBox.Show($"ListFileAddOneItem: {e.ToString()}");
      }

  }