SheetCorner not exported to Excel

Posted by: qiismo on 8 September 2017, 2:28 pm EST

  • Posted 8 September 2017, 2:28 pm EST

    .NET 3.5
    FarPoint Spred fro Windows Forms 4  Version 4.0.3512.2008


    When exporting to Excel, SaveExcel("filename", xcelSaveFlags.SaveBothCustomRowAndColumnHeaders),  the SheetCorner is not exported.

  • Replied 8 September 2017, 2:28 pm EST

    Thanks, but is seems strange that there is no way to export SheetCorners, which causes, obviously, the lose of information. 
  • Replied 8 September 2017, 2:28 pm EST

    Hello,

    I am currently working on your issue in order to provide you with the workaround for the same. Need some more time. Will get back to you soon.

    Thanks.

  • Replied 8 September 2017, 2:28 pm EST

    Hello,

    This is the intended behavior wherein the Spread's SheetCorner is not exported to Excel, just the RowHeader and Column Header's are exported and that too only when they are customized. If the Row/Column headers are kept to default they are not
    exported by default. So setting text for RowHeader and ColumnHeader make it customized
    and hence it is exported, but not the SheetCorner.

    Thanks.

  • Replied 8 September 2017, 2:28 pm EST

    Hello,

    I have come up with the possible workaround for the same wherein I have copied the columnHeader's text in first Row starting from second Column and similarly have copied RowHeader's text in first Column starting from second Row and then later on hide the default Column and Row Header's of the Spread before exporting it to Excel. Please have a look at the code and let us know if it helps:-

            private void FrmSheetCorner_Load(object sender, EventArgs e)

            {

                fpSpread1.Sheets[0].Rows.Count = 10;

                fpSpread1.Sheets[0].Columns.Count = 6;

                fpSpread1.Sheets[0].SheetCorner.Cells[0, 0].Text = "Corner";

                fpSpread1.Sheets[0].SheetCorner.Columns[0].Width = 100;

     
                fpSpread1.ActiveSheet.ColumnHeader.RowCount = 1;

                fpSpread1.ActiveSheet.ColumnHeader.SetClip(0, 0, 1, 4, "one\ttwo\tthree\tfour");

     
                FarPoint.Win.Spread.RowHeader r;

                string s;

                r = fpSpread1.ActiveSheet.RowHeader;

                r.SetClip(0, 0, 4, 1, "R1\r\nR2\r\nR3\r\nR4");

                s = r.GetClip(0, 0, 2, 4);

     
                string colText;

                string rowText;

     
                for (int i = 0; i < 5; i++)

                {

                    colText = fpSpread1.ActiveSheet.ColumnHeader.GetClip(0, 0, 1, i);

                    fpSpread1.ActiveSheet.SetClip(0, 1, 1, 4, colText);

     
                    rowText = fpSpread1.ActiveSheet.RowHeader.GetClip(0, 0, i, 1);

                    fpSpread1.ActiveSheet.SetClip(1, 0, 4, 1, rowText);

                }

                fpSpread1.Sheets[0].Cells[0, 0].Text = fpSpread1.Sheets[0].SheetCorner.Cells[0, 0].Text;

     
                fpSpread1.Sheets[0].FrozenColumnCount = 1;

                fpSpread1.Sheets[0].FrozenRowCount = 1;

                fpSpread1.Sheets[0].Rows[0].BackColor = Color.Silver;

                fpSpread1.Sheets[0].Cells[0, 0, fpSpread1.Sheets[0].Rows.Count - 1, fpSpread1.Sheets[0].Columns.Count - 1].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;

                fpSpread1.Sheets[0].Columns[0].BackColor = Color.Silver;

     
                fpSpread1.Sheets[0].ColumnHeader.Visible = false;

                fpSpread1.Sheets[0].RowHeader.Visible = false;

            }

            private void button2_Click(object sender, EventArgs e)

            {

                fpSpread1.SaveExcel("D:\\_SpreadCorner.xls", FarPoint.Excel.ExcelSaveFlags.NoFlagsSet);

            }

    Hope this workaround will work for you. Thanks.

  • Replied 8 September 2017, 2:28 pm EST

    Thanks.  Sure this looks like it would work; however, what it means is I have to recode my program to to essentially not use rowheaders, columnheaders and sheetcorners.   It seems my code would have to recognize the the frozen rows, columns etc, and index appropriately when dealing with cells. For example, sheet.Cell[0,0] would become sheet.Cell[frozenRowOffset, frozenColumnOffset].  Correct?
  • Replied 8 September 2017, 2:28 pm EST

    Hello,

    Yes, you are right. Since it was a workaround wherein the Column and Row headers are moved to DataArea by copying the text from headers to cells. Hence, you need to recognize the frozen rows and columns and need to deal with the cells appropriately.

    Thanks.

Need extra support?

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

Learn More

Forum Channels