spreadsheet-tools 0.0.1

All-in-one spreadsheet tools.

Spreadsheet Tools

This application allows you

• open, view and edit small to large (more than 1,048,576 rows) Excel and CSV files,
• merge Excel/CSV file into single file,
• split Excel/CSV file into equal parts,
• to create a single data set from multiple excel files in the same template,
• to create multiple excel files in the same template from a single data set
• run any python code in Python shell.

Contents

• Installation
• Usage
  • Excel/CSV Viewer
  • Merge Excel/CSV
  • Split Excel/CSV
  • Multiple Excel Reader
  • Multiple Excel Writer
  • Python Shell
• Example usage
• Open Source Licenses
• TODO

Installation

Python package

Install package with pip:

pip install spreadsheet-tools

in shell run command:

spreadsheet-tools

Windows Users Download Packages

Click here to download packages.

Run from code

Clone the repository:

git clone https://github.com/fatihmete/spreadsheet-tools

Change directory:

cd spreadsheet-tools

Before running you have to install required packages:

pip install requirements.txt

Finally run:

python st.py

Usage

Excel/CSV Viewer

You can view and edit Excel/CSV file with this screen. To select Excel/CSV file please click "..." button at top right. If you open CSV file, It asks CSV seperator of file from you. You can navigate in data with Next, Prev, First and Last button at bottom. Also you can change count of rows will be shown on screen with change values of Show rows. This screen allow you filter loaded file with pandas query, drop selected cols and rows, run python code (pandas functions), save result data.

Query

You can write pandas query to filter data. To apply filter press Enter key. It uses python as engine. Below are examples that you can use in the titanic dataset.

Filter only who survived data:

survived == 1

Filter who is survived and female data:

survived == 1 and sex=="female"

Filter who is survived, female and name contains "Becker" or "Wells":

survived == 1 and sex=="female" and (name.str.contains("Becker") or name.str.contains("Wells"))

Filter who is survived, female, name contains "Becker" or "Wells" and pclass + sibsp greater than 2:

survived == 1 and sex=="female" and (name.str.contains("Becker") or name.str.contains("Wells")) and pclass + sibsp > 2

You can use any pandas function (e.g. .isna(), isnull()) that is supported in pandas query.

Drop Cols/Rows

To drop columns/rows firstly select columns/rows will be removed then press Drop Cols/Rows button at top right. This operations can't be undo so be careful. But It's not affect original data.

Run Python Code

You can edit your data with python code. For example you can create new columns with functions (like abs(), min(), max()), fill Nan values (fillna()), split columns with seperator. Your limit is your pandas/python knowledge. You can reach your data with predefined df variable (Pandas DataFrame) and pandas with predefined pd variable.

Below are examples that you can use in the titanic dataset.

To create new column that sum of pclass and survived:

df["sum"] = df["pclass"] + df["survived"]

Create last name and first name column using str.split function:

df[["last_name","first_name"]] = df["name"].str.split(",", expand=True)

fare column in the Titanic dataset contains "?" for nan values and column type is Object. To fix this:

df["fare"] = pd.to_numeric(df["fare"], errors="coerce")

Or you can set new value for "?" values in home.dest column:

df["home.dest"] = df["home.dest"].str.replace("?","Anywhere")

Note: You can't directly edit df variable.

Saving Data

To save the filtered and edited data, please click the Save Data button at the bottom left.

Merge Excel/CSV

You can merge more than one Excel/CSV files in single data file. All files will be append one after the other. You can select which files will be read in input files path. It is possible to set *.xlsx, *.csv or mix type. Also you can set csv seperator for input files.

Split Excel/CSV

You can split a Excel/CSV file into parts containing the number of lines you want.

You can select file that will be splitted with ... button. After you have to select Output Files Path where you new files will be save. You can change format of new files either *.xlsx or *.csv . Row number default set 1000, you can change it. Also you can set input file and out files csv seperator.

Multiple Excel Reader

You can create a single data set from multiple excel files in the same template with this screen.

Adding Sheets

