Changeset 7654

Timestamp:

Mar 15, 2019, 2:35:35 PM (4 years ago)

Author:

Nicklas Nordborg

Message:

References #2161: Import data from Excel files

Started to implement a utility for parsing an Excel file like it was a CSV file. The XlsxToCsvUtil is inspired by the one originally developed by Reggie, but it need to support a wider range of Excel features.

The "Test with file" function is currently used as a testbed.

Location:

trunk

Files:

• src/core/net/sf/basedb/core/Install.java (modified) (2 diffs)
• src/core/net/sf/basedb/util/excel/XlsxToCsvUtil.java (added)
• www/common/plugin/parse_file.js (modified) (1 diff)
• www/common/plugin/parse_file.jsp (modified) (7 diffs)
• www/common/plugin/test_with_file.js (modified) (7 diffs)
• www/common/plugin/test_with_file.jsp (modified) (7 diffs)
• www/filemanager/files/ajax.jsp (modified) (3 diffs)

trunk/src/core/net/sf/basedb/core/Install.java

@@ -71 +71 @@
 import net.sf.basedb.util.Values;
 import net.sf.basedb.util.XmlUtil2;
+import net.sf.basedb.util.excel.XlsxToCsvUtil;
 import net.sf.basedb.util.extensions.manager.Settings;
 import net.sf.basedb.util.extensions.xml.PluginInfo;
@@ -627 +628 @@
       createMimeType("application/vnd.ms-excel", "Excel spreadsheet (Microsoft)", "xlm", null, true);
       createMimeType("application/vnd.ms-excel", "Excel spreadsheet (Microsoft)", "xlw", null, true);
-      createMimeType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Microsoft Office Excel 2007 workbook", "xlsx", null, false);
+      createMimeType(XlsxToCsvUtil.XLSX_MIME_TYPE, "Microsoft Office Excel 2007 workbook", "xlsx", null, false);
       createMimeType("application/x-compress", "Unix compressed file", "z", null, false);
       createMimeType("application/zip", "DOS/PC - Pkzipped archive", "zip", null, false);

trunk/www/common/plugin/parse_file.js

@@ -44 +44 @@
   }
 
