Limit the number of worksheets in a excel file using python

Question

I need to limit the number of worksheets in a excel file to a specific number using python. The user cannot add a new worksheet once the number of sheets in that workbook reaches a particular number.

I couldn't find any solution using xlsxwriter, openpyxl or xlrd

Is there any option available using some other packages?

I dont know whether it has any such feature, since rows and columns can be restricted, i think there will be an option for this also.. I'm not sure about that — Jithin, Dec 04 '16 at 18:30
First - check if excel support it (which I'm really not sure). If it does - next thing is to see if any of the libraries implemented it. If not - it's time to implement and give it back to the open source community :) — Dekel, Dec 04 '16 at 18:32

score 1 · Accepted Answer · edited May 23 '17 at 10:30

Excel doesn't have such functionality built in. You can only disallow creating new sheets by protecting the workbook or with a VBA handler that reverses the operation immediately.

Protecting the workbook also disables other worksheet operations like moving, renaming and hiding/unhiding which may or may not be desirable.

OTOH, the VBA handler can be more intelligent than the one on the link:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    If ThisWorkbook.Worksheets.Count > <Maximum> Then
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            Sh.Delete
            .DisplayAlerts = True
            .ScreenUpdating = True
        End With
    End If
End Sub

Of course, this will have no effect if one edits the file with a 3rd-party package that doesn't run VBA, or disables macros in Excel.

To have macros, the workbook must be saved as .xlsm, or Excel would give an error upon opening.

See Working with VBA Macros — XlsxWriter Documentation about Python implementation. openpyxl cannot work with macros, only preserve them at most, and xlrd looks like being designed to only read rather than edit. Alternatively, there's always Excel's own COM interface that pywin32 can use.

Limit the number of worksheets in a excel file using python

1 Answers1