Python 2.7如何将结果写入xlsx文件[复制]

Question

我有一些读物：

ATCAAGTCCGTAGTACGGGAATGCAAAAAAAA GGGCTAGTAGGATTGGCCTAGTCACTAGGGGGG TAGCTAGGTAGGATGCCTAGTCAGCGG

...

...

现在，我想将每个读取和写入文件左侧的12个碱基剪切：

f2 = open("./read1.csv","w")
with open('../001.fastq') as reader:
     for index, line in enumerate(reader):
         if index % 4 == 1:
             f2.write(line[:12]+'\n')
f2.close()

我想知道如何编写xlsx文件

Answer 1

假设你在视窗系统上安装了Excel。有多个库可以从python中使用Excel。我只使用了win32com.client，但我对它很满意。我想默认情况下它带有蟒蛇，但如果没有，你可以从这里下载：https://github.com/mhammond/pywin32/releases（别忘了选择合适的版本/架构）。以下是最重要功能的迷你参考：

from win32com.client import Dispatch  # import the necessary library

xlApp = Dispatch("Excel.Application") # starts excel
xlApp.Visible = False                 # hides window (this makes things a bit faster)
xlBook = xlApp.Workbooks.Open( fullPathToXlsx ) # opens an existing workbook

xlSheet = xlBook.Sheets(3)            # which sheet you want to use, indexing starts from 1!
print [sheet.Name for sheet in xlBook.Sheets] # if it is not the one you want you can see their order this way
xlSheet = xlBook.Sheets(sheetName)    # or you can use its name

xlSheet.Cells(row, col).Value = newValue # newValue is what you want to assign to the cell, row and col indexing starts from 1

xlApp.DisplayAlerts = False           # turns off any affirmation popup
xlBook.Save()                         # saving workbook
xlBook.SaveAs(newFullPath)            # saving as...

xlBook.Close()                        # close workbook
xlApp.Quit()                          # exit application

您可能需要在列/行索引和字母表示之间进行转换（我指的是左上角单元格的'A1'等等）。以下是我用过的：

def cellStringToNum(aStr):
    """
    returns an excel sheet cell reference as numbers in a list as [col, row]
    """
    import string

    colS = ""
    for i in xrange(len(aStr)):
        if aStr[i] in string.ascii_uppercase:
            colS += aStr[i]
        else:
            row = int(aStr[i:])
            break
    col = 0
    for i in xrange(len(colS)):
        col += (string.ascii_uppercase.find(colS[len(colS)-1-i])+1)*(26**i)
    return [col, row]

def cellNumToString(col, row):
    import string

    colS = string.ascii_uppercase[col % 26 - 1]
    if col % 26 == 0:
        col -= 26
    else:
        col -= col % 26
    while col != 0:
        col /= 26
        colS = string.ascii_uppercase[col % 26 - 1] + colS
        if col % 26 == 0:
            col -= 26
        else:
            col -= col % 26
    return colS+str(row)

编辑

但是这个问题已经在这里得到了回答：Python-写入Excel电子表格

Answer 2

每个读取有4行

@

ATCAAGTCCGTAGTACGGGAATGCAAAAAA

JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ

@

GGGGCTAGGTAGGGATTGCCTAGTCAACTGGGGGGG

JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ

@

TAGCTAGGTAGGGATTGCCTAGTCAACTGGCCCGG

JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ

对于这个例子，输出是：

ATCAAAGTCCCG

GGGCTAGGTAGG

TAGCTAGGTAGG