C#学习两年的增删改查和C#导入导出（去重）案例

C#学习两年的增删改查和C#导入导出（去重）案例

前言：

已经快一周没有写了，各位志同道合的朋友支持的我必记住，感谢观看

技术栈：

开发语言：C#、.net、C#mvc项目
前端开发框架：axios、ElementUI框架
开发工具：
编译工具：VS2022
数据库：SQLServer

案例需求分析：

1、前端页面简洁客观
2、数据的增删改查
3、完整的Excel数据导入导出功能，导入数据需要去重复判断（上传数据自身判断、上传数据与数据库判断）
4、数据导出：可以一次全部导出，可以根据指定条件查询导出显示当前页的数据
5、所有的请求都通过axios开发

项目实现图

1、数据设计

该数据库的设计简单，全是一对一的关系，没有中间表操作，该案例不实现对围绕的4张外键表进行增删改查操作，请在数据库写死

2、新增物资数据

新增物资数据的模型和保存验证前端代码如下：

// 写在data函数内
// 添加表单数据
formData: {},
// 表单验证规则
rules: {
   matter_name: [
          { required: true, message: '请填物资名称', trigger: 'blur' }
	],
     matter_code: [
          { required: true, message: '请输入物资编号', trigger: 'blur' }
     ],
     product_type_id: [
         { required: true, message: '请选择产品类型', trigger: 'change' }
    ],
    the_winning_type_id: [
         { required: true, message: '请选择中标类型', trigger: 'change' }
    ],
    purchase_price: [
         { required: true, message: '请输入采购价格', trigger: 'blur' }
    ],
    Selling_price: [
         { required: true, message: '请输入售卖价格', trigger: 'blur' }
    ],
    franchiser_id: [
         { required: true, message: '请选择经销商', trigger: 'change' }
    ],
    manufacturer_id: [
         { required: true, message: '请选择生产商', trigger: 'change' }
    ]
}
// 提交方法
// 添加物质保存事件
submitForm(formName) {
    this.$refs[formName].validate((valid) => {
        if (valid) {
            // 异步请求保存数据
            axios.post("/MaterialManagement/add", this.formData).then((res) => {
                if (res.data.flag) {
                    this.$notify({
                        title: '成功提示',
                        message: res.data.message,
                        type: 'success'
                    });
                    // 调用重置刷新页面
                    this.resetForm2('formData');
                    this.findPage();
                } else {
                    this.$notify.error({
                        title: '错误提示',
                        message: res.data.message
                    });
                }
            });
        } else {
            this.$notify({
                title: '错误信息',
                type: 'warning',
                message: '请检查是否填写正确信息'
            });
            return false;
        }
    });
},
// 对话框重置表单
resetForm2(formName) {
    this.$refs[formName].resetFields();
    this.dialogVisible = false;
    this.dialogVisible2 = false;
}

// 回填下拉框事件封装
  selectQuery(url) {
      axios.get(url).then((res) => {
          // 判断回显哪一个模型数据
          switch (url) {
              case "/MaterialManagement/productType":
                  this.ComboBoxData.productType = res.data;
                  break;
              case "/MaterialManagement/TheWinningType":
                  this.ComboBoxData.TheWinningType = res.data;
                  break;
              case "/MaterialManagement/manufacturer":
                  this.ComboBoxData.manufacturer = res.data;
                  break;
              case "/MaterialManagement/franchiser":
                  this.ComboBoxData.franchiser = res.data;
                  break;
          }
      });
  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

3、实现数据库分页查询

效果如首图中展示一样，默认展示10条一页，请求参数附带了当前页和每页显示几条数据和一共几条数据以及需要模糊查询的条件，后端根据接收的条件进行分页查询处理

// 写在data内初始数据
// 分页相关属性
pagination: {
    currentPage: 1,
    pageSize: 10,
    total: 1,
    name: '',
    code: ''
}

// 写在methods方法内的
// 分页查询
findPage() {
    axios.post("/MaterialManagement/qieryList", {
        currentPage: this.pagination.currentPage,
        pageSize: this.pagination.pageSize,
        name: this.pagination.name,//查询条件
        code: this.pagination.code//查询编码
    }).then((res) => {
        // 判断是否成功
        if (!res.data.flag && res.data.flag != null) {
            this.$notify.error({
                title: '错误',
                message: res.data.message
            });
        }
        // 回显数据
        this.tableData = res.data.rows;
        this.pagination.total = res.data.total;
    });
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

后端实现分页数据封装，条件查询方法在后面的几个地方需要使用到而且都是同样的代码，可以进行重构抽取处理，通过方法名称+传递参数即可查询数据反会

/// <summary>
/// 封装查询的方法
/// </summary>
public posttingClass ListData(queryData queryData, Boolean flag)
{
    posttingClass postting = new posttingClass();
    try
    {
        List<selectData> listFaultInfo = (from tba in myModels.matter_table
                                              // 连接产品类型表
                                          join tbb in myModels.product_type on tba.product_type_id equals tbb.product_type_id
                                          // 连接中标类型标
                                          join tbc in myModels.the_winning_type on tba.the_winning_type_id equals tbc.the_winning_type_id
                                          // 连接经销商表
                                          join tbd in myModels.franchiser_table on tba.franchiser_id equals tbd.franchiser_id
                                          // 连接生产商表
                                          join tbe in myModels.manufacturer_table on tba.manufacturer_id equals tbe.manufacturer_id
                                          // 排序
                                          orderby tba.matterid
                                          select new selectData
                                          {
                                              Id = tba.matterid,
                                              Name = tba.matter_name,
                                              Code = tba.matter_code,
                                              purchasePrice = tba.purchase_price,
                                              SellingPrice = tba.Selling_price,
                                              producer = tbe.manufacturer_name,
                                              franchiser = tbd.franchiser_name,
                                              productType = tbb.product_type_name,
                                              TheWinningType = tbc.the_winning_type_name
                                          }).ToList();
        // 查询数据的总数
        var count = myModels.matter_table.Count();
        postting.selectDatas = listFaultInfo;
        if (flag == false) return postting;

        // 进行模糊查询
        if (queryData.name != null && queryData.name.Length > 0)
        {
            listFaultInfo = listFaultInfo.Where(o => o.Name.Contains(queryData.name)).ToList();
            count = listFaultInfo.Count();
        }
        if (queryData.code != null && queryData.code.Length > 0)
        {
            listFaultInfo = listFaultInfo.Where(o => o.Code.Contains(queryData.code)).ToList();
            count = listFaultInfo.Count();
        }

        listFaultInfo = listFaultInfo.Take(queryData.pageSize * queryData.currentPage).Skip(queryData.pageSize * (queryData.currentPage - 1)).ToList();
        postting.selectDatas = listFaultInfo;
        postting.Count = count;
        return postting;
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
        return null;
    }
}

// 调用封装的查询方法
#region 二、模糊分页查询物质数据
public ActionResult qieryList(queryData queryData)
{
    Result result = new Result();

    // 分页查询数据
    try
    {
        var list = ListData(queryData, true).selectDatas;
        var count = ListData(queryData, true).Count;
        // 赋值返回的分页数据
        PageResult pageResult = new PageResult();
        pageResult.total = count;
        //分页
        // var list_Subject_set = llist.Take(PageSize*PageIndex).Skip(PageSize* (PageIndex- 1)).ToList();
        pageResult.rows = list;
        return Json(pageResult, JsonRequestBehavior.AllowGet);
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
        result.flag = false;
        result.message = "数据查询失败" + e.Message;
        return Json(result, JsonRequestBehavior.AllowGet);
    }
}
#endregion
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

4、删除物资数据

所有的删除业务几乎都要使用询问，这里也是避免不了的，否则数据一删除就没了，删除的条件是根据当前点击行的id来完成的

// 删除事件
deltetData(Id) {
    // 询问是否要删除数据
    this.$confirm('此操作将永久删除该文件, 是否继续?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
    }).then(() => {
        // 异步请求删除数据
        axios.post("/MaterialManagement/remove?Id=" + Id).then((res) => {
            if (res.data.flag) {
                this.$notify({
                    title: '成功提示',
                    message: res.data.message,
                    type: 'success'
                });
                // 为了在删除最后一页的最后一条数据时能成功跳转回最后一页的上一页（pageSize=1时不生效）
                var totalPage = Math.ceil((this.pagination.total - 1) / this.pagination.pageSize) 
                var currentPage = this.pagination.currentPage > totalPage ? totalPage : this.pagination.currentPage
                this.pagination.currentPage = this.pagination.currentPage < 1 ? 1 : currentPage;
                // 查询查询数据
                this.findPage();
            } else {
                this.$notify({
                    title: '错误信息',
                    type: 'warning',
                    message: res.data.message
                });
                return false;
            }
        });
    }).catch(() => {
        this.$message({
            type: 'info',
            message: '已取消删除'
        });
    });
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

根据删除传递的id进行数据查询再市场数据

#region 四、删除物资数据
public ActionResult remove(int Id)
{
    Result result = new Result();
    // 异常捕获
    try
    {
        // 根据id查询物资信息
        matter_table matter = myModels.matter_table.Where(o => o.matterid == Id).Single();
        if (matter != null)
        {
            // 执行删操作
            myModels.matter_table.Remove(matter);
            if (myModels.SaveChanges() > 0)
            {
                result.flag = true;
                result.message = "物资数据删除成功";
            }
            else
            {
                result.flag = false;
                result.message = "物资数据删除失败";
            }
        }
        else
        {
            result.flag = false;
            result.message = "根据id删除数据失败(后台可能没有该id数据)";
        }
        return Json(result, JsonRequestBehavior.AllowGet);
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
        result.flag = false;
        result.message = "根据Id删除数据异常：" + e.Message;
        return Json(result, JsonRequestBehavior.AllowGet);
    }
}
#endregion
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

5、数据的导入

.net的导出可以借助微软的PNOI引用来完成，该案例使用的版本是：1.2.4.0，不同版本之间会有差异

上传数据的实现图

导出数据的实体工具类

类中包含了导入和导出的基本功能，直接通过调用即可实现

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.Text;
using System.Web;

namespace LibraryManagementSystem.Entity
{
    public class ExcelReader
    {
        /// <summary>
        /// 根据Excel列类型获取列的值
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        public static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.BLANK:
                    return string.Empty;
                case CellType.BOOLEAN:
                    return cell.BooleanCellValue.ToString();
                case CellType.ERROR:
                    return cell.ErrorCellValue.ToString();
                case CellType.NUMERIC:
                case CellType.Unknown:
                default:
                    return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
                case CellType.STRING:
                    return cell.StringCellValue;
                case CellType.FORMULA:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }

        /// <summary>
        /// 自动设置Excel列宽
        /// </summary>
        /// <param name="sheet">Excel表</param>
        private static void AutoSizeColumns(ISheet sheet)
        {
            if (sheet.PhysicalNumberOfRows > 0)
            {
                IRow headerRow = sheet.GetRow(0);
                for (int i = 0, l = headerRow.LastCellNum; i < l; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
            }
        }

        /// <summary>
        /// 保存Excel文档流到文件
        /// </summary>
        /// <param name="ms">Excel文档流</param>
        /// <param name="fileName">文件名</param>
        private static void SaveToFile(MemoryStream ms, string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
                data = null;
            }
        }

        /// <summary>
        /// 输出文件到浏览器
        /// </summary>
        /// <param name="ms">Excel文档流</param>
        /// <param name="context">HTTP上下文</param>
        /// <param name="fileName">文件名</param>
        private static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
        {
            if (context.Request.Browser.Browser == "IE")
                fileName = HttpUtility.UrlEncode(fileName);
            context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
            context.Response.BinaryWrite(ms.ToArray());
        }

        /// <summary>
        /// DataReader转换成Excel文档流
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static MemoryStream RenderToExcel(IDataReader reader)
        {
            MemoryStream ms = new MemoryStream();

            using (reader)
            {
                using (IWorkbook workbook = new HSSFWorkbook())
                {
                    using (ISheet sheet = workbook.CreateSheet())
                    {
                        IRow headerRow = sheet.CreateRow(0);
                        int cellCount = reader.FieldCount;

                        // handling header.
                        for (int i = 0; i < cellCount; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(reader.GetName(i));
                        }

                        // handling value.
                        int rowIndex = 1;
                        while (reader.Read())
                        {
                            IRow dataRow = sheet.CreateRow(rowIndex);

                            for (int i = 0; i < cellCount; i++)
                            {
                                dataRow.CreateCell(i).SetCellValue(reader[i].ToString());
                            }

                            rowIndex++;
                        }

                        AutoSizeColumns(sheet);

                        workbook.Write(ms);
                        ms.Flush();
                        ms.Position = 0;
                    }
                }
            }
            return ms;
        }

        /// <summary>
        /// DataReader转换成Excel文档流，并保存到文件
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="fileName">保存的路径</param>
        public static void RenderToExcel(IDataReader reader, string fileName)
        {
            using (MemoryStream ms = RenderToExcel(reader))
            {
                SaveToFile(ms, fileName);
            }
        }

        /// <summary>
        /// DataReader转换成Excel文档流，并输出到客户端
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="context">HTTP上下文</param>
        /// <param name="fileName">输出的文件名</param>
        public static void RenderToExcel(IDataReader reader, HttpContext context, string fileName)
        {
            using (MemoryStream ms = RenderToExcel(reader))
            {
                RenderToBrowser(ms, context, fileName);
            }
        }

        /// <summary>
        /// DataTable转换成Excel文档流
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static MemoryStream RenderToExcel(DataTable table)
        {
            MemoryStream ms = new MemoryStream();

            using (table)
            {
                using (IWorkbook workbook = new HSSFWorkbook())
                {
                    using (ISheet sheet = workbook.CreateSheet())
                    {
                        IRow headerRow = sheet.CreateRow(0);

                        // handling header.
                        foreach (DataColumn column in table.Columns)
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value

                        // handling value.
                        int rowIndex = 1;

                        foreach (DataRow row in table.Rows)
                        {
                            IRow dataRow = sheet.CreateRow(rowIndex);

                            foreach (DataColumn column in table.Columns)
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                            }

                            rowIndex++;
                        }
                        AutoSizeColumns(sheet);

                        workbook.Write(ms);
                        ms.Flush();
                        ms.Position = 0;
                    }
                }
            }
            return ms;
        }

        /// <summary>
        /// DataTable转换成Excel文档流，并保存到文件
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fileName">保存的路径</param>
        public static void RenderToExcel(DataTable table, string fileName)
        {
            using (MemoryStream ms = RenderToExcel(table))
            {
                SaveToFile(ms, fileName);
            }
        }

        /// <summary>
        /// DataTable转换成Excel文档流，并输出到客户端
        /// </summary>
        /// <param name="table"></param>
        /// <param name="response"></param>
        /// <param name="fileName">输出的文件名</param>
        public static void RenderToExcel(DataTable table, HttpContext context, string fileName)
        {
            using (MemoryStream ms = RenderToExcel(table))
            {
                RenderToBrowser(ms, context, fileName);
            }
        }

        /// <summary>
        /// Excel文档流是否有数据
        /// </summary>
        /// <param name="excelFileStream">Excel文档流</param>
        /// <returns></returns>
        public static bool HasData(Stream excelFileStream)
        {
            return HasData(excelFileStream, 0);
        }

        /// <summary>
        /// Excel文档流是否有数据
        /// </summary>
        /// <param name="excelFileStream">Excel文档流</param>
        /// <param name="sheetIndex">表索引号，如第一个表为0</param>
        /// <returns></returns>
        public static bool HasData(Stream excelFileStream, int sheetIndex)
        {
            using (excelFileStream)
            {
                using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
                //using (IWorkbook workbook = new XSSFWorkbook(excelFileStream))
                {
                    if (workbook.NumberOfSheets > 0)
                    {
                        if (sheetIndex < workbook.NumberOfSheets)
                        {
                            using (ISheet sheet = workbook.GetSheetAt(sheetIndex))
                            {
                                return sheet.PhysicalNumberOfRows > 0;
                            }
                        }
                    }
                }
            }
            return false;
        }

        /// <summary>
        /// Excel文档流转换成DataTable
        /// 第一行必须为标题行
        /// </summary>
        /// <param name="excelFileStream">Excel文档流</param>
        /// <param name="sheetName">表名称</param>
        /// <returns></returns>
        public static DataTable RenderFromExcel(Stream excelFileStream, string sheetName)
        {
            return RenderFromExcel(excelFileStream, sheetName, 0);
        }

        /// <summary>
        /// Excel文档流转换成DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文档流</param>
        /// <param name="sheetName">表名称</param>
        /// <param name="headerRowIndex">标题行索引号，如第一行为0</param>
        /// <returns></returns>
        public static DataTable RenderFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex)
        {
            DataTable table = null;

            using (excelFileStream)
            {
                using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
                {
                    using (ISheet sheet = workbook.GetSheet(sheetName))
                    {
                        table = RenderFromExcel(sheet, headerRowIndex);
                    }
                }
            }
            return table;
        }

        /// <summary>
        /// Excel文档流转换成DataTable
        /// 默认转换Excel的第一个表
        /// 第一行必须为标题行
        /// </summary>
        /// <param name="excelFileStream">Excel文档流</param>
        /// <returns></returns>
        public static DataTable RenderFromExcel(Stream excelFileStream)
        {
            return RenderFromExcel(excelFileStream, 0, 0);
        }

        /// <summary>
        /// Excel文档流转换成DataTable
        /// 第一行必须为标题行
        /// </summary>
        /// <param name="excelFileStream">Excel文档流</param>
        /// <param name="sheetIndex">表索引号，如第一个表为0</param>
        /// <returns></returns>
        public static DataTable RenderFromExcel(Stream excelFileStream, int sheetIndex)
        {
            return RenderFromExcel(excelFileStream, sheetIndex, 0);
        }

        /// <summary>
        /// Excel文档流转换成DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文档流</param>
        /// <param name="sheetIndex">表索引号，如第一个表为0</param>
        /// <param name="headerRowIndex">标题行索引号，如第一行为0</param>
        /// <returns></returns>
        public static DataTable RenderFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex)
        {
            DataTable table = null;

            using (excelFileStream)
            {
                using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
                {
                    using (ISheet sheet = workbook.GetSheetAt(sheetIndex))
                    {
                        table = RenderFromExcel(sheet, headerRowIndex);
                    }
                }
            }
            return table;
        }

        /// <summary>
        /// Excel表格转换成DataTable
        /// </summary>
        /// <param name="sheet">表格</param>
        /// <param name="headerRowIndex">标题行索引号，如第一行为0</param>
        /// <returns></returns>
        private static DataTable RenderFromExcel(ISheet sheet, int headerRowIndex)
        {
            DataTable table = new DataTable();

            IRow headerRow = sheet.GetRow(headerRowIndex);
            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] = GetCellValue(row.GetCell(j));
                    }
                }

                table.Rows.Add(dataRow);
            }

            return table;
        }

        /// <summary>
        /// Excel文档导入到数据库
        /// 默认取Excel的第一个表
        /// 第一行必须为标题行
        /// </summary>
        /// <param name="excelFileStream">Excel文档流</param>
        /// <param name="insertSql">插入语句</param>
        /// <param name="dbAction">更新到数据库的方法</param>
        /// <returns></returns>
        public static int RenderToDb(Stream excelFileStream, string insertSql, DBAction dbAction)
        {
            return RenderToDb(excelFileStream, insertSql, dbAction, 0, 0);
        }

        public delegate int DBAction(string sql, params IDataParameter[] parameters);

        /// <summary>
        /// Excel文档导入到数据库
        /// </summary>
        /// <param name="excelFileStream">Excel文档流</param>
        /// <param name="insertSql">插入语句</param>
        /// <param name="dbAction">更新到数据库的方法</param>
        /// <param name="sheetIndex">表索引号，如第一个表为0</param>
        /// <param name="headerRowIndex">标题行索引号，如第一行为0</param>
        /// <returns></returns>
        public static int RenderToDb(Stream excelFileStream, string insertSql, DBAction dbAction, int sheetIndex, int headerRowIndex)
        {
            int rowAffected = 0;
            using (excelFileStream)
            {
                using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
                {
                    using (ISheet sheet = workbook.GetSheetAt(sheetIndex))
                    {
                        StringBuilder builder = new StringBuilder();

                        IRow headerRow = sheet.GetRow(headerRowIndex);
                        int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
                        int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

                        for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row != null)
                            {
                                builder.Append(insertSql);
                                builder.Append(" values (");
                                for (int j = row.FirstCellNum; j < cellCount; j++)
                                {
                                    builder.AppendFormat("'{0}',", GetCellValue(row.GetCell(j)).Replace("'", "''"));
                                }
                                builder.Length = builder.Length - 1;
                                builder.Append(");");
                            }

                            if ((i % 50 == 0 || i == rowCount) && builder.Length > 0)
                            {
                                //每50条记录一次批量插入到数据库
                                rowAffected += dbAction(builder.ToString());
                                builder.Length = 0;
                            }
                        }
                    }
                }
            }
            return rowAffected;
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478

导入前端页面实现

上传文件基于ElementUI的上传功能

<el-upload class="upload-demo"
   ref="upload"
   :file-list="foleList"
   v-show="!hasFile"
   action="/MaterialManagement/upload"
   name="wrapper"
   :limit="1"
   :show-file-list="false"
   accept="application/xls,.xls"
   :on-success="successFile"
   :auto-upload="autoUpload"
   :before-upload="beforeUpload">
  <el-button size="small" type="primary">点击上传</el-button>
</el-upload>

<!--上面的方法可以根据官方API来完成-->
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

js代码编写

// 上传之前进行文件格式校验
beforeUpload(file) {
    const isXLS = file.type === 'application/vnd.ms-excel';
    if (isXLS) {
        return true;
    }
    const isXLSX = file.type === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
    if (isXLSX) {
        return true;
    }
    this.$message.error('上传文件只能是xls或者xlsx格式!');
    return false;
},
// 上传文件成功
successFile(response) {
    if (response.flag) {
        // 隐藏伤处文件按钮;
        this.hasFile = true;
        this.$message({
            message: response.message,
            type: 'success'
        });
        this.tableData2 = response.data.Data.rows;
        this.pagination2.total = response.data.Data.total;
    } else {
        this.$message({
            message: response.message,
            type: 'warning'
        });
    } 
    this.$refs.upload.clearFiles();
},
// 上传数据分页查询
handleCurrentChange2(currentPage) {
    this.pagination2.currentPage = currentPage;
    axios.post("/MaterialManagement/findByPage", this.pagination2).then((res) => {
        this.tableData2 = res.data.rows;
        this.pagination2.total = res.data.total;
    });
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

后端导入数据的实现【双重去重复判断】

该方法主要是对上传文件进行一个过滤，在前期就以及过滤了后面直接保存数据即可，同时还实现了一个上传数据的分页查询
注意观察自身数据去重复的嵌套for循环

#region 六、文件上传事件
public ActionResult upload(HttpPostedFileBase wrapper)
{
    Result result = new Result();
    //using (TransactionScope scope = new TransactionScope())
    //{
    //    scope.Complete();
    //}
    int ServiceStationCount = 0;//保存成功的条数
    int oldCount = 0;//已经存在的数据条数
    int renice = 0; // 上传文件已存在数据条数
    try
    {
        //文件类型
        string fileExtension = Path.GetExtension(wrapper.FileName).ToLower();
        if (".xls".Equals(fileExtension) || ".xlsx".Equals(fileExtension))
        {
            byte[] fileBytes = new byte[wrapper.ContentLength];//定义二进制数组
            wrapper.InputStream.Read(fileBytes, 0, wrapper.ContentLength);//读取文件内容
            if (ExcelReader.HasData(new MemoryStream(fileBytes)))
            {  // 判断上传的excel是否为空
               // 创建一个列表实体类，将上传的数据临时保存起来展示
                List<selectData> selectData = new List<selectData>();
                //获取 Excel的数据，放进DataTable中
                DataTable dtexcel = ExcelReader.RenderFromExcel(new MemoryStream(fileBytes), 0, 1);
                // 将第二行删除
                dtexcel.Rows.RemoveAt(0);

                foreach (DataRow row in dtexcel.Rows)
                {
                    // 创建一个对象存储上传的数据，并循环添加到一个List结合中
                    selectData selects = new selectData();
                    try
                    {
                        var Code = row.ItemArray[5].ToString().Trim();

                        // 循环遍历上传的数据与数据进行比对【数据比对之后再添加数据进入对象中】
                        var repetitionCount = myModels.matter_table.Where(o => o.matter_code == Code).Count();
                        // 查询重复数量
                        if (repetitionCount > 0)
                        {
                            oldCount++;
                        }
                        else
                        {
                            // 上传的excel单元格赋值实体类信息
                            selects.producer = row.ItemArray[0].ToString().Trim();
                            selects.franchiser = row.ItemArray[1].ToString().Trim();
                            selects.productType = row.ItemArray[2].ToString().Trim();
                            selects.TheWinningType = row.ItemArray[3].ToString().Trim();
                            selects.Name = row.ItemArray[4].ToString().Trim();
                            selects.purchasePrice = Convert.ToDecimal(row.ItemArray[6]);
                            selects.SellingPrice = Convert.ToDecimal(row.ItemArray[7]);
                            selects.Code = Code;
                            // 保存数据到集合中
                            selectData.Add(selects);
                            ServiceStationCount++;
                        }
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e);
                        result.flag = false;
                        result.message = "上传文件添加集合失败：" + e.Message;
                    }
                }

                // 遍历上传文件的重复数据
                List<selectData> selectData2 = new List<selectData>();

                for (int i = 0; i < selectData.Count; i++)
                {
                    if (i != 0)
                    {
                        // 倒序删除的方法，否则删除回出现报错
                        // 每一次删除都会导致集合的大小和元素索引值发生变化，从而导致在foreach中删除元素会出现异常。
                        for (int j = selectData2.Count - 1; j >= 0; j--)
                        {
                            if (selectData[i].Code.Trim() == selectData2[j].Code.Trim())
                            {
                                renice++;
                                selectData2.Remove(selectData2[j]);
                            }
                        }
                        selectData2.Add(selectData[i]);
                    }
                    else
                    {
                        selectData2.Add(selectData[0]);
                    }
                }
                // Session.Abandon();//清除全部Session
                //清除某个Session
                Session["ExcelFile"] = null;
                Session.Remove("ExcelFile");
                #region 返回数据
                // 判断读取成功的数据是否为0
                if (ServiceStationCount == 0 && oldCount > 0)
                {
                    result.flag = false;
                    result.message = "上传数据比对重复：" + oldCount + "条，请重新上传";
                }
                else
                {
                    // 先清空再赋值
                    result.flag = true;
                    result.message = "成功读取数据：" + selectData.Count + "条，数据库比对重复：" + oldCount + "条，上传数据自身重复：" + renice + "条,成功上传：" + (ServiceStationCount - renice) + "条数据";
                    queryData query = new queryData();
                    query.currentPage = 1;
                    query.pageSize = 5;
                    Session["ExcelFile"] = selectData2; // 赋值给本地保存
                    result.data = findByPage(query); // 返回分页的数据
                }
                #endregion
            }
            else
            {
                result.flag = false;
                result.message = "该Excel文件为空！";
            }
        }
        else
        {
            result.flag = false;
            result.message = "上传的文件类型不符";
        }
        return Json(result, JsonRequestBehavior.AllowGet);
    }
    catch (Exception e)
    {
        result.flag = false;
        result.message = "文件上传异常：" + e.Message;
        Console.WriteLine(e);
        return Json(wrapper, JsonRequestBehavior.AllowGet);
    }
}
#endregion
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137

