使用 Bash 写入 Excel 表格

Writing to an excel sheet using Bash

提问by Gil

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

是否可以从 bash 脚本写入 Excel 工作表（任何类型）？

What I am looking for is something along these lines :

我正在寻找的是沿着这些路线的东西：

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 ) 

Thank you for your replies and suggestion .

感谢您的回复和建议。

回答by Alessandra Bilardi

You could write excel by bash, perl, python, .. I think that each program language has its solutions.

您可以通过bash, perl, python, ..编写 excel 。我认为每种程序语言都有其解决方案。

bash

猛击

You could use joinor awk, and I think that there are other solutions.

您可以使用joinor awk，我认为还有其他解决方案。

join

加入

If you want jointo files with same column, look these posts: Bash join commandand join in bash like in SAS

如果您想要join具有相同列的文件，请查看这些帖子：Bash join commandand join in bash like in SAS

awk

awk

You could write a csv, but you could renameinto xlsand then with excel, gnumeric, or other programs, it is recognized like xls.

你可以写一个CSV文件，但你可以rename进入xls，然后用Excel的编辑，Gnumeric，或其他程序，它是公认喜欢XLS。

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

when you modify xls with excel, gnumeric, or other programs, and save in xls, you could not read by bash. So that @Geekasaur recommended perlor pythonsolutions.

当你用excel、gnumeric或其他程序修改xls并保存在xls中时，bash无法读取。所以@Geekasaur 推荐perl或python解决方案。

perl

perl

You could write xlsin perl, follow a sample:

你可以用xlsperl编写，遵循一个示例：

#!/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 could modify xlswith Spreadsheet::ParseExcelpackage: look How can I modify an existing Excel workbook with Perl?and "This link is broken and has been reported to IBM" reading and writing sample

然后你可以xls用Spreadsheet::ParseExcel包修改：看看如何用 Perl 修改现有的 Excel 工作簿？和“此链接已断开，已上报IBM”读写示例

python

Python

You could write real xlsin python, follow a sample:

您可以xls在 python 中编写 real ，请按照示例进行操作：

#!/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|-l n: Split output into files of n lines or less each"
  print "--sep|-s c [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 could also convertto csv with this sourceforge project. And if you could convert to csv, you could rewrite xls.. modifing the script.

然后你也convert可以用这个sourceforge 项目csv 。如果您可以转换为 csv，您可以重写 xls.. 修改脚本。

回答by leco

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

您可以通过首先创建一个 R 脚本 (xsltocsv)，然后从您的 Bash 文件中调用它来轻松完成此操作。

The R script would look something like:

R 脚本如下所示：

#!/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)

Let us say that you put this into your system path after making the file executable (chmod +x xsltocsv). Then, invoke this script passing the associated parameters, and you are good to go ;)

假设您在使文件可执行 ( chmod +x xsltocsv)后将其放入系统路径中。然后，调用这个传递相关参数的脚本，你就可以开始了；)