private void GenerateDataPointsSerie(ExcelChartSerie serie)
{
if (serie is IDrawingChartDataPoints dtpSerie)
{
int rows;
if (_chart.PivotTableSource == null)
{
var address = _chart.WorkSheet.Workbook.GetRange(_chart.WorkSheet, serie.Series);
if (address == null)
{
return;
}
rows = address.Rows;
}
else
{
rows = 48;
}
for (var i = 0; i < rows; i++)
{
if (!dtpSerie.DataPoints.ContainsKey(i))
{
dtpSerie.DataPoints.AddDp(i, "0000000D-5D51-4ADD-AFBE-74A932E24C89");
}
}
}
}
public static void InvisibleSerie(this ExcelBarChart chart, ExcelChartSerie series)
{
var i = 0;
var found = false;
foreach (var s in chart.Series)
{
if (s == series)
{
found = true;
break;
}
++i;
}
if (!found)
{
throw new InvalidOperationException("series not found.");
}
var nsm = chart.WorkSheet.Drawings.NameSpaceManager;
var nschart = nsm.LookupNamespace("c");
var nsa = nsm.LookupNamespace("a");
var node = chart.ChartXml.SelectSingleNode(@"c:chartSpace/c:chart/c:plotArea/c:barChart/c:ser[c:idx[@val='" + i.ToString(CultureInfo.InvariantCulture) + "']]", nsm);
var doc = chart.ChartXml;
var spPr = doc.CreateElement("c:spPr", nschart);
spPr.AppendChild(doc.CreateElement("a:noFill", nsa));
var ln = spPr.AppendChild(doc.CreateElement("a:ln", nsa));
ln.Attributes.Append(doc.CreateAttribute("w")).Value = "25400";
ln.AppendChild(doc.CreateElement("a:noFill", nsa));
node.AppendChild(spPr);
}
private dynamic GetChartSerie(eChartType type, ExcelChartSerie serie, RSerie rserie)
{
if (type.Equals(eChartType.Line))
{
var s = serie as ExcelLineChartSerie;
if (rserie.ColorString != null)
{
if (rserie.ColorString != null)
{
s.LineColor = rserie.ColorString;
}
// not use, i cannot change the marker color!
// s.Marker = eMarkerStyle.Diamond;
}
return(s);
}
//else if (type.Equals(eChartType.ColumnStacked))
//{
// var c = serie as ExcelChartSerie;
// c.ChartColor = rserie.ColorString;
// return c;
// }
return(serie);
}
public static void SetSeriesStyle(this ExcelLineChart chart, ExcelChartSerie series, Color color, decimal?thickness = null)
{
if (thickness < 0)
{
throw new ArgumentOutOfRangeException("thickness");
}
var i = 0;
var found = false;
foreach (var s in chart.Series)
{
if (s == series)
{
found = true;
break;
}
++i;
}
if (!found)
{
throw new InvalidOperationException("series not found.");
}
//Get the nodes
var nsm = chart.WorkSheet.Drawings.NameSpaceManager;
var nschart = nsm.LookupNamespace("c");
var nsa = nsm.LookupNamespace("a");
var node = chart.ChartXml.SelectSingleNode(@"c:chartSpace/c:chart/c:plotArea/c:lineChart/c:ser[c:idx[@val='" + i.ToString(CultureInfo.InvariantCulture) + "']]", nsm);
var doc = chart.ChartXml;
//Add the solid fill node
var spPr = doc.CreateElement("c:spPr", nschart);
var ln = spPr.AppendChild(doc.CreateElement("a:ln", nsa));
if (thickness.HasValue)
{
var w = ln.Attributes.Append(doc.CreateAttribute("w"));
w.Value = Math.Round(thickness.Value * 12700).ToString(CultureInfo.InvariantCulture);
var cap = ln.Attributes.Append(doc.CreateAttribute("cap"));
cap.Value = "rnd";
}
var solidFill = ln.AppendChild(doc.CreateElement("a:solidFill", nsa));
var srgbClr = solidFill.AppendChild(doc.CreateElement("a:srgbClr", nsa));
var valattrib = srgbClr.Attributes.Append(doc.CreateAttribute("val"));
//Set the color
valattrib.Value = color.ToHex().Substring(1);
node.AppendChild(spPr);
}
public void GenerateCharts()
{
double chartOffsetHeight = ((rowOffsetCount + 1) * defaultRowHeight) / 0.75;
RChart[] charts = chartXmlParser.GetCharts();
for (int i = 0; i < charts.Length; i++)
{
var chart = worksheet.Drawings.AddChart(charts[i].Title, eChartType.ColumnStacked) as ExcelChart;
if (!charts[i].ShowLegend)
{
chart.Legend.Remove();
}
chart.Title.Text = charts[i].Title;
chart.SetPosition(Convert.ToInt32(chartOffsetHeight), 10);
if (charts[i].Height.HasValue && charts[i].Width.HasValue)
{
chart.SetSize(charts[i].Width.Value, charts[i].Height.Value);
}
RChartType[] chartTypes = charts[i].ChartTypes;
for (var n = 0; n < chartTypes.Length; n++)
{
ExcelChart chartType = chart.PlotArea.ChartTypes.Add(chartTypes[n].Type);
RSerie[] series = chartTypes[n].Series;
for (var j = 0; j < series.Length; j++)
{
var cc = GetChartType(chartTypes[n].Type, chartType, series[j]);
ExcelChartSerie serie = cc.Series.Add(worksheet.Cells[series[j].YAixs], worksheet.Cells[series[j].XAixs]);
serie = GetChartSerie(chartTypes[n].Type, serie, series[j]);
if (series[j].HeaderAddress != null)
{
serie.HeaderAddress = worksheet.Cells[series[j].HeaderAddress];
}
if (series[j].UseSecondaryAxis)
{
chartType.UseSecondaryAxis = true;
chartType.YAxis.Format = series[j].YAxisFormatString;
}
//chartType.Grouping = eGrouping.Stacked;
}
chartOffsetHeight += (double)charts[i].Height.Value + 10;
}
// chartOffsetHeight += chart.To.RowOff * defaultRowHeight/0.75+10;
}
}
public static void SetDataPointStyle(this ExcelChart chart, ExcelChartSerie series, string color, string borderColor = "000000")
{
var i = 0;
var found = false;
foreach (var s in chart.Series)
{
if (s == series)
{
found = true;
break;
}
++i;
}
if (!found)
{
throw new InvalidOperationException("series not found.");
}
var nsm = chart.WorkSheet.Drawings.NameSpaceManager;
var nschart = nsm.LookupNamespace("c");
var nsa = nsm.LookupNamespace("a");
var node = chart.ChartXml.SelectSingleNode(@"c:chartSpace/c:chart/c:plotArea/c:barChart/c:ser[c:idx[@val='" + i.ToString(
CultureInfo.InvariantCulture) + "']]", nsm);
var doc = chart.ChartXml;
var spPr = doc.CreateElement("c:spPr", nschart);
var solidFill = spPr.AppendChild(doc.CreateElement("a:solidFill", nsa));
var srgbClr = solidFill.AppendChild(doc.CreateElement("a:srgbClr", nsa));
var valattrib = srgbClr.Attributes.Append(doc.CreateAttribute("val"));
valattrib.Value = color;
var ln = spPr.AppendChild(doc.CreateElement("a:ln", nsa));
var lnSolidFill = ln.AppendChild(doc.CreateElement("a:solidFill", nsa));
var lnSrgbClr = lnSolidFill.AppendChild(doc.CreateElement("a:srgbClr", nsa));
var lnValattrib = lnSrgbClr.Attributes.Append(doc.CreateAttribute("val"));
lnValattrib.Value = borderColor;
node.AppendChild(spPr);
}
private static void SetExcelChart(ExcelPackage package)
{
ExcelWorksheet sheet = package.Workbook.Worksheets.Add("chart");
// eChartType define chart type.
ExcelChart chart = sheet.Drawings.AddChart("chart", eChartType.ColumnClustered);
// set xAxis and yAxis
ExcelChartSerie serie = chart.Series.Add(sheet.Cells[2, 3, 5, 3], sheet.Cells[2, 1, 5, 1]);
// set legend for chart
serie.HeaderAddress = sheet.Cells[1, 3];
// set style for chart
chart.SetPosition(150, 10);
chart.SetSize(500, 300);
chart.Title.Text = "Chart Title";
chart.Title.Font.Color = Color.FromArgb(89, 89, 89);
chart.Title.Font.Bold = true;
chart.Title.Font.Size = 15;
chart.Style = eChartStyle.Style15;
chart.Legend.Border.LineStyle = eLineStyle.Solid;
chart.Legend.Border.Fill.Color = Color.FromArgb(217, 217, 217);
}
public void exportExcelFile()
{
//double timeScale = getTimeScaleFromString(scale);
//bool summaryPage = summaryPage;
string combindReportName = exportPath.Replace("\r", "");
Sample exemplarSample = groups[0].samples[0];
Dataset strainDataset = exemplarSample.strain.dataSetInfo;
Dataset stressDataset = exemplarSample.stress.dataSetInfo;
Dataset strainRateDataset = exemplarSample.strainRate.dataSetInfo;
Dataset timeDataset = exemplarSample.time.dataSetInfo;
Dataset frontFaceForceDataset = null;
Dataset backFaceForceDataset = null;
bool hasFaceForces = false;
if (exemplarSample.hasFaceForce())
{
hasFaceForces = true;
frontFaceForceDataset = exemplarSample.frontFaceForce.dataSetInfo;
backFaceForceDataset = exemplarSample.backFaceForce.dataSetInfo;
}
FileInfo newFile = new FileInfo(combindReportName);
if (newFile.Exists)
{
try
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(combindReportName);
}
catch
{
Console.WriteLine("Close File to Save Over");
//MessageBox.Show("Close File to save over");
}
}
ExcelPackage combinedReport = new ExcelPackage(newFile);
String strainHeaderUnits = (strainDataset.dataType == "" ? "" : strainDataset.dataType + " ") + strainDataset.dataName + " " + strainDataset.dataUnits;
String stressHeaderUnits = (stressDataset.dataType == "" ? "" : stressDataset.dataType + " ") + stressDataset.dataName + " " + stressDataset.dataUnits;
String strainRateHeaderUnits = (strainRateDataset.dataType == "" ? "" : strainRateDataset.dataType + " ") + strainRateDataset.dataName + " " + strainRateDataset.dataUnits;
String timeHeaderUnits = timeDataset.dataName + " " + timeDataset.dataUnits;//"Time (" + scale + ")";
String frontFaceForceUnits = "";
String backFaceForceUnits = "";
if (hasFaceForces)
{
frontFaceForceUnits = (frontFaceForceDataset.dataType == "" ? "" : frontFaceForceDataset.dataType + " ") + frontFaceForceDataset.dataName + " " + frontFaceForceDataset.dataUnits;
backFaceForceUnits = (backFaceForceDataset.dataType == "" ? "" : backFaceForceDataset.dataType + " ") + backFaceForceDataset.dataName + " " + backFaceForceDataset.dataUnits;
}
String strainTitle = strainDataset.dataName;
String stressTitle = stressDataset.dataName;
String strainRateTitle = strainRateDataset.dataName;
String timeTitle = timeDataset.dataName;
String frontFaceForceTitle = "";
String backFaceForceTitle = "";
if (hasFaceForces)
{
frontFaceForceTitle = frontFaceForceDataset.dataName;
backFaceForceTitle = backFaceForceDataset.dataName;
}
var SummarySheet = combinedReport.Workbook.Worksheets.Add("Summary");
var SumstrainRateChart = SummarySheet.Drawings.AddChart(strainRateHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
SumstrainRateChart.SetPosition(400, 0);
SumstrainRateChart.SetSize(600, 400);
SumstrainRateChart.XAxis.Title.Text = timeHeaderUnits;
SumstrainRateChart.YAxis.Title.Text = strainRateHeaderUnits;
SumstrainRateChart.Title.Text = strainRateTitle + " vs " + timeTitle;
SumstrainRateChart.YAxis.MinValue = 0;
SumstrainRateChart.XAxis.MinValue = 0;
var SumstrainChart = SummarySheet.Drawings.AddChart(strainHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
SumstrainChart.SetPosition(0, 0);
SumstrainChart.SetSize(600, 400);
SumstrainChart.XAxis.Title.Text = timeHeaderUnits;
SumstrainChart.YAxis.Title.Text = strainHeaderUnits;
SumstrainChart.Title.Text = strainTitle + " vs " + timeTitle;
SumstrainChart.YAxis.MinValue = 0;
SumstrainChart.XAxis.MinValue = 0;
var SumstressChart = SummarySheet.Drawings.AddChart(stressHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
SumstressChart.SetPosition(0, 600);
SumstressChart.SetSize(600, 400);
SumstressChart.XAxis.Title.Text = timeHeaderUnits;
SumstressChart.YAxis.Title.Text = stressHeaderUnits;
SumstressChart.Title.Text = stressTitle + " vs " + timeTitle;
SumstressChart.YAxis.MinValue = 0;
SumstressChart.XAxis.MinValue = 0;
var SumstressStrainChart = SummarySheet.Drawings.AddChart(stressHeaderUnits + " vs " + strainHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
SumstressStrainChart.SetPosition(400, 600);
SumstressStrainChart.SetSize(600, 400);
SumstressStrainChart.XAxis.Title.Text = strainHeaderUnits;
SumstressStrainChart.YAxis.Title.Text = stressHeaderUnits;
SumstressStrainChart.Title.Text = stressTitle + " vs " + strainTitle;
SumstressStrainChart.YAxis.MinValue = 0;
SumstressStrainChart.XAxis.MinValue = 0;
int idx = 0;
foreach (Group group in groups)
{
var Sheet = combinedReport.Workbook.Worksheets.Add(group.name);
//FileInfo placeHolderFile = new FileInfo("tmp");
//ExcelPackage placeHolderExcel = new ExcelPackage(placeHolderFile);
//var fakeSheet = placeHolderExcel.Workbook.Worksheets.Add("placeholder");
int columnCountName = 21;
int columnCountLabels = 21;
int spaceName = 5;
foreach (Sample sample in group.samples)
{
double[] timeData = sample.time.data;
double[] stressData = sample.stress.data;
double[] strainData = sample.strain.data;
double[] strainRateData = sample.strainRate.data;
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "1"].Value = sample.name;
columnCountName += spaceName;
//time
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = timeHeaderUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = timeData[i];
}
columnCountLabels++;
//Strain Rate
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = strainRateHeaderUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = strainRateData[i];
}
columnCountLabels++;
//Strain
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = strainHeaderUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = strainData[i];
}
columnCountLabels++;
//Stress
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = stressHeaderUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = stressData[i];
}
columnCountLabels++;
if (sample.hasFaceForce())
{
double[] frontFaceData = sample.frontFaceForce.data;
double[] backFaceData = sample.backFaceForce.data;
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = frontFaceForceUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = frontFaceData[i];
}
columnCountLabels++;
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = backFaceForceUnits;
for (int i = 0; i < timeData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = backFaceData[i];
}
columnCountLabels++;
columnCountLabels++;
}
else
{
columnCountLabels += 3;
}
}
var strainChart = Sheet.Drawings.AddChart(strainHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
strainChart.SetPosition(0, 0);
strainChart.SetSize(600, 400);
strainChart.XAxis.Title.Text = timeHeaderUnits;
strainChart.YAxis.Title.Text = strainHeaderUnits;
strainChart.Title.Text = strainTitle + " vs " + timeTitle;
strainChart.YAxis.MinValue = 0;
strainChart.XAxis.MinValue = 0;
var strainRateChart = Sheet.Drawings.AddChart(strainRateHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
strainRateChart.SetPosition(400, 0);
strainRateChart.SetSize(600, 400);
strainRateChart.XAxis.Title.Text = timeHeaderUnits;
strainRateChart.YAxis.Title.Text = strainRateHeaderUnits;
strainRateChart.Title.Text = strainRateTitle + " vs " + timeTitle;
strainRateChart.YAxis.MinValue = 0;
strainRateChart.XAxis.MinValue = 0;
var stressChart = Sheet.Drawings.AddChart(stressHeaderUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
stressChart.SetPosition(0, 600);
stressChart.SetSize(600, 400);
stressChart.XAxis.Title.Text = timeHeaderUnits;
stressChart.YAxis.Title.Text = stressHeaderUnits;
stressChart.Title.Text = stressTitle + " vs " + timeTitle;
stressChart.YAxis.MinValue = 0;
stressChart.XAxis.MinValue = 0;
var stressStrainChart = Sheet.Drawings.AddChart(stressHeaderUnits + " vs " + strainHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
stressStrainChart.SetPosition(400, 600);
stressStrainChart.SetSize(600, 400);
stressStrainChart.XAxis.Title.Text = strainHeaderUnits;
stressStrainChart.YAxis.Title.Text = stressHeaderUnits;
stressStrainChart.Title.Text = stressTitle + " vs " + strainTitle;
stressStrainChart.YAxis.MinValue = 0;
stressStrainChart.XAxis.MinValue = 0;
ExcelChart frontFaceForceChart = null;
ExcelChart backFaceForceChart = null;
if (hasFaceForces)
{
frontFaceForceChart = Sheet.Drawings.AddChart("front " + frontFaceForceUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
backFaceForceChart = Sheet.Drawings.AddChart("back " + backFaceForceUnits + " vs " + timeHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
frontFaceForceChart.SetPosition(800, 0);
frontFaceForceChart.SetSize(600, 400);
frontFaceForceChart.XAxis.Title.Text = timeHeaderUnits;
frontFaceForceChart.YAxis.Title.Text = frontFaceForceUnits;
frontFaceForceChart.Title.Text = frontFaceForceTitle + " vs " + timeTitle;
frontFaceForceChart.YAxis.MinValue = 0;
frontFaceForceChart.XAxis.MinValue = 0;
backFaceForceChart.SetPosition(800, 600);
backFaceForceChart.SetSize(600, 400);
backFaceForceChart.XAxis.Title.Text = timeHeaderUnits;
backFaceForceChart.YAxis.Title.Text = backFaceForceUnits;
backFaceForceChart.Title.Text = backFaceForceTitle + " vs " + timeTitle;
backFaceForceChart.YAxis.MinValue = 0;
backFaceForceChart.XAxis.MinValue = 0;
}
int newColumnHunter = 21;
int trialnumber = 1;
bool trialAddedToSummary = false;
foreach (Sample sample in group.samples)
{
int endIndex = sample.time.data.Length - 1;
var timeExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter) + "3:" + GetExcelColumnName(newColumnHunter) + (endIndex + 2).ToString()];
var StrainRateExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter + 1) + "3:" + GetExcelColumnName(newColumnHunter + 1) + (endIndex + 2).ToString()];
var strainExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter + 2) + "3:" + GetExcelColumnName(newColumnHunter + 2) + (endIndex + 2).ToString()];
var stressExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter + 3) + "3:" + GetExcelColumnName(newColumnHunter + 3) + (endIndex + 2).ToString()];
var forceIncidentExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter + 4) + "3:" + GetExcelColumnName(newColumnHunter + 4) + (endIndex + 2).ToString()];
var forceTransmissionExcel = combinedReport.Workbook.Worksheets[group.name].Cells[GetExcelColumnName(newColumnHunter + 5) + "3:" + GetExcelColumnName(newColumnHunter + 5) + (endIndex + 2).ToString()];
newColumnHunter += 7;
var strainRateChartSeries = strainRateChart.Series.Add(StrainRateExcel, timeExcel);
var strainChartSeries = strainChart.Series.Add(strainExcel, timeExcel);
var stressChartSeries = stressChart.Series.Add(stressExcel, timeExcel);
var stressStrainSeries = stressStrainChart.Series.Add(stressExcel, strainExcel);
//initialize the face force charts
ExcelChartSerie forceIncidentSeries = null; //always_fake.Series.Add(stressExcel, strainExcel);
ExcelChartSerie forceTransmissionSeries = null; //always_fake.Series.Add(stressExcel, strainExcel);
if (sample.hasFaceForce())
{
forceIncidentSeries = frontFaceForceChart.Series.Add(forceIncidentExcel, timeExcel);
forceTransmissionSeries = backFaceForceChart.Series.Add(forceTransmissionExcel, timeExcel);
}
if ((oneTrialOnSummaryPerGroup && !trialAddedToSummary) || !oneTrialOnSummaryPerGroup)
{
var sumstrainSeries = SumstrainChart.Series.Add(strainExcel, timeExcel);
var sumStressSeries = SumstressChart.Series.Add(stressExcel, timeExcel);
var sumStrainRateSeries = SumstrainRateChart.Series.Add(StrainRateExcel, timeExcel);
var sumStressStrainSeries = SumstressStrainChart.Series.Add(stressExcel, strainExcel);
sumstrainSeries.Header = group.name;
sumStressSeries.Header = group.name;
sumStrainRateSeries.Header = group.name;
sumStressStrainSeries.Header = group.name;
// int sumColorSpot = (idx) % summaryColors.Length;
sumstrainSeries.LineColor = group.color.TrimStart('#').Substring(0, 6); //summaryColors[sumColorSpot];
sumStressSeries.LineColor = group.color.TrimStart('#').Substring(0, 6);;; //summaryColors[sumColorSpot];
sumStrainRateSeries.LineColor = group.color.TrimStart('#').Substring(0, 6);;; //summaryColors[sumColorSpot];
sumStressStrainSeries.LineColor = group.color.TrimStart('#').Substring(0, 6);;; //summaryColors[sumColorSpot];
}
strainRateChartSeries.Header = sample.name;
strainChartSeries.Header = sample.name;
stressChartSeries.Header = sample.name;
stressStrainSeries.Header = sample.name;
int colorSpot = (trialnumber - 1) % trialColors.Length;
int transColor = (trialnumber) % trialColors.Length;
strainRateChartSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
strainChartSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
stressChartSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
stressStrainSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
if (sample.hasFaceForce())
{
forceIncidentSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
forceTransmissionSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
forceIncidentSeries.Header = sample.name;
forceTransmissionSeries.Header = sample.name;
}
trialnumber++;
trialAddedToSummary = true;
}
idx++;
}
if (this.referenceSamples.Count > 0)
{
String SHEET_NAME = "Reference Samples";
var Sheet = combinedReport.Workbook.Worksheets.Add(SHEET_NAME);
int columnCountName = 11;
int columnCountLabels = 11;
int spaceName = 5;
foreach (ReferenceSample sample in this.referenceSamples)
{
double[] stressData = sample.stressData.data;
double[] strainData = sample.strainData.data;
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "1"].Value = sample.name;
columnCountName += spaceName;
//Strain
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = strainHeaderUnits;
for (int i = 0; i < strainData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = strainData[i];
}
columnCountLabels++;
//Stress
Sheet.Cells[GetExcelColumnName(columnCountLabels) + "2"].Value = stressHeaderUnits;
for (int i = 0; i < stressData.Length; i++)
{
Sheet.Cells[GetExcelColumnName(columnCountLabels) + (i + 3).ToString()].Value = stressData[i];
}
columnCountLabels++;
columnCountLabels += 1;
}
var stressStrainChart = Sheet.Drawings.AddChart(stressHeaderUnits + " vs " + strainHeaderUnits, eChartType.XYScatterSmoothNoMarkers);
stressStrainChart.SetPosition(0, 0);
stressStrainChart.SetSize(600, 400);
stressStrainChart.XAxis.Title.Text = strainHeaderUnits;
stressStrainChart.YAxis.Title.Text = stressHeaderUnits;
stressStrainChart.Title.Text = stressTitle + " vs " + strainTitle;
stressStrainChart.YAxis.MinValue = 0;
stressStrainChart.XAxis.MinValue = 0;
int newColumnHunter = 10;
int trialnumber = 1;
foreach (ReferenceSample sample in this.referenceSamples)
{
int endIndex = sample.strainData.data.Length - 1;
var strainExcel = combinedReport.Workbook.Worksheets[SHEET_NAME].Cells[GetExcelColumnName(newColumnHunter + 1) + "3:" + GetExcelColumnName(newColumnHunter + 1) + (endIndex + 2).ToString()];
var stressExcel = combinedReport.Workbook.Worksheets[SHEET_NAME].Cells[GetExcelColumnName(newColumnHunter + 2) + "3:" + GetExcelColumnName(newColumnHunter + 2) + (endIndex + 2).ToString()];
newColumnHunter += 3;
var stressStrainSeries = stressStrainChart.Series.Add(stressExcel, strainExcel);
var sumStressStrainSeries = SumstressStrainChart.Series.Add(stressExcel, strainExcel);
sumStressStrainSeries.Header = SHEET_NAME;
int sumColorSpot = (idx) % summaryColors.Length;
sumStressStrainSeries.LineColor = summaryColors[sumColorSpot];
stressStrainSeries.Header = sample.name;
int colorSpot = (trialnumber - 1) % trialColors.Length;
int transColor = (trialnumber) % trialColors.Length;
stressStrainSeries.LineColor = sample.color == null ? trialColors[colorSpot] : sample.color;
trialnumber++;
}
}
if (!makeSummarypage)
{
combinedReport.Workbook.Worksheets.Delete(combinedReport.Workbook.Worksheets["Summary"]);
}
combinedReport.Save();
}
private static void SetExcelChartDemo(ExcelPackage package)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");
worksheet.Cells.Style.WrapText = true;
worksheet.View.ShowGridLines = false;//去掉sheet的网格线
worksheet.Cells[1, 1].Value = "名称";
worksheet.Cells[1, 2].Value = "价格";
worksheet.Cells[1, 3].Value = "销量";
worksheet.Cells[2, 1].Value = "大米";
worksheet.Cells[2, 2].Value = 56;
worksheet.Cells[2, 3].Value = 100;
worksheet.Cells[3, 1].Value = "玉米";
worksheet.Cells[3, 2].Value = 45;
worksheet.Cells[3, 3].Value = 150;
worksheet.Cells[4, 1].Value = "小米";
worksheet.Cells[4, 2].Value = 38;
worksheet.Cells[4, 3].Value = 130;
worksheet.Cells[5, 1].Value = "糯米";
worksheet.Cells[5, 2].Value = 22;
worksheet.Cells[5, 3].Value = 200;
using (ExcelRange range = worksheet.Cells[1, 1, 5, 3])
{
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
using (ExcelRange range = worksheet.Cells[1, 1, 1, 3])
{
range.Style.Font.Bold = true;
range.Style.Font.Color.SetColor(Color.White);
range.Style.Font.Name = "微软雅黑";
range.Style.Font.Size = 12;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));
}
worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[1, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[1, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[2, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[2, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[2, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[3, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[3, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[3, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[4, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[4, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[4, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
worksheet.Cells[5, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnClustered);
ExcelChartSerie serie = chart.Series.Add(worksheet.Cells[2, 3, 5, 3], worksheet.Cells[2, 1, 5, 1]);
serie.HeaderAddress = worksheet.Cells[1, 3];
chart.SetPosition(150, 10);
chart.SetSize(500, 300);
chart.Title.Text = "销量走势";
chart.Title.Font.Color = Color.FromArgb(89, 89, 89);
chart.Title.Font.Size = 15;
chart.Title.Font.Bold = true;
chart.Style = eChartStyle.Style15;
chart.Legend.Border.LineStyle = eLineStyle.Solid;
chart.Legend.Border.Fill.Color = Color.FromArgb(217, 217, 217);
package.Save();
}
private static bool Export(List <DisplayResults> allDisplayResults, bool splitPackageIntoSheets,
out string errMsg, out MemoryStream excelStream)
{
errMsg = string.Empty;
excelStream = null;
try
{
if (allDisplayResults == null || allDisplayResults.Count == 0)
{
errMsg = "Nothing to export."; return(false);
}
HardDefinitions.ExportDescriptionMode descriptionMode = allDisplayResults.First().info.exportDescriptionMode;
List <SheetContent> sheetsContent = new List <SheetContent>();
if (!splitPackageIntoSheets)
{
foreach (DisplayResults displayResults in allDisplayResults)
{
if (displayResults == null || displayResults.displayPages == null || displayResults.displayPages.Count == 0)
{
continue;
}
List <ExportPage> expPages = GetPackagePages(displayResults);
SheetContent sheetContent = new SheetContent
{
Caption = displayResults.info.title,
SubCaption = displayResults.info.subtitle,
SheetName = displayResults.info.button,
Pages = expPages
};
sheetsContent.Add(sheetContent);
}
}
else // this option is only called with a single package (see ExportDisplayPages above)
{
DisplayResults displayResults = allDisplayResults[0];
if (displayResults == null || displayResults.displayPages == null || displayResults.displayPages.Count == 0)
{
errMsg = "Nothing to export."; return(false);
}
;
List <ExportPage> expPages = GetPackagePages(displayResults);
foreach (ExportPage expPage in expPages)
{
SheetContent sheetContent = new SheetContent
{
SheetName = expPage.PageName,
Caption = displayResults.info.title,
SubCaption = displayResults.info.subtitle,
Pages = new List <ExportPage>()
{
expPage
}
};
sheetsContent.Add(sheetContent);
}
}
if (sheetsContent.Count == 0)
{
errMsg = "Nothing to export."; return(false);
}
;
ExcelPackage excel = new ExcelPackage();
foreach (SheetContent sheetContent in sheetsContent)
{
string sheetName = VerifyUniqueSheetName(excel.Workbook.Worksheets, sheetContent.SheetName);
var workSheet = excel.Workbook.Worksheets.Add(sheetName);
int chartCount = 0;
int rPos = 1;
workSheet.Cells[rPos, 1].Style.Font.Bold = true;
workSheet.Cells[rPos, 1].Style.Font.Size = 16;
workSheet.Cells[rPos, 1].Style.Font.Color.SetColor(Color.DarkBlue);
workSheet.Cells[rPos++, 1].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(sheetContent.Caption));
if (!string.IsNullOrEmpty(sheetContent.SubCaption))
{
workSheet.Cells[rPos, 1].Style.Font.Color.SetColor(Color.DarkBlue);
workSheet.Cells[rPos, 1].Style.Font.Bold = true;
workSheet.Cells[rPos++, 1].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(sheetContent.SubCaption));
}
++rPos;
foreach (ExportPage exportPage in sheetContent.Pages)
{
if (!string.IsNullOrEmpty(exportPage.Caption))
{
workSheet.Cells[rPos, 1].Style.Font.Bold = true;
workSheet.Cells[rPos, 1].Style.Font.Size = 14;
workSheet.Cells[rPos++, 1].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(exportPage.Caption));
++rPos;
}
foreach (ExportTable exportTable in exportPage.Tables)
{
if (!string.IsNullOrEmpty(exportTable.Caption))
{
workSheet.Cells[rPos, 1].Style.Font.Bold = true;
workSheet.Cells[rPos, 1].Style.Font.Size = 12;
workSheet.Cells[rPos++, 1].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(exportTable.Caption));
}
if (!string.IsNullOrEmpty(exportTable.SubCaption))
{
workSheet.Cells[rPos, 1].Style.Font.Bold = true;
workSheet.Cells[rPos++, 1].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(exportTable.SubCaption));
}
for (int ch = 0; ch < exportTable.ColHeaders.Count; ++ch)
{
workSheet.Cells[rPos, ch + 2].Style.Font.Italic = true;
workSheet.Cells[rPos, ch + 2].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(exportTable.ColHeaders[ch]));
}
for (int rh = 1; rh <= exportTable.RowHeaders.Count; ++rh)
{
workSheet.Cells[rPos + rh, 1].Style.Font.Italic = true;
workSheet.Cells[rPos + rh, 1].Value = exportTable.RowHeaders[rh - 1];
}
ExcelRangeBase pastedTable = workSheet.Cells[++rPos, 2].LoadFromDataTable(exportTable.Content, false);
if (pastedTable != null) // may be null if for example Content does not have any rows (just column-headers)
{
for (int c = pastedTable.Start.Column; c <= pastedTable.End.Column; c++)
{
List <string> nf = exportTable.NumberFormats[c - pastedTable.Start.Column];
List <string> sv = exportTable.StringValues[c - pastedTable.Start.Column];
for (int r = pastedTable.Start.Row; r <= pastedTable.End.Row; r++)
{
if (sv[r - pastedTable.Start.Row] == null)
{
workSheet.Cells[r, c].Style.Numberformat.Format = nf[r - pastedTable.Start.Row];
}
else
{
workSheet.Cells[r, c].Value = sv[r - pastedTable.Start.Row];
}
}
}
if (exportTable.Graph != null && exportTable.Graph.allSeries.Count > 0)
{
List <eChartType> chartTypes = GetAllChartTypes(exportTable.Graph);
ExcelChart baseChart = (ExcelChart)workSheet.Drawings.AddChart("myChart" + (++chartCount), chartTypes[0]);
Dictionary <eChartType, ExcelChart> allAreas = new Dictionary <eChartType, ExcelChart>
{
{ chartTypes[0], baseChart }
};
for (int i = 1; i < chartTypes.Count; i++)
{
allAreas.Add(chartTypes[i], baseChart.PlotArea.ChartTypes.Add(chartTypes[i]));
}
baseChart.SetSize(900, 500);
baseChart.SetPosition(pastedTable.Start.Row, 0, pastedTable.End.Column, 50);
baseChart.Title.Text = exportTable.Graph.title;
baseChart.Axis[0].LabelPosition = eTickLabelPosition.Low;
if (exportTable.Graph.seriesInRows)
{
int labelPos = pastedTable.Start.Row - 1;
if (!string.IsNullOrEmpty(exportTable.Graph.axisX.valuesFrom))
{
for (int r = pastedTable.Start.Row; r <= pastedTable.End.Row; r++)
{
if (workSheet.Cells[r, pastedTable.Start.Column - 1].Value.ToString() == exportTable.Graph.axisX.valuesFrom)
{
labelPos = r;
}
}
}
ExcelRange axisLabels = workSheet.Cells[labelPos, pastedTable.Start.Column, labelPos, pastedTable.End.Column];
for (int r = pastedTable.Start.Row; r <= pastedTable.End.Row; r++)
{
string seriesName = workSheet.Cells[r, pastedTable.Start.Column - 1].Value.ToString();
DisplayResults.DisplayPage.DisplayTable.DisplayGraph.Series s = exportTable.Graph.allSeries.Where(x => x.name == seriesName).First();
if (s.visible)
{
ExcelChartSerie serie = allAreas[GetChartType(s)].Series.Add(workSheet.Cells[r, pastedTable.Start.Column, r, pastedTable.End.Column], axisLabels);
SetSerieDetails(s, serie);
}
}
}
else
{
int labelPos = pastedTable.Start.Column - 1;
if (!string.IsNullOrEmpty(exportTable.Graph.axisX.valuesFrom))
{
for (int c = pastedTable.Start.Column; c <= pastedTable.End.Column; c++)
{
if (workSheet.Cells[pastedTable.Start.Row - 1, c].Value.ToString() == exportTable.Graph.axisX.valuesFrom)
{
labelPos = c;
}
}
}
ExcelRange axisLabels = workSheet.Cells[pastedTable.Start.Row, labelPos, pastedTable.End.Row, labelPos];
for (int c = pastedTable.Start.Column; c <= pastedTable.End.Column; c++)
{
string seriesName = workSheet.Cells[pastedTable.Start.Row - 1, c].Value.ToString();
DisplayResults.DisplayPage.DisplayTable.DisplayGraph.Series s = exportTable.Graph.allSeries.Where(x => x.name == seriesName).First();
if (s.visible)
{
ExcelChartSerie serie = allAreas[GetChartType(s)].Series.Add(workSheet.Cells[pastedTable.Start.Row, c, pastedTable.End.Row, c], axisLabels);
SetSerieDetails(s, serie);
}
}
}
}
rPos += exportTable.RowHeaders.Count;
if (descriptionMode == HardDefinitions.ExportDescriptionMode.InSheets)
{
InsertDescription(workSheet, ref rPos, exportTable.description);
}
rPos++; // Keep a space between tables
}
}
if (descriptionMode == HardDefinitions.ExportDescriptionMode.InSheets)
{
InsertDescription(workSheet, ref rPos, exportPage.description);
}
rPos++; // Keep an extra space between pages
}
}
AddDescriptionPage(excel, allDisplayResults[0], descriptionMode);
// todo: not yet clear whether the file-selection should be in the presenter or the library
excelStream = new MemoryStream();
excel.SaveAs(excelStream);
excel.Dispose();
return(true);
}
catch (Exception exception) { errMsg = exception.Message; return(false); }
}
private static void SetSerieDetails(DisplayResults.DisplayPage.DisplayTable.DisplayGraph.Series s, ExcelChartSerie serie)
{
serie.Header = s.name;
if (!string.IsNullOrEmpty(s.colour))
{
Color sc = s.colour[0] == '#' ? Color.FromArgb(int.Parse("FF" + s.colour.Substring(1), System.Globalization.NumberStyles.HexNumber)) : Color.FromName(s.colour);
serie.Fill.Color = sc;
if (GetChartType(s) == eChartType.Line)
{
serie.Border.Fill.Color = sc;
}
if (serie is ExcelScatterChartSerie)
{
ExcelScatterChartSerie se = (serie as ExcelScatterChartSerie);
se.Marker = GetMarkerStyle(s);
se.MarkerSize = s.size;
se.MarkerColor = sc;
}
}
}
public Task Execute(IJobExecutionContext context)
{
try
{
const string reportTitle = "2013 年度五大公司实际情况与原计划的百分比";
var file = new FileInfo($"{_exportFileAddress.ExportExcel}\\Excel图表测试.xlsx");
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var package = new ExcelPackage(file);
#region research
var worksheet = package.Workbook.Worksheets.Add("Data");
var dataPercent = GetDataPercent();
//chart = Worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.Line) as ExcelLineChart;
if (worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.LineMarkers) is ExcelLineChart chart)
{
chart.Legend.Position = eLegendPosition.Right;
chart.Legend.Add();
chart.Title.Text = reportTitle; // 设置图表的名称
//chart.SetPosition (200, 50);// 设置图表位置
chart.SetSize(800, 400); // 设置图表大小
chart.ShowHiddenData = true;
//chart.YAxis.MinorUnit = 1;
chart.XAxis.MinorUnit = 1; // 设置 X 轴的最小刻度
//chart.DataLabel.ShowCategory = true;
chart.DataLabel.ShowPercent = true; // 显示百分比
// 设置月份
for (var col = 1; col <= dataPercent.Columns.Count; col++)
{
worksheet.Cells[1, col].Value = dataPercent.Columns[col - 1].ColumnName;
}
// 设置数据
for (var row = 1; row <= dataPercent.Rows.Count; row++)
{
for (var col = 1; col <= dataPercent.Columns.Count; col++)
{
var strValue = dataPercent.Rows[row - 1][col - 1].ToString();
if (col == 1)
{
worksheet.Cells[row + 1, col].Value = strValue;
}
else
{
var realValue = double.Parse(strValue !);
worksheet.Cells[row + 1, col].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + 1, col].Style.Numberformat.Format = "#0\\.00%"; // 设置数据的格式为百分比
worksheet.Cells[row + 1, col].Value = realValue;
if (realValue < 0.90d) // 如果小于 90% 则该单元格底色显示为红色
{
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Red);
}
else if (realValue >= 0.90d && realValue <= 0.95d) // 如果在 90% 与 95% 之间则该单元格底色显示为黄色
{
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
else
{
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor
.SetColor(Color.Green); // 如果大于 95% 则该单元格底色显示为绿色
}
}
}
//chartSerie = chart.Series.Add(worksheet.Cells["A2:M2"], worksheet.Cells["B1:M1"]);
//chartSerie.HeaderAddress = worksheet.Cells["A2"];
//chart.Series.Add () 方法所需参数为：chart.Series.Add (X 轴数据区，Y 轴数据区)
ExcelChartSerie chartSerie = chart.Series.Add(
worksheet.Cells[row + 1, 2, row + 1, 2 + dataPercent.Columns.Count - 2],
worksheet.Cells["B1:M1"]);
chartSerie.HeaderAddress = worksheet.Cells[row + 1, 1]; // 设置每条线的名称
}
// 因为假定每家公司至少完成了 80% 以上，所以这里设置 Y 轴的最小刻度为 80%，这样使图表上的折线更清晰
chart.YAxis.MinValue = 0.8d;
//chart.SetPosition (200, 50);// 可以通过制定左上角坐标来设置图表位置
// 通过指定图表左上角所在的行和列及对应偏移来指定图表位置
// 这里 CompanyNames.Length + 1 及 3 分别表示行和列
chart.SetPosition(CompanyNames.Length + 1, 10, 3, 20);
}
#endregion research
package.Save(); // 保存文件
}
catch (Exception ex)
{
_log.Error(ex);
}
return(Task.CompletedTask);
}
private void minChartExcel(List <string> listTime, List <string> listTps, string message, string TimeType)
{
string fileName = "CEDA_Tps" + DateTime.Now.ToString("yyyy_MM_dd_HHmmss") + ".xlsx";
string reportTitle = "MDT Smart Kit CEDA Tps统计。" + "\n" + message;
FileInfo file = new FileInfo(filePath + "\\data\\" + fileName);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = null;
ExcelChartSerie chartSerie = null;
ExcelChart chart = null;
worksheet = package.Workbook.Worksheets.Add("Tps_Data");
int inttime = listTime.Count;
if (inttime <= 720)
{
#region research
string[] arrayTime = new string[listTime.Count];
listTime.CopyTo(arrayTime, 0);
string[] arrayTps = new string[listTps.Count];
listTps.CopyTo(arrayTps, 0);
DataTable dt = GetDatasec(arrayTime, arrayTps, TimeType);
//chart = Worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.Line) as ExcelLineChart;
chart = worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.ColumnClustered) as ExcelChart;//设置图表样式
chart.Legend.Position = eLegendPosition.Right;
chart.Legend.Add();
string startTime = listTime[0];
string endTime = listTime[listTime.Count - 1];
DateTime start = DateTime.Parse(startTime);
DateTime end = DateTime.Parse(endTime);
string sumTime = (end - start).ToString();
string timeTitle = ",开始时间:" + startTime + ",结束时间:" + endTime + ",总耗时:" + sumTime;
chart.Title.Text = reportTitle + timeTitle;//设置图表的名称
chart.Title.Font.Size = 10;
//chart.SetPosition(200, 50);//设置图表位置
chart.SetSize(1300, 600); //设置图表大小
chart.ShowHiddenData = true;
chart.XAxis.MinorUnit = 1; //设置X轴的最小刻度
//设置月份
for (int col = 1; col <= dt.Columns.Count; col++)
{
worksheet.Cells[1, col].Value = dt.Columns[col - 1].ColumnName;
worksheet.Cells[1, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//设置数据
for (int row = 1; row <= dt.Rows.Count; row++)
{
for (int col = 1; col <= dt.Columns.Count; col++)
{
string strValue = dt.Rows[row - 1][col - 1].ToString();
if (col == 1)
{
worksheet.Cells[row + 1, col].Value = strValue;
worksheet.Cells[row + 1, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
else
{
double realValue = double.Parse(strValue);
worksheet.Cells[row + 1, col].Style.Fill.PatternType = ExcelFillStyle.Solid;
//worksheet.Cells[row + 1, col].Style.Numberformat.Format = "#0\\.00%";//设置数据的格式为百分比
worksheet.Cells[row + 1, col].Value = realValue;
if (realValue < 1)//如果小于90%则该单元格底色显示为红色
{
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Red);
}
else
{
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.GreenYellow);//如果大于95%则该单元格底色显示为绿色
}
}
}
//chart.Series.Add()方法所需参数为：chart.Series.Add(X轴数据区,Y轴数据区)
chartSerie = chart.Series.Add(worksheet.Cells[row + 1, 2, row + 1, dt.Columns.Count], worksheet.Cells[1, 2, 1, dt.Columns.Count + 1]);
chartSerie.HeaderAddress = worksheet.Cells[row + 1, 1];//设置每条线的名称 2 + dt.Columns.Count - 2
}
//因为假定每家公司至少完成了80%以上，所以这里设置Y轴的最小刻度为80%，这样使图表上的折线更清晰
chart.YAxis.MinValue = 0;
chart.SetPosition(3, 0, 0, 0);
#endregion research
}
else
{
int Nm = inttime / 720;
for (int i = 0; i < Nm; i++)
{
#region research
xy = 40 * i;
List <string> ltTime = new List <string>();
List <string> ltTps = new List <string>();
for (int x = 0; x < 720; x++)
{
ltTime.Add(listTime[x]);
}
listTime.RemoveRange(0, 720);
for (int y = 0; y < 720; y++)
{
ltTps.Add(listTps[y]);
}
listTps.RemoveRange(0, 720);
string[] arrayTime = new string[ltTime.Count];
ltTime.CopyTo(arrayTime, 0);
string[] arrayTps = new string[ltTps.Count];
ltTps.CopyTo(arrayTps, 0);
string startTime = ltTime[0];
string endTime = ltTime[ltTime.Count - 1];
DateTime start = DateTime.Parse(startTime);
DateTime end = DateTime.Parse(endTime);
string sumTime = (end - start).ToString();
string timeTitle = ",开始时间:" + startTime + ",结束时间:" + endTime + ",总耗时:" + sumTime;
DataTable dt = GetDatasec(arrayTime, arrayTps, TimeType);
//chart = Worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.Line) as ExcelLineChart;
chart = worksheet.Drawings.AddChart("ColumnStackedChart" + xy.ToString(), eChartType.ColumnClustered) as ExcelChart;//设置图表样式
chart.Legend.Position = eLegendPosition.Right;
chart.Legend.Add();
chart.Title.Text = reportTitle + timeTitle;//设置图表的名称
chart.Title.Font.Size = 10;
chart.ShowHiddenData = true;
//chart.YAxis.MinorUnit = 1;
chart.XAxis.MinorUnit = 1;//设置X轴的最小刻度
//设置月份
for (int col = 1; col <= dt.Columns.Count; col++)
{
worksheet.Cells[1 + xy, col].Value = dt.Columns[col - 1].ColumnName;
worksheet.Cells[1 + xy, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//设置数据
//for (int row = 1; row <= dt.Rows.Count; row++)
//{
for (int col = 1; col <= dt.Columns.Count; col++)
{
string strValue = dt.Rows[0][col - 1].ToString();
if (col == 1)
{
worksheet.Cells[2 + xy, col].Value = strValue;
worksheet.Cells[2 + xy, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
else
{
double realValue = double.Parse(strValue);
worksheet.Cells[2 + xy, col].Style.Fill.PatternType = ExcelFillStyle.Solid;
//worksheet.Cells[row + 1, col].Style.Numberformat.Format = "#0\\.00%";//设置数据的格式为百分比
worksheet.Cells[2 + xy, col].Value = realValue;
if (realValue < 1)//如果小于90%则该单元格底色显示为红色
{
worksheet.Cells[2 + xy, col].Style.Fill.BackgroundColor.SetColor(Color.Red);
}
else
{
worksheet.Cells[2 + xy, col].Style.Fill.BackgroundColor.SetColor(Color.GreenYellow);//如果大于95%则该单元格底色显示为绿色
}
}
}
chartSerie = chart.Series.Add(worksheet.Cells[2 + xy, 2, 2 + xy, dt.Columns.Count], worksheet.Cells[1 + xy, 2, 1 + xy, dt.Columns.Count + 1]);
chartSerie.HeaderAddress = worksheet.Cells[2 + xy, 1];//设置每条线的名称 2 + dt.Columns.Count - 2
chart.YAxis.MinValue = 0;
chart.SetPosition(4 + xy, 0, 0, 0);
chart.SetSize(1300, 600);//设置图表大小
#endregion research
}
if (listTime.Count > 0 && listTps.Count > 0)
{
xy = xy + 40;
string[] arrTime = new string[listTime.Count];
listTime.CopyTo(arrTime, 0);
string[] arrTps = new string[listTps.Count];
listTps.CopyTo(arrTps, 0);
string startTime2 = listTime[0];
string endTime2 = listTime[listTime.Count - 1];
DateTime start2 = DateTime.Parse(startTime2);
DateTime end2 = DateTime.Parse(endTime2);
string sumTime2 = (end2 - start2).ToString();
string timeTitle2 = ",开始时间:" + startTime2 + ",结束时间:" + endTime2 + ",总耗时:" + sumTime2;
if (arrTime.Length > 0 && arrTps.Length > 0)
{
DataTable dtSur = GetDatasec(arrTime, arrTps, TimeType);
chart = worksheet.Drawings.AddChart("ColumnStackedChart" + xy.ToString(), eChartType.ColumnClustered) as ExcelChart;//设置图表样式
chart.Legend.Position = eLegendPosition.Right;
chart.Legend.Add();
chart.Title.Text = reportTitle + timeTitle2;//设置图表的名称
chart.Title.Font.Size = 10;
chart.ShowHiddenData = true;
//chart.YAxis.MinorUnit = 1;
chart.XAxis.MinorUnit = 1;//设置X轴的最小刻度
//设置月份
for (int col = 1; col <= dtSur.Columns.Count; col++)
{
worksheet.Cells[1 + xy, col].Value = dtSur.Columns[col - 1].ColumnName;
worksheet.Cells[1 + xy, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
for (int col = 1; col <= dtSur.Columns.Count; col++)
{
string strValue = dtSur.Rows[0][col - 1].ToString();
if (col == 1)
{
worksheet.Cells[2 + xy, col].Value = strValue;
worksheet.Cells[2 + xy, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
else
{
double realValue = double.Parse(strValue);
worksheet.Cells[2 + xy, col].Style.Fill.PatternType = ExcelFillStyle.Solid;
//worksheet.Cells[row + 1, col].Style.Numberformat.Format = "#0\\.00%";//设置数据的格式为百分比
worksheet.Cells[2 + xy, col].Value = realValue;
if (realValue < 1)//如果小于90%则该单元格底色显示为红色
{
worksheet.Cells[2 + xy, col].Style.Fill.BackgroundColor.SetColor(Color.Red);
}
else
{
worksheet.Cells[2 + xy, col].Style.Fill.BackgroundColor.SetColor(Color.GreenYellow);//如果大于95%则该单元格底色显示为绿色
}
}
}
chartSerie = chart.Series.Add(worksheet.Cells[2 + xy, 2, 2 + xy, dtSur.Columns.Count], worksheet.Cells[1 + xy, 2, 1 + xy, dtSur.Columns.Count + 1]);
chartSerie.HeaderAddress = worksheet.Cells[2 + xy, 1];
chart.YAxis.MinValue = 0;
chart.SetPosition(3 + xy, 0, 0, 0);
chart.SetSize(1300, 600);//设置图表大小
}
}
}
package.Save();//保存文件
OpenFile(file.ToString());
}
}
public EPPlusChartSerie(ExcelChartSerie serie)
{
_serie = serie;
}
private void CreateChart(ExcelWorksheet worksheet, String nameKpi, Dictionary <String, List <Tuple <int, int> > > points,
ExcelRangeBase startCell, int paddingChart)
{
ExcelBarChart barChart = worksheet.Drawings.AddChart(nameKpi, eChartType.ColumnClustered) as ExcelBarChart;
barChart.Legend.Position = eLegendPosition.Bottom;
barChart.Title.Text = nameKpi;
string monthRangeString = points["month"]
.Select(e => ExcelCellBase.GetAddress(e.Item1, e.Item2))
.Join(",");
var rangeMonth = worksheet.Cells[$"{monthRangeString}"];
/* ----------------------- Fact ---------------------------------*/
string rangeFactString = points["fact"]
.Select(e => ExcelCellBase.GetAddress(e.Item1, e.Item2))
.Join(",");
var rangeFact = worksheet.Cells[$"{rangeFactString}"];
ExcelChartSerie factChartSerie = barChart.Series.Add(rangeFact, rangeMonth);
factChartSerie.Header = "Факт";
factChartSerie.TrendLines.Add(eTrendLine.Linear);
/* -------------------------- Target ------------------------------*/
string rangeTargetString = points["target"]
.Select(e => ExcelCellBase.GetAddress(e.Item1, e.Item2))
.Join(",");
var rangeTarget = worksheet.Cells[$"{rangeTargetString}"];
ExcelChart chartTarget = barChart.PlotArea.ChartTypes.Add(eChartType.Line);
chartTarget.Series.Add(rangeTarget, rangeMonth).Header = "Цель";
chartTarget.SetLineChartColor(1, 0, Color.Red);
/*/* -------------------------- Deviation ------------------------------#1#
* string rangeDeviationString = points["deviation"]
* .Select(e => ExcelCellBase.GetAddress(e.Item1,e.Item2))
* .Join(",");
* var rangeDeviation = worksheet.Cells[$"{rangeDeviationString}"];
*
* ExcelChart chartDeviation = barChart.PlotArea.ChartTypes.Add(eChartType.Line);
* chartDeviation.Series.Add(rangeDeviation,rangeMonth ).Header= "Отклонение";*/
/* -------------------------- CountOrder ------------------------------*/
string rangeCountOrderString = points["countOrder"]
.Select(e => ExcelCellBase.GetAddress(e.Item1, e.Item2))
.Join(",");
var rangeCountOrder = worksheet.Cells[$"{rangeCountOrderString}"];
ExcelChart chartCountOrder = barChart.PlotArea.ChartTypes.Add(eChartType.Line);
chartCountOrder.Series.Add(rangeCountOrder, rangeMonth).Header = "Кол-во заказов";
barChart?.SetSize(500, 400);
barChart?.SetPosition(startCell.Start.Row, 0, startCell.Start.Column, 0);
//barChart?.SetPosition(10+(paddingChart*500),500);
}
static void DrawTest()
{
//ExcelBarChart
//ExcelBubbleChart
//ExcelChart
//ExcelDoughnutChart
//ExcelLineChart
//ExcelOfPieChart
//ExcelPieChart
//ExcelRadarChart
//ExcelScatterChart
//ExcelSurfaceChart
var path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx");
if (File.Exists(path))
{
File.Delete(path);
}
using (ExcelPackage package = new ExcelPackage())
{
var hideWorksheet = package.Workbook.Worksheets.Add("dic");
hideWorksheet.Hidden = eWorkSheetHidden.VeryHidden;
hideWorksheet.Cells.Style.WrapText = true;
hideWorksheet.Cells[1, 1].Value = "名称";
hideWorksheet.Cells[1, 2].Value = "价格";
hideWorksheet.Cells[1, 3].Value = "销量";
hideWorksheet.Cells[2, 1].Value = "大米";
hideWorksheet.Cells[2, 2].Value = 56;
hideWorksheet.Cells[2, 3].Value = 100;
hideWorksheet.Cells[3, 1].Value = "玉米";
hideWorksheet.Cells[3, 2].Value = 45;
hideWorksheet.Cells[3, 3].Value = 150;
hideWorksheet.Cells[4, 1].Value = "小米";
hideWorksheet.Cells[4, 2].Value = 38;
hideWorksheet.Cells[4, 3].Value = 130;
hideWorksheet.Cells[5, 1].Value = "糯米";
hideWorksheet.Cells[5, 2].Value = 22;
hideWorksheet.Cells[5, 3].Value = 200;
using (ExcelRange range = hideWorksheet.Cells[1, 1, 5, 3])
{
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
using (ExcelRange range = hideWorksheet.Cells[1, 1, 1, 3])
{
range.Style.Font.Bold = true;
range.Style.Font.Color.SetColor(Color.White);
range.Style.Font.Name = "微软雅黑";
range.Style.Font.Size = 12;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));
}
hideWorksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[1, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[1, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[2, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[2, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[2, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[3, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[3, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[3, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[4, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[4, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[4, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
hideWorksheet.Cells[5, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");
worksheet.Cells.Style.WrapText = true;
worksheet.View.ShowGridLines = false;//去掉sheet的网格线
//ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnClustered);
ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ConeCol);
//ExcelChartSerie serie = chart.Series.Add(worksheet.Cells[2, 3, 5, 3], worksheet.Cells[2, 1, 5, 1]);
//serie.HeaderAddress = worksheet.Cells[1, 3];
ExcelChartSerie serie = chart.Series.Add("dic!$C$1:$C$5", "dic!$A$1:$A$5");
serie.HeaderAddress = hideWorksheet.Cells[1, 3];
chart.SetPosition(150, 10);
chart.SetSize(500, 300);
chart.Title.Text = "销量走势";
chart.Title.Font.Color = Color.FromArgb(89, 89, 89);
chart.Title.Font.Size = 15;
chart.Title.Font.Bold = true;
chart.Style = eChartStyle.Style15;
chart.Legend.Border.LineStyle = eLineStyle.Solid;
chart.Legend.Border.Fill.Color = Color.FromArgb(217, 217, 217);
package.SaveAs(new FileInfo(path));
}
}
static void Main()
{
FileInfo newFile = new FileInfo(@"EAR.xlsx");
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(@"EAR.xlsx");
}
string dir = Path.GetFullPath("../..");
string filePath = dir + "\\THC NII Report Template.xlsx";
FileInfo templateFile = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(templateFile))
{
//EaR summary
ExcelWorksheet shtES = package.Workbook.Worksheets["EaR summary"];
shtES.Cells[11, 4].Value = "Dn 200BP";
shtES.Cells[11, 5].Value = "Dn 100BP";
shtES.Cells[11, 6].Value = "Base";
shtES.Cells[11, 7].Value = "Up 100BP";
shtES.Cells[11, 8].Value = "Up 200BP";
shtES.Cells[11, 9].Value = "Up 300BP";
shtES.Cells[11, 10].Value = "Up 400BP ";
shtES.Cells[11, 11].Value = "Flattener";
shtES.Cells[11, 12].Value = "Ramp Up";
shtES.Cells[21, 4].Value = "Dn 200BP";
shtES.Cells[21, 5].Value = "Dn 100BP";
shtES.Cells[21, 6].Value = "Base";
shtES.Cells[21, 7].Value = "Up 100BP";
shtES.Cells[21, 8].Value = "Up 200BP";
shtES.Cells[21, 9].Value = "Up 300BP";
shtES.Cells[21, 10].Value = "Up 400BP ";
shtES.Cells[21, 11].Value = "Flattener";
shtES.Cells[21, 12].Value = "Ramp Up";
shtES.Cells[12, 4].Value = 18138;
shtES.Cells[12, 5].Value = 19032;
shtES.Cells[12, 6].Value = 20507;
shtES.Cells[12, 7].Value = 22503;
shtES.Cells[12, 8].Value = 24569;
shtES.Cells[12, 9].Value = 26659;
shtES.Cells[12, 10].Value = 28697;
shtES.Cells[12, 11].Value = 26756;
shtES.Cells[12, 12].Value = 20998;
shtES.Cells[14, 4].Value = -11.55;
shtES.Cells[14, 5].Value = -7.19;
shtES.Cells[14, 6].Value = 0.00;
shtES.Cells[14, 7].Value = 9.73;
shtES.Cells[14, 8].Value = 19.81;
shtES.Cells[14, 9].Value = 30.00;
shtES.Cells[14, 10].Value = 39.94;
shtES.Cells[14, 11].Value = 30.47;
shtES.Cells[14, 12].Value = 2.39;
shtES.Cells[24, 4].Value = -7.31;
shtES.Cells[24, 5].Value = -5.20;
shtES.Cells[24, 6].Value = 0.00;
shtES.Cells[24, 7].Value = 7.62;
shtES.Cells[24, 8].Value = 15.85;
shtES.Cells[24, 9].Value = 24.21;
shtES.Cells[24, 10].Value = 32.35;
shtES.Cells[24, 11].Value = 22.40;
shtES.Cells[24, 12].Value = 4.27;
//ExcelChart excelChart = shtES.Drawings.add
ExcelChart chart = shtES.Drawings.AddChart("chart", eChartType.ColumnClustered);
ExcelChartSerie serie = chart.Series.Add(shtES.Cells[12, 4, 12, 12], shtES.Cells[11, 4, 11, 12]);
var chartType = chart.PlotArea.ChartTypes.Add(eChartType.LineStacked);
chartType.UseSecondaryAxis = true;
ExcelChartSerie chartSerie = chartType.Series.Add(shtES.Cells[14, 2, 14, 12], shtES.Cells[11, 4, 11, 12]);
var charType2 = chart.PlotArea.ChartTypes.Add(eChartType.LineMarkers);
charType2.UseSecondaryAxis = true;
ExcelChartSerie excelChartSerie = charType2.Series.Add(shtES.Cells[24, 2, 24, 12], shtES.Cells[11, 4, 11, 12]);
chart.SetPosition(980, 155);
serie.HeaderAddress = shtES.Cells[11, 4];
chartSerie.HeaderAddress = shtES.Cells[11, 5];
excelChartSerie.HeaderAddress = shtES.Cells[11, 6];
//1st year projection
ExcelWorksheet sht1year = package.Workbook.Worksheets["1st year projection"];
sht1year.Cells.Style.Font.Name = "Calibri";
sht1year.Cells.Style.Border.BorderAround(ExcelBorderStyle.Medium);
Program pro = new Program();
FinProjection finProjection = pro.ReadFromJson("azf201906.json");
sht1year.Cells[7, 2].Value = "Scenario:Base Case";
sht1year.Cells[8, 2].Value = "ASSETS INTEREST INCOME";
sht1year.Cells[9, 2].Value = "LIABILITIES INTEREST COST";
sht1year.Cells[10, 2].Value = "NET INTEREST INCOME";
for (int i = 0; i <= 36; i++)
{
sht1year.Cells[8, i + 4].Value = finProjection.TotalLines.NetIncome.InterestIncome[i];
sht1year.Cells[9, i + 4].Value = finProjection.TotalLines.NetIncome.InterestCost[i];
sht1year.Cells[10, i + 4].Value = finProjection.TotalLines.NetIncome.NetInterestIncome[i];
double dblNIC = finProjection.TotalLines.NetIncome.NonInterestExpense.Value[i];
double dblNII = finProjection.TotalLines.NetIncome.NonInterestIncome.Value[i];
sht1year.Cells[11, i + 4].Value = dblNIC - dblNII;
sht1year.Cells[12, i + 4].Value = finProjection.TotalLines.NetIncome.LoanLossProvision[i];
sht1year.Cells[14, i + 4].Value = finProjection.TotalLines.NetIncome.TaxPayments[i];
sht1year.Cells[15, i + 4].Value = finProjection.TotalLines.NetIncome.NI[i];
sht1year.Cells[16, i + 4].Value = finProjection.TotalLines.NetIncome.DividendPayment[i];
sht1year.Cells[18, i + 4].Value = finProjection.TotalLines.NetIncome.THCNetChangeUnRealizedGain[i];
if (finProjection.TotalLines.Capital != null)
{
sht1year.Cells[19, i + 4].Value = finProjection.TotalLines.Capital.Equity[i];
}
}
for (int i = 11; i < 24; i++)
{
sht1year.Row(i).OutlineLevel = 1;
}
sht1year.Cells[11, 2].Value = "Non Interest Expense(income)";
sht1year.Cells[12, 2].Value = "Provision of losses";
sht1year.Cells[13, 2].Value = "Profit before taxes";
sht1year.Cells[14, 2].Value = "Tax";
sht1year.Cells[15, 2].Value = "Net Income";
sht1year.Cells[16, 2].Value = "Dividend Payment";
sht1year.Cells[17, 2].Value = "Retained Earning chg";
sht1year.Cells[18, 2].Value = "Unrealized G/L";
sht1year.Cells[6, 2].Value = "Date";
using (ExcelRange range = sht1year.Cells[7, 2, 10, 2])
{
range.Style.Font.Bold = true;
range.Style.Font.Name = "微软雅黑";
sht1year.Cells[7, 2].Value = "Scenario:Base Case";
sht1year.Cells[8, 2].Value = "ASSETS INTEREST INCOME";
sht1year.Cells[9, 2].Value = "LIABILITIES INTEREST COST";
sht1year.Cells[10, 2].Value = "NET INTEREST INCOME";
}
using (ExcelRange range = sht1year.Cells["B7"])
{
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));
}
sht1year.Cells[5, 15].Value = "Currency: USD .Amounts in 000s";
using (ExcelRange range = sht1year.Cells[11, 2, 18, 2])
{
range.Style.Font.Italic = true;
range.Style.Font.Name = "Calibri";
sht1year.Cells[11, 2].Value = "Non Interest Expense(income)";
sht1year.Cells[12, 2].Value = "Provision of losses";
sht1year.Cells[13, 2].Value = "Profit before taxes";
sht1year.Cells[14, 2].Value = "Tax";
sht1year.Cells[15, 2].Value = "Net Income";
sht1year.Cells[16, 2].Value = "Dividend Payment";
sht1year.Cells[17, 2].Value = "Retained Earning chg";
sht1year.Cells[18, 2].Value = "Unrealized G/L";
}
sht1year.Cells[19, 2].Value = "Equity";
sht1year.Cells[19, 2].Style.Font.Name = "Calibri";
sht1year.Cells[19, 2].Style.Font.Bold = true;
sht1year.Cells[19, 2].Style.Font.Italic = true;
sht1year.Cells[19, 2].Style.Font.Size = 11;
pro.PrepareSampleEaRLayoutSettings();
pro.WriteCOAData(sht1year, finProjection.COA, pro.settings, 21);//写入树的相关数据
package.SaveAs(newFile);
}
}
public static void Run()
{
string fileName = "ExcelReport-" + DateTime.Now.ToString("yyyy_MM_dd_HHmmss") + ".xlsx";
string reportTitle = "2013年度五大公司实际情况与原计划的百分比";
FileInfo file = new FileInfo("C:\\Excel\\" + fileName);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = null;
ExcelChartSerie chartSerie = null;
ExcelBarChart chart = null;
#region research
worksheet = package.Workbook.Worksheets.Add("Data");
DataTable dataPercent = GetDataPercent();
//chart = Worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.Line) as ExcelLineChart;
chart = worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.ColumnStacked) as ExcelBarChart;//设置图表样式
//chart.Legend.Position = eLegendPosition.Top;
//chart.Legend.Add();
chart.Legend.Remove();
chart.Title.Text = reportTitle; //设置图表的名称
//chart.SetPosition(200, 50);//设置图表位置
chart.SetSize(800, 400); //设置图表大小
chart.ShowHiddenData = true;
// chart.PlotArea.Fill.Color = Color.Red;
// chart.Fill.Color = Color.DarkBlue;
//chart.YAxis.MinorUnit = 1;
chart.XAxis.MinorUnit = 1;//设置X轴的最小刻度
chart.DataLabel.ShowValue = true;
//chart.YAxis.LabelPosition = eTickLabelPosition.High;
// chart.YAxis.TickLabelPosition = eTickLabelPosition.NextTo;
//chart.DataLabel.ShowCategory = true;
//chart.DataLabel.ShowPercent = true;//显示百分比
//设置月份
for (int col = 1; col <= dataPercent.Columns.Count; col++)
{
worksheet.Cells[1, col].Value = dataPercent.Columns[col - 1].ColumnName;
}
//设置数据
for (int row = 1; row <= dataPercent.Rows.Count; row++)
{
for (int col = 1; col <= dataPercent.Columns.Count; col++)
{
string strValue = dataPercent.Rows[row - 1][col - 1].ToString();
if (col == 1)
{
worksheet.Cells[row + 1, col].Value = strValue;
}
else
{
double realValue = double.Parse(strValue);
worksheet.Cells[row + 1, col].Style.Fill.PatternType = ExcelFillStyle.Solid;
// worksheet.Cells[row + 1, col].Style.Numberformat.Format = "#0\\.00%";//设置数据的格式为百分比
worksheet.Cells[row + 1, col].Value = realValue;
worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.White);
worksheet.Cells[row + 1, col].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
//if (realValue < 0.90d)//如果小于90%则该单元格底色显示为红色
//{
// worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Red);
//}
//else if (realValue >= 0.90d && realValue <= 0.95d)//如果在90%与95%之间则该单元格底色显示为黄色
//{
// worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
//}
//else
//{
// worksheet.Cells[row + 1, col].Style.Fill.BackgroundColor.SetColor(Color.Green);//如果大于95%则该单元格底色显示为绿色
//}
}
}
//chartSerie = chart.Series.Add(worksheet.Cells["A2:M2"], worksheet.Cells["B1:M1"]);
//chartSerie.HeaderAddress = worksheet.Cells["A2"];
//chart.Series.Add()方法所需参数为：chart.Series.Add(X轴数据区,Y轴数据区)
chart.Series.Add(worksheet.Cells[row + 1, 2, row + 1, 2 + dataPercent.Columns.Count - 2], worksheet.Cells[row, 2, row, 2 + dataPercent.Columns.Count - 2]);
//chartSerie.HeaderAddress = worksheet.Cells[row + 1, 1];//设置每条线的名称
}
//因为假定每家公司至少完成了80%以上，所以这里设置Y轴的最小刻度为80%，这样使图表上的折线更清晰
//chart.YAxis.MinValue = 0.8d;
//chart.SetPosition(200, 50);//可以通过制定左上角坐标来设置图表位置
//通过指定图表左上角所在的行和列及对应偏移来指定图表位置
//这里CommpanyNames.Length + 1及3分别表示行和列
chart.SetPosition(CommpanyNames.Length + 1, 0, 3, 0);
//chart.Border.Fill.Color = Color.Yellow;
#endregion research
package.Save();//保存文件
}
}
