source: trunk/www/common/plugin/parse_file.jsp @ 7654

Last change on this file since 7654 was 7654, checked in by Nicklas Nordborg, 3 years ago

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.

  • Property svn:eol-style set to native
  • Property svn:keywords set to Date Id
File size: 16.5 KB
Line 
1<%-- $Id: parse_file.jsp 7654 2019-03-15 13:35:35Z nicklas $
2  ------------------------------------------------------------------
3  Copyright (C) 2006 Johan Enell, Nicklas Nordborg
4  Copyright (C) 2007 Nicklas Nordborg
5
6  This file is part of BASE - BioArray Software Environment.
7  Available at http://base.thep.lu.se/
8
9  BASE is free software; you can redistribute it and/or
10  modify it under the terms of the GNU General Public License
11  as published by the Free Software Foundation; either version 3
12  of the License, or (at your option) any later version.
13
14  BASE is distributed in the hope that it will be useful,
15  but WITHOUT ANY WARRANTY; without even the implied warranty of
16  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17  GNU General Public License for more details.
18
19  You should have received a copy of the GNU General Public License
20  along with BASE. If not, see <http://www.gnu.org/licenses/>.
21  ------------------------------------------------------------------
22
23  @author Nicklas
24  @version 2.0
25--%>
26<%@ page pageEncoding="UTF-8" session="false"
27  contentType="text/html; charset=UTF-8" 
28  import="net.sf.basedb.core.SessionControl"
29  import="net.sf.basedb.core.DbControl"
30  import="net.sf.basedb.core.Config"
31  import="net.sf.basedb.core.Item"
32  import="net.sf.basedb.core.File"
33  import="net.sf.basedb.core.Path"
34  import="net.sf.basedb.core.Location"
35  import="net.sf.basedb.core.PluginConfigurationRequest"
36  import="net.sf.basedb.core.plugin.Plugin"
37  import="net.sf.basedb.util.parser.FlatFileParser"
38  import="net.sf.basedb.util.parser.WrappedConfigureByExample"
39  import="net.sf.basedb.clients.web.Base"
40  import="net.sf.basedb.util.Values"
41  import="net.sf.basedb.util.formatter.DateFormatter"
42  import="net.sf.basedb.util.NumberFormatUtil"
43  import="net.sf.basedb.util.excel.XlsxToCsvUtil"
44  import="net.sf.basedb.util.excel.XlsxToCsvUtil.SheetInfo"
45  import="net.sf.basedb.util.fuzzy.StringMatcher"
46  import="net.sf.basedb.util.fuzzy.StringMatcher.FuzzyMatch"
47  import="net.sf.basedb.clients.web.util.HTML"
48  import="java.util.regex.Pattern"
49  import="java.util.List"
50  import="java.util.ArrayList"
51  import="java.util.Set"
52  import="java.io.InputStream"
53  import="org.json.simple.JSONArray"
54  import="org.json.simple.JSONObject"
55%>
56<%@ taglib prefix="base" uri="/WEB-INF/base.tld" %>
57<%@ taglib prefix="m" uri="/WEB-INF/menu.tld" %>
58<%@ taglib prefix="t" uri="/WEB-INF/tab.tld" %>
59<%@ taglib prefix="tbl" uri="/WEB-INF/table.tld" %>
60
61<%
62request.setCharacterEncoding("UTF-8");
63final SessionControl sc = Base.getExistingSessionControl(pageContext, true);
64final String ID = sc.getId();
65final DbControl dc = sc.newDbControl();
66final float scale = Base.getScale(sc);
67InputStream fileInputStream = null;
68try
69{
70  String path = request.getParameter("path");
71  boolean excelMode = "excel".equals(Values.getString(request.getParameter("filemode")));
72  String charsetName = Values.getString(request.getParameter("charset"), excelMode ? "UTF-8" : Config.getCharset());
73  String sheet = Values.getStringOrNull(request.getParameter("sheet"));
74  PluginConfigurationRequest pcRequest = sc.getSessionSetting("plugin.configure.request");
75  Plugin plugin = pcRequest.getPlugin();
76 
77  File file = null;
78  FlatFileParser parser = null;
79  FlatFileParser.LineType lastLine = null;
80  Pattern splitter = null;
81  boolean dataIsFound = false;
82  int maxLines = Values.getInt(request.getParameter("maxLines"), FlatFileParser.DEFAULT_MAX_UNKNOWN_LINES);
83 
84  List<String> messages = new ArrayList<String>();
85  JSONArray linePatterns = new JSONArray();
86  JSONArray jsonFuzzy = new JSONArray();
87 
88  if (path != null)
89  {
90    Path p = new Path(path, Path.Type.FILE);
91    file = File.getByPath(dc, p, false);
92    parser = new FlatFileParser();
93    parser.setMaxUnknownLines(maxLines);
94   
95    String header = Values.getStringOrNull(request.getParameter("header"));
96    if (header != null)
97    {
98      try
99      {
100        parser.setHeaderRegexp(Pattern.compile(header));
101      }
102      catch (Throwable t)
103      {
104        messages.add("Invalid regular expression for header: " + HTML.encodeTags(header));
105      }
106    }
107   
108    String dataSplitter = Values.getString(request.getParameter("dataSplitter"), excelMode ? "\\t" : null);
109    if (dataSplitter != null)
110    {
111      try
112      {
113        splitter = Pattern.compile(dataSplitter);
114        parser.setDataSplitterRegexp(splitter);
115      }
116      catch (Throwable t)
117      {
118        messages.add("Invalid regular expression for data splitter: " + HTML.encodeTags(dataSplitter));
119      }
120    }
121
122    String dataHeader = Values.getStringOrNull(request.getParameter("dataHeader"));
123    if (dataHeader != null)
124    {
125      try
126      {
127        parser.setDataHeaderRegexp(Pattern.compile(dataHeader));
128      }
129      catch (Throwable t)
130      {
131        messages.add("Invalid regular expression for data header: " + HTML.encodeTags(dataHeader));
132      }
133    }
134   
135    String dataFooter = Values.getStringOrNull(request.getParameter("dataFooter"));
136    if (dataFooter != null)
137    {
138      try
139      {
140        parser.setDataFooterRegexp(Pattern.compile(dataFooter));
141      }
142      catch (Throwable t)
143      {
144        messages.add("Invalid regular expression for data footer: " + HTML.encodeTags(dataFooter));
145      }
146    }
147   
148    String ignore = Values.getStringOrNull(request.getParameter("ignore"));
149    if (ignore != null)
150    {
151      try
152      {
153        parser.setIgnoreRegexp(Pattern.compile(ignore));
154      }
155      catch (Throwable t)
156      {
157        messages.add("Invalid regular expression for ignore: " + HTML.encodeTags(ignore));
158      }
159    }
160
161    parser.setTrimQuotes(Values.getBoolean(request.getParameter("trimQuotes")));
162    parser.setMinDataColumns(Values.getInt(request.getParameter("minDataColumns"), 0));
163    parser.setMaxDataColumns(Values.getInt(request.getParameter("maxDataColumns"), -1));
164   
165    fileInputStream = file.getDownloadStream(0);
166    if (plugin instanceof WrappedConfigureByExample)
167    {
168      WrappedConfigureByExample wex = (WrappedConfigureByExample)plugin;
169      fileInputStream = wex.wrapInputStream(fileInputStream);
170    }
171   
172    if (excelMode)
173    {
174      // TODO - this should be inside the FlatFileParser
175      String dateFormat = Values.getString(request.getParameter("dateFormat"), "yyyy-MM-dd");
176      XlsxToCsvUtil util = new XlsxToCsvUtil();
177      util.setDateFormat(new DateFormatter(dateFormat));
178      util.setEvaluateFormulas(true);
179      util.readWorkbook(fileInputStream);
180      SheetInfo sheetInfo = util.getSheetAsCsv(sheet);
181      fileInputStream = sheetInfo.parseToCsv();
182    }
183   
184    parser.setInputStream(fileInputStream, charsetName);
185    lastLine = parser.parseHeaders();
186    dataIsFound = lastLine == FlatFileParser.LineType.DATA || lastLine == FlatFileParser.LineType.DATA_HEADER;
187    if (!dataIsFound)
188    {
189      messages.add("Start of data couldn't be found. Please try again with different settings.");
190    }
191  }
192  %>
193  <base:page type="iframe" title="Test with file">
194  <base:head scripts="tabcontrol-2.js,~parse_file.js" styles="table.css,tabcontrol.css">
195    <style>
196    #fileData td
197    {
198      white-space: nowrap;
199      max-width: 20em;
200      min-width: 4em;
201      overflow: hidden;
202      text-overflow: ellipsis;
203    }
204    #fileData td:first-child
205    {
206      font-weight: bold;
207    }
208    .skipped
209    {
210      padding: 1px 2px 1px 2px;
211      color: #777777;
212      font-style: italic;
213    }
214    </style>
215  </base:head>
216  <base:body>
217  <div class="absolutefull bottomborder">
218
219  <t:tabcontrol 
220    id="parsedFile"
221    subclass="absolutefull dialogtabcontrol"
222    position="top" active="filedata"
223    >
224  <t:tab
225    id="filedata"
226    title="File data"
227    >
228    <div id="waitWhileParsing" class="absolutefull bg-filled-100" style="display: none;">
229    <div class="messagecontainer error">
230      Parsing file. Please wait...
231    </div>
232    </div>
233    <div id="myContent">
234    <%
235    if (file == null)
236    {
237      %>
238      <div class="absolutefull bg-filled-100">
239      <div class="messagecontainer error">
240        No file selected. Please select a file to test above.
241      </div>
242      </div>
243      <%
244    }
245    else
246    {
247      if (messages.size() > 0)
248      {
249        %>
250        <div class="bg-filled-100" style="padding: 5px;">
251        <div class="messagecontainer error" style="margin: 0;">
252        <b>Could not parse the file <code><%=HTML.encodeTags(path)%></code></b>
253        <ol>
254        <li><%=Values.getString(messages, "<li>", true)%>
255        </ol>
256        </div>
257        </div>
258        <%
259      }
260      %>
261      <div class="itemlist" style="<%=messages.size()==0 ? "" : "border-top-width: 1px;"%>">
262        <div class="data">
263        <table style="border: 0;" id="fileData">
264          <thead class="bg-filled-100">
265          <tr>
266            <th>Line</th>
267            <th>Columns</th>
268            <th>Type</th>
269            <th>Use as</th>
270            <th colspan="99">File data</th>
271          </tr>
272          </thead>
273          <tbody class="rows">
274     
275        <%
276        int currentLine = 0;
277        int headerLines = parser.getLineCount();
278        String[] rowclass = new String[] { "bg-oddrow", "bg-evenrow" };
279        int rowClassIndex = 0;
280
281        while (currentLine < maxLines && currentLine < headerLines)
282        {
283          FlatFileParser.Line line = parser.getLine(currentLine);
284          linePatterns.add(Pattern.quote(line.line()));
285          String[] data = null;
286          int numMoreColumns = 0;
287          if (line.type() == FlatFileParser.LineType.HEADER)
288          {
289            data = new String[] { line.name(), line.value() };
290          }
291          else if (splitter != null)
292          {
293            data = parser.trimQuotes(splitter.split(line.line(), 99));
294            if (data.length == 99)
295            {
296              numMoreColumns = splitter.split(line.line()).length - 99;
297            }
298          }
299          else
300          {
301            data = new String[] { line.line() };
302          }
303          %>
304          <tr class="<%=rowclass[rowClassIndex]%> highlight">
305            <td class="cell"><%=line.lineNo()%></td>
306            <td class="cell"><%=data.length+numMoreColumns%></td>
307            <td class="cell"><%=line.type()%></td>
308            <td class="cell">
309              <select class="auto-init" 
310                data-auto-init="line-pattern"
311                data-line-no="<%=currentLine%>">
312              <option value="">
313              <option value="dataHeader">Data header
314              <option value="dataFooter">Data footer
315              </select>
316            </td>
317            <%
318            int colNo = 0;
319            for (String d : data)
320            {
321              %>
322              <td class="cell"><%=HTML.encodeTags(d) %></td>
323              <%
324              colNo++;
325              if (colNo == 99) break;
326            }
327            if (colNo < 99)
328            {
329              %>
330              <td class="cell" colspan="<%=99-colNo%>"></td>
331              <%
332            }
333            %>
334          </tr>
335          <%
336          currentLine++;
337          rowClassIndex = 1 - rowClassIndex;
338        }
339        if (currentLine < maxLines)
340        {
341          if (lastLine == FlatFileParser.LineType.DATA) parser.nextData();
342          while (currentLine < maxLines && parser.hasMoreData())
343          {
344            FlatFileParser.Data data = parser.nextData();
345            linePatterns.add(Pattern.quote(data.line()));
346            if (parser.getNumSkippedLines() > 0)
347            {
348              %>
349              <tr>
350                <td class="skipped bg-filled-100" colspan="103">
351                Skipped <%=parser.getNumSkippedLines()%> lines
352                (<%=parser.getIgnoredLines()%> ignored; <%=parser.getUnknownLines()%> unknown)
353                </td>
354              </tr>
355              <%
356            }
357            %>
358            <tr class="<%=rowclass[rowClassIndex]%> highlight">
359              <td class="cell"><%=data.lineNo()%></td>
360              <td class="cell"><%=data.columns()%></td>
361              <td class="cell">Data</td>
362              <td class="cell">
363                <select class="auto-init" 
364                  data-auto-init="line-pattern"
365                  data-line-no="<%=currentLine%>">
366                <option value="">
367                <option value="dataHeader">Data header
368                <option value="dataFooter">Data footer
369                </select>
370              </td>
371              <%
372              for (int i = 0; i < data.columns() && i < 99; ++i)
373              {
374                %>
375                <td class="cell"><%=HTML.encodeTags(data.get(i))%></td>
376                <%
377              }
378              if (data.columns() < 99)
379              {
380                %>
381                <td class="cell" colspan="<%=99-data.columns()%>"></td>
382                <%
383              }
384              %>
385            </tr>
386            <%
387            currentLine++;
388            rowClassIndex = 1 - rowClassIndex;
389          }
390          if (parser.getNumSkippedLines() > 0)
391          {
392            %>
393            <tr>
394              <td class="error" colspan="99">
395              Skipped <%=parser.getNumSkippedLines()%> lines
396              (<%=parser.getIgnoredLines()%> ignored; <%=parser.getUnknownLines()%> unknown)
397              </td>
398            </tr>
399            <%
400          }
401        }
402        %>
403        </tbody>
404        </table>
405      </div>
406      </div>
407      <%
408    }
409    %>
410    </div>
411    </t:tab>
412
413    <t:tab
414      id="mappings"
415      title="Column mappings"
416      visible="<%=dataIsFound && request.getParameter("mappingParameterNames") != null%>"
417      >
418      <%
419      StringBuilder sb = new StringBuilder();
420      List<String> headers = parser.getColumnHeaders();
421      int index = 0;
422      if (headers != null)
423      {
424        for (String header : headers)
425        {
426          sb.append("<option value=\"" + index + "\">" + HTML.encodeTags(header));
427          index++;
428        }
429      }
430      else
431      {
432        int maxDataColumns = Values.getInt(request.getParameter("maxDataColumns"), -1);
433        while (index < maxDataColumns)
434        {
435          sb.append("<option value=\"" + index + "\">" + index);
436          index++;
437        }
438      }
439      String mappings = sb.toString();
440      String[] mappingParameters = request.getParameter("mappingParameterNames").split(",");
441      if (headers != null)
442      {
443        List<String> labels = new ArrayList<String>();
444        for (String name : mappingParameters)
445        {
446          labels.add(request.getParameter("mapping." + name + ".label"));
447        }
448        StringMatcher sm = new StringMatcher();
449        List<FuzzyMatch> matches = sm.getBestPairs(labels, headers);
450        int i = 0;
451        for (FuzzyMatch fm : matches)
452        {
453          String name = mappingParameters[i];
454          if (fm != null)
455          {
456            JSONObject jsonFuzzyMatch = new JSONObject();
457            jsonFuzzyMatch.put("name", mappingParameters[i]);
458            jsonFuzzyMatch.put("columnIndex", headers.indexOf(fm.getValue()));
459            jsonFuzzyMatch.put("score", fm.getScore());
460            jsonFuzzy.add(jsonFuzzyMatch);
461          }
462          else
463          {
464            jsonFuzzy.add(null);
465          }
466          ++i;
467        }
468      }
469      %>
470      <form name="mappings">
471      <div class="absolutefull bg-filled-100" style="height: 2em;">
472        <table>
473        <tr>
474          <td style="padding-left: 4px;">
475            <b>Mapping style:</b>
476            <input type="radio" name="expressionStyle" id="expressionStyleSimple" value="1" checked><label for="expressionStyleSimple">Simple</label>
477            <input type="radio" name="expressionStyle" id="expressionStyleExpression" value="2"><label for="expressionStyleExpression">Expression</label>
478          </td>
479          <%
480          if (jsonFuzzy.size() > 0)
481          {
482            %>
483            <td style="padding-left: 4px;">
484            <base:button id="btnFuzzyMatches" title="Auto generate" 
485              tooltip="Generate mappings by finding the best match between Property and File column"/>
486            </td>
487            <td style="padding-left: 4px; width: 50%;">
488              Similarity score:
489              <input type="text" id="similarityScore" name="similarity" value="0.85">
490              (0 = bad; 1 = good)
491            </td>
492            <%
493          }
494          %>
495        </tr>
496        </table>
497      </div>
498     
499      <div class="absolutefull input100" style="top: 2em;">
500
501        <tbl:table id="col-mappings">
502          <tbl:columndef 
503            id="property"
504            title="Property"
505          />
506          <tbl:columndef 
507            id="expression"
508            title="Mapping expression"
509          />
510          <tbl:columndef 
511            id="columns"
512            title="File columns"
513          />
514          <tbl:data>
515            <tbl:headers>
516              <tbl:headerrow>
517                <tbl:columnheaders />
518              </tbl:headerrow>
519            </tbl:headers>           
520            <tbl:rows>
521            <%
522            for (String name : mappingParameters)
523            {
524              %>
525              <tbl:row>
526                <tbl:cell column="property"><%=HTML.encodeTags(request.getParameter("mapping." + name + ".label"))%></tbl:cell>
527                <tbl:cell column="expression">
528               
529                    <table style="width: 100%;">
530                    <tr>
531                      <td>
532                      <input type="text" class="text auto-init" data-auto-init="column-mapping"
533                        name="mapping.<%=name%>.expression"
534                        maxlength="80" 
535                        value="<%=HTML.encodeTags(request.getParameter("mapping." + name + ".expression"))%>">
536                      </td>
537                    <td style="width: 22px;">
538                      <base:icon image="cancel.png" subclass="auto-init" id="<%="clear."+name %>"
539                        data-auto-init="column-mapping-clear" data-mapping="<%=name%>"
540                        tooltip="Clear this expression"/>
541                    </td>
542                    </tr>
543                    </table>
544                </tbl:cell>
545               
546                <tbl:cell column="columns">
547                  <select name="list.<%=name%>" class="auto-init"
548                    data-auto-init="column-mapping-preset" data-mapping="<%=name%>">
549                  <option value="">
550                  <%=mappings%>
551                  </select>
552                </tbl:cell>
553             
554              </tbl:row>
555              <%
556            }
557            %>
558            </tbl:rows>
559          </tbl:data>
560        </tbl:table>
561      </div>
562    </form>
563    </t:tab>
564     
565    </t:tabcontrol>
566   
567      <div id="page-data" class="datacontainer"
568        data-line-patterns="<%=HTML.encodeTags(linePatterns.toJSONString())%>"
569        <%
570        if (parser != null && parser.getColumnHeaders() != null)
571        {
572          %>
573          data-column-headers="<%=HTML.encodeTags(JSONArray.toJSONString(parser.getColumnHeaders())) %>"
574          <%
575        }
576        %>
577        data-fuzzy-matches="<%=HTML.encodeTags(jsonFuzzy.toJSONString()) %>"
578        ></div>
579    </div>
580  </base:body>
581  </base:page>
582  <%
583}
584finally
585{
586  if (fileInputStream != null) fileInputStream.close();
587  if (dc != null) dc.close();
588}
589%>
590
Note: See TracBrowser for help on using the repository browser.