java调用python脚本，生成excel

java:

1 /**
 2      * 使用python创建excel并且输出
 3      * @throws Exception
 4      */
 5     public void pyExportExcel() throws Exception{
 6         //查询数据
 7         Map data = new HashMap<>();
 8         data.put("patientList",super.selectAll());
 9         data=PyHelper.executeFile("exportTestPatientInf.py",JSON.toJSONString(data, SerializerFeature.WriteMapNullValue));
10         System.out.println(JSON.toJSONString(data));
11     }

import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.ResourceUtils;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.*;

/**
 * 执行python文件
 * @author luwl
 */
public class PyHelper {
    /**
     * 默认本地已经配置了python的环境变量(否则指向python.exe文件)
     */
    public static String pyPath="python";

    /**
     * 执行python文件
     * @param pyFile .py文件地址 在resource下(resource/test/test.py->test.py)
     * @param data json格式的数据
     * @return status->状态(500 调用失败,0 调用成功)  values->python文件执行给的返回值
     */
    public static Map executeFile(String pyFile, String data){
        //返回值
        Map map  = new HashMap();
        //python文件执行时的返回数据(print打印)
        List lines=new ArrayList();
        BufferedReader in = null;
        Process pr = null;
        try {
            //python执行路径,py文件
            File file = ResourceUtils.getFile("classpath:pySource/"+pyFile);
            //判断文件是否存在
            if(!file.exists()&&!file.isFile())return null;
            //参数
            String[] args = null;
            if(data==null){
                args=new String[]{pyPath, file.getAbsolutePath()};
            }else{
                args=new String[]{pyPath, file.getAbsolutePath(), StringUtil.toBase64(data)};
            }
            //黑窗体执行
            pr=Runtime.getRuntime().exec(args);
            //文件流输出
            in = new BufferedReader(new InputStreamReader(
                pr.getInputStream(),"gb2312"));
            String line;
            while ((line = in.readLine()) != null) {
                lines.add(line);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                if(in!=null){
                    in.close();
                }
                if(pr!=null) {
                    map.put("status", pr.waitFor());
                }else{
                    map.put("status","500");
                }
            } catch (IOException e) {
                e.printStackTrace();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            map.put("values",lines);
        }
        return map;
    }
}

1 /**
2      * BASE64加密
3      * @param json
4      * @return
5      */
6     public static String toBase64(String json){
7         byte[] bytes = json.getBytes();
8         return Base64.getEncoder().encodeToString(bytes);
9     }

python:

coding=utf-8
import base64
import json

import xlsxwriter
from xlsxwriter.workbook import Workbook
import xlsxwriter
import sys
import traceback
from pandas import pandas as pd
import numpy as np

from utils import excelUtil

def exportTable(data):
    # 加载JSON数据
    patientList = json.loads(data)['patientList'];
    # 创建excel文件,并且命名问patient.xlsx
    fileName="patient.xlsx";
    # workbook=xlsxwriter.Workbook("./icuisapi-web/src/main/resources/pySource/patient.xlsx");
    workbook = xlsxwriter.Workbook(fileName)
    # 创建第一个sheet页码
    workSheet = workbook.add_worksheet('患者信息一览表');
    # 表头
    headStr = ['ID', '入ICU时间', '出院时间', '转科时间', '诊断情况', '转归', '呼吸机使用', '高流量', 'PICCO', 'ECMO', 'CRRT'];
    # 创建表头
    workSheet.write_row(1, 0, headStr, excelUtil.header_style(workbook))
    # 设置第一行表头
    workSheet.write(0, 0, '患者信息一览表');
    # 合并单元格
    workSheet.merge_range(0, 0, 0, len(headStr) - 1, "患者信息一览表", excelUtil.title_style(workbook))
    # 表格样式
    # 设置表格框黑色
    workSheet.conditional_format(0, 0, len(patientList) + 1, len(headStr) - 1,
                                 {'type': 'blanks', 'format': excelUtil.border_format(workbook)})
    workSheet.conditional_format(0, 0, len(patientList) + 1, len(headStr) - 1,
                                 {'type': 'no_blanks', 'format': excelUtil.border_format(workbook)})
    # 设置从0-最后一列的列宽位15
    workSheet.set_column(0, len(headStr) - 1, 20)
    # 设置第一行行高40
    workSheet.set_row(0, 40)
    # 设置第二行行高35
    workSheet.set_row(1, 35)
    # 循环数值
    for i in range(0, len(patientList)):
        patient = patientList[i];
        workSheet.write(i + 2, 0, excelUtil.noneFormat(patient['id']), excelUtil.str_style(workbook));
        workSheet.write(i + 2, 1, excelUtil.noneFormat(patient['icuTimeIn']), excelUtil.date_format(workbook));
        workSheet.write(i + 2, 2, excelUtil.noneFormat(patient['dischargeTime']), excelUtil.date_format(workbook));
        workSheet.write(i + 2, 3, excelUtil.noneFormat(patient['collegeTime']), excelUtil.date_format(workbook));
        workSheet.write(i + 2, 4, excelUtil.noneFormat(patient['diagnosis']), excelUtil.str_style(workbook));
        workSheet.write(i + 2, 5, excelUtil.noneFormat(patient['outcome']), excelUtil.str_style(workbook));
        workSheet.write(i + 2, 6, excelUtil.noneFormat(patient['hxjsy']), excelUtil.str_style(workbook));
        workSheet.write(i + 2, 7, excelUtil.noneFormat(patient['gll']), excelUtil.str_style(workbook));
        workSheet.write(i + 2, 8, patient['picco'], excelUtil.number_format(workbook));
        workSheet.write(i + 2, 9, excelUtil.noneFormat(patient['ecmo']), excelUtil.str_style(workbook));
        workSheet.write(i + 2, 10, excelUtil.noneFormat(patient['crrt']), excelUtil.str_style(workbook));
        # 设置每一行的行高为30
        workSheet.set_row(i + 2, 30)
    workbook.close()
    pass

访问入口
if __name__ == '__main__':
    # sys.argv[1] 获取传递的json参数
    try:
        #正式
        # Base64解码
        # data=base64.b64decode(sys.argv[1]).decode()
        # export(data)
        #测试
        testVal = "eyJwYXRpZW50TGlzdCI6W3siY29sbGVnZVRpbWUiOiIyMDIwLTA3LTA1IDE1OjQ3OjQ2IiwiY3JydCI6bnVsbCwiZGlhZ25vc2lzIjoiQUJDIiwiZGlzY2hhcmdlVGltZSI6IjIwMjAtMDctMDEgMTU6NDc6MzQiLCJlY21vIjpudWxsLCJnbGwiOm51bGwsImh4anN5IjpudWxsLCJpY3VUaW1lSW4iOiIyMDIwLTAyLTA0IDAyOjAwOjAxIiwiaWQiOiJlYmM3OTQ5MWI5ZDkxMWVhYjc3ZWI0MmU5OWU0NTA0YyIsIm91dGNvbWUiOiLkuJPnp5EiLCJwaWNjbyI6OTkuMH0seyJjb2xsZWdlVGltZSI6IjIwMjAtMDctMDUgMTU6NDc6NDYiLCJjcnJ0IjpudWxsLCJkaWFnbm9zaXMiOiJBRUMiLCJkaXNjaGFyZ2VUaW1lIjoiMjAyMC0wNy0wMSAxNTo0NzozNCIsImVjbW8iOm51bGwsImdsbCI6bnVsbCwiaHhqc3kiOm51bGwsImljdVRpbWVJbiI6IjIwMjAtMDItMDQgMDI6MDA6MDEiLCJpZCI6ImViYzdjZDBkYjlkOTExZWFiNzdlYjQyZTk5ZTQ1MDRjIiwib3V0Y29tZSI6IuS4k+enkSIsInBpY2NvIjo4OC4wfSx7ImNvbGxlZ2VUaW1lIjoiMjAyMC0wNy0wNSAxNTo0Nzo0NiIsImNycnQiOm51bGwsImRpYWdub3NpcyI6IkFCQyIsImRpc2NoYXJnZVRpbWUiOiIyMDIwLTA3LTAxIDE1OjQ3OjM0IiwiZWNtbyI6bnVsbCwiZ2xsIjpudWxsLCJoeGpzeSI6bnVsbCwiaWN1VGltZUluIjoiMjAyMC0wMi0wNCAwMjowMDowMSIsImlkIjoiZWJjN2NkZDhiOWQ5MTFlYWI3N2ViNDJlOTllNDUwNGMiLCJvdXRjb21lIjoi6L2s5Lit5b+D5Yy76ZmiIiwicGljY28iOjQ1LjB9LHsiY29sbGVnZVRpbWUiOiIyMDIwLTA3LTA1IDE1OjQ3OjQ2IiwiY3JydCI6bnVsbCwiZGlhZ25vc2lzIjoiQUVDIiwiZGlzY2hhcmdlVGltZSI6IjIwMjAtMDctMDEgMTU6NDc6MzQiLCJlY21vIjpudWxsLCJnbGwiOm51bGwsImh4anN5IjpudWxsLCJpY3VUaW1lSW4iOiIyMDIwLTAyLTA0IDAyOjAwOjAxIiwiaWQiOiJlYmM3Y2UzOWI5ZDkxMWVhYjc3ZWI0MmU5OWU0NTA0YyIsIm91dGNvbWUiOiLmrbvkuqEiLCJwaWNjbyI6NTIuMH0seyJjb2xsZWdlVGltZSI6IjIwMjAtMDctMDUgMTU6NDc6NDYiLCJjcnJ0IjpudWxsLCJkaWFnbm9zaXMiOiJBR0MiLCJkaXNjaGFyZ2VUaW1lIjoiMjAyMC0wNy0wMSAxNTo0NzozNCIsImVjbW8iOm51bGwsImdsbCI6bnVsbCwiaHhqc3kiOm51bGwsImljdVRpbWVJbiI6IjIwMjAtMDItMDQgMDI6MDA6MDEiLCJpZCI6ImViYzdjZTk2YjlkOTExZWFiNzdlYjQyZTk5ZTQ1MDRjIiwib3V0Y29tZSI6Iuatu+S6oSIsInBpY2NvIjoxNC4wfSx7ImNvbGxlZ2VUaW1lIjoiMjAyMC0wNy0wNSAxNTo0Nzo0NiIsImNycnQiOm51bGwsImRpYWdub3NpcyI6IkFFQyIsImRpc2NoYXJnZVRpbWUiOiIyMDIwLTA3LTAxIDE1OjQ3OjM0IiwiZWNtbyI6bnVsbCwiZ2xsIjpudWxsLCJoeGpzeSI6bnVsbCwiaWN1VGltZUluIjoiMjAyMC0wMi0wNCAwMjowMDowMSIsImlkIjoiZWJjN2NlZWJiOWQ5MTFlYWI3N2ViNDJlOTllNDUwNGMiLCJvdXRjb21lIjoi6L2s5Lit5b+D5Yy76ZmiIiwicGljY28iOjY3LjB9LHsiY29sbGVnZVRpbWUiOiIyMDIwLTA3LTA1IDE1OjQ3OjQ2IiwiY3JydCI6bnVsbCwiZGlhZ25vc2lzIjoiQUJDIiwiZGlzY2hhcmdlVGltZSI6IjIwMjAtMDctMDEgMTU6NDc6MzQiLCJlY21vIjpudWxsLCJnbGwiOm51bGwsImh4anN5IjpudWxsLCJpY3VUaW1lSW4iOiIyMDIwLTAyLTA0IDAyOjAwOjAxIiwiaWQiOiJlYmM3Y2YzYmI5ZDkxMWVhYjc3ZWI0MmU5OWU0NTA0YyIsIm91dGNvbWUiOiLovazkuK3lv4PljLvpmaIiLCJwaWNjbyI6MjYuMH0seyJjb2xsZWdlVGltZSI6IjIwMjAtMDctMDUgMTU6NDc6NDYiLCJjcnJ0IjpudWxsLCJkaWFnbm9zaXMiOiJBQkMiLCJkaXNjaGFyZ2VUaW1lIjoiMjAyMC0wNy0wMSAxNTo0NzozNCIsImVjbW8iOm51bGwsImdsbCI6bnVsbCwiaHhqc3kiOm51bGwsImljdVRpbWVJbiI6IjIwMjAtMDItMDQgMDI6MDA6MDEiLCJpZCI6ImViYzdjZjhjYjlkOTExZWFiNzdlYjQyZTk5ZTQ1MDRjIiwib3V0Y29tZSI6Iui9rOS4reW/g+WMu+mZoiIsInBpY2NvIjo4MS4wfV19";
        data = base64.b64decode(testVal).decode()
        exportTable(data)
    except:
        print(traceback.format_exc())
    pass

coding=utf-8
from datetime import time

import xlsxwriter

将单元格数字设置为财务格式
def money_format(workbook):
    return workbook.add_format({
        'num_format': '$#,##0',
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
        'font_size': 10
    })  # 数字格式

单元格数值类型
def number_format(workbook):
    return workbook.add_format({
        'num_format': '#.##0',
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
        'font_size': 10
    })  # 数字格式

添加一个日期格式
def date_format(workbook):
    return workbook.add_format({
        'num_format': 'yyyy-mm-dd hh:mm:ss',
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
        'font_size': 10
    })

普通文本样式
def str_style(workbook):
    return workbook.add_format({
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
        'font_size': 10
    })

边框样式
def border_format(workbook):
    return workbook.add_format({
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
        'border': 1
    })

title标题格式
def title_style(workbook):
    return workbook.add_format({
        'bold': True,
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
        'font_size': 16  # 字体大小 16
        # 'fg_color': '#D7E4BC',  # 颜色填充
    })

表头样式
def header_style(workbook):
    return workbook.add_format({
        'bold': True,
        'border': 6,
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
        'font_size': 14
    })

判断null值
def noneFormat(val):
    if val == None:
        return "无"
    elif len(val) == 0:
        return "无"
    else:
        return val

java传递json给python的时候会有各种问题，所以我当前使用base64加密（转码以下） 在python中在重新解析

Original: https://www.cnblogs.com/Sora-L/p/13213063.html
Author: 雨梦大木
Title: java调用python脚本，生成excel