Fixed columns and rows to be exported into Excel freeze panes

Hi,

I have an UltraGrid with fixed rows on the top and fixed columns on the left. When data are exported to Excel, I would like to freeze the header row and fixed rows on top of Excel and the fixed columns on the left. I also need to set the bottom border of last frozen row and the right border of the last frozen column to be thick.

Your help is greatly appreciated!

Best Regards,
Shaolin

Hello Shaolin,

The UltraGridExcelExporter does not currently support exporting fixed rows/columns, and that is a new product idea. You can suggest new product ideas for future versions (or vote for existing ones) at <https://www.infragistics.com/community/ideas>.

Submitting your idea will allow you to communicate directly with our product management team, track the progress of your idea at any time, see how many votes it got, read comments from other developers in the community, and see if someone from the product team has additional questions for you.

Remember when submitting your idea to explain the context in which a feature would be used and why it is needed as well as anything that would prevent you from accomplishing this today. You can even add screenshots to build a stronger case. Remember that for your suggestion to be successful, you need other members of the community to vote for it.  You can also link back to this thread for additional details. Thank you in advance to submitting your product idea.

Outside of adding this as a feature to the UltraGridExcelExporter, you could manually handle freezing the rows/columns in Excel through code by capturing which cell would have to be frozen according to your locked grid rows/columns.

Please let me know if I can further assist you.

Exporting data from UltraGrid to Excel does support freezing columns and rows. Here is the code snippet:

using (var excelExporter = new UltraGridExcelExporter())
{
	excelExporter.ExportStarted += (sender, eventArgs) =>
	{
		...
	};
	excelExporter.RowExporting += (sender, eventArgs) =>
	{
		...
	};
	excelExporter.InitializeColumn += (sender, eventArgs) =>
	{
		...
	};
	Workbook workbook = excelExporter.Export(GridControl, filePath, WorkbookFormat.Excel2007);
	Worksheet worksheet = workbook.Worksheets[0];
	worksheet.DisplayOptions.PanesAreFrozen = true;
	int frozenRows = 3;    // your logic to count fixed rows plus header
	int frozenColumns = 3; // your logic to count fixed columns
	worksheet.DisplayOptions.FrozenPaneSettings.FrozenRows = frozenRows;
	worksheet.DisplayOptions.FrozenPaneSettings.FrozenColumns = frozenColumns;
	workbook.Save(filePath);
}

Best Regards,
Shaolin

Shaolin,

You are right, and I misspoke. Freezing panes is possible to do through the Infragistics ExcelEngine, and the UltraGridExcelExporter will honor that in the Excel file that it saves. However, the UltraGrid does not automatically send the exporter which columns/rows are fixed, and consequently where the ExcelEngine will freeze the pane. This last step is what I meant would have to be handled through code.

I've been working on a sample which demonstrates how one would handle this through code, and have attached it below. Please take a look if you need more info about how to freeze the correct columns/rows in the ExcelEngine.

Please let me know if I can assist you further.

Hi John,

Thanks for your sample code, which helped me to understand exporting to Excel better.

Best Regards,
Shaolin