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