Excelのセル色の変更 ～Pythonを操るFCMPプロシジャ～

*---セル色を変更したいファイル保存場所(パス記入);

%let _inpath=.;

*---セル色変更後ファイル出力場所(パス記入);

%let _outpath=.;

*---セル色を変更したいファイル名(拡張子ごと記入);

%let _infile=Before.xlsx;

*---セル色変更後ファイル名(拡張子ごと記入);

%let _outfile=After.xlsx;

 

filename _inpath "&_inpath.";

filename _outpath "&_outpath.";

data _null_;

  inpath=tranwrd(pathname("_inpath"),"\","/")||"/&_infile.";

  call symputx("infile",unicodec(inpath,'utf8'));

  outpath=tranwrd(pathname("_outpath"),"\","/")||"/&_outfile.";

  call symputx("outfile",unicodec(outpath,'utf8'));

run;

 

proc fcmp;

declare object py(python);

submit into py;

def PyProduct(infile,outfile):

    """Output:"""

    import shutil

    shutil.copy(infile,outfile)

endsubmit;

rc=py.publish();

rc=py.call('PyProduct',"&infile.","&outfile.");

run;

proc delete data=_all_; run;

proc fcmp outlib=work.fcmp.pyfuncs;

function MyFunc(arg0 $,arg1 $,arg2 $,arg3 $) $50;

length Result $50;

declare object py(python);

submit into py;

def Product(sheet,range,color,outfile):

    """Output: MyKey"""

    from openpyxl import load_workbook

    import openpyxl

    wb=load_workbook(outfile)

    ws=wb[sheet] #ｼｰﾄ名

    ws[range].fill=openpyxl.styles.PatternFill(patternType='solid',fgColor=color)

    wb.save(outfile)

    return range

endsubmit;

rc=py.publish();

rc=py.call('Product',arg0,arg1,arg2,arg3);

Result=py.results['MyKey'];

return(Result);

endfunc;

run;

options cmplib=work.fcmp;

%macro var(_sheet=,_range=,_color=);

data _null_;

  x=MyFunc(unicodec(&_sheet.,'utf8'),&_range.,&_color.,"&outfile.");

  put x=;

run;

%mend var;

%var(_sheet="Sheet 1",_range="B3",_color="ccffff");

%var(_sheet="Sheet 1",_range="C5",_color="ff99cc");