How to change colour of cells in Excel using programming languages

Question

I have googled a while for the methods to create excel files (*.xlsx, not the csv files) using programming languages, such as C++ or PHP. here is an example, http://www.the-art-of-web.com/php/dataexport/

But Ideally, I want to be able to specify the colours for each cell. For example, in VBScript using COM object Excel.Application, the code looks like:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
With objWorksheet
.Cells(1,1).Interior.Color=RGB(245,245,245)
...
...
...
End With
objWorkbook.SaveAs("sample.xlsx")
objExcel.Quit

How can I do this without the use of COM object? I need the program to work platform-independent so COM object is not a good choice.

Update: here is an interesting post, that generates the VBS file and double click the VBS file will give you a nice picture in Excel by drawing cells with different colors.

yes, VBA is not good. I want to write a C++ or PHP program that runs under Linux — justyy, Aug 19 '14 at 15:49
You're not going to be able to do that if you can't open Excel on the Linux box. the only other option I can think of is to generate the necessary XML details with color and everything, then convert that to an .xlsx. But I have no idea how to do that, or even if it's a good idea. — guitarthrower, Aug 21 '14 at 23:27

score 6 · Accepted Answer · answered Aug 24 '14 at 20:54

You can modify cell backgrounds using python and the XLSX Writer module. While it is not the C++ or PHP language you mentioned, Python is cross platform and the code below works on the Fedora box I tested with.

First, you need python and the module mentioned above. You can install the module by doing this:

pip install xlsxwriter

Next, we are going to modify the first tutorial to provide a little color to the "Total" value.

import xlsxwriter

# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
worksheet = workbook.add_worksheet()

# Some data we want to write to the worksheet.
expenses = (
    ['Rent', 1000],
    ['Gas',   100],
    ['Food',  300],
    ['Gym',    50],
)

# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0

green_format = workbook.add_format()
green_format.set_pattern(1)  # This is optional when using a solid fill.
green_format.set_bg_color('#008000')

# Iterate over the data and write it out row by row.
for item, cost in (expenses):
    worksheet.write(row, col, item)
    worksheet.write(row, col + 1, cost)
    row += 1

# Write a total using a formula.
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)', green_format)

workbook.close()

The things that were added are these lines. This utilizes the set_bg_color function.

green_format = workbook.add_format()
green_format.set_pattern(1)  # This is optional when using a solid fill.
green_format.set_bg_color('green')

And the =SUM row was modified to use this green format:

worksheet.write(row, 1, '=SUM(B1:B4)', green_format)

This code creates a green cell at B5.

Excel Green Cell

A note about the color: I provided the color 'green', because that is one of the few colors that are mapped to an RGB value. If you need to use something other than these colors, you can provide an RGB value. #008000 maps to green

green_format.set_bg_color('#008000')

This line is functionally identical to the one provided above.

score 0 · Answer 2 · answered Aug 28 '14 at 14:00

If you want to use PHP, there are at least two different toolkits, which will generate Excel files without the use of COM Object : PhpExcel and ExcelWriterXML.

For ExcelWriterXML for instance, changing the background color is $format->bgColor('Black'); , see complete example on the site.

Another way is to produce xlsx files (which are XML inside) through a template engine like TinyButStrong.

How to change colour of cells in Excel using programming languages

2 Answers2