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

Free Support Forum - aspose.com

When convert xls to pdf, date column change in output pdf

Hi
We convert xls,excel to pdf file, in that xls file got date column, before convert xls got date like
5/7/2017, 8/7/2017 and 18/7/2017 .

After convert to pdf , we will get date format as
7/5/2017,7/8/2017 and 7/18/2017.

Why it change date form in out pdf file ?

in ASP.Net , vb

Regards
Aravind

@bpanchu,

Thanks for your query.

Please open your template XLS file into MS Excel and check how the DateTime values are displayed. The output PDF should be same as the DateTime values are displayed in MS Excel. In fact, the DateTime or other numeric values are displayed/rendered in accordance with the locale set or regional settings of your OS in your environment.

Thank you.

Hi
It just wording, how it related OS setting, if word file got date and time, when convert word to pdf it change according to OS setting , how possible ? or if in image file, when convert image (png) to pdf, ur dll will change wording ha ?

@bpanchu,

The Numbers formatting and DateTime formatting would be displayed according to your locale set of OS, you may confirm this by opening the file into MS Excel. Now change the regional/locale settings in Control panel in your OS. Open the file again into MS Excel, you will see different formatting for your DateTime or numeric values. You may also render to PDF file in MS Excel and check the results comparing with your displayed Excel file.

Could you provide us your template Excel file and output PDF file. Also give us details about your locale/regional settings. we will check it soon.

Thank you.

Hi, We notice that date form changing according to system date format. But we have to fix this problem.
If file open in ms excel , it will change according to system date format.
How about like this , we store file in sql database in the format for binary, using that binary without store in local, we can convert into pdf file ? like anything using stream or binary format.

Pls reply asap,

Regards,
Aravind

@bpanchu,

Aspose.Cells supports reading Excel file from streams and file path. Well, you got to use your own code to first fetch the binary data (of file) into array of bytes or/and write to stream based on it, then use Aspose.Cells APIs to instantiate the workbook from the streams and convert to PDF file format.

Thank you.

Let me know any sample document got ? we have excel file in binary format, after that what need to do, any sample document for instantiate the workbook from the streams and convert to PDF file format.

Regards,
Aravind

@bpanchu,

See the sample code snippets for your reference:
e.g
Sample code:

  1. // Get the file_content - you got to use your own code first to get the binary data of the file.
    byte[] byteArray = Encoding.ASCII.GetBytes(file_content);
    MemoryStream stream = new MemoryStream(byteArray);
    Workbook workbook = new Workbook(stream);
    workbook.Save(“out1.pdf”);

  2. var fileData = File.ReadAllBytes(“e:\test2\Book1.xls”);
    using (Stream stream = new MemoryStream())
    {
    stream.Write(fileData, 0, fileData.Length);
    stream.Position = 0;

                 var loadOptions = new LoadOptions(LoadFormat.Auto)
                 {
                     LoadDataOnly = false,
                 };
    
                 var workbook = new Workbook(stream, loadOptions);
                 workbook.Save("e:\\test2\\out1.pdf");
    

Please note, you got to fetch the binary data and store into some stream by yourself using your own code.

Thank you.

Hi when u reply , pls check in which language i am asking, i am purely from vb , i dont know C ,C++ or C#
and LoadOptions from which api ? Cells or word or Pdf

Always loadoptions must what file format we going to save ? here we go to save in pdf, so loadoption must pdf api ?

Regards,
Aravind

Hi
When i upload file in to database in the format of binary, that time xls and system date format is yyyy-M-d , and before convert i changed system date format to d-M-yyyy and the out pdf file changed to system date. Using below code.
Here i take binary file directly from database and pass to conversion, here i am not store file in locally, but it changed data format.
Dim buffer As Byte()
buffer = Nothing
buffer = fadt(0)(“Document”) ’ Binary format from sql database
’ actualFilePath
Dim outputfile As String = “C:\inetpub\wwwroot\ooo.pdf”
Dim stream As New MemoryStream(buffer)

                                Dim loadOptions = New Aspose.Words.LoadOptions()
                                loadOptions.LoadFormat = Aspose.Words.LoadFormat.Auto
                                Dim workbook As New Workbook(stream)
                                workbook.Save(outputfile, Aspose.Cells.SaveFormat.Pdf)

screenshot - http://prntscr.com/g9zvy6

Regards,
Aravind

@bpanchu,

Well, since you are manipulating Excel file (reading and updating XLS file and converting to PDF file format), so kindly use LoadOptions from Aspose.Cells APIs and not from Aspose.Words. You should change the lines of code:
i.e.,

Dim loadOptions = New Aspose.Words.LoadOptions()
loadOptions.LoadFormat = Aspose.Words.LoadFormat.Auto

to:

Dim loadOptions = New Aspose.Cells.LoadOptions()
loadOptions.LoadFormat = Aspose.Cells.LoadFormat.Auto

Thank you.

@bpanchu,

We think for your requirement, you should set the Region/CultureInfo for the Workbook manually (e.g Workbook.Settings.Region/CultureInfo). To keep the older formatting rule, you can set the workbook’s region same with the old one before rendering to PDF file format.

Thank you.

Hi
My requirements is, in A’s pc xls date format is dd-MM-yyyy and form B pc i will convert xls to pdf i dont want change date format while B’s pc date format is yyyy-MM-dd, is it possible ?

From above reply, how to set Region/CultureInfo in woorkbook ? is set it wont change date format ?

Form your previous reply (Aug 18, 4:26 PM),if xls file move to one pc to anther pc, then xls change date form according to new pc is it correct ? i tried like this, without store or download in new pc, directly i pass binary to MemoryStream and convert from meomorystream to pdf, but still date format changed according to OS.

Regards,
Aravind

@bpanchu,

Well, the format of date-time data relates to the region of the machine on which you process the conversion task via Aspose.Cells APIs. You may set region of A’s pc on B by following code:
e.g
Sample code:

Aspose.Cells.LoadOptions loadOptions = new Aspose.Cells.LoadOptions();
loadOptions.LanguageCode = CountryCode.France;
loadOptions.Region = CountryCode.France;
Workbook book = new Workbook(sourceFile, loadOptions); 

Hope, this helps a bit.

Thank you.

No, what i am said,
in A’s pc xls date format is dd-MM-yyyy and form B pc i will convert xls to pdf i dont want change date format while B’s pc date format is yyyy-MM-dd
Above one for example i mention A’s pc date format, ok ? understand ? u simply say when convert to pdf , pass A’s pc date format, if not know A’s pc date format ? then how, and also date format got more than one, so how can pass region code while convert ?
If A,B,C,D,E,F got different date format and when convert pc got yyyy-MM-dd format, then how ?

According to you previous reply, we have xls file binary format, if download and store in pc, it will change date format according to OS or in Microsoft SQL server will change date format for xls file while in the form of binary (File stream database) ?

If MS SQL not change date format while file in the form of binary, when directly using that binary we can convert to pdf file ?

Regards,
Aravind

@bpanchu,

Thanks for sharing further details.

I think you are trying to accomplish something which might be impossible. How could you achieve with MS Excel manually. I mean render Excel file to PDF manually on B pc in accordance with the DateTime format of A’s pc. If you could do it with MS Excel manually, we will investigate on your issue and try to provide you solution. As you do not want to manually specify the region or cultureinfo of previous pc (OS). How could we know what’s the DateTime format of A’s pc on B pc?
As we have only Excel file to work with it, so next thing to be explored is if there is any relevant entry in the source .xml of the Excel file which could denote the DateTime formatting of the pc where it was created. The answer is no, as generally there is no certain detail of the regional settings/cultureinfo used to create/modify the Excel file.

We think the best way to cope with your scenario is you should specify the DateTime by Style.Custom attribute (for your desired DateTime formatting) if you are creating/modifying the file via Aspose.Cells APIs. Doing this will not change the DateTime or numbers formattings when rendering to PDF file format according to your new environment (B,C,D, etc.) datetime format settings.

Thanks for your understanding!

1 Like

Hi, Thanks for your reply.

Regards,
Aravind