There could be several reasons why writing data to Excel using VSTO is slow. Here are a few possible solutions to improve the performance:
-
Use the Excel API efficiently: Make sure you are using the Excel API efficiently. For example, instead of writing data to each cell individually, consider writing the data to an array and then copying the array to Excel in one go. This can significantly reduce the time it takes to write data to Excel.
-
Avoid unnecessary calculations: Excel performs calculations automatically whenever a cell's value changes. If you have a lot of formulas in your Excel workbook, this can slow down the performance. To improve performance, consider turning off automatic calculations while you are writing data to Excel, and then turn them back on when you are done.
-
Use batch processing: If you are writing a large amount of data to Excel, consider using batch processing. This means writing data in chunks rather than all at once. This can help to reduce the memory footprint and improve performance.
-
Optimize your code: Make sure your VSTO code is optimized for performance. For example, avoid unnecessary loops and use efficient data structures.
-
Use a different approach: Consider using a different approach to writing data to Excel, such as using the Open XML SDK or the ExcelDataReader library. These libraries can provide better performance for some use cases.
-
Use the "AutoFlush" property: By default, VSTO buffers data in memory and writes it to the worksheet in batches. This can result in slower write speeds, especially when writing large amounts of data. You can improve performance by setting the "AutoFlush" property to "true", which will force VSTO to write data to the worksheet immediately.
-
Use the "Value2" property: When writing data to Excel using VSTO, you have the option of using either the "Value" or "Value2" property. The "Value" property is more flexible, but it is also slower. The "Value2" property is faster, but it has some limitations (e.g., it cannot write rich text). If you don't need the flexibility of the "Value" property, try using "Value2" instead.
-
Use arrays: If you need to write a large amount of data to an Excel worksheet, it is often faster to use arrays instead of writing data cell by cell.
-
Use the
Range.Value
property instead of
Range.Formula
property: When writing data to Excel, it is faster to use the
Range.Value
property instead of
Range.Formula
property, because the latter requires Excel to parse the formula and calculate the result.
-
Disable screen updating and calculation: When you write a large amount of data to Excel, turning off screen updating and calculation can significantly improve performance. You can do this by setting the
Application.ScreenUpdating
and
Application.Calculation
properties to
False
.
-
Use arrays instead of loops: When writing a large amount of data to Excel, it is often faster to use arrays instead of looping through each cell. You can read the data into an array, manipulate it, and then write it back to Excel in one operation.
-
Use the
ExcelPackage
class: If you are using Excel 2007 or later, you can use the
ExcelPackage
class to write data to Excel. This class is optimized for performance and can write data much faster than the
Excel.Application
object.
-
Use a BackgroundWorker or ThreadPool: To avoid freezing the UI of your application while writing data to Excel, you can use a
BackgroundWorker
or
ThreadPool
to perform the operation in the background.
Overall, it's important to identify the specific cause of slow performance before trying to optimize your code. Profiling your code and using performance monitoring tools can help you identify bottlenecks and improve performance.
Dim dt As New DataTable
Dim
Excel
App As New
Excel
.Application
Dim WorkBook As
Excel
.Workbook
Dim WorkSheet As
Excel
.Worksheet
Dim dt As New DataTable
严重性 代码 说明 项目 文件 行 禁止显示状态
错误 签名时出错: 未能对 bin\Debug\app.publish\\setup.exe 签名。SignTool Error: No certificates were found that met all the given criteria.
重新创建证书
Excel
VBA Dictionary - A Complete Guide -
Excel
Macro Mastery
A Quick Guide to the VBA Dictionary
Function
Params
Early binding reference
“Microsoft Scripting Runtime”
(Add using Tools->References from the VB menu)
Declare (early binding)
var sourceSheet=wb.Worksheets["模板表"];
Worksheet addSheet = wb.Worksheets.Add();
//sheet拷贝完，激活的sheet就是addSheet
sourceSheet.Copy(addSheet);
Worksheet activeSheet = workbook.ActiveSheet;
//给sheet改名
activeSheet.Name = priceName;
参考【btn_Save_Click_1】
NewWorkbook.SaveAs(savepath,
Excel
.XlFileFormat.xlWorkbookDefault, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing);
namespace Microsoft.Office.Interop.
Excel
// 摘要:
public class CustomResourceRequestHandler : ResourceRequestHandler
protected override CefReturnValue OnBeforeResourceLoad(IWebBrowser chromiumWebBrowser, IBrowser browser, IFrame frame, IRequest request, IRequestCallback callback)
var .
Excel
.Application app;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
app = Globals.ThisAddIn.Application;
app.SheetSelectionChange += App_SheetSelectionChange;
private void App_SheetSelectionChange(object Sh,
Excel
.Range T.
