protected override void Execute(CodeActivityContext context)
{
//Range xlActiveRange = base.worksheet.UsedRange;
var ignoreEmptyRows = (IgnoreEmptyRows != null ? IgnoreEmptyRows.Get(context) : false);
var useHeaderRow = (UseHeaderRow != null? UseHeaderRow.Get(context) : false);
base.Execute(context);
var cells = Cells.Get(context);
Microsoft.Office.Interop.Excel.Range range = null;
if (string.IsNullOrEmpty(cells))
{
range = base.worksheet.UsedRange;
//Range last = base.worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
//Range range = base.worksheet.get_Range("A1", last);
//int lastUsedRow = range.Row;
//int lastUsedColumn = range.Column;
}
else
{
if (!cells.Contains(":"))
{
throw new ArgumentException("Cell should contain a range dedenition, meaning it should contain a colon :");
}
range = base.worksheet.get_Range(cells);
}
//object[,] valueArray = (object[,])range.Value;
object[,] valueArray = (object[, ])range.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
if (valueArray != null)
{
var o = ProcessObjects(useHeaderRow, ignoreEmptyRows, valueArray);
System.Data.DataTable dt = o as System.Data.DataTable;
dt.TableName = base.worksheet.Name;
if (string.IsNullOrEmpty(dt.TableName))
{
dt.TableName = "Unknown";
}
DataTable.Set(context, dt);
}
//dt.AsEnumerable();
//string json = Newtonsoft.Json.JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented);
////context.SetValue(Json, JObject.Parse(json));
//context.SetValue(Json, JArray.Parse(json));
if (ClearFormats.Get(context))
{
worksheet.Columns.ClearFormats();
worksheet.Rows.ClearFormats();
}
if (lastUsedColumn != null || lastUsedRow != null)
{
// Unhide All Cells and clear formats
// Detect Last used Row - Ignore cells that contains formulas that result in blank values
//int lastRowIgnoreFormulas = worksheet.Cells.Find(
// "*",
// System.Reflection.Missing.Value,
// XlFindLookIn.xlValues,
// XlLookAt.xlWhole,
// XlSearchOrder.xlByRows,
// XlSearchDirection.xlPrevious,
// false,
// System.Reflection.Missing.Value,
// System.Reflection.Missing.Value).Row;
// Detect Last Used Column - Ignore cells that contains formulas that result in blank values
//int lastColIgnoreFormulas = worksheet.Cells.Find(
// "*",
//System.Reflection.Missing.Value,
// System.Reflection.Missing.Value,
// System.Reflection.Missing.Value,
// XlSearchOrder.xlByColumns,
// XlSearchDirection.xlPrevious,
// false,
// System.Reflection.Missing.Value,
// System.Reflection.Missing.Value).Column;
// Detect Last used Row / Column - Including cells that contains formulas that result in blank values
//int lastColIncludeFormulas = worksheet.UsedRange.Columns.Count;
//int lastColIncludeFormulas = worksheet.UsedRange.Rows.Count;
//range = base.worksheet.UsedRange;
int _lastUsedColumn = worksheet.UsedRange.Columns.Count;
int _lastUsedRow = worksheet.UsedRange.Rows.Count;
if (lastUsedColumn != null)
{
context.SetValue(lastUsedColumn, ColumnIndexToColumnLetter(_lastUsedColumn));
}
if (lastUsedRow != null)
{
context.SetValue(lastUsedRow, _lastUsedRow);
}
}
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Protect(sheetPassword);
}
}
protected override void Execute(CodeActivityContext context)
{
//Range xlActiveRange = base.worksheet.UsedRange;
var ignoreEmptyRows = (IgnoreEmptyRows != null ? IgnoreEmptyRows.Get(context) : false);
var useHeaderRow = (UseHeaderRow != null ? UseHeaderRow.Get(context) : false);
var guessColumnType = (GuessColumnType != null ? GuessColumnType.Get(context) : false);
base.Execute(context);
var cells = Cells.Get(context);
Microsoft.Office.Interop.Excel.Range range = null;
if (string.IsNullOrEmpty(cells))
{
range = base.worksheet.UsedRange;
}
else
{
if (!cells.Contains(":"))
{
throw new ArgumentException("Cell should contain a range dedenition, meaning it should contain a colon :");
}
range = base.worksheet.get_Range(cells);
}
object[,] valueArray = (object[, ])range.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
if (valueArray != null)
{
var o = ProcessObjects(range, useHeaderRow, guessColumnType, ignoreEmptyRows, valueArray);
System.Data.DataTable dt = o as System.Data.DataTable;
dt.TableName = base.worksheet.Name;
if (string.IsNullOrEmpty(dt.TableName))
{
dt.TableName = "Unknown";
}
DataTable.Set(context, dt);
}
if (ClearFormats.Get(context))
{
worksheet.Columns.ClearFormats();
worksheet.Rows.ClearFormats();
}
if (lastUsedColumn != null || lastUsedRow != null)
{
//range = base.worksheet.UsedRange;
int _lastUsedColumn = worksheet.UsedRange.Columns.Count;
int _lastUsedRow = worksheet.UsedRange.Rows.Count;
if (lastUsedColumn != null)
{
context.SetValue(lastUsedColumn, ColumnIndexToColumnLetter(_lastUsedColumn));
}
if (lastUsedRow != null)
{
context.SetValue(lastUsedRow, _lastUsedRow);
}
}
var sheetPassword = SheetPassword.Get(context);
if (string.IsNullOrEmpty(sheetPassword))
{
sheetPassword = null;
}
if (!string.IsNullOrEmpty(sheetPassword) && worksheet != null)
{
worksheet.Protect(sheetPassword);
}
}