|
Re: Append new line in excel sheets
by roboticus (Chancellor) on Jul 11, 2014 at 13:13 UTC
|
perl_new_b:
If you can't use office, then you'll have to use something like Spreadsheet::XLSX to read the spreadsheet, and Excel::Writer::XLSX to write the new one. If you're wanting to copy formatting, etc., then it can be a bit tedious, but once you have the hang of it, it's pretty simple.
...roboticus
When your only tool is a hammer, all problems look like your thumb.
| [reply] |
Re: Append new line in excel sheets
by kcott (Archbishop) on Jul 11, 2014 at 13:22 UTC
|
| [reply] |
|
|
Hi guys,
I have .xls files which contain around 8k lines ...I want to append one more line at the end... So either i need a function to append a line or the no. Of rows present in the file as I don't know how long a file can be.
There is nothing in WriteExcel to find total no of rows in a file.
Thanks for your suggestions friends...I'm stuck here. I don't know how to proceed.
| [reply] |
|
|
Here is an example of a solution:
use Modern::Perl '2014';
use Spreadsheet::ParseExcel::Simple;
use Spreadsheet::WriteExcel;
my $xls_in = Spreadsheet::ParseExcel::Simple->read('./spreadsheet_in.
+xls');
my $xls_out = Spreadsheet::WriteExcel->new('./spreadsheet_out.xls');
foreach my $sheet ( $xls_in->sheets ) {
my $active_sheet = $xls_out->add_worksheet();
my @sheet_data;
while ( $sheet->has_data ) {
my @data = $sheet->next_row;
push @sheet_data, \@data;
}
push @sheet_data, ['This data is added at the end'];
$active_sheet->write_col( 0, 0, \@sheet_data );
}
$xls_out->close();
This is not a complete solution: for instance, it does not pick up any formatting or names of the sheets. If you want that, you cannot use Spreadsheet::ParseExcel::Simple but you will have to use Spreadsheet::ParseExcel.
CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James My blog: Imperial Deltronics
| [reply]
[d/l]
[select] |
Re: Append new line in excel sheets
by CountZero (Bishop) on Jul 11, 2014 at 16:42 UTC
|
the end of excel file which contains multiple worksheets I think that is rather vague. How do you define "end of the excel file"? If you cannot use Win32::OLE then there are other solutions, but they all involve reading the whole workbook into memory, changing what needs to be changed or adding what needs to be added and then writing everything back to disk.
CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James My blog: Imperial Deltronics
| [reply] |
|
|
| [reply] |
|
|
since it reads the file inmemory so its memory usage hits upto 1GB for only 11MB file .
And so what? Does the program fail? 1 GB in memory is quite large, but is not necessarily a problem with current computers. (Although I am quite a bit surprised that it should take that much.)
The next question is which version of Excel produced your files. Although I haven't used Perl to work on Excel files for at least 6 years (except one relatively small project writing fairly simple Excel files under Unix), my understanding is that the most recent Excel file format is really a zipped file containing a number of XML file components. If this is the case with your version, then updating the right file component might be sufficient. I have never done it, and have no idea whether it is simple or not, whether it works or not, but it might be a useful clue.
Another alternative might be to export your Excel file in CSV format, to update the CSV file, and to convert it back to Excel.
I dunno if this is gonna get you anywhere, but these are just two possible solutions.
| [reply] |
|
|
|
|
Is the memory usage a problem? Do you get out of memory errors? If not, I wouldn't worry.Unfortunately .XLS files are a binary format which you cannot process on a row by row basis. Everything will be read in memory at once and I do not think there is another way around this. I did find Spreadsheet::ParseExcel_XLHTML (an old module, last updated in 2009) which claims to be faster and does "less fancy stuff". It replaces the parsing subroutine of Spreadsheet::ParseExcel and perhaps uses less memory? I haven't tried it, so I have no idea if it works well. Also there is no ::Simple version of it. However, if you look at the source code of Spreadsheet::ParseExcel::Simple it seems easy to make a Spreadsheet::ParseExcel_XLHTML::Simple yourself. Update: Unfortunately I cannot install Spreadsheet::ParseExcel_XLHTML since it needs bin:xlhtml which is not available on my PC.
CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James My blog: Imperial Deltronics
| [reply]
[d/l]
[select] |
|
|
|
|
|
