. NET Excel Reading and Writing Tool Spire. Xls use the Excel file control (2),.netspire.xls

. NET Excel Reading and Writing Tool Spire. Xls use the Excel file control (2),.netspire.xls

In the previous article, ". NET Excel tool Spire. Xls (1) Introduction" introduced the basic functions and comparisons of Spire. XLS. This article focuses on the settings and global control of the Excel file in C. This is also an article that C # often encounters when reading and writing Excel files. It is also a learning step in my use process. This article will first introduce Spire. the basic knowledge of creating and loading documents in XLS, saving files, and merging multiple Excel files. You can also make more detailed settings for the generated Excel files, for example, set the attributes of an Excel file. In many projects, not only do you need to generate an Excel file, but you also need to print and obtain paging information; finally, we will introduce how to use C # To set encryption to protect Excel files.

1. Create, load, and save Excel documents

In our preliminary introduction to the previous article, there is a Hello Excel example, which actually includes the method for creating and saving Excel files, which is very simple. This section will focus on more detailed research on each detail.

1.1 save an Excel file

The SaveToFile method of Workbook is used to save Excle files. The following versions can be saved Based on the file name, Excel file version, and file format as needed. The prototype of the method is as follows, it is relatively simple and will not be demonstrated. It will be used in the code in subsequent articles.

// Save file based on file name, file version, file format information
public void SaveToFile (string fileName);
public void SaveToFile (string fileName, ExcelVersion version);
public void SaveToFile (string fileName, FileFormat fileFormat);
public void SaveToFile (string fileName, string separator);

1.2 load Excel files from different channels

Using C # To Operate Excel files may not always generate Excel file reports. Sometimes, you need to load existing data (such as XML and Excle) for modification, save it to the corresponding Excel file. The methods for loading data to Excel are LoadFromFile, LoadFromStream, and LoadFromXml of the Workbook object. The method prototypes include the following:

// Load from file, according to file name, protection mode and Excel version
public void LoadFromFile (string fileName);
public void LoadFromFile (string fileName, bool preserveMode);
public void LoadFromFile (string fileName, ExcelVersion version);
public void LoadFromFile (string fileName, string separator);
public void LoadFromFile (string fileName, string separator, int row, int column);
public void LoadFromFile (string fileName, string separator, int row, int column, ExcelVersion version);
// Load from the data stream
public void LoadFromStream (Stream stream);
public void LoadFromStream (Stream stream, bool loadStyles);
public void LoadFromStream (Stream stream, ExcelVersion version);
// Load from XML file
public void LoadFromXml (Stream stream);
public void LoadFromXml (string fileName);
// Load from template file
public void LoadTemplateFromFile (string fileName);
public void LoadTemplateFromFile (string fileName, bool loadStyles);

2. C # Set Excel file attributes

Although file attributes are rarely used by most people, we should introduce them. After all, if the file is distributed out, it is necessary to complete the relevant attribute information. For Excel file attributes, right-click the file and choose Properties> View Interface. For example, if I use WPS to view the Excel file attributes and use Office Excel to view the attributes, they are displayed on the right although they are different, but the principle is the same:

1. Excel properties displayed by WPS 2. File properties displayed by Office Excle

The method for setting the Excel file attributes is in the DocumentProperties attribute of the Workbook object. This attribute is of the XlsBuiltInDocumentProperties type, in Spire. xls. core. spreadsheet. collections namespace, through the Object Browser of VS, you can also find some of its implementation principles, which can be extended to other components. Let's look at a simple example:

#region 2.Excel file attribute example
static void ExcelTest2 ()
{
  Workbook workbook = new Workbook ();

  // Modify the document attribute information, so that when publishing, the information of the company and the filer can be displayed through the document
  workbook.DocumentProperties.Author = "张三"; // Author
  workbook.DocumentProperties.Subject = "Test file properties"; // Subject
  workbook.DocumentProperties.Title = "Test Excel file"; // Title
  workbook.DocumentProperties.Company = "XX Limited"; // Unit
  workbook.DocumentProperties.Comments = "Keep file comments"; // Comments
  workbook.DocumentProperties.Keywords = "Test Excel"; // Keyword
  workbook.DocumentProperties.CreatedTime = DateTime.Now; // Creation time
  // TODO: There are other properties, you can refer to the related properties of XlsBuiltInDocumentProperties

  // Save the Excel file to the specified file, you can also specify the Excel version
  workbook.SaveToFile ("Sample.xls", ExcelVersion.Version2007);
}
#endregion

Note: Because I have not installed Office 2007 or a later version, it may be a problem to use WPS for display. You can see it only after converting WPS to 2003. This may be the reason for WPS, the file generated by debugging finds that these attribute values exist. So I didn't make any effort, and it was really troublesome to install an Office.

3. C # encrypt and protect Excel files

I believe many people have used Office encryption and protection functions. For Excel, encryption and protection are more extensive: not only files can be encrypted, but also specified Sheet can be protected, the specified cell.

3.1 encrypt Excel files

Excel file-level encryption is actually very simple. It is the Protect method of the Workbook object. You can pass the encrypted password. If you want to cancel the password, use workbook. unProtect (), note that password is not required to cancel encryption, and Excel software operations are also required, so program operations are not required.

#region 3. Example of encrypted Excel file
static void ExcelTest3 ()
{
  Workbook workbook = new Workbook ();
  workbook.Protect ("pwd111"); // Set the password for protection encryption: pwd111
  // Save the Excel file
  workbook.SaveToFile ("Sample.xls", ExcelVersion.Version2007);
}
#endregion

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

3.2 protect Sheet and cells

Sheet protection is more flexible. The Worksheet object Protect method is used. The parameter passed is the protected password and the optional protection type (more diverse protection types ), take a look at the following example:

Workbook workbook = new Workbook ();

Worksheet sheet = workbook.Worksheets [0];

// protect sheet
sheet.Protect ("test", SheetProtectionType.All);

// Save the Excel file
workbook.SaveToFile ("Sample.xlsx", ExcelVersion.Version2010);

The implementation effect should be similar to the "protect worksheet" interface of WPS, such:

The function of protecting worksheets is generally to prevent misoperations or prevent unauthorized changes. I have seldom used it here, but I have learned about it. By the way.

static void ExcelTest4 ()
{
  Workbook workbook = new Workbook ();
  workbook.CreateEmptySheets (1);
  Worksheet sheet = workbook.Worksheets [0];

  sheet.Range ["A1"]. Text = "Locked";
  sheet.Range ["B1"]. Text = "Unlocked";

  sheet.Range ["A1"]. Style.Locked = true;
  sheet.Range ["B1"]. Style.Locked = false;

  // Must protect the worksheet to take effect
  sheet.Protect ("test", SheetProtectionType.All);

  workbook.SaveToFile (@ "Sample_Lock.xlsx", ExcelVersion.Version2010);
} 

So far today, although they are all very simple, it is good to be skilled in development in the future. The example is very simple, and the code is on top. After the series is complete, package a copy of the code.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.