Excel integration, excel Integration

Excel integration, excel Integration

Recently, some projects are integrated with Excel. The problem is as follows:

How to operate in Win

How to handle the Excel Process

How to Create AddIn

How to apply in the Web

Other ..

 

//

WinApplication under

Excel. Application _ excelApp = new Excel. Application ();

Excel. _ Workbook reportWB = null;

Excel. _ Worksheet reportWS = null;

Try

{
ReportWB = (Excel. _ Workbook) Schedule. excelApp. workbooks. _ Open (fileReport, 0, false, 5, "", "", true, Excel. xlPlatform. xlWindows, "\ t", false, false, 0, true );

ReportWS = (Excel. _ Worksheet) reportWB. Worksheets ["Sheet1"];

............

.........

.........

ReportWB. Save ();

}

Marshal. ReleaseComObject (reportWB );

Marshal. ReleaseComObject (reportWS );

ReportWB = null;

ReportWS = null;

ExcelApp. Workbooks. Close ();

ExcelApp. Quit ();
System. Runtime. InteropServices. Marshal. ReleaseComObject (ExcelApp );
GC. Collect ();

However, it is troublesome to close the Excel process.

Simple operations

Assignment:

ReportWS. get_Range ("D3", "D3"). Value2 = "xxx ";

Insert row and column:

(ReportWS. Rows [beginRow, Missing. Value] as Excel. Range). Insert (Excel. XlDirection. xlDown, Missing. Value );

Copy row and column

ReportWS. get_Range ("A3", "I4"). Copy (Missing. Value );

Paste row and column:

ReportWS. get_Range ("A4" "I5"). PasteSpecial (Excel. XlPasteType. xlPasteAll, Excel. XlPasteSpecialOperation. xlPasteSpecialOperationNone, Missing. Value, Missing. Value );

Formula:

ReportWS. get_Range ("D3", "D3"). Value2 = "= sum (D1: D2 )";

There are also Excel class libraries provided by other experts, but sometimes you need to do some fine-grained control on your own, so you generally do these operations on your own.

Note:

1: The cell format can be attached to the copy operation.

2: Set the circular reference of the formula. Otherwise, there will be annoying prompts.

About installation:

1: You need to install an XP interface oxpPIA. Download from Microsoft website

2: Decompress oxppia. during registration, a program named gacutil.exe is released.

 

About closing ExcelProcess Problems

In Windows/Web, an Excel Application object can be generated. However, this object may encounter problems when it is disabled. The main reason is that even if you release an object, it is difficult to disable this instance. Every time I go to TaskMan to check whether it is still in the memory.

Solution

1: use only one global Application object. If it cannot be closed, it will not be closed. Use this App to open the new Worbook. In this way, you only need to close the Workbook and Worksheet. Only one instance does not occupy a lot of resources.

2: completely shut down the process

Process [] excelPro;

ExcelPro = Process. GetProcessesByName ("Excel ");

Foreach (Process myProcess in excelPro)

{

MyProcess. Kill ();

}

It's a little violent, but it works.

 

Create Add-In

Create a project:

There is a template in vs. studio, And it is relatively easy to create an AddIn.

At the beginning, I used the Express version, and I had to write the code manually. This ctor inherits an implementation from Extensibility. IDTExtensibility2.

In fact, the key lies in that Connector. You can also register this control. After the development in VB, Excel recognized him as a Dcom, but it was not recognized by C. It's strange. Finally, you can write the Registry directly.

Debugging:

Debugging is not a problem in vs. studio. You only need to set the project debug startup parameter: Project/attribute/Debug/Start Action/Start external program/select the location of excel.exe.

WebApplication under

Write Excel at the bottom of the Web. In addition to the operations under Win, there are other methods to achieve this, that is, directly write Response output, and then evolve to write Excel Data in Html format.

1: directly output to Response. This method directly generates an Html page and changes the type of the page to application/ms-excel, IE on the client will open the html page in Excel. Specific can see http://www.cnblogs.com/caizinet/

If this method is used for output, all content on the page can be directly output to Excel. Including various formats and data.

However, there is also a small problem: Sometimes everything on the interface is displayed in Excel.

2: directly output the file to the. xls file so that the file is stored by the customer.

The exported. xls file is also implemented in html, that is, the html data is directly saved as an xls file. In this way, the user can directly access this file.

The output data can be directly <Table>:

A :) strData = "<Table> .............. "

B: string file, File. Write (strData)

C :) Response. Redirect (file );

Another interesting thing about this implementation is that formulas can be used directly. <Table> <td> = Sum (A1: A3) </td> </table>

 

More: Use macros instead of IEClient

A friend is developing a program. This is because the processed data is too large, which leads to the situation where IE occupies nearly MB of memory after being loaded. Later, he used macro processing and directly used Excel as the client. This is also a good method.

What is the 'excel integration toolbox 'available? Feeling unstable

The Excel integration toolkit is an enhanced application-type plug-in written in Visual Basic for Applications. Includes 160 menu functions and about 100 custom functions. It combines 160 tools, but is smaller than 15 MB. After the integration toolbox is installed, if you use Excel 2003, the toolbox menu is generated, including more than 160 sub-menus. If you use Excel 2007 or 2010, the integration and Toolbox functional areas are generated. According to the features of each function, 18 sub-menus are classified, and about 100 new functions are generated in the Function Wizard dialog box to expand the Excel computing function. All functions are applicable to Excel 2002, 2003, 2007, and 2010. Supports both Chinese and English display and traditional and simplified operating systems, including IKEA/Win7 and XP interfaces, over 20 gorgeous skin interfaces, and all tabs hidden by Excel2007, this plug-in also simulates an Excel2003 menu to make it easier for friends who have switched from Excel2003 to version 2007 or 2010. It is estimated that the offi version you installed is incorrect.

Excel integrates three functions: Table processing and ____.

B. database processing C. Graphic Processing