Delete hidden columns in excel after exported to excel

Posted by: vjsvjs on 8 September 2017, 3:21 pm EST

  • Posted 8 September 2017, 3:21 pm EST

    Hi,

    Grid1.SaveExcel(xDestinationPath, FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders) ' Export the Excel File

    Dim startInfo As New ProcessStartInfo("EXCEL.exe")
    startInfo.WindowStyle = ProcessWindowStyle.Maximized
    startInfo.Arguments = """" & xDestinationPath & """"
    Process.Start(startInfo)
    Now I have to delete hidden columns in excel file only,
  • Replied 8 September 2017, 3:21 pm EST

    Hi,

    Do you set Protect = false on the sheet before exporting to Excel?
    http://sphelp.grapecity.com/WebHelp/SpreadNet9/WF/webframe.html#FarPoint.Win.Spread~FarPoint.Win.Spread.SheetView~Protect.html
    If you use SaveExcel to save the contents of Spread to a new XLS file, then user should be able to unhide and delete the hidden columns after opening the file in Excel (unless the sheet is protected).
    If the sheet is protected, then the user must first unprotect the sheet in Excel.

    Regards,
    -Sean
  • Marked as Answer

    Replied 8 September 2017, 3:21 pm EST

    Hi vjsvjs,

    maybe this is what you want.

    1. Dim sp As New FarPoint.Win.Spread.FpSpread
    2. create a copy from each sheet and add it to the new fpSpread
    3. remove from each sheet the hidden columns
    4. Save the created copy of fpSpread to Excel

    You may use the following functions (VB)

    Public Function CopySheet(ByVal sheet As Spread.SheetView) As Spread.SheetView
    Dim newSheet As Spread.SheetView = Nothing
    If sheet IsNot Nothing Then
    newSheet = DirectCast(FarPoint.Win.Serializer.LoadObjectXml(GetType(FarPoint.Win.Spread.SheetView), FarPoint.Win.Serializer.GetObjectXml(sheet, "CopySheet"), "CopySheet"), FarPoint.Win.Spread.SheetView)
    End If
    Return newSheet
    End Function

    Public Function RemoveHiddenColumns(ByVal sheet As Spread.SheetView)
    For col As Integer = sheet.ColumnCount - 1 To 0 Step -1
    If Not sheet.Columns(col).Visible Then
    sheet.RemoveColumns(col, 1)
    End If
    Next
    Return sheet
    End Function

    Hope this helps,

    Regards

    -Piet
Need extra support?

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

Learn More

Forum Channels