How to use C # To create an Excel addin

Addin Chinese translation is a plug-in, sometimes also called plug in. In my understanding, accordingProgramInterfaces provided by developers, allowing programs to load customCodeFragment to complete a function.
This requirement stems from a friend's request, who gave me the reason for the demand.
The quality department of a friend's factory needs to inspect a large number of product components every day and make the inspection data into reports. This is the daily work of QC surveyors. Due to the recent increase in company orders and the increase in production tasks, surveyors need to detect a large number of product parts each day and generate a large amount of measurement data. QC surveyors also need to organize the data into a report in the specified format and submit it to the team lead for review. The increase in orders will increase the workload of QC surveyors. He asked me if I could reduce the workload of QC surveyors and improve work efficiency.

From his description, we can see that two tasks are repeated: Measuring parts and making reports. I have no way to improve the efficiency of measuring parts, unless it is specified that only part parts are inspected, not all. The first process is difficult to optimize when all tests are required by the factory. For the second process, make the measurement data into a report. This step seems to allow programs to improve efficiency. Because the format of the report to be created is specified, you only need to import the measurement result data to the report in the specified format, and the second step will be completed smoothly.
The report is in Excel format. The layout of each column in each row is determined. You only need to place the specified data in the corresponding column position.

The item column is the part name, and the mean column is the measurement data of the corresponding part.
The data format of the parts exported by the measuring machine supports common CSV, XML, and TXT formats. The exported data looks like this in Excel.

