how to create a excel file only with python standard library?

Question

I am new to Python.

I use putty to manage some servers. I want to use Python to create a Excel file on each server, for that I think if I can use some orders like ssh ip "python abc.py" to create the file. It is possible to write a bash script to manage all servers. This is the trouble I meet:

The servers can't use internet.

And it is not allowed to use any third party libraries. When a linux(redhat 6.5) was installed, is there any library in python that can be used to create Excel immediately? Please help me, thanks.

Overpass

Can you not use CSV? – Hiddenite

sorry, the boss want a excel, TT – Overpass

Excel can open CSV file... – Zaneta

Answer 1

Actually there is a way. if you have an empty excel file, you can open it as a zip file and this is the content

    .
    ├── [Content_Types].xml
    ├── docProps
    │   ├── app.xml
    │   ├── core.xml
    │   └── custom.xml
    ├── _rels
    └── xl
        ├── calcChain.xml
        ├── charts
        │   ├── chart1.xml
        │   ├── chart2.xml
        │   ├── colors1.xml
        │   ├── colors2.xml
        │   ├── _rels
        │   │   ├── chart1.xml.rels
        │   │   └── chart2.xml.rels
        │   ├── style1.xml
        │   └── style2.xml
        ├── drawings
        │   ├── drawing1.xml
        │   ├── drawing2.xml
        │   └── _rels
        │       ├── drawing1.xml.rels
        │       └── drawing2.xml.rels
        ├── printerSettings
        │   └── printerSettings1.bin
        ├── _rels
        │   └── workbook.xml.rels
        ├── sharedStrings.xml
        ├── styles.xml
        ├── theme
        │   └── theme1.xml
        ├── workbook.xml
        └── worksheets
            ├── _rels
            │   ├── sheet1.xml.rels
            │   └── sheet2.xml.rels
            ├── sheet1.xml
            └── sheet2.xml

On this file I had two sheets which compare to the tab on the workbook.

the workbook.xml, in the xl directory, has the names of the sheets on it and it looks like this:

<sheets><sheet name="Test1" sheetId="1" r:id="rId1"/><sheet name="Test2" sheetId="2" r:id="rId2"/></sheets>

and each sheet(1/2).xml, in the worksheet directory contains the columns/rows
and it looks like this:

<dimension ref="A1:J1352"/>
   <sheetViews>
      <sheetView topLeftCell="A3" workbookViewId="0">
         <selection activeCell="F28" sqref="F28"/>
      </sheetView>
   </sheetViews>
   <sheetFormatPr defaultRowHeight="14.4" x14ac:dyDescent="0.3"/>
   <cols>
      <col min="1" max="1" width="27.6640625" customWidth="1"/>
      <col min="2" max="2" width="15.109375" customWidth="1"/>
      <col min="3" max="3" width="14.6640625" customWidth="1"/>
      <col min="4" max="4" width="15.33203125" customWidth="1"/>
      <col min="7" max="7" width="13.6640625" customWidth="1"/>
   </cols>
   <sheetData>
      <row r="1" spans="1:10" x14ac:dyDescent="0.3"> 
          <c r="A1" t="s">
              <v>1</v>
          </c>
      </row>
      <row r="2" spans="1:10" x14ac:dyDescent="0.3">
         <c r="B2" t="s"> 
            <v>4</v> 
         </c>
         <c r="C2" t="s">
            <v>5</v> 
         </c>
         <c r="D2" t="s">
            <v>6</v>
         </c>
      </row>
      <row r="3" spans="1:10" x14ac:dyDescent="0.3">
         <c r="A3" s="1">
            <v>42969.681041666663</v>
         </c>
         <c r="B3"> 
            <v>892.76</v>
         </c>
         <c r="C3">
            <v>138.62</v>
         </c>
         <c r="D3">
            <v>138.62</v>
         </c>
      </row>
      <row r="4" spans="1:10" x14ac:dyDescent="0.3">
         <c r="A4" s="1">
           <v>42969.68173611111</v>
         </c>
         <c r="B4">
           <v>954.83</v>
         </c>
         <c r="C4">
           <v>88.97</v>
         </c>
         <c r="D4">
           <v>88.97</v>
         </c>
         <c r="G4" s="4"/>
         <c r="H4" s="3" t="s">
           <v>9</v>
         </c>
         <c r="I4" s="3" t="s">
           <v>10</v>
         </c>
         <c r="J4" s="3" t="s">
           <v>11</v>
         </c>
      </row>
      <row r="5" spans="1:10" x14ac:dyDescent="0.3">
         <c r="A5" s="1">
           <v>42969.682430555556</v>
         </c>
         <c r="B5">
           <v>875.17</v>
         </c>
         <c r="C5">
           <v>94.14</v>
         </c>
         <c r="D5">
           <v>94.14</v>
         </c>
         <c r="G5" s="3" t="s">
           <v>2</v>
         </c>
         <c r="H5" s="3">
           <f>AVERAGE(B3:B1352)</f>
           <v>699.96431851851924</v>
         </c>
         <c r="I5" s="3">
           <f>MAX(B3:B1352)</f>
           <v>1270.3399999999999</v>
         </c>
         <c r="J5" s="3">
           <f>MIN(B3:B1352)</f>
           <v>177.93</v>
         </c>
      </row>
   </sheetData>
   <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
   <pageSetup orientation="portrait" r:id="rId1"/>
   <drawing r:id="rId2"/>
</worksheet>

So if you can modify the xml files using the tags correctly, you can alter or create a excel worksheet just using a text editor and a compression program

University

Amazing! so that excel is a zip file...it is a good way to try but a little complex,thank you! – Overpass

@Raxl, Reading old answers: I like the deep core hack (+1) – Dapplegray

