jsp:
<input type="file" id="file" name="file" style="width: 450px;" >
点击“Import”进入process方法：（Action开始 ）
将文件上传到服务器：
DynaActionForm tariffForm = (DynaActionForm) form;
FormFile tempFile = (FormFile) tariffForm.get("file");
//String myFile = (String)tariffForm.get("myfile");
log.info("excel fileName : " + tempFile.getFileName());
//将tempFile path保存到cookies中去
String uploadTariffFile = tempFile.getFileName();
Cookie uploadFilePath = new Cookie("uploadFilePath", uploadTariffFile);
response.addCookie(uploadFilePath);
String actionto = "import";
if(!XslUtil.isXslType(tempFile.getFileName())){
request.setAttribute(MyConstants.ERROR_KEY, "The file must be xsl format.eg .(xsl)");
return "importFail";
}
//检XslFile
if(!checkXslFile(request, tempFile)){
request.setAttribute(MyConstants.ERROR_KEY, "The file must be xsl format.eg .(xsl)");
return "importFail";
}
List list = new ArrayList();
File cFile =null;
String xslPath = null;
try {
//1.将excel文件上传到tariff中去先
Configuration config = (Configuration) request.getSession().getServletContext().getAttribute(MyConstants.CONFIGURATION_KEY);
xslPath = config.getString("ImportHotelPath");
log.info("Excel file: " + xslPath);
cFile = new File(xslPath,tempFile.getFileName());
if(cFile.exists()){
cFile.delete();
}
this.writeFile(tempFile, cFile);
} catch (Exception e) {
log.error("upload excel file error:" + e.getMessage());
request.setAttribute(MyConstants.ERROR_KEY, "Upload excel file error! ");
return "importFail";
}
辅助方法：
public static boolean isXslType(String fileName){
Pattern p = Pattern.compile(REG_XSL_TYPE,Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(fileName);
return m.find();
}
private boolean checkXslFile(HttpServletRequest request,FormFile tempFile) throws SystemException {
if (tempFile == null) {
log.error(" error :file is null.");
request.setAttribute(MyConstants.ERROR_KEY, "The file isn't exist.");
return false;
}
if (tempFile.getFileSize() > 1024 * 1024 * 45) {
log.error(" error :file lenght is error.");
request.setAttribute(MyConstants.ERROR_KEY, "The size of file cann't more than 45M.");
return false;
}
return true;
}
public void writeFile(FormFile formFile, File file) throws Exception{
byte [] fileDate=formFile.getFileData();
FileOutputStream fileOutStream = null;
try {
fileOutStream = new FileOutputStream(file);
fileOutStream.write(fileDate);
fileOutStream.flush();
} finally {
if(fileOutStream != null) {
fileOutStream.close();
fileOutStream = null;
}
}
}
调用存储过程ImportHotel：
boolean result = tariffImportFacade.importHotel(cFile.getPath());
@Transactional(propagation=Propagation.REQUIRED)
public boolean importHotel(final String path) throws SystemException {
boolean ff = (Boolean) this.baseDao.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
Connection conn = session.connection();
java.sql.CallableStatement cs = conn.prepareCall("{call FareAdmin.ImportHotel(?)}",
java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
cs.setString(1, path);
boolean bb = cs.execute();
if(!bb){
throw new SQLException("call FareAdmin.ImportHotel(?) error occured.");
}
return bb;
}
});
if(!ff){
throw new SystemException("call FareAdmin.ImportHotel(?) error occured.");
}
log.info(" read ok.");
return true;
}
从临时表中查出数据并验证：
String hql = "select * from FareAdmin.temp_hotel";
list = tariffImportFacade.findListByHQL(hql);
String s = this.checktariff(list, request);
if(!s.equalsIgnoreCase("")) {
request.setAttribute(MyConstants.ERROR_KEY, s);
return "importFail";
}
辅助方法：
@Transactional(propagation=Propagation.REQUIRED)
public List findListByHQL(final String hql) throws SystemException {
List list = new ArrayList();
list = (List) this.baseDao.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
SQLQuery sql = session.createSQLQuery(hql);
List list = sql.list();
return list;
}
});
return list;
}
保存数据到数据库部分：
String error = "";
try {
error = this.saveTariffListToDB(list, request);
} catch (Exception e) {
log.error(e.getMessage());
e.printStackTrace();
request.setAttribute(MyConstants.ERROR_KEY, "Can't save data to databases!");
return "importFail";
}
if(!"".equals(error)) {
request.setAttribute(MyConstants.ERROR_KEY, error);
} else {
request.setAttribute(MyConstants.MESSAGE_KEY, "Import tariff data success!");
}
return "import";
Action到此结束！
private String saveTariffListToDB(List list,HttpServletRequest request) throws Exception{
HttpSession session = request.getSession();
Agentuser user = (Agentuser)session.getAttribute(MyConstants.CLIENTUSER_KEY);
List<Tariff> tlist = new ArrayList<Tariff>();
List<com.techson.himsnanhwa.admin.hibernate.hbm.Tariff> updatelist = (List<com.techson.himsnanhwa.admin.hibernate.hbm.Tariff>)request.getAttribute("tariffUpdateList");
if(list.size()<=4){
return "The excel can't find the record!";
}else{
for(int i = 4;i < list.size();i ++){
log.info("conver to tariff no = " + (i-3));
Object[] objTemp = (Object[]) list.get(i);
for (int j = 0; j < objTemp.length; j++) {
if(null==objTemp[j]){
objTemp[j] = "";
}
}
Tariff tariff = new Tariff();
tariff.setUpdateby(user.getLoginname());
...
tlist.add(tariff); //将临时表中查出的数据保存到tariff表中（将临时表中属性F1、F2对应到数据库hotel-ng-egl-temp的表tariff中）
}
try {
String name = user.getLoginname();
String message = tariffImportFacade.saveList(tlist,name);
return message;
} catch (Exception e) {
log.error(e.getMessage());
e.printStackTrace();
return "Can't save data to databases!";
}
}
}
@Transactional(propagation=Propagation.REQUIRED)
public String saveList(List<Tariff> entity,final String loginName) throws SystemException{
int index = 0;
boolean isDelete = (Boolean) this.baseDao.getHibernateTemplate()
.execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Boolean ret = true;
try {
SQLQuery query1 = session.createSQLQuery("delete from FareAdmin.cxlpolicy");
SQLQuery query2 = session.createSQLQuery("delete from FareAdmin.focpolicy");
SQLQuery query3 = session.createSQLQuery("delete from FareAdmin.blackout");
SQLQuery query4 = session.createSQLQuery("delete from FareAdmin.tariff");
query1.executeUpdate();
query2.executeUpdate();
query3.executeUpdate();
query4.executeUpdate();
} catch (Exception e) {
log.error(e);
ret = false;
}
return ret;
}
});
try {
for(int i = 0; i < entity.size();i ++){
index = i;
Tariff tariff = (Tariff)entity.get(i);
System.out.println("Insert record "+String.valueOf(i)+" Tariff "+String.valueOf(tariff.getHotelid())+" "+String.valueOf(tariff.getRoomcategoryid()));
System.out.println(tariff.getValidfrom());
this.getBaseDao().insert(tariff);
Iterator<Focpolicy> it = tariff.getFocpolicies().iterator();
while(it.hasNext()){
Focpolicy focpolicy = it.next();
if(null != focpolicy){
focpolicy.setTariff(tariff);
this.getBaseDao().getHibernateTemplate().save(focpolicy);
}
}
Iterator<Cxlpolicy> itc = tariff.getCxlpolicies().iterator();
while(itc.hasNext()){
Cxlpolicy cxlpolicy = itc.next();
if(null != cxlpolicy){
cxlpolicy.setTariff(tariff);
this.getBaseDao().getHibernateTemplate().save(cxlpolicy);
}
}
Iterator<Blackout> bkSet = tariff.getBlackout().iterator();
while(bkSet.hasNext()) {
Blackout bk = bkSet.next();
if(null != bk) {
bk.setTariff(tariff);
this.getBaseDao().getHibernateTemplate().save(bk);
}
}
if(i % 2000 == 0){
this.getBaseDao().getHibernateTemplate().flush();
this.getBaseDao().getHibernateTemplate().clear();
}
}
} catch (RuntimeException e) {
this.errorMessage = "Excel duplicate row in " + (index);
throw new SystemException("Excel duplicate row in " + (index), "Duplicate key");
}
String message = (String)this.baseDao.getHibernateTemplate().execute(
new HibernateCallback(){
public Object doInHibernate(Session session) throws HibernateException, SQLException{
Connection conn = session.connection();
java.sql.CallableStatement cs = conn.prepareCall("{Call FareAdmin.ImportAndUpdateTariff(?)}",
java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
cs.setString(1, loginName);
cs.execute();
String message = "";
ResultSet rs = cs.getResultSet();
if(rs != null) {
while(rs.next()) {
String msg = rs.getString(1);
int linenumber = rs.getInt(2);
message += "<br/> Row : " + linenumber + " " + msg + "";
}
}
return message;
}
});
log.info(" save ok.");
return message;
}
导出Excel，将Excel中数据导入到表中：
导入时，先将文件上传到服务器，再调用hotel-ng-egl-temp数据库中存储过程ImportHotel ,存储过程创建临时表temp-hotel(属性为F1、F2...),并将数据导入到表temp-hotel中 ，然后程序从temp-hotel表中查出数据，将其中数据导入自定义的 hotel-ng-egl-temp数据库中 表tariff ，再调用存储过程ImportAndUpdateTariff，从tariff中查出数据导入数据库hotel-ng-egl的表Tariff 中。
存储过程：
ImportHotel：(创建一个临时表，并将Excel中数据赋给它)
http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure
/****** Object: Stored Procedure [ImportHotel] Script Date: 2011/3/30 15:58:48 ******/
USE [hotel_nh_egl_temp];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [ImportHotel]
(@xlsFile varchar(255))
//该存储过程参数
WITH
EXECUTE AS CALLER
AS
SELECT 'SUCCESS' AS boolsuccess
begin
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
declare @sql varchar(5000)
declare @errMessage varchar(5000)
if object_id('FareAdmin.temp_hotel') is not null
DROP TABLE FareAdmin.temp_hotel
print(@xlsFile)
set @sql = 'SELECT * INTO FareAdmin.temp_hotel FROM openrowset(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;HDR=NO;IMEX=1; Database='+@xlsFile+ ''', ''select * from [Sheet1$]'')'
exec(@sql)
end
GO
知识点：
1、object_id('objectname');
返回数据库对象标识号。
参数表示要使用的对象，返回类型为int，表示该对象在系统中的编号。
if object_id('FareAdmin.temp_hotel') is not null
DROP TABLE FareAdmin.temp_hotel
2、
你可能常常会需要运行一个ad hoc查询从远程OLE DB数据源提取数据，或者批量向SQL Server表导入数据 。在这种情况下，你可以在T-SQL（Transact-SQL，微软对SQL的扩展）中用OPENROWSET函数给数据源传入一个连接串和查询来提取需要的数据。
你可以使用OPENROWSET函数从任何支持注册OLE DB的数据源获取数据，比如从SQL Server或Access的远程实例中提取数据。如果你用OPENROWSET从SQL Server实例中获取数据，该实例必须配置为允许ad hoc分布式查询 。
要配置远程SQL Server实例支持ad hoc查询，需要使用系统存储过程sp_configure先设置advanced options，再启用Ad Hoc Distributed Queries（ad hoc分布式查询）。请看下面的T-SQL脚本：
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE;
GO
要注意的是，在运行完存储过程之后，你必须运行“RECONFIGURE”命令。
一旦你配置好了远程SQL Server实例，你就可以对它使用OPENROWSET函数。这个函数可以在SELECT语句的FROM从句里使用。下面的例子显示了该函数的基本语法：
OPENROWSET('provider', 'connection string', target)
Provider —— 某特定数据源支持的OLE DB提供者的人机友好名称（ProgID）。Provider的名字必须用单引号括起来。
Connection string —— 连接串。它是与具体提供者provider相关的字符串，包括连接到给字符串中指定的数据源所需要的细节信息。根据provider的不同，连接串信息需要用一对或多对单引号括起来。
Target —— target参数可以使一个数据库对象或者一个查询。
set @sql = 'SELECT * INTO FareAdmin.temp_hotel FROM openrowset(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;HDR=NO;IMEX=1; Database='+@xlsFile+ ''', ''select * from [Sheet1$]'')'
exec(@sql)
3、select * into target_table from source_table;
//此句要求目标表不存在，在插入时候自动创建
//并将source_table表中所有数据复制到target_table表中(两表结构一样)
4、WITH EXECUTE AS CALLER
Mary 创建了一个引用某个表的存储过程，她不拥有该表，但具有对该表的 SELECT 权限。她在 CREATE PROCEDURE 语句中指定了 EXECUTE AS CALLER，如下例中所示：
CREATE PROCEDURE AccessTable
WITH EXECUTE AS CALLER
AS SELECT * FROM dbo.SomeTable;
然后，Mary 将授予 Scott 对存储过程的 EXECUTE 权限。
当 Scott 执行存储过程时，数据库引擎将验证他（调用方）是否具有执行该存储过程的权限。Scott 拥有 EXECUTE 权限，但是由于 Mary 不是被引用表的所有者，因此数据库引擎将检查 Scott 是否对该表具有权限。如果 Scott 没有权限，则该存储过程语句将失败。
ImportAndUpdateTariff：
/****** Object: Stored Procedure [ImportAndUpdateTariff] Script Date: 2011/3/30 15:59:32 ******/
USE [hotel_nh_egl_temp];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author: KO
-- Create date: 17NOV2010
-- Description: Import tariff and update tariff from temp table
-- =============================================
CREATE PROCEDURE [ImportAndUpdateTariff]
(@updatedby nvarchar(50))
WITH
EXECUTE AS CALLER
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- DECLARE @TranName char(2)
-- SET @TranName = 'T1'
-- PRINT @TranName
DECLARE @itfilterfetch int
DECLARE @vittariffid numeric(18, 0),
@vithotelid numeric(18, 0),
@vitclienttype int,
@vitratetype int,
@vitroomcategoryid numeric(18, 0),
@vitrefhotelid numeric(18, 0),
@vitvalidfrom datetime,
@vitvalidto datetime,
@vitdayofweek varchar(13),
@vitmealcode char(2),
@vitsuppliercode varchar(200),
@vitapplnationality varchar(600),
@vtariffid numeric(18,0)
DECLARE --@vhotelid,
--@vsuppliercode,
@vbookingcode varchar(200),
--@vclienttype,
--@vratetype,
--@vroomcategoryid,
--@vrefhotelid,
--@vvalidfrom,
--@vvalidto,
@vsgl decimal(18,2),
@vtwn decimal(18,2),
@vdbl decimal(18,2),
@vtrp decimal(18,2),
@vqud decimal(18,2),
@vxba decimal(18,2),
@vxbc decimal(18,2),
@vtsu decimal(18,2),
@vdsu decimal(18,2),
@vsdbl decimal(18,2),
@vetwn decimal(18,2),
--@vmealcode,
@vmeal decimal(18,2),
@vbonusmeal decimal(18,2),
@vaaltermealcode char(2),
@vaaltermeal decimal(18,2),
@vbaltermealcode char(2),
@vbaltermeal decimal(18,2),
@vcostcurr char(3),
--@v[dayofweek],
@vminstay int,
@vmaxstay int,
--@vapplnationality,
@vnonapplnationality varchar(600),
@vbonusstay varchar(50),
@vmaxbonusstay decimal(18,1),
@vinclmealonbonusstay bit,
@vchargemealonbonusstay bit,
@vallowextensionnight bit,
@vallow2ndnightrate bit,
--allowprestayrate
--specialnight
--maxextnight
@vratedesc_en varchar(4000),
@vratedesc_tw nvarchar(4000),
@vratedesc_cn nvarchar(4000),
@vcplycharge decimal(18,2),
@vcplyitem_en varchar(200),
@vcplyitem_tw nvarchar(200),
@vcplyitem_cn nvarchar(200),
@vsuppcharge decimal(18,2),
@vsupphalfboard decimal(18,2),
@vsuppfullboard decimal(18,2),
@vsuppmisc decimal(18,2),
@vsuppitem_en varchar(200),
@vsuppitem_tw nvarchar(200),
@vsuppitem_cn nvarchar(200),
-- hasfocpolicy
-- hascxlpolicy
@vcxldesc_en varchar(4000),
@vcxldesc_tw nvarchar(4000),
@vcxldesc_cn nvarchar(4000),
@vremarks nvarchar(4000),
@vinternalremarks nvarchar(4000),
@vagentpermit varchar(4000),
@vrsvntel nvarchar(50),
@vrsvnfax nvarchar(50),
@vrsvnemail nvarchar(50),
@vhasfocpolicy bit,
@vhascxlpolicy bit,
@vadditionalitemtype1 varchar(4),
@vadditionalitemtype2 varchar(4),
@vadditionalitemtype3 varchar(4),
@vadditionalitemtype4 varchar(4),
@vadditionalitemtype5 varchar(4),
@vadditionalitemtype6 varchar(4),
@vadditionalitemname1 nvarchar(50),
@vadditionalitemname2 nvarchar(50),
@vadditionalitemname3 nvarchar(50),
@vadditionalitemname4 nvarchar(50),
@vadditionalitemname5 nvarchar(50),
@vadditionalitemname6 nvarchar(50),
@vadditionalitemprice1 decimal(18,2),
@vadditionalitemprice2 decimal(18,2),
@vadditionalitemprice3 decimal(18,2),
@vadditionalitemprice4 decimal(18,2),
@vadditionalitemprice5 decimal(18,2),
@vadditionalitemprice6 decimal(18,2),
@linenumber int
--transportation
--cplymarkup
--cplybreakfastmarkup
--agentgroup
--创建一个临时表
DECLARE @resultTbl TABLE
(
duplicateMsg varchar(400) NOT NULL,
linenumber int
)
-- 创建游标
DECLARE IT_FILTER_CURSOR CURSOR DYNAMIC FOR
SELECT internalid, tariffid,hotelid, clienttype, ratetype, roomcategoryid, refhotelid, validfrom, validto, [dayofweek], mealcode, suppliercode, applnationality,
bookingcode,sgl, twn, dbl, trp, qud, xba, xbc, tsu, dsu,sdbl,etwn, meal, bonusmeal, aaltermealcode, aaltermeal, baltermealcode, baltermeal, costcurr, minstay, maxstay, nonapplnationality,
bonusstay, maxbonusstay, inclmealonbonusstay, chargemealonbonusstay, allowextensionnight, allow2ndnightrate, ratedesc_en, ratedesc_tw,
ratedesc_cn, cplycharge, cplyitem_en, cplyitem_tw, cplyitem_cn, suppcharge, supphalfboard, suppfullboard, suppmisc, suppitem_en, suppitem_tw, suppitem_cn,
cxldesc_en, cxldesc_tw, cxldesc_cn, remarks, internalremarks, agentpermit, rsvntel, rsvnfax, rsvnemail,
hasfocpolicy, hascxlpolicy,
additionalitemtype1,additionalitemtype2,additionalitemtype3,additionalitemtype4,additionalitemtype5,additionalitemtype6,
additionalitemname1,additionalitemname2,additionalitemname3,additionalitemname4,additionalitemname5,additionalitemname6,
additionalitemprice1,additionalitemprice2,additionalitemprice3,additionalitemprice4,additionalitemprice5,additionalitemprice6,linenumber
FROM [FareAdmin].[tariff]
-- 打开游标
OPEN IT_FILTER_CURSOR
-- 读取游标
FETCH FIRST FROM IT_FILTER_CURSOR INTO
@vittariffid,@vtariffid, @vithotelid, @vitclienttype, @vitratetype, @vitroomcategoryid, @vitrefhotelid, @vitvalidfrom, @vitvalidto, @vitdayofweek, @vitmealcode,@vitsuppliercode,@vitapplnationality,
@vbookingcode, @vsgl, @vtwn, @vdbl, @vtrp, @vqud, @vxba, @vxbc, @vtsu, @vdsu,@vsdbl,@vetwn, @vmeal,@vbonusmeal, @vaaltermealcode, @vaaltermeal, @vbaltermealcode, @vbaltermeal, @vcostcurr, @vminstay, @vmaxstay, @vnonapplnationality,
@vbonusstay, @vmaxbonusstay, @vinclmealonbonusstay, @vchargemealonbonusstay, @vallowextensionnight, @vallow2ndnightrate,@vratedesc_en, @vratedesc_tw,
@vratedesc_cn, @vcplycharge, @vcplyitem_en, @vcplyitem_tw, @vcplyitem_cn, @vsuppcharge, @vsupphalfboard, @vsuppfullboard, @vsuppmisc, @vsuppitem_en, @vsuppitem_tw, @vsuppitem_cn,
@vcxldesc_en, @vcxldesc_tw, @vcxldesc_cn, @vremarks, @vinternalremarks, @vagentpermit, @vrsvntel, @vrsvnfax, @vrsvnemail,
@vhasfocpolicy, @vhascxlpolicy,
@vadditionalitemtype1,@vadditionalitemtype2,@vadditionalitemtype3,@vadditionalitemtype4,
@vadditionalitemtype5,@vadditionalitemtype6,@vadditionalitemname1,@vadditionalitemname2,
@vadditionalitemname3,@vadditionalitemname4,@vadditionalitemname5,@vadditionalitemname6,
@vadditionalitemprice1,@vadditionalitemprice2,@vadditionalitemprice3,@vadditionalitemprice4,
@vadditionalitemprice5,@vadditionalitemprice6,@linenumber
SET @itfilterfetch = @@FETCH_STATUS
-- print 'itfilterfetch : ' + @itfilterfetch
DECLARE @vRecHit INT
DECLARE @duplicate INT
DECLARE @index INT
DECLARE @tmpDuplicateMsg nvarchar(500)
set @tmpDuplicateMsg = ''
while (@itfilterfetch = 0)
begin
set @index = 0
-- 检查hotel_nh_egl tariff table中是否duplicate
SELECT @vRecHit=count(*) FROM [hotel_nh_egl].[FareAdmin].[tariff] t
WHERE t.hotelid=@vithotelid AND t.clienttype=@vitclienttype
AND t.ratetype=@vitratetype AND t.roomcategoryid=@vitroomcategoryid
AND t.mealcode=@vitmealcode AND t.suppliercode=@vitsuppliercode
AND t.applnationality=@vitapplnationality
AND (t.refhotelid = @vitrefhotelid OR (ISNULL(@vitrefhotelid,0)=0 AND t.refhotelid is Null))
AND (t.validfrom <= @vitvalidfrom OR t.validfrom <= @vitvalidto)
AND (t.validto >= @vitvalidto OR t.validto >= @vitvalidfrom)
AND (
(CHARINDEX('1',t.dayofweek)>0 AND CHARINDEX('1',@vitdayofweek)>0) OR
(CHARINDEX('2',t.dayofweek)>0 AND CHARINDEX('2',@vitdayofweek)>0) OR
(CHARINDEX('3',t.dayofweek)>0 AND CHARINDEX('3',@vitdayofweek)>0) OR
(CHARINDEX('4',t.dayofweek)>0 AND CHARINDEX('4',@vitdayofweek)>0) OR
(CHARINDEX('5',t.dayofweek)>0 AND CHARINDEX('5',@vitdayofweek)>0) OR
(CHARINDEX('6',t.dayofweek)>0 AND CHARINDEX('6',@vitdayofweek)>0) OR
(CHARINDEX('7',t.dayofweek)>0 AND CHARINDEX('7',@vitdayofweek)>0) OR
(CHARINDEX('0',t.dayofweek)>0 AND CHARINDEX('0',@vitdayofweek)>0)
)
IF @vRecHit = 0 PRINT 'NO Duplicate'
IF @vRecHit = 1 and @vtariffid is null
begin
PRINT 'duplicate in : hotelid=' + STR(@vithotelid) + ' roomcategoryid=' + STR(@vitroomcategoryid) +' ratetype=' + STR(@vitratetype) + ' clienttype='+STR(@vitclienttype) + ' suppliercode='+@vitsuppliercode + ' mealcode='+@vitmealcode
SET @tmpDuplicateMsg = 'duplicate in : hotelid=' + STR(@vithotelid) + ' roomcategoryid=' + STR(@vitroomcategoryid) +' ratetype=' + STR(@vitratetype) + ' clienttype='+STR(@vitclienttype) + ' suppliercode='+@vitsuppliercode + ' mealcode='+@vitmealcode
PRINT @tmpDuplicateMsg
INSERT INTO @resultTbl ( duplicateMsg,linenumber ) values ( @tmpDuplicateMsg,@linenumber )
SET @duplicate = 1
PRINT @duplicate
end
-- 检查tariff table中是否duplicate
SELECT @vRecHit=count(*) FROM [FareAdmin].[tariff] t
WHERE t.hotelid=@vithotelid AND t.clienttype=@vitclienttype
AND t.ratetype=@vitratetype AND t.roomcategoryid=@vitroomcategoryid
AND t.mealcode=@vitmealcode AND t.suppliercode=@vitsuppliercode
AND t.applnationality=@vitapplnationality
AND (t.refhotelid = @vitrefhotelid OR (ISNULL(@vitrefhotelid,0)=0 AND t.refhotelid is Null))
AND (t.validfrom <= @vitvalidfrom OR t.validfrom <= @vitvalidto)
AND (t.validto >= @vitvalidto OR t.validto >= @vitvalidfrom)
AND (
(CHARINDEX('1',t.dayofweek)>0 AND CHARINDEX('1',@vitdayofweek)>0) OR
(CHARINDEX('2',t.dayofweek)>0 AND CHARINDEX('2',@vitdayofweek)>0) OR
(CHARINDEX('3',t.dayofweek)>0 AND CHARINDEX('3',@vitdayofweek)>0) OR
(CHARINDEX('4',t.dayofweek)>0 AND CHARINDEX('4',@vitdayofweek)>0) OR
(CHARINDEX('5',t.dayofweek)>0 AND CHARINDEX('5',@vitdayofweek)>0) OR
(CHARINDEX('6',t.dayofweek)>0 AND CHARINDEX('6',@vitdayofweek)>0) OR
(CHARINDEX('7',t.dayofweek)>0 AND CHARINDEX('7',@vitdayofweek)>0) OR
(CHARINDEX('0',t.dayofweek)>0 AND CHARINDEX('0',@vitdayofweek)>0)
)
AND t.internalid <> @vittariffid
IF @vRecHit = 0 PRINT 'NO Duplicate'
IF @vRecHit = 1
begin
PRINT 'duplicate in : hotelid=' + STR(@vithotelid) + ' roomcategoryid=' + STR(@vitroomcategoryid) +' ratetype=' + STR(@vitratetype) + ' clienttype='+STR(@vitclienttype) + ' suppliercode='+ @vitsuppliercode + ' mealcode='+@vitmealcode
SET @tmpDuplicateMsg = 'duplicate in : hotelid=' + STR(@vithotelid) + ' roomcategoryid=' + STR(@vitroomcategoryid) +' ratetype=' + STR(@vitratetype) + ' clienttype='+STR(@vitclienttype) + ' suppliercode='+@vitsuppliercode + ' mealcode='+@vitmealcode
PRINT @tmpDuplicateMsg
INSERT INTO @resultTbl ( duplicateMsg,linenumber ) values ( @tmpDuplicateMsg,@linenumber )
SET @duplicate = 1
PRINT @duplicate
end
-- set @index = @index + 1
FETCH NEXT FROM IT_FILTER_CURSOR INTO @vittariffid,@vtariffid, @vithotelid, @vitclienttype, @vitratetype, @vitroomcategoryid, @vitrefhotelid, @vitvalidfrom, @vitvalidto, @vitdayofweek, @vitmealcode,@vitsuppliercode,@vitapplnationality,
@vbookingcode, @vsgl, @vtwn, @vdbl, @vtrp, @vqud, @vxba, @vxbc, @vtsu, @vdsu, @vsdbl,@vetwn,@vmeal,
@vbonusmeal, @vaaltermealcode, @vaaltermeal, @vbaltermealcode, @vbaltermeal, @vcostcurr, @vminstay,
@vmaxstay, @vnonapplnationality,@vbonusstay, @vmaxbonusstay, @vinclmealonbonusstay, @vchargemealonbonusstay,
@vallowextensionnight, @vallow2ndnightrate,@vratedesc_en, @vratedesc_tw,
@vratedesc_cn, @vcplycharge, @vcplyitem_en, @vcplyitem_tw,
@vcplyitem_cn, @vsuppcharge, @vsupphalfboard, @vsuppfullboard,
@vsuppmisc, @vsuppitem_en, @vsuppitem_tw, @vsuppitem_cn,
@vcxldesc_en, @vcxldesc_tw, @vcxldesc_cn, @vremarks,
@vinternalremarks, @vagentpermit, @vrsvntel,
@vrsvnfax, @vrsvnemail, @vhasfocpolicy, @vhascxlpolicy,
@vadditionalitemtype1,@vadditionalitemtype2,@vadditionalitemtype3,@vadditionalitemtype4,
@vadditionalitemtype5,@vadditionalitemtype6,@vadditionalitemname1,@vadditionalitemname2,
@vadditionalitemname3,@vadditionalitemname4,@vadditionalitemname5,@vadditionalitemname6,
@vadditionalitemprice1,@vadditionalitemprice2,@vadditionalitemprice3,@vadditionalitemprice4,
@vadditionalitemprice5,@vadditionalitemprice6,@linenumber
SET @itfilterfetch = @@FETCH_STATUS
end
-- 关闭游标
CLOSE IT_FILTER_CURSOR
-- 删除游标
DEALLOCATE IT_FILTER_CURSOR
-- 如果存在duplicate return
IF @duplicate = 1
begin
PRINT @tmpDuplicateMsg
--SELECT @duplicateMsg = @tmpDuplicateMsg
--SELECT @status = 0
SELECT duplicateMsg,linenumber from @resultTbl
DELETE from @resultTbl
return
end
-- 如果不存在duplicate
--BEGIN TRY
BEGIN TRANSACTION
print 'BEGIN TRANSACTION'
-- 创建游标
DECLARE RESULT_CURSOR CURSOR DYNAMIC FOR
SELECT internalid, tariffid,hotelid, clienttype, ratetype, roomcategoryid, refhotelid, validfrom, validto, [dayofweek], mealcode, suppliercode, applnationality,
bookingcode,sgl, twn, dbl, trp, qud, xba, xbc, tsu, dsu,sdbl,etwn, meal, bonusmeal, aaltermealcode, aaltermeal, baltermealcode, baltermeal, costcurr, minstay, maxstay,nonapplnationality,
bonusstay, maxbonusstay, inclmealonbonusstay, chargemealonbonusstay, allowextensionnight, allow2ndnightrate, ratedesc_en, ratedesc_tw,
ratedesc_cn, cplycharge, cplyitem_en, cplyitem_tw, cplyitem_cn, suppcharge, supphalfboard, suppfullboard, suppmisc, suppitem_en, suppitem_tw, suppitem_cn,
cxldesc_en, cxldesc_tw, cxldesc_cn, remarks, internalremarks, agentpermit, rsvntel, rsvnfax, rsvnemail,
hasfocpolicy, hascxlpolicy,
additionalitemtype1,additionalitemtype2,additionalitemtype3,additionalitemtype4,additionalitemtype5,additionalitemtype6,
additionalitemname1,additionalitemname2,additionalitemname3,additionalitemname4,additionalitemname5,additionalitemname6,
additionalitemprice1,additionalitemprice2,additionalitemprice3,additionalitemprice4,additionalitemprice5,additionalitemprice6
FROM [FareAdmin].[tariff]
-- 打开游标
OPEN RESULT_CURSOR
-- 读取游标
FETCH FIRST FROM RESULT_CURSOR INTO
@vittariffid,@vtariffid, @vithotelid, @vitclienttype, @vitratetype, @vitroomcategoryid, @vitrefhotelid, @vitvalidfrom, @vitvalidto, @vitdayofweek, @vitmealcode,@vitsuppliercode,@vitapplnationality,
@vbookingcode, @vsgl, @vtwn, @vdbl, @vtrp, @vqud, @vxba, @vxbc, @vtsu, @vdsu,@vsdbl,@vetwn, @vmeal,@vbonusmeal, @vaaltermealcode, @vaaltermeal, @vbaltermealcode, @vbaltermeal, @vcostcurr, @vminstay, @vmaxstay, @vnonapplnationality,
@vbonusstay, @vmaxbonusstay, @vinclmealonbonusstay, @vchargemealonbonusstay, @vallowextensionnight, @vallow2ndnightrate,@vratedesc_en, @vratedesc_tw,
@vratedesc_cn, @vcplycharge, @vcplyitem_en, @vcplyitem_tw, @vcplyitem_cn, @vsuppcharge, @vsupphalfboard, @vsuppfullboard, @vsuppmisc, @vsuppitem_en, @vsuppitem_tw, @vsuppitem_cn,
@vcxldesc_en, @vcxldesc_tw, @vcxldesc_cn, @vremarks, @vinternalremarks, @vagentpermit, @vrsvntel, @vrsvnfax, @vrsvnemail,
@vhasfocpolicy, @vhascxlpolicy,
@vadditionalitemtype1,@vadditionalitemtype2,@vadditionalitemtype3,@vadditionalitemtype4,
@vadditionalitemtype5,@vadditionalitemtype6,@vadditionalitemname1,@vadditionalitemname2,
@vadditionalitemname3,@vadditionalitemname4,@vadditionalitemname5,@vadditionalitemname6,
@vadditionalitemprice1,@vadditionalitemprice2,@vadditionalitemprice3,@vadditionalitemprice4,
@vadditionalitemprice5,@vadditionalitemprice6
SET @itfilterfetch = @@FETCH_STATUS
WHILE @itfilterfetch = 0
BEGIN
IF @vtariffid is null
begin
PRINT 'INSERT tariff FROM temp id is'+STR(@vittariffid)
INSERT INTO [hotel_nh_egl].[FareAdmin].[tariff]
( hotelid, suppliercode, bookingcode, clienttype, ratetype, roomcategoryid, refhotelid, validfrom, validto, sgl, twn, dbl, trp, qud, xba, xbc, tsu, dsu,sdbl,etwn,
mealcode, meal, bonusmeal, aaltermealcode, aaltermeal, baltermealcode, baltermeal, costcurr, [dayofweek], minstay, maxstay, applnationality, nonapplnationality,
bonusstay, maxbonusstay, inclmealonbonusstay, chargemealonbonusstay, allowextensionnight, allow2ndnightrate, ratedesc_en, ratedesc_tw,
ratedesc_cn, cplycharge, cplyitem_en, cplyitem_tw, cplyitem_cn, suppcharge, supphalfboard, suppfullboard, suppmisc, suppitem_en, suppitem_tw, suppitem_cn,
cxldesc_en, cxldesc_tw, cxldesc_cn, remarks, internalremarks, agentpermit, rsvntel, rsvnfax, rsvnemail,
hasfocpolicy, hascxlpolicy, updateby, createby,
additionalitemtype1,additionalitemtype2,additionalitemtype3,additionalitemtype4,additionalitemtype5,additionalitemtype6,
additionalitemname1,additionalitemname2,additionalitemname3,additionalitemname4,additionalitemname5,additionalitemname6,
additionalitemprice1,additionalitemprice2,additionalitemprice3,additionalitemprice4,additionalitemprice5,additionalitemprice6
) SELECT hotelid, suppliercode, bookingcode, clienttype, ratetype, roomcategoryid, refhotelid, validfrom, validto, sgl, twn, dbl, trp, qud, xba, xbc, tsu, dsu,sdbl,etwn,
mealcode, meal, bonusmeal, aaltermealcode, aaltermeal, baltermealcode, baltermeal, costcurr, [dayofweek], minstay, maxstay, applnationality, nonapplnationality,
bonusstay, maxbonusstay, inclmealonbonusstay, chargemealonbonusstay, allowextensionnight, allow2ndnightrate, ratedesc_en, ratedesc_tw,
ratedesc_cn, cplycharge, cplyitem_en, cplyitem_tw, cplyitem_cn, suppcharge, supphalfboard, suppfullboard, suppmisc, suppitem_en, suppitem_tw, suppitem_cn,
cxldesc_en, cxldesc_tw, cxldesc_cn, remarks, internalremarks, agentpermit, rsvntel, rsvnfax, rsvnemail,
hasfocpolicy, hascxlpolicy, @updatedby, @updatedby,
additionalitemtype1,additionalitemtype2,additionalitemtype3,additionalitemtype4,additionalitemtype5,additionalitemtype6,
additionalitemname1,additionalitemname2,additionalitemname3,additionalitemname4,additionalitemname5,additionalitemname6,
additionalitemprice1,additionalitemprice2,additionalitemprice3,additionalitemprice4,additionalitemprice5,additionalitemprice6
FROM [FareAdmin].[tariff] WHERE internalid = @vittariffid
DECLARE @itNewTariffId numeric(18,0)
SELECT @itNewTariffId = @@IDENTITY
PRINT 'NEW TARIFF ID: '+str(@itNewTariffId)
INSERT INTO [hotel_nh_egl].[FareAdmin].[cxlpolicy] (tariffid, cxldays, cxlnights, cxlpercent)
SELECT @itNewTariffId, cxldays, cxlnights, cxlpercent FROM [FareAdmin].[cxlpolicy] WHERE tariffid = @vittariffid
INSERT INTO [hotel_nh_egl].[FareAdmin].[focpolicy] (tariffid, foc_noofnight, foc_en, foc_tw, foc_cn)
SELECT @itNewTariffId, foc_noofnight, foc_en, foc_tw, foc_cn FROM [FareAdmin].[focpolicy] WHERE tariffid = @vittariffid
INSERT INTO [hotel_nh_egl].[FareAdmin].[blackout] (tariffid, validfrom, validto)
SELECT @itNewTariffId, validfrom, validto FROM [FareAdmin].[blackout] WHERE tariffid = @vittariffid
end
else
begin
print @vtariffid
UPDATE [hotel_nh_egl].[FareAdmin].[tariff]
SET [hotelid] = @vithotelid
,[suppliercode] = @vitsuppliercode
,[bookingcode] = @vbookingcode
,[clienttype] = @vitclienttype
,[ratetype] = @vitratetype
,[roomcategoryid] = @vitroomcategoryid
,[refhotelid] = @vitrefhotelid
,[validfrom] = @vitvalidfrom
,[validto] = @vitvalidto
,[sgl] = @vsgl
,[twn] = @vtwn
,[dbl] = @vdbl
,[trp] = @vtrp
,[qud] = @vqud
,[xba] = @vxba
,[xbc] = @vxbc
,[tsu] = @vtsu
,[dsu] = @vdsu
,[sdbl]= @vsdbl
,[etwn]= @vetwn
,[mealcode] = @vitmealcode
,[meal] = @vmeal
,[bonusmeal] = @vbonusmeal
,[aaltermealcode] = @vaaltermealcode
,[aaltermeal] = @vaaltermeal
,[baltermealcode] = @vbaltermealcode
,[baltermeal] = @vbaltermeal
,[costcurr] = @vcostcurr
,[dayofweek] = @vitdayofweek
,[minstay] = @vminstay
,[maxstay] = @vmaxstay
,[applnationality] = @vitapplnationality
,[nonapplnationality] = @vnonapplnationality
,[bonusstay] = @vbonusstay
,[maxbonusstay] = @vmaxbonusstay
,[inclmealonbonusstay] = @vinclmealonbonusstay
,[chargemealonbonusstay] = @vchargemealonbonusstay
,[allowextensionnight] = @vallowextensionnight
,[allow2ndnightrate] = @vallow2ndnightrate
--,[allowprestayrate] = <allowprestayrate, bit,>
--,[specialnight] = <specialnight, int,>
--,[maxextnight] = <maxextnight, int,>
,[ratedesc_en] = @vratedesc_en
,[ratedesc_tw] = @vratedesc_tw
,[ratedesc_cn] = @vratedesc_cn
,[cplycharge] = @vcplycharge
,[cplyitem_en] = @vcplyitem_en
,[cplyitem_tw] = @vcplyitem_tw
,[cplyitem_cn] = @vcplyitem_cn
,[suppcharge] = @vsuppcharge
,[supphalfboard] = @vsupphalfboard
,[suppfullboard] = @vsuppfullboard
,[suppmisc] = @vsuppmisc
,[suppitem_en] = @vsuppitem_en
,[suppitem_tw] = @vsuppitem_tw
,[suppitem_cn] = @vsuppitem_cn
,[hasfocpolicy] = @vhasfocpolicy
,[hascxlpolicy] = @vhascxlpolicy
,[cxldesc_en] = @vcxldesc_en
,[cxldesc_tw] = @vcxldesc_tw
,[cxldesc_cn] = @vcxldesc_cn
,[remarks] = @vremarks
,[internalremarks] = @vinternalremarks
,[agentpermit] = @vagentpermit
,[rsvntel] = @vrsvntel
,[rsvnfax] = @vrsvnfax
,[rsvnemail] = @vrsvnemail
--,[updatedate] = <updatedate, datetime,>
,[updateby] = @updatedby
--,[createdate] = <createdate, datetime,>
--,[createby] = <createby, nvarchar(50),>
,[additionalitemtype1] = @vadditionalitemtype1
,[additionalitemtype2] = @vadditionalitemtype2
,[additionalitemtype3] = @vadditionalitemtype3
,[additionalitemtype4] = @vadditionalitemtype4
,[additionalitemtype5] = @vadditionalitemtype5
,[additionalitemtype6] = @vadditionalitemtype6
,[additionalitemname1] = @vadditionalitemname1
,[additionalitemname2] = @vadditionalitemname2
,[additionalitemname3] = @vadditionalitemname3
,[additionalitemname4] = @vadditionalitemname4
,[additionalitemname5] = @vadditionalitemname5
,[additionalitemname6] = @vadditionalitemname6
,[additionalitemprice1] = @vadditionalitemprice1
,[additionalitemprice2] = @vadditionalitemprice2
,[additionalitemprice3] = @vadditionalitemprice3
,[additionalitemprice4] = @vadditionalitemprice4
,[additionalitemprice5] = @vadditionalitemprice5
,[additionalitemprice6] = @vadditionalitemprice6
--,[transportation] = <transportation, bit,>
--,[cplymarkup] = <cplymarkup, int,>
--,[cplybreakfastmarkup] = <cplybreakfastmarkup, decimal(18,2),>
--,[agentgroup] = <agentgroup, numeric,>
WHERE internalid = @vtariffid
end
FETCH NEXT FROM RESULT_CURSOR INTO @vittariffid,@vtariffid, @vithotelid, @vitclienttype, @vitratetype, @vitroomcategoryid, @vitrefhotelid, @vitvalidfrom, @vitvalidto, @vitdayofweek, @vitmealcode,@vitsuppliercode,@vitapplnationality,
@vbookingcode, @vsgl, @vtwn, @vdbl, @vtrp, @vqud, @vxba, @vxbc, @vtsu, @vdsu, @vsdbl,@vetwn,@vmeal,
@vbonusmeal, @vaaltermealcode, @vaaltermeal, @vbaltermealcode, @vbaltermeal, @vcostcurr, @vminstay,
@vmaxstay,@vnonapplnationality, @vbonusstay, @vmaxbonusstay, @vinclmealonbonusstay, @vchargemealonbonusstay,
@vallowextensionnight, @vallow2ndnightrate,@vratedesc_en, @vratedesc_tw,
@vratedesc_cn, @vcplycharge, @vcplyitem_en, @vcplyitem_tw,
@vcplyitem_cn, @vsuppcharge, @vsupphalfboard, @vsuppfullboard,
@vsuppmisc, @vsuppitem_en, @vsuppitem_tw, @vsuppitem_cn,
@vcxldesc_en, @vcxldesc_tw, @vcxldesc_cn, @vremarks,
@vinternalremarks, @vagentpermit, @vrsvntel,
@vrsvnfax, @vrsvnemail, @vhasfocpolicy, @vhascxlpolicy,
@vadditionalitemtype1,@vadditionalitemtype2,@vadditionalitemtype3,@vadditionalitemtype4,
@vadditionalitemtype5,@vadditionalitemtype6,@vadditionalitemname1,@vadditionalitemname2,
@vadditionalitemname3,@vadditionalitemname4,@vadditionalitemname5,@vadditionalitemname6,
@vadditionalitemprice1,@vadditionalitemprice2,@vadditionalitemprice3,@vadditionalitemprice4,
@vadditionalitemprice5,@vadditionalitemprice6
SET @itfilterfetch = @@FETCH_STATUS
END
-- 关闭游标
CLOSE RESULT_CURSOR
-- 删除游标
DEALLOCATE RESULT_CURSOR
--SELECT @duplicateMsg = @tmpDuplicateMsg
--SELECT 'SUCCESS' AS boolsuccess
SELECT duplicateMsg,linenumber from @resultTbl
--SELECT @status = 1
COMMIT TRANSACTION
END
GO
。。。
相关推荐
- Python：使用pandas库读取Excel数据，再利用pandas的`to_sql`函数将数据写入数据库，例如`df.to_sql('table_name', con=engine, if_exists='append')`。 - Java：使用JDBC驱动，先加载Excel数据到Java的...
3. **使用其他格式**：如果可能，可以考虑将超长文本存储在单独的表格或文件中，然后通过外键关联到主数据表。 在实际操作中，Python的pandas库是一个强大的工具，可以方便地读取Excel文件（`pd.read_excel()`）和...
Excel读取数据导入DB2数据库需要使用POI库读取Excel文件，并使用JDBC API连接DB2数据库，并执行SQL语句将Excel数据导入到数据库中。同时，我们还需要考虑实现一个通用转换类，以便于提高代码的灵活性和可维护性。
本文将详细讲解如何将Excel数据导入到Oracle数据库中，以及在这个过程中涉及的关键技术点。 首先，Excel是一种广泛使用的电子表格工具，它允许用户组织、计算和存储数据。而Oracle数据库则是一个强大的关系型数据库...
本主题聚焦于如何利用C#处理Excel文件，并将其数据导入到SQL Server数据库中。这一过程通常涉及到两个主要步骤：读取Excel数据和与数据库进行交互。 首先，我们来看C#读取Excel数据的部分。在.NET框架中，可以使用...
2. 创建Excel工作簿和工作表对象，使用Apache POI将数据写入到工作表中。 3. 设置单元格格式，如对齐方式、字体、颜色等，以提高数据的可读性。 4. 将工作簿写入到OutputStream中，这样可以通过HTTP响应发送到客户端...
以下将详细介绍如何将Excel文件导入到数据库中，以及如何将数据库中的数据导出到Excel中。 首先，将Excel文件导入数据库通常涉及到以下几个步骤： 1. 数据预处理：在Excel中整理数据，确保格式正确，无错误或缺失...
Excel 把数据导入到 SQL 数据库中 Excel 是一个功能强大的电子表格软件，而 SQL 数据库则是存储和管理大量数据的理想选择。将 Excel 数据导入到 SQL 数据库中可以实现数据的集中管理和快速查询。下面是将 Excel ...
在本项目中，"SpringBootMybatis+poi+Thymeleaf实现excel文件数据导入到数据库以及从数据库将数据导出成excel.zip"，我们主要关注的是如何利用Java技术栈来处理Excel文件，并与数据库进行交互。以下是相关知识点的...
当需要处理Excel中的大量数据并将其快速导入到数据库时，C#提供了一些高效的方法来实现这一目标。本文将详细讲解如何利用C#实现Excel数据的高效导入。 首先，我们需要了解如何在C#中读取Excel文件。.NET Framework...
在"EXCEL导入数据库"的示例项目中，很可能包含了实现上述步骤的源代码。开发者可以通过阅读和学习这些代码，了解如何在实际应用中执行Excel数据到数据库的迁移。 总结来说，"将Excel中的数据导入数据库"涉及到数据...
本项目"java实现Excel数据导入到mysql数据库"旨在利用Java技术将Excel表格中的数据高效地导入到MySQL数据库，并且在数据库中存在相同数据时进行更新，同时也支持将数据库中的数据导出到Excel表中。这个过程涉及到多...
本示例主要关注如何利用ASP（Active Server Pages）技术将本地Excel数据导入到Access数据库中。这种方法同样适用于其他类型的数据库，只要适当地调整连接和处理方式。以下将详细介绍这个过程的关键知识点。 1. ASP...
Java实现从excel中批量高效导入数据到数据库中，包括图片的导入存储，读取图片显示在jsp页面等，需要下载JspSmartUpload.jar和jxl.jar两个jar包，将两个包导入项目才能运行
标题“利用jxl把excel中的数据导入数据库”指的是使用Java Excel API（jxl）这个库来读取Excel文件中的数据，并将这些数据批量地导入到关系型数据库中。这是一个常见的需求，特别是在处理大量结构化数据时，例如进行...
本知识点将深入讲解如何将Excel表数据导入到SQL数据库中，这在数据分析、报表生成、业务系统集成等场景下非常常见。 首先，我们需要理解Excel和SQL数据库之间的基本差异。Excel是微软公司开发的一款电子表格应用，...
标题中的“Excel数据导入Access数据库小程序”指的是一个ASP（Active Server Pages）编程实现的应用程序，它的主要功能是帮助用户高效地将Excel电子表格中的数据批量导入到Access数据库中。这样的工具在数据处理和...
在这个场景中，我们关注的是如何使用易语言（E语言）将Excel文件中的数据导入到MDB（Microsoft Access）数据库中特定的字段，并在此过程中进行数据清洗，如删除空格和星号，以及统计导入的人数。下面将详细解释这个...
本篇文章将深入探讨如何使用Delphi编程环境，将Excel数据高效地导入到MySQL数据库中，并独立创建数据库。 首先，我们需要理解Excel的数据结构。Excel文件（.xls或.xlsx）本质上是二维表格，包含行、列和单元格，...