Python creates Map to write Excel table instance parsing, pythonmap

Python creates Map to write Excel table instance parsing, pythonmap

This article mainly studies the code for creating a Map to write an Excel table using the Python language. The details are as follows.

We are very skilled in writing scripts related to Excel tables. The general operation is to retrieve data from the database, create an Excel template, and create a new Excel table based on the template to write data in the database. Finally, send the email. The previous record blog was well written. Here we will talk about some new problems.

When we wrote a similar script, we didn't think about it. Why should we create a template and then write data? Eh... In fact, it is not a matter of consideration, but it is just a matter of laziness. Just hurry up and complete the task...

Here we will discuss this issue!

[Why create an Excel table template ?]

Creating an Excel template involves historical data. Why?

If we need a data table, this table is the data of this month and runs one row every day. In the next month, you need to create a table for the next month.

In this way, we only need to get the data table on the last day of each month for data statistics, because the data table on the last day contains all the data of the current month.

For such a requirement, when the code in the script changes in the month, you must create a new table and no longer use the original table as the template.

Naturally, the table of the next month cannot include the data of the previous month. Therefore, you must write a new table header.

I think deeply here, and another problem is involved.

[Essence of creating an Excel template]

Def createTemplateExcel (): ''' create an Excel file template ''' wb = xlwt. workbook (encoding = "UTF-8", style_compression = True) sht0 = wb. add_sheet ("online", cell_overwrite_ OK = True) sht0.col (0 ). width = 4000 sht0.write (0, 0, 'game name', style1) sht0.write (0, 1, 'channel', style1) sht0.write (0, 2, 'transaction volume ', style1) sht0.write (0, 3, 'amount of orders below ', style1) sht0.write (0, 4, 'amount of failed', style1) sht1 = wb. add_sheet ("offline", cell_overwrite_ OK = True) sht1.col (0 ). width = 4000 sht1.write (0, 0, 'game name', style1) sht1.write (0, 1, 'transaction volume ', style1) sht1.write (0, 2, 'order weight', style1) sht1.write (0, 3, 'failed', style1) return wb or wb. save (tempFileName)

What has this so-called template creation method done?
Just create a sheet with a custom name and write the table header into it. Therefore, the template creation method is essentially to write the table header.

We can return wb or wb. save (tempFileName)

That is, we can directly return the written header, and use the following data writing method.

Alternatively, you can directly save a template Excel table and use it in the script background. Call the generated template every time you need to write a new table.

The problem here can be further solved...

[Is it necessary to save the template file with the so-called template creation method for writing the header ?]

It is actually a balance problem. Each time we call a method to write the table header, the running time is consumed. Or directly call the template file that has already been written? Direct calling is fast, but the template file occupies space in the script background.

How can it be cost-effective? Select time or space?

In addition, is writing slower than calling? If writing is faster than calling, it saves both time and space, but the code only adds a method to write the table header.

It also facilitates later maintenance.

Here, there is a conflict. Now that we have an operation to write the table header. Do you still need to save it as a template file?

Is it okay to write data directly during each operation? Why do I need to save it?

In addition to space occupation, it can be used for calling (in essence, it can be directly written without calling). What is the purpose?

This is something worth improving... Right

[Personal temporary solutions related to template creation methods]

Currently, I can think of a way to save the template file in the future. Writing a table header is to write the table header, and return directly. You can concatenate it with the following method.

When the month changes, that is, when the first table of the month is written on the first day of the month, you can call the method of writing the table header.

Okay, the first question is over. Next I will discuss the second question?

[Fetch data from the database, process the data, and then write it again]

When writing an Excel table, most of the situations are that the obtained data is directly written to the corresponding position of the Excel table.

The same method template is used to retrieve data. Data processing occurs when data is written. There are two situations that need to be handled:

1. Excel functions, such as SUM, need to be called. You need to use this

xlwt.Formula

2. Data must be processed in addition to computation. This is what we encounter when dealing with the needs of this example.

