• Home
  • Product-Life
  • PDF
  • Excel
  • Word
  • My Music Box
It's a long road...

Excel to PDF .Net

A standalone C# library to convert Excel spreadsheets and workbooks to PDF, Word, RTF.

Learn More

C# Excel – Insert Excel Columns

4/11/2012

0 Comments

 
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:

Picture
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...
0 Comments



Leave a Reply.

    Categories

    全部
    Add Background Color
    Add Cell Comments
    C# Open Excel
    Copy Excel Cells
    Draw Excel Borders
    Excel Chart
    Excel Font Style
    Excel Number Style
    Excel Text Alignment
    Excel To Html
    Excel To Image
    Insert Columns
    Insert Rows
    Merge Excel Cells
    Merge Excel Files
    Remove Columns/Rows

    RSS Feed

    Recommend Blog:


    Blu-ray/DVD Backup
    Office Tricks
    No.1 Software Pick

Powered by Create your own unique website with customizable templates.