/// <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); }