7
$\begingroup$

With the Excel import function we can get the formatted data of the values in the cell by using “FormattedData”. I really would like to get the cell background color because it’s used as markers (like e.g. Red as a warning or Blue as a certain category). I don’t think it’s available in Mathematica but would like to know if other users also had this issue and how they possibly solved it.

I use MM 14.0 and the XLSX format with Windows Excel "latest version". I tried using any option available with mathematica but it only import the format of the data itself and not the cell information.

At this moment I'll use hardcoding to reach my goal with some fact checking. The cell background in these files is used to indicate categories and sub-categories etc. Unfortunately the authors did use the same font type and sizes. I tried to use the XML files as a shortcut but after some time I noticed that it's a bit more complicated then expected.

So if somebody has found a method to retrieve cell background in an easy way I'll like to know. Otherwise it's a pity.

Improve this question
$\endgroup$
5
  • $\begingroup$ It’s an XLSX format. I tried all options available in Mathematica. I looked at the XML files (in the unzipped xlsx) but that seems as a daunting task but the info seems to be there. Did not look for a python importer yet. $\endgroup$
    – Lou
    Commented Jul 4 at 7:42
  • $\begingroup$ FullForm@Import["test.xlsx",{"XLSX","FormattedData"}] doesn't seem to contain the background colour. It seems to be your only choice is to dig into Import["test.xlsx",{"ZIP","xl\\styles.xml"}] $\endgroup$
    – rhermans
    Commented Jul 4 at 8:12
  • $\begingroup$ Yes and I wanted to avoid that :( $\endgroup$
    – Lou
    Commented Jul 4 at 8:16
  • 3
    $\begingroup$ You should edit your question to improve it and share all you have learned so far, and how. Share the code you are using. $\endgroup$
    – rhermans
    Commented Jul 4 at 8:23
  • 1
    $\begingroup$ I think you can do this through the NETLink framework. This will allow you to manipulate Excel files directly, but you'll need to open them in Excel and communicate with Excel to achieve this. $\endgroup$
    – Sjoerd Smit
    Commented Jul 8 at 8:55

1 Answer 1

Reset to default
5
$\begingroup$

I think this should put you on the right track:

Create an Excel NETObject:

<<NETLink`
xlsObj = CreateCOMObject["Excel.Application"]

Open the file you're interested in:

xlsObj @ Workbooks @ Open["path\\to\\Book.xlsx"]

Get the color of the B1 cell on the 1st sheet:

xlsObj @ Sheets[1] @ Cells[1, 2] @ Interior @ Color

Unfortunately, it seems like this gives you a real number that you'll have to convert to a color somehow. You'd need to read the Excel docs to figure out what this number exactly represents. Probably some hexadecimal that you need to pull apart into RBG components.

To clean up:

ReleaseNETObject @ xlsObj
Improve this answer
$\endgroup$
5
  • $\begingroup$ Thanks! Will try this! $\endgroup$
    – Lou
    Commented Jul 9 at 16:25
  • $\begingroup$ It seems to work indeed but the xlsx files I have are large and take forever to load (10 minutes) with the COM objects. Don't know why. They contain multiple sheets. $\endgroup$
    – Lou
    Commented Jul 15 at 7:36
  • $\begingroup$ It was slow due to using a file on onedrive. Local copy solved it. $\endgroup$
    – Lou
    Commented Jul 15 at 7:52
  • $\begingroup$ That makes sense. It should be quite fast if you use a file on your local hard drive. $\endgroup$
    – Sjoerd Smit
    Commented Jul 15 at 8:56
  • $\begingroup$ Do you know if there is a way to call for information of multiple cells at once? I'm now using Map for each Cell and that is still slow Like (xlsObj @ Sheets[1] @ Cells[#, 2] @ Interior @ Color)&/@Range[1,100] $\endgroup$
    – Lou
    Commented Jul 17 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.