def createXML(dailysummary):
    global floderName
    global fileRoot

    y = datetime.datetime.now().date()-timedelta(days=1)
    dateStr = str(y)
    filename = fileRoot + "_"+dateStr+".xlsx"

    workbook = Workbook(floderName+"/"+filename)
    sheet = workbook.add_worksheet()

    sheet.write(0,0,"usercode")
    sheet.write(0,1,"summary")
    sheet.write(0,2,"loginCnt")
    sheet.write(0,3,"zeroCnt")
    sheet.write(0,4,"flowsize(MB)")
    sheet.write(0,5,"visitcountry")


    r =1
    for summary in dailysummary:
        sheet.write(r,0,summary[0])
        sheet.write(r,1,summary[1])
        sheet.write(r,2,summary[2])
        sheet.write(r,3,summary[3])
        sheet.write(r,4,summary[4])
        sheet.write(r,5,summary[5])
        r += 1

    workbook.close()

    def WorkBook_writeSheet(self, filename):
        columns = ['Date', 'Month', 'ID #', 'Contact ID #', \
            'Talked to Person X?', 'Closeness/Trust with X', \
            'Connecting ID', 'Connector ID']

        # Writes to csv file
        with open(filename, 'w') as f:
            writer = csv.writer(f)
            writer.writerow(columns)
            for row in self.sheet:
                date = row[DATE_COLUMN]
                month = row[MONTH_COLUMN]
                connecting = row[CONNECTING_COLUMN]
                connector = row[CONNECTOR_COLUMN]
                talkVal = row[TALKED_WEIGHT]
                closeVal = row[CLOSENESS_WEIGHT]
                connectingID = row[CONNECTING_ID_COLUMN]
                connectorID = row[CONNECTOR_ID_COLUMN]

                row = [date, month, connecting, connector, talkVal, \
                    closeVal, connectingID, connectorID]

                writer.writerow(row)

        # Converts from the written csv file to xlsx
        for csvfile in glob.glob(os.path.join('.', '*.csv')):
            workbook = Workbook(csvfile[0:-4] + '.xlsx')
            worksheet = workbook.add_worksheet()
            with open(csvfile, 'rb') as f:
                reader = csv.reader(f)
                for r, row in enumerate(reader):
                    for c, col in enumerate(row):
                        worksheet.write(r, c, col)
            workbook.close()
        sys.exit()

    def cvstran2xls(self, filename):
        with open(filename, 'rb') as f:
            reader = csv.reader(f)
            cvscontent = []
            #rownum=0
            for row in reader:
                #rownum = rownum + 1
                #print rownum
                # print len(cvscontent)
                try:
                    cvscontent.append([x.decode('utf-8') for x in row])
                    #print '1',len(cvscontent)
                except:
                    cvscontent.append([x.decode('gbk') for x in row])
                    #print '0',len(cvscontent)
                '''
                else:
                    cvscontent.append(row)
                    print '3', len(cvscontent)
                '''


        #file = xlwt.Workbook()
        #table = file.add_sheet('sheet1', cell_overwrite_ok=True)
        workbook = Workbook(os.path.splitext(filename)[0] + '.xlsx')
        worksheet = workbook.add_worksheet()
        #print 'content',len(cvscontent)
        for row in range(len(cvscontent)):
            #print len(cvscontent[row])
            for col in range(len(cvscontent[row])):
                worksheet.write(row, col, cvscontent[row][col])
                #table.write(row, col, cvscontent[row][col])
        #file.save(os.path.splitext(filename)[0] + '.xlsx')
        workbook.close()
        print os.path.splitext(filename)[0] + '.xlsx', 'OK'

def createXML(type,record):
    global floderName

    y = datetime.datetime.now().date()-timedelta(days=1)
    dateStr = str(y)
    filename = type + "_"+dateStr+".xlsx"

    workbook = Workbook(floderName+"/"+filename)
    sheet = workbook.add_worksheet()

    sheet.write(0,0,"imei")
    sheet.write(0,1,"loginCnt")
    sheet.write(0,2,"zeroCnt")
    sheet.write(0,3,"flowsize")
    sheet.write(0,4,"visitcountry")
    sheet.write(0,5,"version")

    r =1
    for summary in record:
        sheet.write(r,0,summary[0])
        sheet.write(r,1,summary[1])
        sheet.write(r,2,summary[2])
        sheet.write(r,3,summary[3])
        sheet.write(r,4,summary[4])
        sheet.write(r,5,summary[5])
        r += 1

    workbook.close()

