用R語言處理Excel數(shù)據(jù).doc

用R語言處理Excel數(shù)據(jù)和LP說起我在琢磨R，這東西有很強(qiáng)的數(shù)據(jù)處理能力。LP在她公司的財(cái)務(wù)部門工作。我告訴她如果有什么數(shù)據(jù)處理分析類的工作可以讓我給她寫程序做，原來利用Excel手工做的一些事情，現(xiàn)在或許可以較方便地用R來處理。LP說好啊，她正在做固定資產(chǎn)清理，有一個(gè)包含幾萬條記錄的Excel表，現(xiàn)在需要分發(fā)到各個(gè)下級(jí)單位分別補(bǔ)充信息、蓋章確認(rèn)、反饋匯總。手工拆成上百個(gè)文件比較費(fèi)勁，而讓各個(gè)單位篩選填寫自己的數(shù)據(jù)又擔(dān)心有操作錯(cuò)誤出現(xiàn)遺漏。如果程序可以實(shí)現(xiàn)自動(dòng)拆分的功能就好了。我想起R的數(shù)據(jù)輸出輸入功能有xlsx等包，而數(shù)據(jù)篩選又是很簡(jiǎn)單的事情，于是馬上說可以做。我盤算著數(shù)據(jù)文件應(yīng)該是一個(gè)標(biāo)題行和幾萬個(gè)數(shù)據(jù)行組成，這樣我只要讀入xlsx文件，然后根據(jù)單位字段篩選出子集，再輸出到不同的文件中，這樣就大功搞成了。一、實(shí)際需求打開她的工作xlsx文件，發(fā)現(xiàn)情況和所想的有區(qū)別。1.文件上方有一個(gè)旋轉(zhuǎn)透視表，用于匯總數(shù)據(jù)結(jié)果。這個(gè)表在每個(gè)分拆開的文件中都要有。2.子集不是從第一行開始，而是從旋轉(zhuǎn)透視表的下方指定某行開始。數(shù)據(jù)要含表頭。3.拆分時(shí)要按照一級(jí)單位建立目錄（十幾個(gè)），并在一級(jí)單位的目錄中按照二級(jí)單位生成文件（上百個(gè)）。這樣就可以按照一級(jí)單位的目錄下發(fā)，再由一級(jí)單位下發(fā)二級(jí)單位組織填寫。大致文件的頭部截圖如下：按照第5列（E列）建立目錄，按照第6列（F列）在對(duì)應(yīng)目錄下建立文件。這比預(yù)想的復(fù)雜一點(diǎn)點(diǎn)，不過現(xiàn)實(shí)需求應(yīng)該是復(fù)雜的。我說我試試看能夠?qū)崿F(xiàn)多少。二、尋找合適的函數(shù)、測(cè)試、實(shí)現(xiàn)的過程1.安裝加載xlsx程序包。2.讀入數(shù)據(jù)使用read.xlsx()函數(shù)讀入數(shù)據(jù)。發(fā)現(xiàn)幾個(gè)問題：讀入速度較慢、讀到最后java報(bào)崩潰，另外，如果只讀入少量數(shù)據(jù)進(jìn)行測(cè)試時(shí)，發(fā)現(xiàn)讀入的漢字為亂碼，用write.xlsx()生成xlsx文件時(shí)，還是亂碼。同事建議可以調(diào)高java的heap數(shù)值，但是我想這點(diǎn)數(shù)據(jù)就要調(diào)整heap值，那更多的數(shù)據(jù)豈非不用玩了？既然只有一個(gè)sheet，我于是將xlsx文件轉(zhuǎn)存為csv文件，作為文本形式，用read.table()函數(shù)讀入。讀取時(shí)遇到報(bào)錯(cuò)，讀入失敗。經(jīng)過定位，確定某行數(shù)據(jù)的字段里有個(gè)單引號(hào)導(dǎo)致加載失敗。于是用文本替換，將所有的單引號(hào)剔掉，這樣就正確加載了。發(fā)生替換的地方不多，一共三百多處。但是我想這無論如何是修改了數(shù)據(jù)，這或許對(duì)數(shù)據(jù)會(huì)產(chǎn)生實(shí)際的影響呢。于是我搜了一下幫助，找到了read.csv()函數(shù)，用它快速完整加載所有的記錄，沒有報(bào)錯(cuò)。3.建立目錄，生成文件名根據(jù)一級(jí)單位字段生成目錄，先使用factor()函數(shù)將相關(guān)字段因子化。然后根據(jù)這個(gè)因子的水平（level()）進(jìn)行for()循環(huán)，使用dir.create()函數(shù)來建立目錄再根據(jù)一級(jí)單位選擇數(shù)據(jù)的子集（subset()），將子集的二級(jí)單位字段因子化進(jìn)行for()循環(huán)，來再次篩選子集，并生成文件。在這個(gè)過程中發(fā)現(xiàn)相關(guān)字段有不能用來建立windows文件名的字符，如“#/”等等。與LP確認(rèn)是錯(cuò)亂的單位名，并修正了數(shù)據(jù)。然后用write.xlsx()函數(shù)測(cè)試了一下，循環(huán)正常，生成的文件記錄數(shù)總和與原文件相同，這樣就確認(rèn)了整體架構(gòu)是可靠的。4.生成文件現(xiàn)在進(jìn)入最難度的部分：如何按照LP的要求生成每個(gè)xlsx。write.xlsx()能否實(shí)現(xiàn)呢？仔細(xì)看了幫助文檔，沒找到辦法。有函數(shù)能實(shí)現(xiàn)對(duì)單元格字體、顏色等等的控制，但是沒有能整塊輸出旋轉(zhuǎn)透視表的功能。由于需要的旋轉(zhuǎn)透視表并不復(fù)雜，所以也考慮用Excel公式來實(shí)現(xiàn)相同的功能，這樣我也能通過拼接數(shù)據(jù)框來變通，但沒有找到相關(guān)的函數(shù)，輸出=A1 A2之類的格式，如果這樣做，最終的結(jié)果都變成了字符串而不是公式。read.xlsx()倒是有讀入公式的選項(xiàng)，但是write.xlsx沒有輸出公式的選項(xiàng)。忽然我發(fā)現(xiàn)有一個(gè)addDataFrame()函數(shù)，能夠在一個(gè)Excel對(duì)象的某個(gè)sheet上的某個(gè)格子為左上角來輸出數(shù)據(jù)框。我想能不能先做一個(gè)只包含旋轉(zhuǎn)透視表（或者公式）以及表頭的xlsx文件模板，然后通過將數(shù)據(jù)框追加到sheet上來實(shí)現(xiàn)呢？經(jīng)過測(cè)試，這是可行的。唯一的缺點(diǎn)是打開后，透視表或公式的數(shù)據(jù)并不是根據(jù)下面的數(shù)據(jù)自動(dòng)計(jì)算的，而是原來的樣子，需要刷新數(shù)據(jù)才能變化。這點(diǎn)對(duì)我的開發(fā)目的不是問題，因?yàn)楸砀窬褪且鱾€(gè)單位去填寫，填寫完了他們本來就需要手工操作透視表的刷新。那一切就迎刃而解了。三、最終代碼library(xlsx)rm(list=ls()mydata - read.csv(data.csv,head=FALSE,stringsAsFactors=FALSE,sep=,na.strings= ,colClasses=c(character)nrow(mydata)status - factor(mydata$V5)dir.create(result)setwd( result )for ( i in levels(status) )dir.create(i) status - factor(mydata$V6)for ( i in levels(status) ) subdata - subset(mydata, V6=i) print (nrow(subdata) ) dirnm - subdata$V51 wb - loadWorkbook(model.xls) sheets - getSheets(wb) sheet - sheets1 addDataFrame(subdata,sheet,s=FALSE, s=FALSE,startRow=16,startCol=1) levels(dirnm) setwd(dirnm) saveWorkbook(wb, paste(i,.xls,sep=) setwd(.)總結(jié)：（個(gè)人通過解決問題所產(chǎn)生的總結(jié)，一孔之見。有些我認(rèn)為“不能”的事情，未進(jìn)行特別的深究；有些問題的解決方案，可能有更好更優(yōu)雅的解決辦法。請(qǐng)大家指正）1.讀入較多數(shù)據(jù)時(shí)，xlsx文件可能有內(nèi)存問題，另外還碰到漢字問題。這時(shí)轉(zhuǎn)存為csv文件是較好的解決辦法。如果使用read.table()讀入，在碰到一些特殊字符時(shí)可能會(huì)有問題，用read.csv()讀入就沒有這些問題了。但是如果有很多文件需要處理、自動(dòng)處理，那還是需要解決xlsx與csv的轉(zhuǎn)化問題。2.未找到如何輸出Excel公式或者透視表（）。3.生成的新Excel里面的旋轉(zhuǎn)透視表和公式，還是模板里面的值，需要人工