public async Task<ActionResult> ExportDataToExcel()
{
//查询出的数据
var list = new List<TestDataViewModel>();
list.Add(new TestDataViewModel
{
PlanSubject = "test1",
MobileNo = "13577778888",
TelcoOperatorText = "移动"
});
list.Add(new TestDataViewModel
{
PlanSubject = "test2",
MobileNo = "13577779999",
TelcoOperatorText = "移动"
});
list.Add(new TestDataViewModel
{
PlanSubject = "test3",
MobileNo = "8077778888",
TelcoOperatorText = "电信"
});
//var model = await DataTrafficGiftRecordQueryEntry.SelectAsync();
//已经领取了流量
//var hasGetList = model.Where(m => !string.IsNullOrWhiteSpace(m.MobileNo) && m.MobileNo.Length == 11);
//var result = hasGetList.MapList<DataTrafficGiftRecordViewModel>().ToList();
var setting = new ExcelExportOption<TestDataViewModel>
{
FileName = "客户领取流量记录",
DefaultRowHeight = 270
};
if (list.Count() >= 65535)
setting.Description = "结果数据总数:" + list.Count() + "但Excel最多支持65535条数据, 请分开条件导出";
setting.Source = list.Take(65534);
var colums = new List<ExcelColum<TestDataViewModel>>
{
new ExcelColum<TestDataViewModel>
{
Name = "赠送计划标题",
Width = 7000,
ColumType = ExcelColumType.String,
ResultFunc = x => x.PlanSubject
},
new ExcelColum<TestDataViewModel>
{
Name = "手机号码",
Width = 7000,
ColumType = ExcelColumType.String,
ResultFunc = x => x.MobileNo
},
new ExcelColum<TestDataViewModel>
{
Name = "手机运营商",
Width = 7000,
ColumType = ExcelColumType.String,
ResultFunc = x => x.TelcoOperatorText
},
};
setting.Colums = colums;
//发送文件路径到客户端
var steam = ExcelUtility.ExportToStream(setting);
var filename = HttpUtility.UrlEncode(setting.FileName + DateTime.Now.ToString("_yyyyMMdd-HHmmss") + ".xls", System.Text.Encoding.UTF8);
return File(steam, "application/vnd.ms-excel", filename);
//Response.AddHeader("Content-Disposition", $"attachment;filename={filename}");
//Response.Clear();
//Response.BinaryWrite(steam.GetBuffer());
//Response.End();
}
/// <summary>
/// 导出数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="setting"></param>
/// <returns></returns>
public static MemoryStream ExportToStream <T>(ExcelExportOption <T> setting)
{
var cols = setting.Colums.Count();
var hssworkbook = new HSSFWorkbook();
var sheet1 = hssworkbook.CreateSheet("Sheet1");
if (setting.DefaultRowHeight > 0)
{
sheet1.DefaultRowHeight = setting.DefaultRowHeight;
}
var docSummaryInfo = PropertySetFactory.CreateDocumentSummaryInformation();
docSummaryInfo.Company = string.Empty;
var summaryInfo = PropertySetFactory.CreateSummaryInformation();
summaryInfo.Subject = setting.Title;
hssworkbook.DocumentSummaryInformation = docSummaryInfo;
hssworkbook.SummaryInformation = summaryInfo;
//自动调整某列宽度
for (var i = 0; i < cols; i++)
{
sheet1.AutoSizeColumn(i, true);
}
var currentRowIndex = 0;
//设置标题信息
if (!String.IsNullOrWhiteSpace(setting.Title))
{
var row0 = sheet1.CreateRow(currentRowIndex);
for (var i = 0; i < cols; i++)
{
row0.CreateCell(i).Row.Height = 600;
}
row0.GetCell(0).SetCellValue(setting.Title);
currentRowIndex++;
//标题样式
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, cols - 1));
var titlestyle = hssworkbook.CreateCellStyle();
titlestyle.WrapText = true;
var titleFont = hssworkbook.CreateFont();
titleFont.FontHeight = 20 * 20;
titlestyle.SetFont(titleFont);
titlestyle.Alignment = HorizontalAlignment.Center;
sheet1.GetRow(0).GetCell(0).CellStyle = titlestyle;
}
//设置描述信息
if (!string.IsNullOrWhiteSpace(setting.Description))
{
var row1 = sheet1.CreateRow(currentRowIndex);
for (var i = 0; i < cols; i++)
{
row1.CreateCell(i).Row.Height = 1000;
}
row1.GetCell(0).SetCellValue(setting.Description);
currentRowIndex++;
//描述样式
sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 0, cols - 1));
}
//设置列分组
if (setting.ColumGroups != null && setting.ColumGroups.Any())
{
var rowGroup = sheet1.CreateRow(currentRowIndex);
var columGroup = setting.ColumGroups;
for (var i = 0; i < cols; i++)
{
var cell = rowGroup.CreateCell(i, CellType.String);
cell.SetCellValue(String.Empty);
cell.Row.Height = 500;
}
var groupStyle = hssworkbook.CreateCellStyle();
var groupFont = hssworkbook.CreateFont();
groupFont.FontHeight = 15 * 15;
groupFont.IsBold = true;
groupStyle.SetFont(groupFont);
groupStyle.Alignment = HorizontalAlignment.Center;
var excelColumGroups = columGroup as ExcelColumGroup[] ?? columGroup.ToArray();
for (var j = excelColumGroups.Length - 1; j >= 0; j--)
{
var cell = rowGroup.GetCell(excelColumGroups.ElementAt(j).StartIndex);
cell.SetCellValue(excelColumGroups.ElementAt(j).Name);
cell.CellStyle = groupStyle;
sheet1.AddMergedRegion(new CellRangeAddress(2, 2, excelColumGroups.ElementAt(j).StartIndex, excelColumGroups.ElementAt(j).StartIndex + excelColumGroups.ElementAt(j).Cols - 1));
}
currentRowIndex++;
}
//列头行索引
var columHeaderRowIndex = currentRowIndex;
//列头样式
var headerfont = hssworkbook.CreateFont();
headerfont.FontHeightInPoints = 12;
headerfont.IsBold = true;
var headerstyle = hssworkbook.CreateCellStyle();
headerstyle.SetFont(headerfont);
//设置列头信息
var row2 = sheet1.CreateRow(columHeaderRowIndex);
for (var i = 0; i < cols; i++)
{
var cell = row2.CreateCell(i);
cell.Row.Height = 500;
cell.SetCellValue(setting.Colums.ElementAt(i).Name);
cell.CellStyle = headerstyle;
}
var dataStyle = hssworkbook.CreateCellStyle();
//水平对齐
dataStyle.Alignment = HorizontalAlignment.Left;
//垂直对齐
dataStyle.VerticalAlignment = VerticalAlignment.Top;
//自动换行
dataStyle.WrapText = true;
//输出数据
for (var i = 0; i < setting.Source.Count(); i++)
{
var model = setting.Source.ElementAt(i);
var rowTemp = sheet1.CreateRow(i + columHeaderRowIndex + 1);
//Type modelType = model.GetType();
//PropertyInfo[] properties = modelType.GetProperties();
//var proDic = new Dictionary<string, PropertyInfo>();
//foreach (var item in properties)
//{
// proDic.Add(item.Name, item);
//}
for (var j = 0; j < setting.Colums.Count(); j++)
{
rowTemp.CreateCell(j).SetCellValueT(
setting.Colums.ElementAt(j).ColumType,
dataStyle,
setting.Colums.ElementAt(j).ResultFunc == null ? setting.Colums.ElementAt(j).ResultByFieldName(model, setting.Colums.ElementAt(j).FieldName) : (setting.Colums.ElementAt(j).ResultFunc(model)));
}
}
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssworkbook);
//对设定并可行的列求和
var containSum = setting.Colums.Any(item => item.DoColumSum);
if (containSum)
{
var style = hssworkbook.CreateCellStyle();
var font = hssworkbook.CreateFont();
font.FontHeight = 15 * 15;
font.IsBold = true;
font.Color = HSSFColor.Blue.Index;
style.SetFont(font);
//求和 SUM(A2:A12)
var letter = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
var totalRow = sheet1.CreateRow(columHeaderRowIndex + 1 + setting.Source.Count());
for (var x = 0; x < setting.Colums.Count(); x++)
{
var rowName = x == 0 ? "A" : (x > 26 ? letter.ElementAt(x / 26).ToString() + letter.ElementAt(x % 26).ToString() : letter.ElementAt(x % 26).ToString());
if (setting.Colums.ElementAt(x).DoColumSum)
{
var tempCell = totalRow.CreateCell(x);
tempCell.CellFormula = "SUM(" + rowName + (columHeaderRowIndex + 2) + ":" + rowName + (columHeaderRowIndex + 1 + setting.Source.Count()) + ")";
tempCell.CellStyle = style;
e.EvaluateInCell(tempCell);
}
else
{
var temp = totalRow.CreateCell(x);
temp.CellStyle = style;
if (x == 0)
{
temp.SetCellValue("合计");
}
}
}
}
//表格样式
for (var i = 0; i < cols; i++)
{
if (setting.Colums.ElementAt(i).Width > 0)
{
sheet1.SetColumnWidth(i, setting.Colums.ElementAt(i).Width);
}
}
var stream = new MemoryStream();
hssworkbook.Write(stream);
return(stream);
}
public async Task ExportDataToExcel2()
{
//查询出的数据
// 1.获取数据集合
var list = new List<UserViewModel>() {
new UserViewModel{Name="刘一",Age=22,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=80,MathScores=90}},
new UserViewModel{Name="陈二",Age=23,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=81,MathScores=91} },
new UserViewModel{Name="张三",Age=24,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=82,MathScores=92} },
new UserViewModel{Name="李四",Age=25,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=83,MathScores=93} },
new UserViewModel{Name="王五",Age=26,Gender="Male",TranscriptsEn=new TranscriptsEntity{ChineseScores=84,MathScores=94} },
};
var setting = new ExcelExportOption<UserViewModel>
{
FileName = "客户领取流量记录",
DefaultRowHeight = 270
};
if (list.Count() >= 65535)
setting.Description = "结果数据总数:" + list.Count() + "但Excel最多支持65535条数据, 请分开条件导出";
setting.Source = list.Take(65534);
// 2.设置单元格抬头
// key：实体对象属性名称，可通过反射获取值
// value：Excel列的名称
Dictionary<string, string> cellheader = new Dictionary<string, string> {
{ "Name", "姓名" },
{ "Age", "年龄" },
{ "GenderName", "性别" },
{ "TranscriptsEn", "成绩" },
{ "TranscriptsEn.ChineseScores", "语文成绩" },
{ "TranscriptsEn.MathScores", "数学成绩" },
};
//发送文件路径到客户端
var urlPath = ExcelUtility.ExportExcel(cellheader, list, "学生成绩");
System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer();
HttpContext.Response.ContentType = "text/plain";
HttpContext.Response.Write(js.Serialize(urlPath)); // 返回Json格式的内容
//var column = new List<ExcelColum<UserViewModel>>
//{
// new ExcelColum<UserViewModel>
// {
// Name = "姓名",
// Width = 7000,
// ColumType = ExcelColumType.String,
// ResultFunc = x => x.Name
// },
// new ExcelColum<UserViewModel>
// {
// Name = "年龄",
// Width = 7000,
// ColumType = ExcelColumType.String,
// ResultFunc = x => x.Age
// },
// new ExcelColum<UserViewModel>
// {
// Name = "性别",
// Width = 7000,
// ColumType = ExcelColumType.String,
// ResultFunc = x => x.GenderName
// },
// new ExcelColum<UserViewModel>
// {
// Name = "语文成绩",
// Width = 7000,
// ColumType = ExcelColumType.String,
// ResultFunc = x => x.TranscriptsEn.ChineseScores
// },
// new ExcelColum<UserViewModel>
// {
// Name = "数学成绩",
// Width = 7000,
// ColumType = ExcelColumType.String,
// ResultFunc = x => x.TranscriptsEn.MathScores
// },
//};
//var option = new ExcelExportOption<UserViewModel>
//{
// Title = "学生成绩",
// Source = list,
// Colums = column
//};
//var filename = $"民办初中-{DateTime.Now:yyyyMMddHHmmssfff}.xls";
//var file = ExcelUtility.ExportToStream<UserViewModel>(option);
//return File(file, "application/vnd.ms-excel", filename);
}
