Hi Rick,

Do you have a sample code using EPPlus that you can share with me? I need to dump a SQL select from workflow into a an excel file and this solution looks like the way to go.

Hi Pedro,

Sure thing.  I have a workflow kick off and write a .csv file from tokens in the workflow I gathered, and then I use a script with the EPPlus program to write it to an XLS or XLSX and format it the way I want.  I just used an instance of StreamWriter to write the CSV but if you want a copy of that I will post it as well.  Here is the code to use EPPlus to take the .csv and write it nicely to XLSX format:

namespace WorkflowActivity.Scripting.SDKScript2
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using Laserfiche.RepositoryAccess;
    using Laserfiche.DocumentServices;
    using System.Collections;
    using System.Linq;
    using System.IO;
    using OfficeOpenXml;
    using OfficeOpenXml.Drawing;
    using OfficeOpenXml.Style;
    using System.Drawing;
    using OfficeOpenXml.ConditionalFormatting;

    public class Script1 : RAScriptClass91
    {
      protected override void Execute()
        {
        Session mySess = this.RASession;
        string ent = this.TokenReplace("%(FindEntry_OutputEntry_ID)");
        int entID = int.Parse(ent);
        DocumentInfo docInfo = Document.GetDocumentInfo(entID, mySess);
        StreamReader objReader = new StreamReader("C:\\Users\\svc_laserfiche\\My Documents\\Qualified Plans CSV\\ReportTester.csv");
        string sLine = "";
        ArrayList arrText = new ArrayList();
        while (sLine != null)
          {
             sLine = objReader.ReadLine();
             if (sLine != null)
              arrText.Add(sLine);
          }
        string csvFileName = "C:\\Users\\svc_laserfiche\\My Documents\\Qualified Plans CSV\\ReportTester.csv";
        string excelFileName = "C:\\ProgramData\\Laserfiche\\WF\\ServerData\\$\\0000\\0000\\0000\\TempReportFiles\\007F.1.xlsx";
        string worksheetsName = "Worksheet1";
        bool firstRowIsHeader = true;
        var format = new ExcelTextFormat();
        format.Delimiter = ',';
        format.EOL = "\r";
        using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName))) {
            package.Workbook.Worksheets.Delete("Worksheet1");
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
            worksheet.Cells["A2"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);
            int totalRows = worksheet.Dimension.End.Row;
            int totalCols = worksheet.Dimension.End.Column;
            var headerCells = worksheet.Cells[1,1,1,totalCols];
            var headerFont = headerCells.Style.Font;
            headerFont.Bold = true;
            
                 using (ExcelRange rng = worksheet.Cells["A2:I2"])
                {
                    rng.Style.Font.Bold = true;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;            //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);  //Set color to dark blue
                    rng.Style.Font.Color.SetColor(Color.White);
                 }
            worksheet.Cells[1, 1].Value = "New Hire Status Report";
            worksheet.Cells[1, 1, 1, 9].Merge = true;
            worksheet.Cells[1, 1, 1, 9].Style.Font.Bold = true;
            worksheet.Cells[1, 1, 1, 9].Style.Font.Size = 22;
            worksheet.Cells[1, 1, 1, 9].Style.Font.Color.SetColor(Color.DarkBlue);
            worksheet.Row(1).Height = 35;
            worksheet.Cells[1, 1, 1, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells[3, 1, totalRows, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
            worksheet.Column(2).Style.Numberformat.Format = @"MM/dd/yyyy";
            worksheet.Column(4).Style.Numberformat.Format = @"MM/dd/yyyy";
            worksheet.Column(8).Style.Numberformat.Format = @"MM/dd/yyyy";
            worksheet.Column(9).Style.Numberformat.Format = @"";
            worksheet.Column(7).AutoFit();
            worksheet.Column(2).AutoFit();
            worksheet.Column(1).AutoFit();
            worksheet.Column(8).AutoFit();
            worksheet.Column(6).AutoFit();
            worksheet.Column(3).Width = 28;
            worksheet.Column(3).Style.WrapText = true;
            worksheet.Column(4).Width = 53;
            worksheet.Column(4).Style.WrapText = true;
            worksheet.Column(5).Width = 39;
            worksheet.Column(5).Style.WrapText = true;
            worksheet.Column(9).Width = 10;
            worksheet.Column(9).Style.WrapText = true;
            worksheet.TabColor = Color.Black;
            worksheet.View.FreezePanes(3,10);
            package.Save();
        }
      }
    }
 }

As you will see, it is much easier than using InteropExcel.  You can download it free here:  http://epplus.codeplex.com/

You will want version 4.1.  After that, just reference the EPPLus dll in your script, and then also put in using statements at the top for OfficeOpenXml, OfficeOpenXml.Drawing, and OfficeOpenXml.Style.  Let me know if you would like anything else, glad to help!

Rick

Thanks Rick, I will test it and let you know.

Thanks again.

Hi what references did you include especially when you want to focus on the csv files format only?

Hi Prudence,

I just added System.IO and System.Text in order to use StreamWriter and StringBuilder classes.