source: trunk/www/common/plugin/test_with_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: 9.4 KB
Line 
1<%-- $Id: test_with_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.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="decimalSeparator" value="">
183    <input type="hidden" name="dateFormat" value="">
184    <input type="hidden" name="timestampFormat" value="">
185
186    <div class="content">
187      <div class="absolutefull" style="height: 15em;">
188      <table class="fullform input100 bottomborder">
189      <tr>
190        <th>File (CSV or Excel)</th>
191        <td colspan="3">
192          <table style="width: 100%;">
193            <tr>
194            <td><input type="text" class="text required" name="path" id="path" value=""></td>
195            <td style="width: 11em;"><base:button id="btnBrowse" title="Browse&hellip;" style="width: 10em;" /></td>
196            <td style="width: 11em;"><base:button id="btnParse" title="Parse the file" style="width: 10em;" image="gonext.png"/></td>
197            </tr>
198          </table>
199        </td>
200      </tr>
201      <tr>
202        <th>Lines to parse</th>
203        <td>
204          <input type="text" class="text" name="maxLines" 
205            id="maxLines" style="width: 15em;" maxlength="10"
206            value="<%=FlatFileParser.DEFAULT_MAX_UNKNOWN_LINES%>">
207        </td>
208        <th class="leftborder">Excel Sheet</th>
209        <td>
210          <select id="sheet" name="sheet" style="min-width: 20em;" disabled>
211          </select>
212        </td>
213      </tr>
214      <tr >
215        <th>Header regexp</th>
216        <td>
217          <table style="width: 100%;">
218          <tr>
219          <td><input type="text" class="text" name="header"></td>
220          <td style="width: 11em;">
221            <base:button title="Predefined&hellip;" style="width: 10em;"
222              id="btnHeaderRegexp" data-predefined="predefinedHeaders" />
223          </td>
224          </tr>
225          </table>
226        </td>
227        <th class="leftborder">Character set</th>
228        <td>
229          <select name="charset" style="min-width: 20em;">
230          <%
231          String defaultCharset = Config.getCharset();
232          int numCommon = Config.getCommonCharsets().size();
233          int i = 0;
234          for (String charset : Config.getAllCharsets())
235          {
236            %>
237            <option value="<%=charset%>" 
238              <%=defaultCharset.equalsIgnoreCase(charset) ? "selected" : ""%> 
239              <%=i==numCommon ? "style=\"border-top: 1px solid #666666;\"" : "" %>><%=charset%>
240            <%
241            i++;
242          }
243          %>
244          </select>
245        </td>
246      </tr>
247      <tr>
248        <th>Data splitter regexp</th>
249        <td>
250          <table style="width: 100%;">
251          <tr>
252          <td><input type="text" class="text required" name="dataSplitter"></td>
253          <td style="width: 11em;">
254            <base:button title="Predefined&hellip;" style="width: 10em;"
255              id="btnSplitterRegexp" data-predefined="predefinedSplitters" />
256          </td>
257          </tr>
258          </table>
259        </td>
260        <th class="leftborder">Min data columns</th>
261        <td>
262          <input type="text" class="text" name="minDataColumns" 
263            id="minDataColumns" style="width: 15em;" maxlength="10">
264        </td>
265      </tr>
266      <tr >
267        <th>Ignore regexp</th>
268        <td>
269          <table style="width: 100%;">
270          <tr>
271          <td><input type="text" class="text" name="ignore"></td>
272          <td style="width: 11em;">
273            <base:button title="Predefined&hellip;" style="width: 10em;"
274              id="btnIgnoreRegexp" data-predefined="predefinedIgnore" />
275          </td>
276          </tr>
277          </table>
278        </td>
279        <th class="leftborder">Max data columns</th>
280        <td>
281          <input type="text" class="text" name="maxDataColumns" 
282            id="maxDataColumns" style="width: 15em;" maxlength="10">
283        </td>
284      </tr>
285      <tr >
286        <th>Data header regexp</th>
287        <td><input type="text" class="text" name="dataHeader"></td>
288        <th class="leftborder"><label for="trimQuotes">Remove quotes</label></th>
289        <td><input type="checkbox" name="trimQuotes" id="trimQuotes" value="1"></td>
290      </tr>
291      <tr class="dynamic">
292        <th>Data footer regexp</th>
293        <td><input type="text" class="text" name="dataFooter"></td>
294        <th class="subprompt leftborder"></th>
295        <td></td>
296      </tr>
297       
298      </table>
299    </div>
300     
301    <div class="absolutefull" style="top: 16em;"><iframe class="absolutefull"
302      name="parsed" id="idParsed" src="parse_file.jsp?ID=<%=ID%>" 
303        style="width: 100%; height: 100%;"></iframe></div>
304 
305    </div>
306    </form>
307 
308    <base:buttongroup subclass="dialogbuttons">
309      <base:button id="btnSave" title="Ok" />
310      <base:button id="close" title="Cancel" />
311    </base:buttongroup>
312  </base:body>
313  </base:page>
314  <%
315}
316finally
317{
318  if (dc != null) dc.close();
319}
320%>
321
Note: See TracBrowser for help on using the repository browser.