Unable to export merged or spanned cells to Excel.

Posted by: xucaen on 8 September 2017, 2:21 pm EST

  • Posted 8 September 2017, 2:21 pm EST

    Hi, I am trying to export a sheet to Excel. The sheet has some columns with a ColumnSpan of 4 but it seems to be lost when I save the file as Excel. I have tried also to set the MergePolicy for the rows in question to MergePolicy.Always but that still doesn't seem to work. I am using FarPoint Spread for Windows Form 2.5.2008.2005.


     


    Thanks,

  • Replied 8 September 2017, 2:21 pm EST

    Hello,


    We are unable to replicate the issue with last build of Spread version 2.5 which is 2.5.2015.2005 using the below code and all the exported cells are merged and even spanned:



           private void ImportExcel_Load(object sender, EventArgs e)
            {
                fpSpread1.Sheets[0].SetRowMerge(-1, FarPoint.Win.Spread.Model.MergePolicy.Always);
                fpSpread1.Sheets[0].SetColumnMerge(-1, FarPoint.Win.Spread.Model.MergePolicy.Always);


                fpSpread1.ActiveSheet.Cells[1, 1].Text = "These six cells are spanned.";
                fpSpread1.ActiveSheet.Cells[5, 5].Text = "This is a long text which gets merged on exporting to excel";
                fpSpread1.ActiveSheet.AddSpanCell(1, 1, 2, 3);
            }
            private void Save_Click(object sender, EventArgs e)
            {
                fpSpread1.SaveExcel(@"d:\ColSpanAndMerge.xls", FarPoint.Excel.ExcelSaveFlags.NoFlagsSet);
            }


    Please try upgrading your spread to the last build available of Spread version 2.5 and you can download it from this link ftp://ftp.fpoint.com/SpreadWinForms25/


    Thanks.

  • Replied 8 September 2017, 2:21 pm EST

    When I use the FarPoint.Excel.ExcelSaveFlags.NoFlagsSet parameter, then column headers are not saved. Also it didn't fix the issue with spanned cells being saved correctly.  I am currently using fpSpread1.SaveExcel(fileName, FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly)  to save the column headers. I am using Spread version 2.5.2008.2005.


     


    Thanks,

  • Replied 8 September 2017, 2:21 pm EST

    Hello,


    When we save Spread in Excel, then Excel's default Column and Row Header's text are not replaced by the Spread's Headers, rather Spread's Headers gets accomodated in Excel's initial Rows and Columns because the Excel's default Column headers (A, B, C) can not be replaced with Spread's Headers. However you can make them hidden after exporting Spread to Excel by going in Tools>options>view tab>uncheck row and column headers.


    And also along with that i would suggest you to upgrade your spread to the latest build of Spread version 2.5 which is 2.5.2015.2005. And here is the code i used to test the issue, wherein Spread's ColumnHeaders are exported to Excel initial rows with spanned cells:



            private void ImportExcel_Load(object sender, EventArgs e)
            {
                fpSpread1.Sheets[0].SetRowMerge(-1, FarPoint.Win.Spread.Model.MergePolicy.Always);
                fpSpread1.Sheets[0].SetColumnMerge(-1, FarPoint.Win.Spread.Model.MergePolicy.Always);


                fpSpread1.ActiveSheet.Cells[1, 1].Text = "These six cells are spanned.";
                fpSpread1.ActiveSheet.Cells[5, 5].Text = "This is a long text which gets merged on exporting to excel";
                fpSpread1.ActiveSheet.AddSpanCell(1, 1, 2, 3);


                fpSpread1.ActiveSheet.ColumnHeader.RowCount = 3;
                fpSpread1.ActiveSheet.ColumnHeader.Cells[0, 0].Text = "LONG COLUMN HEADER";
                fpSpread1.ActiveSheet.ColumnHeader.Cells[0, 0].ColumnSpan = 3;
                fpSpread1.ActiveSheet.ColumnHeader.Cells[0, 0].RowSpan = 2;
            }
            private void Save_Click(object sender, EventArgs e)
            {
                fpSpread1.SaveExcel(@"d:\ColSpanAndMerge.xls", FarPoint.Win.Spread.Model.IncludeHeaders.BothCustomOnly);
            }



    Hope this will help you. Thanks.

  • Replied 8 September 2017, 2:21 pm EST

    Hi, I appreciate all of your help. Thank you.
Need extra support?

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

Learn More

Forum Channels