Changeset 7655

Timestamp:

Mar 19, 2019, 11:09:07 AM (4 years ago)

Author:

Nicklas Nordborg

Message:

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.

Location:

trunk

Files:

• src/core/net/sf/basedb/util/excel/XlsxToCsvUtil.java (modified) (6 diffs)
• src/core/net/sf/basedb/util/formatter/NumberFormatFormatter.java (added)
• src/core/net/sf/basedb/util/parser/FlatFileParser.java (modified) (6 diffs)
• www/common/plugin/parse_file.jsp (modified) (1 diff)
• www/common/plugin/test_with_file.js (modified) (2 diffs)
• www/common/plugin/test_with_file.jsp (modified) (1 diff)

trunk/src/core/net/sf/basedb/util/excel/XlsxToCsvUtil.java

@@ -10 +10 @@
 import java.util.List;
 
+import org.apache.poi.poifs.filesystem.FileMagic;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellType;
@@ -57 +58 @@
   }
   
+  /**
+    Check if the given stream is an Excel file by looking at the first few bytes.
+    The stream must support seek(). Call {@link #prepareForCheck(InputStream)}
+    before calling this method if it is not sure.
+  */
+  public static boolean isAnExcelFile(InputStream in)
+  {
+    try
+    {
+      return FileMagic.valueOf(in) == FileMagic.OOXML;
+    }
+    catch (Exception ex)
+    {}
+    return false;
+  }
+  
+  /**
+    Prepare the input stream for checking with 
+    {@link #isAnExcelFile(InputStream)}. If the given stream
+    supports seek() it is returned as it is, otherwise it is
+    wrapped by a buffered stream.
+  */
+  public static InputStream prepareForCheck(InputStream in)
+  {
+    return FileMagic.prepareToCheckMagic(in);
+  }
+
   private Charset charset;
   private String columnSeparator;
@@ -64 +92 @@
   private EncoderDecoder encoder;
   private Formatter<Date> dateFormat;
+  private Formatter<Date> timestampFormat;
   private Formatter<Number> numberFormat;
   
@@ -192 +221 @@
     this.dateFormat = dateFormat;
   }
+  
+  /**
+    Get the date formatter that is used for formatting cells with timestamp-like values.
+    If null, the default implementation is used (which may not be predicatable).
+  */
+  public Formatter<Date> getTimestampFormat()
+  {
+    return timestampFormat;
+  }
+  
+  /**
+    Set a date formatter to use for formatting cells with timestamp-like values.
+    If null, the default implementation is used (which may not be predicatable).
+  */
+  public void setTimestampFormat(Formatter<Date> timestampFormat)
+  {
+    this.timestampFormat = timestampFormat;
+  }
+
   
   /**
@@ -405 +453 @@
               if (isDate)
               {
-                if (dateFormat != null) 
+                String formatString = cell.getCellStyle().getDataFormatString();
+                boolean hasTimePart = hasTimePart(formatString);
+                boolean hasDatePart = hasDatePart(formatString);          
+                Date d = cell.getDateCellValue();
+                if (hasDatePart && hasTimePart)
                 {
-                  value = dateFormat.format(cell.getDateCellValue());
+                  if (timestampFormat != null) value = timestampFormat.format(d);
+                }
+                else if (hasDatePart)
+                {
+                  if (dateFormat != null) value = dateFormat.format(d);
+                }
+                else
+                {
+                  // Only time part -- let it be handled by the DataFormat
                 }
               }
@@ -431 +491 @@
   }
   
-  
+  private boolean hasTimePart(String formatString)
+  {
+    if (formatString == null) return false;
+    return formatString.contains("S") || formatString.contains("H");
+  }
+  
+  private boolean hasDatePart(String formatString)
+  {
+    if (formatString == null) return false;
+    return formatString.contains("Y") || formatString.contains("D");
+  }
+
 }

trunk/src/core/net/sf/basedb/util/parser/FlatFileParser.java

@@ -34 +34 @@
 import java.util.regex.Matcher;
 import java.util.regex.PatternSyntaxException;
+
+
 import java.util.Arrays;
 import java.util.LinkedList;
@@ -42 +44 @@
 import java.util.Set;
 import java.util.Collections;
+import java.util.Date;
 
 import net.sf.basedb.core.BaseException;
@@ -47 +50 @@
 import net.sf.basedb.core.signal.ThreadSignalHandler;
 import net.sf.basedb.util.InputStreamTracker;
+import net.sf.basedb.util.NumberFormatUtil;
+import net.sf.basedb.util.Values;
 import net.sf.basedb.util.charset.CharsetUtil;
+import net.sf.basedb.util.excel.XlsxToCsvUtil;
+import net.sf.basedb.util.formatter.DateFormatter;
+import net.sf.basedb.util.formatter.Formatter;
+import net.sf.basedb.util.formatter.NumberFormatFormatter;
 import net.sf.basedb.util.jep.JepFunction;
 
@@ -270 +279 @@
   
   /**
+    The default date formatter to use when creating mappers.
+  */
+  private Formatter<Date> dateFormat = null;
+  
+  /**
+    The default timestamp format to use when creating mappers.
+  */
+  private Formatter<Date> timestampFormat = null;
+
+  /**
     The value that was captured by the bofMarker pattern.
   */
