Writing to Excel worksheet using Bash

Question

Is it possible to write to an excel sheet (any type) from a bash script?

I am looking for something like that:

sed -e :a -e '$!N; s/\n/ /; ta' file.c > #( first coloumn ,second row of the spread sheet ) echo "$cdvar" > #( second coloumn ,third row of the spread sheet ) 

Thanks for your answers and suggestions.

Answer 1

You can write excel using bash , perl , python , .. I think that each program language has its own solutions.

bash

You can use join or awk , and I think there are other solutions.

join to

If you want to join with files with the same column, see these posts: Bash join team and join in bash, as in SAS

Awk

You can write csv, but you could rename in xls and then using excel, gnumeric or other programs, this is recognized as xls.

 ls -R -ltr / | head -50 | awk '{if ($5 >0) print $5,$9}' OFS="," > sample.xls 

when you modify xls with excel, gnumeric or other programs and save in xls, you cannot read bash. For @Geekasaur to recommend perl or python solutions.

Perl

You can write xls in perl, follow sample :

 #!/usr/bin/perl use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new("test.xls"); my $worksheet = $workbook->add_worksheet(); open(FH,"<file") or die "Cannot open file: $!\n"; my ($x,$y) = (0,0); while (<FH>){ chomp; @list = split /\s+/,$_; foreach my $c (@list){ $worksheet->write($x, $y++, $c); } $x++;$y=0; } close(FH); $workbook->close(); 

And then you can change xls using the Spreadsheet::ParseExcel : look package. How can I change an existing Excel workbook using Perl? and "This link is broken and IBM reported" an example of reading and writing

python

You can write real xls in python, follow sample :

 #!/usr/local/bin/python # Tool to convert CSV files (with configurable delimiter and text wrap # character) to Excel spreadsheets. import string import sys import getopt import re import os import os.path import csv from pyExcelerator import * def usage(): """ Display the usage """ print "Usage:" + sys.argv[0] + " [OPTIONS] csvfile" print "OPTIONS:" print "--title|-t: If set, the first line is the title line" print "--lines|-ln: Split output into files of n lines or less each" print "--sep|-sc [def:,] : The character to use for field delimiter" print "--output|o : output file name/pattern" print "--help|h : print this information" sys.exit(2) def openExcelSheet(outputFileName): """ Opens a reference to an Excel WorkBook and Worksheet objects """ workbook = Workbook() worksheet = workbook.add_sheet("Sheet 1") return workbook, worksheet def writeExcelHeader(worksheet, titleCols): """ Write the header line into the worksheet """ cno = 0 for titleCol in titleCols: worksheet.write(0, cno, titleCol) cno = cno + 1 def writeExcelRow(worksheet, lno, columns): """ Write a non-header row into the worksheet """ cno = 0 for column in columns: worksheet.write(lno, cno, column) cno = cno + 1 def closeExcelSheet(workbook, outputFileName): """ Saves the in-memory WorkBook object into the specified file """ workbook.save(outputFileName) def getDefaultOutputFileName(inputFileName): """ Returns the name of the default output file based on the value of the input file. The default output file is always created in the current working directory. This can be overriden using the -o or --output option to explicitly specify an output file """ baseName = os.path.basename(inputFileName) rootName = os.path.splitext(baseName)[0] return string.join([rootName, "xls"], '.') def renameOutputFile(outputFileName, fno): """ Renames the output file name by appending the current file number to it """ dirName, baseName = os.path.split(outputFileName) rootName, extName = os.path.splitext(baseName) backupFileBaseName = string.join([string.join([rootName, str(fno)], '-'), extName], '') backupFileName = os.path.join(dirName, backupFileBaseName) try: os.rename(outputFileName, backupFileName) except OSError: print "Error renaming output file:", outputFileName, "to", backupFileName, "...aborting" sys.exit(-1) def validateOpts(opts): """ Returns option values specified, or the default if none """ titlePresent = False linesPerFile = -1 outputFileName = "" sepChar = "," for option, argval in opts: if (option in ("-t", "--title")): titlePresent = True if (option in ("-l", "--lines")): linesPerFile = int(argval) if (option in ("-s", "--sep")): sepChar = argval if (option in ("-o", "--output")): outputFileName = argval if (option in ("-h", "--help")): usage() return titlePresent, linesPerFile, sepChar, outputFileName def main(): """ This is how we are called """ try: opts,args = getopt.getopt(sys.argv[1:], "tl:s:o:h", ["title", "lines=", "sep=", "output=", "help"]) except getopt.GetoptError: usage() if (len(args) != 1): usage() inputFileName = args[0] try: inputFile = open(inputFileName, 'r') except IOError: print "File not found:", inputFileName, "...aborting" sys.exit(-1) titlePresent, linesPerFile, sepChar, outputFileName = validateOpts(opts) if (outputFileName == ""): outputFileName = getDefaultOutputFileName(inputFileName) workbook, worksheet = openExcelSheet(outputFileName) fno = 0 lno = 0 titleCols = [] reader = csv.reader(inputFile, delimiter=sepChar) for line in reader: if (lno == 0 and titlePresent): if (len(titleCols) == 0): titleCols = line writeExcelHeader(worksheet, titleCols) else: writeExcelRow(worksheet, lno, line) lno = lno + 1 if (linesPerFile != -1 and lno >= linesPerFile): closeExcelSheet(workbook, outputFileName) renameOutputFile(outputFileName, fno) fno = fno + 1 lno = 0 workbook, worksheet = openExcelSheet(outputFileName) inputFile.close() closeExcelSheet(workbook, outputFileName) if (fno > 0): renameOutputFile(outputFileName, fno) if __name__ == "__main__": main() 

And then you can also convert to csv with this sourceforge project . And if you can convert to csv, you can rewrite xls .. modifing script.

Answer 2

You can easily do this by first creating an R script (xsltocsv) and then calling it from your Bash file.

The R script will look something like this:

 #!/usr/bin/Rscript suppressMessages(library("gdata")) suppressMessages(library("argparse")) #. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . parser <- ArgumentParser( description = "A script to convert a given xsl file to a csv one" ) parser$add_argument( '-rn', '--print-row-names', action = 'store_true', help = 'outputs row names in the output csv file' ) parser$add_argument( '-cn', '--print-column-names', action = 'store_true', help = 'outputs column names in the output csv file' ) parser$add_argument( '-s', '--separator', metavar='separator', type='character', default=';', action = 'store', help = 'outputs column names in the output csv file' ) parser$add_argument( "xsl", metavar = "xsl-file", action = "store", help = "xsl input file" ) parser$add_argument( "csv", metavar = "csv-file", action = "store", help = "csv output file" ) args <- parser$parse_args(commandArgs(TRUE)) #. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vals <- read.xls(args$xsl) write.table(n, file=args$csv, quote = FALSE, col.names=args$print_column_names, row.names=args$print_row_names, sep=args$separator) 

Say you put this in your system path after executing an executable file ( chmod +x xsltocsv ). Then call this script passing the related parameters and you are good to go;)