protected override async Task <Action <AsyncCodeActivityContext> > ExecuteAsync(AsyncCodeActivityContext context, CancellationToken cancellationToken)
{
// Inputs
var excelFilePath = ExcelFilePath.Get(context);
var sapProcessName = SAPProcessName.Get(context);
var xmlFolderPath = XMLFolderPath.Get(context);
var xmlFilePath = string.Empty;
XmlDocument doc = null;
ExcelToXMLConverter excelToXMLConverter = new ExcelToXMLConverter();
if (!string.IsNullOrEmpty(xmlFolderPath))
{
xmlFilePath = excelToXMLConverter.ConvertExcelToXMLFile(excelFilePath, sapProcessName, xmlFolderPath);
}
else
{
doc = excelToXMLConverter.ConvertExcelToXMLDocument(excelFilePath, sapProcessName);
}
// Outputs
return((ctx) => {
XMLDocument.Set(ctx, doc);
XMLPath.Set(ctx, xmlFilePath);
});
}
private void InitPageData()
{
ExcelFilePath.Init(null, null, false, true, UCValueExpression.eBrowserType.File, "xlsx");
xExcelExportQuery.Init(null, mExcelConfig, nameof(ExportToExcelConfig.ExportQueryValue), true);
xExportSheetName.Init(null, mExcelConfig, nameof(ExportToExcelConfig.ExcelSheetName), true);
xRdoByCustomExport.IsChecked = true;
if (mWhereConditionList == null)
{
mWhereConditionList = new ObservableList <ActDSConditon>();
}
xGrdExportCondition.DataSourceList = mWhereConditionList;
ExcelFilePath.ValueTextBox.TextChanged += ExcelFilePathTextBox_TextChanged;
xExportSheetName.ValueTextBox.TextChanged += ExcelSheetNameTextBox_TextChanged;
xExcelExportQuery.ValueTextBox.TextChanged += ExcelExportQuery_ValueTextBox_TextChanged;
SetConditionGridView();
}
public DataSourceExportToExcel(ActDSTableElement actDSTableElement)
{
InitializeComponent();
mActDSTableElement = actDSTableElement;
if (mActDSTableElement.ExcelConfig == null)
{
mActDSTableElement.ExcelConfig = new ExportToExcelConfig();
}
SetFilePath();
SetDataTable();
SetSheetName();
ExcelFilePath.Init(Context.GetAsContext(mActDSTableElement.Context), mActDSTableElement.ExcelConfig, nameof(ExportToExcelConfig.ExcelPath), true, true, UCValueExpression.eBrowserType.File, "xlsx");
xExcelExportQuery.Init(Context.GetAsContext(mActDSTableElement.Context), mActDSTableElement.ExcelConfig, nameof(ExportToExcelConfig.ExportQueryValue), true);
xExportSheetName.Init(Context.GetAsContext(mActDSTableElement.Context), mActDSTableElement.ExcelConfig, nameof(ExportToExcelConfig.ExcelSheetName), true);
BindingHandler.ObjFieldBinding(xRdoByCustomExport, RadioButton.IsCheckedProperty, mActDSTableElement.ExcelConfig, nameof(ExportToExcelConfig.IsCustomExport));
BindingHandler.ObjFieldBinding(xRdoByQueryExport, RadioButton.IsCheckedProperty, mActDSTableElement.ExcelConfig, nameof(ExportToExcelConfig.IsExportByQuery));
BindingHandler.ObjFieldBinding(xExportWhereChkBox, CheckBox.IsCheckedProperty, mActDSTableElement.ExcelConfig, nameof(ExportToExcelConfig.ExportByWhere));
ExcelFilePath.ValueTextBox.TextChanged += ExcelFilePathTextBox_TextChanged;
xExportSheetName.ValueTextBox.TextChanged += ExcelSheetNameTextBox_TextChanged;
xExcelExportQuery.ValueTextBox.TextChanged += ExcelExportQuery_ValueTextBox_TextChanged;
if (mDataTable != null)
{
InitColumnListGrid(mDataTable.Columns);
}
SetConditionGridView();
UpdateQueryValue();
}
protected override async Task <Action <AsyncCodeActivityContext> > ExecuteAsync(AsyncCodeActivityContext context, CancellationToken cancellationToken)
{
// Inputs
var excelFilePath = ExcelFilePath.Get(context);
var sapProcessName = SAPProcessName.Get(context);
var xmlFolderPath = XMLFolderPath.Get(context);
///////////////////////////
// Add execution logic HERE
///////////////////////////
// Outputs
return((ctx) => {
XMLDocument.Set(ctx, null);
XMLPath.Set(ctx, null);
});
}
public DataSourceExportToExcel(string sTableName = "")
{
InitializeComponent();
ExcelFilePath.Init(null, false, true, UCValueExpression.eBrowserType.File, "xlsx", new RoutedEventHandler(BrowseButton_Click));
if (sTableName == "")
{
SheetRow.Height = new GridLength(0);
this.Height = 30;
}
else
{
OutSheetName.Text = sTableName;
SheetRow.Height = new GridLength(30);
this.Height = 60;
}
}
protected override void Execute(NativeActivityContext context)
{
try
{
filepath = ExcelFilePath.Get(context);
//_Application excel = new ApplicationClass();
ExcelHelper.Shared.Close_OpenedFile(filepath);
excel.DisplayAlerts = false;
wb = excel.Workbooks._Open(@filepath, False, False, Missing.Value, Missing.Value, False, False, Missing.Value, Missing.Value, False, Missing.Value, Missing.Value, True);
String sheet = SheetName.Get(context);
if (sheet != null)
{
ws = (_Worksheet)wb.Worksheets[sheet];
}
else
{
ws = (_Worksheet)wb.Worksheets[1];
}
string temp = Range.Get(context);
char[] a = { ':', ';' };
if (temp != null)
{
string[] temp2 = temp.Split(a[0]);
if (temp2.Length == 1)
{
from = temp2[0];
Range last = ws.UsedRange.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
rng = ws.get_Range(from, last);
}
if (temp2.Length == 2)
{
from = temp2[0];
to = temp2[1];
rng = ws.get_Range(from, to);
}
}
else
{
Range last = ws.UsedRange.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
from = "A1";
rng = ws.get_Range("A1", last);
}
Range findRng = rng.Find(Value.Get(context), Missing.Value, XlFindLookIn.xlValues, Missing.Value, Missing.Value, XlSearchDirection.xlNext, False, False, Missing.Value);
string Address = RemoveSpecialCharacters(findRng.Address);
Result.Set(context, Address);
//Log.Logger.LogData("\n## The Address is " + Address, LogLevel.Info);
string RemoveSpecialCharacters(string str)
{
return(Regex.Replace(str, "[^a-zA-Z0-9_.]+", "", RegexOptions.Compiled));
}
wb.Save();
}
catch (Exception ex)
{
Log.Logger.LogData("Exception in LookUpRange: " + ex.Message, LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
finally
{
wb.Close();
excel.Quit();
Marshal.ReleaseComObject(ws);
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(excel);
}
}
private void GainExcel()
{
var info = string.Empty;
if (!System.IO.File.Exists(ExcelFilePath))
{
info = string.Format("未找到统计表格：{0}，无法进行检查", ExcelFilePath);
Console.WriteLine(info);
_paralleQuestions.Add(new Question {
Code = "6101", Name = Name, TableName = ExcelName, Description = info
});
}
else
{
var workbook = ExcelFilePath.OpenExcel();
if (workbook != null)
{
_sheet = workbook.GetSheetAt(0);
if (_sheet != null)
{
var flag = false;
_startline = -1;
for (var i = 0; i <= _sheet.LastRowNum; i++)
{
var row = _sheet.GetRow(i);
if (row != null)
{
var heads = ExcelClass.GetCellValues(row, 0, Fields.Count + 2);
if (heads[0] == DM && heads[1] == MC)
{
flag = true;
#region 验证每个表头名称
for (var j = 2; j < heads.Length; j++)
{
info = heads[j];
if (!string.IsNullOrEmpty(info))
{
var field = Fields.FirstOrDefault(e => e.Index == j);
if (field == null)
{
flag = false;
break;
}
if (field.Title.ToLower() != info.ToLower())
{
flag = false;
break;
}
}
}
#endregion
}
}
if (flag)
{
_startline = i;
break;
}
}
if (_startline == -1)
{
_paralleQuestions.Add(new Question {
Code = "6101", Name = Name, TableName = ExcelName, Description = "未获取表格的表头，请核对数据库标准"
});
}
else
{
Analyze(_sheet, _startline + Space);//读取excel文件的数据值
}
}
else
{
_paralleQuestions.Add(new Question {
Code = "6101", Name = Name, TableName = ExcelName, Description = "无法获取Excel中的工作表"
});
}
}
else
{
_paralleQuestions.Add(new Question {
Code = "6101", Name = Name, TableName = ExcelName, Description = "无法打开Excel文件"
});
}
}
}
