Det er et af skæbnens lunefulde tvist, at da Microsoft i 1984 lancerede Excel, var det designet til at arbejde på Macintosh! Først tre år senere kom den første version til Windows. Nu er Excel den gyldne standard i regnearkenes verden, og programmet har endda opnået den tvivlsomme ære sprogligt at blive pseudonym for New Public Management, hvor “Excel-drengene” styrer de varme hænder med deres “Excel-regneark”.
EPPLUS er et fremragende .NET-bibliotek, der på de dage, hvor man har brug for at smide data fra kode til Excel eller omvendt, opleves som intet mindre end en af Guds gaver til menneskeheden. EPPLUS hjælper med at skrive og læse avancerede Excel-filer og bruger Office Open XML format (xlsx) uden at være afhængig af interop eller at Office-pakken eller Excel er installeret.
PM> Install-Package EPPlus -Version 4.5.3.2Det er måske nok en mild overdrivelse at påstå, at hvad man kan gøre i Excel kan man også gøre via EPPLUS fra C# – men det er tæt på.
Jeg har med begejstring brugt EPPLUS i flere år, men desværre er det altid med så tilpas store pauser, at jeg – sikkert fremskyndet af tidlig aldring – har glemt hvordan det nu lige er, man gør. Og her tilstøder den ulempe ved EPPLUS, at opdateret dokumentation er en mangelvare. – Og så går der tid med det.
Derfor besluttede jeg at gøre tilværelsen lidt lettere for mig selv, ved at pakke det ind i seks simple og selvindlysende funktioner, der opfylder 95% af de behov, jeg i almindelighed har ved eksport af data til Excel:
- Oprette Excel-fil
- Oprette et eller flere ark
- Smide data fra objekter eller liste af objekter over i tabeller i et eller flere ark
- Have mulighed for at placere flere tabeller på sammen ark på en pæn og ordentlig måde – arrangeret hosisontalt og/eller vertikalt
- Vælge data-serie fra tabellerne og få oprettet tilhørende grafer.
Opgaven er indkapslet i følgende funktioner, der altså kan kaldes en slags “Keep-It-Simple EPPLUS” eller “EPPLUS for Digitale Immigranter”:
var excel = new Excel("Output.xlsx");
Tilføj et nyt ark til filen og angiv om tabeller på arket i udgangspunktet skal placeres horisontalt eller vertikalt og angiv antal kolonner/rækker mellem tabellerne på arket:
var sheet = excel.AddSheet("Ordfrekvensliste",true, 1);
En AddChart med parametrene:
var chart = sheet.AddChart("ZIPF's LOV", eChartType.XYScatterLinesNoMarkers, "Log (Rank)", "Log (Hyppighed)",500, 500, logarithmic: true);
Tilføj nogle tabeller og data-serier til grafen. Tilføj evt. overskrift til tabellen og i komma-separeret streng navn på kolonner, der ikke skal medtages. Angiv evt. om kolonnebredde skal tilpasses indhold og hvilken af EPPLUS’ ca. 20 forskellige måder at style tabellen på, som skal anvendes.
foreach (var book in books)
{
//Indlæs tekst
var text = PreProcessText(Path.Combine(@"..\..\Tekster\", book.Filename));
//Hug det op i ord, fjern stopord? Nej tak
var words = GetWords(text, false);
//Dan ord-frekvensliste med tre decimaler
List<WordRank> wordFrequency = GetWordFrequencyList(words, 3);
//Tilføj tabel med ord-frekvenslisten til arket
sheet.AddTable($"{book.Title}", wordFrequency);
//Tilføj data-serie til grafen, hent label fra kolonnen "Rank", og Data fra kolonnen "Pct".
sheet.AddSeries(chart, "Rank", "Pct");
}
Så er der bare tilbage at gemme Excel-filen og eventuelt vælge at få den åbnet i Excel med det samme:
excel.Save(openInExcel:true);
Ovenstående få kodelinjer giver det ønskede resultat:
Der er naturligvis stadig mulighed for at anvende nødvendige guldkorn fra EPPLUS, f.eks:
chart.Legend.Position = eLegendPosition.Top;
chart.XAxis.LogBase = 10;
chart.YAxis.LogBase = 10;
chart.XAxis.MaxValue = 20000;
Hele koden følger her. Data omdannes først til CSV-streng, hvilket udelukkende skyldes, at jeg har brug for det format i anden sammenhæng. Er man performance-høg eller blot et menneske med særlige behov, så kan der sikkert spares et split-sekund ved at anvende LoadFromCollection i stedet for LoadFromText.
public static string ToCsv<T>(IEnumerable<T> items, string excludedFields = "", bool changeDecimalSeparator = true)
{
// Sikrer at decimal-separator er ændret fra komma til punktum for korrekt export til Excel
if (changeDecimalSeparator)
{
CultureInfo customCulture = (CultureInfo)Thread.CurrentThread.CurrentCulture.Clone();
customCulture.NumberFormat.NumberDecimalSeparator = ".";
Thread.CurrentThread.CurrentCulture = customCulture;
}
var sb = new StringBuilder();
var itemType = typeof(T);
var props = itemType.GetProperties();
var excluded = excludedFields.Replace(" ", "").ToLower();
var headers = String.Join(";", props.Select(p => p.Name).Where(p => !excluded.Contains(p.ToLower())));
sb.AppendLine(headers);
foreach (var item in items)
{
try
{
var line = String.Join(";",
props.Where(p => !excluded.Contains(p.Name.ToLower())).Select(pp => pp.GetValue(item, null)));
if (line.Trim() != "") sb.AppendLine(line);
}
catch (Exception)
{
}
}
var x = sb.ToString();
return sb.ToString();
}
public class Excel
{
public string FileName { get; set; }
public ExcelPackage Package { get; set; }
public Excel(string fileName)
{
Package = new ExcelPackage(new FileInfo(fileName));
FileName = fileName;
}
public ExcelSheet AddSheet(string sheetName, bool placeTablesHorizontal, int spaceBetweenTables)
{
var sheet = new ExcelSheet(sheetName, placeTablesHorizontal, spaceBetweenTables);
var existingSheet = Package.Workbook.Worksheets.SingleOrDefault(x => x.Name == sheetName);
if (existingSheet != null) Package.Workbook.Worksheets.Delete(existingSheet);
sheet.WorkSheet = Package.Workbook.Worksheets.Add(sheetName);
return sheet;
}
public void Save(bool openInExcel)
{
try
{
Package.Save();
if (openInExcel) Process.Start(FileName);
}
catch (Exception)
{
Console.WriteLine("Problemer med at gemme excel-filen. Det kan skyldes, at en tidligere version allerede er åbnet i Excel.");
}
}
}
public class ExcelSheet
{
public ExcelWorksheet WorkSheet { get; set; }
public bool PlaceTablesHorizontal { get; set; } = true;
public int PositionHorizontal { get; set; } = 1;
public int PositionVertical { get; set; } = 1;
private int SpaceBetweenTables { get; set; }
private ExcelRangeBase LastDataRange { get; set; }
public ExcelSheet(string sheetName, bool placeTablesHorizontal = true, int spaceBetweenTables = 1)
{
PlaceTablesHorizontal = placeTablesHorizontal;
PositionHorizontal = 1;
PositionVertical = 1;
SpaceBetweenTables = spaceBetweenTables;
}
public ExcelChart AddChart(string chartTitle, eChartType chartType = eChartType.LineMarkers, string XLabel = "", string YLabel = "", int width = 300, int height = 300, bool logarithmic = false)
{
var ec = WorkSheet.Drawings.AddChart(chartTitle, chartType);
ec.SetPosition(1, 0, 3, 0);
ec.SetSize(width, height);
ec.XAxis.Title.Text = XLabel;
ec.YAxis.Title.Text = YLabel;
ec.Legend.Position = eLegendPosition.Top;
ec.XAxis.Title.Font.Size = 12;
ec.YAxis.Title.Font.Size = 12;
ec.Title.Font.Bold = false;
ec.Title.Font.Size = 18;
ec.Title.Text = chartTitle;
if (logarithmic)
{
ec.XAxis.LogBase = 10;
ec.YAxis.LogBase = 10;
ec.XAxis.CrossesAt = 0;
}
return ec;
}
public ExcelChartSerie AddSeries(ExcelChart chart, string labelColumn, string dataColumn)
{
var range = LastDataRange;
var start = range.Start;
var end = range.End;
var labelColumnRange = default(ExcelRange);
var dataColumnRange = default(ExcelRange);
for (int i = 1; i <= end.Column; i++)
{
if (WorkSheet.Cells[start.Row, i].Value != null)
{
if (WorkSheet.Cells[start.Row, i].Value.ToString().ToLower() == labelColumn.ToLower())
{
labelColumnRange = WorkSheet.Cells[start.Row + 1, i, end.Row - 1, i];
}
if (WorkSheet.Cells[start.Row, i].Value.ToString().ToLower() == dataColumn.ToLower())
{
dataColumnRange = WorkSheet.Cells[start.Row + 1, i, end.Row - 1, i];
}
}
}
var serie = default(ExcelChartSerie);
if (labelColumnRange == null)
{
Console.WriteLine($"Kunne ikke finde Labelkolonne '{labelColumn}'");
}
if (dataColumnRange == null)
{
Console.WriteLine($"Kunne ikke finde Datakolonne '{dataColumn}'");
}
if ((labelColumnRange != null) && (dataColumnRange != null))
{
serie = chart.Series.Add(dataColumnRange, labelColumnRange);
serie.Header = WorkSheet.Cells[start.Row - 1, start.Column].Value.ToString();
}
return serie;
}
public void AddTable<T>(string tableHeader, IEnumerable<T> items, string excludedColumns = "",
bool autoFitColumns = true, TableStyles style = TableStyles.Medium4)
{
var data = Export.ToCsv(items, excludedColumns);
ExcelRangeBase dataRange;
var format = new ExcelTextFormat() { Delimiter = ';' };
if (PlaceTablesHorizontal)
{
WorkSheet.Cells[1, PositionHorizontal].Value = tableHeader;
WorkSheet.Cells[1, PositionHorizontal].Style.Font.Bold = true;
dataRange = WorkSheet.Cells[2, PositionHorizontal].LoadFromText(data, format, style, true);
PositionHorizontal += dataRange.Columns + SpaceBetweenTables;
if (dataRange.Rows + SpaceBetweenTables > PositionVertical)
PositionVertical += dataRange.Rows + SpaceBetweenTables;
}
else
{
WorkSheet.Cells[PositionVertical, 1].Value = tableHeader;
WorkSheet.Cells[PositionVertical, 1].Style.Font.Bold = true;
dataRange = WorkSheet.Cells[PositionVertical + 1, 1].LoadFromText(data, format, style, true);
if (dataRange.Columns + SpaceBetweenTables > PositionHorizontal)
PositionHorizontal += dataRange.Columns + SpaceBetweenTables;
PositionVertical += dataRange.Rows + SpaceBetweenTables;
}
LastDataRange = dataRange;
if (autoFitColumns) dataRange.AutoFitColumns();
}
}