I chose to export the measurement data in CSV format, which is easy to view and analyze. If the XML format is used, most data employees cannot understand it and it is inconvenient to view it. CSV is the first choice for exporting data of this type of plane structure (relative to the hierarchy.
Next, we will analyze how to quickly make the measurement data into a report.

Most Excel users are familiar with the formulas and can make reports very professionally and quickly. I will try to solve the problem by using the formula first.
This requirement is described by the formula: Find the value in the specified region, and then return the specified value to the corresponding data column.
The Vlookup function of Excel can achieve my goal. The formula Vlookup (C1, A: B, 2,) is used to search for the data C1 from column A to Column B, and displays the data of Column B corresponding to it.
However, the Report and measurement data are stored in two files respectively, and the measurement data cannot be stored in the report file. You need to add the measurement data to the report file first, then use the formula above to find the matching value, and then delete the worksheet where the measurement data is located. If there are many such reports to be done, you need to add and delete worksheet repeatedly. Although the problem can be solved, it is a bit imperfect and it is inconvenient for users to operate. This solution can solve the problem, but it is not perfect,

Try using VBA. There are quite a few documents about VBA on the Internet. VBA is equivalent to the program method. My idea is to first read the measurement data file, put it in a dictionary-like structure (hashtable, dictionary), and then read the parts that need data in the report cyclically, obtain the corresponding measurement data and assign it to the corresponding cell. I have never studied VBA and I don't know how to start with it. The keyword "how to read a file using VBA" was searched online for a long time and the related content was not found. This solution was not implemented by programming and had to be abandoned.

consider using vsto. This component is installed by default in vs 2005/2008, which is equivalent to the managed VBA version. Moreover, vsto supports the C # language and can solve this problem with the most familiar technology. In C #, reading a file is quite easy, and a streamreader can solve the problem of reading the file.
open vs2008 and create a workbook project in Excel 2003.
The Wizard creates a class whose subject code is as follows
Public partial class thisaddin
{< br> private void thisaddin_startup (Object sender, system. eventargs e) {}< br> private void thisaddin_shutdown (Object sender, system. eventargs e) {}< BR >}< br> You can guess that startup and shutdown are events executed when the plug-in is loaded and shut down.
In thisaddin_startup, load interface.
you can add a menu or a tool button. The effect of adding a menu is as follows
the code is written as
private void thisaddin_startup (Object sender, system. eventargs e)
{< br> # region vsto generated code

This. Application = (Excel. Application) Microsoft. Office. Tools. Excel. excellocale1033proxy. Wrap (typeof (Excel. Application), this. application );

# Endregion
This. addmenu ();
}
Office. commandbarcontrol menutop;
Office. commandbarcontrol menucreatemail = NULL;

Private void addmenu ()
{
Office. commandbar bar = This. application. commandbars. activemenubar;

Menutop. Caption = "yihu Tools ";

Office. commandbarpopup commandbarpopuptmp = menutop. Control as office. commandbarpopup;
Menucreatemail. Caption = "import data ";

Office. commandbarbutton buttonmenu = menucreatemail as office. commandbarbutton;
Buttonmenu. Click + = new Microsoft. Office. Core. _ commandbarbuttonevents_clickeventhandler (buttonmenu_click );

}
Here is a tip. If menutop or menucreatemail is defined in the addmenu method, after opening the Excel file, click the button multiple times and it is executed only once. Unless you restart excel.
The Event code for clicking "import data" in the menu is as follows:
Void buttonmenu_click (Microsoft. Office. Core. commandbarbutton Ctrl, ref bool canceldefault)

{

// Analyze the CSV file and set the value of the specified report Column

}
The code is long. In the dialog box that appears, select the file containing the measurement data to be imported, analyze and import the value.
The code for importing values involves Excel operations. The Code is as follows:
Excel. worksheet sheet = (Excel. worksheet) application. activeworkbook. activesheet;
Int rowindex = 10; int maxindex = 300;
For (rowindex = 10; rowindex <maxindex; rowindex ++)
{
Excel. Range source = sheet. get_range ("A" + rowindex, "a" + rowindex );
Excel. Range target = sheet. get_range ("F" + rowindex, "F" + rowindex );
If (string. isnullorempty (source. value2.tostring ()))
Continue;
Else
{
String hashkey = source. value2.tostring ();
If (table. Contains (hashkey ))
Target. value2 = table [hashkey]. tostring ();
}
}
The table type is hashtable, which uses the part name as the key name and the measurement data of the part as the value.
Reading file code is also a bit tricky. My machine is XP SP2 in English, and Excel is 2003 in English. If you use the com InterOP API to read data files and read data files as Excel files, garbled characters often occur. If you read data files as text files, there is no problem. I cannot find the cause of this problem.

The following describes how to deploy this plug-in. The following steps are required and must be performed as an administrator:
1 on the target machine, install the. NET Framework 2.0 http://www.microsoft.com/downloads/details.aspx? Familyid = 0856eacb-4362-4b0d-8edd-aab15c5e04f5 & displaylang = en

2 install Office 2007 PIAs

Http://www.microsoft.com/downloads/details.aspx? Familyid = 59daebaa-bed4-4282-a28c-b864d8bfa513 & displaylang = en

3. Install vsto runtime 2.0:

Http://www.microsoft.com/downloads/details.aspx? Familyid = 4468d8cb-b43e-4b09-82f6-8ba3f7b5e935 & displaylang = en # filelist

4. Make sure that the sp2 patch is installed on Excel 2003. Otherwise, install the sp2 patch.

5. Compile and install the project and execute it to install the plug-in.

6. Run the command line tool to install the plug-in directory and set the Assembly permission (the Assembly name is qcexceladdin)
Caspol-machine-addfulltrust qcexceladdin. dll
After these steps, open the Excel file and you will see the plug-in menu. If you still cannot see the menu, check whether the plug-in is disabled by Excel. Activate the plug-in disalbeitems.

So far, this problem has been successfully solved. After a period of use, the surveyor needs to modify the format of the measurement data file, as shown in

The code for parsing this format is relatively simple. You can simply use the regular expression to break down the string. The format of the above version also needs to determine whether it is an odd or even line, and the odd line uses the part name, read measurement data from even rows.

The registration information of this plug-in has corresponding key-value pairs in the registry.
The path of the registry key where the plug-in is located is

Shows the value.

This method may not be the most optimal method. You are welcome to provide your ideas for your reference.