C# Excel – Insert Excel Columns

How to Insert Excel Columns

When we handle Excel spreadsheet, we usually need insert Excel columns to add data information. Insert Excel columns in Excel worksheet can be very easy through Microsoft Excel. Here in this post, you will learn another solution to insert Excel columns which you even don’t need Microsoft Excel installed on system. This solution requires some basic programming skills because we will use C# to insert Excel columns.

With the help of Spire.XLS, we can easily use C# to insert Excel columns. Download Spire.XLS and installed on system. Follow the simple guide below to insert Excel columns.

Step 1 Create Project
Create a C# project in Visual Studio and Add Spire.Xls.dll as reference. The default setting of Spire.Xls.dll is placed under "C:\Program Files\e-iceblue\Spire.Xls\Bin”. Select assembly Spire.Xls.dll and click OK to add it to the project.

using Spire.Xls;
namespace deletecolumns
{
    class Program
    {
        static void Main(string[] args)
        {
        }
    }
}

Step 2 Load Excel Document
Put the Excel file which we want to insert columns into project folder. Use the code below to load it into the project.

            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\Sample.xls");
            Worksheet sheet = workbook.Worksheets[0];

Step 3 Insert Columns with Data Information
We can use Spire.XLS to insert one column with data information. And we can also insert multiple columns with data information either. Furthermore, Spire.XLS enables users to set format.

            //insert one column
            sheet.InsertColumn(3);

            //get the data of the new column
            DataColumn dataColumn = GetColumnData(4);

            //insert the data into the new column
            sheet.InsertDataColumn(dataColumn, true, 1, 3);

            //insert two columns
            sheet.InsertColumn(5, 2);

            //get the data of the two new columns
            DataColumn dataColumn4 = GetColumnData(5);
            DataColumn dataColumn5 = GetColumnData(6);

            //insert the data into the new columns
            sheet.InsertDataColumn(dataColumn4, true, 1, 5);
            sheet.InsertDataColumn(dataColumn5, true, 1, 6);

            int rowCount = 0;
            rowCount = sheet.Rows.Length;

            //set the added columns's style
            SetFormat(sheet, 3,rowCount);
            SetFormat(sheet, 5,rowCount);
            SetFormat(sheet, 6,rowCount);

Details to Get data information

        static private DataColumn GetColumnData(int columnIndex)
        {
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\..\\..\\Data\\database.accdb;Persist Security Info=False;";
            DataTable parts = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                string sqlParts = "select * from parts";
                OleDbDataAdapter adapter = new OleDbDataAdapter(sqlParts, conn);
                adapter.Fill(parts);
            }
            DataColumn column = parts.Columns[columnIndex];
            return column;
        }

Details to Set Format

        static private void SetFormat(Worksheet sheet, int columnIndex,int rowCount)
        {
            //set the header style
            sheet.Range[1, columnIndex].Style.Font.IsBold = true;
            sheet.Range[1, columnIndex].Style.Color = Color.DeepSkyBlue;
            sheet.Range[1, columnIndex].HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range[1, columnIndex].AutoFitColumns();

            //draw the lines
            sheet.Range[1, columnIndex, rowCount-1, columnIndex].Style.Borders.LineStyle = LineStyleType.Thin;
            sheet.Range[1, columnIndex, rowCount-1, columnIndex].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.Range[1, columnIndex, rowCount-1, columnIndex].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;

            //set the data rows's color
            sheet.Range[2, columnIndex, rowCount-1, columnIndex].Style.Color = Color.SkyBlue;
        }

Step 4 Save

            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);

Effective Screenshot:

More about Spire.XLS

Spire.XLS is a professional Excel component which enables developers/programmers to fast generate, read, write and modify Excel document for .NET and Silverlight. It supports C#, VB.NET, ASP.NET, ASP.NET MVC and Silverlight. Click to learn more...