First, import excel
(1) Upload files using spring
A. Home page submission
& ltform name = " excelImportForm " action = " $ { pagecontext . request . context path }/brand/importBrandSort " method = " post " on submit = " return checkImportPath()；" enctype = " multipart/form-data " id = " excelImportForm " >
& ltinput type = " hidden " name = " ids " id = " ids " & gt；
& ltdiv class="modal-body " >
& ltdiv class="row gap " >
& ltlabel class = " col-sm-7 control-label " >& ltinput class = " BTN BTN-default " id = " excel _ file " type = " file " name = " filename " accept = " xls "/& gt； & lt/label & gt；
& ltdiv class="col-sm-3 " >
& ltinput class = " BTN BTN-primary " ID = " Excel _ button " Type = " Submit " Value = " Import Excel "/& gt；
& lt/div & gt；
& lt/div & gt；
& lt/div & gt；
& ltdiv class="modal-footer " >
& ltbutton type = " button " class = " BTN BTN-default " data-dissolve = " modal " onClick = " uncheck boxes()；" & gt cancel
& lt/div & gt；
B.spring's controller performs related operations in the background. Here we mainly talk about using spring to upload files and read file information.
Before uploading files using spring, you need to configure the bean.
& ltbean id = " multipart resolver " class = " org . spring framework . web . multipart . commons . commonsmultipartresolver " & gt； & lt/bean & gt； @ request mapping(value = "/importBrandSort "，method = RequestMethod。 Post)
Public model and view importbrandsort (@ requestparam ("filename") multi-file file,
HttpServletRequest, HttpServletResponse) throws an exception {
String temp = request.getSession()。 getServletContext()
. getRealPath(File.separator)
+“temp”； //temporary directory
File tempFile = new file (temp);
If (! tempFile.exists()) {
tempfile . mkdirs()；
}
DiskFileUpload fu = new DiskFileUpload()；
fu . setsizemax( 10 * 1024 * 1024)； //Set the file size, in bits, that users are allowed to upload.
fu . setsize threshold(4096)； //Set the data that the memory can only store at most, in bits.
fu . setrepositorypath(temp)； //Set the directory where the data will be stored on the hard disk once the file size exceeds the value of getSizeThreshold ().
//Start reading the uploaded information
//
int index = 0；
/* List file items = null；
Try {
FileItems = fu.parseRequest (request);
}
Catch (exception e)
e . printstacktrace()；
}
iterator ITER = file items . iterator()； //Process each uploaded file in turn.
FileItem fileItem = null
while (iter.hasNext()) {
FileItem item =(FileItem)ITER . next()； //Ignore all other form information that is not a file field.
If (! item.isFormField()) {
fileItem = item
//index++；
}
}
if (fileItem == null)
Returns null
*/
if (file == null)
Returns null
logger . info(file . getoriginalfilename())；
string name = file . getoriginalfilename()； //Get the upload file name, including the path.
//name = name . substring(name . lastindexof(" \ \ "+ 1)； //Extract the file name from the full path
long size = file . getsize()；
if((name = = null | | name . equals(" ")& amp； & amp size == 0)
Returns null
InputStream in = file . getinputstream()；
List & ltbrandmobileinfoentity & gtbrandmobileinfos = brandservice
. importbrandperiodssort(in)；
//manually refresh the cache keycontextmanager. Clearperiodcachedata (new
//period dimensions())； //Clear all caches
int count = brandmobileinfos . size()；
String strAlertMsg =
If (calculate! =0){
StrAlertMsg= "Successfully imported" +count+ "entries!" ;
} Otherwise {
StrAlertMsg = "Import failed!" ;
}
logger . info(strAlertMsg)；
//request . set attribute(" brandPeriodSortList "，BrandMobileInfos)；
//request . set attribute(" strAlertMsg "，strAlertMsg)；
request.getSession()。 setAttribute("msg "，strAlertMsg)；
Return get (request, response);
//returns null
}
The comment part of the code is how to get the submitted file name if you don't use spring (you need some apache toolkits). In fact, the same is true for people who use spring, but it is packaged for us to write code.
The latter part of the code is the information that reads the uploaded file information and updates the database and then outputs it to the foreground page.
In the above code: InputStream in = file. GetInputStream()；
List & ltbrandmobileinfoentity & gtbrandmobileinfos = brandservice
. importbrandperiodssort(in)； Read the information of excel.
(2) Reading excel with poi
A. Updating the database
@ Overlay
The public list & ltbrandmobileinfoentity & gtimportbrandperiodsort (inputstream in) threw an exception {
List & ltbrandmobileinfoentity & gtbrandmobileinfos = readbrandperiodsorxls (in);
for(BrandMobileInfoEntity brandMobileInfo:brandMobileInfos){
mapper . updateby conditions(brandMobileInfo)；
}
Return to brandMobileInfos
}
This part is the code of service layer, which is used to update database data after reading excel information. I use mybatis here. Define a class BrandMobileInfoEntity, which is used to save the information of each row in the excel table and list it at the same time.
B. Reading excel information
Private distribution list & ltbrandmobileinfoentity & gtreadbrandperiodsorxls (input stream is yes)
Throw IOException, ParseException {
hssf workbook hssf workbook = new hssf workbook(is)；
List & ltbrandmobileinfoentity & gtbrandmobileinfos = new ArrayList < BrandMobileInfoEntity & gt();
BrandMobileInfoEntity brandMobileInfo；
//Cycle worksheet worksheet
for(int numSheet = 0；
numSheet & lthssf workbook . getnumberofsheets()； numSheet++) {
hssf sheet hssf sheet = hssf workbook . get sheet at(num sheet)；
if (hssfSheet == null) {
Continue;
}
//Loop line
for(int rowNum = 1； rowNum & lt= hssf sheet . getlastrownum()； rowNum++) {
brandMobileInfo = new BrandMobileInfoEntity()；
HSS flow HSS flow = hssf sheet . getrow(rowNum)；
for(int I = 0； Me & lthssflow. getlastcellnum (); i++) {
hssf cell brandIdHSSFCell = HSS flow . get cell(I)；
if (i == 0) {
BrandMobileInfo.setBrandId (integer
. parse int(getCellValue(brandIdHSSFCell)))；
} else if (i == 1) {
Continue;
} else if (i == 2) {
brandmobileinfo . setmobileshowfom(integer . parse int(getCellValue(brandIdHSSFCell)))；
} else if (i == 3) {
brandmobileinfo . setmobileshowto(integer . parse int(getCellValue(brandIdHSSFCell)))；
} else if (i == 4) {
brandmobileinfo . setsellmarkvalue(getCellValue(brandIdHSSFCell))；
} else if (i == 5) {
brandmobileinfo . set warehouse(getCellValue(brandIdHSSFCell))；
} else if (i == 6) {
brandmobileinfo . set sorta 1(integer . parse int(getCellValue(brandIdHSSFCell)))；
} else if (i == 7) {
brandmobileinfo . set sorta 2(integer . parse int(getCellValue(brandIdHSSFCell)))；
} else if (i == 8) {
brandmobileinfo . set sortb(integer . parse int(getCellValue(brandIdHSSFCell)))；
} else if (i == 9) {
brandmobileinfo . set sortc 10(integer . parse int(getCellValue(brandIdHSSFCell)))；
}
else if (i == 10) {
brandmobileinfo . set sortc(integer . parse int(getCellValue(brandIdHSSFCell)))；
} else if (i == 1 1) {
brandmobileinfo . sethita(getCellValue(brandIdHSSFCell))；
} else if (i == 12) {
brandmobileinfo . sethitb(getCellValue(brandIdHSSFCell))；
} else if (i == 13) {
brandmobileinfo . sethitc(getCellValue(brandIdHSSFCell))；
} else if (i == 14) {
brandmobileinfo . setcustomselltype(getCellValue(brandIdHSSFCell))；
}else if (i == 15)
{
Continue;
}else if (i == 16) {
brandmobileinfo . setchannelid(integer . parse int(getCellValue(brandIdHSSFCell)))；
}
}
brandmobileinfos . add(brandMobileInfo)；
}
}
Return to brandMobileInfos
}
This kind of code is a bit awkward and has not been optimized. You can probably see how to read information.
(3) using mybatis to update data.