Date format setting in rendering

I have converted 0_7.xlsx to Images but date format in the images is not same to original file’s one.

Do you have a function to set date format in rendering?



This is my code.





This is my code

----

private PageListResult ConvertExcelToImage()

{

try

{

var pageResultList = new List();



// Instantiate the License class

Aspose.Cells.License license = new Aspose.Cells.License();

license.SetLicense(“Aspose.Total.lic”);



var pageImgDir = CreateTempDirectory();



// Convert .xls and .xlsx file to PDF.

Workbook book = new Workbook(this._stream);



//Define ImageOrPrintOptions

ImageOrPrintOptions imgOptions = new ImageOrPrintOptions()

{

ImageFormat = ImageFormat.Png, //Specify the image format

VerticalResolution = this.config.ImageReadDpi,

HorizontalResolution = this.config.ImageReadDpi

};



int totalPageCount = 0;

foreach (Worksheet sheet in book.Worksheets)

{

//Render the sheet with respect to specified image/print options

SheetRender sr = new SheetRender(sheet, imgOptions);



for (int i = 0; i
{

totalPageCount++;



if (totalPageCount > this.config.DocumentMaxPageNumber)

break;



using (Bitmap bitmap = sr.ToImage(i))

{

var imgPath = ExportImageFile(pageImgDir, totalPageCount, bitmap);



var thumbPath = ExportThumbnailFile(imgPath, totalPageCount, bitmap);



var pageResult = new PageResult(totalPageCount, imgPath, thumbPath);

pageResultList.Add(pageResult);

}

}



if (totalPageCount >= this.config.DocumentMaxPageNumber) break;

}



return new PageListResult(pageResultList, this.config.DocumentMaxPageNumber < totalPageCount);

}

catch (Exception ex)

{

throw new Exception(“Failure converting excel”, ex);

}

}

----



Thank you,

PFU DSOL2

Hi,


Thanks for your posting and using Aspose.Cells.

Please see the screenshot of your Excel file on my computer. It is different than yours. It is actually a machine (OS) specific thing and can be controlled via Control Panel in Windows.

Besides, you can use these properties (as shown inside the code) to control them with Aspose.Cells.

C#
Workbook wb = new Workbook(“0_7.xlsx”);
wb.Settings.LanguageCode = CountryCode.Canada;
wb.Settings.Region = CountryCode.Canada;

Hello team,



Thank you for your support.



Your screenshot is same as my environment’s one. I can see 30-1-2012 in the converted image but actuall data is 30-Jan-2012. I think 30-Jan-2012 shoud be rendered.



Please find attached files which are screenshot and a converted page.



Thank you,

PFU DSOL2

Hi,


Thanks for your screenshots and using Aspose.Cells.

We have tested this issue with the latest version (please try the latest version) and found, it is working good. Please check the MS-Excel 2016 generated Pdf and Aspose.Cells generated Pdf with the following code. Both have correct date format.

Latest Version Links:
Aspose.Cells for .NET v17.4.6 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v17.4.6 (.NET 4.0) compiled in .NET Framework 4.0.

C#
Workbook wb = new Workbook(“0_7.xlsx”);

PdfSaveOptions opts = new PdfSaveOptions();
opts.OnePagePerSheet = true;

wb.Save(“Aspose.Cells.pdf”, opts);

Please also check the PNG image of the entire worksheet generated by the latest version of Aspose.Cells with the following code. It also displays correct date format.

C#
Workbook wb = new Workbook(“0_7.xlsx”);

Worksheet ws = wb.Worksheets[0];

ImageOrPrintOptions opts = new ImageOrPrintOptions();
opts.OnePagePerSheet = true;
opts.ImageFormat = ImageFormat.Png;
opts.VerticalResolution = 400;
opts.HorizontalResolution = 400;

SheetRender sr = new SheetRender(ws, opts);
sr.ToImage(0, “Aspose.Cells.png”);

Hello team,



Thank you for your support.



I have attached pdf files with Aspose.Cells 17.5 on my computer.



C#



------------------------------------------------

Workbook wb = new Workbook(“0_7.xlsx”);



wb.Settings.LanguageCode = CountryCode.Canada;



wb.Settings.Region = CountryCode.Canada;

------------------------------------------------

->07_Canada.pdf (It shows 30-Jan-2012)



C#



------------------------------------------------

Workbook wb = new Workbook(“0_7.xlsx”);



wb.Settings.LanguageCode = CountryCode.Default;



wb.Settings.Region = CountryCode.Default;

------------------------------------------------

->07_Default.pdf (It shows 30-1-2012)



C#



------------------------------------------------

Workbook wb = new Workbook(“0_7.xlsx”);



wb.Settings.LanguageCode = CountryCode.Japan;



wb.Settings.Region = CountryCode.Japan;

------------------------------------------------

->07_Default.pdf (It shows 30-1-2012)



I identified date format is affected by LanguageCode and Region settings as you have said. However, In my opinon, date format should be affected by “Format Cells” of Excel first.

MS Excel outputs the following pdf by SaveAs on my computer. I hope Aspose.Cells API behaves as with MS Excel SaveAs operation.

->07_SaveAsByExcel.pdf (It shows 30-Jan-2012)





Thank you,

PFU DSOL2

Hi,


Thanks for your posting and using Aspose.Cells.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, there is some fix or other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-45410 - Issue in Date format setting in Rendering

Hi,


Thanks for using Aspose.Cells.

Please check what is the default CultureInfo in your program. You can use the following sample code to know it.

C#
Console.WriteLine(System.Globalization.CultureInfo.CurrentCulture);

When you do not specify the region for the workbook and there is no specified region saved in the template file, we use the default region settings of the application (i.e. CultureInfo.CurrentCulture). If it is such as ja_JP, then we will use this region to do formatting. If you see different formatted result in MS-Excel even you have changed the machine’s regional settings, maybe you should check whether you have specified different region for MS-Excel itself.

Also, there is another way to check what the formatted result should be with specific region: You can specify region in the custom pattern for cell’s style, such as when using “[$-411]dd-mmm-yyyy”, 411 denotes the date should be formatted according with ja_JP region. You can format the cell with this custom in MS-Excel to see what’s the correct result.

Hello team,



Thank you for your support.



Console.WriteLine outputs "ja_JP"on my computer. My OS and Office settings is all “Japan”.

It seems that date format “dd-mmmm-yyyy” is not affected by region settings of OS and Office when Excel outputs pdf.



In case of [%-411], Excel outputs pdf with 30-1-2012 on my computer. however I can not use this workaround because my customer own the excel files.



Let me know if you need anything, I will do test on my computer.



Thank you,

PFU DSOL2

Hi,


Thanks for your posting and using Aspose.Cells.

If I am not mistaken, MS-Excel will show you different output (or output Pdf) on machines with different Regional & Date & Number Formatting.

It means, when user will open your Excel file, they will see it differently on their machines and when you will open your Excel file, you will see it differently.

This depends upon your machine Regional & Date & Number Formatting (as stated earlier).

But if you want that Excel shows everything as you want to show it, then we have given you a solution of “[$-411]dd-mmm-yyyy” in the previous post.

Hi,

Thanks for using Aspose.Cells.

We have done more tests for date formatting in MS-Excel and got some odd findings for MS-Excel’s behavior. When the default language of MS-Excel is set to ja-JP and the regional setting of the machine is set to ja-JP too, month such as January will be formatted as "Jan" by "mmm" pattern. If the default language of MS-Excel is set en-US, then when regional setting of machine is ja-JP, month is formatted as "1"; when regional setting of machine is en-US, month is formatted as "Jan". However, no matter what those settings is, if we specify regional settings in the custom pattern, such as when custom formatting is "[$-411]mmm" which will force the cell be formatted according with ja-JP region, month will be formatted as "1" instead of "Jan" always. And by C# program, formatting datetime with pattern "MMM" also output "1" for January. We are afraid we cannot support those odd behaviors like MS-Excel does.