|
|
|
|
|
|
|
Page Views :
|
82115
|
|
Downloads :
|
0
|
|
Rating :
|
Rate it
|
|
Level :
|
Intermediate
|
|
Introduction
Excel Automation is a buzz word in both webapps and winapps. In the programming life (like Mr.Anderson neo) I guess almost every one could have come across this word. For others let me go further in detail. Excel Automation is automating some or all of the process involved in creating or updating excel worksheets.
The real life scenario of an excel automation can be a daily account maintenance where you have an excel sheet template with graphs and calculations already in place except the data. So you want the data to be pulled in from a database and written to the excel sheet. After this the template takes care of the data by manipulating for graph generation.
Analysis
For the above said scenario we can go for a simple console application in .Net. Why I didn't go for an ASP.Net web application? Because running a web application requires a browser to be opened and closed. This becomes tedious when you schedule the process using windows scheduler to occur in particular intervals. And running a console based application is quite easy.
Getting Started
Pardon me for beating around the bush. Now let us jump in to the good part (coding). For this automation process we need to follow the below steps
- Add a referrence to the Microsoft Excel object library COM component.
- Add the namespace Excel
- Instantiate the class Excel.ApplicationClass as below
Excel.Application xl=new Excel.ApplicationClass();
To open an excel file,
Excel.Workbook wb=xl.Workbooks.Open(Environment.CurrentDirectory+"/SampleExcel.xls",0, false, 5, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value,true, false, System.Reflection.Missing.Value, false, false, false);//Open the excel sheet
To read cell(s) in the worksheet,
Excel.Sheets xlsheets = wb.Sheets; //Get the sheets from workbook
Excel.Worksheet excelWorksheet = (Excel.Worksheet)xlsheets[1]; //Select the first sheet
Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("B4:FZ4", Type.Missing); //Select a range of cells
Excel.Range excelCell2 = (Excel.Range)excelWorksheet.get_Range("A5:A5", Type.Missing); //Select a single cell
Console.WriteLine(excelCell2.Cells.Value2.ToString()); //Print the value of the cell for a single cell selection
System.Array myvalues = (System.Array)excelCell.Cells.Value2; //Assign it to an array
string[] strArray = ConvertToStringArray(myvalues); //Convert array into String array
foreach (string str in strArray)
Console.WriteLine(" Text in Cell " + str); //Loop through the array to print the values in the cell
To save a value in a cell
excelCell2.Cells.Value2 = "SampleText"; //Assign a value to the cell
wb.Save(); //Save the workbook
Finally Quit the Excel Application
xl.Quit();
Conclusion
Excel is a great tool to work with. When it comes to automating, we need to consider many things. Always remember to quit the excel application in code before exiting. If not, the memory consumed by the excel application will not be freed up.
|
|
Comment Request!
Thank you for reading this post. Please post your feedback, question, or comments about this post
Here.
|
|
|
|
|
Login
to add your contents and source code to this article
|
|
|
|
|
|
|
|
|
|
|
|
Thiagarajan Alagarsamy
Thiagu is living in Bangalore, India. His native is Madurai, a historic city in south India. He loves to code in C#. He frequents c# corner articles when he is not coding. Thiagu loves reading Jeffrey Archer and Sidney Sheldon novels. He is very much interested in Artificial Intelligence (AI). To view his blog - http://csharpnet.blogspot.com
|
|
|
|
|
|
|
|
|
C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional
consulting company, our consultants are well-known experts in .NET and many of them
are MVPs, authors, and trainers. We specialize in Microsoft .NET development and
utilize Agile Development and Extreme Programming practices to provide fast pace
quick turnaround results. Our software development model is a mix of Agile Development,
traditional SDLC, and Waterfall models.
|
|
Click here to learn more about C# Consulting. |
|
|
|
|
|
|
|
Introducing MaxV - one click. infinite control. Hyper-V Hosting from MaximumASP.
Finally – a virtual platform that delivers next-generation Windows Server 2008 Hyper-V virtualization technology from a managed hosting partner you can truly depend on. Visit www.maximumasp.com/max for a FREE 30 day trial. Hurry offer ends soon.
Climb aboard the MaxV platform and take advantage of High Availability, Intelligent Monitoring, Recurrent Backups, and Scalability – with no hassle or hidden fees.
As a managed hosting partner focused solely on Microsoft technologies since 2000, MaximumASP is uniquely qualified to provide the superior support that our business is built on. Unparalleled expertise with Microsoft technologies lead to working directly with Microsoft as first to offer IIS 7 and SQL 2008 betas in a hosted environment; partnering in the Go Live Program for Hyper-V; and product co-launches built on WS 2008 with Hyper-V technology.
|
Dynamic PDF
ceTE software specializes in components for dynamic PDF generation and manipulation. The DynamicPDF™ product line allows you to dynamically generate PDF documents, merge PDF documents and new content to existing PDF documents from within your applications.
|
Nevron Chart for .NET 2010.1 Now Available
The leading .NET charting control now features PDF, Flash and Silverlight export, visualization of large datasets and more. Deliver true charting functionality to your BI, Scorecard, Presentation or Scientific apps. Download evaluation now.
|
ASP.NET 4 Hosting
Get 2 Months Free of ASP.NET Hosting for Only $4.95/month! Receive FREE MS SQL and MySQL Databases Including ASP.NET 4/3.5, MVC 3.0, Silverlight 4, Windows 2008/IIS 7.0 Plus FREE IIS 7 Modules. Host UNLIMITED ASP.NET Web Sites – Click Here!
|
|
|
|
|
|
|
|
|
|
|
|
|