# -*- coding: UTF-8 -*-
from Tkinter import *
import tkMessageBox
import tkFileDialog
import xlrd
import pyExcelerator as xl
import string
import datetime,sys,types
 
global namesheet 
global rowsnum
rowsnum = 0
class ListBoxChoice(object):
    def __init__(self, master=None, title=None, message=None, list=[]):
        self.master = master
        self.value = None
        self.list = list[:]
 
        self.modalPane = Toplevel(self.master)
 
        self.modalPane.transient(self.master)
        self.modalPane.grab_set()
 
        self.modalPane.bind("<Return>", self._choose)
        self.modalPane.bind("<Escape>", self._cancel)
 
        if title:
            self.modalPane.title(title)
 
        if message:
            Label(self.modalPane, text=message).pack(padx=5, pady=5)
 
        listFrame = Frame(self.modalPane)
        listFrame.pack(side=TOP, padx=5, pady=5)
 
        scrollBar = Scrollbar(listFrame)
        scrollBar.pack(side=RIGHT, fill=Y)
        self.listBox = Listbox(listFrame, selectmode=SINGLE)
        self.listBox.pack(side=LEFT, fill=Y)
        scrollBar.config(command=self.listBox.yview)
        self.listBox.config(yscrollcommand=scrollBar.set)
        self.list.sort()
        for item in self.list:
            self.listBox.insert(END, item)
 
        buttonFrame = Frame(self.modalPane)
        buttonFrame.pack(side=BOTTOM)
 
        chooseButton = Button(buttonFrame, text="Choose", command=self._choose)
        chooseButton.pack()
 
        cancelButton = Button(buttonFrame, text="Cancel", command=self._cancel)
        cancelButton.pack(side=RIGHT)
 
    def _choose(self, event=None):
        try:
            firstIndex = self.listBox.curselection()[0]
            self.value = self.list[int(firstIndex)]
        except IndexError:
            self.value = None
        self.modalPane.destroy()
 
    def _cancel(self, event=None):
        self.modalPane.destroy()
 
    def returnValue(self):
        self.master.wait_window(self.modalPane)
        return self.value
 
#msg box ยืนยันการปิดโปรแกรม
def YouQuit():
     if tkMessageBox.askokcancel("ยืนยัน","คุณต้องการปิดโปรแกรมใช่ไหม ?"):
          root.destroy()
          #sys.exit()
 
def TxtAlert(txt):
    if len(txt) == 0:
        aboutp = ''
    else:
        aboutp = txt
 
    tkMessageBox.showinfo("About Us",aboutp)
 
#msg box เกี่ยวกับโปรแกรม
def AboutPrograme():
     aboutp = """
     โปรแกรม LoadEx v 0.1 นี้ เป็นโปรแกรมสำหรับการแปลง
     ไฟล์ไมโครซอร์ฟเอ็กซ์เซล ให้เป็นไฟล์ CSV และนำเข้าฐานข้อมูล
     """
     tkMessageBox.showinfo("About Us",aboutp)
 
#box บันทึกไฟล์
def SaveNewFile():
     savefilename = ''
     myFormats = [
     ('CSV','*.csv'),
     ]
     savefilename = tkFileDialog.asksaveasfilename(filetypes=myFormats ,title="บันทึกไฟล์...")
     #print "savefilename = ",savefilename
     if savefilename.find(".csv") == -1 :
         savefilename += savefilename + ".csv"
 
     return savefilename
#ค้นหาไฟล์ Excel
def OpenFileExcel():
 
     myFormats = [
     ('Microsoft Excel','*.xls'),
     ]
     oldfilename = tkFileDialog.askopenfilename(filetypes=myFormats ,title="ค้นหาไฟล์ MS Excel...")
     if len(oldfilename) > 3:
          newFile = SaveNewFile()
          SaveCSV(oldfilename , newFile)
 
