Changeset 7654 for trunk/www/common/plugin/parse_file.jsp

Timestamp:

Mar 15, 2019, 2:35:35 PM (3 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.

File:

• trunk/www/common/plugin/parse_file.jsp (modified) (7 diffs)

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>