none
Excel 匯入 SQL的問題 RRS feed

  • 問題

  • 我是想做一個上傳EXCEL

    然後可以預覽上傳的資料再匯入到SQL裡

    可是日期怎麼用都是錯的

    上傳的時候預覽會從201X/X/X變成一串數字

    然後傳到SQL資料庫日期那欄就會整個不見了

    請各位幫我看看

    程式碼:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Microsoft.Office.Interop;
    using Microsoft.Office.Interop.Excel;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace WebApplication2
    {
        public partial class WebForm5 : System.Web.UI.Page
        {
            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Workbook wb = null;
            Worksheet ws = null;
            Range aRange = null;
            string upload_excel_Dir = @"D:\web\myWeb\";
            protected void Page_Load(object sender, EventArgs e)
            {
               
    
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                string excel_filePath = "";
                try
                {
                    excel_filePath = SaveFileAndReturnPath();//先上傳EXCEL檔案給Server
    
                    if (this.xlApp == null)
                    {
                        this.xlApp = new Microsoft.Office.Interop.Excel.Application();
                    }
                    //打開Server上的Excel檔案
                    this.xlApp.Workbooks.Open(excel_filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    this.wb = xlApp.Workbooks[1];//第一個Workbook
                    this.wb.Save();
    
                    //從第一個Worksheet讀資料
                    SaveOrInsertSheet(excel_filePath, (Worksheet)xlApp.Worksheets[1]);
    
    
    
                    ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入完成", "alert('匯入完成');", true);
    
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    try
                    {
                        //刪除 Windows工作管理員中的Excel.exe 處理緒.
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlApp);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(this.ws);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(this.aRange);
                    }
                    catch { }
                    this.xlApp = null;
                    this.wb = null;
                    this.ws = null;
                    this.aRange = null;
    
    
                    //是否刪除Server上的Excel檔
                   // bool isDeleteFileFromServer = true;
                  //  if (isDeleteFileFromServer)
                  //  {
                   //     System.IO.File.Delete(excel_filePath);
                   // }
    
    
                    GC.Collect();
                }
            }
            private string SaveFileAndReturnPath()
            {
                string return_file_path = "";//上傳的Excel檔在Server上的位置
                if (FileUpload1.FileName != "")
                {
                    return_file_path = System.IO.Path.Combine(this.upload_excel_Dir, Guid.NewGuid().ToString() + ".xls");
    
                    FileUpload1.SaveAs(return_file_path);
                }
                return return_file_path;
            }
    
    
            #region 把Excel資料Insert into Table
            private void SaveOrInsertSheet(string excel_filename, Worksheet ws)
            {
    
                //要開始讀取的起始列(微軟Worksheet是從1開始算)
                int rowIndex = 1;
    
                //取得一列的範圍
                this.aRange = ws.get_Range("A" + rowIndex.ToString(), "I" + rowIndex.ToString());
                System.Data.DataTable dt = new System.Data.DataTable();
    
                dt.Columns.Add("值一");
                          dt.Columns.Add("值二");
                           dt.Columns.Add("值三");
                dt.Columns.Add("值4");
                dt.Columns.Add("值5");
                dt.Columns.Add("值6");
                dt.Columns.Add("值7");
                dt.Columns.Add("值8");
                dt.Columns.Add("值9");
                //判斷Row範圍裡第1格有值的話,迴圈就往下跑
                while (((object[,])this.aRange.Value2)[1, 1] != null)//用this.aRange.Cells[1, 1]來取值的方式似乎會造成無窮迴圈?
                {
    
                    //範圍裡第1格的值
                    string cell1 = ((object[,])this.aRange.Value2)[1, 1] != null ? ((object[,])this.aRange.Value2)[1, 1].ToString() : "";
    
                    //範圍裡第2格的值
                    string cell2 = ((object[,])this.aRange.Value2)[1, 2] != null ? ((object[,])this.aRange.Value2)[1, 2].ToString() : "";
    
                    //範圍裡第3格的值
                    string cell3 = ((object[,])this.aRange.Value2)[1, 3] != null ? ((object[,])this.aRange.Value2)[1, 3].ToString() : "";
                    string cell4 = ((object[,])this.aRange.Value2)[1,4] != null ? ((object[,])this.aRange.Value2)[1, 4].ToString() : "";
                    string cell5 = ((object[,])this.aRange.Value2)[1, 5] != null ? ((object[,])this.aRange.Value2)[1, 5].ToString() : "";
                    string cell6 = ((object[,])this.aRange.Value2)[1, 6] != null ? ((object[,])this.aRange.Value2)[1, 6].ToString() : "";
                    string cell7 = ((object[,])this.aRange.Value2)[1, 7] != null ? ((object[,])this.aRange.Value2)[1, 7].ToString() : "";
                    string cell8 = ((object[,])this.aRange.Value2)[1, 8] != null ? ((object[,])this.aRange.Value2)[1, 8].ToString() : "";
                    string cell9 = ((object[,])this.aRange.Value2)[1, 9] != null ? ((object[,])this.aRange.Value2)[1, 9].ToString() : "";
                    //再對各Cell處理完商業邏輯後,Insert into Table...(略
                    
                    
    
                    System.Data.DataRow dr = dt.NewRow();
            
                    // 第一格資料
                     dr[0] = cell1;
                                   // 第二格資料
                    dr[1] = cell2;
                                    // 第三格資料
                      dr[2] = cell3;
                    dr[3] = cell4;
                    dr[4] = cell5;
                    dr[5] = cell6;
    
                    dr[6] = cell7;
                    dr[7] = cell8;
                    dr[8] = cell9;
    
                    // 加入到表格內
                    dt.Rows.Add(dr);
                    SqlConnection conDataBase = new SqlConnection("Data Source=localhost;Initial Catalog=WebDB;Integrated Security=True");
                    SqlCommand cmd = new SqlCommand("select * from hospital", conDataBase);
                    conDataBase.Open();
                    string con_str = @"Data Source=localhost;Initial Catalog=WebDB;Integrated Security=True";
                    SqlBulkCopy bulk = new SqlBulkCopy(con_str);
                    bulk.DestinationTableName = "hospital";
                    bulk.WriteToServer(dt);
                  
                    conDataBase.Close();
    
                    //往下抓一列Excel範圍
                    rowIndex++;
                    rowIndex++;
                    this.aRange = ws.get_Range("A" + rowIndex.ToString(), "I" + rowIndex.ToString());
                }
                GridView1.DataSource = dt;
                            // 並繫結資料
                 GridView1.DataBind();
                
            }
            #endregion
    
        }
    }
    

    本想想傳圖片上來

    卻出現

    • 在我們完成您的帳戶驗證之前,內文不能包含影像或連結。

    ......

    2016年7月14日 下午 12:28

