编辑Excel工作表的特定单元格 [英] editing particular cells of an Excel sheet

问题描述

我有一个Excel工作簿，我想使用R编辑/填充某些特定的单元格，而不更改任何格式.

I have an Excel workbook of which I want to edit/fill some particular cells using R, without changing any of the formatting.

到目前为止，我已经尝试过 XLConnect 软件包，看来它可以满足我的要求，但我只是没有找到解决方法.

So far I've tried XLConnect package and it seems it could do what I'm looking for, I just didn't find a way to do it.

我的直接解决问题的方法:

wb <- loadWorkbook("file1.xls")
data1 <- readWorksheet(wb, "Sheet1", header=TRUE)

## adding a value to a particular cell:
data1[11,12] <- 3.2 

## rewriting old data:
writeWorksheet(wb, data1, "Sheet1")
saveWorkbook(wb, "new_file1.xls")

但是，这样，新工作簿将丢失所有以前的格式(合并的单元格，公式等).

However, this way the new workbook loses all of the previous formatting (merged cells, formulas, etc).

是否有一种方法可以更改某些单元格中的值，而又不会丢失剩余工作表的任何格式?

Is there a way to change values in some of the cells without losing any of the formatting of the remaining sheet?

推荐答案

以下是使用R自动执行Excel的示例.

Here is an example using R to automate Excel.

library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
wb    <- xlApp[["Workbooks"]]$Open("file.1.xls")
sheet <- wb$Worksheets("Sheet1")

# change the value of a single cell
cell  <- sheet$Cells(11,12)
cell[["Value"]] <- 3.1

# change the value of a range
range <- sheet$Range("A1:F1")
range[["Value"]] <- paste("Col",1:6,sep="-")

wb$Save()                  # save the workbook
wb$SaveAS("new.file.xls")  # save as a new workbook
xlApp$Quit()               # close Excel

这篇关于编辑Excel工作表的特定单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！