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

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

References #2161: Import data from Excel files

Moved auto-detection of Excel files into the FlatFileParser and added some methods for date/timestamp formatting with sensible defaults. This should make it possible for more or less all plug-ins or code that uses the FlatFileParser to import from Excel files (via auto-conversion to CSV) as long as the data is in the first sheet. To get access to other sheets the 'charset' must specify the sheet name or index instead.

  • Property svn:eol-style set to native
  • Property svn:keywords set to Date Id
File size: 16.8 KB
Line 
1<%-- $Id: parse_file.jsp 7655 2019-03-19 10:09:07Z 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    String dateFormat = Values.getStringOrNull(request.getParameter("dateFormat"));
173    if (dateFormat != null) parser.setDefaultDateFormat(new DateFormatter(dateFormat));
174    String timestampFormat = Values.getStringOrNull(request.getParameter("timestampFormat"));
175    if (timestampFormat != null) parser.setDefaultTimestampFormat(new DateFormatter(timestampFormat));
176    String decimalSeparator = Values.getStringOrNull(request.getParameter("decimalSeparator"));
177    if (decimalSeparator != null)
178    {
179      char ds = "dot".equals(decimalSeparator) ? '.' : ',';
180      parser.setDefaultNumberFormat(NumberFormatUtil.getNumberFormat(ds, (char)0));
181    }
182   
183    parser.setInputStream(fileInputStream, excelMode ? sheet : charsetName);
184    lastLine = parser.parseHeaders();
185    dataIsFound = lastLine == FlatFileParser.LineType.DATA || lastLine == FlatFileParser.LineType.DATA_HEADER;
186    if (!dataIsFound)
187    {
188      messages.add("Start of data couldn't be found. Please try again with different settings.");
189    }
190  }
191  %>
192  <base:page type="iframe" title="Test with file">
193  <base:head scripts="tabcontrol-2.js,~parse_file.js" styles="table.css,tabcontrol.css">
194    <style>
195    #fileData td
196    {
197      white-space: nowrap;
198      max-width: 20em;
199      min-width: 4em;
200      overflow: hidden;
201      text-overflow: ellipsis;
202    }
203    #fileData td:first-child
204    {
205      font-weight: bold;
206    }
207    .skipped
208    {
209      padding: 1px 2px 1px 2px;
210      color: #777777;
211      font-style: italic;
212    }
213    </style>
214  </base:head>
215  <base:body>
216  <div class="absolutefull bottomborder">
217
218  <t:tabcontrol 
219    id="parsedFile"
220    subclass="absolutefull dialogtabcontrol"
221    position="top" active="filedata"
222    >
223  <t:tab
224    id="filedata"
225    title="File data"
226    >
227    <div id="waitWhileParsing" class="absolutefull bg-filled-100" style="display: none;">
228    <div class="messagecontainer error">
229      Parsing file. Please wait...
230    </div>
231    </div>
232    <div id="myContent">
233    <%
234    if (file == null)
235    {
236      %>
237      <div class="absolutefull bg-filled-100">
238      <div class="messagecontainer error">
239        No file selected. Please select a file to test above.
240      </div>
241      </div>
242      <%
243    }
244    else
245    {
246      if (messages.size() > 0)
247      {
248        %>
249        <div class="bg-filled-100" style="padding: 5px;">
250        <div class="messagecontainer error" style="margin: 0;">
251        <b>Could not parse the file <code><%=HTML.encodeTags(path)%></code></b>
252        <ol>
253        <li><%=Values.getString(messages, "<li>", true)%>
254        </ol>
255        </div>
256        </div>
257        <%
258      }
259      %>
260      <div class="itemlist" style="<%=messages.size()==0 ? "" : "border-top-width: 1px;"%>">
261        <div class="data">
262        <table style="border: 0;" id="fileData">
263          <thead class="bg-filled-100">
264          <tr>
265            <th>Line</th>
266            <th>Columns</th>
267            <th>Type</th>
268            <th>Use as</th>
269            <th colspan="99">File data</th>
270          </tr>
271          </thead>
272          <tbody class="rows">
273     
274        <%
275        int currentLine = 0;
276        int headerLines = parser.getLineCount();
277        String[] rowclass = new String[] { "bg-oddrow", "bg-evenrow" };
278        int rowClassIndex = 0;
279
280        while (currentLine < maxLines && currentLine < headerLines)
281        {
282          FlatFileParser.Line line = parser.getLine(currentLine);
283          linePatterns.add(Pattern.quote(line.line()));
284          String[] data = null;
285          int numMoreColumns = 0;
286          if (line.type() == FlatFileParser.LineType.HEADER)
287          {
288            data = new String[] { line.name(), line.value() };
289          }
290          else if (splitter != null)
291          {
292            data = parser.trimQuotes(splitter.split(line.line(), 99));
293            if (data.length == 99)
294            {
295              numMoreColumns = splitter.split(line.line()).length - 99;
296            }
297          }
298          else
299          {
300            data = new String[] { line.line() };
301          }
302          %>
303          <tr class="<%=rowclass[rowClassIndex]%> highlight">
304            <td class="cell"><%=line.lineNo()%></td>
305            <td class="cell"><%=data.length+numMoreColumns%></td>
306            <td class="cell"><%=line.type()%></td>
307            <td class="cell">
308              <select class="auto-init" 
309                data-auto-init="line-pattern"
310                data-line-no="<%=currentLine%>">
311              <option value="">
312              <option value="dataHeader">Data header
313              <option value="dataFooter">Data footer
314              </select>
315            </td>
316            <%
317            int colNo = 0;
318            for (String d : data)
319            {
320              %>
321              <td class="cell"><%=HTML.encodeTags(d) %></td>
322              <%
323              colNo++;
324              if (colNo == 99) break;
325            }
326            if (colNo < 99)
327            {
328              %>
329              <td class="cell" colspan="<%=99-colNo%>"></td>
330              <%
331            }
332            %>
333          </tr>
334          <%
335          currentLine++;
336          rowClassIndex = 1 - rowClassIndex;
337        }
338        if (currentLine < maxLines)
339        {
340          if (lastLine == FlatFileParser.LineType.DATA) parser.nextData();
341          while (currentLine < maxLines && parser.hasMoreData())
342          {
343            FlatFileParser.Data data = parser.nextData();
344            linePatterns.add(Pattern.quote(data.line()));
345            if (parser.getNumSkippedLines() > 0)
346            {
347              %>
348              <tr>
349                <td class="skipped bg-filled-100" colspan="103">
350                Skipped <%=parser.getNumSkippedLines()%> lines
351                (<%=parser.getIgnoredLines()%> ignored; <%=parser.getUnknownLines()%> unknown)
352                </td>
353              </tr>
354              <%
355            }
356            %>
357            <tr class="<%=rowclass[rowClassIndex]%> highlight">
358              <td class="cell"><%=data.lineNo()%></td>
359              <td class="cell"><%=data.columns()%></td>
360              <td class="cell">Data</td>
361              <td class="cell">
362                <select class="auto-init" 
363                  data-auto-init="line-pattern"
364                  data-line-no="<%=currentLine%>">
365                <option value="">
366                <option value="dataHeader">Data header
367                <option value="dataFooter">Data footer
368                </select>
369              </td>
370              <%
371              for (int i = 0; i < data.columns() && i < 99; ++i)
372              {
373                %>
374                <td class="cell"><%=HTML.encodeTags(data.get(i))%></td>
375                <%
376              }
377              if (data.columns() < 99)
378              {
379                %>
380                <td class="cell" colspan="<%=99-data.columns()%>"></td>
381                <%
382              }
383              %>
384            </tr>
385            <%
386            currentLine++;
387            rowClassIndex = 1 - rowClassIndex;
388          }
389          if (parser.getNumSkippedLines() > 0)
390          {
391            %>
392            <tr>
393              <td class="error" colspan="99">
394              Skipped <%=parser.getNumSkippedLines()%> lines
395              (<%=parser.getIgnoredLines()%> ignored; <%=parser.getUnknownLines()%> unknown)
396              </td>
397            </tr>
398            <%
399          }
400        }
401        %>
402        </tbody>
403        </table>
404      </div>
405      </div>
406      <%
407    }
408    %>
409    </div>
410    </t:tab>
411
412    <t:tab
413      id="mappings"
414      title="Column mappings"
415      visible="<%=dataIsFound && request.getParameter("mappingParameterNames") != null%>"
416      >
417      <%
418      StringBuilder sb = new StringBuilder();
419      List<String> headers = parser.getColumnHeaders();
420      int index = 0;
421      if (headers != null)
422      {
423        for (String header : headers)
424        {
425          sb.append("<option value=\"" + index + "\">" + HTML.encodeTags(header));
426          index++;
427        }
428      }
429      else
430      {
431        int maxDataColumns = Values.getInt(request.getParameter("maxDataColumns"), -1);
432        while (index < maxDataColumns)
433        {
434          sb.append("<option value=\"" + index + "\">" + index);
435          index++;
436        }
437      }
438      String mappings = sb.toString();
439      String[] mappingParameters = request.getParameter("mappingParameterNames").split(",");
440      if (headers != null)
441      {
442        List<String> labels = new ArrayList<String>();
443        for (String name : mappingParameters)
444        {
445          labels.add(request.getParameter("mapping." + name + ".label"));
446        }
447        StringMatcher sm = new StringMatcher();
448        List<FuzzyMatch> matches = sm.getBestPairs(labels, headers);
449        int i = 0;
450        for (FuzzyMatch fm : matches)
451        {
452          String name = mappingParameters[i];
453          if (fm != null)
454          {
455            JSONObject jsonFuzzyMatch = new JSONObject();
456            jsonFuzzyMatch.put("name", mappingParameters[i]);
457            jsonFuzzyMatch.put("columnIndex", headers.indexOf(fm.getValue()));
458            jsonFuzzyMatch.put("score", fm.getScore());
459            jsonFuzzy.add(jsonFuzzyMatch);
460          }
461          else
462          {
463            jsonFuzzy.add(null);
464          }
465          ++i;
466        }
467      }
468      %>
469      <form name="mappings">
470      <div class="absolutefull bg-filled-100" style="height: 2em;">
471        <table>
472        <tr>
473          <td style="padding-left: 4px;">
474            <b>Mapping style:</b>
475            <input type="radio" name="expressionStyle" id="expressionStyleSimple" value="1" checked><label for="expressionStyleSimple">Simple</label>
476            <input type="radio" name="expressionStyle" id="expressionStyleExpression" value="2"><label for="expressionStyleExpression">Expression</label>
477          </td>
478          <%
479          if (jsonFuzzy.size() > 0)
480          {
481            %>
482            <td style="padding-left: 4px;">
483            <base:button id="btnFuzzyMatches" title="Auto generate" 
484              tooltip="Generate mappings by finding the best match between Property and File column"/>
485            </td>
486            <td style="padding-left: 4px; width: 50%;">
487              Similarity score:
488              <input type="text" id="similarityScore" name="similarity" value="0.85">
489              (0 = bad; 1 = good)
490            </td>
491            <%
492          }
493          %>
494        </tr>
495        </table>
496      </div>
497     
498      <div class="absolutefull input100" style="top: 2em;">
499
500        <tbl:table id="col-mappings">
501          <tbl:columndef 
502            id="property"
503            title="Property"
504          />
505          <tbl:columndef 
506            id="expression"
507            title="Mapping expression"
508          />
509          <tbl:columndef 
510            id="columns"
511            title="File columns"
512          />
513          <tbl:data>
514            <tbl:headers>
515              <tbl:headerrow>
516                <tbl:columnheaders />
517              </tbl:headerrow>
518            </tbl:headers>           
519            <tbl:rows>
520            <%
521            for (String name : mappingParameters)
522            {
523              %>
524              <tbl:row>
525                <tbl:cell column="property"><%=HTML.encodeTags(request.getParameter("mapping." + name + ".label"))%></tbl:cell>
526                <tbl:cell column="expression">
527               
528                    <table style="width: 100%;">
529                    <tr>
530                      <td>
531                      <input type="text" class="text auto-init" data-auto-init="column-mapping"
532                        name="mapping.<%=name%>.expression"
533                        maxlength="80" 
534                        value="<%=HTML.encodeTags(request.getParameter("mapping." + name + ".expression"))%>">
535                      </td>
536                    <td style="width: 22px;">
537                      <base:icon image="cancel.png" subclass="auto-init" id="<%="clear."+name %>"
538                        data-auto-init="column-mapping-clear" data-mapping="<%=name%>"
539                        tooltip="Clear this expression"/>
540                    </td>
541                    </tr>
542                    </table>
543                </tbl:cell>
544               
545                <tbl:cell column="columns">
546                  <select name="list.<%=name%>" class="auto-init"
547                    data-auto-init="column-mapping-preset" data-mapping="<%=name%>">
548                  <option value="">
549                  <%=mappings%>
550                  </select>
551                </tbl:cell>
552             
553              </tbl:row>
554              <%
555            }
556            %>
557            </tbl:rows>
558          </tbl:data>
559        </tbl:table>
560      </div>
561    </form>
562    </t:tab>
563     
564    </t:tabcontrol>
565   
566      <div id="page-data" class="datacontainer"
567        data-line-patterns="<%=HTML.encodeTags(linePatterns.toJSONString())%>"
568        <%
569        if (parser != null && parser.getColumnHeaders() != null)
570        {
571          %>
572          data-column-headers="<%=HTML.encodeTags(JSONArray.toJSONString(parser.getColumnHeaders())) %>"
573          <%
574        }
575        %>
576        data-fuzzy-matches="<%=HTML.encodeTags(jsonFuzzy.toJSONString()) %>"
577        ></div>
578    </div>
579  </base:body>
580  </base:page>
581  <%
582}
583finally
584{
585  if (fileInputStream != null) fileInputStream.close();
586  if (dc != null) dc.close();
587}
588%>
589
Note: See TracBrowser for help on using the repository browser.