Transpose

Aspose.Cells Product Family
1

Transpose copies the last row only if I specify a +1 - Why is that?



Sample code below:



Range rng = cells.CreateRange(0, 1, cells.MaxDataRow+1, cells.MaxDataColumn);





PasteOptions opts = new PasteOptions();

opts.Transpose = true; //Paste transpose





…

Range rng2 = cells2.CreateRange(0, 0, cells.MaxDataColumn , cells.MaxDataRow );

rng2.Copy(rng, opts);

2

Hi,

Thanks for your posting and using Aspose.Cells.

To make Transpose work correctly, you need to specify correct rows and columns in the destination range, so if your source range is 3x4 then your destination range should be 4x3 or larger.

I have tested this issue with the following code using the latest version: Aspose.Cells for .NET 8.5.1 and it worked correctly.

I have also attached the source excel file used in this code and the output excel file generated by it for your reference.

C#


String filePath = “F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


Cells cells = worksheet.Cells;


Range range = cells.CreateRange(“B3:E6”);

Range dRange = cells.CreateRange(“H5:K8”);


PasteOptions opts = new PasteOptions();

opts.Transpose = true;


dRange.Copy(range, opts);


workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);

3

Thanks for responding Shakeel.



My range is not fixed - so I cant use cell references. I totally agree if the source range is 3*5 - the destination range should be 5 * 3



But in my case, if there are 6 rows and 3 columns in the source range , destination range is created with 3 rows and 5 columns - I am not sure why? But if I create source range with 7 rows and 3 columns - It works !



I have attached my code. Please let me know.



Thanks!

4
Nams:
My range is not fixed - so I cant use cell references. I totally agree if the source range is 3*5 - the destination range should be 5 * 3

But in my case, if there are 6 rows and 3 columns in the source range , destination range is created with 3 rows and 5 columns - I am not sure why? But if I create source range with 7 rows and 3 columns - It works !

I have attached my code.


Hi,

We have checked your provided code and it seems to manipulate a specific spreadsheet (us_1013.xls) as you are currently deleting some columns where a few are also hidden. It would be appropriate that you should also share the spreadsheet referenced in your code for thorough investigation.

By the way, have you tried the scenario with latest release Aspose.Cells for .NET 8.5.1 (Shakeel has shared the download link)?
5

Hi,



Please find the file attached. I use different version (.NET 4.5.1) . if I use exact references like Shakeel, it works for me too. Please try to use references like me as in the code attached in my previous response. Thx!

6

Hi,


Please check the attached spreadsheet that I have generated on my side while using the latest revision of the Aspose.Cells for .NET 8.5.1.1 and your provided code snippet without modifications. Could you please see if this is your desired results? If not then we well also require a sample spreadsheet exhibiting your desired results that you may create manually using Excel application.

7


Hi,
That's the desired result. The code attached works for me too. Does it work if you remove +1 ? Please find the code updated. I am trying to understand why +1 is required.
8

Hi,


The reason for +1 (cells.MaxDataRow + 1) is simple, that is; in Aspose.Cells APIs the row index starts with 0 whereas in Excel the row index starts with 1. If you remove the +1 from the above statement the last row (6) will be skipped from the copy range operation.
9

Hi,

The column index also starts at 0 right. Why cells.MaxDataColumn + 1 was not required? I don’t think +1 should be required at all. cells.maxdatarow and cells.maxdatacolumn already points to the maximum data row and data column respectively. Since the index starts at 0, they will be one less but +1 should not be needed. I checked if that was because my source file has hidden column but it was not so. Please provide me more info .

10

Hi,


Well, let me share the description of the overloaded version of Cells.CreateRange() method here:

Creates a Range object from a range of
cells.

public Range CreateRange(
int firstRow,
int firstColumn,
int totalRows,
int totalColumns
);

Parameters

firstRow
First row of this range
firstColumn
First column of this range
totalRows
Number of rows
totalColumns
Number of columns

Now let us examine your line of code and try to explain you a bit:
Range rng = cells.CreateRange(0, 1, cells.MaxDataRow, cells.MaxDataColumn); //Source Range --Not sure why +1 is required
Mind you, totalRows refers to count of rows used in the range. So, if you are creating a range e.g "B1:H6", totalRows should have 6 value. But since cells.MaxDataRow gives you 5 (0-based indexed position), so you will add cells.MaxDataRow +1 here. Similarly if you talk about totalColumns that refers to count of columns used in the range. But here, totalColumns should be 7 as we exclude A column while defining the range. But since cells.MaxDataColumn gives farthest column index which has data in it --> i.e., it gives 7 (Column H is the farthest column which has data in it and is @ 7th indexed position (0-based index position)), so now you won't need to add +1 anymore here as you start creating the range from B column already.

Hope, this helps a bit.

Thank you.

11

Thanks so much. That was very helpful!

12

Hi,


Good to know that it is helpful to you. Feel free to contact us any time if you need further help or have some other issue or queues, we will be happy to assist you soon.

Thank you.
13

Great! Thanks.