C # Collect and sort out frequently asked questions in Excel (regular updates are welcome)

There are often projects that need to export a table as an Excel file, or import an Excel file for operations. Therefore, how to operate an Excel file in C # is the most basic problem.

In the past few years, developers have also met such requirements. However, because they are not frequent, they often go online to search for problems. In a recent project, the Excel file to be exported involves a lot of remote operations, so I decided to open an article here to collect and sort the used code and some tips. If you have some of your own experiences or have better solutions, you are welcome to discuss them. I will update it from time to time.

 

0. Before use

Before writing code, we need to add reference first, in the Assembly-
Extension: Microsoft. Office. InterOP. Excel.
You must also set the "embedded interoperability type" in the attribute
Flase. Otherwise, errors may occur during compilation.
Then remember using:

using Microsoft.Office.Interop.Excel;using System.Reflection;

▲The second using here is because a default value of missing. value is often used in Excel operations. Therefore, you must first reference system. reflection.

 

1. Start Using

In general, we only operate the first Worksheet (sheet) in an Excel file. The following describes how to create and read the first sheet in an Excel file.

// Define a value for missing to facilitate later use of missing Miss = missing. value; // create an Excel file and create an invisible applicationclass Excel = new applicationclass (); excel. visible = false; // create a workbook or open the existing file workbook WB = excel. workbooks. add (); workbook WB = excel. workbooks. open ("demo.xls"); // obtain the first worksheet, or obtain the current default worksheet Ws = WB. sheets [1] As worksheet; worksheet Ws = WB. activesheet as worksheet;

Note: In Excel operations, indexes start from 1 rather than 0, which is different from most program syntaxes.

 

2. After use

Now that you have created all the Excel files, let's talk about the end of the application and the problems that need to be paid attention to when saving the files. This is like writing code. The two curly braces are all typed at the same time, and then write the code in it.

In many documents, Excel = NULL is used directly after Excel is used to end the code. Didn't these friends find that many excel. EXE processes will be left in the system? For example:

If this is on the user's client, the problem may be ignored due to shutdown. However, if an Excel file is generated on the server and a user generates a process once, the consequences can be imagined. So let's talk about how to end the Excel process after use.

To end an Excel file, you cannot simply end the Excel. EXE file. In this way, an Excel file is closed if it is opened.
The following is the correct Excel Code:

[Dllimport ("user32.dll", charset = charset. auto)] public static extern int getwindowthreadprocessid (intptr hwnd, out int ID); // end the Excel process public static void killexcel (Application Excel) {intptr T = new intptr (Excel. hwnd); int K = 0; getwindowthreadprocessid (T, out k); system. diagnostics. PROCESS p = system. diagnostics. process. getprocpolicyid (k); p. kill ();}

Note that dllimport requires using system. runtime. interopservices.

The next step is to close the service and call the above Code:

// Close the workbook and release the resource WB. Close (); WB = NULL; // exit Excel and release the resource excel. Quit (); killexcel (Excel); Excel = NULL;

 

3. Storage Format Problems

「 The file format and extension do not match. The file may be damaged or insecure .」 It seems very serious, especially for some computers, the word "insecure" is very dazzling. This problem may be encountered by many of my friends during Excel export, including some software I have used. Most of them have not dealt with this problem, let the user click "yes. However, this is certainly not acceptable for developers who pay close attention to user experience. So what exactly is the cause?

In fact, solving this problem is very simple. The reason for this problem is related to the Office Excel version. We all know that excel has
97-2003 is the most common. xls file.
. XLSX file, which is a new format after Office EXCEL 2007. In addition, Excel also supports saving tables
. Xml or even plain text format. When we use a program to generate an Excel file, a large number
Office 2003 users, so we will save as. xls for better compatibility with them. Most of them are saved like the following code:

// Save WB. saveas ("demo.xls ");

Although your save path contains. XLS suffix, but at this time, Excel does not know what format you want to save, so it may be unformatted, or the default format of the office version in the current system.

Now let's take a look at the saveas parameters. We will find the second parameter fileformat, as the name suggests, is the file format, which is exactly the parameter we want, so we only need to tell the format to be saved in Excel, face-to-face solution:

// Save. The format is encoded as 56 (xls) WB. saveas ("demo.xls", 56 );

Now open the generated demo.xls file and you will find that the file is opened directly without any problems.

 

4. common format settings

In addition to the above basic problems, there are some common format settings during Excel operations. Including font size, bold, merged cells, vertical center, horizontal center, Row Height, column width, cell format, border style, and so on. The following code includes these common settings:

// Select an area (between one or more cells) range = ws. get_range (WS. cells [1, 1], WS. cells [2, 10]); // sets the cell format. @ refers to the text format (when a long number is exported, such as a mobile phone number, it is processed as a number, so we need to force the text) range. numberformat = "@"; // merge the range of cells. mergecells = true; // set the Row Height and column width range. rowheight = 35; range. columnwidth = 100; // set the font size, bold, and font (and most font-related attributes are included in the font attribute) range. font. size = 12; range. font. bold = true; range. font. name = ""; // horizontal center, vertical center range. horizontalalignment = xlhalign. xlhaligncenter; range. verticalalignment = xlhalign. xlhaligncenter; // set the border range. borders. linestyle = 1; range. borders. linestyle = xllinestyle. xlcontinuous; // set the value (content) range for the cell. set_value (Miss, "abel.cnblogs.com ");

The trouble is that we need to reset the range for each operation of a region, which is equivalent to selecting some cells in Excel. Therefore, if the data volume is large, it takes a little time to generate an Excel file.

 

5. Common print settings

This part may be rare on the Internet, but some projects also have related requirements, such as the default horizontal paper, print the title line (no matter the page to print, this line appears, usually the first line of the table). Let's look at the Code:

// Set the horizontal paper ws. pagesetup. Orientation = xlpageorientation. xllandscape; // set the range of the Print Title ws. pagesetup. printtitlerows = "$3: $3 ";

 

6. other settings

There are some other settings and operations, which should be sorted here for the time being.

// Set the workbook name ws. Name = "Hello C #";

 

7. Solutions for slow generation...

As we have mentioned above, if you use a program to generate an Excel file, if you encounter a large amount of data (100,000-level data is enough), it will be slow. How can this problem be solved?
Instead of using the Excel operation class, we can directly use Io to generate an Excel table in XML format and save it
The. xls file can be used to increase the speed by N times. Of course, there will be problems mentioned in the third point above.
Is there a fast way to avoid the security prompt? There are also some. Let's take a look at the Code:

// Open the Excel file workbook WB = excel in XML format. workbooks. openxml ("temp. XML "); // save it as an Excel file in XLS format. saveas ("demo.xls", 56 );

Yes, you can open an Excel file in XML format generated by I/O, and save it as an xls file in Office 97-2003 format.
Note: This method is only applicable to easy-to-use Excel files. Otherwise, compatibility issues will be prompted during storage!

 

Last

I have been preparing to write this article for several days. I did not write it until last night. It took me two nights to write a large part of the code and I practiced it again in Visual Studio, it took two nights to finish writing. In addition to making it easier for you to use it later, you also hope to help friends who need it.

If you have experience or skills in this area, or have any questions in this article, please join us!