Hi,
I'm struggling with the worksheet.autoSizeColumn() method. I've looked over
posts on the web, but can't seem to tease out what I'm doing wrong. I followed
all of the advice out there about picking a supported font-Arial, setting a
common font size, and so on, but the file that goes out doesn't get resized.
The place where it should all be happening is right after the call to
populateDetailedWorksheet.
int maxCols = populateDetailedWorksheet(data,
sheet, workbook, wbConfig, shConfig, trioList);
for(int i = 0; i > maxCols; i++){
sheet.autoSizeColumn(i);
}
Everything seems to work, except that the cells don't get resized.
Does anyone have any ideas?
Thanks,
Dave
package gov.nrel.nbc.spreadsheet.server;
import gov.nrel.nbc.spreadsheet.client.AppConstants;
import gov.nrel.nbc.spreadsheet.client.CriteriaTrioDTO;
import gov.nrel.nbc.spreadsheet.dao.CellHeaderDAOHibernate;
import gov.nrel.nbc.spreadsheet.dao.DataFormatDAOHibernate;
import gov.nrel.nbc.spreadsheet.dao.MetadataHeaderDAOHibernate;
import gov.nrel.nbc.spreadsheet.dto.DataFormat;
import gov.nrel.nbc.spreadsheet.dto.DataType;
import gov.nrel.nbc.spreadsheet.hibernate.HibernateSessionFactory;
import gov.nrel.nbc.spreadsheet.utilities.XLogger;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
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;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
/**
* A class to implement the methods to create Excel files from <CellData>
objects.
*
* @author James Albersheim
* @author David Crocker
*
*/
/**
* @author dcrocker
*
*/
public class ExportData implements AppConstants {
private static final String REAL_FORMAT = "##0.";
private static final String LONG_FORMAT = "##0";
private static final String STRING = "STRING";
private static final String DATE = "DATE";
private static final String REAL = "REAL";
private static final String LONG = "LONG";
private static final String BOOLEAN = "BOOLEAN";
/**
* Long constant that specifies the format of dates in the Detailed
Excel worksheet.
*/
private final Long DATE_FORMAT = new Long(4);
/**
* Long constant that specifies the format of dates in the Detailed
Excel worksheet.
*/
private final Long DATE_FORMAT_SHORT = new Long(1);
private final String STRING_FORMAT_PREFIX = "Len";
/**
* number of meta headers for workBook
*/
private int numHeaders = 0;
private short fontSize = 10;
/**
* Holder for the Excel file.
*/
private File excelFile = null;
private CellStyle longCellStyle = null;
private CellStyle headerCellStyle = null;
private CellStyle basicCellStyle = null;
private Font font = null;
private Font headerFont = null;
/**
* A holder for the logging class.
*/
private static final XLogger log = new XLogger(ExportData.class);
private List<String> headers = new ArrayList<String>();
private Map<Long, Short> cellStyleHashMap = Collections.synchronizedMap(new
HashMap<Long, Short>());
private Map<Long, String> formatStringHashMap =
Collections.synchronizedMap(new HashMap<Long, String>());
/**
* Constructor gets DataFormats and generates a <HashMap> of format
Strings.
*
*
*/
public ExportData(int numHeaders) {
this.numHeaders = numHeaders+1;
headers.add("Workbook ID");
Session session = null;
DataFormatDAOHibernate dfdh = new DataFormatDAOHibernate();
List<DataFormat> dataFormatList;
DataFormat df;
Long key;
String formatString;
try {
session = HibernateSessionFactory.getSession();
dfdh.setSession(session);
Transaction tx = session.beginTransaction();
dataFormatList = dfdh.findAll();
Iterator<DataFormat> dfIterator = dataFormatList.iterator();
while(dfIterator.hasNext()){
df = dfIterator.next();
formatString = df.getFormat();
// FIXME DataFormat.type is stored as camel case
if(df.getType().equalsIgnoreCase(REAL)) {
formatString = this.getRealFormat(formatString);
}
// FIXME This kludge deals with the DataFormat objects
used in the setup screens for the Spreadsheet Admin app.
if (!formatString.startsWith(STRING_FORMAT_PREFIX)) {
key = df.getData_format_id();
if(!formatStringHashMap.containsKey(key)) {
formatStringHashMap.put(key,
formatString);
}
}
}
tx.commit();
} catch (HibernateException he) {
log.severe("Hibernate exception on getting type. error: " + he);
String stack = SpreadSheetUploadServiceImpl.getStackTrace(he);
log.warning(stack);
try {
if (session != null && session.isConnected())
session.getTransaction().rollback();
} catch (HibernateException rbEx) {
log.severe("Couldn't roll back transaction! Error: " +
rbEx);
}
} finally {
if (session != null && session.isConnected())
if (session.isOpen()) {
session.flush();
session.close();
}
}
}
/**
* Private method to initialize the Excel file.
*/
private void initExcelFile(String fileDirectory) {
File tempDir = new File(fileDirectory);
if (!tempDir.exists()) {
boolean ret = tempDir.mkdirs();
if (!ret) {
log.warning("failed to create directories for
"+tempDir.getPath());
}
}
excelFile = new File(tempDir.getPath() + File.separator +
EXCEL_FILE_NAME + new Date().getTime() + AppConstants.EXCEL_FILE_SUFFIX_2007);
excelFile.setExecutable(true);
excelFile.setReadable(true);
excelFile.setWritable(true);
}
/**
* Method to create and save the Detailed Excel file.
*
* @param data <List<List<String>>> A list of rows of data (list of
strings) to write
* @param sheetName <String> The name of the excel spreadsheet
* @return <String> Path to Excel file, locally.
*/
public String createExcelFile(List<List<String>> data, String
sheetName, String wbConfig, String shConfig, List<CriteriaTrioDTO> trioList,
String fileDirectory) {
if (data != null) {
CreationHelper createHelper;
try {
initExcelFile(fileDirectory);
Workbook workbook = new XSSFWorkbook();
FileOutputStream fos = new
FileOutputStream(excelFile);
Sheet sheet = null;
if (sheetName != null) {
// Create new sheet and add it to end
of list.
sheet = workbook.createSheet(sheetName);
}
headerFont = workbook.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints(fontSize);
font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints(fontSize);
headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFont(headerFont);
createHelper = workbook.getCreationHelper();
longCellStyle = workbook.createCellStyle();
longCellStyle.setDataFormat(createHelper.createDataFormat().getFormat(LONG_FORMAT));
longCellStyle.setFont(font);
basicCellStyle = workbook.createCellStyle();
basicCellStyle.setFont(font);
populateFormats(workbook, createHelper);
int maxCols = populateDetailedWorksheet(data,
sheet, workbook, wbConfig, shConfig, trioList);
for(int i = 0; i > maxCols; i++){
sheet.autoSizeColumn(i);
}
workbook.write(fos);
fos.close();
return excelFile.getPath();
} catch (IOException ie) {
log.severe("Error creating workbook: " + ie);
return null;
} catch (Exception ree) {
log.severe("Exception: " + ree);
log.severe(SpreadSheetServiceImpl.getStackTrace(ree));
return null;
}
} else {
return null;
}
}
/**
* Method to get type based on the selected header
*
* @param selection <String> The selected header
* @return <String> A type string based on the parameterized types,
such as STRING
*/
private String getType(String selection) {
Session session = null;
String type = null;
if (selection.toLowerCase().equals("attachments") ||
selection.toLowerCase().equals("workbook id"))
type = "LONG";
else {
try {
session = HibernateSessionFactory.getSession();
CellHeaderDAOHibernate ctdh = new
CellHeaderDAOHibernate();
MetadataHeaderDAOHibernate mdh = new
MetadataHeaderDAOHibernate();
ctdh.setSession(session);
mdh.setSession(session);
Transaction tx = session.beginTransaction();
DataType dataType =
ctdh.getTypeBySynonym(selection);
if (dataType == null) {
dataType = mdh.getTypeByName(selection);
}
if (dataType != null)
type = dataType.getDescription();
tx.commit();
} catch (HibernateException he) {
log.severe("Hibernate exception on getting
type. error: " + he);
String stack =
SpreadSheetUploadServiceImpl.getStackTrace(he);
log.warning(stack);
try {
if (session != null &&
session.isConnected())
session.getTransaction().rollback();
} catch (HibernateException rbEx) {
log.severe("Couldn't roll back
transaction! Error: " + rbEx);
}
} finally {
if (session != null && session.isConnected())
if (session.isOpen()) {
session.flush();
session.close();
}
}
}
return type;
}
/**
* Method to get type based on the selected header
*
* @param selection <String> The selected header
* @return <String> A format string based on the parameterized formats,
such as MM/dd/yy
*/
private Long getFormat(String selection, int col) {
Session session = null;
Long format = null;
try {
session = HibernateSessionFactory.getSession();
CellHeaderDAOHibernate ctdh = new
CellHeaderDAOHibernate();
MetadataHeaderDAOHibernate mdh = new
MetadataHeaderDAOHibernate();
ctdh.setSession(session);
mdh.setSession(session);
Transaction tx = session.beginTransaction();
DataFormat dataFormat = null;
if (col < numHeaders)
dataFormat =
mdh.getFormatByName(selection);
if (dataFormat == null) {
dataFormat =
ctdh.getFormatByName(selection);
}
if (dataFormat != null)
format = dataFormat.getData_format_id();
tx.commit();
} catch (HibernateException he) {
log.severe("Hibernate exception on getting
type. error: " + he);
String stack =
SpreadSheetUploadServiceImpl.getStackTrace(he);
log.warning(stack);
try {
if (session != null &&
session.isConnected())
session.getTransaction().rollback();
} catch (HibernateException rbEx) {
log.severe("Couldn't roll back
transaction! Error: " + rbEx);
}
} finally {
if (session != null && session.isConnected())
if (session.isOpen()) {
session.flush();
session.close();
}
}
return format;
}
private String getRealFormat(String input) {
int digitsAfterDecimal = 0;
try {
digitsAfterDecimal = Integer.parseInt(input);
} catch (NumberFormatException nfe) {}
String format = REAL_FORMAT;
for (int i=0;i<digitsAfterDecimal;i++) {
format += "0";
}
return format;
}
/**
* Private method to create and populate a sheet in a workbook for
* a Detailed report.
*
* @param data - <List<List<String>>> Spreadsheet represented as a set
of <String>'s
* @param sheet - <WritableSheet> MS Excel work sheet to write data to.
* @param sheetName - <String> name of work sheet
* @param workbook - <WritableWorkbook> - MS Excel work book to write
data to.
* @throws <RowsExceededException>
* @throws <WriteException>
*/
private int populateDetailedWorksheet(List<List<String>> data, Sheet
sheet, Workbook workbook, String wbConfig, String shConfig,
List<CriteriaTrioDTO> trioList)
{
// Create a reusable format for long data
// Organize data
int maxCols = 0;
Long format1;
String header;
String type = "";
Date dateValue = null;
int rowCtr = 0;
Cell cell = null;
Row row = null;
row = sheet.createRow(rowCtr);
cell = row.createCell(0, Cell.CELL_TYPE_STRING);
cell.setCellValue("Search Criteria");
cell.setCellStyle(headerCellStyle);
rowCtr = 1;
row = sheet.createRow(rowCtr);
cell = row.createCell(0, Cell.CELL_TYPE_STRING);
cell.setCellValue("workbook");
cell.setCellStyle(headerCellStyle);
cell = row.createCell(1, Cell.CELL_TYPE_STRING);
cell.setCellValue(wbConfig);
cell.setCellStyle(basicCellStyle);
rowCtr = 2;
row = sheet.createRow(rowCtr);
cell = row.createCell(0, Cell.CELL_TYPE_STRING);
cell.setCellValue("worksheet");
cell.setCellStyle(headerCellStyle);
cell = row.createCell(1, Cell.CELL_TYPE_STRING);
cell.setCellStyle(basicCellStyle);
cell.setCellValue(shConfig);
rowCtr = 3;
row = sheet.createRow(rowCtr);
cell = row.createCell(0, Cell.CELL_TYPE_STRING);
cell.setCellValue("header");
cell.setCellStyle(headerCellStyle);
cell = row.createCell(1, Cell.CELL_TYPE_STRING);
cell.setCellValue("operator");
cell.setCellStyle(headerCellStyle);
cell = row.createCell(2, Cell.CELL_TYPE_STRING);
cell.setCellValue("value");
cell.setCellStyle(headerCellStyle);
rowCtr = 4;
Iterator<CriteriaTrioDTO> trit = trioList.iterator();
while (trit.hasNext()) {
CriteriaTrioDTO trio = trit.next();
row = sheet.createRow(rowCtr);
cell = row.createCell(0, Cell.CELL_TYPE_STRING);
cell.setCellStyle(basicCellStyle);
cell.setCellValue(trio.getHeader());
cell = row.createCell(1, Cell.CELL_TYPE_STRING);
cell.setCellStyle(basicCellStyle);
cell.setCellValue(trio.getOperator());
cell = row.createCell(2, Cell.CELL_TYPE_STRING);
cell.setCellStyle(basicCellStyle);
cell.setCellValue(trio.getValue());
rowCtr++;
}
row = sheet.createRow(rowCtr++);
row = sheet.createRow(rowCtr++);
int firstDataRow = rowCtr;
//List<String> formats = getFormats();
// Write out values
Iterator<List<String>> it = data.iterator();
while (it.hasNext()) {
List<String> rowData = it.next();
Iterator<String> it2 = rowData.iterator();
row = sheet.createRow(rowCtr);
int colCtr = 0;
while (it2.hasNext()) {
String stringValue = it2.next();
// Write out tags
cell = null;
if (rowCtr == firstDataRow) {
if (colCtr == 0) {
cell = row.createCell(0,
Cell.CELL_TYPE_STRING);
cell.setCellValue("Workbook
ID");
cell.setCellStyle(headerCellStyle);
colCtr++;
}
cell = row.createCell(colCtr,
Cell.CELL_TYPE_STRING);
cell.setCellValue(stringValue);
headers.add(stringValue);
cell.setCellStyle(headerCellStyle);
colCtr++;
} else {
// Write out values
header = headers.get(colCtr);
if (colCtr == 0)
type = "LONG";
else
type = getType(header);
if (type != null) {
if (stringValue == null ||
stringValue.isEmpty()){
cell =
row.createCell(colCtr, Cell.CELL_TYPE_BLANK);
cell.setCellStyle(basicCellStyle);
colCtr++;
} else if (type.equals(STRING))
{
cell =
row.createCell(colCtr, Cell.CELL_TYPE_STRING);
cell.setCellStyle(basicCellStyle);
cell.setCellValue(stringValue);
colCtr++;
} else if (type.equals(LONG)) {
Long longValue = 0L;
try {
longValue =
Long.parseLong(stringValue);
cell =
row.createCell(colCtr, Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(longValue);
cell.setCellStyle(longCellStyle);
} catch
(NumberFormatException nfe) {
//log.info("problem parsing for LONG: ["+stringValue+"]");
cell =
row.createCell(colCtr, Cell.CELL_TYPE_BLANK);
}
colCtr++;
} else if (type.equals(REAL)) {
Double realValue = 0.0;
format1 =
getFormat(header,colCtr);
try {
realValue =
Double.parseDouble(stringValue);
cell =
row.createCell(colCtr, Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(realValue);
if(cellStyleHashMap.containsKey(format1)) {
cell.setCellStyle(workbook.getCellStyleAt(cellStyleHashMap.get(format1)));
}
} catch
(NumberFormatException nfe) {
//log.info("problem parsing for REAL: ["+stringValue+"]");
cell =
row.createCell(colCtr, Cell.CELL_TYPE_BLANK);
}
colCtr++;
} else if (type.equals(DATE)) {
cell =
row.createCell(colCtr, Cell.CELL_TYPE_NUMERIC);
format1 =
getFormat(header,colCtr);
dateValue = null;
try {
if (format1 !=
null)
dateValue = new
SimpleDateFormat(formatStringHashMap.get(format1)).parse(stringValue);
else if (colCtr
< 3)
dateValue = new
SimpleDateFormat(formatStringHashMap.get(DATE_FORMAT_SHORT)).parse(stringValue);
else
dateValue = new
SimpleDateFormat(formatStringHashMap.get(DATE_FORMAT)).parse(stringValue);
} catch (ParseException
pe) {
log.severe("Error parsing dates: " + pe);
}
if(dateValue == null) {
row.getCell(colCtr).setCellType(Cell.CELL_TYPE_BLANK);
} else {
cell.setCellValue(dateValue);
if (format1 !=
null) {
if(cellStyleHashMap.containsKey(format1)) {
cell.setCellStyle(workbook.getCellStyleAt(cellStyleHashMap.get(format1)));
}
} else if
(colCtr < numHeaders) {
if(cellStyleHashMap.containsKey(DATE_FORMAT_SHORT)) {
cell.setCellStyle(workbook.getCellStyleAt(cellStyleHashMap.get(DATE_FORMAT_SHORT)));
}
} else {
if(cellStyleHashMap.containsKey(DATE_FORMAT)) {
cell.setCellStyle(workbook.getCellStyleAt(cellStyleHashMap.get(DATE_FORMAT)));
}
}
}
colCtr++;
} else if
(type.equals(BOOLEAN)) {
cell =
row.createCell(colCtr, Cell.CELL_TYPE_BOOLEAN);
cell.setCellStyle(basicCellStyle);
Boolean boolValue =
false;
boolValue =
Boolean.parseBoolean(stringValue);
cell.setCellValue(boolValue);
colCtr++;
}
} else { // type is null here
colCtr++;
}
}
}
rowCtr++;
maxCols = Math.max(colCtr, maxCols);
}
return maxCols;
}
private void populateFormats(Workbook workbook, CreationHelper
createHelper){
Long formatID;
String formatString;
CellStyle cellStyle;
Short cellIndex;
for (Map.Entry<Long, String> entry :
formatStringHashMap.entrySet()) {
formatID = entry.getKey();
formatString = entry.getValue();
cellStyle = workbook.createCellStyle();
// FIXME The String length formats in the DB are bogus. They can't
be used.
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(formatString));
cellStyle.setFont(font);
formatString = cellStyle.getDataFormatString();
cellIndex = cellStyle.getIndex();
if (!cellStyleHashMap.containsKey(formatID)) {
cellStyleHashMap.put(formatID, cellIndex);
}
}
}
public short getFontSize() {
return fontSize;
}
public void setFontSize(short fontSize) {
this.fontSize = fontSize;
}
}
