普元nui：上传下载、读取Excel文件信息
姬乐池
Report.jsp
<style type="text/css">
div {
text-align: center;
margin-top: 20px;
margin-bottom: 10px;
}
#sub {
color: #3474D7;
background: white;
}
#dow {
width: 200px;
margin-top: 10px;
opacity: 0.5;
border-radius: 25px;
letter-spacing: 15px;
}
</style>
<body>
<div class="nui-fit">
<form id="form" action="<%=request.getContextPath() + "/syzcdDksqbbb/upload.do"%>" method="post" enctype="multipart/form-data">
<input class="nui-htmlfile" name="upload" style="width: 250px" />
</form>
<a class="nui-button" id="dow" iconCls="icon-down" onclick="downClick"> 模板下载</a>
</div>
<div class="nui-toolbar">
<a class="nui-button" id="sub" iconCls="icon-ok" onclick="upClick">
<fmt:message key="确认" />
</a>
<span style="display: inline-block; width: 25px;"></span>
<a class="nui-button" iconCls="icon-close" onclick="cloClick" style="width: 60px;">
<fmt:message key="关闭" />
</a>
</div>
<script type="text/javascript">
$(function() {
$J.get("sub").enable();
})
/* 上传 */
function upClick() {
if (check()) {
$("#form").submit();
$J.get("sub").disable();
}
}
/* 关闭 */
function cloClick() {
$J.closedialog("ok");
}
/* 下载 */
function downClick() {
var reqUrl = appContext + "/syzcdDksqbbb/download.do";
location.href = reqUrl;
}
/* 格式校验 */
function check() {
/* 文件路径不能为空 */
var upload = $J.getbyName("upload");
var pathLocal = upload.value;
if (pathLocal == "") {
$J.cap4jAlert(message.common.uploadPrompt);
return;
}
/* 根据客户端的操作系统获取分隔符 */
var separate = "\\";
if (!isWindows) {
separate = "/";
}
/* 文件名称不能超过128 */
if (pathLocal.substring(pathLocal.lastIndexOf(separate) + 1,
pathLocal.length).length > 128) {
$J.cap4jAlert(message.common.maxLengthValidationMsg,
message.common.prompt);
return;
}
/* 文件后缀名 */
var postfix = pathLocal.substring(pathLocal.lastIndexOf(".") + 1);
if (postfix != "xls") {
$J.cap4jAlert(message.staff.excelUploadPrompt);
upload.setValue("");
upload.setText("");
return;
}
return true;
}
</script>
</body>
ctrler
@FunctionDesc("下载")
@ResponseBody
@RequestMapping(value = "/download")
public void download(HttpServletRequest request, HttpServletResponse response) {
String fileDir = request.getSession().getServletContext().getRealPath("/");
String tempName = "\\views\\syfh_zcd\\report\\uploadReport\\template\\ReporttablExcel.xls";
String path = fileDir + tempName;
File file = new File(path);
String filename = file.getName();
InputStream fis;
try {
fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
response.reset();
response.addHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode("reportDemo.xls", "UTF-8"));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (IOException e) {
e.printStackTrace();
}
}
@FunctionDesc("批量提交SyzcdDksqbbb")
public String upload(HttpServletRequest req, @RequestParam("upload") MultipartFile uploadFile, Model model) throws IOException {
log.info("批量提交SyzcdDksqbbb");
String path = req.getSession().getServletContext().getRealPath("/") + "/views\\syfh_zcd\\report\\uploadReport\\";
File filePath = new File(path);
// 先判断地址是否存在
if (!filePath.exists()) {
// 目录不存在创建这个目录
filePath.mkdirs();
}
// 文件相对路径，没有带目录
String originalFilename = uploadFile.getOriginalFilename();
// 查找“.”在文件名的下标，以便将文件名与文件后缀分开
int indexOf = originalFilename.indexOf(".");
// 文件名称
String name = originalFilename.substring(0, indexOf);
// 文件后缀.xml
String tail = originalFilename.substring(indexOf);
// 当前时间
String dateNow = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
// 重新命名文件名称
String nameNew = name + dateNow + tail;
String pathName = path + nameNew;
// 获取输入流
InputStream in = uploadFile.getInputStream();
OutputStream out = new FileOutputStream(new File(pathName));
String str = uploadFile.getOriginalFilename();
byte data[] = new byte[1024 * 1024];
int len = 0;
while ((len = in.read(data)) != -1) {
out.write(data, 0, len);
}
in.close();
out.close();
// 解析Excel获取数据
ReadExcel readExcel = new ReadExcel();
int flag = readExcel.doExcel(pathName, nameNew);
req.setAttribute("flag", flag);
return "\\syfh_zcd\\report\\reportApter";
}ReadExcel.java
public class ReadExcel {
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
private static List<Map<Integer, String>> listData = null;
private SyzcdDksqbbbServiceImpl syzcdDksqbbbServiceImpl = ContextUtil.getBean("iSyzcdDksqbbbService", SyzcdDksqbbbServiceImpl.class);
/**
* 日志记录类.
*/
private Logger logger = LoggerFactory.getLogger(ReadExcel.class);
public int doExcel(String... args) {
String pathName = args[0];
String nameNew = args[1];
int flag = 0;
logger.info("解析xml数据：ReadExcel doExcel：" + pathName);
readExcel(pathName);
List<SyzcdDksqbbbVO> listVO = new ArrayList<SyzcdDksqbbbVO>();
for (int i = 0; i < listData.size(); i++) {
SyzcdDksqbbbVO vo = new SyzcdDksqbbbVO();
vo.setCnname(listData.get(i).get(0).trim());
vo.setUnitcode(listData.get(i).get(1).trim());
listVO.add(vo);
}
// "文件上传失败，请确认文件重新上传！"
if (listVO == null || listVO.size() < 1) {
flag = 0;
return flag;
}
// 批量入库
syzcdDksqbbbServiceImpl.deleteList(listVO);
syzcdDksqbbbServiceImpl.saveList(listVO);
flag=1;
return flag;
}
/**
* 将Excel文件内容存储到list
*
* @param filePath
* 文件路径
* @return
* @return list
*/
public List<Map<Integer, String>> readExcel(String pathName) {
listData = new LinkedList<Map<Integer, String>>();
Workbook workbook = null;
// 格式效验
this.preReadCheck(pathName);
workbook = this.getWorkbook(pathName);
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workbook.getSheetAt(numSheet);
if (sheet == null) {
logger.info("Excel文件无sheet页");
}
int firstRowIndex = sheet.getFirstRowNum();
int lastRowIndex = sheet.getLastRowNum();
// 读取首行 即,表头
Row firstRow = sheet.getRow(firstRowIndex);
for (int i = firstRow.getFirstCellNum(); i <= firstRow.getLastCellNum(); i++) {
Cell cell = firstRow.getCell(i);
String cellValue = this.getCellValue(cell);// 将表头格式装换后返回
logger.info(" " + cellValue + "\t");
}
// 读取数据行
for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
Map<Integer, String> mapData = new LinkedHashMap<Integer, String>();
Row currentRow = sheet.getRow(rowIndex);// 当前行
if (currentRow == null) {
continue;
}
// 读取每一行的单元格数据
int key = 0;
int getFirstCellNum = currentRow.getFirstCellNum(); // 首列
int getLastCellNum = currentRow.getLastCellNum();// 最后一列
for (int columnIndex = getFirstCellNum; columnIndex < getLastCellNum; columnIndex++) {
Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格
String currentCellValue = this.getCellValue(currentCell);// 当前单元格的值
mapData.put(key, currentCellValue);
key++;
}
logger.info(" mapData" + mapData + "\t");
if (mapData != null && mapData.size() > 0) {
listData.add(mapData);
logger.info("addlist" + listData + "\t");
}
try {
if (workbook != null) {
workbook.close();
}
} catch (IOException e) {
logger.error("解析Excel文件错误..." + e.getMessage());
e.printStackTrace();
}
}
}
return listData;
}
// Excel文件名格式效验
private void preReadCheck(String pathName) {
File file = new File(pathName);
try {
if (!file.exists()) {
throw new FileNotFoundException("传入的文件不存在：" + pathName);
}
if (!(pathName.endsWith(XLS) || pathName.endsWith(XLSX))) {
throw new BusinessException("传入的文件不是excel");
}
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
// 获得workbook对象
private Workbook getWorkbook(String pathName) {
Workbook workbook = null;
try {
InputStream is = new FileInputStream(pathName);
if (pathName.endsWith(XLS)) {
workbook = new HSSFWorkbook(is);
} else if (pathName.endsWith(XLSX)) {
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
logger.info("获得workbook");
return workbook;
}
// 将表头格式装换后返回
private String getCellValue(Cell cell) {
if (cell == null) {
return "";
}
// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
// 加上下面这句，临时把它当做文本来读取
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
}ReportAfter.jsp
$(function() {
var flag = "<c:out value='${flag}'/>";
switch (flag) {
case "0":
msg = "文件上传失败，请检测文件重新上传！";
break;
case "1":
msg = "文件上传成功！";
break;
default:
msg = "文件上传失败，请联系管理员！";
}
$J.cap4jConfirm(msg + "是否继续上传？", "提示", function(action) {
if (action == 'ok') {
window.location.href = appContext
+ "/syzcdDksqbbb/report.do";
} else {
$J.closedialog("ok")
}
});
})
类似资料：