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

(0) Share

Report

Posted on by ..

1,965

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)

All responses (18)

Answers (0)

Martin Dráb 237,844 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
.. 1,965 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Martin Dráb 237,844 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
.. 1,965 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Martin Dráb 237,844 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
.. 1,965 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Martin Dráb 237,844 Most Valuable Professional on at

Like (0)

Report

Then use the design I suggested in my previous reply.

Was this reply helpful? Yes No
.. 1,965 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Martin Dráb 237,844 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
.. 1,965 on at

Like (0)

Report
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?

Was this reply helpful? Yes No

Join the conversation

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

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

Featured topics

Product updates

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

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

Featured topics

Product updates