public ResponseMessageModel DownloadStaffExcel()
{
try
{
string fileconfig = JsonConfig.JsonRead("EnterpriseStaffTemplate", "CGTExcel");
string filepath = Path.Combine(Directory.GetCurrentDirectory(), fileconfig);
string result = null;
if (System.IO.File.Exists(filepath))
{
var stream = FileUtily.FileToStream(filepath);
var ExcelBytes = FileUtily.StreamToBytes(stream);
result = Convert.ToBase64String(ExcelBytes);
return(new ResponseMessageModel
{
Data = result,
Message = "成功",
IsSuccess = true
});
}
else
{
return(new ResponseMessageModel
{
Data = "文件不存在路径异常",
Message = "成功",
IsSuccess = true
});
}
}
catch (Exception ex)
{
return(new ResponseMessageModel
{
Data = null,
Message = ex.Message,
IsSuccess = false,
ErrorCode = "9999"
});
}
}
public byte[] ExcelExport <T>(List <T> dataList, List <string> TitleList, List <string> KeyList)
{
lock (locker) {
string sWebRootFolder = _hostingEnvironment.WebRootPath + @"\ExcelOutput\";
if (!Directory.Exists(sWebRootFolder))
{
Directory.CreateDirectory(sWebRootFolder);
}
string sFileName = $"{Guid.NewGuid()}.xlsx";
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
using (ExcelPackage package = new ExcelPackage(file)) {
// 添加worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("aspnetcore");
//添加头
worksheet.Name = "workbook";
Type type = typeof(T);
PropertyInfo[] propertys = type.GetProperties();
List <PropertyInfo> propertys_newListorder = new List <PropertyInfo>();
foreach (var pi in KeyList)
{
if (propertys.Where(p => p.Name == pi).Count() == 1)
{
propertys_newListorder.Add(propertys.Where(p => p.Name == pi).FirstOrDefault());
}
}
int hangshu = dataList.Count;
int lieshu = propertys_newListorder.Count();
var propertys_newarry = propertys_newListorder.ToArray();
//填写标题
for (int t = 0; t < TitleList.Count; t++)
{
worksheet.Cells[1, (t + 1)].Value = TitleList[t];
}
//添加值
for (int i = 0; i < hangshu; i++)
{
for (int j = 0; j < lieshu; j++)
{
//string值自动格式化 execl单元格
if (propertys_newarry[j].PropertyType == typeof(Nullable <Byte>))
{
worksheet.Cells[(i + 2), (j + 1)].Value = ConvertIsByteNuablle((byte?)propertys_newarry[j].GetValue(dataList[i], null));
}
else if (propertys_newarry[j].PropertyType == typeof(DateTime))
{
worksheet.Cells[(i + 2), (j + 1)].Value = propertys_newarry[j].GetValue(dataList[i], null).ToString();
}
else
{
worksheet.Cells[(i + 2), (j + 1)].Value = propertys_newarry[j].GetValue(dataList[i], null);
}
}
}
//保存文件
package.Save();
var stream = FileUtily.FileToStream(Path.Combine(sWebRootFolder, sFileName));
var ExcelBytes = FileUtily.StreamToBytes(stream);
//删除文件
file.Delete();
return(ExcelBytes);
}
}
}