How to Insert Rows and Columns in Worksheet with C#

Although the data has been imported to an Excel worksheet, sometimes, we need to insert rows or columns for writing additional data information. For example, there is an Excel file about employees contact information with several groups, each of which shows information in one department. If a new employee joins one department, we need to insert a new row in the corresponding group and writing his/her contact information.

This post focuses on how to insert rows and columns in Excel worksheet with C#. Because Spire.XLS for .NET is used in this example, so please download and install it on your system at the beginning. Then, add necessary reference, Spire.XLS dll file and System.Drawing.

STEPS

  1. Load file which we want to insert rows and columns and initialize Worksheet.
  2. Insert row with worksheet.InsertRow(). If we just want to add one row, pass one parameter to this method. This parameter sepcifies where to insert the row.
  3. Copy contents from other rows to new rows by using worksheet.Copy(). There are three parameters in this method, the source range and a bool value.
  4. Save and launch file.

Main Coding:

using System;
using System.Drawing;
using Spire.Xls;

namespace InsertRowandColumn
{
    class InsertRC
    {
        static void Main(string[] args)
        {

            //Load File and Initialize Worksheet
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\Work\My Documents\Student Info.xlsx”);
            Worksheet worksheet = workbook.Worksheets[0];

            //Insert Blank Row
            worksheet.InsertRow(3);

            //Insert Row with Copying Data from Other Rows
            worksheet.InsertRow(13, 2);
            worksheet.Copy(worksheet.Range[“A11:E12”],worksheet.Range[“A13:E14”],true);

            //Set Style for New Rows
            worksheet.Range[“A13:E14”].Style.Color = Color.LightCyan;
            worksheet.Range[“A13:E14”].Style.Font.IsBold = true;

            //Insert Blank Column
            worksheet.InsertColumn(1);

            //Insert Column with Copying Data from Other Columns
            worksheet.InsertColumn(7, 2);
            worksheet.Copy(worksheet.Range[“D1:E21”], worksheet.Range[“G1:H21”], true);

            //Set Style for New Columns
            worksheet.Range[“G1:H21”].Style.Font.IsBold = true;
            worksheet.Range[“G1:H21”].Style.Color = Color.LightCyan;

            //Save and Launch File
            workbook.SaveToFile(“InsertRC.xlsx”,ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Result Shown As Following:

Freely Download Spire.XLS for .NET

Advertisements

10 thoughts on “How to Insert Rows and Columns in Worksheet with C#

  1. balakumardotk says:

    If Any one want To use Only Micro Soft Code Log :

    Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 3];
    Microsoft.Office.Interop.Excel.Range column = rng.EntireColumn;
    column.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, false);

  2. Emory says:

    Thank you a bunch for sharing this with all people you really
    recognize what you’re talking about! Bookmarked. Kindly also talk over with my site =).

    We will have a link change arrangement between us

  3. Sex shop Madrid says:

    I think this is among the most vital information for me.
    And i’m happy reading your article. But wanna observation on few general issues, The site style is
    wonderful, the articles is really excellent : D.
    Good task, cheers

  4. learn css says:

    Howdy I am so excited I found your blog page, I really found you by mistake,
    while I was browsing on Aol for something else, Anyways I am here now
    and would just like to say kudos for a marvelous post and a
    all round exciting blog (I also love the theme/design), I don’t have time to browse it all at the moment but I have saved it and
    also included your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the superb job.

  5. learn css says:

    Howdy I am so excited I found your blog page, I really found you by mistake,
    while I was browsing on Aol for something else, Anyways I am here now
    and would just like to say kudos for a marvelous post and a
    all round exciting blog (I also love the theme/design), I don’t have time to browse it all at the moment but I have saved it and
    also included your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the superb job.

  6. multiple choice ounce says:

    Hi! Someone in my Facebook group shared this site with us so I came to give it a look.
    I’m definitely loving the information. I’m book-marking and will be tweeting this to my followers!

    Exceptional blog and wonderful style and design.

  7. Leo Gurdian says:

    Great article! Nice and clean. I really like the color coded format. Now, to take this to the next level – a dynamic capture of that newly added column would be just off the charts to make this thing really robust.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s