c#用EPPLUS操作excel
参考:
http://www.cnblogs.com/rumeng/p/3785748.html
http://www.cnblogs.com/libla/p/5824296.html#3818995
结果:
首先新建一个winform程序,然后加一个button,
之后双击button1,进入代码区域,输入下面的代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 using OfficeOpenXml; 2 using OfficeOpenXml.Drawing; 3 using OfficeOpenXml.Drawing.Chart; 4 using OfficeOpenXml.Style; 5 using System; 6 using System.Collections.Generic; 7 using System.ComponentModel; 8 using System.Data; 9 using System.Drawing; 10 using System.IO; 11 using System.Linq; 12 using System.Text; 13 using System.Threading.Tasks; 14 using System.Windows.Forms; 15 16 namespace WindowsFormsApplication1epplus 17 { 18 public partial class Form1 : Form 19 { 20 public Form1() 21 { 22 InitializeComponent(); 23 } 24 25 private void button1_Click(object sender, EventArgs e) 26 { 27 using (ExcelPackage package = new ExcelPackage(new FileStream(@"E:\test.xlsx", FileMode.Open))) 28 { 29 for (int i = 1; i <= package.Workbook.Worksheets.Count; ++i)//循环sheet 30 { 31 ExcelWorksheet sheet = package.Workbook.Worksheets[i]; 32 for (int j = sheet.Dimension.Start.Column, k = sheet.Dimension.End.Column; j <= k; j++) 33 { 34 for (int m = sheet.Dimension.Start.Row, n = sheet.Dimension.End.Row; m <= n; m++) 35 { 36 string str = GetValue(sheet, m, j); 37 if (str != null) 38 { 39 // do something 40 } 41 } 42 } 43 } 44 } 45 46 47 48 49 FileInfo newFile = new FileInfo(@"E:\test.xlsx"); 50 if (newFile.Exists) 51 { 52 newFile.Delete(); 53 newFile = new FileInfo(@"E:\test.xlsx"); 54 } 55 using (ExcelPackage package = new ExcelPackage(newFile)) 56 { 57 ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test"); 58 59 worksheet.Cells.Style.WrapText = true; 60 worksheet.View.ShowGridLines = false;//去掉sheet的网格线 61 62 worksheet.Cells[1, 1].Value = "名称"; 63 worksheet.Cells[1, 2].Value = "价格"; 64 worksheet.Cells[1, 3].Value = "销量"; 65 66 worksheet.Cells[2, 1].Value = "大米"; 67 worksheet.Cells[2, 2].Value = 56; 68 worksheet.Cells[2, 3].Value = 100; 69 70 worksheet.Cells[3, 1].Value = "玉米"; 71 worksheet.Cells[3, 2].Value = 45; 72 worksheet.Cells[3, 3].Value = 150; 73 74 worksheet.Cells[4, 1].Value = "小米"; 75 worksheet.Cells[4, 2].Value = 38; 76 worksheet.Cells[4, 3].Value = 130; 77 78 worksheet.Cells[5, 1].Value = "糯米"; 79 worksheet.Cells[5, 2].Value = 22; 80 worksheet.Cells[5, 3].Value = 200; 81 82 using (ExcelRange range = worksheet.Cells[1, 1, 5, 3]) 83 { 84 range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; 85 range.Style.VerticalAlignment = ExcelVerticalAlignment.Center; 86 } 87 88 using (ExcelRange range = worksheet.Cells[1, 1, 1, 3]) 89 { 90 range.Style.Font.Bold = true; 91 range.Style.Font.Color.SetColor(Color.White); 92 range.Style.Font.Name = "微软雅黑"; 93 range.Style.Font.Size = 12; 94 range.Style.Fill.PatternType = ExcelFillStyle.Solid; 95 range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128)); 96 } 97 98 worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 99 worksheet.Cells[1, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 100 worksheet.Cells[1, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 101 102 worksheet.Cells[2, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 103 worksheet.Cells[2, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 104 worksheet.Cells[2, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 105 106 worksheet.Cells[3, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 107 worksheet.Cells[3, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 108 worksheet.Cells[3, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 109 110 worksheet.Cells[4, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 111 worksheet.Cells[4, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 112 worksheet.Cells[4, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 113 114 worksheet.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 115 worksheet.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 116 worksheet.Cells[5, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); 117 118 ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnClustered); 119 120 ExcelChartSerie serie = chart.Series.Add(worksheet.Cells[2, 3, 5, 3], worksheet.Cells[2, 1, 5, 1]); 121 serie.HeaderAddress = worksheet.Cells[1, 3]; 122 123 chart.SetPosition(150, 10); 124 chart.SetSize(500, 300); 125 chart.Title.Text = "销量走势"; 126 chart.Title.Font.Color = Color.FromArgb(89, 89, 89); 127 chart.Title.Font.Size = 15; 128 chart.Title.Font.Bold = true; 129 chart.Style = eChartStyle.Style15; 130 chart.Legend.Border.LineStyle = eLineStyle.Solid; 131 chart.Legend.Border.Fill.Color = Color.FromArgb(217, 217, 217); 132 133 package.Save(); 134 } 135 136 137 138 139 140 } 141 } 142 }
会提示错误, 注意要引用EPPlus,安装好效果如下:
方法是:
输入epplus,搜索,安装即可。
注意路径在E盘,没有E盘的肯定会出错。