def map_to_csv(file_name, track_info_map):
    headers =["","acousticness","danceability","duration","energy","instrumentalness","key","liveness","loudness","mode",
              "speechiness","tempo","time_signature","valence"]

    csv_file = open(file_name,"wb")
    writer = csv.writer(csv_file, quoting=csv.QUOTE_NONNUMERIC)
    writer.writerow(headers)
    song_count = 0
    for song_name in track_info_map.keys():
        values = []
        song_count += 1
        for header in headers:
            if header in track_info_map[song_name]:
                 values.append(track_info_map[song_name][header])

        values.insert(0,song_name)
        writer.writerow(values)
    csv_file.close()

    try:
        work_book = xlrd.open_workbook("finalproject.xls")
        work_sheet = work_book.get_sheet('datasheet1')

    except:
        base_name = os.path.splitext(file_name)[0]
        work_book = Workbook(base_name + ".xlsx")
        work_sheet = work_book.add_worksheet()

    with open(file_name,'rb') as f:
        reader = csv.reader(f)
        for rindex, row in enumerate(reader):
            for cindex, column in enumerate(row):
                work_sheet.write(rindex,cindex,column)
    work_book.close()

def publishQuery(period):
    yesterQuery = """
    SELECT * FROM
    t_flowsummary_partner_adv
    WHERE DATE(epochTime) = DATE_SUB(DATE(NOW()),INTERVAL 1 DAY)
    """
    weeklyQuery = """
    SELECT WEEK(NOW()) AS weeknum,iso2,partner,SUM(reserveup),SUM(reservedown),SUM(freeup),SUM(freedown),SUM(reservecnt),SUM(freecnt) FROM
    t_flowsummary_partner_adv
    WHERE DATE(epochTime) BETWEEN DATE_SUB(DATE(NOW()),INTERVAL 7 DAY) AND DATE_SUB(DATE(NOW()),INTERVAL 1 DAY)
    GROUP BY iso2,partner
    """
    rList = list()
    con,cur = getCon("REMOTE","login_history")
    if period == 'weekly':
        cur.execute(weeklyQuery)
    else:
        cur.execute(yesterQuery)

    tm = cur.fetchall()
    for t in tm:
        rList.append(t)

    con.close()

    foldername = createDailyFloder(period)
    y = datetime.datetime.now().date()
    dateStr = str(y)
    filename = period + "_"+dateStr+".xlsx"

    workbook = Workbook(foldername+"/"+filename)
    sheet = workbook.add_worksheet()
    date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})
    if period == "daily":
        sheet.write(0,0,u"日期".encode('utf-8'),date_format)
    else:
        sheet.write(0,0,u"周".encode('utf-8'))
    sheet.write(0,1,u"国家".encode('utf-8'))
    sheet.write(0,2,"partner")
    sheet.write(0,3,u"包卡上行".encode('utf-8'))
    sheet.write(0,4,u"包卡下行".encode('utf-8'))
    sheet.write(0,5,u"非包卡上行".encode('utf-8'))
    sheet.write(0,6,u"非包卡下行".encode('utf-8'))
    sheet.write(0,7,u"包卡用户数".encode('utf-8'))
    sheet.write(0,8,u"非包卡用户数".encode('utf-8'))

    r =1
    for row in rList:
        sheet.write(r,0,row[0])
        sheet.write(r,1,row[1])
        sheet.write(r,2,row[2])
        sheet.write(r,3,row[3])
        sheet.write(r,4,row[4])
        sheet.write(r,5,row[6])
        sheet.write(r,6,row[5])
        sheet.write(r,7,row[7])
        sheet.write(r,8,row[8])
        r += 1

    workbook.close()

def wrap_csvs(csvpath, outname):
   
    directory_path = os.path.abspath(csvpath)
    
    if outname is None:
        filename = os.path.basename(directory_path + ".xlsx")
    else:
        filename = outname

    workbook_name = os.path.join(directory_path, filename)
    workbook = Workbook(workbook_name)

    for c in glob.glob(os.path.join(csvpath, "*.csv")):
        sheetname = os.path.basename(c[:c.rfind(".")])
        print("Adding {} to {}".format(c, workbook_name)) 
        worksheet = workbook.add_worksheet(sheetname)
    
        with open(c, 'r') as f:
            reader = csv.reader(f)
        
            for rindex, row in enumerate(reader):
                for cindex, col in enumerate(row):
                    try:
                        worksheet.write(rindex,cindex, float(col))
                    except ValueError:
                        worksheet.write(rindex, cindex, col)

    workbook.close()

 def write_to_excel(self,filename,items,fdir=DATA_DIR):
     fpath=os.path.join(fdir,filename+'.xlsx')
     workbook=Workbook(fpath)
     worksheet=workbook.add_worksheet()
     for i in range(len(items)):
         for j in range(len(items.split(':::'))):
             worksheet.write(i,j,items.split(':::')[j].decode('gbk'))

