• 如何在没有安装微软Excel环境下操作Excel文件?


    在以前接触的项目中,由于很多客户对微软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全面。开源和非开源,存在即合理,各有优势。

  • 相关阅读:
    查看windows以前连过的wifi密码
    winscp可能的替代品?
    查看windows的进程启动参数
    Ubuntu Server 安全补丁、版本升级
    AtCoder Beginner Contest 217 题解
    F. Alice and Recoloring 1&2
    E. Bored Bakry
    C. Bakry and Partitioning
    2021牛客多校 第四场
    AES解密报Given final block not properly padded
  • 原文地址:https://www.cnblogs.com/isaboy/p/excel.html
Copyright © 2020-2023  润新知