如何在 Linux 中使用 Shell 脚本从 Excel 工作表中读取数据？

How to read data from Excel sheet in Linux using Shell Script?

提问by Reetesh Chauhan

EveryOne, I am new in Linux & very much interested to write shell scripts. This time I am making a excel sheet. In that excel sheet there will be S.No. in 1st column & Domain names in second column. There are 100 of domains in my excel sheet. So, I do not want to manually copy every domain & paste them into browser to check all. I simply want to write a shell script which can open all the domains into browser by running the single shell script. Please help me out. I will be very grateful to you.

大家，我是 Linux 新手，对编写 shell 脚本非常感兴趣。这次我正在制作一个excel表。在那个 excel 表中会有 S.No. 在第一列和在第二列的域名。我的 Excel 表中有 100 个域。所以，我不想手动复制每个域并将它们粘贴到浏览器中进行检查。我只是想编写一个 shell 脚本，它可以通过运行单个 shell 脚本将所有域打开到浏览器中。请帮帮我。我将非常感谢你。

Thanks & Regards- Reetesh Chauhan

感谢和问候 - Reetesh Chauhan

回答by MLSC

you can use samples: help1help2help3

您可以使用示例：help1 help2 help3

you can use perl:

你可以使用perl：

#!/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 pythonas well:

并且python还有：

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

回答by Jesse Hogan

xls2csvwill get your xls file into a CSV format.

xls2csv会将您的 xls 文件转换为 CSV 格式。

Pipe that to sedto remove the double-quotes.

通过管道将sed其删除以删除双引号。

Use whileto iterate over each line.

使用while遍历每一行。

echoeach line (l) to awkto get the second ($2) column into the $d(for domain) variable.

echo每行 ( l) 到awk将第二 ( $2) 列放入$d（域）变量中。

Then send that to browser. You can replace lynxwith your favorite browser. A GUI browser will work as easily as a TUI one.

然后将其发送到浏览器。您可以替换lynx为您喜欢的浏览器。GUI 浏览器将像 TUI 浏览器一样轻松工作。

xls2csv domains.xls | sed -e's/"//g' | while read l; do d=`echo "$l" | awk  '{print }'`; lynx "$d"; done