Python excel.ExcelWriter类代码示例

def save_dump(workbook, filename):
    if workbook.worksheets == []:
        workbook.create_sheet()
    writer = ExcelWriter(workbook)
    writer.comment_writer = DumpCommentWriter
    writer.save(filename)
    return True

def main():

    if len(sys.argv) != 3:
        print_help()
        return

    excel_file = Workbook()
    excel_writer = ExcelWriter(workbook=excel_file)
    sheet = excel_file.worksheets[0]

    csv_file = open(sys.argv[1], "r")

    row = 1
    for line in csv_file:
        line = line.strip().decode("utf-8", "ignore")
        ary = line.split("@")
        col = 1

        for item in ary:
            col_letter = get_column_letter(col)
            sheet.cell("%s%s" % (col_letter, row)).value = "%s" % (item.encode("utf-8", "ignore"))
            col = col + 1
        row = row + 1
        
    excel_writer.save(filename=sys.argv[2])

    return

def save_search_data(get_list, session, location=0, num=0):
# location: 是否要获取目标地址,1获取，0不获取
    wb = load_workbook(SAVE_FILE_NAME)
    ew = ExcelWriter(wb)
    ws = wb.get_sheet_by_name(SHEET_NAME)
    for i in get_list:
        i = i.encode('utf-8')
        i += u''
        if location == 0:
            coordinate = None
        else:
            coordinate = get_location(i, session)
        temp_replace = re.compile('<[\s\S]+?>')
        i = temp_replace.sub(' ', i)
        i += u'\n'
        i = i.decode('string-escape').encode('gbk')
        i = unicode(i, 'unicode-escape')
        i = i.encode('utf-8')
        if coordinate:
            num += 1
            longitude = coordinate[0]
            latitude = coordinate[1]
            ws.cell(row=num+1, column=2).value = longitude
            ws.cell(row=num+1, column=3).value = latitude
            ws.cell(row=num+1, column=1).value = i        
        elif num == 0:
            num += 1
            ws.cell(row=num+1, column=1).value = i
    ew.save(SAVE_FILE_NAME)
    print (num)
    return num

def write_to_xlsx_file(content, keys, file_path):
    if os.path.exists(file_path) == False:
        createFile(file_path)

    wb = openpyxl.Workbook()
    ew = ExcelWriter(workbook=wb)
    wsheet = wb.worksheets[0]

    is_close = raw_input('please be sure to close your xlsx file (y/n)?')
    if is_close != 'y':
        print '未关闭文件，退出程序'
        return

    rows = [row for row in wsheet.iter_rows(get_content_range_in_xlsx(keys.__len__() + 1))]
    rows[0][0].value = TITLE_ANDROID_KEY
    rows[0][1].value = TITLE_CN_STRING_VALUE
    rows[0][2].value = TITLE_EN_STRING_VALUE
    rows[0][3].value = TITLE_REMARKS

    for i in range(0, keys.__len__()):
        rows[i + 1][0].value = keys[i]
        rows[i + 1][1].value = content[keys[i]]
        continue
        # print rows[i + 1][0].value, rows[i + 1][1].value

    ew.save(file_path)
    return

def wirteContent(file, data, cell):
    wb = load_workbook(file)
    ew = ExcelWriter(wb)
    ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])
    ws.cell(cell).value = data
    ws.title = os.path.splitext(file)[0]
    ew.save(file)

    def save(self, xls_file):
        """Save log items as Excel files.

        Args:
            xls_file (str): path to the Excel file.
        """
        ew = ExcelWriter(workbook=self.ner_log_workbook)
        ew.save(filename=xls_file)

def txtToCSV(pdf, omit_pages):
    for i, item in enumerate(pdf):
        pages = layout.get_pages(item)
        text = []
        for index, page in enumerate(pages):
            if index+1 not in omit_pages[i]:
                text.append(page)
        text = ''.join(text)
        sents = sent_tokenize(text)
        wb = Workbook()
        ew = ExcelWriter(workbook=wb)
        dest_filename = item.split('.')[0]+'.xlsx'
        ws = wb.worksheets[0]
        for index, line in enumerate(sents):
            ws.cell(row=index, column=0).value = line
        ew.save(filename=dest_filename)

