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;