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 a reference in :.
Note that you need to set "" In the attribute to "After referencing", otherwise errors may occur during compilation.
Then remember 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.

Missing miss =ApplicationClass excel = = Workbook wb == excel.Workbooks.Open(Worksheet ws = wb.Sheets[] = wb.ActiveSheet  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, the code is directly ended after Excel is used. Have these friends not found out that many EXCEL. EXE processes will be left in the system? For example:

, CharSet = GetWindowThreadProcessId (IntPtr hwnd, = k =

Note DllImport.

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

= = ;

 

3. Storage Format Problems

It seems very serious, especially for some computers. 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 the most common file format. In addition to this, there is also a file, which is a new form of self-developed format. In addition to these, excel also supports saving tables in plain text format. When we use a program to generate an Excel file, considering that there are still a large number of users in China, we will save it for better compatibility with them. Most of them are saved like the following code:

wb.SaveAs();

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, and we will find that there is another second parameter, as the name suggests, which is the file format, which is exactly the parameter we want, so we only need to tell the Excel file format to be saved, face-to-face solution:

wb.SaveAs(, );

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:

Range range = ws.get_Range(ws.Cells[, ], ws.Cells[, range.NumberFormat = range.MergeCells = range.RowHeight = = range.Font.Size = = = range.HorizontalAlignment ==range.Borders.LineStyle = =range.set_Value(miss, );

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:

ws.PageSetup.Orientation =ws.PageSetup.PrintTitleRows = ;

 

6. other settings

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

ws.Name = ;

 

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?
In fact, we can not use Excel operation classes to generate, but directly use IO to generate, and finally save it as a file, which can 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:

Workbook wb = excel.Workbooks.OpenXML(wb.SaveAs(, );

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!