Use vsto to copy and save some EXCEL cells (in the format) to another Excel file.

This article from: http://www.cnblogs.com/zhaojunqi/archive/2009/04/23/1442124.html

 

Recently, vsto was used to create a small project. One of the requirements is to save many areas with formats in an Excel worksheet to a separate EXCEL file, and retain the source format.
Although the requirements are simple, there are several technical points to understand:
1. Format replication, but only copying values and formats, without copying formulas, references, verification, and so on.
2. Save as Excel
First, I thought it was very simple at first. It was not that simple to directly use the selective paste and paste everything. Start using the followingCode:

Pastespecial
WS. get_range (begincell, pasterange). pastespecial

(Microsoft. Office. InterOP. Excel. xlpastetype. xlpasteall,

Microsoft. Office. InterOP. Excel. xlpastespecialoperation. xlpastespecialoperationnone,

Type. Missing, type. Missing );

 

However, we found that all the copied values were not copied in the format. So I studied Microsoft. Office. InterOP. Excel. xlpastetype and found that there were more than a dozen options. I tried them one by one and found that none of the enumerated values could meet my requirements. Later, I tried two copies to solve the problem, one copy value and one copy format! The Code is as follows:

Pastespecial
WS. get_range (begincell, pasterange). pastespecial

(Microsoft. Office. InterOP. Excel. xlpastetype. xlpastevalues,

Microsoft. Office. InterOP. Excel. xlpastespecialoperation. xlpastespecialoperationnone,

Type. Missing, type. Missing );
WS. get_range (begincell, pasterange). pastespecial

(Microsoft. Office. InterOP. Excel. xlpastetype. xlpasteformats,

Microsoft. Office. InterOP. Excel. xlpastespecialoperation. xlpastespecialoperationnone,

Type. Missing, type. Missing );

 

The second problem is not difficult. It is mainly because different object models use different storage methods (the WB object is Microsoft. office. interOP. excel. workbook object. I used Microsoft in my previous experiments. office. interOP. excel. applicationclass). After testing, the workbook object's savecopyas method is ideal because it does not pop up any dialog box and automatically overwrites existing files with the same name.
The complete code is listed below:

Commandbarbutton
Void Creatcommand_click (Microsoft. Office. Core. commandbarbutton Ctrl, Ref   Bool  

Canceldefault)
{
// Start a new Excel process, but it is not displayed
Clipboard. Clear ();
Globals. sheet1.application. screenupdating =   False ;
Microsoft. Office. InterOP. Excel. Application app =   New  

Microsoft. Office. InterOP. Excel. Application ();
App. Visible =   False ;

// Repeat and save it
For ( Int I =   1 ; I <   14 ; I ++ )
{
// Obtain the file name and copy area.
Globals. sheet10.range [ " O1 " , Type. Missing]. value2 = I;
String Filename = Globals. thisworkbook. Path + Utility. creatfilename

(Globals. sheet1.range [ " B5 " , Type. Missing]. value2.tostring (), globals. sheet3.range [ " B2 " ,

Type. Missing]. value2.tostring (), globals. sheet1.range [ " C5 " , Type. Missing]. value2.tostring

());
String Strpasterange = Utility. selectandcopyrange ( " B " , 5 , 9 , " N " , 22 );

// Save as Method
Utility. creatandpaste (app, strpasterange, " B5 " , Filename );
}

// Close the Excel Process
Globals. sheet1.application. screenupdating =   True ;
App. Quit ();

MessageBox. Show ( " Success! " );
}

 

The utility. selectandcopyrange method is used to copy the specified region. The code is simple and the core code is as follows:

Globals. sheet1.range [begincell + Begincellnamber. tostring (), strpasterange]. Copy

(Type. Missing );

 

The utility. creatandpaste method is saved as follows:

 

Creatandpaste
Public   Static   Void Creatandpaste (Excel. Application app, String Pasterange, String Begincell, String Filename)
{
// Use a new Excel process to add a new workbook
Microsoft. Office. InterOP. Excel. Workbook WB = App. workbooks. Add (type. Missing );
Excel. worksheet WS = (Excel. worksheet) WB. worksheets [ 1 ];

// Paste
ws. get_range (begincell, pasterange ). pastespecial (Microsoft. office. interOP. excel. xlpastetype. xlpastevalues, Microsoft. office. interOP. excel. xlpastespecialoperation. xlpastespecialoperationnone, type. missing, type. missing);
ws. get_range (begincell, pasterange ). pastespecial (Microsoft. office. interOP. excel. xlpastetype. xlpasteformats, Microsoft. office. interOP. excel. xlpastespecialoperation. xlpastespecialoperationnone, type. missing, type. missing);
ws. get_range (begincell, pasterange ). columns. autofit ();
// Save
WB. savecopyas (filename );

// Close a workbook
Clipboard. Clear ();
WB. Close ( False , Type. Missing, type. Missing );

}