Late response.... But might help if some one is looking for this.
public static void ExportToExcel(this UltraGrid Grid)
{
if (Grid == null || Grid.Rows.Count == 0) return;
var xlApp = new Excel.Application();
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
List<String> Columns = new List<string>();
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Range range;
Excel.Workbook workbook = workbooks.Add();
Excel.Worksheet worksheet = workbook.Worksheets[1];
long totalCount = Grid.Rows.Count;
long rowRead = 0;
UltraGridColumn column = Grid.DisplayLayout.Bands[0].Columns[0];
column = column.GetRelatedVisibleColumn(VisibleRelation.First);
while (null != column)
{
Columns.Add(column.Key);
column = column.GetRelatedVisibleColumn(VisibleRelation.Next);
}
for (var i = 0; i < Columns.Count; i++)
{
UltraGridColumn Column = Grid.DisplayLayout.Bands[0].Columns[Columns[i]];
worksheet.Cells[1, i + 1] = Column.Header.Caption;
range = (Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 20;
range.Font.Bold = true;
}
for (var r = 0; r < Grid.Rows.Count; r++)
{
for (var i = 0; i < Columns.Count; i++)
{
UltraGridColumn Column = Grid.DisplayLayout.Bands[0].Columns[Columns[i]];
if (Column.Format != null)
{
if (Column.Format.Equals("##,###") || Column.Format.Equals("#,##"))
{
worksheet.Cells[1, i + 1].EntireColumn.NumberFormat = "#,###;(#,###);-";
worksheet.Cells[r + 2, i + 1] = Grid.Rows[r].Cells[Columns[i]].Value;
}
else if (Column.Format.StartsWith("##,###.##") || Column.Format.Equals("#,##.##"))
{
worksheet.Cells[1, i + 1].EntireColumn.NumberFormat = "#,##0.00;(#,##0.00);-";
worksheet.Cells[r + 2, i + 1] = Grid.Rows[r].Cells[Columns[i]].Value;
}
else if (Column.Format.StartsWith("MM/dd/yyyy"))
{
worksheet.Cells[1, i + 1].EntireColumn.NumberFormat = "MM/dd/yyyy";
worksheet.Cells[r + 2, i + 1] = Grid.Rows[r].Cells[Columns[i]].Value;
}
else
{
worksheet.Cells[r + 2, i + 1] = Grid.Rows[r].Cells[Columns[i]].Value;
}
}
else
{
worksheet.Cells[r + 2, i + 1] = Grid.Rows[r].Cells[Columns[i]].Value.ToString();
}
}
rowRead++;
}
Microsoft.Office.Interop.Excel.Range columns = worksheet.UsedRange.Columns;
columns.AutoFit();
xlApp.Visible = true;
}
catch (Exception ex)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
System.Windows.Forms.MessageBox.Show("Exception occured while exporting rows to Excel. Exception Details : " + ex.Message.ToString(), "Excel Export", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
foreach (Excel.Workbook wb in xlApp.Workbooks)
{
foreach (Excel.Worksheet ws in wb.Worksheets)
{
Marshal.FinalReleaseComObject(ws);
}
wb.Close(false, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(wb);
}
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
}
}