目的:利用Epplus将資料儲存到Excel,并在Excel中插入圖表。
【WPF學習手記】Epplus寫入資料到Excel private void Button_Click(object sender, RoutedEventArgs e)
{
string file_path = "test.xlsx";
FileInfo newFile = new FileInfo(file_path);
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(file_path);
}
using (var package = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Results");
worksheet.Cells[1, 1].Value = "測試";
worksheet.Cells[1, 1, 1, 2].Merge = true; // 合并
worksheet.Cells[1, 1, 1, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // 居中
for (int i = 0; i < 10; i++)
{
worksheet.Cells[3 + i, 1].Value = i + 1;
worksheet.Cells[3 + i, 2].Value = Math.Sin(i / 10.0 * Math.PI);
}
// 網格線
worksheet.View.ShowGridLines = true;
// 繪制圖表
// 曲線
var linechart = worksheet.Drawings.AddChart("lineChart", eChartType.XYScatterSmoothNoMarkers);
linechart.SetPosition(3, 10, 2, 40);
linechart.SetSize(700, 500);
linechart.Legend.Remove(); // 删除圖例
var ser = linechart.Series.Add(worksheet.Cells[3, 2, 3 + 10, 2], worksheet.Cells[3, 1, 3 + 10, 1]);
//ser.Header = "Distribution"; // series名稱
linechart.XAxis.Title.Text = "XAxis";
linechart.XAxis.LogBase = 10;
linechart.XAxis.MajorTickMark = eAxisTickMark.Out;
linechart.XAxis.MinorTickMark = eAxisTickMark.In;
linechart.XAxis.MinorGridlines.LineStyle = OfficeOpenXml.Drawing.eLineStyle.LongDash;
linechart.XAxis.TickLabelPosition = eTickLabelPosition.Low;
linechart.YAxis.Title.Text = "YAxis";
linechart.YAxis.MajorTickMark = eAxisTickMark.Out;
linechart.YAxis.MinorTickMark = eAxisTickMark.In;
linechart.YAxis.MajorGridlines.LineStyle = OfficeOpenXml.Drawing.eLineStyle.LongDash;
linechart.YAxis.CrossesAt = 0.1;
linechart.YAxis.MinValue = 0;
linechart.Title.Text = "LineChart Example";
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
// 柱狀圖
var barchart = worksheet.Drawings.AddChart("barChart", eChartType.ColumnClustered);
barchart.SetPosition(3, 5, 15, 40);
barchart.SetSize(700, 500);
barchart.Legend.Remove(); // 删除圖例
barchart.Series.Add(worksheet.Cells[3, 2, 3 + 10, 2], worksheet.Cells[3, 1, 3 + 10, 1]);
barchart.XAxis.Title.Text = "XAxis";
barchart.XAxis.MajorTickMark = eAxisTickMark.Out;
barchart.XAxis.MinorTickMark = eAxisTickMark.None;
barchart.YAxis.Title.Text = "YAxis";
barchart.YAxis.MajorTickMark = eAxisTickMark.Out;
barchart.YAxis.MinorTickMark = eAxisTickMark.None;
barchart.YAxis.MinValue = 0;
barchart.YAxis.MajorGridlines.LineStyle = OfficeOpenXml.Drawing.eLineStyle.LongDash;
barchart.Title.Text = "BarChart Example";
// 儲存
package.Save();
}
}