Perl | Creating Excel Files

Excel files are the most commonly used office application to communicate between computers. It is used to create rows and columns of text, numbers, and formulas for calculations. It is a good way to send reports. This demonstration works on Linux, Windows and other platforms as well. In excel, rows are numbered from 1 to n… and columns are marked by letters from A, B, C and so on.. hence, A1 refers to the top left corner. For creating excel files with Perl you can use padre IDE, we will also use Excel::Writer::XLSX module.
Perl uses write() function to add content to the excel file.

Syntax: write(cell_address, content)

Parameters:
cell_address: Address of the cell where content is to be added.
content: which is to be added to the worksheet.

Creating an Excel File

 
Excel Files can be created using Perl command line but first we need to load Excel::Writer::XLSX module.

filter_none

edit
close

play_arrow

link
brightness_4
code

#!/usr/bin/perl 
use Excel::Writer::XLSX;
  
my $Excelbook = Excel::Writer::XLSX->new( 'GFG_Sample.xlsx' );
my $Excelsheet = $Excelbook->add_worksheet();
  
$Excelsheet->write( "A1", "Hello!" );
$Excelsheet->write( "A2", "GeeksForGeeks" );
$Excelsheet->write( "B1", "Next_Column" );
  
$Excelbook->close;

chevron_right


Output:



Here is how the Program works:
Step 1: Load the module Excel::Writer::XLSX.
Step 2: Create an object $Excelbook which represents the whole Excel File.
Step 3: Call write() method to add data to the worksheet.
Step 4: Now, save the file with .pl extension.
Step 5: Run your .pl file on command line and Excelsheet will be created.
 

Use of Basic Formulas

 
Excel provides the use of various Mathematical Formulae for the ease of calculations on the excelsheets like balance sheet, business records, etc.
Here is the description of two basic formulas of Excel:

  • Addition:
    Excel provides a method ‘SUM’ for the addition of values on specific cells.

    Syntax: =SUM(Start, End)

    Parameter:
    Start: Address of the starting cell
    End: Address of the Ending cell

    Returns: the summation of values between the Starting and Ending cell.

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    #!/usr/bin/perl 
    use Excel::Writer::XLSX;
      
    my $Excelbook = Excel::Writer::XLSX->new( 'GFG_Sample.xlsx' );
    my $Excelsheet = $Excelbook->add_worksheet();
      
    # Writing values at A1 and A2
    $Excelsheet->write( "A1", 55 );
    $Excelsheet->write( "A2", 47 );
      
    # Adding without use of SUM method
    $Excelsheet->write( "A3", "= A1 + A2" );
      
    # Addition of a Range of cells
    $Excelsheet->write( "A4", " =SUM(A1:A3)" );

    chevron_right

    
    

    Output:

  • Count:
    This function in Excel is used to count all the cells in the given range which contain only numeric value.

    Syntax: =COUNT(Start, End)
    Returns: count of all cells containing numeric value

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    #!/usr/bin/perl 
    use Excel::Writer::XLSX;
      
    my $Excelbook = Excel::Writer::XLSX->new( 'GFG_Sample.xlsx' );
    my $Excelsheet = $Excelbook->add_worksheet();
      
    # Writing values
    $Excelsheet->write( "A1", 5 );
    $Excelsheet->write( "A2", 40 );
    $Excelsheet->write( "A3", "Hello" );
    $Excelsheet->write( "A4", 10 );
      
    # Addition of a Range of cells
    $Excelsheet->write( "A5", "Count =");
    $Excelsheet->write( "B5", "=COUNT(A1:A4)" );

    chevron_right

    
    

    Output:

Adding Colors to ExcelSheet

 
Colors can be used in ExcelSheets to mark specific values separately. These colors are specified with the use of add_format() method.

Syntax: add_format(color=> ‘color_name’)

filter_none

edit
close

play_arrow

link
brightness_4
code

#!/usr/bin/perl
use Excel::Writer::XLSX;
  
my $Excelbook = Excel::Writer::XLSX->new( 'GFG_Sample.xlsx' );
my $Excelsheet = $Excelbook->add_worksheet();
  
# Setting value of color
my $color1 = $Excelbook->add_format(color=> 'blue',);
my $color2 = $Excelbook->add_format(color=> 'red',);
my $color3 = $Excelbook->add_format(color=> 'green',);
  
$Excelsheet->write( "A2", "Geeks", $color1 );
$Excelsheet->write( "B2", "For", $color2 );
$Excelsheet->write( "C2", "Geeks", $color3 );
$Excelbook->close;

chevron_right


Output:

Adding values at specific coordinates

 
Values can be added at specific coordinates by providing address of cells at which value is to be added.

Syntax: write(R,C, “value”)
Parameters:
R and C are the coordinates of the Row and Column respectively.

filter_none

edit
close

play_arrow

link
brightness_4
code

#!/usr/bin/perl 
use Excel::Writer::XLSX;
  
my $Excelbook = Excel::Writer::XLSX->new( 'GFG_Sample.xlsx' );
my $Excelsheet = $Excelbook->add_worksheet();
  
$Excelsheet->write( 0, 0, "Hello!" );
$Excelsheet->write( 1, 0, "GeeksForGeeks" );
$Excelsheet->write( 3, 2, "Welcome!!!" );
  
$Excelbook->close;

chevron_right


Output:



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.