web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to add an addition...
Finance | Project Operations, Human Resources, ...
Unanswered

How to add an additional Excel field to an existing Excel file?

(0) ShareShare
ReportReport
Posted on by .. 1,979

Hi,

There is a standard method does the same thing to add a field to XML, how can i add the same field to Excel?

protected void preProcessCatalogMaintenanceRequestXML()
    {
        CatVendorDateTime uploadDateTime = CatVendorCatalogMaintenanceRequest::findRecId(catVendorCatalogMaintenanceRecId).UploadDateTime;

        XmlDocument xmlDoc = XMLDocument::newFromStream(fileStream);

        XMLElement catalogMaintenanceRequestNode = xmlDoc.documentElement().getNamedElement(tableStr(CatVendorCatalogMaintenanceRequestEntity));
        XMLNode uploadDateTimeNode = xmlDoc.createElement(fieldStr(CatVendorCatalogMaintenanceRequestEntity, UploadDateTime));
       
        if (CatVendorCatalogImportUploadDateTimeFormatFlight::instance().isEnabled())
        {
            uploadDateTimeNode.text(DateTimeUtil::toStr(uploadDateTime));
        }
        else
        {
            uploadDateTimeNode.text(datetime2Str(uploadDateTime));
        }

        catalogMaintenanceRequestNode.appendChild(uploadDateTimeNode);
       
        System.IO.MemoryStream outputStream = new System.IO.MemoryStream();
        ClrObject inputArray = System.Text.Encoding::get_UTF8().GetBytes(xmlDoc.InnerXML());
        outputStream.Write(inputArray, 0, inputArray.get_length());

        fileId = this.uploadFileToTempStorage(outputStream, fullFileName);
    }

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 238,734 Most Valuable Professional on at

    Yes, you can add a text value to an Excel file. You can use OfficeOpenXml.ExcelPackage for this purpose.

    But first, you need to decide how exactly you want to change the file, i.e. where you want to put the value, if you want to add a column header and such things.

  • .. Profile Picture
    .. 1,979 on at

    Hi Martin,

    So the excel file that I uploaded doesn't contain the uploadDateTime..now by code and before importing to the entity, I'm going to add this new coloumn which means new header and new value...can you please give me an example on how to do it.

  • Martin Dráb Profile Picture
    Martin Dráb 238,734 Most Valuable Professional on at

    Okay, so you want to set two cells - one with some header text and one with uploadDateTime. Now you need to decide where you want to put it. You likely can't simpy write to first two cells on the first sheet, because you would overwrite other data.

  • .. Profile Picture
    .. 1,979 on at

    Yes because this how we upload vendCatalog in case of xml. We don't add the "upload date time"node and it seems it get filled by code.

    So for excel, do you mean I should add it at the end of the columns? Is there a way to check what's the last column then add to it?

  • Martin Dráb Profile Picture
    Martin Dráb 238,734 Most Valuable Professional on at

    No, I'm not saying what you should do, because I don't know what you want. It's something that *you* need to think about.

    How do you define the last column? Remember that all I know about your file is what you told me - almost nothing.

    Assuming, for example, that the first row contains column names and no column name can be empty, you can iterate cells in the first row until you find the first one without a value. But if the assumptions are wrong, this design is wrong too.

  • .. Profile Picture
    .. 1,979 on at

    Hi Martin,

    The business scenario is that the standard functionoality to import vendor catalog only supports xml. And based on the standard code you saw, the uploadDateTime field gets added and filled by code.

    The requirement is to import vendor catalog by excel. Which means I'll need to create a single entity instead of composite and duplicate some standard methods to let them work for importing excel.

    The file is going to be a normal excel file. It could have columns with empty values.

    So if let's say the file is like this

    Col1 col2 col3 col4 clo5 col6

    1        xx     3             y

    2        yy     4    g             hh    

    I want it to become like this:

    Col1 col2 col3 col4 clo5 col6 NewField

    1        xx     3             y              dateTime

    2        yy     4    g             hh      dateTime

  • Martin Dráb Profile Picture
    Martin Dráb 238,734 Most Valuable Professional on at

    Then use the design I suggested in my previous reply.

  • .. Profile Picture
    .. 1,979 on at

    Hi Martin,

    How can iterate to get the last column name then add a new column next to it?

    And how can I iterate the number of rows used so that I insert a value for that column in each row?

  • Martin Dráb Profile Picture
    Martin Dráb 238,734 Most Valuable Professional on at

    Are you already able to open a file and read its contents (e.g. the first cell)? If not, you can't start iterating columns and row; you need to deal with basics first. "Look at Read excel through X++ in D365FO" (community.dynamics.com/.../read-excel-through-x-in-d365fo), for example (or look for other source - you know the keyword since my first reply).</p>

    If you know how to open the file, iterating columns and rows is trivial. Simply increase the index of the column or row by one and call get_Item().

    If you get into troubles, show us your code and tell us what exactly got wrong.

  • .. Profile Picture
    .. 1,979 on at

    Hi Martin,

    I've tried alittle but I'm not sure I understand what the code does

    protected void preProcessCatalogMaintenanceRequestExcel()

{

  CatVendorDateTime uploadDateTime = CatVendorCatalogMaintenanceRequest::findRecId(catVendorCatalogMaintenanceRecId).UploadDateTime;

  using(ExcelPackage package= new ExcelPackage(this.fileStream))

  {
    int rowCount, iterator;

    package.Load(this.fileStream);

    ExcelWorksheet worksheet= package.get_workbook().get_worksheets().get_Item(1);

    OfficeOpenXml.ExcelRange range=worksheet.Cells;

    rowCount = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row   1; // what does this one mean?

   }

}
    what shall i do to add an additional column header next to the last column? and how to put a value for this new column in all rows?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the January Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 577 Super User 2026 Season 1

#2
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 309

#3
Diego Mancassola Profile Picture

Diego Mancassola 259

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans