Export FpSpread into Excel with all CellType and herder formating

Posted by: raja-parvez on 8 September 2017, 1:32 pm EST

  • Posted 8 September 2017, 1:32 pm EST

    Hey ,


    I am using fpSpread.SaveExcel method to save fpSread to excel , but it is not maintaing celltype and heder type formating. It is making simple excel without any formats.


    Please help me in exporting fpSpread to excel with all formating.


    Thanks.
     


     

  • Replied 8 September 2017, 1:32 pm EST

    Hey,


     


    fpSpread1.SaveExcel("C:\\Test3.xls", FarPoint.Excel.ExcelSaveFlags.SaveBothCustomRowAndColumnHeaders);


    _xlApp = new Microsoft.Office.Interop.Excel.Application();


    _wrkBooks = _xlApp.Workbooks;


    _wrkBook = _wrkBooks.Open("C:\\Test3.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,


    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,


    Type.Missing, Type.Missing, Type.Missing);


    _sht = (Worksheet)_wrkBook.Worksheets[1];


    Range firstRow = (Range)_sht.get_Range("A1", "N1");


     


    firstRow.Interior.ColorIndex = 50;   // here error comes.


     


    It is not allowing me to format excel file.


    I tried to open excel manually and tried to do it but I was not able to change the color , it is protected. 


     

  • Replied 8 September 2017, 1:32 pm EST

    Make sure you are using the appropriate ExcelSaveFlags.  What type of cell type and formatting are you trying to export?
  • Replied 8 September 2017, 1:32 pm EST

    Hi,


    I am using code.


    SaveExcel("C:\\Test.xls", FarPoint.Excel.ExcelSaveFlags.SaveAsViewed);


    But in number cell type I am able to add text value.


    And color of header is not exporting in excel file.


    Thnaks. 


     


     

  • Replied 8 September 2017, 1:32 pm EST

    Hello,


    In order to have the headers exported to Excel, you need to set that flag (SaveCustomColumnHeaders). You need to make sure you set the BackColor of the header cell for it to be persisted to Excel on the export. When exporting a cell as a NumberCellType to Excel, we set the format in the cell. However, Excel will not limit the input to numbers as Spread does. You can reproduce this by creating an Excel file, setting the format of one of the cells to Number and type text into it. It will be allowed.


     SaveExcel("C:\\Test.xls", FarPoint.Excel.ExcelSaveFlags.SaveAsViewed | FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders);

  • Replied 8 September 2017, 1:32 pm EST

    Thanks for reply.


    My header row is of green color , while exporting to excel it is coming in white color. I set the background color property to green.


     


    Thanks. 


     

  • Replied 8 September 2017, 1:32 pm EST

    Hello,


    What code are you using to set the color for the headers? You need to set the BackColor property of the cell. By default the Protect property of Spread is True and that causes the exported Excel file to be protected. You need to set Spread's Protect property to False before exporting to have the exported file unprotected.

  • Replied 8 September 2017, 1:32 pm EST

    Hey ,


    Not able to find spread protect property,


    I set Excel protect propert , now I am able to format header.Thanks.


    Code Working for me.


    fpSpread1.SaveExcel("C:\\Book1.xls", FarPoint.Excel.ExcelSaveFlags.SaveBothCustomRowAndColumnHeaders);


    _xlApp = new Microsoft.Office.Interop.Excel.Application();


    _wrkBooks = _xlApp.Workbooks;


    _wrkBook = _wrkBooks.Open("C:\\Book1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,


    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,


    Type.Missing, Type.Missing, Type.Missing);


    _sht = (Worksheet)_wrkBook.Worksheets[1];


    _sht.Unprotect("");    // I have added this line and start working .


    Range firstRow = (Range)_sht.get_Range("A1", "N1");


    //firstRow.RowHeight = "36";


    firstRow.Interior.ColorIndex = 50;


     


     Today is my second day with Farpoint. Thanks for your immediate reply.............


    Thanks..........


     

  • Replied 8 September 2017, 1:32 pm EST

    Hello,


    The Protect property is on the SheetView level.


    FpSpread1.Sheets[0].Protect = false;


    //then call your save code

  • Replied 8 September 2017, 1:32 pm EST

    Thankssssssssssss.
Need extra support?

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

Learn More

Forum Channels