We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Cells upgrade issue

I’ve got some code which seems to have issues when I went to a newer version of Aspose.Cells. Specifically, there is a snippet of the code that is giving me a problem:

        Range = ExcelSheet.Cells.CreateRange(Row, 2, 1, 1)
        Range.Merge()
        Range.GetCellOrNull(0, 0).PutValue("LIST")
        Range.MakeBold()
        Range.CenterHorizontal()
        Range.BorderAround()

The .GetCellOrNull line is throwing an error and it looks like the issue might be when trying to use the .PutValue method on a GetCellOrNull which evaluates to Nothing. My previous version of .cells was very old and I’ve tried 16.12 and 18.10 with similar results.

I suspect that I doing something fundamentally wrong in my code but it has been several years since I wrote the quoted code do not remember exactly how I got to the earlier code. Is there something that fundamentally changed in the syntax in the newer versions? I’ve bounced around in the various on-line discussions but have not found anything which seems to apply.

Thanks,
/jeff

@JeffAH,
Please share your sample files, runnable code snippet, images showing comparison of outputs created with working old version and problematic new version and expected output created using Excel with us for our testing. We will reproduce the problem and provide our feedback after analysis.

Sorry, I do not have a sample file… this is just a very small part of a very long process and I don’t have anything working at the moment. I am primarily interested in a) understanding the SYNTAX and b) any documentation which explains why this would have changed with (relatively) newer versions of .cells. In other words, am I using the Range.GetCellOrNull correctly?

Thanks.

@JeffAH,

We have investigated your scenario a bit, please note the difference between Cells[rowIndex,colIndex] and Cells.GetCellOrNull(rowIndex,colIndex):

For Cells.GetCellOrNull(rowIndex,colIndex), it checks whether one cell has been instantiated and only returns the existing Cell object to user. if the required cell at given position has not been instantiated, null will be returned. This method is useful for the scenario where you only want to check/fetch cell’s data. Such as one may need to traverse all cells in a large range, if all cells in that range be instantiated, much more memory will be required and commonly it is needless.

For Cells[rowIndex,colIndex], if the required cell at given position has not been instantiated, then the Cell object will be instantiated and returned to user. This method commonly being used when one needs to modify the settings for the cell, such as setting style, putting value etc.

For different versions of our component, we may change the logic for instantiating Cell objects. For example, as an enhancement for performance consideration we may try to avoid to instantiate an empty Cell object when it has no any special data to be set. So, for some versions Cells.GetCellOrNull(rowIndex,colIndex) may return a Cell object but for some other versions it may return null. Anyways, one should check the returned value first and only invoke methods on it when it is not null. And if you need to change the data of the cell, the proper method is Cells[rowIndex,colIndex] because it is obvious that the Cell object needs to be instantiate.

Hope, this helps.

Thanks very much for the feedback. I was able to replace one of the calls to Cells.GetCellOrNull(rowIndex,colIndex) with a Cells[rowIndex,colIndex] call and that has solved my immediate issue. One minor follow-up question: the Range.BorderAround() line in my sample code does not seem to have a direct Cell vs. Range replacement call – is there a preferred way of drawing a border around a cell?

Thanks again for the rapid response.

/jeff

@JeffAH,
I am afraid that I could not find any function named BorderAround() in the Range class.However for your reference, please use following sample code sample to draw border around range:

// Instantiate a new Workbook.
Workbook workbook = new Workbook();

// Access the cells in the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;

// Create a range of cells.
Range range = cells.CreateRange("D6", "M16");

range.SetOutlineBorders(CellBorderType.Hair, Color.Red);

// Save the excel file.
workbook.Save(outputDir + "outputSetBorderAroundRange.xlsx");

For drawing border around each cell, give a try to the following sample code:

// Instantiate a new Workbook.
Workbook workbook = new Workbook();

// Access the cells in the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;

// Create a range of cells.
Range range = cells.CreateRange("D6", "M16");

// Declare style.
Style stl;

// Create the style adding to the style collection.
stl = workbook.CreateStyle();

// Specify the font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
stl.Font.Color = Color.Blue;

// Set the borders
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.TopBorder].Color = Color.Blue;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.LeftBorder].Color = Color.Blue;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.BottomBorder].Color = Color.Blue;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.RightBorder].Color = Color.Blue;


// Create StyleFlag object.
StyleFlag flg = new StyleFlag();
// Make the corresponding formatting attributes ON.
flg.Font = true;
flg.Borders = true;

// Apply the style with format settings to the range.
range.ApplyStyle(stl, flg);

// Save the excel file.
workbook.Save(outputDir + "outputSetBorderAroundEachCell.xlsx");

Thanks very much. Your examples helped me solve my issue. BTW, it seems that the BorderAround() sub is actually a wrapper I had added some years ago… sorry for the false reference.

@JeffAH,

Good to know that your issue is sorted out by the suggested code segment. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.