解答

  • 如果不是字串的話

    日期型別部分

    Excel 使用的是 Variant Date ,實際為 Double (8 bytes)

    .Net 使用的是 Ticks Date ,實際為 Long (8 bytes)

    若是你讀到的是 Double

    可用 FromOADate / ToOADate 轉換

    Ticks Date = DateTime.FromOADate(Variant Date)

    Variant Date = DateTime.ToOADate(Ticks Date)


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    • 已標示為解答 MUSERRR 2016年8月10日 上午 09:25
    2016年7月15日 下午 06:04

所有回覆

  • 我改成這樣:

     private string SaveFileAndReturnPath()
            {
                string return_file_path = "";//上傳的Excel檔在Server上的位置
                if (FileUpload1.FileName != "")
                {
                    return_file_path = System.IO.Path.Combine(this.upload_excel_Dir, Guid.NewGuid().ToString() + ".xls");
    
                    FileUpload1.SaveAs(return_file_path);
                }
                return return_file_path;
            }
           
    
            #region 把Excel資料Insert into Table
            private void SaveOrInsertSheet(string excel_filename, Worksheet ws)
            {
    
    
                //要開始讀取的起始列(微軟Worksheet是從1開始算)
                int rowIndex = 1;
    
                //取得一列的範圍
                this.aRange = ws.get_Range("A" + rowIndex.ToString(), "I" + rowIndex.ToString());
                System.Data.DataTable dt = new System.Data.DataTable();
    
                dt.Columns.Add("值一");
                          dt.Columns.Add("值二");
                           dt.Columns.Add("值三");
                dt.Columns.Add("值4");
                dt.Columns.Add("值5");
                dt.Columns.Add("值6");
                dt.Columns.Add("值7");
                dt.Columns.Add("值8");
                dt.Columns.Add("值9");
                //判斷Row範圍裡第1格有值的話,迴圈就往下跑
                while (((object[,])this.aRange.Value2)[1, 1] != null)//用this.aRange.Cells[1, 1]來取值的方式似乎會造成無窮迴圈?
                {
    
                    //範圍裡第1格的值
                    string cell1 = ((object[,])this.aRange.Value2)[1, 1] != null ? ((object[,])this.aRange.Value2)[1, 1].ToString() : "";
    
                    //範圍裡第2格的值
                    string cell2 = ((object[,])this.aRange.Value2)[1, 2] != null ? ((object[,])this.aRange.Value2)[1, 2].ToString() : "";
    
                    //範圍裡第3格的值
                    string cell3 = ((object[,])this.aRange.Value2)[1, 3] != null ? ((object[,])this.aRange.Value2)[1, 3].ToString() : "";
                    string cell4 = ((object[,])this.aRange.Value2)[1,4] != null ? ((object[,])this.aRange.Value2)[1, 4].ToString() : "";
                    string cell5 = ((object[,])this.aRange.Value2)[1, 5] != null ? ((object[,])this.aRange.Value2)[1, 5].ToString() : "";
                    string cell6 = ((object[,])this.aRange.Value2)[1, 6] != null ? ((object[,])this.aRange.Value2)[1, 6].ToString() : "";
                    string cell7 = ((object[,])this.aRange.Value2)[1, 7] != null ? ((object[,])this.aRange.Value2)[1, 7].ToString() : "";
                    string cell8 = ((object[,])this.aRange.Value2)[1, 8] != null ? ((object[,])this.aRange.Value2)[1, 8].ToString() : "";
                    string cell9 = ((object[,])this.aRange.Value2)[1, 9] != null ? ((object[,])this.aRange.Value2)[1, 9].ToString() : "";
                    //再對各Cell處理完商業邏輯後,Insert into Table...(略
                    
                    
    
                    System.Data.DataRow dr = dt.NewRow();
            
                    // 第一格資料
                     dr[0] = cell1;
                                   // 第二格資料
                    dr[1] = cell2;
                                    // 第三格資料
                      dr[2] = cell3;
                    dr[3] = cell4;
                    dr[4] = cell5;
                    dr[5] = cell6;
    
                    dr[6] = cell7;
                    dr[7] = cell8;
                    dr[8] = cell9;
    
                    // 加入到表格內
                    dt.Rows.Add(dr);
                    string con_str1 = @"Data Source=localhost;Initial Catalog=WebDB;Integrated Security=True";
                    using (var bulk = new SqlBulkCopy(con_str1, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
                    {
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("date", "值一"));
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("time", "值二"));
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("dur", "值三"));
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("activity", "值4"));
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("description", "值5"));
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("dept", "值6"));
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("loc", "值7"));
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("pri stf", "值8"));
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("sts", "值9"));
                        bulk.BatchSize = 25;
                        bulk.DestinationTableName = "hospita4";
                        bulk.WriteToServer(dt);
    
    
    
                    }
    
    
                    //往下抓一列Excel範圍
                    rowIndex++;
                    rowIndex++;
                    this.aRange = ws.get_Range("A" + rowIndex.ToString(), "I" + rowIndex.ToString());
                }
               
                
               
                GridView1.DataSource = dt;
                            // 並繫結資料
                 GridView1.DataBind();
                
            }

    會出現:指定的 ColumnName 'date' 欲任何資料來源的資料行不相符。

    這個錯誤

    是哪裡錯了?還是excel或sql的格式是存錯了

    2016年7月14日 下午 01:31
  • 可以了

    日期那行不是空白的了

    只是日期還是一串數字

    要怎樣才能變成跟excel一樣是正常的數字

    private void SaveOrInsertSheet(string excel_filename, Worksheet ws) { //要開始讀取的起始列(微軟Worksheet是從1開始算) int rowIndex = 1; //取得一列的範圍 this.aRange = ws.get_Range("A" + rowIndex.ToString(), "I" + rowIndex.ToString()); System.Data.DataTable dt = new System.Data.DataTable(); dt.Columns.Add(new DataColumn("值一", typeof(String))); dt.Columns.Add(new DataColumn("值二", typeof(String))); dt.Columns.Add(new DataColumn("值三", typeof(String))); dt.Columns.Add(new DataColumn("值4", typeof(String))); dt.Columns.Add(new DataColumn("值5", typeof(String))); dt.Columns.Add(new DataColumn("值6", typeof(String))); dt.Columns.Add(new DataColumn("值7", typeof(String))); dt.Columns.Add(new DataColumn("值8", typeof(String))); dt.Columns.Add(new DataColumn("值9", typeof(String))); //判斷Row範圍裡第1格有值的話,迴圈就往下跑 while (((object[,])this.aRange.Value2)[1, 1] != null)//用this.aRange.Cells[1, 1]來取值的方式似乎會造成無窮迴圈? { //範圍裡第1格的值 string cell1 = ((object[,])this.aRange.Value2)[1, 1] != null ? ((object[,])this.aRange.Value2)[1, 1].ToString() : ""; //範圍裡第2格的值 string cell2 = ((object[,])this.aRange.Value2)[1, 2] != null ? ((object[,])this.aRange.Value2)[1, 2].ToString() : ""; //範圍裡第3格的值 string cell3 = ((object[,])this.aRange.Value2)[1, 3] != null ? ((object[,])this.aRange.Value2)[1, 3].ToString() : ""; string cell4 = ((object[,])this.aRange.Value2)[1,4] != null ? ((object[,])this.aRange.Value2)[1, 4].ToString() : ""; string cell5 = ((object[,])this.aRange.Value2)[1, 5] != null ? ((object[,])this.aRange.Value2)[1, 5].ToString() : ""; string cell6 = ((object[,])this.aRange.Value2)[1, 6] != null ? ((object[,])this.aRange.Value2)[1, 6].ToString() : ""; string cell7 = ((object[,])this.aRange.Value2)[1, 7] != null ? ((object[,])this.aRange.Value2)[1, 7].ToString() : ""; string cell8 = ((object[,])this.aRange.Value2)[1, 8] != null ? ((object[,])this.aRange.Value2)[1, 8].ToString() : ""; string cell9 = ((object[,])this.aRange.Value2)[1, 9] != null ? ((object[,])this.aRange.Value2)[1, 9].ToString() : ""; //再對各Cell處理完商業邏輯後,Insert into Table...(略 System.Data.DataRow dr = dt.NewRow(); // 第一格資料 dr[0] = cell1; // 第二格資料 dr[1] = cell2; // 第三格資料 dr[2] = cell3; dr[3] = cell4; dr[4] = cell5; dr[5] = cell6; dr[6] = cell7; dr[7] = cell8; dr[8] = cell9; // 加入到表格內 dt.Rows.Add(dr); string con_str1 = @"Data Source=localhost;Initial Catalog=WebDB;Integrated Security=True"; using (var bulk = new SqlBulkCopy(con_str1, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls)) { bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("值一", "date")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("值二", "time")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("值三", "dur")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("值4", "activity")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("值5", "description")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("值6", "dept")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("值7", "loc")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("值8", "pri stf")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("值9", "sts")); bulk.BatchSize = 25; bulk.DestinationTableName = "hospita4"; bulk.WriteToServer(dt); } //往下抓一列Excel範圍 rowIndex++; rowIndex++; this.aRange = ws.get_Range("A" + rowIndex.ToString(), "I" + rowIndex.ToString()); } GridView1.DataSource = dt; // 並繫結資料 GridView1.DataBind(); }

    2016年7月14日 下午 01:56
  • 我已經解決了
    2016年7月15日 上午 04:44
  • 如果不是字串的話

    日期型別部分

    Excel 使用的是 Variant Date ,實際為 Double (8 bytes)

    .Net 使用的是 Ticks Date ,實際為 Long (8 bytes)

    若是你讀到的是 Double

    可用 FromOADate / ToOADate 轉換

    Ticks Date = DateTime.FromOADate(Variant Date)

    Variant Date = DateTime.ToOADate(Ticks Date)


    不精確的問法,就會得到隨便猜的答案;自己都不肯花時間好好描述問題,又何必期望網友會認真回答?

    • 已標示為解答 MUSERRR 2016年8月10日 上午 09:25
    2016年7月15日 下午 06:04