SKRS E-Reçete İlaç Listesi adresindeki excel dosyalarını indirmek ve MSSQL veritabanına atmak isitiyorum.
Adım adım ne nasıl yapılıyor bakalım.
İnternet sayfasından dosya adreslerini bulup indirmek
Bir web sayfasını indirmek istiytorsanız yöntem çok:
WebClient Class,
HttpRequest Class
Ben yaygın olan WebClient'ı kullandım:
string sDomain = "http://www.iegm.gov.tr/";
string sPage = "Default.aspx?sayfa=erecete_liste&lang=tr-TR";
WebClient wc = new WebClient();
// sayfa UTF8 kodlamasıyla yayında olsun
wc.Encoding = Encoding.UTF8;
string s = wc.DownloadString(sDomain + sPage);
Html içindeki bir elementi bulmak istiyorsanız en kestirmeden
Parsing HTML Tags in C# makalesindeki kodu uygulayabilirsiniz.
HtmlTag tag;
var parse = new HtmlParser(s);
while (parse.ParseNext("a", out tag))
{
string value;
if (tag.Attributes.TryGetValue("href", out value))
{
// Excel dosyalarının isimlerindeki ortak metin
if (value.IndexOf("farmasotik_urunler_listesi") > 0)
{
Uri uri = new Uri(sDomain + value);
// URL üzerinde dosya adını split etilmiş Segmenets özelliğinin sonunda bulacağız.
wc.DownloadFile(uri, @"c:\Temp\" + uri.Segments[uri.Segments.Length - 1]);
}
}
}
Dosyalar indiğinde bir excel dosyasına bakalım ve yapısını görelim:
Excel içindeki verileri çekip VT'ye aktarma
Provider sağlam olmalı. Eğer
*.xlsx dosyalarıyla boğuşmak istiyorsanız
Microsoft Access Database Engine 2010 Redistributable indirmeli ve kurulmalı.
Excel dosyasına az önce kurduğumuz artık
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\klasorAdi\dosyaAdi;Extended Properties="Excel 8.0;HDR=YES;IMEX=1;" bağlantı cümlesi ile bağlanabiliriz. Bunun için
OleDbAdapter sınıfını kullanacağız.
Excel içinde her bir sheet içindeki satırları
SELECT * FROM ["sheetAdı"$] ifadesi ile bir DataTable'a çekebiliriz.
public static DataTable f_SheetToDataTable(string _sExcelPath, string _sSheetName)
{
// OleDbAdaptor'de oluşturduğumuz nesneneyle excel dosyamıza bağlanmak için kullanacağımız bağlantı cümlesi
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", _sExcelPath);
// Excel içinde çalıştırmak istediğimi sorgumuz
string query = String.Format("select * from [{0}$]", _sSheetName);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
DataTable dataSet = new DataSet();
// Sonucu DataTable içine atalım.
var dt = new DataTable();
dataAdapter.Fill(dt);
return dt;
}
}
DataTable ile dönen bilgilerimizi şimdide veri tabanına aktaralım. Ama önce tabloyu oluşturup bu tablo için otomatik insert ifadelerini oluşturacak şekile getirelim.
CREATE TABLE [dbo].[IlaclarAktif](
[id] [int] IDENTITY(1,1) NOT NULL,
[ad] [nvarchar](255) NULL,
[Barkodb] [float] NULL,
[firma] [nvarchar](255) NULL,
[skrs_recete_turu] [nvarchar](255) NULL,
[skrs_durum] [nvarchar](255) NULL,
[aciklama] [nvarchar](255) NULL,
CONSTRAINT [PK_IlaclarAktif] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Tabloyu oluşturduktan sonra yansısını kodumuzda oluşturalım ki bize sql cümleleri oluşturtmak zorunda bırakmasın:
string connectionString = "Data Source=10.130.214.20,8586;Initial Catalog=cop;User Id=sa;Password=xxx;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// SQL cümlemizde maksadımız tablonun şemasını alabilmek. Bu yüzden tek satır veri çekiyoruz.
using (var adapter = new SqlDataAdapter("SELECT top(1) * FROM TabloAdi", connection))
{
using (var builder = new SqlCommandBuilder(adapter))
{
// adapter nesnenimizin xxxCommand ozelliklerini set etmek için:
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
}
}
}
Eğer
SqlCommandBuilder sınıfına ait bir nesne oluşturmazsanız InsertCommand oluşturulmamış olacaktır ve aşağıdaki hatayı alırsınız:
Update requires a valid InsertCommand when passed DataRow collection with new rows.
Bir DataTable nesnesi yaratıp içine bir kaç satır ekleyerek bunları VT'ye basacağız. Bunu yapmak için SqlDataAdapter nesnemizin
Update() metodunu çağıracağız.
DataTable dtExcel = f_SheetToDataTable("c:\\Temp\\ilacListesi.xls, "AKTİF ÜRÜNLER LİSTESİ");
string connectionString = "Data Source=10.130.214.20,8586;Initial Catalog=cop;User Id=sa;Password=xxx;";
using (var cnn = new SqlConnection(connectionString))
{
cnn.Open();
using (var adapter = new SqlDataAdapter("SELECT top(1) * FROM IlaclarAktif", cnn))
{
var dtIlaclarAktif = new DataTable("IlaclarAktif");
try
{
// Tablomuzun yapısını(şemasını) dataTable nesnemize aktaralım
adapter.Fill(dtIlaclarAktif);
// Excel'de ilk satır sutun adlarını içerdiği için Skip(1)
// Tablomuzda ilk sütun olan id alanı için her yeni satıra 0 değeri atıyor,
// dtExcel İçindeki satırları tablomuzdaki sıraya göre object[] tipinde arr nesnesinde oluşturuyor,
// VT Tablomuzun şemasını ve 1 satırını içeren dtIlaclarAktif tablomuzun satırlarına ekliyoruz.
var v = dtExcel.Rows.Cast().Skip(1).Select(
delegate(DataRow row)
{
var arr = new[] {0, row[0], row[1], row[2], row[3], row[4], row[5]};
dtIlaclarAktif.LoadDataRow(arr, LoadOption.Upsert);
return arr;
});
// InsertCommand'ın null olması sorun çıkartmasın diye SqlCommandBuilder sınıfından bir nesne yaratıyoruz
using (var builder = new SqlCommandBuilder(adapter))
{
adapter.Update(dtIlaclarAktif);
}
}
catch (Exception ex)
{
throw (ex);
}
}
}
Excel dosyasındaki bir sheet'i DataTable yapan metot budur:
public static DataTable f_SheetToDataTable(string _sExcelPath, string _sSheetName)
{
//
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", _sExcelPath);
string query = String.Format("select * from [{0}$]", _sSheetName);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
var dt = new DataTable();
dataAdapter.Fill(dt);
return dt;
}
Tüm kodu buraya yapıştırmak isterdim ama zaten siz nasıl olduğunu anladınız ve parçaları bir araya getirmek sizin için artık çocuk oyuncağı değil mi?
Projenin açık kaynak koduna:
http://code.google.com/p/skrs-erecete-ilac-listesi/
adresinden erişebilirsiniz.
DbContext ile Code First yöntemi ve LinqToExcel projesiyle yeniden yaptım
E tabi kod son derece küçüldü. Excel dosyalarını SQL'de tutma kararım uygulamaların taşınması esnasında veri kaybı yaşanmaması içindi.
Önce POCO(
Plain Old CLR Object) sınıfları:
ExcelDosya Sınıfı, VT'da excel dosyasını indirdiğimiz URL'yi, hangi tarihli ilaç listesi olduğunu ve excel dosyasını barındıracak.
using System;
using System.Configuration;
using System.Linq;
namespace ExcelToVT
{
public class ExcelDosya
{
public int id { get; set; }
public string Adres { get; set; }
public byte[] Dosya { get; set; }
public DateTime DosyaTarihi { get; set; }
public DateTime EklenmeTarihi { get; set; }
public void f_ToVT()
{
Kontext ktx = new Kontext(ConfigurationManager.ConnectionStrings["VtCnn"].ConnectionString);
var a = ktx.ExcelDosyalari.Where(e => e.Adres.Equals(this.Adres)).FirstOrDefault();
if (a == null)
{
ktx.SaveChanges();
}
}
}
}f_ToVT() metodu hızlı bir şekilde bu dosya yoksa VT'na ekleme işini yapıyor.
Ilac sınıfında excel içinden çekilen satırların VT'na aktarımını(varsa ve değişmişse güncellemesini, yoksa eklenmesini) sağlayacak.
using System.Configuration;
using System.Linq;
namespace ExcelToVT
{
public class Ilac
{
public int id { get; set; }
public string skrs_ilac_adi { get; set; }
public decimal skrs_barkod { get; set; }
public string skrs_firma { get; set; }
public string skrs_recete_turu { get; set; }
public string skrs_durum { get; set; }
public string Aciklama { get; set; }
public Ilac() { }
public override bool Equals(object _iegm)
{
var iegm = _iegm as IEGMIlac;
if (iegm == null)
{
return false;
}
bool bDegisti = iegm.skrs_ilac_adi.Equals(this.skrs_ilac_adi) &&
iegm.skrs_durum.Equals(this.skrs_durum) &&
iegm.skrs_firma.Equals(this.skrs_firma) &&
iegm.skrs_recete_turu.Equals(this.skrs_recete_turu) &&
iegm.Aciklama.Equals(this.Aciklama);
if (!bDegisti)
{
this.skrs_ilac_adi = iegm.skrs_ilac_adi;
this.skrs_durum = iegm.skrs_durum;
this.skrs_firma = iegm.skrs_firma;
this.skrs_recete_turu = iegm.skrs_recete_turu;
this.Aciklama = iegm.Aciklama;
}
return !bDegisti;
}
public Ilac(string _sCnnStr, IEGMIlac _iegm)
{
Kontext ktx = new Kontext(ConfigurationManager.ConnectionStrings[_sCnnStr].ConnectionString);
Ilac ilac = ktx.Ilaclar.FirstOrDefault(k => k.skrs_barkod.Equals(_iegm.skrs_barkod));
if (ilac == null)
{
this.skrs_barkod = _iegm.skrs_barkod;
this.skrs_durum = _iegm.skrs_durum;
this.skrs_firma = _iegm.skrs_firma;
this.skrs_ilac_adi = _iegm.skrs_ilac_adi;
this.skrs_recete_turu = _iegm.skrs_recete_turu;
this.Aciklama = _iegm.Aciklama;
ktx.Ilaclar.Add(this);
int a = ktx.SaveChanges();
}
else
{
if (ilac.Equals(_iegm))
{
ktx.SaveChanges();
}
}
}
}
}
IEGMIlac sınıfı excel içindeki her satır ilacın önce geçici bir nesneye aktarılmasını, ardından VT'na aktarımında
Ilac sınıfından nesne oluşturmada yapıcı metoda parametre olarak geçirilmede kullanılacak.
namespace ExcelToVT
{
public class IEGMIlac
{
public int id { get; set; }
public string skrs_ilac_adi { get; set; }
public decimal skrs_barkod { get; set; }
public string skrs_firma { get; set; }
public string skrs_recete_turu { get; set; }
public string skrs_durum { get; set; }
public string Aciklama { get; set; }
}
}
Code First için DbContext sınıfından bir türetme yapmanız ve VT yapınızı burada belirtmeniz gerekiyordu(önceki yazılarımda konuyla ilgili bilgi bulabilirsiniz):
using System.Data.Entity;
namespace ExcelToVT
{
public class Kontext : DbContext
{
public Kontext(string connectionString)
: base(connectionString) { }
protected override void OnModelCreating(DbModelBuilder mb)
{
mb.Entity().Map(p => p.ToTable("Ilaclar"));
mb.Entity().Property(p => p.id).HasColumnName("id").IsRequired();
mb.Entity().Property(p => p.skrs_barkod).HasColumnName("skrs_barkod").IsRequired();
mb.Entity().Property(p => p.skrs_durum).HasColumnName("skrs_durum");
mb.Entity().Property(p => p.skrs_firma).HasColumnName("skrs_firma");
mb.Entity().Property(p => p.skrs_ilac_adi).HasColumnName("skrs_ilac_adi");
mb.Entity().Property(p => p.skrs_recete_turu).HasColumnName("skrs_recete_turu");
mb.Entity().HasKey(p => p.id);
mb.Entity().Map(a => a.ToTable("ExcelDosyalari"));
mb.Entity().HasKey(a => a.id);
}
public DbSet Ilaclar { get; set; }
public DbSet ExcelDosyalari{ get; set; }
}
}
İşin toplu olarak yapıldığı
SKRS3ExcelToDB sınıf:
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using HtmlAgilityPack;
using LinqToExcel;
namespace ExcelToVT
{
static class ExtensionIEnumerable
{
///
/// IEnumerable tipinde dizi içinde parametrede verilen metoda dizi elemanlarını ve indeksini geçirir.
///
/// İçinde dönülecek dizinin tipi
/// İçinde dönülecek dizi /// Dizi elemanlarının ve elemanın indeks değerinin gönderileceği metot public static void ForEachIndex(this IEnumerable e, Action del)
{
var i = 0;
foreach (var cur in e)
{
del(cur, i);
}
}
}
public class SKRS3ExcelToDB
{
private string m_CnnStrName;
public SKRS3ExcelToDB(string _sConnectionStringName)
{
m_CnnStrName = _sConnectionStringName;
}
public FileInfo f_ToVT(ExcelDosya _excel, string _sSheetName = "AKTİF ÜRÜNLER LİSTESİ")
{
if (!Directory.Exists("c:\\Temp"))
{
throw new FileNotFoundException("C:\\Temp klasörü bulunamadı! Bu yüzden excel dosyası oluşturulamıyor ve sorgulanamıyor.");
}
string sExcelFileName = string.Format("C:\\Temp\\excel_{0}.xlsx", DateTime.Now.ToString("dd.MM.yyyy_hh.mm.ss"));
var fi = new FileInfo(sExcelFileName);
File.WriteAllBytes(fi.FullName,_excel.Dosya);
// Excel'deki verilerimizi DataTable nesnemiz dtExcel'e aktarıyoruz.
IQueryable dtExcel = f_SheetToDataTable(fi.FullName, _sSheetName);
var a = "";
dtExcel.AsEnumerable().ForEachIndex((g, h) =>
{
new Ilac(m_CnnStrName, g);
});
var b = from iegmIlac in dtExcel
select new[] {new Ilac(m_CnnStrName, iegmIlac)};
return fi;
}
public string f_SayfayiIndir(string _sUrl)
{
Uri uri = _sUrl != null
? new Uri(_sUrl)
: new Uri("http://www.iegm.gov.tr/Default.aspx?sayfa=erecete_liste&lang=tr-TR");
try
{
return new WebClient
{
Encoding = Encoding.UTF8
}.DownloadString(uri);
}
catch (Exception ex)
{
throw new Exception(String.Format("{0} Adresinden HTML kaynak kodu indirilirken istisna fırlatıldı.", uri.AbsoluteUri));
}
}
FileInfo f_DosyaIndir(Uri _Uri, DirectoryInfo _dirKaydedilecekKlasor)
{
try
{
string sDosyaYolu = _dirKaydedilecekKlasor.FullName + "\\" + _Uri.Segments[_Uri.Segments.Length - 1];
new WebClient().DownloadFile(_Uri, sDosyaYolu);
return new FileInfo(sDosyaYolu);
}
catch (Exception ex)
{
throw (ex);
}
}
public List f_DosyaIndir(string _sDomain, string _sHtmlCode, DirectoryInfo _dirKaydedilecekKlasor, string _sATaginda = "E-Reçete İlaç Listesi - ")
{
var doc = new HtmlDocument();
doc.LoadHtml(_sHtmlCode);
var ed = from lnks in doc.DocumentNode.Descendants()
where lnks.Name == "a"
&& lnks.InnerText.StartsWith(_sATaginda)
select
new ExcelDosya()
{
Adres = lnks.Attributes["href"].Value,
DosyaTarihi = Convert.ToDateTime(lnks.InnerText.Substring(lnks.InnerText.IndexOf(" - ") + 2).Trim(), new DateTimeFormatInfo() { ShortDatePattern = "dd.MM.yyyy" }),
EklenmeTarihi = DateTime.Now,
Dosya = File.ReadAllBytes(f_DosyaIndir(f_HrefToUri(_sDomain, lnks.Attributes["href"].Value), _dirKaydedilecekKlasor).FullName),
};
return ed.ToList();
}
private Uri f_HrefToUri(string _sDomain, string _sAdres)
{
return _sAdres.StartsWith("http")
? new Uri(_sAdres)
: new Uri(_sDomain.Insert(_sDomain.Length, "/") + _sAdres);
}
public IQueryable f_SheetToDataTable(string _sExcelPath, string _sSheetName)
{
var excel = new ExcelQueryFactory(_sExcelPath);
var aktif = from c in excel.WorksheetRangeNoHeader("A3", "F65000", _sSheetName)
select new IEGMIlac()
{
skrs_ilac_adi = c[0],
skrs_barkod = Convert.ToDecimal(c[1]),
skrs_firma = c[2],
skrs_recete_turu = c[3],
skrs_durum = c[4],
Aciklama = c[5],
};
return aktif;
}
public ExcelDosya f_SonExcelDosyasi()
{
List lstExcelDosyalari = f_DosyaIndir("http://www.iegm.gov.tr/", f_SayfayiIndir(null), new DirectoryInfo(@"c:\temp"));
if (lstExcelDosyalari.Count == 0)
{
throw new Exception("Excel dosyaları bulunamadı!");
}
Kontext ktx = new Kontext(m_CnnStrName);
lstExcelDosyalari.ForEachIndex((cur, idx) => ktx.ExcelDosyalari.Add(cur));
ktx.SaveChanges();
ExcelDosya sayfadakiSonExcel = lstExcelDosyalari.OrderByDescending(p => p.DosyaTarihi).FirstOrDefault();
return sayfadakiSonExcel;
}
}
}
Console Application olduğuna göre bir tetikleyiciye ihtiyacımız var o da:
using ExcelToVT;
namespace NameSpaceProgram
{
class Program
{
private static void Main(string[] args)
{
SKRS3ExcelToDB skrs = new SKRS3ExcelToDB("VtCnn");
ExcelDosya sayfadakiSonExcel = skrs.f_SonExcelDosyasi();
skrs.f_ToVT(sayfadakiSonExcel, "PASİF ÜRÜNLER LİSTESİ");
skrs.f_ToVT(sayfadakiSonExcel, "AKTİF ÜRÜNLER LİSTESİ");
}
}
}
Proje sayfasına yenisini güncelledim:
http://code.google.com/p/skrs-erecete-ilac-listesi/
