Jump to content
Son zamanlarda artan kullanıcı hesap hırsızlıkları sebebiyle tüm kullanıcılara şifre sıfırlama maili gönderilmiştir. Lütfen güveli şifreler seçiniz. Mevcut e-mail adresinize erişemiyorsanız, en aşağıdaki destek linkinden bize ulaşınız. ×

Excel'e Bağlanıp Veri Çekmek


pairs
 Share

Recommended Posts

  • Editor

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 by pairs
Link to comment
Share on other sites

  • Editor

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.

Link to comment
Share on other sites

  • 3 hafta sonra ...
  • 2 hafta sonra ...
  • 3 hafta sonra ...
  • Editor

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 by pairs
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

  • 7 ay sonra...

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...