Click here to Skip to main content
11,706,801 members (45,693 online)
Click here to Skip to main content

Using C# to Create an Excel Document

, 26 Aug 2007 CPOL 424.7K 19K 100
Rate this:
Please Sign up or sign in to vote.
Shows how to create an Excel document using C# and formatting the cells
Screenshot - excell.gif

Introduction

Microsoft Excel is widely used in business because it offers the ability to present data to business executives in a way that makes sense to them. In this article, we are going to learn how easy it is to create an Excel document using C#.

Background

While looking to find an easy solution of how to read and write into a spreadsheet, I came across an article on the Microsoft site, How To Use ADO.NET to Retrieve and Modify Records in Excel. This is a wonderful article; however, formatting the data and the cosmetics of the document is not possible using this approach.

Using the Code

In order to use the class, you must add as a reference the Microsoft Excel XX.X Object Library in your project; this is a COM component; adding in Visual Studio is straight forward. Right click on your project on the Solution explorer and you should see the add reference option. Once selected, click the COM tab and look for the Excel library.
Once added, cut and paste the class below:

using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
namespace ExcelExample
{
    class CreateExcelDoc
    {
        private Excel.Application app = null;
        private Excel.Workbook workbook = null;
        private Excel.Worksheet worksheet = null;
        private Excel.Range workSheet_range = null;
        public CreateExcelDoc()
        {
            createDoc(); 
        }
        public void createDoc()
        {
            try
            {       
                app = new Excel.Application();
                app.Visible = true;
                workbook = app.Workbooks.Add(1);
                worksheet = (Excel.Worksheet)workbook.Sheets[1];
            }
            catch (Exception e)
            {
                Console.Write("Error");
            }
            finally
            {
            }
        }

public void createHeaders(int row, int col, string htext, string cell1,
string cell2, int mergeColumns,string b, bool font,int size,string
fcolor)
        {
            worksheet.Cells[row, col] = htext;
            workSheet_range = worksheet.get_Range(cell1, cell2);
            workSheet_range.Merge(mergeColumns);
            switch(b)
            {
                case "YELLOW":
                workSheet_range.Interior.Color = System.Drawing.Color.Yellow.ToArgb();
                break;
                case "GRAY":
                    workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
                break;
                case "GAINSBORO":
                    workSheet_range.Interior.Color = 
			System.Drawing.Color.Gainsboro.ToArgb();
                    break;
                case "Turquoise":
                    workSheet_range.Interior.Color = 
			System.Drawing.Color.Turquoise.ToArgb();
                    break;
                case "PeachPuff":
                    workSheet_range.Interior.Color = 
			System.Drawing.Color.PeachPuff.ToArgb();
                    break;
                default:
                  //  workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
                    break;
            }
         
            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            workSheet_range.Font.Bold = font;
            workSheet_range.ColumnWidth = size;
            if (fcolor.Equals(""))
            {
                workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb();
            }
            else {
                workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
            }
        }

        public void addData(int row, int col, string data, 
			string cell1, string cell2,string format)
        {
            worksheet.Cells[row, col] = data;
            workSheet_range = worksheet.get_Range(cell1, cell2);
            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            workSheet_range.NumberFormat = format;
        }    
    }
}

Let's Go Over the Class

The first thing we do is declare the variables that are responsible for creating an Excel document.

  • private Excel.Application app = null; creates the Excel document
  • private Excel.Workbook workbook = null; create the workbook in the recently created document
  • private Excel.Worksheet worksheet = null; allows us to work with current worksheet
  • private Excel.Range workSheet_range = null; allows us to modify cells on the sheet

Note that the variables are not initialized; since they will be initialized in the constructor.

The Class has Two Methods

The class has only two methods: createHeaders and addData. Both of them take almost similar arguments. The createHeaders takes more arguments since headers must be noticeable as such. Let's explain what the arguments are:

  • row,col -- They are used to tell Excel where in the cell we want the text
  • htext -- This variable will hold the text for the header
  • cell1,cell2 -- This will be used to specify what cells we will use, e.g. A1:B1
  • mergeColumns -- It holds the number of cells we want to merge in a cell
  • b -- It will hold the color of the background for the chosen cell
  • font -- True or False for the font of the text on the selected cell
  • size -- To specify the size of the cell
  • fcolor-- Specify the color font

Below is the non-similar variable used in the addData method:

  • format -- It is used to tell Excel what time of format we want

How to Use the Class

Below is an example of how to use the class and if everything goes well; you should see the spreadsheet.

