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: [email protected]
For additional commands, e-mail: [email protected]
