Changeset 7656


Ignore:
Timestamp:
Mar 19, 2019, 2:32:03 PM (3 years ago)
Author:
Nicklas Nordborg
Message:

References #2161: Import data from Excel files

Added support for selecting a specific sheet when importing to the AbstractItemImporter class which means that all batch item importers now should support this.

Location:
trunk
Files:
8 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/core/net/sf/basedb/util/excel/XlsxToCsvUtil.java

    r7655 r7656  
    379379      this.firstColumn = minCol;
    380380      this.lastColumn = maxCol;
     381    }
     382   
     383    /**
     384      Get the name of the sheet.
     385    */
     386    public String getName()
     387    {
     388      return sheet.getSheetName();
    381389    }
    382390   
     
    487495        out.append(rowSeparator);
    488496      }     
     497     
    489498      csvBytes = out.toString().getBytes(charset);
    490499    }
  • trunk/src/core/net/sf/basedb/util/parser/FlatFileParser.java

    r7655 r7656  
    2929import java.io.IOException;
    3030import java.nio.charset.Charset;
    31 
    3231import java.text.NumberFormat;
    3332import java.util.regex.Pattern;
     
    5453import net.sf.basedb.util.charset.CharsetUtil;
    5554import net.sf.basedb.util.excel.XlsxToCsvUtil;
     55import net.sf.basedb.util.excel.XlsxToCsvUtil.SheetInfo;
    5656import net.sf.basedb.util.formatter.DateFormatter;
    5757import net.sf.basedb.util.formatter.Formatter;
     
    192192 
    193193  /**
     194    Name of the Excel sheet to parse if the file is an Excel file.
     195  */
     196  private String excelSheet;
     197 
     198  /**
    194199    The regular expression for matching the beginning-of-file marker
    195200  */
     
    561566  {
    562567    this.keepSkippedLines = keep;
     568  }
     569 
     570  /**
     571    Set the name of Excel worksheet to parse if the given file is an Excel
     572    file, otherwise this is ignored.
     573    @since 3.15
     574  */
     575  public void setExcelSheet(String name)
     576  {
     577    this.excelSheet = name;
     578  }
     579 
     580  /**
     581    Get the name of the Excel sheet that should be or is parsed.
     582    @since 3.15
     583  */
     584  public String getExcelSheet()
     585  {
     586    return excelSheet;
    563587  }
    564588 
     
    606630        throw new RuntimeException(ex);
    607631      }
    608       if (xlsUtil.getSheetNames().contains(charsetOrSheetName))
     632      List<String> sheets = xlsUtil.getSheetNames();
     633      SheetInfo sheet = null;
     634      if (sheets.contains(charsetOrSheetName))
    609635      {
    610         // Get the named sheet
    611         in = xlsUtil.getSheetAsCsv(charsetOrSheetName).parseToCsv();
     636        sheet = xlsUtil.getSheetAsCsv(charsetOrSheetName);
     637      }
     638      else if (sheets.contains(excelSheet))
     639      {
     640        sheet = xlsUtil.getSheetAsCsv(excelSheet);
    612641      }
    613642      else
    614643      {
    615644        // Or try to convert to index with 0 as default
    616         in = xlsUtil.getSheetAsCsv(Values.getInt(charsetOrSheetName)).parseToCsv();
     645        sheet = xlsUtil.getSheetAsCsv(Values.getInt(charsetOrSheetName));
    617646      }
     647      excelSheet = sheet.getName();
     648      in = sheet.parseToCsv();
    618649    }
    619650    Charset cs = CharsetUtil.getCharset(charsetName);
  • trunk/src/plugins/core/net/sf/basedb/plugins/AbstractFlatFileImporter.java

    r7102 r7656  
    5656import net.sf.basedb.util.FileUtil;
    5757import net.sf.basedb.util.NumberFormatUtil;
     58import net.sf.basedb.util.Values;
    5859import net.sf.basedb.util.error.ClassMapErrorHandler;
    5960import net.sf.basedb.util.error.ErrorHandler;
    6061import net.sf.basedb.util.error.SimpleErrorHandler;
     62import net.sf.basedb.util.formatter.DateFormatter;
     63import net.sf.basedb.util.formatter.Formatter;
    6164import net.sf.basedb.util.jep.JepFunction;
    6265import net.sf.basedb.util.parser.CropStringMapper;
     
    7073import java.util.Arrays;
    7174import java.util.Collections;
     75import java.util.Date;
    7276import java.util.Iterator;
    7377import java.util.regex.Pattern;
     
    289293    fileType
    290294  );
    291    
     295 
     296  /**
     297    Parameter definition for specifying the name of the sheet to use if the selected file
     298    is an Excel file.
     299    @since 3.15
     300  */
     301  protected static final PluginParameter<String> excelSheetParameter = new PluginParameter<>(
     302    "excelSheet", "Excel sheet",
     303    "If the selected file is an Excel workbook, this parameter should hold the name of the " +
     304    "sheet to import from. If not specified, the first sheet is used.",
     305    optionalRegexpType);
     306 
    292307  /**
    293308    Section definition for grouping all file parser settings (ie. regular expressions)
     
    530545  {
    531546    FlatFileParser ffp = getInitializedFlatFileParser();
    532     ffp.setDefaultNumberFormat(getNumberFormat());
    533547    try
    534548    {
     
    565579    FlatFileParser ffp = getInitializedFlatFileParser();
    566580    FlatFileParser.Data dataline = null;
    567     ffp.setDefaultNumberFormat(getNumberFormat());
    568581    boolean useNullIfException = "null".equals(job != null ? job.getValue("numberFormatError") : "");
    569582    ffp.setUseNullIfException(useNullIfException);
     
    972985    ffp.setMinDataColumns(IntegerUtil.getInt((Integer)getJobOrConfigurationValue(minDataColumnsParameter.getName())));
    973986    ffp.setMaxDataColumns(IntegerUtil.getInt((Integer)getJobOrConfigurationValue(maxDataColumnsParameter.getName())));
     987   
     988    ffp.setDefaultNumberFormat(getNumberFormat());
     989    ffp.setDefaultDateFormat(getDateFormatter("yyyy-MM-dd"));
     990    ffp.setDefaultTimestampFormat(getTimestampFormatter("yyyy-MM-dd HH:mm:ss"));
     991
     992    ffp.setExcelSheet((String)getJobOrConfigurationValue(excelSheetParameter.getName()));
    974993    return ffp;
    975994  }
     
    10531072    }
    10541073    return ds == 0 ? null : NumberFormatUtil.getNumberFormat(ds, ';');
     1074  }
     1075 
     1076  protected Formatter<Date> getDateFormatter(String defaultFormat)
     1077  {
     1078    String dateFormat = Values.getString((String)getJobOrConfigurationValue(Parameters.DATE_FORMAT_PARAMETER), defaultFormat);
     1079    if (dateFormat == null) return null;   
     1080    DateFormatter dateFormatter = new DateFormatter(dateFormat);
     1081    dateFormatter.getDateFormat().setLenient(false);
     1082    return dateFormatter;
     1083  }
     1084
     1085  protected Formatter<Date> getTimestampFormatter(String defaultFormat)
     1086  {
     1087    String dateFormat = Values.getString((String)getJobOrConfigurationValue(Parameters.TIMESTAMP_FORMAT_PARAMETER), defaultFormat);
     1088    if (dateFormat == null) return null;   
     1089    DateFormatter dateFormatter = new DateFormatter(dateFormat);
     1090    dateFormatter.getDateFormat().setLenient(false);
     1091    return dateFormatter;
    10551092  }
    10561093 
  • trunk/src/plugins/core/net/sf/basedb/plugins/batchimport/AbstractItemImporter.java

    r7627 r7656  
    116116import net.sf.basedb.util.error.ErrorHandler;
    117117import net.sf.basedb.util.error.SimpleErrorHandler;
    118 import net.sf.basedb.util.formatter.DateFormatter;
     118import net.sf.basedb.util.formatter.Formatter;
    119119import net.sf.basedb.util.parser.ConfigureByExample;
    120120import net.sf.basedb.util.parser.DefaultItemFunction;
     
    403403          InputStream in = f.getDownloadStream(0);
    404404          ffp.setInputStream(in, getCharset(request));
    405           ffp.setDefaultNumberFormat(getNumberFormat());
    406405          FlatFileParser.LineType result = ffp.parseHeaders();
    407406          in.close();
     
    556555  private Project activeProject;
    557556  private FlatFileParser ffp;
    558   private DateFormatter dateFormatter;
     557  private Formatter<Date> dateFormatter;
    559558 
    560559  private IdMethod idMethod;
     
    597596  private int numExisted;
    598597 
    599   @Override
    600   /**
    601     The flat file parser is initialised with settings from either the job or configuration.
    602   */
    603   protected FlatFileParser getInitializedFlatFileParser()
    604     throws BaseException
    605   {
    606     ParameterValuesWrapper wrapper = new ParameterValuesWrapper(null, job, configuration, true);
    607     return getInitializedFlatFileParser(wrapper);
    608   }
    609  
    610598  /**
    611599    Don't use AbstractFlatFileImporter to handle errors.
     
    624612    this.dc = sc.newDbControl();
    625613    String dateFormat = Values.getString((String)job.getValue(Parameters.DATE_FORMAT_PARAMETER), "yyyy-MM-dd");
    626     this.dateFormatter = new DateFormatter(dateFormat);
    627     this.dateFormatter.getDateFormat().setLenient(false);
     614    this.dateFormatter = ffp.getDefaultDateFormat();
    628615    this.idMethod = getIdMethod(dc, (String)job.getValue("idMethod"));
    629616    this.idMapping = (String)job.getValue(idMethod.getColumnMappingParameterName());
     
    24602447    ffp.setMinDataColumns(IntegerUtil.getInt((Integer)parameters.getValue(minDataColumnsParameter.getName())));
    24612448    ffp.setMaxDataColumns(IntegerUtil.getInt((Integer)parameters.getValue(maxDataColumnsParameter.getName())));
     2449   
     2450    ffp.setExcelSheet(parameters.getValue(excelSheetParameter.getName()));
     2451   
    24622452    return ffp;
    24632453  }
     
    24992489          // The file to import from
    25002490          parameters.add(fileParameter);
     2491          parameters.add(excelSheetParameter);
    25012492        }
    25022493        else
  • trunk/www/common/plugin/configure.js

    r7604 r7656  
    533533  }
    534534
     535  configure.getParameterDef = function(name)
     536  {
     537    return parametersByName[name];
     538  }
    535539 
    536540  configure.getParameterValue = function(name)
  • trunk/www/common/plugin/parse_file.jsp

    r7655 r7656  
    180180      parser.setDefaultNumberFormat(NumberFormatUtil.getNumberFormat(ds, (char)0));
    181181    }
    182    
    183     parser.setInputStream(fileInputStream, excelMode ? sheet : charsetName);
     182    parser.setExcelSheet(sheet);
     183    parser.setInputStream(fileInputStream, charsetName);
    184184    lastLine = parser.parseHeaders();
    185185    dataIsFound = lastLine == FlatFileParser.LineType.DATA || lastLine == FlatFileParser.LineType.DATA_HEADER;
  • trunk/www/common/plugin/test_with_file.js

    r7655 r7656  
    2828  var test = {};
    2929
     30  var hasExcelSheetParameter = false;
     31  var currentExcelSheet;
     32 
    3033  test.PRE_QUOTE = '(?!")';
    3134  test.POST_QUOTE = '(?=")';
     
    135138      for (var i = 0; i < response.sheets.length; i++)
    136139      {
    137         frm.sheet[frm.sheet.length] = new Option(response.sheets[i]);
     140        var sheet = response.sheets[i];
     141        frm.sheet[frm.sheet.length] = new Option((i+1) + ': '+sheet, sheet, false, currentExcelSheet == sheet);
     142      }
     143      if (!hasExcelSheetParameter && frm.sheet.length > 1)
     144      {
     145        Doc.show('noSheetSupportWarning', 'inline');
     146      }
     147      else
     148      {
     149        Doc.hide('noSheetSupportWarning');
    138150      }
    139151    }
     
    198210    frm.dateFormat.value = configWin.Configure.getParameterValue('dateFormat');
    199211    frm.timestampFormat.value = configWin.Configure.getParameterValue('timestampFormat');
    200      
     212    hasExcelSheetParameter = configWin.Configure.getParameterDef('excelSheet') != null;
     213    currentExcelSheet = configWin.Configure.getParameterValue('excelSheet');
     214   
    201215    // Copy the list since ith is not certain that it is the same as Config.allCharsets()
    202216    var allCharsets = configWin.Doc.element('parameter-charset');
     
    244258    configWin.Configure.setParameterValue('trimQuotes', frm.trimQuotes.checked ? 'true' : 'false');
    245259    configWin.Configure.setParameterValue('charset', frm.charset.value);
     260    configWin.Configure.setParameterValue('excelSheet', frm.sheet.value);
    246261   
    247262    var allParameters = configWin.Configure.getAllParameters();
  • trunk/www/common/plugin/test_with_file.jsp

    r7655 r7656  
    206206            value="<%=FlatFileParser.DEFAULT_MAX_UNKNOWN_LINES%>">
    207207        </td>
    208         <th class="leftborder">Excel Sheet</th>
     208        <th class="leftborder">Excel sheet</th>
    209209        <td>
    210210          <select id="sheet" name="sheet" style="min-width: 20em;" disabled>
    211211          </select>
     212          <base:icon image="warning.png" id="noSheetSupportWarning"
     213            tooltip="This plug-in only support import from the first sheet"
     214            style="display: none;"
     215          />
    212216        </td>
    213217      </tr>
Note: See TracChangeset for help on using the changeset viewer.