All geek questions in one place

How to export html table to excel with correct latitude / longitude format?

I have an HTML table that contains 2 columns of latitudes and longitudes. I can easily export this table to excel using the following JavaScript function:

function downloadExcel() {
var tab_text = '<html xmlns:x="urn:schemas-microsoft-com:office:excel"> <meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">';
tab_text = tab_text + '<head><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>';

tab_text = tab_text + '<x:Name>Results</x:Name>';

tab_text = tab_text + '<x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet>';
tab_text = tab_text + '</x:ExcelWorksheets></x:ExcelWorkbook></xml></head><body>';

tab_text = tab_text + "<table border='1px'>";
tab_text = tab_text + $('#gcResults_table').html();
tab_text = tab_text + '</table></body></html>';

var data_type = 'data:application/vnd.ms-excel; charset=UTF-8;base64,';

var ua = window.navigator.userAgent;
var msie = ua.indexOf("MSIE ");

if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
    if (window.navigator.msSaveBlob) {
        var blob = new Blob([tab_text], {
            type: "application/csv;charset=utf-8;"
        });
        navigator.msSaveBlob(blob, 'Results.xls');
    }
} else {
    $('#test').attr('href', data_type + ', ' + encodeURIComponent(tab_text));
    $('#test').attr('download', 'Results.xls');
}
}// END of downloadExcel()

It works great. But when the excel file exported the table, latitudes and longitudes are not displayed in the correct format. An example excel file line can be seen below:

enter image description here

Therefore, my question is: how can I correctly record these coordinates in order to succeed? I searched for highlighting on several blogs, but couldn't find a solution. Thanks for your time and support.

UPDATE: Using the above excel export method, whenever the xls file is exported, I get the following notification when I open the file in MS Excel:

enter image description here

"", excel, / , .

+4
3

XML. :

<Table border='1px'>
    <Column ss:Width="75" ss:AutoFitWidth="0"/>
    <Row>
      <Cell>
          <Data ss:Type="String">48.210221, 16.390036</Data>
      </Cell>
    </Row>
</Table>

mhtml , , :

<html xmlns:x="urn:schemas-microsoft-com:office:excel"> 
<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">
  <head>
    <xml>
      <x:ExcelWorkbook>
     <x:ExcelWorksheets>
            <x:ExcelWorksheet>
        <x:Name>Results</x:Name>
        <x:WorksheetOptions>
            <x:Panes></x:Panes>
        </x:WorksheetOptions>
        </x:ExcelWorksheet>
     </x:ExcelWorksheets>
       </x:ExcelWorkbook>
    </xml>
  </head>
  <body>
     <table border='1px'>
        <tr>
           <td >48.210221, 16.390036</td>
        </tr>

     </table>
  </body>
</html>

, . , .

, , , Excel (xls). html , xls. MS Excel , HTML .

+2

, excel (mso-number-format) . , .

mso-number-formats excel.

css

.text {
   mso-number-format: "\@";
}
.number {
   mso-number-format: General;
}

css .

<div class="text">11</div>
<div class="number">11.0</div>
0

Simply export from JS to a CSV file and format the coordinates using the syntax = "[NUM]". Like this:

Name;Lat;Lng
Test1;="32.056717";="11.335333"
Test2;="12.056717";="19.335333"

In Excel:

enter image description here

0
source

Source: https://habr.com/ru/post/1674662/

More articles:


All Articles