def getTask1():
    global floderName
    global fileRoot

    # returnList=getAss()
    bssList = getBss()

    print bssList

    y = datetime.datetime.now().date()-timedelta(days=1)
    dateStr = str(y)
    filename = fileRoot + "_"+dateStr+".xlsx"

    workbook = Workbook(floderName+"/"+filename)
    sheet = workbook.add_worksheet()

    sheet.write(0,0,"visitcountry")
    sheet.write(0,1,"imei")
    sheet.write(0,2,"flowsize")

    r =1
    for bssResutle in bssList:
        sheet.write(r,0,bssResutle[0])
        sheet.write(r,1,bssResutle[1])
        sheet.write(r,2,bssResutle[2])
        r += 1

    workbook.close()

    def makedb(self):
        conn = sqlite3.connect("registration.db")
        c = conn.cursor()
        table = "new_entries"
        try:
            query = "select * from %s" % table
            c.execute(query)
            workbook = Workbook("output.xlsx")
            sheet = workbook.add_worksheet()
            bold = workbook.add_format({'bold': True})
            sheet.set_column('A:A', 5)
            sheet.set_column('B:B', 18)
            sheet.set_column('C:C', 8)
            sheet.set_column('D:D', 8)
            sheet.set_column('E:E', 13)
            sheet.set_column('F:F', 17)
            sheet.set_column('G:G', 5)
            sheet.write("A1", 'S.No', bold)
            sheet.write("B1", 'Name', bold)
            sheet.write("C1", 'Branch', bold)
            sheet.write("D1", 'Section', bold)
            sheet.write("E1", 'Phone no', bold)
            sheet.write("F1", 'Email', bold)
            sheet.write("G1", 'Size', bold)
            for r, row in enumerate(c.fetchall()):
                for c, col in enumerate(row):
                    sheet.write(r + 1, c, col)
            workbook.close()
            conn.close()
            messagebox.showinfo(title="Registration", message="Successfully made excel file")
        except:
            messagebox.showerror(title="Registration", message="Database doesn't exist")

        return

class XLSXWriter(BOMWriterBase):
    def open(self):
        try:
            from xlsxwriter.workbook import Workbook
        except ImportError:
            raise ImportError("Please install xlsxwriter: `pip install xlsxwriter`")
        self.workbook = Workbook(self.fname)
        self.title_format = self.workbook.add_format({'bold': True, 'bg_color': '#999999'})
        self.alter_format = self.workbook.add_format({'bg_color': '#dddddd'})
        self.writer = self.workbook.add_worksheet()
        self.writer.set_column(0, 0,  4.00)
        self.writer.set_column(1, 1, 14.50)
        self.writer.set_column(2, 2,  3.00)
        self.writer.set_column(3, 3,  4.30)
        self.writer.set_column(4, 5, 20.00)
        self.writer.set_column(6, 7, 48.00)
        self.line = 0

    def writerow(self, row, header=False):
        for col, cell in enumerate(row):
            if header:
                format = self.title_format
            else:
                if self.line % 2 == 0:
                    format = self.alter_format
                else:
                    format = None
            self.writer.write_string(self.line, col, unicode(cell), format)
        self.line += 1

    def close(self):
        self.workbook.close()

def csvsToWorkbook( args ):

  workbook = Workbook( args.workbook_filename )

  icsv=0  # CSV file count

  csv_filenames = args.csv_filenames.split( ',' )

  for csv_filename in csv_filenames:

    # Format name of sheet
    print( "<"+csv_filename+">" )
    basename = ntpath.basename( csv_filename )
    namesplit = basename.split( '_' )
    sheetname = ( "_".join( namesplit[3:] ) ).split( '.' )[0]

    # Add the worksheet to the workbook
    worksheet = workbook.add_worksheet( sheetname )

    # Save data in the worksheet
    with open( csv_filename, mode='r') as csv_file:
      reader = csv.reader( csv_file )
      for r, row in enumerate( reader ):
        for c, col in enumerate( row ):
          worksheet.write( r, c, col )

  workbook.close()

