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:
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...
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...