Preserving Indentation while exporting C1TrueDbGrid Grouped columns to Excel
C1TrueDbGrid data can be exported as an Excel file using ExportToExcel() method. However, when the C1TrueDbGrid with grouped columns is exported to a spreadsheet, the group indentation is not preserved and all the columns appear together. This blog explains the approach to preserve indentation using C1Excel.
Binding and Grouping
Initial step for this approach is to bind C1TrueDbGrid and enable grouping. For simplicity we will bind the control with Order Details table of C1Nwind database. To enable grouping set the DataView property to GroupBy and add OrderID, ProductID and UnitPrice column to the GroupedColumns collection. Following code can be used to enable grouping
c1TrueDBGrid1.DataView = C1.Win.C1TrueDBGrid.DataViewEnum.GroupBy;
c1TrueDBGrid1.GroupedColumns.Add(c1TrueDBGrid1.Columns["OrderID"]);
c1TrueDBGrid1.GroupedColumns.Add(c1TrueDBGrid1.Columns["ProductID"]);
c1TrueDBGrid1.GroupedColumns.Add(c1TrueDBGrid1.Columns["UnitPrice"]);
Default Excel Export
To use C1TrueDBGrid's export feature uses Reports for WinForms' components internally, and we need to reference Reports for WinForms' assemblies (C1.Win.C1Report and C1.C1Report). Following method will export the C1TrueDbGrid into excel file :
c1TrueDBGrid1.ExportToExcel("DefaultBehaviour.xls");
The indentation of grouped columns is not preserved in the generated excel file : 
Customized Excel Export Using C1Excel
Following code is used to export grouped C1TrueDbGrid to excel :
// step 1: create a new workbook
C1XLBook book = new C1XLBook();
// step 2: create styles for odd and even values
XLStyle styleMain = new XLStyle(book);
XLStyle styleHeader = new XLStyle(book);
XLStyle styleGroupHeader = new XLStyle(book);
// step 3: write content and styles into some cells
XLSheet sheet = book.Sheets[0];
int row = 0;
// Pointer for looping through the rows
int col = 0;
int xcol = 0;
//C1.Win.C1TrueDBGrid.C1DisplayColumn CurrentDColumn = default(C1.Win.C1TrueDBGrid.C1DisplayColumn);
// Pointer for looping through the columns
bool blnContinue = true;
ArrayList arrayGroupedColumnHeaders = new ArrayList();
int CurrentGroupLevel = 0;
XLCell cell = default(XLCell);
C1.Win.C1TrueDBGrid.GroupRow gr = null;
decimal Sum = default(decimal);
decimal MinMax = default(decimal);
decimal Denominator = default(decimal);
for (int i = 0; i <= c1TrueDBGrid1.GroupedColumns.Count - 1; i++)
{
arrayGroupedColumnHeaders.Add(string.Empty);
}
#region Headers
foreach (C1DisplayColumn CurrentDColumn in c1TrueDBGrid1.Splits[0].DisplayColumns)
{
if ((c1TrueDBGrid1.Splits[0].DisplayColumns[col].Visible == true) && (c1TrueDBGrid1.Splits[0].DisplayColumns[col].Width != null))
{
cell = sheet[0, xcol];
cell.Value = CurrentDColumn.DataColumn.Caption;
cell.Style = new C1.C1Excel.XLStyle(book);
cell.Style.BackColor = Color.Gray;
cell.Style.ForeColor = Color.Black;
cell.Style.Font = new Font("Tahoma", 9, FontStyle.Bold);
cell.Style.BorderBottom = XLLineStyleEnum.Medium;
//cell.Style = styleHeader
cell.Style.AlignHorz = (XLAlignHorzEnum)CurrentDColumn.HeadingStyle.HorizontalAlignment;
xcol = xcol + 1;
}
col = col + 1;
}
int SelectedRowsOnly = c1TrueDBGrid1.SelectedRows.Count;
if ((SelectedRowsOnly == 0) | (c1TrueDBGrid1.GroupedColumns.Count > 0))
{
//Rows
for (row = 0; row <= (c1TrueDBGrid1.Splits[0].Rows.Count - 1); row++)
{
col = 0;
xcol = 0;
foreach (C1DisplayColumn CurrentDColumn in c1TrueDBGrid1.Splits[0].DisplayColumns)
{
if ((c1TrueDBGrid1.Splits[0].DisplayColumns[col].Visible == true) && (c1TrueDBGrid1.Splits[0].DisplayColumns[col].Width != null))
{
cell = sheet[row + 1, xcol];
cell.Style = new C1.C1Excel.XLStyle(book);
cell.Style.Font = new Font("Tahoma", 9, FontStyle.Regular);
cell.Style.ForeColor = Color.Black;
if (c1TrueDBGrid1.Splits[0].Rows[row].RowType == C1.Win.C1TrueDBGrid.RowTypeEnum.DataRow)
{
//Regular Data row
//cell.Style = styleMain
if (CurrentDColumn.DataColumn.CellValue(c1TrueDBGrid1.RowBookmark(row)) is DateTime)
{
cell.Value = (string)CurrentDColumn.DataColumn.CellText(c1TrueDBGrid1.RowBookmark(row));
}
else
{
if (CurrentDColumn.DataColumn.NumberFormat != string.Empty & CurrentDColumn.DataColumn.NumberFormat != "Percent")
{
if (CurrentDColumn.DataColumn.CellText(c1TrueDBGrid1.RowBookmark(row)) != string.Empty)
{
cell.Value = CurrentDColumn.DataColumn.CellText(c1TrueDBGrid1.RowBookmark(row));
}
}
else
{
cell.Value = CurrentDColumn.DataColumn.CellValue(c1TrueDBGrid1.RowBookmark(row));
}
}
sheet.Columns[xcol].Width = c1TrueDBGrid1.Splits[0].DisplayColumns[col].Width * 18;
cell.Style.AlignHorz = (XLAlignHorzEnum)CurrentDColumn.Style.HorizontalAlignment;
if ((gr != null) & xcol == 0)
{
for (int i = 0; i <= gr.Level; i++)
{
cell.Value = " " + cell.Value;
cell.Style.AlignHorz = XLAlignHorzEnum.Left;
}
}
}
else
{
cell.Style = new C1.C1Excel.XLStyle(book);
cell.Style.BackColor = Color.FromArgb(150, 150, 150);
cell.Style.ForeColor = Color.Black;
cell.Style.Font = new Font("Tahoma", 9, FontStyle.Bold);
//Group Header row
if (xcol == 0)
{
gr = (GroupRow)c1TrueDBGrid1.Splits[0].Rows[row];
cell.Value = (gr.GroupedText == string.Empty ? "No " + c1TrueDBGrid1.GroupedColumns[gr.Level].Caption : gr.GroupedText) + " (" + gr.Count + ")";
for (int i = 0; i <= gr.Level - 1; i++)
{
cell.Value = " " + cell.Value;
}
cell.Style.AlignHorz = XLAlignHorzEnum.Left;
}
else
{
switch (c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].Aggregate)
{
case C1.Win.C1TrueDBGrid.AggregateEnum.Sum:
Sum = 0;
for (int i = gr.StartIndex; i <= gr.EndIndex; i++)
{
if (!Convert.IsDBNull(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i)))
{
Sum += Convert.ToDecimal(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i));
}
}
cell.Value = Sum;
break;
case C1.Win.C1TrueDBGrid.AggregateEnum.Average:
Sum = 0;
Denominator = 0;
for (int i = gr.StartIndex; i <= gr.EndIndex; i++)
{
if (!Convert.IsDBNull(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i)))
{
Sum += Convert.ToDecimal(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i));
Denominator += 1;
}
}
if (Denominator > 0)
{
if (Sum / Denominator == Math.Round(Sum / Denominator, 0))
{
cell.Value = Sum / Denominator;
}
else if (Sum / Denominator == Math.Round(Sum / Denominator, 1))
{
cell.Value = String.Format("##,###,##0.0", Sum / Denominator);
}
else if (Sum / Denominator == Math.Round(Sum / Denominator, 1))
{
cell.Value = String.Format("##,###,##0.00", Sum / Denominator);
}
else
{
cell.Value = String.Format("##,###,##0.000", Sum / Denominator);
}
}
else
{
cell.Value = 0;
}
break;
case C1.Win.C1TrueDBGrid.AggregateEnum.Min:
MinMax = Convert.ToDecimal(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(gr.StartIndex));
for (int i = gr.StartIndex; i <= gr.EndIndex; i++)
{
if (!Convert.IsDBNull(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i)))
{
if ((Decimal)c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i) < MinMax)
{
MinMax = Convert.ToDecimal(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i));
}
}
}
cell.Value = MinMax;
break;
case C1.Win.C1TrueDBGrid.AggregateEnum.Max:
MinMax = Convert.ToDecimal(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(gr.StartIndex));
for (int i = gr.StartIndex; i <= gr.EndIndex; i++)
{
if (!Convert.IsDBNull(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i)))
{
if ((int)c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i) > MinMax)
{
MinMax = Convert.ToDecimal(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i));
}
}
}
cell.Value = MinMax;
break;
case C1.Win.C1TrueDBGrid.AggregateEnum.Custom:
string strGridName = c1TrueDBGrid1.Name;
switch (strGridName)
{
case "Data Analysis - Sales":
Sum = 0;
Denominator = 0;
switch (CurrentDColumn.DataColumn.DataField)
{
case "MarginPercent":
for (int i = gr.StartIndex; i <= gr.EndIndex; i++)
{
if (!Convert.IsDBNull(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i)))
{
if (!Convert.IsDBNull(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i))) Sum += Convert.ToDecimal(c1TrueDBGrid1.Columns["Margin"].CellValue(i));
if (!Convert.IsDBNull(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i))) Denominator +=Convert.ToDecimal(c1TrueDBGrid1.Columns["ExtendedPrice"].CellValue(i));
}
}
break;
case "PercentLate":
for (int i = gr.StartIndex; i <= gr.EndIndex; i++)
{
if (!Convert.IsDBNull(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i)))
{
if (!Convert.IsDBNull(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i))) Sum += Convert.ToDecimal(c1TrueDBGrid1.Columns["LateShipQty"].CellValue(i));
if (!Convert.IsDBNull(c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].CellValue(i))) Denominator += Convert.ToDecimal(c1TrueDBGrid1.Columns["QtyShip"].CellValue(i));
}
}
break;
}
Sum *= 100;
if (Denominator > 0)
{
cell.Value = String.Format("##,###,##0.00", Sum / Denominator);
}
else
{
cell.Value = 0;
}
break;
}
break;
case C1.Win.C1TrueDBGrid.AggregateEnum.None:
break;
default:
cell.Value = c1TrueDBGrid1.Columns[CurrentDColumn.DataColumn.DataField].Aggregate.ToString();
break;
}
cell.Style.AlignHorz = (XLAlignHorzEnum)CurrentDColumn.Style.HorizontalAlignment;
}
}
xcol = xcol + 1;
}
col = col + 1;
}
}
}
else
{
//Rows
for (row = 0; row <= (c1TrueDBGrid1.SelectedRows.Count - 1); row++)
{
col = 0;
xcol = 0;
foreach (C1DisplayColumn CurrentDColumn in c1TrueDBGrid1.Splits[0].DisplayColumns)
{
if ((c1TrueDBGrid1.Splits[0].DisplayColumns[col].Visible == true) && (c1TrueDBGrid1.Splits[0].DisplayColumns[col].Width != null))
{
cell = sheet[row + 1, xcol];
if (CurrentDColumn.DataColumn.CellValue(c1TrueDBGrid1.SelectedRows[row]) is DateTime)
{
cell.Value = (string)CurrentDColumn.DataColumn.CellText(c1TrueDBGrid1.SelectedRows[row]);
}
else
{
if (CurrentDColumn.DataColumn.NumberFormat != string.Empty & CurrentDColumn.DataColumn.NumberFormat != "Percent")
{
if (CurrentDColumn.DataColumn.CellText(c1TrueDBGrid1.SelectedRows[row]) != string.Empty)
{
cell.Value = Convert.ToDecimal(CurrentDColumn.DataColumn.CellText(c1TrueDBGrid1.SelectedRows[row]));
}
}
else
{
cell.Value = CurrentDColumn.DataColumn.CellValue(c1TrueDBGrid1.SelectedRows[row]);
}
}
//cell.Style = styleMain
cell.Style = new C1.C1Excel.XLStyle(book);
cell.Style.Font = new Font("Tahoma", 9, FontStyle.Regular);
cell.Style.ForeColor = Color.Black;
sheet.Columns[xcol].Width = c1TrueDBGrid1.Splits[0].DisplayColumns[col].Width * 18;
cell.Style.AlignHorz = (XLAlignHorzEnum)CurrentDColumn.Style.HorizontalAlignment;
xcol = xcol + 1;
}
col = col + 1;
}
}
}
#endregion
//Footers
col = 0;
xcol = 0;
if (SelectedRowsOnly == 0 & c1TrueDBGrid1.GroupedColumns.Count == 0)
{
foreach (C1DisplayColumn CurrentDColumn in c1TrueDBGrid1.Splits[0].DisplayColumns)
{
if ((c1TrueDBGrid1.Splits[0].DisplayColumns[col].Visible == true) && (c1TrueDBGrid1.Splits[0].DisplayColumns[col].Width != null))
{
Debug.Write(CurrentDColumn.DataColumn.Caption + " - " + c1TrueDBGrid1.Splits[0].DisplayColumns[col].Width + "\\n");
cell = sheet[row + 1, xcol];
cell.Value = CurrentDColumn.DataColumn.FooterText;
cell.Style = new C1.C1Excel.XLStyle(book);
cell.Style.AlignHorz = (XLAlignHorzEnum)CurrentDColumn.FooterStyle.HorizontalAlignment;
xcol = xcol + 1;
}
col = col + 1;
}
}
if (blnContinue)
{
//c1TrueDBGrid1.ExportToExcel(book, strTitle);
book.Save("ExpectedResult.xls");
}
}
Now on exporting the grouped C1TrueDbGrid, proper indentation is shown for multi level grouped columns. This way it is more clear to the user what level are they looking in the spreadsheet. 
Download Sample
GrapeCity
GrapeCity Developer Tools