4

When i wrote a value that does have a percentage sign, excel shows me a green arrow on the top of that cell.

This is what I use to write a value in a specific cell.

worksheet.write(1, 46, '12%')

I tried this :

worksheet.write_string(1, 46, '12%')

and this

worksheet.write_number(1, 46, '12%')

but I get the same results.

How can i get rid of the green arrow?

Thanks!

Andras Deak -- Слава Україні
  • 33,737
  • 11
  • 83
  • 111
mongotop
  • 7,114
  • 14
  • 51
  • 76
  • 4
    @pnuts I think OP means the green triangle which is indicative of "number stored as text". @mongotop you're writing a string even with the `.write_number` I think, because of the single-quotes. Probably you would have to pass a double value of `.12` and then use Excel cell format to apply the percentage format, `"0%"`. – David Zemens Sep 22 '13 at 01:55
  • 2
    See page 14 and then also page 121 of the python xlsxwriter documentation :) – David Zemens Sep 22 '13 at 02:20
  • It works perfect! Can you please put it as an answer? Thanks a lot @DavidZemens – mongotop Sep 22 '13 at 16:58

2 Answers2

8

The green arrow/triangle is an Excel warning. It is probably the warning about numbers stored as text.

The way to avoid this is to write the number without the percentage and then format it with a number format so that the percentage sign is displayed. That is how it is generally done in Excel.

With XlsxWriter you can do it as follows:

import xlsxwriter

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

# Create a percentage number format.
percent_format = workbook.add_format({'num_format': '0%'})

# Write a number as a percentage.
worksheet.write('A1', .12, percent_format)

workbook.close()
jmcnamara
  • 38,196
  • 6
  • 90
  • 108
-2

One way to do this is to write the cell as a formula rather than as a String. E.g. in Apache POI, write as

row.createCell(col++).setCellFormula("\"" + myValue + "\"");

rather than row.createCell(col++).setCellValue(myValue);

I've done it this way in VBA scripts as well. If this doesn't match your programming scenarios, well, basically if you go into Excel and put in '123 you'll see you get a green triangle. But if you put in ="123" you don't. That's the behavior you need to replicate.

Of course, you can just turn the error off. File-Options-Formulas-Numbers-Formatted as Text or preceded by an apostrophe. But that's an Excel setting, not a workbook setting. If you send the workbook to others and they have the option on, they will see those pesky triangles. The Formula way is they way to go for consistency imho.

Chris Hulley
  • 9