上传数据实现分页查询返回页面

实现的原理与查询第一页数据展示一样的，都是将一个List集合数据分页返回而已

#region 七、对上传的文件进行分页查询返回页面
public ActionResult findByPage(queryData queryData)
{
    PageResult pageResult = new PageResult();
    try
    {
        //创建一个SeconBook集合对象
        List<selectData> listSecond = new List<selectData>();
        //判断Session中是否为空
        if (Session["ExcelFile"] != null)
        {
            //获取Session中保存的数据强转为selectData集合对象
            listSecond = Session["ExcelFile"] as List<selectData>;
        }
        //总行数
        int totalRow = listSecond.Count();
        //分页
        pageResult.total = totalRow;
        // var list_Subject_set = llist.Take(PageSize*PageIndex).Skip(PageSize* (PageIndex- 1)).ToList();
        pageResult.rows = listSecond.Take(queryData.pageSize * queryData.currentPage).Skip(queryData.pageSize * (queryData.currentPage - 1)).ToList();
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
    }
    return Json(pageResult, JsonRequestBehavior.AllowGet);
}
#endregion
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

6、导出数据实现

导出分为两种
1、条件查询导出
2、所有数据导出

导出数据效果图

前端页面代码判断请求

通过一个变量flag来判断是否导出当前页数还是全部数据

// 导出事件
derive() {
    // 异步请求下载
    this.$confirm('请问是导出当前页内容还是导出全部内容', '导出确认提示', {
        confirmButtonText: '导出当前页',
        cancelButtonText: '导出全部',
        type: 'warning'
    }).then(() => {
        window.location.href = "/MaterialManagement/ExportToExcel?currentPage=" + this.pagination.currentPage + "&pageSize=" + this.pagination.pageSize + "&name=" + this.pagination.name + "&code=" + this.pagination.code +"&flag=true";
    }).catch(() => {
        window.location.href = "/MaterialManagement/ExportToExcel?flag=false";
    });
}
1
2
3
4
5
6
7
8
9
10
11
12
13

导出当前页数据的实现

通过调用前面封装的ListData传递数据进行查询，根据查询的内容进行返回即可，同时下面的导出样式还封装了一个方法如下代码顶部

/// <summary>
/// 统一的导出格式
/// </summary>
/// <param name="style"></param>
public void iCellStyle(ICellStyle style)
{
    // 设置剧中
    style.Alignment = HorizontalAlignment.CENTER;
    style.VerticalAlignment = VerticalAlignment.CENTER;
    //设置边框线为实线
    style.BorderLeft = CellBorderType.THIN;
    style.BorderTop = CellBorderType.THIN;
    style.BorderRight = CellBorderType.THIN;
    style.BorderBottom = CellBorderType.THIN;
}

#region 九、NPOI导出到Excel 
//NPOI导出到Excel(故障码)
public ActionResult ExportToExcel(queryData queryData, Boolean flag)
{
    try
    {
        var listFaultInfo = ListData(queryData, flag).selectDatas;
        //二：代码创建一个Excel表格（这里称为工作簿）
        //创建Excel文件的对象 工作簿(调用NPOI文件)
        HSSFWorkbook excelBook = new HSSFWorkbook();

        #region 样式设计
        ICellStyle style2 = excelBook.CreateCellStyle();
        iCellStyle(style2);

        ICellStyle style3 = excelBook.CreateCellStyle();
        IFont font = excelBook.CreateFont();
        font.Color = IndexedColors.RED.Index;// 字体红色提示
        style3.SetFont(font);
        iCellStyle(style3);
        #endregion
        //创建Excel工作表 Sheet=物资信息
        ISheet sheet1 = excelBook.CreateSheet("物资信息");

        #region 表题合并列样式
        // 第一行表格表题
        IRow row0 = sheet1.CreateRow(0);
        row0.Height = 60 * 15;
        row0.CreateCell(0).SetCellValue("物资信息导出");
        sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 7)); // 合并信息
                                                                               // 设置表头样式
        ICellStyle style4 = excelBook.CreateCellStyle(); // 表头的
        iCellStyle(style4); // 实线
        style4.FillPattern = FillPatternType.SOLID_FOREGROUND;//设置背景填充效果
        style4.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.AQUA.index;//设置背景填充颜色
        IFont font_header = excelBook.CreateFont();//声明字体
        font_header.FontHeightInPoints = 20;//字体大小
        style4.SetFont(font_header);//加入单元格
        row0.GetCell(0).CellStyle = style4; // 输入表同样式
        #endregion

        //给Sheet添加第二行的头部标题
        IRow row1 = sheet1.CreateRow(1);
        row1.Height = 30 * 15;
        string[] strings = new string[] { "物资名称", "物资编号", "采购价格", "售卖价格", "生产商名称", "经销商名称", "产品类型", "中标类型" };
        int[] widths = new int[] { 40, 40 };
        // 遍历创建表头行
        for (int i = 0; i < strings.Length; i++)
        {
            row1.CreateCell(i).SetCellValue(strings[i]);//给标题的每一个单元格赋值
            row1.GetCell(i).CellStyle = style2;//初始化设置样式
            sheet1.SetColumnWidth(i, 20 * 256);
            if (i == 0 || i == 1)
            {
                row1.GetCell(i).CellStyle = style3;//初始化设置样式
            }
            else if (i == 4 || i == 5)
            {
                sheet1.SetColumnWidth(i, widths[i - 4] * 256);//初始化设置宽度
            }
        }
        //添加数据行：将表格数据逐步写入sheet1各个行中（也就是给每一个单元格赋值）
        for (int i = 0; i < listFaultInfo.Count; i++)
        {
            //sheet1.CreateRow(i).
            //创建行
            IRow rowTemp = sheet1.CreateRow(i + 2);
            rowTemp.Height = 30 * 15;

            //物资名称
            rowTemp.CreateCell(0).SetCellValue(listFaultInfo[i].Name);
            //物资编码
            rowTemp.CreateCell(1).SetCellValue(listFaultInfo[i].Code);
            //采购价格
            rowTemp.CreateCell(2).SetCellValue((double)listFaultInfo[i].purchasePrice);
            //售卖价格
            rowTemp.CreateCell(3).SetCellValue((double)listFaultInfo[i].SellingPrice);
            /// 生产商名称
            rowTemp.CreateCell(4).SetCellValue(listFaultInfo[i].producer);
            //经销商名称
            rowTemp.CreateCell(5).SetCellValue(listFaultInfo[i].franchiser);
            //产品类型
            rowTemp.CreateCell(6).SetCellValue(listFaultInfo[i].productType);
            //中标类型
            rowTemp.CreateCell(7).SetCellValue(listFaultInfo[i].TheWinningType);

            // 内层循环创建单元格数据
            for (int j = 0; j < 8; j++)
            {
                // rowTemp.CreateCell(j).SetCellValue();
                if (j == 0 || j == 1)
                {
                    rowTemp.GetCell(j).CellStyle = style3;
                }
                else
                {
                    rowTemp.GetCell(j).CellStyle = style2;
                }
            }
        }
        //输出的文件名称
        string fileName = "物资信息导出" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
        //把Excel转为流，输出
        //创建文件流
        System.IO.MemoryStream bookStream = new System.IO.MemoryStream();
        //将工作薄写入文件流
        excelBook.Write(bookStream);

        //输出之前调用Seek（偏移量，游标位置) 把0位置指定为开始位置
        bookStream.Seek(0, System.IO.SeekOrigin.Begin);
        //Stream对象,文件类型,文件名称
        return File(bookStream, "application/vnd.ms-excel", fileName);
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
        return null;
    }
}
#endregion
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136