How to Insert Excel Rows
When we handle Excel spreadsheet, we usually need insert Excel rows to add data information. Insert Excel rows in Excel worksheet can be very easy through Microsoft Excel. Here in this post, you will learn another solution to insert Excel rows 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 rows.
With the help of Spire.XLS, we can easily use C# to insert Excel rows. Download Spire.XLS and installed on system. Follow the simple guide below to insert Excel rows.
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 System.Drawing;
using System.Data;
using System.Data.OleDb;
using Spire.Xls;
namespace AddRow
{
class Program
{
static void Main(string[] args)
{
}
}
}
Step 2 Load Excel Document
Put the Excel file into the project folder. Use the code below to load Excl workbook in which we will insert rows in.
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\Data\parts.xls");
Worksheet sheet = workbook.Worksheets[0];
Step 3 Insert Rows with Data Information
By using Spire.XLS, we can insert multiple rows in Excel worksheet and also can insert single row. Furthermore, Spire.XLS allows uses to set the rows style at will.
//add 3 rows in sheet
sheet.InsertRow(3,3);
//set the new rows' style
sheet.Range["A3:G5"].Style.Color = Color.GreenYellow;
sheet.Range["A3:G5"].Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range["A3:G5"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.Range["A3:G5"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.Range["A3:B5"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["C3:C5"].HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range["D3:G5"].HorizontalAlignment = HorizontalAlignType.Right;
//add one row in sheet
sheet.InsertRow(7);
//set the style of the added row
sheet.Range["A7:G7"].Style.Color = Color.GreenYellow;
sheet.Range["A7:G7"].Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range["A7:G7"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.Range["A7:G7"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.Range["A7:B7"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["C7"].HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range["D7:G7"].HorizontalAlignment = HorizontalAlignType.Right;
Step 4 Import Data into the New Rows
//insert data into the new rows
DataTable parts = GetData();
for (int i = 3,datarowIndex=8; i <= 5;datarowIndex++, i++)
{
for (int j = 1; j <= parts.Columns.Count; j++)
{
sheet.Range[i, j].Value2 = parts.Rows[datarowIndex][j - 1];
}
}
for (int j = 1; j <= parts.Columns.Count; j++)
{
sheet.Range[7, j].Value2 = parts.Rows[6][j - 1];
}
static private DataTable GetData()
{
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\..\\..\\Data\\database.accdb;Persist Security Info=False;";
string sqlParts = "select * from parts";
DataTable parts = new DataTable();
using(OleDbConnection conn=new OleDbConnection(connString))
{
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlParts, conn);
adapter.Fill(parts);
}
return parts;
}
Effective Screenshot:
With the help of Spire.XLS, we can easily use C# to insert Excel rows. Download Spire.XLS and installed on system. Follow the simple guide below to insert Excel rows.
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 System.Drawing;
using System.Data;
using System.Data.OleDb;
using Spire.Xls;
namespace AddRow
{
class Program
{
static void Main(string[] args)
{
}
}
}
Step 2 Load Excel Document
Put the Excel file into the project folder. Use the code below to load Excl workbook in which we will insert rows in.
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\Data\parts.xls");
Worksheet sheet = workbook.Worksheets[0];
Step 3 Insert Rows with Data Information
By using Spire.XLS, we can insert multiple rows in Excel worksheet and also can insert single row. Furthermore, Spire.XLS allows uses to set the rows style at will.
//add 3 rows in sheet
sheet.InsertRow(3,3);
//set the new rows' style
sheet.Range["A3:G5"].Style.Color = Color.GreenYellow;
sheet.Range["A3:G5"].Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range["A3:G5"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.Range["A3:G5"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.Range["A3:B5"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["C3:C5"].HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range["D3:G5"].HorizontalAlignment = HorizontalAlignType.Right;
//add one row in sheet
sheet.InsertRow(7);
//set the style of the added row
sheet.Range["A7:G7"].Style.Color = Color.GreenYellow;
sheet.Range["A7:G7"].Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range["A7:G7"].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.Range["A7:G7"].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.Range["A7:B7"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["C7"].HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range["D7:G7"].HorizontalAlignment = HorizontalAlignType.Right;
Step 4 Import Data into the New Rows
//insert data into the new rows
DataTable parts = GetData();
for (int i = 3,datarowIndex=8; i <= 5;datarowIndex++, i++)
{
for (int j = 1; j <= parts.Columns.Count; j++)
{
sheet.Range[i, j].Value2 = parts.Rows[datarowIndex][j - 1];
}
}
for (int j = 1; j <= parts.Columns.Count; j++)
{
sheet.Range[7, j].Value2 = parts.Rows[6][j - 1];
}
static private DataTable GetData()
{
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\..\\..\\Data\\database.accdb;Persist Security Info=False;";
string sqlParts = "select * from parts";
DataTable parts = new DataTable();
using(OleDbConnection conn=new OleDbConnection(connString))
{
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlParts, conn);
adapter.Fill(parts);
}
return parts;
}
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...