You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Rajiv Poddar <ra...@gmail.com> on 2011/04/12 10:30:15 UTC
Re: setting column to be text in XSSF
In our use case, there is a sheet with one column for postal codes. If user
enters '01234' in that column, then by default, excel removes the leading 0
and puts as '1234' because the default formatting of the column/cell is
'general'. If we can pre-format the cell type to be 'text', then data
entered by the user won't be automatically changed.
Please let me know if there is a way to do this.
Thanks n regards,
Rajiv
On Tue, Apr 12, 2011 at 12:18 PM, Mark Beardsley
<ma...@tiscali.co.uk>wrote:
> You would normally never bother setting the data type for an empty cell as
> doing this requires you to create a record for each empty cell and that
> will
> massivly increase the file size of the workbook. On a sheet there are
> something like sixty five thousand rows and creating a record for each row
> and each emty cell on that row will lead to a potentially bloated file and
> slow your application somewhat.
>
> Is there any reason why these empty cells must have their data type set?
> When Excel marks a cell as 'General' that means it can accept any value and
> when it is populated by the user, the application - Excel - will ensure
> that
> features are automatically configured to suit the type of data the user is
> entering. By this, I mean that numeric value will be right aligned, for
> example, and I do not know that setting the data type to text will prevent
> this from happening.
>
> Yours
>
> Mark B
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/setting-column-to-be-test-in-XSSF-tp4296158p4297528.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 column to be text in XSSF
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Yes, this should be possible. Take a look at the HSSFSheet class and more
specifically at the setDefaultColumnStyle() method. That takes a CellStyle
object as one of it's arguments and you may be able to use the data format
setting of the format stirng to enforce the text type. I do not know if this
will be possible but it is one place to look.
If this does not work then you will have to explicitly create a cell for
that column in every row of the worksheet and set it's data type.
Alternativly, if you can use a template as the basis for your application,
then it will be possible - at least I believe so - to set the type of the
column using Excel, save that away as the template file, open this with POI
and populate it.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Re-setting-column-to-be-text-in-XSSF-tp4297728p4298048.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 column to be text in XSSF
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
I do not know if you have made any progress with this problem but I wanted to
post again in case other users face a similar problem/requirement.
It does seem as though using the setDeafultColumnStyle() method and passing
an appropriate CellStyle object to it will set the data type of the cells in
a column. Take a look at this piece of code;
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author Mark B
*/
public class ColumnTest {
public ColumnTest(String filename) throws IOException {
File file = null;
FileOutputStream fos = null;
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
CellStyle style = null;
DataFormat format = null;
try {
// Instantiate the correct type of workbook based upon the
filename's
// extension.
if(filename.endsWith(".xls")) {
workbook = new HSSFWorkbook();
}
else {
workbook = new XSSFWorkbook();
}
// Get a DataFormat object and use it to create a CellStyle
object
// with the following format set for the cells @. The @ or
ampersand
// sets the format so that the cell will hold text.
format = workbook.createDataFormat();
style = workbook.createCellStyle();
style.setDataFormat(format.getFormat("@"));
// Create a sheet and write dummy data into the first row just
as
// if setting the headings onto the columns for the sheet.
sheet = workbook.createSheet("Column Format Test.");
row = sheet.createRow(0);
for(int i = 0; i < 4; i++) {
cell = row.createCell(i);
cell.setCellValue("Cell " + i + ".");
}
// Set the deafult style for a column, in this case column 1 or
// B. If all works correctly, this should result in a worksheet
// where Excel expects text to be entered into the cells in
column B.
sheet.setDefaultColumnStyle(1, style);
// Oddly, I found that in the OOXML file format (.xlsx) workbook
// column number 1 (B) disappeared following the call to set the
// default column style. By this, I mean that it was very narrow
// indeed and I needed to add the call to autosize - or to
manually
// set the width of - the column to make it appear again. This
extra
// step was not necessary if I was creating a binary (.xls)
workbook.
sheet.autoSizeColumn(1);
// Write the workbook away.
file = new File(filename);
fos = new FileOutputStream(file);
workbook.write(fos);
}
finally {
if(fos != null) {
fos.close();
fos = null;
}
}
}
public static void main(String[] args) {
if(args.length == 1) {
try {
new ColumnTest(args[0]);
}
catch(Exception ex) {
System.out.println("Caught an: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stacktrace follows:.....");
ex.printStackTrace(System.out);
}
}
}
}
By creating a data format with the @ symbol and using that in a CellStyle
object that is then passed to the setDefaultColumnStyle() method, it was
possible to set the default data type of the column to, in this case, text.
I have not experiemented further but do suspect it would be possible to do
something similar with other style objects to set the default type to
numeric or even a customised format such as currency.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Re-setting-column-to-be-text-in-XSSF-tp4297728p4305335.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