Using Form Data to Populate an Excel Spreadsheet

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):

01namespace WorkflowActivity.Scripting.SDKScript
02{
03    using System;
04    using System.Collections.Generic;
05    using System.ComponentModel;
06    using System.Data;
07    using System.Data.SqlClient;
08    using System.Text;
09    using System.IO;
10    using Laserfiche.RepositoryAccess;
11    using Laserfiche.DocumentServices;
12    using NPOI.XSSF.UserModel;
13    using NPOI.SS.UserModel;
14 
15    /// <summary>
16    /// Provides one or more methods that can be run when the workflow scripting activity is performed.
17    /// </summary>
18    public class Script1 : RAScriptClass104
19    {
20        /// <summary>
21        /// This method is run when the activity is performed.
22        /// </summary>
23        protected override void Execute()
24        {
25            DocumentExporter DE = new DocumentExporter();
26            DocumentInfo DI = (DocumentInfo)BoundEntryInfo;
27            MemoryStream MS = new MemoryStream();
28            DE.ExportElecDoc(DI,MS);
29            XSSFWorkbook WB;
30            try
31            {
32                MS.Seek(0, SeekOrigin.Begin);
33                WB = new XSSFWorkbook(MS);
34                WorkflowApi.TrackInformation(WB.Count.ToString());
35                ISheet SH = WB.GetSheet("Sheet1");
36                for (int row = 0; row <= SH.LastRowNum; row++)
37                {
38                    if (SH.GetRow(row) != null) //null is when the row only contains empty cells
39                    {
40                        WorkflowApi.TrackInformation(string.Format("Row {0} = {1}", row, SH.GetRow(row).GetCell(0).StringCellValue));
41                    }
42                }
43            }
44            catch (Exception e)
45            {
46                throw(e);
47            }
48 
49        }
50    }
51}

Sample to read and write to an excel document:

01namespace WorkflowActivity.Scripting.SDKScript
02{
03    using System;
04    using System.Collections.Generic;
05    using System.ComponentModel;
06    using System.Data;
07    using System.Data.SqlClient;
08    using System.Text;
09    using System.IO;
10    using Laserfiche.RepositoryAccess;
11    using Laserfiche.DocumentServices;
12    using NPOI.XSSF.UserModel;
13    using NPOI.SS.UserModel;
14 
15    /// <summary>
16    /// Provides one or more methods that can be run when the workflow scripting activity is performed.
17    /// </summary>
18    public class Script1 : RAScriptClass104
19    {
20        /// <summary>
21        /// This method is run when the activity is performed.
22        /// </summary>
23        protected override void Execute()
24        {
25            try
26            {
27                DocumentExporter DE = new DocumentExporter();
28                DocumentInfo DI = (DocumentInfo)BoundEntryInfo;
29                MemoryStream MS = new MemoryStream();
30                MemoryStream MSOUT = new MemoryStream();
31                DE.ExportElecDoc(DI,MS);
32                MS.Seek(0, SeekOrigin.Begin);
33                XSSFWorkbook WB;
34                WB = new XSSFWorkbook(MS);
35                WorkflowApi.TrackInformation(WB.Count.ToString());
36                ISheet SH = WB.GetSheet("Sheet1");
37                for (int row = 0; row <= SH.LastRowNum; row++)
38                {
39                    if (SH.GetRow(row) != null) //null is when the row only contains empty cells
40                    {
41                        //sample code
42                        string mytoken = (string)WorkflowApi.GetTokenValueFromName("Mytoken"); //retreive token value from workflow
43                        SH.GetRow(row).GetCell(0).SetCellValue(1.2);//numeric
44                        WorkflowApi.TrackInformation(string.Format("Row {0} = {1}", row, SH.GetRow(row).GetCell(0).NumericCellValue.ToString()));
45                        SH.GetRow(row).GetCell(1).SetCellValue("This is a string"); //text
46                        WorkflowApi.TrackInformation(string.Format("Row {0} = {1}", row, SH.GetRow(row).GetCell(1).StringCellValue));
47                        SH.GetRow(row).GetCell(2).SetCellValue(mytoken); //text from token
48                        WorkflowApi.TrackInformation(string.Format("Row {0} = {1}", row, SH.GetRow(row).GetCell(2).StringCellValue));
49                        SH.GetRow(row).GetCell(3).SetCellValue(true); //true or false
50                        WorkflowApi.TrackInformation(string.Format("Row {0} = {1}", row, SH.GetRow(row).GetCell(3).BooleanCellValue.ToString()));
51                    }
52                }
53                
54                WB.Write(MSOUT);
55                MSOUT.Seek(0, SeekOrigin.Begin);
56                DocumentImporter Importer = new DocumentImporter();
57                Importer.Document = DI;   
58                string contentType = DI.MimeType.ToString();
59                string extension = DI.Extension;
60                DI.DeleteEdoc();
61                 
62                Importer.ImportEdoc(contentType,MSOUT);
63                DI.Extension = extension;
64                DI.Save();
65            }
66            catch (Exception e)
67            {
68                throw(e);
69            }
70 
71        }
72    }
73}

2 0

Question

Question

Using Form Data to Populate an Excel Spreadsheet

Answer

Replies

Sign in to reply to this post.