落园 » 从R里面底层操纵Excel/xlsx(自动化报告福音)|专注经济视角下的互联网

从R里面底层操纵Excel/xlsx(自动化报告福音)

好吧,我在eBay折腾的最多的就是生成自动化报告时候各种软件之间的相互调用,什么R啊,SAS啊,Teradata啊,Excel啊,Python啊,反正基本都有机会相互调用一下。每到此时我就深深感慨选择一个library丰富的工具是多么的重要!You could hardly expect what you colleagues are handy with!(P.s. 不要跟我提VBA这种逆天存在的东西。有哪个时间研究它你学点啥别的不好...)

今天忍无可忍+心情大好的折腾了一下R和excel。这个不是简单的从R里面读写excel数据,而是真心用R去操纵excel里面的单元格(cell),除了读写数据之外还要定义样式什么的。excel作为一个奇葩的软件,you may never expect where people would paste data to! 然后他们再自定义一堆样式(我恨这种点点鼠标就能改的东西,你丫又不是Photoshop...)。

但是没办法,人家定义好的“高端洋气”的报表姿态你不能轻易动啊。只能乖乖的往里面paste数据。这件事虽说一次两次手动也就罢了,三五次真的是要疯掉的。anyway,万事总有解决的途径...

很久以前从Yixuan 的博客上得知有xlsx这么个包,当时只记得这东西可以读写xlsx...直到后面折腾了一下才知道这货底层居然调用的是java的xlsx API,也就是说不用写Java也可以操作xlsx了,yeah!

为了生成excel格式的自动化报告(不要问我为啥不用knitr,不用***,说起来都是泪呀!),我主要需要解决的就是:

  • 读取原有xlsx文件,保持格式、附加新格式。
  • 在相应的位置粘进去新的数据。(当然如果只有这么一个需求可以通过ODBC来做...)

第一个倒是满简单的,就是较之yixuan代码里面的createWorkbook(),改成loadWorkbook()就可以了。然后就是找到相应的sheet,这个也满简单的,一行getSheets搞定。

然后第二步建议不要去操作cell(太没效率了),直接操作cellblock。CellBlock()可以用来定义一个新的CellBlock,然后灵活运用CB.setBorder()和CB.setColData()就可以先增加边框、然后一列列填充数据。这里使用按列填充数据主要是因为R里面的Data Frame是一列一个数据格式的,一下子把一块儿都paste到excel的cellblock里面的话,会报错...BTW为了定义边框的样式,需要用到Border()。类似的还可以定义Fill和Font这些。

同上,最好不要直接用addDataFrame()来直接贴数据...格式不能覆盖。如果是要在一个新的sheet上贴数据,那么就write.xlsx(sheetName="newsheet",append=T)好了。不需要通过上述底层的API折腾了。

最后还有一个比较有用的函数,autoSizeColumn()可以用来自动调整列宽。全鼓捣完之后saveWorkbook()保存就可以啦。

最后的最后,一个珍贵的建议——都在R里面把数据整理好再去想输出到excel里面(什么reshape2啊,data.table啊,plyr啊,该上的一起上啊!),千万别手贱在excel里面改一点点小东西...每一次都手动改一下下你的时间就被白白浪费了好几分钟!珍爱生命,远离excel...

附上一段我最后搞定自动化报告的代码:

 



Comments

Leave a Reply

Your email address will not be published. Required fields are marked *