Has anybody figured out how to parse an excel file with the new sandbox?

Kinetic ERP
1

So I know this is like the bible on how to do this, but, its using the old method, and its csv not excel:

I figured out how to pick up the file and read it using the new sandbox methods which is all well and good. But I can’t figure out a way for it to process an excel file, seems like the only option is csv.

Since I am in the cloud, I can’t install any packages to enable this functionality. I am curious if anybody else has figured this out?

Or if Epicor intends to release any helper libraries that would enable this?

Or what about a an application studio tool to “import from excel”? You could feed it the file location via the sandbox file path, and the corresponding dataview to dump it to, and then it would just, do it? I know I know, way too much to hope for.

1 Like
2

I’ve probably got it covered, I just need a few moments to sit down and work.

2 Likes
3

OpenXML comes prepackaged with Kinetic :tada: :see_no_evil:

Necessary using statements:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

Here’s a quick snip of code I use to grab a file and open it for editing:

// Get template file
CallService<Ice.Contracts.FileTransferSvcContract>(fileTransfer =>
  {
    fileBytes = ((Ice.Contracts.FileTransferSvcContract)fileTransfer).DownloadFile(Epicor.ServiceModel.Utilities.SpecialFolder.CompanyData, $"{folder}\\{fileName}");
  });
        
using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream())
{
  // Make an expandable memorystream
  memoryStream.Write(fileBytes, 0, (int)fileBytes.Length);
  
  // Open the document for editing.
  using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(memoryStream, true))
  {
    // Access the main Workbook part, which contains all references.
    WorkbookPart workbookPart = spreadSheet.WorkbookPart;
    // get sheet by name
    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();
    
    // get worksheetpart by sheet id
    WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;
    
    // The SheetData object will contain all the data.
    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

Adapted from Server file access from function on cloud - #9 by klincecum … and someone else on Stack Exchange that I can’t find…

3 Likes
4

That’s basically what I was gonna do.

Now we gotta sandbox it up.

1 Like
5
1 Like