Kezdőlap > .NET, C#, Excel, Office > Exportálás Excelbe

Exportálás Excelbe

2012. január 23. hétfő Hozzászólás Go to comments

Feladat ismertetése

Ebben a feladatban egy egyszerű WPF alkalmazást fogunk elkészíteni, amely Linq To SQL segítségével egy Microsoft SQL Serverből kérdez le adatokat. Ezeket az adatokat egy Data Gridben megjelenítjük, majd exportáljuk egy Excel fájlba, és készítünk hozzá egy grafikont.

Eredmény képekben

Az alkalmazás

image

Excel adatok

 

image

Előkészületek

Az alábbi két alfejezet azoknak szól, akiknek a gépén még nincs SQL Server, és SQL Server Management Studio telepítve, illetve a Northwind példa adatbázis sincs létrehozva.

Amennyiben az előbbi követelmények teljesülnek, ugorj tovább a következő fejezetre.

Adatbázisszerver telepítése

Ahhoz, hogy az alkalmazásunkat elkészítsük, szükségünk lesz egy adatbázisra is. Az adatbázis motorunk ebben az esetben a Microsoft SQL Server 2008 Express változata lesz. Ha esetleg nincs telepítve (Visual Studio 2010 eleve telepíti), akkor legegyszerűbben a Web Platform Installerrel tudjuk a telepítést elvégezni.

Web Platform Installert innen tudjuk letölteni.

Miután elindítottuk a mit a Web Platform installert, kattintsunk a Products fülre felül, majd a bal oldali menüben válasszuk ki a Database menüpontot. A listából válasszuk ki az SQL Server Express 2008 R2 változatát illetve az SQL Server 2008 R2 Management Studio Express változatát, és kattintsunk az Add gombra (ha már telepítve van, akkor az Installed üzenet fog fogadni). Miután hozzáadtuk a kívánt komponenseket, kattintsunk az Install gombra, és a varázslót végigkövetve feltelepül az SQL Server Express 2008 R2 és a Management Studio a gépünkre.

image

Adatbázis telepítése

Ha sikerült a telepítés, vagy már volt korábban is SQL Server a gépünkön, akkor már csak a példa adatbázis létrehozása maradt hátra. A feladatok elvégzéséhez mi a Northwind példa adatbázist fogjuk használni. Töltsük le a Northwind adatbázis SQL Scriptjét!

imageNorthwind példa adatbázis

SQL Script

Indítsuk el az SQL Server 2008 R2 Management Studio Express-t (vagy a számunkra megfelelő menedzsment felületet), majd jelentkezzünk be a helyi SQL Server-re az ábrán látható módon!

image

Amennyiben a bejelentkezés nem sikerül, abban az esetben vizsgáljuk meg a következő lehetőségeket:

·         Fut-e az SQL Server Express szolgáltatás (services.msc),

·         Van-e jogunk elérni az adatbázist,

·         Megfelelő címen és példány nevén akarjuk-e elérni az adatbázist,

·         Nincs-e valamilyen speciális tűzfal / proxy szabály, amely akadályozná a hozzáférést az SQL Serverhez?

Miután sikerült a bejelentkezés, kattintsunk a New Query gombra a bal felső sarokban, ekkorimage megjelenik a Management Studio SQL Script editorja. A File menü -> Open -> Open File menüpontjára kattintva tallózzuk ki a letöltött SQL Scriptet, és nyissuk meg! Ekkor az SQL Editorban fogjuk látni a teljes SQL Script tartalmát. Ezt követően nincs más dolgunk, mint az Execute gombra kattintani. A scipt lefut és létrehozza a Northwind adatbázist a példa adatokkal együtt.

image

Ha mindent jól csináltunk és nem kaptunk hibaüzenetet, akkor a bal oldali Object Explorerbe megjelenik az adatbázisunk. (Célszerű a Refresh gombra kattintani.)

Kliens alkalmazás elkészítése

Indítsuk el a Visual Studio 2010 Ultimate változatát! Kattintsunk File -> New -> Project… menüpontra, majd a megjelenő ablakban állítsuk be az alábbiakat:

A jobb oldali menüből válasszuk ki a Visual C# elemet azon belül is a Windows elemet. A template listából válasszuk ki a WPF Application-t, és adjuk neki az OfficeClientAppExportToExcel projekt nevet! (Figyeljünk arra, hogy a .NET Framework 4 legyen kiválasztva! A feladatokat meg lehet ugyan oldani .NET 3.5-tel is, csak sokkal körülményesebb módon!)

image

Megjelenik egy üres WPF-es ablak. Készítsük el az alábbi felhasználói felületet:
Helyezzünk fel egy gombot, amelynek a Content tulajdonsága az „Export To Excel” legyen, míg a neve a btnExport. Ezt követően helyezzünkel egy DataGrid-et, melynek a neve a dgResult legyen és az AutoGeneratedColumns tulajdonságát állítsuk „True”-ra.

image

El is készítettük a felhasználói felületünket. Bár nagyon egyszerű, de a jelenlegi példához nem is kell több. Itt az ideje elérni az adatbázist és lekérdezéseket megfogalmazni rajta. Ehhez mi a példa kedvéért Linq To SQL –t fogunk használni (használhatnánk Entity Framework-öt, vagy akár ADO.NET kapcsolat alapú lekérdezést is). Kattintsunk a Solution Explorer -ben (jobb oldalt) a projektünkre Add -> New Item!

A megjelenő ablakban válasszuk ki a LINQ To SQL Classes –t és adjuk neki az Nw.dbml nevet.

image

Ez egy speciális osztály, amely majd az adatbázisunk leképzéséért felel. Így Objektum Orientált módon tudunk majd az adatbázison lekérdezéseket megfogalmazni. (Tehát nem szöveg alapon fogjuk megmondani a lekérdezést, hogy SELECT * FROM Products hanem magába a C# nyelvbe beágyazva fogalmazzuk meg a lekérdezéseket. Így típus-biztosan tudunk lekérdezéseket megfogalmazni, valamint elkerüljük a futás időben történő lekérdezési hibákat is.)

A megjelenő ablakban kattintsunk a Server Explorer linkre, melynek hatására a bal oldalt megjelenik az adott bővítmény, a Server Explorerben pedig kattintsunk a Connect To DataBase menüpontra!

image

Ha először kapcsolódunk Server Explorer-ből az adatbázishoz megkérdezni, hogy milyen adatforráshoz szeretnénk csatlakozni, a Microsoft SQL Servert kell kiválasztani.

image

Ha már használtuk a Server Explorer-t, akkor a lentebbi kép fog minket fogadni, ahol meg kell adnunk az SQL Server nevét, ami ebben az esetben a .\SQLEXPRESS ( a helyi gépet jelöli, azaz a localhostot, az SQLEXPRESS pedig a példány nevét, ugyanis a többi adatbázis kezelő rendszerrel ellentétben az SQL Serverből egy gépen több példány is futhat). Miután megadtuk az SQL Szerver elérést a Select or enter a database name –nél válasszuk ki a Northwindet, majd kattintsunk az OK gombra!

image

Ekkor a Server Explorerben megjelenik a Northwind adatbázis. Bontsuk ki az elemeit, majd a Tables elemek közül fogjuk meg a Products táblát és helyezzük át O/R Designer ablakába. Ekkor az SQLMetal.exe a háttérben az adatbázis Products táblájából Entitás osztályokat generál, amelyeken lekérdezéseket tudunk megfogalmazni egyszerűen, objektum orientált módon.

image

Az előkészületek végére értünk, immár nincs más hátra, mint lekérdezni az adatokat, megjeleníteni őket majd az exportálást elkészíteni.

Első körben lekérdezzük az adatokat és megjelenítjük őket.

Kattintsunk a Solution Explorerben a MainWindow.xaml.cs fájlra és konstruktor előtt deklaráljuk a következő elemeket.

nwDataContext nwData;

List<Product> myProducts;

A nwDataContext fog az adatbázishoz történő kapcsolódásért és az adatbázison történő műveletekért felelni. A myProducts egy generikus lista amely Product-okat fog tartalmazni. Ez a lista fogja eltárolni a lekérdezés eredményét.

Ezt követően a konstruktorba írjuk a következőt:

nwData = new nwDataContext();

myProducts = (from c in nwData.Products

                select c).Take(20).ToList<Product>();

 

dgResult.ItemsSource = myProducts;

Itt példányosítjuk az nwDataContextet, majd egy Linq lekérdezést fogalmazunk meg, amely lekérdezi az első 20 terméket az adatbázisból, és a végén az eredményt egy generikus listává alakítjuk át. Miután az eredményt megkaptuk ideje megjeleníteni, így a dgResult ItemSource tulajdonságának átadjuk a myProducts -ot.

A teljes kód így néz ki:

nwDataContext nwData;

List<Product> myProducts;

public MainWindow()

{

    InitializeComponent();

    nwData = new nwDataContext();

    myProducts = (from c in nwData.Products

                    select c).Take(20).ToList<Product>();

 

    dgResult.ItemsSource = myProducts;

}

Indítsuk el az alkalmazást (F5)! Ha mindent jól csináltunk, akkor kb. az alábbi képernyő fog fogadni minket.

image

Ha hibaüzenet fogad nézzük át újra a lépéseket és javítsuk ki a hibánkat!

Eddig még csak az alkalmazásunk készült el, így itt az ideje az exportálást is megírni. Zárjuk be az alkalmazást, és kezdjük el megírni az Excelbe történő exportálást!

A Solution Expoloreren kattintsunk a MainWindow.Xaml-re és a felhasználói felületen kattintsunk kétszer az Export To Excel gombra! Ekkor betöltődik a gomb alapértelmezett eseményvezérlője, ami kb. így néz ki:

private void btnExport_Click(object sender, RoutedEventArgs e)

{

 

}

imageAhhoz, hogy elkezdjük kimenteni az adatokat Excelbe, szükségünk lesz a Microsoft.Office.Interop.Excel névtérre.

Kattintsunk jobb egérgombbal a Solution Explorerben található Refrences-re, a megjelenő helyi menüben pedig válasszuk, ki az Add Reference… menüpontot. A megjelenő ablakban válasszuk a .NET fület, és keressük ki a Microsof.Office.Interop.Excel komponens nevet (Lehetőség szerint a 14.0.0.0 verziót válasszuk ki), majd kattinsunk az OK gombra!

Érdemes megemlíteni, hogy C# 4.0 –tól a NoPIA (Primary Interop Assemblys) segítségével már imagenem a teljes interop-os dll kerül lefordításra, hanem csak azok a részek, amelyeket a projektünk során igénybe vettünk, így a publikálandó alkalmazás mérete is kisebb lett, valamint a deployolásnál bekövetkező hibák száma is csökkent.

image

Térjünk vissza a MainWindow.xaml.cs fájlhoz, és a usingok közé adjuk meg a Microsoft.Office.Interop.Excel –t, méghozzá úgy, hogy adunk a számára egy aliast is a könnyebb használat érdekében (főleg 4.0 előtt volt hasznos az interopnál az alias).

using Excel = Microsoft.Office.Interop.Excel;

A névtér betöltése után már elkezdhetjük használni az Excel funkcióit. Térjünk vissza az Export Button törtézsébe (btnExport), és adjuk hozzá a következő sorokat:

Excel.Application myExcel = new Excel.Application();

myExcel.Workbooks.Add();

myExcel.Visible = true;

Itt példányosítottunk egy Excel alkalmazást, amelyhez hozzáadtunk egy Workbook-ot,majd beállítottuk, hogy jelenjen meg ez a felhasználó számára. Ha most elindítanánk az alkalmazásunkat és rákatintanánk az Export To Excel gombra, csak egy üres excel munkafüzet fog betöltődni. A következőekben adunk hozzá tartalmat is.

Az előző kódsor alá adjuk a következőket:

myExcel.Cells[1, 1].Value2 = "ID";

myExcel.Cells[1, 2].Value2 = "Product Name";

myExcel.Cells[1, 3].Value2 = "Unit Price";

Itt az excelünk celláira hivatkozunk. Az első index a sornak az indexe, a második pedig az oszlopé. FONTOS megjegyezni, hogy a sorok és az oszlopok indexelése NEM nullától kezdődik hanem egytől!

A fentebbi kódsornál tehát az első sor első oszlopába írjuk az “ID” szöveget, a második oszlopba a “Product Name” szöveget és így tovább. Ne lepődjünk meg, hogy ha kapcsos zárójel bezárása után nem kapunk IntelliSense támogatást. Ez a C# 4.0-ban megjelent Dynamic (Dinamikus típus) miatt lehetséges (ha valamit elgépelünk, futás közben megkapjuk a hibaüzenetet) .

Most indítsuk el az alkalmazást, és kattintsunk az Export To Excel gombra! Ekkor egy, már minimális tartalommal feltöltött excel munkafüzetet kapunk.

image

Megjelennek a fejlécek, de a tartalom még nem, ráadásul a Product Name (B oszlop) nem is látszik rendesen. A következőkben adatokkal töltjük fel.

Térjünk vissza a kód nézethez és a legutóbbi sor alá adjuk hozzá a következő kódokat:

int rowIndex = 2;

 

foreach (var item in myProducts)

{

    myExcel.Cells[rowIndex, 1].Value2 = item.ProductID;

    myExcel.Cells[rowIndex, 2].Value2 = item.ProductName;

    myExcel.Cells[rowIndex, 3].Value2 = item.UnitPrice;

 

    rowIndex++;

}

Az alábbi kódnál egy Foreach ciklussal végigiterálunk a listánk elemein, és a megfelelő sor megfelelő oszlopába beírjuk az értékét. Csak a sor indexét kell növelnünk a ciklusban, értelemszerűen a rowIndex 2 -től indul, hisz az oszlopok és a sorok számozása 1-től indul, és az első sorba a korábban beírt fejlécek kerültek.

Próbáljuk ki most az alkalmazást! Most már az adatink is megjelennek az excelben.

image

Ez már jó is, de cifrázzuk kicsit tovább ezt az Excel fájlt. Látunk most magunk előtt 20 terméket, de tegyük fel, olyan excelt szeretnénk készíteni ami, megjelöli, hogy melyik termék az, amelyik drágább 15$ -nál.

Térjünk vissza a kódnézethez, és a rowIndex növelése elé írjuk be az alábbi kódrészletet.

Az alábbi sornál megvizsgáljuk, hogy az adott termék értéke nagyobb-e mint 15$, és ha igen, kiválasztom a cellát és az adott sor harmadik oszlopának háttérszínét beállítom pirosra (255).

if (item.UnitPrice > 15)

{

    var activeCell = myExcel.Cells;

    activeCell[rowIndex, 3].Interior.Color = 255;

}

Ha most indítjuk el az alkalmazást és exportálunk, akkor az alábbi nézet fog fogadni minket:

image

Most már kimentettük az adatokat, meg is formáztuk a meghatározott feltételek szerint. Talán csak annyi hibádzik, hogy az adatok ki is férjenek a cellába.

Térjünk vissza kódnézethez és a foreach ciklus után írjuk a következő kódot:

myExcel.Columns[1].AutoFit();

myExcel.Columns[2].AutoFit();

myExcel.Columns[3].AutoFit();

Ez a három sor már kevésbé szorul magyarázatra. Kijelöljük az adott oszlopot, és meghívjuk az AutoFit() metódusát. Így az eredmény már teljesen látható lesz.

image

Most már szépen kitudjuk menteni az adatokat egy Excel fájlba, és ha kell akkor tudjuk formázni is. Már majdnem kész a cél, egyedül a grafikon(Chart) készítés maradt hátra. Térjünk vissza kódunkhoz és az előző kódsor alá írjuk a következőket:

Excel.Range range = myExcel.Cells[1, 2];

Excel.Chart chart = myExcel.ActiveWorkbook.Charts.Add(After: myExcel.ActiveSheet);

chart.ChartWizard(Source: range.CurrentRegion, Title: "Termékek listája");

chart.ChartType = Excel.XlChartType.xl3DColumn;

Az első sorban pozícionálunk. A második sorban létrehozunk egy Chartot, ami az aktuális munkafüzet után helyezkedik el. A harmadik sorban meghívjuk a ChartWizard metódust, ahol megadjuk a grafikon forrását, valamint a grafikon címét (a C# 4.0 –tól a nevesíthetőek a paraméterek, ráadásul nem szükséges minden paramétert Type.Missing-gel kitölteni!) A negyedik (utolsó) sorban pedig megadjuk, hogy a grafikonunknak milyen legyen a típusa.

Itt az idő lefutatni az alkalmazást és kipróbálni az Exportálást!

image

Az eredmény pedig magáért beszél. A Sheet1-en vannak az exportált adatok, a Chart1-es munkafüzeten pedig a grafikonunk.

Ha hibaüzenet fogad nézzük át újra a lépéseket és javítsuk ki a hibánkat!

Látható tehát, hogy nem olyan bonyolult adatokat exportálni Excelbe. A korábbi C# verziók interop hiányosságait javítva a C# 4.0 sokat javult. Egyszerűsíti a munkánkat, és a mágikus Office világ is közelebb került így a fejlesztőkhöz.

A teljes kód:

private void btnExport_Click(object sender, RoutedEventArgs e)

{

    Excel.Application myExcel = new Excel.Application();

    myExcel.Workbooks.Add();

    myExcel.Visible = true;

 

    myExcel.Cells[1, 1].Value2 = "ID";

    myExcel.Cells[1, 2].Value2 = "Product Name";

    myExcel.Cells[1, 3].Value2 = "Unit Price";

 

    int rowIndex = 2;

 

    foreach (var item in myProducts)

    {

        myExcel.Cells[rowIndex, 1].Value2 = item.ProductID;

        myExcel.Cells[rowIndex, 2].Value2 = item.ProductName;

        myExcel.Cells[rowIndex, 3].Value2 = item.UnitPrice;

 

        if (item.UnitPrice > 15)

        {

            var activeCell = myExcel.Cells;

            activeCell[rowIndex, 3].Interior.Color = 255;

        }

        rowIndex++;

    }

    myExcel.Columns[1].AutoFit();

    myExcel.Columns[2].AutoFit();

    myExcel.Columns[3].AutoFit();

 

    Excel.Range range = myExcel.Cells[1, 2];

    Excel.Chart chart = myExcel.ActiveWorkbook.Charts.Add(After: myExcel.ActiveSheet);

    chart.ChartWizard(Source: range.CurrentRegion, Title: "Termékek listája");

    chart.ChartType = Excel.XlChartType.xl3DColumn;

}

Kategóriák:.NET, C#, Excel, Office Címke: , , , , ,
  1. 2012. január 23. hétfő - 12:34

    És ha nincsen excel-ed (ezen a gépen)? Vajon Open XML SDK-val mennyire lenne bonyolultabb/egyszerűbb az ügy?

  2. 2012. január 23. hétfő - 13:10

    Open XML SDK-val is megoldható. Lásd itt:
    http://devportal.hu/tv/Default.aspx?auth=0&sid=07402e30-4e0d-43fb-a0bd-65ae89972011
    és itt
    http://devportal.hu/tv/Default.aspx?auth=0&sid=29cd6a49-e26f-4c6a-bc11-ee6b15ca1e64

    Előbbi videó magáról az SDK-ról szól utóbbi a praktikumról. Am az Open XML SDK, nagyságrendekkel gyorsabb, mint az Interopos megoldás.

  1. No trackbacks yet.

Vélemény, hozzászólás?

Adatok megadása vagy bejelentkezés valamelyik ikonnal:

WordPress.com Logo

Hozzászólhat a WordPress.com felhasználói fiók használatával. Kilépés / Módosítás )

Twitter kép

Hozzászólhat a Twitter felhasználói fiók használatával. Kilépés / Módosítás )

Facebook kép

Hozzászólhat a Facebook felhasználói fiók használatával. Kilépés / Módosítás )

Google+ kép

Hozzászólhat a Google+ felhasználói fiók használatával. Kilépés / Módosítás )

Kapcsolódás: %s

%d blogger ezt kedveli: