[Original]. net used 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:

object miss = Missing.Value;Application excelApp = new Application();excelApp.Workbooks.Open(TemplateName, miss, true, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, 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:

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.

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.

Range = worksheet. get_range (worksheet. cells [1, 1], Worksheet. cells [1, colcount]); range. Interior. Color = 255; // set the area 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.

// Set the chart size. Chartobject chartobj = charts. Add (0, 0,400,300); Chart chart = chartobj. Chart; // set the chart data area. 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:

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.

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:

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:

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:

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.

Workbook workBook = excelApp.Workbooks[1];object miss = Missing.Value;workBook.SaveAs(path, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);

Close the workbook after saving:

workBook.Close(false, miss, miss);workBook = null;

7. The memory needs to be cleared.

excelApp.Quit();excelApp = null;GC.Collect();