[Requirement]

Obtain the transaction volume, order volume, and failure volume of each game in the database. Write a table as follows:

The key issue to be dealt with is: three pieces of data for each game, each of which is obtained by a separate SQL statement, that is, each item is retrieved separately and sorted differently.

If you write data directly to an Excel table

As you can see, sorting is messy. We need to process the data by name and generate a table shown in the first figure.

What method does data processing need?

[How to process data ?]

Previously, we thought of the following methods:

Def writeInfo0 (sht, rs, length, rs2, leng2, rs3, length3): ''' write specific online data ''' for j in range (leng22.): sht. write (j + 1, 0, str (rs2 [j] [0]). decode ('utf-8'), style1) sht. write (j + 1, 1, str (rs2 [j] [1]). decode ('utf-8'), style1) sht. write (j + 1, 3, rs2 [j] [2], style1) for j in range (leng22.): for I in range (length ): if (str (rs [I] [0]) = str (rs2 [j] [0]) and (str (rs [I] [1]) = str (rs2 [j] [1]): sht. write (j + 1, 2, rs [I] [2], style1) for j in range (leng22.): for k in range (length3 ): if (str (rs3 [k] [0]) = str (rs2 [j] [0]) and (str (rs3 [k] [1]) = str (rs2 [j] [1]): sht. write (j + 1, 4, rs3 [k] [2], style1)

Write data directly. Write the most data first, and then write less data. When a few two items are written, they are placed at the corresponding position based on the corresponding name.
This can be written, but there are two problems:

1. We need to display zero where there is no data. How to judge and write data? This is a problem, and it is quite troublesome.

2. We need complete data, that is, can we ensure that the game in the most data is all? Whether the data corresponding to each item of data has its own unique game.

If the most game entry data does not contain all, this is a fatal error. Missing data is not a small task.

More than that, we can ensure that the most at this time will be the most tomorrow? If it becomes the least tomorrow, a lot of data will not be written because the name cannot be matched.

Still, data is missing, which is a fatal issue.

Therefore, this method has many vulnerabilities and defects. Not feasible!

[Create a dictionary DICT to process data]

The ideal solution is to extract all the data and put it into a dictionary.

Sort the data during the import process. That is, if this game exists, the value is assigned directly. If not, create a new key and write it.

After the dictionary is created, it contains all the data before writing. Is relatively reliable.

There are two key points for this method:

1. How to handle the data written?

2. How do I write data after DICT is set up?

Let's talk one by one.

[How to Create a DICT to process data during writing ?]

Take the simple offline data writing as an example.

def getInfo1(rs, rs2, rs3): dict={} for i in range(len(rs)):  key=str(rs[i][0])  value=rs[i][1]  dict[key]=[value, 0, 0] dlist=list(dict.keys()) for i in range(len(rs2)):  key2=str(rs2[i][0])  value2=rs2[i][1]  if key2 in dlist:   value=dict[key2][0]  else:   value=0  dict[key2]=[value, value2, 0] dlist=list(dict.keys()) for i in range(len(rs3)):  key3=str(rs3[i][0])  value3=rs3[i][1]  if key3 in dlist:   value=dict[key3][0]   value2=dict[key3][1]  else:   value=0   value2=0  dict[key3]=[value, value2, value3] return dict

The rs data received is a list. A game corresponds to one data type.

In this way, the game name is assigned to the key and the data is assigned to the value. Loop traversal to create a DICT.

Next is the key logic: If yes, assign values directly; if not, create a new key and then write it.

The more critical logic in this key logic is how to determine whether the original key exists.

The solution is as follows:

dlist=list(dict.keys())for i in range(len(rs2)):

if key2 in dlist: value=dict[key2][0] else: value=0 dict[key2]=[value, value2, 0] If yes, the value of the first value is obtained from the DICT in the current state.

If not, the value is 0.

Finally, directly

dict[key2]=[value, value2, 0]

That is, keys are created now. We only need to care about the value.

[Question]

This raises a question. Will duplicate key creation not happen?

No. Because

dict[key2]=[value, value2, 0]

This code, the = symbol. There are two re-operations: assign value and create a new one. If not! Is new. If yes! Is change.

We only need to include the original value in the change. The original value is obtained from the existing DICT... No.

Next, we can understand more complex code.

dlist=list(dict.keys())for i in range(len(rs3)): key3=str(rs3[i][0]) value3=rs3[i][1] if key3 in dlist:  value=dict[key3][0]  value2=dict[key3][1] else:  value=0  value2=0 dict[key3]=[value, value2, value3]return dict

When the third data item appears, DICT is created as shown above.

You need to take into account the two data items that have been written.

[How to Write DICT into an Excel worksheet]

Def writeInfo1 (sht, dict): ''' write offline data ''' dlist = list (dict. keys () for I in range (len (dlist): sht. write (I + 1, 0, str (dlist [I]). decode ('utf-8'), style1) sht. write (I + 1, 1, dict [dlist [I] [0], style1) sht. write (I + 1, 2, dict [dlist [I] [1], style1) sht. write (I + 1, 3, dict [dlist [I] [2], style1)

In fact, it is the method of dictionary layer-by-layer stripping.

The key is to get the data and write it directly!

In a more complex DICT, when processing online data, we have two keys and three values.

We know that the value of the dictionary data type can be an array containing multiple data, but the key can only have one and cannot be an array.

What should I do? We can only combine multiple bonds into one key, and then separate them when writing.

The key issue is merging and splitting.

[Key merging]

key=str(rs[i][0])+'--'+str(rs[i][1])

Yes, that's all. Connect two data items. Merged into a string.

[Split the key when writing]

sht.write(i+1, 0, str(dlist[i]).decode('utf-8').split('--')[0], style1)sht.write(i+1, 1, str(dlist[i]).decode('utf-8').split('--')[1], style1)

Use the split function.

The split function splits strings and returns an array. Separate data by array number.

Now, all the key issues of this requirement have been explained.

The complete code is as follows.

#! /Usr/bin/python #-*-coding: UTF-8-*-_ author _ = "$ Author: wangxin. xie $ "_ version _ =" $ Revision: 1.0 $ "_ date _ =" $ Date: 2018 $ "##################################### ########################## functions: order Status Report #################################### ########################### import sysimport datetimeimport xlwtfrom myyutil. DBUtil import DBUtilreload (sys) sys. setdefaultencoding ('utf8 ')##################### ### Global variables #################################### orderDBUtil = DBUtil ('moyoyo _ Order ') fileDir = 'd: // 'filename= filedir='order_message_test2.xls 'style1 = xlwt. XFStyle () font1 = xlwt. font () font1.height = 220font1. name = 'simsun' style1. font = font1 ##################################### ############################ def createTemplateExcel (): '''create an Excel file template ''' wb = xlwt. workbook (encoding = "UTF-8", style_compre Ssion = True) sht0 = wb. add_sheet ("online", cell_overwrite_ OK = True) sht0.col (0 ). width = 4000 sht0.write (0, 0, 'game name', style1) sht0.write (0, 1, 'channel', style1) sht0.write (0, 2, 'transaction volume ', style1) sht0.write (0, 3, 'amount of orders below ', style1) sht0.write (0, 4, 'amount of failed', style1) sht1 = wb. add_sheet ("offline", cell_overwrite_ OK = True) sht1.col (0 ). width = 4000 sht1.write (0, 0, 'game name', style1) sht1.write (0, 1, 'transaction volume ', style1) sht1.write (0, 2, 'order quantity', style1) sht1.write (0, 3, 'failure quantity', style1) return wbdef genSuccessOrderOnline (): SQL = ''' SELECT. GAME_NAME,. GOODS_NAME, COUNT (B. ORDER_ID) FROM moyoyo_order.BASE_INFO B inner join moyoyoyo_order.account_info a on B. ORDER_ID =. ORDER_ID inner join netgame_trade.GOODS g on. GOODS_ID = G. id left join moyoyo_order.RELATION_INFO r on B. ORDER_ID = R. PARENT_ORDER_ID where B. COMPLETE_DATE> = DATE_SUB (NOW (), INTERVAL 7 DAY) and B. COMPLETE_DATE <NOW () AND A. CS_ADMIN_ID! = 241 and a. GOODS_ID! = 1213 and g. GOODS_TYPE_ID! = 6 and. GOODS_IS_OFFLINE = 0 and. IS_ B2C IS NULL AND (. GOODS_ID IN (1240,124 1, 1608) and B. SELLER_ID IN (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) OR (B. SELLER_ID not in (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) AND R. PARENT_ORDER_ID is null and B. SELLER_ID not in (693356,147 91127) and B. STATUS = 1 and B. TRADE_STATUS = 4 and B. TYPE = 1 group by. GAME_ID, G. GOODS_CH ANNEL_ID order by count (B. ORDER_ID) DESC; ''' rs = orderDBUtil. queryList (SQL, () if not rs: return None return rsdef genGenerateOrderOnline (): SQL = ''' SELECT. GAME_NAME,. GOODS_NAME, COUNT (B. ORDER_ID) FROM moyoyo_order.BASE_INFO B inner join moyoyoyo_order.account_info a on B. ORDER_ID =. ORDER_ID inner join netgame_trade.GOODS g on. GOODS_ID = G. id left join moyoyo_order.RELATION_INFO r on B. ORDER_ID = R. PARENT_ORDER_ID where B. CREATED_DATE> = DATE_SUB (NOW (), INTERVAL 7 DAY) and B. CREATED_DATE <NOW () AND A. CS_ADMIN_ID! = 241 and a. GOODS_ID! = 1213 and g. GOODS_TYPE_ID! = 6 and. GOODS_IS_OFFLINE = 0 and. IS_ B2C IS NULL AND (. GOODS_ID IN (1240,124 1, 1608) and B. SELLER_ID IN (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) OR (B. SELLER_ID not in (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) AND R. PARENT_ORDER_ID is null and B. SELLER_ID not in (693356,147 91127) and B. STATUS = 1 and B. TYPE = 1 group by. GAME_ID, G. GOODS_CHANNEL_ID ORDER BY COUNT (B. ORDER_ID) DESC; ''' rs = orderDBUtil. queryList (SQL, () if not rs: return None return rsdef genFailOrderOnline (): SQL = ''' SELECT. GAME_NAME,. GOODS_NAME, COUNT (B. ORDER_ID) FROM moyoyo_order.BASE_INFO B inner join moyoyoyo_order.account_info a on B. ORDER_ID =. ORDER_ID inner join netgame_trade.GOODS g on. GOODS_ID = G. id left join moyoyo_order.RELATION_INFO r on B. ORDER_ID = R. PARENT_ORDER_ I D where B. COMPLETE_DATE> = DATE_SUB (NOW (), INTERVAL 7 DAY) and B. COMPLETE_DATE <NOW () AND A. CS_ADMIN_ID! = 241 and a. GOODS_ID! = 1213 and g. GOODS_TYPE_ID! = 6 and. GOODS_IS_OFFLINE = 0 and. IS_ B2C IS NULL AND (. GOODS_ID IN (1240,124 1, 1608) and B. SELLER_ID IN (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) OR (B. SELLER_ID not in (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) AND R. PARENT_ORDER_ID is null and B. SELLER_ID not in (693356,147 91127) and B. STATUS = 1 and B. TRADE_STATUS = 5 and B. TYPE = 1 group by. GAME_ID, G. GOODS_CH ANNEL_ID order by count (B. ORDER_ID) DESC; ''' rs = orderDBUtil. queryList (SQL, () if not rs: return None return rsdef genSuccessOrderOffline (): SQL = ''' SELECT. GAME_NAME, COUNT (B. ORDER_ID) FROM moyoyo_order.BASE_INFO B inner join moyoyoyo_order.account_info a on B. ORDER_ID =. ORDER_ID inner join netgame_trade.GOODS g on. GOODS_ID = G. id left join moyoyo_order.RELATION_INFO r on B. ORDER_ID = R.P ARENT_ORDER_ID where B. COMPLETE_DATE> = DATE_SUB (NOW (), INTERVAL 7 DAY) and B. COMPLETE_DATE <NOW () AND A. CS_ADMIN_ID! = 241 and a. GOODS_ID! = 1213 and g. GOODS_TYPE_ID! = 6 and. GOODS_IS_OFFLINE = 1 and. IS_ B2C IS NULL AND (. GOODS_ID IN (1240,124 1, 1608) and B. SELLER_ID IN (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) OR (B. SELLER_ID not in (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) AND R. PARENT_ORDER_ID is null and B. SELLER_ID not in (693356,147 91127) and B. STATUS = 1 and B. TRADE_STATUS = 4 and B. TYPE = 1 group by. GAME_ID ORDER BY CO UNT (B. ORDER_ID) DESC; ''' rs = orderDBUtil. queryList (SQL, () if not rs: return None return rsdef genGenerateOrderOffline (): SQL = ''' SELECT. GAME_NAME, COUNT (B. ORDER_ID) FROM moyoyo_order.BASE_INFO B inner join moyoyoyo_order.account_info a on B. ORDER_ID =. ORDER_ID inner join netgame_trade.GOODS g on. GOODS_ID = G. id left join moyoyo_order.RELATION_INFO r on B. ORDER_ID = R. PARENT_ORDER_ID WHER E B. CREATED_DATE> = DATE_SUB (NOW (), INTERVAL 7 DAY) and B. CREATED_DATE <NOW () AND A. CS_ADMIN_ID! = 241 and a. GOODS_ID! = 1213 and g. GOODS_TYPE_ID! = 6 and. GOODS_IS_OFFLINE = 1 and. IS_ B2C IS NULL AND (. GOODS_ID IN (1240,124 1, 1608) and B. SELLER_ID IN (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) OR (B. SELLER_ID not in (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) AND R. PARENT_ORDER_ID is null and B. SELLER_ID not in (693356,147 91127) and B. STATUS = 1 and B. TYPE = 1 group by. GAME_ID order by count (B. ORDER_ID) DESC ;' ''Rs = orderDBUtil. queryList (SQL, () if not rs: return None return rsdef genFailOrderOffline (): SQL = ''' SELECT. GAME_NAME, COUNT (B. ORDER_ID) FROM moyoyo_order.BASE_INFO B inner join moyoyoyo_order.account_info a on B. ORDER_ID =. ORDER_ID inner join netgame_trade.GOODS g on. GOODS_ID = G. id left join moyoyo_order.RELATION_INFO r on B. ORDER_ID = R. PARENT_ORDER_ID where B. COMPLETE_DATE> = DATE_S UB (NOW (), INTERVAL 7 DAY) and B. COMPLETE_DATE <NOW () AND A. CS_ADMIN_ID! = 241 and a. GOODS_ID! = 1213 and g. GOODS_TYPE_ID! = 6 and. GOODS_IS_OFFLINE = 1 and. IS_ B2C IS NULL AND (. GOODS_ID IN (1240,124 1, 1608) and B. SELLER_ID IN (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) OR (B. SELLER_ID not in (SELECT MEMBER_ID FROM netgame_trade. B2C _MEMBER) AND R. PARENT_ORDER_ID is null and B. SELLER_ID not in (693356,147 91127) and B. STATUS = 1 and B. TRADE_STATUS = 5 and B. TYPE = 1 group by. GAME_ID order by count (B. ORDER_ID) DESC; ''' rs = orderDBUtil. queryList (SQL, () if not rs: return None return rsdef getInfo0 (rs, rs2, rs3): dict ={} for I in range (len (rs )): key = str (rs [I] [0]) + '--' + str (rs [I] [1]) value = rs [I] [2] dict [key] = [value, 0, 0] dlist = list (dict. keys () for I in range (len (rs2): key2 = str (rs2 [I] [0]) + '--' + str (rs2 [I] [1]) value2 = rs2 [I] [2] if key2 in dlist: value = dict [key2] [0] else: value = 0 dict [key2] = [value, value2, 0] dlist = list (dict. keys () for I in range (len (rs3): key3 = str (rs3 [I] [0]) + '--' + str (rs3 [I] [1]) value3 = rs3 [I] [2] if key3 in dlist: value = dict [key3] [0] value2 = dict [key3] [1] else: value = 0 value2 = 0 dict [key3] = [value, value2, value3] return dictdef writeInfo0 (sht, dict): ''' write specific online data ''' dlist = list (dict. keys () for I in range (len (dlist): sht. write (I + 1, 0, str (dlist [I]). decode ('utf-8 '). split ('--') [0], style1) sht. write (I + 1, 1, str (dlist [I]). decode ('utf-8 '). split ('--') [1], style1) sht. write (I + 1, 2, dict [dlist [I] [0], style1) sht. write (I + 1, 3, dict [dlist [I] [1], style1) sht. write (I + 1, 4, dict [dlist [I] [2], style1) def getInfo1 (rs, rs2, rs3 ): '''get offline data ''' dict = {} for I in range (len (rs): key = str (rs [I] [0]) value = rs [I] [1] dict [key] = [value, 0, 0] dlist = list (dict. keys () for I in range (len (rs2): key2 = str (rs2 [I] [0]) value2 = rs2 [I] [1] if key2 in dlist: value = dict [key2] [0] else: value = 0 dict [key2] = [value, value2, 0] dlist = list (dict. keys () for I in range (len (rs3): key3 = str (rs3 [I] [0]) value3 = rs3 [I] [1] if key3 in dlist: value = dict [key3] [0] value2 = dict [key3] [1] else: value = 0 value2 = 0 dict [key3] = [value, value2, value3] return dictdef writeInfo1 (sht, dict ): '''write offline data ''' dlist = list (dict. keys () for I in range (len (dlist): sht. write (I + 1, 0, str (dlist [I]). decode ('utf-8'), style1) sht. write (I + 1, 1, dict [dlist [I] [0], style1) sht. write (I + 1, 2, dict [dlist [I] [1], style1) sht. write (I + 1, 3, dict [dlist [I] [2], style1) def writeExcel (): ''' write report ''' wb = createTemplateExcel () rs = genSuccessOrderOnline () rs2 = genGenerateOrderOnline () rs3 = genFailOrderOnline () sheet0 = wb. get_sheet (0) dict0 = getInfo0 (rs, rs2, rs3) writeInfo0 (sheet0, dict0) rs4 = partition () rs5 = genGenerateOrderOffline () rs6 = genFailOrderOffline () sheet1 = wb. get_sheet (1) dict1 = getInfo1 (rs4, rs5, rs6) writeInfo1 (sheet1, dict1) wb. save (fileName) def main (): print "= % s start = % s" % (sys. argv [0], datetime. datetime. strftime (datetime. datetime. now (), "% Y-% m-% d % H: % M: % S") writeExcel () print "= % s end = % s" % (sys. argv [0], datetime. datetime. strftime (datetime. datetime. now (), "% Y-% m-% d % H: % M: % S") if _ name _ = '_ main __': try: main () finally: if orderDBUtil: orderDBUtil. close ()

Summary

The above is all the content for parsing the example of creating a Map to write an Excel table in Python. I hope it will be helpful to you. If you are interested, you can continue to refer to other related topics on this site. If you have any shortcomings, please leave a message. Thank you for your support!