Export Spread To Excel Formular does not work

Posted by: zxx2403 on 8 September 2017, 2:30 pm EST

  • Posted 8 September 2017, 2:30 pm EST

    Hi all

         I set the formular for the cell [0,2] ,code  as following

          this.fpSpread1_Sheet1.Cells[0, 2].Formula = "R1C1 + R1C2";

         After I execute the code   fpSpread1.SaveExcel("C:\\ddd.xls", FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly);

         the spread export to excel, but I find that the fomular in cells[0,2] is =R3C1+R3C2 ,But I wish it is R1C1+R1C2,

         What I can do, Thanks for your help.

         notes:

         when i used fpSpread1.SaveExcel("C:\\ddd.xls");  the result is right

     

     

  • Replied 8 September 2017, 2:30 pm EST

    Hello,

    I am unable to replicate the issue at my end wherein if the ReferenceStyle is set to R1C1 then also the formula is cell remains the same. Please have a look at the attached Excel file for the same which is being created using the code provided by you.

    Let us know if we are missing something in-order to replicate the issue.

    Thanks.


    2010/11/ddd.xls.zip
  • Replied 8 September 2017, 2:30 pm EST

    Hi 

        thanks for your reply.

        I saw your attachement.  I have changed the excel option to R1C1 ,I found the formular is =R2C1+R2C2 ,The result that I expect is R1C1 + R1C2.

        You can change the excel option  Tools -- > Options --> General -- >R1C1 reference style.

     Best Regards

  • Replied 8 September 2017, 2:30 pm EST

    Hello,

    On saving the Spread to Excel using ColumnHeadersCustomOnly SaveExcelFlag, first row in Excel is being assigned as the column headers and hence rows are moved to bottom by the number of rows in column header( default being set to 1) wherein it maintains the referenced cells and that is why you are seeing R1C1 as R2C1 and R1C2 as R2C2 which is the intended behavior.

    Thanks.

  • Replied 8 September 2017, 2:30 pm EST


    Hi



         thanks for you helping .



         I have understanded what do you mean.



         But I have a question.  












    public partial class Form1 : Form
    {
    public Form1()
    {
    InitializeComponent();
    }
    private void Form1_Load(object sender, EventArgs e)
    {
    this.fpSpread1_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
    this.fpSpread1_Sheet1.Cells[0, 0].Value = 123;
    this.fpSpread1_Sheet1.Cells[0, 1].Value = 456;
    this.fpSpread1_Sheet1.Cells[0, 2].Formula = "R1C1 + R1C2";
    }
    private void button1_Click(object sender, EventArgs e)
    {
    fpSpread1.LoadFormulas(false);
    fpSpread1.SaveExcel("C:\\ddd.xls", FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly);
    }
    }
    I run this code , In the window form . the value of the cell[0,2]  is 579 , but when i click the button . The value of [0,2] is 0 , I wish 579. 
     what is the best practiecs to process the trouble.
     thanks 
    Best Regard. 

  • Replied 8 September 2017, 2:30 pm EST

    Hello,

    This is the intended behavior wherein rows in data area of excel is being set as Column header in Excel when saved using ColumnHeaderCustomOnly enumeration and in case you want to maintain the same cell references in Excel as that of Spread then you may use the None enumeration of IncludeHeaders but that will not save the custom headers being created in Spread.

    Thanks.

     

Need extra support?

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

Learn More

Forum Channels