Format Entire Column of cells

Topics: User Forum
Aug 18, 2009 at 7:32 PM

I am looking to reformat an entire column of cells as say 'Text', but I am not finding a function that does that.

I've looked at several discussions here and they all seem to point to changing formating on CELLS, not *WHOLE COLUMNS*.

I am after something like:

$phpExcel ->setcolumnformat('Text',U);

This then applies to all columns.

Any help and pointers would be greatly appreceiated... as I am searched out.

Thanks!

Developer
Aug 18, 2009 at 10:02 PM

This feature is planned for PHPExcel 1.7.1. Column and row styles:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=7333

As you can see it is not completely finished yet. But if you download latest source code:
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

You can already try this:

$excel = new PHPExcel();

$sheet = $excel->getActiveSheet();
$sheet->getStyle('A')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$writer->save('write.xls');

As you can see, column 'A' is formatted as text:
http://img7.imageshack.us/img7/9446/ss20090818225435.png

Note: This should not be confused with text "data type" which can only be set for the cell, and this has nothing to do with number formats (format codes) which is a style property.

Aug 19, 2009 at 3:55 PM
Thanks for the info, but I can not take the style/format from any part of the sheet. Users may have changed what is expected and excel seems to want to make stupid assumptions of its own. Just because data in the cell is 1-6 or 1/6 doesn't make it a date, some data is formatted this way for a reason other than a date. This is one of the reasons we prefer to import CSV only, no BS like this. Unfortunately we send out a format for CSV, and get sent back a spreadsheet, that the user feels they need to "pretty up" and send back in proprietary formats that just have to be converted back to plain old text CSV. I then have to run it through a sanitation script to clean up all their "pretty" and spit back out a CSV. I just need to get all the cells of a column formatted the way I specify just like I highlighted the column, hit Format, Cells, Text.
Developer
Aug 19, 2009 at 11:27 PM

I now understand the background for your question. What I don't understand is how highlighting the column, hitting Format, Cells, Text solves the problem. Because when one does that all dates get converted to numbers, e.g. 20-08-2009 -> 40045. But it sounds like you are trying to fetch the string '20-08-2009' as displayed on screen?

Would it be possible to provide an explicit example what problem you are dealing with?