Excel文件导入到数据库

此案例需要引入NPOI.dll文件和Ionic.Zip.dll文件

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using NPOI.SS.UserModel;

using NPOI.HSSF.UserModel;

using System.Data.SqlClient;

using System.Data;

using Microsoft.International.Converters.PinYinConverter;

using System.Configuration;

 

namespace  Excel文件导入到数据库

{

    class Program

    {

        static void Main(string[] args)

        {

            

            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(connStr))

            {

                conn.Open();

//创建回滚事务，如果操作错误，则数据库回到操作前的状态

                SqlTransaction trans = conn.BeginTransaction();

                try

                {

    //sql语句

                    string sql = @"insert into T_Customers values(@CustomerName,

                                       @CellPhone,@Landline,@Postcode,@Email,@Address,@BranchId,

                                       @BuyDate,@CarNum,@BracketNum,@Brand,@TypeNum,@Suggestion,

                                       @Remarks,@Pinyin,@PinyinShort)";

                    

                    using (FileStream fsRead = File.OpenRead("客户资料new.xls"))

                    {

    //创建工作簿

                        using (Workbook book = new HSSFWorkbook(fsRead))

                        {

    

                            using (Sheet sheet = book.GetSheetAt(0))

                            {

                                //将Excel文件中每个字段的值与数据库中每个字段的值相对应

                                for (int i = 1; i < sheet.LastRowNum; i++)

                                {

                                    //参数

                                    SqlParameter[] paras ={

                                                 new SqlParameter("@CustomerName",SqlDbType.NVarChar),

                                                 new SqlParameter("@CellPhone",SqlDbType.VarChar),

                                                 new SqlParameter("@Landline",SqlDbType.VarChar),

                                                 new SqlParameter("@CarNum",SqlDbType.VarChar),

                                                 new SqlParameter("@BracketNum",SqlDbType.VarChar),

                                                 new SqlParameter("@BuyDate",SqlDbType.DateTime),

                                                 new SqlParameter("@Postcode",DBNull.Value),

                                                 new SqlParameter("@Email",DBNull.Value),

                                                 new SqlParameter("@Address",DBNull.Value),

                                                 new SqlParameter("@BranchId",DBNull.Value),

                                                 new SqlParameter("@Brand",DBNull.Value),

                                                 new SqlParameter("@TypeNum",DBNull.Value),

                                                 new SqlParameter("@Suggestion",DBNull.Value),

                                                 new SqlParameter("@Remarks",DBNull.Value),

                                                 new SqlParameter("@Pinyin",SqlDbType.VarChar),

                                                 new SqlParameter("@PinyinShort",SqlDbType.VarChar),

 

                                             };

                                    Row row = sheet.GetRow(i);

                                    for (int c = 0; c <= row.LastCellNum; c++)

                                    {

    //如果Excel文件中的单元格的内容为空，那么就将数据库中对应字段的值设置为DBNull.Value

                                        paras[c].Value = row.GetCell(c) == null ? DBNull.Value : (object)row.GetCell(c).ToString();

 

 

 

                                        paras[14].Value = PinyinConvert(row.GetCell(0).ToString());

                                        paras[15].Value = PinyinConverta(row.GetCell(0).ToString());

 

                                    }

                                    //执行ql语句，如果执行成功，则提交事务

                                    SqlHelper.ExecuteNonQuery(sql, paras);

                                    trans.Commit();

                                    Console.WriteLine("成功");

 

                                }

                            }

                        }

                    }

                }

                //如果执行失败，则回滚事务

                catch (Exception e)

                {

                    trans.Rollback();//回滚事务

                    Console.WriteLine("失败"+e.Message.ToString());

                    throw;

                }

            }

        }

                

                      

                 

           

    

        #region 

        private static string  PinyinConvert(string str)

        {

            StringBuilder sb1 = new StringBuilder();

           

            foreach (char item in str)

            {

                if (ChineseChar.IsValidChar(item))

                {

                    ChineseChar cnChar = new ChineseChar(item);

                    if (cnChar.PinyinCount > 0)

                    {

                        sb1.Append(cnChar.Pinyins[0].Substring(0, cnChar.Pinyins[0].Length - 1));

                     

 

                    }

                }

            }

            return sb1.ToString();

        }

        #endregion

        #region 

        private static string  PinyinConverta(string str)

        {

           

            StringBuilder  sb2=new StringBuilder();

            foreach (char item in str)

            {

                if (ChineseChar.IsValidChar(item))

                {

                    ChineseChar cnChar = new ChineseChar(item);

                    if (cnChar.PinyinCount > 0)

                    {

                      

                        sb2.Append(cnChar.Pinyins[0].Substring(0,1));

 

                    }

                }

            }

            return sb2.ToString();

        }

        #endregion

    }

}