今天接手一个重复性工作， 需要手工把产品运营们在excel里写的活动规则， 插入数据库表中。为了减少出错， 提高效率。 再加上最近刚刚学R语言， 就用R练练手， 自动生成mysql的sql语句。 一次性提交。
刚才就是判断一个值是否是整数折腾了一小会儿。后来发现R判断这个很简单。
就是as.integer(money) != as.numeric(money)
require("XLConnect")
library("WriteXLS")
wb <-loadWorkbook("activity.xls",create=FALSE)
df1 <- readWorksheet(wb,sheet="work")
for(i in 2:length(df1$activityID) )
{
activityID <- df1$activityID[i]
money = df1$money[i];
if(as.integer(money) != as.numeric(money))
{
rules <- sprintf("'rules/taxiRebateBySameCashAmount/rebateRules%3.2fyuan.xls'", as.numeric(money))
}else
{
rules <- sprintf("'rules/taxiRebateBySameCashAmount/rebateRules%dyuan.xls'", as.integer(money))
}
df1$sql[i]=sprintf("INSERT INTO t_activity_rule (activity_id, rule_type, rule_location) SELECT %d, 1, %s FROM dual WHERE not exists (select * from t_activity_rule where t_activity_rule.activity_id = %d and t_activity_rule.rule_type=1); update t_activity_rule set rule_location = %s where t_activity_rule.activity_id = %d and t_activity_rule.rule_type=1;"
, activityID, rules, activityID,rules ,activityID)
}
WriteXLS(df1,ExcelFileName = "activity.xls", SheetNames = "work")