def GenCSV(listData):
    dataCsv = ''
    if len(listData) >= 1:
        for rData in listData:
            dataColCsv = ''
            reData = ''
            for cData in rData:
                reData = cData.replace(',','')
                dataColCsv += ',"' + str(reData.encode('utf-8', 'backslashreplace')) + '"'
 
            dataCsv += str(dataColCsv[1:]) + "\n" 
    else:
        dataCsv = ''
 
    return dataCsv
 
def SaveCSV(originalFile,fname):
     namesheet = []
     csv_row = ''
     #nameCols = []
     #listNameCol = []
     #for k in range(65,90):
     #    nameCols.append(chValue)
 
     shn = 0
     if len(originalFile) == 0:
         TxtAlert("ไม่มีไฟล์ครับ"+str(originalFile))
     else:
         wBooks = xlrd.open_workbook(originalFile)
         #TxtAlert( "The number of worksheets is" + str(wBooks.nsheets))
         for nBooks in range(wBooks.nsheets):
             nSheets = wBooks.sheet_by_index(nBooks)
             listlabel = str(nBooks)+" "+str(nSheets.name.encode('utf-8'))
             namesheet.append(listlabel)
 
         SheetSelect = SelectSheet(namesheet)
         numSheetSelect = int(SheetSelect[0:1])
         SheetSelected = wBooks.sheet_by_index(numSheetSelect)
         #txtx = "sheet = " + str(SheetSelected.name)+" nrow =" +str(SheetSelected.nrows) + " ncol =" + str(SheetSelected.ncols)
         #TxtAlert("numSheetSelect = " + str(txtx))
         cData = [] 
         dataincell = ''
         for row in range(0,SheetSelected.nrows):
              cDataII = []
              for col in  range(0,SheetSelected.ncols):
                   dataincell = unicode(SheetSelected.cell_value(rowx=row, colx=col))
                   cDataII.append(unicode(dataincell))
              cData.append(cDataII)
 
         csv_row = GenCSV(cData)
         #print csv_row
 
     #print "rowsnum =",rowsnum
     fout = open(fname, "w+")
     fout.write(csv_row)
     fout.close()
 
 
 
def SelectSheet(nlist,txtTitle = "กรุณาเลือกแผ่นงานที่จะใช้ด้วยครับ..."):
     returnValue = ''
     returnValue = ListBoxChoice(root, "แผ่นงาน ", txtTitle,nlist).returnValue() 
     return returnValue
 
 
def getSheet(valuelist):
     return valuelist
 
 
def delrow(num):
     if num:
         rowsnum = 1
     else:
         rowsnum = 0
 
root = Tk()
root.title("โปรแกรม LoadExcel v 0.5")
root.maxsize(500,200)
Frame(root, width=800, height=600).grid(column=6 ,row=5, sticky=E)
blRows = BooleanVar()
c = Checkbutton(root, text="ยกเว้นแถวแรก", variable=blRows, command=delrow(blRows.get()),
                onvalue="RGB", offvalue="L")
c.grid(pady=2, padx=2, column=1 ,row=0, sticky=E)
Label(root, text="").grid(pady=2, padx=2, column=0 ,row=0, sticky=E)
Label(root, text="").grid(pady=2, padx=2, column=2 ,row=0, sticky=E)
Label(root, text="").grid(pady=2, padx=2, column=3 ,row=0, sticky=E)
Button(root, text = 'ค้นหาไฟล์', command = OpenFileExcel).grid(row =2,column=3)
Label(root, text="").grid(pady=2, padx=2, column=6 ,row=5, sticky=E)
 
 
# สร้าง menu
menu = Menu(root)
 
filemenu = Menu(menu)
menu.add_cascade(label="แฟ้ม ", menu=filemenu)
filemenu.add_command(label="ค้นหาไฟล์ ", command=OpenFileExcel)
filemenu.add_separator()
filemenu.add_command(label="จบการทำงาน ", command=YouQuit)
 
helpmenu = Menu(menu)
menu.add_cascade(label="ช่วยเหลือ ", menu=helpmenu)
helpmenu.add_command(label="เกี่ยวกับโปรแกรม", command=AboutPrograme)
 
 
 
 
root.config(menu=menu)
root.protocol("WM_DELETE_WINDOW", YouQuit)
root.mainloop()