在ssis package中处理excel时遇到data missing

Problem:  when using Excel Source task to load excel data into database, if the data type in one column is different, it will appears Null into our database.

                             

 

Solution: 

View Code

using Microsoft.Office.Interop.Excel to process excel file

 

 

public System.Data.DataTable ReadExcel()

        {

            System.Data.DataTable dt = new System.Data.DataTable("tmp");

            Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();

            app.Visible = false;

            Workbook workbook = app.Workbooks.Open("aa.xls", Missing.Value, Missing.Value,

                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

                Missing.Value, Missing.Value);

 

            var sheet = workbook.Sheets[1] as Worksheet;

            try

            {          

                var c = new DataColumn("a", typeof(int));

                c.AutoIncrement = true;

                dt.Columns.Add(c);

 

                for (int i = 1; i <= sheet.UsedRange.Rows.Count; i++)

                {

 

                    DataRow dr = dt.NewRow();

 

                    for (int j = 1; j <= sheet.UsedRange.Columns.Count; j++)

                    {

 

                        Range range = sheet.Rows[i, Missing.Value] as Range;

                        if (i == 1)

                        {

 

                            var header = (range.Cells[Missing.Value, j] as Range).Text.ToString();

                            var type = string.Empty;

                            if (j == 1 || j == 66)

                            {

 

                                dt.Columns.Add(header, typeof(DateTime));

                            }

                            else if (j == 65)

                            {

                                dt.Columns.Add(header, typeof(int));

                            }

                            else

                            {

                                dt.Columns.Add(header, typeof(string));

 

                            }

                        }

                        else

                        {

                            var v = (range.Cells[Missing.Value, j] as Range).Text.ToString();

                            if (j == 1 || j == 66)

                            {

                                if (string.IsNullOrEmpty(v))

                                {

                                    dr[j] = DBNull.Value;

                                }

                                else

                                {

                                    dr[j] = DateTime.Parse(v);

                                }

                            }

                            else if (j == 65)

                            {

 

                                if (string.IsNullOrEmpty(v))

                                {

                                    dr[j] = DBNull.Value;

                                }

                                else

                                {

                                    dr[j] = int.Parse(v);

                                }

                            }

 

                            else

                            {

                                dr[j] = v;

                            }

                        }

                    }

 

                    if (i > 1)

                    {

                        dt.Rows.Add(dr);

                    }

                }

 

            }

            catch (Exception ex)

            {

                throw ex;

            }

            finally

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);

                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);   

            }

            return dt;

        }

 

        public void InsertIntoTable(System.Data.DataTable dt)

        {

            try

            {

                string strCon = "Integrated Security = ;Data Source=;Initial Catalog=;";

                SqlConnection connection = new SqlConnection(strCon);

                connection.Open();

                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection);

                sqlBulkCopy.DestinationTableName = "";

                sqlBulkCopy.WriteToServer(dt);

            }

            catch (Exception ex)

            {

                throw ex;

            }  

 

        }

    }

 

转载于:https://www.cnblogs.com/SunnySun03/archive/2013/01/28/2880380.html