C#使用FreeSpire.XLS创建Excel

谷凯Jump

已于 2022-12-22 16:28:21 修改

阅读量9.4k

点赞数 7

分类专栏： C# 文章标签： C# Spire Spire.XLS Excel Chart

于 2020-04-30 17:18:06 首次发布

本文链接：https://blog.csdn.net/jump_gu/article/details/105865249

版权

C# 专栏收录该内容

22 篇文章 1 订阅

订阅专栏

前言

对于使用C#程序生成Excel文件，我经历了以下几个阶段，强烈推荐使用Spire.XLS：

Microsoft.Office.Interop.Excel.dll
EPPlus.dll
Spire.XLS.dll

对于Spire.XLS的免费版本，Free Spire.XLS有以下限制，但一般够用了，其官网说明如下

免费 Spire.XLS 感兴趣。它是完全免费的，没有任何警告消息供您商业和个人使用。
免费版限于每个工作簿5张和每张200行。在读取或写入XLS或PDF文件期间会强制执行此限制。
从Free Spire.XLS v7.8开始，加载和保存.xlsx文件格式没有任何限制。
将Excel文件转换为PDF文件时，只能获取PDF文件的前3页。

DLL引用

使用NuGet搜索FreeSpire.XLS安装到项目即可

我们用下图的一个表格，来做示例代码操作

代码如下

using Spire.Xls;
using Spire.Xls.Charts;
using System;
using System.Data;
using System.Drawing;

