在kettle中,请用JS把以上Excel数据的省份和城市拆分开，增加未回款比例列

1. 首先，你需要使用"Microsoft Excel Input"步骤来读取Excel文件。

2. 接下来，你需要使用"JavaScript"步骤来拆分省份和城市。这可以通过以下代码实现：

var field_province = "province"; // 省份字段名
var field_city = "city"; // 城市字段名
var field_customer = "customer"; // 客户字段名

for (var i = 0; i < row.length; i++) {
  var customer = row[i][field_customer];
  var province = "";
  var city = "";
  if (customer.indexOf("省") > -1) {
    var index = customer.indexOf("省");
    province = customer.substring(0, index + 1);
    customer = customer.substring(index + 1);
  }
  if (customer.indexOf("市") > -1) {
    var index = customer.indexOf("市");
    city = customer.substring(0, index + 1);
    customer = customer.substring(index + 1);
  }
  row[i][field_province] = province;
  row[i][field_city] = city;
  row[i][field_customer] = customer;
}

3. 最后，你需要使用"Add Constants"步骤来添加未回款比例列，并使用"Calculator"步骤来计算未回款比例。这可以通过以下代码实现：

var field_total = "total"; // 总金额字段名
var field_received = "received"; // 已回款金额字段名
var field_unreceived = "unreceived"; // 未回款金额字段名
var field_ratio = "ratio"; // 未回款比例字段名

for (var i = 0; i < row.length; i++) {
  var total = row[i][field_total];
  var received = row[i][field_received];
  var unreceived = total - received;
  row[i][field_unreceived] = unreceived;
  row[i][field_ratio] = (unreceived / total).toFixed(2);
}

完成这些步骤后，你可以使用"Microsoft Excel Output"步骤将数据写回到Excel文件中。