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