You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Christian Lercher <cl...@gmx.net> on 2011/07/05 11:04:21 UTC
Setting the cell type to "text" for an entire column (.xls)
Hi,
with Excel, it's possible to set the data type (like text, numeric, ...) for an entire column. This is applied to all cells in the column - even those, which do not contain data yet. The setting survives saving/loading an xls file.
I haven't been able to figure out how to do this with Apache POI. I know, there's
cell.setCellType(Cell.CELL_TYPE_STRING)
but that's for individual cells (or would I really have to create 65000 rows, and set it manually on each of them?)
Maybe I could use NamedRange somehow, but - how?
Thanks
Chris
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting the cell type to "text" for an entire column (.xls)
Posted by Christian Lercher <cl...@gmx.net>.
Or rather:
sheet.setDefaultColumnCellType(1, Cell.CELL_TYPE_STRING);
(where 1 is my column number)
-------- Original-Nachricht --------
> Datum: Tue, 05 Jul 2011 15:57:11 +0200
> Von: cl_for_mailing@gmx.net
> An: "POI Users List" <us...@poi.apache.org>
> Betreff: Re: Setting the cell type to "text" for an entire column (.xls)
> Yes, I also assumed, that this would be the method I was looking for -
> however, I believe that a CellStyle is not responsible for setting the data
> type (?)
>
> Ideally, I would like to call something like:
>
> sheet.setDefaultColumnCellType(Cell.CELL_TYPE_STRING);
>
>
>
> -------- Original-Nachricht --------
> > Datum: Tue, 5 Jul 2011 14:33:29 +0100 (BST)
> > Von: Nick Burch <ni...@alfresco.com>
> > An: POI Users List <us...@poi.apache.org>
> > Betreff: Re: Setting the cell type to "text" for an entire column (.xls)
>
> > On Tue, 5 Jul 2011, Christian Lercher wrote:
> > > with Excel, it's possible to set the data type (like text, numeric,
> ...)
> > > for an entire column. This is applied to all cells in the column -
> even
> > > those, which do not contain data yet. The setting survives
> > > saving/loading an xls file.
> >
> > Did you try setting the default column style to a style with your
> required
> > formatting? It's XSSFSheet.setDefaultColumnStyle(int,CellStyle)
> >
> > Nick
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > For additional commands, e-mail: user-help@poi.apache.org
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting the cell type to "text" for an entire column (.xls)
Posted by jahroy <ed...@skilogs.com>.
Christian Lercher wrote
> Ideally, I would like to call something like:
>
> sheet.setDefaultColumnCellType(Cell.CELL_TYPE_STRING);
The following code will set the default style of the first column to text:
DataFormat fmt = workbook.createDataFormat();
CellStyle textStyle = workbook.createCellStyle();
textStyle.setDataFormat(fmt.getFormat("@"));
worksheet.setDefaultColumnStyle(0, textStyle);
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Setting-the-cell-type-to-text-for-an-entire-column-xls-tp4552613p5712764.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting the cell type to "text" for an entire column (.xls)
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 5 Jul 2011, Christian Lercher wrote:
> 1. styles.xml gets a new entry:
>
> <cellXfs count="2">
> <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
> <xf numFmtId="49" fontId="0" fillId="0" borderId="0" xfId="0"
> applyNumberFormat="1" />
> </cellXfs>
There are two bits in that entry of interest:
* numFmtId="49"
* applyNumberFormat="1"
I suspect both may be needed.
> 2. sheet1.xml gets a new entry:
>
> <cols>
> <col min="1" max="1" width="11.42578125" style="1" />
> </cols>
That means the 1st column has a style. If you use POI to set a default
column style, do you get the same? (I'd expect you to). And if you set up
the style to be the same as excel gives you (i.e. with number format 49,
and apply number format set), does it behave the same or not?
> 3. sheet1.xml gets a new attribute "s" on the "c" element:
>
> <c r="A1" s="1">
> <v>55</v>
> </c>
Looks like the cell has an explicit style set on it, rather than relying
on the column default, interesting. If you were to manually remove that
style s="1" entry, what does excel show for the cell, styled or not?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting the cell type to "text" for an entire column (.xls)
Posted by Christian Lercher <cl...@gmx.net>.
Ok, good idea! So I did the following:
- I created a new file, entered just the number "55" in cell A1.
- Then saved the file to "without.xlsx".
- Set the data type for column 1 to string.
- Saved the result to "with.xlsx".
.. and the main differences seem to be the following:
1. styles.xml gets a new entry:
<cellXfs count="2">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
<xf numFmtId="49" fontId="0" fillId="0" borderId="0" xfId="0"
applyNumberFormat="1" />
</cellXfs>
2. sheet1.xml gets a new entry:
<cols>
<col min="1" max="1" width="11.42578125" style="1" />
</cols>
3. sheet1.xml gets a new attribute "s" on the "c" element:
<c r="A1" s="1">
<v>55</v>
</c>
I'm not exactly sure, what all of this does (I can only guess a little bit) ...
-------- Original-Nachricht --------
> Datum: Tue, 5 Jul 2011 15:08:43 +0100 (BST)
> Von: Nick Burch <ni...@alfresco.com>
> An: POI Users List <us...@poi.apache.org>
> Betreff: Re: Setting the cell type to "text" for an entire column (.xls)
> Can you try setting the type on a column in excel, and seeing what gets
> written to the file for that? (Unzip the .xlsx file and compare the xml
> between a file with and without the setting).
>
> If it isn't done with a style, then I don't personally know how it'd be
> done, but luckily we can use excel as an oracle for the file format :)
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting the cell type to "text" for an entire column (.xls)
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 5 Jul 2011, cl_for_mailing@gmx.net wrote:
> Ideally, I would like to call something like:
>
> sheet.setDefaultColumnCellType(Cell.CELL_TYPE_STRING);
Can you try setting the type on a column in excel, and seeing what gets
written to the file for that? (Unzip the .xlsx file and compare the xml
between a file with and without the setting).
If it isn't done with a style, then I don't personally know how it'd be
done, but luckily we can use excel as an oracle for the file format :)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting the cell type to "text" for an entire column (.xls)
Posted by cl...@gmx.net.
Yes, I also assumed, that this would be the method I was looking for - however, I believe that a CellStyle is not responsible for setting the data type (?)
Ideally, I would like to call something like:
sheet.setDefaultColumnCellType(Cell.CELL_TYPE_STRING);
-------- Original-Nachricht --------
> Datum: Tue, 5 Jul 2011 14:33:29 +0100 (BST)
> Von: Nick Burch <ni...@alfresco.com>
> An: POI Users List <us...@poi.apache.org>
> Betreff: Re: Setting the cell type to "text" for an entire column (.xls)
> On Tue, 5 Jul 2011, Christian Lercher wrote:
> > with Excel, it's possible to set the data type (like text, numeric, ...)
> > for an entire column. This is applied to all cells in the column - even
> > those, which do not contain data yet. The setting survives
> > saving/loading an xls file.
>
> Did you try setting the default column style to a style with your required
> formatting? It's XSSFSheet.setDefaultColumnStyle(int,CellStyle)
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Setting the cell type to "text" for an entire column (.xls)
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 5 Jul 2011, Christian Lercher wrote:
> with Excel, it's possible to set the data type (like text, numeric, ...)
> for an entire column. This is applied to all cells in the column - even
> those, which do not contain data yet. The setting survives
> saving/loading an xls file.
Did you try setting the default column style to a style with your required
formatting? It's XSSFSheet.setDefaultColumnStyle(int,CellStyle)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org