/**
* 导入文件Action;
*/
private File excelFile;
// 保存原始文件名
private String excelFileFileName;
// 保存原始文件名
private String importResult;
// 将Excel文件解析完毕后信息存放到这个User对象中
private ExcelWorkSheet<TabUser> excelUserSheet;
/**
* 文件导入
*
* @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("a")) {
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 = "导出数据.xls";
/** 导出数据 */
private void exportExcel(OutputStream os) {
Workbook book = new HSSFWorkbook();
Sheet sheet = book.createSheet("导出信息");
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("a")) {
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;
}
/** 设置响应头 */
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"));
// 客户端不缓存
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
/**
* 判断导入文件格式;
*/
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部分;
<div class="container">
<div class="row">
<div class="col-md-12">
<%-- 记住这里需要设置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"/>
导入Excel文件：<s:file id="excelFile" name="excelFile"></s:file> <br/>
<s:submit value="导入"></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="导出数据"></s:submit>
</s:form>
<div style="margin-left: 40%">
<button class="ats" roleType="a">admin</button>
<button class="ats" roleType="t">teacher</button>
<button class="ats" roleType="s">student</button>
</div>
</div>
</div>
</div>
<s:if test="importResult=='ok'">
<script>
alert("文件导入成功!");
</script>
</s:if>
<s:elseif test="importResult=='fail'">
<script>
alert("导入文件存在错误或空,请核对后再导入!");
</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]);
}
}
});
});
});