– 記録 –

よくわからないときはまずやってみる。

2次元の疎なデータを補間しエクセルファイルへ追加

投稿日: 投稿者: yamktm

Entry Page

Entry Page

Result Page

Result Page
main.py

Interpolate y data from 2D data and new x data

粗めの2次元データで定義される写像から新しいxデータに対するyデータを補間しエクセルファイルへ挿入する。fastAPIを使用した作例。ローカル環境でのみ動作確認した。

データフローを下図に示す。interpolate1d_template.xlsx にデータを入力する。Web I/Fでアップロードし計算後に結果ファイルをダウンロードする。結果ファイルには補間データが挿入される。

graph LR
    indata[input data]
    temp[interpolate1d_template.xlsx]
    src[input.xlsx]
    web[Web App<br>Interpolating]
    dist[input_interp.xlsx]
    indata --> src
    temp --> src
    src --upload--> web --download--> dist

動作確認環境

ローカル環境で動作確認したときの各バージョン情報

python=3.10.14
numpy==2.1.2
matplotlib==3.9.2
openpyxl==3.1.5
scipy==1.14.1
fastapi==0.115.3
uikit==3.6.3

Directory Tree

ローカル環境のディレクトリ構成。

interpolate/
├── static/
│   ├── css/
│   │   └── uikit.min.css
│   └── js/
│       ├── uikit-icons.min.js
│       └── uikit.min.js
├── templates/
│   ├── base.html
│   ├── main.html
│   └── repotr.html
├── template/
│   └── interpolate1d_template.xlsx
├── work/
├── main.py
└── interpolate.py

Template file

計算条件と基となるデータはエクセルに入力しアップロードする。そのためのテンプレートを用意した。

interpolate1d_template.xlsx

Source code

エンドポイントの定義

main.py

import uvicorn
import os
import shutil
import socket
import glob
import platform
import json
import openpyxl as px
import interpolate
import tempfile
from fastapi import FastAPI, Request, Response
from fastapi.responses import HTMLResponse, FileResponse, JSONResponse
from fastapi.staticfiles import StaticFiles
from fastapi.templating import Jinja2Templates

PORT = 9000

WORK_DIR = 'work'
INFO_FILE = 'info.json'
COOKIE_KEY = 'interpolatetmpdir'

BASE_DIR = os.path.dirname(os.path.abspath(__file__))

PLATFORM = platform.system()
if PLATFORM == 'Windows':       # windows
    HOSTNAME = socket.gethostname()
    DIR_SEP = '\\'
elif PLATFORM == 'Darwin':      # my mac
    HOSTNAME = 'localhost'
    DIR_SEP = '/'
elif PLATFORM == 'FreeBSD':     # freebsd
    HOSTNAME = ""
    DIR_SEP = '/'
elif PLATFORM == 'Linux':       # Linux
    HOSTNAME = socket.gethostname()
    DIR_SEP = '/'
else:
    raise('Error: Unknown platform.')

app = FastAPI()
app.mount("/static", StaticFiles(directory="static"), name="static")
templates = Jinja2Templates(directory="templates")

@app.get("/interpolate", response_class=HTMLResponse)
async def start_interpolate(request: Request):
    os.chdir(BASE_DIR)
    if COOKIE_KEY in request.cookies:
        request.cookies[COOKIE_KEY] = None

    fs = glob.glob('./template/*.xlsx')
    request.templatefiles = []
    for f in fs:
        request.templatefiles.append(os.path.basename(f))

    request.templatefileurl = f"{HOSTNAME}:{PORT}/interpolate/download/template"
    return templates.TemplateResponse("main.html", {"request": request})

@app.post("/interpolate/exec", response_class=HTMLResponse)
async def exec_interpolate(request: Request):
    os.chdir(BASE_DIR)
    form = await request.form()
    tmpdir = request.cookies[COOKIE_KEY]
    infopath = os.path.join(BASE_DIR, WORK_DIR, tmpdir, INFO_FILE)
    with open(infopath, "r") as fp:
        infor = json.load(fp)

    infname = infor['infname']
    outfname = infor['outfname']
    infpath = os.path.join(BASE_DIR, WORK_DIR, tmpdir, infname)
    outfpath = os.path.join(BASE_DIR, WORK_DIR, tmpdir, outfname)

    sheets = form.getlist('sheetname')
    request.sheets = sheets
    request.downloadurl = f"{HOSTNAME}:{PORT}/interpolate/download/result/{tmpdir}/{outfname}"
    request.filename = outfname
    if len(sheets) > 0:
        interpolate.interpolates(infpath, outfpath, sheets)
        request.imgurls = []
        imgpaths = glob.glob(os.path.join(BASE_DIR, WORK_DIR, tmpdir, '*.svg'))
        imgpaths.sort(key=os.path.getctime, reverse=False)
        for imgpath in imgpaths:
            imgname = os.path.split(imgpath)[1]
            request.imgurls.append({
                'url': f"{HOSTNAME}:{PORT}/interpolate/display/{tmpdir}/{imgname}",
                # 'imgurl': f"{HOSTNAME}:{PORT}/interpolate/download/result/{tmpdir}/{imgname}",
                'name': f"{imgname}"
            })

        request.status = 'OK'
    else:
        request.status = 'Error'

    return templates.TemplateResponse("report.html", {"request": request})

