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);
}
}
}
private void button1_Click(object sender, RibbonControlEventArgs e)
{
MySqlConnection myconn = null;
MySqlCommand mycom = null;
MySqlDataReader sdr;
Excel.Worksheet activeWorksheet = (Globals.ThisAddIn.Application.ActiveSheet);
try {
//MySqlDataAdapter myrec = null;
myconn = new MySqlConnection("Host =192.168.56.2;Database=MEPurchase;Username=root;Password=123456;Charset=utf8;");
myconn.Open();
mycom = myconn.CreateCommand();
Excel.Range selectedrange = Globals.ThisAddIn.Application.Selection;
foreach (Excel.Range cel in selectedrange)
{
if (cel.Value2 != null)
{
string keyword = cel.Value2.ToString();
keyword = new Regex("[\\s]+").Replace(keyword, "%");
string sql = string.Format(@"select
ordernumber, name, type, currency, price, provider
from vprice where type like {0} limit 1"
, "'%" + keyword + "%'");
mycom.CommandText = sql;
sdr = mycom.ExecuteReader();
sdr.Read();
cel.ClearComments();
SqlReturn sqlrt = new SqlReturn();
try { sqlrt.ordernumber = sdr.GetString(0); }
catch { };
try { sqlrt.name = sdr.GetString(1); }
catch { };
try { sqlrt.type = sdr.GetString(2); }
catch { };
try { sqlrt.currency = sdr.GetString(3); }
catch { };
try { sqlrt.price = sdr.GetFloat(4); }
catch { };
try { sqlrt.provider = sdr.GetString(5); }
catch { };
sdr.Close();
Excel.Comment c = cel.AddComment(string.Format("订货号：{0}\n名称 ：{1}\n型号 ：{2}\n币种 ：{3}\n单价 ：{4}\n供应商：{5}\n",
sqlrt.ordernumber, sqlrt.name, sqlrt.type, sqlrt.currency, sqlrt.price, sqlrt.provider));
c.Shape.TextFrame.AutoSize = true;;
}
}
}
catch (Exception ee) {
MessageBox.Show(ee.ToString());
}
}
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);
}
/// <summary>
/// 获取指定工作表所有批注
/// </summary>
/// <param name="ws"></param>
/// <returns></returns>
public IList <Excel.Comment> GetAllComments(Excel.Worksheet ws)
{
IList <Excel.Comment> cms = new List <Excel.Comment>();
System.Collections.IEnumerator cmenum = ws.Comments.GetEnumerator();
while (cmenum.MoveNext())
{
Excel.Comment tcm = cmenum.Current as Excel.Comment;
if (tcm != null)
{
cms.Add(tcm);
}
}
return(cms);
}
public bool Resolve(object sender, IBindingContextElement element)
{
ExcelInterop.Range concernedRange = sender as ExcelInterop.Range;
if (concernedRange == null)
{
return(false);
}
try
{
ExcelInterop.Range concernedRangeFirstCell = concernedRange.Cells[1, 1];
// We delete the previous concernedRange comment
ExcelInterop.Comment comment = concernedRangeFirstCell.Comment;
comment?.Delete();
// Invoke decorator resolver
object result = EventCallbacksManager.DecoratorInvoke(Callback, concernedRange, element.DataSource, null);
if (result != null)
{
string commentStr = result as string;
if (!string.IsNullOrEmpty(commentStr))
{
concernedRange.AddComment(commentStr);
ExcelInterop.Comment addedComment = concernedRange.Comment;
ExcelInterop.Shape shape = addedComment.Shape;
ExcelInterop.TextFrame textFrame = shape.TextFrame;
textFrame.AutoSize = true;
}
return(commentStr != null);
}
return(false);
}
catch (Exception ex)
{
log.LogExceptionFormat(LogType.Error, ex, $"Cannot resolve decorator2 '{Ident}':{ex.Message}");
return(false);
}
}
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);
}
}
}
public object GDA(ExcelInterop.Range caller, string dataType, object[] parameters)
{
if (caller == null)
{
return(null);
}
if (ETKExcel.ExcelApplication.IsInEditMode())
{
return("#Edit Mode");
}
if (test == null)
{
IExcelTemplateView view = ETKExcel.TemplateManager.AddView("Templates Customer", "AllCustomers", caller.Worksheet.Name, caller.Address);
test = new ExcelRequestDefinition("Test", "Ceci est un test", view as ExcelTemplateView);
ExcelInterop.Comment comment = caller.Comment;
if (comment != null)
{
comment.Delete();
}
caller.Application.Application.Caller.AddComment(test.Description);
}
ExcelInterop.Range firstOutputCell = (caller as ExcelInterop.Range).Offset[++yOffset, ++xOffset];
(test.View as ExcelTemplateView).FirstOutputCell = firstOutputCell;
try
{
//if (parameters.Length < 2)
// return "#N/A";
ETKExcel.TemplateManager.ClearView(test.View);
ExcelApplication application = (ETKExcel.TemplateManager as ExcelTemplateManager).ExcelApplication;
application.PostAsynchronousAction(() =>
{
try
{
application.PostAsynchronousAction(() => (test.View as ExcelTemplateView).FirstOutputCell.Value2 = "#Retrieving Data");
Task task = new Task(() =>
{
//Thread.Sleep(5000);
test.View.SetDataSource(parameters[0]);
application.PostAsynchronousAction(() => test.View.FirstOutputCell.Value2 = string.Empty);
application.PostAsynchronousAction(() => ETKExcel.TemplateManager.Render(test.View));
});
task.Start();
}
catch (Exception ex)
{
string errorMessage = $"#ERR:{ex.Message}.{(ex.InnerException == null ? string.Empty : ex.InnerException.Message)}";
application.PostAsynchronousAction(() => (test.View as ExcelTemplateView).FirstOutputCell.Value2 = errorMessage);
}
});
return(test.Name);
}
catch (Exception ex)
{
return($"#ERR: {ex.Message}");
}
}
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));
}
/// <summary>
/// 获取批注文本
/// </summary>
/// <param name="ws"></param>
/// <param name="rowIndex"></param>
/// <param name="columnIndex"></param>
/// <returns></returns>
public string GetCommentText(Excel.Worksheet ws, int rowIndex, int columnIndex)
{
Excel.Comment comment = GetComment(ws, rowIndex, columnIndex);
return(GetCommentText(comment));
}
/// <summary>
/// 获取批注文本
/// </summary>
/// <param name="sheetname"></param>
/// <param name="rowIndex1"></param>
/// <param name="rowIndex2"></param>
/// <returns></returns>
public string GetCommentText(string sheetname, int rowIndex, int columnIndex)
{
Excel.Comment comment = GetComment(sheetname, rowIndex, columnIndex);
return(GetCommentText(comment));
}
private async Task <int> TranslateSheet2(Excel.Worksheet currentSheet, List <Excel.Range> arRange, int numPrevRequest)
{
ActionF1 ActionF1 = new ActionF1();
var ienum = arRange.AsEnumerable();
TaskScheduler tsc = TaskScheduler.Current;
int i = 0;
Excel.Range whatIR = null;
int limit = arRange.Count;
limit = Math.Min(limit, _iMaxRequest - numPrevRequest);
for (i = 0; i < limit; i++)
{
whatIR = arRange[i];
//DataTable orgTa = whatIR.GetDataTable(SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders); //error convert type double (set columntype = type of first cell
DataTable orgTa = GetTableFromIrange(whatIR);
string originalText = GetTextFromTable2(orgTa);
if (originalText.Length > 5000)
{
continue;
}
JArray jarr = await ActionF1.GetSingle(originalText, _fromLang, _toLang);
toolStripProgressBar1.Value = (int)((i + 1) * 100 / limit);
Thread.Sleep(480);
List <string> transateText = ActionF1.ReadJArrayRes2(jarr);
////fake start
//JArray jarr = await FakeTrans(originalText);
//List<string> transateText = null;
////fake end
DataTable traTa = GetTableFromText(transateText, orgTa);
//translate sucess
if (traTa != null)
{
for (int im = 1; im <= traTa.Rows.Count; im++)
{
object vv = traTa.Rows[im - 1][0];
//string[] speStart = new string[] {"\n", "「" , "\"","“",};
if (vv != null && vv.ToString().Trim().Length > 1)
//&& (System.Text.RegularExpressions.Regex.IsMatch(vv.ToString().Trim().Substring(0, 1), @"[a-zA-Z0-9]") || speStart.Contains(vv.ToString().Substring(0, 1))))
{
if (currentSheet.Range[whatIR.Address].Cells[im, 1].Comment != null)
{
currentSheet.Range[whatIR.Address].Cells[im, 1].ClearComments();
}
currentSheet.Range[whatIR.Address].Cells[im, 1].AddComment(vv.ToString());
Excel.Comment ic = currentSheet.Range[whatIR.Address].Cells[im, 1].Comment;
ic.Shape.TextFrame.AutoSize = true;
//using (Graphics g = this.CreateGraphics())
//{
// string item = ic.ToString();
// SizeF sizeF = g.MeasureString(item, Font);
// ic.Shape.Width = sizeF.Width;
//}
}
}
}
else
{
//remain originalText
}
}
return(limit + numPrevRequest);
}
static void Main(string[] args)
{
Console.WriteLine("Interop Assemblies Performance Test - 15000 Cells.");
Console.WriteLine("Write simple text, change Font, NumberFormat, WrapText and add a comment.");
// start excel, and get a new sheet reference
Excel.Application excelApplication = CreateExcelApplication();
Excel.Workbooks books = excelApplication.Workbooks;
Excel.Workbook book = books.Add(Missing.Value);
Excel.Sheets sheets = book.Worksheets;
Excel.Worksheet sheet = sheets.Add() as Excel.Worksheet;
// do test 10 times
List <MarshalByRefObject> comReferencesList = new List <MarshalByRefObject>();
List <TimeSpan> timeElapsedList = new List <TimeSpan>();
for (int i = 1; i <= 10; i++)
{
sheet.UsedRange.ClearComments();
DateTime timeStart = DateTime.Now;
for (int y = 1; y <= 15000; y++)
{
string rangeAdress = "$A" + y.ToString();
Excel.Range cellRange = sheet.get_Range(rangeAdress);
cellRange.Value = "value";
Excel.Font font = cellRange.Font;
font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.WrapText = true;
Excel.Comment sampleComment = cellRange.AddComment("Sample Comment");
comReferencesList.Add(font as MarshalByRefObject);
comReferencesList.Add(sampleComment as MarshalByRefObject);
comReferencesList.Add(cellRange as MarshalByRefObject);
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
foreach (var item in comReferencesList)
{
Marshal.ReleaseComObject(item);
}
comReferencesList.Clear();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test3-Interop.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(books);
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
}