class ExcelWorkbook(object):
    def __init__(self, encoding='utf-8', style_compression=0):
        self.wb = Workbook(encoding, style_compression)
        self.sheets = []

    def sheet(self, sheet_name=''):
        """get a sheet by name, if no name creates a new sheet"""
        if not self.sheets:
            return self.add_sheet(sheet_name)

        else:
            for sheet in self.sheets:
                if sheet.name == sheet_name:
                    return sheet

    def add_sheet(self, sheet_name='Sheet 1', headers=[], header_line_no=0, use_borders=False, styleHeaders=None, styleDict={}, widths={}):
        ws = self.wb.add_sheet(sheet_name, cell_overwrite_ok=True)
        self.sheets.append(ExcelSheet(ws, headers, header_line_no, use_borders, styleHeaders, styleDict, widths))
        return self.sheets[-1]

    def save(self, filename):
        """saves excel file"""
        if os.path.splitext(filename)[-1] not in ('.xls', '.xlsx'):
            filename = os.path.splitext(filename)[0] + '.xls'

        for sheet in iter(self):
            sheet.autoFit()

        self.wb.save(filename)
        return filename

    def __iter__(self):
        """generator to iterate through sheets"""
        for sheet in self.sheets:
            yield sheet

    def render(self, request, context, **response_kwargs):
        from xlwt import Workbook, XFStyle, easyxf

        w = Workbook(encoding='utf-8')

        ws = w.add_sheet('Report')
        style = XFStyle()

        row = 0
        heading_xf = easyxf('font:height 200; font: bold on; align: wrap on, vert centre, horiz center')
        ws.write(row, 0, '#', style)

        for col, fieldname in enumerate(context['report'].headers, start=1):
            ws.write(row, col, str(fieldname), heading_xf)
            ws.col(col).width = 5000
        ws.row(row).height = 500

        # we have to prepare all the styles before going into the loop
        # to avoid the "More than 4094 XFs (styles)" Error
        styles = self._get_styles(context)
        for rownum, data in enumerate(context['report']):
            ws.write(rownum + 1, 0, rownum + 1)
            for idx, (fieldname, rowvalue) in enumerate(data.items()):
                style = styles[rowvalue.column.name]
                try:
                    ws.write(rownum + 1, idx + 1, with_widget(rowvalue, format='xls'), style)
                except Exception:
                    #logger.warning("TODO refine this exception: %s" % e)
                    ws.write(rownum + 1, idx + 1, smart_str(with_widget(rowvalue)), style)

        f = StringIO.StringIO()
        w.save(f)
        f.seek(0)

        return f.read()

def init_xls(sheetname='1'):
    book = Workbook()
    if sheetname:
        sheet = book.add_sheet(sheetname)
    else:
        sheet = book.add_sheet('1')
    return book, sheet

def output_mesg(company_lack):
    book = Workbook()
    sheet1 = book.add_sheet(u'1')
    i = 0
    num = 1
    for key, value in company_lack.items():
        for s, d in value.items():

            sheet1.write(i, 0, key)
            sheet1.write(i, num, s)
            sheet1.write(i, num+1, d)
            i = i + 1
        num = 1


    book.save('4.xls')  # 存储excel
    book = xlrd.open_workbook('4.xls')

    print('----------------------------------------------------------------------------------------')
    print('----------------------------------------------------------------------------------------')
    print(u'计算完成')

    print('----------------------------------------------------------------------------------------')

    print('----------------------------------------------------------------------------------------')

    time.sleep(10)

def write_excel(totals):
    """Write out the collected totals to an excel file
    """
    workbook = Workbook()
    worksheet = workbook.add_sheet('New Sheet')

    # write the header for the first block
    total_hours = 0
    total_tasks = 0

    block_tasks, block_total = write_time_block(worksheet, totals)
    total_hours += block_total
    total_tasks += block_tasks

    block_tasks, block_total = write_time_block(worksheet, totals, block_tasks)
    total_hours += block_total
    total_tasks += block_tasks

    # write out the total hours
    worksheet.write(total_tasks + 6, 0, 'Monthly Total', summary_total_header)
    worksheet.write(total_tasks + 6, 1, total_hours, data_cell)

    # write out the user and date
    name = raw_input('Who is this report for? ')
    worksheet.write(total_tasks + 8, 0, 'Name: %s' % (name))
    worksheet.write(total_tasks + 9, 0, 'Date: %s' % (datetime.strftime(datetime.today(), '%m/%d/%Y')))

    # write the signature field
    worksheet.write(total_tasks + 8, 6, 'Signature:')
    # save the file to disk
    curpath = os.path.dirname(__file__)
    workbook.save(os.path.join(curpath, 'test.xls'))

