ExcelTechnology
Conclusion of Creating Excel, Inserting Data and Edit Method in .Net
If we want to create an Excel file in .Net, it is necessary to add Excel Object Library and Microsoft. Office. Interop. Excel. And both of them can generate Microsoft.Interop.Excel.dll.
At first, I want to talk something about how to create Excel in .Net. There are two ways. One is to use Open method to open a template file. In this way, we should use several parameters. Two parameters should be set, template name or path and read-only setting in Open method. The other way is to use Add method and template file is not needed. In this way, only one parameter is used.
Then, we insert data table in Excel. Actually, we often operate Excel by using Excel object in .Net. We also need to use object when adding data to the generated Worksheet in Workbook. We need to invoke get_Range method to get insert range of Worksheet interface and then set value by Value2.
Next, edit Excel format. The formatting focuses on setting property of Range object. Similar to insert data, we also get the range which needs to be formatted by using get_Range method and then set property to change format.
After having data and format, if we want to insert chart, how do we do? We need to operate ChartObject object and Excel Chart object to complete this requirement.
Set Chart Size
ChartObject chartObj = charts.Add(0, 0, 400, 300);
Chart chart = chartObj.Chart;
Set Chart Data Range
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);
Make Chart below Data Range
chartObj.Left = Convert.ToDouble(range.Left);
chartObj.Top = Convert.ToDouble(range.Top) + Convert.ToDouble(range.Height);
Now, the Excel file has been filled. We need to save it. Before saving, refresh to make the new record be saved. Then, there are two methods to save. One is to save directly. It is used when creating Excel file by Open method and it doesn’t set read-only. The other one is to use SaveAs method. After saving, close Workbook. Please note that we shoul
