Operating on Excel Row and Column in C#.NET
  1. Home
  2. Excel Editing in .NET
  3. Edit Spreadsheet Row and Column C# Tutorial
Error processing SSI file

How to Manipulate Excel Row and Column in C#.NET Code

Row and column in the Excel is very similar, which is a collection of cells. In most time, the API and property in Row and Column class can be interchangeable.

How to Edit Spreadsheet Row and Column in C# language

iDiTect.Excel provides rows and columns manipulation in worksheet.

Get and select Rows / Columns in Worksheet

Support to get single or multiple lines row and column.

Select single/contiguous/spread columns by column id or column name.

//Get single column by column name var columnA = worksheet.Column("A"); //Get contiguous columns by start/end columnd id var columnBD = worksheet.Columns(2, 4); //Get contiguous columns by column name var columnEF = worksheet.Columns("E:F"); //Get contiguous columns by column id var columnGH = worksheet.Columns("7:8"); //Get spread columns by column name var columnIJ_LM = worksheet.Columns("I:J,L:M");

Select single/contiguous/spread rows by row id.

//Get single row by row id var row1 = worksheet.Row(1); //Get contiguous rows by start/end row id var row24 = worksheet.Rows(2, 4); //Get contiguous rows by row id var row56 = worksheet.Rows("5:6"); //Get spread rows by row id var row78_1011 = worksheet.Rows("7:8,10:11");

Insert Rows and Columns to Worksheet

Call the InsertRowsAbove()/InsertRowsBelow() and InsertColumnsBefore()/InsertColumnsAfter() methods to create single or multiple lines row and column to spreadsheet. Rows/Columns inserting before target row/column is available. Then select row or column by id for further use.

var columnG = worksheet.Column(7); //Insert 2 columns after current column (column G) var insertedColumn = columnG.InsertColumnsAfter(2); //Insert 2 rows above current row (row 7) var insertedRow = worksheet.Row(7).InsertRowsAbove(2);

Height of Row / Width of Column

Support to modify single column width and multiple columns' width.

//Set single column width columnA.Width = 50; //Set multi-column width worksheet.Columns("B:E").Width = 5;

Support to modify single row height and multiple rows' height.

//Set single row height row1.Height = 30; //Set multi-row height worksheet.Rows("2:5").Height = 5;

View full C# code for modifying Row and Column in Excel spreadsheet.

How to Edit Column/Row in Range using C# language

Excel cell, range, row and column are the basic elements in the spreadsheet.

Select Range in Worksheet

Select C3:E5 as a range.

var range = worksheet.Range("C3:E5");

Select Row and Column in Range

Get row and column in range by id.

//Select the second column in the range var columnInRange = range.Column(2); //Select the second row in the range var rowInRange = range.Row(2);

Change Style

Modify selected row and column's style, like text alignment, font, border, etc.

//Change column style columnInRange.Style.Font.FontSize = 20; //Chnage row style rowInRange.Style.Border.OutsideBorder = BorderStyleValues.Thick; rowInRange.Style.Border.OutsideBorderColor = ExcelColor.Blue;

Auto Fit Contents

Call AutoFitContents() method to resize the selected columns to their content. Single target column and all columns are both supported to use this method.

//For column A worksheet.Column(1).AutoFitContents(); //For all columns worksheet.Columns().AutoFitContents();

View full C# code for modifying Row/Column in Excel Range.

Full code for editing Excel spreadsheet Row and Column in C#

public static void ModifyColumns() { var workbook = new Workbook(); var worksheet = workbook.Worksheets.Add("ModifyColumns"); //Get single column by column name var columnA = worksheet.Column("A"); //Set column style columnA.Style.Fill.BackgroundColor = ExcelColor.Red; //Set single column width columnA.Width = 50; //Get contiguous columns by start/end columnd id var columnBD = worksheet.Columns(2, 4); columnBD.Style.Fill.BackgroundColor = ExcelColor.Blue; //Get contiguous columns by column name var columnEF = worksheet.Columns("E:F"); columnEF.Style.Fill.BackgroundColor = ExcelColor.Cadet; //Get contiguous columns by column id var columnGH = worksheet.Columns("7:8"); columnGH.Style.Fill.BackgroundColor = ExcelColor.Dandelion; //Get spread columns by column name var columnIJ_LM = worksheet.Columns("I:J,L:M"); columnIJ_LM.Style.Fill.BackgroundColor = ExcelColor.Ecru; //Set multi-column width worksheet.Columns("B:E").Width = 5; //Get single column by column id var columnG = worksheet.Column(7); //Insert 2 columns after current column (column G) var insertedColumn = columnG.InsertColumnsAfter(2); insertedColumn.Style.Fill.BackgroundColor = ExcelColor.Orange; workbook.Save("ModifyColumns.xlsx"); } public static void ModifyRows() { var workbook = new Workbook(); var worksheet = workbook.Worksheets.Add("ModifyRows"); //Get single row by row id var row1 = worksheet.Row(1); //Set row style row1.Style.Fill.BackgroundColor = ExcelColor.Red; //Set single row height row1.Height = 30; //Get contiguous rows by start/end row id var row24 = worksheet.Rows(2, 4); row24.Style.Fill.BackgroundColor = ExcelColor.Blue; //Get contiguous rows by row id var row56 = worksheet.Rows("5:6"); row56.Style.Fill.BackgroundColor = ExcelColor.Cadet; //Get spread rows by row id var row78_1011 = worksheet.Rows("7:8,10:11"); row78_1011.Style.Fill.BackgroundColor = ExcelColor.Dandelion; //Set multi-row height worksheet.Rows("2:5").Height = 5; //Insert 2 rows above current row (row 7) var insertedRow = worksheet.Row(7).InsertRowsAbove(2); insertedRow.Style.Fill.BackgroundColor = ExcelColor.Orange; workbook.Save("ModifyRows.xlsx"); }

Full code for editing Excel Row/Column in Range using C#

var workbook = new Workbook(); var worksheet = workbook.Worksheets.Add("ColumnRowInRange"); //Add some data to cells for (int row = 1; row < 10; row++) { for (int column = 1; column < 10; column++) { worksheet.Cell(row, column).Value = row + "-" + column; } } //Select C3:E5 as a range var range = worksheet.Range("C3:E5"); range.Style.Fill.BackgroundColor = ExcelColor.Red; //Select the second column in the range var columnInRange = range.Column(2); //Change column style columnInRange.Style.Font.FontSize = 20; //Select the second row in the range var rowInRange = range.Row(2); //Chnage row style rowInRange.Style.Border.OutsideBorder = BorderStyleValues.Thick; rowInRange.Style.Border.OutsideBorderColor = ExcelColor.Blue; worksheet.Columns().AutoFitContents(); workbook.Save("ColumnRowInRange.xlsx");
What iDiTect .NET Document component can do

We provide powerful & profession document & image controls: Mail merge fields in word document through C# you can define a structure for the document and decorate the it with dynamic text. C# - Convert HTML to PDF in .NET converting html to pdf, easy to use, thread safe. How to Convert PDF to Image (JPG or PNG) In C# a C# example to convert PDF page to images, contains jpg, png, tiff, multi-page tiff. Search text in PDF using C# use Find feature to locate specific text. Programmatically convert MS Word DOCX files to PDF in C# Online C# Tutorial for Converting Word to PDF (.pdf) Document. Office Convert DOCX file to HTML file in C# Convert HTML from Word (DOCX) document in C# and VB.NET with iDiTect.Converter component.

More Programming Tutorials
More Programming FAQs

We offer full featured SDK for 30 days evaluation test.

Download Free     Order Now