Subversion Repositories SmartDukaan

#!/usr/bin/python

'''
Tool to convert CSV files (with configurable delimiter) to Excel spreadsheets.

This is an adaptation of the script present at
http://sujitpal.blogspot.com/2007/02/python-script-to-convert-csv-files-to.html

The CSV to excel conversion logic has been preserved but the parsing of options
has been moved to the more recent optparse.

Created on 08-Nov-2011

@author: Chandranshu
'''
import optparse
import csv
import string
import sys
import os.path
from pyExcelerator import Workbook

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
    
    worksheet.panes_frozen = True
    worksheet.horz_split_pos = 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 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 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 main():
    parser = optparse.OptionParser()
    parser.add_option("-t", "--title", dest="title_present",
                       action="store_true",
                       help="If set, the first line is the title line")
    parser.add_option("-l", "--lines", dest="lines",
                       type="string",
                       help="Split output into files of LINE lines or less each.",
                       metavar="LINE")
    parser.add_option("-s", "--sep", dest="sep",
                       type="string",
                       help="Use SEP as a record separator rather",
                       metavar="SEP")
    parser.add_option("-o", "--output", dest="outputFileName",
                       type="string",
                       help="Create the output file with name NAME",
                       metavar="NAME")
    parser.set_defaults(title_present=False, lines=-1, sep="\t", outputFileName="")
    parser.set_usage("%prog [options] csvfile")
    (options, args) = parser.parse_args()
    if len(args) != 1:
        parser.error("Please at least provide the input file name")
    
    inputFileName = args[0]
    try:
        inputFile = open(inputFileName, 'r')
    except IOError:
        print "File not found:", inputFileName, "...aborting"

    titlePresent, linesPerFile, sepChar, outputFileName = options.title_present, options.lines, options.sep, options.outputFileName
    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()