To use this example through the docker container, drop down first docker mirror of jxTMS And follow the instructions to launch the tms container, and from helloWorld Start trying.

Importing excel into jxTMS

In the long-term practice, the author believes that the biggest bottleneck of business management software lies in the nature of IT: preset hard-coded form automaton. This makes it difficult for IT systems to follow the changes of environment and business, which makes IT either excluded from the operation of business and only provide functional support. Or it solidifies the business process and limits the development of the business to some extent.

For large organizations, because they are large enough to fight environmental uncertainty, they also pay the cost of IT change. However, for small and micro organizations, the competitive basis is to capture market opportunities flexibly and flexibly, so consolidated business processes will greatly lose opportunity costs, which makes it difficult for small and micro enterprises to automate and digitize, let alone intelligently, but only to obtain general efficiency improvement from IT infrastructure with substantially reduced costs.

Therefore, the development idea of jxTMS is to strike iron instead of casting, that is, to provide IT support along with the development of user business, and to provide low-cost, continuous, fine-tuned customized services in coordination with gradually improved business management. Reflected in the development process are low thresholds for out-of-the-box use, low requirements for developers, fast presentation and low-cost, fast customization of communication costs with textual definitions.

Obviously, since it is not a one-step process, but a point entry, there are many times links that must be made with the business links of manual work, which in general use excel as a tool for data exchange.

So in this section, we will first show how to import excel files in jxTMS to read data.

Note: jxTMS uses POI to support xls (as long as it is not an old excel generally supported) and xlsx (less used by the author). It is recommended to use XLS format as the author has been developing in XLS format all along, so there will be no problem. In the current demo environment, all are in xls format, xlsx format is not open

File upload interface

Let's start by making an interface for file upload by adding:

web importDiscount type div;
web importDiscountt1 parent importDiscount type table title="Import discount",width=900,alone=true;
with importDiscountt1 row 0 col c0 web n type text text='upload excel: ',width=200;
with importDiscountt1 row 0 col c1 web n type fileInput width=700;
with importDiscountt1 row 1 col c0 web n type button width=80,text='Import',motion=cmd,demand=importDiscount,confirm='Confirm import sales discount? Previous discounts will be cleared!!';

As you can see, a new control appears: fileInput:

All but one width is specified by default:

• fileExts: Specifies the file suffix that allows uploading, if'*', any file can be uploaded, and if multiple formats of files are allowed, separated by English commas. The default is:'xls'

• maxFileSize: Maximum allowable file length in K bytes, defaulting to 10240, or 10M bytes

Other fileInput features include:

• Only one file can be uploaded at a time

• Files that meet fileExts requirements are automatically uploaded when they are dragged into

So you may have a question, what should I do if I upload too many 10M files? The uploaded files are placed in the tmp directory under the web service's root directory [webRoot], and jxTMS clears out the files a month ago every day. Files uploaded by everyone are also recorded in the log. If the upload file is full, you should first consider whether this is a business need or a malicious attack. If it's a business need, you need to consider the storage design of the files, and if it's an attack, you need to deploy a system monitoring tool that checks the jxTMS log for attackers when abnormal hard disk growth is detected.

Note 1: To avoid DDOS attacks, jxTMS imposes resource restrictions on the file upload interface, which allows only 10 uploads per second for all users

Note 2: After uploading the file, you can go into the tmp directory and see how this newly uploaded file name differs from the one we uploaded? jxTMS automatically adds a random number long enough to all uploaded files to reduce the probability of collisions between files of the same name in order to prevent errors caused by multiple people uploading files of the same name at the same time.

Import Processing

Everyone is in capa. Add:

@myModule.event('cmd', 'importDiscount')
def importDiscount(self, db, ctx):
	dt=dataTable.getOrCreateTable(db,'authorize','discount')
	dt.clear(db)
	with  jxExcel(self.importFilepath, 'discount') as e:
		dn=e.getCellBigDecimalValue('B2')
		authorize.setRightDefault(db,ctx,dt,dn)

		e.head(3)
		rs=e.rows(4)
		for r in rs:
			name=e.getCellStringValue(r,'Name'.decode('utf-8'))
			if name is None:
				continue
			ty=e.getCellStringValue(r,'type'.decode('utf-8'))
			cs=e.colsWithName(r,2)
			authorize.addRight(db,ctx,dt,ty,name,cs)
	ctx.getCurrentOrg().clearAuthorize()

We need to use the [importDiscount.xls] file in the domeCode directory. Now open the [importDiscount.xls] file and compare it with what importDiscount does.

dt=dataTable.getOrCreateTable(db,'authorize','discount')
dt.clear(db)

These two lines are the first to get a table [jxTMS built-in table] that we need for our discount jobs. Appendix 5-jxTMS System Data Table ) The table is authorize d by name discount and created if it does not exist. Then, regardless of whether the table is new or not, clear it all to ensure that the table's data is imported this time.

with  jxExcel(self.importFilepath, 'discount') as e:

Open the sheet named discount in the [importDiscount.xls] file we uploaded with the with statement and specify that the sheet object is the variable e.

