XSSF AddPicture Slow

[lq709708490]

XSSF AddPicture Slow

Adding pictures using XSSF is slow. It takes only a few seconds to use HSSF, but it takes a few minutes to use XSSF.

[tonyqus]

Can you provide the reproduce code? How many pictures did you insert into a Excel and it takes minutes?

[lq709708490]

I'm sorry to say that, but because I used Dotnet.core.npoi.dll, I retested the data. Although there is not such a big gap, there is still a gap of nearly 10 seconds.

This is the latest report that I have tested 50 pictures. xlsx 14676ms xls 3496ms

I need to add 50 or more pictures for production reports.

Can this be optimized?

thanks

[lq709708490]

public class TTTTHelper
{
public static TTTTHelper Ins = new TTTTHelper();

   //样本序号样式
   private ICellStyle? LeftTitleStyle;
   private ICellStyle? FlagStyle;
   private ICellStyle? TitleStyle;
   private ICellStyle? SuccessStyle;
   private ICellStyle? FailStyle;

   public void DrawLeftTitle(ref IWorkbook workbook,ref ISheet sheet,int startRow,int startCell,string takePhotoPosition)
   {
       var region = new CellRangeAddress(startRow + 1, startRow + 3, startCell, startCell);
 
       sheet.AddMergedRegion(region);
       sheet.SetColumnWidth(startCell, 200);
       LeftTitleStyle ??= workbook.GetStyle(new CellModel()
       {
           Value = "样本序号",
           FillColor = IndexedColors.Grey25Percent.Index,
           IsShowBasicBorder = true
       });
       //if (workbook is HSSFWorkbook)
       //{
       //    LeftTitleStyle ??= workbook.GetStyle(new CellModel()
       //    {
       //        Value = "样本序号",
       //        FillColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index,
       //        IsShowBasicBorder = true
       //    });
       //}
       //else
       //{
       //    LeftTitleStyle ??= workbook.GetStyle(new CellModel()
       //    {
       //        Value = "样本序号",
       //        FillColor = IndexedColors.Grey25Percent.Index,
       //        IsShowBasicBorder = true
       //    });
       //}
       sheet.CreateRowEx(ref workbook, startRow).CreateCellEx(ref workbook, startCell, "样本序号").SetStyle(LeftTitleStyle).SetBorder(IndexedColors.Black.Index);
       ////图片Title为合并单元格
       sheet.CreateRowEx(ref workbook, startRow + 1, 3000).CreateCellEx(ref workbook, startCell, $"结果图片({takePhotoPosition})").SetStyle(LeftTitleStyle);
       sheet.CreateRowEx(ref workbook, startRow + 4).CreateCellEx(ref workbook, startCell, "胶水覆盖占比%").SetStyle(LeftTitleStyle).SetBorder(IndexedColors.Black.Index);
       sheet.CreateRowEx(ref workbook, startRow + 5).CreateCellEx(ref workbook, startCell, "检测结果").SetStyle(LeftTitleStyle).SetBorder(IndexedColors.Black.Index);
       IndexedColors.Black.Index.SetRangeBorder(ref workbook, ref sheet, region);
   }

   /// <summary>
   ///  绘制边缘状态数据  1 1 1 1 1 ....
   /// </summary>
   /// <param name="workbook"></param>
   /// <param name="sheet"></param>
   /// <param name="startRow"></param>
   /// <param name="startCell"></param>
   /// <param name="flagList"></param>
   public void DrawFlag(ref IWorkbook workbook, ref ISheet sheet, int startRow, int startCell, List<int> flagList)
   {
       FlagStyle ??= workbook.GetStyle(new CellModel()
       {
           FillColor = new byte[] {226, 239, 218},
           IsBold = true,
           IsShowBasicBorder = true
       });
       for (int i = 0; i < 10; i++)
       {
           object? val = flagList.Count > i ? flagList[i] : null;
           int cellIndex = startCell + i; 
           sheet.CreateRowEx(ref workbook, startRow, 350).CreateCellEx(ref workbook, cellIndex, val).SetStyle(FlagStyle).SetBorder(IndexedColors.Black.Index);
           sheet.SetColumnWidth(cellIndex, 1000);
       }
   }

