MVC4 导入execl和下载模板
上传预览
var IsIllegal = false;
function SelectFile(obj) {
document.getElementById('textfield').value = obj.value;
IsIllegal = false;
}
<input type='text' disabled="disabled" name='textfield' id='textfield' class='txt' />
<input type='button' class='btn' value='浏览' />
<input type="file" accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" name="excel" class="file" id="excel" onchange="SelectFile(this)" /> 打资源管理器添加execl文件
$("上传按钮ID").click(function () {
if ($('放置execl文件的文本框ID').val().length == 0) {
alert('请选择要上传的文件~！');
return false;
}
$("表单ID").ajaxSubmit(options);
});
var options = {
url: 'Controller/Action',
success: function (后台回传数据) {
需求；
};
#region
public ActionResult ProExcel(HttpPostedFileBase excel)//保存到程序缓存
{
if (excel == null)//判断有没有选择文件
{
return Json(new { InfoCode = "1000", Msg = "请添加 excel文件~！" });
}
List<ProMacCreatExcelModel> models = new List<ProMacCreatExcelModel>();//定义一个有三列的类
try
{
using (excel.InputStream)
{
string fileName = excel.FileName;
string ext = fileName.Substring(fileName.LastIndexOf('.') + 1);
string s = Path.GetExtension(fileName);
if (s != ".xls" && s != ".xlsx")//判断是不是execl格式的文件
{
return Json(new { InfoCode = "1000", Msg = "请添加 excel文件~！" });
}
IWorkbook workbook;//一个execl文件
if (!(ext.Equals("xls") || ext.Equals("xlsx")))//判断是不是execl格式的文件
{
return Json(new { InfoCode = "1000", Msg = "请添加 excel文件~！" });
}
if (ext.Equals("xls"))
workbook = new HSSFWorkbook(excel.InputStream);//把execl文件读到web里
else
workbook = new XSSFWorkbook(excel.InputStream);
ISheet sheet = workbook.GetSheetAt(0);//取第一个表
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);//第一行为标题行
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).GetCellValue();
}
}
}
table.Rows.Add(dataRow);
}
foreach (DataRow row in table.Rows)
{
ProMacCreatExcelModel t = new ProMacCreatExcelModel();
t.cell1 = row[0].ObjToString();
t.cell2 = row[1].ObjToString();
if (table.Columns.Count == 3)
t.cell3 = row[2].ObjToString();
models.Add(t);
}
return Json(new { InfoCode = "0000", data = models });
}
}
catch (Exception ex)
{
CustomerFilterAttribute.ErrorQueue.Enqueue(ex);
return Json(new { InfoCode = "4000", data = models });
}
}
#endregion
#region Model层定义的类
public class ProMacCreatExcelModel
{
public string cell1 { get; set; }
public string cell2 { get; set; }
public string cell3 { get; set; }
}
#endregion
public ActionResult ProMultityInport(Dto_ProdMacProMI PMPI)//保存到数据库
{
using (PMPI.excel.InputStream)
{
try
{
string fileName = PMPI.excel.FileName;
string ext = fileName.Substring(fileName.LastIndexOf('.') + 1);
IWorkbook workbook;
if (ext.Equals("xls"))
workbook = new HSSFWorkbook(PMPI.excel.InputStream);
else
workbook = new XSSFWorkbook(PMPI.excel.InputStream);
ISheet sheet = workbook.GetSheetAt(0);//取第一个表
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);//第一行为标题行
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
List<prodmaccreat> models = new List<prodmaccreat>();
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).GetCellValue();
}
}
}
table.Rows.Add(dataRow);
}
foreach (DataRow row in table.Rows)
{
models.Add(new prodmaccreat()
{
cid = row[0].ObjToString(),
macno = row[1].ObjToString(),
ptserial = string.IsNullOrEmpty(PMPI.ptserial) ? null : PMPI.ptserial,
uiversion = string.IsNullOrEmpty(PMPI.uiversion) ? null : PMPI.uiversion,
mscserial = string.IsNullOrEmpty(PMPI.mscserial) ? null : PMPI.mscserial,
utid = int.Parse(PMPI.utid),
officialversion = PMPI.officialversion,
developmentversion = PMPI.developmentversion,
suhardver = PMPI.suhardver,
susoftver = PMPI.susoftver,
state = "0",
importdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
});
}
List<string> cids2 = (from o in models join p in prodmaccreatbll.GetList(o => true).ToList() on o.cid equals p.cid select o.cid).ToList();
if (cids2.Count != 0)
{
string s = "";
cids2.ForEach(c =>
{
s = s + c + ",";
});
return Json(new { InfoCode = "4000", Msg = "盒子ID" + s + "重复!" });
}
prodmaccreatbll.AddModels(models);
var cids = (from o in models select o.cid).ToArray();
MongoWSModel prodmaccreatdataModel = new MongoWSModel(ActionOption.add)
{
TableName = "prodmaccreat",
KeyName = "cid",
KeyValue = cids
};
for (int i = 1; 1 <= 3; i++)
{
bool res = client.PushData(JsonConvert.SerializeObject(prodmaccreatdataModel));
if (res)
break;
}
List<ProMacCreatExcelModel> prodmaccreatexcekmodel = new List<ProMacCreatExcelModel>();
foreach (DataRow row in table.Rows)
{
ProMacCreatExcelModel t = new ProMacCreatExcelModel();
t.cell1 = row[0].ObjToString();
t.cell2 = row[1].ObjToString();
if (table.Columns.Count == 3)
t.cell3 = row[2].ObjToString();
prodmaccreatexcekmodel.Add(t);
}
WriteLog("000010002005", "导入用户", true);
return Json(new { InfoCode = "0000", data = prodmaccreatexcekmodel });
}
catch (Exception ex)
{
WriteLog("000010002005", "导入用户", false);
CustomerFilterAttribute.ErrorQueue.Enqueue(ex);
return Json(new { InfoCode = "4000", Msg = "系统异常~！" });
}
}
}
<a style="font-size:12px;color:blue;" href="~/Content/themes/DownLoadFiles/批量导入用户模板.xlsx">下载导入模板</a>
