This discussion is archived
2 Replies Latest reply: Sep 20, 2007 4:04 AM by 807600 RSS

Error in Parsing the Headers of the Excel or CSV File Using POI HSSF

807600 Newbie
Currently Being Moderated
HI All,

I am parsing a Excel file using HSSF and converting it into the XML format. I am taking First Line As Header of the Excel File. Also I am using these headers as the node. Here Name and E-Mail are Headers. Like

<?xml version = '1.0'?>
<root>
<row>
<Name>Ashish</Name>
<E-mailAddress>ambm16@gmail.com</E-mailAddress>
</row>
<row>
<Name>Anjali</Name>
<E-mailAddress>aj@gmail.com</E-mailAddress>
</row>
</root>

Everything is fine, but when I try to read the header, which contains spaces and other special symbols. It gives Exception.

How can we handle these Exceptions. Also Many a times it is reading some Excel files. and doesn't read to others.

Can Somebody tell me why it happens. Is it a Bug in POI or what ?
  • 1. Re: Error in Parsing the Headers of the Excel or CSV File Using POI HSSF
    DrClap Expert
    Currently Being Moderated
    You could definitely provide more details than what you did. Like the stack trace of the exception, and some of your code. Also using vague terms like "header" and "node" doesn't help. Especially since Excel files don't have a first line and they don't have headers.

    Just a wild guess based on what I think you might be asking: No, XML element names are not allowed to contain spaces.
  • 2. Re: Error in Parsing the Headers of the Excel or CSV File Using POI HSSF
    807600 Newbie
    Currently Being Moderated

    Here is what i m doing.

    public void generateXML(File excelFile, PrintWriter out) {
              try {
                   DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
                   DocumentBuilder builder = factory.newDocumentBuilder();
                   Document document = builder.newDocument();
                   Element rootElement = document.createElement("root");
                   document.appendChild(rootElement);               
                   //Element rowElement1 = document.createElement("row");
                   //rootElement.appendChild(rowElement1);

                   InputStream input = new FileInputStream(excelFile);
                   HSSFWorkbook workbook = new HSSFWorkbook(input);
                   HSSFSheet spreadsheet = workbook.getSheetAt(0);
                   HSSFRow row = spreadsheet.getRow(0);


                   //Getting the Colunm Names in the Array.
                   ArrayList colNames = new ArrayList();
                   for(int i=0; i
                        colNames.add((row.getCell((short)i)).toString());
                        System.out.println(row.getCell((short)i));
                   }


                   for (int i = 0; i <= spreadsheet.getLastRowNum(); i++) {
                        Element rowElement = document.createElement("row");
                        rootElement.appendChild(rowElement);
                        HSSFRow currentRow = spreadsheet.getRow(i);
                        if (currentRow == null) {
                             continue;
                        }
                        //System.out.println("Total Rows: " + (spreadsheet.getLastRowNum()+1));
                        //System.out.println("This row, row " + (i+1) + ", has a total of " + currentRow.getPhysicalNumberOfCells() + " cells.");


                        for (int col =0; col
                             int tempCount = col + 0;
                             short useColumn = (short)tempCount;
                             Element cellElement = document.createElement(colNames.get(col));
                             rowElement.appendChild(cellElement);
                             if(currentRow != null){
                                  String cellValue = getCellValue(currentRow.getCell(useColumn));
                                  if(cellValue != null){
                                       cellElement.appendChild(document.createTextNode(cellValue));
                                  }else{
                                       cellElement.appendChild(document.createTextNode("null"));
                                  }
                             }                                                  
                        }
                   }


                   TransformerFactory tFactory = TransformerFactory.newInstance();
                   Transformer transformer = tFactory.newTransformer();
                   //Add indentation to output
                   transformer.setOutputProperty(OutputKeys.INDENT, "yes");
                   transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2");


                   DOMSource source = new DOMSource(document);               
                   StreamResult result = new StreamResult(out);
                   transformer.transform(source, result);               


              } catch (IOException e) {
                   System.out.println("IOException " + e.getMessage());
              } catch (ParserConfigurationException e) {
                   System.out.println("ParserConfigurationException " + e.getMessage());
              } catch (TransformerConfigurationException e) {
                   System.out.println("TransformerConfigurationException "+ e.getMessage());
              } catch (TransformerException e) {
                   System.out.println("TransformerException " + e.getMessage());
              }
         }

    Here is the getCellValue()

    private String getCellValue (HSSFCell cell) {
    if (cell == null) return null;

    String result = null;

    int cellType = cell.getCellType();
    switch (cellType) {
    case HSSFCell.CELL_TYPE_BLANK:
    result = "null";
    break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
    result = cell.getBooleanCellValue() ? "true" : "false";
    break;
    case HSSFCell.CELL_TYPE_ERROR:
    result = "ERROR: " + cell.getErrorCellValue();
    break;
    case HSSFCell.CELL_TYPE_FORMULA:
    result = cell.getCellFormula();
    break;
    case HSSFCell.CELL_TYPE_NUMERIC:
    HSSFCellStyle cellStyle = cell.getCellStyle();
    short dataFormat = cellStyle.getDataFormat();

    if (dataFormat == 15) {
    result = cell.getDateCellValue().toString();
    } else {
    result = String.valueOf (
    cell.getNumericCellValue());
    }
    break;
    case HSSFCell.CELL_TYPE_STRING:
    result = cell.getStringCellValue();
    break;
    default: break;
    }

    return result;
    }

    Can you tell me, how can i solve the problem. In My XLS file, the First line Contains Column Names. These column names as node for each cell.