Saving C1FlexGrid to excel and print with cell borders

Posted by: wknauf on 6 November 2019, 7:38 pm EST

  • Posted 6 November 2019, 7:38 pm EST

    Hi C1,

    see attached sample: a C1FlexGrid is saved to excel. When printing, no borders/grid lines are printed.
    C1ExcelExport.zip

    A customer asked whether it would be possible to export the C1FlexGrid cell borders (of the "Normal" style) to excel, too. Thus, the borders would be visible in the printed document.

    The customer could activate printing the gridlines in the print settings (see screenshot, taken from German office):

    But the C1FlexGrid borders might be altered (removed for some cells) in the original grid, so it would be best if the grid borders were exported.

    Do you see any chance to add this to the grid - e.g. by adding a "FileFlags" enum value?

    I will try to add the borders by doing some postprocessing using C1Excel.

    Best regards

    Wolfgang
  • Replied 6 November 2019, 8:55 pm EST

    Hi Wolfgang,

    I've asked the developers to consider this as an enhancement [Internal Tracking ID: 404771].
    I'll let you know once we receive more information on this.

    Regards,
    Jitender
  • Replied 6 November 2019, 10:33 pm EST

    Thanks!

    While coding my own workaround, I noticed some special cases that should be kept in mind:

    a) current cell style has DefinedElements.Border - then set the style color of this cell to the right/bottom border. The width might also be matched to an excel line style.
    b) current cell style has no defined border - then it has the default border. The RGB value 240/240/240 might be too bright to be usable in excel printout - better use a darker color?
    c) first row/col should also have a left/top border.

    Best regards

    Wolfgang
  • Replied 7 November 2019, 5:33 pm EST

    Wolfgang, thanks for posting your observations while working on the workaround. I'm sure these would be helpful to others while this is not directly supported.

    Regards,
    Jitender
  • Replied 29 December 2020, 6:57 pm EST

    As another user asked the same question, I post here my code to copy the borders:

    The code uses C1Excel to read the saved grid and apply the borders.


    /// <summary>
    /// Copies the FlexGrid Borders (Gridlines) to Excel.
    /// </summary>
    /// <param name="flexGrid">source Flexgrid</param>
    /// <param name="_strFileName">Grid was saved to this file. Will be opened by C1Excel and saved afterwards.</param>
    private static void SaveGridExcel_ApplyBorders(C1FlexGrid flexGrid, string _strFileName)
    {
    // default border color of Normal-Style:
    Color COLOR_BORDER_REPLACE = Color.FromArgb(240, 240, 240);

    // default border color is replaced by something darker:
    COLOR_BORDER_DEFAULT = Color.FromArgb(212, 208, 200);

    C1XLBook excelBook = new C1XLBook();
    excelBook.Load(_strFileName);
    XLSheet sheetDispo = excelBook.Sheets[0];

    int intExcelRow = 0;
    for (int intRow = 0; intRow < flexGrid.Rows.Count; intRow++)
    {
    //Ignore invisible rows:
    if (flexGrid.Rows[intRow].Visible == true)
    {
    int intExcelCol = 0;
    for (int intCol = 0; intCol < flexGrid.Cols.Count; intCol++)
    {
    //Ignore invisible cols
    if (flexGrid.Cols[intCol].Visible == true)
    {
    //Get cell style in grid:
    CellStyle styleCell = flexGrid.GetCellStyleDisplay(intRow, intCol);
    if (styleCell != null)
    {
    bool bolCanExportBorder = false;
    //Dies the style define a border? If yes then check whether it has a width:
    if (styleCell.DefinedElements.HasFlag(StyleElementFlags.Border) == true && styleCell.Border.Style != BorderStyleEnum.None && styleCell.Border.Width != 0)
    {
    bolCanExportBorder = true;
    }
    else if (styleCell.DefinedElements.HasFlag(StyleElementFlags.Border) == false)
    {
    //"Normal" style has no border definition:
    bolCanExportBorder = true;
    }
    if (bolCanExportBorder == true)
    {
    XLCell cellAktuell = sheetDispo.GetCell(intExcelRow, intExcelCol);
    if (cellAktuell == null)
    {
    throw new EXSystemException("Could not find cell " + intExcelRow + "/" + intExcelCol + " (grid cell: " + intRow + "/" + intCol + ") in Excel file!");
    }

    //Create new style:
    XLStyle styleAktuell = cellAktuell.Style.Clone();
    //Default Grid border color is very light replace by something darker
    Color colorBorder = styleCell.Border.Color;
    if (styleCell.Border.Color.R == COLOR_BORDER_REPLACE.R && styleCell.Border.Color.G == COLOR_BORDER_REPLACE.G && styleCell.Border.Color.B == COLOR_BORDER_REPLACE.B)
    {
    colorBorder = COLOR_BORDER_DEFAULT;
    }
    //Set BorderStyle for all:
    //Actually, the width should be set, too...
    if (styleCell.Border.Direction == BorderDirEnum.Both || styleCell.Border.Direction == BorderDirEnum.Horizontal)
    {
    //Set only Bottom border - thats the way the grid does it.
    styleAktuell.BorderColorBottom = colorBorder;
    styleAktuell.BorderBottom = XLLineStyleEnum.Thin;
    //In row 0 set also top border
    if (intExcelRow == 0)
    {
    styleAktuell.BorderColorTop = colorBorder;
    styleAktuell.BorderTop = XLLineStyleEnum.Thin;
    }
    }
    if (styleCell.Border.Direction == BorderDirEnum.Both || styleCell.Border.Direction == BorderDirEnum.Vertical)
    {
    //Set only Right border - thats the way the grid does it.
    styleAktuell.BorderColorRight = colorBorder;
    styleAktuell.BorderRight = XLLineStyleEnum.Thin;

    //In col 0 set also left border
    if (intExcelCol == 0)
    {
    styleAktuell.BorderColorLeft = colorBorder;
    styleAktuell.BorderLeft = XLLineStyleEnum.Thin;
    }
    }

    cellAktuell.Style = styleAktuell;
    }
    }

    //Increment excel col index:
    ++intExcelCol;
    }
    }

    //Increment excel row index:
    ++intExcelRow;
    }
    }
    excelBook.Save(_strFileName);
    excelBook.Dispose();
    }
  • Replied 29 December 2020, 7:00 pm EST

    The other question is here: https://www.grapecity.com/forums/winforms-edition/not-working-to-display-ima
  • Replied 30 December 2020, 9:31 pm EST

    Hi Wolfgang,

    Thank You for sharing the workaround code. While the team investigates on this feature, it will definitely help others.

    Regards
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels