Aspose cells add static text in row 1 followed by json data for table from 2nd row

Aspose.Cells Product Family
1

Hi
Currently cell is working for JSON data using the below code
string jsonInput = File.ReadAllText(“Data.json”);

JsonLayoutOptions options = new JsonLayoutOptions();
options.ArrayAsTable = true;
JsonUtility.ImportData(jsonInput, worksheet.Cells, 0, 0, options);

I want to add something like this
worksheet.Cells[“A1”].PutValue(“Summary of the excel data”);

JsonInput should come from Cells[“A2”]

So when i change the above line to change the index of ROW to 1 it is throwing error
JsonUtility.ImportData(jsonInput, worksheet.Cells, 1, 0, options);

Please provide the solution asap.

Thanks,
Priya

2

@manipriya,

Which version of Aspose.Cells for .NET you are using? I tested your scenario/case with sample JSON data to import it starting from A2 using our latest version/fix: Aspose.Cells for .NET 22.6.1, it works fine and I do not get any issue or error. The output file is fine tuned.
e.g.
Sample code:

            string inputJson = @"[
                                    { BEFORE: 'before cell', TEST: 'asd1', AFTER: 'after cell' },
                                    { BEFORE: 'before cell', TEST: 'asd2', AFTER: 'after cell' },
                                    { BEFORE: 'before cell', TEST: 'asd3', AFTER: 'after cell' },
                                    { BEFORE: 'before cell', TEST: 'asd4', AFTER: 'after cell' }
                                ]";
            string sheetName = "Sheet1";
            int row = 1; 
            int column = 0;

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

            Aspose.Cells.Utility.JsonLayoutOptions options = new Aspose.Cells.Utility.JsonLayoutOptions { ArrayAsTable = true };
            Aspose.Cells.Utility.JsonUtility.ImportData(inputJson, worksheet.Cells, row, column, options);
           
            worksheet.Cells[“A1”].PutValue(“Summary of the excel data”);
            workbook.Save("e:\\test2\\out1.xlsx");

Please try newer version and let us know with details and sample code (runnable) if you still find any issue.

3

Im using “Aspose.Cells” Version=“21.12.0”
And it is throwing the following error

Row number or column number cannot be zero

4

@manipriya,

It might be a bug in the older version (Aspose.Cells” Version=“21.12.0) which got fixed in newer versions. As suggested earlier, please try our newer version and let us know your feedback.

5

Sure Amjad_Sahi will use latest version of Aspose cells and post here.
Thanks for your quick response.

6

@manipriya,

Thanks and we are looking forward to get your feedback.

7

hi on latest version it is working properly.

I have one more query

How to add color to the header or row 1.

Style boldStyle = workbook.CreateStyle();
boldStyle.BackgroundColor = System.Drawing.Color.Red;
//boldStyle.ForegroundColor = System.Drawing.Color.Yellow;
boldStyle.Pattern = BackgroundType.Solid;
StyleFlag boldStyleFlag = new StyleFlag();
boldStyleFlag.HorizontalAlignment = true;
boldStyleFlag.FontBold = true;

Row row = worksheet.Cells.Rows[1];
// Apply the style to it.
row.ApplyStyle(boldStyle, boldStyleFlag);

tried this but dint add colour to row 1.

Do i need to change code to add color. pl suggest

8

@manipriya,

See the sample code for your reference:

......
Style boldStyle = workbook.CreateStyle();
boldStyle.ForegroundColor = System.Drawing.Color.Red;
boldStyle.Pattern = BackgroundType.Solid;
StyleFlag boldStyleFlag = new StyleFlag();
//boldStyleFlag.HorizontalAlignment = true;
//boldStyleFlag.FontBold = true;
boldStyleFlag.CellShading = true; 
Row row = worksheet.Cells.Rows[1];
// Apply the style to it.
row.ApplyStyle(boldStyle, boldStyleFlag);
9

Hi It worked. Thanks @amjad_sahi for quick response.

10

@manipriya,

Good to know that the suggested code segment works for your needs. Feel free to write us back if you have further queries or comments.