c#用EPPLUS操作excel

  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 }