You are viewing limited content. For full access, please sign in.

Question

Question

Using Form Data to Populate an Excel Spreadsheet

asked on May 26, 2020 Show version history

Hi, everyone!

I know we can use Workflow to populate a fillable PDF with Form data.

Is there any way to populate the data to Excel?  We have to submit account requests to a business partner using their specific request form, which they built in Excel. I've attached a snip - we would need "First Name" to populate field D16, "Last Name" to populate field D17, etc.

Hoping you can point me in the right direction - thank you!

 

screensnip.JPG
screensnip.JPG (54.93 KB)
0 0

Answer

SELECTED ANSWER
replied on May 27, 2020 Show version history

You could use a script in workflow with a.NET library like NPOI (free)

A .NET library for reading and writing Microsoft Office binary and OOXML file formats.

https://github.com/dotnetcore/NPOI

https://poi.apache.org/components/spreadsheet/quick-guide.html

If you want a prebuilt version:https://intranet.groupeaa.com/techniciens/inno/NPOI.zip

To install it, unzip the zip file on your workflow server to c:\NPOI

Open cmd.exe as administrator

cd c:\NPOI

gacutil.exe -i ICSharpCode.SharpZipLib.dll

gacutil.exe -i NPOI.dll

gacutil.exe -i NPOI.OOXML.dll

gacutil.exe -i NPOI.OpenXml4Net.dll

gacutil.exe -i NPOI.OpenXmlFormats.dll

 

Add a "SDK Script" activity to your workflow

Add the reference to NPOI and Laserfiche.DocumentServices (to import and export document)

 

Sample script to read an excel file(the document you are running the script on in laserfiche must be an excel file):

namespace WorkflowActivity.Scripting.SDKScript
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using System.IO;
    using Laserfiche.RepositoryAccess;
    using Laserfiche.DocumentServices;
    using NPOI.XSSF.UserModel;
    using NPOI.SS.UserModel;

    /// <summary>
    /// Provides one or more methods that can be run when the workflow scripting activity is performed.
    /// </summary>
    public class Script1 : RAScriptClass104
    {
        /// <summary>
        /// This method is run when the activity is performed.
        /// </summary>
        protected override void Execute()
        {
            DocumentExporter DE = new DocumentExporter();
            DocumentInfo DI = (DocumentInfo)BoundEntryInfo;
            MemoryStream MS = new MemoryStream();
            DE.ExportElecDoc(DI,MS);
            XSSFWorkbook WB;
            try
            {
                MS.Seek(0, SeekOrigin.Begin);
                WB = new XSSFWorkbook(MS);
                WorkflowApi.TrackInformation(WB.Count.ToString());
                ISheet SH = WB.GetSheet("Sheet1");
                for (int row = 0; row <= SH.LastRowNum; row++)
                {
                    if (SH.GetRow(row) != null) //null is when the row only contains empty cells
                    {
                        WorkflowApi.TrackInformation(string.Format("Row {0} = {1}", row, SH.GetRow(row).GetCell(0).StringCellValue));
                    }
                }
            }
            catch (Exception e)
            {
                throw(e);
            }

        }
    }
}

Sample to read and write to an excel document:

namespace WorkflowActivity.Scripting.SDKScript
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using System.IO;
    using Laserfiche.RepositoryAccess;
    using Laserfiche.DocumentServices;
    using NPOI.XSSF.UserModel;
    using NPOI.SS.UserModel;

    /// <summary>
    /// Provides one or more methods that can be run when the workflow scripting activity is performed.
    /// </summary>
    public class Script1 : RAScriptClass104
    {
        /// <summary>
        /// This method is run when the activity is performed.
        /// </summary>
        protected override void Execute()
        {
            try
            {
                DocumentExporter DE = new DocumentExporter();
                DocumentInfo DI = (DocumentInfo)BoundEntryInfo;
                MemoryStream MS = new MemoryStream();
                MemoryStream MSOUT = new MemoryStream();
                DE.ExportElecDoc(DI,MS);
                MS.Seek(0, SeekOrigin.Begin);
                XSSFWorkbook WB;
                WB = new XSSFWorkbook(MS);
                WorkflowApi.TrackInformation(WB.Count.ToString());
                ISheet SH = WB.GetSheet("Sheet1");
                for (int row = 0; row <= SH.LastRowNum; row++)
                {
                    if (SH.GetRow(row) != null) //null is when the row only contains empty cells
                    {
                        //sample code
                        string mytoken = (string)WorkflowApi.GetTokenValueFromName("Mytoken"); //retreive token value from workflow
                        SH.GetRow(row).GetCell(0).SetCellValue(1.2);//numeric
                        WorkflowApi.TrackInformation(string.Format("Row {0} = {1}", row, SH.GetRow(row).GetCell(0).NumericCellValue.ToString()));
                        SH.GetRow(row).GetCell(1).SetCellValue("This is a string"); //text
                        WorkflowApi.TrackInformation(string.Format("Row {0} = {1}", row, SH.GetRow(row).GetCell(1).StringCellValue));
                        SH.GetRow(row).GetCell(2).SetCellValue(mytoken); //text from token
                        WorkflowApi.TrackInformation(string.Format("Row {0} = {1}", row, SH.GetRow(row).GetCell(2).StringCellValue));
                        SH.GetRow(row).GetCell(3).SetCellValue(true); //true or false
                        WorkflowApi.TrackInformation(string.Format("Row {0} = {1}", row, SH.GetRow(row).GetCell(3).BooleanCellValue.ToString()));
                    }
                }
               
                WB.Write(MSOUT);
                MSOUT.Seek(0, SeekOrigin.Begin);
                DocumentImporter Importer = new DocumentImporter();
                Importer.Document = DI;   
                string contentType = DI.MimeType.ToString();
                string extension = DI.Extension;
                DI.DeleteEdoc();
                
                Importer.ImportEdoc(contentType,MSOUT);
                DI.Extension = extension;
                DI.Save();
            }
            catch (Exception e)
            {
                throw(e);
            }

        }
    }
}

 

2 0

Replies

replied on May 27, 2020

Simon is correct, in order to do this you will want to create a script that will take your data and write it to an Excel file in the format you need. There is no built in activity to achieve this in the same manner as the Fill Out PDF Form activity.

 

We do support reading and writing to excel files via an ODBC driver for use as a data source, but this would not be sufficient for creating individual request forms.

1 0
replied on May 27, 2020

Thank you both for your replies! I was hoping this would be less complicated, but it's good to know it is possible. I appreciate you taking the time to include so much detail.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.