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