Excel上傳示例及後臺解析Excel

<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實現：待實現