Perl操作Excel：如何排序工作表及实现隐藏表点击显示？

1. Sort Extra Sheets Alphabetically After Fixed Sheets #

Create the Summary and Detail sheets first, then collect all extra sheet names, sort them alphabetically, and add them in the sorted order. This keeps the first two sheets fixed while extras are ordered by name.

2. Enable Hyperlinks to Hidden Sheets #

Excel blocks direct hyperlinks to hidden sheets by default. Fix this by adding a VBA macro that unhides and activates the target sheet when a hyperlink is clicked.

Step-by-Step Implementation #

Perl Script

use strict;
use warnings;
use Excel::Writer::XLSX;

# Create output workbook
my $workbook = Excel::Writer::XLSX->new('sorted_hidden_sheets.xlsx');

# Add fixed sheets (Summary first, then Detail)
my $summary = $workbook->add_worksheet('Summary');
my $detail = $workbook->add_worksheet('Detail');

# List of extra sheet names (replace with your actual sheet names)
my @extra_sheets = qw(trains cars planes);

# Sort extra sheets alphabetically
my @sorted_extras = sort @extra_sheets;

# Hash to store sheet objects for hyperlinking
my %sheets;

# Add sorted extra sheets and set to hidden by default
foreach my $name (@sorted_extras) {
    my $sheet = $workbook->add_worksheet($name);
    $sheet->set_hidden(1); # Hide the sheet
    $sheets{$name} = $sheet;
    
    # Optional: Add test content to the sheet
    $sheet->write('A1', "This is the $name sheet");
}

# Add hyperlinks to Summary sheet
my $row = 0;
foreach my $name (@sorted_extras) {
    # Create internal hyperlink to the sheet's A1 cell
    $summary->write_url(
        $row, 0,
        qq{internal:'$name'!A1},
        $workbook->add_format({ color => 'blue', underline => 1 })
    );
    $summary->write($row, 1, "Go to $name");
    $row++;
}

# Add VBA project to handle hyperlink clicks (requires vba_project.bin)
$workbook->add_vba_project('vba_project.bin');

# Close the workbook
$workbook->close();

Create the VBA Project File

1. Open Excel and create a new macro-enabled workbook (.xlsm).
2. Rename the first sheet to Summary.
3. Press Alt+F11 to open the VBA editor.
4. In the Project Explorer, double-click the Summary sheet to open its code window.
5. Paste the following macro code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim sheetName As String
    ' Extract sheet name from hyperlink address (remove leading #)
    sheetName = Mid(Target.Address, 2)
    
    ' Unhide and activate the target sheet
    On Error Resume Next
    With ThisWorkbook.Sheets(sheetName)
        .Visible = xlSheetVisible
        .Activate
    End With
    On Error GoTo 0
End Sub

6. Save the workbook.
7. Use the extract_vba utility (included with Excel::Writer::XLSX) to generate the VBA project bin file:

extract_vba your_macro_file.xlsm > vba_project.bin

Place this vba_project.bin file in the same directory as your Perl script.

How It Works #

• Sorting: Extra sheets are sorted alphabetically and added after Summary and Detail, maintaining their fixed positions.
• Hiding: Each extra sheet is hidden by default using set_hidden(1).
• Hyperlink Handling: When a hyperlink in Summary is clicked, the VBA macro intercepts the event, unhides the target sheet, and activates it—allowing navigation to previously hidden sheets.

内容的提问来源于stack exchange，提问作者Sheila