Excel导入问题 外部表不是预期的格式。

           string XLS_Path = "~/Admin/upFile/";

            string XLS_Name = "裸钻导入_" + DateTime.Now.ToString("yyyyMMddHHmmsssss") + ".xls";

            string XLS_FullName = string.Empty;

            string fileExtName = "";

            if (fup2.PostedFile.ContentLength > 0)

            {

                try

                {

                    fileExtName = fup2.PostedFile.FileName.Substring(fup2.PostedFile.FileName.LastIndexOf("."));

                    if (fileExtName != ".xls" || fileExtName.Length < 1)

                    {

                        lbMsg0.Text = "无效的文件，。";

                        return;

                    }

                    XLS_FullName = Server.MapPath(XLS_Path + XLS_Name);

                    fup2.PostedFile.SaveAs(XLS_FullName);

                }

                catch (Exception ex)

                {

                    lbMsg0.Text = ex.ToString();

                }

            }

            else

            {

                lbMsg0.Text = "请选择文件后再上传！！！";

                return;

            }

            var msg = "";

            var begin = DateTime.Now;





            try

            {

                string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + XLS_FullName + "'" + ";Extended Properties=Excel 8.0;Persist Security Info=False ";

                OleDbConnection cnnxls = new OleDbConnection(mystring);

                cnnxls.Open();





                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　

                DataTable dtSheetName = cnnxls.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                //包含excel中表名的字符串数组

                string[] strTableNames = new string[dtSheetName.Rows.Count];

                for (int k = 0; k < dtSheetName.Rows.Count; k++)

                {

                    strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();

                }

                OleDbDataAdapter myCommand = null;

                DataTable dt = new DataTable();

                //从指定的表明查询数据,可先把所有表明列出来供用户选择

                string strExcel = "select*from[" + strTableNames[0] + "]";

                myCommand = new OleDbDataAdapter(strExcel, cnnxls);

                dt = new DataTable();

                myCommand.Fill(dt);

                string AdminCName ="aa"

                var str = bll.BatchDiamond1(dt, AdminCName, pnum);

   public bool ExportExcel(DataTable myDataTable, string saveFileName)

        {

            try

            {

                if (saveFileName.IndexOf(":") < 0) return false; //被点了取消



                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

                object missing = System.Reflection.Missing.Value;



                if (xlApp == null)

                {

                    MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");

                    return false;

                }

                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];            //取得sheet1

                Microsoft.Office.Interop.Excel.Range range;

                range = worksheet.Columns;

                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;              //水平对齐设置 

                range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;                //垂直对齐设置

                range.ColumnWidth = 15;

                #region 写入字段

                worksheet.Cells[1, 1] = "钻石号";

             //.....

              worksheet.Cells[1, 21] = "类型外键";

                #endregion

                //写入数值

                for (int r = 0; r < myDataTable.Rows.Count; r++)

                {

                    

                   worksheet.Cells[r + 2, 1] = myDataTable.Rows[r]["DiamNumber"];  

                  //.........                                

                     worksheet.Cells[r + 2, 21] = myDataTable.Rows[r]["PcID"];        

                     

                    }

                worksheet.SaveAs(saveFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);

                workbook.Close(missing, missing, missing);

                xlApp.Quit();

                GC.Collect();

                GC.WaitForPendingFinalizers();

                return true;

            }

            catch (Exception ex)

            {

                return false;

            }

        }