Ga naar inhoud

Excel

Een veel gebruikte applicatie door iedereen is Microsoft Excel. Het programma bestaat al geruime tijd en wordt nog steeds door velen gebruikt.

De codes die te vinden zijn op deze pagina zijn gericht op het uitlezen/aanpassen van Excel-bestanden (.xls en .xlsx).

Benodigheden

Om daadwerkelijk Excel bestanden te kunnen verwerken heb je de applicatie Microsoft Excel nodig.

Door de applicatie te hebben geïnstalleerd op de machine waarop je werkt krijg je toegang tot de referentie die je nodig hebt om de Excel applicatie en bestanden aan te kunnen sturen.

Toevoegen van de referentie

Om de Excel applicatie en bestanden te kunnen aanspreken moet de referentie tot de uitbreidingextensie (DLL) worden toegevoegd aan het script.

RoboPlatform

Via de tab 'References' kun je klikken op de knop 'GAC Assemblies' welke je toegang geeft tot de standaard referenties op de machine. Zoek en selecteer hier de referentie Microsoft.Office.Interop.Excel.dll (mocht er meer dan één referentie naar voor komen, selecteer dan de nieuwste).

Visual Studio

Via de 'Solution Explorer' kun je via het project referenties toevoegen. Dit doe je door op de rechter muisknop te drukken op het project en via de contextmenu naar 'Add' te gaan en vervolgens op 'Reference' te klikken.

