469,344 Members | 6,478 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

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

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
Jul 24 '07 #1
11 10452
numberwhun
3,503 Expert Mod 2GB
Just an idea, but what about creating a variable that contains the two, concatenated values? As such:

Expand|Select|Wrap|Line Numbers
  1.       my $value3 = $value1 . ", " . $value2;
  2.  
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
Jul 24 '07 #2
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
Jul 24 '07 #3
miller
1,089 Expert 1GB
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
Jul 24 '07 #4
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
Jul 24 '07 #5
numberwhun
3,503 Expert Mod 2GB
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)
Jul 24 '07 #6
miller
1,089 Expert 1GB
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
Jul 24 '07 #7
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
Jul 24 '07 #8
numberwhun
3,503 Expert Mod 2GB
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
Jul 24 '07 #9
numberwhun
3,503 Expert Mod 2GB
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
Jul 24 '07 #10
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.

Expand|Select|Wrap|Line Numbers
  1. use Win32::OLE;
  2.  
  3. my $Excel = Win32::OLE->new('Excel.Application');
  4. $Excel->{SheetsInNewWorkbook} = 1;
  5. $Excel->{Visible} = 1;
  6.  
  7. my $Book = $Excel->Workbooks->Add;
  8. my $Sheet = $Book->Worksheets(1);
  9. $Sheet->{Name} = 'Sample';
  10.  
  11. my $text = "abcdefghijklmnopqrstuvwxyz" x 44;
  12.  
  13. print "Length is " . length($text) . "\n";
  14.  
  15. $Range = $Sheet->Range("A1:C1");
  16.  
  17. $Range->{1} = $text;
  18. $Range->{2} = $text;
  19. $Range->{3} = $text;
  20.  
-Dilip
Jul 25 '07 #11
Just format cell as general, not text
Oct 20 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

18 posts views Thread by Frank M. Walter | last post: by
1 post views Thread by Mike P | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.