java调用python脚本，生成excel
java:
1 /** 2 * 使用python创建excel并且输出 3 * @throws Exception 4 */ 5 public void pyExportExcel() throws Exception{ 6 //查询数据 7 Map<String,Object> 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<String,Object> executeFile(String pyFile, String data){ //返回值 Map<String,Object> map = new HashMap<String,Object>(); //python文件执行时的返回数据(print打印) List<String> lines=new ArrayList<String>(); 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中在重新解析