def write_file(result_list, deal_date, company_name, filename):
    '''
    given a list, put it into excel file.
    deal_date specifies a string which will be rendered as bold
    company_name and filename are self-explanatory
    '''
    w = Workbook()
    sheet = w.add_sheet(company_name)
    row = 2
    boldfont = easyxf(strg_to_parse='font: bold on')
    normalfont = easyxf(strg_to_parse='')
    sheet.write(0, 0, company_name)
    sheet.write(1, 0, 'Date')
    sheet.write(1, 1, 'Open')
    sheet.write(1, 2, 'Close')

    for line in result_list:
        elements = line.decode('utf8').split(',')
        date_string = elements[0]
        open_value = elements[1]
        close_value = elements[4]
        if datetime.strptime(date_string, '%Y-%m-%d') == deal_date:
            style = boldfont
        else:
            style = normalfont
        sheet.write(row, 0, date_string, style)
        sheet.write(row, 1, open_value, style)
        sheet.write(row, 2, close_value, style)
        row += 1

        print(date_string, open_value, close_value)
    w.save(filename)

    def write_entries_to_xl(self, contras):
        xl = Workbook()
        self.xlsh = xl.add_sheet("POSTINGS")
        self.row_in_xlsh = 0

        self.write_batch_no_and_headers()

        sequence = 1
        total_dr, total_cr = 0, 0

        for contra in contras:
            self.write_xl_row(
                (sequence, contra.account.number, abs(contra.amount),
                 contra.dr_cr(), contra.entry_code(), contra.narration,
                 contra.ref, contra.branch_for_itf_int,),
                self.row_in_xlsh)

            sequence += 1
            self.row_in_xlsh += 1
            total_dr += contra.amount if contra.dr_cr() == 'DR' else 0
            total_cr += contra.amount if contra.dr_cr() == 'CR' else 0
            contra.entry.time_processed_for_posting = datetime.now()
            contra.entry.save()

        return xl

 def exportToExcel(self,objectProject):
     
     book = Workbook();
     sheet1 = book.add_sheet('Sheet 1')
     if( objectProject):
         i=0;
         
         row1 = sheet1.row(i) ;
         row1.write(0, ('ประเภท').decode('UTF8') );
         row1.write(1, ('ชื่อโครงการ').decode('UTF8'));
         
         row1.write(2, ('รายละเอืยด').decode('UTF8') );
         row1.write(3, ('งบประมาณรวม').decode('UTF8') );
         row1.write(4, ('งบประมาณ').decode('UTF8') );
         row1.write(5, ('เงินบำรุง').decode('UTF8') );
         row1.write(6, ('งบประมาณอื่น').decode('UTF8') );
         row1.write(7, ('งบประมาณอื่นจาก').decode('UTF8') );
         row1.write(8, ('ผู้รับผิดชอบ').decode('UTF8') );
         row1.write(9, ('กลุ่ม').decode('UTF8') );
         row1.write(10, ('หน่วย/งาน').decode('UTF8') );
         
         i=i+1; 
         style = XFStyle();
         style.num_format_str = '#,##0.00';
         
         for value in  objectProject:
             
             row1 = sheet1.row(i) ;
             
             row1.write(0, value.get('project_type').decode('UTF8') );
             row1.write(1, value.get('project_name').decode('UTF8') );
              
             row1.write(2, value.get('detail').decode('UTF8') );
             row1.write(3, value.get('allBudget') ,style  );
             row1.write(4, value.get('project_budget'  ) ,style  );
             row1.write(5, value.get('maintenance_funds_budget'),style   );
             row1.write(6, value.get('budget_other') ,style  );
            
             if(value.get('budget_other_from')):
                 row1.write(7, value.get('budget_other_from').decode('UTF8') );
             if(value.get('user_name')):
                 row1.write(8, value.get('user_name').decode('UTF8') );
             
             row1.write(9, value.get('division').decode('UTF8') );
             row1.write(10, value.get('section').decode('UTF8') );
              
              
             i=i+1; 
     
      
     dirTempFile = gettempdir() + _os.sep + str('simple.xls');
             
     book.save(dirTempFile);
     #book.save(TemporaryFile());
     
     return dirTempFile;
      
     
     
      

