Tip: How to use Excel Sheets without an Installed Excel, but via EPPlus Library

For some time now, I have been wondering if it is possible to use Excel Worksheets without an installed Excel with UiPath. While considering this question, I found the library EPPlus. With EPPlus is it possible to use Excel spreadsheets with dotNET, without the need for an Excel installation to be available. The code of the library is freely available at GitHub and it can be free used for non commercial purposes.

The first step is to download the library from the Nuget repository. Download the necessary dependencies Microsoft.IO.RecyclableMemoryStream and System.ComponentModel.Annotations. Install the EPPlus library via Manage Packages into your project.

image

Now it is possible to use EPPlus inside the Invoke Code activity. Here an example from the UiPath Christmas challenge. There the data is copied from the Excel Worksheet into a DataTable.

//-Begin----------------------------------------------------------------

string ret = string.Empty;
string Dir = @"C:\Dummy";

DataTable WishList = new DataTable();
WishList.Columns.Add("Kid");
WishList.Columns.Add("Wish");

try {

  string[] Files = Directory.GetFiles(Dir, "*.xlsx");
  ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
  foreach(string File in Files) {
    FileInfo fi = new FileInfo(File);
    ExcelPackage Excel = new ExcelPackage(fi);
    ExcelWorksheet Christmas = Excel.Workbook.Worksheets["Christmas"];
    for (int row = 2; row < 99; row++) {
      if(Christmas.Cells[row, 1].Value != null) {
        WishList.Rows.Add(new object[] { 
          Christmas.Cells[row, 1].Value, 
          Christmas.Cells[row, 2].Value
        });
      }
    }
    Christmas.Dispose();
    Excel.Dispose();
  }

  WishList.DefaultView.Sort = "Kid ASC";
  WishList = WishList.DefaultView.ToTable(/*distinct*/ true);

  foreach(DataRow row in WishList.Rows) {
    Console.WriteLine("Kid " + row["Kid"] + " wish " + row["Wish"]);
    ret += "Kid " + row["Kid"] + " wish " + row["Wish"];
  }

} catch(Exception ex) {
  Console.WriteLine(ex.Message);
}

//-End------------------------------------------------------------------

It is necessary to add a dummy variable from the ExcelPackage, to be able to execute the code without errors.

image

EPPlus is an interesting library with interesting possibilities.

Additional links:

6 Likes