import win32com.client as w
from win32com.client import constants


class ex():
    def __init__(self):
        self.excel = w.DispatchEx('Excel.Application')
        self.excel.Visible = True
        self.excel.DisplayAlerts = False

    def newBook(self):
        wb = self.excel.Workbooks.Add()
        return wb

    def activeBook(self, wb):
        wb.Activate()
        self.wb = wb

    def activeSheet(self, ws):
        self.ws = self.wb.Sheets(ws)

    def openBook(self, name):
        self.wb = self.excel.Workbooks.Open(name)
        return self.wb

    def refresh(self, name, tab=False, com=True):
        try:
            qr = self.ws.QueryTables.Add(r"""TEXT;""" + name + '.txt', self.ws.Range("A1"))
            qr.Name = "ACM"
            qr.TextFileTabDelimiter = tab
            qr.TextFileCommaDelimiter = com
            qr.Refresh()
        except Exception:
            print "Could not open source, make sure it's not being used"
            self.excel.Quit()

    def insertSheet(self, sheet_name):
        sheets = self.wb.Sheets.Count
        self.ws = self.excel.Worksheets.Add(After=self.wb.Sheets (sheets))
        self.ws.Name = sheet_name

    def saveAs(self, filename):
        self.wb.SaveAs(Filename=filename)

    def delete(self, param, criteria):
        critList = criteria.split(';')
        ad = 0
        for i in range(1, self.lastCell()[1]):
            if param in self.ws.Cells(1, i).Value:
                ad = i
                break

        m = 2
        while m <= (self.ws.UsedRange.Rows.Count):

            if self.ws.Cells(m, ad).Value is None:
                temp = []

            else:
                temp = filter(lambda x: self.ws.Cells(m, ad).Value in x, critList)

            if len(temp) > 0:
                self.excel.Rows("%d:%d" % (m, m)).Select()
                self.excel.Selection.Delete(Shift=constants.xlUp)

            else:
                m += 1

    def move(self, expr, param, sheet):
        ad = 0
        for i in range(1, self.lastCell()[1]):
            if param in self.ws.Cells(1, i).Value:
                ad = i
                break

        m = 2
        while m <= (self.lastCell()[1]):
            if eval('"' + str(self.ws.Cells(m, ad).Value) + '"' + expr):
                self.excel.Rows("%d:%d" % (m, m)).Select()
                self.excel.Selection.Copy()
                self.wb.Sheets(sheet).Cells(self.lastCell(sheet)[0] + 1, 1).PasteSpecial()
                self.excel.Rows("%d:%d" % (m, m)).Delete()

            else:
                m += 1

    def deleteAll(self, sheet=None):
        if sheet is None:
            self.ws.Range(self.ws.Cells(1, 1), self.ws.Cells(self.lastCell(sheet)[0], self.lastCell(sheet)[1])).Delete()
        else:
            self.wb.Sheets("A1").Delete()



    def lastCell(self, sheet=None):
        if sheet is None:
            used = self.ws.UsedRange
        else:
            used = self.wb.Sheets(sheet).UsedRange

        (nrows, ncols) = (used.Row + used.Rows.Count - 1, used.Column + used.Columns.Count - 1)
        return (nrows, ncols)

    def Quit(self):
        self.excel.Quit()

if __name__ == '__main__':
    e = ex()
    wb1 = e.newBook()
    e.activeBook(wb1)
    e.activeSheet('Sheet1')
    e.refresh(r'//cpsrv/cp/forms/ACM', com=True)

    tmp = []

    e.delete('dbstcode','PS;W')

    e.move('=="C"', "dbstcode", 'Sheet2')

    e.deleteAll()

    # for i in range(1, e.lastCell()[0]):
    #     tmp.append(e.ws.Cells(i, 1).Value)

    # e.wb.Close(SaveChanges=0)

    # e.activeBook(wb2)
    # e.activeSheet('AAA')

    # for i in range(1, e.lastCell()[0]):
    #     e.ws.Cells(i, 1).Value = None

    # for i in range(1, len(tmp)):
    #     e.ws.Cells(i, 1).Value = tmp[i]

    # e.wb.Close(SaveChanges=1)

    # e.Quit()