Monday, August 25, 2014

Excel: Write to / Read From Cells

After I explained in the previous posts, how to include Excel in C#, how to create a new document or how to open one, I today want to show, how one can access the contents of the documents. This is pretty easy, via the property Range of an ExcelWorksheet one can access the single cells. The desired cell is either selected by specifying Row,Column or, like in Excel, by LetterNumber. Via the property Value then the content of the cell can be accessed. Note that Value contains a string, if the cell contains text, or a double, if the cell contains a number.
The following code creates a new Excel file, writes text in the cells 2,1 and B4, and eventually reads out the content of B4:

            Microsoft.Office.Interop.Excel.Application ExcelApp;
            Workbook ExcelWorkbook;
            Worksheet ExcelWorksheet;

            ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            ExcelWorkbook = ExcelApp.Workbooks.Add();
            ExcelWorksheet = (Worksheet)ExcelWorkbook.Worksheets.get_Item(1);

            ExcelWorksheet.Cells[2, 1].Value = "Cell 2-1";
            ExcelWorksheet.Range["B4"].Value = "Cell B4";

            string Read = ExcelWorksheet.Range["B4"].Value;

            ExcelWorkbook.SaveAs("MyFirstExcelSheet.xls");
            ExcelWorkbook.Close();
            ExcelApp.Quit();

Tuesday, August 19, 2014

Collapse All Code Sections

Today I want to show you a little trick I learned today. It is about the collapsing of all code sections, that means, hiding all code segments, which increases the readability of the code view. In Visual Studio .Net we can achieve this via the hotkey Ctrl + M + L.

Monday, August 11, 2014

Open Excel Document

After I explained in the previous post basics about the integration of Excel in C# and the creation and saving of a new document, I want to show in this post how to open an Excel document with C#.
For this we use the function Microsoft.Office.Interop.Excel.Application.Workbooks.Open().
For it many overloadings exist, which are described here. Here we pass just the filename, the corresponding file is then opened via the Excel control and can be used. The complete code is:

            Microsoft.Office.Interop.Excel.Application ExcelApp;
            Workbook ExcelWorkbook;

            ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            ExcelWorkbook = ExcelApp.Workbooks.Open("MyFirstExcelSheet.xls");
            ExcelWorkbook.Close();
            ExcelApp.Quit();

Monday, August 4, 2014

Create and Save an Excel Document

After I wrote some time ago a little tutorial about the inclusion of Word in C#, I today want to start a posting series about the inclusion of Excel in C#.
In today's post I will describe the integration of Excel, the creation of a new document and the saving of it.
To be able to use Excel, we first have to add a reference to Microsoft Excel 14.0 Object Library (or an arbitrary other version number, depending on the Excel version), which is located in COM in references. Then we include this to our project via using: using Microsoft.Office.Interop.Excel;
Afterwards we create 3 variables for needed objects, an Excel application, a workbook and a table (a worksheet):

            Microsoft.Office.Interop.Excel.Application ExcelApp;
            Workbook ExcelWorkbook;
            Worksheet ExcelWorksheet;

(The type Application is ambigous, that is why we have to append the included namespace before it.)
The creation and saving of an Excel document is then relatively straight forward:

                ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                ExcelWorkbook = ExcelApp.Workbooks.Add();
                ExcelWorksheet = (Worksheet)ExcelWorkbook.Worksheets.get_Item(1);
                ExcelWorkbook.SaveAs("MyFirstExcelSheet.xls");
                ExcelWorkbook.Close();
                ExcelApp.Quit();

First we create the application and the workbook, then we access its first worksheet. Via the function SaveAs() we save the worksheet, if no path is specified it is saved in the folder "Documents". The function SaveAs() accepts many other arguments, which I do not want to explain here though, a detailed description can be found here.
If the file is already existing a dialog pops up, asking the user whether to overwrite the file or not. Unfortunately though to this argument via C# there seems to be no access, as the only workaround to get rid of this I found the following solution in the internet (which works without problems): Via ExcelApp.DisplayAlerts = false; we disable the output of alerts by the Excel app, the overwriting is then done automatically.
The complete code looks as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace Excel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application ExcelApp;
            Workbook ExcelWorkbook;
            Worksheet ExcelWorksheet;

            try
            {
                ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                ExcelWorkbook = ExcelApp.Workbooks.Add();
                ExcelWorksheet = (Worksheet)ExcelWorkbook.Worksheets.get_Item(1);
                ExcelApp.DisplayAlerts = false;
                ExcelWorkbook.SaveAs("MyFirstExcelSheet.xls");
                ExcelWorkbook.Close();
                ExcelApp.Quit();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}