Change Column Position in Excel Worksheet using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
1

I would like to switch the positions of columns in an existing worksheet (i.e. move existing column C in sheet 1 to column B position in sheet 1... and have existing column B data automatically be shifted to the Column C position) . Is this possible?

2

Hi,

Well, you may try any of the following approaches or workaround to perform your task.
1) Try MoveTo method for the Range class. You may try to move the B column’s data to some blank column. Now, move the Column C to B column’s position, then move the original B column to C column’s position now.

Sample code:
Workbook book = new Workbook(@“e:\test\Book1.xls”);
Worksheet sheet = book.Worksheets[0];

Range r1 = sheet.Cells.CreateRange(“B1”, “B65536”);
//Move to D column – we suppose it is a blank column
r1.MoveTo(0, 3);

Range r2 = sheet.Cells.CreateRange(“C1”, “C65536”);
//Move to B column
r2.MoveTo(0, 1);

//Now move the original B column to C column
r1.MoveTo(0, 2);

book.Save(@“e:\test2\shiftedcols.xls”);

2) You may try to use CopyColumn method to copy your desired columns to your desired position, see the document:
https://docs.aspose.com/display/cellsnet/Copy+Rows+and+Columns

3) Try Cells.MoveRange() method, this approach is same as 1).
See the document:
https://docs.aspose.com/display/cellsnet/Move+Range+of+Cells+in+a+Worksheet

Thank you.

3

Can we do it before putting the data in the sheet?


I have list of data with object containing 4 columns (Col1,Col2,Col3 and Col4).

Now without changing the source data, I want to render it like Col2, Col1, Col4,Col3.

Can we supply some index order etc to do the same?

Nirdesh
4

Hi Nirdesh,


Do you need to re-order MS Excel’s worksheet column headers (i.e., A, B, C, D…) or if you just have column labels (e.g Col1, Col2, Col3 and Col4 etc.) on the cells and you need to change their positions (in order e.g Col2, Col1, Col4, Col3 etc.). Well, if you need to change the MS Excel’s worksheet headers, I am afraid, this might not be possible in MS Excel, if you know how to do it in Ms Excel as per your needs, let us know, we will check it soon. And, if you just need to the shift custom column labels (in the cells) with each other, you may do it easily, refer to my previous post with example for shifting data in the cells.

Let us know if you still have any confusion.

Thank you.