Precision lost with xlswrite?
in [Matlab]
|
Prev: java and simulink
Next: mincx to gevp
From: dpb on 2 Dec 2009 15:21 Jos wrote: .... > The problem is that in the last step, the position of the dot changes > (except on the first line) Posting's crossed--see my other response where I went back to original post and saw what I had overlooked earlier. --
From: Rune Allnor on 2 Dec 2009 15:33 On 1 Des, 13:55, "Jos " <jappedane...(a)mathworks.com> wrote: > Hi, > > I have a cell array of numbers and want to write it into a cell array of strings (this is because besides numerical input, there will also be added strings to the table. After this, the table of strings is printed to excel > > this is the input: > 4 3,69599639557127 188,410000000000 86,0893000000000 > 6 7,08828645208912 274,569000000000 97,3328000000000 > 8 9,38747390641889 349,375000000000 102,099000000000 > 10 10,9083049302071 447,311000000000 120,367000000000 > 12 11,3831813630739 448,195000000000 157,617000000000 > 14 12,9220834663217 452,480000000000 159,910000000000 > 16 15,2383797236977 459,235000000000 327,560000000000 > 18 22,1045498361762 483,264000000000 346,441000000000 > 20 37,8022334739675 486,764000000000 452,589000000000 > 22 55,6718604320812 519,297000000000 479,373000000000 > 24 76,8186179083778 557,011000000000 510,505000000000 > > And this is what appears in the table of strings: > ' 4' ' 3.696' ' 188.41' '86.0893' > ' 6' '7.08829' '274.569' '97.3328' > ' 8' '9.38747' '349.375' '102.099' > '10' '10.9083' '447.311' '120.367' > '12' '11.3832' '448.195' '157.617' > '14' '12.9221' ' 452.48' ' 159.91' > '16' '15.2384' '459.235' ' 327.56' > '18' '22.1045' '483.264' '346.441' > '20' '37.8022' '486.764' '452.589' > '22' '55.6719' '519.297' '479.373' > '24' '76.8186' '557.011' '510.505' > > But when I use xlswrite, the excel file gives this: > 4 3.696 188.41 860.893 > 6 708.829 274.569 973.328 > 8 938.747 349.375 102.099 > 10 109.083 447.311 120.367 > 12 113.832 448.195 157.617 > 14 129.221 452.48 159.91 > 16 152.384 459.235 327.56 > 18 221.045 483.264 346.441 > 20 378.022 486.764 452.589 > 22 556.719 519.297 479.373 > 24 768.186 557.011 510.505 > > Anyone with an idea of why this happens? Excel files either use a text-based internal storage format with a small number of significant digits (remember, excel was never intended used in engineering) or it exports only a small number of significant digits through the excel API. You *can* probably use some voodoo to specify the number of significant digits to be stored or exported, but you will be far better off ditching the excel format as such. Store the data to .csv text files. Rune
From: Jos on 3 Dec 2009 06:51 > > Anyone with an idea of why this happens? > > Excel files either use a text-based internal storage > format with a small number of significant digits > (remember, excel was never intended used in engineering) > or it exports only a small number of significant digits > through the excel API. > > You *can* probably use some voodoo to specify the number > of significant digits to be stored or exported, but you > will be far better off ditching the excel format as such. > Store the data to .csv text files. > > Rune I tried writing a .csv-file (using the cell2csv.m function found here: http://www.mathworks.com/matlabcentral/fileexchange/4400). But unfortunately, this gives me exactly the same output as when using xlswrite.
From: Branko on 3 Dec 2009 07:57 "Jos " <jappedaneels(a)mathworks.com> wrote in message <hf88n8$1ks$1(a)fred.mathworks.com>... > > > > Anyone with an idea of why this happens? > > > > Excel files either use a text-based internal storage > > format with a small number of significant digits > > (remember, excel was never intended used in engineering) > > or it exports only a small number of significant digits > > through the excel API. > > > > You *can* probably use some voodoo to specify the number > > of significant digits to be stored or exported, but you > > will be far better off ditching the excel format as such. > > Store the data to .csv text files. > > > > Rune > > I tried writing a .csv-file (using the cell2csv.m function found here: http://www.mathworks.com/matlabcentral/fileexchange/4400). But unfortunately, this gives me exactly the same output as when using xlswrite. Jos, Basic concept is how do you treat comma? Is this seperator? OR maybe looking your data as decimal separator-which is wrong. Yes, it is true that in some countries (like where I'm comming from - Slovenia) we using comma as decimal sepertaor and dot as separation each group of three digits-which is opposite of SI (ML). After you clerify this, use sugestion by Rune -save it to csv format and Excel should read your data in proper manner. Branko
From: Jos on 3 Dec 2009 08:53
"Branko " <bogunovic(a)mbss.org> wrote in message <hf8cj2$1cr$1(a)fred.mathworks.com>... > "Jos " <jappedaneels(a)mathworks.com> wrote in message <hf88n8$1ks$1(a)fred.mathworks.com>... > > > > > > Anyone with an idea of why this happens? > > > > > > Excel files either use a text-based internal storage > > > format with a small number of significant digits > > > (remember, excel was never intended used in engineering) > > > or it exports only a small number of significant digits > > > through the excel API. > > > > > > You *can* probably use some voodoo to specify the number > > > of significant digits to be stored or exported, but you > > > will be far better off ditching the excel format as such. > > > Store the data to .csv text files. > > > > > > Rune > > > > I tried writing a .csv-file (using the cell2csv.m function found here: http://www.mathworks.com/matlabcentral/fileexchange/4400). But unfortunately, this gives me exactly the same output as when using xlswrite. > > Jos, > > Basic concept is how do you treat comma? Is this seperator? OR maybe looking your data as decimal separator-which is wrong. > Yes, it is true that in some countries (like where I'm comming from - Slovenia) we using comma as decimal sepertaor and dot as separation each group of three digits-which is opposite of SI (ML). > After you clerify this, use sugestion by Rune -save it to csv format and Excel should read your data in proper manner. > > Branko Great, it all starts to make sense to me now :) The dot indeed needs to be a decimal seperator instead of a thousand seperator! That I didn't see this myself :s The only problem I have now is that csv-files are automatically opened in Excel and therefor, the problem remains the same. The only thing I can do is export them as txt and then import them in Excel. Unfortunately, I want the tables to be automatically generated in Excel to save time. Possible solutions are: - When writing to csv, change the dot into a comma. But I don't know how :s - Change the standard settings of Excel, so the dot becomes the decimal seperator and comma the thousand seperator. But again, no idea how to do this :s |