private ImportXlsStmtRunner(INotebook notebook, ScriptEnv env, ScriptRunner runner, Ast.ImportXlsStmt stmt) { _notebook = notebook; _env = env; _runner = runner; _stmt = stmt; _filePath = _runner.EvaluateExpr <string>(_stmt.FilenameExpr, _env); if (!File.Exists(_filePath)) { throw new Exception($"The specified XLS/XLSX file was not found: \"{_filePath}\""); } if (_stmt.WhichSheetExpr != null) { _whichSheet = _runner.EvaluateExpr(_stmt.WhichSheetExpr, _env); } int?index; foreach (var option in _stmt.OptionsList.GetOptionKeys()) { switch (option) { case "FIRST_ROW": _firstRowIndex = _stmt.OptionsList.GetOptionInt(option, _runner, _env, 1, minValue: 1) - 1; break; case "LAST_ROW": var lastRowNum = _stmt.OptionsList.GetOptionInt(option, _runner, _env, 0, minValue: 0); if (lastRowNum == 0) { _lastRowIndex = null; } else { _lastRowIndex = lastRowNum - 1; } break; case "FIRST_COLUMN": index = XlsUtil.ColumnRefToIndex(_stmt.OptionsList.GetOption <object>(option, _runner, _env, null)); if (index.HasValue) { _firstColumnIndex = index.Value; } else { throw new Exception($"The {option} option must be a valid column number or string."); } break; case "LAST_COLUMN": var lastColumnValue = _stmt.OptionsList.GetOption <object>(option, _runner, _env, null); if (lastColumnValue.Equals(0)) { _lastColumnIndex = null; break; } index = XlsUtil.ColumnRefToIndex(lastColumnValue); if (index.HasValue) { _lastColumnIndex = index.Value; } else { throw new Exception($"The {option} option must be a valid column number or string."); } break; case "HEADER_ROW": _headerRow = _stmt.OptionsList.GetOptionBool(option, _runner, _env, true); break; case "TRUNCATE_EXISTING_TABLE": _truncateExistingTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false); break; case "TEMPORARY_TABLE": _temporaryTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false); break; case "IF_CONVERSION_FAILS": _ifConversionFails = (IfConversionFails)_stmt.OptionsList.GetOptionLong( option, _runner, _env, 1, minValue: 1, maxValue: 3); break; default: throw new Exception($"\"{option}\" is not a recognized option name."); } } }
// must be run from the SQLite thread public static void Run(INotebook notebook, ScriptEnv env, ScriptRunner runner, Ast.ImportXlsStmt stmt) { var importer = new ImportXlsStmtRunner(notebook, env, runner, stmt); SqlUtil.WithTransaction(notebook, importer.Import); }
public static Encoding GetOptionEncoding(this Ast.OptionsList optionsList, string name, ScriptRunner runner, ScriptEnv env) { var encodingNum = optionsList.GetOptionInt(name, runner, env, 0); if (encodingNum == 0) { return(null); } else if (encodingNum < 0 || encodingNum > 65535) { throw new Exception($"{name} must be between 0 and 65535."); } else { Encoding encoding = null; try { encoding = Encoding.GetEncoding(encodingNum); } catch (ArgumentOutOfRangeException) { // invalid codepage } catch (ArgumentException) { // invalid codepage } catch (NotSupportedException) { // invalid codepage } if (encoding == null) { throw new Exception($"The code page {encodingNum} is invalid or is not supported on this system."); } else { return(encoding); } } }
public static bool GetOptionBool(this Ast.OptionsList optionsList, string name, ScriptRunner runner, ScriptEnv env, bool defaultValue) { long num = optionsList.GetOption <long>(name, runner, env, defaultValue ? 1 : 0); if (num != 0 && num != 1) { throw new Exception($"{name} must be 0 or 1."); } else { return(num == 1); } }
public static int GetOptionInt(this Ast.OptionsList optionsList, string name, ScriptRunner runner, ScriptEnv env, int defaultValue, int?minValue = null, int?maxValue = null) { var longValue = GetOptionLong(optionsList, name, runner, env, defaultValue, minValue, maxValue); if (longValue < int.MinValue || longValue > int.MaxValue) { throw new Exception($"{name} must be a 32-bit integer."); } else { return((int)longValue); } }
public static long GetOptionLong(this Ast.OptionsList optionsList, string name, ScriptRunner runner, ScriptEnv env, long defaultValue, long?minValue = null, long?maxValue = null) { var num = optionsList.GetOption <long>(name, runner, env, defaultValue); bool outOfRange = (minValue.HasValue && num < minValue.Value) || (maxValue.HasValue && num > maxValue.Value); if (outOfRange) { if (minValue.HasValue && !maxValue.HasValue) { throw new Exception($"{name} must be an integer ≥ {minValue.Value}."); } else if (minValue.HasValue && maxValue.HasValue) { throw new Exception($"{name} must be an integer between {minValue.Value} and {maxValue.Value}."); } else if (!minValue.HasValue && maxValue.HasValue) { throw new Exception($"{name} must be an integer ≤ {maxValue.Value}."); } } return(num); }