複数のCSVファイルからExcel-Bookを作成する
複数のCSVファイルから複数のシートで構成されるBOOKを作成します。
次の機能を持ちます
- ファイル名がシート名となる
- 列のサイズを内容にそろえる
- 先頭行はタイトルと解釈され、スクロールしない
- 奇数行、偶数行で色を変える
- セルを罫線で囲む
次のようなコマンドを作成しました。
Linux:
csvToExcel.py
-in_dir CSVファイルを置いてあるフォルダ
-out 出力するEXCELファイル名
[-fit サイズ余裕] 1.5程度；カラム調整をしない場合 -fit 0)
[-no_fix] 先頭行を固定にしない
[-title_color 先頭行のバックグラウンド色、文字色] デフォルト:00008b,ffffff
[-line_coloi 奇数行色,偶数行色またはnone] デフォルト:dce6f0,FFFFFF
[-font fit指定時のフォント名] デフォルト:"MS Gothic")
[-border_style ボーダー線指定 thin/hair/dotted/none] デフォルトはthin
[-cross_border] 縦横の罫線をいれる
[-number] 数値文字列をexcelの判断で数値として扱う
Windows:
csvToExcel.bat
引数は同じ
制限事項;行とカラム
全行のカラム数は一致している必要があります。
必須ではありませんが、先頭行はタイトル行としてカラム名を表すとされます。
後述の-numberを付けても数値扱いされません。
デフォルトでは先頭行はタイトル行として固定されスクロールされません。
-no_fixオプションを付けると、全行スクロール対象となります。
デフォルトではカラムサイズは内容全体を表示できるように調整されます。
-fitオプションで、カラムサイズを内容最大長の何倍にするかを指定できます。デフォルトは1.3です。
-fit 0を指定すると、カラムサイズの調整を含め、カラムに関する一切の調整を行いません。
文字列/数値の取り扱い
デフォルトでは全ての値を文字列として取り扱います。
01,02,1-2,0.9999999999999999999といった数値文字列もそのまま表示できます。
数値形式の文字列はExcelでは警告対象ですので、各セルの左肩に緑三角マークが表示されてしまいます。このマークを自動で消す方法は今の所分かっていません。
-numberオプションを付けると、これらはExcelの数値として取り扱われます。
Excelの仕様上、表示が内容と異なるものとなることが多いので注意が必要です。
CSVからBOOKを作る例
次の２つのCSVからExcelのBOOKを作ってみました。
csv_sample/sample1.csv
名前,id,年齢,性別,趣味,好きな食べ物
田中太郎,001,25,男性,読書,寿司
山田花子,002,30,女性,料理,ラーメン
佐藤健太,003,35,男性,スポーツ,カレーライス
鈴木みゆき,004,28,女性,旅行,焼肉
伊藤健介,005,40,男性,映画鑑賞,寿司
小林美加,006,22,女性,ショッピング,ピザ
高橋一郎,007,33,男性,音楽鑑賞,ラーメン
csv_sample/sample2.csv
都道府県,id,市区町村,人口,面積,主要産業系,交通手段,観光スポット,名所
東京都,01,千代田区,693033,10.21,金融業,電車,東京タワー,皇居
大阪府,02,大阪市北区,1321543,15.97,製造業,地下鉄,大阪城,道頓堀
北海道,03,札幌市中央区,198875,46.45,情報通信業,バス,時計台,狸小路
沖縄県,04,那覇市,321364,38.99,観光業,レンタカー,首里城,国際通り
福岡県,05,福岡市博多区,1593506,340.24,サービス業,飛行機,博多祇園山笠,太宰府天満宮
次のような２つのシートからなるBOOKが生成できました。
オプション無:
-cross_border -number:
プログラム
Python3を用いています。
次の設定が必要です。（この例はLinuxです)
$ sudo apt install python3
$ sudo apt install python3-pip
#---------------------------------------
$ pip install pandas
$ pip install xlsxwriter
$ pip install openpyxl
$ pip install pyarrow
ソースは次のURLでダウンロードできます。
csvtoexcel.py
次のファイルから構成されています。
csv2excel/
|-- bin
| |-- CsvToExcel.py # python プログラム
| |-- csvToExcel # コマンド風bashスクリプト（不要）
| `-- csvToExcel.bat # Windowsバッチ
|-- test
| |-- A01_test.bat # 試験起動バッチ
| |-- A01_test.sh # 試験起動bash
| `-- csv_sample
| |-- sample1.csv # CSVファイル1
| `-- sample2.csv # CSVファイル2
`-- 準備
|-- setPythonEnv.bat # pythonライブラリ設定バッチ
`-- setPythonEnv.sh # pythonライブラリ設定bash
Linuxの場合、ファイルに実行権を付加しておく必要があります。
Linuxの場合
$ unzip csv2excel.zip
$ cd csv2excel
$ chmod a+x */*.sh */*.py
試験、使い方
サンプルを用意してあります。
test下で
・A01_test.bat
または
・A01_test.sh
を実施。
データはcsv_sample下にあり、結果はcsv_sample下にsample.xlsxとして出力されます。
テストスクリプトは次のようになっています。ここではパスを書き下ろしていますが実行パス環境変数を設定しておけば、直接コマンドを書くことも可能です。
ーーーー A01_test.bat －－－ー
@echo off
:: SJIS CR/LF
setlocal
set "THIS_DIR=%~dp0"
set "BIN_DIR=%~dp0..\bin\"
cd %THIS_DIR%
if ERRORLEVEL 1 ( echo "SOME ERROR OCCURED";exit 1)
call %BIN_DIR%csvToExcel.bat -in_dir csv_sample -out csv_sample\sample.xlsx
if ERRORLEVEL 1 ( echo "SOME ERROR OCCURED";exit 1)
echo OK
endlocal
pause
::この行を消してはならない
ーーーー A01_test.sh －－－ー
#!/bin/bash
# UTF-8 LF
THIS_DIR=$(cd $(dirname ${BASH_SOURCE:-$0}); pwd)
BIN_DIR=$(cd ${THIS_DIR}/../bin;pwd)
cd ${THIS_DIR}
if [ $? -ne 0 ]; then echo "SOME ERROR OCCURED";exit;fi
${BIN_DIR}/CsvToExcel.py -in_dir csv_sample -out csv_sample/sample.xlsx
if [ $? -ne 0 ]; then echo "SOME ERROR OCCURED";exit;fi
echo "OK"
# この行を消してはならない
コード
Windowsでは一旦プロセスを終了しないと一時ファイルが解放されないため、
２度の手続き(２度めは一時ファイルの削除のみ)を行っている。
Linuxはコマンド呼び出し風のスクリプトを置いたが、これを使用せず
CsvToExcel.pyを直接起動する形でもよい。
ーーーー csvToExcel －－－ー
#!/bin/bash
# UTF-8 LF
THIS_DIR=$(cd $(dirname ${BASH_SOURCE:-$0}); pwd)
python3 ${THIS_DIR}/CsvToExcel.py $*
#この行を消してはならない
ーーーー csvToExcel.bat －－－ー
@echo off
:: SJIS CR/LF
setlocal
set "THIS_DIR=%~dp0"
echo python3 %THIS_DIR%CsvToExcel.py -do_not_remove_temp %*
python3 %THIS_DIR%CsvToExcel.py -do_not_remove_temp %*
echo python3 %THIS_DIR%CsvToExcel.py -do_remove_temp %*
python3 %THIS_DIR%CsvToExcel.py -do_remove_temp %*
endlocal
pause
:: この行を消してはならない
ーーーー CsvToExcel.py －－－ー
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# utf-8 LF
#----------------------------------------------
# 複数のCSVファイルからexcel-bookを作成する
# 各CSVファイルがシートを構成する
from pickle import NONE
import traceback,argparse,os,sys
from types import NoneType
import pandas
import glob
import unicodedata
import warnings
from pandas import DataFrame
from typing import Tuple, List,Literal,Optional
from openpyxl import load_workbook,Workbook
from openpyxl.cell.cell import Cell
from openpyxl.styles import Font,PatternFill,Border,Side,NamedStyle
# 等幅フォントの文字幅を設定
char_width_jp:float = 2.0 # 日本語文字の文字幅
char_width_en:float = 1.0 # 英数字の文字幅
# 文字の幅を計算する関数
def calculate_width(text_:str)->float:
_width:float = 0
for _char in text_:
# _char:strだぜ！驚け！
if unicodedata.east_asian_width(_char) in ('F', 'W', 'A'):
_width += char_width_jp # 日本語文字の場合
#end-if
else:
_width += char_width_en # 英数字の場合
#end-else
#end-for
return _width
#end-def
def main():
try:
_args_parser:argparse.ArgumentParser= argparse.ArgumentParser()
_args_parser.add_argument("-in_dir",dest="input_dir" , help="Input dir")
_args_parser.add_argument("-out" ,dest="output_file", help="Output file")
_args_parser.add_argument('-fit' ,dest="fit" , type=float,default=1.3,
help='Specify the colum fix margin value. may 1.0 or over')
_args_parser.add_argument('-font' ,dest="font" , type=str,default="MS Gothic",
help='Specify the font')
_args_parser.add_argument('-no_fix',dest="no_fix" , action='store_true',
help='do not fix top row')
_args_parser.add_argument('-title_color' ,dest="title_color" ,type=str,default="00008b,FFFFFF",
help='color of title line as BG,TXT (00008b,ffffff)')
_args_parser.add_argument('-line_color' ,dest="line_color" ,type=str,default="ffffff,dce6f0",
help='color of line as EVEN,ODD (ffffff,dce6f0) or -line_color none')
_args_parser.add_argument('-border_style',dest="border_style" ,type=str,default="thin",
help='boder style as none/thin/hair/dotted')
_args_parser.add_argument('-cross_border',dest="cross_border" ,action='store_true',
help='set only vertical border')
_args_parser.add_argument('-number',dest="number" ,action='store_true',
help='tlet excel determine number')
"""
'thin'：最も細い罫線
'medium'：中間の太さの罫線
'thick'：最も太い罫線
'hair'：髪の毛のように細い罫線
'dashDot'：点線と破線が交互に表示される罫線
'dashDotDot'：点線と二重破線が交互に表示される罫線
'dashed'：破線の罫線
'dotted'：点線の罫線
'double'：二重線の罫線
'slantDashDot'：斜めの点線と破線が交互に表示される罫線
"""
# Windowsではcloseしても同一プロセス内ではremoveできない
_args_parser.add_argument('-do_remove_temp',dest="do_remove_temp" , action='store_true',
help='do only remove tempolary file')
_args_parser.add_argument('-do_not_remove_temp',dest="do_not_remove_temp",action='store_true',
help='do not remove tempolary file')
#86.3,90.2.94.5-> 220,230,240 -> dce6f0
_args:argparse.Namespace =_args_parser.parse_args()
_csv_directory :str = _args.input_dir
_excel_file_name:str = _args.output_file
_excel_temp_file:str = _excel_file_name # book書き換えは出来ないため、一時ファイル
_fit :float = _args.fit
_font :str = _args.font
_fix_top :bool = not _args.no_fix
_title_color :str = _args.title_color
_line_color :str = _args.line_color
_border_style :str = _args.border_style
_cross_border :bool = _args.cross_border
_do_not_remove :bool = _args.do_not_remove_temp
_number :bool = _args.number
_color_palette :List[str]=None
if _line_color!="none" :
_color_palette = _line_color.split(",")
_color_palette[0]=_color_palette[0].lower()
_color_palette[1]=_color_palette[1].lower()
#end-else
_title_colors:List[str] = _title_color.split(",")
if _fit != 0.0:
_excel_temp_file=_csv_directory+"/_tempolary_for_column_fitting.xlsx"
if _args.do_remove_temp:
print(f"remove tempolary book file {_excel_temp_file}")
os.remove(_excel_temp_file)
sys.exit(0)
#end-if
#end-if
_title_border: Border=None
_border: Border=None
_border_bottom:Border=None
if _border_style!=None and _border_style!="none" :
_title_border = Border(right =Side(style=_border_style,color=_title_colors[1]))
if _cross_border :
_border = Border(left =Side(style=_border_style),
right =Side(style=_border_style),
top =Side(style=_border_style),
bottom=Side(style=_border_style))
_border_bottom=_border
#end-if
else:
_border = Border(left =Side(style=_border_style),
right =Side(style=_border_style))
_border_bottom = Border(left =Side(style=_border_style),
right =Side(style=_border_style),
bottom=Side(style=_border_style))
#end-else
#end-if
print(f"csv_directory={_csv_directory}")
print(f"excel_file_name={_excel_file_name}")
# CSVファイルの一覧を取得
_csv_files:List[str] = sorted(glob.glob(_csv_directory + '/*.csv'))
#print(f"csv_filez={_csv_files}")
# Excelファイルを作成
with pandas.ExcelWriter(_excel_temp_file, engine='xlsxwriter') as _writer:
# _writer:pandas.io.excel._XlsxWriter
# 各CSVファイルをExcelの別シートに追加
for _csv_file in _csv_files:
# CSVファイル名からシート名を抽出
_sheet_name,_file_extension = os.path.splitext(os.path.basename(_csv_file))
print(f"create sheet {_sheet_name}")
# CSVファイルをDataFrameに読み込み
_df:DataFrame = (pandas.read_csv(_csv_file) if _number else
pandas.read_csv(_csv_file, dtype=str))
# DataFrameをExcelのシートに追加
_df.to_excel(_writer,sheet_name=_sheet_name,index=False)
#end-for
print(f"create book file {_excel_temp_file}")
#end-with
#if not _writer.close:
# _writer.close()
if _fit != 0.0:
print("セルを調整する")
_input_file :str =_excel_temp_file
_output_file :str =_excel_file_name
# Excelファイルを読み込む
_wb:Workbook = load_workbook(_input_file)
# ワークブック内のすべてのシートに対して処理を行う
_color:str=None
for _ws in _wb.worksheets:
# _ws:Worksheet
print(f"set column border {_ws.title}")
# シート内の全てのセルに対して処理を行う
#for _index, _row in enumerate(_ws.iter_rows(), start=1):
_last_row:Tuple[Cell]=None
_index:int=0
for _row in _ws.iter_rows(): #enumerateの使用は極端に遅くなる
_last_row=_row
_index= (_index+1)%2
if _color_palette!=None:
_color=_color_palette[_index]
#end-if
# (rows): Iterable[Tuple[Cell, ...]] = ws.iter_rows()
# _row : Tuple[Cell, ...] 横方向のセル並び
for _cell in _row:
# _cel:Cell (openpyxl.cell.cell.Cell)
# セルのフォントを固定幅に設定する
if _color!=None and _color!="ffffff":
_cell.fill = PatternFill(start_color=_color, end_color=_color, fill_type="solid")
#end-if
_cell.font = Font(name=_font)
if _border!=None :
_cell.border = _border
#end-for cells
#end-for rows
if _border_bottom != None:
for _cell in _row:
_cell.border = _border_bottom
#end-for
#end-if
#end-for sheets
# 各シートのカラム幅を内容に合わせる
for _ws in _wb.worksheets:
print(f"set column size {_ws.title}")
for _column in _ws.columns:
# _column : Tuple[Cell, ...] 縦方向のセル並び
_max_width:float = 0
for _cell in _column:
# セルの内容の幅を計算して比較
_cell_width:float = calculate_width(str(_cell.value))
if _cell_width > _max_width:
_max_width = _cell_width
#end-if
#end-for cells
# カラムの幅を設定
_adjusted_width:float = _max_width * char_width_en # 英数字の文字幅に合わせて調整
_ws.column_dimensions[_column[0].column_letter].width = _adjusted_width + _fit
#end-for 列
if _fix_top :
# 行の背景色を設定します
for _row in _ws.iter_rows(min_row=1, max_row=1):
_columns:int=len(_row)
for _cell in _row:
_cell.fill = PatternFill(start_color=_title_colors[0], end_color=_title_colors[0], fill_type="solid")
_cell.font = Font(color=_title_colors[1])
_cell.border = _title_border
#end row
#end-for rows
_ws.freeze_panes = 'A2'
#end-if
#end-for sheets
# Excelファイルを保存
print(f"create book file {_output_file}")
_wb.save(_output_file)
#_wb.close()
# 一時ファイル削除
if not _do_not_remove:
print(f"remove tempolary book file {_excel_temp_file}")
os.remove(_excel_temp_file)
#end-if
#end-if FIT
#end-try
except Exception as _e:
print("例外発生:"+str(_e)+"\n"+traceback.format_exc())
sys.exit(1)
#end-except
#end-def
if __name__ == '__main__':
main()
#end-def
