You’re offline. This is a read only version of the page.
1,325
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?
Share
Reportprotected 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);
}
You did changed something, but nothing related to the problem of wrong types, which is what I talked about.
I'm sorry, but I'm not sure what you mean by "How can I do it with excel?". Regarding "What did I wrong with the save package?", what problem do you have with it? Please look at the sample code I gave you, try it and tell us if it works for you. If so, you can learn from it when writing your own code.
Hi Martin,
I did change sth, at first the saveAs took this.FileStream()
Now it took the output stream.
In the question description, I specified the I want to replace what the standard xml method does and do it with excel
As you can see in the code specified in the first description, in the xml method, after they created a new node for the field. They wrote it to a stream then upload it to temp storage.
How can I do it with excel? What did I wrong with the save package?
I didn't say you need.
Regarding the error, you seem to be provided a parameter of a wrong type. You changed nothing in it, therefore you're still getting the same error. Look again what you method expects and what values you're actually passing into it.
If necessary, create a new thread and explain this problem there. This thread is about adding a field to an Excel file.
Hi Martin,
Why do I need to write to a new stream?
I did this but I got the same error(specified cast is not valid) when it reached save as function
Using (System.IO.MemoryStream outputStream = new System.IO.MemoryStream())
{
package.SaveAs(outputStream);
fileId= thisUploadFileToTempStorage(outputStream, fullFileName)
}
A new stream you want to write the data to. See an example in Writing to file in DocuRef using StreamIO.
Let's focus on the problem, i.e. updating an Excel file. uploadFileToTempStorage is a different topic.
Hi Martin,
What does the package.saveAs() take?
I did this now:
package.SaveAs(package.Stream)
FileId= this.uploadFileToTempStorage(package.stream, fullFileName); -- is this line correct?
But it errored on save as function -- I got "Specified cast is not valid"
As you see, just knowing that you got "an error" isn't sufficient to address the issue. Look at what actual error you got.
By the way, you can use SaveAs() method to write to an output stream.
Hi Martin,
I did the following but it's throwing an error when I reach package.save();
what should I do with these two lines? and is what I did correct?
All responses (
Answers (
using OfficeOpenXml;
Using officeOpenXml.ExcelPackage
public class xxCatVendorCatalogPostEntityImport extends CatVendorCatalog
{
private SharedServiceUnitFileId fieldId;
protected void preProcessCatalogMaintenanceRequestXML()
{
CatVendorDateTime uploadDateTime = CatVendorCatalogMaintenanceRequest::findRecId(catVendorCatalogMaintenanceRecId).UploadDateTime;
using(ExcelPackage package= new ExcelPackage(this.fileStream))
{
int rowCount,columnCount, rowIterator, columnIterator;
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;
columnCount = worksheet.Dimension.End.Column - worksheet.Dimension.Start.Column 1;
package.Save();
fileId = this.uploadFileToTempStorage(package.Stream, fullFileName);
}
}
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?
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.
#1
André Arnaud de Cal...
292,160
Super User 2025 Season 1
#2
Martin Dráb
230,962
Most Valuable Professional
#3
nmaenpaa
101,156