Note: The uploaded file will be placed in the tmp directory, but the user does not need to care, just know self. The importFilepath variable holds the imported file name [with path]

dn=e.getCellBigDecimalValue('B2')

Read the B2 grid in the sheet and read its contents as numbers.

Note: The most common problem with Excel processing is that cells have their own formatting. The resulting view is not necessarily read by the system [For example, in a project, the user's mobile phone number is used as the user's login name, but the user sees the normal mobile phone number in excel, but in fact excel stores it as a floating point number, so jxTMS reads the string as a floating point number, which causes the user to clearly import, but the system reports: no one can log on, and only by directly viewing the number So I suggest that if you are unfamiliar with this, you should select the entire sheet and format the cells as text after filling it out.

authorize.setRightDefault(db,ctx,dt,dn)

Set the default value of the discount rights table with the dn value, that is, when a user's discount rights cannot be queried.

e.head(3)
rs=e.rows(4)

jxExcel divides the data in excel into two categories:

• Grid-by-cell stored discrete data, such as dn, is read directly by a grid number

• For table data, jxTMS defaults to having a header for each table listing the names of columns. So e.head(3) means that line 3 (the row number inside excel starts at 0) is treated as the header to perform the mapping of column number to column name. e.rows(4) reads rows from line 4 until the empty line ends

Note: Because the number of rows in the table data is variable, it is recommended that you place discrete data on top of the table. It is recommended that you do not have discrete data below the table data.

In this case, the contents of the discount table are in rs, and then the data of each row is accessed row by row in a for loop.

name=e.getCellStringValue(r,'Name'.decode('utf-8'))
ty=e.getCellStringValue(r,'type'.decode('utf-8'))

After e.head(3) has mapped the column number to the column name of the table data, we can directly read the data in the corresponding grid in the row using the column header instead of the column number. To avoid surprises, make a deliberate check for the name you read.

cs=e.colsWithName(r,2)

Starting from column 2 (the column number inside excel starts at 0), read all the remaining data at once and submit it it by the map set (column name, value).

authorize.addRight(db,ctx,dt,ty,name,cs)

The meaning of a discount privilege is: the maximum discount rate that a person or role can give for a certain product type. The above statement is a collection of product-discounts read, written once to a role or person specified by ty-name.

ctx.getCurrentOrg().clearAuthorize()

Finally, after the permissions have been updated, clear all the permission data that has been cached in the current organization.

Note: Permissions are available through the getAuthorizeValue function of the current organization [ctx.getCurrentOrg()]:

public Object getAuthorizeValue(IDBop db, String peoplename, String rightclass, String auth) throws Exception；

Where:

• peoplename is the permission corresponding to the name of the person to be queried (jxTMS is used for short names, organization names, etc. to avoid duplicate names). If no permission is granted to the person, the permissions corresponding to all the roles associated with the person are queried. If no default value for auth is queried, and the default value for rightclass is queried if no permission is available yet.

• rightclass: the classification of permissions, set up by yourself as needed

• auth: the name of the permission, set by yourself as needed

Note: rightclass, auth should be unified enterprise data design, after overall consideration, give a naming specification of permissions. Then the developer sets it up according to these specifications

View permissions

Add to the web file:

web litDiscount type div;
web litDiscountt1 parent litDiscount type table title="List of discounts",width=900;
with litDiscountt1 col authType head type width=80;
with litDiscountt1 col authName head Name width=80;
with litDiscountt1 col rightName head category width=80;
with litDiscountt1 col authValue head Discount width=80;

Add in sql file:

sql litDiscount
from dataTable as ta,dataItem as ti
select ti.Category,ti.Name,ti.Info
where ta.Type=='authorize' and ta.Name=='discount' and ta.ID==ti.dataTableID and ti.NoUsed==false ;

In capa. Add:

@myModule.event('prepareDisp', 'litDiscount')
def litDiscount(self, db, ctx):
	wc = ctx.getCurrentOrg().getSQL('demo.litDiscount')
	table = authorize.listRights(db,wc)
	self.setOutput('litDiscountt1',table)

Add in the op.py file:

@biz.Motion('demo.demo1','disp','importDiscount')
@biz.OPDescr
def op1(json):
	json.setShortcut('Demonstration'.decode('utf-8'),'Import discount'.decode('utf-8'))

@biz.Motion('demo.demo1','disp','litDiscount')
@biz.OPDescr
def op1(json):
	json.setShortcut('Demonstration'.decode('utf-8'),'List of discounts'.decode('utf-8'))

We'll do sql, web, op.py, capa.py and other files by Manage jxTMS code with sftp The description is updated to the / home/tms/codeDefine/demo/demo/demo1 directory.

Then execute once Heat engine refresh After that, because the entry in the shortcut bar has changed, you will exit the login first, click Demo - > Import Discount on the shortcut bar again, then drag [importDiscount.xls] into the upload file box and click the Confirm button.

Then click on the Shortcut Bar to see if the discount data is correct after importing. You can modify some of the data in [importDiscount.xls] and import it several times to see if it is correct.