This is a migrated thread and some comments may be shown as answers.

Data formating

3 Answers 101 Views

General Discussions

This is a migrated thread and some comments may be shown as answers.

3 Answers, 1 is accepted

answered on 26 Aug 2013, 07:58 PM

You are looking at this the wrong way. If your requirement governs how the end result will be used in Excel then you are working on the wrong end. Exports from Telerik, or any third party tool, use data formats that Excel can read. They do not actually use Excel. For example a CSV formatted output file is a text file with fields separated by commas. Excel natively knows how to read this. This format cannot be formatted.

Based upon your apparent need you should be looking at using office automation programming to control your resulting spreadsheet down to the individual cell. The trouble with this technology is that you also therefore bind yourself to a specific version of office and are forever bound by upgrade/dll hell problems. This method would give you exactly what you are asking for but you will probably regret the compatibility problems it introduces.

answered on 27 Aug 2013, 01:17 PM

I think you misunderstand my point. What you are observing when you open the document in Excel is the native format defined by the current users profile who is using excel. If you examine the options of excel (not any telerik item) you will find buried there how to display values using default. As you effectively export values from Telerik without formatting, you are subject to Excel's default formatting. I.E., it normally defaults to "GENERAL". Using the rules built into excel it formats "GENERAL" one way for numbers and another for text. It basically becomes a "best guess" format

I suspect you are under the impression that Telerik exports the number "-3280593.595" as "-3.28E+06". No it does not. Put your cursor on the cell and look at the "formula" box (to the right of the Fx). It will show the actual number. What you are observing is Excel mangling your numbers by its attempts to display numbers using its most meaningful manner. In my opinion it's definition of meaningful manner is way off.

Your solutions are three fold:

Realize that what is being observed is a display problem and not a data problem. That is to say the data remains accurate it is just not displayed in a useful manner. Educate your users and have them set their defaults. As this is a per-user thing it really is not a good option. I.e., tell your users "live with it". The problem is excel not your program.
Use office automation to set the format of a given range of cells/column/rows/sheets to the format required to display the data in the manner you wish. This method has the advantage of displaying the data in a meaningful manner. It introduces dependencies upon specific versions of Office. If your solution is web based then you have potential licensing issues as you must have a license for Office for each users of your website. (This is the only good argument I have ever found for Sharepoint services) With experience you will find that Office automation leaks memory like most Microsoft products. I would recommend that you encapsulate the logic into its own DLL and perform Garbage collections to recover the leaking memory. Given Office is a 32 bit product you will run into issues running this from a 64 bit application which are difficult but can be overcome. As a programmer which prefers C#, I find that I prefer to write Office code in VB.NET. It handles things easier. If your product is commercial, Microsoft will want to pick your pocket.
Export your data as text. This means you format the data on export. Your usage of "1.0MM" of course means you will no longer be a number so running totals of columns in excel will be a problem.