Hello!
I am using the latest version of the components (2021.2) and when I am exporting to Excel from an UltraGrid (WinForms) via the UltraGridExcelExporter, the column widths in the Excel file are changed.
My exportformattingoptions are set to none, and I am exporting to an existing excel file with already set column widths that we don't want to change.
Can you advice?
/Henrik
Hello Henrik,
The reason why the styling of the exported excel file is different is because, when exporting the UltraGridExcelExporter uses a copy of the layout of the grid, meaning that any changes done to this layout would be applied on the exported file, but not on the grid in the application. The copy of the layout is then used for the exporting and overrides the file that you are exporting it to. The easiest solution that I can think of is using the BeginExport event and setting the desired width of the columns there. The code below demonstrates the use of the event and changes the width of the first column in the exported excel file.
private void UltraGridExcelExporter1_BeginExport(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.BeginExportEventArgs e) { e.Layout.Bands[0].Columns[0].Width = 200; }
Please let me know if you have any questions.
Regards, Ivan Kitanov
I cannot say that I agree on that
There is a property in the UltraWInGrid.ExcelExport object called "ExportFormattingOptions", and before one of your colleagues ( Michael DiFilippo) helped me with finding that one, everything in the Excel file was changed (Forecolor, Background color) when the export was made. But when setting that property to None, ForeColors, BackgroundColors, Fonts etc. were not changed. The width of the columns should be included in that scope if you ask me.
Anyway, if you say that this cannot be done in any other way, it is a bit sad, since we loose all kinds of dynamics in terms of Excel files that can be opened and exported to. We can never now which template the user wants to export to.
Any other ideas?
When set to none the ExportFormattingOptions property excludes formatting from the grid such as foreground and background colors, text alignment, font data, word wrapping, etc., however what is being exported is still a copy of the grid’s layout.
Since you mentioned that your users decide which columns are being exported, what I can suggest you is looping through the exported columns inside the BeginExport event and assigning the width of the column based on the column key. This could be achieved with the following code:
private void UltraGridExcelExporter1_BeginExport(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.BeginExportEventArgs e) { foreach (UltraGridColumn col in e.Layout.Bands[0].Columns) { if(col.Key.Equals("Address")) e.Layout.Bands[0].Columns[0].Width = 350; } }
Yes, but how do you mean that I should know which width to put in, if it can be whatever template they are using?
The width shouyld be intact in terms of how the file is formatted in the very beginning, not according to the grid.
By design the UltraGridExcelExporter exports the data based how the grid looks and tries to replicate its layout in the excel file, having this in mind it doesn’t matter how the file has been formatted previously, since the UltraGridExcelExporter would override it.
Could you please provide some more information on the template excel files you are using?
For example, the way I understand it is as follows: If you have a grid with 4 columns – A, B, C and D and all of them have width of 100, and you want a template with columns such as A (width 75), B (width 150) and D (width 200). This template could be achieved by setting the widths of the columns the way I suggested in my previous reply and then hiding the column C.
Additionally if you would like to load an excel file and keep its formatting and only modifying the cells of the WorkSheet with the data that comes from the grid I suggest you reviewing our Excel Engine.
Hello Ivan! It is true what you say and I understand it completely.
The problem is that we don't want the excel file to be specified in terms of columns widths from the very beginning, meaning that when we click the export button, we dont know the layout of the file. We just know which rowIndex and colIndex that the export function should start at.
But in terms of functionality, it seems that the export function will change the excel column swidth meaning that we will have to do some sort of mapping in-between. I hope we can manage to fix something here.
Thanks
If you only want to export the grid data from specific rowIndex and colIndex, then I believe that the Excel Engine would be a better option than the UltraGridExcelExporter. With it you would be able to load the specific template file and preserve its format as well as entering the data from the grid in the cells of the template file.
I am attaching a small sample that demonstrates what I have explained above, for template I am using an excel file with specified widths of the columns as well as a background color for the headers. When the user presses the “Load Excel File and Export Specific Data” button the template file is loaded by the Excel Engine, the required data is written and then the file is saved under a different name in order to compare if the formatting of the file is preserved successfully. Along with the sample I am attaching the “template” that I have used as well as the resulting excel file.
Please test the sample on your side and let me know if you have any questions or converns.
ExportGridWithMultipleRows.zip
I'm glad that you found my suggestion helpful.
Thank you for choosing Infragistics!
Regards,Ivan Kitanov
Thank you, it works fine.