using System;

最新推荐文章于 2020-07-13 17:24:38 发布

gophychan

最新推荐文章于 2020-07-13 17:24:38 发布

阅读量783

点赞数

分类专栏： Asp.net(C#) 文章标签： system string excel exception server path

Asp.net(C#) 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一种从Excel文件批量导入数据到SQL Server数据库的方法。涵盖了如何读取Excel文件内容、利用ADO.NET进行数据库操作及错误处理等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.IO;

using Microsoft.Office.Core;
namespace cncic_util
{
public class import_excel
{

public static string str_conn_excel = "Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended Properties=Excel 8.0";

private import_excel() { }

        public static string setExcelContentToDb(string excel_path, int columnCount, string sqlcmd)
        {
            int cTmp = 0;
            string[] valuesTmp = new string[columnCount];
            int iRows = 0;
            string copySqlcmd = sqlcmd;
            string error = string.Empty;
            int iTmp2 = 0;

            Excel.Application m_xlsApp = null;
            Excel.Workbook m_Workbook = null;   //工作簿
            Excel.Worksheet m_Worksheet = null; //表单
            Excel.Range m_RangeMSPart = null;   //单元格
            string vTmp = string.Empty;
            try
            {
                object objOpt = System.Reflection.Missing.Value;
                m_xlsApp = new Excel.Application();
                m_Workbook = m_xlsApp.Workbooks.Open(excel_path, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt);
                m_Worksheet = (Excel.Worksheet)m_Workbook.Worksheets.get_Item(1);

int data_count = m_Worksheet.UsedRange.Rows.Count;

                for (int i = 2; i <= data_count; i++)
                {
                    sqlcmd = copySqlcmd;
                    try
                    {
                        for (int j = 1; j <= columnCount; j++)
                        {
                            //此种方式取得单元格也可以
                            m_RangeMSPart = (Excel.Range)m_Worksheet.Cells[i, j];
                            #region 1
                            //m_RangeMSPart = m_Worksheet.get_Range("A" + i.ToString(), objOpt);
                            //m_RangePartDesc = m_Worksheet.get_Range("B" + i.ToString(), objOpt);
                            //m_RangeType = m_Worksheet.get_Range("C" + i.ToString(), objOpt);
                            //m_RangeStatus = m_Worksheet.get_Range("D" + i.ToString(), objOpt);
                            #endregion

                            if (m_RangeMSPart.Value2 != null)
                            {
                                //vTmp = m_RangeMSPart.Value2.ToString();
                                vTmp = m_RangeMSPart.Text.ToString();
                            }
                            else
                            {
                                vTmp = string.Empty;
                            }
                            valuesTmp[cTmp++] = vTmp;
                        }
                        if (valuesTmp[0].Trim().Length == 0)
                        {
                            return "导入完成~";
                        }
                        sqlcmd = string.Format(sqlcmd, valuesTmp);
                        ++iRows;
                        cTmp = 0;

                        cncic.kmp.DataBase.helper.SQL.ExecuteNonQuery(connection_string.manger_system, CommandType.Text, sqlcmd);
                    }
                    catch (SqlException sqlex)
                    {
                        continue;
                    }
                }
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                m_xlsApp.Quit();
                m_xlsApp = null;
            }

return error;
}

        public static DataTable getDataTableByExcelPath(string columnName, string excel_path, int iColumnValue)
        {
            DataTable dtTemp = new DataTable();
            DataColumn column;
            column = new DataColumn(columnName);
            dtTemp.Columns.Add(column);

            Excel.Application m_xlsApp = null;
            Excel.Workbook m_Workbook = null;
            Excel.Worksheet m_Worksheet = null;
            Excel.Range m_RangeMSPart = null;
            try
            {
                object objOpt = System.Reflection.Missing.Value;
                m_xlsApp = new Excel.Application();
                m_Workbook = m_xlsApp.Workbooks.Open(excel_path, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt);
                m_Worksheet = (Excel.Worksheet)m_Workbook.Worksheets.get_Item(1);
                DataRow newRow;
                for (int i = 1; i <= m_Worksheet.UsedRange.Rows.Count; i++)
                {
                    //此种方式取得单元格也可以
                    m_RangeMSPart = (Excel.Range)m_Worksheet.Cells[i, iColumnValue];

                    //m_RangeMSPart = m_Worksheet.get_Range("A" + i.ToString(), objOpt);
                    //m_RangePartDesc = m_Worksheet.get_Range("B" + i.ToString(), objOpt);
                    //m_RangeType = m_Worksheet.get_Range("C" + i.ToString(), objOpt);
                    //m_RangeStatus = m_Worksheet.get_Range("D" + i.ToString(), objOpt);

                    newRow = dtTemp.NewRow();
                    if (m_RangeMSPart.Value2 != null)
                    {
                        newRow[columnName] = m_RangeMSPart.Value2.ToString();
                    }
                    else
                    {
                        newRow[columnName] = "";
                    }
                    dtTemp.Rows.Add(newRow);
                }
                return dtTemp;
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                m_xlsApp.Quit();
                m_xlsApp = null;
            }
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="sqlcmd">select * from [{0}]</param>
        /// <param name="excel_path">excel in server path</param>
        /// <param name="dt">out DataTable</param>
        /// <returns></returns>
        public static bool read_excel(string sqlcmd, string excel_path, out DataTable dt)
        {
            dt = new DataTable();
            str_conn_excel = string.Format(str_conn_excel, excel_path);
            string table_name = string.Empty;
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(str_conn_excel);
                conn.Open();
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                table_name = dt.Rows[0]["TABLE_NAME"].ToString().Trim();
                dt.Clear();
                OleDbDataAdapter adp = new OleDbDataAdapter(string.Format(sqlcmd, table_name), conn);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                dt = ds.Tables[0];
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
                return false;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// upload method
        /// </summary>
        /// <param name="hpf">FileUpLoad Control</param>
        /// <param name="server_path">file in local path</param>
        /// <param name="error">out error</param>
        /// <returns></returns>
        public static bool set_excel_to_server(HttpPostedFile hpf, string server_path, out string error)
        {
            error = string.Empty;
            if (server_path.Length == 0)
            {
                error = "服务端路径不能为空!";
                return false;
            }
            else
            {
                hpf.SaveAs(server_path);
                error = "上传成功!";
            }
            return true;
        }

        public static bool set_excel_to_server_and_next_to_db(string sqlcmd, string table_name, out DataTable dt, HttpPostedFile hpf,
            string server_path, string userId, string connection_string, out string error)
        {
            error = string.Empty;
            dt = new DataTable();
            if ((set_excel_to_server(hpf, server_path, out error) && read_excel(sqlcmd, server_path, out dt)))
            {
                return set_excel_content_to_db(dt, table_name, userId, connection_string, out error);
            }
            else
            {
                return false;
            }
        }

        public static bool set_excel_content_to_db(DataTable dt, string table_name, string userId, string connection_string, out string error)
        {
            error = string.Empty;
            try
            {
                return InsertSqlserver(dt, table_name, connection_string, out error);
            }
            catch (Exception ex)
            {
                error = ex.ToString();
                return false;
            }
        }

        private static bool InsertSqlserver(DataTable dt, string tableName, string connection_string, out string error)
        {
            error = string.Empty;
            try
            {
                using (SqlConnection conn = new SqlConnection(connection_string))
                {
                    conn.Open();
                    using (SqlBulkCopy sqlbulk = new SqlBulkCopy(conn))
                    {
                        sqlbulk.DestinationTableName = tableName;
                        sqlbulk.NotifyAfter = dt.Rows.Count;
                        sqlbulk.ColumnMappings.Add("固定资产编号", "BH");
                        sqlbulk.ColumnMappings.Add("使用部门", "SYBM");
                        sqlbulk.ColumnMappings.Add("存放部门", "BMMC");
                        sqlbulk.ColumnMappings.Add("固定资产名称", "GDZCMC");
                        sqlbulk.ColumnMappings.Add("规格型号", "GGXH");
                        sqlbulk.ColumnMappings.Add("开始使用日期", "GMRQ");
                        sqlbulk.ColumnMappings.Add("凭证号", "PZH");
                        sqlbulk.ColumnMappings.Add("使用年限(月)", "SYNX");
                        sqlbulk.ColumnMappings.Add("原值", "YZJE");
                        sqlbulk.ColumnMappings.Add("本月计提折旧额", "BYZJ");
                        sqlbulk.ColumnMappings.Add("累计折旧", "LJZJ");
                        sqlbulk.ColumnMappings.Add("净值", "JZ");
                        sqlbulk.ColumnMappings.Add("存放地点", "CFDD");
                        sqlbulk.ColumnMappings.Add("使用人", "SYR");
                        sqlbulk.ColumnMappings.Add("年折旧额", "NZJE");
                        sqlbulk.ColumnMappings.Add("userid", "userid");
                        sqlbulk.WriteToServer(dt);
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                error = ex.ToString();
            }
            error = "导入成功!";
            return false;
        }

        public static bool set_excel_content_to_db(string userId, string sqlcmd, string tableName, string connection_string, string server_path, string local_path,
            out string error, out DataTable dtTmp)
        {
            dtTmp = new DataTable();
            error = string.Empty;
            DataTable dt = null;
            List<string> list = get_file_by_mu_lu(local_path);
            try
            {
                if (read_excel(sqlcmd, server_path, out dt))
                {
                    DataTable dt2 = dt.Clone();
                    int c = dt.Rows.Count;
                    //去掉在 DataTable 中不存在的(行)
                    for (int i = 0; i < c; i++)
                    {
                        string upload_file_name = dt.Rows[i]["教材名称"].ToString();
                        if (IsExists(list, upload_file_name))
                        {
                            DataRow dr = dt2.NewRow();
                            dr.ItemArray = dt.Rows[i].ItemArray;
                            dt2.Rows.Add(dr);
                        }
                        else
                        {
                            error += (i + 1) + ",";
                        }
                    }

dtTmp = dt2.Copy();

                    //批量导入数据库
                    using (SqlConnection conn = new SqlConnection(connection_string))
                    {
                        conn.Open();
                        using (SqlBulkCopy sqlbulk = new SqlBulkCopy(conn))
                        {
                            sqlbulk.DestinationTableName = tableName;
                            sqlbulk.NotifyAfter = dt2.Rows.Count;
                            sqlbulk.ColumnMappings.Add("教材名称", "JCMC");
                            sqlbulk.ColumnMappings.Add("教材类别", "JCLB");
                            sqlbulk.ColumnMappings.Add("编制单位", "BZDW");
                            sqlbulk.ColumnMappings.Add("教材形式", "JCXS");
                            sqlbulk.ColumnMappings.Add("原件", "YJ");
                            sqlbulk.ColumnMappings.Add("编制日期", "BZRQ");
                            sqlbulk.ColumnMappings.Add("备注", "BZ");
                            sqlbulk.ColumnMappings.Add("采编部门", "CBBM");
                            sqlbulk.ColumnMappings.Add("采编者", "CBR");
                            sqlbulk.ColumnMappings.Add("采编日期", "CBRQ");
                            sqlbulk.WriteToServer(dt2);
                            return true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                error = ex.ToString();
            }
            return false;
        }

        public static bool IsExists(List<string> s, string file)
        {
            return s.Contains(file);
        }

        //由一个路径得到这个目录下的所有文件
        public static List<string> get_file_by_mu_lu(string server_path)
        {
            List<string> file_list = new List<string>();
            string local_ml = server_path.Substring(0, server_path.LastIndexOf('//'));
            DirectoryInfo UnPostil = new DirectoryInfo(local_ml);
            FileInfo[] ArrUnPostil = UnPostil.GetFiles();
            foreach (FileInfo FileName in ArrUnPostil)
            {
                if (FileName.Length > 0)
                {
                    string sTmp = FileName.Name.Substring(0, FileName.Name.LastIndexOf('.'));
                    file_list.Add(sTmp);
                }
            }
            return file_list;
        }
        /// <summary>
        /// 这个方法就是批量入库的方法
        /// </summary>
        /// <param name="dic"></param>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <param name="connection_string"></param>
        /// <param name="error"></param>
        /// <returns></returns>
        public static bool set_excel_content_to_db(Dictionary<int, string> dic, DataTable dt, string tableName,
            string connection_string, out string error)
        {
            error = string.Empty;
            try
            {
                using (SqlConnection conn = new SqlConnection(connection_string))
                {
                    conn.Open();
                    using (SqlBulkCopy sqlbulk = new SqlBulkCopy(conn))
                    {
                        sqlbulk.DestinationTableName = tableName;
                        sqlbulk.NotifyAfter = dt.Rows.Count;
                        foreach (KeyValuePair<int, string> kvp in dic)
                        {
                            sqlbulk.ColumnMappings.Add(kvp.Key, kvp.Value);
                        }
                        sqlbulk.WriteToServer(dt);
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                error = ex.ToString();
            }
            if (error.Replace(" ", "") == string.Empty)
            {
                error = "导入成功!";
            }
            return false;
        }
        /// <summary>
        /// 使用方法:
        /// </summary>
        /// <param name="dic">dic 中参数: int 是来源列, string 是目标列</param>
        /// <param name="dt">要导入的DataTable</param>
        /// <param name="tableName">目标表</param>
        /// <param name="connection_string"></param>
        /// <returns></returns>
        public static bool _bSetContentToDb(Dictionary<int, string> dic, DataTable dt, string tableName,
            string connection_string)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connection_string))
                {
                    conn.Open();
                    using (SqlBulkCopy sqlbulk = new SqlBulkCopy(conn))
                    {
                        sqlbulk.DestinationTableName = tableName;
                        sqlbulk.NotifyAfter = dt.Rows.Count;
                        sqlbulk.BulkCopyTimeout = 60 * 2;
                        foreach (KeyValuePair<int, string> kvp in dic)
                        {
                            sqlbulk.ColumnMappings.Add(kvp.Key, kvp.Value);
                        }
                        sqlbulk.WriteToServer(dt);
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="dic">dic 中参数: string 是来源列, string 是目标列</param>
        /// <param name="dt">要导入的DataTable</param>
        /// <param name="tableName">目标表</param>
        /// <param name="connection_string"></param>
        /// <returns></returns>
        public static bool _bSetContentToDb(Dictionary<string, string> dic, DataTable dt, string tableName,
           string connection_string)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connection_string))
                {
                    conn.Open();
                    using (SqlBulkCopy sqlbulk = new SqlBulkCopy(conn))
                    {
                        sqlbulk.DestinationTableName = tableName;
                        sqlbulk.NotifyAfter = dt.Rows.Count;
                        sqlbulk.BulkCopyTimeout = 60 * 2;
                        foreach (KeyValuePair<string, string> kvp in dic)
                        {
                            sqlbulk.ColumnMappings.Add(kvp.Key, kvp.Value);
                        }
                        sqlbulk.WriteToServer(dt);
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}