Hello,
we are using the "UltraGridExporter" to export datas from an ultraingrid to excel. After upgrading our solution from the very old version 2010/3 to 2018/2 we have two issues:
#1: Decimal-Columns are in the wrong format: The result ist by example "923.000", expected is "923.00"
We use this code, wich works perfect in the old version:
private static void grdExport_InitializeColumn(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.InitializeColumnEventArgs e) { if (e.Column.DataType == typeof(Decimal)) { e.ExcelFormatStr = "#,#0.00"; } }
#2: Columns with drop-down-combos shows this combo also in excel.
See this screenshot:
The whole excelfile contains only "5500" (Ultragrid was filtered before exported)
The combo shows every value from the grid
What can we do?
Greetings from germany, Karsten Heimer.
Hi Karsten,
1) I tried this out and it works fine for me. Nothing has changed in this area of the exporter as far as I can remember. You might want to check the Excel file itself and examine the properties of those cells and see what format is actually applied. And maybe put a breakpoint inside the event handler for InitializeColumn and make sure that code is still getting hit. Maybe the event handler got disconnected somehow? Or the data type of the column has changed. 2) This was an update we made to the exporter. It now exports the ValueLists in your grid by default. But if you don't want the dropdown list in Excel, you can turn this off:
this.ultraGridExcelExporter1.ExportValueLists = false;
I am attaching a small sample project here that I used to test this. You can see that the formatting on the decimal column works fine and that the lists are not exported in my sample.
0508.MemDataTable CS.zip
Hi Mike,
great to hear from you.
#1 ist finished, setting "ExportValueLists" to "false" done it...
#2 is not finished. I've tested your example an the result is as same wrong:
Could this be a version-depend problem? We are using "Infragistics.Win.UltraWinGrid.ExcelExport.dll" in version "18.2.20182.277" Filedate: "04.04.2019".
Greetings from Germany, Karsten Heimer.
Well... since it works fine for me and it's not working for you, the only possible explanation would seem to be that it's a bug in the version of the controls you are using. That seems unlikely, but there are two service releases after the version you are using. The latest one was:18.2.2018.429. So you could try updating to that version and seeing if that helps. The only other alternative I can imagine is that it's somehow related to some setting on your machine. But that seems even more unlikely.
If you can post the resulting Excel file that my sample generated on your machine here, I would like to take a look at it is and see if the same file displays differently on my machine. And I can also check to see if the problem is that the Excel cells don't have the correct format or if there is something else going on.
Hello Mike,
this is the excelfile created with your testprogramm: x.xlsx
Very strange. So I see the problem (three zeroes) in the Excel file you sent me. And I looked at the Format of the cell and it shows: "#.#000" So that's clearly wrong. And if I manually change the format on the cell to "#.#000", it works fine.
So the problem lies in the Excel file being generated by the application. Which seem to indicate a bug in the exporter. I don't remember any bugs like that. And it seems very strange that the exporter would just add an arbitrary 0 onto the format. But maybe there was such a bug in the version you have and it was fixed.
I'd be curious what happens on your machine if you change the code to have only ONE zero ("#.#000")? Does the output show two zeroes in that case?
I dug up the version you are using (18.2.277) and I do not see a problem with that version on my machine.
So now I'm thinking this must be something specific to your machine. I see that you mention you are in Germany, so presumably the regional settings on your machine are using German. Doesn't German use a comma (instead of a decimal) as the decimal separator? So maybe the format needs to be: "#,#000" (with a comma between the two "3" signs?
i have solved the problem, but the solution is very odd for me:
- While exporting to excel i have to use this string: "#.#,00"
- But, everywehere else, even in ultraWinGrid i have to use this string: "#,#.00"
Example:
- grdBuchungen.DisplayLayout.Bands[0].Summaries["Mengensumme"].DisplayFormat = "{0:#,#.00}";
So I guess some properties take the Invariant culture and some need a culture-specific format string. And we must have changed that at some point. But I'm glad you got it resolved.