First you have to add sheets in your excel file.

Please write sheet name (be sure it's correct!) then press Add New Sheet button. If you want to delete sheet, select related sheet after press Delete Selected Sheet button.

Adding Rules

To add new rule please press Add New Rule button then select sheet name, set cell and column name.

While reading excel files merw uses this rules. For example, supposing our first rule is "Sheet1", "B1" and "NAME", merw will open Sheet1 of excel file and get "B1" cell value. After write this value on "NAME" column of output file. There isn't rule limit. If you want to delete rule, select related rule after press Delete Selected Rule button.

Setting Input Files Path and Output File

Input Files Path is where your excel files at located. Output File can be xlsx or csv format, it is single dataset will be created from excel files at Input Files Path.

To set Input Files Path and Output File, please press ... button where right of them.

Finally press Run Rules button, it creates single dataset (Output File). If file format of your ouput file is *.csv, application asks you csv seperator. You can set any value as seperator.

Saving and Loading Rules

If you want to use rules later, you can save rules with Save Reading Rules button. To load rules that priorly you saved, press Load Reading Rules button and select rules file.

Multiple Excel Writer

You can create multiple excel files in the same template from a single data set.

Adding Sheets

First you have to add sheets in your excel file.

Please write sheet name (be sure it's correct!) then press Add New Sheet button. If you want to delete sheet, select related sheet after press Delete Selected Sheet button.

Adding Rules

To add new rule please press Add New Rule button then select sheet name, set cell and column name.

While writing excel files merw uses this rules. For example, supposing our first rule is "NAME", "Sheet1", "B1", merw will read value in "NAME" column of dataset and set B1 cell of Sheet1 of template excel to this value. There isn't rule limit. If you want to delete rule, select related rule after press Delete Selected Rule button.

Setting Input Files Path and Output File

Output Files Path is where your new excel files at located. Input File can be xlsx or csv format, it is single dataset will be used to create new excel files. Template File is template. merw will create copy of this file and fill each copy of file with dataset values.

To set Output Files Path , Inut File, Template File please press ... button where right of them.

Finally press Run Rules button, it creates multiple excel files (1.xlsx, 2.xlsx ...) compatible with template file(at Output Files Path). If your file format of your dataset is *.csv, application asks you csv seperator. You can set any value as seperator.

Saving and Loading Rules

If you want to use rules later, you can save rules with Save Writing Rules button. To load rules that priorly you saved, press Load Writing Rules button and select rules file.

Python Shell

If the other functions not enough for you or you want to make different things, you can run python code inside of the application. It offer interactive python shell for you. For example you need to get and save currency values from API:

import json
import pandas as pd
import urllib.request
with urllib.request.urlopen("https://open.exchangerate-api.com/v6/latest") as response:
   content = response.read()
content = json.loads(content)
rates = []
for rate, value in content["rates"].items():
	rates.append([rate, value])
df = pd.DataFrame(rates, columns=["rate","value"])
print(df.head())
df.to_excel(r"rates.xlsx", index=False)

Example usage

Create ticket (excel file) for titanic passangers

First download titanic dataset. Then press Multiple Excel Writer button and load titanic_write.json file. I looks below:

Then set Output Files Path, Input File (titanic.csv) and Template File (ticket.xlsx). Finally press Run Rules. It can creates copy of ticket.xlsx for every passanger in data set at output location.

Create dateset from titanic tickets

In previous example we create ticket.xlsx every passenger of Titanic. To create a dataset from this tickets, we use outputs of previously example. Then press Multiple Excel Writer button and load titanic_read.json file. I looks below:

Then set Input Files Path (where copies of ticket.xlsx files located) and Output File. Finally press Run Rules. It can creates a single dataset from titanic tickets.

Open Source Licenses

This software uses other software below:

• Python licence
• Pyqt5 license
• Pandas license
• Google Material Icons license
• Qt5 license
• Openpyxl license

TODO

• Improve GUI
• Add *.xls and other open document files support
• Prebuild package for other OS's