/// <summary>
/// Shows a few different ways to load / save asynchronous
/// </summary>
/// <param name="connectionString">The connection string to the SQLite database</param>
public static async Task RunAsync(string connectionString)
{
var file = FileOutputUtil.GetFileInfo("03-AsyncAwait.xlsx");
using (ExcelPackage package = new ExcelPackage(file))
{
var ws = package.Workbook.Worksheets.Add("Sheet1");
using (var sqlConn = new SQLiteConnection(connectionString))
{
sqlConn.Open();
using (var sqlCmd = new SQLiteCommand("select CompanyName, [Name], Email, c.Country, o.OrderId, orderdate, ordervalue, currency from Customer c inner join Orders o on c.CustomerId=o.CustomerId inner join SalesPerson s on o.salesPersonId = s.salesPersonId ORDER BY 1,2 desc", sqlConn))
{
var range = await ws.Cells["B2"].LoadFromDataReaderAsync(sqlCmd.ExecuteReader(), true, "Table1", TableStyles.Medium10);
range.AutoFitColumns();
}
}
await package.SaveAsync();
}
//Load the package async again.
using (var package = new ExcelPackage())
{
await package.LoadAsync(file);
var newWs = package.Workbook.Worksheets.Add("AddedSheet2");
var range = await newWs.Cells["A1"].LoadFromTextAsync(FileInputUtil.GetFileInfo("03-UsingAsyncAwait", "Importfile.txt"), new ExcelTextFormat {
Delimiter = '\t'
});
range.AutoFitColumns();
await package.SaveAsAsync(FileOutputUtil.GetFileInfo("03-AsyncAwait-LoadedAndModified.xlsx"));
}
}
public static void Run()
{
var p = new ExcelPackage();
var ws = p.Workbook.Worksheets.Add("IgnoreErrors");
//Suppress Number stored as text
ws.Cells["A1"].Value = "1";
ws.Cells["A2"].Value = "2";
var ie = ws.IgnoredErrors.Add(ws.Cells["A2"]);
ie.NumberStoredAsText = true; // Ignore errors on A2 only
ws.Cells["A2"].AddComment("Number stored as text error is ignored here", "EPPlus Sample");
ws.Cells["C1"].Value = "1";
ws.Cells["C2"].Value = "2";
ws.Cells["C3"].Value = "3";
ws.Cells["C4"].Value = "4";
ws.Cells["C5"].Value = "5";
ie = ws.IgnoredErrors.Add(ws.Cells["C1:C5"]); // Ignore errors on the range
ie.NumberStoredAsText = true;
ws.Cells["D1:D5"].Formula = "A1+C1";
ws.Cells["D2"].Formula = "A2+B2"; //This will generate a Inconsistant formula error
ws.Cells["D4"].Formula = "A1+B2"; //This will generate a Inconsistant formula error
ws.Cells["D2,D4"].AddComment("Inconsistant formula error is ignored here", "EPPlus Sample");
ie = ws.IgnoredErrors.Add(ws.Cells["D2,D4"]);
ie.Formula = true; // Ignore the inconsistant formula error
p.SaveAs(FileOutputUtil.GetFileInfo("22-IgnoreErrors.xlsx"));
}
public static string Run()
{
FileInfo templateFile = FileOutputUtil.GetFileInfo("18-PivotTables.xlsx", false);
FileInfo newFile = FileOutputUtil.GetFileInfo("18-PivotTables-Styling.xlsx");
using (ExcelPackage pck = new ExcelPackage(newFile, templateFile))
{
//These two sample shows how to style different parts on the pivot table using pivot areas.
StylePivotTable1_PerCountry(pck);
StylePivotTable2_WithDataGrouping(pck);
//This sample styles the pivot table by combining a named style and use pivot areas. For named styles please also see sample 27
StylePivotTable3_WithPageFilter(pck);
//This sample styles the pivot table using pivot areas in various ways and create a custom named slicer style for the slicers.
StylePivotTable4_WithASlicer(pck);
//Adds a slicer and do some styling.
StylePivotTable5_WithACalculatedField(pck);
//Sets the pivot table into tabular mode to display the filter boxes on the row fields then styles the button fields
StylePivotTable6_CaptionFilter(pck);
pck.Save();
}
return(newFile.FullName);
}
/// <summary>
/// Sample 7 - open Sample 1 and add 2 new rows and a Piechart
/// </summary>
public static string Run()
{
FileInfo newFile = FileOutputUtil.GetFileInfo("07-OpenWorkbookAndAddDataAndChartSample.xlsx");
FileInfo templateFile = FileInputUtil.GetFileInfo("07-OpenWorkbookAddDataAndChart", "ExistingWorkbook.xlsx");
using (ExcelPackage package = new ExcelPackage(newFile, templateFile))
{
//Open the first worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
worksheet.InsertRow(5, 2);
worksheet.Cells["A5"].Value = "12010";
worksheet.Cells["B5"].Value = "Drill";
worksheet.Cells["C5"].Value = 20;
worksheet.Cells["D5"].Value = 8;
worksheet.Cells["A6"].Value = "12011";
worksheet.Cells["B6"].Value = "Crowbar";
worksheet.Cells["C6"].Value = 7;
worksheet.Cells["D6"].Value = 23.48;
worksheet.Cells["E2:E6"].FormulaR1C1 = "RC[-2]*RC[-1]";
var name = worksheet.Names.Add("SubTotalName", worksheet.Cells["C7:E7"]);
name.Style.Font.Italic = true;
name.Formula = "SUBTOTAL(9,C2:C6)";
//Format the new rows
worksheet.Cells["C5:C6"].Style.Numberformat.Format = "#,##0";
worksheet.Cells["D5:E6"].Style.Numberformat.Format = "#,##0.00";
var chart = worksheet.Drawings.AddPieChart("PieChart", ePieChartType.Pie3D);
chart.Title.Text = "Total";
//From row 1 colum 5 with five pixels offset
chart.SetPosition(0, 0, 5, 5);
chart.SetSize(600, 300);
ExcelAddress valueAddress = new ExcelAddress(2, 5, 6, 5);
var ser = (chart.Series.Add(valueAddress.Address, "B2:B6") as ExcelPieChartSerie);
chart.DataLabel.ShowCategory = true;
chart.DataLabel.ShowPercent = true;
chart.Legend.Border.LineStyle = eLineStyle.Solid;
chart.Legend.Border.Fill.Style = eFillStyle.SolidFill;
chart.Legend.Border.Fill.Color = Color.DarkBlue;
//Set the chart style to match the preset style for 3D pie charts.
chart.StyleManager.SetChartStyle(ePresetChartStyle.Pie3dChartStyle3);
//Switch the PageLayoutView back to normal
worksheet.View.PageLayoutView = false;
// save our new workbook and we are done!
package.Save();
}
return(newFile.FullName);
}
public static void Run()
{
using (var package = new ExcelPackage())
{
// Comments/Notes
var sheet1 = package.Workbook.Worksheets.Add("Comments");
AddComments(sheet1);
// Threaded comments
var sheet2 = package.Workbook.Worksheets.Add("ThreadedComments");
AddAndReadThreadedComments(sheet2);
package.SaveAs(FileOutputUtil.GetFileInfo("23-Comments.xlsx"));
}
}
/// <summary>
/// Loads two CSV files into tables and adds a chart to each sheet.
/// </summary>
/// <param name="outputDir"></param>
/// <returns></returns>
public static async Task <string> Run()
{
FileInfo newFile = FileOutputUtil.GetFileInfo(@"05-LoadDataFromCsvFilesIntoTables.xlsx");
using (ExcelPackage package = new ExcelPackage())
{
LoadFile1(package); //Load the text file without async
await LoadFile2Async(package); //Load the second text file with async
await ExportTableAsync(package);
await package.SaveAsAsync(newFile);
}
return(newFile.FullName);
}
private static async Task ExportTableAsync(ExcelPackage package)
{
var ws = package.Workbook.Worksheets[1];
var tbl = ws.Tables[0];
var format = new ExcelOutputTextFormat
{
Delimiter = ';',
Culture = new CultureInfo("en-GB"),
Encoding = new UTF8Encoding(),
SkipLinesEnd = 1 //Skip the totals row
};
await ws.Cells[tbl.Address.Address].SaveToTextAsync(FileOutputUtil.GetFileInfo("05-ExportedFromEPPlus.csv"), format);
Console.WriteLine($"Writing the text file 'ExportedTable.csv'...");
}
public static void Run()
{
//The output package
var outputFile = FileOutputUtil.GetFileInfo("14-ShapesAndImages.xlsx");
//Create the template...
using (ExcelPackage package = new ExcelPackage(outputFile))
{
FillAndColorSamples(package);
EffectSamples(package);
ThreeDSamples(package);
PictureSample(package);
package.Save();
}
}
public static string Run()
{
FileInfo output = FileOutputUtil.GetFileInfo("12-DataValidation.xlsx");
using (var package = new ExcelPackage(output))
{
AddIntegerValidation(package);
AddListValidationFormula(package);
AddListValidationValues(package);
AddTimeValidation(package);
AddDateTimeValidation(package);
ReadExistingValidationsFromPackage(package);
package.SaveAs(output);
}
return(output.FullName);
}
public static async Task RunAsync(string connectionString)
{
var p = new ExcelPackage();
//Autofilter on the worksheet
await ValueFilter(connectionString, p);
await DateTimeFilter(connectionString, p);
await CustomFilter(connectionString, p);
await Top10Filter(connectionString, p);
await DynamicAboveAverageFilter(connectionString, p);
await DynamicDateAugustFilter(connectionString, p);
//Filter on a table
await TableFilter(connectionString, p);
p.SaveAs(FileOutputUtil.GetFileInfo("13-Filters.xlsx"));
}
public async Task <string> GenerateExcel(string excelFilename, int?refId = null,
int?id = null, List <string> poNumbers = null, DateTime?poDateFrom = null, DateTime?poDateTo = null, List <string> remarkss = null,
Dictionary <string, int> exact = null,
CancellationToken cancellationToken = default)
{
try
{
var dt = await _unitOfWork.PurchaseOrderRepository.GetDataTable(poNumbers, poDateFrom, poDateTo);
var excel = DataTableToExcel(dt);
if (excel != null)
{
FileOutputUtil.OutputDir = new DirectoryInfo(Path.GetDirectoryName(excelFilename));
var xFile = FileOutputUtil.GetFileInfo(Path.GetFileName(excelFilename));
excel.SaveAs(xFile);
}
// update database information (if needed)
if (refId.HasValue)
{
if (dt == null || !File.Exists(excelFilename))
{
await _downloadProcessService.FailedToGenerate(refId.Value, "Gagal membuat file excel", cancellationToken);
return(string.Empty);
}
excelFilename = Path.GetFileName(excelFilename);
await _downloadProcessService.SuccessfullyGenerated(refId.Value, excelFilename);
}
return(excelFilename);
}
catch (Exception ex)
{
if (refId.HasValue)
{
await _downloadProcessService.FailedToGenerate(refId.Value, ex.Message);
}
throw;
}
}
public static void Run()
{
// sample data
var actors = new List <Actor>
{
new Actor {
Salary = 256.24, Tax = 0.21, FirstName = "John", MiddleName = "Bernhard", LastName = "Doe", Birthdate = new DateTime(1950, 3, 15)
},
new Actor {
Salary = 278.55, Tax = 0.23, FirstName = "Sven", MiddleName = "Bertil", LastName = "Svensson", Birthdate = new DateTime(1962, 6, 10)
},
new Actor {
Salary = 315.34, Tax = 0.28, FirstName = "Lisa", MiddleName = "Maria", LastName = "Gonzales", Birthdate = new DateTime(1971, 10, 2)
}
};
var subclassActors = new List <Actor2>
{
new Actor2 {
Salary = 256.24, Tax = 0.21, FirstName = "John", MiddleName = "Bernhard", LastName = "Doe", Birthdate = new DateTime(1950, 3, 15)
},
new Actor2 {
Salary = 278.55, Tax = 0.23, FirstName = "Sven", MiddleName = "Bertil", LastName = "Svensson", Birthdate = new DateTime(1962, 6, 10)
},
new Actor2 {
Salary = 315.34, Tax = 0.28, FirstName = "Lisa", MiddleName = "Maria", LastName = "Gonzales", Birthdate = new DateTime(1971, 10, 2)
}
};
using (var package = new ExcelPackage(FileOutputUtil.GetFileInfo("04-LoadFromCollectionAttributes.xlsx")))
{
// using the Actor class above
var sheet = package.Workbook.Worksheets.Add("Actors");
sheet.Cells["A1"].LoadFromCollection(actors);
// using a subclass where we have overridden the EpplusTableAttribute (different TableStyle and highlight last column instead of the first).
var subclassSheet = package.Workbook.Worksheets.Add("Using subclass with attributes");
subclassSheet.Cells["A1"].LoadFromCollection(subclassActors);
package.Save();
}
}
public static string Run(string connectionStr)
{
var list = GetDataFromSQL(connectionStr);
FileInfo newFile = FileOutputUtil.GetFileInfo("18-PivotTables.xlsx");
using (ExcelPackage pck = new ExcelPackage(newFile))
{
// get the handle to the existing worksheet
var wsData = pck.Workbook.Worksheets.Add("SalesData");
var dataRange = wsData.Cells["A1"].LoadFromCollection
(
from s in list
orderby s.Name
select s,
true, OfficeOpenXml.Table.TableStyles.Medium2);
wsData.Cells[2, 6, dataRange.End.Row, 6].Style.Numberformat.Format = "mm-dd-yy";
wsData.Cells[2, 7, dataRange.End.Row, 11].Style.Numberformat.Format = "#,##0";
dataRange.AutoFitColumns();
var pt1 = CreatePivotTableWithPivotChart(pck, dataRange);
var pt2 = CreatePivotTableWithDataGrouping(pck, dataRange);
var pt3 = CreatePivotTableWithPageFilter(pck, pt2.CacheDefinition);
var pt4 = CreatePivotTableWithASlicer(pck, pt2.CacheDefinition);
var pt5 = CreatePivotTableWithACalculatedField(pck, pt2.CacheDefinition);
//Filter samples
var pt6 = CreatePivotTableCaptionFilter(pck, dataRange);
pck.Save();
}
return(newFile.FullName);
}
public static void Run()
{
// Create a list of dynamic objects
dynamic p1 = new ExpandoObject();
p1.Id = 1;
p1.FirstName = "Ivan";
p1.LastName = "Horvat";
p1.Age = 21;
dynamic p2 = new ExpandoObject();
p2.Id = 2;
p2.FirstName = "John";
p2.LastName = "Doe";
p2.Age = 45;
dynamic p3 = new ExpandoObject();
p3.Id = 3;
p3.FirstName = "Sven";
p3.LastName = "Svensson";
p3.Age = 68;
List <ExpandoObject> items = new List <ExpandoObject>()
{
p1,
p2,
p3
};
// Create a workbook with a worksheet and load the data into a table
using (var package = new ExcelPackage(FileOutputUtil.GetFileInfo("04-LoadDynamicObjects.xlsx")))
{
var sheet = package.Workbook.Worksheets.Add("Dynamic");
sheet.Cells["A1"].LoadFromDictionaries(items, c =>
{
// Print headers using the property names
c.PrintHeaders = true;
// insert a space before each capital letter in the header
c.HeaderParsingType = HeaderParsingTypes.CamelCaseToSpace;
// when TableStyle is not TableStyles.None the data will be loaded into a table with the
// selected style.
c.TableStyle = TableStyles.Medium1;
});
package.Save();
}
// Load data from json (in this case a file)
var jsonItems = JsonConvert.DeserializeObject <IEnumerable <ExpandoObject> >(File.ReadAllText(FileInputUtil.GetFileInfo("04-LoadingData", "testdata.json").FullName));
using (var package = new ExcelPackage(FileOutputUtil.GetFileInfo("04-LoadJsonFromFile.xlsx")))
{
var sheet = package.Workbook.Worksheets.Add("Dynamic");
sheet.Cells["A1"].LoadFromDictionaries(jsonItems, c =>
{
// Print headers using the property names
c.PrintHeaders = true;
// insert a space before each capital letter in the header
c.HeaderParsingType = HeaderParsingTypes.CamelCaseToSpace;
// when TableStyle is not TableStyles.None the data will be loaded into a table with the
// selected style.
c.TableStyle = TableStyles.Medium1;
});
sheet.Cells["D:D"].Style.Numberformat.Format = "yyyy-mm-dd";
sheet.Cells[1, 1, sheet.Dimension.End.Row, sheet.Dimension.End.Column].AutoFitColumns();
package.Save();
}
}
public static void Run()
{
//Create a Sample 19 directory...
var outputDir = FileOutputUtil.GetDirectoryInfo("19-EncryptionAndProtection");
//create the three FileInfo objects...
FileInfo templateFile = FileOutputUtil.GetFileInfo(outputDir, "template.xlsx");
FileInfo answerFile = FileOutputUtil.GetFileInfo(outputDir, "answers.xlsx");
FileInfo JKAnswerFile = FileOutputUtil.GetFileInfo(outputDir, "JKAnswers.xlsx");
//Create the template...
using (ExcelPackage package = new ExcelPackage(templateFile))
{
//Lock the workbook totally
var workbook = package.Workbook;
workbook.Protection.LockWindows = true;
workbook.Protection.LockStructure = true;
workbook.View.SetWindowSize(150, 525, 14500, 6000);
workbook.View.ShowHorizontalScrollBar = false;
workbook.View.ShowVerticalScrollBar = false;
workbook.View.ShowSheetTabs = false;
//Set a password for the workbookprotection
workbook.Protection.SetPassword("EPPlus");
//Encrypt with no password
package.Encryption.IsEncrypted = true;
var sheet = package.Workbook.Worksheets.Add("Quiz");
sheet.View.ShowGridLines = false;
sheet.View.ShowHeaders = false;
using (var range = sheet.Cells["A:XFD"])
{
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
range.Style.Font.Name = "Broadway";
range.Style.Hidden = true;
}
sheet.Cells["A1"].Value = "Quiz-Sweden";
sheet.Cells["A1"].Style.Font.Size = 18;
sheet.Cells["A3"].Value = "Enter your name:";
sheet.Column(1).Width = 30;
sheet.Column(2).Width = 80;
sheet.Column(3).Width = 20;
sheet.Cells["A7"].Value = "What is the name of the capital of Sweden?";
sheet.Cells["A9"].Value = "At which place did the Swedish team end up in the Soccer Worldcup 1994?";
sheet.Cells["A11"].Value = "What is the first name of the famous Swedish inventor/scientist that founded the Nobel-prize?";
using (var r = sheet.Cells["B3,C7,C9,C11"])
{
r.Style.Fill.BackgroundColor.SetColor(Color.WhiteSmoke);
r.Style.Border.Top.Style = ExcelBorderStyle.Dotted;
r.Style.Border.Top.Color.SetColor(Color.Black);
r.Style.Border.Right.Style = ExcelBorderStyle.Dotted;
r.Style.Border.Right.Color.SetColor(Color.Black);
r.Style.Border.Bottom.Style = ExcelBorderStyle.Dotted;
r.Style.Border.Bottom.Color.SetColor(Color.Black);
r.Style.Border.Left.Style = ExcelBorderStyle.Dotted;
r.Style.Border.Left.Color.SetColor(Color.Black);
r.Style.Locked = false;
r.Style.Hidden = false;
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
sheet.Select("B3");
sheet.Protection.SetPassword("EPPlus");
sheet.Protection.AllowSelectLockedCells = false;
//Options question 1
var list1 = sheet.Cells["C7"].DataValidation.AddListDataValidation();
list1.Formula.Values.Add("Bern");
list1.Formula.Values.Add("Stockholm");
list1.Formula.Values.Add("Oslo");
list1.ShowErrorMessage = true;
list1.Error = "Please select a value from the list";
var list2 = sheet.Cells["C9"].DataValidation.AddListDataValidation();
list2.Formula.Values.Add("First");
list2.Formula.Values.Add("Second");
list2.Formula.Values.Add("Third");
list2.ShowErrorMessage = true;
list2.Error = "Please select a value from the list";
var list3 = sheet.Cells["C11"].DataValidation.AddListDataValidation();
list3.Formula.Values.Add("Carl Gustaf");
list3.Formula.Values.Add("Ingmar");
list3.Formula.Values.Add("Alfred");
list3.ShowErrorMessage = true;
list3.Error = "Please select a value from the list";
//Save, and the template is ready for use
package.Save();
//Quiz-template is done, now create the answer template and encrypt it...
using (var packageAnswers = new ExcelPackage(package.Stream)) //We use the stream from the template here to get a copy of it.
{
var sheetAnswers = packageAnswers.Workbook.Worksheets[0];
sheetAnswers.Cells["C7"].Value = "Stockholm";
sheetAnswers.Cells["C9"].Value = "Third";
sheetAnswers.Cells["C11"].Value = "Alfred";
packageAnswers.Encryption.Algorithm = EncryptionAlgorithm.AES192; //For the answers we want a little bit stronger encryption
packageAnswers.SaveAs(answerFile, "EPPlus"); //Save and set the password to EPPlus. The password can also be set using packageAnswers.Encryption.Password property
}
//Ok, Since this is qan example we create one user answer...
using (var packageAnswers = new ExcelPackage(package.Stream))
{
var sheetUser = packageAnswers.Workbook.Worksheets[0];
sheetUser.Cells["B3"].Value = "Jan Källman";
sheetUser.Cells["C7"].Value = "Bern";
sheetUser.Cells["C9"].Value = "Third";
sheetUser.Cells["C11"].Value = "Alfred";
packageAnswers.SaveAs(JKAnswerFile, "JK"); //We use default encryption here (AES128) and Password JK
}
}
//Now lets correct the user form...
var packAnswers = new ExcelPackage(answerFile, "EPPlus"); //Supply the password, so the file can be decrypted
var packUser = new ExcelPackage(JKAnswerFile, "JK"); //Supply the password, so the file can be decrypted
var wsAnswers = packAnswers.Workbook.Worksheets[0];
var wsUser = packUser.Workbook.Worksheets[0];
//Enumerate the three answers
foreach (var cell in wsAnswers.Cells["C7,C9,C11"])
{
wsUser.Cells[cell.Address].Style.Fill.PatternType = ExcelFillStyle.Solid;
if (cell.Value.ToString().Equals(wsUser.Cells[cell.Address].Value.ToString(), StringComparison.OrdinalIgnoreCase)) //Correct Answer?
{
wsUser.Cells[cell.Address].Style.Fill.BackgroundColor.SetColor(Color.Green);
}
else
{
wsUser.Cells[cell.Address].Style.Fill.BackgroundColor.SetColor(Color.Red);
}
}
packUser.Save();
packUser.Dispose();
packAnswers.Dispose();
packUser.Dispose();
}
/// <summary>
/// Sample 6 - Reads the filesystem and makes a report.
/// </summary>
/// <param name="outputDir">Output directory</param>
/// <param name="dir">Directory to scan</param>
/// <param name="depth">How many levels?</param>
/// <param name="skipIcons">Skip the icons in column A. A lot faster</param>
public static string Run(DirectoryInfo dir, int depth, bool skipIcons)
{
_maxLevels = depth;
FileInfo newFile = FileOutputUtil.GetFileInfo("20-CreateAFileSystemReport.xlsx");
//Create the workbook
ExcelPackage pck = new ExcelPackage(newFile);
//Add the Content sheet
var ws = pck.Workbook.Worksheets.Add("Content");
ws.View.ShowGridLines = false;
ws.Column(1).Width = 2.5;
ws.Column(2).Width = 60;
ws.Column(3).Width = 16;
ws.Column(4).Width = 20;
ws.Column(5).Width = 20;
//This set the outline for column 4 and 5 and hide them
ws.Column(4).OutlineLevel = 1;
ws.Column(4).Collapsed = true;
ws.Column(5).OutlineLevel = 1;
ws.Column(5).Collapsed = true;
ws.OutLineSummaryRight = true;
//Headers
ws.Cells["B1"].Value = "Name";
ws.Cells["C1"].Value = "Size";
ws.Cells["D1"].Value = "Created";
ws.Cells["E1"].Value = "Last modified";
ws.Cells["B1:E1"].Style.Font.Bold = true;
ws.View.FreezePanes(2, 1);
ws.Select("A2");
//height is 20 pixels
double height = 20 * 0.75;
//Start at row 2;
int row = 2;
//Load the directory content to sheet 1
row = AddDirectory(ws, dir, row, height, 0, skipIcons);
ws.OutLineSummaryBelow = false;
//Format columns
ws.Cells[1, 3, row - 1, 3].Style.Numberformat.Format = "#,##0";
ws.Cells[1, 4, row - 1, 4].Style.Numberformat.Format = "yyyy-MM-dd hh:mm";
ws.Cells[1, 5, row - 1, 5].Style.Numberformat.Format = "yyyy-MM-dd hh:mm";
//Add the textbox
var shape = ws.Drawings.AddShape("txtDesc", eShapeStyle.Rect);
shape.SetPosition(1, 5, 6, 5);
shape.SetSize(400, 200);
shape.Text = "This example demonstrates how to create various drawing objects like pictures, shapes and charts.\n\r\n\rThe first sheet contains all subdirectories and files with an icon, name, size and dates.\n\r\n\rThe second sheet contains statistics about extensions and the top-10 largest files.";
shape.Fill.Style = eFillStyle.SolidFill;
shape.Fill.Color = Color.DarkSlateGray;
shape.Fill.Transparancy = 20;
shape.TextAnchoring = eTextAnchoringType.Top;
shape.TextVertical = eTextVerticalType.Horizontal;
shape.TextAnchoringControl = false;
shape.Effect.SetPresetShadow(ePresetExcelShadowType.OuterRight);
shape.Effect.SetPresetGlow(ePresetExcelGlowType.Accent3_8Pt);
ws.Calculate();
ws.Cells[1, 2, row, 5].AutoFitColumns();
//Add the graph sheet
AddGraphs(pck, row, dir.FullName);
//Add a HyperLink to the statistics sheet.
var namedStyle = pck.Workbook.Styles.CreateNamedStyle("HyperLink"); //This one is language dependent
namedStyle.Style.Font.UnderLine = true;
namedStyle.Style.Font.Color.SetColor(Color.Blue);
ws.Cells["K13"].Hyperlink = new ExcelHyperLink("Statistics!A1", "Statistics");
ws.Cells["K13"].StyleName = "HyperLink";
//Printer settings
ws.PrinterSettings.FitToPage = true;
ws.PrinterSettings.FitToWidth = 1;
ws.PrinterSettings.FitToHeight = 0;
ws.PrinterSettings.RepeatRows = new ExcelAddress("1:1"); //Print titles
ws.PrinterSettings.PrintArea = ws.Cells[1, 1, row - 1, 5];
pck.Workbook.Calculate();
//Done! save the sheet
pck.Save();
return(newFile.FullName);
}
public static void Run()
{
var pck = new ExcelPackage();
//Create a datatable with the directories and files from the current directory...
DataTable dt = GetDataTable(FileOutputUtil.GetDirectoryInfo("."));
var wsDt = pck.Workbook.Worksheets.Add("FromDataTable");
//Load the datatable and set the number formats...
wsDt.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium9);
wsDt.Cells[2, 2, dt.Rows.Count + 1, 2].Style.Numberformat.Format = "#,##0";
wsDt.Cells[2, 3, dt.Rows.Count + 1, 4].Style.Numberformat.Format = "mm-dd-yy";
wsDt.Cells[wsDt.Dimension.Address].AutoFitColumns();
//Select Name and Created-time...
var collection = (from row in dt.Select() select new { Name = row["Name"], Created_time = (DateTime)row["Created"] });
var wsEnum = pck.Workbook.Worksheets.Add("FromAnonymous");
//Load the collection starting from cell A1...
wsEnum.Cells["A1"].LoadFromCollection(collection, true, TableStyles.Medium9);
//Add some formating...
wsEnum.Cells[2, 2, dt.Rows.Count - 1, 2].Style.Numberformat.Format = "mm-dd-yy";
wsEnum.Cells[wsEnum.Dimension.Address].AutoFitColumns();
//Load a list of FileDTO objects from the datatable...
var wsList = pck.Workbook.Worksheets.Add("FromList");
List <FileDTO> list = (from row in dt.Select()
select new FileDTO
{
Name = row["Name"].ToString(),
Size = row["Size"].GetType() == typeof(long) ? (long)row["Size"] : 0,
Created = (DateTime)row["Created"],
LastModified = (DateTime)row["Modified"],
IsDirectory = (row["Size"] == DBNull.Value)
}).ToList <FileDTO>();
//Load files ordered by size...
wsList.Cells["A1"].LoadFromCollection(from file in list
orderby file.Size descending
where file.IsDirectory == false
select file, true, TableStyles.Medium9);
wsList.Cells[2, 2, dt.Rows.Count + 1, 2].Style.Numberformat.Format = "#,##0";
wsList.Cells[2, 3, dt.Rows.Count + 1, 4].Style.Numberformat.Format = "mm-dd-yy";
//Load directories ordered by Name...
wsList.Cells["F1"].LoadFromCollection(from file in list
orderby file.Name ascending
where file.IsDirectory == true
select new
{
Name = file.Name,
Created = file.Created,
Last_modified = file.LastModified
}, //Use an underscore in the property name to get a space in the title.
true, TableStyles.Medium11);
wsList.Cells[2, 7, dt.Rows.Count + 1, 8].Style.Numberformat.Format = "mm-dd-yy";
//Load the list using a specified array of MemberInfo objects. Properties, fields and methods are supported.
var rng = wsList.Cells["J1"].LoadFromCollection(list,
true,
TableStyles.Medium10,
BindingFlags.Instance | BindingFlags.Public,
new MemberInfo[] {
typeof(FileDTO).GetProperty("Name"),
typeof(FileDTO).GetField("IsDirectory"),
typeof(FileDTO).GetMethod("ToString")
}
);
wsList.Tables.GetFromRange(rng).Columns[2].Name = "Description";
wsList.Cells[wsList.Dimension.Address].AutoFitColumns();
//...and save
var fi = FileOutputUtil.GetFileInfo("04-LoadingData.xlsx");
pck.SaveAs(fi);
}
/// <summary>
/// Sample 3 - Creates a workbook and populates using data from a SQLite database
/// </summary>
/// <param name="outputDir">The output directory</param>
/// <param name="templateDir">The location of the sample template</param>
/// <param name="connectionString">The connection string to the SQLite database</param>
public static string Run(string connectionString)
{
var file = FileOutputUtil.GetFileInfo("08-Salesreport.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(file))
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Sales");
var namedStyle = xlPackage.Workbook.Styles.CreateNamedStyle("HyperLink");
namedStyle.Style.Font.UnderLine = true;
namedStyle.Style.Font.Color.SetColor(Color.Blue);
const int startRow = 5;
int row = startRow;
//Create Headers and format them
worksheet.Cells["A1"].Value = "Fiction Inc.";
using (ExcelRange r = worksheet.Cells["A1:G1"])
{
r.Merge = true;
r.Style.Font.SetFromFont(new Font("Britannic Bold", 22, FontStyle.Italic));
r.Style.Font.Color.SetColor(Color.White);
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
}
worksheet.Cells["A2"].Value = "Sales Report";
using (ExcelRange r = worksheet.Cells["A2:G2"])
{
r.Merge = true;
r.Style.Font.SetFromFont(new Font("Britannic Bold", 18, FontStyle.Italic));
r.Style.Font.Color.SetColor(Color.Black);
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
r.Style.Fill.PatternType = ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
}
worksheet.Cells["A4"].Value = "Company";
worksheet.Cells["B4"].Value = "Sales Person";
worksheet.Cells["C4"].Value = "Country";
worksheet.Cells["D4"].Value = "Order Id";
worksheet.Cells["E4"].Value = "OrderDate";
worksheet.Cells["F4"].Value = "Order Value";
worksheet.Cells["G4"].Value = "Currency";
worksheet.Cells["A4:G4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells["A4:G4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
worksheet.Cells["A4:G4"].Style.Font.Bold = true;
// lets connect to the sample database for some data
using (var sqlConn = new SQLiteConnection(connectionString))
{
sqlConn.Open();
using (var sqlCmd = new SQLiteCommand("select CompanyName, [Name], Email, c.Country, o.OrderId, orderdate, ordervalue, currency from Customer c inner join Orders o on c.CustomerId=o.CustomerId inner join SalesPerson s on o.salesPersonId = s.salesPersonId ORDER BY 1,2 desc", sqlConn))
{
using (var sqlReader = sqlCmd.ExecuteReader())
{
// get the data and fill rows 5 onwards
while (sqlReader.Read())
{
int col = 1;
// our query has the columns in the right order, so simply
// iterate through the columns
for (int i = 0; i < sqlReader.FieldCount; i++)
{
// use the email address as a hyperlink for column 1
if (sqlReader.GetName(i) == "email")
{
// insert the email address as a hyperlink for the name
string hyperlink = "mailto:" + sqlReader.GetValue(i).ToString();
worksheet.Cells[row, 2].Hyperlink = new Uri(hyperlink, UriKind.Absolute);
}
else
{
// do not bother filling cell with blank data (also useful if we have a formula in a cell)
if (sqlReader.GetValue(i) != null)
{
worksheet.Cells[row, col].Value = sqlReader.GetValue(i);
}
col++;
}
}
row++;
}
sqlReader.Close();
worksheet.Cells[startRow, 2, row - 1, 2].StyleName = "HyperLink";
worksheet.Cells[startRow, 5, row - 1, 5].Style.Numberformat.Format = "yyyy/mm/dd";
worksheet.Cells[startRow, 6, row - 1, 6].Style.Numberformat.Format = "[$$-409]#,##0";
//Set column width
worksheet.Column(1).Width = 35;
worksheet.Column(2).Width = 28;
worksheet.Column(3).Width = 28;
worksheet.Column(4).Width = 10;
worksheet.Column(5).Width = 12;
worksheet.Column(6).Width = 12;
worksheet.Column(7).Width = 12;
}
}
sqlConn.Close();
// lets set the header text
worksheet.HeaderFooter.OddHeader.CenteredText = "Fiction Inc. Sales Report";
// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.OddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
// add the sheet name to the footer
worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
// add the file path to the footer
worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
}
// we had better add some document properties to the spreadsheet
// set some core property values
xlPackage.Workbook.Properties.Title = "Sales Report";
xlPackage.Workbook.Properties.Author = "Jan Källman";
xlPackage.Workbook.Properties.Subject = "Sales Report Samples";
xlPackage.Workbook.Properties.Keywords = "Office Open XML";
xlPackage.Workbook.Properties.Category = "Sales Report Samples";
xlPackage.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel file from scratch using EPPlus";
// set some extended property values
xlPackage.Workbook.Properties.Company = "Fiction Inc.";
xlPackage.Workbook.Properties.HyperlinkBase = new Uri("https://EPPlusSoftware.com");
// set some custom property values
xlPackage.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan Källman");
xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1");
xlPackage.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
// save the new spreadsheet
xlPackage.Save();
}
return(file.FullName);
}
public async Task GenerateExcel(int?refId = null, string firstName = "", string lastName = "", CancellationToken cancellationToken = default)
{
try
{
string excelDestinationPath = Path.Combine(
AppDomain.CurrentDomain.BaseDirectory,
"DownloadMonitor",
DateTime.Now.ToString("yyyy"),
DateTime.Now.ToString("MM")
);
var filterSpec = new UserInfoFilterSpecification(string.Empty, firstName, lastName);
var results = await _unitOfWork.UserInfoRepository.ListAsync(filterSpec, null, cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
using (var package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("Sheet 1");
// add header
ws.Cells[1, 1].Value = "User Name";
ws.Cells[1, 2].Value = "First Name";
ws.Cells[1, 3].Value = "Last Name";
// format header
using (var range = ws.Cells[1, 1, 1, 4])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DarkBlue);
range.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
if (results?.Count > 0)
{
// write excel content
int row = 2;
foreach (var item in results)
{
ws.Cells[row, 1].Value = item.UserName;
ws.Cells[row, 2].Value = item.FirstName;
ws.Cells[row, 3].Value = item.LastName;
row++;
}
}
ws.Cells.AutoFitColumns(0);
string fileName = DateTime.Now.ToString("yyyyMMdd_hhmmss_") + Guid.NewGuid().ToString() + ".xlsx";
FileOutputUtil.OutputDir = new DirectoryInfo(excelDestinationPath);
var xFile = FileOutputUtil.GetFileInfo(fileName);
package.SaveAs(xFile);
// update database information (if needed)
if (refId.HasValue)
{
await _downloadProcessService.SuccessfullyGenerated(refId.Value, fileName);
}
}
}
catch (Exception ex)
{
if (refId.HasValue)
{
await _downloadProcessService.FailedToGenerate(refId.Value, ex.Message);
}
throw;
}
}
public static void Run()
{
using (var package = new ExcelPackage())
{
//Sample fx data
var txt = "Date;AUD;CAD;CHF;DKK;EUR;GBP;HKD;JPY;MYR;NOK;NZD;RUB;SEK;THB;TRY;USD\r\n" +
"2016-03-01;6,17350;6,42084;8,64785;1,25668;9,37376;12,01683;1,11067;0,07599;2,06900;0,99522;5,69227;0,11665;1,00000;0,24233;2,93017;8,63185\r\n" +
"2016-03-02;6,27223;6,42345;8,63480;1,25404;9,35350;12,14970;1,11099;0,07582;2,07401;0,99311;5,73277;0,11757;1,00000;0,24306;2,94083;8,63825\r\n" +
"2016-03-07;6,33778;6,38403;8,50245;1,24980;9,32373;12,05756;1,09314;0,07478;2,07171;0,99751;5,77539;0,11842;1,00000;0,23973;2,91088;8,48885\r\n" +
"2016-03-08;6,30268;6,31774;8,54066;1,25471;9,36254;12,03361;1,09046;0,07531;2,05625;0,99225;5,72501;0,11619;1,00000;0,23948;2,91067;8,47020\r\n" +
"2016-03-09;6,32630;6,33698;8,46118;1,24399;9,28125;11,98879;1,08544;0,07467;2,04128;0,98960;5,71601;0,11863;1,00000;0,23893;2,91349;8,42945\r\n" +
"2016-03-10;6,24241;6,28817;8,48684;1,25260;9,34350;11,99193;1,07956;0,07392;2,04500;0,98267;5,58145;0,11769;1,00000;0,23780;2,89150;8,38245\r\n" +
"2016-03-11;6,30180;6,30152;8,48295;1,24848;9,31230;12,01194;1,07545;0,07352;2,04112;0,98934;5,62335;0,11914;1,00000;0,23809;2,90310;8,34510\r\n" +
"2016-03-15;6,19790;6,21615;8,42931;1,23754;9,22896;11,76418;1,07026;0,07359;2,00929;0,97129;5,49278;0,11694;1,00000;0,23642;2,86487;8,30540\r\n" +
"2016-03-16;6,18508;6,22493;8,41792;1,23543;9,21149;11,72470;1,07152;0,07318;2,01179;0,96907;5,49138;0,11836;1,00000;0,23724;2,84767;8,31775\r\n" +
"2016-03-17;6,25214;6,30642;8,45981;1,24327;9,26623;11,86396;1,05571;0,07356;2,01706;0,98159;5,59544;0,12024;1,00000;0,23543;2,87595;8,18825\r\n" +
"2016-03-18;6,25359;6,32400;8,47826;1,24381;9,26976;11,91322;1,05881;0,07370;2,02554;0,98439;5,59067;0,12063;1,00000;0,23538;2,86880;8,20950";
// Add a new worksheet to the empty workbook and load the fx rates from the text
var ws = package.Workbook.Worksheets.Add("SEKRates");
//Load the sample data with a Swedish culture setting
ws.Cells["A1"].LoadFromText(txt, new ExcelTextFormat()
{
Delimiter = ';', Culture = CultureInfo.GetCultureInfo("sv-SE")
}, TableStyles.Light10, true);
ws.Cells["A2:A12"].Style.Numberformat.Format = "yyyy-mm-dd";
// Add a column sparkline for all currencies
ws.Cells["A15"].Value = "Column";
var sparklineCol = ws.SparklineGroups.Add(eSparklineType.Column, ws.Cells["B15:Q15"], ws.Cells["B2:Q12"]);
sparklineCol.High = true;
sparklineCol.ColorHigh.SetColor(Color.Red);
// Add a line sparkline for all currencies
ws.Cells["A16"].Value = "Line";
var sparklineLine = ws.SparklineGroups.Add(eSparklineType.Line, ws.Cells["B16:Q16"], ws.Cells["B2:Q12"]);
sparklineLine.DateAxisRange = ws.Cells["A2:A12"];
// Add some more random values and add a stacked sparkline.
ws.Cells["A17"].Value = "Stacked";
ws.Cells["B17:Q17"].LoadFromArrays(new List <object[]> {
new object[] { 2, -1, 3, -4, 8, 5, -12, 18, 99, 1, -4, 12, -8, 9, 0, -8 }
});
var sparklineStacked = ws.SparklineGroups.Add(eSparklineType.Stacked, ws.Cells["R17"], ws.Cells["B17:Q17"]);
sparklineStacked.High = true;
sparklineStacked.ColorHigh.SetColor(Color.Red);
sparklineStacked.Low = true;
sparklineStacked.ColorLow.SetColor(Color.Green);
sparklineStacked.Negative = true;
sparklineStacked.ColorNegative.SetColor(Color.Blue);
ws.Cells["A15:A17"].Style.Font.Bold = true;
ws.Cells.AutoFitColumns();
ws.Row(15).Height = 40;
ws.Row(16).Height = 40;
ws.Row(17).Height = 40;
package.SaveAs(FileOutputUtil.GetFileInfo("16-Sparklines.xlsx"));
}
}
/// <summary>
/// This sample creates a new workbook from a template file containing a chart and populates it with Exchange rates from
/// the database and set the three series on the chart.
/// </summary>
/// <param name="connectionString">Connectionstring to the db</param>
/// <param name="template">the template</param>
/// <param name="outputdir">output dir</param>
/// <returns></returns>
public static string Run(string connectionString)
{
FileInfo template = FileInputUtil.GetFileInfo("17-FXReportFromDatabase", "GraphTemplate.xlsx");
using (ExcelPackage p = new ExcelPackage(template, true))
{
//Set up the headers
ExcelWorksheet ws = p.Workbook.Worksheets[0];
ws.Cells["A20"].Value = "Date";
ws.Cells["B20"].Value = "EOD Rate";
ws.Cells["B20:F20"].Merge = true;
ws.Cells["G20"].Value = "Change";
ws.Cells["G20:K20"].Merge = true;
ws.Cells["B20:K20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
using (ExcelRange row = ws.Cells["A20:G20"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
row.Style.Font.Color.SetColor(Color.White);
row.Style.Font.Bold = true;
}
ws.Cells["B21"].Value = "USD/SEK";
ws.Cells["C21"].Value = "USD/EUR";
ws.Cells["D21"].Value = "USD/INR";
ws.Cells["E21"].Value = "USD/CNY";
ws.Cells["F21"].Value = "USD/DKK";
ws.Cells["G21"].Value = "USD/SEK";
ws.Cells["H21"].Value = "USD/EUR";
ws.Cells["I21"].Value = "USD/INR";
ws.Cells["J21"].Value = "USD/CNY";
ws.Cells["K21"].Value = "USD/DKK";
using (ExcelRange row = ws.Cells["A21:K21"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
row.Style.Font.Color.SetColor(Color.Black);
row.Style.Font.Bold = true;
}
int startRow = 22;
//Connect to the database and fill the data
using (var sqlConn = new SQLiteConnection(connectionString))
{
int row = startRow;
sqlConn.Open();
using (var sqlCmd = new SQLiteCommand("SELECT date, SUM(Case when CurrencyCodeTo = 'SEK' Then rate Else 0 END) AS [SEK], SUM(Case when CurrencyCodeTo = 'EUR' Then rate Else 0 END) AS [EUR], SUM(Case when CurrencyCodeTo = 'INR' Then rate Else 0 END) AS [INR], SUM(Case when CurrencyCodeTo = 'CNY' Then rate Else 0 END) AS [CNY], SUM(Case when CurrencyCodeTo = 'DKK' Then rate Else 0 END) AS [DKK] FROM CurrencyRate where [CurrencyCodeFrom]='USD' AND CurrencyCodeTo in ('SEK', 'EUR', 'INR','CNY','DKK') GROUP BY date ORDER BY date", sqlConn))
{
using (var sqlReader = sqlCmd.ExecuteReader())
{
// get the data and fill rows 22 onwards
while (sqlReader.Read())
{
ws.Cells[row, 1].Value = sqlReader[0];
ws.Cells[row, 2].Value = sqlReader[1];
ws.Cells[row, 3].Value = sqlReader[2];
ws.Cells[row, 4].Value = sqlReader[3];
ws.Cells[row, 5].Value = sqlReader[4];
ws.Cells[row, 6].Value = sqlReader[5];
row++;
}
}
//Set the numberformat
ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd";
ws.Cells[startRow, 2, row - 1, 6].Style.Numberformat.Format = "#,##0.0000";
//Set the Formulas
ws.Cells[startRow + 1, 7, row - 1, 11].Formula = $"B${startRow}/B{startRow+1}-1";
ws.Cells[startRow, 7, row - 1, 11].Style.Numberformat.Format = "0.00%";
}
//Set the series for the chart. The series must exist in the template or the program will crash.
var chart = ws.Drawings["SampleChart"].As.Chart.LineChart; //We know the chart is a linechart, so we can use the As.Chart.LineChart Property directly
chart.Title.Text = "Exchange rate %";
chart.Series[0].Header = "USD/SEK";
chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7);
chart.Series[1].Header = "USD/EUR";
chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 8, row - 1, 8);
chart.Series[2].Header = "USD/INR";
chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 9, row - 1, 9);
var serie = chart.Series.Add("'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 10, row - 1, 10),
"'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1));
serie.Header = "USD/CNY";
serie.Marker.Style = eMarkerStyle.None;
serie = chart.Series.Add("'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 11, row - 1, 11),
"'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1));
serie.Header = "USD/DKK";
serie.Marker.Style = eMarkerStyle.None;
chart.Legend.Position = eLegendPosition.Bottom;
//Set the chart style
chart.StyleManager.SetChartStyle(236);
}
//Get the documet as a byte array from the stream and save it to disk. (This is useful in a webapplication) ...
var bin = p.GetAsByteArray();
FileInfo file = FileOutputUtil.GetFileInfo("17-FxReportFromDatabase.xlsx");
File.WriteAllBytes(file.FullName, bin);
return(file.FullName);
}
}
public static string Run(string connectionStr)
{
var list = GetDataFromSQL(connectionStr);
FileInfo newFile = FileOutputUtil.GetFileInfo("18-PivotTables.xlsx");
using (ExcelPackage pck = new ExcelPackage(newFile))
{
// get the handle to the existing worksheet
var wsData = pck.Workbook.Worksheets.Add("SalesData");
var dataRange = wsData.Cells["A1"].LoadFromCollection
(
from s in list
orderby s.Name
select s,
true, OfficeOpenXml.Table.TableStyles.Medium2);
wsData.Cells[2, 6, dataRange.End.Row, 6].Style.Numberformat.Format = "mm-dd-yy";
wsData.Cells[2, 7, dataRange.End.Row, 11].Style.Numberformat.Format = "#,##0";
dataRange.AutoFitColumns();
var wsPivot = pck.Workbook.Worksheets.Add("PivotSimple");
var pivotTable1 = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "PerCountry");
pivotTable1.RowFields.Add(pivotTable1.Fields["Country"]);
var dataField = pivotTable1.DataFields.Add(pivotTable1.Fields["OrderValue"]);
dataField.Format = "#,##0";
pivotTable1.DataOnRows = true;
var chart = wsPivot.Drawings.AddPieChart("PivotChart", ePieChartType.PieExploded3D, pivotTable1);
chart.SetPosition(1, 0, 4, 0);
chart.SetSize(800, 600);
chart.Legend.Remove();
chart.Series[0].DataLabel.ShowCategory = true;
chart.Series[0].DataLabel.Position = eLabelPosition.OutEnd;
chart.StyleManager.SetChartStyle(ePresetChartStyle.Pie3dChartStyle6);
var wsPivot2 = pck.Workbook.Worksheets.Add("PivotDateGrp");
var pivotTable2 = wsPivot2.PivotTables.Add(wsPivot2.Cells["A3"], dataRange, "PerEmploeeAndQuarter");
pivotTable2.RowFields.Add(pivotTable2.Fields["Name"]);
//Add a rowfield
var rowField = pivotTable2.RowFields.Add(pivotTable2.Fields["OrderDate"]);
//This is a date field so we want to group by Years and quaters. This will create one additional field for years.
rowField.AddDateGrouping(eDateGroupBy.Years | eDateGroupBy.Quarters);
//Get the Quaters field and change the texts
var quaterField = pivotTable2.Fields.GetDateGroupField(eDateGroupBy.Quarters);
quaterField.Items[0].Text = "<"; //Values below min date, but we use auto so its not used
quaterField.Items[1].Text = "Q1";
quaterField.Items[2].Text = "Q2";
quaterField.Items[3].Text = "Q3";
quaterField.Items[4].Text = "Q4";
quaterField.Items[5].Text = ">"; //Values above max date, but we use auto so its not used
//Add a pagefield
var pageField = pivotTable2.PageFields.Add(pivotTable2.Fields["CompanyName"]);
//Add the data fields and format them
dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["OrderValue"]);
dataField.Format = "#,##0";
dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["Tax"]);
dataField.Format = "#,##0";
dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["Freight"]);
dataField.Format = "#,##0";
//We want the datafields to appear in columns
pivotTable2.DataOnRows = false;
pck.Save();
}
return(newFile.FullName);
}
/// <summary>
/// This sample load a number of rows, style them and insert a row at the top.
/// A password is set to protect locked cells. Column 3 & 4 will be editable, the rest will be locked.
/// </summary>
/// <param name="rows"></param>
public static string Run(int rows)
{
var newFile = FileOutputUtil.GetFileInfo("09-PerformanceAndProtection.xlsx");
using (ExcelPackage package = new ExcelPackage())
{
Console.WriteLine("{0:HH.mm.ss}\tStarting...", DateTime.Now);
//Load the sheet with one string column, one date column and a few random numbers.
var ws = package.Workbook.Worksheets.Add("Performance Test");
//Format all cells
ExcelRange cols = ws.Cells["A:XFD"];
cols.Style.Fill.PatternType = ExcelFillStyle.Solid;
cols.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
var rnd = new Random();
for (int row = 1; row <= rows; row++)
{
ws.SetValue(row, 1, row); //The SetValue method is a little bit faster than using the Value property
ws.SetValue(row, 2, string.Format("Row {0}", row));
ws.SetValue(row, 3, DateTime.Today.AddDays(row));
ws.SetValue(row, 4, rnd.NextDouble() * 10000);
if (row % 10000 == 0)
{
Console.WriteLine("{0:HH.mm.ss}\tWriting row {1}...", DateTime.Now, row);
}
}
//Set the formula using the R1C1 format
ws.Cells[1, 5, rows, 5].FormulaR1C1 = "RC[-4]+RC[-1]";
//Add a sum at the end
ws.Cells[rows + 1, 5].Formula = string.Format("Sum({0})", new ExcelAddress(1, 5, rows, 5).Address);
ws.Cells[rows + 1, 5].Style.Font.Bold = true;
ws.Cells[rows + 1, 5].Style.Numberformat.Format = "#,##0.00";
Console.WriteLine("{0:HH.mm.ss}\tWriting row {1}...", DateTime.Now, rows);
Console.WriteLine("{0:HH.mm.ss}\tFormatting...", DateTime.Now);
//Format the date and numeric columns
ws.Cells[1, 1, rows, 1].Style.Numberformat.Format = "#,##0";
ws.Cells[1, 3, rows, 3].Style.Numberformat.Format = "YYYY-MM-DD";
ws.Cells[1, 4, rows, 5].Style.Numberformat.Format = "#,##0.00";
Console.WriteLine("{0:HH.mm.ss}\tInsert a row at the top...", DateTime.Now);
//Insert a row at the top. Note that the formula-addresses are shifted down
ws.InsertRow(1, 1);
//Write the headers and style them
ws.Cells["A1"].Value = "Index";
ws.Cells["B1"].Value = "Text";
ws.Cells["C1"].Value = "Date";
ws.Cells["D1"].Value = "Number";
ws.Cells["E1"].Value = "Formula";
ws.View.FreezePanes(2, 1);
using (var rng = ws.Cells["A1:E1"])
{
rng.Style.Font.Bold = true;
rng.Style.Font.Color.SetColor(Color.White);
rng.Style.WrapText = true;
rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
}
Console.WriteLine("{0:HH.mm.ss}\tAutofit columns and lock and format cells...", DateTime.Now);
ws.Cells[rows - 100, 1, rows, 5].AutoFitColumns(5); //Auto fit using the last 100 rows with minimum width 5
ws.Column(5).Width = 15; //We need to set the width for column F manually since the end sum formula is the widest cell in the column (EPPlus don't calculate any forumlas, so no output text is avalible).
//Now we set the sheet protection and a password.
ws.Cells[2, 3, rows + 1, 4].Style.Locked = false;
ws.Cells[2, 3, rows + 1, 4].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[2, 3, rows + 1, 4].Style.Fill.BackgroundColor.SetColor(Color.White);
ws.Cells[1, 5, rows + 2, 5].Style.Hidden = true; //Hide the formula
ws.Protection.SetPassword("EPPlus");
ws.Select("C2");
Console.WriteLine("{0:HH.mm.ss}\tSaving...", DateTime.Now);
package.Compression = CompressionLevel.BestSpeed;
package.SaveAs(newFile);
}
Console.WriteLine("{0:HH.mm.ss}\tDone!!", DateTime.Now);
return(newFile.FullName);
}
