protected override void Execute(CodeActivityContext context)
{
var useHeaderRow = false;
if (UseHeaderRow != null)
{
useHeaderRow = UseHeaderRow.Get(context);
}
string[] delimeters = null;
if (Delimeter != null)
{
var d = Delimeter.Get(context);
if (!string.IsNullOrEmpty(d))
{
delimeters = new string[] { d };
}
}
if (delimeters == null || delimeters.Length == 0)
{
delimeters = new string[] { ",", ";" }
}
;
System.Data.DataTable result = null;
var filename = Filename.Get(context);
filename = Environment.ExpandEnvironmentVariables(filename);
result = GetDataTabletFromCSVFile(filename, useHeaderRow, delimeters);
if (DataTable != null)
{
context.SetValue(DataTable, result);
}
}
}
protected override void Execute(CodeActivityContext context)
{
var useHeaderRow = false;
if (UseHeaderRow != null)
{
useHeaderRow = UseHeaderRow.Get(context);
}
System.Data.DataSet result = null;
var filename = Filename.Get(context);
filename = Environment.ExpandEnvironmentVariables(filename);
using (var stream = System.IO.File.Open(filename, System.IO.FileMode.Open, System.IO.FileAccess.Read))
{
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx)
var conf = new ExcelDataReader.ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataReader.ExcelDataTableConfiguration
{
UseHeaderRow = useHeaderRow
}
};
using (var reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream))
{
// Choose one of either 1 or 2:
// 1. Use the reader methods
//do
//{
// while (reader.Read())
// {
// // reader.GetDouble(0);
// }
//} while (reader.NextResult());
// 2. Use the AsDataSet extension method
result = reader.AsDataSet(conf);
}
}
if (DataSet != null)
{
context.SetValue(DataSet, result);
}
}
protected override void Execute(CodeActivityContext context)
{
var useHeaderRow = (UseHeaderRow != null ? UseHeaderRow.Get(context) : false);
base.Execute(context);
var dt = DataTable.Get(context);
var cells = Cells.Get(context);
Microsoft.Office.Interop.Excel.Range xlRange = null;
if (string.IsNullOrEmpty(cells))
{
xlRange = base.worksheet.UsedRange;
}
else
{
xlRange = base.worksheet.get_Range(cells);
}
var idx = 1;
//Header
if (useHeaderRow)
{
for (var i = 0; i < dt.Columns.Count; i++)
{
xlRange.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
idx = 2;
}
//Datas
for (var i = 0; i < dt.Rows.Count; i++)
{
for (var j = 0; j < dt.Columns.Count; j++)
{
xlRange.Cells[i + idx, j + 1] = dt.Rows[i][j];
}
}
}
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)
{
var useHeaderRow = (UseHeaderRow != null ? UseHeaderRow.Get(context) : false);
base.Execute(context);
var dt = DataTable.Get(context);
if (dt == null)
{
throw new ArgumentException("DataTable is null", "DataTable");
}
var cells = Cells.Get(context);
Microsoft.Office.Interop.Excel.Range xlRange = null;
if (string.IsNullOrEmpty(cells))
{
xlRange = base.worksheet.UsedRange;
// Find the last real row
if (xlRange.Count > 1)
{
try
{
var nInLastRow = base.worksheet.Cells.Find("*", System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, XlSearchOrder.xlByRows, XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
//// Find the last real column
var nInLastCol = base.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;
// var o = base.worksheet.Cells[nInLastRow, nInLastCol];
var o = base.worksheet.Cells[nInLastRow + 1, 1];
xlRange = o as Range;
// Range last = base.worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
// xlRange = base.worksheet.get_Range("A1", last);
}
catch (Exception)
{
}
}
}
else
{
xlRange = base.worksheet.get_Range(cells);
}
// var idx = 0;
//Header
if (useHeaderRow)
{
for (var i = 0; i < dt.Columns.Count; i++)
{
xlRange.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
// idx = 1;
var o = base.worksheet.Cells[xlRange.Row + 1, xlRange.Column];
xlRange = o as Range;
}
//Datas
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
for (int r = 0; r < dt.Rows.Count; r++)
{
System.Data.DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
// Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[topRow, 1];
Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[xlRange.Row + dt.Rows.Count - 1, (xlRange.Column + dt.Columns.Count) - 1];
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(xlRange, c2);
//range.Value = arr;
range.set_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault, arr);
//for (var i = 0; i < dt.Rows.Count; i++)
//{
// for (var j = 0; j < dt.Columns.Count; j++)
// {
// xlRange.Cells[i + idx, j + 1] = dt.Rows[i][j];
// }
//}
}
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);
}
}
