Excel export/enhancement: Specify column type for excel export.

[SKPFCW]

In our company we use organizational terms, which consist of numeric characters (often with leading zeros) and which are converted (undesirably) to numbers or dates when exporting to excel:

Example: Term "012345" -> result in excel: [number] 12345 Term "01.020" -> result in excel: [number] 1.2 Term "01.03.20" -> result in excel: [date] 03-01-2020

I would like to propose to introduce an option to specify the format of the excel column for a given grid-column i.e. within the colModel:

For example:

exportoptions = {
    excel_format: "text"
};

[tonytomov]

Please, consult the docs, how to do this: http://www.guriddo.net/documentation/guriddo/javascript/user-guide/exporting/#custom-export-to-excel

[SKPFCW]

Unfortunately, I have not had widespread success with the capabilities of the jqGrid.

I spend much time but couldn't find a custom format string equivalent for the built-in excel cell format "text".

I can turn off the excel parsers and thus solve the problem of "leading zeros", but those values in the excel table that are interpreted as purely numerical are aligned right-justified, whereas those that contain letters or leading zeros are aligned left-justified. This does not happen if the column was formatted as text in excel.

In addition, specifying a user-defined cell format different from the built-in formats in the "export_format" property leads to an error message when opening the excel file, as I described in Issue #983.

[tonytomov]

We add a possibility to set a excel cell to be a text. It is not with excel_format, but with excel_style.

To do this set the following option:

exportoptions = {
    excel_parser : false, excel_style: "text"
};

Additionally to this you can use one of 67 predefined excel formatters setting the excel_style to number: like this:

exportoptions = {
    excel_parser : false, excel_style: 50
};

The predefined formatters are as follow (will be added into the docs)

0 - Normal text
1 - White text
2 - Bold
3 - Italic
4 - Underline
5 - Normal text, grey background
6 - White text, grey background
7 - Bold, grey background
8 - Italic, grey background
9 - Underline, grey background
10 - Normal text, red background
11 - White text, red background
12 - Bold, red background
13 - Italic, red background
14 - Underline, red background
15 - Normal text, green background
16 - White text, green background
17 - Bold, green background
18 - Italic, green background
19 - Underline, green background
20 - Normal text, blue background
21 - White text, blue background
22 - Bold, blue background
23 - Italic, blue background
24 - Underline, blue background
25 - Normal text, thin black border
26 - White text, thin black border
27 - Bold, thin black border
28 - Italic, thin black border
29 - Underline, thin black border
30 - Normal text, grey background, thin black border
31 - White text, grey background, thin black border
32 - Bold, grey background, thin black border
33 - Italic, grey background, thin black border
34 - Underline, grey background, thin black border
35 - Normal text, red background, thin black border
36 - White text, red background, thin black border
37 - Bold, red background, thin black border
38 - Italic, red background, thin black border
39 - Underline, red background, thin black border
40 - Normal text, green background, thin black border
41 - White text, green background, thin black border
42 - Bold, green background, thin black border
43 - Italic, green background, thin black border
44 - Underline, green background, thin black border
45 - Normal text, blue background, thin black border
46 - White text, blue background, thin black border
47 - Bold, blue background, thin black border
48 - Italic, blue background, thin black border
49 - Underline, blue background, thin black border
50 - Left aligned text 
51 - Centred text 
52 - Right aligned text 
53 - Justified text 
54 - Text rotated 90° 
55 - Wrapped text 
56 - Percentage integer value (automatically detected when parser is true)
57 - Dollar currency values (automatically detected when parser is true)
58 - Pound currency values (automatically detected when parser is true)
59 - Euro currency values (automatically detected when parser is true)
60 - Percentage with 1 decimal place (automatically detected when parser is true)
61 - Negative numbers indicated by brackets (automatically detected when parser is true)
62 - Negative numbers indicated by brackets - 2 decimal places (automatically detected when parser is true)
63 - Numbers with thousand separators (automatically detected when parser is true)
64 - Numbers with thousand separators - 2 decimal places (automatically detected when parser is true)
65 - Numbers without thousand separators (automatically detected when parser is true)
66 - Numbers without thousand separators - 2 decimal places (automatically detected when parser is true)
67 - Date yyyy-mm-dd (automatically detected when parser is true)

[SKPFCW]

Many thanks for the support. That's great.

I did different tests:

With excel_style: "text" the representation of our terms works correctly. However, the column is still formatted as "Standard" (=Default) in Excel. That means, when I click in the column and edit the content, the leading zero disappears again (see screenshot).

Using excel_style: 50 does not preserve leading zeros. However, an error message as in Issue #983 appears when the Excel file is opened.

Erorr message while open excel file:

We have identified a problem with some of the content in "{filename}.xlsx". Should we restore as much as possible? If you trust the source of this workbook, click "Yes".

If I choose another number from the list, e.g. excel_style: 10, then the color option is applied correctly, but the formatting to text and left-align/leading zeros are not. Here, too, an error message appears when the Excel table is opened.

[tonytomov]

Unfortunately I can not reproduce your problem. For me it is working. I have updated the online demo and using Microsoft Excel 2010 everything work Normal for me. Check the demo: http://guriddo.net/demo/guriddojs/export/customexcel/

See the Quantity field

Edit: I see what you mean. Unfortunately at the moment our excel experience does not allow to fix this. We will try to investigate the problem. The error should disappear IMHO.

[SKPFCW]

Thank you for your patience and outstanding commitment.

[tonytomov]

We have do some progress on this. Can you please test if the editing change the value.

Use excel_type : "text"

to format the field.

Thank you

[SKPFCW]

Problem solved - this is marvelous! Thank you very much for your help.