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));
}
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 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;
}));
}
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
{
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));
}
/// <summary>
/// Serves as the default hash function.
/// </summary>
/// <returns>
/// A hash code for the current object.
/// </returns>
public override int GetHashCode()
{
unchecked {
if (string.IsNullOrEmpty(SheetName))
{
return(LocalAddress.GetHashCode());
}
return(SheetName.GetHashCode() ^ LocalAddress.GetHashCode());
}
}
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);
}
/// <summary>
/// 返回一个包含<see cref="SheetName"/>和<see cref="LocalAddress"/>的单元格地址
/// </summary>
/// <returns></returns>
public override string ToString()
{
if (String.IsNullOrEmpty(SheetName))
{
return(LocalAddress);
}
if (SheetName.Contains(" "))
{
return($"'{SheetName}'!{LocalAddress}");
}
return($"{SheetName}!{LocalAddress}");
}
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));
}
public bool Equals(FileSetting other)
{
return
(Name.Equals(other.name) &&
SheetIndex.Equals(other.SheetIndex) &&
SheetName.Equals(other.SheetName) &&
IsStartupSheet.Equals(other.IsStartupSheet) &&
ColumnHeaderIndex.Equals(other.ColumnHeaderIndex) &&
RowHeaderIndex.Equals(other.RowHeaderIndex) &&
RowHeaderName.Equals(other.RowHeaderName) &&
ExactMatch.Equals(other.ExactMatch) &&
UseRegex.Equals(other.UseRegex) &&
MaxRowHeaderWidth.Equals(other.MaxRowHeaderWidth));
}
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));
}
private DataSet Parse(string FileName)
{
DataSet ds = new DataSet();
//ds.ReadXmlSchema("price.xsd");
OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes\"");
cnn.Open();
try
{
string[] SheetNames = GetSheetNames(cnn);
foreach (string SheetName in SheetNames)
{
/*
* if (SheetName.EndsWith("$Print_Titles") || SheetName.EndsWith("$Print_Area")
|| SheetName.EndsWith("$Database") || SheetName.EndsWith("$Criteria")
|| SheetName.EndsWith("$Data_form") || SheetName.EndsWith("$Sheet_Title"))
||{
||}
||else
*/
if (SheetName.EndsWith("$"))
{
DataTable table = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "]", cnn);
//da.FillSchema(ds, SchemaType.Mapped);
da.Fill(table);
RemoveEmptyRows(table);
if (table.Rows.Count != 0)
{
ds.Tables.Add(table);
}
}
}
}
finally
{
cnn.Close();
}
return(ds);
}
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));
}
public virtual DataSet Parse(string fileName, Stream stream)
{
if (stream != null)
{
throw new NotSupportedException();
}
DataSet ds = new DataSet();
OleDbConnection cnn = new OleDbConnection(_connectionString.Replace("$1", fileName));
cnn.Open();
try
{
string[] SheetNames = GetSheetNames(cnn);
foreach (string SheetName in SheetNames)
{
/*
* if (SheetName.EndsWith("$Print_Titles") || SheetName.EndsWith("$Print_Area")
|| SheetName.EndsWith("$Database") || SheetName.EndsWith("$Criteria")
|| SheetName.EndsWith("$Data_form") || SheetName.EndsWith("$Sheet_Title"))
||{
||}
||else
*/
if (SheetName.EndsWith("$") || SheetName.IndexOf("$") == -1)
{
DataTable table = Parse(cnn, SheetName, false);
if (table.Rows.Count != 0)
{
ds.Tables.Add(table);
}
}
}
}
finally
{
cnn.Close();
}
return(ds);
}
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);
}
public bool Contains(CellReference cell)
{
if (cell == null)
{
return(false);
}
// check if cell is on the same sheet as the table
if (!SheetName.Equals(cell.SheetName))
{
return(false);
}
// check if the cell is inside the table
if (cell.Row >= StartRowIndex &&
cell.Row <= EndRowIndex &&
cell.Col >= StartColIndex &&
cell.Col <= EndColIndex)
{
return(true);
}
return(false);
}
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");
}
}
public ExcelResultEntity GetExcelAttribute()
{
ExcelResultEntity excelresult = new ExcelResultEntity();
int cnt;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // >2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
excelresult.SheetNumber = workbook.NumberOfSheets;
for (cnt = 0; cnt < excelresult.SheetNumber; cnt++)
{
SheetName sn = new SheetName();
sn.Name = workbook.GetSheetAt(cnt).SheetName;
excelresult.SheetName.Add(sn);
}
excelresult.Code = 0;
excelresult.ErrMsg = "";
}
catch (Exception ex)
{
excelresult.Code = 9999;
excelresult.ErrMsg = "获取EXCEL属性异常： " + ex.Message;
}
return(excelresult);
}
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);
}
public bool IsForSheet(string sheetName)
{
return(SheetName.Equals(sheetName));
}
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 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 CreateSheetBasic(SheetName sheetName)
{
Dictionary<string, string> dictionary = null;
string[] caption = null;
ISheet sheet = null; ;
switch (sheetName)
{
case SheetName.Corporate:
sheet = workbook.CreateSheet("Корпоративка");
dictionary = Initializer.GetCorporateDictionary();
caption = Initializer.GetCorporateCaption();
break;
case SheetName.MvzOrder:
sheet = workbook.CreateSheet("МВЗ=ЗАКАЗ");
dictionary = Initializer.GetMvzOrderDictionary();
caption = Initializer.GetMvzOrderCaption();
break;
case SheetName.MvzPhone:
sheet = workbook.CreateSheet("МВЗ=Телефон");
dictionary = Initializer.GetMvzPhoneDictionary();
caption = Initializer.GetMvzPhoneCaption();
break;
}
CreateCaption(caption, sheet, styleTable);
int row = 1;
foreach (KeyValuePair<string, string> kvp in dictionary)
{
IRow currentRow = sheet.CreateRow(row++);
ICell cellKey = currentRow.CreateCell(0);
cellKey.SetCellValue(int.Parse(kvp.Key));
cellKey.CellStyle = styleTable;
ICell cellValue = currentRow.CreateCell(1);
if (kvp.Value != "")
cellValue.SetCellValue(double.Parse(kvp.Value));
else
cellValue.SetCellValue(kvp.Value);
cellValue.CellStyle = styleTable;
}
sheet.SetColumnWidth(0, 256 * 21);
sheet.SetColumnWidth(1, 256 * 21);
}
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");
}
}
private bool ExportTable(Excel.Workbook ExcelWbk, IMxDocument MxDoc, IStandaloneTable StandTable, ref IProgressDialog2 progressDialog, ref IStepProgressor stepProgressor, ref ITrackCancel trackCancel)
{
ITableProperties TableProperties = null;
IEnumTableProperties EnumTableProperties = null;
ITableProperty3 TableProperty = null;
ITableCharacteristics TableCharacteristics = null;
ITableSelection TabSel = null;
IDisplayTable DisplayTable = null;
ITable Table = null;
IRow TabRow = null;
IObjectClass ObjectClass = null;
ISubtypes Subtypes = null;
ITableFields TableFields = null;
ICursor Cursor = null;
IField CurField = null;
IDomain Domain = null;
ICodedValueDomain CodedValueDomain = null;
object missing = null;
object sheet = null;
Microsoft.Office.Interop.Excel.Style style = null;
try
{
bool UseDescriptions;
int subtype;
string SheetName;
int Col = 0;
int Row = 0;
int i;
int j;
missing = System.Reflection.Missing.Value;
sheet = ExcelWbk.Sheets[ExcelWbk.Sheets.Count];
Excel.Worksheet ExcelSheet;
Excel.Range ExcelRange;
//Add new Excel worksheet
ExcelSheet = (Excel.Worksheet)ExcelWbk.Sheets.Add(missing, sheet, missing, missing);
//style = ExcelWbk.Styles.Add("Style1");
//style.NumberFormat = "@";
//style.Font.Name = "Arial";
//style.Font.Bold = True
//style.Font.Size = 12;
//style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPattern Solid
//style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLe ft
SheetName = StandTable.Name;
if (SheetName.Length > 30)
{
SheetName = SheetName.Substring(0, 30);
}
ExcelSheet.Name = SheetName;
//Determine whether to use descriptions or codes for domains
UseDescriptions = true;
TableProperties = MxDoc.TableProperties;
EnumTableProperties = (IEnumTableProperties)TableProperties;
EnumTableProperties.Reset();
TableProperty = (ITableProperty3)EnumTableProperties.Next();
while (TableProperty != null)
{
if (TableProperty.StandaloneTable != null)
{
if (TableProperty.StandaloneTable.Equals(StandTable))
{
TableCharacteristics = (ITableCharacteristics)TableProperty;
UseDescriptions = TableCharacteristics.ShowCodedValueDomainDescriptions;
break;
}
}
TableProperty = (ITableProperty3)EnumTableProperties.Next();
}
TabSel = (ITableSelection)StandTable;
DisplayTable = (IDisplayTable)StandTable;
Table = (ITable)DisplayTable.DisplayTable;
//Get subtype info
ObjectClass = Table as IObjectClass;
Subtypes = ObjectClass as ISubtypes;
//Get TableFields so later we can determine whether that field is visible
TableFields = (ITableFields)StandTable;
//loop through each field and write column headings
Row = 1;
for (j = 0; j < TableFields.FieldCount; j++)
{
CurField = TableFields.get_Field(j);
//skip blob and geometry fields
if ((CurField.Type != esriFieldType.esriFieldTypeBlob) && (CurField.Type != esriFieldType.esriFieldTypeGeometry))
{
Col += 1;
//Write field alias name as Excel column header
ExcelSheet.Cells[Row, Col] = TableFields.get_FieldInfo(j).Alias;
if (CurField.Type == esriFieldType.esriFieldTypeString)
{
ExcelSheet.get_Range(ExcelSheet.Cells[1, Col], ExcelSheet.Cells[65535, Col]).EntireColumn.NumberFormat = "@";
}
}
}
//Get all selected records for this table (use IDisplayTable to get any joined data)
DisplayTable.DisplaySelectionSet.Search(null, true, out Cursor);
//subtype = Subtypes.DefaultSubtypeCode;
//For each selected record
TabRow = Cursor.NextRow();
//stepProgressor.Step();
// progressDialog.Description = A4LGSharedFunctions.Localizer.GetString("ExportAsset") + stepProgressor.Position + A4LGSharedFunctions.Localizer.GetString("Of") + MxDoc.FocusMap.SelectionCount.ToString() + ".";
while (TabRow != null)
{
Row += 1;
if (Subtypes != null && Subtypes.HasSubtype == true &&
(TabRow.get_Value(Subtypes.SubtypeFieldIndex) != null))
{
subtype = Convert.ToInt32(TabRow.get_Value(Subtypes.SubtypeFieldIndex));
}
else
{
subtype = -99999;
}
//For each column
Col = 0;
for (j = 0; j < TableFields.FieldCount; j++)
{
CurField = TableFields.get_Field(j);
//skip blob and geometry fields in data also
if ((CurField.Type != esriFieldType.esriFieldTypeBlob) && (CurField.Type != esriFieldType.esriFieldTypeGeometry))
{
Col += 1;
ExcelSheet.Cells[Row, Col] = TabRow.get_Value(j);
if (UseDescriptions == true && subtype == -99999)
{
Domain = CurField.Domain;
if (Domain != null)
{
if (Domain.Type == esriDomainType.esriDTCodedValue)
{
CodedValueDomain = (ICodedValueDomain)CurField.Domain;
for (i = 0; i < CodedValueDomain.CodeCount; i++)
{
if ((CodedValueDomain.get_Value(i)).ToString() == (TabRow.get_Value(j)).ToString())
{
//System.Diagnostics.Debug.Print(CodedValueDomain.get_Name(0).ToString());
ExcelSheet.Cells[Row, Col] = CodedValueDomain.get_Name(i);
i = CodedValueDomain.CodeCount;
}
}
}
}
}
else if (UseDescriptions == true && subtype != -99999)
{
if (Subtypes.SubtypeFieldIndex == j)
{
ExcelSheet.Cells[Row, Col] = Subtypes.get_SubtypeName(subtype);
}
else
{
Domain = Subtypes.get_Domain(subtype, CurField.Name);
if ((Domain != null) && (Domain.Type == esriDomainType.esriDTCodedValue))
{
CodedValueDomain = (ICodedValueDomain)Domain;
for (i = 0; i < CodedValueDomain.CodeCount; i++)
{
if ((CodedValueDomain.get_Value(i)).ToString() == (TabRow.get_Value(j)).ToString())
{
//System.Diagnostics.Debug.Print(CodedValueDomain.get_Name(0).ToString());
ExcelSheet.Cells[Row, Col] = CodedValueDomain.get_Name(i);
i = CodedValueDomain.CodeCount;
}
}
}
}
}
}
}
stepProgressor.Step();
progressDialog.Description = A4LGSharedFunctions.Localizer.GetString("ExportAsset") + stepProgressor.Position + A4LGSharedFunctions.Localizer.GetString("Of") + MxDoc.FocusMap.SelectionCount.ToString() + ".";
if (!trackCancel.Continue())
{
return(false);
}
TabRow = Cursor.NextRow();
}
//Hide Columns
Col = 0;
for (j = 0; j < TableFields.FieldCount; j++)
{
CurField = TableFields.get_Field(j);
//skip blob and geometry fields in data also
if ((CurField.Type != esriFieldType.esriFieldTypeBlob) && (CurField.Type != esriFieldType.esriFieldTypeGeometry))
{
Col += 1;
//Autofit
ExcelRange = ExcelSheet.get_Range(ExcelSheet.Cells[1, Col], ExcelSheet.Cells[Row, Col]);
ExcelRange.EntireColumn.AutoFit();
//Hide column if invisible in ArcMap
if (TableFields.get_FieldInfo(j).Visible == false)
{
ExcelRange = ExcelSheet.get_Range(ExcelSheet.Cells[1, Col], ExcelSheet.Cells[Row, Col]);
ExcelRange.EntireColumn.Hidden = true;
}
}
}
return(true);
}
catch
{
MessageBox.Show(A4LGSharedFunctions.Localizer.GetString("ExportXLError"));
return(true);
}
finally
{
TableProperties = null;
EnumTableProperties = null;
TableProperty = null;
TableCharacteristics = null;
TabSel = null;
DisplayTable = null;
Table = null;
TabRow = null;
ObjectClass = null;
Subtypes = null;
TableFields = null;
if (Cursor != null)
{
Marshal.ReleaseComObject(Cursor);
}
Cursor = null;
CurField = null;
Domain = null;
CodedValueDomain = null;
missing = null;
sheet = null;
style = null;
}
}
