public void Execute(IActivityRequest request, IActivityResponse response)
{
OleDbConnection con = null;
UpdateFieldValues(request);
Excel.ExcelFileType FileType = Excel.GetFileType(FieldValue_ExcelFilePath);
try
{
con = ExcelObj.OpenExcelFile(FieldValue_ExcelFilePath, FieldValue_SheetName, FileType, Convert.ToBoolean(FieldValue_HasHeaderRow), Convert.ToBoolean(FieldValue_ForceMixedData), FieldValue_TypeGuessRows, FieldValue_CSVDelimiter);
ExcelObj.InsertRowIntoExcel(FieldValue_ExcelFilePath, FieldValue_SheetName, FileType, ref con,
FieldValue_ColumnName1, FieldValue_ColumnValue1, FieldValue_ColumnName2, FieldValue_ColumnValue2,
FieldValue_ColumnName3, FieldValue_ColumnValue3, FieldValue_ColumnName4, FieldValue_ColumnValue4,
FieldValue_ColumnName5, FieldValue_ColumnValue5, FieldValue_ColumnName6, FieldValue_ColumnValue6,
FieldValue_ColumnName7, FieldValue_ColumnValue7, FieldValue_ColumnName8, FieldValue_ColumnValue8,
FieldValue_ColumnName9, FieldValue_ColumnValue9, FieldValue_ColumnName10, FieldValue_ColumnValue10,
FieldValue_ColumnName11, FieldValue_ColumnValue11, FieldValue_ColumnName12, FieldValue_ColumnValue12,
FieldValue_ColumnName13, FieldValue_ColumnValue13, FieldValue_ColumnName14, FieldValue_ColumnValue14,
FieldValue_ColumnName15, FieldValue_ColumnValue15, FieldValue_ColumnName16, FieldValue_ColumnValue16,
FieldValue_ColumnName17, FieldValue_ColumnValue17, FieldValue_ColumnName18, FieldValue_ColumnValue18,
FieldValue_ColumnName19, FieldValue_ColumnValue19, FieldValue_ColumnName20, FieldValue_ColumnValue20);
}
catch (System.Exception ex)
{
ExcelObj.CloseExcelFile(ref con); //Ensure the excel file is closed before throwing the error.
throw (ex);
}
ExcelObj.CloseExcelFile(ref con);
response.Publish("Excel File Path", FieldValue_ExcelFilePath);
response.Publish("Sheet Name", FieldValue_SheetName);
response.Publish("Has Header Row", FieldValue_HasHeaderRow);
response.Publish("Force mixed data to be converted to text", FieldValue_ForceMixedData);
response.Publish("TypeGuessRows", FieldValue_TypeGuessRows);
response.Publish("CSV Delimiter", FieldValue_CSVDelimiter);
}
public void Execute(IActivityRequest request, IActivityResponse response)
{
string FilePath = request.Inputs["Excel File Path"].AsString();
string SheetName = request.Inputs["Sheet Name"].AsString();
if (SheetName == "")
{
SheetName = FieldValue_SheetName;
}
bool HasHeaderRow = request.Inputs["Has Header Row"].AsBoolean();
bool ForceMixedDataAsText;
string CSVDelimiter = request.Inputs["CSV Delimiter"].AsString();
if (CSVDelimiter == "")
{
CSVDelimiter = FieldValue_CSVDelimiter;
}
if (request.Inputs["Force mixed data to be converted to text"].ToString() != "")
{
ForceMixedDataAsText = request.Inputs["Force mixed data to be converted to text"].AsBoolean();
}
else
{
ForceMixedDataAsText = true;
}
int TypeGuessRows;
if (request.Inputs["TypeGuessRows"].ToString() != "")
{
TypeGuessRows = request.Inputs["TypeGuessRows"].AsInt16();
}
else
{
TypeGuessRows = 8;
}
string SemiColonReplacement = request.Inputs["; Replacement"].AsString();
if (SemiColonReplacement == "")
{
SemiColonReplacement = "_";
}
//Read Filter Values
string SelectFilter = "Select *";
string WhereFilter = "";
foreach (IFilterCriteria filter in request.Filters)
{
switch (filter.Name)
{
case "Select filter":
switch (filter.Relation)
{
case Relation.EqualTo:
SelectFilter = filter.Value.AsString();
break;
}
break;
case "Where filter":
switch (filter.Relation)
{
case Relation.EqualTo:
WhereFilter = filter.Value.AsString();
break;
}
break;
}
}
if (SelectFilter == "")
{
SelectFilter = "Select *";
}
else if (SelectFilter.ToLower().StartsWith("select ") == false)
{
SelectFilter = "Select " + SelectFilter;
}
if (WhereFilter != "" && WhereFilter.ToLower().StartsWith("where ") == false)
{
WhereFilter = "Where " + WhereFilter;
}
System.Collections.IEnumerable FullLines = null;
int RowCount = 0;
DataSet ds = null;
OleDbConnection con = null;
ExcelObj = new Excel();
Excel.ExcelFileType FileType = Excel.GetFileType(FilePath);
try
{
con = ExcelObj.OpenExcelFile(FilePath, SheetName, FileType, HasHeaderRow, ForceMixedDataAsText, TypeGuessRows, CSVDelimiter);
ds = ExcelObj.ReadSheetFromExcel(FilePath, SheetName, FileType, ref con, SelectFilter, WhereFilter);
}
catch (System.Exception ex)
{
ExcelObj.CloseExcelFile(ref con); //Ensure the excel file is closed before throwing the error.
throw (ex);
}
ExcelObj.CloseExcelFile(ref con);
if (ds.Tables.Count > 0)
{
RowCount = ds.Tables[0].Rows.Count;
FullLines = GetFullLines(ds, SemiColonReplacement);
}
response.Publish("Excel File Path", FilePath);
response.Publish("Sheet Name", SheetName);
response.Publish("Number of rows", RowCount);
response.Publish("Has Header Row", HasHeaderRow);
response.Publish("Force mixed data to be converted to text", ForceMixedDataAsText);
response.Publish("TypeGuessRows", TypeGuessRows);
response.Publish("CSV Delimiter", CSVDelimiter);
response.Publish("; Replacement", SemiColonReplacement);
response.Publish("Select filter", SelectFilter);
response.Publish("Where filter", WhereFilter);
if (FullLines != null)
{
response.PublishRange("Full line as string with fields separated by ;", FullLines);
}
else
{
response.Publish("Full line as string with fields separated by ;", "No data returned");
}
}
public void Execute(IActivityRequest request, IActivityResponse response)
{
string FilePath = request.Inputs["Excel File Path"].AsString();
string SheetName = request.Inputs["Sheet Name"].AsString();
if (SheetName == "")
{
SheetName = FieldValue_SheetName;
}
bool HasHeaderRow = request.Inputs["Has Header Row"].AsBoolean();
bool ForceMixedDataAsText;
string CSVDelimiter = request.Inputs["CSV Delimiter"].AsString();
if (CSVDelimiter == "")
{
CSVDelimiter = FieldValue_CSVDelimiter;
}
string UpdateStatement = request.Inputs["Update statement"].AsString();
if (request.Inputs["Force mixed data to be converted to text"].ToString() != "")
{
ForceMixedDataAsText = request.Inputs["Force mixed data to be converted to text"].AsBoolean();
}
else
{
ForceMixedDataAsText = false;
}
int TypeGuessRows;
if (request.Inputs["TypeGuessRows"].ToString() != "")
{
TypeGuessRows = request.Inputs["TypeGuessRows"].AsInt16();
}
else
{
TypeGuessRows = 8;
}
OleDbConnection con = null;
ExcelObj = new Excel();
Excel.ExcelFileType FileType = Excel.GetFileType(FilePath);
try
{
con = ExcelObj.OpenExcelFile(FilePath, SheetName, FileType, HasHeaderRow, ForceMixedDataAsText, TypeGuessRows, CSVDelimiter);
ExcelObj.UpdateExcelDocument(FilePath, SheetName, FileType, ref con, UpdateStatement);
}
catch (System.Exception ex)
{
ExcelObj.CloseExcelFile(ref con); //Ensure the excel file is closed before throwing the error.
throw (ex);
}
ExcelObj.CloseExcelFile(ref con);
response.Publish("Excel File Path", FilePath);
response.Publish("Sheet Name", SheetName);
response.Publish("Has Header Row", HasHeaderRow);
response.Publish("Force mixed data to be converted to text", ForceMixedDataAsText);
response.Publish("TypeGuessRows", TypeGuessRows);
response.Publish("CSV Delimiter", CSVDelimiter);
response.Publish("Update statement", UpdateStatement);
}