@app.get("/interpolate/download/template/{filename}")
async def download_file_template(filename):
    fpath = f"{os.getcwd()}/template/{filename}"
    return FileResponse(fpath)

@app.get("/interpolate/download/result/{workdir}/{filename}")
async def download_file_result(workdir, filename):
    fpath = os.path.join(BASE_DIR, WORK_DIR, workdir, filename)
    return FileResponse(fpath)

@app.post("/interpolate/upload", response_class=JSONResponse)
async def upload_file_xlsx(request: Request, response: Response):
    os.chdir(BASE_DIR)
    form = await request.form()
    uploadf = form['file']
    infor = dict()
    sheets = list()
    infname = uploadf.filename
    workdirpath = tempfile.mkdtemp(dir=os.path.join(BASE_DIR, WORK_DIR))
    tmpdir = workdirpath.split(DIR_SEP)[-1]
    response.set_cookie(key=COOKIE_KEY, value=tmpdir)
    infopath = os.path.join(workdirpath, INFO_FILE)

    os.chdir(workdirpath)
    with open(infname, 'wb') as fp:
        fp.write(uploadf.file.read())

    base, ext = os.path.splitext(infname)
    outfname = base + '_interp' + ext
    # os.rename(infname, outfname)
    shutil.copy2(infname, outfname)
    sheets = getSheetNames(infname)
    infor = {
        'infname': infname,
        'outfname': outfname,
        'sheets': sheets
    }
    with open(infopath, "w") as fp:
        json.dump(infor, fp)

    return {"infor": infor}

@app.get('/interpolate/display/{workdirpath}/{filename}')
def display_img(workdirpath, filename):
    fpath = os.path.join(BASE_DIR, WORK_DIR, workdirpath, filename)
    return FileResponse(fpath)

def getSheetNames(workFilePath):
    wb = px.load_workbook(workFilePath)
    sheets = wb.sheetnames
    return sheets

if __name__ == "__main__":
    uvicorn.run("main:app", host="0.0.0.0", port=PORT, reload=False)

openpyxl を利用してエクセルファイルを操作する。補間は scipy の interpolate を使用しグラフは matplotlib で描画した。

interpolate.py