namespace Spire_XLS_Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            //定义Excel保存路径，在临时目录中
            string strFileName = System.IO.Path.GetTempPath() + "DEMO_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".xlsx";

            //实例化Excel
            Workbook book = new Workbook();
            book.DefaultFontName = "Arial";
            Worksheet sheet1 = book.Worksheets[0];
            Worksheet sheet2 = book.Worksheets[1];
            //删除自动创建的Sheet3
            book.Worksheets[2].Remove();

            //加载数据
            DataTable dtMyData = GetMyData();

            //保存数据
            sheet1.Range[2, 2].Value2 = "我的销售额 2019";
            sheet1.Range[3, 2].Value2 = "月份";
            sheet1.Range[3, 3].Value2 = "销售额";
            sheet1.Range[3, 4].Value2 = "利润率";

            for (int i = 0; i < dtMyData.Rows.Count; i++)
            {
                sheet1.Range[4 + i, 2].Value2 = dtMyData.Rows[i]["月份"];
                sheet1.Range[4 + i, 2].NumberFormat = "yyyy年mm月";       //设置文本格式为日期
                sheet1.Range[4 + i, 3].Value2 = dtMyData.Rows[i]["销售额"];
                sheet1.Range[4 + i, 3].NumberFormat = "0,0";            //设置文本格式为金额(千分位)
                sheet1.Range[4 + i, 4].Value2 = dtMyData.Rows[i]["利润率"];
                sheet1.Range[4 + i, 4].NumberFormat = "0.00%";          //设置文本格式为百分比
            }

            //Style设置
            sheet1.Range[2, 2, 2, 4].Merge();
            sheet1.Range[2, 2, 2, 4].Style.Font.Size = 16;
            sheet1.Range[2, 2, 2, 4].Style.Font.IsBold = true;
            sheet1.Range[2, 2, 2, 4].Style.Font.IsItalic = true;
            sheet1.Range[2, 2, 2, 4].Style.Color = Color.FromArgb(255, 192, 0);
            sheet1.Range[3, 2, 3, 4].Style.Color = Color.GreenYellow;
            sheet1.Range[3, 2, 3, 4].HorizontalAlignment = HorizontalAlignType.Center;

            //边框设置
            for (int iRow = 3; iRow <= dtMyData.Rows.Count + 3; iRow++)
            {
                for (int iCol = 2; iCol <= 4; iCol++)
                {
                    sheet1.Range[iRow, iCol].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
                    sheet1.Range[iRow, iCol].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
                    sheet1.Range[iRow, iCol].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
                    sheet1.Range[iRow, iCol].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
                }
            }
            //自适应列
            for (int i = 2; i <= 4; i++)
            {
                sheet1.AutoFitColumn(i);
            }
            //自适应行
            sheet1.AutoFitRow(2);

            //产生Chart
            Chart chart1 = sheet1.Charts.Add();
            
            chart1.DataRange = sheet1.Range[3, 2, dtMyData.Rows.Count + 3, 4];
            chart1.SeriesDataFromRange = false;
            chart1.LeftColumn = 6;
            chart1.RightColumn = 15;
            chart1.TopRow = 2;
            chart1.BottomRow = 20;

            ChartSerie cs1 = chart1.Series[0];
            ChartSerie cs2 = chart1.Series[1];
            cs1.SerieType = ExcelChartType.ColumnClustered; //柱形图
            cs2.SerieType = ExcelChartType.LineMarkers; //折线图
            chart1.SecondaryCategoryAxis.IsMaxCross = true;
            //使用副坐标轴
            cs2.UsePrimaryAxis = false;
            //图表标题
            chart1.ChartTitle = "我的销售额 2019";

            //透视表
            PivotCache pivotCache = book.PivotCaches.Add(sheet1.Range[3, 2, dtMyData.Rows.Count + 3, 4]);
            PivotTable pivotTable = sheet2.PivotTables.Add("2019Summry", sheet2.Range["A1"], pivotCache);
            //设置行列值
            pivotTable.PivotFields[0].Axis = AxisTypes.Row;
            pivotTable.PivotFields[1].Axis = AxisTypes.Column;
            pivotTable.PivotFields[2].Axis = AxisTypes.Data;
            //添加汇总
            pivotTable.DataFields.Add(pivotTable.PivotFields[2], "Demo Pivot", SubtotalTypes.Sum);
            //设置标题
            pivotTable.Options.RowHeaderCaption = "日期";
            pivotTable.Options.ColumnHeaderCaption = "销售额";
            //设置透视表样式
            pivotTable.Options.RowLayout = PivotTableLayoutType.Tabular;
            
            //保存Excel，主要要选择相应的Excel版本
            book.SaveToFile(strFileName, ExcelVersion.Version2013);

            //打开生成的Excel文件
            System.Diagnostics.Process.Start(strFileName);

        }

        //模拟数据
        static DataTable GetMyData()
        {
            DataTable dt = new DataTable("我的销售额");
            dt.Columns.Add("月份", typeof(DateTime));
            dt.Columns.Add("主打产品", typeof(string));
            dt.Columns.Add("销售额", typeof(double));
            dt.Columns.Add("利润率", typeof(double));

            DataRow dr;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 1, 1); dr["主打产品"] = "手机"; dr["销售额"] = 21469; dr["利润率"] = 0.32;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 2, 1); dr["主打产品"] = "电脑"; dr["销售额"] = 36224; dr["利润率"] = 0.45;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 3, 1); dr["主打产品"] = "电视"; dr["销售额"] = 24619; dr["利润率"] = 0.35;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 4, 1); dr["主打产品"] = "手机"; dr["销售额"] = 35478; dr["利润率"] = 0.36;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 5, 1); dr["主打产品"] = "电视"; dr["销售额"] = 37168; dr["利润率"] = 0.48;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 6, 1); dr["主打产品"] = "电脑"; dr["销售额"] = 37034; dr["利润率"] = 0.36;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 7, 1); dr["主打产品"] = "手机"; dr["销售额"] = 43200; dr["利润率"] = 0.29;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 8, 1); dr["主打产品"] = "电脑"; dr["销售额"] = 19159; dr["利润率"] = 0.36;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 9, 1); dr["主打产品"] = "电视"; dr["销售额"] = 30971; dr["利润率"] = 0.34;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 10, 1); dr["主打产品"] = "手机"; dr["销售额"] = 29757; dr["利润率"] = 0.64;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 11, 1); dr["主打产品"] = "电脑"; dr["销售额"] = 13180; dr["利润率"] = 0.45;
            dr = dt.Rows.Add(); dr["月份"] = new DateTime(2019, 12, 1); dr["主打产品"] = "电视"; dr["销售额"] = 15730; dr["利润率"] = 0.25;
            return dt;
        }

    }
}

