C#导入和导出Excel文件数据

最新推荐文章于 2025-03-19 12:21:34 发布

Ctrl+Z侠

最新推荐文章于 2025-03-19 12:21:34 发布

阅读量871

点赞数 5

文章标签： excel c#

本文链接：https://blog.youkuaiyun.com/m0_52522230/article/details/144137816

版权

这里使用到了反射的机制，更灵活性的去修改Excel文件的表头名称

一、导入Excel文件数据

思路：当系统有导入Excel文件的功能，一般情况我们都是需要自己设置好一个Excel模版让用户去下载模版进行添加数据后在导入到系统里。

以下是个示例代码：

1、控制器

        /// <summary>
        /// Excel导入员工信息
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        [Route("ExcelcImportEmployeeData"), HttpPost]
        public async Task<IHttpActionResult> ExcelcImportEmployeeData(string filePath)
        {
            return Ok(await _dal.ExcelcImportEmployeeData(filePath));
        }

2、逻辑层

下面代码不用看逻辑，这里只是做个例子，重点就是判断Excel文件标题是否一致，主要是看ExcelDBHelper帮助类

 /// <summary>
        /// Excel导入员工信息
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        public async Task<ImportExcelDataResult> ExcelcImportEmployeeData(string filePath)
        {
            ImportExcelDataResult result = new ImportExcelDataResult();
            //解码路径
            filePath = HttpUtility.UrlDecode(filePath);
            string currentDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Excel文件"); //存放Excel文件夹路径
            string fileName = Path.GetFileName(filePath); //获取文件名
            //拼接绝对文件全路径
            string file = Path.Combine(currentDirectory, fileName);
            //在这里根据期望的列标题进行验证
            List<string> expectedTitles = new List<string> { "姓名", "身份证号", "手机号", "开户行", "银行", "银行账号", "行业", "部门", "职位" };
            if (await ExcelDBHelper.ValidateExcel(file, expectedTitles)) //验证Excel标题
            {
                //读取Excel数据并转换成DataTable数据类型
                DataTable table = await ExcelDBHelper.ReadExcelToDataTable(file);
                //过滤Excel文件里重复的身份证号数据 并 获取一条数据
                var duplicateRows = table.AsEnumerable().GroupBy(row => row["身份证号"]).Where(x => x.Count() > 1).SelectMany(x => x.Skip(1));
                int excelTotal = 0; //Excel文件重复条数
                if (duplicateRows != null) // 删除重复数据中的一条
                {
                    foreach (var row in duplicateRows)
                    {
                        excelTotal++;
                        // 删除重复的数据 只保留一条
                        table.Rows.Remove(row);
                    }
                }

                StringBuilder sbSql = new StringBuilder(); //存放sql语句

                int total = table.Rows.Count; //获取总条数
                int successTotal = 0; //添加成功条数
                int failTotal = 0;//添加失败条数
                int filter = 0; //重复数据条数
                string sqlStr = string.Empty;
                string filterSql = string.Empty;
                string idNumberArr = string.Empty;
                //按(身份证号)判断有多少条重复数据 过滤掉重复数据
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    idNumberArr += "'" + table.Rows[i]["身份证号"] + "',";
                }
                idNumberArr = idNumberArr.TrimEnd(',');
                filterSql = $"SELECT IdNumber FROM employeeinfo WHERE IdNumber IN ({idNumberArr})";
                //重复身份证号数据
                DataTable filterTable = await MySqlHelper.ExecuteReaderAsync(filterSql);
                List<FilterTableResult> filterList = JsonConvert.DeserializeObject<List<FilterTableResult>>(JsonConvert.SerializeObject(filterTable));
                //遍历数据
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    //这里过滤掉重复数据 只添加没有重复数据
                    if (filterList.Where(x => x.IdNumber == table.Rows[i]["身份证号"].ToString()).ToList().Count <= 0)
                    {
                        //添加 SQL 语句
                        sqlStr = @"INSERT INTO employeeinfo(Name,IdNumber,Phone,BankOfDeposit,Bank,BankAccountNumber,Industry,Department,Position)VALUES(@Name, @IdNumber, @Phone, @BankOfDeposit, @Bank, @BankAccountNumber, @Industry, @Department, @Position)";
                        //参数
                        Dictionary<string, object> parments = new Dictionary<string, object>
                    {
                        {"@Name",table.Rows[i]["姓名"] },
                        {"@IdNumber",table.Rows[i]["身份证号"] },
                        {"@Phone",table.Rows[i]["手机号"] },
                        {"@BankOfDeposit",table.Rows[i]["开户行"] },
                        {"@Bank",table.Rows[i]["银行"] },
                        {"@BankAccountNumber",table.Rows[i]["银行账号"] },
                        {"@Industry",table.Rows[i]["行业"] },
                        {"@Department",table.Rows[i]["部门"] },
                        {"@Position",table.Rows[i]["职位"] }
                    };
                        if (await MySqlHelper.ExecuteNonQueryAsync(sqlStr, parments) > 0) //成功
                        {
                            successTotal++;
                        }
                        else //失败
                        {
                            failTotal++;
                        }
                    }
                    else //身份证号重复数据
                    {
                        filter++;
                    }
                }
                result = new ImportExcelDataResult { type = 200, message = $"数据导入成功 共：{total}条数据、成功：{successTotal}条数据、失败：{failTotal}条数据、重复：{filter}条数据、Excel文件里重复：{excelTotal}条数据" };
            }
            else  //Excel标题不正确
            {
                result = new ImportExcelDataResult { type = 1, message = "文件标题不合规,请正确下载文件模版" };
            }
            return result;
        }

3、ExcelDBHelper帮助类

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace WYC.DAL.DBHelper
{
    /// <summary>
    /// Excel帮助类  注意：有可能需要安装驱动：
    /// 下载地址：https://www.microsoft.com/zh-cn/download/details.aspx?id=13255
    /// </summary>
    public class ExcelDBHelper
    {
        /// <summary>
        /// 验证Excel标题
        /// </summary>
        /// <param name="filePath">文件路径(不支持外网路径)</param>
        /// <param name="expectedTitles">列标题进行验证</param>
        /// <returns>false:标题不一致 true:标题一致</returns>
        public async static Task<bool> ValidateExcel(string filePath, List<string> expectedTitles)
        {
            bool titlesMatch = false;
            //获取连接字符串
            string connectionString = GetExcelConnectionString(filePath);
            //连接数据库
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                await connection.OpenAsync(); //打开数据库
                DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //获取数据
                if (schemaTable != null && schemaTable.Rows.Count > 0) //有数据
                {
                    string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString();

                    //读取 Excel 文件的第一行（标题行）
                    string commandText = $"SELECT * FROM [{sheetName}]";
                    using (OleDbCommand command = new OleDbCommand(commandText, connection))
                    {
                        //连接状态不等于打开状态 则打开数据库
                        if (connection.State != ConnectionState.Open) await connection.OpenAsync();
                        using (OleDbDataReader reader = command.ExecuteReader())
                        {
                            if (reader != null && reader.HasRows)
                            {
                                //获取列标题
                                List<string> columnTitles = new List<string>();
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    columnTitles.Add(reader.GetName(i));
                                }
                                //检查标题是否一致
                                titlesMatch = columnTitles.SequenceEqual(expectedTitles);
                            }
                        }
                    }
                }
            }
            return titlesMatch;
        }

        /// <summary>
        /// 读取Excel数据并转换DataTable
        /// </summary>
        /// <param name="filePath">文件路径(不支持外网路径)</param>
        /// <returns></returns>
        public async static Task<DataTable> ReadExcelToDataTable(string filePath)
        {
            DataTable dataTable = new DataTable();
            //获取Excel连接字符串
            string connectionString = GetExcelConnectionString(filePath);

            //使用 OleDbConnection 打开 Excel 连接
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                await connection.OpenAsync(); //打开数据库
                // 获取 Excel 中的表格信息
                DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //没有数据 直接返回
                if (schemaTable == null || schemaTable.Rows.Count == 0) return dataTable;
                //读取第一个表的名称
                string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString();
                //sql语句
                string sqlStr = $"SELECT * FROM [{sheetName}]";
                using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sqlStr, connection))
                {
                    dataAdapter.Fill(dataTable);
                }
            }
            return dataTable;
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private static string GetExcelConnectionString(string filePath)
        {
            // 根据 Excel 文件的扩展名选择合适的驱动
            string extension = Path.GetExtension(filePath);
            string excelDriver;
            if (extension != null && extension.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                excelDriver = "Microsoft.ACE.OLEDB.12.0";
            }
            else if (extension != null && extension.Equals(".xls", StringComparison.OrdinalIgnoreCase))
            {
                excelDriver = "Microsoft.Jet.OLEDB.4.0";
            }
            else
            {
                throw new NotSupportedException("Unsupported Excel file format.");
            }

            // 构造连接字符串
            return $"Provider={excelDriver};Data Source={filePath};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
        }
    }
}

二、导出数据到Excel文件

思路：系统都会存在将查询出来的数据导出成Excel文件，但有时候会修改或者添加或者删除Excel标题，如果是硬写代码的话修改起来就比较多而且繁琐，这里使用的是反射机制来处理的，只需要给对应的属性添加特性就可以显示Excel标题了，如果要删除列或者修改名称或者添加列只需要修改Model的对应属性的特性足以。

1、控制器

/// <summary>
  /// 报告/图书寄送 导出
  /// </summary>
  /// <param name="request">数据</param>
  /// <returns></returns>
  [Route("ShippingExcelDerive"), HttpPost]
  public IHttpActionResult ShippingExcelDerive([FromBody] List<ShippingExcelRequest> request)
  {
      return Ok(ReportBookHelper.Instanct.ShippingExcelDerive(request));
  }

2、逻辑层

/// <summary>
 /// 报告/图书寄送 导出
 /// </summary>
 /// <param name="request">数据</param>
 /// <returns></returns>
 public string ShippingExcelDerive(List<ShippingExcelRequest> request)
 {
     //上传成功后的文件路径
     string ossFilePath = "";
     ossFilePath = ExcelDeriveHelper.ExceDerive(request); //导出Excel
     ossFilePath = AliYunOssHelper.ThisFileUploadOss(ossFilePath, "AllExcelFile", true); //上传OSS
     return ossFilePath;
 }

3、Model

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HRflag.Model.Model
{
    public class ShippingExcelRequest
    {
        /// <summary>
        /// 报告图书名称
        /// </summary>
        [ExcelColumn("报告图书名称")]
        public string BokksSendName { get; set; }
        /// <summary>
        /// 数量
        /// </summary>
        [ExcelColumn("数量")]
        public string SonBooksNumber { get; set; }
        /// <summary>
        /// 收件人
        /// </summary>
        [ExcelColumn("收件人")]
        public string Name { get; set; }
        /// <summary>
        /// 公司名称
        /// </summary>
        [ExcelColumn("公司名称")]
        public string CompanyName { get; set; }
        /// <summary>
        /// 部门
        /// </summary>
        [ExcelColumn("部门")]
        public string Department { get; set; }
        /// <summary>
        /// 职位
        /// </summary>
        [ExcelColumn("职位")]
        public string Position { get; set; }
        /// <summary>
        /// 客户手机号
        /// </summary>
        [ExcelColumn("手机号")]
        public string ClientPhone { get; set; }
        /// <summary>
        /// 邮箱
        /// </summary>
        [ExcelColumn("邮箱")]
        public string Email { get; set; }
        /// <summary>
        /// 地址
        /// </summary>
        [ExcelColumn("地址")]
        public string AddressTxt { get; set; }
        /// <summary>
        /// 快递公司
        /// </summary>
        [ExcelColumn("快递公司")]
        public string ExpressName { get; set; }
        /// <summary>
        /// 快递单号
        /// </summary>
        [ExcelColumn("快递单号")]
        public string OddNumber { get; set; }
    }
}

4、ExcelDeriveHelper帮助类：

using HRflag.Model.Model;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DBConnection.Helper
{
    /// <summary>
    /// Excel导出帮助类
    /// </summary>
    internal class ExcelDeriveHelper
    {
        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// <remarks>
        /// <code>
        /// 注意：把Model属性需要作为Excel表头的都加上[ExcelColumn("联系ID")]这个特性 ，如果不加这个特性，那么就会以属性名作为表头
        /// </code>
        /// </remarks>
        /// <typeparam name="T">集合数据</typeparam>
        /// <param name="t"></param>
        /// <returns>返回文件本地路径</returns>
        /// <exception cref="Exception"></exception>
        public static string ExceDerive<T>(List<T> t) where T : class, new()
        {
            string filePath = Path.Combine(AppContext.BaseDirectory, "所有Excel文件");  //获取当前项目路径
            string saveFilePath = string.Empty; //保存文件路径
            try
            {
                // 获取当前时间的时间戳（毫秒）
                long timestamp = DateTimeOffset.Now.ToUnixTimeMilliseconds();
                byte[] bytes = ToExcelBytes(t);  //获取将数据转换成字节
                if (!Directory.Exists(filePath)) //说明该路径下面没有文件  则创建一个文件夹
                    Directory.CreateDirectory(filePath);
                string pathStr = DateTime.Now.ToString("yyyyMMddhhmmss") + "_"+ timestamp + "共(" + t.Count + ")条.xls";
                saveFilePath = Path.Combine(filePath, pathStr);  //文件名称
                using (FileStream fs = new FileStream(saveFilePath, FileMode.Create, FileAccess.Write))  //写入Excel文件
                {
                    fs.Write(bytes, 0, bytes.Length);
                    fs.Flush();
                    return saveFilePath;  //创建成功
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 将集合转换成字节数组
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="items"></param>
        /// <returns></returns>
        private static byte[] ToExcelBytes<TEntity>(IList<TEntity> items) where TEntity : class, new()
        {
            var workbook = new HSSFWorkbook();  //HSSFWorkbook()导出Excel最大行数是65536行   XSSFWorkbook()导出Excel最大行数是1048576行
            ISheet sheet = workbook.CreateSheet("sheet1");  //创建工作簿
            int sheetNum = 1;  //统计几个工作簿
            SetTitle<TEntity>(workbook, sheet);  //编写标题
            int index = 1;  //统计条数
            int col = 0;  //统计单元格个数
            foreach (TEntity entity in items)
            {
                if (index % 65535 == 0)  //创建多个工资表单  这里就是解决.xls文件导出数据超出65536行的问题 可以用创建多个工作簿来解决这个问题
                {
                    sheetNum++; //统计几个工作簿
                    sheet = workbook.CreateSheet("sheet" + sheetNum);  //创建工作簿
                    index = 1;  //从第二个工作表单第一行开始插入数据
                    SetTitle<TEntity>(workbook, sheet);  //编写标题
                }
                IRow row = sheet.CreateRow(index);
                PropertyInfo[] values = entity.GetType().GetProperties();
                col = 0;
                foreach (PropertyInfo property in values)  //添加单元格个数
                {
                    object val = property.GetValue(entity, null);
                    if (val == null)
                        row.CreateCell(col).SetCellValue("");
                    else
                        row.CreateCell(col).SetCellValue(val.ToString());
                    col++;
                }
                index++;
            }
            //写入
            using (var file = new MemoryStream())
            {
                workbook.Write(file);
                return file.GetBuffer();
            }
        }

        /// <summary>
        /// 写入Excel标题方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        private static void SetTitle<T>(HSSFWorkbook workbook, ISheet sheet)
        {
            ICellStyle style = workbook.CreateCellStyle();  //创建样式对象
            IFont fonts = workbook.CreateFont(); //创建字体样式
            style.FillForegroundColor = HSSFColor.Green.Index;  //设置样式
            style.FillPattern = FillPattern.SolidForeground;
            fonts.Color = IndexedColors.Yellow.Index; //字体颜色
            style.SetFont(fonts);
            var titles = new List<string>();
            PropertyInfo[] propertys = typeof(T).GetProperties();
            foreach (PropertyInfo property in propertys)
            {
                object[] excelColumns = property.GetCustomAttributes(typeof(ExcelColumnAttribute), true);
                if (excelColumns.Length > 0)
                {
                    string like = (excelColumns[0] as ExcelColumnAttribute).DisplayNameLike;

                    if (!string.IsNullOrEmpty(like))
                    {
                        titles.Add(like);
                    }
                    else
                    {
                        titles.Add(property.Name);
                    }
                }
            }

            //写入
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < titles.Count; i++)
            {

                row.CreateCell(i).SetCellValue(titles[i]);
                row.Cells[i].CellStyle = style; //设置标题单元格和字体样式
            }
        }

    }
}