Allowing access to Autofilters in a protected worksheet

[jmcnamara]

On the CPAN RT site someone asked about how to allow access to
autofilters in a protected worksheet.

In ordinary circumstances an autofilter in Excel is locked if the
worksheet is protected. However, Excel 2003 allows you to grant access
to the following features on a locked worksheet (Excel 97 only allowed
control of the first two):

SelectLockedCells
SelectUnlockedCells

FormatCells
FormatColumns
FormatRows

InsertColumns
InsertRows
InsertHyperlinks

DeleteColumns
DeleteRows

Sort

UseAutoFilters
UsePivotTableReports

EditObjects
EditScenarios

This additional control isn't available in Spreadsheet::WriteExcel,
however it is available via Stepan Riha's
Spreadsheet::WriteExcel::Worksheet::SheetProtection module:

http://search.cpan.org/~sriha/Spreadsheet-WriteExcel-Worksheet-SheetProtection/

Here is an example, see the SheetProtection documentation for more
information:

#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Worksheet::SheetProtection;

my $workbook = Spreadsheet::WriteExcel-
>new('unprotected_filter.xls');
my $worksheet = $workbook->add_worksheet();
my $bold = $workbook->add_format(bold => 1);


# Protect the worksheet but allow Autofilters to work.
$worksheet->protect();
$worksheet->sheet_protection( UseAutoFilters => 1 );

# Add some formatting.
$worksheet->set_column('A:D', 12);
$worksheet->set_row(0, 20, $bold);

# Extract the data embedded at the end of this file.
my @headings = split ' ', <DATA>;
my @data;
push @data, [split] while <DATA>;

$worksheet->write('A1', \@headings);
$worksheet->autofilter('A1:D51');
$worksheet->write('A2', [[@data]]);


__DATA__
Region Item Volume Month
East Apple 9000 July
East Apple 5000 July
South Orange 9000 September
North Apple 2000 November
West Apple 9000 November
South Pear 7000 October
North Pear 9000 August
West Orange 1000 December
West Grape 1000 November
South Pear 10000 April
West Grape 6000 January


John.
--

[Body]

thank you, John
It helped to my project ....
Great job !!!!

Body.