C#使用FreeSpire.XLS创建Excel
前言
对于使用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();
}
}
}