def fwriteinexcel(xlsname, results_gen, results_load_lambda, results_branch):
    book = Workbook()
    Sheet1 = book.add_sheet('Sheet1')

    Sheet1.write(0, 0, 'Bus')
    Sheet1.write(0, 1, 'Generation (MW)')
    Sheet1.write(0, 2, 'Load (MW)')
    Sheet1.write(0, 3, 'Lambda (€/MWh)')
    Sheet1.write(0, 5, 'From Bus')
    Sheet1.write(0, 6, 'To Bus')
    Sheet1.write(0, 7, 'P (MW)')

    for i in range(len(results_gen)):
        Sheet1.write(i+1, 0, results_gen[i, 0])
        Sheet1.write(i+1, 1, results_gen[i, 1])

    compteur = 0
    for j in range(len(results_load_lambda)):
        if j != 15 and j != 16 and j != 32 and j != 38 and j != 39:
            Sheet1.write(compteur+1, 2, results_load_lambda[j, 1])
            Sheet1.write(compteur+1, 3, results_load_lambda[j, 2])
            compteur += 1

    for k in range(len(results_branch)):
        Sheet1.write(k+1, 5, results_branch[k, 0])
        Sheet1.write(k+1, 6, results_branch[k, 1])
        Sheet1.write(k+1, 7, results_branch[k, 2])

    book.save(xlsname)

    def write_report_xl(self, itfs):
        headers = ("S/N", "LC NUMBER", "CUSTOMER NAME",
                   "CCY", "AMOUNT", 'RELATIONSHIP MANAGER')

        xl = Workbook()
        self.xlsh = xl.add_sheet("ITF-REPORT")
        row_in_xlsh = 0

        self.write_xl_row(("", "", "ITF INTEREST REPORT",), row_in_xlsh)
        row_in_xlsh += 1

        self.write_xl_row(headers, row_in_xlsh)
        row_in_xlsh += 1

        sequence = 1
        for itf in itfs:
            self.write_xl_row(
                (sequence, itf.lc_number, itf.customer.name, itf.currency(),
                 itf.amount, itf.rm_name(),),
                row_in_xlsh)
            row_in_xlsh += 1
            sequence += 1

        xl.save(open(itf_report_xl_path, 'wb'))

        return '%d Itfs reported for the given period' % len(itfs)

    def write(self, collection, filename=None, fail_silently=False):
        # create new book
        book = Workbook()

        # write dataset
        for name, dataset in collection.items():
            self._write_dataset(dataset, book)

        # write peakset if there are more than single dataset
        if len(dataset) > 1:
            sheet = book.add_sheet('peakset')
            offsets = [0, 1]
            for name, dataset in collection.items():
                # write classify name
                # Note: +1 for heading line
                sheet.write(offsets[0]+1, 0, get_sheet_name(name))
                # write peakset
                self._write_peakset(dataset, offsets, sheet,
                                    self.peakset_basecolumn,
                                    self.peakset_method,
                                    self.peakset_where_function)
                # update offsets
                offsets[0] += len(dataset) + 1

        # save
        book.save(filename or self.default_filename)

class XLWriter(BookWriter):
    """
    xls, xlsx and xlsm writer
    """
    def __init__(self, file, encoding='ascii',
                 style_compression=2, **keywords):
        """Initialize a xlwt work book


        :param encoding: content encoding, defaults to 'ascii'
        :param style_compression: undocumented, but 2 is magically
                                  better
        reference: `style_compression <https://groups.google.com/
        forum/#!topic/python-excel/tUZkMRi8ITw>`_
        """
        BookWriter.__init__(self, file, **keywords)
        self.wb = Workbook(style_compression=style_compression,
                           encoding=encoding)

    def create_sheet(self, name):
        """Create a xlwt writer"""
        return XLSheetWriter(self.wb, None, name)

    def close(self):
        """
        This call actually save the file
        """
        self.wb.save(self.file)

    def write(self, dirname=None):

        if dirname is None:
            dirname = self.description

        self.create_workbooks()
        dir = self.safe_mkdir(dirname)

        print 'Saving annotation in directory %s' % dir
        for workbook_name, sheets in self.workbooks.items():

            workbook_name = self.escape_name(workbook_name)
            workbook = Workbook()

            for sheet_name, sentences in sorted(sheets.items()):
                sheet_name = self.escape_name(sheet_name)
                sheet = workbook.add_sheet(sheet_name)
                sheet.col(1).width = 0x3000
                sheet.col(3).width = 0x3000

                for index, sentence in enumerate(sentences):
                    self.write_splitted(sentence, sheet, index)

            meta_sheet = workbook.add_sheet('meta')
            meta_sheet.write(0, 0, self.description)
            meta_sheet.write(1, 0, str(datetime.now()))

            outfile = os.path.join(dir, '%s.xls' % workbook_name)
            workbook.save(outfile)

        sentence_file_name = os.path.join(dir, 'sentences.json')
        write_sentence_file(self.sentences, sentence_file_name)

def process_test(request):
    start_date=request.GET.get('start','')
    end_date=request.GET.get('end','')
    
    now = dt.datetime.now().isocalendar()
    this_week_start,this_week_end = get_week_days(now[0],now[1])
    if start_date == '':
        start_date=this_week_start.strftime("%Y-%m-%d")
    if end_date == '':
        end_date=this_week_end.strftime("%Y-%m-%d")
    
    start_date=time.strptime(start_date,'%Y-%m-%d')
    start_date=dt.datetime.fromtimestamp(time.mktime(start_date))
    end_date=time.strptime(end_date,'%Y-%m-%d')
    end_date=dt.datetime.fromtimestamp(time.mktime(end_date))
    end_date=end_date+dt.timedelta(1)
    
    wb = Workbook()
    ws = wb.add_sheet('Sheetname')
    ws.write(0, 0, 'Firstname')
    ws.write(0, 1, 'Surname')
    ws.write(1, 0, 'Hans')
    ws.write(1, 1, 'Muster')

    fname = 'process_excel-testfile.xls'
    response = HttpResponse(mimetype="application/ms-excel")
    response['Content-Disposition'] = 'attachment; filename=%s' % fname

    wb.save(response)

    return response

def write_datatests(crisis_def_list, location = "./out", suffix = ""):
    book = Workbook()
    sheet1 = book.add_sheet('Sheet 1')
    result_crises = combine_crises(crisis_def_list)
    row_num = 0
    for country_code in sort(result_crises.keys()):
        years = sort(list(result_crises[country_code]))
        try:
            len(years)
        except:
            print(years)
            print(country_code)
        sheet1.write(row_num, 0, country_code)
        crisis_row = sheet1.row(row_num)
        crisis_row.write(1, "crisis")
        for j in range(len(years)):
            crisis_row.write(j+2, years[j])
        normal_row = sheet1.row(row_num+1)
        normal_row.write(1, "normal")
        normal_years = pick_normal_years(years)
        for j in range(len(normal_years)):
            normal_row.write(j+2, normal_years[j])
        row_num+=2
    saveloc = os.path.expanduser(location)+suffix+".xls"
    book.save(saveloc)

