运用Aspose.Cells组件将数据导至Execl
工作了几年，却一直没有做过将数据导出至Execl，想想也很奇葩。
最近我们在做一个供应链系统，这类系统，里面自然会涉及到操作Execl的功能，正好我所负责的功能里面有这个。
导出Execl的需求大概是这样的，一个主表，一个从表，关系是一对多，主从表关联，导出的数据是主表有多少条数据，那么Execl里面就有多少行数据，这里自然会涉及到合并单元格的动作，并且数据里面还有图片列，但看这需求总结出两点：1) 合并单元格，2) 显示图片。
最后导出的效果图截取部分：
部门里面的架构组提供的导出Execl组建不支持对指定列合并单元格功能，依赖于NOPI，所以后来选择了Aspose.Cells这玩意，听说网上代码一大堆，就找了找，不过么有找到合适的，之后就去官网下了示例代码看了一些，有些代码片段还是有参考价值的。
下面就说自己的思路了。
1.原始数据准备
因为主从关系，且导出行数以主表数据行数决定，所以这里用到了oracle里面的行列转换函数wm_concat，其要点是：
select id, wm_concat(to_char(colorname)) from tb group by id
因为colorname是中文，如果不加to_char函数的话，查询出来的数据会是乱码。
2. 图片显示与合并单元格
因为这里的图片都存在图片服务器上面，这个组件不支持网络图片，需要先将图片转换成MemoryStream，我觉得吧，这个很鸡肋。
方法是sheet.Pictures.Add，前两个参数是起始行列，行列索引默认是从1开始的，这个需要注意下。合并单元格的方法就是sheet.Cells.Merge，前两个参数是起始行列，索引是从0开始，不知为什么要这样设计？
还有一个我觉得很不友好的是，给单元格加边框的时候竟然分上下左右4个部分加边线，很是奇怪，下面有代码。
3. 代码设计
这里设计了一个接口IExeclDataExporter，里面只有一个方法void DataExport(ExporterParams exporterParams); 代码如下：
/// <summary>
/// Execl数据导出服务(依赖于Aspose.Cells组件)
/// </summary>
internal interface IExeclDataExporter
{
void DataExport(ExporterParams exporterParams);
}
/// <summary>
/// 参数
/// </summary>
internal sealed class ExporterParams
{
/// <summary>
/// Execl列头
/// </summary>
public IList<DataHeader> ExeclDataHeader { get; set; }
/// <summary>
/// Execl数据体
/// </summary>
public DataTable ExeclDataBody { get; set; }
public string ExeclTitle { get; set; }
/// <summary>
/// 不需要合并列
/// </summary>
public string NonMergeColumnCode { get; set; }
/// <summary>
/// 图片列
/// </summary>
public string ImageColumnCode { get; set; }
/// <summary>
/// 导出(绝对路径)
/// </summary>
public string DocumentFileName { get; set; }
}
/// <summary>
/// 列头
/// </summary>
internal sealed class DataHeader
{
/// <summary>
/// 列显示名称
/// </summary>
public string DisplayName { get; set; }
/// <summary>
/// 列编码
/// </summary>
public string ColumnCode { get; set; }
/// <summary>
/// 列宽度
/// </summary>
public int ColumnWidth { get; set; }
}
实现该接口的类是ExeclDataExporter，代码是：
internal class ExeclDataExporter:IExeclDataExporter
{
public void DataExport(ExporterParams eParams)
{
Guard.AgainstNull(eParams.ExeclDataHeader, "ExeclDataHeader");
//1. 变量定义,样式
Workbook workbook = new Workbook();
DefineStyle(workbook);
//2. 设置表头数据
SetExeclHeadData(workbook, eParams.ExeclTitle, eParams.ExeclDataHeader);
//3. 设置表体数据
SetExeclBodyData(workbook, eParams);
//4. 保存
workbook.Save(eParams.DocumentFileName);
}
private void SetExeclHeadData(Workbook workbook, string title, IList<DataHeader> ExeclDataHeader)
{
Worksheet sheet = workbook.Worksheets[0];
sheet.Cells.Merge(0, 0, 1, ExeclDataHeader.Count);
sheet.Cells[0, 0].PutValue(title);
Style style1 = workbook.Styles["Font25Center"];
sheet.Cells[0, 0].SetStyle(style1);
Style style2 = workbook.Styles["FontTitleCenter"];
for (var i = 0; i < ExeclDataHeader.Count; i++)
{
var head = ExeclDataHeader[i];
sheet.Cells.SetColumnWidth(i, head.ColumnWidth);
sheet.Cells[1, i].PutValue(head.DisplayName);
sheet.Cells[1, i].SetStyle(style2);
}
//设置当前数据行索引
CurrentRowIndex = 2;
}
private void SetExeclBodyData(Workbook workbook, ExporterParams eParams)
{
var dtBody = eParams.ExeclDataBody;
Style style = workbook.Styles["FontCenter"];
for (var i = 0; i < dtBody.Rows.Count; i++)
{
var row = dtBody.Rows[i];
//填充行数据
FillRowData(workbook.Worksheets[0], style, dtBody.Rows[i], eParams);
}
}
private void FillRowData(Worksheet sheet, Style style, DataRow row, ExporterParams eParams)
{
//上一次当前行索引
var oldCurrentRowIndex = CurrentRowIndex;
//不合并列索引
var nonMergeColumnIndex = GetColumnIndex(eParams.NonMergeColumnCode, eParams.ExeclDataHeader);
//图片列索引
var imageColumnIndex = GetColumnIndex(eParams.ImageColumnCode, eParams.ExeclDataHeader);
//合并行的数据
var mergeRowArr = row[eParams.NonMergeColumnCode] == DBNull.Value ? new string[0] : row[eParams.NonMergeColumnCode].ToString().Split(',');
//重新设置当前行索引
CurrentRowIndex = mergeRowArr.Length == 0 ? CurrentRowIndex + 1 : CurrentRowIndex + mergeRowArr.Length;
for (var j = 0; j < eParams.ExeclDataHeader.Count; j++)
{
var head = eParams.ExeclDataHeader[j];
var obj = row[head.ColumnCode] == DBNull.Value ? "" : row[head.ColumnCode];
//需要合并
if (mergeRowArr.Length >= 1)
{
if (j != nonMergeColumnIndex)
{
//合并
sheet.Cells.Merge(oldCurrentRowIndex, j, mergeRowArr.Length, 1);
if (j == imageColumnIndex)
{
if (!string.IsNullOrWhiteSpace(obj.ToString()))
{
var ms = GetMemoryStream(obj.ToString());
if (ms != null)
{
sheet.Cells.SetRowHeight(oldCurrentRowIndex, 45);
int index = sheet.Pictures.Add(oldCurrentRowIndex, j, ms, 100, 100);
SetImagePosition(sheet, index);
}
}
SetCellStyle(sheet, style, oldCurrentRowIndex, j);
}
else
{
//赋值
sheet.Cells[oldCurrentRowIndex, j].PutValue(obj);
for (var k = 0; k < mergeRowArr.Length; k++)
{
SetCellStyle(sheet, style, oldCurrentRowIndex + k, j);
}
}
}
else
{
for (var k = 0; k < mergeRowArr.Length; k++)
{
sheet.Cells[oldCurrentRowIndex + k, j].PutValue(mergeRowArr[k]);
SetCellStyle(sheet, style, oldCurrentRowIndex + k, j);
}
}
}
else
{
if (j == imageColumnIndex)
{
var ms = GetMemoryStream(obj.ToString());
if (ms != null)
{
sheet.Cells.SetRowHeight(oldCurrentRowIndex, 45);
int index = sheet.Pictures.Add(oldCurrentRowIndex, j, ms);
SetImagePosition(sheet, index);
}
}
else
{
sheet.Cells[oldCurrentRowIndex, j].PutValue(obj);
}
SetCellStyle(sheet, style, oldCurrentRowIndex, j);
}
}
}
private void SetCellStyle(Worksheet sheet, Style style, int k, int j)
{
sheet.Cells[k, j].SetStyle(style);
}
private void SetImagePosition(Worksheet sheet, int index)
{
Aspose.Cells.Drawing.Picture pic = sheet.Pictures[index];
pic.Left = 6;
pic.Top = 2;
}
private void DefineStyle(Workbook workbook)
{
Style style;
int styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 25;
style.Font.IsBold = true;
style.HorizontalAlignment = TextAlignmentType.Center;
style.Borders[BorderType.LeftBorder].Color = Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Black;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Name = "Font25Center";
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.IsBold = true;
style.HorizontalAlignment = TextAlignmentType.Center;
style.Borders[BorderType.LeftBorder].Color = Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Black;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Name = "FontTitleCenter";
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.HorizontalAlignment = TextAlignmentType.Center;
style.IsTextWrapped = true;
style.Borders[BorderType.LeftBorder].Color = Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Black;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Name = "FontCenter";
}
#region 辅助方法
private int GetColumnIndex(string columnCode, IList<DataHeader> ExeclDataHeader)
{
var item = ExeclDataHeader.FirstOrDefault(w => w.ColumnCode == columnCode);
return ExeclDataHeader.IndexOf(item);
}
private MemoryStream GetMemoryStream(string url)
{
try
{
WebClient wc = new WebClient();
byte[] bytes = wc.DownloadData(url);
return new MemoryStream(bytes);
}
catch
{
return null;
}
}
#endregion
#region 私有属性
private int CurrentRowIndex { set; get; }
#endregion
}
调用代码如下：
var data = GetExportProductInfo(querymodel).Data; //这里是datatable
if (data.Rows.Count == 0)
return null;
var exporterParams = new ExporterParams();
exporterParams.ExeclDataHeader = new List<DataHeader>() {
new DataHeader { DisplayName = "样版编号", ColumnCode = "SampleCode", ColumnWidth = 20 },
new DataHeader { DisplayName = "图片", ColumnCode = "SmallPicStyle", ColumnWidth = 8 },
new DataHeader { DisplayName = "颜色", ColumnCode = "ColorName", ColumnWidth = 15 },
};
exporterParams.DocumentFileName = filePath;
exporterParams.ExeclDataBody = data;
exporterParams.NonMergeColumnCode = "ColorName";
exporterParams.ImageColumnCode = "SmallPicStyle";
exporterParams.ExeclTitle = "产品资料列表";
IExeclDataExporter exporter = new ExeclDataExporter();
exporter.DataExport(exporterParams);
你们看了上面的代码是不是觉得很简单啊，呵呵，个人觉得吧，在合并单元格的计算逻辑需要注意一下，其他的都是搬砖的活，如果有什么疑问可以给我留言，写的不是很好请勿见怪。
