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

Notifications

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 read excel cell...
Finance | Project Operations, Human Resources, ...
Suggested Answer

How to read excel cell font and background color

(0) ShareShare
ReportReport
Posted on by Seriahl 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!

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

    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.

  • Seriahl Profile Picture
    Seriahl 20 on at

    Thanks Martin, is there any demonstration code that i can start with? Thanks a lot! Much more appreciated if the code is related with font color value.

  • Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    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?

  • Seriahl Profile Picture
    Seriahl 20 on at

    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!!

  • Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    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.

  • Seriahl Profile Picture
    Seriahl 20 on at

    Sorry Martin, i googled but found no code to get the font color and background color out of excel cells through X++, mostly the code i found are setting data of excel 

    when exporting data from AX to excel. 

  • Suggested answer
    Fangyuan Hou Profile Picture
    Fangyuan Hou on at

    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.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

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

#1
Martin Dráb Profile Picture

Martin Dráb 451 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 428 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 239 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans