Export in Hierarchy

Posted by: vishnupriya on 8 September 2017, 2:16 pm EST

  • Posted 8 September 2017, 2:16 pm EST

    Hi,


    We have hierarchy data. And when we are trying to export the data to the Excel, it is only exporting the parent table data only.


    Can some one help us How I can export the data having hierarchy?


    Thanks in advance,
    Vishnupriya

  • Replied 8 September 2017, 2:16 pm EST

    Hello,


    In order to increase the performance you can try setting AutoUpdateNotes to false if you are not using the stick notes to prevent the component from checking for sticky notes that need to be made visible or hidden or moved. And also turning off the AllowCellOverflow property increases the performance of the layout calculations, because that feature requires lots of text width calculations on each change to the data in a cell.


    Hope the above mentioned information will help you. Thanks.


     

  • Replied 8 September 2017, 2:16 pm EST

    Vishnupriya,


    Spread has a hierarchy feature (i.e. parent worksheet containing child worksheets).  Excel does not support this feature.  Thus, this feature can not be exported to Excel.

  • Replied 8 September 2017, 2:16 pm EST

    Hi,


    Thanks for the info.


    Atleast can we import all the data alone in all the levels from fpspread to the excel..?  (Without hierarchy feature)


    Thanks,


    Vishnupriya

  • Replied 8 September 2017, 2:16 pm EST

    Hello Vishnupriya,


    Yes you can export data from all the levels of Spread to Excel by flattening the hierarchy of sheets into a single sheet. But this raises several issues. For example, it is sometimes unclear how to handle formulas in the parent sheet (such as a sum of a column) may not make sense in the flattened sheet.


    But you can always perform your own flattening. Create a new flat spreadsheet. Loop through the hierarchical spreadsheet and copy the cell information (data) into the flat spreadsheet. Then export the flat spreadsheet.


    Hope this will help you. Thanks.

  • Replied 8 September 2017, 2:16 pm EST

    Thanks for the information.


    As we don't ahve any formulas we can follow this way.


    But ,is it will cause any perforamce issue  if the spread having huge data..? (as we have to loop through all the rows for copying the data)


    Thanks in advance,


    Vishnupriya

  • Replied 8 September 2017, 2:16 pm EST

    Hi Reeva,


    Can you give some sample for this. Actaullay I am planning to do as below. But it is giving error 'Unable to cast object of type 'Farpoint.Win.Spread.Row' to type 'System.Iconvertible'


    Can you please let us know How we can proceed..? Is ther any other way without explicitly going for each cell and copy to either datatable or to a new excel sheet for a hirarchy data ..?


     


    DataTable dtdata = new DataTable();


    dtdata = fpSpreadRangeDecision.ActiveSheet.GetDataView(true).ToTable().Clone();


    FarPoint.Win.Spread.SheetView ssChild;


    FarPoint.Win.Spread.SheetView ssGrandChild;


    ssChild = ssGrandChild = null;


    for (int Rownum = 0; Rownum < fpSpreadRangeDecision.ActiveSheet.Rows.Count; Rownum++)


    {


     


    //Import First level Data Row


     


    dtdata.Rows.Add(fpSpreadRangeDecision.ActiveSheet.Rows[Rownum]);


    if (fpSpreadRangeDecision.Sheets[0].ChildRelationCount > 0)


    ssChild = fpSpreadRangeDecision.Sheets[0].GetChildView(Rownum, 0);


    if (ssChild != null)


    {


    for (int i = 0; i < ssChild.RowCount; i++)


    {


    //Import First level child Row


    //dtdata.Rows.Add(ssChild.RowsIdea);


    if (ssChild.ChildRelationCount > 0)


    ssGrandChild = ssChild.GetChildView(i, 0);


    else


    ssGrandChild = null;


    if (ssGrandChild != null)


    for (int j = 0; j < ssGrandChild.RowCount; j++)


    //Import Second level child Row


    }


    }


     

  • Replied 8 September 2017, 2:16 pm EST

    Hello,

    Below is an example of copying ChildSheet to another Spread,

    Dim sv, sv1 As New FarPoint.Win.Spread.SheetView
            sv = FpSpread1.Sheets(0).GetChildView(0, 0)
            sv1 = FpSpread1.ActiveSheet.GetChildView(0, 0).GetChildView(0, 0)
            For i As Integer = 0 To sv.Rows.Count - 1
                For j As Integer = 0 To sv.Columns.Count - 1
                    FpSpread2.Sheets(0).Cells(i, j).Value = sv.Cells(i, j).Text
                Next
            Next
            For i As Integer = 0 To sv1.Rows.Count - 1
                For j As Integer = 0 To sv1.Columns.Count - 1
                    FpSpread2.Sheets(1).Cells(i, j).Value = sv1.Cells(i, j).Text
                Next
            Next

    You may also try cloning the ChildSheet view e.g.

       Dim stream As System.IO.Stream = System.IO.File.Open("spreadsave.bin", System.IO.FileMode.CreateNew)
            Dim bf As New System.Runtime.Serialization.Formatters.Binary.BinaryFormatter()
            bf.Serialize(stream, sv)
            stream.Close()
            stream = System.IO.File.Open("spreadsave.bin", System.IO.FileMode.Open)
            FpSpread1.Sheets.Add(bf.Deserialize(stream))

     I hope it will help you.

     

    Thanks,

     

  • Replied 8 September 2017, 2:16 pm EST

    Thanks Deepak for the code.


    Is there any way to copy the entire row of a spread at one time instead of each cell.


    As we have lot of columns in the spread, copying each cell will cause performance issue. 


    Thanks in advance,


    Vishnupriya

  • Replied 8 September 2017, 2:16 pm EST

    Hello,

    You may use SpreadSheet's GetClip() & SetClip()method to copy an entire Row.for example,

    Dim s As String
    s = FpSpread1.Sheets(0).GetClip(4, 0, 1, -1)
    FpSpread2.Sheets(0).SetClip(4, 0, 1, -1, s) 

    I hope it will help you.

    Thanks,

     

Need extra support?

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

Learn More

Forum Channels