   public void DrawOneContent(ref IWorkbook workbook, ref ISheet sheet,int startRow,int startCell,int index,List<int> flagList,string result)
   {
       //1、序号绘制
       var region = new CellRangeAddress(startRow, startRow, startCell, startCell + 9);
       sheet.AddMergedRegion(region);
       TitleStyle ??= workbook.GetStyle(new CellModel()
       {
           IsBold = true,
           IsShowBasicBorder = true
       });
       sheet.CreateRowEx(ref workbook, startRow,500).CreateCellEx(ref workbook, startCell, index).CellStyle = TitleStyle;

       //2、上位置绘制

       DrawFlag(ref workbook, ref sheet, startRow+1, startCell, flagList.Take(10).ToList());

       //3、图片绘制，此时还没设置图片
       var imageRegion = new CellRangeAddress(startRow + 2, startRow + 2, startCell, startCell + 9);
       sheet.AddMergedRegion(imageRegion);

       sheet.CreateRowEx(ref workbook, startRow + 2, 3000)
           .CreateCellEx(ref workbook, startCell, $"image");
       string filePath = AppDomain.CurrentDomain.BaseDirectory + "Template";
       var images = File.ReadAllBytes($@"{filePath}\111.png");
       if (true)
       {

           int pictureIndex = workbook.AddPicture(images, PictureType.PNG);
           ICreationHelper helper = workbook.GetCreationHelper();
           IDrawing drawing = sheet.CreateDrawingPatriarch();
           IClientAnchor anchor = helper.CreateClientAnchor();
           anchor.Col1 = startCell;//0 index based column
           anchor.Row1 = startRow + 2;//0 index based row
           anchor.Col2 = startCell + 10;
           anchor.Row2 = startRow + 3;
           drawing.CreatePicture(anchor, pictureIndex);
       }
      



       //4、下位置绘制
       DrawFlag(ref workbook, ref sheet, startRow + 3, startCell, flagList.Skip(10).ToList());
       //占比计算
       var percentRegion = new CellRangeAddress(startRow + 4, startRow + 4, startCell, startCell + 9);
       sheet.AddMergedRegion(percentRegion);

       double okNum = flagList.Count(n => n == 1);
       double percent = Math.Round(flagList.Count == 0 ? 0 : okNum / flagList.Count * 100,2);
       sheet.CreateRowEx(ref workbook, startRow + 4,350).CreateCellEx(ref workbook, startCell, $"{okNum} / {flagList.Count} = {percent}%").SetStyle(TitleStyle);
       //5、检测结果绘制
       var resultRegion = new CellRangeAddress(startRow + 5, startRow + 5, startCell, startCell + 9);
       sheet.AddMergedRegion(resultRegion);

       SuccessStyle ??= workbook.GetStyle(new CellModel()
       {
           Value = $"{result}",
           FontColor = IndexedColors.Green.Index,
           IsBold = true,
           FontSize = 16,
           IsShowBasicBorder = true
       });
       FailStyle ??= workbook.GetStyle(new CellModel()
       {
           Value = $"{result}",
           FontColor = IndexedColors.Red.Index,
           IsBold = true,
           FontSize = 16,
           IsShowBasicBorder = true
       });
       ICellStyle resultStyle = result?.ToUpper() == "PASS" ? SuccessStyle : FailStyle;
       sheet.CreateRowEx(ref workbook, startRow + 5,600)
           .CreateCellEx(ref workbook, startCell, $"{result}").SetStyle(resultStyle);


       IndexedColors.Black.Index.SetRangeBorder(ref workbook, ref sheet, region);
       IndexedColors.Black.Index.SetRangeBorder(ref workbook, ref sheet, imageRegion);
       IndexedColors.Black.Index.SetRangeBorder(ref workbook, ref sheet, percentRegion);
       IndexedColors.Black.Index.SetRangeBorder(ref workbook, ref sheet, resultRegion);
   }

   public void DrawAllContent(ref IWorkbook workbook, ref ISheet sheet)
   {
       int startRow = 1;
       for (int i = 0; i < 10; i++)
       {

           List<int> flagList = new List<int>()
           {
               1, 1, 1, 1, 1, 1,1, 1, 1, 0, 1, 1, 1, 1, 1, 1,1, 1, 1, 1
           };

           DrawLeftTitle(ref workbook, ref sheet, startRow,1, "侧面1");
           int startCell = 2;
           for (int j = 0; j < 5; j++)
           {
               var next = new Random().Next(0, 2);
               string result = next == 0 ? "Fail" : "Pass";
               DrawOneContent(ref workbook, ref sheet, startRow, startCell, j + 1, flagList, result);
               startCell += 10;
           }

           startRow += 6;
       }
       sheet.SetColumnWidth(0, 500);
       sheet.SetColumnWidth(1, 5000);
   }

   public long DrawXls()
   {
       Stopwatch s = new Stopwatch();
       s.Start();
       string filePath = $"{AppDomain.CurrentDomain.BaseDirectory}Template";
       IWorkbook workbook = new HSSFWorkbook(new MemoryStream(File.ReadAllBytes($@"{filePath}\Test.xls")));
       ISheet sheet = workbook.GetSheet("Sheet1");
       DrawAllContent(ref workbook, ref sheet);
       string fileName = @$"{filePath}\{DateTime.Now.Ticks}.xls";
       MemoryStream stream = new MemoryStream();
       workbook.Write(stream);
       File.WriteAllBytes(fileName, stream.ToArray());
       s.Stop();
       var elapsedMilliseconds = s.ElapsedMilliseconds;
       LeftTitleStyle = null;
       FlagStyle = null;
       TitleStyle = null;
       SuccessStyle = null;
       FailStyle = null;
       return elapsedMilliseconds;
   }

   public long DrawXlsx()
   {
       string filePath = $"{AppDomain.CurrentDomain.BaseDirectory}Template";
       Stopwatch s = new Stopwatch();
       s.Start();
       //重置EXCEL
       IWorkbook workbook = new XSSFWorkbook($@"{filePath}\Test.xlsx");
       ISheet sheet = workbook.GetSheet("Sheet1");
       DrawAllContent(ref workbook, ref sheet);
       string fileName = @$"{filePath}\{DateTime.Now.Ticks}.xlsx";
       MemoryStream stream = new MemoryStream();
       workbook.Write(stream);
       File.WriteAllBytes(fileName, stream.ToArray());
       var elapsedMilliseconds = s.ElapsedMilliseconds;
       return elapsedMilliseconds;
   }

}

[tonyqus]

Dotnetcore.npoi is a very old branch of NPOI (8-years-ago version). Can you test your case with the latest version of NPOI?