Skip to main content Skip to footer

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

MESCIUS inc.

comments powered by Disqus