protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
Int32 sheetIndex = SheetIndex.Get(context);
string oldSheetName = OldSheetName.Get(context);
string sheetName = SheetName.Get(context);
Excel::Sheets sheets = excelApp.ActiveWorkbook.Sheets;
if (oldSheetName != "" && oldSheetName != null)
{
sheets.Item[oldSheetName].Name = sheetName;
}
else
{
sheets.Item[sheetIndex].Name = sheetName;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
sheets = null;
GC.Collect();
}
catch (Exception e)
{
new CommonVariable().realaseProcessExit(excelApp);
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL重命名工作表执行过程出错", e.Message);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
string sheetName = SheetName.Get(context);
string cellName_Begin = CellName_Begin.Get(context);
string cellName_End = CellName_End.Get(context);
int cellRow_Begin = CellRow_Begin.Get(context);
int cellColumn_Begin = CellColumn_Begin.Get(context);
int cellRow_End = CellRow_End.Get(context);
int cellColumn_End = CellColumn_End.Get(context);
Excel::_Worksheet sheet;
if (sheetName != null)
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
else
{
sheet = excelApp.ActiveSheet;
}
Excel::Range range1, range2;
range1 = cellName_Begin == null ? sheet.Cells[cellRow_Begin, cellColumn_Begin] : sheet.Range[cellName_Begin];
range2 = cellName_End == null ? sheet.Cells[cellRow_End, cellColumn_End] : sheet.Range[cellName_End];
Excel::Range range = sheet.Range[range1, range2];
if (!ShiftCells)
{
range.Clear();
}
else
{
if (ShiftType == ShiftTypes.ShiftUp)
{
range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
else if (ShiftType == ShiftTypes.ShiftLeft)
{
range.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
}
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
sheet = null;
range = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL删除区域执行过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
private int Execute(ActivityContext context, IWorkbookAdapter adapter)
{
var sheetName = SheetName.Get(context);
var range = CellRange.Get(context);
return(adapter.RemoveHyperlink(sheetName, range));
}
protected override Task ExecuteAsync(AsyncCodeActivityContext context, SheetsService sheetService)
{
var includeHeaders = IncludeHeaders;
var sheet = SheetName.Get(context);
var startingCell = StartingCell.Get(context);
var dataTable = DataTable.Get(context);
string cellToPassToService;
if (string.IsNullOrWhiteSpace(sheet))
{
cellToPassToService = startingCell;
}
else
{
cellToPassToService = string.Format("{0}!{1}", sheet, startingCell);
}
return(Task.Factory.StartNew <object>(() =>
{
ValueRange requestBody = CreateRequestBodyWithValues(dataTable, includeHeaders);
SpreadsheetsResource.ValuesResource.UpdateRequest request =
sheetService.Spreadsheets.Values.Update(requestBody, SpreadsheetId, cellToPassToService);
//request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
var response = request.Execute();
return response;
}));
}
private string[] Execute(ActivityContext context, IWorkbookAdapter adapter)
{
var sheetName = SheetName.Get(context);
var range = CellRange.Get(context);
return(adapter.GetHyperlinks(sheetName, range).ToArray());
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
m_Delegate = new runDelegate(Run);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
object data = new object();
Excel.Range xCopyFromRange = null;
Int32 rowColNum = RowColNum.Get(context);
if (CurrReadType == ReadType.读取行)
{
data = sheet.Rows[rowColNum].Value;
xCopyFromRange = sheet.Rows[rowColNum] as Excel.Range;
xCopyFromRange.Copy();
}
else
{
data = sheet.Columns[rowColNum].Value;
xCopyFromRange = sheet.Columns[rowColNum] as Excel.Range;
xCopyFromRange.Copy();
}
//Collection<object> _data = new Collection<object>();
//foreach (object cell in ((object[,])data))
//{
// if (cell != null)
// {
// _data.Add(cell);
// }
// else
// break;
//}
RolColData.Set(context, data);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL行列读取执行过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
return(m_Delegate.BeginInvoke(callback, state));
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
string sheetName = SheetName.Get(context);
string cellName_Begin = CellName_Begin.Get(context);
int cellRow_Begin = CellRow_Begin.Get(context);
int cellColumn_Begin = CellColumn_Begin.Get(context);
System.Data.DataTable dt = DataTable.Get(context);
Excel::_Worksheet sheet;
if (sheetName != null)
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
else
{
sheet = excelApp.ActiveSheet;
}
Excel::Range range = cellName_Begin == null ? sheet.Cells[cellRow_Begin, cellColumn_Begin] : sheet.Range[cellName_Begin];
int iRowBegin = range.Row;
int iColBegin = range.Column;
int i = 0;
if (isTitle)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
sheet.Cells[iRowBegin + i, j + iColBegin] = dt.Columns[j].ColumnName;
}
i++;
}
for (int a = 0; a <= dt.Rows.Count - 1; a++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
sheet.Cells[iRowBegin + i + a, j + iColBegin] = dt.Rows[a][j];
}
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL写入区域执行过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
protected override void Execute(CodeActivityContext context)
{
this.strFilePath = FilePath.Get(context);
this.strSheetName = SheetName.Get(context);
this.strAccessFilePath = AccessFilePath.Get(context);
this.strAccessTableName = AccessTableName.Get(context);
this.strCellRange = CellRange.Get(context);
this.ExportToAccess();
}
private bool Execute(ActivityContext context, IWorkbookAdapter adapter)
{
var sheetName = SheetName.Get(context);
var cellAddress = Cell.Get(context);
var link = Link.Get(context);
var label = Label.Get(context);
var tooltip = Tooltip.Get(context);
adapter.AddHyperlink(sheetName, cellAddress, label, link, tooltip);
return(true);
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
m_Delegate = new runDelegate(Run);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
int rowCounts = 0, colCounts = 0;
//有效行列数 不包含中间的空行
if (isValid)
{
rowCounts = sheet.UsedRange.Rows.Count;
colCounts = sheet.UsedRange.Columns.Count;
}
//空行/列截止
else if (isActive)
{
rowCounts = sheet.UsedRange.CurrentRegion.Rows.Count;
colCounts = sheet.UsedRange.CurrentRegion.Columns.Count;
}
else if (isSingleLine)
{
rowCounts = sheet.get_Range("A65535").get_End(Excel.XlDirection.xlUp).Row;
colCounts = sheet.get_Range("IV1").get_End(Excel.XlDirection.xlToLeft).Column;
}
RowCounts.Set(context, rowCounts);
ColCounts.Set(context, colCounts);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL获取行列总数过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
return(m_Delegate.BeginInvoke(callback, state));
}
protected override void Execute(CodeActivityContext context)
{
string ExcelRange = Cell.Get(context);
string ExcelPath = FilePath.Get(context);
string ExcelValue = Value.Get(context);
string ExcelSheet = SheetName.Get(context);
Microsoft.Office.Interop.Excel.Application xlApp = new Application();
Workbook xlWorkbook = xlApp.Workbooks.Open(ExcelPath);
Worksheet xlWorksheet = xlWorkbook.Worksheets.get_Item(ExcelSheet);
try
{
// To get range with some data in it
//Range rng = xlWorksheet.UsedRange;
Range rng = xlWorksheet.get_Range(ExcelRange);
// To change the format of cell to Text
rng.NumberFormat = "@";
// Get ASCII Value
char c = char.Parse(ExcelRange.Substring(0, 1));
// Get Cell row and column value
int colIndex = (int)c - 64;
int rowIndex = int.Parse(ExcelRange.Substring(1));
xlWorksheet.Cells[rowIndex, colIndex] = ExcelValue;
// Close Excel Objects
xlWorkbook.Save();
xlWorkbook.Close();
xlApp.Quit();
}
catch (Exception e)
{
Console.WriteLine(e.Message + Environment.NewLine + e.Source);
}
finally
{
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
}
}
protected override void Execute(CodeActivityContext context)
{
Excel.Sheets sheets = null;
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Worksheet workSheet = null;
try
{
var filePath = FilePath.Get(context);
var sheetName = SheetName.Get(context);
var cell = Cell.Get(context);
var comment = Comment.Get(context);
excelApp = new Excel.Application();
excelApp.Visible = false;
workbook = excelApp.Workbooks.Open(filePath);
sheets = workbook.Sheets;
for (int i = 1; i <= sheets.Count; i++)
{
dynamic val = sheets[i];
Excel.Worksheet worksheet = val as Excel.Worksheet;
if (worksheet != null && worksheet.Name.ToLowerInvariant().Equals(sheetName?.ToLowerInvariant()))
{
workSheet = worksheet;
workSheet.Activate();
break;
}
Marshal.ReleaseComObject(val);
}
Excel.Range oCell = workSheet.Range[cell, cell] as Excel.Range;
if (oCell.Comment != null)
{
oCell.Comment.Delete();
}
oCell.AddComment(comment);
workbook.Save();
excelApp.Quit();
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workSheet);
Marshal.ReleaseComObject(excelApp);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
Int32 cellColumn = CellColumn.Get(context);
Int32 cellRow = CellRow.Get(context);
string cellName = CellName.Get(context);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
object cellContent = null;
if (cellName == null)
{
cellContent = sheet.Cells[cellRow, cellColumn].Value2;
}
else
{
cellContent = sheet.Range[cellName].Value2;
}
CellContent.Set(context, cellContent);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL获取单元格内容出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
string sheetName = SheetName.Get(context);
excelApp.Worksheets.Add();
excelApp.ActiveSheet.Name = sheetName;
}
catch (Exception e)
{
new CommonVariable().realaseProcessExit(excelApp);
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL增加新工作表执行过程出错", e.Message);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
m_Delegate = new runDelegate(Run);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
object data = new object();
Int32 rowColNum = RowColNum.Get(context);
if (CurrInsertType == InsertType.制行)
{
sheet.Rows[rowColNum].Value = RolColData.Get(context);
}
else
{
sheet.Columns[rowColNum].Value = RolColData.Get(context);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL行列复制执行过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
return(m_Delegate.BeginInvoke(callback, state));
}
protected override void Execute(CodeActivityContext context)
{
string path = Path.Get(context);
string sheetName = SheetName.Get(context);
string InstanceName = instanceName.Get(context);
var EngineInstance = (Program)instance.Get(context);
Excel.Application eXL = new Excel.Application();
eXL.Visible = true;
Excel.Workbook eWB = eXL.Workbooks.Open(path);
Excel.Worksheet eWS;
if (sheetName == null)
{
eWS = eWB.Worksheets.get_Item(1);
}
else
{
eWS = eWB.Worksheets.Item[sheetName];
}
EngineInstance.appInstance.Add(InstanceName, eXL);
}
protected override void Execute(CodeActivityContext context)
{
var EngineInstance = (Program)instance.Get(context);
string InstanceName = instanceName.Get(context);
string sheetName = SheetName.Get(context);
string range = Range.Get(context);
object excel;
if (EngineInstance.appInstance.TryGetValue(InstanceName, out excel))
{
Excel.Application eXL = (Excel.Application)excel;
Excel.Workbook eWB = eXL.ActiveWorkbook;
Excel.Worksheet eWS = eWB.Worksheets.Item[sheetName];
Excel.Range eRng = eWS.Range[range];
Data.Set(context, eRng.Value);
}
else
{
Console.WriteLine("Read2 Cell Failed");
}
}
protected override void Execute(CodeActivityContext context)
{
//get Excel context
XLExcelContextInfo customContext = Utils.GetXLExcelContextInfo(context);
//retrieve the parameters from the Context
string filePath = customContext.Path;
//retrieve the parameters from the Context
string range = Range.Get(context);
string sheetName = SheetName.Get(context);
bool addHeaders = Headers;//.Get(context);
//excel range init
ExcelRange excelRange = new ExcelRange(range);
DataTable result = Utils.ReadSAXRange(excelRange, filePath, sheetName, addHeaders);
//"C:\\Users\\bucur\\source\\repos\\ExcelSheetExtensionClasses\\TestExcelExtensions\\TestFiles\\LALALA.xlsx", "Sheet1", false);
//set the result
Result.Set(context, result);
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
string cellName_Begin = CellName_Begin.Get(context);
string cellName_End = CellName_End.Get(context);
int cellRow_Begin = CellRow_Begin.Get(context);
int cellColumn_Begin = CellColumn_Begin.Get(context);
int cellRow_End = CellRow_End.Get(context);
int cellColumn_End = CellColumn_End.Get(context);
double rowHeight = RowHeight.Get(context);
double colWidth = ColWidth.Get(context);
Int32 fontSize = FontSize.Get(context);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet = null;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
Excel::Range range1, range2;
range1 = cellName_Begin == null ? sheet.Cells[cellRow_Begin, cellColumn_Begin] : sheet.Range[cellName_Begin];
range2 = cellName_End == null ? sheet.Cells[cellRow_End, cellColumn_End] : sheet.Range[cellName_End];
Excel::Range range = sheet.Range[range1, range2];
/*对齐设置*/
if ((int)_AlignStyle != 0)
{
range.HorizontalAlignment = (AlignEnum)_AlignStyle;
}
/*字体*/
range.Font.Bold = isBold;
range.Font.Italic = isItalic;
range.Font.Underline = isUnderLine;
if (Font != 0)
{
range.Font.Name = ConvertFont(Font.ToString());
}
range.Font.Size = fontSize;
if ((int)_FontColor != 0)
{
range.Font.ColorIndex = (int)_FontColor;
}
/*填充色*/
if ((int)_CellColor != 0)
{
range.Interior.ColorIndex = (int)_CellColor;
}
/*行列宽度*/
range.RowHeight = rowHeight;
range.ColumnWidth = colWidth;
/*边框*/
if ((int)_BorderStyle != 0)
{
switch ((int)_BorderType)
{
case 0:
{
range.Borders.LineStyle = (int)_BorderStyle;
break;
}
case 1:
{
range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = (int)_BorderStyle;
break;
}
case 2:
{
range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = (int)_BorderStyle;
break;
}
case 3:
{
range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = (int)_BorderStyle;
break;
}
case 4:
{
range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = (int)_BorderStyle;
break;
}
default:
break;
}
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
sheet = null;
range = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL区域设置执行过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
m_Delegate = new runDelegate(Run);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
Int32 rowColBegin = RowColBegin.Get(context);
Int32 rowColEnd = RowColEnd.Get(context);
if (rowColBegin > rowColEnd)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL行列操作开始值不能大于结束值");
}
switch ((int)Operation)
{
case 1:
{
if (RowColSel == (RowCols)1)
{
sheet.Range[
sheet.Cells[rowColBegin, 1],
sheet.Cells[rowColEnd, sheet.Columns.Count]].
Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
else
{
sheet.Range[
sheet.Cells[1, rowColBegin],
sheet.Cells[sheet.Rows.Count, rowColEnd]]
.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
}
break;
}
case 2:
{
if (RowColSel == (RowCols)1)
{
sheet.Range[
sheet.Cells[rowColBegin, 1],
sheet.Cells[rowColEnd, 1]].
EntireRow.Hidden = true;
}
else
{
sheet.Range[
sheet.Cells[1, rowColBegin],
sheet.Cells[sheet.Rows.Count, rowColEnd]].
EntireColumn.Hidden = true;
}
break;
}
case 3:
{
if (RowColSel == (RowCols)1)
{
sheet.Range[
sheet.Cells[rowColBegin, 1],
sheet.Cells[rowColEnd, sheet.Columns.Count]].
Insert(Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
else
{
sheet.Range[
sheet.Cells[1, rowColBegin],
sheet.Cells[sheet.Rows.Count, rowColEnd]].
Insert(Excel.XlInsertShiftDirection.xlShiftToRight);
}
break;
}
default:
return(m_Delegate.BeginInvoke(callback, state));
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL行列操作执行过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
return(m_Delegate.BeginInvoke(callback, state));
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
string sheetName = SheetName.Get(context);
string cellName_Begin = CellName_Begin.Get(context);
string cellName_End = CellName_End.Get(context);
int cellRow_Begin = CellRow_Begin.Get(context);
int cellColumn_Begin = CellColumn_Begin.Get(context);
int cellRow_End = CellRow_End.Get(context);
int cellColumn_End = CellColumn_End.Get(context);
Excel::_Worksheet sheet;
if (sheetName != null)
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
else
{
sheet = excelApp.ActiveSheet;
}
Excel::Range range1, range2;
range1 = cellName_Begin == null ? sheet.Cells[cellRow_Begin, cellColumn_Begin] : sheet.Range[cellName_Begin];
range2 = cellName_End == null ? sheet.Cells[cellRow_End, cellColumn_End] : sheet.Range[cellName_End];
Excel::Range range3 = sheet.Range[range1, range2];
System.Data.DataTable dt = new System.Data.DataTable();
int iRowCount = range3.Rows.Count;
int iColCount = range3.Columns.Count;
int rowBegin = range3.Row;
int colBegin = range3.Column;
//生成列头
for (int i = 0; i < iColCount; i++)
{
var name = "column" + i;
if (isTitle)
{
var txt = ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowBegin, i + colBegin]).Text.ToString();
if (!string.IsNullOrEmpty(txt))
{
name = txt;
}
}
while (dt.Columns.Contains(name))
{
name = name + "_1";//重复行名称会报错。
}
dt.Columns.Add(new System.Data.DataColumn(name, typeof(string)));
}
//生成行数据
Microsoft.Office.Interop.Excel.Range range;
int rowIdx = isTitle ? 2 : 1;
for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
{
System.Data.DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[iRow + rowBegin - 1, iCol + colBegin - 1];
dr[iCol - 1] = (range.Value2 == null) ? "" : range.Value2;
}
dt.Rows.Add(dr);
}
DataTable.Set(context, dt);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL读取区域执行过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
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);
}
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
string cellName_Begin = CellName_Begin.Get(context);
string cellName_End = CellName_End.Get(context);
int cellRow_Begin = CellRow_Begin.Get(context);
int cellColumn_Begin = CellColumn_Begin.Get(context);
int cellRow_End = CellRow_End.Get(context);
int cellColumn_End = CellColumn_End.Get(context);
string findData = FindData.Get(context);
string replaceData = ReplaceData.Get(context);
object obj = null;
Excel::Range range1, range2;
Excel::_Worksheet sheet = null;
Excel::_Worksheet currSheet = null;
Excel.XlLookAt lookAt = IsCellMatch ? Excel.XlLookAt.xlWhole : Excel.XlLookAt.xlPart;
if (SheetName.Get(context) != null)
{
sheet = excelApp.ActiveWorkbook.Sheets[SheetName];
}
else
{
sheet = excelApp.ActiveSheet;
}
try
{
if (AllSheets) //全部工作表
{
int sheetCount = excelApp.ActiveWorkbook.Worksheets.Count;
for (int i = 1; i < sheetCount + 1; i++)
{
if (AllRange)
{
excelApp.ActiveWorkbook.Worksheets[i].Range["A1", "IV65535"].Replace(obj, replaceData, lookAt, Type.Missing, IsTextTransform, isSemiFull);
}
else
{
currSheet = excelApp.ActiveWorkbook.Worksheets[i];
range1 = cellName_Begin == null ? currSheet.Cells[cellRow_Begin, cellColumn_Begin] : currSheet.Range[cellName_Begin];
range2 = cellName_End == null ? currSheet.Cells[cellRow_End, cellColumn_End] : currSheet.Range[cellName_End];
currSheet.Range[range1, range2].Replace(findData, replaceData, lookAt, Type.Missing, IsTextTransform, isSemiFull);
}
}
}
else
{
if (AllRange) //全部区域
{
sheet.Range["A1", "IV65535"].Replace(findData, replaceData, lookAt, Type.Missing, IsTextTransform, isSemiFull);
}
else
{
range1 = cellName_Begin == null ? sheet.Cells[cellRow_Begin, cellColumn_Begin] : sheet.Range[cellName_Begin];
range2 = cellName_End == null ? sheet.Cells[cellRow_End, cellColumn_End] : sheet.Range[cellName_End];
sheet.Range[range1, range2].Replace(findData, replaceData, lookAt, Type.Missing, IsTextTransform, isSemiFull);
}
}
/* 资源回收释放 */
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
if (currSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(currSheet);
currSheet = null;
}
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL查找替换过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
protected override void Execute(CodeActivityContext context)
{
WorkflowDataContext dc = context.DataContext;
//get Excel context
XLExcelContextInfo customContext = Utils.GetXLExcelContextInfo(context);
//retrieve the parameters from the Context
string filePath = customContext.Path;
string sheetName = SheetName.Get(context);
string rowNum = "";
//open file
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
//determine ID of the Sheet
string relId = workbookPart.Workbook.Descendants <Sheet>().First(s => sheetName.Equals(s.Name)).Id;
if (String.IsNullOrEmpty(relId))
{
throw new Exception("Could not indentify the Excel Sheet");
}
//open reader for Sheet
WorksheetPart worksheetPart = workbookPart.GetPartById(relId) as WorksheetPart;
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
//read the XML objects until we reach the rows
while (reader.Read())
{
//check if current elem is row
if (reader.ElementType == typeof(Row))
{
//loop through row siblings
do
{
if (reader.HasAttributes)
{
//at each step, read the current rowNum
rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
}
} while (reader.ReadNextSibling());
break;
}
}
}
int result;
//convert the result to Int and pass it as an output argument
if (Int32.TryParse(rowNum, out result))
{
NumberOfRows.Set(context, result);
}
else
{
throw new Exception("Unable to parse the line number");
}
}
