MemoryStream stream = new MemoryStream();
try
{
//从数据库中读取数据
if (main == null && list.Count == 0)
{
return new MemoryStream();
}
string fileName = $"文件名";
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage package = new ExcelPackage())
{
// Add VBA Code -此处不可用
//package.Workbook.CreateVBAProject();
//package.Workbook.CodeModule.Name = "Module1";//auto_run
//StringBuilder module = new StringBuilder();
//module.Append("Sub Auto_run()").Append("\r\n");
//module.Append("ActiveWorkbook.Password").Append("=").Append("123456").Append("\r\n");
//module.Append("ActiveWorkbook.Save").Append("\r\n");
//module.Append("End Sub").Append("\r\n");
//package.Workbook.CodeModule.Code = module.ToString();
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");//添加表格
//worksheet.SetValue(row, col, value);//这种赋值方法比上面的性能好一些
worksheet.Cells.Style.WrapText = true;//自动换行
double widthsub = 0.7;
//设置列宽
worksheet.Column(1).Width = 6 + widthsub;
worksheet.Column(2).Width = 6.5 + widthsub;
worksheet.Column(3).Width = 43.75 + widthsub;
worksheet.Column(4).Width = 13.25 + widthsub;
worksheet.Column(5).Width = 10 + widthsub;
worksheet.Row(1).Height = 12;//设置行高
worksheet.Row(2).Height = 16.5;//设置行高
worksheet.Row(3).Height = 16.5;//设置行高
worksheet.Row(4).Height = 14.25;//设置行高
worksheet.Row(5).Height = 14.25;//设置行高
using (var range = worksheet.Cells[1, 1, 1, 1]) //获取一个区域,区域范围是第一行第一列到第一行第五列
{
range.Style.Font.Bold = false;
range.Style.Font.Size = 12;
range.Style.Font.Name = "仿宋_GB2312";
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
worksheet.SetValue(1, 1, "角标");
}
//标题栏
using (var range = worksheet.Cells[2, 1, 2, 5]) //获取一个区域,区域范围是第一行第一列到第一行第五列
{
range.Merge = true;
range.Style.Font.Bold = true;
range.Style.Font.Size = 16;
range.Style.Font.Name = "仿宋_GB2312";
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
worksheet.SetValue(2, 1, "表名");
}
for (int i = 6; i < list.Count + 4 + 2; i++)
{
worksheet.Row(i).Height = 25;//设置行高
}
//表号栏
//int FromRow, int FromCol, int ToRow, int ToCol
using (var range = worksheet.Cells[3, 5, 3, 5])
{
range.Style.Font.Bold = false;
range.Style.Font.Size = 10;
range.Style.Font.Name = "宋体";//range.Merge = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;//水平居中
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
//worksheet.SetValue(3, 5, "肃宁运16-2");
worksheet.SetValue(3, 5, "");
}
string space = " ";
//表信息栏
string msg = "表头序;
using (var range = worksheet.Cells[4, 1, 4, 5])
{
range.Merge = true;
range.Style.Font.Size = 12;
range.Style.Font.Name = "仿宋_GB2312";
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
worksheet.SetValue(4, 1, msg);
}
//表头信息
using (var range = worksheet.Cells[5, 1, 5, 1])
{
range.Style.Font.Size = 12;
range.Style.Font.Name = "宋体";
worksheet.SetValue(5, 1, "列1");
}
using (var range = worksheet.Cells[5, 2, 5, 2])
{
range.Style.Font.Size = 12;
range.Style.Font.Name = "宋体";
worksheet.SetValue(5, 2, "列2");
}
using (var range = worksheet.Cells[5, 3, 5, 3])
{
range.Style.Font.Size = 12;
range.Style.Font.Name = "宋体";
worksheet.SetValue(5, 3, "列3");
}
using (var range = worksheet.Cells[5, 4, 5, 4])
{
range.Style.Font.Size = 12;
range.Style.Font.Name = "宋体";
worksheet.SetValue(5, 4, "列4");
}
using (var range = worksheet.Cells[5, 5, 5, 5])
{
range.Style.Font.Size = 12;
range.Style.Font.Name = "宋体";
worksheet.SetValue(5, 5, "列5");
}
for (int i = 1; i < 6; i++)
{
worksheet.Cells[5, i].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0,0,0));//设置单元格所有边框
worksheet.Cells[5, i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
worksheet.Cells[5, i].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
}
Dictionary<string, int> dic = new Dictionary<string, int>();
//
for (int m = 0; m < list.Count; m++)
{
if (dic.ContainsKey(list[m].department))
{
dic[list[m].属性] = dic[list[m].属性] + 1;
}
else
{
dic.Add(list[m].属性, 1);
}
//列1
using (var range = worksheet.Cells[m + 6, 2, m + 6, 2])
{
range.Style.Font.Size = 12;
range.Style.Font.Name = "仿宋_GB2312";
worksheet.SetValue(m + 6, 2, m + 1);
}
//列2
using (var range = worksheet.Cells[m + 6, 3, m + 6, 3])
{
range.Style.Font.Size = 10.5f;
range.Style.Font.Name = "仿宋_GB2312";
worksheet.SetValue(m + 6, 3, list[m].属性);
}
//列3
using (var range = worksheet.Cells[m + 6, 4, m + 6, 4])
{
range.Style.Font.Size = 10.5f;
range.Style.Font.Name = "仿宋_GB2312";
worksheet.SetValue(m + 6, 4, list[m].属性);
}
//列4
using (var range = worksheet.Cells[m + 6, 4, m + 6, 4])
{
range.Style.Font.Size = 12;
range.Style.Font.Name = "仿宋_GB2312";
if (list[m].acceptsub == 0)
{
worksheet.SetValue(m + 6, 5, "");
}
else
{
worksheet.SetValue(m + 6, 5, list[m].属性);
}
}
for (int i = 2; i < 6; i++)
{
worksheet.Cells[m + 6, i].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0,0,0));//设置单元格所有边框
worksheet.Cells[m + 6, i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
worksheet.Cells[m + 6, i].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
}
}
int num = 0;
int prenum = 0;
int curtemp = 0;
//合并列
foreach (var item in dic)
{
if (num == 0)
{
prenum = item.Value;
using (var range = worksheet.Cells[6, 1, 6 + item.Value - 1, 1])
{
range.Merge = true;
range.Style.Font.Size = 12;
range.Style.Font.Name = "仿宋_GB2312";
worksheet.SetValue(6, 1, item.Key);
range.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));//设置单元格所有边框
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
range.Style.SetTextVertical();
}
}
else
{
curtemp = item.Value;
using (var range = worksheet.Cells[6 + prenum, 1, 6 + curtemp + prenum - 1, 1])
{
range.Merge = true;
range.Style.Font.Size = 12;
range.Style.Font.Name = "仿宋_GB2312";
worksheet.SetValue(6 + prenum, 1, item.Key);
range.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));//设置单元格所有边框
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
range.Style.SetTextVertical();
}
prenum = curtemp;
}
num++;
}
string remark = "备注";
string str1 = $"备注";
using (var range = worksheet.Cells[6 + list.Count, 1, 6 + list.Count, 5])
{
range.Merge = true;
range.Style.WrapText = true;//自动换行
range.Style.Font.Size = 12;
range.Style.Font.Name = "宋体";
worksheet.SetValue(6 + list.Count, 1, str1);
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;//水平居中
range.Style.VerticalAlignment = ExcelVerticalAlignment.Top;//垂直居中
worksheet.Row(list.Count + 6).Height = 100;//设置行高
}
worksheet.Protection.AllowAutoFilter = false;
worksheet.Protection.AllowDeleteColumns = false;
worksheet.Protection.AllowDeleteRows = false;
worksheet.Protection.AllowEditObject = false;
worksheet.Protection.AllowEditScenarios = false;
worksheet.Protection.AllowFormatCells = false;
worksheet.Protection.AllowFormatColumns = false;
worksheet.Protection.AllowFormatRows = false;
worksheet.Protection.AllowInsertColumns = false;
worksheet.Protection.AllowInsertHyperlinks = false;
worksheet.Protection.AllowInsertRows = false;
worksheet.Protection.AllowPivotTables = false;
worksheet.Protection.AllowSelectLockedCells = false;
worksheet.Protection.AllowSelectUnlockedCells = false;
worksheet.Protection.AllowSort = false;
worksheet.Protection.IsProtected = false;
worksheet.Protection.SetPassword("密码");//给工作作表添加密码 防止修改
//package.SaveAs(stream,"密码");//给文件添加密码
package.SaveAs(stream);//给文件添加密码
stream.Flush();
stream.Position = 0;
//保存
//var pt = new MemoryStream(package.GetAsByteArray());
//return pt;
}
}
catch (Exception ex)
{
return new MemoryStream();
}
return stream;