#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""
V001:
"""
import os
import datetime
import numpy as np
import openpyxl as px
import matplotlib.pyplot as plt
from scipy import interpolate
from dataclasses import dataclass
from typing import List, Dict

HEADER_POS = 'C5'
NEW_X_POS = 'E6'
GRAPH_POS = 'M6'
GRAPH_POS_DEF = 26

@dataclass
class Interpolate:
    x:      np.ndarray
    y:      np.ndarray
    x_label:    str
    y_label:    str
    n_data:     int
    nx_label:   List[str]
    ny_label:   List[str]
    methods:    List[str]
    n_nx:       int

def interpolates(infpath, outfpath, sheets):
    starttime = datetime.datetime.now()
    for sheet in sheets:
        exec_interpolate(infpath, outfpath, sheet)

    endtime = datetime.datetime.now()

def exec_interpolate(infpath, outfpath, sheet):
    interp1d = set_interpolate(infpath, sheet)
    make_interpolate(infpath, outfpath, sheet, interp1d)

def set_interpolate(fpath, sheet) -> Dict:
    workdir = os.path.dirname(fpath)
    wb = px.load_workbook(fpath, data_only=True)
    ws = wb[sheet]
    wb.active = ws

    x_label = ws[HEADER_POS].value
    y_label = ws[HEADER_POS].offset(0, 1).value
    nx_label = []
    ny_label = []
    methods = []
    x = np.array([])
    y = np.array([])
    for row in ws.iter_rows(min_row=6, min_col=3, max_col=4):
        if is_num(row[0].value):
            x = np.r_[x, float(row[0].value)]
        if is_num(row[1].value):
            y = np.r_[y, float(row[1].value)]

    n_data = np.size(x, 0)

    for col in ws.iter_cols(min_row=5, min_col=5, max_row=5):
        for head in col:
            if not head.value is None and not head.offset(-1,0).value is None:
                nx_label.append(head.value)
                ny_label.append(head.offset(0, 1).value)
                methods.append(head.offset(-2, 0).value)

    n_nx = len(nx_label)
    wb.close()

    interp1d = Interpolate(
        x = x,
        y = y,
        x_label = x_label,
        y_label = y_label,
        n_data = n_data,
        nx_label = nx_label,
        ny_label = ny_label,
        methods = methods,
        n_nx = n_nx,
    )

    return interp1d

def make_interpolate(infpath, outfpath, sheet, par):
    wb = px.load_workbook(infpath, data_only=True)
    ws = wb[sheet]
    wb.active = ws
    outwb = px.load_workbook(outfpath, data_only=False)
    for w in outwb.worksheets:
        w.sheet_view.tabSelected = False

    outws = outwb[sheet]
    outwb.active = outws
    wGraph = outws[GRAPH_POS]
    pos = wGraph

    nxpos = ws[NEW_X_POS]
    nypos = outws[NEW_X_POS].offset(0, 1)
    for i in range(par.n_nx):
        nx = np.array([])
        j = 0
        while nxpos.offset(j, 0).value != None:
            nx = np.r_[nx, float(nxpos.offset(j, 0).value)]
            j += 1

        par.n_nx = j
        f = interpolate.interp1d(par.x, par.y, kind=par.methods[i], bounds_error=False, fill_value='extrapolate')
        ny = f(nx)
        for j in range(par.n_nx):
            nypos.offset(j, 0).value = ny[j]

        nxpos = nxpos.offset(0, 2)
        nypos = nypos.offset(0, 2)

        fig = plt.figure()
        ax0 = fig.add_subplot(111)
        ax0.set_xlabel(par.nx_label[i])
        ax0.set_ylabel(par.ny_label[i])
        ax0.grid(which='major', color='gray', linestyle='-')
        ax0.plot(par.x, par.y, 'o')
        ax0.plot(nx, ny)
        workdir = os.path.dirname(infpath)
        basename = ws.title + '_' + par.nx_label[i] + '-' + par.ny_label[i] + '_'
        extstr = ''
        while os.path.exists(os.path.join(workdir, basename + extstr + '.png')):
            extstr = str(i)

        fpng = os.path.join(workdir, basename + extstr + '.png')
        plt.savefig(fpng, format='png')
        img = px.drawing.image.Image(fpng)
        outws.add_image(img, pos.coordinate)
        pos = pos.offset(GRAPH_POS_DEF, 0)
        plt.close()

    outwb.save(outfpath)
    outwb.close()
    wb.close()

def is_num(s):
    try:
        float(s)
    except ValueError:
        return False
    except TypeError:
        return False
    else:
        return True

if __name__ == '__main__':
    pass

エントリーページのHTMLファイル。

main.html

{% extends "base.html" %}

{% block title %}Main{% endblock %}

{% block head %}
  {{ super() }}
{% endblock %}

{% block content %}
<style>
  .uk-form-custom{
    color:deepskyblue;
  }
</style>
<div class="uk-container">
  <p class="uk-margin-top">Download and use the template from the link below.</p>
  <ul class="uk-list">Template file list
    {% for templatefile in request.templatefiles %}
    <a class="uk-margin uk-form-small" href="http://{{request.templatefileurl}}/{{templatefile}}">{{templatefile}}</a>
    {% endfor %}
  </ul>
  <p class="uk-margin-top">Drag and drop your Excel file into the area below or click the icon to select it.</p>
  <div class="js-upload uk-placeholder uk-text-center uk-background-muted uk-padding-small">
    <div class="uk-text-right">
      <div uk-form-custom>
        <input id="fileinput" type="file" multiple/>
        <span class="upload-icon"><span uk-icon='icon: cloud-upload; ratio: 1.2'></span></span>
      </div>
    </div>
  </div>
  <progress id="js-progressbar" class="uk-progress" value="0" max="100" hidden></progress>

  <form id="work" class="uk-form" action="/interpolate/exec" method="POST">

    <!-- <input type="hidden" name="user" value="{{user}}"> -->

    <div id="filelist" class="uk-margin">
    </div>

    <div id="sheetlist" class="uk-margin uk-grid-small uk-child-width-auto uk-grid">
    </div>

    <div class="uk-margin">
      <button id="execbutton" class="uk-button uk-button-default" type="submit" disabled>Execute</button>
    </div>

    <div class="uk-margin">
      <span id="statusmsg"></span>
    </div>

  </form>

<script>
    function setParameter(formid, id, name, value) {
      if(document.getElementById(id) == null) {
        var el = document.createElement('input');
        el.setAttribute('type', 'hidden');
        el.setAttribute('id', id);
        el.setAttribute('name', name);
        el.setAttribute('value', value);
        document.getElementById(formid).appendChild(el);
      } else {
        var el = document.getElementById(id);
        el.setAttribute('name', name);
        el.setAttribute('value', value);
      }
    }

    function refresh_workspace(formid, infordata) {
      var filelist = document.getElementById('filelist');
      var sheetlist = document.getElementById('sheetlist');
      var filehtml = '';
      var sheethtml = '';
      var infor = JSON.parse(infordata).infor;
      filehtml += '<div>Output filename: ' + infor.outfname + '</div>\n';
      if(infor.sheets.length > 0) {
        sheethtml += '<p class="uk-margin">Check the sheet to be calculated.</p>\n';
        for(var j = 0; j < infor.sheets.length; j++) {
          var sheetname = infor.sheets[j];
          sheethtml += '<label>';
          sheethtml += '<input class="uk-checkbox" type="checkbox" name="sheetname" value="' + infor.sheets[j] + '"">';
          sheethtml += ' <span class="uk-text-emphasis">' + infor.sheets[j] +'</span></label>\n';
        }
      }
      sheetlist.innerHTML = sheethtml;
      filelist.innerHTML = filehtml;
      document.getElementById("execbutton").disabled = false;
    }

    var bar = document.getElementById('js-progressbar');

    UIkit.upload('.js-upload', {
      url: '/interpolate/upload',
      method: 'post',
      multiple: false,
      name: 'file',
      error: function () {
        console.log('error', arguments);
      },
      loadStart: function (e) {
        bar.removeAttribute('hidden');
        bar.max = e.total;
        bar.value = e.loaded;
      },
      progress: function (e) {
        bar.max = e.total;
        bar.value = e.loaded;
      },
      loadEnd: function (e) {
        bar.max = e.total;
        bar.value = e.loaded;
        refresh_workspace('work', e.target.response);
      },
      completeAll: function () {
        setTimeout(function () {
          bar.setAttribute('hidden', 'hidden');
        }, 1000);
      }
    });
  </script>       

</div>

{% endblock %}

結果を表示するページのHTMLファイル。

report.html

{% extends "base.html" %}

{% block title %}Notice{% endblock %}

{% block head %}
{{ super() }}
{% endblock %}

{% block content %}

<div class="uk-container">

  {% if request.status=='OK' %}
  <ul class="uk-list">
    <li>Working sheet list</li>
    {% for sheetname in request.sheets %}
    <li class="uk-list-basic uk-text-bold">{{ sheetname }}</li>
    {% endfor %}
  </ul>

  {% else %}
  <p class="uk-margin">The specified file was refused processing. Please check the following.</p>
  <ul class="uk-list">
    <li>The Excel sheet is different from the one specified.</li>
    <li>The calculation sheet is not selected.</li>
  </ul>
  {% endif %}

  <h2>Crick to download following file.</h2>
  <ul class="uk-list">
    <li>
      <span class="uk-icon" uk-icon="download"></span>
      <a class="uk-link-heading uk-text-bold" href="http://{{ request.downloadurl }}">{{ request.filename }}</a>
    </li>
  </ul>

  <div class="uk-margin">
    {% for imgurl in request.imgurls %}
    <div id="graph{{ imgurl.name }}" class="uk-text-bold">
      <div class="uk-padding uk-padding-remove-horizontal uk-padding-remove-bottom">
        {{ imgurl.name }}
      </div>
      <div>
        <img src="http://{{ imgurl.url }}"
          style="box-shadow: 10px 10px 20px rgba(0, 0, 0, 0.5), -10px -10px 15px rgba(255, 255, 255, 0.3);">
      </div>
    </div>
    {% endfor %}
  </div>

  <div class="uk-margin">
    <span id="statusmsg">End line.</span>
  </div>

</div>

{% endblock %}

基本のページ設定。これを継承し各ページを作成する。

base.html

<!DOCTYPE html>
<html lang="ja">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link href="{{ url_for('static', path='/css/uikit.min.css') }}" rel="stylesheet">
  <script type=text/javascript src="{{ url_for('static', path='/js/uikit.min.js') }}"></script>
  <script type=text/javascript src="{{ url_for('static', path='/js/uikit-icons.min.js') }}"></script>
  <!-- <script type=text/javascript src="{{ url_for('static', path='/js/util.js') }}"></script> -->
  <link rel="shortcut icon" href="#">
  {% block head %}
  <title>{% block title %}{% endblock %} - Interpolate 1D</title>
  {% endblock %}
</head>

<body class="text-center">
  <header>
    <div class="uk-heading-small uk-container uk-heading-divider">
      Interpolate 1D
    </div>
  </header>
  <main>
    {% block content %}
    <!-- Contents -->
    {% endblock %}
  </main>
  <script>
    // Disable drag & drop
    window.addEventListener('dragover', function (e) {
      e.preventDefault();
    }, false);
    window.addEventListener('drop', function (e) {
      e.preventDefault();
      e.stopPropagation();
    }, false);    
  </script>
</body>

</html>

2024/11/06: 複数シートを選択するとタググループ選択となるのを回避
2024/11/05: 複数シートを選択すると計算できない場合がある問題を修正

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です