导入Excel到数据库时出现“外部表不是预期格式”的错误信息，针对这个问题开始很纠结，如果导入的Excel是新建的就不会出现该问题，如果是在导出数据的基础上进行导入，将会出现这样的错误提示，也许很多朋友会问这是为什么呢？
因为你的Excel是从程序中导出的并且是使用 Response的方式直接把html代码输出到Excel中的 ，其实这是可以解决的，导出的时候使用Excel操作Api来导出就是标准格式，第二种方法在导入的时候启动Excel.exe进程，这样将会有一个新的Excel.
下面我贴出第二种方法的代码：
View Code
1 #region ====Import Vendor按钮=====
2 [AjaxMethod]
3 public void UploadVendorData_Click(object sender, AjaxEventArgs e)
4 {
5 // 上传的文件保存在服务器端的路径
6 string fullFileName = this.FileUploadFieldSelectVendor.PostedFile.FileName;
7 //验证文件类型
8 if (this.CheckFileType(fullFileName))
9 {
10 string serverPath = Server.MapPath("UpLoad/") + fullFileName.Substring(fullFileName.LastIndexOf("\\") + 1);
11
12 // 保存文件到服务器
13 this.FileUploadFieldSelectVendor.PostedFile.SaveAs(serverPath);
14
15 // 导入到DataSet
16 DataSet myDataSet = new DataSet();
17
18 Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();//创建一个Excel对象(同时启动EXCEL.EXE进程)
19
20 Microsoft.Office.Interop.Excel._Workbook workbook = (Microsoft.Office.Interop.Excel._Workbook)(myExcel.Workbooks.Add(serverPath));//添加新工作簿
21 Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
22
23 //导入数据临时存入DataSet
24 if (this.ImportExcelToDataSet1(serverPath, ref myDataSet,sheets))
25 {
26 #region =========必须有释放Excel=======
27 this.ExcelDispose(myExcel);
28 #endregion
29
30 // 校验数据合法性
31 if (this.CheckVendorDataSet(myDataSet))
32 {
33 //插入临时表
34 this.projectResourceLogic.AddVendorResource(myDataSet.Tables[0], Request.QueryString["ProjectID"].ToString(), this.CurrentUserInfo.UserID.ToString());
35
36 FileUploadFieldSelectVendor.Reset();
37
38 this.BindStoreVenderResourceList(Request.QueryString["ProjectID"].ToString());
39
40 Ext.Msg.Alert("Tip", this.GetMessage("MSG00117")).Show();
41
42 this.WindowUploadVendorData.Hide();
43 }
44 }
45
46 // 从服务器删除文件
47 System.IO.File.Delete(serverPath);
48 }
49 }
50
51
52 #endregion
2. 导入数据临时存入Dataset方法：
View Code
1 #region ====导入数据临时存入Dataset(在导出的模板中修改再导入)======
2 /// <summary>
3 /// 获得Excel文件放入dataset
4 /// </summary>
5 /// <param name="filepath"></param>
6 /// <returns></returns>
7 private bool ImportExcelToDataSet1(string filepath,ref DataSet myDataSet, Microsoft.Office.Interop.Excel.Sheets sheets)
8 {
9 bool success = true;
10 try
11 {
12 for (int i = 0; i < sheets.Count; i++)
13 {
14 Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
15 DataTable dt = new DataTable();
16 string cellContent;
17 int iRowCount = worksheet.UsedRange.Rows.Count;
18 int iColCount = worksheet.UsedRange.Columns.Count;
19 Microsoft.Office.Interop.Excel.Range range;
20 for (int iRow = 1; iRow <= iRowCount; iRow++)
21 {
22 DataRow dr = dt.NewRow();
23
24 for (int iCol = 1; iCol <= iColCount; iCol++)
25 {
26 range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
27
28 cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
29
30 if (iRow == 1)
31 {
32 dt.Columns.Add(cellContent);
33 }
34 else
35 {
36 dr[iCol - 1] = cellContent;
37 }
38 }
39
40 if (iRow != 1)
41 dt.Rows.Add(dr);
42 }
43 myDataSet.Tables.Add(dt);
44 }
45 }
46 catch (Exception ex)
47 {
48 Ext.MessageBox.Alert("Error", "The excel format is wrong.please download from the not filled timesheet page first.\n" + ex.Message).Show();
49 success = false;
50 }
51
52 return success;
53 }
54 #endregion
3. 释放Excel
View Code
1 #region ==========释放Excel===========
2 /// <summary>
3 /// 关闭Excel进程
4 /// </summary>
5 /// <param name="CurExcel"></param>
6 protected void ExcelDispose(Microsoft.Office.Interop.Excels.Application CurExcel)
7 {
8 try
9 {
10 if (CurExcel != null)
11 {
12 CurExcel.Workbooks.Close();
13 CurExcel.Quit();
14 System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
15 CurExcel = null;
16 //销毁Excel对象
17 GC.Collect();
18
19 }
20 }
21 catch (Exception ex)
22 {
23 Ext.Msg.Alert("Excel disponse error：", ex.ToString()).Show();
24 }
25 }
26 #endregion