重要说明：

引用Spire.XLS时，必须同时引用Spire.Pdf和Spire.License，或将dll文件放在同一目录

解决方案源代码可到资源区下载

其他关于Spire.Xls的操作：

跨列居中：（显示效果类似合并单元格，但是实际并没有合并）

sheet1.Range[2, 3, 2, 6].HorizontalAlignment = HorizontalAlignType.CenterAcrossSelection;

设置折叠分组：

sheet1.GroupByRows(iStartRow, iEndRow, true);

设置冻结窗口：

//冻结首行
sheet1.FreezePanes(2, 1);

//冻结首列
sheet1.FreezePanes(1, 2);

透视表刷新：

//手动刷新数据
pt.CalculateData();
//设置打开时自动刷新数据
pt.Cache.IsRefreshOnLoad = true;

设置文档属性：

//设置摘要
book.DocumentProperties.Company = "凯神科技";
book.DocumentProperties.Author = "凯神Jump";
book.DocumentProperties.Title = "财务报表";
//设置自定义属性
book.CustomDocumentProperties.Add("联系电话", "12345678");

搜索替换

if (sheet.FindString("#LotNo#", false, false) != null)
{
    sheet.Replace("#LotNo#","AA00001");
}

查找单元格

CellRange[] mycells = sheet.FindAllString("mydemostring", False, False);

插入图片

sheet.Pictures.Add(1, 1, bitmap);

隐藏行

sheet.HideRow(1);

插入行

sheet.InsertRow(3, 100, Spire.Xls.InsertOptionsType.FormatAsAfter);
//3：在第3行后面插入，100：插入100行，FormatAsAfter：格式同后面的行，不填此参数则默认样式

设置外边框

sheet.Range[2,2,8,8].BorderAround(LineStyleType.Thin);

激活单元格

sheet.Range["A1"].Activate();

激活Worksheet

sheet.Activate();

隐藏网格线

sheet.GridLinesVisible = false;

有时候如果读取的模板文件中有公式，但是更新数据后，公式字段显示不更新，使用以下代码

foreach( Worksheet sheet in book.Worksheets)
{
    sheet.ReparseFormula();
    //在有一次使用上面的方法时，出现了将公式清除的情况，后来使用了下面的方法以刷新公式，生效了
    sheet.CalculateAllValue();
    //使用book.CalculateAllValue();也是无效
}

Excel根据列号转string，如：sheet.Range[28,1] --> sheet.Range["C1"]

public string ChangeColumnNoToStr(int num)
{
    if (num <= 26)
    {
        return Convert.ToChar(num + 65 - 1).ToString();
    }
    else
    {
        int x, y;
        x = 0;
        y = 0;
        x = num / 26;
        y = num % 26;
        if (y == 0)
        {
            return Convert.ToChar(x + 65 - 2).ToString() + "Z";
        }
        else
        {
            return Convert.ToChar(x + 65 - 1).ToString() + Convert.ToChar(y + 65 - 1).ToString();
        }
    }
}

源码下载

谷凯Jump

关注

7
点赞
踩
36

收藏

觉得还不错? 一键收藏
0
评论
C#使用FreeSpire.XLS创建Excel

前言对于使用C#程序生成Excel文件，我经历了以下几个阶段，强烈推荐使用Spire.XLS：Microsoft.Office.Interop.Excel.dll EPPlus.dll Spire.XLS对于Spire.XLS的免费版本，Free Spire.XLS有以下限制，但一般够用了，其官网说明如下免费 Spire.XLS 感兴趣。它是完全免费的，没有任何警告消息供您商业...
复制链接

扫一扫