panelMain.js部分代码
// 导入
"afterlead" : function(toolbar) {
me.excelImport();
},
// 导出excel
"afterexport" : function(toolbar) {
me.excelExport();
}
// 导入
excelImport : function() {
var me = this;
Ext.create('app.system.common.CommonExcelImportWindow',{
tempUrl:'stockInformationController/exportTemplate.do',
importUrl:'stockInformationController/excelImport.do',
title:"导入股金信息",
grid:me.stockInformationGrid
}).show();
me.reLoad();
},
/**
* 导出Excel
*/
excelExport : function() {
var me =this;
var conditions = me.stockInformationToolBar.getConditions();
conditions=conditions?conditions:{};
var dataDate =conditions.dataDate;
var stockAccount =conditions.stockAccount;
var name =conditions.name;
var certificateNo =conditions.certificateNo;
// if (!instNo || !orgName || !custMgrId || !custMgrName || !dataDateStart || !dataDateEnd) {
// Ext.toastWarn('请选择机构号、机构名称、客户经理编号、客户经理名称、数据时间!');
// return false;
// }
Ext.Msg.confirm('友情提示','您确定要导出吗？',function(btn){
if(btn=='yes'){
location.href = "stockInformationController/exportExcel.do?dataDate=" + dataDate + "&stockAccount ="+stockAccount +"&name = "+ name +"&certificateNo = "+ certificateNo;
}
});
}
toolBar部分代码
btnArr.push({
text : '导入Excel',
iconCls : 'bt-login',
handler : function() {
me.fireEvent("afterlead",me);
}
});
btnArr.push({
text : '导出Excel',
iconCls : 'bt-logout',
handler : function() {
me.fireEvent("afterexport", me);
}
});
controller部分代码
/**
* 导出Excel模板
*
* @param date
* @param response
*/
@RequestMapping(value = "/exportTemplate.do")
@ResponseBody
public void exportTemplate(HttpServletResponse response) {
stockInformationService.exportTemplate(response);
}
/**
* Excel导入任务
*
* @param request
* @return
*/
@SuppressWarnings("all")
@RequestMapping(value = "/excelImport.do", produces = "application/json; charset=utf-8")
@ResponseBody
public String excelImport(HttpSession paramHttpSession, HttpServletRequest request, HttpServletResponse response) {
UserSession userSession = SessionListener.getInstance().getUserSession(paramHttpSession);
String resultMsg = null;
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
InputStream in = null;
List<List<Object>> listob = null;
MultipartFile file = multipartRequest.getFile("file");
if (file.isEmpty()) {
resultMsg = "请重新上传正确的文件";
}
try {
in = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
}
Workbook workbook = PmsExcelUtil.createWorkbook(in);
try {
resultMsg = stockInformationService.insertExcelImport(workbook);
if(StringUtils.isNotEmpty(resultMsg)){
return PmsUtils.response(resultMsg);
}else{
return PmsUtils.response(null);
}
}catch (Exception e) {
return PmsUtils.response("EXCEL导入失败");
}
}
/**
* 导出数据
* @param request
* @param response
*/
@RequestMapping(value = "/exportExcel.do", produces = "application/json; charset=utf-8")
@ResponseBody
public void exportExcel(StockInformationVo stockInformationVo,HttpServletRequest request, HttpServletResponse response) {
stockInformationService.exportExcel(stockInformationVo,response);
}
service部分代码
static final String[] headers = { "数据日期", "股金账号", "户名", "账户类型(0:个人账户,1:企业账户)", "客户证件号",
"余额", "帐户状态(0:正常,1:异常)" };
/**
* 下载excel导入模板
*
**/
@SuppressWarnings("rawtypes")
public void exportTemplate(HttpServletResponse response) {
String title = "股金信息导入";
String fileName = PmsUtils.uft82Iso(title);
PmsExcelUtil excelUtil = new PmsExcelUtil();
String[] eg = { "2019-01-01", "63233456223435", "张三", "0", "350403003020212",
"12345", "0"};
XSSFWorkbook workbook = excelUtil.getExcelTemplateForXlsx(title,
headers, eg);
PmsExcelUtil.exportExcelForXlsx(workbook, response, fileName);
}
/**
* 导入
*
* @param workbook
* @return
*/
public String insertExcelImport(Workbook workbook) {
String result = "";
Sheet sheet = workbook.getSheetAt(0);
// 内容的首行号
int firstBobyRowNum = 2;
// 最大行号
int lastRowNum = sheet.getLastRowNum();
if (!sheet.getRow(1).getCell(1).toString().equals("股金账号")) {
return "EXCEL内容缺少关键列[ 股金帐号]，无法导入";
}
// 遍历当前sheet中的所有行
for (int rowNum = firstBobyRowNum; rowNum <= lastRowNum; rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
String dataDate = "";
String stockAccount = "";
String name = "";
String accountType = "";
String certificateNo = "";
String balance = "";
String accountStatus = "";
if (StringUtils.isEmpty(getExcelCellValue(row.getCell(1)))) {
result += "第" + (rowNum - 1) + "行数据,股金帐号为空！\\n";
continue;
} else {
stockAccount = getExcelCellValue(row.getCell(1));
}
Map<String, Object> props = new HashMap<String, Object>();
props.put("stockAccount", stockAccount);
StockInformation stockInformation = (StockInformation) commonDao.getObjectByProperties(
StockInformation.class, props);
if (stockInformation != null) {
result += "第" + (rowNum - 1) + "行数据,股金账号已存在！\\n";
continue;
} else {
StockInformationVo stockInformationVo = new StockInformationVo();
dataDate = getExcelCellValue(row.getCell(0));
stockInformationVo.setDataDate(dataDate);
stockInformationVo.setStockAccount(stockAccount);
name = getExcelCellValue(row.getCell(2));
stockInformationVo.setName(name);
accountType = getExcelCellValue(row.getCell(3));
stockInformationVo.setAccountType(accountType);
certificateNo = getExcelCellValue(row.getCell(4));
stockInformationVo.setCertificateNo(certificateNo);
balance = getExcelCellValue(row.getCell(5));
stockInformationVo.setBalance(balance);
accountStatus = getExcelCellValue(row.getCell(6));
stockInformationVo.setAccountStatus(accountStatus);
saveStockInformation(stockInformationVo);
}
}
return result;
}
public static String getExcelCellValue(Cell cell) {
String ret = "";
try {
if (cell == null) {
ret = "";
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
ret = (cell.getStringCellValue().trim());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
ret = NumberToTextConverter.toText(cell.getNumericCellValue());
String temp = ret.substring(ret.indexOf(".") + 1, ret.length());
try {
if (Integer.parseInt(temp) == 0) {
ret = ret.substring(0, ret.indexOf("."));
}
} catch (Exception ex) {
}
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {// 有公式的Excel单元格
try {
ret = String.valueOf(cell.getStringCellValue());
} catch (IllegalStateException e) {
ret = String.valueOf(cell.getNumericCellValue());
}
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
ret = "" + cell.getErrorCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
ret = "" + cell.getBooleanCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
ret = "";
}
} catch (Exception ex) {
ex.printStackTrace();
ret = "";
}
return ret;
}
/**
* 导出excel数据
*
* @param rptAllLoanAnalyVo
* @param response
*/
@SuppressWarnings("unchecked")
public void exportExcel(StockInformationVo stockInformationVo, HttpServletResponse response) {
stockInformationVo = stockInformationVo == null ? new StockInformationVo() : stockInformationVo;
stockInformationVo.setLimit(Integer.MAX_VALUE);
Page<StockInformationVo> stockInformationList = (Page<StockInformationVo>) findStockInformations(stockInformationVo);
List<StockInformationVo> rpts3 = (List<StockInformationVo>) stockInformationList.getRecords();
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
for (StockInformationVo vo : rpts3) {
Map<String, Object> result = new HashMap<String, Object>();
result.put("数据日期", vo.getDataDate());
result.put("股金账号", vo.getStockAccount());
result.put("户名", vo.getName());
result.put("账户类型(0:个人账户,1:企业账户)", vo.getAccountType());
result.put("客户证件号", vo.getCertificateNo());
result.put("余额", vo.getBalance());
result.put("帐户状态(0:正常,1:异常)", vo.getAccountStatus());
resultList.add(result);
}
String title = "股金信息";
String fileName = PmsUtils.uft82Iso(title);// 文件名
PmsExcelUtil<List<Map<String, Object>>> excelUtil = new PmsExcelUtil<List<Map<String, Object>>>();
XSSFWorkbook workbook = excelUtil.excelDataByListForXlsx(title, headers, resultList);
PmsExcelUtil.exportExcelForXlsx(workbook, response, fileName);
}
public XSSFWorkbook excelDataByListForXlsx(String title, String[] headers, List<Map<String, Object>> resultList) {
return excelDataByListForXlsxV2(title, headers, headers, resultList);
}
public XSSFWorkbook excelDataByListForXlsxV2(String title, String[] headers, String[] fields,
List<Map<String, Object>> resultList) {
XSSFWorkbook workbook = new XSSFWorkbook();// 声明一个工作薄
XSSFSheet sheet = workbook.createSheet(title);// 生成一个表格、并设置sheet的标题
sheet.setDefaultColumnWidth(20);
CellRangeAddress fristRowRegion = new CellRangeAddress(0, (short) 0, 0, (short) ((short) headers.length - 1));// 第一行
sheet.addMergedRegion(fristRowRegion);
XSSFCellStyle style = workbook.createCellStyle();// 生成一个样式
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 设置这些样式
XSSFFont font = workbook.createFont();// 生成一个字体
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
XSSFFont font1 = workbook.createFont();// 生成一个字体
font1.setColor(HSSFColor.BLACK.index);
font1.setFontHeightInPoints((short) 12);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
XSSFCellStyle style2 = workbook.createCellStyle();
style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 把字体应用到当前的样式
style2.setFont(font);
XSSFCellStyle style3 = workbook.createCellStyle();
style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
style3.setFont(font1);
// 产生表格标题行
XSSFRow fristRow = sheet.createRow(0);
XSSFCell fristCell = fristRow.createCell(0);
fristCell.setCellStyle(style);
fristCell.setCellValue(title);
// HSSFRow secondRow = sheet.createRow(1);
// HSSFCell secondCell = secondRow.createCell(0);
// secondCell.setCellStyle(style3);
// secondCell.setCellValue("导出人：AAA");
XSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLACK.index);
XSSFRow row = sheet.createRow(1);
for (short i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
Iterator iterator = resultList.iterator();
int index = 1;
while (iterator.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) iterator.next();
for (short i = 0; i < fields.length; i++) {
String fieldName = fields[i];
XSSFCell contentCell = row.createCell(i);
contentCell.setCellStyle(style2);
try {
Map map = null;
Object data = null;
if (t instanceof Map) {
map = (Map) t;
data = map.get(fieldName);
}
Boolean isNum = false;//data是否为数值型
Boolean isInteger=false;//data是否为整数
Boolean isPercent=false;//data是否为百分数
Boolean isIdCard=false;//data是否为身份证
if (data != null || "".equals(data)) {
//判断data是否为数值型
isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
//判断data是否为整数（小数部分是否为0）
isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
//判断data是否为百分数（是否包含“%”）
isPercent=data.toString().contains("%");
//是否身份证
isIdCard=this.cardCodeVerifySimple(data.toString());
}
//如果单元格内容是数值类型，涉及到金钱（金额、本、利），则设置cell的类型为数值型，设置data的类型为数值类型
if (isNum && !isPercent && !isIdCard) {
XSSFDataFormat df = workbook.createDataFormat(); // 此处设置数据格式
// if (isInteger) {
// contextstyle.setDataFormat(df.getFormat("#,#0"));//数据格式只显示整数
// }else{
// contextstyle.setDataFormat(df.getFormat("#,##0.00"));//保留两位小数点
// }
// 设置单元格格式
contentCell.setCellStyle(style2);
// 设置单元格内容为double类型
contentCell.setCellValue(Double.parseDouble(data.toString()));
} else {
contentCell.setCellStyle(style2);
// 设置单元格内容为字符型
String tmpData=data!=null?data+"":"";
contentCell.setCellValue(tmpData);
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} finally {
// 清理资源
}
}
}
return workbook;
}
//是否是身份证
private boolean cardCodeVerifySimple(String cardcode) {
//第一代身份证正则表达式(15位)
String isIDCard1 = "^[1-9]\\d{7}((0\\d)|(1[0-2]))(([0|1|2]\\d)|3[0-1])\\d{3}$";
//第二代身份证正则表达式(18位)
String isIDCard2 ="^[1-9]\\d{5}[1-9]\\d{3}((0\\d)|(1[0-2]))(([0|1|2]\\d)|3[0-1])((\\d{4})|\\d{3}[A-Z])$";
//验证身份证
if (cardcode.matches(isIDCard1) || cardcode.matches(isIDCard2)) {
return true;
}
return false;
}
1365
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
