According to the TYPE File Import and Export function, type File Import and Export

According to the TYPE File Import and Export function, type File Import and Export

/**

* Import file Action;
*/
Private File excelFile;
// Save the original file name
Private String excelFileFileName;
// Save the original file name
Private String importResult;

// After the Excel file is parsed, the information is stored in this User object.
Private ExcelWorkSheet <TabUser> excelUserSheet;

/**
* File Import
*
* @ Return
*/
@ Action (value = "importStuUser", results = {@ Result (name = "success", location = "/view/student/queryStu/stuGrid. jsp ")})
Public String importStuUser (){
Try {
Workbook workbook = createWorkBook (new FileInputStream (excelFile ));

If (roleType. equals ("")){
Sheet sheet = workbook. getSheetAt (0 );

ExcelUserSheet = new ExcelWorkSheet <TabUser> ();

Row firstRow = sheet. getRow (0 );
Iterator <Cell> iterator = firstRow. iterator ();

List <String> cellNames = new ArrayList <String> ();
While (iterator. hasNext ()){
CellNames. add (iterator. next (). getStringCellValue ());
}
ExcelUserSheet. setColumns (cellNames );
For (int I = 0; I <sheet. getLastRowNum (); I ++ ){
Row row = sheet. getRow (I );
TabRole tabRole = studentService. queryRoleByType (roleType );
TabUser tabUser = new TabUser ();
If (row. getCell (0). getNumericCellValue ()! = 0 ){
TabUser. setUserId (int) row. getCell (0). getNumericCellValue ());
} Else {
Throw new Exception ();
}
TabUser. setUserCode (row. getCell (1). getStringCellValue ());
TabUser. setUserName (row. getCell (2). getStringCellValue ());
TabUser. setUserPassword (row. getCell (3). getStringCellValue ());

ExcelUserSheet. getData (). add (tabUser );
StudentService. saveUser (tabUser );

StudentService. saveUserAndRole (tabUser, tabRole );
}
} Else if (roleType. equals ("t ")){
Sheet sheet = workbook. getSheetAt (0 );

ExcelUserSheet = new ExcelWorkSheet <TabUser> ();

Row firstRow = sheet. getRow (0 );
Iterator <Cell> iterator = firstRow. iterator ();

List <String> cellNames = new ArrayList <String> ();
While (iterator. hasNext ()){
CellNames. add (iterator. next (). getStringCellValue ());
}
ExcelUserSheet. setColumns (cellNames );
For (int I = 0; I <sheet. getLastRowNum (); I ++ ){
Row row = sheet. getRow (I );
TabRole tabRole = studentService. queryRoleByType (roleType );
TabUser tabUser = new TabUser ();

If (row. getCell (0). getNumericCellValue ()! = 0 ){
TabUser. setUserId (int) row. getCell (0). getNumericCellValue ());
} Else {
Throw new Exception ();
}
TabUser. setUserCode (row. getCell (1). getStringCellValue ());
TabUser. setUserName (row. getCell (2). getStringCellValue ());
TabUser. setUserPassword (row. getCell (3). getStringCellValue ());

ExcelUserSheet. getData (). add (tabUser );
StudentService. saveUser (tabUser );
StudentService. saveUserAndRole (tabUser, tabRole );
}
} Else if (roleType. equals ("s ")){
Sheet sheet = workbook. getSheetAt (0 );

ExcelUserSheet = new ExcelWorkSheet <TabUser> ();

Row firstRow = sheet. getRow (0 );
Iterator <Cell> iterator = firstRow. iterator ();

List <String> cellNames = new ArrayList <String> ();
While (iterator. hasNext ()){
CellNames. add (iterator. next (). getStringCellValue ());
}
ExcelUserSheet. setColumns (cellNames );
For (int I = 0; I <sheet. getLastRowNum (); I ++ ){
Row row = sheet. getRow (I );
TabRole tabRole = studentService. queryRoleByType (roleType );
TabUser tabUser = new TabUser ();

If (row. getCell (0). getNumericCellValue ()! = 0 ){
TabUser. setUserId (int) row. getCell (0). getNumericCellValue ());
} Else {
Throw new Exception ();
}
TabUser. setUserCode (row. getCell (1). getStringCellValue ());
TabUser. setUserName (row. getCell (2). getStringCellValue ());
TabUser. setUserPassword (row. getCell (3). getStringCellValue ());

ExcelUserSheet. getData (). add (tabUser );
StudentService. saveUser (tabUser );
StudentService. saveUserAndRole (tabUser, tabRole );
}
}
ImportResult = "OK ";
VersionService. updateVersionInformation ("user ");
} Catch (Exception e ){
ImportResult = "fail ";
E. printStackTrace ();
}

Return SUCCESS;
}

Private String format = "xls ";
Private String fileName = "export data .xls ";

