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

Kinetic ERP
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