Er volgt een popup. Ga in het menu naar 'COM' en zoek hier op de referentie 'Microsoft Excel'. Je krijgt een resultaat vergelijkbaar aan `Microsoft Excel 16.0 Object Library'. Selecteer indien meer dan één aanwezig de nieuwste versie.

Referentie

Om daadwerkelijk gebruik te kunnen maken van de uitbreidingsextensie moet je de referentie toevoegen aan je script. Voeg aan het begin van je script de volgende regel toe:

using Excel = Microsoft.Office.Interop.Excel;

Applicatie

Om Excel-bestanden te kunnen manipuleren heb je het applicatie object nodig in je script. Vanuit deze applicatie kun je de bestanden openen, maken en aanspreken.

Zo maak je het applicatie object:

Excel.Applicatie app = new Excel.Applicatie();

Standaard zie je de Excel applicatie niet op je bureaublad, maar dit kun je aanpassen door de volgende regel:

app.Visible = true;

Sluiten van een applicatie

Nadat je klaar bent met het aanspreken van de bestanden wil je de applicatie niet meer open hebben staan. Dit doe je met de volgende code:

app.Quit();

Werkmap

Een werkmap is een Excel-bestand. Denk hierbij aan de bestanden die je het meest gebruikt met Excel, .csv en .xlsx.

Een nieuwe werkmap maken

Als je een nieuw Excel-bestand wilt maken heb je een werkmap nodig. Deze maak je zo:

Excel.Workbook workbook = app.Workbooks.Add();

Een bestaand werkmap openen

Het kan ook voorkomen dat je juist een Excel-bestand moet uitlezen of aanpassen waardoor je een werkmap moet openen. Het openen van een werkmap doe je zo:

Excel.Workbook workbook = app.Workbooks.Open(@"file.xlsx");

Een werkmap opslaan

Nadat je aanpassingen hebt gemaakt aan de werkmap wil je deze vaak opslaan. Dit doe je op de volgende manier:

workbook.Save();

Het kan ook zijn dat je een bestand voor de eerste keer gaat opslaan wanneer deze nog niet bestaat op je schijf. Deze zul je dan moeten opslaan als een bestand voordat je deze zomaar kunt opslaan.

Ook kan het zijn dat je de werkmap wilt opslaan als een nieuw bestand. Dit doe je op dezelfde manier:

workbook.SaveAs(@"file.xlsx");

Je kunt ook het type van de werkmap bepalen door het formaat op te geven:

workbook.SaveAs(@"file.csv", Excel.XlFileFormat.xlCSV);

Een werkmap sluiten

Nadat je klaar bent met het aanspreken van de werkmap moet je het bestand sluiten. Wanneer je het bestand niet sluit kan deze niet meer worden bewerkt door anderen doordat er bij het openen van het bestand wordt aangegeven dat het bestand is geopend door een andere persoon of is gelocked door een proces.

Zo sluit je het bestand:

workbook.Close(false);

Door het woordje false op te geven in de method .close() worden de aangepaste wijzigingen niet opgeslagen.

Werkblad

Een werblad is een blad binnen een werkmap.

Een werkblad activeren

Om een werkblad actief te maken moet je deze activeren. Dit doe je zo:

Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[name];
worksheet.Activate();

Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[index];
worksheet.Activate();

Je kunt een werkblad selecteren aan de hand van de naam, maar ook aan de hand van de index.

Een nieuw werkblad maken

Zo maak je een nieuw werkblad:

Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.Add();

De naam van een werkblad wijzigen

Zodra je een werkblad hebt geselecteerd kun je de naam van het werkblad wijzigen.

worksheet.Name = "New name";

Je kunt een werkblad selecteren door de naam of index - net zoals het activeren.

Een werkblad verwijderen

Het kan ook zo zijn dat je een werkblad wilt verwijderen van een werkmap. Dit doe je op de volgende manier:

worksheet.Delete();

Je kunt een werkblad selecteren door de naam of index - net zoals het activeren.

Cel

Een cel is een vakje binnen het werkblad (bijvoorbeeld A1).

Een cel verkrijgen

Om een cell te kunnen lezen/aanpassen moet je deze zien op te halen uit je werkblad. In dit voorbeeld halen wij de cel A1 aan de hand van een index op:

Excel.Range cell = (Excel.Range)worksheet.Cells[1, 1];

Je kunt ook een cell ophalen aan de hand van de naam:

Excel.Range cell = worksheet.Range["A1"];

De waarde van een cel verkrijgen

Je kunt de waarde van een cel op ophalen door middel van de property .Text:

string val = (string)cell.Text;

Door de property .Text te gebruiken krijg je de waarde van de cell welke je ziet wanneer je de werkmap hebt geopend. Het getal 236277145 wordt afgekort in een werkmap naar 2,36E+08 wanneer de kolom te klein is. Hierdoor krijg je in de variabele val de waarde 2,36E+08 in plaats van 236277145.

Dit valt te omzeilen door de cell door middel van de functie .AutoFit() te gebruiken. Hierdoor wordt de cel vergroot zodat de tekst van de cel geheel te zien is in de cel.

De waarde in een cel zetten

Je kunt een waarde in een cel zetten door middel van de property .Value:

cell.Value = "New value";

Wanneer je bijvoorbeeld een datum opgeeft als nieuwe waarde dan kan het zijn dat het formaat van de datum niet wordt herkend door Excel waardoor je niet (goed) kunt filteren op de datum. Dit kun je oplossen door het volgende te gebruiken:

cell.Value = date.ToOADate();
cell.NumberFormat = "dd/MM/yyyy";

De variabele date in dit voorbeeld is altijd een object van het type System.DateTime.

Kopiëren

Het kan zijn dat je een cel wilt kopiëren naar een andere cel (in bijvoorbeeld een ander werkmap of werkblad). Hiervoor moet je beide cellen hebben opgehaald.

Excel.Range cell1 = (Excel.Range)worksheet.Cells[1, 1];
Excel.Range cell2 = (Excel.Range)worksheet.Cells[2, 1];
cell1.Copy(cell2);

Formules

Als je getallen wijzigt in een bestand waar vervolgens berekeningen mee gedaan worden wil je graag gebruik maken van de formules binnen Excel zodat je zelf niet hoeft te rekenen.

cell.Value = "=SUM(A2:A4)";

Cel opties

Size

cell.Font.Size = 12;

Bold

cell.Font.Bold = true;

Italic

cell.Font.Italic = true;

Underline

cell.Font.Underline = true;

Strikethrough

cell.Font.Strikethrough = true;

Color

cell.Font.Color = System.Drawing.ColorTranslator.FromHtml("#ff0000");

Background color

cell.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#0000ff");

Border

Het kan best zijn dat je graag een border hebt om de cell. Standaard is een border de dunste lijn die beschikbaar is (Excel.XlBorderWeight.xlThin).

Je kan de border doen verschijnen door er een kleur aan te geven of door de dikte te zetten.

cell.Borders.Weight = Excel.XlBorderWeight.xlThin;

cell.Borders.Color = System.Drawing.ColorTranslator.FromHtml("#000000");

Je kan ook een border aan één of meerdere zijdes zetten. Echter moet je deze wel allemaal individueel aanspreken en zetten. Je hebt in totaal 8 mogelijke borders die je kunt zetten.

// Alle borders
Excel.XlBordersIndex.xlDiagonalDown         //  \   = Schuin van linksboven naar rechtsonder
Excel.XlBordersIndex.xlDiagonalDown         //  /   = Schuin van linksonder naar rechtsboven
Excel.XlBordersIndex.xlEdgeBottom           //  _   = Onder
Excel.XlBordersIndex.xlEdgeLeft             // |    = Links
Excel.XlBordersIndex.xlEdgeRight            //   |  = Rechts
Excel.XlBordersIndex.xlEdgeTop              //  ‾   = Boven
Excel.XlBordersIndex.xlInsideHorizontal     //  —   = Horizontaal door het midden
Excel.XlBordersIndex.xlInsideVertical       //  |   = Verticaal door het midden

cell.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;

Filter

Je kunt ook een filter toevoegen aan een kolom. In onderstaan voorbeeld voeg je alleen de knop toe voor het filteren.

cell.AutoFilter(1);

Je wilt natuurlijk ook kunnen filteren op een bepaalde waarde. Dit kan zo:

cell.AutoFilter(1, "Zoek criteria");

Beschermen/Blokkeren

Het kan voorkomen dat je een cell, een werkblad of een werkmap wilt blokkeren/beschermen. Standaard worden alle cellen geblokkeerd zodra je een werkblad gaat beschermen. Dit kun je simpel aanpassen door alle cellen binnen het werkblad niet te laten blokkeren.

worksheet.Cells.Locked = false;

Vervolgens kun je voor elke cell die je wilt blokkeren deze blokkeren.

cell.Locked = true;

Stel je wilt juist alle cellen hebben geblokkeerd en een aantal niet, dan vervang je de true naar false en de false naar true in de bovenstaande voorbeelden.

Vervolgens kom je op het punt dat je het werkblad wilt blokkeren. Hierbij kun je een wachtwoord zetten op het werkblad zodat je het werkblad alsnog kunt bewerken.

worksheet.Protect();                // Zonder wachtwoord
worksheet.Protect("password");      // Met wachtwoord

Het kan ook zijn dat je de gebruikers geen bladen wil laten verwijderen/aanmaken/bewerken.

workbook.Protect();                // Zonder wachtwoord
workbook.Protect("password");      // Met wachtwoord

Voorbeeld code

Een Excel-bestand uitlezen

Voor het uitlezen van een Excel werkmap maken wij altijd gebruik van de class 'Header':

public class Header
{
    /// <summary>
    /// The column number of the header.
    /// </summary>
    public int Column { get; set; }

    /// <summary>
    /// The text of the header.
    /// </summary>
    public string Title { get; set; }
}

Vervolgens lezen wij de daadwerkelijke werkmap als volgt uit:

// Open the file
Application app = new Application();

// Open workbook
Workbook workbook = app.Workbooks.Open(@"file\to\open.xlsx");

// Get active worksheet
Worksheet worksheet = (Worksheet)workbook.ActiveSheet;

// Headers in the document
List<Header> headers = new List<Header>();

// The start cell of the headers
int row = 1;
int column = 1;

// Get headers
while (true)
{
    // Get header
    Range header = (Range)worksheet.Cells[row, column];

    // AutoFit cell
    header.EntireColumn.AutoFit();
    header.EntireRow.AutoFit();

    // Get text
    string text = ((string)header.Text)?.ToLower()?.Trim();

    // If no header, stop loop
    if (string.IsNullOrWhiteSpace(text)) break;

    // Add header
    headers.Add(new Header
    {
        Column = column,
        Title = text
    });

    // Increase column
    column++;
}

// Get necessary headers
Header hHeader1 = headers.FirstOrDefault(x => x.Title == "header1");
Header hHeader2 = headers.FirstOrDefault(x => x.Title == "header2");
Header hHeader3 = headers.FirstOrDefault(x => x.Title == "header3");

// Check if all headers are found
if (hHeader1 == null) throw new MissingHeaderException("Cannot find the header 'header1'");
if (hHeader2 == null) throw new MissingHeaderException("Cannot find the header 'header2'");
if (hHeader3 == null) throw new MissingHeaderException("Cannot find the header 'header3'");

// The start row of the workbook
row = 2;

// Go through all the rows
while (true)
{
    // Get values
    Range rHeader1 = (Range)worksheet.Cells[row, hHeader1.Column];
    Range rHeader2 = (Range)worksheet.Cells[row, hHeader2.Column];
    Range rHeader3 = (Range)worksheet.Cells[row, hHeader3.Column];

    // AutoFit row
    rHeader1.EntireRow.AutoFit();

    // Stop loop (make sure that this column is always filled)
    if (string.IsNullOrWhiteSpace((string)rAanvrager.Text)) break;

    // Do something with the values

    // Increase row
    row++;
}

// Close worksheet
worksheet = null;

// Close workbook
workbook.Close(0);
workbook = null;

// Close Excel
app.Quit();
app = null;