/** Export data */
Private void exportExcel (OutputStream OS ){
Workbook book = new HSSFWorkbook ();
Sheet sheet = book. createSheet ("export information ");
Row row = sheet. createRow (0 );
Row. createCell (0). setCellValue ("userId ");
Row. createCell (1). setCellValue ("userCode ");
Row. createCell (2). setCellValue ("userName ");
Row. createCell (3). setCellValue ("userPassword ");

If (roleType. equals ("")){
Reports = studentService. queryUserByType (roleType );

For (int I = 0; I <reports. size (); I ++ ){
TabUser tabUser = reports. get (I );
Row = sheet. createRow (I );
Row. createCell (0). setCellValue (tabUser. getUserId ());
Row. createCell (1). setCellValue (tabUser. getUserCode ());
Row. createCell (2). setCellValue (tabUser. getUserName ());
Row. createCell (3). setCellValue (tabUser. getUserPassword ());
}

} Else if (roleType. equals ("t ")){
Reports = studentService. queryUserByType (roleType );

For (int I = 0; I <reports. size (); I ++ ){
TabUser tabUser = reports. get (I );
Row = sheet. createRow (I );
Row. createCell (0). setCellValue (tabUser. getUserId ());
Row. createCell (1). setCellValue (tabUser. getUserCode ());
Row. createCell (2). setCellValue (tabUser. getUserName ());
Row. createCell (3). setCellValue (tabUser. getUserPassword ());
}

} Else if (roleType. equals ("s ")){
Reports = studentService. queryUserByType (roleType );

For (int I = 0; I <reports. size (); I ++ ){
TabUser tabUser = reports. get (I );
Row = sheet. createRow (I );
Row. createCell (0). setCellValue (tabUser. getUserId ());
Row. createCell (1). setCellValue (tabUser. getUserCode ());
Row. createCell (2). setCellValue (tabUser. getUserName ());
Row. createCell (3). setCellValue (tabUser. getUserPassword ());
}
}

Try {
Book. write (OS );
} Catch (Exception ex ){
Ex. printStackTrace ();
}
}

@ Action (value = "exportStuUser ")
Public String exportStuUser () throws Exception {

SetResponseHeader ();
Try {
ExportExcel (response. getOutputStream ());
Response. getOutputStream (). flush ();
Response. getOutputStream (). close ();
} Catch (IOException e ){
E. printStackTrace ();
}
Return null;
}

/** Set the Response Header */
Public void setResponseHeader (){
Try {
Response. setContentType ("application/msexcel; charset = UTF-8 ");
Response. setHeader ("Content-Disposition", "attachment; filename =" + java.net. URLEncoder. encode (this. fileName, "UTF-8 "));
// The client does not cache
Response. addHeader ("Pargam", "no-cache ");
Response. addHeader ("Cache-Control", "no-cache ");
} Catch (Exception ex ){
Ex. printStackTrace ();
}
}

/**
* Determine the format of the imported file;
*/
Private Workbook createWorkBook (FileInputStream fileInputStream) throws Exception {
If (excelFileFileName. toLowerCase (). endsWith ("xls ")){
Return new HSSFWorkbook (fileInputStream );
}
If (excelFileFileName. toLowerCase (). endsWith ("xlsx ")){
Return new XSSFWorkbook (fileInputStream );
}
Return null;
}

 

Js section;

<Div class = "container">
<Div class = "row">
<Div class = "col-md-12">
<% -- Remember to set enctype = "multipart/form-data" -- %>
<S: form id = "importForm" action = "/student/importStuUser. action" method = "post" enctype = "multipart/form-data">
<Input type = "hidden" name = "roleType" id = "roleType" value = "a"/>
Import an Excel file: <s: file id = "excelFile" name = "excelFile"> </s: file> <br/>
<S: submit value = "import"> </s: submit>
</S: form>
<S: form name = "form1" action = "/student/exportStuUser. action" method = "post">
<Input type = "hidden" name = "format" value = "xls"/>
<Input type = "hidden" name = "roleType" id = "roleTypeSecond" value = "a"/>
<S: submit name = "sub" value = "export data"> </s: submit>
</S: form>

<Div style = "margin-left: 40%">
<Button class = "ats" roleType = "a"> admin </button> & nbsp;

<Button class = "ats" roleType = "t"> teacher </button> & nbsp;

<Button class = "ats" roleType = "s"> student </button>
</Div>

</Div>

</Div>

</Div>

<S: if test = "importResult = 'OK'">
<Script>
Alert ("file imported successfully! ");
</Script>
</S: if>
<S: elseif test = "importResult = 'fail '">
<Script>
Alert ("the import file is incorrect or empty. Check the file before importing it! ");
</Script>
</S: elseif>

<Script type = "text/javascript">
Var roleType = 'a ';
$ (Function (){

$ (". Ats"). click (function (){
RoleType = $ (this). attr ("roleType ");
$ ("# RoleType"). val (roleType );
$ ("# RoleTypeSecond"). val (roleType );

If (! $ ("# GridTable_ilcancel"). hasClass ('ui-state-disabled ')){
$ ("# GridTable_ilcancel"). trigger ("click ");
}
$ ("# GridTable"). jqGrid ("clearGridData ");
$. Ajax ({
Type: 'post ',
Url: "$ {pageContext. request. contextPath}/student/queryStu. action ",
Data :{
Type: 'json ',
RoleType: roleType
},
Success: function (data ){
For (var I = 0; I <= data. length; I ++ ){
$ ("# GridTable"). jqGrid ('addrowdata', I + 1, data [I]);
}

}
});

});

});