href rst var dao operation chan ide ffi ram
1、html
- <td class="queryTdRight" style="width:22%;"> 交易年度:
- <input id="dealYear" name="dealYear" style="width: 140px"/>
- </td>
- <td class="queryTdRight" style="width:40%;">
- 请选择文件导入:
- <input class="easyui-filebox" style="width:75%" name="uploadExcel" id="uploadExcel" data-options="onChange:function(){},prompt:‘选择文件‘,accept:‘application/vnd.ms-excel application/x-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet‘"/>
- </td>
- <td align="left" style="padding-left:5px;">
- <a href="javascript:uploadExcel()" class="easyui-linkbutton" data-options="iconCls:‘icon-upload‘">上 传</a>
- <a href="javascript:matching()" class="easyui-linkbutton" data-options="iconCls:‘icon-goto‘">匹 配</a>
- <a href="javascript:saveUploadExcel()" class="easyui-linkbutton" data-options="iconCls:‘icon-download‘">导 入</a>
- <a href="javascript:deleteData()" class="easyui-linkbutton" data-options="iconCls:‘icon-cancel‘">删 除</a>
- <a href="javascript:initTable(0)" class="easyui-linkbutton" data-options="iconCls:‘icon-search‘">查 询</a>
- </td>
2、js
- /**
- * 上传按钮
- *
- */
- function uploadExcel() {
- //得到上传文件的全路径
- var fileName = $(‘#uploadExcel‘).filebox(‘getText‘);
- if (fileName == "") {
- $.messager.alert(‘提示‘, ‘请选择上传文件!‘, ‘info‘);
- return;
- }
- $.messager.progress({
- title: ‘提示‘,
- msg: ‘正在校验,请稍候...‘
- });
- $.ajax({
- url: basePath + "/demandForecast/selectTempData.action",
- data: "",
- type: "post",
- cache: false,
- dataType: "json",
- success: function(data) {
- $.messager.progress("close");
- var d = data.resultData;
- if (d.length > 0) {
- $.messager.confirm(‘提示‘, ‘数据已经存在,是否要覆盖?‘,
- function(r) {
- if (r) {
- importExcel(fileName);
- }
- });
- } else {
- importExcel(fileName);
- }
- },
- error: function(error) {
- },
- complete: function() {
- }
- });
- }
- /**
- * 导入文件上传
- */
- function importExcel(fileName) {
- var dealYear = $(‘#dealYear‘).combobox(‘getValue‘);
- if (dealYear != "") {
- //进行基本校验
- //debugger;
- if (fileName == "") {
- $.messager.alert(‘提示‘, ‘请选择上传文件!‘, ‘info‘);
- } else {
- //对文件格式进行校验
- var f_content = fileName;
- var fileext = f_content.substring(f_content.lastIndexOf("."), f_content.length);
- fileext = fileext.toLowerCase();
- //fileName=encodeURI(fileName);
- if (fileext == ‘.xls‘ || fileext == ‘.xlsx‘) {
- /*异步提交*/
- $.messager.progress({
- title: ‘提示‘,
- msg: ‘正在上传文件,请稍候...‘
- });
- $("#questionTypesManage").ajaxSubmit({
- url: basePath + "/demandForecast/importToTemp.action?dealYear=" + encodeURI(encodeURI(dealYear)),
- dataType: "text/html",
- type: ‘post‘,
- clearForm: false,
- complete: function(data) {
- $.messager.progress("close");
- data = "" + data.responseText;
- var sd = data.split("\"");
- $.messager.alert(‘提示‘, sd[1], ‘info‘,
- function() {
- initTable();
- });
- }
- });
- } else {
- $.messager.alert(‘提示‘, ‘请选择xls / xlsx格式文件!‘, ‘info‘);
- $(‘#uploadExcel‘).filebox(‘setValue‘, ‘‘);
- }
- }
- } else {
- $.messager.alert(‘提示‘, ‘请选择日期!‘, ‘info‘);
- }
- }
3、struts xml
- <action name="importToTemp" class="com.icss.action.demandForecast.DemandForecastAction" method="importToTemp">
- <result name="importResult" type="json">
- <param name="contentType">
- text/html
- </param>
- <param name="root">mark</param>
- </result>
- </action>
4、action
- public String importToTemp() {
- try {
- demandForecastService.saveMonthSalePlanImportTemp(new FileInputStream(uploadExcel), dealYear);
- mark = "上传成功";
- operationLogService.saveLog(ConstantConfig.DEMAND_FORECAST_IMPORT_CODE, ConstantConfig.DEMAND_FORECAST_IMPORT_NAME, "[电脑][上传][成功]");
- } catch(Exception e) {
- e.printStackTrace();
- mark = "上传失败,模板数据不规范";
- try {
- operationLogService.saveLog(ConstantConfig.DEMAND_FORECAST_IMPORT_CODE, ConstantConfig.DEMAND_FORECAST_IMPORT_NAME, "[电脑][上传][失败][" + e.getMessage() + "]");
- } catch(Exception e1) {
- e1.printStackTrace();
- }
- }
- return "importResult";
- }
5、service
- @Override public void saveMonthSalePlanImportTemp(FileInputStream is, String dealYear) throws Exception {
- //先清空临时表
- String condition = "1=1";
- baseDao.delete("WP_DEMAND_FORECAST_IMPORT_TEMP", condition);
- Workbook wb = WorkbookFactory.create(is);
- Sheet sheet = wb.getSheetAt(0); //获取工作簿
- DemandForecastImportTemp demandForecastImportTemp = null;
- for (int i = 0, rowNum = sheet.getLastRowNum(); i < rowNum; i++) { //迭代行
- demandForecastImportTemp = new DemandForecastImportTemp();
- Row row = sheet.getRow(i + 1); //获取工作簿中的行(不取第一行)
- boolean isLasttextRow = false; //是否最后一行
- for (int j = 0, cellNum = row.getLastCellNum(); j < cellNum; j++) { //迭代列
- Cell cell = row.getCell(j); //取得列中的值
- if (cell == null) {
- break;
- }
- String cellValue = dataType(cell);
- if (j == 0 && StringUtils.isEmpty(cellValue)) {
- isLasttextRow = true;
- break;
- }
- setData(demandForecastImportTemp, cellValue, j, dealYear);
- }
- if (isLasttextRow) {
- break;
- }
- //保存数据
- demandForecastImportTemp.setDealYear(dealYear);
- this.save(demandForecastImportTemp);
- }
- }
- private void save(DemandForecastImportTemp demandForecastImportTemp) {
- ArrayList < String > columns = new ArrayList < String > ();
- ArrayList < Object > values = new ArrayList < Object > ();
- columns.add("ORG_ID");
- values.add(demandForecastImportTemp.getOrgId());
- columns.add("ORG_NAME");
- values.add(demandForecastImportTemp.getOrgName());
- columns.add("IND_ID");
- values.add(demandForecastImportTemp.getIndId());
- columns.add("IND_NAME");
- values.add(demandForecastImportTemp.getIndName());
- columns.add("CIG_CODE");
- values.add(demandForecastImportTemp.getCigCode());
- columns.add("CIG_NAME");
- values.add(demandForecastImportTemp.getCigName());
- columns.add("CIG_CLASS");
- values.add(demandForecastImportTemp.getCigClass());
- columns.add("CIG_PRICE");
- values.add(demandForecastImportTemp.getCigPrice());
- columns.add("YEAR_DEMAND");
- values.add(demandForecastImportTemp.getYearDemand());
- columns.add("PRE_HALFYEAR_STORE");
- values.add(demandForecastImportTemp.getPreHalfyearStore());
- columns.add("HALFYEAR_DEMAND_FORECAST");
- values.add(demandForecastImportTemp.getHalfyearDemandForecast());
- columns.add("YEAR_END_STORE");
- values.add(demandForecastImportTemp.getYearEndStore());
- columns.add("NEXT_HALFYEAR_DEMAND");
- values.add(demandForecastImportTemp.getNextHalfyearDemand());
- columns.add("NEXT_HALFYEAR_AUDIT_DEMAND");
- values.add(demandForecastImportTemp.getNextHalfyearAuditDemand());
- columns.add("CHECK_STATUS");
- values.add(demandForecastImportTemp.getCheckStatus());
- columns.add("DEAL_YEAR");
- values.add(demandForecastImportTemp.getDealYear());
- baseDao.insert("WP_DEMAND_FORECAST_IMPORT_TEMP", columns, values);
- }
- //判断excel单元格内容的格式,并对其进行转换,以便插入数据库
- private final String dataType(Cell cell) {
- switch (cell.getCellType()) {
- case 0:
- return String.valueOf(cell.getNumericCellValue());
- case 1:
- return cell.getStringCellValue();
- case 2:
- return String.valueOf(cell.getDateCellValue());
- case 4:
- return String.valueOf(cell.getBooleanCellValue());
- case 5:
- return String.valueOf(cell.getErrorCellValue());
- }
- return "";
- }
- private final void setData(DemandForecastImportTemp demandForecastImportTemp, String cellValue, int j, String dealYear) {
- if (j == 0) {
- demandForecastImportTemp.setOrgName(cellValue);
- } else if (j == 1) {
- demandForecastImportTemp.setCigName(cellValue);
- } else if (j == 2) {
- demandForecastImportTemp.setCigCode(cellValue);
- } else if (j == 3) {
- demandForecastImportTemp.setIndName(cellValue);
- } else if (j == 4) {
- double cigClass = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0": cellValue);
- demandForecastImportTemp.setCigClass(cigClass);
- } else if (j == 5) {
- double cigPrice = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0": cellValue);
- demandForecastImportTemp.setCigPrice(cigPrice);
- } else if (j == 6) {
- double yearDemand = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0": cellValue);
- demandForecastImportTemp.setYearDemand(yearDemand);
- } else if (j == 7) {
- double preHalfyearStore = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0": cellValue);
- demandForecastImportTemp.setPreHalfyearStore(preHalfyearStore);
- } else if (j == 8) {
- double halfyearDemandForecast = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0": cellValue);
- demandForecastImportTemp.setHalfyearDemandForecast(halfyearDemandForecast);
- } else if (j == 9) {
- double yearEndStore = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0": cellValue);
- demandForecastImportTemp.setYearEndStore(yearEndStore);
- } else if (j == 10) {
- double nextHalfyearDemand = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0": cellValue);
- demandForecastImportTemp.setNextHalfyearDemand(nextHalfyearDemand);
- } else if (j == 11) {
- double nextHalfyearAuditDemand = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0": cellValue);
- demandForecastImportTemp.setNextHalfyearAuditDemand(nextHalfyearAuditDemand);
- }
- demandForecastImportTemp.setDealYear(dealYear);
- demandForecastImportTemp.setCheckStatus(0);
- }
spring+struts2+mybatis中poi导入excel数据
来源: http://www.bubuko.com/infodetail-2328676.html