Add Row in Excel Workbook in C#, VB.NET

  • Demo
  • C# source
  • VB.Net source

The sample demonstrates how to add workbook row via Spire.XLS.

using System.Drawing;
using System.Data;
using System.Data.OleDb;
using Spire.Xls;

namespace AddRow
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\..\..\Data\parts.xls");
            Worksheet sheet = workbook.Worksheets[0];
            
            //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;

           //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];
            }
            
           
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
        }

        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;
        }
    }
}

Imports System.Drawing
Imports System.Data
Imports System.Data.OleDb
Imports Spire.Xls

Namespace AddRow
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\..\..\..\Data\parts.xls")
			Dim sheet As Worksheet = workbook.Worksheets(0)

			'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

		   'insert data into the new rows
			Dim parts As DataTable = GetData()
			Dim i As Integer = 3
			Dim datarowIndex As Integer=8
			Do While i <= 5
				For j As Integer = 1 To parts.Columns.Count
					sheet.Range(i, j).Value2 = parts.Rows(datarowIndex)(j - 1)
				Next j
				datarowIndex += 1
				i += 1
			Loop

			For j As Integer = 1 To parts.Columns.Count
				sheet.Range(7, j).Value2 = parts.Rows(6)(j - 1)
			Next j


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

		Private Shared Function GetData() As DataTable
			Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\..\..\Data\database.accdb;Persist Security Info=False;"
			Dim sqlParts As String = "select * from parts"
			Dim parts As New DataTable()
			Using conn As New OleDbConnection(connString)
				Dim adapter As New OleDbDataAdapter(sqlParts, conn)
				adapter.Fill(parts)
			End Using
			Return parts
		End Function
	End Class
End Namespace