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

　　导入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

转载于:https://www.cnblogs.com/mystar/archive/2011/02/24/1964030.html