def dataToXLSX(dataList, outFileName):
    workbook = Workbook(outFileName)
    worksheetData = workbook.add_worksheet('Data')

    # Can we make this mapping automatic?
    columnMap = {0: "A", 1: "B", 2: "C", 3: "D", 4: "E", 5: "F", 6: "G", 7: "H", 8: "I", 9: "J", 10: "K", 11: "L",
                 12: "M", 13: "N", 14: "O", 15: "P"}
    bold = workbook.add_format({'bold': 1})

    # Format the worksheet1 data cells that the chart will refer to
    headings = dataList[0]

    # Set column width. Note that it is not possible to "auto-fit" as this only happens at run-time in Excel
    worksheetData.set_column('A:%s' % columnMap[len(dataList[0])], 15)

    # Write headings row to workbook
    worksheetData.write_row('A1', headings, bold)  # note that alignment not applicable to the write_row method

    # write data
    rowCount = 2
    for data in dataList[1:]:
        columnCount = 0
        for item in data:
            if item[-1] == "C":
                item = item[:-2]
            worksheetData.write('%s%i' % (columnMap[columnCount], rowCount), float(item))
            columnCount += 1
        rowCount += 1

    # add an auto filter and freeze pane on the headers row
    worksheetData.autofilter('A1:%s%d' % (columnMap[len(dataList[0]) - 1], len(dataList)))
    worksheetData.freeze_panes(1, 0)
    workbook.close()

def download_replies(modeladmin, request, queryset):
    output = BytesIO()
    workbook = Workbook(output, {'in_memory': True})
    sheet = workbook.add_worksheet('test')

    question_columns = OrderedDict()
    max_column = 0
    row = 1
    for reply in queryset:
        for question in reply.answers.keys():
            current_column = question_columns.get(question, max_column)
            if current_column == max_column:
                question_columns[question] = max_column
                max_column += 1
            sheet.write(row, current_column, reply.answers[question])
        row += 1

    responses = {}
    for reply in queryset:
        for question in reply.answers.keys():
            if responses.get(question) is None:
                responses[question] = []
            responses[question].append(reply.answers[question])
    for question in question_columns.keys():
        sheet.write(0, question_columns[question], question)

    workbook.close()
    output.seek(0)
    response = HttpResponse(output.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    response['Content-Disposition'] = "attachment; filename=test.xlsx"
    return response

  def __push_excel(self,file_name):
    '''
    Writes a set of data to an excel file
    '''
    # Open work book
    workbook = Workbook(file_name)
    worksheet = workbook.add_worksheet()

    # start at top left corner and write headers
    row, col = 0, 0
    for header in self.excel['headers']:
      worksheet.write(row,col, str(header))
      col += 1

    # start in second row and write data
    row, col = 1, 0
    for row_data in self.excel['data']:
      for cell_data in row_data:
        worksheet.write(row, col, str(cell_data))
        col += 1
      row += 1
      col = 0

    workbook.close()

    # Empty memory (this usually happens every 4000 timesteps)
    self.excel['header'] = []
    self.excel['data'] = [[]]

    def cvstran2xls(self, filename):  #将csv文件转换为xlsx
        with open(filename, 'rb') as f:
            reader = csv.reader(f)
            cvscontent = []
            #rownum=0
            for row in reader:
                #rownum = rownum + 1
                #print rownum
                # print len(cvscontent)
                try:
                    cvscontent.append([x.decode('utf-8') for x in row])
                    #print '1',len(cvscontent)
                except:
                    cvscontent.append([x.decode('gbk') for x in row])
                    #print '0',len(cvscontent)
                '''
                else:
                    cvscontent.append(row)
                    print '3', len(cvscontent)
                '''


        workbook = Workbook(os.path.splitext(filename)[0] + '.xlsx')
        worksheet = workbook.add_worksheet()
        for row in range(len(cvscontent)):
            for col in range(len(cvscontent[row])):
                worksheet.write(row, col, cvscontent[row][col])
        workbook.close()
        print os.path.splitext(filename)[0] + '.xlsx', 'OK'

def convert_tsv_xls(tsv):
	name = tsv.strip("tsv$")
	workbook = Workbook(name+"xls")
	worksheet = workbook.add_worksheet()
	tsv_reader = csv.reader(open(tsv, 'rb'), delimiter='\t')
	for row, data in enumerate(tsv_reader):
		worksheet.write_row(row, 0, data)
	workbook.close()

 def create(input):
     filename = common.pop_dict(input, "filename")
     workbook = Workbook(filename)
     formats = InputFactory.init_formats(input, workbook)
     for sheet in input['sheets']:
         XlsFactory.process_sheet(workbook, sheet, formats)
     workbook.close()
     return filename

 def _create_excel(self):
     output = StringIO.StringIO()
     xls = Workbook(output, {"default_date_format": self.date_format})
     sheet_name = self.sheet_name.strip()
     if len(sheet_name) > 31:
         sheet_name = sheet_name[0:31]
     sheet = xls.add_worksheet(sheet_name)
     return xls, sheet, output

 def convert_csv_to_excel(self, csv_file, excel_file):
     workbook = Workbook(excel_file)
     worksheet = workbook.add_worksheet()
     with open(csv_file, 'rb') as f:
         reader = csv.reader(f)
         for r, row in enumerate(reader):
             for c, col in enumerate(row):
                 worksheet.write(r, c, col)
     workbook.close()