xls_to_csv.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "'''Open in google colab please'''\n",
    "\n",
    "%matplotlib notebook\n",
    "import pandas as pd\n",
    "import csv\n",
    "import base64\n",
    "from zipfile import ZipFile\n",
    "from IPython.display import display, clear_output\n",
    "import ipywidgets as widgets\n",
    "\n",
    "\n",
    "class ExcelToCSV_Dialog():\n",
    "\n",
    "  def __init__(self):\n",
    "    self.excel_file = None\n",
    "    self.file_hboxes = []\n",
    "    self.start_dialog()\n",
    "    self.filename = \"\"\n",
    "\n",
    "\n",
    "  def on_clear(self, _):\n",
    "    self.file_hboxes = []\n",
    "    self.excel_file = None\n",
    "    self.uploader.value.clear()\n",
    "    self.uploader._counter = 0\n",
    "\n",
    "  def readout_checkbox_values(self):\n",
    "    \"\"\"\n",
    "      returns a list of sheetnames, which are to be parsed from the\n",
    "      specified excel_file\n",
    "    \"\"\"\n",
    "    return [cb.description for cb in self.checkboxes if cb.value]\n",
    "\n",
    "  def return_function(self, *args):\n",
    "    pass\n",
    "\n",
    "\n",
    "  def select_all_function(self, *args):\n",
    "    for cb in self.checkboxes:\n",
    "      cb.value=True\n",
    "\n",
    "  def display_sheet_checkboxes(self):\n",
    "\n",
    "    for name in self.excel_file.sheet_names:\n",
    "\n",
    "      try:\n",
    "        df = self.excel_file.parse(sheet=name)\n",
    "        res = df.to_csv(index=False)\n",
    "\n",
    "        filename = name + \".csv\"\n",
    "        b64 = base64.b64encode(res.encode())\n",
    "        payload = b64.decode()\n",
    "        html_button = '''<html>\n",
    "        <head>\n",
    "        <meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">\n",
    "        </head>\n",
    "        <body>\n",
    "        <a download=\"{filename}\" href=\"data:text/csv;base64,{payload}\" download>\n",
    "        <button class=\"p-Widget jupyter-widgets jupyter-button widget-button mod-warning\">Download File</button>\n",
    "        </a>\n",
    "        </body>\n",
    "        </html>\n",
    "        '''\n",
    "        button = widgets.HTML(html_button.format(payload=payload,filename=filename))\n",
    "        self.file_hboxes.append(widgets.HBox([widgets.Text(name), button]))\n",
    "\n",
    "      except:\n",
    "        # sometimes, excel sheets can't be turned into csv files,e.g. when\n",
    "        # the excel sheet contains a diagram\n",
    "        continue\n",
    "\n",
    "    display(widgets.VBox(self.file_hboxes))\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "  def upload_excel_file(self, *args):\n",
    "\n",
    "    with self.out:\n",
    "\n",
    "      self.out.clear_output()\n",
    "      if not self.uploader.value.keys():\n",
    "        print('pls upload a file first')\n",
    "        return\n",
    "\n",
    "      tmp_file = self.uploader.value[list(self.uploader.value.keys())[0]]\n",
    "      self.filename = tmp_file[\"metadata\"][\"name\"]\n",
    "      content = tmp_file[\"content\"]\n",
    "      self.excel_file = pd.ExcelFile(content)\n",
    "      self.display_sheet_checkboxes()\n",
    "\n",
    "\n",
    "  def start_dialog(self):\n",
    "    self.uploader = widgets.FileUpload(\n",
    "      accept='.xls',  # Accepted file extension e.g. '.txt', '.pdf', 'image/*', 'image/*,.pdf'\n",
    "      multiple=False,  # True to accept multiple files upload else False\n",
    "      description='select xls file'\n",
    "    )\n",
    "\n",
    "    self.clear = widgets.Button(description='Clear!', layout=widgets.Layout(width='100px'));\n",
    "    self.clear.on_click(self.on_clear)\n",
    "    self.file = widgets.HBox([widgets.Label(value=\"File:\"), self.uploader, self.clear])\n",
    "\n",
    "    self.upload_excel_button = widgets.Button(description='upload', layout=widgets.Layout(width='200px'));\n",
    "    self.upload_excel_button.on_click(self.upload_excel_file)\n",
    "    self.out = widgets.Output()\n",
    "\n",
    "    display(self.file, self.upload_excel_button, self.out)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "#@title XLS to CSV { vertical-output: true }\n",
    "a = ExcelToCSV_Dialog()\n",
    "\n",
    "HBox(children=(Label(value='File:'), FileUpload(value={}, accept='.xls', description='select xls file'), Butto…\n",
    "\n",
    "Button(description='upload', layout=Layout(width='200px'), style=ButtonStyle())\n",
    "\n",
    "Output()\n",
    "\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}