source: trunk/www/common/plugin/test_with_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: 9.3 KB
Line 
1<%-- $Id: test_with_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.Item"
31  import="net.sf.basedb.core.Config"
32  import="net.sf.basedb.core.Location"
33  import="net.sf.basedb.util.parser.FlatFileParser"
34  import="net.sf.basedb.clients.web.Base"
35  import="net.sf.basedb.util.Values"
36  import="net.sf.basedb.plugins.util.Parameters"
37  import="net.sf.basedb.clients.web.util.HTML"
38  import="java.nio.charset.Charset"
39%>
40<%@ taglib prefix="base" uri="/WEB-INF/base.tld" %>
41<%@ taglib prefix="m" uri="/WEB-INF/menu.tld" %>
42
43<%
44final SessionControl sc = Base.getExistingSessionControl(pageContext, true);
45final String ID = sc.getId();
46final DbControl dc = sc.newDbControl();
47try
48{
49  %>
50  <base:page type="popup" title="Test with file">
51  <base:head scripts="menu.js,~test_with_file.js" styles="menu.css">
52  <style>
53  body:not([data-filemode="csv"]) .csv-mode
54  {
55    display: none;
56  }
57  body:not([data-filemode="excel"]) .excel-mode
58  {
59    display: none;
60  }
61  </style>
62  </base:head>
63  <base:body data-filemode="csv">
64
65  <m:menu 
66    id="predefinedSplitters"
67    style="display: none;">
68 
69    <m:menuitem 
70      subclass="auto-init"
71      data-auto-init="set-preset"
72      data-field="dataSplitter"
73      data-value="\t"
74      title="Tab" 
75      tooltip="Splits data on tab"
76    />
77    <m:menuitem 
78      subclass="auto-init"
79      data-auto-init="set-preset"
80      data-field="dataSplitter"
81      data-value=","
82      title="Comma" 
83      tooltip="Splits data on comma"
84    />
85    <m:menuitem 
86      subclass="auto-init"
87      data-auto-init="set-preset"
88      data-field="dataSplitter"
89      data-value=";"
90      title="Semicolon" 
91      tooltip="Splits data on semicolon"
92    />
93    <m:menuseparator />
94    <m:menuitem 
95      subclass="auto-init"
96      data-auto-init="set-preset"
97      data-field="dataSplitter"
98      data-value="(?!&#34;)\t(?=&#34;)"
99      title="Tab with quotes" 
100      tooltip="Splits data on tab, but only if it is has quotes before and after"
101    />
102    <m:menuitem 
103      subclass="auto-init"
104      data-auto-init="set-preset"
105      data-field="dataSplitter"
106      data-value="(?!&#34;),(?=&#34;)"
107      title="Comma with quotes" 
108      tooltip="Splits data on comma, but only if it is has quotes before and after"
109    />
110    <m:menuitem 
111      subclass="auto-init"
112      data-auto-init="set-preset"
113      data-field="dataSplitter"
114      data-value="(?!&#34;);(?=&#34;)"
115      title="Semicolon with quotes" 
116      tooltip="Splits data on semicolon, but only if it is has quotes before and after"
117    />
118    <m:menuseparator />
119    <m:menuitem 
120      subclass="auto-init"
121      data-auto-init="set-preset"
122      data-field="dataSplitter"
123      data-value=",(?=(?:[^&#34;]*&#34;[^&#34;]*&#34;)*(?![^&#34;]*&#34;))"
124      title="Comma; ignored inside quotes" 
125      tooltip="Splits data on comma, but not if they are inside quotes"
126    />
127    <m:menuitem 
128      subclass="auto-init"
129      data-auto-init="set-preset"
130      data-field="dataSplitter"
131      data-value=";(?=(?:[^&#34;]*&#34;[^&#34;]*&#34;)*(?![^&#34;]*&#34;))"
132      title="Semicolon; ignored inside quotes"
133      tooltip="Splits data on semicolon, but not if they are inside quotes"
134    />
135  </m:menu>
136
137  <m:menu 
138    id="predefinedHeaders"
139    style="display: none;">
140    <m:menuitem 
141      subclass="auto-init"
142      data-auto-init="set-preset"
143      data-field="header"
144      data-value="(.+)=(.*)"
145      title="Key = value" 
146      tooltip="Headers have a key and value separated by an equal sign"
147    />
148    <m:menuitem
149      subclass="auto-init"
150      data-auto-init="set-preset"
151      data-field="header"
152      data-value="[\s\&#34;]*(.+?)[\s\&#34;]*=[\s\&#34;]*(.*?)[\s\&#34;]*"
153      title="Key = value; remove whitespace and quotes"
154      tooltip="Header have a key and value separated by an equal sign; whitespace and quotes are removed"
155    />
156  </m:menu>
157 
158  <m:menu 
159    id="predefinedIgnore"
160    style="display: none;">
161    <m:menuitem 
162      subclass="auto-init"
163      data-auto-init="set-preset"
164      data-field="ignore"
165      data-value="#.*"
166      title="# Comment" 
167      tooltip="Ignore all lines starting with #"
168    />
169    <m:menuitem 
170      subclass="auto-init"
171      data-auto-init="set-preset"
172      data-field="ignore"
173      data-value="\s*"
174      title="Empty line" 
175      tooltip="Ignore all lines that contains nothing but white-space"
176    />
177  </m:menu>
178    <h1>Test with file <base:help helpid="runplugin.testwithfile" /></h1>
179    <form name="testwithfile" action="parse_file.jsp?ID=<%=ID%>" target="parsed" method="post">
180    <input type="hidden" name="file_id" value="">
181    <input type="hidden" name="filemode" value="csv">
182    <input type="hidden" name="dateFormat" value="">
183
184    <div class="content">
185      <div class="absolutefull" style="height: 15em;">
186      <table class="fullform input100 bottomborder">
187      <tr>
188        <th>File (CSV or Excel)</th>
189        <td colspan="3">
190          <table style="width: 100%;">
191            <tr>
192            <td><input type="text" class="text required" name="path" id="path" value=""></td>
193            <td style="width: 11em;"><base:button id="btnBrowse" title="Browse&hellip;" style="width: 10em;" /></td>
194            <td style="width: 11em;"><base:button id="btnParse" title="Parse the file" style="width: 10em;" image="gonext.png"/></td>
195            </tr>
196          </table>
197        </td>
198      </tr>
199      <tr>
200        <th>Lines to parse</th>
201        <td>
202          <input type="text" class="text" name="maxLines" 
203            id="maxLines" style="width: 15em;" maxlength="10"
204            value="<%=FlatFileParser.DEFAULT_MAX_UNKNOWN_LINES%>">
205        </td>
206        <th class="leftborder">Excel Sheet</th>
207        <td>
208          <select id="sheet" name="sheet" style="min-width: 20em;" disabled>
209          </select>
210        </td>
211      </tr>
212      <tr >
213        <th>Header regexp</th>
214        <td>
215          <table style="width: 100%;">
216          <tr>
217          <td><input type="text" class="text" name="header"></td>
218          <td style="width: 11em;">
219            <base:button title="Predefined&hellip;" style="width: 10em;"
220              id="btnHeaderRegexp" data-predefined="predefinedHeaders" />
221          </td>
222          </tr>
223          </table>
224        </td>
225        <th class="leftborder">Character set</th>
226        <td>
227          <select name="charset" style="min-width: 20em;">
228          <%
229          String defaultCharset = Config.getCharset();
230          int numCommon = Config.getCommonCharsets().size();
231          int i = 0;
232          for (String charset : Config.getAllCharsets())
233          {
234            %>
235            <option value="<%=charset%>" 
236              <%=defaultCharset.equalsIgnoreCase(charset) ? "selected" : ""%> 
237              <%=i==numCommon ? "style=\"border-top: 1px solid #666666;\"" : "" %>><%=charset%>
238            <%
239            i++;
240          }
241          %>
242          </select>
243        </td>
244      </tr>
245      <tr>
246        <th>Data splitter regexp</th>
247        <td>
248          <table style="width: 100%;">
249          <tr>
250          <td><input type="text" class="text required" name="dataSplitter"></td>
251          <td style="width: 11em;">
252            <base:button title="Predefined&hellip;" style="width: 10em;"
253              id="btnSplitterRegexp" data-predefined="predefinedSplitters" />
254          </td>
255          </tr>
256          </table>
257        </td>
258        <th class="leftborder">Min data columns</th>
259        <td>
260          <input type="text" class="text" name="minDataColumns" 
261            id="minDataColumns" style="width: 15em;" maxlength="10">
262        </td>
263      </tr>
264      <tr >
265        <th>Ignore regexp</th>
266        <td>
267          <table style="width: 100%;">
268          <tr>
269          <td><input type="text" class="text" name="ignore"></td>
270          <td style="width: 11em;">
271            <base:button title="Predefined&hellip;" style="width: 10em;"
272              id="btnIgnoreRegexp" data-predefined="predefinedIgnore" />
273          </td>
274          </tr>
275          </table>
276        </td>
277        <th class="leftborder">Max data columns</th>
278        <td>
279          <input type="text" class="text" name="maxDataColumns" 
280            id="maxDataColumns" style="width: 15em;" maxlength="10">
281        </td>
282      </tr>
283      <tr >
284        <th>Data header regexp</th>
285        <td><input type="text" class="text" name="dataHeader"></td>
286        <th class="leftborder"><label for="trimQuotes">Remove quotes</label></th>
287        <td><input type="checkbox" name="trimQuotes" id="trimQuotes" value="1"></td>
288      </tr>
289      <tr class="dynamic">
290        <th>Data footer regexp</th>
291        <td><input type="text" class="text" name="dataFooter"></td>
292        <th class="subprompt leftborder"></th>
293        <td></td>
294      </tr>
295       
296      </table>
297    </div>
298     
299    <div class="absolutefull" style="top: 16em;"><iframe class="absolutefull"
300      name="parsed" id="idParsed" src="parse_file.jsp?ID=<%=ID%>" 
301        style="width: 100%; height: 100%;"></iframe></div>
302 
303    </div>
304    </form>
305 
306    <base:buttongroup subclass="dialogbuttons">
307      <base:button id="btnSave" title="Ok" />
308      <base:button id="close" title="Cancel" />
309    </base:buttongroup>
310  </base:body>
311  </base:page>
312  <%
313}
314finally
315{
316  if (dc != null) dc.close();
317}
318%>
319
Note: See TracBrowser for help on using the repository browser.