Perl Tutorial

Fundamentals

Input and Output

Control Flow

Arrays and Lists

Hash

Scalars

Strings

Object Oriented Programming in Perl

Subroutines

Regular Expressions

File Handling

Context Sensitivity

CGI Programming

Misc

Creating Excel Files in Perl

Using Perl to generate Excel files can be quite useful for automating reporting tasks, data extraction, or even simple data manipulations. The most popular Perl module for this purpose is Excel::Writer::XLSX. This module allows you to create a new Excel file and write data to it.

Below is a step-by-step tutorial on how to create Excel files using Perl:

1. Installing the module

Firstly, you need to install the module. You can do this using CPAN:

cpan Excel::Writer::XLSX

2. Creating a basic Excel file

Here's a simple script that demonstrates creating an Excel file:

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

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

# Add a worksheet to the workbook
my $worksheet = $workbook->add_worksheet();

# Write data to the worksheet
$worksheet->write(0, 0, 'Hello');
$worksheet->write(0, 1, 'World');

# Close the workbook (This is important!)
$workbook->close();

Run the script, and you'll get a new file perl_excel_tutorial.xlsx with the words "Hello" and "World" written in the first row.

3. Formatting the data

You can add various formats to your data:

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

my $workbook  = Excel::Writer::XLSX->new('perl_excel_formatting.xlsx');
my $worksheet = $workbook->add_worksheet();

# Define formats
my $bold = $workbook->add_format();
$bold->set_bold();

my $red_bg = $workbook->add_format();
$red_bg->set_bg_color('red');

# Write data with formats
$worksheet->write(0, 0, 'Hello', $bold);
$worksheet->write(0, 1, 'World', $red_bg);

$workbook->close();

4. Working with multiple worksheets

You can easily add multiple worksheets to your workbook:

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

my $workbook = Excel::Writer::XLSX->new('perl_excel_sheets.xlsx');

my $sheet1 = $workbook->add_worksheet('First Sheet');
my $sheet2 = $workbook->add_worksheet('Second Sheet');

$sheet1->write(0, 0, 'This is the first sheet.');
$sheet2->write(0, 0, 'This is the second sheet.');

$workbook->close();

5. Adding formulas

Formulas can also be added to the cells:

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

my $workbook = Excel::Writer::XLSX->new('perl_excel_formulas.xlsx');
my $worksheet = $workbook->add_worksheet();

$worksheet->write(0, 0, 10);
$worksheet->write(1, 0, 20);
$worksheet->write(2, 0, '=A1+A2');

$workbook->close();

When you open the generated Excel file, you'll see the cells A1 and A2 populated with numbers and A3 with the sum of A1 and A2.

There's a lot more you can do with Excel::Writer::XLSX, like adding charts, images, or even handling larger data sets. The above examples are just a starting point, and you can find extensive documentation and more advanced examples on CPAN or in the module's documentation.

  1. Writing data to Excel in Perl:

    • Description: Create a simple Excel file and write data to it.
    • Code:
      use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new('output.xlsx');
my $worksheet = $workbook->add_worksheet();

# Write data to Excel
$worksheet->write('A1', 'Hello');
$worksheet->write('B1', 'World');

$workbook->close();

  2. Perl Excel module for spreadsheet generation:

    • Description: Use the Excel::Writer::XLSX module for creating Excel files.
    • Code:
      use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new('output.xlsx');
my $worksheet = $workbook->add_worksheet();

# Write data to Excel
$worksheet->write('A1', 'Hello');
$worksheet->write('B1', 'World');

$workbook->close();

  3. Creating Excel charts with Perl:

    • Description: Add a chart to an Excel file using Excel::Writer::XLSX.
    • Code:
      use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new('chart_example.xlsx');
my $worksheet = $workbook->add_worksheet();

# Add data to Excel
my @data = (1, 2, 3, 4, 5);
$worksheet->write('A1', \@data);

# Add chart
my $chart = $workbook->add_chart(type => 'line');
$chart->add_series(values => '=Sheet1!$A$1:$A$5');

$worksheet->insert_chart('C1', $chart);

$workbook->close();

  4. Formatting cells in Perl Excel files:

    • Description: Apply cell formatting in an Excel file.
    • Code:
      use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new('formatted_cells.xlsx');
my $worksheet = $workbook->add_worksheet();

# Apply formatting
my $format = $workbook->add_format(color => 'red', bold => 1);
$worksheet->write('A1', 'Formatted Text', $format);

$workbook->close();

  5. Adding formulas to Excel using Perl:

    • Description: Add formulas to Excel cells.
    • Code:
      use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new('formulas.xlsx');
my $worksheet = $workbook->add_worksheet();

# Add data
$worksheet->write('A1', 10);
$worksheet->write('A2', 20);

# Add formula
$worksheet->write_formula('A3', '=SUM(A1:A2)');

$workbook->close();

  6. Handling multiple sheets in Perl Excel files:

    • Description: Create and write to multiple sheets in an Excel file.
    • Code:
      use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new('multiple_sheets.xlsx');

# Sheet 1
my $worksheet1 = $workbook->add_worksheet('Sheet1');
$worksheet1->write('A1', 'Sheet 1 Data');

# Sheet 2
my $worksheet2 = $workbook->add_worksheet('Sheet2');
$worksheet2->write('A1', 'Sheet 2 Data');

$workbook->close();

  7. Perl module for Excel file manipulation:

    • Description: Use the Excel::Writer::XLSX module for Excel manipulation.
    • Code:
      use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new('excel_manipulation.xlsx');
my $worksheet = $workbook->add_worksheet();

# Write data
$worksheet->write('A1', 'Hello');
$worksheet->write('B1', 'World');

$workbook->close();

  8. Working with images in Perl Excel:

    • Description: Add an image to an Excel file using Excel::Writer::XLSX.
    • Code:
      use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new('image_example.xlsx');
my $worksheet = $workbook->add_worksheet();

# Insert image
$worksheet->insert_image('A1', 'image.jpg');

$workbook->close();

  9. Exporting data to Excel from Perl script:

    • Description: Export data from a Perl script to an Excel file.
    • Code:
      use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new('export_data.xlsx');
my $worksheet = $workbook->add_worksheet();

# Data to export
my @data = ('Name', 'Age', 'Location');
$worksheet->write('A1', \@data);

$workbook->close();
Copyright © devanddep.com | All Rights Reserved