R/zslSaveToExcel.R

In baixueyuan/zsl: Download and manipulate discount rate of bond from China Clear.

#' Save zsl Result to the Excel File
#'
#' Save the data to Excel file which is generated by \code{zslTwoDayDiff} and
#' other functions giving the comparison result.
#'
#' For the convenience of viewing the result, the function changed somewhere to
#' Chinese, including the "mark" column and the column names.
#'
#' The funcion uses \code{XLConnect} package to manipulate the Excel file. For
#' the format, the style action is set to "NAME_PREFIX" and the style is already
#' adjusted well in the "template.xlsx".
#'
#' Besides, using param \code{assign} can assign the workbook object to the
#' Global environment. This is uesful for generating multiple sheets Excel file.
#'
#' The function \code{zslSaveToExcelP} saves the data returned by
#' \code{zslPeriodDiff}.
#'
#' @param data the data to be saved to Excel file.
#'
#' @param output.file the file path of the ouput, if missed, it is set to
#'   "zsl.xlsx" in the working directory.
#' @param assign logical, default is FALSE, if TRUE, the workbook object
#'   generated by \code{XLConnect} package will be assigned to the Global
#'   environment, named "wb".
#' @param wb the \code{workbook} object, used in \code{zslSaveToExcel} to add
#'   worksheet, it can be missed and generate a new Excel file with template.
#'
#' @return No obvious return, the data will be saved to Excel file.
#'
#' @name SaveToExcel
NULL

#' @rdname SaveToExcel
#' @export
zslSaveToExcel <- function(data, output.file='zsl.xlsx', assign=FALSE) {

  # 更改备注列，NEW改为“新增”，STOP改为“停止”
  data$mark <- stringr::str_replace(data$mark, 'NEW', '\u65b0\u589e')
  data$mark <- stringr::str_replace(data$mark, 'STOP', '\u505c\u6b62')

  # 将列名称改为中文列名称
  # “债券代码”、“债券简称”、“前折算率”、“新折算率”、“变动”、“备注”
  colnames(data) <- c('\u503a\u5238\u4ee3\u7801',
                     '\u503a\u5238\u7b80\u79f0',
                     '\u524d\u6298\u7b97\u7387',
                     '\u65b0\u6298\u7b97\u7387',
                     '\u53d8\u52a8',
                     '\u5907\u6ce8')

  # 如果增加了债券信息，则增加列名称
  # 此时的7到13列为“剩余期限”、“债项”、“主体”、“评级变动”、“债券类型”、“行业”
  # 和“城投”
  if (length(data)==13) {
    colnames(data)[7:13] <- c('\u5269\u4f59\u671f\u9650',
                              '\u503a\u9879',
                              '\u4e3b\u4f53',
                              '\u8bc4\u7ea7\u53d8\u52a8',
                              '\u503a\u5238\u7c7b\u578b',
                              '\u884c\u4e1a',
                              '\u57ce\u6295')
  }

  # 读取模板文件
  tmpl <- system.file('template', 'template.xlsx', package='zsl')
  wb <- XLConnect::loadWorkbook(tmpl)
  XLConnect::renameSheet(wb, 'Sheet1', 'zsl')

  # 设置文档的样式模式为命名前缀模式，并将前缀设为“zsl”
  # 相关的样式已经在模板文件中设置好了
  XLConnect::setStyleAction(wb, XLC$"STYLE_ACTION.NAME_PREFIX")
  XLConnect::setStyleNamePrefix(wb, 'zsl')

  # 写入数据
  XLConnect::createName(wb, 'data', 'zsl!$A$1', overwrite=TRUE)
  XLConnect::writeNamedRegion(wb, data, 'data')

  # 为变动较大的做标记，按照0.05、0.1和0.2分三段标记，负数用红色，正数用绿色
  small <- XLConnect::getCellStyle(wb, 'zsl.Numeric.Small')
  less <- XLConnect::getCellStyle(wb, 'zsl.Numeric.Less')
  least <- XLConnect::getCellStyle(wb, 'zsl.Numeric.Least')
  big <- XLConnect::getCellStyle(wb, 'zsl.Numeric.Big')
  bigger <- XLConnect::getCellStyle(wb, 'zsl.Numeric.Bigger')
  biggest <- XLConnect::getCellStyle(wb, 'zsl.Numeric.Biggest')
  ind <- which(data[, 5] > 0.05 & data[, 5] <= 0.1 & data[, 6]=='')
  if (length(ind) > 0) {
    for (i in ind)
      XLConnect::setCellStyle(wb, sheet='zsl', row=i+1, col=5, cellstyle=big)
  }
  ind <- which(data[, 5] > 0.1 & data[, 5] <= 0.2 & data[, 6]=='')
  if (length(ind) > 0) {
    for (i in ind)
      XLConnect::setCellStyle(wb, sheet='zsl', row=i+1, col=5, cellstyle=bigger)
  }
  ind <- which(data[, 5] > 0.2 & data[, 6]=='')
  if (length(ind) > 0) {
    for (i in ind)
      XLConnect::setCellStyle(wb, sheet='zsl', row=i+1, col=5, cellstyle=biggest)
  }
  ind <- which(data[, 5] < -0.05 & data[, 5] >= -0.1 & data[, 6]=='')
  if (length(ind) > 0) {
    for (i in ind)
      XLConnect::setCellStyle(wb, sheet='zsl', row=i+1, col=5, cellstyle=small)
  }
  ind <- which(data[, 5] < -0.1 & data[, 5] >= -0.2 & data[, 6]=='')
  if (length(ind) > 0) {
    for (i in ind)
      XLConnect::setCellStyle(wb, sheet='zsl', row=i+1, col=5, cellstyle=less)
  }
  ind <- which(data[, 5] < -0.2 & data[, 6]=='')
  if (length(ind) > 0) {
    for (i in ind)
      XLConnect::setCellStyle(wb, sheet='zsl', row=i+1, col=5, cellstyle=least)
  }

  # 设置首行冻结、筛选、自动列宽
  XLConnect::createFreezePane(wb, 'zsl', colSplit=1, rowSplit=2)
  XLConnect::setAutoFilter(wb, 'zsl', aref('A1', dim(data)))
  XLConnect::setColumnWidth(wb, sheet='zsl', column=c(1:length(data)), width=-1)


  # 是否将Workbook对象存放在Global环境供后续使用，否则保存文件
  if (assign) {
    assign('wb', wb, envir=.GlobalEnv)
  } else {
    # 将Sheet名称改为“最新折算率”
    XLConnect::renameSheet(wb, 'zsl', '\u6700\u65b0\u6298\u7b97\u7387')
    # 保存文件
    XLConnect::saveWorkbook(wb, file=output.file)
  }
}