Aspose------导出Excel

/// <summary>
/// 导出Excel
/// </summary>
/// <typeparam name="T">泛型类</typeparam>
/// <param name="list">数据列</param>
/// <param name="row">插入行索引</param>
/// <param name="column">插入列索引</param>
public static void ExportToExcel<T>(List<T> list,int row, int column)
{
//模板地址
var template = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Temp", "A_Login.xlsx");Workbook book = new Workbook(template);
Worksheet sheet = book.Worksheets[];
//设置筛选
sheet.AutoFilter.SetRange(, , );Cells cell = sheet.Cells;for (var i = ; i < list.Count; i++)
{
cell.InsertRows(row + ,);
}int rowIndex = ;
int colIndex = column;Type type = typeof(T);
PropertyInfo []propertyInfo = type.GetProperties();
foreach (var pi in propertyInfo)
{
rowIndex = row;
foreach (var li in list)
{
var value = pi.GetValue(li);
cell[rowIndex, colIndex].PutValue(value);
rowIndex++;
}
colIndex++;
}var path = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Temp", "成品.xlsx");
book.Save(path);
}

/// <summary>
/// 导出合并单元格的Excel
/// </summary>
/// <typeparam name="T">泛型类</typeparam>
/// <param name="list">需要合并的数据</param>
public void ExportToMergeExcel<T>(List<T> list)
{
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[];
Cells cell = sheet.Cells;
Type type = typeof(T); int rowIndex = ;
int colIndex = ;
PropertyInfo[] property = type.GetProperties();
//合并的第一个单元格数据
var name = "";
//是否取出第一个单元格数据
var isFirst = true;
//记录需要合并的行数
var num = ;
List<int> mergeIndex = new List<int>();
List<int> mergeCount = new List<int>(); foreach (var pi in property)
{
rowIndex = ;
cell[rowIndex, colIndex].PutValue(pi.Name); foreach (var li in list)
{
++rowIndex;
var value = pi.GetValue(li);
cell[rowIndex, colIndex].PutValue(value); if (pi.Name == "Component")
{
if (isFirst == true)
{
isFirst = false;
continue;
} var v = value.ToString();
if (v == name)
{
num++;
if (rowIndex == list.Count())
{
if (num > )
{
mergeIndex.Add(rowIndex - num + );
mergeCount.Add(num);
}
}
}
else
{
name = v;
mergeIndex.Add(rowIndex - num);
mergeCount.Add(num);
num = ;
}
}
if (pi.Name == "SubComponent")
{
if (value == null)
{
cell.Merge(rowIndex, , , );
}
}
}
colIndex++;
} //合并
for (var i = ; i < mergeIndex.Count(); i++)
{
var f = mergeIndex[i];
var r = mergeCount[i];
cell.Merge(f, , r, );
} SetCellsStyle(cell); //设置筛选行
sheet.AutoFilter.SetRange(, , ); //自动行列宽
sheet.AutoFitColumns();
sheet.AutoFitRows(); var path = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Temp", "成品nck.xlsx");
book.Save(path);
} /// <summary>
/// 设置整行/整列样式
/// </summary>
/// <param name="cells"></param>
private void SetCellsStyle(Cells cells)
{
//设置样式
Style style1 = new Style();
//style.Font.Size = 12;
StyleFlag flag1 = new StyleFlag();
flag1.VerticalAlignment = true;
flag1.Font = true;
cells.ApplyColumnStyle(, style1, flag1);
//cell.Columns[0].ApplyStyle(style1, flag1); Style style2 = new Style();
style2.Font.Size = ;
style2.Pattern = BackgroundType.Solid;
style2.ForegroundColor = Color.FromArgb(, , , );
style2.Font.IsBold = true;
style2.HorizontalAlignment = TextAlignmentType.Left;
StyleFlag flag2 = new StyleFlag();
flag2.Font = true;
flag2.All = true;
//flag2.HorizontalAlignment = true;
cells.ApplyRowStyle(, style2, flag2);
} private void SetCellStyle(Cell cell, int fontSize, bool isBold = false, bool isItalic = false, List<int> argb = null)
{
Style style = new Style();
style.Font.Size = fontSize;
style.Font.IsBold = isBold;
style.Font.IsItalic = isItalic;
style.HorizontalAlignment = TextAlignmentType.Left;
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.FromArgb(argb[], argb[], argb[], argb[]);
cell.SetStyle(style, true);
}

private List<MergeItem> MergeEfficientItem(List<ProjectItem> list)
{
PropertyInfo[] propertyInfo = typeof(MergeItem).GetProperties();
//var data = _ProjectItemRepository.AsQueryable().where(p => p.ProjectCode == projectCode);
var data = list;
var level1List = data.Where(p => p.ItemLevel == "Level1").ToList();
var level2List = data.Where(p => p.ItemLevel == "Level2").ToList();
var level3List = data.Where(p => p.ItemLevel == "Level3").ToList();
var datalist = new List<MergeItem>(); //一级条目
foreach (var li in level1List)
{
var pitem = new MergeItem();
pitem.Component = li.Component;
datalist.Add(pitem); //三级条目
var citem = level3List.Where(p => p.Component == li.Component).OrderBy(p => p.Seq);
var subitem = new List<MergeItem>();
foreach (var cli in citem)
{
var sitem = new MergeItem();
sitem.Component = cli.SubComponent;
sitem.SubComponent = cli.Description;
sitem.Unit = cli.Unit;
sitem.Remark = cli.Remark;
sitem.FillInstruct = cli.FillInstruct; if (subitem.Any(p => p.Component == cli.SubComponent && p.SubComponent == cli.Description))
{
sitem = subitem.Where(p => p.Component == cli.SubComponent && p.SubComponent == cli.Description).FirstOrDefault();
}
else
{
subitem.Add(sitem);
} foreach (var pi in propertyInfo)
{
foreach (var c in citem)
{
var trim = c.Scenario.Replace(" ", "");
if (pi.Name == trim && c.Description == sitem.SubComponent)
{
pi.SetValue(sitem, c.UnitCost);
}
}
}
}
datalist.AddRange(subitem);
}
_ExcelUtil.ExportToMergeExcel<MergeItem>(datalist); return datalist;
}