You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Crocker, David" <Da...@nrel.gov> on 2014/03/03 22:39:57 UTC
yet another autoSizeColumn problem.
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;
}
}
Re: yet another autoSizeColumn problem.
Posted by Nick Burch <ap...@gagravarr.org>.
On Tue, 4 Mar 2014, David Law wrote:
> what exactly is the problem with JDK 7?
If someone could write a very small unit test based on that, which works
on 6 but fails on 7, that'd be very helpful!
Perhaps something that does:
* new workbook
* create some cells
* sets some styles, fonts etc
* autosizes two columns
* saves
* loads
* checks column widths
* asserts that simple columns are right (6 and 7 ok?)
* asserts that some more complex ones are too (fails on 7?)
The simpler and more standalone it is, the better the chances someone will
be able to reproduce + investigate + fix!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: yet another autoSizeColumn problem.
Posted by David Law <Da...@apconsult.de>.
Aram,
what exactly is the problem with JDK 7?
All the best,
DaveLaw
On 04/03/2014 00:43, Aram Mirzadeh wrote:
>
> It's a JDK 7 problem ... if you can recompile with 1.6 it'll work.
>
> On 3/3/2014 4:39 PM, Crocker, David wrote:
>> 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;
>> }
>>
>> }
>>
>>
>>
>
>
> ---------------------------------------------------------------------
> 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: yet another autoSizeColumn problem.
Posted by "Crocker, David" <Da...@nrel.gov>.
Thanks!
-----Original Message-----
From: Aram Mirzadeh [mailto:awm@mbcli.com]
Sent: Monday, March 03, 2014 4:44 PM
To: user@poi.apache.org
Subject: Re: yet another autoSizeColumn problem.
It's a JDK 7 problem ... if you can recompile with 1.6 it'll work.
On 3/3/2014 4:39 PM, Crocker, David wrote:
> 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;
> }
>
> }
>
>
>
---------------------------------------------------------------------
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: yet another autoSizeColumn problem.
Posted by Aram Mirzadeh <aw...@mbcli.com>.
It's a JDK 7 problem ... if you can recompile with 1.6 it'll work.
On 3/3/2014 4:39 PM, Crocker, David wrote:
> 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;
> }
>
> }
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org