Ignore:
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:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/www/common/plugin/parse_file.jsp

    r7605 r7654  
    3939  import="net.sf.basedb.clients.web.Base"
    4040  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"
    4145  import="net.sf.basedb.util.fuzzy.StringMatcher"
    4246  import="net.sf.basedb.util.fuzzy.StringMatcher.FuzzyMatch"
     
    6569{
    6670  String path = request.getParameter("path");
    67   String charsetName = Values.getString(request.getParameter("charset"), Config.getCharset());
     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"));
    6874  PluginConfigurationRequest pcRequest = sc.getSessionSetting("plugin.configure.request");
    6975  Plugin plugin = pcRequest.getPlugin();
     
    100106    }
    101107   
    102     String dataSplitter = Values.getStringOrNull(request.getParameter("dataSplitter"));
     108    String dataSplitter = Values.getString(request.getParameter("dataSplitter"), excelMode ? "\\t" : null);
    103109    if (dataSplitter != null)
    104110    {
     
    163169      fileInputStream = wex.wrapInputStream(fileInputStream);
    164170    }
     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   
    165184    parser.setInputStream(fileInputStream, charsetName);
    166185    lastLine = parser.parseHeaders();
     
    207226    title="File data"
    208227    >
    209    
     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">
    210234    <%
    211235    if (file == null)
    212236    {
    213237      %>
    214       <div class="absolutefull bg-filled-50">
     238      <div class="absolutefull bg-filled-100">
    215239      <div class="messagecontainer error">
    216240        No file selected. Please select a file to test above.
     
    224248      {
    225249        %>
    226         <div class="bg-filled-50" style="padding: 5px;">
     250        <div class="bg-filled-100" style="padding: 5px;">
    227251        <div class="messagecontainer error" style="margin: 0;">
    228252        <b>Could not parse the file <code><%=HTML.encodeTags(path)%></code></b>
     
    384408    }
    385409    %>
     410    </div>
    386411    </t:tab>
    387412
Note: See TracChangeset for help on using the changeset viewer.