CreateExcelDoc excell_app = new CreateExcelDoc();         
//creates the main header
excell_app.createHeaders(5, 2, "Total of Products", "B5", "D5", 2,"YELLOW",true,10,"n");
//creates subheaders
excell_app.createHeaders(6, 2, "Sold Product", "B6", "B6", 0, "GRAY", true,10,"");
excell_app.createHeaders(6, 3, "", "C6", "C6", 0, "GRAY", true,10,"");
excell_app.createHeaders(6, 4, "Initial Total", "D6", "D6", 0, "GRAY", true,10,"");
//add Data to cells
excell_app.addData(7, 2, "114287", "B7", "B7","#,##0");
excell_app.addData(7, 3, "", "C7", "C7", "");
excell_app.addData(7, 4, "129121", "D7", "D7", "#,##0");
//add percentage row
excell_app.addData(8, 2, "", "B8", "B8", "");
excell_app.addData(8, 3, "=B7/D7", "C8", "C8", "0.0%");
excell_app.addData(8, 4, "", "D8", "D8", "");
//add empty divider
excell_app.createHeaders(9, 2, "", "B9", "D9", 2, "GAINSBORO", true, 10, "");

Enjoy it!

History

  • 26th August, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Hector Pacheco
Software Developer
United States United States
Currently working as an application/web developer for a Hospital in New York City.
I also do consulting work on the side.

You may also be interested in...