+  parse.pleaseWaitWhileParsing = function()
+  {
+    TabControl.setActiveTab('parsedFile.filedata');
+    Doc.hide('myContent');
+    Doc.show('waitWhileParsing');
+  }
+  
   parse.initColumnMapping = function(element, autoInit)
   {

trunk/www/common/plugin/parse_file.jsp

@@ -39 +39 @@
   import="net.sf.basedb.clients.web.Base"
   import="net.sf.basedb.util.Values"
+  import="net.sf.basedb.util.formatter.DateFormatter"
+  import="net.sf.basedb.util.NumberFormatUtil"
+  import="net.sf.basedb.util.excel.XlsxToCsvUtil"
+  import="net.sf.basedb.util.excel.XlsxToCsvUtil.SheetInfo"
   import="net.sf.basedb.util.fuzzy.StringMatcher"
   import="net.sf.basedb.util.fuzzy.StringMatcher.FuzzyMatch"
@@ -65 +69 @@
 {
   String path = request.getParameter("path");
-  String charsetName = Values.getString(request.getParameter("charset"), Config.getCharset());
+  boolean excelMode = "excel".equals(Values.getString(request.getParameter("filemode")));
+  String charsetName = Values.getString(request.getParameter("charset"), excelMode ? "UTF-8" : Config.getCharset());
+  String sheet = Values.getStringOrNull(request.getParameter("sheet"));
   PluginConfigurationRequest pcRequest = sc.getSessionSetting("plugin.configure.request");
   Plugin plugin = pcRequest.getPlugin();
@@ -100 +106 @@
     }
     
-    String dataSplitter = Values.getStringOrNull(request.getParameter("dataSplitter"));
+    String dataSplitter = Values.getString(request.getParameter("dataSplitter"), excelMode ? "\\t" : null);
     if (dataSplitter != null)
     {
@@ -163 +169 @@
       fileInputStream = wex.wrapInputStream(fileInputStream);
     }
+    
+    if (excelMode)
+    {
+      // TODO - this should be inside the FlatFileParser
+      String dateFormat = Values.getString(request.getParameter("dateFormat"), "yyyy-MM-dd");
+      XlsxToCsvUtil util = new XlsxToCsvUtil();
+      util.setDateFormat(new DateFormatter(dateFormat));
+      util.setEvaluateFormulas(true);
+      util.readWorkbook(fileInputStream);
+      SheetInfo sheetInfo = util.getSheetAsCsv(sheet);
+      fileInputStream = sheetInfo.parseToCsv();
+    }
+    
     parser.setInputStream(fileInputStream, charsetName);
     lastLine = parser.parseHeaders();
@@ -207 +226 @@
     title="File data"
     >
-    
+    <div id="waitWhileParsing" class="absolutefull bg-filled-100" style="display: none;">
+    <div class="messagecontainer error">
+      Parsing file. Please wait...
+    </div>
+    </div>
+    <div id="myContent">
     <%
     if (file == null)
     {
       %>
-      <div class="absolutefull bg-filled-50">
+      <div class="absolutefull bg-filled-100">
       <div class="messagecontainer error">
         No file selected. Please select a file to test above.
@@ -224 +248 @@
       {
         %>
-        <div class="bg-filled-50" style="padding: 5px;">
+        <div class="bg-filled-100" style="padding: 5px;">
         <div class="messagecontainer error" style="margin: 0;">
         <b>Could not parse the file <code><%=HTML.encodeTags(path)%></code></b>
@@ -384 +408 @@
     }
     %>
+    </div>
     </t:tab>
 

trunk/www/common/plugin/test_with_file.js

@@ -44 +44 @@
     Events.addEventHandler('path', 'base-selected', test.setFileCallback);
     Buttons.addClickHandler('btnParse', test.parseFile);
-    
+
+    Events.addEventHandler('sheet', 'change', test.autoParse);
     Events.addEventHandler('maxLines', 'keypress', Events.integerOnly)
     Events.addEventHandler('maxDataColumns', 'keypress', Events.integerOnly)
@@ -54 +55 @@
     
     test.initParameters();
-    test.autoParse();
+    var frm = document.forms['testwithfile'];
+    test.getFileInfo(Strings.trim(frm.path.value));
   }
   
@@ -76 +78 @@
     frm.file_id.value = event.detail.id;
     frm.path.value = event.detail.name;
-    test.updateCharset(event.detail.id);
-  }
-  
-  test.updateCharset = function(fileId)
+    test.getFileInfo(event.detail.name, event.detail.id);
+  }
+  
+  test.getFileInfo = function(path, fileId)
   {
     var request = Ajax.getXmlHttpRequest();
@@ -85 +87 @@
     {
       var url = App.getRoot()+'filemanager/files/ajax.jsp?ID='+App.getSessionId();
-      url += '&cmd=GetFileInfo&item_id=' + fileId;
+      url += '&cmd=GetFileInfo&checktype=1';
+      if (fileId) 
+      {
+        url += '&item_id=' + fileId;
+      }
+      else if (path)
+      {
+        url += '&path=' + encodeURIComponent(path);
+      }
+      else
+      {
+        return false;
+      }
+      var frm = document.forms['testwithfile'];
+      Doc.addClass('btnParse', 'disabled');
+      frm.sheet.disabled = true;
+
       request.open("GET", url, true);
-      Ajax.setReadyStateHandler(request, test.updateCharsetCallback);
+      Ajax.setReadyStateHandler(request, test.fileInfoCallback);
       request.send(null);
     }
@@ -93 +111 @@
   }
   
