Is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).
supports:
Cell Ranges
Cell styling (Border, Color, Fill, Font, Number, Alignments)
Charts
Pictures
Shapes
Comments
Tables
Protection
Encryption
Pivot tables
Data validation
Conditional formatting
VBA
Formula calculation
Many more...
http://epplus.codeplex.com/
the following samples made with EPPlus v4.1
XLSX SNIPPET:
--
Excel Graphs with EPPlus
Select used worksheet dimension :
download :
v4.5.3 [nuget] - the last version of EPPlus under LGPL
latest - under Polyform Noncommercial
alternative lib :
https://github.com/ClosedXML/ClosedXML nuget
supports:
Cell Ranges
Cell styling (Border, Color, Fill, Font, Number, Alignments)
Charts
Pictures
Shapes
Comments
Tables
Protection
Encryption
Pivot tables
Data validation
Conditional formatting
VBA
Formula calculation
Many more...
http://epplus.codeplex.com/
the following samples made with EPPlus v4.1
JavaScript:
//get the data from dbase
DataTable dT = SQLc.GetDATATABLE(q);
//if file exists will try to open it, as excel XLSX
FileInfo newfILE= new FileInfo(@"C:\test.xlsx");
using (ExcelPackage package = new ExcelPackage(newfILE))
{
//create a sheet, name it DATA
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("DATA");
//get the first sheet - ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int row = 1;
int col = 0;
//print col name to sheet
foreach (DataColumn col_item in dT.Columns)
{
col += 1;
worksheet.Cells[row, col].Value = col_item.Caption;
}
foreach (DataRow item in dT.Rows)
{
//row counter
row += 1;
col = 0;
//for each column get his value
foreach (var col_item in dT.Columns)
{
col += 1;
worksheet.Cells[row, col].Value = item[col - 1].ToString();
}
//add one extra column at the end and use excel formula to subtract (datediff) startdate&enddate fields came from dbase
//use of NETWORKDAYS (doesnt calculate the weekend) - use of IFERROR - avoid error cells when the date by dbase is null
//using lib ExcelAddress function with the vars row+col we go there
//..
col += 1;
worksheet.Cells[new ExcelAddress(row, col, row, col).Address].Formula = string.Format("IFERROR(NETWORKDAYS({0},{1}),0)", new ExcelAddress(row, 3, row, 3).Address, new ExcelAddress(row, 10, row, 10).Address);
}
//linq to datatable - group by (get unique values)
var product_codes_grp = from r in dT.AsEnumerable()
group r by r.Field<string>("PRODUCT_NAME") into grp
select new
{
product_code = grp.Key
};
//go to second sheet
ExcelWorksheet worksheet2 = package.Workbook.Worksheets[2];
//write the #header text# on first row
row = 1;
worksheet2.Cells[1, 1].Value = "PRODUCT_NAME";
worksheet2.Cells[1, 2].Value = "Days";
worksheet2.Cells[1, 3].Value = "Average";
//for each product
foreach (var item in product_codes_grp)
{
row += 1;
//write the product name
col = 1;
worksheet2.Cells[row, col].Value = item.PRODUCT_NAME;
//on next column write the SUM of datediff column (last column) we add at first steps
//using again an excel function we get only the lines for current PRODUCT_NAME
//what says : goto sheet DATA get the value of column FN, where the U column equals with PRODUCT_NAME
col += 1;
worksheet2.Cells[row, col].Formula = "SUMIFS(DATA!FN:FN,DATA!U:U,\"" + item.PRODUCT_NAME + "\")";
// worksheet2.Cells[row, col].Style.Numberformat.Format = "#.##";
//in continue, do the same but do the AVERAGE
col += 1;
worksheet2.Cells[row, col].Formula = "AVERAGEIFS(DATA!FN:FN,DATA!U:U,\"" + item.PRODUCT_NAME + "\")";
// worksheet2.Cells[row, col].Style.Numberformat.Format = "#.##";
}
//autofit
worksheet2.Cells[worksheet2.Dimension.Address].AutoFitColumns();
//****************
//PIVOT
//****************
//var data_sheet = package.Workbook.Worksheets["DATA"];
//get the data of first sheet created at first
ExcelRangeBase dataRange;
dataRange = worksheet.Cells[worksheet.Dimension.Address];
//create a new sheet
var wsPivot = package.Workbook.Worksheets.Add("STATS");
//on A1, with datarange the DATA sheet, create a PivotTable
ExcelPivotTable pivot = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "PivotTable1");
//Add row field
pivot.RowFields.Add(pivot.Fields["PRODUCT_NAME"]);
//Set row caption
pivot.RowHeaderCaption = "Products";
//Add column field
pivot.ColumnFields.Add(pivot.Fields["FN"]);
// pivot.DataOnRows = false;
// pivot.RowGrandTotals = true;
pivot.DataFields.Add(pivot.Fields["FN"]);
pivot.DataFields[0].Function = DataFieldFunctions.Count;
pivot.DataFields[0].Name = "GROUP BY XLS!";
//****************
//PIVOT CHART PIES
//****************
//add extra sheet for chart pies
var wsPivotChart = package.Workbook.Worksheets.Add("STAT_PIE");
//chart by pivot1 table - http://epplus.codeplex.com/wikipage?title=StatisticsSheetExample
var chart = wsPivotChart.Drawings.AddChart("PivotChart1", eChartType.Pie, pivot) as ExcelPieChart;
chart.SetPosition(1, 0, 0, 0);
chart.SetSize(600, 600);
chart.Title.Text = "Products";
chart.DataLabel.ShowCategory = true;
chart.DataLabel.ShowPercent = true;
chart.DataLabel.ShowLeaderLines = true;
//********************************
//IMPORT PNG FILE TO NEW SHEET
//********************************
try
{
//save MSCHART to a PNG
string fl = Path.GetTempFileName();
chart_sc_report.SaveImage(fl, ChartImageFormat.Png);
//create a new sheet
ExcelWorksheet worksheet3 = package.Workbook.Worksheets.Add("product_picture");
// img variable actually is your image path
System.Drawing.Image myImage = System.Drawing.Image.FromFile(fl);
var pic = worksheet3.Drawings.AddPicture("NAME", myImage);
// Row, RowoffsetPixel, Column, ColumnOffSetPixel
pic.SetPosition(1, 0, 2, 0);
}
catch (Exception x)
{
MessageBox.Show("Error when generating chart image\r\n\r\n" + x.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
//****************
//PLAIN CHART
//****************
//first put data to XLS cells by query the datatable
var ci = new CultureInfo("en-US");
DateTime startdate = DateTime.ParseExact(dtp3.Value.ToString("01/MM/yyyy"), "dd/MM/yyyy", ci);
DateTime enddate = DateTime.ParseExact(dtp4.Value.ToString("01/MM/yyyy"), "dd/MM/yyyy", ci);
//scroll2bottom for function - function diff date1 - date2 in months
int product_cells = GetMonthsBetween(dtp3.Value, dtp4.Value);
//////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////// product START
row = 1;
col = 1;
//product_SPLIT
ExcelWorksheet worksheet_product_split = package.Workbook.Worksheets.Add("product_SPLIT");
worksheet_product_split.Cells[row, col].Value = "product Split";
worksheet_product_split.Cells["A1:B2"].Merge = true;
worksheet_product_split.Cells["C1:C2"].Merge = true;
worksheet_product_split.Cells["C1"].Value = "Target";
worksheet_product_split.Cells["D1"].Value = "Actuals per month";
worksheet_product_split.Cells[new ExcelAddress(1,4,1,product_cells+3).Address].Merge = true;
worksheet_product_split.Cells["A3"].Value = "shareware";
worksheet_product_split.Cells["A3:A4"].Merge = true;
worksheet_product_split.Cells["A5"].Value = "freeware";
worksheet_product_split.Cells["A5:A6"].Merge = true;
worksheet_product_split.Cells["B3"].Value = "storage";
worksheet_product_split.Cells["B4"].Value = "sold";
worksheet_product_split.Cells["B5"].Value = "storage";
worksheet_product_split.Cells["B6"].Value = "sold";
col = 4;
row = 3;
while (startdate < enddate)
{
worksheet_product_split.Cells[2, col].Value = startdate.ToString("dd/MM/yyyy") + " - " + startdate.AddMonths(1).ToString("dd/MM/yyyy");
// mandatory for calculation
int shareware_sold_total = (from r in dTenumerable
where (r.Field<DateTime>("OpenTime") > startdate && r.Field<DateTime>("OpenTime") < startdate.AddMonths(1)) &&
(r.Field<string>("sold_product") != null && r.Field<string>("queue").Equals("shareware"))
select r).Count();
int shareware_storage_total = (from r in dTenumerable
where (r.Field<DateTime>("OpenTime") > startdate && r.Field<DateTime>("OpenTime") < startdate.AddMonths(1)) &&
(r.Field<string>("storage_product") != null && r.Field<string>("queue").Equals("shareware"))
select r).Count();
int freeware_sold_total = (from r in dTenumerable
where (r.Field<DateTime>("OpenTime") > startdate && r.Field<DateTime>("OpenTime") < startdate.AddMonths(1)) &&
(r.Field<string>("sold_product") != null && r.Field<string>("queue").Equals("freeware"))
select r).Count();
int freeware_storage_total = (from r in dTenumerable
where (r.Field<DateTime>("OpenTime") > startdate && r.Field<DateTime>("OpenTime") < startdate.AddMonths(1)) &&
(r.Field<string>("storage_product") != null && r.Field<string>("queue").Equals("freeware"))
select r).Count();
// mandatory for calculation
int shareware_sold_made = (from r in dTenumerable
where (r.Field<DateTime>("OpenTime") > startdate && r.Field<DateTime>("OpenTime") < startdate.AddMonths(1)) &&
(r.Field<string>("sold_product") != null && (r.Field<string>("sold_product").Equals("MADE") && r.Field<string>("queue").Equals("shareware")))
select r).Count();
int shareware_storage_made = (from r in dTenumerable
where (r.Field<DateTime>("OpenTime") > startdate && r.Field<DateTime>("OpenTime") < startdate.AddMonths(1)) &&
(r.Field<string>("storage_product") != null && (r.Field<string>("storage_product").Equals("MADE") && r.Field<string>("queue").Equals("shareware")))
select r).Count();
int freeware_sold_made = (from r in dTenumerable
where (r.Field<DateTime>("OpenTime") > startdate && r.Field<DateTime>("OpenTime") < startdate.AddMonths(1)) &&
(r.Field<string>("sold_product") != null && (r.Field<string>("sold_product").Equals("MADE") && r.Field<string>("queue").Equals("freeware")))
select r).Count();
int freeware_storage_made = (from r in dTenumerable
where (r.Field<DateTime>("OpenTime") > startdate && r.Field<DateTime>("OpenTime") < startdate.AddMonths(1)) &&
(r.Field<string>("storage_product") != null && (r.Field<string>("storage_product").Equals("MADE") && r.Field<string>("queue").Equals("freeware")))
select r).Count();
//scroll2bottom - for function getPercentage
worksheet_product_split.Cells[row, col].Value = decimal.Parse(getPercentage(shareware_storage_made, shareware_storage_total).ToString());
worksheet_product_split.Cells[row + 1, col].Value = decimal.Parse(getPercentage(shareware_sold_made, shareware_sold_total).ToString());
worksheet_product_split.Cells[row + 2, col].Value = decimal.Parse(getPercentage(freeware_storage_made, freeware_storage_total).ToString());
worksheet_product_split.Cells[row + 3, col].Value = decimal.Parse(getPercentage(freeware_sold_made, freeware_sold_total).ToString());
// pull out month and year
startdate = startdate.AddMonths(1);
col += 1;
}
//draw first plain chart based on XLS cells
var chartshareware = worksheet_product_split.Drawings.AddChart("chart_shareware", eChartType.LineMarkers);
//storage -- 2nd param is the X-AXIS labels
var series = chartshareware.Series.Add(new ExcelAddress(3, 4, 3, product_cells + 3).Address, new ExcelAddress(2, 4, 2, product_cells + 3).Address);
//sold -- 2nd param is the X-AXIS labels
var series2 = chartshareware.Series.Add(new ExcelAddress(4, 4, 4, product_cells + 3).Address, new ExcelAddress(2, 4, 2, product_cells + 3).Address);
//set legends text
chartshareware.Series[0].Header = "storage";
chartshareware.Series[1].Header = "sold";
//set position on sheet
chartshareware.SetPosition(10, 0, 0, 0);
//MOVE TO END
package.Workbook.Worksheets.MoveToEnd("DATA");
package.Workbook.Worksheets.MoveToEnd("STAT_PIE");
//xls properties
package.Workbook.Properties.Title = "Report " + dtp1.Value.ToString("dd-MMM-yyyy") + " - " + dtp2.Value.ToString("dd-MMM-yyyy");
package.Workbook.Properties.Author = "costas";
package.Workbook.Properties.Company = "PipisCrew";
//save XLSX!
package.Save();
}
}
public static string getPercentage(decimal current, decimal total)
{
try
{
//http://stackoverflow.com/a/4561446
//http://stackoverflow.com/a/9288913
//string.Format("{0:N2}%", x);
return string.Format("{0:N2}", ((current / total) * 100m));
//return ((current / total) * 100m).ToString("{0:N2}%") ; //(int)Math.Round((double)(100 * complete) / total);
}
catch { return "0%"; }
}
public static int GetMonthsBetween(DateTime from, DateTime to)
{
if (from > to) return GetMonthsBetween(to, from);
var monthDiff = Math.Abs((to.Year * 12 + (to.Month - 1)) - (from.Year * 12 + (from.Month - 1)));
if (from.AddMonths(monthDiff) > to || to.Day < from.Day)
{
return monthDiff - 1;
}
else
{
return monthDiff;
}
}XLSX SNIPPET:
--
Excel Graphs with EPPlus
JavaScript:
//source - http://danlb.blogspot.com/2014/06/excel-graphs-with-epplus.html
using (var package = new ExcelPackage())
{
var workbook = package.Workbook;
var worksheet = workbook.Worksheets.Add("Sheet1");
worksheet.Cells["C1"].Value = "Widgets";
worksheet.Cells["C2"].Value = 20;
worksheet.Cells["C3"].Value = 5;
worksheet.Cells["C4"].Value = 30;
worksheet.Cells["C5"].Value = 32;
worksheet.Cells["C6"].Value = 17;
worksheet.Cells["B2"].Value = "Jan";
worksheet.Cells["B3"].Value = "Feb";
worksheet.Cells["B4"].Value = "Mar";
worksheet.Cells["B5"].Value = "Apr";
worksheet.Cells["B6"].Value = "May";
var chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnStacked);
//data series to our chart
var series = chart.Series.Add(“C2:C6", "B2:B6");
//default name in the legend of the chart
series.HeaderAddress = new ExcelAddress("'Sheet1'!C1");
//Since this is a stacked column chart we can add more than one series.
var series2 = chart.Series.Add("D2:D6", "B2:B6");
series2.HeaderAddress = new ExcelAddress("'Sheet1'!D1");
//save!
package.SaveAs(new System.IO.FileInfo(@"c:\temp\demoOut.xlsSelect used worksheet dimension :
JavaScript:
//first way line95 on example1^
//second way
worksheet.Cells["F2:" + "F:" + worksheet.Dimension.Rows].Style.Numberformat.Format = "0.00";download :
v4.5.3 [nuget] - the last version of EPPlus under LGPL
latest - under Polyform Noncommercial
alternative lib :
https://github.com/ClosedXML/ClosedXML nuget