1. Add Excel Reference
can be in. NET tab, or add the Microsoft Excel 12.0 Object Library under COM. They all generate Microsoft.Office.Interop.Excel.dll.
2. Create Excel.
There are two ways to create an instance of Excel workbook.
1. Need a template file, using the open method, more parameters:
Copy Code code as follows:
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);
Where the first and third parameters of the Open method are template names (paths) and whether they are read-only. The rest of the parameters generally do not need to be set. This sets the original file to read-only because the template file is not modified.
2. No template files are required, using the Add method requires only one parameter:
Copy Code code as follows:
Object miss = Missing.Value;
Application Excelapp = new application ();
EXCELAPP.WORKBOOKS.ADD (Miss);
3. Insert a datasheet into Excel
The method of inserting a datasheet is simpler, using the worksheet in the previously generated workbook to add two-dimensional data to it, you need to call the worksheet interface's Get_range method to get the insertion area and then assign the value via Value2 (Ignore format).
Copy Code code 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;
The first line, the direct use of the worksheet in Excelapp, is because the default workbook is Workbooks[1], that is, direct access to workbooks[1] Worksheets into the Worksheets property of the Application object.
In the penultimate line, the two parameters in the Get_range method are the start and stop coordinates to insert the data region (the upper-left coordinate is "1,1"). Here, inserting a two-dimensional array directly into the table is more efficient than inserting each grid at 1.1 points.
4. Modify Excel Table Style
Setting table styles is primarily about setting properties of the Range class object. Similar to the insertion data, the worksheet Get_range method is used to get the area where the style needs to be set, and the corresponding property changes the style.
Copy Code code as follows:
Range range = Worksheet.get_range (worksheet.cells[1, 1], worksheet.cells[1, ColCount]);
Range. Interior.Color = 255; Sets the area background color.
Range. Font.Bold = true; Sets the font bold.
Range. Borderaround (xllinestyle.xlcontinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, 15); Set area border
Almost all of the settings that can be used in the graphic world for Excel formatting, including formulas, sorting, and so on, can be set here. However, because the properties and methods are not detailed description, so need a little bit of slowly groping ...
5. Insert a chart into Excel
Inserting a chart is primarily about manipulating ChartObject objects and chart objects.
Copy Code code as follows:
Sets the chart size.
ChartObject chartobj = charts. ADD (0, 0, 400, 300);
Chart Chart = Chartobj.chart;
Set up 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 below the data area.
Chartobj.left = convert.todouble (range. left);
Chartobj.top = convert.todouble (range. Top) + convert.todouble (range. Height);
Setting the chart area is critical, and the area contains the header row. The second parameter of ChartWizard indicates the type of the chart, and the fourth parameter indicates whether the value of the row or column is a data series, and the fifth and sixth parameters indicate the cells that are the horizontal coordinates and series names respectively.
Take the line type chart for 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 |
Then there are two different graphs depending on the fourth argument:
Copy Code code as follows:
Chart. ChartWizard (range, xlcharttype.xlline,miss,xlrowcol.xlrows, 1, 1, True, "title", "X-axis title", "Y-axis title", Miss);
When PlotBy row, a line is drawn for a data series with one row of data, and a column of each row is used as the heading of the line, and each column in the first row is used as the horizontal axis of the line.
Copy Code code as follows:
Chart. ChartWizard (range, Xlcharttype.xlline,miss,xlrowcol.xlcolumns, 1, 1, True, "title", "X-axis title", "Y-axis title", Miss);
In PlotBy column, a line is drawn for a data series with a column of data, and one row of each column is used as the title of the line, and each row of the first column is used as the horizontal axis of the line.
The similarities between the two modes are that they are all ordinate by the values in the coordinate lattice. Note here that if the fifth and sixth arguments are changed to 2, it is not the second row of the row or the second line of the column as the title, but the first two rows or the first two columns, for example:
Copy Code code as follows:
Chart. ChartWizard (range, xlcharttype.xlline,miss,xlrowcol.xlcolumns, 2, 0, True, "title", "X-axis title", "Y-axis title", Miss);
Because the series header is set to 0, the default "series X" is used as the title, and the horizontal axis takes the first two lines of each column, so there are three lines, 10 dots per line.
6. Save Excel
Before saving, you need to refresh so that new records can be written down:
Copy Code code as follows:
Workbook workbook = excelapp.workbooks[1];
Workbook.refreshall ();
After the save, there are two ways:
1. Save directly, when you create an Excel file by using the Open method, and it is not set to read-only, this method is simpler:
Copy Code code as follows:
Workbook workbook = excelapp.workbooks[1];
Workbook.save ();
2. This method is more flexible, is SaveAs (), equivalent to the interface operation of the Save as, but this method of the problem and the creation of workbook when the first method, the parameters are more, although most can use miss.
Copy Code code as follows:
Workbook workbook = excelapp.workbooks[1];
Object miss = Missing.Value;
Workbook.saveas (Path, Miss, Miss, Miss, Miss, Miss, Xlsaveasaccessmode.xlnochange, Miss, Miss, Miss, Miss, Miss);
To close workbook after saving:
Copy Code code as follows:
Workbook.close (false, Miss, Miss);
workbook = null;
7. Final need to empty memory
Copy Code code as follows:
Excelapp.quit ();
Excelapp = null;
Gc. Collect ();
