hey, i create an excel file just be putting a simple rhtml to the browser with @headers["Content-type"] = "application/vnd.ms-excel" but i have a small problem. i also put pinnumbers in the excel file like this: Lastname Firstname Phone Phone pin<% for employee in @employees %> <%= employee.last_name %> <%= employee.first_name %> <%= employee.phone %> <%employee.phone_pin.rjust(4,"0") %><% end %> Lastname Firstname Phone Phone pin Brutyn A. xxxxxxxxxx 21 Brutyn G. xxxxxxxxxx 6641 Brutyn L. xxxxxxxxxx 1237 Brutyn N. xxxxxxxxxx 1134 this is my problem 21 should be 0021 i also have tried to_s and ''<%= employee.phone_pin.rjust(4,"0") %> but then i get ''0021 and ''6641 which is not beautiful can anyone help me with some cell format or something or some handy trick thx in advance
Brutyn nick wrote:> i also put pinnumbers in the excel file like this: > > Lastname Firstname Phone Phone pin<% for employee in @employees %> > <%= employee.last_name %> <%= employee.first_name %> <%= employee.phone %> <%> employee.phone_pin.rjust(4,"0") %><% end %> > > Lastname Firstname Phone Phone pin > Brutyn A. xxxxxxxxxx 21 > Brutyn G. xxxxxxxxxx 6641 > Brutyn L. xxxxxxxxxx 1237 > Brutyn N. xxxxxxxxxx 1134 > > this is my problem 21 should be 0021My hope is that someone out there will contradict me, but everything I''ve seen says that if you open a plain text file in Excel and there''s a column that contains only numbers, the values in that column will come into Excel with all leading zeros stripped. And once it''s in Excel there''s no way to get the leading zeros back, without turning the file into a native Excel document and setting a custom number format on that column/cell. Simply changing the column to be "Text", which is supposed to leave the data as entered, doesn''t bring back the leading zeros. Again, I''m hoping that someone can tell me I''m wrong, but this is something I''ve been wrestling with for years with Excel and I''ve never seen a solution... -Brian
Brian V. Hughes <brianvh@...> writes:> My hope is that someone out there will contradict me, but everything > I''ve seen says that if you open a plain text file in Excel and there''s a > column that contains only numbers, the values in that column will come > into Excel with all leading zeros stripped. And once it''s in Excel > there''s no way to get the leading zeros back, without turning the file > into a native Excel document and setting a custom number format on that > column/cell. Simply changing the column to be "Text", which is supposed > to leave the data as entered, doesn''t bring back the leading zeros. > > Again, I''m hoping that someone can tell me I''m wrong, but this is > something I''ve been wrestling with for years with Excel and I''ve never > seen a solution... > > -Brian >Brian, When importing a text file into excel there is a panel in the import wizard that lets you set the column type. Changing it from ''general'' to ''text'' will stop the suppression of the leading zeroes, but your column type will be a text type, not a numerirc type. You can click on the format/cell option in the tool bar, click number and fill in the number of zeroes you want the width of your cell to be. This will keep the number, and even zeroes will be just as wide as any other numbers. There is also a trick with concatenate and RIGHT in a formula, but I don''t have a copy of excel or windows, so I can''t remember how to do that. It has been many years since I worked with excel, but you can probably set these attributes with vba if you are importing with a vba script
> Again, I''m hoping that someone can tell me I''m wrong, but this is > something I''ve been wrestling with for years with Excel and I''ve never > seen a solution...i found a solution =TEKST(<%= employee.phone_pin %>;"0000") then i get 0021 for u engligh guys tekst() => text() i think thanks by the way