@@ -545 +564 @@
   
   /**
-    Set the input stream that will be parsed.
+    Set the input stream that will be parsed. The stream can be either a
+    text CSV-like stream or an Excel workbook (xlsx). 
+    
+    If the stream is an Excel workbook the following apply:
+    
+    * If no date format has been specified, yyyy-MM-dd is used
+    * If no timestamp format has been specified, yyyy-MM-dd hh:mm:ss is used
+    * If no number format has been specified, 'dot' is used
+    * The data splitter regular expression is changed to \\t
+    
     @param in The <code>InputStream</code>
-    @param charsetName The name of the character set to use when parsing
-      the file, or null to use the default charset specified by
+    @param charsetOrSheetName If CSV, the name of the character set to 
+      use when parsing the file, or null to use the default charset specified by
       {@link Config#getCharset()}
+      If Excel, the name or index of the worksheet in the workbook, the default is
+      to parse the first sheet (index=0)
     @since 2.1.1
   */
-  public void setInputStream(InputStream in, String charsetName)
-  {
+  public void setInputStream(InputStream in, String charsetOrSheetName)
+  {
+    in = XlsxToCsvUtil.prepareForCheck(in);
+    String charsetName = charsetOrSheetName;
+    if (XlsxToCsvUtil.isAnExcelFile(in))
+    {
+      XlsxToCsvUtil xlsUtil = new XlsxToCsvUtil();
+      if (dateFormat == null) dateFormat = new DateFormatter("yyyy-MM-dd");
+      if (timestampFormat == null) timestampFormat = new DateFormatter("yyyy-MM-dd hh:mm:ss");
+      if (numberFormat == null) numberFormat = NumberFormatUtil.getNumberFormat('.', (char)0);
+      xlsUtil.setDateFormat(dateFormat);
+      xlsUtil.setTimestampFormat(timestampFormat);
+      xlsUtil.setNumberFormat(new NumberFormatFormatter(numberFormat));
+      xlsUtil.setEvaluateFormulas(true);
+      setDataSplitterRegexp(Pattern.compile("\\t"));
+      charsetName = "UTF-8";
+      try
+      {
+        xlsUtil.readWorkbook(in);
+      }
+      catch (IOException ex)
+      {
+        throw new RuntimeException(ex);
+      }
+      if (xlsUtil.getSheetNames().contains(charsetOrSheetName))
+      {
+        // Get the named sheet
+        in = xlsUtil.getSheetAsCsv(charsetOrSheetName).parseToCsv();
+      }
+      else
+      {
+        // Or try to convert to index with 0 as default
+        in = xlsUtil.getSheetAsCsv(Values.getInt(charsetOrSheetName)).parseToCsv();
+      }
+    }
     Charset cs = CharsetUtil.getCharset(charsetName);
     this.tracker = new InputStreamTracker(in);
@@ -914 +977 @@
   {
     return numberFormat;
+  }
+  
+  /**
+    Set the default date format to use when creating mappers.
+    If null, xxx is used.
+    @since 3.15
+  */
+  public void setDefaultDateFormat(Formatter<Date> dateFormat)
+  {
+    this.dateFormat = dateFormat;
+  }
+  
+  /**
+    Get the default date format.
+    @since 3.15
+  */
+  public Formatter<Date> getDefaultDateFormat()
+  {
+    return dateFormat;
+  }
+  
+  /**
+    Set the default timestamp format to use when creating mappers.
+    If null, xxx is used.
+    @since 3.15
+  */
+  public void setDefaultTimestampFormat(Formatter<Date> timestampFormat)
+  {
+    this.timestampFormat = timestampFormat;
+  }
+  
+  /**
+    Get the default timestamp format.
+    @since 3.15
+  */
+  public Formatter<Date> getDefaultTimestampFormat()
+  {
+    return timestampFormat;
   }
   

trunk/www/common/plugin/parse_file.jsp

@@ -169 +169 @@
       fileInputStream = wex.wrapInputStream(fileInputStream);
     }
-    
-    if (excelMode)
-    {
-      // TODO - this should be inside the FlatFileParser
-      String dateFormat = Values.getString(request.getParameter("dateFormat"), "yyyy-MM-dd");
-      XlsxToCsvUtil util = new XlsxToCsvUtil();
-      util.setDateFormat(new DateFormatter(dateFormat));
-      util.setEvaluateFormulas(true);
-      util.readWorkbook(fileInputStream);
-      SheetInfo sheetInfo = util.getSheetAsCsv(sheet);
-      fileInputStream = sheetInfo.parseToCsv();
-    }
-    
-    parser.setInputStream(fileInputStream, charsetName);
+
+    String dateFormat = Values.getStringOrNull(request.getParameter("dateFormat"));
+    if (dateFormat != null) parser.setDefaultDateFormat(new DateFormatter(dateFormat));
+    String timestampFormat = Values.getStringOrNull(request.getParameter("timestampFormat"));
+    if (timestampFormat != null) parser.setDefaultTimestampFormat(new DateFormatter(timestampFormat));
+    String decimalSeparator = Values.getStringOrNull(request.getParameter("decimalSeparator"));
+    if (decimalSeparator != null)
+    {
+      char ds = "dot".equals(decimalSeparator) ? '.' : ',';
+      parser.setDefaultNumberFormat(NumberFormatUtil.getNumberFormat(ds, (char)0));
+    }
+    
+    parser.setInputStream(fileInputStream, excelMode ? sheet : charsetName);
     lastLine = parser.parseHeaders();
     dataIsFound = lastLine == FlatFileParser.LineType.DATA || lastLine == FlatFileParser.LineType.DATA_HEADER;

trunk/www/common/plugin/test_with_file.js

@@ -175 +175 @@
     
     var parseWin = window.frames['parsed'];
-    parseWin.ParseFile.pleaseWaitWhileParsing();
+    if (parseWin && parseWin.ParseFile)
+    {
+      parseWin.ParseFile.pleaseWaitWhileParsing();
+    }
     frm.submit();
   }
@@ -192 +195 @@
     frm.maxDataColumns.value = configWin.Configure.getParameterValue('maxDataColumns');
     frm.trimQuotes.checked = configWin.Configure.getParameterValue('trimQuotes') != 'false';
+    frm.decimalSeparator.value = configWin.Configure.getParameterValue('decimalSeparator');
     frm.dateFormat.value = configWin.Configure.getParameterValue('dateFormat');
+    frm.timestampFormat.value = configWin.Configure.getParameterValue('timestampFormat');
       
     // Copy the list since ith is not certain that it is the same as Config.allCharsets() 

trunk/www/common/plugin/test_with_file.jsp

@@ -180 +180 @@
     <input type="hidden" name="file_id" value="">
     <input type="hidden" name="filemode" value="csv">
+    <input type="hidden" name="decimalSeparator" value="">
     <input type="hidden" name="dateFormat" value="">
+    <input type="hidden" name="timestampFormat" value="">
 
     <div class="content">