导入Excel至数据库中 "外部表不是预期格式"错误信息

　　导入Excel到数据库时出现“外部表不是预期格式”的错误信息，针对这个问题开始很纠结，如果导入的Excel是新建的就不会出现该问题，如果是在导出数据的基础上进行导入，将会出现这样的错误提示，也许很多朋友会问这是为什么呢？

　　因为你的Excel是从程序中导出的并且是使用 Response的方式直接把html代码输出到Excel中的 ，其实这是可以解决的，导出的时候使用Excel操作Api来导出就是标准格式，第二种方法在导入的时候启动Excel.exe进程，这样将会有一个新的Excel.

　　下面我贴出第二种方法的代码：

 #region ====Import Vendor按钮=====
        [AjaxMethod]
        public void UploadVendorData_Click(object sender, AjaxEventArgs e)
        {
            // 上传的文件保存在服务器端的路径
           string fullFileName = this.FileUploadFieldSelectVendor.PostedFile.FileName;
            //验证文件类型
             if (this.CheckFileType(fullFileName))
            {
                string serverPath = Server.MapPath("UpLoad/") + fullFileName.Substring(fullFileName.LastIndexOf("\\") + 1);

                // 保存文件到服务器
                 this.FileUploadFieldSelectVendor.PostedFile.SaveAs(serverPath);

                // 导入到DataSet
                 DataSet myDataSet = new DataSet();

                Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();//创建一个Excel对象(同时启动EXCEL.EXE进程) 

                Microsoft.Office.Interop.Excel._Workbook workbook = (Microsoft.Office.Interop.Excel._Workbook)(myExcel.Workbooks.Add(serverPath));//添加新工作簿
                Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;

                //导入数据临时存入DataSet
                if (this.ImportExcelToDataSet1(serverPath, ref myDataSet,sheets))
                {
                    #region =========必须有释放Excel=======
                    this.ExcelDispose(myExcel);
                    #endregion

                    // 校验数据合法性
                    if (this.CheckVendorDataSet(myDataSet))
                    {
                        //插入临时表
                        this.projectResourceLogic.AddVendorResource(myDataSet.Tables[0], Request.QueryString["ProjectID"].ToString(), this.CurrentUserInfo.UserID.ToString());

                        FileUploadFieldSelectVendor.Reset();

                        this.BindStoreVenderResourceList(Request.QueryString["ProjectID"].ToString());

                        Ext.Msg.Alert("Tip", this.GetMessage("MSG00117")).Show();

                        this.WindowUploadVendorData.Hide();
                    }
                }

                // 从服务器删除文件
                System.IO.File.Delete(serverPath);
            }
        }

        
        #endregion

2.  导入数据临时存入Dataset方法：

  #region ====导入数据临时存入Dataset(在导出的模板中修改再导入)======
        /// <summary>
        /// 获得Excel文件放入dataset
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        private bool ImportExcelToDataSet1(string filepath,ref DataSet myDataSet, Microsoft.Office.Interop.Excel.Sheets sheets)
        {
            bool success = true;
            try
            {
                for (int i = 0; i < sheets.Count; i++)
                {
                    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
                    DataTable dt = new DataTable();
                    string cellContent;
                    int iRowCount = worksheet.UsedRange.Rows.Count;
                    int iColCount = worksheet.UsedRange.Columns.Count;
                    Microsoft.Office.Interop.Excel.Range range;
                    for (int iRow = 1; iRow <= iRowCount; iRow++)
                    {
                        DataRow dr = dt.NewRow();

                        for (int iCol = 1; iCol <= iColCount; iCol++)
                        {
                            range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];

                            cellContent = (range.Value2 == null) ? "" : range.Text.ToString();

                            if (iRow == 1)
                            {
                                dt.Columns.Add(cellContent);
                            }
                            else
                            {
                                dr[iCol - 1] = cellContent;
                            }
                        }

                        if (iRow != 1)
                            dt.Rows.Add(dr);
                    }
                    myDataSet.Tables.Add(dt);
                }
            }
            catch (Exception ex)
            {
                Ext.MessageBox.Alert("Error", "The excel format is wrong.please download from the not filled timesheet page first.\n" + ex.Message).Show();
                success = false;
            }

            return success;
        }
        #endregion

3. 释放Excel

 #region ==========释放Excel===========
        /// <summary>
        /// 关闭Excel进程
        /// </summary>
        /// <param name="CurExcel"></param>
        protected void ExcelDispose(Microsoft.Office.Interop.Excels.Application CurExcel)
        {
            try
            {
                if (CurExcel != null)
                {
                    CurExcel.Workbooks.Close();
                    CurExcel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
                    CurExcel = null;
                    //销毁Excel对象 
                    GC.Collect();
                                   
                }
            }
            catch (Exception ex)
            {
                Ext.Msg.Alert("Excel disponse error：", ex.ToString()).Show();
            }
        }
        #endregion