How to Create dynamic range?

Aspose.Cells Product Family
1

Hello,


I have been working on export to excel functionality for last few days. I have used Aspose.Cells dll in my Project to achieve all customize export to excel functionality. We have lots of HTML tables(Lists) in our project which are suppose to export to excel. For that we are using Aspose.Cellls dll. Now I have some problem with creating dynamic range in Excel cells.

I have headers which come dynamically and I do not know the range of all the headers. I have used following code to create dynamic range.

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

Cells cell = worksheet.Cells;

//Create a new style adding to the workbook styles collection
Style style = workbook.Styles[workbook.Styles.Add()];
//Specify the style’s fill color
style.Font.IsBold = true;

//Create a styleflag object
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;

Range range = cell.CreateRange(“A3”, “I3”);

Now instead of this static range A3 to I3 I want to give dynamic range. like I know starting cell but I dont know the ending cell.

Range range = cell.CreateRange(?,?);

I would appreciate if someone could help me ASAP.

Thanks & Regards
Ashish Rajguru
2

Hi Ashish,


Thank you for considering Aspose.Cells for your solutions.

In order to dynamically create a range, you can use some of the following as per your needs as the ending limit in your code in combination with Cells.CreateRange(Start:End) :

  • Worksheet.Cells.MaxColumn //Max Column in a Worksheet
  • Worksheet.Cells.MaxRow //Max Row in a Worksheet
  • Worksheet.Cells.MaxDataColumn //Max Data Column in a Worksheet
  • Worksheet.Cells.MaxDisplayRange //Max Data range in a worksheet
  • Worksheet.Cells.MaxDataRow //Max Data Row in a Worksheet
  • Worksheet.Cells.MinColumn
  • Worksheet.Cells.MinRow

3

Hi,


If you need to create a range based on the whole data/contents in the sheet dynamically, please try to use CellsHelper static class’s methods, e.g.,

Range range = cells.CreateRange(“A1”, CellsHelper.CellIndexToName(cells.MaxDataRow, cells.MaxDataColumn));

Thank you.
4

Hi,

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

Please download and use the latest version:
Aspose.Cells for .NET (Latest Version)


Worksheet.Cells.MaxDisplayRange will serve the purpose. It will cover all the cells in your worksheet that has some data.

Your code will look like this

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Cells cell = worksheet.Cells;


//Create a new style adding to the workbook styles collection

Style style = workbook.Styles[workbook.Styles.Add()];


//Specify the style’s fill color

style.Font.IsBold = true;


//Create a styleflag object

StyleFlag styleFlag = new StyleFlag();


//Specify all attributes

styleFlag.All = true;


Range range = worksheet.Cells.MaxDisplayRange;


5

Hello,


Thanks for prompt replies. I have tried both ways of creating dynamic range but still style is not being applied to my header row. my code is as follows.


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Cells cell = worksheet.Cells;


//Create a new style adding to the workbook styles collection

Style style = workbook.Styles[workbook.Styles.Add()];


//Specify the style’s fill color

style.Font.IsBold = true;


//Create a styleflag object

StyleFlag styleFlag = new StyleFlag();


//Specify all attributes

styleFlag.All = true;

//Range range = cell.CreateRange(“A3”, CellsHelper.CellIndexToName(cell.MaxDataRow, cell.MaxDataColumn));
Range range = worksheet.Cells.MaxDisplayRange;

//Creating headers
for (int i = 0; i < headerText.Length; i++)
{
cell[1, i].PutValue(headerText[i]);

}

//Applying style to header row range.ApplyStyle(style, styleFlag);

I want to apply above style in only one(header) row.


Thanks & Regards
Ashish Rajguru
6

Hi,

Thanks for your feedback.

Could you please provide us your sample source xls/xlsx file and the expected output xls/xlsx file? You can create both of them manually using Ms-Excel 2010 and attach here.

It will help us see your problem and we will provide you a sample code accordingly.

7

Hi Ashish,

You can get the ending cell of a row using LastCell parameter of the row object. For example, considering that Row 0 is your header row, you can write your statements like:

Workbook workbook = new Workbook(“K://temp.xls”);

//Get the Cells Collection object
Cells cells = workbook.Worksheets[0].Cells;

//Get the last cell in the Row
Cell cell = cells.Rows[0].LastCell;

//Create Range
Range range = cells.CreateRange(“A1”, cell.Name);

I hope this gives what you want.

8

Hello,


I have attached my sample source and expected output files. please find the attachments

Thanks & Regards
Ashish Rajguru
9

Hello Kashif,


Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

Cells cell = worksheet.Cells;

Cell lastcell = cell.Rows[2].LastCell;


Now lastcell is giving null value.


Thanks & Regards
Ashish Rajguru
10

Hi,


Please see the following code for your purpose. I have used it and generated the output file as attached here:
Workbook workbook = new Workbook("K://SampleSourceFile.xlsx");
Cells cells = workbook.Worksheets[0].Cells;
Cell cellA3 = cells["A3"];
int iHeaderRow = cellA3.Row;
Cell LastCell = cells.Rows[iHeaderRow].LastCell;
Range range = cells.CreateRange(cellA3.Name, LastCell.Name);
//Create a new style adding to the workbook styles collection
Style style = workbook.Styles[workbook.Styles.Add()];
//Specify the style's fill color
style.Font.IsBold = true;
//Create a styleflag object
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
range.ApplyStyle(style, styleFlag);
workbook.Save("K://SampleSourceFileOut.xlsx");
11
Hi,

Thank you. It has solved my problem. I was creating lastcell before rendering of my header row so it was giving me null. first all data should be rendered and then style should be given. I think you can not give styles or create ranges on blank rows and columns in empty sheet.


Thanks & Regards
Ashish Rajguru
12

Hi,


For:

"Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

Cells cell = worksheet.Cells;

Cell lastcell = cell.Rows[2].LastCell;


Now lastcell is giving null value."

Well, this is understandable as when you need to create a range dynamically based on header row, the data in the header row should be filled, least you would always get null/wrong data for the cells to be created for a range.

If you have further query or confusion, let us know, we will be happy to assist you.

Thanks for your time and have a good day.
13

Hi Ashish,


Styles do apply to the empty columns/rows as well. You can see it from the following code:

Workbook workbook = new Workbook();
//Get the Cells Collection object
Cells cells = workbook.Worksheets[0].Cells;
Column col = cells.Columns[0];
Style style = workbook.Styles[workbook.Styles.Add()];
style.Font.IsBold = true;
//Create a styleflag object
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
col.ApplyStyle(style, styleFlag);
cells["A1"].PutValue("Hello");
workbook.Save("K://output.xls");

Also, the same is true for ranges that don't have pre-existing data. You can
try it the same way as above reference code for a Range.