How to i transpose rows and columns in Django excel export file

Question

Here is the sample code which i am struggling to transpose the data

def export_users_xls(request):
   response = HttpResponse(content_type='application/ms-excel')
   response['Content-Disposition'] = 'attachment; filename="users.xls"'

  wb = xlwt.Workbook(encoding='utf-8')
  ws = wb.add_sheet('Users')

   # Sheet header, first row
   row_num = 0

   font_style = xlwt.XFStyle()
   font_style.font.bold = True

   columns = ['Username', 'First name', 'Last name', 'Email address', ]

   for col_num in range(len(columns)):
    ws.write(row_num, col_num, columns[col_num], font_style)

   # Sheet body, remaining rows
   font_style = xlwt.XFStyle()

    rows = User.objects.all().values_list('username', 'first_name', 'last_name', 'email')
   for row in rows:
    row_num += 1
    for col_num in range(len(row)):
        ws.write(row_num, col_num, row[col_num], font_style)

   wb.save(response)
   return response

All line indentation are correct My goal is to create a file in excel format from the above code it's fine but the problem is rows and columns are not inter changing

If you people suggest me any other library to do excel export file that can able to transpose the data

score 1 · Answer 1 · answered Dec 20 '17 at 07:25

1

Assuming you just want to transpose data in a sheet (And assuming django-excel has this same function):

import pyexcel as p
my_dic = { "col1": [1, 2, 3], "col2": [4, 5, 6], "col3": [7, 8, 9]}
sheet = p.get_sheet(adict=my_dic)

# sheet now is this:
# pyexcel_sheet1:
# +------+------+------+
# | col1 | col2 | col3 |
# +------+------+------+
# | 1    | 4    | 7    |
# +------+------+------+
# | 2    | 5    | 8    |
# +------+------+------+
# | 3    | 6    | 9    |
# +------+------+------+

sheet.transpose()

# sheet now is this:
# pyexcel_sheet1:
# +------+---+---+---+
# | col1 | 1 | 2 | 3 |
# +------+---+---+---+
# | col2 | 4 | 5 | 6 |
# +------+---+---+---+
# | col3 | 7 | 8 | 9 |
# +------+---+---+---+

answered Dec 20 '17 at 07:25

nakiya

14,063
21
79
118

after doing above code i got this error Exception Value: 'Sheet' object has no attribute 'get' – Access_Doomer Dec 20 '17 at 09:41
@Access_Doomer, there is something wrong with your input data, where a dictionary is expected. – chfw Jan 02 '18 at 13:32
rows = User.objects.all().values_list('username', 'first_name', 'last_name ....... – Access_Doomer Jan 03 '18 at 14:48

score 1 · Accepted Answer · answered Dec 20 '17 at 13:21

def export_users_xls(request):
   response = HttpResponse(content_type='application/ms-excel')
   response['Content-Disposition'] = 'attachment; 
 filename="users.xls"'

  wb = xlwt.Workbook(encoding='utf-8')
  ws = wb.add_sheet('Users')

   # Sheet header, first row
   row_num = 0

  font_style = xlwt.XFStyle()
  font_style.font.bold = True

  columns = ['Username', 'First name', 'Last name', 'Email address', ]

  for col_num in range(len(columns)):
    ws.write(col_num, row_num, columns[col_num], font_style)

  # Sheet body, remaining rows
 font_style = xlwt.XFStyle()

   rows = User.objects.all().values_list('username', 'first_name', 'last_name', 'email')
 for row in rows:
  row_num += 1
  for col_num in range(len(row)):
      ws.write(col_num+1, row_num, row[col_num], font_style)

 wb.save(response)
 return response

All lineindentation are correct

How to i transpose rows and columns in Django excel export file

2 Answers2