如何使用Shell脚本从Linux中的Excel表读取数据?

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表格里会有。在第一列和域名在第二列。在我的excel表格中有100个域。因此，我不想手工复制每个域并将它们粘贴到浏览器中以检查所有域。我只想编写一个shell脚本，它可以通过运行单个shell脚本将所有域打开到浏览器中。请帮帮我。我将非常感谢你。

Thanks & Regards- Reetesh Chauhan

谢谢和问候——Reetesh Chauhan

2 个解决方案

#1

xls2csv will get your xls file into a CSV format.

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

Pipe that to sed to remove the double-quotes.

将其连接到sed以删除双引号。

Use while to iterate over each line.

使用while循环遍历每一行。

echo each line (l) to awk to get the second ($2) column into the $d (for domain) variable.

将每行(l)回传给awk，将第二个($2)列转换为$d (for domain)变量。

Then send that to browser. You can replace lynx with 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 $2}'`; lynx "$d"; done

#2

you can use samples: help1 help2 help3

你可以使用示例:help1 help2 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 python as 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()