private static String[,] GetComments(String sheetName, Excel.Range range, int lastRow, int lastCol) { String[,] commentArray = Array.CreateInstance(typeof(String), new int[2] { lastRow, lastCol }, new int[2] { 1, 1 }) as String[, ]; Excel.Range commentCells = null; Excel.Comment comment = null; //Excel.Shape cShape = null; try { commentCells = searchComments(range); if (commentCells == null) { return(commentArray); } //System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch(); //stopwatch.Start(); //int ccount = 0; foreach (Excel.Range singleCell in commentCells) { comment = singleCell.Comment; if (comment != null) { String author = comment.Author; String text = comment.Text(); commentArray[singleCell.Row, singleCell.Column] = text; } else { //GlobalFunctions.InfoLog("Empty Comment", new CellUpdate(singleCell, Enums.CellChangeType.Comment)); } //cShape = comment.Shape; //ccount++; } //stopwatch.Stop(); //GlobalFunctions.InfoLog(String.Format("Sheet: {0}, Comments: {1}, Time elapsed: {2}", sheetName, ccount, stopwatch.Elapsed)); return(commentArray); } catch (Exception) { throw; } finally { if (commentCells != null) { Marshal.ReleaseComObject(commentCells); } if (comment != null) { Marshal.ReleaseComObject(comment); } } }
public static void LogMessagetoExcelFile(IEnumerable <BudgetReport> report) { var excelApp = new Excel.Application(); // Make the object visible. excelApp.Visible = true; object misValue = System.Reflection.Missing.Value; // Create a new, empty workbook and add it to the collection returned // by property Workbooks. The new workbook becomes the active workbook. // Add has an optional parameter for specifying a praticular template. // Because no argument is sent in this example, Add creates a new workbook. Excel.Workbook workBook = excelApp.Workbooks.Add(misValue); // This example uses a single workSheet. The explicit type casting is // removed in a later procedure. Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet; // Establish column headings in cells A1 and B1. workSheet.Cells[1, "A"] = "Category"; workSheet.Cells[1, "B"] = "Total Amount"; var row = 1; foreach (var budget in report) { row++; workSheet.Cells[row, "A"] = budget.Category; workSheet.Cells[row, "B"] = budget.TotalAmount; if (!String.IsNullOrEmpty(budget.Notes)) { Excel.Range notesCell = excelApp.Application.get_Range("B" + row); Excel.Comment comment = notesCell.AddComment(); comment.Shape.TextFrame.AutoSize = true; comment.Text(budget.Notes); } } workSheet.Columns[1].AutoFit(); workSheet.Columns[2].AutoFit(); //workBook.SaveAs(GetTempPath() + fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); //workBook.Close(true, misValue, misValue); //excelApp.Quit(); //Marshal.ReleaseComObject(workSheet); //Marshal.ReleaseComObject(workBook); //Marshal.ReleaseComObject(excelApp); }
public CellUpdate(Excel.Range cell, Enums.CellChangeType type) { Excel.Comment comment = null; try { Row = cell.Row; Col = cell.Column; Worksheet = cell.Worksheet.Name.ToUpper(); this.TypeEnum = type; switch (TypeEnum) { case Enums.CellChangeType.Value: val = cell.Formula == null ? "" : cell.Formula.ToString(); break; case Enums.CellChangeType.Comment: comment = cell.Comment; val = comment == null ? "" : comment.Text(); break; } UpdateWSBounds(); } catch (Exception ex) { GlobalFunctions.ErrorLog(ex); throw; } finally { if (comment != null) { Marshal.ReleaseComObject(comment); } } }
private Boolean RefreshSheet(CellUpdate uc) { Excel.Worksheet ws = null; Excel.Range thisRange = null; Excel.Comment comment = null; try { ws = GlobalFunctions.findWorksheetByName(uc.Worksheet); if (ws == null) { throw new Exception("Worksheet not found: " + uc.Worksheet); } GlobalFunctions.WaitForApplicationReady(); thisRange = GlobalFunctions.createRange(ws, uc.Row, uc.Col); if (thisRange != null) { CellUpdate oldCell = new CellUpdate(thisRange, uc.TypeEnum); if (!uc.Equals(oldCell)) { switch (uc.TypeEnum) { case Enums.CellChangeType.Value: thisRange.Formula = uc.val; break; case Enums.CellChangeType.Comment: comment = thisRange.Comment; if (comment == null) { thisRange.AddComment(uc.val); } else { if (String.IsNullOrEmpty(uc.val)) { thisRange.ClearComments(); } else { comment.Text(uc.val); } } break; } } GlobalFunctions.InfoLog("Received", uc); //RefreshedCell rc = new RefreshedCell(thisRange, uc, oldCell.val); //RefreshedCell rc = new RefreshedCell(thisRange, uc, ""); Vars.LatestUpdateTime = GlobalFunctions.MaxDate(Vars.LatestUpdateTime, uc.changeTime.AddSeconds(-1)); } else { Marshal.ReleaseComObject(thisRange); } return(true); } catch (Exception ex) { GlobalFunctions.ErrorLog(ex, uc); throw ex; } finally { if (thisRange != null) { Marshal.ReleaseComObject(thisRange); } if (ws != null) { Marshal.ReleaseComObject(ws); } } }
/// <summary> /// 获取批注文本 /// </summary> /// <returns></returns> public string GetCommentText(Excel.Comment comment) { return(comment.Text(Type.Missing, Type.Missing, Type.Missing)); }