def export(title,header,rows):
    wb = Workbook()  
    ew = ExcelWriter(workbook = wb)  
    dest_filename = r'/opt/aoaola/web/html/excel/' + title +'.xlsx'  
    ws = wb.worksheets[0]  
    
    for x in range(1,len(header)+1):  
        col = get_column_letter(x)  
        ws.cell('%s%s'%(col, 1)).value = '%s' % (header[x-1])        

    for i in range(len(rows)):
        assert len(rows[i]) == 6
        for j in range(len(rows[i])):
            ws.cell(row=i+1,column=j).value = rows[i][j]

    ew.save(filename = dest_filename)  

def write_excel(filename, sheetname,headings, data):
    wb=Workbook()
    ew=ExcelWriter(workbook=wb)
    ws=wb.worksheets[0]
    ws.title=sheetname
    #begin to write the headings
    row=1
    for i,heading in enumerate(headings):
        col=get_column_letter(i+1)
        ws.cell('%s%s'%(col,row)).value='%s'%(heading)

    for row,rowdata in enumerate(data):
        for i,celldata in enumerate(rowdata):
            col=get_column_letter(i+1)
            ws.cell('%s%s'%(col,(row+2))).value='%s'%(celldata)
    ew.save(filename)

    def create(self, tables, pdic):
        doc = Workbook()
        ew = ExcelWriter(workbook=doc)
        for table in tables:
            sheet = doc.worksheets[0]
            sheet.title = table
            i = 0
            for port in tables[table]:
                c = sheet.cell(row=0, column=i)

                c.value = port + "[" + ` pdic[port][2] ` + "]"
                i += 1
        try:
            ew.save(filename=self.file_name)
            return True
        except:
            return False

def test_write_images():
    wb = Workbook()
    ew = ExcelWriter(workbook=wb)
    from openpyxl.drawing import Image
    imagepath = os.path.join(DATADIR, "plain.png")
    img = Image(imagepath)

    buf = BytesIO()

    archive = zipfile.ZipFile(buf, 'w')
    ew._write_images([img], archive, 1)
    archive.close()

    buf.seek(0)
    archive = zipfile.ZipFile(buf, 'r')
    zipinfo = archive.infolist()
    assert len(zipinfo) == 1
    assert zipinfo[0].filename == 'xl/media/image1.png'

class Excel2007Write():  
    excelWriter = path = workBook = sheet = None;  
    def __init__(self, path):  
        self.path = path  
        self.workBook = Workbook()  
        self.excelWriter = ExcelWriter(workbook=self.workBook)  
      
    def createSheet(self, index, name):  
        sheet = self.workBook.worksheets[index]  
        sheet.title = name  
        return sheet  
      
    def set(self, sheet_obj, x, y, value):  
        col = get_column_letter(x + 1)  
        sheet_obj.cell('%s%s' % (col, y + 1)).value = value  
          
    def save(self):  
        self.excelWriter.save(filename=path)  

def test_write_images(datadir):
    datadir.chdir()
    wb = Workbook()
    ew = ExcelWriter(workbook=wb)
    from openpyxl.drawing.image import Image
    img = Image("plain.png")
    wb._images.append(ref(img))

    buf = BytesIO()

    archive = zipfile.ZipFile(buf, 'w')
    ew._write_images(archive)
    archive.close()

    buf.seek(0)
    archive = zipfile.ZipFile(buf, 'r')
    zipinfo = archive.infolist()
    assert len(zipinfo) == 1
    assert zipinfo[0].filename == 'xl/media/image1.png'

    def to_bytes(self, table, **kargs):
        wb = Workbook(optimized_write=True)
        ws = wb.create_sheet()

        # Determine columns. We may need an extra (first) column which 'names' the row
        columns = list(map(str, list(table.get_columns())))
        ws.append(columns)

        # Write rows to worksheet
        for row in table.get_rows():
            ws.append(tuple(_get_values(table, row)))
        writer = ExcelWriter(wb)

        # Need to do a little bit more work here, since the openpyxl library only
        # supports writing to a filename, while we need a buffer here..
        buffer = io.BytesIO()
        with zipfile.ZipFile(buffer, 'w', zipfile.ZIP_DEFLATED) as zf:
            writer.write_data(zf)
        return buffer.getvalue()

def test_write_images(datadir):
    datadir.chdir()
    wb = Workbook()
    ew = ExcelWriter(workbook=wb)
    from openpyxl.drawing import Image

    img = Image("plain.png")

    buf = BytesIO()

    archive = zipfile.ZipFile(buf, "w")
    ew._write_images([img], archive, 1)
    archive.close()

    buf.seek(0)
    archive = zipfile.ZipFile(buf, "r")
    zipinfo = archive.infolist()
    assert len(zipinfo) == 1
    assert zipinfo[0].filename == "xl/media/image1.png"