Exporting column data to a specific column and row range

Question

When importing from an Excel file for the data contained in column 3 and rows 2 through 58, for example, one can use the following command:

d=Import["file.xlsx", {"Data", 1, Range[2, 58], 3}]

I would like to do the "reverse." That is, export to the file.xlsx to the same column and row range:

As a simple test example, I have tried

test=Column[Table[i^2 , {i, 0, 56}]];Export["file.xlsx", test, {"Data", 1, Range[2, 58], 3}]

But it fails, as does, incidentally,

test=Table[i^2 , {i, 0, 56}];Export["file.xlsx", test, {"Data", 1, Range[2, 58], 3}]

Any suggestions on how to do this simply?

PS: Currently, I simply Export["file1.xlsx", test] and then copy and paste into file.xlsx within Excel. I am trying to cut out the intermediate step.

Answer 1

First, run this code to register a custom export format:

ImportExport`RegisterExport["myXLSX", MyExportFormat`Exporter ]
MyExportFormat`Exporter[filename_, data_, opts___] :=
 Module[{temp, tmin},

  tmin = Min[Length@opts[[2, 1]], Length@data];
  
  temp = ConstantArray["", Max@opts[[2, 1]]];
  
  temp[[opts[[2, 1]][[;; tmin]]]] = data[[;; tmin]];
  
  temp = Append[ConstantArray["", opts[[2, 2]] - 1], #] & /@ temp;
  
  Export[filename, temp, "XLSX"]
  ]

Then export with the defined format:

Export["D:\\file.xlsx", 
  test, "myXLSX", "Data" -> {Range[2, 58], 3}]

The export option accepts two elements:

Export[..., "Data" -> {rows, col}]

rows is a list of positive integers the specifies the order of data to be written in the column col.

rows doesn't need to be sorted or equal to the length of expr:

Export["D:\\file2.xlsx",
  Range[5], "myXLSX", "Data" -> {{3, 2, 1, 4, 5, 10, 11}, 5}]