在以前接触的项目中,由于很多客户对微软Excel的操作比较熟练,客户经常要求系统支持对Excel文件的读写。用.NET传统方法对Excel进行读写时,往往会涉及到不同版本兼容的问题,导致在本地测试一切正常,但是到客户那经常会出现各种问题。后来发现一个Spire.XLS的NET库,可以很方便的对不同版本的Excel文件进度操作,还可以生成各种常见的图形。下面结合一个简单的例子,看一下效果:
1、首先要安装Spire.XLS,可以自定百度,然后建立一个windows应用程序WinExcelDemo,注意添加相关dll引用:
2、编写代码,这里当主界面打开时,就开始进行Excel的数据准备和文件生产操作,代码如下:
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Threading.Tasks; 9 using System.Windows.Forms; 10 11 namespace WinExcelDemo 12 { 13 using Spire.Xls; 14 using Spire.Xls.Charts; 15 public partial class Form1 : Form 16 { 17 public Form1() 18 { 19 InitializeComponent(); 20 } 21 22 private void Form1_Load(object sender, EventArgs e) 23 { 24 Run(); 25 } 26 private void Run() 27 { 28 Workbook workbook = new Workbook(); 29 30 //初始化 工作簿worksheet 31 workbook.CreateEmptySheets(1); 32 Worksheet sheet = workbook.Worksheets[0]; 33 sheet.Name = "数据"; 34 sheet.GridLinesVisible = false; 35 36 //创建图形数据 37 CreateChartData(sheet); 38 //在工作簿中添加一个新的图表 39 Chart chart = sheet.Charts.Add(); 40 41 //设置数据的区域 42 chart.DataRange = sheet.Range["A1:C5"]; 43 chart.SeriesDataFromRange = false; 44 45 //设置图的位置 46 chart.LeftColumn = 1; 47 chart.TopRow = 6; 48 chart.RightColumn = 11; 49 chart.BottomRow = 29; 50 //图类型 51 chart.ChartType = ExcelChartType.Area; 52 //图标题 53 chart.ChartTitle = "Sales market by country"; 54 chart.ChartTitleArea.IsBold = true; 55 chart.ChartTitleArea.Size = 12; 56 57 chart.PrimaryCategoryAxis.Title = "Country"; 58 chart.PrimaryCategoryAxis.Font.IsBold = true; 59 chart.PrimaryCategoryAxis.TitleArea.IsBold = true; 60 61 chart.PrimaryValueAxis.Title = "Sales(in Dollars)"; 62 chart.PrimaryValueAxis.HasMajorGridLines = false; 63 chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; 64 chart.PrimaryValueAxis.MinValue = 1000; 65 chart.PrimaryValueAxis.TitleArea.IsBold = true; 66 67 68 foreach (Spire.Xls.Charts.ChartSerie cs in chart.Series) 69 { 70 //不同颜色 71 cs.Format.Options.IsVaryColor = true; 72 //显示数据标签 73 cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true; 74 } 75 //Legend位置 76 chart.Legend.Position = LegendPositionType.Top; 77 workbook.SaveToFile("Sample.xls"); 78 ExcelDocViewer(workbook.FileName); 79 } 80 81 private void CreateChartData(Worksheet sheet) 82 { 83 //Country 84 sheet.Range["A1"].Value = "Country"; 85 sheet.Range["A2"].Value = "Cuba"; 86 sheet.Range["A3"].Value = "Mexico"; 87 sheet.Range["A4"].Value = "France"; 88 sheet.Range["A5"].Value = "German"; 89 90 //Jun 91 sheet.Range["B1"].Value = "Jun"; 92 sheet.Range["B2"].NumberValue = 6000; 93 sheet.Range["B3"].NumberValue = 8000; 94 sheet.Range["B4"].NumberValue = 9000; 95 sheet.Range["B5"].NumberValue = 8500; 96 97 //Jun 98 sheet.Range["C1"].Value = "Aug"; 99 sheet.Range["C2"].NumberValue = 3000; 100 sheet.Range["C3"].NumberValue = 2000; 101 sheet.Range["C4"].NumberValue = 2300; 102 sheet.Range["C5"].NumberValue = 4200; 103 104 //Style 105 sheet.Range["A1:C1"].Style.Font.IsBold = true; 106 sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow; 107 sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1; 108 sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange; 109 sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise; 110 111 //Border 112 sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128); 113 sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin; 114 sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128); 115 sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin; 116 sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128); 117 sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin; 118 sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128); 119 sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin; 120 121 sheet.Range["B2:C5"].Style.NumberFormat = ""$"#,##0"; 122 } 123 124 private void ExcelDocViewer(string fileName) 125 { 126 try 127 { 128 //打开生成的Excel 129 System.Diagnostics.Process.Start(fileName); 130 } 131 catch { } 132 } 133 } 134 }
3、运行代码,可以看到如下结果:
4、总结
上面只是该库很小一部分功能,该库还能对Excel进行分组、支持VBA扩展等功能,该库虽然强大,但是不开源,是商业软件。不过经过了解,Spire.XLS也提供免费版,只不过免费版有一个Excel文档不能超过5个sheet的限制。对于一些小项目,免费版也完全够用可以考虑。至于较大应用,则需要商业版。NOPI是开源的Excel库,也可以方便的进行Excel读写操作且无需安装office,但功能不及Spire.XLS全面。开源和非开源,存在即合理,各有优势。