OP, why have you not acccepted this excellent answer? Doing so will help others who read this question in future. – Embryology

@MawgsaysreinstateMonica it is a cool hack way to modify a excel file, and it do solve this question. But it is not suitable for most people, it will make your code unreadable. so I think the easy way is to use a third package, which will make your code clear and brief. – Overpass

Some of us don't have the luxury of installing extra s/w on our work PCS. Sucks, but it can't be changed – Embryology

Answer 2

Unfortunately it is not possible as this. Creating Excel files may require two parts running on the client-side:

• Office Excel installed (can be relaxed if Open XML format is supported);
• Python Wrapper for Excel such as xrld or openpyxls.

Python Standard Library does not include wrapper for Office products. Instead they provide commodities for Comma Separated Value files that are easily readable with Excel.

The Openpyxl project works with Open XML format and then there is no need for Office to be installed. Citing its documentation:

Openpyxl is a Python library for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files.

It was born from lack of existing library to read/write natively from Python the Office Open XML format.

Then you just have to update your Python setup with this package to be able to create spreadsheet.

If your server cannot access internet but accept external media to be mounted, then you can download source on another machine and store it on a removable medium, mount the medium on your server, then decompress it and install it using python setup.py interface:

tar -xvzf openpyxl.tar.gz /somepath
cd /somepath/to/installer
python setup.py install

It works on Debian (no office installed, using pipy source):

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.save('test.xlsx')

Produces an empty spreadsheet readable by Excel.

Dapplegray

You don't need a python wrapper if you use win32com. – Zaneta

@DavidZemens, updated my answer to be less restrictive. Maybe you can propose another answer using this solution. By the way: is win32com another generic wrapper? – Dapplegray

It may not be... but your main point remains: even with win32com library, you still need Excel/Office installed. – Zaneta

@DavidZemens, never heard before, is it helpful? Openpyxl does relax the need of Office installed if only Open XML format is used. Maybe it will be part of the Standard Library in few years. – Dapplegray

It's helpful if you do a lot of office development (and especially if you have any background with handling Office apps via VB or VBA), because it exposes the full object models and familiar methods/properties for Excel, PowerPoint, Word, etc. There are a lot of things you can't do, for instance in XLWT or XLRD, that you can do via win32com. – Zaneta

@Dapplegray Thank you very much. but I need to run Python on all severs to get my need by analysing the data. That means I have to install 'openpyxl' on all the machines...it is not acceptable...my boss will kill me.. – Overpass

@Z.Lun, ok you also may want to have a look on sqlite3 module in standard library, it may lead you toward a more flexible solution. You server outputs in sqlite format and then you reprocess it after in xls. – Dapplegray

Answer 3

I am not sure if this is what the OP was looking for,but if you have to manipulate data in python without installing any modules (just standard library), you can try the sqlite3 module, which of course allows you to interact with sqlite files (a Relational Database Management System).

These databases are conceptually similar to an Excel file. If an excel file is basically a collection of sheets, with each sheet being a matrix where you can put data, sqlite databases are the same (but each "matrix" is called a table instead).

This format is scripting friendly, as you can read and write data using SQL, but it does not follow the client-server model other DBMS are based on. The whole database is contained in a single file that you can email to a colleague, and you can also install a GUI that gives you a spreadsheet like interface to make it more user-friendly (DB browser for SQLite is avaible for Windows, Linux and Mac).

This allows you to include SQL code in your python scripts, which adds a lot of data processing capabilities, and it is an excellent way to achieve data persistence for simple programs.

Forelock

Answer 4

I find a way which is not recommended, but it do solve the problem.

For example, I use the third pacakge openpyxl to show how to do that. Every machine is not allowed to install modules at will, but you can upload any file.

Find a networked machine，execute pip install openpyxl

cd your pacakge dir, on my machine it is cd /usr/lib/python2.7/site-packages/

zip the dir or file updated recently. you can see them by using ls -ltr | tail -10

drwxr-xr-x   3 root root   4096 Jan 12 11:21 shadowsocks
drwxr-xr-x   2 root root   4096 Jan 12 11:21 shadowsocks-2.8.2-py2.7.egg-info
-rw-r--r--   1 root root  12462 Apr 21 10:38 jdcal.py
-rw-r--r--   1 root root  13004 Apr 21 10:38 jdcal.pyc
drwxr-xr-x   2 root root   4096 Apr 21 10:38 jdcal-1.4.1.dist-info
drwxr-xr-x   3 root root   4096 Apr 21 10:38 et_xmlfile
drwxr-xr-x   2 root root   4096 Apr 21 10:38 et_xmlfile-1.0.1-py2.7.egg-info
drwxr-xr-x  20 root root   4096 Apr 21 10:38 openpyxl
drwxr-xr-x   2 root root   4096 Apr 21 10:38 openpyxl-2.6.4-py2.7.egg-info

We will find that openpyxl and its dependencies jdcal,et_xmlfile

so I zip file by order zip -qr openpyxl-2.6.4.zip jdcal.py jdcal.pyc et_xmlfile openpyxl

Then upload this file to an unconnected machine to use by:

>>> import os,sys
>>> sys.path.insert(0,'/root/openpyxl-2.6.4.zip')
>>> import openpyxl

it success.

Overpass

Answer 5

Try CSV if you haven't tried yet.

https://docs.python.org/2/library/csv.html

Shetler

Thanks, I will try it. – Overpass

Answer 6

I have a script to read/write xlsx that does not depend on third-party libraries, it just uses the standard library. You can simply copy the single file to the target system - it can be used both as commandline tool to convert other data like csv, or as a library to directly read/write Excel files.

https://pypi.org/project/tabxlsx/

Saber