Keskustelu: Nettisivujen teko: MS Excel

pistemies [05.05.2008 14:46:41]

Miten saisin MS Excelin xls-tiedoston taulukon helpoiten poimittua?
Tarkoitus on siirtää sen tiedot tietokantaan.

ajv [05.05.2008 16:49:32]

Save as... => CSV ja useimmista tietokannanhallintasoftista löytynee CSV-import.

Tapoja on toki n+1 kpl riippuen ympäristöstä, mutta tuo csv on aika universaali.

btw: miten tämä liittyy nettisivuihin?

Short Php [05.05.2008 16:58:55]

Uskoisin että Pekka Mansikka haluaa php:llä tai jollain poimia ne tiedot.

neau33 [05.05.2008 17:28:50]

Heippa Pekka Mansikka!

ajv:n esittämä CSV muotoon tallenntaminen on kyllä ehdottomasti paras ratkaisu Excel taulukon tietokantaan pukkaamisessa. Mutta mikäli tarkoituksena on esittää samaa dataa nettisivu(i)lla niin miksi pukata data tietokantaan..?
Jos tallennat tulukot XML muotoon voit poimia kamat vaivatta suoraan .xml tiedostoista AJAX:lla. Pistä Googlen hakuun sanat AJAX XML niin löydä lähes valmiin tarkoitukseesi sopivan viritelmän...

pistemies [05.05.2008 22:03:31]

Kiitos. Tuo tekikin siitä kovin helposti käsiteltävän paketin...

neau33: Data sisältää erään verkkokaupan tuotteita. Kyseessä on "tuotelistan" päivitys uudella, kannassa jo oleva lista vaihdetaan tähän uuteen listaan.

neau33 [07.05.2008 18:53:31]

Heippa taas!

Jos jäi auki toi AJAX-EXCEL-XML jutska niin tässä kevennetty malli

xml.html

<html><head><script language="javascript" src="ajax.js">
</script></head><body><form id="form1">
<input onclick="do_xml();" type="button" value="GET XML" name="button" />
<br /><br />Excel data requested from sever:<br /><br />
<table id="exceltaulu" border="1">
</table></form></body></html>

ajax.js

   var http_request = false;
   function makeRequest(url, parameters) {
      http_request = false;
      if (window.XMLHttpRequest) { // Mozilla, Safari,...
         http_request = new XMLHttpRequest();
         if (http_request.overrideMimeType) {
            http_request.overrideMimeType('text/xml');
         }
      } else if (window.ActiveXObject) { // IE
         try {
            http_request = new ActiveXObject("Msxml2.XMLHTTP");
         } catch (e) {
            try {
               http_request = new ActiveXObject("Microsoft.XMLHTTP");
            } catch (e) {}
         }
      }
      if (!http_request) {
         alert('Cannot create XMLHTTP instance');
         return false;
      }
      http_request.onreadystatechange = alertContents;
      http_request.open('GET', url + parameters, true);
      http_request.send(null);
   }

   function alertContents() {

      if (http_request.readyState == 4) {
         if (http_request.status == 200) {
            var xmldoc = http_request.responseXML;
            var root = xmldoc.getElementsByTagName('Worksheet').item(0);

            for (var iNode = 0; iNode < root.childNodes.length; iNode++) {
              var node = root.childNodes.item(iNode);
               for (i = 0; i < node.childNodes.length; i++) {
                  var sibl = node.childNodes.item(i);
                  var len = parseInt(sibl.childNodes.length / 2);
                  var arr = new Array(len);
                  var cnt = 0;
                  for (x = 0; x < sibl.childNodes.length; x++) {
                    try{
                      var sibl2 = sibl.childNodes.item(x);
                      var sibl3 = sibl2.childNodes.item(0);
                      var sibl4 = sibl3.childNodes.item(0);
                      arr[cnt] = sibl4.data;
                      cnt++;
                    }
                    catch(e){}
                  }
                  if(arr != undefined && arr!=""){
                  addrow("exceltaulu", arr);
                 }
               }
            }
         } else {
            alert('Pikku problemo noutopyynnössä.');
         }
      }
   }
   function do_xml(){
      document.getElementById('exceltaulu').innerHTML = "<tbody></tbody>"
      makeRequest('http://localhost/excel.xml','?');
   }

   function addrow(tablename, arr) {
   var tbl = document.getElementById(tablename);
   var lastRow = tbl.rows.length;
   var row = tbl.insertRow(lastRow);
      for (r = 0; r < arr.length; r++) {
         var cell = row.insertCell(r);
         cell.innerHTML = arr[r];
      }
   }

excel.xml

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>User name</Author>
  <LastAuthor>User name</LastAuthor>
  <Created>2008-05-06T23:43:39Z</Created>
  <Version>11.9999</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8445</WindowHeight>
  <WindowWidth>11355</WindowWidth>
  <WindowTopX>120</WindowTopX>
  <WindowTopY>45</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Taul1">
  <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="7" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">231</Data></Cell>
    <Cell><Data ss:Type="Number">312</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">231</Data></Cell>
    <Cell><Data ss:Type="Number">312</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">231</Data></Cell>
    <Cell><Data ss:Type="Number">312</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">738</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">738</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">738</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">1062</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">1305</Data></Cell>
    <Cell ss:Formula="=SUM(RC[-5]:RC[-1])"><Data ss:Type="Number">4581</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.4921259845"/>
    <Footer x:Margin="0.4921259845"/>
    <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996"
     x:Right="0.78740157499999996" x:Top="0.984251969"/>
   </PageSetup>
   <Zoom>115</Zoom>
   <Selected/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Taul2">
  <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="7" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">231</Data></Cell>
    <Cell><Data ss:Type="Number">312</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">231</Data></Cell>
    <Cell><Data ss:Type="Number">312</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">231</Data></Cell>
    <Cell><Data ss:Type="Number">312</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">738</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">738</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">738</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">1062</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">1305</Data></Cell>
    <Cell ss:Formula="=SUM(RC[-5]:RC[-1])"><Data ss:Type="Number">4581</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.4921259845"/>
    <Footer x:Margin="0.4921259845"/>
    <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996"
     x:Right="0.78740157499999996" x:Top="0.984251969"/>
   </PageSetup>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Taul3">
  <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="7" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">231</Data></Cell>
    <Cell><Data ss:Type="Number">312</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">231</Data></Cell>
    <Cell><Data ss:Type="Number">312</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="Number">231</Data></Cell>
    <Cell><Data ss:Type="Number">312</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">738</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">738</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">738</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">1062</Data></Cell>
    <Cell ss:Formula="=SUM(R[-6]C:R[-1]C)"><Data ss:Type="Number">1305</Data></Cell>
    <Cell ss:Formula="=SUM(RC[-5]:RC[-1])"><Data ss:Type="Number">4581</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.4921259845"/>
    <Footer x:Margin="0.4921259845"/>
    <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996"
     x:Right="0.78740157499999996" x:Top="0.984251969"/>
   </PageSetup>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

Vastaus

Aihe on jo aika vanha, joten et voi enää vastata siihen.

Kirjautuminen

Haku

Tehtävät