Api---导入Excel表格数据形式存放到sql数据库中

本文主要是介绍Api---导入Excel表格数据形式存放到sql数据库中，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

命名空间：

using System.Data;

using System.Linq;

using System;

using System.IO;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

接口：

namespace Yes.Manage.Api.Controllers.Craftsman
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class CraftsmanController : ControllerBase    {
       [HttpPost]
        public ActionResult<SysStatus> daotru(IFormFile fct)
        {
            SysStatus status = new SysStatus();
            try
            {
                if (fct != null)
                {
                    if (fct.Length > 0)
                    {
                        DataTable dt = new DataTable();
                        string strMsg = "";
                        //利用IFormFile里面的OpenReadStream()方法直接读取文件流
                        dt =ExcelToDatatable(fct.OpenReadStream(), Path.GetExtension(fct.FileName), out strMsg);//
                        if (!string.IsNullOrEmpty(strMsg))
                        {
                            status.Code = 5;
                            status.Message = strMsg;
                        }
                        else
                        {
                            if (dt.Rows.Count > 0)
                            {
                                if (Posdate(dt))//posdate是把DataTable dt数据导入数据库中，返回bool类型
                                {
                                    status.Code = 0;
                                    status.Message = "数据导入成功";
                                }
                                else
                                {
                                    status.Code = 2;
                                    status.Message = "数据导入失败";
                                }
                            }
                            else
                            {
                                status.Code = 3;
                                status.Message = "Excel导入表无数据!";
                            }
                        }
                    }
                }
                else
                {
                    status.Code = 6;
                    status.Message = "文件不能为空";
                }
            }
            catch (Exception ex)
            {

                status.Code = 4;
                status.Message = ex.Message;
            }

            return status;
        }
    }
}

#region excel表格数据加载数据库表
        /// <summary>
        /// 将Excel单表转为Datatable
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="fileType"></param>
        /// <param name="strMsg"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public DataTable ExcelToDatatable(Stream stream, string fileType, out string strMsg, string sheetName = null)
        {
            strMsg = "";
            DataTable dt = new DataTable();
            ISheet sheet = null;
            IWorkbook workbook = null;
            try
            {
                #region 判断excel版本
                //2007以上版本excel
                if (fileType == ".xlsx")
                {
                    workbook = new XSSFWorkbook(stream);
                }
                //2007以下版本excel
                else if (fileType == ".xls")
                {
                    workbook = new HSSFWorkbook(stream);
                }
                else
                {
                    throw new Exception("传入的不是Excel文件！");
                }
                #endregion
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;
                    for (int i = firstRow.FirstCellNum; i < cellCount; i++)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue.Trim();
                            switch (cellValue)
                            {
                                case "证书类型":
                                    cellValue = "CertificateType";
                                    break;
                                case "姓名":
                                    cellValue = "CraftsmanName";
                                    break;
                                case "性别":
                                    cellValue = "CraftsmanSex";
                                    break;
                                case "学历":
                                    cellValue = "CraftsmanEducation";
                                    break;
                                case "手机号":
                                    cellValue = "CraftsmanPhone";
                                    break;
                                case "身份证号":
                                    cellValue = "IdCard";
                                    break;
                                case "证书编号":
                                    cellValue = "CertificateId";
                                    break;
                                case "证书有效期":
                                    cellValue = "CertificateTimes";
                                    break;
                                case "户籍地址":
                                    cellValue = "HjAddress";
                                    break;
                                case "常住地址":
                                    cellValue = "CzAddress";
                                    break;
                                case "所属乡镇":
                                    cellValue = "Township";
                                    break;
                                case "证书链接":
                                    cellValue = "CertificateUrl";
                                    break;
                                case "头像":
                                    cellValue = "CraftsmanUrl";
                                    break;
                                case "身份证正面":
                                    cellValue = "IcFrontUrl";
                                    break;
                                case "身份证反面":
                                    cellValue = "IcBackUrl";
                                    break;
                            }
                            if (!string.IsNullOrEmpty(cellValue))
                            {
                                DataColumn dataColumn = new DataColumn(cellValue);
                                dt.Columns.Add(dataColumn);
                            }
                        }
                    }
                    DataRow dataRow = null;
                    //遍历行
                    for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
                    {
                        IRow row = sheet.GetRow(j);
                        dataRow = dt.NewRow();
                        if (row == null || row.FirstCellNum < 0)
                        {
                            continue;
                        }
                        //遍历列
                        for (int i = row.FirstCellNum; i < cellCount; i++)
                        {
                            ICell cellData = row.GetCell(i);
                            if (cellData != null)
                            {
                                //判断是否为数字型，必须加这个判断不然下面的日期判断会异常
                                if (cellData.CellType == CellType.Numeric)
                                {
                                    //判断是否日期类型
                                    if (DateUtil.IsCellDateFormatted(cellData))
                                    {
                                        dataRow[i] = cellData.DateCellValue;
                                    }
                                    else
                                    {
                                        dataRow[i] = cellData.ToString().Trim();
                                    }
                                }
                                else
                                {
                                    dataRow[i] = cellData.ToString().Trim();
                                }
                            }
                        }
                        dt.Rows.Add(dataRow);
                    }
                }
                else
                {
                    throw new Exception("没有获取到Excel中的数据表！");
                }
            }
            catch (Exception ex)
            {
                strMsg = ex.Message;
            }
            return dt;
        }
        #endregion

这篇关于Api---导入Excel表格数据形式存放到sql数据库中的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！