对具有特定值的单元格进行计数-线程通信 [英] Count cells with specific value - Threading communication
问题描述
我需要知道在特定列(列"C")中具有特定值("DE"或"CH"或"AT")的行数
I need to know the number of rows, which have specific value ("DE" or "CH" or "AT") in a specific column (column "C")
目前，我正在使用for循环，但是可以解决的问题是，我的程序需要近10分钟才能浏览excel工作表的4万行.
At the Moment I'm working with a for-loop, this works, but the problem is, that my program needs nearly 10 minutes to get through the 40k rows of my excel sheet
有没有一种方法可以更快地获取此信息?
Is there a way to get this information faster?
我现在遇到问题了，我有3个与同一Excelsheet一起工作的线程，当我等待"//MessageBox.Show("Es gibt" + Convert.ToString(count)+"Verträgefür"+ searchword +"！);"所有线程，并在所有线程就绪后单击确定". 不等时，我从Excel收到2条提示(下图)，程序崩溃.
I have the Problem now, that I have 3 Threads which work with the same Excelsheet, it's no Problem, when I wait for the "//MessageBox.Show("Es gibt " + Convert.ToString(count) + " Verträge für " + searchword + "!");" for all threads and click OK when all of them are ready. When I don't wait for it, I get 2 prompts from Excel(picture below) and the Programm crashes.
如我所见，当第一个线程准备就绪时，它会关闭excelsheet和App，而其他线程对此有问题吗? 程序崩溃时，它说来自HRESULT的异常:0x800AC472"(注释) 任何的想法?
As I see it, when the first thread is ready, it closes the excelsheet and App and the other threads have problems with that or something? When the Programm Crashes it says "Exception from HRESULT: 0x800AC472" (commented) Any idea?
下一个问题是:我使用一个.txt文件，以便从线程向主窗体获取信息，是否有什么好方法可以将信息返回至主窗体或让线程彼此通信?这个结论不是很好:/
Next Question is: To get information from the Thread to the main Form, I use a .txt file, is there any good way to get information back to the main Form or let the threads communicate with each other? This conclusion is not really good :/
class RowCheckThread
{
public RowCheckThread()
{
}
public void asdf()
{
string localrow = row;
string localsearchword = searchword;
string localfile = file;
Excel.Application ExcelApp = new Excel.Application();
Excel.Workbook ExcelWorkBook = ExcelApp.Workbooks.Open(localfile);
Excel.Sheets ExcelSheets = ExcelWorkBook.Worksheets;
Excel.Worksheet Sheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
Excel.Range allCellsInColumn = Sheet.get_Range(localrow + ":" + localrow);
//
Excel.Range usedCells = allCellsInColumn.Find(localsearchword, LookAt: Excel.XlLookAt.xlWhole, SearchOrder: Excel.XlSearchOrder.xlByRows, SearchDirection: Excel.XlSearchDirection.xlNext);//Sucht ersten wert mit searchwort
string firstFound = usedCells.get_Address();
Excel.Range next = allCellsInColumn.FindNext(usedCells);
string nextFound = next.get_Address();
int count = 1;
while (nextFound != firstFound)
{
next = allCellsInColumn.FindNext(next);//Exception from HRESULT: 0x800AC472
nextFound = next.get_Address();
count++;
if (KeepAlive == false)
{
ExcelWorkBook.Close();
ExcelApp.Quit();
return;
}
}
//MessageBox.Show("Es gibt " + Convert.ToString(count) + " Verträge für " + searchword + "!");
ExcelWorkBook.Close();
ExcelApp.Quit();
string path = System.IO.Path.GetTempPath() + "test.txt";
int wert = 0;
if(File.Exists(path))
{
StreamReader myFile = new StreamReader(path, System.Text.Encoding.Default);
wert = Convert.ToInt32(myFile.ReadToEnd());
myFile.Close();
}
wert = wert + count;
StreamWriter tempfile = new StreamWriter(path);
tempfile.Write(wert);
tempfile.Close();
}
public string row { get; set; }
public string searchword { get; set; }
public string file { get; set; }
public bool KeepAlive { get; set; }
}
推荐答案
如果您仍要自动执行Excel，则最好使用WorksheetFunction.CountIf.
If you're automating Excel anyway, you might as well use WorksheetFunction.CountIf.
使用此功能，您只需给出范围和匹配条件即可.
Using this function, you just give the range and the match criterion.
呼叫看起来像这样:
Application.WorksheetFunction.CountIf(yourRange, "DE");
如果40,000个单元格的范围花费了几秒钟多的时间，我会感到惊讶.
I'd be surprised if this took more than a few seconds for a 40,000 cell range.
该功能的文档为这里.
这篇关于对具有特定值的单元格进行计数-线程通信的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
