本文介绍了使用 Bash 写入 Excel 表格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

是否可以从 bash 脚本写入 Excel 工作表(任何类型)?

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

我正在寻找的是这样的东西:

What I am looking for is something along these lines :

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

感谢您的回复和建议.

推荐答案

您可以通过 bashperlpython、.. 我认为每种程序语言都有其解决方案.

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

bash

您可以使用 joinawk,我认为还有其他解决方案.

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

加入

如果您希望 join 到具有相同列的文件,请查看这些帖子:Bash join命令像SAS一样加入bash

If you want join to files with same column, look these posts: Bash join command and join in bash like in SAS

awk

你可以写一个 csv,但你可以将 rename 改成 xls 然后用 excel、gnumeric 或其他程序,它像 xls 一样被识别.

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

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

当你用excel、gnumeric或其他程序修改xls,并保存在xls中时,您无法通过 bash 读取.所以@Geekasaur 推荐了 perlpython 解决方案.

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

perl

您可以在 perl 中编写 xls,遵循 示例:

You could write xls in perl, follow a 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: $!
";
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();

然后你可以用 Spreadsheet::ParseExcel 包修改 xls:看 如何使用 Perl 修改现有的 Excel 工作簿?阅读和写作示例 [编者注:此链接已断开,已向 IBM 报告]

And then you could modify xls with Spreadsheet::ParseExcel package: look How can I modify an existing Excel workbook with Perl? and reading and writing sample

蟒蛇

您可以在 python 中编写真正的 xls,遵循 示例:

You could write real xls in python, follow a 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|-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()

然后你也可以使用这个 sourceforge 项目转换到 csv/a>.如果您可以转换为 csv,您可以重写 xls.. 修改脚本.

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

这篇关于使用 Bash 写入 Excel 表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

查看更多