Convert XLS File to CSV

There are some situations where we need to import data from the CSV or the XLS file. It's quite easy to deal with a CSV file, as this is a plain text file. But what about the XLS files. Servoy has the inbuilt mechanism to import from XLS file. But this is limited to smart client only.

To fix this issue, we come across some Java code which converts the XLS file to CSV. Then the same code which works for importing CSV data works well for this. This Java code uses Apache POI library. Below is the method which does the conversion. The eval statements are to avoid the Servoy warning markers.

/**
* Convert XLS to csv
* 
* @param {plugins.file.JSFile} file The xls file to be converted to csv
* 
* @return {String} CSV string
* @public  
* @author Pradipta
*
* @properties={typeid:24,uuid:"304D3E9C-0B7F-4689-84C9-1C938F83EE27"}
* @SuppressWarnings(wrongparameters)
* @SuppressWarnings(unused)
*/
function convertXlsToCsv(file) {

  // convert String into InputStream
  var is = new java.io.ByteArrayInputStream(file.getBytes());

  // Get the workbook object for XLS file
  var workbook = eval("new org.apache.poi.hssf.usermodel.HSSFWorkbook(is)");

  // Get first sheet from the workbook
  var sheet = eval("workbook.getSheetAt(0)");
  var cell;
  var data = '';

  // Decide which rows to process
  var rowStart = eval("sheet.getFirstRowNum()");
  var rowEnd = eval("sheet.getLastRowNum()");

  for (var rowNum = rowStart; rowNum <= rowEnd; rowNum++) {
    var r = eval("sheet.getRow(rowNum)");

    var lastColumn = eval("r.getLastCellNum()");

    for (var cn = 0; cn < lastColumn; cn++) {       if (cn > 0) {
        data += (",");
      }

    cell = eval("r.getCell(cn, org.apache.poi.ss.usermodel.Row.RETURN_BLANK_AS_NULL)");
    if (cell == null) {

      // The spreadsheet is empty in this cell
      data += ('""');
    } else {

    // Do something useful with the cell's contents
    switch (eval("cell.getCellType()")) {

      case eval("org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN"):

        data += ("\"" + cell + "\"");
        break;

      case eval("org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC"):

        if (eval("org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)"))
        {

          // Date field
          var dateFormat = eval("new java.text.SimpleDateFormat(dateTimeFormat)");
          var  strCellValue = eval("dateFormat.format(cell.getDateCellValue())");
          data += ("\"" + strCellValue + "\"");
        }
        else {
          data += ("\"" + eval("cell.getNumericCellValue()") + "\"");
        }
        break;

      case eval("org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING"):

        var str = eval("cell.getStringCellValue()");
        /** @type {String} */
        var temp = str.toString();
        temp = temp.split('"').join('\"\"');
        utils.stringReplace(str.toString(), '"', '\"\"');
        data += ("\"" + temp + "\"");
        break;

      case eval("org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK"):

        data += ("\"\"");
        break;

      default:
        data += (cell);
    }
    }
    }

  // Append New Line character
  data += ('\n');
  }

  return data;
}