You’re offline. This is a read only version of the page.
Announcements
20
Hi guys, i have a problem. Say i want to read several excel files into AX, and i can read data according to some certain demands. But there're some cells with different color and background color,
which indicates different kinds of input value. For example, black (normal) text indicates normal value, red (font color) indicates value over critical standards, green (background color) indicates value below necessary standards. How do i read all these values into AX and how can i store them in variables ?
Thanks in advance!
Hi Seriahl,
You are right, we only have the code logic to setting data of excel, like SysExcelWorksheetHelper in X++.
To get the color of excel, you can write your own C# code and build a library file, then add this dll to AX and use it.
Do you have any particular problem with implementing the suggestion I gave you in the second paragraph of my first reply?
Unfortunately I don't work with AX 2012 anymore and I can't check exact method names and things like that.
By the way, please use Insert > Insert Code (in the rich-formatting view) to paste source code. It preserves indentation, making code much easier to read. It would also helped if your code snippet included data types. And use variable names prefixed with underscore just for method parameters and don't change their values inside method body.
Hi martin, I already have basic x++ code that do ordinary jobs. But i lack the code to get the font color and background color input.
what i have is code like this:
...
_workbook = _workbooks.item(1);
_worksheets = _workbook.worksheets();
_worksheet = _worksheets.itemFromNum(1);
_cells = _worksheet.cells();
while(1)
{
_prodId = _cells.item(_lines, _rows).value().bstr();
if (!_prodId)
break;
_rows++;
_measureDate = _cells.item(_lines, _rows).value().date();
_targetData.clear();
_targetData.prodId = _prodId;
_targetData.measureDate = _measureDate;
for (i = 1; i <= 30; i++)
{
_tmpVal = this.COMVariant2Str(_cells.item(_lines, _rows + i).value());
//TODO: get the excel cell font color and background color here
...
}
Right now i need to implement code to get font color and background color inside the for loop, as you can see above. Thanks!!
Do you already have existing code that it needs to be integrated to, or are you going to do it from scratch? Do you already know how to read Excel files and you just don't know how to deal with formatting, or you need to learn even things like opening a file?
In which language do you want to do it?
I would argue that the spreadsheet hasn't been designed correctly. Rather than manually setting colors with special meaning, conditional formatting should be used to set colors based on data.
But if you can't change that, use the Style property of a row range. For example, it has the Fill property which has the BackgroundColor property.
#1
André Arnaud de Cal...
294,219
Super User 2025 Season 1
#2
Martin Dráb
232,980
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
