Thanks!
-----Original Message-----
From: Aram Mirzadeh [mailto:[email protected]]
Sent: Monday, March 03, 2014 4:44 PM
To: [email protected]
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: [email protected] For additional
commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
