"Original". NET read-write Excel tool Spire.xls using (2) Excel file control

　　Total contents of this blog article category: http://www.cnblogs.com/asxinyu/p/4288836.html

. NET read-write Excel tool Spire.xls use Article directory: http://www.cnblogs.com/asxinyu/p/4374032.html

Previous post: ". NET operations Excel Sharps Spire.xls Use (1) Introduction "to introduce the basic functions of spire.xls and some comparisons. This article will focus on the contents of the global control of the relevant settings for the Excel file itself when you manipulate the Excel file in C #. Because this is also C # Read and write Excel files often encountered in the article, but also I use the process of a learning step. This article will first describe the basics of spire.xls Creating and loading documents, as well as how to save files, how to combine multiple Excel files, where we can also make more detailed settings for the generated Excel files, such as setting the properties of Excel files and so on, and then many projects, not only to generate Excel files, but also Need to print and get some paging information, and finally describe how to use C # to set up encryption to protect an Excel file.

Creation, loading and saving of 1.Excel documents

In the preliminary introduction of our previous article, there is an example of Hello Excel, which actually includes the new and saved Excel file method, very simple. This section will focus on a more detailed study of each detail.

1.1 Saving an Excel file

Excle file saving is the use of workbook SaveToFile method, there are several versions, according to their own needs, according to the file name, version of Excel files, as well as file format to save, the method prototype is as follows, relatively simple no longer demonstrated, in subsequent articles in the code used.

1 //save files based on file name, document version, file format information2  Public voidSaveToFile (stringfileName);3  Public voidSaveToFile (stringFileName, excelversion version);4  Public voidSaveToFile (stringFileName, FileFormat fileformat);5  Public voidSaveToFile (stringFileName,stringseparator);

1.2 Loading Excel from different paths

Using C # to manipulate Excel files is not necessarily about generating Excel file reports, and sometimes you need to load existing data (xml,excle, etc.) to make changes and then save to the corresponding Excel file. The methods that are loaded into Excel are workbook objects LoadFromFile, Loadfromstream, LoadFromXML, and so on, with the following methods:

1 //load from file, according to file name, Protected mode and Excel version2  Public voidLoadFromFile (stringfileName);3  Public voidLoadFromFile (stringFileName,BOOLpreservemode);4  Public voidLoadFromFile (stringFileName, excelversion version);5  Public voidLoadFromFile (stringFileName,stringseparator);6  Public voidLoadFromFile (stringFileName,stringSeparatorintRowintcolumn);7  Public voidLoadFromFile (stringFileName,stringSeparatorintRowintcolumn, excelversion version);8 //loading from the data stream9  Public voidLoadfromstream (Stream stream);Ten  Public voidLoadfromstream (Stream stream,BOOLloadstyles); One  Public voidLoadfromstream (Stream stream, excelversion version); A //loading from an XML file -  Public voidLoadFromXml (Stream stream); -  Public voidLOADFROMXML (stringfileName); the //loading from a template file -  Public voidLoadtemplatefromfile (stringfileName); -  Public voidLoadtemplatefromfile (stringFileName,BOOLLoadstyles);

3.c# setting Excel file properties

File properties Although it is seldom used by people, it is still necessary to introduce the relevant attribute information if the file is distributed. Excel file properties can be clicked on the right mouse button-properties see interface, such as I use WPS to view the Excel file properties, using Office Excel to view the words, is the right look, although the display is different, but the principle is the same:

1.WPS display of Excel properties 2.Office excle file properties displayed

To set the properties of an Excel file in the DocumentProperties property of the Workbook object, this property is a xlsbuiltindocumentproperties type, In the Spire.Xls.Core.Spreadsheet.Collections namespace, some of the principles of its implementation can be found through the Object Browser of VS, which can be extended to other component uses. Take a look at a simple example:

1 #region2.Excel File Attribute Example2 Static voidExcelTest2 ()3 {            4Workbook Workbook =NewWorkbook ();5 6     //Modify the Document property information so that the company and the file's information can be displayed in the document at the time of publication7Workbook. Documentproperties.author ="Zhang San";//author8Workbook. Documentproperties.subject ="test file Properties";//Theme9Workbook. Documentproperties.title ="To test an Excel file";//titleTenWorkbook.DocumentProperties.Company ="XX Co., Ltd.";//Unit OneWorkbook.DocumentProperties.Comments ="Keep File Comments";//Reviews AWorkbook. Documentproperties.keywords ="Test Excel";//Key Words -Workbook. Documentproperties.createdtime = DateTime.Now;//creation Time -     //TODO: There are other properties that you can refer to Xlsbuiltindocumentproperties's related properties the  -     //Save the Excel file to the specified file, and you can also specify the Excel version -Workbook. SaveToFile ("Sample.xls", excelversion.version2007); - } + #endregion

Note that because I did not install Office 2007 and above, with WPS display, a bit of a problem, to use WPS to convert to 2003 version can not be seen, this may be the cause of WPS, debugging generated files found that these property values are present. So I'm not going to toss it, it's really a hassle to install Office.

4.c# Encrypt and protect Excel files

Believing that many people have used office encryption and protection, the scope of encryption and protection is more extensive for Excel: Not only can you encrypt files, but you can also protect specified sheet, specified cells, and so on. In fact, open source Npoi components can also do this, previously did not use but know, with npoi friends can see this: http://tonyqus.sinaapp.com/archives/196, since to use Spire.xls, then see how to do.

4.1 Encrypting an Excel file

The Excel file-level encryption is actually very simple, is the Workbook object protect method, passes the encryption password, if wants to cancel the password, uses the workbook. Unprotect (), note that the cancellation of encryption is not required password, Excel software operation is also, so the program does not need to operate.

1 #region3. Encrypt Excel File Example2 Static voidExcelTest3 ()3 {4Workbook Workbook =NewWorkbook ();5Workbook. Protect ("pwd111");//set Password to protect encryption: pwd1116     //Save the Excel file7Workbook. SaveToFile ("Sample.xls", excelversion.version2007);8 }9 #endregion

This is what it looks like to open the file after encryption:

4.2 Protecting sheet and cells

Sheet's protection is more flexible, using the Protect method of the worksheet object, the parameters passed are the protected password, and the optional protection type (richer protection type), take a look at the following example:

1Workbook Workbook =NewWorkbook ();2 3Worksheet sheet = workbook. worksheets[0];4 5 //Protection Sheet6Sheet. Protect ("Test", Sheetprotectiontype.all);7 8 //Save the Excel file9Workbook. SaveToFile ("sample.xlsx", excelversion.version2010);

The effect should be similar to the WPS "protect Sheet" interface, such as:

The protection of worksheets is generally to prevent misoperation or for copyright protection, can not easily change the situation. People see the situation, I seldom use it here, just understand, by the way.

1 Static voidExcelTest4 ()2 {3Workbook Workbook =NewWorkbook ();4Workbook. Createemptysheets (1);5Worksheet sheet = workbook. worksheets[0];6 7Sheet. range["A1"]. Text ="Lock";8Sheet. range["B1"]. Text ="not locked";9 TenSheet. range["A1"]. style.locked =true; OneSheet. range["B1"]. style.locked =false; A  -     //Be sure to protect the worksheet before it takes effect -Sheet. Protect ("Test", Sheetprotectiontype.all); the  -Workbook. SaveToFile (@"sample_lock.xlsx", excelversion.version2010); -}

Today is the end, although all very simple, but to understand, for the future skilled development is still good. The example is very simple, the code is on top, and after this series is complete, we'll package a code.

"Original". NET read-write Excel tool Spire.xls using (2) Excel file control