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());
            }
        }
    }
}

No comments:

Post a Comment