Hi,
This is my first email to [email protected]<mailto:[email protected]>.
This is my Program.
Problem Statement: setDeafultColumnStyle( ) for .xlsx doesn't wotk for new
added cell.
It definitely works for xls files. Just replace XSSFWorkbook() to
HSSFWorkbook() and file name ColumnTest.xlsx to ColumnTest.xls.
I am using apache poi 3.13
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package javaapplication1;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author 502149104
*/
public class ColumnTest {
public ColumnTest() 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 {
workbook = new XSSFWorkbook();
//create font
Font f = workbook.createFont();
f.setFontHeightInPoints((short) 10);
f.setFontName("GE Inspira");
// 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.setFont(f);
style.setDataFormat(format.getFormat("#,##0;[Red](#,##0)"));
// 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.");
// 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);
row = sheet.createRow(0);
for(int i = 0; i < 4; i++) {
cell = row.createCell(i);
cell.setCellValue(-12345.67);
}
// 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("C:\\Temp\\ColumnTest.xlsx");
fos = new FileOutputStream(file);
workbook.write(fos);
}
finally {
if(fos != null) {
fos.close();
fos = null;
}
}
}
public static void main(String[] args) {
try {
new ColumnTest();
}
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);
}
}
}
Thanks & Regards
- Joy
Joy Goswami
Consultant
General Electric Company
Corporate Tax
T +1 518 433 4426
M +1 518 428 4915
[email protected]<mailto:[email protected]>
www.ge.com<http://www.ge.com/>
12 Corporate Woods Blvd.
Suite 300
Albany, NY 12211
