Set excel file headers to Pascal Case in Aspose .net 6

Question

I've following code that maps given json array of objects to excel file. But it outputs the file with headers in lower case. I want the headers to be in Pascal Case. Can someone help or lead me to right direction? Either I should do it with json functions or aspose methods. I'm confused, as I couldn't find anything about header's cases for excel in documentation of Aspose. Thank you.

private async Task<string> JsonToExcel(string jsonString)
    {
        try
        {
            Workbook workbook = new();
            Worksheet worksheet = workbook.Worksheets[0];
            // Set JsonLayoutOptions
            JsonLayoutOptions options = new();
            options.ArrayAsTable = true;
            // Import JSON Data
            JsonUtility.ImportData(jsonString, worksheet.Cells, 0, 0, options);
            // Save Excel file
            Guid guid = Guid.NewGuid();
            string filename = DateTimeOffset.Now.ToString("yyyy_MM_dd") + guid.ToString() + ".xlsx";
            string path = _hostEnvironment.ContentRootPath + "Output\\";
            worksheet.PageSetup.PrintGridlines = true;
            worksheet.PageSetup.FitToPagesWide = 1;
            workbook.Save(path + filename, SaveFormat.Xlsx);
            return (path + filename);
        }
        catch (Exception)
        {
            throw;
        }
    }

As shown in output.

I tried to set headers to camel case using reference of PageSetup of Worksheet as shown in the documentation but nothing related to pascal case :(

// Instantiating a Workbook object
Workbook excel = new Workbook();
// Obtaining the reference of the PageSetup of the worksheet
PageSetup pageSetup = excel.Worksheets[0].PageSetup;
// Setting worksheet name at the left section of the header
pageSetup.SetHeader(0, "&A");

Amjad Sahi · Accepted Answer · 2022-12-19 14:12:11Z

1

You may easily accomplish the task via TextInfo.ToTitleCase(String) method of System.Globalization namespace. This method will convert string to sort of Pascal Case for your needs. So, once you import data into the worksheet via Aspose.Cells APIs, you may re-insert the text (in the first row) into the cells after using System.Globalization APIs. See the sample code segment that you may add before saving to Excel spreadsheet for your reference:

e.g.

Sample code:

...
// Creates a TextInfo based on the "en-US" culture.
TextInfo myTI = new CultureInfo("en-US", false).TextInfo;

//Get the first (header) row
Row row = worksheet.Cells.Rows[0];

IEnumerator ie = row.GetEnumerator();
while (ie.MoveNext())
{
    Cell cell1 = (Cell)ie.Current;
    string stringValue = cell1.StringValue;
    stringValue = myTI.ToTitleCase(stringValue);
     cell1.PutValue(stringValue);
}
....

Hope, this helps.

You may also post your queries or comments in dedicated forums.

PS. I am working as Support developer/ Evangelist at Aspose.

edited Dec 19, 2022 at 14:12

answered Dec 14, 2022 at 20:09

Amjad Sahi

1,9411 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Osama Mirza Over a year ago

Amazing! Thank you so much for this detailed and comprehensive answer/solution. This solved my problem. Thanks again! :)

Collectives™ on Stack Overflow

Set excel file headers to Pascal Case in Aspose .net 6

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related