C#用Aspose.Cells导出Excel,.NET导出Excel
ASP.NET MVC 控制器里面Action处理,下载文件,输出文件流
public async Task<ActionResult> ExportNewsAuthorFee(string deptId, DateTime? startDate, DateTime? endDate)
{
if (startDate == null)
{
startDate = DateTime.Parse(DateTime.Now.Year + "-1-1");
}
string title = startDate.Value.ToString("yyyy-MM-dd");
if (endDate.HasValue)
{
title += "至"+endDate.Value.ToString("yyyy-MM-dd");
}
var list = await newsStatiscBll.GetUserChannelRoyaltiesListAsync(deptId, startDate, endDate);
List<List<string>> excelData = new List<List<string>>();
//标题设置,第一行
List<string> titleList = new List<string>();
titleList.Add("姓名");
titleList.Add("员工编号");
titleList.Add("部门");
titleList.Add("院");
titleList.Add("");
titleList.Add("小计");
titleList.Add("分");
titleList.Add("");
titleList.Add("");
titleList.Add("小计");
titleList.Add("门");
titleList.Add("网");
for (int q = 0; q < 6; q++)
{
titleList.Add("");
}
titleList.Add("小计");
titleList.Add("电视");
titleList.Add("");
titleList.Add("");
titleList.Add("小计");
titleList.Add("绿色1");
titleList.Add("绿色2");
titleList.Add("绿色3");
titleList.Add("西1");
titleList.Add("西2");
titleList.Add("西3");
titleList.Add("川3");
titleList.Add("川4");
titleList.Add("川5");
titleList.Add("铁6");
titleList.Add("铁7");
titleList.Add("铁8");
titleList.Add("省9");
titleList.Add("省12");
titleList.Add("省11");
titleList.Add("小计");
titleList.Add("总计");
excelData.Add(titleList);
//第二行
List<string> titleList2 = new List<string>();
titleList2.Add("");
titleList2.Add("");
titleList2.Add("");
titleList2.Add("摄1");
titleList2.Add("文2");
titleList2.Add("");
titleList2.Add("气1");
titleList2.Add("川2");
titleList2.Add("摄3");
titleList2.Add("");
titleList2.Add("");
titleList2.Add("地简讯");
titleList2.Add("地非简讯");
titleList2.Add("省简讯");
titleList2.Add("省非简讯");
titleList2.Add("简讯1");
titleList2.Add("非简讯2");
titleList2.Add("国");
titleList2.Add("");
titleList2.Add("地市1");
titleList2.Add("省部2");
titleList2.Add("国家3");
titleList2.Add("");
for (int n = 0; n< 17; n++)
{
titleList2.Add("");
}
excelData.Add(titleList2);
//数据封装
foreach (var item in list)
{
List<string> cellDataList = new List<string>();
// 小计
decimal xj_yw1 = item.FeeAnPhotography + item.FeeAnLiterary;
// 小计
decimal xj_fgs = item.FeeFmGasField + item.FeeFmChuanyouFigures + item.FeeFmPhotography;
//报刊小计
decimal xj_wl = item.FeeNewspapers_disShort
+ item.FeeNewspapers_disImgTxt
+ item.FeeNewspapers_provinceShort
+ item.FeeNewspapers_provinceShort
+ item.FeeNewspapers_nationalShort
+ item.FeeNewspapers_nationalImgTxt
+ item.FeeNewspapers_PetroChina
;
//电视新闻,小计
decimal xj_tv = item.FeeTV_dis + item.FeeTV_province + item.FeeTV_national;
//【小计】
decimal xj_mt2 = item.FeeGreen_author
+ item.FeeGreen_edit
+ item.FeeGreen_img
+ item.FeeXi_author
+ item.FeeXi_edit
+ item.FeeXi_img
+ item.FeeZChuanyouFigures_author
+ item.FeeZChuanyFigures_edit
+ item.FeeZChuanyouFigures_img
+ item.FeeZIronMan_author
+ item.FeeZIronMan_edit
+ item.FeeZIronMan_img
+ item.FeeZProvinNewMedia_author
+ item.FeeZProvinNewMedia_edit
+ item.FeeZProvinNewMedia_img
;
decimal total = xj_yw1 + xj_fgs + xj_wl + xj_tv + xj_mt2;
cellDataList.Add(item.UserRealName); /* 姓名*/
cellDataList.Add(item.UserNo); /* 员工编号*/
cellDataList.Add(item.DeptName); /* 部门*/
cellDataList.Add(item.FeeAnPhotography.ToString()); /* 作品*/
cellDataList.Add(item.FeeAnLiterary.ToString()); /* 作品*/
cellDataList.Add(xj_yw1.ToString()); /* 小计*/
cellDataList.Add(item.FeeFmGasField.ToString()); /* 气田风采*/
cellDataList.Add(item.FeeFmChuanyouFigures.ToString()); /* 川油人物*/
cellDataList.Add(item.FeeFmPhotography.ToString()); /* 摄影作品*/
cellDataList.Add(xj_fgs.ToString()); /* 小计 */
cellDataList.Add(item.FeeGpHome.ToString()); /* 门户 */
cellDataList.Add(item.FeeNewspapers_disShort.ToString()); /* 刊_简讯*/
cellDataList.Add(item.FeeNewspapers_disImgTxt.ToString()); /* 报刊_非简讯 */
cellDataList.Add(item.FeeNewspapers_provinceShort.ToString()); /* 报刊_简讯 */
cellDataList.Add(item.FeeNewspapers_provinceImgTxt.ToString()); /* 报刊_非简讯 */
cellDataList.Add(item.FeeNewspapers_nationalShort.ToString()); /* 报刊_简讯 */
cellDataList.Add(item.FeeNewspapers_nationalImgTxt.ToString()); /* 报刊_非简讯 */
cellDataList.Add(item.FeeNewspapers_PetroChina.ToString()); /* 油*/
cellDataList.Add(xj_wl.ToString()); /* 报刊小记 */
//地
cellDataList.Add(item.FeeTV_dis.ToString()); /* 地市级 */
cellDataList.Add(item.FeeTV_province.ToString());
cellDataList.Add(item.FeeTV_national.ToString());
cellDataList.Add(xj_tv.ToString()); /* 报刊小记 */
//绿色
cellDataList.Add(item.FeeGreen_author.ToString());
cellDataList.Add(item.FeeGreen_edit.ToString());
cellDataList.Add(item.FeeGreen_img.ToString());
//西
cellDataList.Add(item.FeeXi_author.ToString());
cellDataList.Add(item.FeeXi_edit.ToString());
cellDataList.Add(item.FeeXi_img.ToString());
//川
cellDataList.Add(item.FeeZChuanyouFigures_author.ToString());
cellDataList.Add(item.FeeZChuanyFigures_edit.ToString());
cellDataList.Add(item.FeeZChuanyouFigures_img.ToString());
//铁
cellDataList.Add(item.FeeZIronMan_author.ToString());
cellDataList.Add(item.FeeZIronMan_edit.ToString());
cellDataList.Add(item.FeeZIronMan_img.ToString());
//省
cellDataList.Add(item.FeeZProvinNewMedia_author.ToString());
cellDataList.Add(item.FeeZProvinNewMedia_edit.ToString());
cellDataList.Add(item.FeeZProvinNewMedia_img.ToString());
cellDataList.Add(xj_mt2.ToString()); /* 小计 */
cellDataList.Add(total.ToString()); /* 总计 */
excelData.Add(cellDataList);
}
//单元格设置合并,标题第1行
Action<Worksheet> ac = sheet =>
{
sheet.Cells.Merge(0, 0, 2, 1); /*姓名,单元格合并*/
sheet.Cells.Merge(0, 1, 2, 1); /*员工编号,单元格合并*/
sheet.Cells.Merge(0, 2, 2, 1); /*部门,单元格合并*/
sheet.Cells.Merge(0, 3, 1, 2); /*院网员工天地,单元格合并*/
sheet.Cells.Merge(0, 5, 2, 1);/*小计,单元格合并*/
sheet.Cells.Merge(0, 6, 1, 3);/*分公司门户,单元格合并*/
sheet.Cells.Merge(0, 9,2, 1);/*小计,单元格合并*/
sheet.Cells.Merge(0, 10,2, 1);/*集,单元格合并*/
sheet.Cells.Merge(0, 11,1, 7);/*网,单元格合并*/
sheet.Cells.Merge(0, 18,2,1);/*网,单元格合并*/
sheet.Cells.Merge(0, 19,1,3);/*电,单元格合并*/
sheet.Cells.Merge(0, 22,2,1);/*电,单元格合并*/
sheet.Cells.Merge(0, 23,2,1);/*绿色,单元格合并*/
for (int i = 24; i < 40; i++) {
sheet.Cells.Merge(0, i, 2, 1);/* 单元格合并*/
}
};
Stream stream = new MemoryStream();
Export.ExportExcelHelpter.ExportAuthorExcelBig(excelData, ref stream, ac, null);
string fileName = "作者稿费统计(" + title + ")" + Guid.NewGuid().ToString("N").Substring(0,4) + ".xlsx";
return File(stream, "application/ocelet-stream", fileName);
}
处理导出
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Threading.Tasks;
using Aspose.Cells;
/// <summary>
/// 导出Excel,ac传入委托设置Cells格式,比如合并;比如单元格宽度设置等。
/// </summary>
/// <param name="data">数据</param>
/// <param name="stream"></param>
/// <param name="stream">委托,设置Cells格式,比如合并;比如单元格宽度设置等</param>
/// <param name="afterAc">委托,数据已经插入后,设置单元格样式,比如列宽、列高配置</param>
/// 创建时间:2024-7-25 17:41:56, xxx
public static void ExportAuthorExcelBig(List<List<string>> data , ref Stream stream, Action<Worksheet> ac, Action<Cells> afterAc)
{
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
sheet.Name = "作者统计分析";
Cells cells = sheet.Cells;
Style style = wb.Styles[wb.Styles.Add()];
style.Font.Size = 12;
cells.ApplyStyle(style, new StyleFlag() { All = true });
int cols = data[0].Count;
//int cols = colsCount;
//标题样式
Style styleTitle = wb.Styles[wb.Styles.Add()];
styleTitle.Font.IsBold = true;
styleTitle.Font.Size = 12;
//styleTitle.Font.Color = Color.Blue;/* 字体颜色*/
styleTitle.ForegroundColor = Color.SpringGreen;/* 背景色 */
styleTitle.Pattern = BackgroundType.Solid;/* 背景色 */
//边框设置
styleTitle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleTitle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
//styleTitle.Borders.SetStyle(CellBorderType.Thick);/*单元格边框有线,中间被叉叉封起*/
styleTitle.HorizontalAlignment = TextAlignmentType.Center;
styleTitle.VerticalAlignment = TextAlignmentType.Center;
Range range = cells.CreateRange(0, 0, 2, cols);
range.ApplyStyle(styleTitle, new StyleFlag() { All = true });
#region 合并表头单元格
//调用传入委托,
ac?.Invoke(sheet);
第一行
//sheet.Cells.Merge(0, 0, 3, 1);//合并第一行第一列开始 合并3行1列
//sheet.Cells.Merge(0, 1, 3, 1);
//sheet.Cells.Merge(0, 2, 3, 1);
//sheet.Cells.Merge(0, 3, 3, 1);
//sheet.Cells.Merge(0, 4, 3, 1);
//sheet.Cells.Merge(0, 5, 3, 1);
//sheet.Cells.Merge(0, 6, 1, 64);
第二行
//sheet.Cells.Merge(1, 6, 1, 4);
//sheet.Cells.Merge(1, 10, 1, 4);
//sheet.Cells.Merge(1, 14, 1, 4);
//sheet.Cells.Merge(1, 18, 1, 4);
//sheet.Cells.Merge(1, 22, 1, 4);
//sheet.Cells.Merge(1, 26, 1, 4);
//sheet.Cells.Merge(1, 30, 1, 4);
//sheet.Cells.Merge(1, 34, 1, 4);
//sheet.Cells.Merge(1, 38, 1, 4);
//sheet.Cells.Merge(1, 42, 1, 4);
//sheet.Cells.Merge(1, 46, 1, 4);
//sheet.Cells.Merge(1, 50, 1, 4);
//sheet.Cells.Merge(1, 54, 1, 4);
//sheet.Cells.Merge(1, 58, 1, 4);
//sheet.Cells.Merge(1, 62, 1, 4);
//sheet.Cells.Merge(1, 66, 1, 4);
#endregion
object[,] dataArr2 = new object[data.Count, cols];
for (int n = 0; n < data.Count; n++)
{
var rowLine = data[n];
for (int j = 0; j < rowLine.Count; j++)
{
dataArr2[n, j] = rowLine[j];
}
}
cells.ImportTwoDimensionArray(dataArr2, 0, 0);
//自适应宽
sheet.AutoFitColumns();
//自适应行高
sheet.AutoFitRows();
//cells.SetRowHeight(0, 20);
//cells.SetRowHeight(1, 20);
//cells.SetRowHeight(2, 20);
//for (int i = 0; i < cols; i++)
//{
// cells.SetColumnWidth(i, 15);
//}
afterAc?.Invoke(cells);
//输出文件流
stream.Seek(0, SeekOrigin.Begin);
wb.Save(stream, SaveFormat.Xlsx);
stream.Seek(0, SeekOrigin.Begin);
//保存文件到本地
//string fileName = Guid.NewGuid().ToString("N") + ".xls";
//string filePath = AppDomain.CurrentDomain.BaseDirectory + fileName;
//string fileFolderDeire = Path.GetDirectoryName(filePath);//目录信息
//if (!Directory.Exists(fileFolderDeire))
//{
// Directory.CreateDirectory(fileFolderDeire);
//}
//如果是大文件,建议返回文件磁盘路径
//wb.Save(filePath);
//stream = new FileStream(filePath, FileMode.Open);
//Task.Run(() =>
//{
// //删除生成的文件
// System.Threading.Thread.Sleep(60000);
// try
// {
// File.Delete(filePath);
// }
// catch (Exception) { }
//});
}
原文地址:https://blog.csdn.net/u011511086/article/details/140711008
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!