How to get range till last column especially when last column is blank

Aspose.Cells Product Family
1

This is my data in excel sheet
Date Client M R C P % P
2/1/2013 F $1.00 $0.00 $1.00 100.00%
2/5/2013 F 17 $0.00 $0.00 $0.00

I have to create a pivot table for (%P). This is the code:
Cells cells = sheet.Cells;
string start = cells.FirstCell.Name;
string end = cells.End.Name;
// here is the problem – the value I get is F3 instead of G3 as G3 is blank
string sourcedata = String.Format("=temp!{0}:{1}", start, end);

int index = pivotTables.Add(sourcedata, 2, 0, “pPivot”);
PivotTable pPivotTable= pivotTables[index];
pPivotTable.AddFieldToArea(PivotFieldType.Row, 1);
pPivotTable.AddFieldToArea(PivotFieldType.Column, 0);
pPivotTable.AddFieldToArea(PivotFieldType.Data, 6);

This brings error “index out of range”. Can you suggest how I can get the range A1 to G3?

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please use Worksheet.Cells.MaxDisplayRange property for your needs. It will give you a Range object that will cover everything in your worksheet.

3

Ok, thanks. This is how I used it to create pivot table:

Range cellRange = cells.MaxDisplayRange;

string sourcedata = cellRange.RefersTo.ToString();

Worksheet pivotSheet = workbook.Worksheets[0];

pivotSheet.Name = "Pivots";

PivotTableCollection pivotTables = pivotSheet.PivotTables;

int index = pivotTables.Add(sourcedata, 2, 0, "pPivot");
PivotTable pPivotTable= pivotTables[index];


pPivotTable.AddFieldToArea(PivotFieldType.Row, 1);
pPivotTable.AddFieldToArea(PivotFieldType.Column, 0);
pPivotTable.AddFieldToArea(PivotFieldType.Data, 6);

Is this the correct way to do? Please advise.

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}

<![endif]–>

4

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

It seems to be a correct way. If you still encounter any issue, then please provide us you sample simple runnable project and the actual and expected output files. We will look into your issue and help or advise you asap.