在工作中，遇到很多项目都有 “将页面展示的表格或者echarts图表导出为Word或者Excel文件”需求，于是做了简单整理。
导出word、excel文件
使用Aspose控件
Aspose是一个很强大的控件,可以用来操作word,excel,ppt等文件,用这个控件来导入、导出数据非常方便。其中Aspose.Cells就是用来操作Excel的，功能有很多。
Aspose.Words for .NET
| api | 描述 |
|---|---|
| Document | 表示 Word 文档。 |
| Style | 表示单个内置或用户定义的样式。 |
| DocumentBuilder | 提供插入文本、图像和其他内容、指定字体、段落和节格式的方法。可用来构建页眉、页脚、表格 |
| DocumentBuilder.Writeln(string) | 在文档中插入一个字符串和一个段落分隔符。 |
| DocumentBuilder.InsertParagraph() | 在文档中插入一个段落分隔符。 |
创建Word，word中插入表格
示例：
/**//// <summary>
/// 动态生成Word文档并填充数据
/// </summary>
/// <returns>返回自定义信息</returns>
public static string ExportWord(string strTime, List<string> volTrip, List<List<string>> tripTable)
{
string time = strTime;
Document doc = new Document(); // 表示 Word 文档。
DocumentBuilder builder = new DocumentBuilder(doc); // 这里用来构建表格
Aspose.Words.StyleCollection styles = doc.Styles; // StyleCollection: Style 对象的集合，代表文档中的内置样式和用户定义的样式。
builder.ParagraphFormat.Style = styles["Title"];
builder.Writeln("重庆智能运检管控系统跳闸事件分析日报"); // 写入文件标题
builder.InsertParagraph(); // 插入一个段落分隔符。
// Specify font formatting
Aspose.Words.Font font = builder.Font;
font.Size = 12;
font.Bold = false;
ParagraphFormat paragraphLeft = builder.ParagraphFormat;
paragraphLeft.Alignment = ParagraphAlignment.Left;
paragraphLeft.LeftIndent = 3;
paragraphLeft.RightIndent = 3;
paragraphLeft.SpaceAfter = 3;
builder.Writeln("时间：" + time + " 00:00:00 - 23:59:59");
builder.Writeln("跳闸统计：");
for (int i = 0; i < volTrip.Count; i++)
{
string item = volTrip[i].ToString();
builder.Writeln(item);
}
ParagraphFormat paragraphCenter = builder.ParagraphFormat;
paragraphCenter.Alignment = ParagraphAlignment.Center;
builder.Writeln(time + " 跳闸事件表"); // 表名
var dt = builder.StartTable();
builder.Font.Size = 11;
builder.CellFormat.BottomPadding = 0;
builder.CellFormat.TopPadding = 0;
builder.CellFormat.LeftPadding = 0;
builder.CellFormat.RightPadding = 0;
string[] titles = new string[] { "序号", "单位", "发生时间", "设备电压等级", "跳闸设备", "恢复送电时间" };
int[] lens = new int[] { 8, 10, 17, 12, 30,17 };
for (int i = 0; i < 6; i++)
{
builder.InsertCell();
builder.Font.Bold = true;
builder.CellFormat.PreferredWidth = PreferredWidth.FromPercent(lens[i]);
builder.CellFormat.Shading.BackgroundPatternColor = Color.LightGray;
builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
builder.Write(titles[i]);
}
builder.EndRow();
// 数据为空时 合并一行单元格展示“暂无数据”
if (tripTable.Count == 0)
{
builder.InsertCell();
builder.Font.Bold = false;
builder.CellFormat.Shading.BackgroundPatternColor = Color.Transparent;
builder.CellFormat.Borders.LineStyle = LineStyle.Single;
builder.CellFormat.Borders.Color = Color.Black;
builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.First;
builder.CellFormat.VerticalAlignment = Aspose.Words.Tables.CellVerticalAlignment.Center;
builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
builder.Write("暂无数据");
for (int j = 1; j < 6; j++)
{
builder.InsertCell();
builder.CellFormat.Borders.LineStyle = LineStyle.Single;
builder.CellFormat.Borders.Color = Color.Black;
builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.Previous;
}
builder.EndRow();
}
// 填入表格数据
else
{
for (int i = 0; i < tripTable.Count; i++)
{
builder.InsertCell();
builder.Font.Bold = false;
builder.CellFormat.PreferredWidth = PreferredWidth.FromPercent(lens[0]);
builder.CellFormat.Shading.BackgroundPatternColor = Color.Transparent;
builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
int k = i + 1;
builder.Write(k.ToString());
for (int j = 2; j < tripTable[i].Count - 2; j++)
{
builder.InsertCell();
builder.Font.Bold = false;
builder.CellFormat.PreferredWidth = PreferredWidth.FromPercent(lens[j - 1]);
builder.CellFormat.Shading.BackgroundPatternColor = Color.Transparent;
builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
builder.Write(tripTable[i][j].ToString());
}
builder.EndRow();
}
}
builder.EndTable();
string filename = "跳闸事件分析日报" + time + ".docx";
string dataDir = strPath + "/FileHis/" + filename;
doc.Save(dataDir);
return filename;
}
生成文件效果：
Aspose.Cells for .NET
Workbook: 表示创建 Excel 电子表格的根对象。
Cells: 封装了单元格相关对象的集合，例如Cell ,Row …等
Aspose.Cells.Style: 表示excel文档的显示样式，如字体、颜色、对齐方式、边框等。 Style对象包含所有样式属性（字体、数字格式、对齐方式等）作为属性。
Cells的方法：
| Merge(int, int, int, int) | 将指定范围的单元格合并为一个单元格。 |
|---|---|
创建Excel
/// <summary>
/// DataTable数据导出Excel
/// </summary>
/// <param name="data"></param>
/// <param name="filepath"></param>
public static void DataTableExport(DataTable data, string filepath, string title, string subtitle)
{
try
{
// Workbook 表示创建 Excel 电子表格的根对象。
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells; // Cells: 封装了单元格相关对象的集合，例如Cell ,Row …等
int Colnum = data.Columns.Count;
int Rownum = data.Rows.Count;
// Aspose.Cells.Style:
// 表示excel文档的显示样式，如字体、颜色、对齐方式、边框等。
// Style对象包含所有样式属性（字体、数字格式、对齐方式等）作为属性。
Aspose.Cells.Style titleStyle = book.Styles[book.Styles.Add()];
titleStyle.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
titleStyle.HorizontalAlignment = TextAlignmentType.Center;
titleStyle.VerticalAlignment = TextAlignmentType.Center;
titleStyle.Font.Name = "宋体";
titleStyle.Font.IsBold = true;
titleStyle.Font.Size = 16;
cells[0, 0].PutValue(title); // 赋值
cells[0, 0].SetStyle(titleStyle); // 设置单元格样式
cells[0, Colnum - 1].SetStyle(titleStyle);
Aspose.Cells.Style subtitleStyle = titleStyle;
subtitleStyle.Font.Size = 11;
subtitleStyle.Font.IsBold = false;
cells[1, 0].PutValue(subtitle);
cells[1, 0].SetStyle(subtitleStyle);
cells[1, Colnum - 1].SetStyle(subtitleStyle);
cells.SetRowHeight(0,25);
cells.SetRowHeight(1,25);
cells.Merge(0, 0, 1, Colnum);
cells.Merge(1, 0, 1, Colnum);
Aspose.Cells.Style style = titleStyle;
style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Font.IsBold = true;
style.Font.Size = 11;
for (int i = 0; i < Colnum; i++)
{
cells[2, i].PutValue(data.Columns[i].ColumnName);
cells[2, i].SetStyle(style);
cells.SetColumnWidth(i, data.Columns[i].ColumnName.Length * 2 + 4);// 设置列宽
cells.SetRowHeight(2, 25); // 设置行高
}
if (Rownum == 0)
{
int mergeRows = 3;
Aspose.Cells.Style noneStyle = titleStyle;
noneStyle.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
noneStyle.Font.IsBold = false;
noneStyle.Font.Size = 11;
for(int i = 3; i < 3 + mergeRows; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells[i, k].PutValue("暂无数据");
cells[i, k].SetStyle(noneStyle);
cells.SetRowHeight(i, 25);
}
}
cells.Merge(3, 0, mergeRows, Colnum);
}
else
{
Aspose.Cells.Style contentStyle = style;
contentStyle.Font.IsBold = false;
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells[3 + i, k].PutValue(data.Rows[i][k].ToString());
cells[3 + i, k].SetStyle(contentStyle);
cells.SetRowHeight(3 + i, 25);
}
}
}
sheet.AutoFitColumns();
book.Save(filepath);
GC.Collect();
}
catch (Exception e)
{
Console.WriteLine("生成excel出错：" + e.Message);
}
}
生成文件效果：