def configErrorReporting(headers):
  """
  Configure import exception log, which is an Excel spreadsheet in the same 
  format as the input format, but with an extra column added - "Error",
  which contains the error message.
  
  Can only be called after first row of input Excel spreadsheet is read
  to initialize the global, "headers"
  """
  dateFmt = easyxf(
    'font: name Arial, bold True, height 200;',
    #'borders: left thick, right thick, top thick, bottom thick;',
     num_format_str='MM-DD-YYYY'
  )
  
  headerFmt = easyxf(
    'font: name Arial, bold True, height 200;',
  )
  global errorsWorkbook, erroutSheet, erroutRow
  errorsWorkbook = Workbook()
  erroutSheet = errorsWorkbook.add_sheet('Import Errors')

  for colnum in range(0, len(headers)):
    erroutSheet.write(0, colnum, headers[colnum][0], 
    tern(headers[colnum][0]==xlrd.XL_CELL_DATE, dateFmt, headerFmt))
   
  # Add extra column for error message
  erroutSheet.write(0, len(headers), "Error", headerFmt)
  erroutSheet.flush_row_data()
  erroutRow = 1
  errorsWorkbook.save('errors.xls')

    def do_export(self):
        """
        Does actual export. Called from a celery task.
        """
        book = Workbook()
        self.render_book(book)

        temp = NamedTemporaryFile(delete=True)
        book.save(temp)
        temp.flush()

        org_root = getattr(settings, 'SITE_ORGS_STORAGE_ROOT', 'orgs')
        filename = '%s/%d/%s/%s.xls' % (org_root, self.org_id, self.directory, random_string(20))
        default_storage.save(filename, File(temp))

        self.filename = filename
        self.save(update_fields=('filename',))

        subject = "Your export is ready"
        download_url = self.org.make_absolute_url(reverse(self.download_view, args=[self.pk]))

        send_email([self.created_by], subject, 'utils/email/export', {'download_url': download_url})

        # force a gc
        import gc
        gc.collect()

def fog1270():
    """
    Create an xls with active evaluators emails by language
    """
    from xlwt import Workbook
    rates = tst.EvaluatorRate.query.join('evaluator','service_provider','contact').\
                filter_by(active=True).all()
    language_dict = {}
    for rate in rates:
        language_dict.setdefault(rate.language,[]).\
    append(rate.evaluator.service_provider.contact.get_email_address())

    distro = Workbook()
    row = 0

    active = distro.add_sheet('Active Evaluators')

    for language in language_dict:
        emails = language_dict[language]
        emails = list(set(emails))
        email_str = ''
        for email in emails:
            email_str = email_str + '%s;' % email
        email_str = email_str[:-1]
        active.write(row, 0, language.name)
        active.write(row, 2, email_str)
        row+=1

    distro.save('evaluators.xls')

def excel(data, name):
    book = Workbook()
    title_info = []
    for i in data:
        for k, v in i.items():
            title_info.append(k)
    title = list(set(title_info))
    if len(data) < 65535:
        excel = book.add_sheet(name, cell_overwrite_ok=True)
        for x in range(len(title)):
            excel.write(0, x, title[x].decode('UTF-8'))

        for i in range(len(data)):
            for j in range(len(data[i])):
                excel.write(i + 1, j, data[i][title[j]])
    else:
        page = len(data) / 65535
        for k in range(1, page + 2):
            excel = book.add_sheet(name + '_' + str(k), cell_overwrite_ok=True)
            for x in range(len(title)):
                excel.write(0, x, title[x].decode('UTF-8'))
            if k < page + 1:
                for i in range(65535 * (k - 1), 65535 * k):
                    for j in title:
                        excel.write(i - 65535 * (k - 1) + 1, j, data[i][title[j]])
            else:
                for i in range(65535 * k, len(data)):
                    for j in title:
                        excel.write(i - 65535 * k + 1, j, data[i][title[j]])

    date = datetime.datetime.now().strftime('%Y%m%d')
    book.save('../static/' + name + '.' + date + '.xls')

def tmz():
    from xlwt import Workbook
    rates = tst.EvaluatorRate.query.join('evaluator','service_provider','contact').\
                filter_by(active=True).all()
    language_dict = {}
    for rate in rates:
        language_dict.setdefault(rate.language,[]).\
    append(rate.evaluator.service_provider.contact.time_zone)

    distro = Workbook()
    row = 0

    active = distro.add_sheet('Active Evaluators')

    for language in language_dict:
        tmzs = language_dict[language]
        tmzs = list(set(tmzs))
        tmz_str = ''
        for tmz in tmzs:
            tmz_str = tmz_str + '%s;' % tmz
        tmz_str = tmz_str[:-1]
        active.write(row, 0, language.name)
        active.write(row, 2, tmz_str)
        row+=1

    distro.save('evaluators.xls')