今天的系统开发中有一个需要解析excel数据的功能,然后将excel的数据插入到数据库。
首先要制作一个excel的导入模版,然后按模版格式读取数据。
实现代码如下,
前台代码:
<script>
//确定
function ok_onclick(){
var validateForm = new Validater();
var frm = document.certificateForm;
//TODO: 以后要把其他校验规则加上
validateForm.addRule(new SimpleRule(frm.CERTBATCH, validateRequired,"批量证书excel附件"));
if ( validateForm.validate() ){
if(confirm("<bean:message key="common.tip.addConfirm"/>") == true){
frm.submit();
}
}
}
function downloadCert(){
var url = '<c:url value="/itms/template/certTemplate.xls"/>';
var obj = document.getElementById('download');
obj.contentWindow.location.href = url;
}
windowHandler.onload = function(){
<c:if test="${ not empty _tipMsg}">
alert("${_tipMsg}");
</c:if>
}
</script>
</head>
<body>
<html:form action="/certificate/certificateAction" method="POST" enctype="multipart/form-data" >
<input type="hidden" id="_actionType" name="_actionType" value="certBatchInput" />
<table class="buttonTable">
<tr>
<td>
<owk:button property="okBtn" οnclick="ok_onclick()" value="导入"/>
<owk:button property="okBtn" οnclick="downloadCert()" value="下载模版"/>
</td>
<td width="60px" ></td>
</tr>
</table>
<table class="subjectEditTable">
<tr>
<td><div class="subject">
<img src="<c:url value='/owk/common/images/Title1.gif'/>">
批量证书录入
</div></td>
</tr>
</table>
<table class="updateTable">
<tr>
<td class="label">
批量证书上传
</td>
<td class="input" colspan="120">
<input type="file" required="true" id="CERTBATCH" name="CERTBATCH" style="width:600px;"></input>
</td>
</tr>
</table>
</html:form>
后台的action代码:
//批量证书信息导入功能,解析excel数据
@SuppressWarnings("unchecked")
public ActionForward certBatchInput(ActionMapping pMapping,
ActionForm pForm, HttpServletRequest pRequest,
HttpServletResponse pResponse) throws Exception {
//读取上传的模版文件
FormFile fileItem = (FormFile) pForm.getMultipartRequestHandler()
.getFileElements().get("CERTBATCH");
InputStream is = null;
try {
is = fileItem.getInputStream();
} catch (IOException e) {
e.printStackTrace();
throw new BusinessException("获取文件对象输入流出错");
}
Workbook workbook = ExcelUtil.getWorkBook(is);
// 获取导入列表sheet(0)
Sheet sheet = workbook.getSheet(0);
if (sheet == null) {
throw new BusinessException("获取Excel第一个列表数据失败");
}
Cell[] row1 = sheet.getRow(1);
Cell[] row2 = sheet.getRow(2);
if ( !row1[0].getContents().trim().equalsIgnoreCase(
"招商银行分行IT管理系统证书信息录入模板")
|| !row2[0].getContents().trim().equalsIgnoreCase("分行名称")
|| !row2[1].getContents().trim().equalsIgnoreCase("姓名")
|| !row2[2].getContents().trim().equalsIgnoreCase("一事通ID")
|| !row2[3].getContents().trim().equalsIgnoreCase("专业条线")
|| !row2[4].getContents().trim().equalsIgnoreCase("级别")
|| !row2[5].getContents().trim()
.equalsIgnoreCase("认证时间")) {
throw new BusinessException("对不起,模板格式不匹配,无法完成上传,请下载模板填写并上传!");
}
// 获取sheet数据
List<Map<String, String>> dataList = ExcelUtil.getRowList(sheet, 3, 0);
// 处理sheet数据
List<String> errorList = new ArrayList<String>();
LOOP: for (int i = 0; i < dataList.size(); i++) {
OWKMap params = new OWKHashMap();
Map<String, String> data = dataList.get(i);
// 获取excel中分行编号
String branchCode = (String) data.get("0");
// 获取excel中姓名
String certOwner = (String) data.get("1");
// 获取excel中一事通ID
String certOwnerID = (String) data.get("2");
// 获取excel中责任条线
String busLine = (String) data.get("3");
// 获取excel中级别
String level = (String) data.get("4");
// 获取excel中认证获取时间
String certDate = (String) data.get("5");
// 分行编号验证
if (StringUtils.isEmpty(branchCode)) {
errorList.add(String.format("第%d行制度名称为空。", i + 4));
continue LOOP;
}
//证书人姓名验证
if (StringUtils.isEmpty(certOwner)) {
errorList.add(String.format("第%d行制度级别内容为空或不符合格式。", i + 4));
continue LOOP;
}
//证书人一事通ID验证
if (StringUtils.isEmpty(certOwnerID)) {
errorList.add(String.format("第%d行制度级别内容为空或不符合格式。", i + 4));
continue LOOP;
}
// 责任条线校验以及翻译
if (StringUtils.isEmpty(busLine)) {
errorList.add(String.format("第%d行责任条线内容为空或不符合格式。", i + 4));
continue LOOP;
}
// 证书级别校验
if (StringUtils.isEmpty(level)) {
errorList.add(String.format("第%d行证书级别为空。", i + 4));
continue LOOP;
} else if (level.trim().equalsIgnoreCase("初级")) {
level = Constants.CERTIFICATE_CERTLEVEL_LOWLEVEL;
}else if (level.trim().equalsIgnoreCase("中级")) {
level = Constants.CERTIFICATE_CERTLEVEL_MIDDLELEVEL;
}else if (level.trim().equalsIgnoreCase("高级")) {
level = Constants.CERTIFICATE_CERTLEVEL_HIGHLEVEL;
}else {
errorList.add(String.format("第%d行证书级别输入级别不对。", i + 4));
continue LOOP;
}
// 认证获取时间校验
if (StringUtils.isEmpty(certDate)) {
errorList.add(String.format("第%d行认证获取时间为空。", i + 4));
continue LOOP;
} else if(certDate.length() != 8){
errorList.add(String.format("第%d行认证获取时间输入格式不正确。", i + 4));
continue LOOP;
}
// 证书录入日期
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmm");
Date today = new Date();
String inputDate = format.format(today);
params.put("INPUTDATE", inputDate);
params.put("BRANCHNO", branchCode);
params.put("CERTOWNER", certOwner);
params.put("CERTOWNERID", certOwnerID);
String lineName = Mapper.getLineName(busLine);
params.put("LINEID", lineName);
params.put("LEVEL", level);
params.put("CERTDATE", certDate);
//获取分行拼音简写,用于生成编号
String branchSpell = Mapper.getBranchSpell(branchCode);
params.put("BRANCHSPELL", branchSpell);
//首先检查数据库中是否已存在该人员的证书记录,如存在先删除再插入新数据
try {
List certList = CertificateService.getInstance().getCertByCertownerID(params);
if (certList.size()>0) {
CertificateService.getInstance().deleteCertRepeat(params);
}
CertificateService.getInstance().insertCertificate(params);
} catch (BusinessException ex) {
ex.printStackTrace();
pRequest.setAttribute("_tipMsg", "证书信息新增失败!" + ex.getMessage());
}
}
return pMapping.findForward("batchIndex");
}
excelutil的代码:
/**
*
*/
package com.rb.itms.util;
/**
*
* @author HO274755
*
*/
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import com.rb.owk.commons.lang.base.orm.BusinessException;
import com.rb.owk.commons.lang.key.KeyGeneratorHelper;
/**
* EXCEL工具类
*
* @author HO274509
*
*/
public class ExcelUtil {
private ExcelUtil() {
};
/**
* 获取excel对象
*
* @param is
* @return
* @throws BusinessException
*/
public static Workbook getWorkBook(InputStream is) throws BusinessException {
Workbook workBook = null;
try {
workBook = Workbook.getWorkbook(is);
} catch (BiffException e) {
throw new BusinessException("生成Excel工作薄出错!");
} catch (IOException e) {
throw new BusinessException("读取输入流出错!");
}
return workBook;
}
/**
* 获取sheet内容,以行为map对象(key值为列值)组装的数组
*
* @param sheet
* @return
*/
public static List<Map<String, String>> getRowList(Sheet sheet) {
return getRowList(sheet, 0, 0);
}
/**
* 获取sheet内容,以行为map对象(key值为列值)组装的数组 下标从0开始
*
* @param sheet
* @return
*/
@SuppressWarnings("unchecked")
public static List<Map<String, String>> getRowList(Sheet sheet,
int startRowIndex, int startColumnIndex) {
int rowCount = sheet.getRows();// 总行数
int columnCount = sheet.getColumns();// 总列数
List<Map<String, String>> result = new ArrayList<Map<String, String>>();
for (int r = startRowIndex; r < rowCount; r++) {// 遍历
Map data = new HashMap<String, String>();
for (int c = startColumnIndex; c < columnCount; c++) {
data.put(String.valueOf(c), sheet.getCell(c, r).getContents()
.trim());
}
result.add(data);
}
return result;
}
/**
* 把对象数组写到一行excel的不同单元格中
*
* @param ws
* @param rowNum
* @param cells
* @throws WriteException
* @throws RowsExceededException
*/
public static void putRows(WritableSheet ws, int rowNum, String[] cells)
throws RowsExceededException, WriteException {
for (int c = 0; c < cells.length; c++) {// 写一行
Label cell = new Label(c, rowNum, cells[c]);
ws.addCell(cell);
}
}
/**
* 把对象数组写到一列excel的不同单元格中
*
* @param ws
* @param rowNum
* @param cells
* @throws WriteException
* @throws RowsExceededException
*/
public static void putColumns(WritableSheet ws, int columnNum,
String[] cells) throws RowsExceededException, WriteException {
for (int r = 0; r < cells.length; r++) {// 写一行
Label cell = new Label(columnNum, r, cells[r]);
ws.addCell(cell);
}
}
@SuppressWarnings("unchecked")
public static void main(String[] args) throws BusinessException,
IOException, RowsExceededException, WriteException {
InputStream is = new FileInputStream(
"E:\\FireFly\\内控风险管理系统\\01文档\\02需求文档\\01客户需求类文档\\1.xls");
Workbook workBook = getWorkBook(is);
List<Map<String, String>> rowList = getRowList(workBook.getSheet(0));
// OutputStream os = new FileOutputStream("E:\\xiaolu.txt");
FileWriter writer = new FileWriter("E:\\xiaolu.txt");
BufferedWriter bufferWriter = new BufferedWriter(writer);
for (Map row : rowList) {
StringBuffer sb = new StringBuffer();
sb.append("\"");
sb.append(KeyGeneratorHelper.generateUUIDHex());
sb.append("\",");
sb.append("\"");
sb.append((String) row.get("0"));
sb.append("\",");
sb.append("\"");
sb.append((String) row.get("1"));
sb.append("\",");
sb.append("\"");
sb.append((String) row.get("2"));
sb.append("\"\n");
bufferWriter.write(sb.toString());
}
bufferWriter.close();
workBook.close();
// 创建可编辑excel文件
WritableWorkbook writeBook = Workbook.createWorkbook(new File(
"E:\\sz.xls"));
// 创建sheet页
WritableSheet ws = writeBook.createSheet("深圳用户", 0);
String[] title={"员工号","姓名","所在机构","分行代码","网点代码"};
int rowNum = 0;
putRows(ws, rowNum, title);//标题
FileReader reader = new FileReader("E:\\sz_users.txt");
BufferedReader bufferReader = new BufferedReader(reader);
String value;
while ((value = bufferReader.readLine()) != null) {
rowNum++;
String[] cells = value.split(" ");
putRows(ws, rowNum, cells);
}
writeBook.write();
writeBook.close();
bufferReader.close();
}
}