近来需要做个将Excel数据导入到SQLServer表中的小程序,查找了一下,解决方法如下:
一、自己编程手动读取Excel文件内容(可采用OLE操作),然后使用SQL语句插入SQLServer目标表,这个方法适应于少量数据,原因你懂的。
二、执行SQL语句插入SQLServer目标表,这个方法效率当然是比较快,原因你懂的。
如下:
print?insert into DestTable(field1,field2) select field1,field2 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=D:\1.xls',sheet1$)
大伙把上句放到SQL查询分析器中基本都能正确执行,但是真正放到程序执行时,大多会出现这个郁闷的错误提示:
OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。
OLE DB 错误跟踪[OLE/DB Provider 'MICROSOFT.JET.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: 提供程序未给出有关错误的任何信息。]。
明明都是同一个SQL语句呀!Google一下,再Google一下,没有答案。好吧,自己分析吧。
下面是我封装一个获取Excel字段的class
class OperatorExcel
{
Variant s_vExcelApp;
Variant s_vWorkBook;
Variant s_vSheet;
bool s_Init;
bool s_Loaded;
UINT s_RowCount;
UINT s_ColCount;
public:
OperatorExcel():s_vExcelApp(Unassigned),
s_vWorkBook(Unassigned),s_vSheet(Unassigned),
s_Init(false),s_Loaded(false),s_RowCount(0),s_ColCount(0)
{
try
{
s_vExcelApp=Variant::CreateObject("Excel.Application");
}catch(...)
{
Application->MessageBoxA("创建Excel对象出错,可能本机未正确安装Excel。","提示",MB_ICONERROR);
s_Init=false;
return ;
}
s_vExcelApp.OlePropertySet("Visible",false);
s_Init=True;
}
~OperatorExcel()
{
try
{
if(s_Init)
{
if(s_Loaded)
s_vWorkBook.OleProcedure("Close");
s_vExcelApp.OleFunction("Quit");
s_vExcelApp=Unassigned;
s_vWorkBook=Unassigned;
s_vSheet=Unassigned;
s_Init=False;//销毁后置为未初始化
}
}catch(...){}
}
void Load(String file)
{
if(s_Init)
{
s_vExcelApp.OlePropertyGet("WorkBooks").OleProcedure("Open",file.c_str());
s_vWorkBook=s_vExcelApp.OlePropertyGet("ActiveWorkBook");
s_vSheet=s_vWorkBook.OlePropertyGet("ActiveSheet");
s_RowCount=(UINT)s_vSheet.OlePropertyGet("UsedRange").OlePropertyGet("Rows").OlePropertyGet("Count");
s_ColCount=(UINT)s_vSheet.OlePropertyGet("UsedRange").OlePropertyGet("Columns").OlePropertyGet("Count");
s_Loaded=True;
}
}
void GetFieldsName(TStrings *fields)
{
if(fields)
{
for(UINT i=1;i<=s_ColCount;++i)
{
String s=s_vSheet.OlePropertyGet("Cells",1,i).OlePropertyGet("Value");
fields->Add(s);
}
}
}
};
瞧了半天,摁是没瞧出什么不对劲,终于在一次未正确释放资源的调试中发现,居然在SQL查询分析器中执行的语句也报上述错误,哎,结果终于一致了,所以当下解决办法就是:打开任务管理器,把未关闭的Excel统统kill,或者在cmd中执行 taskkill /f /im excel.exe 。
然后修改OperatorExcel
class OperatorExcel
{
Variant s_vExcelApp;
Variant s_vWorkBook;
Variant s_vSheet;
bool s_Init;
bool s_Loaded;
bool s_Closed;
UINT s_RowCount;
UINT s_ColCount;
public:
OperatorExcel():s_vExcelApp(Unassigned),
s_vWorkBook(Unassigned),s_vSheet(Unassigned),
s_Init(false),s_Loaded(false),s_Closed(false),
s_RowCount(0),s_ColCount(0)
{
}
~OperatorExcel()
{
try
{
if(!s_Closed)
Destroy();
}
catch(...)
{}
}
void Create()
{
try
{
s_vExcelApp=Variant::CreateObject("Excel.Application");
}catch(...)
{
Application->MessageBoxA("创建Excel对象出错,可能本机未正确安装Excel。","提示",MB_ICONERROR);
s_Init=false;
return ;
}
s_vExcelApp.OlePropertySet("Visible",false);
s_Closed=false;
s_Init=True;
}
void Destroy()
{
if(s_Init)
{
if(s_Loaded)
s_vWorkBook.OleProcedure("Close");
s_vExcelApp.OleFunction("Quit");
s_vExcelApp=Unassigned;
s_vWorkBook=Unassigned;
s_vSheet=Unassigned;
s_Closed=True;//销毁后置为已关闭
s_Init=False;//销毁后置为未初始化
}
}
void Load(String file)
{
if(s_Init)
{
s_vExcelApp.OlePropertyGet("WorkBooks").OleProcedure("Open",file.c_str());
s_vWorkBook=s_vExcelApp.OlePropertyGet("ActiveWorkBook");
s_vSheet=s_vWorkBook.OlePropertyGet("ActiveSheet");
s_RowCount=(UINT)s_vSheet.OlePropertyGet("UsedRange").OlePropertyGet("Rows").OlePropertyGet("Count");
s_ColCount=(UINT)s_vSheet.OlePropertyGet("UsedRange").OlePropertyGet("Columns").OlePropertyGet("Count");
s_Loaded=True;
}
}
void GetFieldsName(TStrings *fields)
{
if(fields)
{
for(UINT i=1;i<=s_ColCount;++i)
{
String s=s_vSheet.OlePropertyGet("Cells",1,i).OlePropertyGet("Value");
fields->Add(s);
}
}
}
};