Dynamics Community Forum Thread Details

Reading and Updating a Excel file using X++ code

Hi, I need to upload a excel file using dialog and i have to read that excel file. After reading, i have to insert some values into that excel file and download that updated excel in some other name. I am able to read my excel but couldn't modify it,i.e i couldn't insert any new values into the sheet. Anybody help me out.This is my code,

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
class Myclassnew
{        
  public static void main(Args _args)
    {
         CustTable custTable;

         MemoryStream            stream;

         ExcelSpreadsheetName        sheeet;

         FileUploadBuild             fileUpload;
  
         DialogGroup                 dlgUploadGroup;

         FileUploadBuild             fileUploadBuild;

         FormBuildControl            formBuildControl;

         Dialog dialog = new Dialog("Import the data from Excel");
            
         dlgUploadGroup = dialog.addGroup("@SYS54759");

         formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());

         fileUploadBuild=formBuildControl.addControlEx(classstr(FileUpload),'Upload');

         fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);

        if (dialog.run() && dialog.closedOk())

        {
           FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId('Upload'));

            FileUploadTemporaryStorageResult fileUploadResult =  fileUploadControl.getFileUploadResult();

 

            if (fileUploadResult != null && fileUploadResult.getUploadStatus())

            {

                stream = fileUploadResult.openResult();

                using (ExcelPackage Package = new ExcelPackage(stream))

                {
                    var currentRow=1;
                    Package.Load(stream);
                    ExcelWorksheet  Worksheets = Package.get_Workbook().get_Worksheets().get_Item(1);
                    OfficeOpenXml.ExcelRange cell = Worksheets.Cells;
                    cell.get_Item(currentRow, 1).Set_Value("Account Number");
                    cell.get_Item(currentRow, 2).Set_Value("Currency");
                     while select custTable
                    {
                        currentRow ++;
                  
                        cell = cell.get_Item(currentRow, 1).Set_Value(CustTable.AccountNum);
                   
                        cell = cell.get_Item(currentRow, 2).Set_Value(CustTable.Currency);
                    
                    }
                package.Save();
                file::SendFileToUser(memoryStream,'downloadedsheet.xlsx');
           }
       }
   }
}

Categories:

Development / Customization / SDK

Quick Links