0
$\begingroup$

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.

$\endgroup$
1
$\begingroup$

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}]
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.