C#: net5 mvc使用DotNetCore.NPOI将Mysql导入导出(ado版)
前言：
-
本项目使用的是.net5 mvc技术，当然也可以做成WebApi。
-
下面代码有些地方为了演示方便，进行了整合和删减，写在了一个类里面，并把有些敏感的地方替换了，sql的字段也许没统一，但不影响代码运行。
-
因项目需求不同,帮助类大同小异，请自行结合项目需求进行修改。
-
导出只做了xls格式，导入xls和xlsx都可以
-
因为是第一次做，代码质量不高。所用到的方法可能不是最先进的.
-
关于导入数据库的效率：只说追加导入(下面演示的是覆盖导入)。写入本地数据库，两万条数据,时间大概在一分钟以内。
-
下面是我在代码实现过程中从B站找到的学习视频。虽然有些许问题，但很感谢这个up。
-
https://www.bilibili.com/video/BV1iU4y1K7Z6
导出Excel有很多种方法，前后端都可以做Excel导出。前端做Excel，后端给json数据，js导出scv格式的excel，依靠插件转成xls或xlsx格式，但这里只做粗略导出。
js导出Excel(scv)
function JsonToCSV(data) {
var str = '教师姓名,教师账户,教师班级,教师年级\n';
for (let i = 0; i < data.length; i++) {
str += `${data[i].name},${data[i].user},${data[i].class},${data[i].grade}\n`
}
var blob = new Blob([str], {
type: "text/plain;charset=utf-8"
});
blob = new Blob([String.fromCharCode(0xFEFF), blob], {
type: blob.type
});
object_url = window.URL.createObjectURL(blob);
var link = document.createElement("a");
link.href = object_url;
link.download = "教师.csv";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
导出Excel(教师导出xls，单页签)
model
为了看起来方便，实体、特性写在了一起
using System;
using System.Collections.Generic;
namespace SchoolManage.Models
{
[AttributeUsage(AttributeTargets.Property)]
public class TitleAttribute : Attribute //有标识为Titilte的字段才会被导出
{
public string Titile { get; set; }
}
public class ExcelDataResource //Excel实体
{
public string SheetName { get; set; }//页签名字
public int TitleIndex { get; set; } //页签
public List<object> SheetDataResource { get; set; } //对应导出的教师数据
}
public class teacher //教师实体
{
[Title(Titile = "教师姓名")]
public string name { get; set; }
[Title(Titile = "教师账户")]
public string user { get; set; }
[Title(Titile = "教师班级")]
public string class { get; set; }
[Title(Titile = "教师年级")]
public string grade { get; set; }
}
}
Excel帮助类ExcelOperationHelper(这里通用)
这里的代码都是视频里面的内容，导出的文件格式为xls。
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
namespace Tool.Excel
{
public class ExcelOperationHelper
{
//生成Excel
public static IWorkbook DataToHssWorkbook(List<ExcelDataResource> data)
{
HSSFWorkbook _Workbook = new();
if (data == null || data.Count == 0)
{
return _Workbook;
}
foreach (var sheet in data)
{
if (sheet.SheetDataResource != null && sheet.SheetDataResource.Count == 0)
{
break;
}
ISheet sheet1 = _Workbook.CreateSheet(sheet.SheetName);
object obj = sheet.SheetDataResource[0];
Type type = obj.GetType();
List<PropertyInfo> prooList = type.GetProperties().Where(c => c.IsDefined(typeof(TitleAttribute), true)).ToList();
int TitleIndex = 0;
if (sheet.TitleIndex >= 0)
{
TitleIndex = sheet.TitleIndex - 1;
}
//sheet页
IRow titleRow = sheet1.CreateRow(TitleIndex);
ICellStyle style = _Workbook.CreateCellStyle();
//Excel样式
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
style.FillPattern = FillPattern.SolidForeground;
style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
style.Alignment = HorizontalAlignment.CenterSelection;
style.VerticalAlignment = VerticalAlignment.Center;
titleRow.Height = 100 * 4;
for (int i = 0; i < prooList.Count(); i++)
{
TitleAttribute proertyAttribute = prooList[i].GetCustomAttribute<TitleAttribute>();
ICell cell = titleRow.CreateCell(i);
cell.SetCellValue(proertyAttribute.Titile);
cell.CellStyle = style;
}
for (int i = 0; i < sheet.SheetDataResource.Count(); i++)
{
//行数据
IRow row = sheet1.CreateRow(i + TitleIndex + 1);
object objInstance = sheet.SheetDataResource[i];
//表数据
for (int j = 0; j < prooList.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(prooList[j].GetValue(objInstance).ToString());
}
}
}
return _Workbook;
}
//转成文件流
public static byte[] ToExcelByteArray(List<ExcelDataResource> data)
{
IWorkbook _WorkBook = DataToHssWorkbook(data);
using (MemoryStream stream = new())
{
_WorkBook.Write(stream);
byte[] bt = stream.ToArray();
stream.Write(bt, 0, (int)stream.Length);
return bt;
}
}
}
}
从数据库获取教师数据
这里为了演示，我对自己的项目进行了拆分，全部写在了业务里面。当然，你也可以进行拆分。
/// <summary>
/// 获取教师Excel导出数据
/// </summary>
/// <returns></returns>
private List<ExcelDataResource> GetExcelTeacherlist()
{
List<object> lst = new();
using (MySqlConnection conn = GetConnection())
{
string sql = "select * from teacher ";
MySqlDataAdapter adp = new MySqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
adp.Fill(ds);
foreach (DataRow item in ds.Tables[0].Rows)
{
lst.Add(new teacher()
{
teachername = item.IsNull("name") ? "" : item["name"].ToString(),
teauser = item.IsNull("user") ? "" : item["user"].ToString(),
classname = item.IsNull("class") ? "" : item["class"].ToString(),
teachergrade = item.IsNull("grade") ? "" : item["grade"].ToString(),
});
}
}
return new List<ExcelDataResource>()
{
new ExcelDataResource()
{
SheetName = "教师",
TitleIndex = 1,
SheetDataResource = lst
}
};
}
api接口(不需要写[httpXXX])
/// <summary>
/// 测试 教师
/// </summary>
/// <returns></returns>
public IActionResult Test()
{
List<ExcelDataResource> Excel = GetExcelTeacherlist();
byte[] bt = ExcelOperationHelper.ToExcelByteArray(Excel);
return File(bt, "application/vnd.ms-excel", "教师数据.xls");
}
效果
导入数据库(不限xls和xlsx格式)
ExcelOperationHelper类新写入一个方法(不通用)
你的项目一定需要更改(请谨慎复制粘贴!!!)，所以请自己观看一遍B站视频，大概了解一下怎么获取Excel和怎么转换成DataTable数据，或者在我的代码基础上进行修改
/// <summary>
/// 教师导入 将Excel数据转成DataTable
/// </summary>
/// <param name="hSSFWorkbook"></param>
/// <returns></returns>
public static List<DataTable> ToTableTest(IWorkbook hSSFWorkbook, out string msg)
{
List<DataTable> datatableList = new();
List<string> drColumn2 = new(); //过滤重复用户名
int RowError = 0;
Regex regexCell = new Regex("[~!@#$%^&*()=+[\\]{}''\";:/?.,><`|！·￥…—（）\\-、；：。，》《]"); //单元格非法字符
for (int sheetIndex = 0; sheetIndex < hSSFWorkbook.NumberOfSheets; sheetIndex++)
{
// 获取sheet页
ISheet sheet = hSSFWorkbook.GetSheetAt(sheetIndex);
//获取头部
IRow header = sheet.GetRow(sheet.FirstRowNum);
if (header == null)
{
break;
}
// 获取第一行的列数 以标题行为基准
int cellCount = header.LastCellNum;
// 判断传入的列数是不是四列。
if (cellCount != 4)
{
msg = "教师导入需要传入四列参数，请检查是否缺少列或多出列";
return datatableList;
}
int startRow = 0;
DataTable dtNpoi = new();
startRow = sheet.FirstRowNum + 1;
for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
{
ICell cell = header.GetCell(i);
string headerName = header.GetCell(i).StringCellValue;
if ("教师姓名" != header.GetCell(0).StringCellValue.Trim() || "教师账户" != header.GetCell(1).StringCellValue.Trim() || "教师班级" != header.GetCell(2).StringCellValue.Trim() || "教师年级" != header.GetCell(3).StringCellValue.Trim())
{
msg = $"请检查标题头是否包含(教师姓名、教师账户、教师班级、教师年级),并且顺序严格按照 [教师姓名=>教师账户=>教师班级=>教师年级]排列";
return datatableList;
}
//获取第一行值 也就是标题头部，写入DataTable。
string cellValueCN = cell.StringCellValue.Trim();
string ColName = string.Empty;
if (cellValueCN == "教师姓名")
{
ColName = "name";
}
if (cellValueCN == "教师账户")
{
ColName = "user";
}
if (cellValueCN == "教师班级")
{
ColName = "class";
}
if (cellValueCN == "教师年级")
{
ColName = "grade";
}
DataColumn col = new();
dtNpoi.Columns.Add(ColName);
}
//对从第二行开始的所有单元格做处理
for (int i = startRow; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
DataRow dr = dtNpoi.NewRow();
for (int j = 0; j < 4; j++) //原row.FirstCellNum row.LastCellNum
{
// int check = row.FirstCellNum;
if (row.GetCell(j) != null && !regexCell.IsMatch(row.GetCell(j).ToString()) && row.GetCell(j).ToString().Trim() != "")
{
//验证教师账户
if (j == 1)
{
if (drColumn2.Contains(row.GetCell(j).ToString().Trim()))
{
msg = $"教师账户第{i + 1}行已包含{row.GetCell(j).ToString()},请重新校验Excel第{i + 1}行数据是否重复";
return datatableList;
}
drColumn2.Add(row.GetCell(j).ToString().Trim());
RowError = i;
}
dr[j] = row.GetCell(j).ToString().Trim();
}
else
{
string errorCell = string.Empty; //判断last或者first单元格为空进行的处理
if (0 == j)
{
errorCell = row.GetCell(j + 1) is null ? "" : row.GetCell(j + 1).ToString();
}
else
{
errorCell = row.GetCell(j - 1).ToString();
}
msg = $"第{i + 1}行第{j + 1}个单元格存在空白单元格或非法符号，请检查第{i + 1}行内容为'{errorCell}'的单元格附近是否出现错误,如果未输入内容请删除所有空格";
return datatableList;
}
}
dtNpoi.Rows.Add(dr);
}
datatableList.Add(dtNpoi);
}
msg = "success";
return datatableList;
}
sql集
同样的，这里为了演示，我将所有内容整合到一个方法里面，并写在业务里，pass是默认的密码，所以DataTable没有加这列，固定就行。
/// <summary>
/// Excel覆盖数据库 ----教师
/// </summary>
/// <param name="dt"></param>
/// <param name="MysqlRes"></param>
private void DataTableToMysqlList(DataTable dt, out string MysqlRes)
{
string sqlDelete = "DELETE FROM teacher";
if (dt.Rows.Count < 1)
{
MysqlRes = "操作失败:没有可以插入的数据";
return;
}
int sqlI = 0;//写入行数
int sqlDeleteRow = 0;//删除行数
List<string> sqlList = string.Empty; //sql集
DateTime createTime = DateTime.Now;//创建时间
StringBuilder sqlVal = new StringBuilder();//每条sql
//请自行百度C#MD5使用
string md5Psd = "999999"; //默认密码
for (int i = 0; i < dt.Rows.Count; i++)
{
sqlVal.Append($"insert into teacher(name,user,class,grade,pass,time) values(");
// sql = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
sqlVal.Append($"'{dt.Rows[i][j]}',");
}
sqlVal.Append($"'{md5Psd}','{createTime}')");
sqlList.Add(sqlVal.ToString());
sqlVal.Length = 0; //清空StringBuilder
}
using (MySqlConnection conn = GetConnection())
{
conn.Open();
//删除所有
MySqlCommand cmdDelete = new(sqlDelete, conn);
sqlDeleteRow = cmdDelete.ExecuteNonQuery();
//插入,隔五百条数据提交事务。这里也是百度了解到的。肯定有最优解，目前没学习到。
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
MySqlTransaction transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
try
{
for (int i = 0; i < sqlList.Count; i++)
{
string sql = sqlList[i].ToString();
if (sql.Trim().Length > 1)
{
cmd.CommandText = sql;
sqlI += cmd.ExecuteNonQuery();
}
if (i > 0 && (i % 500 == 0 || i == sqlList.Count - 1))
{
transaction.Commit();
transaction = conn.BeginTransaction();
}
}
}
catch (System.Exception)
{
transaction.Rollback();
throw;
}
conn.Close();
}
MysqlRes = $"共删除{sqlDeleteRow}行数据，重新写入{sqlI}行数据";
}
前端(覆盖导入)
前端表单提交就可以，Ajax提交的话需要获取到表单里面的内容
Ajax提交
(axios同理，但net普遍使用Ajax所以演示Ajax提交)
这里因为控制器有权限验证,使用的是@Url.Action(),mvc没有验证要求的的话普通地址就可以。当然上传文件需要做前端验证，比如后缀名、文件大小。我前后端都有做。这里由于项目的不同，已删除自己的代码。消息弹出框推荐bootstrap4、toastr、sweetalert2。
function UploadExcel() {
let formData = new FormData($('#Excelfile')[0]);//form表单的id不是文件标签的id
let fileName = getExtension(formDataAppend.get('file').name)//文件标签的id
$.ajax({
type: "POST",
url: "@Url.Action("TestCover","控制器")",
dataType: "JSON",
data: formData,
cache: false,
processData: false,
contentType: false,
success: function (data) {
// console.log(data)
if (data.Code == 201) { //这里Ajax返回的状态Code是自己服务器添加的，不是Ajax自带的。后面的接口我会加上
})
} else {
}
},
error: function (result) {
alert(result.responseText)
}
});
}
后端Api
覆盖导入数据库
安装Newtonsoft.Json做json转换，状态码比较随意，如果有需求也请自行百度。
/// <summary>
/// 教师覆盖导入 ----覆盖MySQL数据,删除所有重新覆盖
/// </summary>
/// <returns></returns>
public string TestCover()
{
ResultStatus result = new ResultStatus(); //这个类在下面
IFormFile file = Request.Form.Files["file"];
string msg = string.Empty;//执行转换DataTable过程中指向的消息
string MysqlRes = string.Empty;//执行转换Mysql命令过程中指向的消息
DataTable dt = new();
if (file != null && file.Length > 0)
{
string suffixName = Path.GetExtension(file.FileName).ToLower();
try
{
if (suffixName == ".xlsx")
{
XSSFWorkbook xssFWorkbook = new XSSFWorkbook(file.OpenReadStream());
//ToExcelDateTable 对于教师导入的Datatable返回
List<DataTable> datatableList = ExcelOperationHelper.ToTableTest(xssFWorkbook, out msg);
if (msg == "success")
{
dt = datatableList[0];
DataTableToMysqlList(dt, out MysqlRes);
result.Code = 201;
result.ResultMessage = $"导入成功,{MysqlRes}";
}
else
{
result.Code = 200;
result.ResultMessage = $"{msg}";
}
}
else if (suffixName == ".xls")
{
HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(file.OpenReadStream());
List<DataTable> datatableList = ExcelOperationHelper.ToTableTest(hSSFWorkbook, out msg);
if (msg == "success")
{
dt = datatableList[0];
DataTableToMysqlList(dt, out MysqlRes);
result.Code = 201;
result.ResultMessage = $"导入成功,{MysqlRes}";
// result.data = dt;
}
else
{
result.Code = 200;
result.ResultMessage = $"{msg}";
}
}
else
{
result.Code = 200;
result.ResultMessage = "请上传xls或者xlsx格式";
}
}
catch (Exception e)
{
result.Code = 200;
result.ResultMessage = e.Message.ToString();
}
}
else
{
result.Code = 200;
result.ResultMessage = "请重新上传文件";
}
return JsonConvert.SerializeObject(result);
}
ResultStatus类
这个状态类是最基本的结构，如果还有需要请自行百度。
public class ResultStatus{
public int Code { get; set; }//状态码
public string ResultMessage { get; set; }//提示消息
public object data { get; set; }//返回数据
public ResultStatus ResultMsg(int code, string msg, object datas)
{
Code = code;
ResultMessage = msg;
data = datas;
return this;
}
}
如果是追加导入(存在更新不存在插入)，方法基本一致，只是sql语句的变化
teauser 是唯一的索引，数据库要添加不然没有办法使用下面的sql语句。之所以C#的代码没贴出是因为不难，可以思考怎么写，锻炼思维能力。
INSERT INTO teacher(name,user,class,grade,pass,time)
VALUES('名字','账户名','1','1','AF8F9DFFA5D420FBC249141645B962EE','2022-01-07 21:43:10')
ON DUPLICATE KEY UPDATE name='更新成功',class='1',grade='1'pass='AF8F9DFFA5D420FBC249141645B962EE',time='2022-01-07 21:43:10'
ResultStatus ResultMsg(int code, string msg, object datas)
{
Code = code;
ResultMessage = msg;
data = datas;
return this;
}
}
1197
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
