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