Excel上传示例及后台解析Excel
来源:互联网 发布:2016欧洲杯数据 编辑:程序博客网 时间:2024/06/06 08:29
<form action="../module/AccountInfoBatch-input.action" method="post" id="formId" enctype="multipart/form-data">
文件:<input type="file" name="file1" id="fileId"/>
<input id="tijiao" type="submit" style="margin-right: 10px; margin-top: 70px;" class="btn btn-md btn-primary" value="提交"></input>
</form>
<script>
function submitData() {
onsubmit ="document.getElementById('tijiao').disabled=true;return true;" ;
var fileId = $("#fileId").val();
if (fileId == "") {
alert("请选择要上传的文件");
return false ;
}
var exec = (/[.]/.exec(fileId)) ? /[^.]+$/.exec(fileId.toLowerCase()) : '';
if (exec != "xlsx") {
alert("文件格式不对,请上传Excel文件!");
return false;
}
$("#tijiao").val("提交ing···");
document.getElementById('tijiao').disabled=true;
$("#formId").submit();
}
</script>
java代码(示例):
/**
* 企业电子账户批量录入
*
* @author kyzeng
*
*/
public class AccountInfoBatchAction extends CommonAction {
/**
*
*/
private static final long serialVersionUID = 1L;
@Autowired
BtUnitService btUnitService;
@Autowired
BtConfService btConfService;
private File file1;
public File getFile1() {
return file1;
}
public void setFile1(File file1) {
this.file1 = file1;
}
public String input() throws InvalidFormatException, IOException {
Map<String, Object> result = new HashMap<>();
setResult(result);
String date = new SimpleDateFormat("yyyyMMddHHmmssSS").format(new Date());
// 将客户端的文件上传到服务端
String desPath = ServletActionContext.getServletContext().getRealPath("/imags");
File destFile = new File(desPath, date + ".xlsx");
FileUtils.copyFile(file1, destFile);
List<AccountCSVModel> csvList = EXCELPioParse.parseExcel(desPath + File.separator + date + ".xlsx");
destFile.delete();
List<String> msgAccountIdList = new ArrayList<String>();
setResult(result);
StringBuilder msg = new StringBuilder();
for (AccountCSVModel accountCSVModel : csvList) {
long sid;
try {
Map<String, Object> map = btUnitService.getBtUnit(accountCSVModel.getP2p());
sid = ((Number)map.get("SID")).longValue();
} catch (NumberFormatException e1) {
msg.append("电子账号【" + accountCSVModel.getAccountId() + "】失败:" + e1.getMessage());
continue;
}
try {
Map<String, Object> btConfCardBinMap = btConfService.getCodeByUnid(sid, Constant.BT_CONF_NAME_CARDBIN);
if (!(accountCSVModel.getAccountId().substring(0, 9)).equals(btConfCardBinMap.get("CODE"))) {
msgAccountIdList.add(accountCSVModel.getAccountId());
msg.append("电子账号【" + accountCSVModel.getAccountId() + "】与平台不匹配。");
continue;
}
} catch (Exception e1) {
msg.append("电子账号【" + accountCSVModel.getAccountId() + "】失败:" + e1.getMessage());
continue;
}
Map<String, Object> btConfClusterMap;
try {
btConfClusterMap = btConfService.getCodeByUnid(sid, Constant.BT_CONF_NAME_CLUSTER);
} catch (Exception e1) {
msg.append("电子账号【" + accountCSVModel.getAccountId() + "】失败:" + e1.getMessage());
continue;
}
try {
insert(accountCSVModel, (String) btConfClusterMap.get("CODE"));
} catch (Exception e) {
msg.append("电子账号【" + accountCSVModel.getAccountId() + "】失败:" + e.getMessage() );
continue;
}
}
if (!StringUtils.isEmpty(msg.toString())) {
result.put("code", msg.toString());
}else{
result.put("code", "企业电子账户录入成功。");
}
return JSON_RESULT;
}
private Map<String,String> CLUSTERMAP=ImmutableMap.<String, String>builder()
.put(Constant.CLUSTER_CODE_20, "2.0")
.put(Constant.CLUSTER_CODE_JD, "JD")
.put(Constant.CLUSTER_CODE_KD, "KD")
.put(Constant.CLUSTER_CODE_LL, "LL")
.build();
private void insert(AccountCSVModel accountCSVModel, String code) throws CustomException {
String version = CLUSTERMAP.get(code);
HttpSendService.httpReq(version, genHttpRequest(accountCSVModel), Constant.TRY_HTTP_TIMES);
}
private JSONObject genHttpRequest(AccountCSVModel accountCSVModel) {
JSONObject jsonObject = new JSONObject();
jsonObject.put("p2p", accountCSVModel.getP2p());
jsonObject.put("name", accountCSVModel.getName());
jsonObject.put("idNo", accountCSVModel.getIdNo());
jsonObject.put("idType", accountCSVModel.getIdType());
jsonObject.put("idCard", accountCSVModel.getCardNo());
jsonObject.put("accountId", accountCSVModel.getAccountId());
jsonObject.put("acctUse", accountCSVModel.getAcctUse());
jsonObject.put("openDate", accountCSVModel.getOpenDate());
jsonObject.put("mobile", accountCSVModel.getBindMobile());
return jsonObject;
}
解析Excel:
public static List<AccountCSVModel> parseExcel(String path) throws InvalidFormatException, IOException {
FileInputStream xlsOrxlsxFile = new FileInputStream(path);
List<AccountCSVModel> accountCSVModelList = new ArrayList<AccountCSVModel>();
Workbook wb = WorkbookFactory.create(xlsOrxlsxFile);
int sheetNum = wb.getNumberOfSheets();
Sheet sheet = null;
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
sheet = wb.getSheetAt(sheetIndex);
Row row = null;
int firstRowNum = sheet.getFirstRowNum()+1;
int lastRowNum = sheet.getLastRowNum();
for (int rowIndex = firstRowNum ; rowIndex <= lastRowNum; rowIndex++) {
row = sheet.getRow(rowIndex);
if (null != row) {
AccountCSVModel accountCVSModel = new AccountCSVModel();
accountCVSModel.setP2p(getValue(row.getCell(1)));
accountCVSModel.setIdNo(getValue(row.getCell(2)));
accountCVSModel.setIdType(getValue(row.getCell(3)));
accountCVSModel.setName(getValue(row.getCell(4)));
accountCVSModel.setBindMobile(getValue(row.getCell(5)));
accountCVSModel.setAcctUse(getValue(row.getCell(6)));
accountCVSModel.setCardNo(getValue(row.getCell(7)));
accountCVSModel.setAccountId(getValue(row.getCell(8)));
accountCVSModel.setOpenDate(getValue(row.getCell(9)));
accountCSVModelList.add(accountCVSModel);
}
}
}
return accountCSVModelList;
}
// 转换数据格式
private static String getValue(Cell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
注意:form表单必须表明:enctype="multipart/form-data"
并且上上传文件只能form表单提交。不能ajax。
ajax实现:待实现
- Excel上传示例及后台解析Excel
- ALSM_EXCEL_TO_INTERNAL_TABLE 上传EXCEL示例
- 上传excel 解析BOM
- POI:上传EXCEL解析
- SpringMVC上传、解析Excel
- SpringMVC上传、解析Excel
- Struts2 POI导出Excel 基本思路解析及相关示例代码
- ExceL文档2.上传解析Excel文档
- springmvc上传excel并解析excel
- 通过poi解析Excel示例
- struts2 解析上传的Excel
- 文件上传之解析EXCEL
- 上传并解析excel文件
- Excel 上传 解析 生成 下载
- Java上传Excel并解析
- poi导出excel后台代码示例
- ASP.NET操作Excel---上传Excel后解析Excel文件
- Excel实现上传 导入 解析Excel 页面展示Excel数据
- java通过JDBC链接SQLServer2012
- 倒计时封装 程序进入后台不会暂停计时器
- React Native入门(十一)之屏幕适配
- Error:Unable to find method 'com.android.build.gradle.api.BaseVariant.getOutputs()Ljava/util/List;'.
- 【KMP】【poj3167】【bzoj1729】Cow Patterns (kmp变形)
- Excel上传示例及后台解析Excel
- 学习Java8--Lambda表达式
- Spark 编程指南(一)
- 论文代发过程注意哪些事项
- 基于802.11协议的节能技术
- ImageNet Evolution论文笔记(3)
- Ucam v8.4.1 WinALL 1CD完整好用\
- JZOJ 5425. 【NOIP2017提高A组集训10.25】数论
- android中的文件操作详解以及内部存储和外部存储