Editor pairs Posted January 7, 2014 Editor Share Posted January 7, 2014 (edited) Global Functions [spoiler] require"luacom" local libName = "luasql" local Private = {} luasql = type(_G[libName]) == "table" and _G[libName] or {} local ADOTypes = {} do local conn = luacom.CreateObject("ADODB.Connection") local typeinfo = luacom.GetTypeInfo(conn) local typelib = typeinfo:GetTypeLib() local enums = typelib:ExportEnumerations() for k, v in pairs(enums.DataTypeEnum) do ADOTypes[k] = v ADOTypes[v] = k end end local metatable = { __metatable = "LuaSQL: you're not allowed to get this metatable" } luasql._COPYRIGHT = "Copyright (C) 2003-2006 Kepler Project" luasql._DESCRIPTION = "LuaSQL is a simple interface from Lua to a DBMS" luasql._VERSION = "LuaSQL 2.1.1" function luasql.ado() local isClosed = false local openConns = {} openConns.n = 0 local env = {} setmetatable(env, metatable) env.ADOTypes = ADOTypes local function closeConn(con) if not openConns[con] then return false end openConns[con] = nil openConns.n = openConns.n - 1 return true end function env:close() if not self then error("You must provide a self parameter") end if isClosed or openConns.n ~= 0 then return false end isClosed = true return true end function env:connect(sourcestr, user, pass, opts) if not self then error("You must provide a self parameter") end if isClosed then error("Environment closed") end if type(sourcestr) ~= "string" then error("Sourcename must be a string") end local conn = luacom.CreateObject("ADODB.Connection") local ok, errmsg = pcall(conn.Open, conn, sourcestr, user, pass, opts) if not ok then return nil, errmsg end if conn.State == 0 then return nil, "Invalid sourcename" end openConns[conn] = true openConns.n = openConns.n + 1 ok, errmsg = pcall(conn.BeginTrans, conn) return Private.createConnection(conn, closeConn) end return env end; function Private.createConnection(conObj, closeFunc) local openCursors = {} openCursors.n = 0 local isClosed = false local con = {} setmetatable(con, metatable) local autocommit = true local function closeCursor(cursor) if not openCursors[cursor] then return false end openCursors[cursor] = nil openCursors.n = openCursors.n - 1 end function con:close() if not self then error("You must provide a self parameter") end if isClosed or openCursors.n ~= 0 then return false end isClosed = true local cond, err = pcall(conObj.RollbackTrans, conObj) conObj:Close() closeFunc(conObj) return true end function con:commit() if not self then error("You must provide a self parameter") end if isClosed then error("Connection closed") end local cond, err = pcall(conObj.CommitTrans, conObj) if not cond then return false, err end local cond, err = pcall(conObj.BeginTrans, conObj) if not cond then return false, err end return true end function con:execute(sql) if not self then error("You must provide a self parameter") end if isClosed then error("Connection closed") end local cond, res, upcount = pcall(conObj.Execute, conObj, sql) if not cond then return nil, res end if not upcount then upcount = 0 end if autocommit then local cond, err = con:commit() if not cond then return nil, err end end if res and res.State ~= 0 then res = Private.createCursor(res, con, closeCursor) openCursors[res] = true openCursors.n = openCursors.n + 1 else res = upcount end return res end function con:rollback() if not self then error("You must provide a self parameter") end if isClosed then error("Connection closed") end local cond, err = pcall(conObj.RollbackTrans, conObj) if not cond then return false, err end local cond, err = pcall(conObj.BeginTrans, conObj) if not cond then return false, err end return true end function con:setautocommit(bool) if not self then error("You must provide a self parameter") end if isClosed then error("Connection closed") end local cond, err = pcall(conObj.CommitTrans, conObj) if not cond then return false, err end autocommit = bool local cond, err = pcall(conObj.BeginTrans, conObj) if not cond then return false, err end return true end return con end; function Private.createCursor(rs, con, closeFunc) local isClosed = false local cursor = rs local res = {} local col_names = nil local col_types = nil setmetatable(res, metatable) function res:close() if not self then error("You must provide a self parameter") end if isClosed then return false end rs:Close() closeFunc(res, con) isClosed = true return true end function res:fetch(tb, modestring) if not self then error("You must provide a self parameter") end if isClosed then error("Cursor closed") end local arg_tb = tb if cursor.EOF then return nil, "End of cursor reached" end if tb == nil or type(tb) ~= "table" then tb = {} end if modestring == nil or type(modestring) ~= "string" then modestring = "n" end for i = 0, cursor.Fields.Count-1 do local fields = cursor.Fields local cond, field = pcall(fields.Item, fields, i) if not cond then return nil, field end if modestring == "n" or modestring == "an" or modestring == "na" then tb[i+1] = field.Value end if modestring == "a" or modestring == "an" or modestring == "na" then tb[field.Name] = field.Value end end local cond, err = pcall(cursor.MoveNext, cursor) if not cond then return nil, err end if modestring == "n" and not arg_tb then return unpack(tb) else return tb end end function res:getcolnames() if not self then error("You must provide a self parameter") end if isClosed then error("Cursor closed") end if col_names then return col_names end col_names = {} local tb = col_names for i = 0, cursor.Fields.Count-1 do local fields = cursor.Fields local cond, field = pcall(fields.Item, fields, i) if not cond then return nil, field end tb[i+1] = field.Name end return tb end function res:getcoltypes() if not self then error("You must provide a self parameter") end if isClosed then error("Cursor closed") end if col_types then return col_types end col_types = {} local tb = col_types for i = 0, cursor.Fields.Count-1 do local fields = cursor.Fields local cond, field = pcall(fields.Item, fields, i) if not cond then return nil, field end tb[i+1] = ADOTypes[field.Type] end return tb end return res end; [/spoiler] Bağlantı sağlamak ve veri almak. function rows(connection, sql_stmt) local cursor = assert(connection:execute(sql_stmt)) return function() return cursor:fetch() end end local fpath = "C:\\deneme.xlsx" local constr = "Provider=Microsoft.ACE.OLEDB.12.0;".. "Data Source=\""..fpath.."\";".. "Extended Properties=\"Excel 12.0 Xml;HDR=YES\""; local env = assert(luasql.ado()) local con = assert(env:connect(constr)) local query = "SELECT * FROM \[Sayfa1$\]" for kolon1, kolon2, kolon3 in rows(con, query) do --kolon sıralamasına göre işlem end con:close() env:close() [attachment=1225:ExcelOrnek.rar] Edited March 5, 2014 by pairs Quote Link to comment Share on other sites More sharing options...
KintaRo Posted January 7, 2014 Share Posted January 7, 2014 bir insanın bu kadar da yüzüne vurulmaz ki canım :glare: Quote Link to comment Share on other sites More sharing options...
Editor pairs Posted January 7, 2014 Author Editor Share Posted January 7, 2014 bir insanın bu kadar da yüzüne vurulmaz ki canım :glare: Sana kastım yoktu üstad :D Maksat bilgi olsun. Quote Link to comment Share on other sites More sharing options...
KintaRo Posted January 7, 2014 Share Posted January 7, 2014 Neyse benim işimi de kolaylaştırdın, eline sağlık :) Quote Link to comment Share on other sites More sharing options...
DivaneTR Posted January 8, 2014 Share Posted January 8, 2014 Plugin haline getireydin ya :) Ellerine sağlık. Quote Link to comment Share on other sites More sharing options...
Editor pairs Posted January 11, 2014 Author Editor Share Posted January 11, 2014 Plugin haline getireydin ya :) Ellerine sağlık. Plugin haline getiriyordum sonradan düşündüm kod yapısını kodların ne işe yaradığını görerek belki bizden daha iyisini yapacak birileri çıkar diye düşündüm. Quote Link to comment Share on other sites More sharing options...
Wextron Posted January 30, 2014 Share Posted January 30, 2014 Örnek proje varmı rica etsem paylaşabilir misiniz ? Quote Link to comment Share on other sites More sharing options...
sevcankonak Posted February 12, 2014 Share Posted February 12, 2014 Paris hocam bizim gibi apz fakirlerini sevindirmek için örnek bir proje paylaşma şansınız varmı acaba Quote Link to comment Share on other sites More sharing options...
Editor pairs Posted March 5, 2014 Author Editor Share Posted March 5, 2014 Örnek proje eklenmiştir. Quote Link to comment Share on other sites More sharing options...
neccooy Posted March 7, 2014 Share Posted March 7, 2014 office 2013 var, versiyonla mı alakalı acaba? Quote Link to comment Share on other sites More sharing options...
Editor pairs Posted March 7, 2014 Author Editor Share Posted March 7, 2014 (edited) Provider=Microsoft.Jet.Oledb.4.0; Provider kısmını bu şekilde düzeltip denermisiniz. Edit : Düzelmezse bunu kurup yine kodların eski haliyle tekrar deneyin.C# ta bu şekilde çözmüştüm sorunu. http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=23734 Edited March 7, 2014 by pairs Quote Link to comment Share on other sites More sharing options...
neccooy Posted March 7, 2014 Share Posted March 7, 2014 Kodu değiştirince aşağıdaki hatayı verdi ama "2007 Office System Driver: Data Connectivity Components" yükleyince sorun giderildi. Teşekkürler... Quote Link to comment Share on other sites More sharing options...
sevcankonak Posted March 7, 2014 Share Posted March 7, 2014 Ellerine saglık hocam veri çekmede başarılı oldu projeye verileri nasıl kaydederiz acaba Quote Link to comment Share on other sites More sharing options...
Editor pairs Posted March 8, 2014 Author Editor Share Posted March 8, 2014 Kaydetmek için excel plugini var onu kullanabilirsiniz. Başka birşey istiyorssanız yardımcı olayım. Quote Link to comment Share on other sites More sharing options...
neccooy Posted March 8, 2014 Share Posted March 8, 2014 Kaydetmek için excel plugini var onu kullanabilirsiniz. Başka birşey istiyorssanız yardımcı olayım. Kaydetmek için Excel plugini forumda aradım ama bulamadım, varsa paylaşır mısınız? Quote Link to comment Share on other sites More sharing options...
Editor pairs Posted March 8, 2014 Author Editor Share Posted March 8, 2014 http://www.tnctr.com/topic/231481-excel-plugin-excele-rapor-alyn/ Quote Link to comment Share on other sites More sharing options...
yakupnun Posted October 20, 2014 Share Posted October 20, 2014 peki bu excel idosyası ağ da başka bir makinada ise ve butona tıklayınca dosya seçmeden belirlediğim dosyayı seçmesini istiyorsam nasıl olacak Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.