导入excel-uploadify+npoi
总结：
- 导入文件时一定要给database设置栏位
- 虚拟表的栏位名称可以与数据库表不一致，但顺序一定要一致，因为它是按照顺序依次插入的
- sheet.FirstRowNum = 0; sheet.FirstCellNum = 1; getRow、getCell从0开始
- 设置的虚拟表字段不赋值时默认为空null
- 虚拟表字段为空时，dt.Rows[i][j].Tostring()="";
前台代码
//第一步：定义按钮
<a class="mini-button" iconcls="icon-upload" onclick="uploadExcel()">导入</a>
//第二步：定义弹出框
<div id="win" class="mini-window" title="Window"style="width:300px;height:200px;"showmodal="true">
<div id="case_0"></div>//导入所需容器
<div id="case_1"> //菜单容器
<a href="javascript:$('#file_upload').uploadify('upload', '*')">上传</a>
<a href="#">|</a>
<a href="javascript:leading_in()">导入</a>
<a href="#">|</a>
<a href="javascript:clear()">清空队列</a>
</div>
</div>
//第三步：js按钮触发事件
function onclick() {
win.setTitle("请选择上传文件");
win.show();
$("#case_0").empty();
$("#case_0").append("<input type='file' id='file_upload' />");
$("#case_1").hide();
initSendFile();
}
//第四步：初始化上传控件
var file_name = null;
function initSendFile() {
$("#file_upload").uploadify({
'swf': '../../Scripts/uploadify-v3.1/uploadify.swf',
'uploader': '/Equipment/Upload',
buttonText: '请选择上传文件',
fileTypeExts: '*.xls;*.xlsx;',
auto: false,
multi: false,
uploadLimit: 1,
'onUploadSuccess': function (file, data, response) {
if (response == true) {
mini.alert("上传成功");
var data = mini.decode(data);
file_name = data.SaveName;
}
},
'removeCompleted': false,
'onDialogClose': function (queueData) {
if (queueData.filesQueued !='0') {
$("#repeat2").show();
}
},
'onCancel': function (file) {
$("#repeat2").hide();
}
});
}
//第五步：上传文件到服务器指定位置
public JsonResult Upload(HttpPostedFileBase fileData){
if (fileData != null)
{
try
{
string saveName = Guid.NewGuid().ToString();
string filePath = Server.MapPath("~/Uploads/UploadExcel/" + saveName);
if (!Directory.Exists(filePath)){ Directory.CreateDirectory(filePath);}
string fileName = Path.GetFileName(fileData.FileName);// 原始文件名称
string fileExtension = Path.GetExtension(fileName); // 文件扩展名
fileData.SaveAs(filePath+"\\" + saveName + fileExtension);//将fileData保存到指定路径后两个参数指定文件名
return Json(new { Success = true, FileName = fileName, SaveName = saveName },JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
LogHelper.WriteLog("EquipmentController.Upload",ex);
return Json(new { Success = false, Message = ex.Message }, JsonRequestBehavior.AllowGet);
}
}
else { return Json(new { Success = false, Message = "请选择要上传的文件！" }, JsonRequestBehavior.AllowGet); }
}
//第六步：(1)将上传到服务器的文件导入数据库-js部分
function toDB() {
$.ajax({
type: 'post',
async: false,
data: { file_name: file_name },
url: '/Equipment/toDB',
success: function (result) {
var result = mini.decode(result);
if (result.Success == true) {
mini.alert(result.Message);
win3.hide();
dg.reload();
tree.reload();
file_name = null;
} else {
mini.alert(result.Message);
}
},
error: function () {
mini.alert("导入失败！");
file_name = null;
}
});
}
//第六步：(2)将上传到服务器的文件导入数据库-控制器部分
public ActionResult toDB( string file_name )
{
if (file_name != null)
{
try
{
string filePath = Server.MapPath("~/Uploads/uploadExcel/" + file_name);
if (!Directory.Exists(filePath)) { Directory.CreateDirectory(filePath); }
string[] filelist = Directory.GetFiles(filePath);//将导入的文件
string fileName = Path.GetFileName(filelist[0]);//将导入的文件名称
Jxd.Mvc.Domain.Model.FileInfo filedate = new Jxd.Mvc.Domain.Model.FileInfo();
filedate.AnnexName = fileName;
filedate.AnnexPath = filePath+"\\" + fileName;
this.EquipmentService.toDB(filedate, filePath);
return Json(new { Success = true ,Message = "导入成功"},JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
LogHelper.WriteLog("ExceptionController.toDB", ex);
return Json(new { Success = false,Message = "请确保单元格内容正确！"});
}
}
else { return Json(new { Success = false, Message = "没有可导入的文件！"},JsonRequestBehavior.AllowGet); }
}
//第七步：将上传到服务器的文件导入数据库-dao层部分
public void toDB(Jxd.Mvc.Domain.Model.FileInfo filedate,string filePath)
{
try
{
//第1步:定义文件输入流
string path = filedate.AnnexPath;
FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read);
//第二步：判断excel的版本，创建工作簿，工作表
string fileName = filedate.AnnexName;
if (fileName.IndexOf(".xlsx") > 0){ workbook = new XSSFWorkbook(stream); }
else if ( fileName.IndexOf(".xls" ) > 0) { workbook = new HSSFWorkbook(stream); }
if (sheet == null) { sheet = workbook.GetSheetAt(0); }
//第三步：设置虚拟表栏位
DataTable dt = new DataTable();
DataColumn field = null;
field = new DataColumn("栏位一"); dt.Columns.Add(field);
field = new DataColumn("栏位二"); dt.Columns.Add(field);
//第四步：创建字典用于渲染字段
//第五步：数据插入虚拟表
for (int i = sheet.FirstRowNum + 3; i <= sheet.LastRowNum; i++){
IRow ergodic_row = sheet.GetRow(i);
int num = ergodic_row.LastCellNum;
DataRow dataRow_1 = dt.NewRow();
dataRow_1[0] = Guid.NewGuid().ToString();
dataRow_1[1] = ergodic_row.GetCell(3).ToString();
dataRow_1[2] = ergodic_row.GetCell(8).StringCellValue;
string b = @"^[0-9]+(\\.[0-9]+)?$";
RegexStringValidator regex = new RegexStringValidator(b);
if (ergodic_row.GetCell(10) != null){
try
{
regex.Validate(ergodic_row.GetCell(10).ToString());
dataRow_1[13] = ergodic_row.GetCell(10).ToString();
}
catch
{
continue;
}
}
//第六步：将虚拟表导入数据库
string connString = ConfigurationManager.AppSettings["SqlConnection"];
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn);
sqlbulkcopy.DestinationTableName = "TB_BASEEQ_EQUIPMENT";
sqlbulkcopy.NotifyAfter = dt.Rows.Count;//有几行数据
sqlbulkcopy.WriteToServer(dt);//数据导入数据库
sqlbulkcopy.Close();//关闭连接
if (Directory.Exists(filePath)) { Directory.Delete(filePath, true); }
}
}
catch(Exception ex){
throw ex;
}
}
