Write Excel data in C#

example

This example shows how to write Excel data into a workbook. You can do that by opening the Excel application and use it as a server to manipulate Excel workbooks.

First open the Add References dialog. On the COM tab, select “Microsoft Excel 14.0 Object Library” (or whatever version you have installed on your system).

Next add the following using statement to make working with the Excel namespace easier. The Excel = part means you can use Excel as an alias for the namespace.

using Excel = Microsoft.Office.Interop.Excel;

This example uses the following code to open a workbook, add a new worksheet to it, write to the worksheet, save the changes, and close everything.

// Write into the Excel workbook.
private void btnWrite_Click(object sender, EventArgs e)
{
    // Get the Excel application object.
    Excel.Application excel_app = new Excel.ApplicationClass();

    // Make Excel visible (optional).
    excel_app.Visible = true;

    // Open the workbook.
    Excel.Workbook workbook = excel_app.Workbooks.Open(
        txtFile.Text,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
        Type.Missing, Type.Missing);

    // See if the worksheet already exists.
    string sheet_name = DateTime.Now.ToString("MM-dd-yy");
    
    Excel.Worksheet sheet = FindSheet(workbook, sheet_name);
    if (sheet == null)
    {
        // Add the worksheet at the end.
        sheet = (Excel.Worksheet)workbook.Sheets.Add(
            Type.Missing, workbook.Sheets[workbook.Sheets.Count],
            1, Excel.XlSheetType.xlWorksheet);
        sheet.Name = DateTime.Now.ToString("MM-dd-yy");
    }

    // Add some data to individual cells.
    sheet.Cells[1, 1] = "A";
    sheet.Cells[1, 2] = "B";
    sheet.Cells[1, 3] = "C";

    // Make that range of cells bold and red.
    Excel.Range header_range = sheet.get_Range("A1", "C1");
    header_range.Font.Bold = true;
    header_range.Font.Color = 
        System.Drawing.ColorTranslator.ToOle(
            System.Drawing.Color.Red);
    header_range.Interior.Color = 
        System.Drawing.ColorTranslator.ToOle(
            System.Drawing.Color.Pink);

    // Add some data to a range of cells.
    int[,] values = 
    { 
        { 2,  4,  6},
        { 3,  6,  9},
        { 4,  8, 12},
        { 5, 10, 15},
    };
    Excel.Range value_range = sheet.get_Range("A2", "C5");
    value_range.Value2 = values;

    // Save the changes and close the workbook.
    workbook.Close(true, Type.Missing, Type.Missing);

    // Close the Excel server.
    excel_app.Quit();

    MessageBox.Show("Done");
}

First the code creates an Excel.Application object to control Excel. It makes that object visible so you can watch it work. Often you will not want to make the server visible.

Next the code opens a workbook. Most of the Open method’s parameters are optional (things such as passwords and flags indicating that you want to open the file read-only) so they are given the value Type.Missing.

The code then calls the FindSheet method described shortly to see if the workbook contains a worksheet named after the current date. (Sorry about hard-coding in the date format. Normally I would use DateTime.Now.ToShortDate() to get an appropriate date but, for me at least, it would contain / characters, which are not allowed in worksheet names.)

If there is no worksheet with that name, the code creates one, adding it after the last worksheet, and sets the sheet’s name to the current date.

Next the code sets the values of three cells individually. It makes a Range representing those three cells and sets their Font.Bold, Color, and Interior.Color values.

The code then shows how to set worksheet values from an array. It creates a 2-dimensional array, makes a Range of the same size, and sets the Range object’s Value2 property to the array to set the cells’ values.

Finally the code closes the workbook, saving the changes, and closes the Excel server.

The following code shows the FindSheet method.

// Return the worksheet with the given name.
private Excel.Worksheet FindSheet(Excel.Workbook workbook, string sheet_name)
{
    foreach (Excel.Worksheet sheet in workbook.Sheets)
    {
        if (sheet.Name == sheet_name) return sheet;
    }

    return null;
}

This method simply loops through the workbook’s worksheets to see if it contains one with the given name.

Much of the work in this kind of Office automation is figuring out what objects in the Office object model do the things you want. For example, figuring out how to use Word’s InlineShape and Shape objects to create and format the picture. If you want to do a lot of this, my book Microsoft Office Programming: A Guide for Experienced Developers may help. The code is in Visual Basic and it’s a few years old, but it should help you figure out how to manipulate the Word, Excel, PowerPoint, Access, and Outlook object models, and those models haven’t changed much since the book was written.


Download Example   Follow me on Twitter   RSS feed   Donate




This entry was posted in Excel, Office and tagged , , , , , , , , , . Bookmark the permalink.

2 Responses to Write Excel data in C#

  1. Dietmar Schoder says:

    I use this Export2Excel.dll. It is simple and very fast.

  2. Rohit says:

    Please help, i want to find specific text and apply bold on that that only in all cells of excel using c#.

Leave a Reply

Your email address will not be published. Required fields are marked *