C#/VB.NET: Rearrange Columns in Excel

2022-08-24 01:02:17 Written by  support iceblue
Rate this item
(0 votes)

The proper adjustment of the columns' order in Excel can improve readability. For example, by setting the date data as the first column, we can quickly locate data based on a specific date. It is easy to move columns in MS Excel by using Shift and Drag. This article, however, focuses on how to rearrange columns in Excel in C# and VB.NET by using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Reorder Excel Columns in C# and VB.NET

The following are the steps to rearrange columns in Excel using Spire.XLS for .NET.

  • Create a Workbook object, and load a sample Excel file using Workbook.LoadFromFile() method.
  • Get the target worksheet using Workbook.Worksheets[index] property.
  • Specify the new column order in an int array.
  • Create a temporary sheet and copy the data from the target sheet into it.
  • Copy the columns from the temporary sheet to the target sheet and store them in the new order.
  • Remove the temporary sheet.
  • Save the workbook to another Excel file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using System.Linq;
using Spire.Xls;

namespace MoveColumn
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook workbook = new Workbook();

            //Load an Excel file
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");

            //Get the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            //Set the new column order (the column index starts from 0)
            int[] newColumnOrder = new int[] { 3, 0, 1, 2, 4, 5 };

            //Add a temporary worksheet
            Worksheet newSheet = workbook.Worksheets.Add("temp");

            //Copy data from the first worksheet to the temporary sheet
            newSheet.CopyFrom(worksheet);

            //Loop through the newColumnOrder array
            for (int i = 0; i < newColumnOrder.Count(); i++)
            {
                //Copy the column from the temporary sheet to the first sheet
                newSheet.Columns[newColumnOrder[i]].Copy(worksheet.Columns[i], true, true);

                //Set the width of a certain column the first sheet to that of the temporary sheet
                worksheet.Columns[i].ColumnWidth = newSheet.Columns[newColumnOrder[i]].ColumnWidth;
            }

            //Remove temporary sheet
            workbook.Worksheets.Remove(newSheet);

            //Save the workbook to another Excel file
            workbook.SaveToFile("MoveColumn.xlsx", FileFormat.Version2016);
        }
    }
}

C#/VB.NET: Rearrange Columns in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Additional Info

  • tutorial_title:
Last modified on Wednesday, 24 August 2022 01:05