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);
}