How to Set Excel Row and Columns with C#

It is usual to find that some data in one column or row is unnecessary in one Excel worksheet. So, we will delete this column or row. Also, we can hide it because the data may be useful in other tables. Besides, we can find that column width and row height is set by default before we export data in worksheet. However, some long data will not be displayed completely if we don’t adjust width and height. Now, in this post, I will introduce something about row and column settings with C#, including hide, delete column and row, set row height and column width.

There is an Excel file which saves some students information, containing Student No., Name, Gender, Birthday, Age and E-mail information. In this file, some columns and rows are not formatted well and I need to hide or delete some unnecessary or important information.

Student Info

Note: I use Spire.XLS in this example, so I add its DLL file as reference in project.

The following steps describes how to set in details.

Step 1: load the file which I want to set column and row from my computer and initialize worksheet which saves data.

            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@”D:\work\My Documents\Student Info.xlsx”, ExcelVersion.Version2010);

            Worksheet sheet = workbook.Worksheets[0];

Step 2: delete age column and information of the student who leaves school.

            sheet.DeleteColumn(5);

            sheet.DeleteRow(17);

Step 3: hide birthday column and information of the student who studies abroad.

            sheet.HideColumn(4);

            sheet.HideRow(18);

Step 4: auto fit the first column and vice-monitor information row.

            sheet.AutoFitColumn(1);

            sheet.AutoFitRow(11);

Step 5:  set column width for E-mail information column and row height for monitor information row.

            sheet.SetColumnWidth(5, 32);

            sheet.SetRowHeight(7, 22);

Step 6: Set format for monitor information row to highlight it.

            sheet.Range[“A7:F7”].Style.VerticalAlignment = VerticalAlignType.Center;

            sheet.Range[“A7:F7”].Style.Font.IsBold = true;

Step 7: save and launch file.

            workbook.SaveToFile(“Row&ColumnSetting.xlsx”, ExcelVersion.Version2010);

            System.Diagnostics.Process.Start(workbook.FileName);

Result:

———————————————————————————————————————————————————————-

Click Here to LEARN MORE about Spire.XLS

Click Here to DOWNLOAD Spire.XLS

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s