hotmaneil熱血新好男人

由於工作專案上要使用NPOI加入圖片，因此記錄下來。

原始碼如下：
 

        /// <summary>
		/// 建立副檔名為xlsx的Excel
		/// </summary>
		/// <param name="FirstFilePath"></param>
		/// <param name="SecondFilePath"></param>
		/// <param name="ShowSearchItemToolBar"></param>
		/// <param name="OperRankVList"></param>
		/// <param name="OperationTitle"></param>
		/// <returns></returns>
		public byte[] CreateXLSX(string FirstFilePath, string SecondFilePath, ShowSearchItemToolBarViewModel ShowSearchItemToolBar, List<OperRankViewModel> OperRankVList,string OperationTitle)
		{
			int firstPictureIndex = 0;
			int secondPictureIndex = 0;

			//建立工作表
			var workbook = new XSSFWorkbook();
			var sheet = workbook.CreateSheet("iBoxEDA");
			var patriarch = sheet.CreateDrawingPatriarch();

			#region style
			XSSFCellStyle cs = (XSSFCellStyle)workbook.CreateCellStyle();
			XSSFFont font = (XSSFFont)workbook.CreateFont();
			font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
			font.FontHeightInPoints = 12;
			cs.SetFont(font);

			XSSFCellStyle csGreen = (XSSFCellStyle)workbook.CreateCellStyle();
			XSSFFont fontGreen = (XSSFFont)workbook.CreateFont();
			fontGreen.Color = NPOI.HSSF.Util.HSSFColor.SeaGreen.Index;
			csGreen.SetFont(fontGreen);
			#endregion

			//建立第一行設定表頭資料
			sheet.CreateRow(0);
			sheet.GetRow(0).CreateCell(0).SetCellValue(ResourceLibrary.Resource.Equipment + ResourceLibrary.Resource.DifferenceSort);

			//建立第二行:查詢條件
			var row=sheet.CreateRow(1);
			sheet.GetRow(1).CreateCell(0).SetCellValue(ResourceLibrary.Resource.Product);
			sheet.GetRow(1).CreateCell(1).SetCellValue(ShowSearchItemToolBar.ProductItem);
			sheet.GetRow(1).CreateCell(2).SetCellValue(ResourceLibrary.Resource.CheckOperation);
			sheet.GetRow(1).CreateCell(3).SetCellValue(ShowSearchItemToolBar.CheckOperation);
			sheet.GetRow(1).CreateCell(4).SetCellValue(ResourceLibrary.Resource.CheckParameter);
			sheet.GetRow(1).CreateCell(5).SetCellValue(ShowSearchItemToolBar.CheckParameterName);
			sheet.GetRow(1).CreateCell(6).SetCellValue(ResourceLibrary.Resource.DataInterval);
			sheet.GetRow(1).CreateCell(7).SetCellValue(ShowSearchItemToolBar.DataInterval);
			
			row.GetCell(0).CellStyle = cs;
			row.GetCell(2).CellStyle = cs;
			row.GetCell(4).CellStyle = cs;
			row.GetCell(6).CellStyle = cs;

			//建立第三行
			var titleRow=sheet.CreateRow(2);
			sheet.GetRow(2).CreateCell(0).SetCellValue(ResourceLibrary.Resource.DifferenceSort);
			sheet.GetRow(2).CreateCell(2).SetCellValue("[" + OperationTitle + "]" + ResourceLibrary.Resource.EquipmentDifferenceAnalysis);
			titleRow.GetCell(0).CellStyle = csGreen;
			titleRow.GetCell(2).CellStyle = csGreen;

			int rowNum = 4;
			sheet.CreateRow(3);
			sheet.GetRow(3).CreateCell(0).SetCellValue(ResourceLibrary.Resource.Coefficient);
			sheet.GetRow(3).CreateCell(1).SetCellValue(ResourceLibrary.Resource.Operation);
			foreach (var item in OperRankVList)
			{
				sheet.CreateRow(rowNum);
				sheet.GetRow(rowNum).CreateCell(0).SetCellValue(item.Coefficient);
				sheet.GetRow(rowNum).CreateCell(1).SetCellValue(item.OperationName);
				rowNum++;
			}

			//建立第4行(第一張圖)
			var titleRow1= sheet.CreateRow(rowNum + 1);
			sheet.CreateRow(rowNum + 1).CreateCell(2).SetCellValue("*" + ResourceLibrary.Resource.MachineDifference);
			var row1 = sheet.CreateRow(rowNum + 2);
			
			//建立第5行(第二張圖)
			var titleRow2 = sheet.CreateRow(rowNum + 3);
			sheet.CreateRow(rowNum + 3).CreateCell(2).SetCellValue(ResourceLibrary.Resource.CentralizedTrendChart);
			var row2 = sheet.CreateRow(rowNum + 4);

			#region 取得圖之 FileStream
			using (FileStream fileStream1 = new FileStream(FirstFilePath, FileMode.Open, FileAccess.Read))
			{
				using (MemoryStream ms1 = new MemoryStream())
				{
					ms1.SetLength(fileStream1.Length);
					fileStream1.Read(ms1.GetBuffer(), 0, (int)fileStream1.Length);
					firstPictureIndex = workbook.AddPicture(ms1.ToArray(), PictureType.PNG);
				}

				//將圖定位到Workbook
				XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, 2, rowNum + 2, 0, 0);
				XSSFPicture picture1 = (XSSFPicture)patriarch.CreatePicture(anchor1, firstPictureIndex);
				var size1 = picture1.GetImageDimension();
				row1.HeightInPoints = size1.Height;
				picture1.Resize();
			}

			using (FileStream fileStream2 = new FileStream(SecondFilePath, FileMode.Open, FileAccess.Read))
			{
				using (MemoryStream ms2 = new MemoryStream())
				{
					ms2.SetLength(fileStream2.Length);
					fileStream2.Read(ms2.GetBuffer(), 0, (int)fileStream2.Length);
					secondPictureIndex = workbook.AddPicture(ms2.ToArray(), PictureType.PNG);
				}

				//將圖定位到Workbook
				XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 2, rowNum + 4, 0, 0);
				XSSFPicture picture2 = (XSSFPicture)patriarch.CreatePicture(anchor2, secondPictureIndex);
				var size2 = picture2.GetImageDimension();
				row2.HeightInPoints = size2.Height;
				picture2.Resize();
			}
			#endregion

			//將資料寫入串流
			MemoryStream file = new MemoryStream();

			//NPOIMemoryStream file = new NPOIMemoryStream();
			//file.AllowClose = false;
			workbook.Write(file);

			byte[] bytes = file.ToArray();

			return bytes;
		}