Comments and Discussions

 
QuestionMerge Rows Pin
Member 1095561420-Aug-15 3:34
memberMember 1095561420-Aug-15 3:34 
QuestionFor those facing an assembly reference issue Pin
Louis Tadman28-Jul-15 11:05
memberLouis Tadman28-Jul-15 11:05 
Questionhow save it in name.xlsx Pin
farhad dalirani22-Jun-15 0:07
memberfarhad dalirani22-Jun-15 0:07 
GeneralA similar github project: https://github.com/geoplex/openxmlutils Pin
Member 1172407627-May-15 14:49
memberMember 1172407627-May-15 14:49 
GeneralThanks! Pin
dliwespf11-Sep-14 6:09
memberdliwespf11-Sep-14 6:09 
GeneralRe: Thanks! Pin
kerrymarke27-Oct-14 19:55
memberkerrymarke27-Oct-14 19:55 
Questionassembly reference issue Pin
fresh_girl20-Apr-14 21:51
memberfresh_girl20-Apr-14 21:51 
QuestionNice Article..!!! Pin
Amol Jadhao4-Feb-14 22:29
memberAmol Jadhao4-Feb-14 22:29 
QuestionAbout Excel Error Pin
Imran.yousuf.52-Oct-13 10:23
memberImran.yousuf.52-Oct-13 10:23 
AnswerRe: About Excel Error Pin
Hector Pacheco3-Oct-13 2:49
memberHector Pacheco3-Oct-13 2:49 
AnswerRe: About Excel Error Pin
Imran.yousuf.54-Oct-13 0:17
memberImran.yousuf.54-Oct-13 0:17 
QuestionSaving excel file Pin
Maylortaylor7-Aug-13 6:48
memberMaylortaylor7-Aug-13 6:48 
AnswerRe: Saving excel file Pin
Amol Jadhao4-Feb-14 22:32
memberAmol Jadhao4-Feb-14 22:32 
GeneralRe: Saving excel file Pin
Zeefee Shaikh16-Jul-14 19:20
memberZeefee Shaikh16-Jul-14 19:20 
Questionmissing reference? Pin
Maylortaylor7-Aug-13 6:04
memberMaylortaylor7-Aug-13 6:04 
AnswerRe: missing reference? Pin
Maylortaylor7-Aug-13 6:13
memberMaylortaylor7-Aug-13 6:13 
QuestionGetting error: System.NullReferenceException: Object reference not set to an instance of an object. Pin
Julius Lule2-May-13 0:57
memberJulius Lule2-May-13 0:57 
AnswerRe: Getting error: System.NullReferenceException: Object reference not set to an instance of an object. Pin
jasygerges201528-Apr-15 4:20
memberjasygerges201528-Apr-15 4:20 
GeneralRe: Getting error: System.NullReferenceException: Object reference not set to an instance of an object. Pin
Hector Pacheco1-May-15 3:41
memberHector Pacheco1-May-15 3:41 
GeneralRe: Getting error: System.NullReferenceException: Object reference not set to an instance of an object. Pin
jasygerges201510-May-15 23:31
memberjasygerges201510-May-15 23:31 
Questionmaking CSV file Pin
vishal.goyal@indiabulls18-Mar-13 22:30
membervishal.goyal@indiabulls18-Mar-13 22:30 
GeneralMy vote of 4 Pin
odairsk814-Jan-13 9:54
memberodairsk814-Jan-13 9:54 
GeneralMy vote of 5 Pin
Member 945184423-Sep-12 7:40
memberMember 945184423-Sep-12 7:40 
QuestionUsing C# to Create an Excel Document--Excel.Application--abstract clas--creting object Pin
nav@123412-Jun-12 20:50
membernav@123412-Jun-12 20:50 
GeneralMy vote of 5 Pin
karamusti9-May-12 1:59
memberkaramusti9-May-12 1:59 
GeneralMy vote of 4 Pin
codeprojectnidaved8-May-12 16:43
membercodeprojectnidaved8-May-12 16:43 
GeneralMy vote of 1 Pin
Webservice548626-Mar-12 9:49
memberWebservice548626-Mar-12 9:49 
QuestionCreating nicely formatted Excel files, without Excel automation Pin
aron.sinoai24-Feb-12 4:57
memberaron.sinoai24-Feb-12 4:57 
AnswerRe: Creating nicely formatted Excel files, without Excel automation Pin
aron.sinoai4-Oct-12 0:01
memberaron.sinoai4-Oct-12 0:01 
QuestionWhere do you dispose your excel objects Pin
ChenNoam6-Dec-11 23:04
memberChenNoam6-Dec-11 23:04 
GeneralMy vote of 4 Pin
Smileychonggo13-Oct-11 15:05
memberSmileychonggo13-Oct-11 15:05 
GeneralMy vote of 5 Pin
SAGKAN3-Oct-11 19:00
memberSAGKAN3-Oct-11 19:00 
QuestionThanks Pin
mphoenixe29-Jul-11 20:39
membermphoenixe29-Jul-11 20:39 
GeneralAmazing!!!! Pin
sing72917-May-11 18:40
membersing72917-May-11 18:40 
GeneralMy vote of 5 Pin
tomi_111-Apr-11 22:08
membertomi_111-Apr-11 22:08 
GeneralMy vote of 2 Pin
Bjerner28-Sep-10 20:50
memberBjerner28-Sep-10 20:50 
QuestionHow to Make Bold in Part of an Excel Cell - Need Eargent help!!!!!!! Pin
supuna2u12-Sep-10 17:25
membersupuna2u12-Sep-10 17:25 
GeneralAwesome Pin
Tuan Jinn31-Aug-10 23:07
memberTuan Jinn31-Aug-10 23:07 
QuestionBackground color is different !!! Pin
samprog30-Jun-10 1:05
membersamprog30-Jun-10 1:05 
AnswerRe: Background color is different !!! Pin
DLChambers28-Jul-10 10:54
memberDLChambers28-Jul-10 10:54 
GeneralRe: Background color is different !!! Pin
ChrisDesjardins2-Apr-12 5:00
memberChrisDesjardins2-Apr-12 5:00 
Generalnice Pin
guzufeng24-May-10 16:05
memberguzufeng24-May-10 16:05 
QuestionHow to merge cells from two adjacent rows e.g A1 and A2? Pin
subaganapathyraman11-May-10 3:12
membersubaganapathyraman11-May-10 3:12 
QuestionExport to excel very slow.. any ideas how to make it faster?? Pin
Kiran Sabinkar20-Oct-09 23:43
memberKiran Sabinkar20-Oct-09 23:43 
AnswerRe: Export to excel very slow.. any ideas how to make it faster?? Pin
Hector Pacheco21-Oct-09 16:24
memberHector Pacheco21-Oct-09 16:24 
GeneralRe: Export to excel very slow.. any ideas how to make it faster?? Pin
Kiran Sabinkar21-Oct-09 20:28
memberKiran Sabinkar21-Oct-09 20:28 
GeneralRe: Export to excel very slow.. any ideas how to make it faster?? Pin
Hector Pacheco24-Oct-09 12:48
memberHector Pacheco24-Oct-09 12:48 
GeneralRe: Export to excel very slow.. any ideas how to make it faster?? Pin
Kiran Sabinkar24-Oct-09 20:41
memberKiran Sabinkar24-Oct-09 20:41 
AnswerRe: Export to excel very slow.. any ideas how to make it faster?? Pin
gg423715-Nov-09 23:43
membergg423715-Nov-09 23:43 
GeneralUsing Microsoft Open XML Format SDK to generate Excel files is much easier Pin
Maciej Gren21-Aug-09 8:14
memberMaciej Gren21-Aug-09 8:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150819.1 | Last Updated 26 Aug 2007
Article Copyright 2007 by Hector Pacheco
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid