pasting a value to xlsx using shell

Question

I have a template file in xlsx format and I want to paste a dynamic value in one particular cell i.e based on the flow of program the value in that cell will change which in turn changes conditions in xlsx file for a different process.

I have tried codes like

awk -v value=$value -v row=$row -v col=$col 'BEGIN{FS=OFS="@"} NR==row {$col=value}1' file.csv

but the issue is I cant use this code for xlsx file format. is there any way to do this for xlsx file format, since it's a template file I need to retain xlsx file format.

Good that you have posted code which you have tried, always try to post sample of input and expected output for people to have complete picture of question, kindly do so now. — RavinderSingh13, Dec 17 '18 at 09:31
if you want to work in `xlsx` format you will have to go for a more powerful tool than `awk` as `xslx` in opposition to csv are not plain text files... Many programming languages have modules to manipulate those kind of files (java, python,...) — Allan, Dec 17 '18 at 09:43

Ed Morton · Accepted Answer · 2018-12-17T16:34:56.683

When I have to extract values from an Excel workbook on my Windows PC I install cygwin and then write a small shell script that does:

cygstart "/path/to/xls2csv.vbs" 'C:/cygwin64/path/to/bookName.xlsx'
awk 'whatever' '/path/to/bookName/sheetName.csv'

and the work of extracting every sheet from the workbook as a separate CSV named based on the sheet name suffixed with ".csv" under a common directory named after the workbook is done by this visual basic script:

$ cat xls2csv.vbs
csv_format = 6

Dim strFilename
Dim objFSO
Set objFSO = CreateObject("scripting.filesystemobject")
strFilename = objFSO.GetAbsolutePathName(WScript.Arguments(0))
If objFSO.fileexists(strFilename) Then
  Call Writefile(strFilename)
Else
  wscript.echo "no such file!"
  wscript.echo strFilename
End If
Set objFSO = Nothing

Sub Writefile(ByVal strFilename)
Dim objExcel
Dim objWB
Dim objws

Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(strFilename)

For Each objws In objWB.Sheets
  objws.Copy
  objExcel.ActiveWorkbook.SaveAs objWB.Path & "\" & objws.Name & ".csv", csv_format
  objExcel.ActiveWorkbook.Close False
Next

objWB.Close False
objExcel.Quit
Set objExcel = Nothing
End Sub

That command would fail given blanks in file or directory names so we need to replace those with, say, underscores. In reality I usually copy the Xls file to a temp directory and give it a temp name before running the above on it so I can run the above on it without affecting the original file and without having to care about the path to the original file. It requires an absolute path to the input Excel workbook.

You might need to throw a wait and/or sleep in before the awk command to ensure the VB script is done before the awk command runs. My not shown shell code is kinda convoluted testing for the VB script creating then removing tmp files to ensure the VB script is done and looping trying and then killing Excel if it doesn't start or hangs before calling awk - I wrote it a long time ago, it's a mess, and I doubt if it's really necessary or a good approach which is why I'm not including it here.

To get those values back INTO a multi-sheet workbook you'd have to open any updated/generated CSV with Excel (or copy/paste). There's probably some other VB script could be written to import the CSVs for you just like I export them above but I've never needed that functionality so idk what that'd look like.

I don't know if you need that though - if your awk script writes CSV then you can just double click on the output .csv and Excel will happily open and display it just like it would any .xls or .xlsx Excel file.

So, to do what you want, assuming your original content is in "Sheet1" of single-sheet Excel workbook "MyStuff.xlsx" you'd do this from cygwin:

cygstart "/path/to/xls2csv.vbs" 'C:/cygwin64/path/to/MyStuff.xlsx'
wait; sleep 10     # or similar
awk -v value="$value" -v row="$row" -v col="$col" 'BEGIN{FS=OFS=","} NR==row {$col=value}1' '/path/to/MyStuff/Sheet1.csv' > "/tmp/tmp$$" &&
mv "/tmp/tmp$$" '/path/to/MyStuff/Sheet1.csv'

and then in Windows just double-click on /path/to/MyStuff/Sheet1.csv to open it in Excel (you may need to associate the .csv file suffix with Excel the first time you do that).

Note that the above will only handle simple CSVs, see What's the most robust way to efficiently parse CSV using awk? for how to robustly handle CSVs with awk in general.

pasting a value to xlsx using shell

1 Answers1