def download_as_excel(data):
    output = StringIO.StringIO()
    wb = Workbook(output)
    ws = wb.add_worksheet("Sheet 1")
    first_row = 0
    for header in data['table_header']:

        col = data['table_header'].index(header) # we are keeping order.
        if header in ['order__'+each for each in OrderHeader._meta.get_all_field_names()]:
            header=header.split('__')[1]
            header=header.replace('_',' ')
            header=header.capitalize()
            print 'header----',header
        else:
            
            header=header.capitalize()
            header=header.replace('_',' ')
        ws.write(first_row,col,header) # we have written first row which is the header of worksheet also.
    srow=1

    for row in data['table_data']:

        for _key,_value in row.items():
            col = data['table_header'].index(_key)
            try:
                ws.write(srow,col,_value)
            except Exception, e:

                ws.write(srow,col, json.dumps(_value))

        srow += 1 #enter the next row

def exportExcelDirect(request, adict):
    try:
        l_parm = adict['ex_parm']
        l_parm['title']  # : '文档标题', 第一行各列合并居中写入title
        l_parm['cols']   # :['列名1','列名2','列名3'], //列标题
        l_parm['rows']   # :[      ['行1列1','行1列2','行1列3'],       #         ['行2列1','行2列2','行2列3'],
        import io
        from xlsxwriter import Workbook
        output = io.BytesIO()
        workbook = Workbook(output)
        worksheet = workbook.add_worksheet('export')
        worksheet.write('A1',l_parm['title'])
        # Text with formatting.              ..........  write( row, cols, content )
        for i in range(len(l_parm['cols'])):
            worksheet.write(1, i, l_parm['cols'][i])
        for i in range(len(l_parm['rows'] )):
            for j in range(len(l_parm['rows'][i])):
                worksheet.write(i + 2, j, l_parm['rows'][i][j])
        workbook.close()
        output.seek(0)
        response = HttpResponse(output.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    except Exception as e:
        ls_err = str(e.args)
        response = HttpResponse(str("导出excel失败：" + ls_err), content_type="application/text")

    response['Content-Disposition'] = "attachment; filename=test.xlsx"
    return response

def action(args):

    book = Workbook()
    filetype = args.type
    (infiles, ) = args.infiles
    if filetype == 'Analysis':
        tabs = ['0_QC', '1_QC_Metrics', '2_QC_by_Gene', '3_QC_by_Exon',
                '4_SV_Gridss', '5_SV_Breakdancer', '6_SV_Pindel',
                '7_CNV_Gene', '8_CNV_Exon', '9_Clinically_Flagged', 
                '10_SNP','11_INDEL', '12_MSI', '13_Amplicons', '14_PolyHunter']
        #    for each tab, find its file and process.
        for tab in tabs:
            try:
                #Find file in infiles
                sheet_name, fname = process_files(infiles, tab, filetype)
                print sheet_name, fname
                write_workbook(sheet_name, book, fname)
            except TypeError:
                print "Tab %s not processed" % tab
    elif filetype == 'Combined':
        for fname in infiles:
            (f_path, f_name) = os.path.split(fname)
            if re.search(str(filetype), f_name):
                (f_short_name, f_extension) = os.path.splitext(f_name)
                sheet_name = f_short_name.split('Combined_')
                sheet_name = '_'.join(sheet_name[1:30])
                print sheet_name, fname
                write_workbook(sheet_name, book, fname)
    book.filename=args.outfile

    book.close()

def write_cell_errors(xlsxname, header, lines, cells_to_mark):
    workbook  = Workbook(join(SPREADSHEETS_DIR, xlsxname))
    cell_fmt  = workbook.add_format()
    cell_fmt.set_bg_color('cyan')
    worksheet = workbook.add_worksheet()
    write_sheet(worksheet, cell_fmt, header, lines, cells_to_mark)
    workbook.close()

def dump_info_xlsx():
    from xlsxwriter import Workbook
    
    wb = Workbook(configuration['outputfile'] + '.xlsx')
    ws = wb.add_worksheet("CollatedInfo")
    
    ordered_list = 'Name,Matric,Team,MainLineCount,MainWordCount,TestLineCount,TestWordCount,DocsLineCount,DocsWordCount,MainFile,TestFile,DocsFile'.split(",")
    
    row = 0
    for header in ordered_list:
        col = ordered_list.index(header)
        ws.write(row, col, header)
        
    row = 1
    
    for matric in sorted(studentlist.keys()):
        for k , v in studentlist[matric].items():
            if k == 'MainFile':
                pass
            elif k == 'Main':
                pass
            elif k == 'TestFile':
                pass
            elif k == 'Test':
                pass
            col = ordered_list.index(k)
            ws.write(row, col, v)
        row += 1
        
    wb.close()

def verified(context, collaborator, test, outpath=None):
    """Export variants which have been verified for an institute
        and write them to an excel file.

    Args:
        collaborator(str): institute id
        test(bool): True if the function is called for testing purposes
        outpath(str): path to output file

    Returns:
        written_files(int): number of written or simulated files
    """
    written_files = 0
    collaborator = collaborator or 'cust000'
    LOG.info('Exporting verified variants for cust {}'.format(collaborator))

    adapter = context.obj['adapter']
    verified_vars = adapter.verified(institute_id=collaborator)
    LOG.info('FOUND {} verified variants for institute {}'.format(len(verified_vars), collaborator))


    if not verified_vars:
        LOG.warning('There are no verified variants for institute {} in database!'.format(collaborator))
        return None

    document_lines = export_verified_variants(verified_vars)

    today = datetime.datetime.now().strftime('%Y-%m-%d')
    document_name = '.'.join(['verified_variants', collaborator, today]) + '.xlsx'

    # If this was a test and lines are created return success
    if test and document_lines:
        written_files +=1
        LOG.info('Success. Verified variants file contains {} lines'.format(len(document_lines)))
        return written_files

    # create workbook and new sheet
    # set up outfolder
    if not outpath:
        outpath = str(os.getcwd())
    workbook = Workbook(os.path.join(outpath,document_name))
    Report_Sheet = workbook.add_worksheet()

    # Write the column header
    row = 0
    for col,field in enumerate(VERIFIED_VARIANTS_HEADER):
        Report_Sheet.write(row,col,field)

    # Write variant lines, after header (start at line 1)
    for row, line in enumerate(document_lines,1): # each line becomes a row in the document
        for col, field in enumerate(line): # each field in line becomes a cell
            Report_Sheet.write(row,col,field)
    workbook.close()

    if os.path.exists(os.path.join(outpath,document_name)):
        LOG.info('Success. Verified variants file of {} lines was written to disk'. format(len(document_lines)))
        written_files += 1

    return written_files

 def write_presentation(self, values, records, output):
     workbook = Workbook(output, {'constant_memory': True})
     worksheet = workbook.add_worksheet()
     for column, value in enumerate(values):
         worksheet.write_string(0, column, value)
     for row, record in enumerate(records):
         value_iterator = enumerate([unicode(record[v]) for v in values])
         for column, text in value_iterator:
             worksheet.write_string(row + 1, column, text)
     workbook.close()

 def _open_worksheet(xlsx_file):
     """
     Enable XlsxWriter Worksheet object to be opened, operated on, and
     then automatically closed within a `with` statement. A filename or
     stream object (such as a ``BytesIO`` instance) is expected as
     *xlsx_file*.
     """
     workbook = Workbook(xlsx_file, {"in_memory": True})
     worksheet = workbook.add_worksheet()
     yield workbook, worksheet
     workbook.close()

    def from_nested_iters(cls, data):
        """Builds an XlrdSheetData from nested iterables.

        param data: is nested iterables representing data for each cell.
                    For example, `[('A1', 'B1'), ('A2', 'B2')]` represents two rows and two columns
                    of data.
        """
        workbook = Workbook(BytesIO())
        sheet = workbook.add_worksheet('test')

        for row, row_data in enumerate(data):
            for column, cell_data in enumerate(row_data):
                sheet.write(row, column, cell_data)

        return XlrdSheetData(xlsx_to_reader(workbook).sheet_by_index(0))

def mt_excel_files(store, case_obj, temp_excel_dir):
    """Collect MT variants and format line of a MT variant report
    to be exported in excel format

    Args:
        store(adapter.MongoAdapter)
        case_obj(models.Case)
        temp_excel_dir(os.Path): folder where the temp excel files are written to

    Returns:
        written_files(int): the number of files written to temp_excel_dir

    """
    today = datetime.datetime.now().strftime('%Y-%m-%d')
    samples = case_obj.get('individuals')

    query = {'chrom':'MT'}
    mt_variants = list(store.variants(case_id=case_obj['_id'], query=query, nr_of_variants= -1, sort_key='position'))

    written_files = 0
    for sample in samples:
        sample_id = sample['individual_id']
        sample_lines = export_mt_variants(variants=mt_variants, sample_id=sample_id)

        # set up document name
        document_name = '.'.join([case_obj['display_name'], sample_id, today]) + '.xlsx'
        workbook = Workbook(os.path.join(temp_excel_dir,document_name))
        Report_Sheet = workbook.add_worksheet()

        # Write the column header
        row = 0
        for col,field in enumerate(MT_EXPORT_HEADER):
            Report_Sheet.write(row,col,field)

        # Write variant lines, after header (start at line 1)
        for row, line in enumerate(sample_lines,1): # each line becomes a row in the document
            for col, field in enumerate(line): # each field in line becomes a cell
                Report_Sheet.write(row,col,field)
        workbook.close()

        if os.path.exists(os.path.join(temp_excel_dir,document_name)):
            written_files += 1

    return written_files

def download_as_excel(data):
    output = StringIO.StringIO()
    wb = Workbook(output)
    ws = wb.add_worksheet("Sheet 1")
    first_row = 0
    for header in data['table_header']:
        col = data['table_header'].index(header) # we are keeping order.
        ws.write(first_row,col,header) # we have written first row which is the header of worksheet also.

    srow=1
    for row in data['table_data']:
        for _key,_value in row.items():
            col = data['table_header'].index(_key)
            try:
                ws.write(srow,col,_value)
            except Exception, e:
                ws.write(srow,col, json.dumps(_value))

        srow += 1 #enter the next row

	def get_query_result_excel(self, sql, excelFile, header='True'):
		'''
			Build an excel file from result set of the sql command \n
			Example: get query result excel		select * from customer		test.xlsx
		'''
		
		cur = self._dbconnection.cursor()
		cur.execute(sql + ';')
		rows = cur.fetchall()
		print(cur.description)
		
		workbook = Workbook(excelFile)
		sheet = workbook.add_worksheet()
		format = workbook.add_format({'bold': True})
		
		if header.lower() == 'true':
			for i, val in enumerate([column[0] for column in cur.description]): sheet.write(0, i, self._f(val), format)	#Write header column
		for r, row in enumerate(rows):
			for c, s in enumerate(row): sheet.write(r+1, c, self._f(s)) # Write table data
		workbook.close()

def export_as_excel(request):
    category_id = request.GET['category'] if 'category' in request.GET else None
    data = get_master_product({'category_id': category_id})
    output = StringIO.StringIO()
    wb = Workbook(output)
    ws = wb.add_worksheet("Sheet 1")
    first_row = 0
    for header in data['table_header']:
        col = data['table_header'].index(header) # we are keeping order.
        ws.write(first_row,col,header) # we have written first row which is the header of worksheet also.

    srow=1
    for row in data['table_data']:
        for _key,_value in row.items():
            col = data['table_header'].index(_key)
            try:
                ws.write(srow,col,_value)
            except Exception, e:
                ws.write(srow,col, json.dumps(_value))

        srow += 1 #enter the next row

def dispatch(dataset):
    buf = StringIO()
    wb = Workbook(buf,
                  {'constant_memory': True,
                   'in_memory': True,
                   'default_date_format': 'yyyy/mm/dd hh:mm:ss'}
                 )
    format_ = wb.add_format(FORMAT)

    # dataset metadata
    ws = wb.add_worksheet('Global attributes')
    write_metadata(ws, dataset, 0, 0, format_)

    # 1D grids
    for grid in [g for g in walk(dataset, GridType) if len(g.shape) == 1]:
        logger.debug('Grid {}'.format(grid.name))
        ws = wb.add_worksheet(grid.name)

        # headers
        ws.write(0, 0, grid.dimensions[0], format_)
        ws.write(0, 1, grid.name, format_)

        # data
        for j, data in enumerate(grid.data):
            for i, value in enumerate(numpy.asarray(data)):
                ws.write(i+1, 1-j, value)

        # add var metadata
        write_metadata(ws, grid, 0, 2, format_)

    # sequences
    for seq in walk(dataset, SequenceType):
        logger.debug('Sequence {}'.format(seq.name))

        ws = wb.add_worksheet(seq.name)

        # add header across the first row
        for j, var_ in enumerate(seq.keys()):
            ws.write(0, j, var_, format_)

        # add data in the subsequent rows
        for i, row in enumerate(seq.data):
            for j, value in enumerate(row):
                ws.write(i+1, j, value)

        # add var metadata in columns to the right of the data
        n = 0
        j = len(seq.keys())+1
        for child in seq.children():
            logger.debug("Child {}".format(child.name))
            ws.merge_range(n, j, n, j+1, child.name, format_)
            n = write_metadata(ws, child, n+1, j, format_)+1

    wb.close()
    return buf

def write_excel(date, name, names=None, shift=1, id_col=0, chart_cell='D1', val_col=1):
    workbook = Workbook(name)
    sheet_name = 'TextTiling'
    worksheet = workbook.add_worksheet(sheet_name)
    i = 0
    for value in date:
        if names == None:
            value_name = "%d - %d" % (i + shift, i + shift + 1)
        else:
            value_name = names[i]
        worksheet.write(i, id_col, value_name)
        worksheet.write(i, val_col, value)
        i += 1
    chart = workbook.add_chart({ 'type': 'column'})
    chart.add_series({
        'name': 'Cosines',
        'categories': [sheet_name, 0, id_col, i - 1, id_col],
        'values': [sheet_name, 0, val_col, i - 1, val_col],
    })
    chart.set_size({'width': 920, 'height': 776})
    worksheet.insert_chart(chart_cell, chart)
    workbook.close()

def save_as_excel(folder, vb_dict, header):
    """Save to an Excel file."""
    wb = Workbook(folder + "/combinedcsvs.xlsx")
    ws = wb.add_worksheet("Sheety")

    print(header)
    row = 0
    col = 0

    for title in header:
        ws.write(row, col, title)
        col += 1
    row += 1
    for vb in vb_dict.keys():
        col = 0
        ws.write(row, col, vb)
        col += 1
        for i in range(1, len(vb_dict[vb])):
            ws.write(row, col, vb_dict[vb][i])
            col += 1
        row += 1
    wb.close()

def generate_change_template(request):
    stream = BytesIO()
    book = Workbook(stream)
    bold = book.add_format({'bold': True})
    # create sheet for user input
    sheet = book.add_worksheet('Updates')
    # set headers
    sheet.write('A1', 'category (like destID)', bold)
    sheet.write('B1', 'title (like input cat)', bold)
    sheet.write('C1', 'url (like output cat)', bold)
    rows = 400
    # no idead
    sheet.data_validation(1, 1, rows, 1, {
        'validate': 'list', 
        'source': 'Valid_Categories'
    })

    # set width
    sheet.set_column('A:A', 25)
    sheet.set_column('B:B', 25)
    sheet.set_column('C:C', 25)

    # Create sheet containing data for user validations
    # sheet = book.add_worksheet('Validations')
    # sheet.write('A1', 'Categories', bold)
    categories = Category.objects.all()
    for i, cat in enumerate(categories):
        sheet.write(i + 1, 0, cat.slug)
    # book.define_name('Valid_Categories', '=Validations!$A$2:$A${}'.format(1 + categories.count()))
    # sheet.set_column('A:A', 10)
    # sheet.set_column('B:B', 2)

    book.close()
    stream.seek(0)
    response = HttpResponse(stream,
                            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachement; filename="page_add_template.xlsx"'
    return response

def xlsx_generate(file_name, sheets):
    log.info(' -> %s', file_name)
    with open(file_name, 'w') as fh:
        workbook = Workbook(fh)
        header_format = workbook.add_format({
            'bold': True,
            'border': 1
        })

        for title, rows in sheets:
            if not len(rows):
                continue
            headers = get_headers(rows)
            if not len(headers):
                continue

            worksheet = workbook.add_worksheet(title)
            worksheet.strings_to_numbers = False
            worksheet.strings_to_urls = False
            worksheet.strings_to_formulas = False

            row_idx = 0
            headers = None
            for row in rows:
                if headers is None:
                    headers = row.keys()
                    for c, cell in enumerate(headers):
                        worksheet.write(row_idx, c, cell, header_format)
                    row_idx += 1

                col_idx = 0
                for cell in headers:
                    worksheet.write(row_idx, col_idx, row.get(cell))
                    col_idx += 1
                row_idx += 1

            worksheet.freeze_panes(1, 0)
        workbook.close()

def write_mismatch(filename, etalon, tocomp, mismatch):
    workbook  = Workbook(join(SPREADSHEETS_DIR, get_filebase(filename)+'.xlsx'))
    cell_fmt  = workbook.add_format()
    cell_fmt.set_bg_color('cyan')
    worksheet = workbook.add_worksheet(name='test')
    write_sheet(worksheet, cell_fmt, *tocomp, cells_to_mark=mismatch)
    worksheet = workbook.add_worksheet(name='etalon')
    write_sheet(worksheet, cell_fmt, *etalon)
    workbook.close()

def make_xlsx_response(sheets, basename):
    output = StringIO()
    workbook = Workbook(output)
    header_format = workbook.add_format({
        'bold': True,
        'border': 1
    })

    for title, rows in sheets.items():
        if not len(rows):
            continue

        worksheet = workbook.add_worksheet(title)

        row_idx = 0
        headers = None
        for row in rows:
            if headers is None:
                headers = row.keys()
                for c, cell in enumerate(headers):
                    worksheet.write(row_idx, c, cell, header_format)
                row_idx += 1

            col_idx = 0
            for cell in headers:
                worksheet.write(row_idx, col_idx, row.get(cell))
                col_idx += 1
            row_idx += 1

        worksheet.freeze_panes(1, 0)

    workbook.close()
    output.seek(0)
    fn = basename + '.xlsx'
    headers = {'Content-Disposition': 'attachment; filename=%s' % fn}
    return Response(output, mimetype=XLSX_MIME, headers=headers)