setclip getclip formula #value! problem

Posted by: lonewolfthe3rd on 8 September 2017, 1:40 pm EST

  • Posted 8 September 2017, 1:40 pm EST



    Normal
    0


    21


    false
    false
    false

    PL
    X-NONE
    X-NONE











































































































































































    /* Style Definitions */
    table.MsoNormalTable
    {mso-style-name:Standardowy;
    mso-tstyle-rowband-size:0;
    mso-tstyle-colband-size:0;
    mso-style-noshow:yes;
    mso-style-priority:99;
    mso-style-qformat:yes;
    mso-style-parent:"";
    mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
    mso-para-margin:0cm;
    mso-para-margin-bottom:.0001pt;
    mso-pagination:widow-orphan;
    font-size:11.0pt;
    font-family:"Calibri","sans-serif";
    mso-ascii-font-family:Calibri;
    mso-ascii-theme-font:minor-latin;
    mso-fareast-font-family:"Times New Roman";
    mso-fareast-theme-font:minor-fareast;
    mso-hansi-font-family:Calibri;
    mso-hansi-theme-font:minor-latin;}



    Thought an easy thing...

     



    Have three sheets, and problem exist only in one.Problem is close to Clip operations (I'm sure):



    Need to copy o big data from one Spread (fP_Source) to second (fP_Dest).

     



    First I using GetClipValue:



    Wartosc = fP_Source.Sheets(1).GetClipValue(Wiersz,
    Kolumna, LiczbaWierszy, LiczbaKolumn)



     



    Then: SetClip



    fP_Dest.Sheets(DestIdSheet).SetClipValue(Wiersz, Kolumna,
    LiczbaWierszy, LiczbaKolumn, Wartosc)



     



     



    Works perfect (a big data is copying very fast), but after
    "Set" cannot obtain any data when use formula!



    Have tried everything: fP_Dest.Refresh()
    fP_Dest.EditModePermanent, SetClipSpecial (not work at all) etc (look
    below on test code).



     



     



                fP_Dest.SuspendLayout()



               
    fP_Dest.ActiveSheet.AutoCalculation = False



               
    fP_Dest.ActiveSheet.AutoUpdateNotes = False



               
    'Docelowy



                Wiersz
    = 0



                Kolumna
    = DestStartCol



     



               
    'fP_Dest.EditModePermanent = True



               
    fP_Dest.Sheets(DestIdSheet).SetClipValue(Wiersz, Kolumna, LiczbaWierszy,
    LiczbaKolumn, Wartosc)



                'Dim
    TypNumeryczny As New FarPoint.Win.Spread.CellType.NumberCellType



     



                'For iI
    As Int16 = Kolumna To Kolumna + LiczbaKolumn - 1



                '    'fP_Dest.Sheets(1).Columns(iI).CellType =
    TypNumeryczny



                '    fP_Dest.Sheets(1).Cells(1, iI).Value =
    fP_Dest.Sheets(1).Cells(1, iI).Value



                '    fP_Dest.Sheets(1).Cells(2, iI).Value =
    fP_Dest.Sheets(1).Cells(2, iI).Value



                '    fP_Dest.Sheets(1).Cells(3, iI).Value =
    fP_Dest.Sheets(1).Cells(3, iI).Value



     



                'Next



               
    'fP_Dest.Sheets(DestIdSheet).SetClipSpecial(Wiersz, Kolumna,
    LiczbaWierszy, LiczbaKolumn, Wartosc, ClipboardPasteOptions.Values)



     



               
    fP_Dest.Refresh()



               
    'fP_Dest.EditModePermanent = False



     



               
    'fP_Dest.Refresh()



     



    After run code have following situation:

    From A1 to A3 have doubles;

    put a formula (by
    hand in sheet 1 - fp_dest; in cell c1) ex "=A1" works; "=A1*23" works correct too, but
    "=MIN(A1:A3)" and any other formulas don't!



    Noticed, that when enter in edit mode and refresh a cell,
    formulas work - formulas "understand" data in sheet.



     

    Have no idea, what to do.
  • Replied 8 September 2017, 1:40 pm EST

    Hello,


    If you are having trouble with formulas not working after a clip, you may need to call the LoadFormulas method. The problem with some formulas not working is if you have strings (even if it is "4"), then Spread can not parse these values in the array. If the values in the Spread are coming form a database, then it sounds like they are coming in as string. When you enter editmode and exit editmode, the CellType's (GeneralCellType) parser is parsing the value in the cell as a number and the formula can parse the number in the array function.

  • Replied 8 September 2017, 1:40 pm EST

    Thank you scott - in minute I'll try LoadFormulas

    in meantime have few suggestion to get/setclipvalue methods:

     

    Normal
    0


    21


    false
    false
    false

    PL
    X-NONE
    X-NONE











































































































































































    /* Style Definitions */
    table.MsoNormalTable
    {mso-style-name:Standardowy;
    mso-tstyle-rowband-size:0;
    mso-tstyle-colband-size:0;
    mso-style-noshow:yes;
    mso-style-priority:99;
    mso-style-qformat:yes;
    mso-style-parent:"";
    mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
    mso-para-margin:0cm;
    mso-para-margin-bottom:.0001pt;
    mso-pagination:widow-orphan;
    font-size:11.0pt;
    font-family:"Calibri","sans-serif";
    mso-ascii-font-family:Calibri;
    mso-ascii-theme-font:minor-latin;
    mso-fareast-font-family:"Times New Roman";
    mso-fareast-theme-font:minor-fareast;
    mso-hansi-font-family:Calibri;
    mso-hansi-theme-font:minor-latin;}



    I think something wrong in GET/SET and it's important, I think.



    The first



    Done this way:



    In Source Spread defined numeric celltypes for all cells. Next use get/set
    values.



    Before paste with setclipvalue in destination had defined numeric
    celltype (for sure).



    Problem not solved!



    The same - if type by hand in dest object, en example, =A1*A2 - works ok.



    If type =MIN(A1:A3) #value! Error.



    It's not correct behavior.



     



    Solved my problem in this way: use simple loop for/next with cell.value
    method.



     Works ok.



                For Kol As Integer =
    Kolumna To Kolumna + LiczbaKolumn - 1



                    For Wie As Long =
    Wiersz To Wiersz + LiczbaWierszy - 1



                       
    fP_Dest.Sheets(Id_Dest).Cells(Wie, Kol).Value =
    fP_Source.Sheets(Id_Sour).Cells(Wie, Kol).Value



                    Next



                Next



     



    But...



    It's not
    ok - getclipvalue and setclipvalue suggest, that I can copy/paste values (not
    strings, not objects but values). Especially, that simple formula =A1*A2 works with
    get/set "values". If this one crash, it could be a logic behavior.



    Not this
    situations.



     



    I'm
    starting calculations, so I "discover" this "flower". Till yesterday
    I wasn't known abut strange behaviors of get/set value methods.



    It's a
    bug in my optionion. Big bug.



     

  • Replied 8 September 2017, 1:40 pm EST

    FpSpread1.LoadFormulas(True) - not working

    Simply I add to destination spread - in documentation I found example with adding sheet:

    fpS.Sheets.Add(FarPoint.Win.Serializer.LoadObject(GetType(FarPoint.Win.Spread.SheetView), Application.StartupPath & "\" & My.Resources.SubPathSerwer & "\" & "SavedSheet.xml", "RootNode"))

     and get a nice info from my log:

    2008-12-11 23:43:16##>Blad tworzenia struktury do obliczen serwera: 53 | Could not find file 'H:\NET_Projekty\EDM\bin\Debug\Serwer\SavedSheet.xml'.

     

    Look - don't understand a reason of getting file name - have two spread objects. That's all.

     

  • Replied 8 September 2017, 1:40 pm EST

    Hello,


    In the first reply post, you can find out exactly what is happening by debugging the Value property of each of the cells being referenced in the non working formula. If the value is something like "4" (string), then this is why it is not calculating and it is not a bug. See my previous post. If the value is something like 4 (integer), then there may be a problem and could you send us a small zipped project reproducing this issue for us to debug?


    In the second reply post, the code you are using is telling the Spread Seriailizer to load an XML file from the file location you gave. The log file is giving a correct error message if there is not a file in that location.

Need extra support?

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

Learn More

Forum Channels