Excel Automation with SAP Generated Report

• Download source - 929.4 KB
• Download demo - 2.3 KB

Introduction

I still remember my father’s rich uncle who owned an IBM PC in his house in the beginning of ninth decade (which may be equivalent to $6500 nowadays). My father's rich uncle had something called spreadsheets Lotus 1,2,3. It was amazing to play with names and numbers on the green color monitor and it was nothing compared to what we have nowadays.

10 years later, I revisited it while studying engineering in the computer lab. What they called MS-Excel had become available to all of us, although most of us still found it too difficult to deal with the highest benefits of it.

Excel can be seen as simple tables of rows and columns that hold data objects of any type: numbers, texts, symbols, even images. Its built-in functions give the ability to apply simple mathematical operations on its contents of the numerical data, including statistical operations. These are commonly used functions which can be used from a variety of institutions like schools, companies, and research centers.

Nowadays, nearly no one is sitting behind an office without Excel. The flexibility we all touch to skip invalid data from the math function or give error notation and to represent these numerical data in graphical forms.

Excel is used to arrange nearly any kind data in a tabular form and this is the point we are here going through beside highlighting the Excel Automation in C# to access and process the data in the worksheets to get useful information as will be seen in the next example.

Background

I noted many software developing companies and machine designers when they want to view the result data they export it to the Excel like the CAD & Database software.

One of them is the SAP ERP, where you find the Menu item "export" in the Menu list that creates Excel file holds the same data you are currently previewing, the nice idea I noted is how these related data are organized on the Excel sheet, before opening the Excel sheet, let’s try to distribute the following data in a table:

The sample we have here is a company that has 4 departments, 100 customers, 1000 items to sell. The company has already sold many items to most of these customers and issued to them 200 invoices, each invoice records miscellaneous of the sold items, and all these data are related to each other in a way specified by user entries, e.g. the date is a property of the invoice not the customer / the department, but we can report all invoices issued from a date to another for a selected customers by one department..

The SAP generates such report of the selected data and exports this report to Excel in a nice & well arranged form that is easy to read excluding the repetition, and teach us how to arrange relational data in a tabular form as shown below:

As shown the data of Departments, customers, invoice types are divided into Levels by avoiding the repeated texts and the horizontal border lines and use vertical border lines to separate these levels, The rest of this arrangement is clear.

Here in this code, I used Excel automation for simple numerical data representation / Math processing.

Now let’s move to the Excel automation using C#, the sample Excel file I have here is a real sample generated by the SAP, the target of this code was to extract some data from the Excel file using the Excel automation for a specific report.

The required information for this report is to find the sold items that are only classified as machine and to mark those still under warranty, although it is not the normal way to get report from the SAP, but this was an urgent case to get this information till the IT developers find their way.

Using the Code

For simplicity, this code is written in a standard way and the whole code is placed under button click event of the only existing Button in the user interface which is named "Start", over this button the final result is displayed in the richEditBox1, then over it is the user entered number of Lines to be processed.

All is shown here in the following UI:

To operate the Excel Automation, the following lines must be referenced:

using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;

Then initiate Excel Application Object:

excelApp = new Excel.Application();

Now, we have to initiate a workbook which is considered as the Excel File we usually open so we can open as many workbooks as we like simultaneously:

Workbook1 = excelApp.Workbooks.Open(openFileDialog1.FileName,0, 
false, 5, "", "", false, Excel.XlPlatform.xlWindows, 
"", true, false, 0, true, false, false);

Under the workbook, we can establish the usual sheets which we can find as small tabs at the bottom of the page, but here it is named worksheet, so now we shouldn’t mix between the workbook and the worksheet, then to apply the data into:

Worksheet WorkSheet1 = Workbook1.Worksheets[1];     //(Sheet1) at the first Tab 
                                            //where to modify the data         

Worksheet WorkSheet2 = Workbook1.Worksheets[2];     //(Sheet2) where to copy the 
                                            //result machine list under warranty

Worksheet WorkSheet3 = Workbook1.Worksheets[3];     //(Sheet3) where to read 
                                            //the machine list into memory

Inside the code, you will find all the demonstrations step by step, and here below is the Flow Chart to describe the flow of how to access and process the data in the WorkSheets to get useful information: