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:
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