Dataframe to Excel, with no border formatting, and auto column width

In writing dataframe to Excel spreadsheet, I want the file to have no border on the first row and column width to be auto adjusted.

With the old package xlwt and xlrd, I can read the file from Pandas produced, to a new file with no border on the header. But it’s .xls (not .xlsx) format.

I can’t make the column width auto adjusted (according to the content of the cells).

The lines looked tedious and troublesome.

What is the Pandas way to do so?

import pandas as pd
from io import StringIO

csvfile = StringIO(
"""Group    Sort    String
123 207 1st line
123 208 2nd line
123 209 3rd line
123 210 4th line
456 625 a1st line
456 626 a2nd line
456 627 a3rd line
""")

df = pd.read_csv(csvfile, sep = 't', engine='python')
df.to_excel("C:\TEM\123.xlsx", index = False)

from xlwt import Workbook,easyxf,Formula
import xlrd
import xlwt
import xlsxwriter
from xlrd import open_workbook

style = easyxf('borders: left no_line, right no_line, top no_line, bottom no_line;')

old_file = open_workbook("C:\TEM\123.xlsx")
old_sheet = old_file.sheet_by_index(0)

new_file = xlwt.Workbook(encoding='utf-8', style_compression = 0)
new_sheet = new_file.add_sheet('Sheet1', cell_overwrite_ok = True)

row_data = []
for row_index in range(old_sheet.nrows):
    rows = old_sheet.row_values(row_index)
    row_data.append(rows)

for row_index, row in enumerate(row_data):
    for col_index, cell_value in enumerate(row):
        new_sheet.write(row_index, col_index, cell_value, style)

new_file.save('C:\TEM\456.xls')

Answer

For the first question, use:

df.T.reset_index().T

Demonstration:

df.to_excel('test1.xlsx', index=None)

df.T.reset_index().T.to_excel('test2.xlsx', header=None, index=None)

Advertisement

Answer