ExcelTechnology
Using C# Excel Interop Class
Sometimes we need to control c# .net excel class in our program in order to make the Microsoft Office c# excel interop assembly working properly. The application program must process a batch of spreadsheets and deal with excel worksheets. Here I would like to offer some tips on c# excel interop performance and introduce you some fundamentals about using the c# excel interop assembly.
To begin with, we should create a new C# excel interop class file in Visual Studio and we can call it something like Excel Interop. And conveniently, we are free to use something more familiar or standard or add underlines to class file to make the Excel namespace handy.
The issue of compatibility in the Excel program on the Apple Mac OS X platform, however, is not trivial. The code above shows one solution, which is to use get Value () instead of Value2. Excel stores OA dates in different formats on Macs and PCs. The function get Value () returns objects that do not vary based on the original platform.
If the program is compatible in all aspects, the process would be performed very smoothly. In case you do some wrong in your c# .net excel processing which results in a long time waiting to process even 5 spreadsheets at one time. Then you are advised to try the Cells [] indexer on a range or getting the range with the Range property.
The important thing is that we must include namespace in your c# .net excel program. We have to use the Add Reference command for this, use Visual Studio's GUI to add an assembly to the application program. Once completed this, you will need a class.
Given all the stuffs I have talked about, we have saw how to start with C# excel interop class and avoid serious performance problems such as compatibility with the assembly.
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
Workbook.Open Method and How to Open Workbook in C#
As is known, Excel object includes four common classes, Application, Workbook, Worksheet and Range. Each class has lots of methods, properties and events.
Workbook object is used to represent a Microsoft Excel workbook, which is a member of the Workbooks collection. All Workbook objects open in Microsoft Excel is contained in Workbooks collection. And there are three properties which can return a Workbook object: Workbooks Property, ActiveWorkbook property and ThisWorkbook property.
Besides property, there are lots of events of Workbook object. Workbook.Open is one of them, which occurs when the workbook is opened. This article introduces something about Workbook.Open Event and how to open Workbook in C#.
The namespace of Workbook.Open event is Microsoft.Office.Tools.Excel and assembly is Microsoft.Office.Tools.Excel which is in Microsoft.office.tools.excel.dll. In C#, the syntax of Workbook.Open event is public event WorkbookEvents_OpenEventHandler Open.
The following example by using C# shows the Open event handler, which maximizes the Microsoft Office Excel application window when the current workbook is opened.
private void WorkbookOpen()
{
this.Open +=
new Excel.WorkbookEvents_OpenEventHandler(
ThisWorkbook_Open);
}
void ThisWorkbook_Open()
{
this.Application.WindowState = Excel.XlWindowState.xlMaximized;
}
How to use C# to open Excel Workbook
If we want to open an Excel workbook in C#, it is necessary to use the Open methods of the Workbooks collection, which makes it possible to work with all open workbooks and to open workbooks. The following code shows you the method to open the workbooks under specified path in C#.
this.Application.Workbooks.Open(@ “YourPathYopurWorkbook.xls”,
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
Way to Save Workbook by SaveAs Method in C#
We save an Excel file after creating it. We have many methods to save an Excel file, which is often called workbook. Generally, workbook will be saved in the current folder with the name it was given when it was created if we don’t save it by specifying a path. Also, we can save a copy of the workbook without modifying the open workbook in memory.
The first way is to save a workbook without changing the path associated with a document-level customization.
Call the Save method of the ThisWorkbook class in C#:
this.save();
Save the active workbook in an application-level add-in.
Call the Save method to save the active workbook in C#:
this.Application.ActiveWorkbook.Save();
The other way is to save a Workbook with a new path associated with a document-level customization.
Call the C# Excel SaveAs method of the ThisWorkbook class.
Save the active workbook in an application-level add-in
Call the SaveAs method to save the active workbook to a new path in C#.
The third way is to save a copy of the workbook without modifying the open workbook in memory associated with a document-level customization
Call the SaveCopyAs method of the ThisWorkbook class in C#:
This.SaveCopyAs(@ “C:Book1.xls”);
Save the active workbook in an application-level add-in
Call the SaveCopyAs method to save a copy of the active workbook in C#:
this.Application.Activeworkbook.SaveCopyAs(@”CBook1.xls”);
Note: during run-time of saving or copying the workbook, if there is error in code, any of the methods will be cancelled interactively. For example, if the procedure calls the SaveAs method but not disable prompts from Excel and user clicks Cancel when promoted, Excel raises a run-time error.
Issues and Solutions of Using Excel Integration
Sometimes, there are some issues when we use Excel integration with various Excel versions. What is the reason for the issues? It may be for the lack of .Net Programmability support or registry permission. Usually, it is useful to reinstall Excel integration. However, sometimes, it is useless. Therefore, we can try the following things.
1. Microsoft Excel integration is disabled. What we should do is to select Help and then click the Disabled Item button. If the Add-in appears in the Disabled Items list, select it and enable button. For this, you need to update Excel if you use .Net 2.0 to make .Net Excel integration available.
2. .Net Programmability Support has not installed in Excel, with which, Excel integration can make full use of its function correctly.
3. If you use Excel 2003, you need to open Add Remove Programs and then select Microsoft Excel. Click Change. Select Add or Remove Features and Click Next to select Choose Advanced Customization of Applications. Expand Excel for ensuring that .Net Programmability Support is set to Run From My Computer and Click Update.
4. For Excel 2007, the step is similar to Excel 2003. The little difference is that you don’t need to select Choose to Advanced Customization of Application, but Expand Excel directly.
5. It is possible that all the above are not useful. Then, we come to the last, to check the registry. It is a little difficult than others and you should make a backup of your registry because you many meet a risk. Firstly, open the registry and find the following key: [HKEY_LOCAL_MACHINESOFTWAREMicrosoftOfficeWordAddins]. And then, make sure that the LoadBehavior value is 3, if not, change it. The next is to reset permissions for the USERS group to full control for the following registry keys:
[HKEY_LOCAL_MACHINE...OfficeWordAddins
STWord.STWordDesigner]([HKEY_LOCAL_MACHINE...OfficeWordAddins
STWord.Connect]([HKEY_LOCAL_MACHINE...OfficeExcelAddinsSTExcel.Connect]
You don’t need to create the keys because you may not see all of above on every computer.
In short, there are the common issues that Excel integration cannot
