合并

这次的主题又是“合并”。
由于一个项目的关系,我需要把大量的以xlsx扩展名结尾的Excel格式的文件合并到一个文件里去。利用一个叫做xlsx2csv的python工具以及如下bash脚本,这个工作可以被轻易自动化。


#!/usr/bin/bash --

THISFILENAME=$0
REQUIRED_ARGS=1

ERROR_OPT=1
ERROR_FILE_NOTEXIST=2
ERROR_WRONG_FILE_FORMAT=3

# Default options
SHEET=1
OUTFILE=all-files.csv

########################################################
# Verify file existence and extension name
########################################################

function validate_file {
if [ ! -e $1 ]; then
echo "File $1 does not exist."
return $ERROR_FILE_NOTEXIST
fi

filename=`basename $1`
ext=${filename##*.}
ext_lower=`echo -n "$ext" | tr '[A-Z]' '[a-z]'`

if [ "xlsx" != $ext_lower ]; then
echo "File $1 does not have .xlsx extension."
return $ERROR_WRONG_FILE_FORMAT
fi
return 0

}

##################################################
# Usage
##################################################
function usage
{
echo ""
echo "USAGE: "
echo " $THISFILENAME [-hso] "
echo ""
echo "OPTIONS:"
echo " -s xlsx sheet number"
echo " -o output file name"
echo " -h this usage information"
echo ""
echo "EXAMPLE:"
echo " $THISFILENAME -s 2 -o output.csv file1.xlsx file2.xlsx"
echo ""
# exit $ERROR_OPT
}

#################################################
# Conversion: arg1 is the xlsx file name
#################################################
function convert
{
csvfile=`basename $1 "xlsx"`csv
python xlsx2csv.py -s $SHEET $1 $csvfile
}

#################################################
# DoConvert
#################################################
function do_convert
{
for ARG in "$@"
do
echo "Converting $ARG"
if validate_file $ARG
then
convert $ARG
else
echo "$ARG is not converted"
fi
done
}

if [ $# -lt "$REQUIRED_ARGS" ]
then
usage
exit $ERROR_OPT
fi

####################################################
# Let's do it
####################################################

SAVEIFS=$IFS
IFS=$(echo -en "\n\b")

#PROCESS ARGS
while getopts ":s:o:h" Option
do
case $Option in
s ) SHEET=$OPTARG;;
o ) OUTFILE=$OPTARG;;
h ) usage
exit 0;;
* ) echo ""
echo "Unimplemented option chosen."
usage # DEFAULT
exit $ERROR_OPT
esac
done

# Decrements the argument pointer so it points to next argument.
shift $(($OPTIND - 1))

do_convert $@

cat *.csv > $OUTFILE.rename
rm *.csv
mv $OUTFILE.rename $OUTFILE

# Restore $IFS
IFS=$SAVEIFS

如果需要,可以用Excel打开生成的csv文件,然后选择“另存为Excel Workbook”,文件合并的工作即告完成。

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s