G'day thonny,

Welcome to the Monastery.

It seems to me that you've taken code from elsewhere and just added it to your script without understanding what it does.

• use lib; — What do think this does? It looks like you don't even need this line (or a variation of it). See the lib documentation.
• Excel::Writer::XLSX->new(...); — You should be checking that this worked (i.e. file found, could be opened, etc.). See "Excel::Writer::XLSX new()" for more about that.
• my $col = 0; — This never changes, i.e. it's invariant, so why use a variable. You could just use 0 in the write() method.
• my $row = 1; — When numbers are used, the $row (and also $col) in write($row, $col, ...) are zero-based. Did you really want to start outputting to the second row? See "Excel::Writer::XLSX Cell notation" for more about that.
• chomp($line); — This isn't a big problem but there's little point in carrying out processing on a line that you're about to skip. Put this after the next ... statement. That would better reflect the logical processing and indicate that you've actually considered this. You may even get a minor speed improvement; although, unless your input has a huge number of lines, that's likely to be negligible.
• my $value = $line; — What is the purpose of this? You could just use $line in the write() method and scrap this statement.
• $row++; — This is more a matter of style and personal taste than any real problem. I'd normally use write($row++, ...) and not include $row++; as a separate statement.

So, there's seven things you can look at and potentially improve.

Now, for us to provide you with help, you need to tell us what the problem is. Show us a few representative lines from the uncompressed input.txt.gz file. Show us how those lines appear in the spreadsheet. Show us how you really wanted those lines to appear in the spreadsheet.

I also suggest you read "How do I post a question effectively?" to see the type of information that we need, and how to present it (e.g. verbatim error messages within <code>...</code> tags).

I will improve on the suggestions given. The problem I am facing is, with the script I have, it deletes the existing histogram in the excel file. The input.txt.gz file looks something like this:

0.1
0.2
0.3
0.4
0.5
[download]

Those lines should just appear in the column A of excel sheet. I wish I can attach image here butI cannot. Hope you can understand what I am trying to say.

You can create charts using Excel::Writer::XLSX (see Excel::Writer::XLSX::Chart) but I can't see a histogram there, only bar charts.

Yes. My first attempt was to edit the existing excel file. When I failed to do so, I tried to generate histogram with the Perl script itself but I couldnt find way to plot histogram.

Plotting a histogram with Perl can be achieved using GD::Graph::histogram or via Chart::Gnuplot. I will demonstrate the latter since the former already has a demo.

use Chart::Gnuplot;

my @counts = (10,11,20,5,13,19);
my @labels = ('a'..'f');

my $dataSet = Chart::Gnuplot::DataSet->new(
    xdata => \@labels,
    ydata => \@counts,
    title => "Histogram",
    style => "histograms",
);
# Create chart object and specify the properties of the chart
my $chart = Chart::Gnuplot->new(
    title  => "Simple testing",
    xlabel => "My x-axis label",
    ylabel => "My y-axis label",
    # this dumbs an ASCII histogram to console
    #terminal => 'dumb',
    # this plots it as a PNG
    output => 'x.png',
    terminal => 'png',
);

# Plot the data set on the chart
$chart->plot2d($dataSet);
[download]

bw, bliako

Question: Is it possible to add new sheets to an existing Excel file?

Cheers Rolf
_{(addicted to the Perl Programming Language :)
Wikisyntax for the Monastery}

Each line is written completely into the first cell of consecutive rows.

So what is your problem?

UPDATE: crossposted SO

It's considered impolite to not tell us that you already asked that question!

https://stackoverflow.com/questions/69657120/histogram-in-the-same-worksheet-using-perl

My apologies. I asked to help on how to plot charts.

Please be clearer.

I have to guess...

Do you want to add new rows to an existing sheet, and expect the existing charts to be updated?

Cheers Rolf
_{(addicted to the Perl Programming Language :)
Wikisyntax for the Monastery}