-  test.updateCharsetCallback = function(request)
-  {
+  test.fileInfoCallback = function(request)
+  {
+    var frm = document.forms['testwithfile'];
+    Doc.removeClass('btnParse', 'disabled');
+    
     var response = JSON.parse(request.responseText);
-    var charset = response.characterSet;
-    var frm = document.forms['testwithfile'];
-    if (charset)
-    {
-      Forms.selectListOption(frm.charset, charset);
+    var excelMode = response.excelMode;
+
+    frm.filemode.value = excelMode ? 'excel' : 'csv';
+    Data.set(document.body, 'filemode', frm.filemode.value);
+
+    frm.sheet.length = 0;
+    frm.sheet.disabled = !excelMode;
+    frm.charset.disabled = excelMode;
+    frm.dataSplitter.disabled = excelMode;
+    Doc.addOrRemoveClass('btnSplitterRegexp', 'disabled', excelMode);
+    frm.trimQuotes.disabled = excelMode;
+    if (excelMode)
+    {
+      frm.dataSplitter.value = '\\t';
+      Forms.selectListOption(frm.charset, 'UTF-8');
+      frm.trimQuotes.checked = false;
+      for (var i = 0; i < response.sheets.length; i++)
+      {
+        frm.sheet[frm.sheet.length] = new Option(response.sheets[i]);
+      }
+    }
+    else
+    {
+      if (charset)
+      {
+        var charset = response.characterSet;
+        Forms.selectListOption(frm.charset, charset);
+      }
+      frm.trimQuotes.checked = true;
     }
     test.autoParse();
@@ -128 +173 @@
       return false;
     }
+    
+    var parseWin = window.frames['parsed'];
+    parseWin.ParseFile.pleaseWaitWhileParsing();
     frm.submit();
   }
-  
   
   test.initParameters = function()
@@ -145 +192 @@
     frm.maxDataColumns.value = configWin.Configure.getParameterValue('maxDataColumns');
     frm.trimQuotes.checked = configWin.Configure.getParameterValue('trimQuotes') != 'false';
-    
+    frm.dateFormat.value = configWin.Configure.getParameterValue('dateFormat');
+      
     // Copy the list since ith is not certain that it is the same as Config.allCharsets() 
     var allCharsets = configWin.Doc.element('parameter-charset');

trunk/www/common/plugin/test_with_file.jsp

@@ -49 +49 @@
   %>
   <base:page type="popup" title="Test with file">
-  <base:head scripts="menu.js,~test_with_file.js" styles="menu.css" />
-  <base:body>
+  <base:head scripts="menu.js,~test_with_file.js" styles="menu.css">
+  <style>
+  body:not([data-filemode="csv"]) .csv-mode
+  {
+    display: none;
+  }
+  body:not([data-filemode="excel"]) .excel-mode
+  {
+    display: none;
+  }
+  </style>
+  </base:head>
+  <base:body data-filemode="csv">
 
   <m:menu 
@@ -168 +179 @@
     <form name="testwithfile" action="parse_file.jsp?ID=<%=ID%>" target="parsed" method="post">
     <input type="hidden" name="file_id" value="">
+    <input type="hidden" name="filemode" value="csv">
+    <input type="hidden" name="dateFormat" value="">
 
     <div class="content">
@@ -173 +186 @@
       <table class="fullform input100 bottomborder">
       <tr>
-        <th>File to test</th>
+        <th>File (CSV or Excel)</th>
         <td colspan="3">
           <table style="width: 100%;">
@@ -191 +204 @@
             value="<%=FlatFileParser.DEFAULT_MAX_UNKNOWN_LINES%>">
         </td>
+        <th class="leftborder">Excel Sheet</th>
+        <td>
+          <select id="sheet" name="sheet" style="min-width: 20em;" disabled>
+          </select>
+        </td>
+      </tr>
+      <tr >
+        <th>Header regexp</th>
+        <td>
+          <table style="width: 100%;">
+          <tr>
+          <td><input type="text" class="text" name="header"></td>
+          <td style="width: 11em;">
+            <base:button title="Predefined&hellip;" style="width: 10em;"
+              id="btnHeaderRegexp" data-predefined="predefinedHeaders" />
+          </td>
+          </tr>
+          </table>
+        </td>
         <th class="leftborder">Character set</th>
         <td>
-          <select name="charset">
+          <select name="charset" style="min-width: 20em;">
           <%
           String defaultCharset = Config.getCharset();
@@ -211 +243 @@
         </td>
       </tr>
-      <tr >
-        <th>Header regexp</th>
+      <tr>
+        <th>Data splitter regexp</th>
         <td>
           <table style="width: 100%;">
           <tr>
-          <td><input type="text" class="text" name="header"></td>
-          <td style="width: 11em;">
-            <base:button title="Predefined&hellip;" style="width: 10em;"
-              id="btnHeaderRegexp" data-predefined="predefinedHeaders" />
-          </td>
-          </tr>
-          </table>
-        </td>
-        <th class="leftborder">Min data columns</th>
-        <td>
-          <input type="text" class="text" name="minDataColumns" 
-            id="minDataColumns" style="width: 15em;" maxlength="10">
-        </td>
-      </tr>
-      <tr >
-        <th>Data splitter regexp</th>
-        <td>
-          <table style="width: 100%;">
-          <tr>
-          <td><input type="text" class="text required" name="dataSplitter" ></td>
+          <td><input type="text" class="text required" name="dataSplitter"></td>
           <td style="width: 11em;">
             <base:button title="Predefined&hellip;" style="width: 10em;"
@@ -243 +256 @@
           </table>
         </td>
-        <th class="leftborder">Max data columns</th>
-        <td>
-          <input type="text" class="text" name="maxDataColumns" 
-            id="maxDataColumns" style="width: 15em;" maxlength="10">
+        <th class="leftborder">Min data columns</th>
+        <td>
+          <input type="text" class="text" name="minDataColumns" 
+            id="minDataColumns" style="width: 15em;" maxlength="10">
         </td>
       </tr>
@@ -262 +275 @@
           </table>
         </td>
-        <th class="leftborder"><label for="trimQuotes">Remove quotes</label></th>
-        <td><input type="checkbox" name="trimQuotes" id="trimQuotes" value="1"></td>
+        <th class="leftborder">Max data columns</th>
+        <td>
+          <input type="text" class="text" name="maxDataColumns" 
+            id="maxDataColumns" style="width: 15em;" maxlength="10">
+        </td>
       </tr>
       <tr >
         <th>Data header regexp</th>
         <td><input type="text" class="text" name="dataHeader"></td>
-        <th class="subprompt leftborder"></th>
-        <td></td>
+        <th class="leftborder"><label for="trimQuotes">Remove quotes</label></th>
+        <td><input type="checkbox" name="trimQuotes" id="trimQuotes" value="1"></td>
       </tr>
       <tr class="dynamic">

trunk/www/filemanager/files/ajax.jsp

@@ -28 +28 @@
   import="net.sf.basedb.core.File"
   import="net.sf.basedb.core.InvalidDataException"
+  import="net.sf.basedb.core.Path"
   import="net.sf.basedb.util.Values"
   import="net.sf.basedb.util.FileUtil"
+  import="net.sf.basedb.util.excel.XlsxToCsvUtil"
   import="net.sf.basedb.util.error.ThrowableUtil"
   import="net.sf.basedb.clients.web.Base"
@@ -49 +51 @@
 final String root = request.getContextPath()+"/";
 final int itemId = Values.getInt(request.getParameter("item_id"));
+final String path = Values.getStringOrNull(request.getParameter("path"));
 DbControl dc = null;
 out.clear();
@@ -58 +61 @@
   {
     dc = sc.newDbControl();
-    File f = File.getById(dc, itemId);
+    File f = null;
+    if (itemId != 0)
+    {
+      f = File.getById(dc, itemId);
+    }
+    else
+    {
+      f = File.getByPath(dc, new Path(path, Path.Type.FILE), false);
+    }
     json.put("id", f.getId());
     json.put("name", f.getName());
     json.put("characterSet", f.getCharacterSet());
+    json.put("mimeType", f.getMimeType());
+    if (Values.getBoolean(request.getParameter("checktype")))
+    {
+      if (XlsxToCsvUtil.seemsLikeAnExcelFile(f))
+      {
+        XlsxToCsvUtil util = new XlsxToCsvUtil();
+        try
+        {
+          util.readWorkbook(f.getDownloadStream(0));
+          json.put("excelMode", true);
+          json.put("sheets", util.getSheetNames());
+        }
+        catch (Exception ex)
+        {} // It was maybe not an Excel file after all
+      }
+    }
     dc.close();
   }