How to write more than 256 characters to an Excel cell?

[thanawala27]

Hi guys,

I had a problem while writing into an Excel file.
What i want to do is:
i want to write the contents of 2 variables in one cell of the Excel field.

to write the contents of one variable, i use
$worksheet->write_string($row, $col, $value1, $format);
this works fine.

but to write 2 variables, if i use the following code then the 2nd variable is overwritten on the first one.
$worksheet->write_string($row, $col, $value1, $format);
$worksheet->write_string($row, $col, $value2, $format);

is there any way of appending a cell by another variable? So we can write the first variable, n then append the second variable.

It would be nice if some1 could help me out.

Any Help is appreciated.

Thank You

[numberwhun]

Just an idea, but what about creating a variable that contains the two, concatenated values? As such:

      my $value3 = $value1 . ", " . $value2;
 

I don't have much experience with writing to excel spreadsheets, or any for that fact. If there is a way to tell if the cell already has a value, then maybe you can grab that value, use the above code to append your new data, then write to the cell with the new value. Like I said, just an idea.

Regards,

Jeff

[thanawala27]

Thanks for that Jeff

That idea did cross me, but the thing is a variable in perl can have max of 256 characters.
n in my application, the combined text in the variables $value1 and $value2 is more than 256.
so on concatenating, the remaining contents(above 256) will be lost.
my $value3 = $value1 . ", " . $value2;
$value3 will be able to hold only 256 char, rest will be lost.

so this option cant be considered.
We need to append $value2 to that particular cell only.

Thanks

[miller]

a variable in perl can have max of 256 characters.

For the third time, this is NOT true.

They excel interface that you are using may have such a limit, or the excel sheet itself may have such a limit, but perl has no limit on the size of variables. period.

- Miller

[thanawala27]

Oh ya.

sorry about that.
you are right, there is no size limit for a variable in Perl. Just checked it

I think i there is some issue with the Excel file.

Lemme find out.

Thanks very much for this

[numberwhun]

For the third time, this is NOT true.

They excel interface that you are using may have such a limit, or the excel sheet itself may have such a limit, but perl has no limit on the size of variables. period.

- Miller

***holds back Miller***


I would run if I were you, I don't know how long I can hold him back.

(he he, sorry, I just had to)

[miller]

Very funny Jeff :)

Yes, I do get annoyed when people fail to listen. But I think I was more than nice enough all considering.

thanawala27,

Feel free to let us know what the real problem is when you discover it.

- Miller

[thanawala27]

well, i found out that variables take 256 characters. Fine with it.

but while putting a text in an Excel file, it does not take more than 256 characters(including spaces).

I tried to hardwired a text into a variable which is more than 256 characters.
while putting that into excel file, it took jus the first 256 characters. remaining were discarded.

Any idea about this.!!!

Something urgent folks. Plz try to help me out here

Thanks

[numberwhun]

To me, that sounds like a limitation with Excel and (obviously) not Perl related. You may want to find a Microsoft related forum to see if that is something that can be changed in Excel, although I don't know if that's possible. (Not to slam them, but to Micro$oft, "issue" like these are features, not bugs).

Suggestions? Well, are you tied to Excel, or would you be able to try another spreadsheet? I don't know if it would have the same limitations, but try downloading OpenOffice and see if its spreadsheet program is as limited.

Regards,

Jeff

[numberwhun]

Very funny Jeff :)

Yes, I do get annoyed when people fail to listen. But I think I was more than nice enough all considering.

thanawala27,

Feel free to let us know what the real problem is when you discover it.

- Miller

Thanks! I saw the opportunity for a little humor in this crazy day and dove head first at it. Nothing meant, all in good fun.

I agree though, that it is rather annoying when you give advice and/or direction, only to have it completely disregarded by the person you are/were advising. And yes, you were nice, but I could somehow feel and understand the frustration. I don't blame you at all. How many times I have we said "Please post your code"?

Regards,

Jeff

[dilipkvarma]

well, i found out that variables take 256 characters. Fine with it.

but while putting a text in an Excel file, it does not take more than 256 characters(including spaces).

I tried to hardwired a text into a variable which is more than 256 characters.
while putting that into excel file, it took jus the first 256 characters. remaining were discarded.

Any idea about this.!!!

Something urgent folks. Plz try to help me out here

Thanks

I do not think that there is such a limitation for Excel.

Please try this code. Look at the output of the command and check the character count in the resulting Excel sheet.

use Win32::OLE;
 
my $Excel = Win32::OLE->new('Excel.Application');
$Excel->{SheetsInNewWorkbook} = 1;
$Excel->{Visible} = 1;
 
my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);
$Sheet->{Name} = 'Sample';
 
my $text = "abcdefghijklmnopqrstuvwxyz" x 44;
 
print "Length is " . length($text) . "\n";
 
$Range = $Sheet->Range("A1:C1");
 
$Range->{1} = $text;
$Range->{2} = $text;
$Range->{3} = $text;
 

-Dilip

[George Kaffejis]

Just format cell as general, not text