比较简洁的excel处理方法。希望对大家有所帮助。
1、界面
<el-upload class="upload-demo"
ref="upload"
:action="uploadUrl()"
:data="uploadData"
name="excelFile"
:on-preview="handlePreview"
:on-remove="handleRemove"
:file-list="fileList"
:on-error="uploadFalse"
:on-success="uploadSuccess"
:auto-upload="false"
:before-upload="beforeAvatarUpload">
<el-button slot="trigger" size="small" type="primary">选取文件</el-button>
<el-button style="margin-left: 10px;" size="small" type="success" v-on:click="submitUpload">批量导入</el-button>
<div slot="tip" class="el-upload__tip">只能上传excel文件</div>
</el-upload>2、js部分 其中有用的为uploadData，uploadSuccess其余方法 根据上传需要自行修改
uploadData: function () { },
uploadUrl: function () {
//文件上传调用的 C#方法
return (
"/Manager/addMajorsByExcel/"
);
},
uploadSuccess(response, file, fileList) {
if (response.code == 0) {
///上传成功刷新页面用 注释即可
this.getLists();
this.Show = 'list';
///上传成功刷新页面用 注释即可----
this.$message({
message: response.msg,
type: 'success'
});
} else {
this.$message({
message: response.msg,
type: 'warn'
});
}
},
uploadFalse(response, file, fileList) {
this.$message({
message: response.msg,
type: 'warn'
});
},
// 上传前对文件的大小的判断
beforeAvatarUpload(file) {
const extension = file.name.split(".")[1] === "xls";
const extension2 = file.name.split(".")[1] === "xlsx";
const isLt2M = file.size / 1024 / 1024 < 10;
if (!extension && !extension2) {
alert("上传模板只能是 xls、xlsx 格式!");
}
if (!isLt2M) {
console.log("上传模板大小不能超过 10MB!");
}
return extension || extension2;
},
submitUpload() {
if (this.businessType != null) {
//触发组件的action
this.$refs.upload.submit();
}
if (this.businessType == null) {
this.businessType = "businessType不能为空";
}
},
handleRemove(file, fileList) {
console.log(file, fileList);
},
handlePreview(file) {
if (file.response.status) {
alert("此文件导入成功");
} else {
alert("此文件导入失败");
}
}3后端处理excel 数据部分 自行修改
public ActionResult addMajorsByExcel()
{
HttpPostedFileBase file = Request.Files[0];
///保存excel
string path=CommonMethod.saveExcel(file);
///打开excel
Tuple<_Worksheet, Sheets, Workbooks, _Workbook, Application> objDim = CommonMethod.openExcel(path);
///处理数据 这个地方 结合自己实际表结构 修改即可 我这边表就code name date等
_Worksheet worksheet = objDim.Item1;
// 获取excel 有效数据行数
int rowNum = worksheet.UsedRange.Rows.Count;
FMSEntities fms = new FMSEntities();
for (int i = 2; i < rowNum + 1; i++)
{
string code = worksheet.Cells[i, 1].Text.ToString();
CT_Major g = new CT_Major();
if (code == null)
{
continue;
}
code = code.Trim();
CT_Major p = fms.CT_Major.Where(s => s.code == code).FirstOrDefault();
if (p != null)
{
continue;
}
g.code = code;
if (worksheet.Cells[i, 2].Text == null)
{
continue;
}
else
{
g.name = worksheet.Cells[i, 2].Text.ToString().Trim();
}
g.addDate = DateTime.Now;
g.updateDate = DateTime.Now;
g.status = true;
fms.CT_Major.Add(g);
fms.SaveChanges();
}
///释放资源
CommonMethod.NARExcel(objDim);
///删除excel
CommonMethod.deleteFlie(path);
return Json(new { code = 0, msg = "成功" }, JsonRequestBehavior.AllowGet);
}4后端公共方法CommonMethod处理excel
/// <summary>
/// 1保存excel
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static string saveExcel(HttpPostedFileBase file)
{
string fileName = Path.GetFileName(file.FileName);
///服务器保存文件路径 自行定义
string directory = "C:\\FMSPIC\\xls";
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
string guid = Guid.NewGuid().ToString();
string filePath = directory + "\\" + guid + fileName;
file.SaveAs(filePath);
return filePath;
}
public static Tuple<_Worksheet , Sheets , Workbooks , _Workbook , Application> openExcel(String filePath)
{
Application app = new Application();
if (app == null)//服务器上缺少Excel组件，需要安装Office软件
{
return null;
}
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(filePath); //加载模板
Sheets sheets = workbook.Sheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1); //第一个工作薄。
if (worksheet == null)//工作薄中没有工作表
{
return null;
}
var objDim = Tuple.Create<_Worksheet, Sheets, Workbooks, _Workbook, Application>(worksheet, sheets, workbooks, workbook, app);
return objDim;
}
///public static void NARExcel(_Worksheet worksheet, Sheets sheets, Workbooks workbooks, _Workbook workbook, Application app)
public static void NARExcel(Tuple<_Worksheet, Sheets, Workbooks, _Workbook, Application> objDim)
{
NAR(objDim.Item1);
NAR(objDim.Item2);
NAR(objDim.Item4);
NAR(objDim.Item3);
objDim.Item5.Quit();
NAR(objDim.Item5);
}
public static void deleteFlie(String filePath)
{
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);//执行IO文件删除,需引入命名空间System.IO;
}
}
/// <summary>
/// 释放资源
/// </summary>
/// <param name="o"></param>
public static void NAR(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch (Exception ex)
{
throw ex;
//WriteLog(ex.ToString());
}
finally
{
o = null;
}
}5效果截图
