. Net: how to create an Excel file (insert data, modify the format, and generate charts)

1. Add Excel reference
You can add Microsoft. Office. Interop. Excel reference under the. Net tab, or add Microsoft Excel 12.0 Object Library Under COM. They all generate Microsoft. Office. Interop. Excel. dll.

2. Create an Excel file.
There are two ways to create an Excel Workbook instance.

1. A template file is required and the Open method is used. There are many parameters:
Copy codeThe Code is as follows:
Object miss = Missing. Value;
Application excelApp = new Application ();
ExcelApp. Workbooks. Open (TemplateName, miss, true, miss, miss );

The first and third parameters of the Open method are the Template Name (PATH) and whether it is read-only. You do not need to set other parameters. Set the original file as read-only because the template file is not modified.

2. You do not need a template file. You only need one parameter to use the Add method:
Copy codeThe Code is as follows:
Object miss = Missing. Value;
Application excelApp = new Application ();
ExcelApp. Workbooks. Add (miss );

3. Insert a data table to Excel
The method for inserting a data table is relatively simple. To add two-dimensional data to a previously generated WorkSheet, call the get_Range method of the WorkSheet interface to obtain the inserted area, and then use Value2 (ignore the format) assign values.
Copy codeThe Code is as follows:
Worksheet workSheet = (Worksheet) excelApp. Worksheets [2];
Int rowCount = 20;
Int colCount = 5;
Object [,] dataArray = new object [rowCount, colCount];
Random rand = new Random (DateTime. Now. Millisecond );
For (int I = 0; I <rowCount; I ++)
{
For (int j = 0; j <colCount; j ++)
{
DataArray [I, j] = I + j;
}
}
WorkSheet. get_Range (workSheet. Cells [1, 1], workSheet. Cells [rowCount, colCount]). Value2 = dataArray;
WorkSheet = null;

In the first line, the Worksheet in the excelApp is used directly because the default Workbook is Workbooks [1], that is, the Worksheets in Workbooks [1] is directly put into the Worksheets attribute of the Application object.

In the penultimate row, the two parameters in the get_Range method are the start and stop coordinates of the data area to be inserted (the coordinates in the upper left corner are [1, 1 ]). Inserting a two-dimensional array into a table is more efficient than inserting a grid at 1.1 points.


4. Modify the Excel sheet style
To set a table style, you can set the attributes of a Range object. Similar to inserting data, you can use the get_Range method of Worksheet to obtain the region where you want to set the style and set the corresponding attribute to change the style.
Copy codeThe Code is as follows:
Range range = workSheet. get_Range (workSheet. Cells [1, 1], workSheet. Cells [1, colCount]);
Range. Interior. Color = 255; // set the regional background Color.
Range. Font. Bold = true; // set the Font to Bold.
Range. BorderAround (XlLineStyle. xlContinuous, Microsoft. Office. Interop. Excel. XlBorderWeight. xlThick, Microsoft. Office. Interop. Excel. XlColorIndex. xlColorIndexAutomatic, 15); // set the area border

Almost all settings of the Excel format, including formulas and sorting, can be set in the graphic interface. However, the attributes and methods are not described in detail ......

5. Insert charts into Excel
You can insert a Chart to operate the ChartObject object and Chart object.
Copy codeThe Code is as follows:
// Set the chart size.
ChartObject chartObj = charts. Add (0, 0,400,300 );
Chart chart = chartObj. Chart;
// Set the chart data area.
Range range = workSheet. get_Range ("A1", "E10 ");
Chart. ChartWizard (range, XlChartType. xl3DColumn, miss, XlRowCol. xlColumns, 1, 1, true, "title", "X axis title", "Y axis title", miss );
// Move the chart to the data area.
ChartObj. Left = Convert. ToDouble (range. Left );
ChartObj. Top = Convert. ToDouble (range. Top) + Convert. ToDouble (range. Height );

It is critical to set the chart area, which contains the title line. The second parameter of ChartWizard specifies the chart type. The fourth parameter specifies whether the value of a row or column is used as a data series, the fifth and sixth parameters indicate the cells used as the horizontal axis coordinates and series names respectively.

Take a Line chart as an example. The data is as follows:

0	1	2	3	4
1	2	3	4	5
2	3	4	5	6
3	4	5	6	7
4	5	6	7	8
5	6	7	8	9
6	7	8	9	10
7	8	9	10	11
8	9	10	11	12
9	10	11	12	13

There are two different charts based on the fourth parameter:
Copy codeThe Code is as follows:
Chart. ChartWizard (range, XlChartType. xlLine, miss, XlRowCol. xlRows, 1, 1, true, "title", "X axis title", "Y axis title", miss );

When PlotBy Row is used, a line is drawn for a data series, and the value of the column in the first line is used as the line title, take each column in the first row as the abscissa of the line.
Copy codeThe Code is as follows:
Chart. ChartWizard (range, XlChartType. xlLine, miss, XlRowCol. xlColumns, 1, 1, true, "title", "X axis title", "Y axis title", miss );

 

When PlotBy Column is used, a line is drawn for a data series, and the value of each Column is taken as the line title, and each row in the first column is used as the abscissa of the line.

The similarities between the two modes are that they all take the values in the coordinate grid as the vertical coordinates. It should be noted that if the fifth and sixth parameters are changed to 2, the second column of the row or the second row of the column is not taken as the title, but the first two rows or the first two columns are taken, for example:
Copy codeThe Code is as follows:
Chart. ChartWizard (range, XlChartType. xlLine, miss, XlRowCol. xlColumns, 2, 0, true, "title", "X axis title", "Y axis title", miss );

Because the series title is set to 0, the default "series X" is used as the title, while the abscissa takes the first two rows of each column, so there are three lines in total, each line has 10 points.
6. Save the Excel file
You need to refresh the record before saving it so that the new record can be noted down:
Copy codeThe Code is as follows:
Workbook workBook = excelApp. Workbooks [1];
WorkBook. RefreshAll ();

There are two ways to save it:

1. Save it directly. This method can be used to create an Excel file without being set as read-only. It is relatively simple:

Copy codeThe Code is as follows:
Workbook workBook = excelApp. Workbooks [1];
WorkBook. Save ();

2. this method is flexible, that is, SaveAs (), which is equivalent to saving the interface operation as. However, the problem of this method is the same as that of the first method when creating a Workbook. There are many parameters, although miss can be used in most cases.
Copy codeThe Code is as follows:
Workbook workBook = excelApp. Workbooks [1];
Object miss = Missing. Value;
WorkBook. SaveAs (path, miss, XlSaveAsAccessMode. xlNoChange, miss, miss );

Close the WorkBook after saving:
Copy codeThe Code is as follows:
WorkBook. Close (false, miss, miss );
WorkBook = null;

7. The memory needs to be cleared.
Copy codeThe Code is as follows:
ExcelApp. Quit ();
ExcelApp = null;
GC. Collect ();