Use VSTO to copy some Excel cells (formatted) and paste and save to another Excel

Recently used VSTO to do a small project, where one of the requirements is to save a number of formatted areas of an Excel worksheet in a separate Excel file, requiring that the source format be preserved.

Although the requirements are simple, there are a few technical points to figure out:

1. With format copy, but only copy values and formats, do not copy formulas, references, checksums and so on.

2. Save As Excel

The first question, I thought it was very simple, direct use paste, paste all on the line, in fact, is not so simple. Start using the following code:

PasteSpecial

ws.get_Range(beginCell, pasteRange).PasteSpecial

(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll,

Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,

Type.Missing, Type.Missing);

But all the values were copied and the format was not copied. So the study of Microsoft.Office.Interop.Excel.XlPasteType, found that there are more than 10 options, one by one, found that the use of any single enumeration does not meet my requirements. Later brainwave, the use of two replication to solve the problem, one copy of the value, one copy format! The code is as follows:

PasteSpecial2

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);