I'm trying to use some code that has extension static methods but having a lot of trouble making it adapt to the script editor. Basically it won't let me change 'Execute' to a static method because then it cannot be overridden, but if I leave it as not static it generates an "Extension method must be defined in a non-generic static class". Of course this runs fine in Visual Studio with the Main method because it's static. Is there a way to make this work in the script editor if I cannot have the main method as static, or am I missing something simple. Most of my experience is in Java so C# is not as familiar.
This is a simple program to read an XLSX file and then write it out to a CSV file. I really need this for the workflow, and I found this code on the net which seems to work great outside the SDK so I'm really hoping this can be adapted to the script editor. Sorry if this is an obvious solution, but I wanted to post it here and not keep spinning my wheels if it is. Thanks! Here is the code:
namespace WorkflowActivity.Scripting.Script { using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Text; using System.IO; using OfficeOpenXml; public class Script1 : ScriptClass90 { protected override void Execute() { string newPath = "C:\\Users\\rick.nagy\\Desktop\\Education Assistance Table.xlsx"; ExcelPackage package = new ExcelPackage(new FileInfo(newPath)); string targetFile = "C:\\Users\\rick.nagy\\Desktop\\SheetsForStuff.csv"; Console.WriteLine(); EpplusCsvConverter.ConvertToCsv(package, targetFile); } private static string DuplicateTicksForSql(this string s) { return s.Replace("'", "''"); } public static string ToDelimitedString(this List<string> list, string delimiter = ":", bool insertSpaces = false, string qualifier = "", bool duplicateTicksForSQL = false) { var result = new StringBuilder(); for (int i = 0; i < list.Count; i++) { string initialStr = duplicateTicksForSQL ? list[i].DuplicateTicksForSql() : list[i]; result.Append((qualifier == string.Empty) ? initialStr : string.Format("{1}{0}{1}", initialStr, qualifier)); if (i < list.Count - 1) { result.Append(delimiter); if (insertSpaces) { result.Append(' '); } } } return result.ToString(); } } public static class EpplusCsvConverter { public static void ConvertToCsv(this ExcelPackage package, string targetFile) { var worksheet = package.Workbook.Worksheets[1]; var maxColumnNumber = worksheet.Dimension.End.Column; var currentRow = new List<string>(maxColumnNumber); var totalRowCount = worksheet.Dimension.End.Row; var currentRowNum = 1; using (var writer = new StreamWriter(targetFile, false, Encoding.ASCII)) { while (currentRowNum <= totalRowCount) { BuildRow(worksheet, currentRow, currentRowNum, maxColumnNumber); WriteRecordToFile(currentRow, writer, currentRowNum, totalRowCount); currentRow.Clear(); currentRowNum++; } } } private static void WriteRecordToFile(List<string> record, StreamWriter sw, int rowNumber, int totalRowCount) { var commaDelimitedRecord = record.ToDelimitedString(","); if (rowNumber == totalRowCount) { sw.Write(commaDelimitedRecord); } else { sw.WriteLine(commaDelimitedRecord); } } private static void BuildRow(ExcelWorksheet worksheet, List<string> currentRow, int currentRowNum, int maxColumnNumber) { for (int i = 1; i <= maxColumnNumber; i++) { var cell = worksheet.Cells[currentRowNum, i]; if (cell == null) { AddCellValue(string.Empty, currentRow); } else { AddCellValue(GetCellText(cell), currentRow); } } } private static string GetCellText(ExcelRangeBase cell) { return cell.Value == null ? string.Empty : cell.Value.ToString(); } private static void AddCellValue(string s, List<string> record) { record.Add(string.Format("{0}{1}{0}", '"', s)); } } }