Thursday, April 28, 2011

RE: This section is converting database table to excel file

Since Excel will import a csv file, why not just write a csv file.  In mysql, while ugly, you could shell down and use the mysql command line options to export a query to csv.

 

From: django-users@googlegroups.com [mailto:django-users@googlegroups.com] On Behalf Of Od-Erdene Ch
Sent: Thursday, April 28, 2011 6:23 AM
To: django-users@googlegroups.com
Subject: This section is converting database table to excel file

 

hi there

this section is converting database table to excel file.

 

problem is when converting database table to excel file (it is containing 6400 [six thousand four hundred] rows) it is spending more than 10 minutes.

that is very slow. is there any idea to make more faster. Help me

 

 

CODE:

 

if request.GET.has_key('excel') :

        if request.GET['excel'] == "true":

            qs = CarDetails.objects.all().order_by(sord + sidx)

            wb = xlwt.Workbook()

            ws = wb.add_sheet(u'CarDetails')

            labels = [u"型式", u"車名", u"車輌詳細コード",u'車体形状',u"類別区分番号",u"車輌重量諸元",u"全長",

                      u"全幅",u"全高",u"用途",u"形状",u"原動機型式",u"総排気量",u"営・自家用区分",

                      u"乗車定員",u"最大積載量",u"車輌総重量",u"燃料の種類",u"乗車定員括弧",u"最大積載量括弧",

                      u"ローター数",u"燃料種類",u"燃料種類括弧 その他"]

            

            ws.write_merge(0, 0, 0, 22, u'車輌マスター管理', get_title_style())

            

            label_style = get_label_style2()

            for i in range(len(labels)) :

                ws.write(1, i, labels[i], label_style)

            ws.col(0).width = get_computed_width(-30) # by pixel

            ws.col(1).width = get_computed_width(150)

            ws.col(2).width = get_computed_width(150)

            ws.col(3).width = get_computed_width(150)

            ws.col(4).width = get_computed_width(150)

            ws.col(5).width = get_computed_width(150)

            ws.col(6).width = get_computed_width(150)

            ws.col(7).width = get_computed_width(150)

            ws.col(8).width = get_computed_width(150)

            ws.col(9).width = get_computed_width(150)

            ws.col(10).width = get_computed_width(150)

            ws.col(11).width = get_computed_width(150)

            ws.col(12).width = get_computed_width(150)

            ws.col(13).width = get_computed_width(150)

            ws.col(14).width = get_computed_width(150)

            ws.col(15).width = get_computed_width(150)

            ws.col(16).width = get_computed_width(150)

            ws.col(17).width = get_computed_width(150)

            ws.col(18).width = get_computed_width(150)

            ws.col(19).width = get_computed_width(150)

            ws.col(20).width = get_computed_width(150)

            ws.col(21).width = get_computed_width(150)

            ws.col(22).width = get_computed_width(150)

            today = datetime.strftime(datetime.today(),"%Y-%m-%d")

            rown = 2

            simple_style = get_simple_style()

 

            for list in qs :

                car_maker_code = ""

                car_maker = ""

                car_name2 = ""

                tax = ""

                model = ""

                car_name = ""

                name = ""

                body = ""

                class_type_number = ""

                weight = ""

                length = ""

                width = ""

                height = ""

                usage = ""

                view_type = ""

                motor_type = ""

                totol_piston_cc = ""

                is_own = ""

                human_size = ""

                kg_size = ""

                totol_weight = ""

                fuel_type = ""

                human_size_kakko = ""

                kg_size_kakko = ""

                rotor_number = ""

                fuel_type_kakko = ""

                fuel_type_kakko_sonota = ""

                if list.model: model = list.model

                if list.carname: car_name = list.carname

                if list.name: name = list.name

                if list.body: body = list.body

                if list.class_type_number: class_type_number = list.class_type_number

                if list.weight: weight = list.weight

                if list.length: length = list.length

                if list.width: width = list.width

                if list.height: height = list.height

                if list.usage: usage = list.get_usage_display()

                if list.view_type: view_type = list.view_type

                if list.motor_type: motor_type = list.motor_type

                if list.totol_piston_cc: totol_piston_cc = list.totol_piston_cc

                if list.is_own: is_own = list.get_is_own_display()

                if list.human_size: human_size = list.human_size

                if list.kg_size: kg_size = list.kg_size

                if list.totol_weight: totol_weight = list.totol_weight

                if list.fuel_type: fuel_type = list.get_fuel_type_display()

                if list.human_size_kakko: human_size_kakko = list.human_size_kakko

                if list.kg_size_kakko: kg_size_kakko = list.kg_size_kakko

                if list.rotor_number: rotor_number = list.rotor_number

                if list.fuel_type_kakko: fuel_type_kakko = list.fuel_type_kakko

                if list.fuel_type_kakko_sonota: fuel_type_kakko_sonota = list.fuel_type_kakko_sonota

                 

                ws.write(rown, 0, unicode(model), simple_style)

                ws.write(rown, 1, unicode(car_name), simple_style)

                ws.write(rown, 2, unicode(name), simple_style)

                ws.write(rown, 3, unicode(body), simple_style)

                ws.write(rown, 4, unicode(class_type_number), simple_style)

                ws.write(rown, 5, unicode(weight), simple_style)

                ws.write(rown, 6, unicode(length), simple_style)

                ws.write(rown, 7, unicode(width), simple_style)

                ws.write(rown, 8, unicode(height), simple_style)

                ws.write(rown, 9, unicode(usage), simple_style)

                ws.write(rown, 10, unicode(view_type), simple_style)

                ws.write(rown, 11, unicode(motor_type), simple_style)

                ws.write(rown, 12, unicode(totol_piston_cc), simple_style)

                ws.write(rown, 13, unicode(is_own), simple_style)

                ws.write(rown, 14, unicode(human_size), simple_style)

                ws.write(rown, 15, unicode(kg_size), simple_style)

                ws.write(rown, 16, unicode(totol_weight), simple_style)

                ws.write(rown, 17, unicode(fuel_type), simple_style)

                ws.write(rown, 18, unicode(human_size_kakko), simple_style)

                ws.write(rown, 19, unicode(kg_size_kakko), simple_style)

                ws.write(rown, 20, unicode(rotor_number), simple_style)

                ws.write(rown, 21, unicode(fuel_type_kakko), simple_style)

                ws.write(rown, 22, unicode(fuel_type_kakko_sonota), simple_style)

                rown += 1

            result = StringIO()

            wb.save(result)

            result.seek(0)

            response = HttpResponse(result.read(), mimetype='application/ms-excel')

            response['Content-Disposition'] = 'attachment; filename=Car_details_%s.xls' % today

            return response 

 

 

thanks

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

No comments:

Post a Comment