对于数据量小的表进行增加,可以一条条增加,但是对于大数据往往希望可以从存满数据的Excel表中向数据库中倒入数据,这无疑是节约人力物力。那么上传Excel的本质是将每一条Excel数据固定格式后,将每一个cell作为一条数据的对应元素存起来![](https://img-blog.csdn.net/20130726091934046)
a)程序效果图
![](https://img-blog.csdn.net/20130726092021343)
b)excel中的格式(让用户明白)
![](https://img-blog.csdn.net/20130726092218328)
c)上传的数据
![](https://img-blog.csdn.net/20130726092308500)
d)根据上传文件给出用户错误提示
前台代码
1)界面
// 定义档案编辑页面
Ext.define('STAFF.view.StaffExcel', {
extend: 'Ext.window.Window',
alias : 'widget.staffexcel',
layout:'fit',
modal:true,
title:'员工导入Excel界面',
width:650,
height:400,
// maximized:true,
border:0,
autoShow: true,
constrain: true,
parentPage:null,
parentData:null,
//编辑窗口的构造函数
constructor: function(config) {
var me = this;
// 缓存parentPage属性
me.parentPage = config.parentPage;
// 参数设为数组或对象,那种方式有效使用那种方式,暂不清楚适用情况,更具wakecombo组件得出的经验
me.callParent([config]);
},
initComponent : function() {// 初始化组件
var me = this;
me.items = [me.createExcelForm()];
me.callParent();
},
createExcelForm : function() {
var me = this;
me.excelForm = Ext.create('Ext.form.Panel', {
id : "uploadForm",
width : 500,
bodyPadding : '10 10 0',
autoScroll : true,
defaults : {
anchor : '100%',
allowBlank : false,
msgTarget : 'side',
labelWidth : 50
},
items : [{
xtype : 'filefield',
id : 'form_file',
emptyText : '选择一个文件',
fieldLabel : '文件',
name : 'filePath',
allowBlank : false,
buttonText : '选择..'
},{
xtype:'displayfield'
,id:'staffExcelErrorMsg'
,height:100
,style:'border-top:1px solid #99bce8'
}],
buttons : [{
text : '上传',
id : "excelUpload"
}, {
text : '清空',
handler : function() {
this.up('form').getForm().reset();
}
}]
});
return me.excelForm;
}
});
2)
/**
* 导入Excel
*/
indexExcel : function() {
// 弹出界面对象建立
excelWin = Ext.widget('staffexcel', {
parentPage : ctrl
});
excelWin.on({
beforeclose : function(winSelf) {// 表格弹窗的关闭事件
ctrl.selectQuery();
}
});
},
excelUpload : function() {
var form = Ext.getCmp('excelUpload').up('form').getForm();
if (form.isValid()) {
form.submit({
url : restPath + path + 'uploadExcel',
waitMsg : '正在上传文件...',
success : function(form, action) {
var errMsg = "<b>文件上传成功:</b><br>";
Ext.getCmp("staffExcelErrorMsg").setValue(errMsg+"导入Excel成功");
},
failure : function(form, action) {
var errMsg = "<b>文件上传出错:</b><br>";
var errArr = action.result.result.split('请检查');
var resultErr = "" ;
for (var i = 0; i < errArr.length; i++) {
var err = errArr[i];
resultErr = resultErr + err +"<br>";
}
Ext.getCmp("staffExcelErrorMsg").setValue(errMsg+resultErr);
}
});
}
},
3)
/**
* 上传Excel
* @throws Exception
*/
@POST
@Consumes(MediaType.MULTIPART_FORM_DATA)
/* 此处必须设置为TXT_HTML,否则ExtJS前台不能获得正确的返回 */
@Produces("text/html;charset=utf-8")
@Path(value = "uploadExcel")
public String loadFile(@Context HttpServletRequest request) throws Exception {
// 输出日志
LogHelper.log(StaffCtrl.class, LogHelper.INFO, ACTION_NAME + "上传Excel" + START);
UploadResponse res = new UploadResponse();
//获取当前路径
String tmpDir = System.getProperty("java.io.tmpdir");
//最大数据量
final int permitedSize = 3 * 1024 * 1024;
MultipartRequestWrapper multipartRequest = new MultipartRequestWrapper(request, tmpDir, permitedSize, "UTF-8");
//获取文件(主要是文件路径)
File fileIn = multipartRequest.getFile("filePath");
//将文件包装成输入流
InputStream is = new FileInputStream(fileIn);
//返回数据列表(RowData类型列表信息)
List<RowData> dates = ExcelHelper.read(new ArchivesExcelResolver(), is);
//对excel内的(RowData类型列表信息)进行操作
String errMsg = staffService.uploadExcelDatas(dates);
if("".equals(errMsg)){
res.setSuccess(true);
res.setResult("success");
}else {
res.setSuccess(false);
res.setResult(errMsg);
}
LogHelper.log(StaffCtrl.class, LogHelper.INFO, ACTION_NAME + "上传Excel" + END);
ObjectMapper ob = new ObjectMapper();
String resStr = ob.writeValueAsString(res);
return resStr;
}
}
4)ExcelHelper
package com.beiyanght.ifks.main.business.upload.utils;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.wakeframework.common.exception.BusinessException;
import org.wakeframework.extend.excel.ExcelResolver;
import org.wakeframework.extend.excel.RowData;
/**
* Excel助手(用来将excel的数据解析到RowData列表中)
* @author
*
*/
public final class ExcelHelper {
public static <T> List<RowData> read(ExcelResolver<T> resolver, InputStream inputStream)
throws IOException, BusinessException
{
//获取一个Excel文件
HSSFWorkbook book = new HSSFWorkbook(inputStream);
//获取一个Sheet表单
Sheet sheet = book.getSheetAt(resolver.getSheetNum());
//将Sheet表单解析成RowData列表
List datas = readData(sheet, resolver);
//返回列表
return datas;
}
/**
* 将Sheet表单解析成RowData列表
* @param sheet表单
* @param resolver解析器
* @return
*/
public static List<RowData> readData(Sheet sheet, ExcelResolver<?> resolver) {
List<RowData> data = new ArrayList<RowData>();
for (Iterator localIterator1 = sheet.iterator(); localIterator1.hasNext(); ) {
//单元格
Cell cell;
//获取一行数据
Row row = (Row)localIterator1.next();
RowData cd = new RowData();
for (Iterator localIterator2 = row.iterator(); localIterator2.hasNext(); ) { cell = (Cell)localIterator2.next();
switch (cell.getCellType())
{
//单元格为string类型put(key,value)
case 1:
cd.put(Integer.valueOf(cell.getColumnIndex()), cell.getStringCellValue());
break;
//单元格为boolean类型
case 4:
cd.put(Integer.valueOf(cell.getColumnIndex()), Boolean.valueOf(cell.getBooleanCellValue()));
break;
//单元格为double类型
case 0:
cd.put(Integer.valueOf(cell.getColumnIndex()), Double.valueOf(cell.getNumericCellValue()));
case 2:
case 3:
}
}
//向RowData对象中插入行号
cd.setRowNum(row.getRowNum());
//向RowData对象中插入cell数目
cd.setCellCount(cd.getData().size());
//向List<RowData>对象data中插入RowData数据cd
data.add(cd);
}
//去掉第一行标题行
if(null != data && data.size() != 0){
data.remove(0);
}
//返回
return data;
}
}
5)后台校验
/**
* 上传excel信息
* @param dates解析到的excel列表信息
* @return 存储完毕的人员信息
* @throws Exception
*/
@Override
public String uploadExcelDatas(List<RowData> dates) throws Exception {
List<Staff> resustList = new ArrayList<Staff>();
//检查符合要求的数据列表
List<RowData> checkData = new ArrayList<RowData>();
//错误列表
List<String> error = new ArrayList<String>();
//返回的错误字符串
String returnErr = "";
//逐行检查列表信息,合格加入合格列表,不合格将错误信息加入错误列表
for(RowData data:dates){
String errMsg = check(data);
if ("".equals(errMsg)){
checkData.add(data);
}else {
error.add(errMsg);
}
}
//新增合格列表中的数据
for(RowData data:checkData){
try {
//建一条staff信息
Staff staff = builder(data);
if(null != staff){
resustList.add(staff);
}
} catch (Exception e) {
error.add(e.getMessage());
}
}
//拼接错误列表中的错误信息
if ((error != null) && (!(error.isEmpty()))) {
StringBuilder errorString = new StringBuilder();
for(String e:error){
errorString.append(e + "\n");
}
returnErr = errorString.toString();
}
//返回错误信息
return returnErr;
}
/**
* 检查每条excel信息是否符合要求
* @param data
* @return
* @throws BusinessException
*/
private String check(RowData data) throws BusinessException {
String returnErr = "";
if (data.getRowNum() > 0 && data.getCellCount() <= 5) {
/*检查数据如果数据有问题则抛出异常,终止解析*/
String errMsg ="";
Boolean returnFlag = true;
//人员编号0
if(null == data.getString(0)){
errMsg = errMsg + " 人员编号|";
returnFlag = false;
}
//姓名1
if(null == data.getString(1)){
errMsg = errMsg + " 姓名|";
returnFlag = false;
}
//性别2
Sex sex = null;
if(null != data.getString(2)){
sex = Sex.getMatchByName(data.getString(2));
}
if(null == data.getString(2) || null == sex ){
errMsg = errMsg + " 性别|";
returnFlag = false;
}
//分类3
List<StaffType> staffTypeList = null;
if(null != data.getString(3)){
staffTypeList = StaffType.findByStaffTypeNameInExcel(data.getString(3), EnableOrDisable.ENABLE);
}
if(null == data.getString(3) || null == staffTypeList || staffTypeList.size() == 0){
errMsg = errMsg + " 分类|";
returnFlag = false;
}
data.getString(3);
//备注4
data.getString(4);
if(!returnFlag){
if(null != data.getString(1)){
returnErr = "第 \'" + data.getRowNum() + "\' 行数据 : 人员为 \'"+data.getString(1)+"\' 的数据"+errMsg+"不符合规范请检查";
}else{
returnErr = "第 \'" + data.getRowNum() + "\' 行 \'未填写人员名称\' 的数据:"+errMsg+"不符合规范请检查";
}
return returnErr;
}else{
return returnErr;
}
}
else {//如果本行数据不等于三列那么将忽略,如果不想忽略则应该抛出BusinessException
if(null != data.getString(1)){
returnErr = "第 \'" + data.getRowNum() + "\' 行数据 : 人员名称为 \'"+data.getString(1)+"\' 的数据列数不符请检查";
}else{
returnErr = "第 \'" + data.getRowNum() + "\' 行 \'未填写人员名称\' 的数据:列数不符请检查";
}
return returnErr;
}
}
/**
* 构建staff信息
* @param data合格数据
* @return
* @throws BusinessException
*/
private Staff builder(RowData data) throws BusinessException {
Staff staff = new Staff();
try {
//如果唯一性校验失败返回
if(!Staff.uniqueCheckInExcel(data.getString(1))){
return null;
}
//人员编码
staff.setStaffCode(data.getString(0));
//姓名
staff.setName(data.getString(1));
//性别
Sex sex = Sex.getMatchByName(data.getString(2));
if(null != sex){
staff.setSex(sex);
}
//分类
StaffType staffType = null;
if(null != StaffType.findByStaffTypeName(data.getString(3))){
staffType = StaffType.findByStaffTypeNameInExcel(data.getString(3),EnableOrDisable.ENABLE).get(0);
}
if(null != staffType){
staff.setStaffType(staffType);
}
//备注
staff.setNote(data.getString(4));
staff.setPhotoAddress("");
//状态
staff.setValidFlag(EnableOrDisable.NEW);
staff.setInsertDate(DateHelper.getCurrentDate());
staff.setUpdateDate(DateHelper.getCurrentDate());
staff.insertStaff();
} catch (Exception e) {
throw new BusinessException("第 \'" + data.getRowNum() + "\' 行数据 : 人员名称为 \'"+data.getString(1)+"\' 的数据中 "+e.getMessage()+"请检查");
}
return staff;
}
}
6)RowData
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
public class RowData
{
private int rowNum;
private int cellCount;
private Map<Integer, Object> data = new HashMap();
public void put(Integer key, Object value)
{
this.data.put(key, value);
}
public Object get(Integer index)
{
return this.data.get(index);
}
public String getString(Integer index)
{
Object obj = this.data.get(index);
if (obj == null)
return null;
if (obj instanceof String)
return ((String)obj);
return obj.toString();
}
public Integer getInteger(Integer index)
{
Object obj = this.data.get(index);
if (obj == null)
return null;
if (obj instanceof String)
return Integer.valueOf(((String)obj).trim());
return Integer.valueOf(((Double)obj).intValue());
}
public Double getDouble(int index)
{
Object obj = this.data.get(Integer.valueOf(index));
if (obj == null)
return null;
if (obj instanceof String)
return Double.valueOf(((String)obj).trim());
return ((Double)obj);
}
public Date getDate(int index, String pattern)
throws ParseException
{
Object obj = this.data.get(Integer.valueOf(index));
if (obj == null)
return null;
if (obj instanceof String)
return parse((String)obj, pattern);
if (obj instanceof Double) {
BigDecimal decimal = new BigDecimal(((Double)obj).doubleValue());
return parse(decimal.setScale(0).toString(), pattern);
}
return parse(obj.toString(), pattern);
}
private static Date parse(String date, String pattern) throws ParseException
{
return new SimpleDateFormat(pattern).parse(date);
}
public int getRowNum() {
return this.rowNum;
}
public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}
public int getCellCount() {
return this.cellCount;
}
public void setCellCount(int cellCount) {
this.cellCount = cellCount;
}
public Map<Integer, Object> getData() {
return this.data;
}
}
7)