void ConvertWorkSheetData(ISheet sheet, ref WorkSheetData target, DataFormatter df) { var rows = sheet.PhysicalNumberOfRows; var columns = sheet.GetRow(0).PhysicalNumberOfCells; var tab = sheet.SheetName; var data = new string[rows, columns]; var rowHeights = new int[rows]; var columnWidths = new int[columns]; IRow rowData; for (int r = 0; r < rows; r++) { rowData = sheet.GetRow(r); rowHeights[r] = Mathf.CeilToInt(rowData.HeightInPoints + 10); for (int c = 0; c < columns; c++) { data[r, c] = df.FormatCellValue(rowData.GetCell(c)); if (r == 0) { columnWidths[c] = Mathf.CeilToInt(sheet.GetColumnWidthInPixels(c) + 10); } } } target = new WorkSheetData(tab, data, rowHeights, columnWidths); }
/** * Calculates the dimensions in EMUs for the anchor of the given picture * * @param picture the picture Containing the anchor * @return the dimensions in EMUs */ public static Size GetDimensionFromAnchor(IPicture picture) { IClientAnchor anchor = picture.ClientAnchor; bool isHSSF = (anchor is HSSFClientAnchor); ISheet sheet = picture.Sheet; double w = 0; int col2 = anchor.Col1; //space in the leftmost cell w = sheet.GetColumnWidthInPixels(col2++); if (isHSSF) { w *= 1 - anchor.Dx1 / 1024d; } else { w -= anchor.Dx1 / (double)Units.EMU_PER_PIXEL; } while (col2 < anchor.Col2) { w += sheet.GetColumnWidthInPixels(col2++); } if (isHSSF) { w += sheet.GetColumnWidthInPixels(col2) * anchor.Dx2 / 1024d; } else { w += anchor.Dx2 / (double)Units.EMU_PER_PIXEL; } double h = 0; int row2 = anchor.Row1; h = GetRowHeightInPixels(sheet, row2++); if (isHSSF) { h *= 1 - anchor.Dy1 / 256d; } else { h -= anchor.Dy1 / (double)Units.EMU_PER_PIXEL; } while (row2 < anchor.Row2) { h += GetRowHeightInPixels(sheet, row2++); } if (isHSSF) { h += GetRowHeightInPixels(sheet, row2) * anchor.Dy2 / 256; } else { h += anchor.Dy2 / (double)Units.EMU_PER_PIXEL; } w *= Units.EMU_PER_PIXEL; h *= Units.EMU_PER_PIXEL; return(new Size((int)Math.Round(w), (int)Math.Round(h))); //return new Size((int)w * Units.EMU_PER_PIXEL, (int)h * Units.EMU_PER_PIXEL); }
/** * Calculate and Set the preferred size (anchor) for this picture. * * @param scaleX the amount by which image width is multiplied relative to the original width. * @param scaleY the amount by which image height is multiplied relative to the original height. * @return the new Dimensions of the scaled picture in EMUs */ public static Size SetPreferredSize(IPicture picture, double scaleX, double scaleY) { IClientAnchor anchor = picture.ClientAnchor; bool isHSSF = (anchor is HSSFClientAnchor); IPictureData data = picture.PictureData; ISheet sheet = picture.Sheet; // in pixel Size imgSize = GetImageDimension(new MemoryStream(data.Data), data.PictureType); // in emus Size anchorSize = ImageUtils.GetDimensionFromAnchor(picture); double scaledWidth = (scaleX == Double.MaxValue) ? imgSize.Width : anchorSize.Width / Units.EMU_PER_PIXEL * scaleX; double scaledHeight = (scaleY == Double.MaxValue) ? imgSize.Height : anchorSize.Height / Units.EMU_PER_PIXEL * scaleY; double w = 0; int col2 = anchor.Col1; int dx2 = 0; //space in the leftmost cell w = sheet.GetColumnWidthInPixels(col2++); if (isHSSF) { w *= 1d - anchor.Dx1 / 1024d; } else { w -= anchor.Dx1 / (double)Units.EMU_PER_PIXEL; } while (w < scaledWidth) { w += sheet.GetColumnWidthInPixels(col2++); } if (w > scaledWidth) { //calculate dx2, offset in the rightmost cell double cw = sheet.GetColumnWidthInPixels(--col2); double delta = w - scaledWidth; if (isHSSF) { dx2 = (int)((cw - delta) / cw * 1024); } else { dx2 = (int)((cw - delta) * Units.EMU_PER_PIXEL); } if (dx2 < 0) { dx2 = 0; } } anchor.Col2 = (/*setter*/ col2); anchor.Dx2 = (/*setter*/ dx2); double h = 0; int row2 = anchor.Row1; int dy2 = 0; h = GetRowHeightInPixels(sheet, row2++); if (isHSSF) { h *= 1 - anchor.Dy1 / 256d; } else { h -= anchor.Dy1 / (double)Units.EMU_PER_PIXEL; } while (h < scaledHeight) { h += GetRowHeightInPixels(sheet, row2++); } if (h > scaledHeight) { double ch = GetRowHeightInPixels(sheet, --row2); double delta = h - scaledHeight; if (isHSSF) { dy2 = (int)((ch - delta) / ch * 256); } else { dy2 = (int)((ch - delta) * Units.EMU_PER_PIXEL); } if (dy2 < 0) { dy2 = 0; } } anchor.Row2 = (/*setter*/ row2); anchor.Dy2 = (/*setter*/ dy2); Size dim = new Size( (int)Math.Round(scaledWidth * Units.EMU_PER_PIXEL), (int)Math.Round(scaledHeight * Units.EMU_PER_PIXEL) ); return(dim); }
public void GetClientAnchor() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(10); ICell cell = row.CreateCell(5); ICreationHelper factory = wb.GetCreationHelper(); IDrawing Drawing = sheet.CreateDrawingPatriarch(); double r_mul, c_mul; if (sheet is HSSFSheet) { double rowheight = Units.ToEMU(row.HeightInPoints) / Units.EMU_PER_PIXEL; r_mul = 256.0 / rowheight; double colwidth = sheet.GetColumnWidthInPixels(2); c_mul = 1024.0 / colwidth; } else { r_mul = c_mul = Units.EMU_PER_PIXEL; } int dx1 = (int)Math.Round(10 * c_mul); int dy1 = (int)Math.Round(10 * r_mul); int dx2 = (int)Math.Round(3 * c_mul); int dy2 = (int)Math.Round(4 * r_mul); int col1 = cell.ColumnIndex + 1; int row1 = row.RowNum; int col2 = cell.ColumnIndex + 2; int row2 = row.RowNum + 1; IClientAnchor anchor = Drawing.CreateAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2); IComment comment = Drawing.CreateCellComment(anchor); comment.Visible = (/*setter*/ true); cell.CellComment = (/*setter*/ comment); anchor = comment.ClientAnchor; Assert.AreEqual(dx1, anchor.Dx1); Assert.AreEqual(dy1, anchor.Dy1); Assert.AreEqual(dx2, anchor.Dx2); Assert.AreEqual(dy2, anchor.Dy2); Assert.AreEqual(col1, anchor.Col1); Assert.AreEqual(row1, anchor.Row1); Assert.AreEqual(col2, anchor.Col2); Assert.AreEqual(row2, anchor.Row2); anchor = factory.CreateClientAnchor(); comment = Drawing.CreateCellComment(anchor); cell.CellComment = (/*setter*/ comment); anchor = comment.ClientAnchor; if (sheet is HSSFSheet) { Assert.AreEqual(0, anchor.Col1); Assert.AreEqual(0, anchor.Dx1); Assert.AreEqual(0, anchor.Row1); Assert.AreEqual(0, anchor.Dy1); Assert.AreEqual(0, anchor.Col2); Assert.AreEqual(0, anchor.Dx2); Assert.AreEqual(0, anchor.Row2); Assert.AreEqual(0, anchor.Dy2); } else { // when anchor is Initialized without parameters, the comment anchor attributes default to // "1, 15, 0, 2, 3, 15, 3, 16" ... see XSSFVMLDrawing.NewCommentShape() Assert.AreEqual(1, anchor.Col1); Assert.AreEqual(15 * Units.EMU_PER_PIXEL, anchor.Dx1); Assert.AreEqual(0, anchor.Row1); Assert.AreEqual(2 * Units.EMU_PER_PIXEL, anchor.Dy1); Assert.AreEqual(3, anchor.Col2); Assert.AreEqual(15 * Units.EMU_PER_PIXEL, anchor.Dx2); Assert.AreEqual(3, anchor.Row2); Assert.AreEqual(16 * Units.EMU_PER_PIXEL, anchor.Dy2); } }
/// <summary> /// Converts the worksheet from the given file to a string. /// Applies the given template file for the conversion. /// </summary> /// <param name="filename">The path to the worksheet file</param> /// <param name="templateFile">The path to the template file that is used for the conversion</param> /// <returns>The converted worksheet as string</returns> public string ConvertWorksheet(string filename, string templateFile) { var workbook = new Workbook { Name = "TestWorkbook" }; var fileinfo = new FileInfo(filename); if (!fileinfo.Exists) { throw new FileNotFoundException(); } workbook.Name = fileinfo.Name; IWorkbook poiWorkbook; using (FileStream file = new FileStream(filename, FileMode.Open, FileAccess.Read)) { if (filename.EndsWith(".xlsx")) { poiWorkbook = new XSSFWorkbook(file); } else if (filename.EndsWith(".xls")) { poiWorkbook = new HSSFWorkbook(file); } else { throw new Exception("Not Supported File Format"); } } var worksheets = new List <Worksheet>(); for (int j = 0; j < poiWorkbook.NumberOfSheets; j++) { ISheet sheet = poiWorkbook.GetSheetAt(j); int rowCount = sheet.LastRowNum; IRow headerRow = sheet.GetRow(0); int colCount = 0; if (headerRow != null) { colCount = headerRow.LastCellNum; for (int rowNum = 0; rowNum < rowCount; rowNum++) { IRow hssfRow = sheet.GetRow(rowNum); if (hssfRow != null) { colCount = hssfRow.LastCellNum > colCount ? hssfRow.LastCellNum : colCount; } } } var worksheet = new Worksheet(); worksheet.Name = sheet.SheetName; var rows = new List <Row>(); var sheetWidth = 0; for (int cellNum = 0; cellNum < colCount; cellNum++) { sheetWidth += Convert.ToInt32(Math.Round(sheet.GetColumnWidthInPixels(cellNum))); } worksheet.WidthPixel = sheetWidth; for (int rowNum = 0; rowNum <= rowCount; rowNum++) { var row = new Row(); var cells = new List <Cell>(); IRow hssfRow = sheet.GetRow(rowNum); if (hssfRow != null) { for (int cellNum = 0; cellNum < colCount; cellNum++) { var cell = new Cell(); var hssfCell = hssfRow.GetCell(cellNum, MissingCellPolicy.CREATE_NULL_AS_BLANK); if (hssfCell.CellStyle.FillPattern == FillPattern.SolidForeground) { byte[] cellBackground = hssfCell.CellStyle.FillForegroundColorColor.RGB; var hexBackground = "#" + string.Concat(cellBackground.Select(b => b.ToString("X2")).ToArray()); cell.BackgroundColor = hexBackground; } if (hssfCell.CellStyle != null && hssfCell.CellStyle.GetFont(poiWorkbook) != null) { var foregroundColor = IndexedColors.ValueOf(hssfCell.CellStyle.GetFont(poiWorkbook).Color); if (foregroundColor != null) { cell.TextColor = foregroundColor.HexString; } } if (hssfCell.CellType == CellType.Formula) { cell.Value = hssfCell.NumericCellValue.ToString(); } else { cell.Value = hssfCell.ToString(); } var cellWidthPixel = Convert.ToInt32(Math.Round(sheet.GetColumnWidthInPixels(cellNum))); cell.WidthPixel = cellWidthPixel; cells.Add(cell); } } row.Cells = cells; rows.Add(row); } worksheet.Rows = rows; worksheets.Add(worksheet); } workbook.Worksheets = worksheets; poiWorkbook = null; return(renderer.RenderWorkbook(workbook, templateFile)); }
public static MemoryStream ToMemoryStram(SummaryInfo summaryInfo, List <Matrix <InspectionDataReport> > list_InspectionDataReportMatrix, List <DefectTypeInfoReport> list_DefectTypeInfoReport, DefectStatistics defectStatistics, int ReportModel) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(ExcelText.SheetName_SummaryOperator); #region SummaryOperator int rowIndex = 0; int code2drowIndex = 0; //1、产品编号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.ProductCode, summaryInfo.ProductCode); //2、批次号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Lot, summaryInfo.Lot); //3、设备号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Machine, summaryInfo.Machine); //4、操作员 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.AIOperator, summaryInfo.AIOperator); //5、开始日期 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.StartDate, summaryInfo.StartDate); //6、开始时间 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.StartTime, summaryInfo.StartTime); //7、结束日期 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.EndDate, summaryInfo.EndDate); //8、结束时间 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.EndTime, summaryInfo.EndTime); rowIndex++; //10、批次理论盘数 没有输入默认10 已屏蔽 //CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.TotalNumberOfStrips, summaryInfo.TotalNumberOfStrips.ToString()); //11、已检测的盘数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStripsInspected, summaryInfo.NumberOfStripsInspected.ToString()); //12、未检测的盘数 已屏蔽 //CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStripsNotInspected, summaryInfo.NumberOfStripsNotInspected.ToString()); //13、单盘芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.QuantityOfDevicesPerStrip, summaryInfo.QuantityOfDevicesPerStrip.ToString()); //14、批次理论芯片数 //CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStartQuantity, summaryInfo.NumberOfStartQuantity.ToString()); //15、批次已检测芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesInspected, summaryInfo.NumberOfDevicesInspected.ToString()); //16、每小时检测芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DevicesPerHour, string.Format("{0:0.##}", summaryInfo.DevicesPerHour)); rowIndex++; //17、合格芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesPassed, summaryInfo.NumberOfDevicesPassed.ToString()); //18、不合格的芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesRejected, summaryInfo.NumberOfDevicesRejected.ToString()); //19、误检芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesFalseCalled, summaryInfo.NumberOfDevicesFalseCalled.ToString()); //新增 OK复看为NG数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, "OK复看NG数", summaryInfo.NumberOfReviewNG.ToString()); //20、跳过的芯片数 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfNoDies, summaryInfo.NumberOfNoDies.ToString()); //21、合格率 存在二维码时根据二维码总数 if (ReportModel == 1) { //二维码总数 只在武汉二维码报表模式生成 CreateOneRowTwoColumnCells(sheet, ref rowIndex, string.Format("{0}", "二维码总数"), summaryInfo.CodeNumber.ToString()); double temp = summaryInfo.NumberOfDevicesPassed / (double)summaryInfo.CodeNumber; CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.YieldByDevice, string.Format("{0:0.##}", temp.ToString())); } else { CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.YieldByDevice, string.Format("{0:0.##}", summaryInfo.YieldByDevice)); } //22、误检率 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.FalseCallDevicePercent, string.Format("{0:0.##}", summaryInfo.FalseCallDevicePercent)); //新增 当存在 K2N时显示算上复看OK到KG的误检率 if (summaryInfo.NumberOfReviewNG != 0) { // CreateOneRowTwoColumnCells(sheet, ref rowIndex, "误检率(%包含复看不合格)", string.Format("{0:0.##}", summaryInfo.DevicePercentOfK2N)); } rowIndex++; //23、错误种类出现数量 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DefectType, ExcelText.Count); foreach (DefectTypeInfoReport defectTypeInfoReport in list_DefectTypeInfoReport) { if (defectTypeInfoReport.Count > 0) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, string.Format("{0}:{1}", defectTypeInfoReport.Index, defectTypeInfoReport.DefectType), defectTypeInfoReport.Count.ToString()); } } rowIndex++; //24、错误种类根据错误优先级统计数量 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DefectType + "(根据错误优先级表)", ExcelText.Count); if (defectStatistics.Flag == false) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, "警告,存在未规定的错误优先级", ""); } else { foreach (KeyValuePair <int, int> ds in defectStatistics.CountDefectResult) { if (defectStatistics.CountN2KDefectResult.ContainsKey(ds.Key)) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, string.Format("{0}:{1}", ds.Key, list_DefectTypeInfoReport.Find(ls => ls.Index == ds.Key).DefectType), string.Format("{0}-{1}={2}", ds.Value, defectStatistics.CountN2KDefectResult[ds.Key], ds.Value - defectStatistics.CountN2KDefectResult[ds.Key])); } else { CreateOneRowTwoColumnCells(sheet, ref rowIndex, string.Format("{0}:{1}", ds.Key, list_DefectTypeInfoReport.Find(ls => ls.Index == ds.Key).DefectType), ds.Value.ToString()); } } //25、是否存在优先级相同并且一同输出的 if (defectStatistics.List_RepeatPriority.Count != 0) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, "警告 存在优先级相同情况", ""); for (int i = 0; i < defectStatistics.List_RepeatPriority.Count; i++) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, defectStatistics.List_RepeatPriority[i].ToString(), ""); } } } sheet.SetColumnWidth(0, 80 * 256); sheet.SetColumnWidth(0, 30 * 256); #endregion workbook.CreateSheet(ExcelText.SheetName_MapOperator); workbook.CreateSheet(ExcelText.SheetName_UDD); int T = 0; #region 创建错误图片Sheet sheet = workbook.CreateSheet("错误图片"); int sheetIndex = workbook.GetSheetIndex(sheet); sheet.SetColumnWidth(1, 30 * 256); sheet.SetColumnWidth(2, 30 * 256); int ImageSheetNum = 0; T = T + rowIndex; rowIndex = 0; foreach (Matrix <InspectionDataReport> inspectionDataReportMatrix in list_InspectionDataReportMatrix) { if (rowIndex >= 65000) { ImageSheetNum++; sheet = workbook.CreateSheet("错误图片续_" + ImageSheetNum); sheetIndex = workbook.GetSheetIndex(sheet); rowIndex = 0; } HSSFPatriarch sheetPatriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFCell cell = null; int defectCountInFrame = 0; //int sheetContinuedIndexEachStrip = 0; int ImageCol = 0; foreach (InspectionDataReport dataReport in inspectionDataReportMatrix) { defectCountInFrame++; int TempImage_row = 0; ImageCol = 5; int startRow = 0; if (dataReport == null) { continue; } if (dataReport.List_DefectData.Count == 0) { if (dataReport.InspectionResult != InspectionResult.K2N) { continue; } } startRow = rowIndex; sheet.CreateRow(rowIndex); cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(0); cell.SetCellValue(string.Format("{0}-{1}", dataReport.RowIndex + 1, dataReport.ColumnIndex + 1)); cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1); cell.SetCellValue(string.Format("第{0}条", dataReport.FrameIndex)); if (dataReport.Code2D != "null") { cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(2); cell.SetCellValue(string.Format("二维码:{0}", dataReport.Code2D)); } // 创建图片 所有缺陷在一张图中 如果存在拍多次的情况 进行横排 第一张图 col为5 System.Drawing.Image[] images = new System.Drawing.Image[dataReport.List_GeneralImageTempPath.Count]; for (int i = 0; i < images.Count(); i++) { //创建图片单元格 cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(ImageCol); if (File.Exists(dataReport.List_GeneralImageTempPath[i])) { byte[] bytes = File.ReadAllBytes(dataReport.List_GeneralImageTempPath[i]); int index = workbook.AddPicture(bytes, PictureType.JPEG); System.Drawing.Image image; using (MemoryStream ms = new MemoryStream(bytes)) { image = System.Drawing.Image.FromStream(ms); } double imageWidth = image.Width; double imageHeight = image.Height; image.Dispose(); double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex); double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96; int imageInCellColumns = (int)(imageWidth / cellWidth); int imageInCellRows = (int)(imageHeight / cellHeight); double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024; double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256; HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, ImageCol, rowIndex, imageInCellColumns + ImageCol, rowIndex + imageInCellRows); sheetPatriarch.CreatePicture(anchor, index); //计算图片高占多少个单元格 TempImage_row = (int)Math.Ceiling(imageHeight / cellHeight) + 1; NPOI.SS.Util.CellReference cellReference = new NPOI.SS.Util.CellReference(rowIndex + TempImage_row, 1); dataReport.ExcelDefectImageLink = string.Format("'{0}'!{1}", sheet.SheetName, cellReference.FormatAsString()); } else { cell.SetCellValue("图片不存在"); } ImageCol = ImageCol + 10; } rowIndex = rowIndex + 1; //记录错误信息 foreach (var defectReort in dataReport.List_DefectData) { rowIndex = rowIndex + 1; sheet.CreateRow(rowIndex); cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1); if (defectReort.DefectTypeIndex <= 0 || defectReort.DefectTypeIndex > list_DefectTypeInfoReport.Count) { cell.SetCellValue(string.Format("{0}:{1}", defectReort.DefectTypeIndex, defectReort.ErrorDetail)); } else { cell.SetCellValue(string.Format("{0}:{1}", list_DefectTypeInfoReport[defectReort.DefectTypeIndex - 1].DefectType, defectReort.ErrorDetail)); } } rowIndex = rowIndex + 1; string[] show_defecttype = new string[dataReport.List_DefectData.Count]; for (int i = 0; i < dataReport.List_DefectData.Count; i++) { show_defecttype[i] = dataReport.List_DefectData[i].DefectTypeIndex.ToString(); } sheet.CreateRow(rowIndex); cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1); cell.SetCellValue(string.Format("详细错误码:{0}", string.Join(";", show_defecttype))); //判断 图片所占行数多 还是写入的错误信息行数多 if (rowIndex - startRow <= TempImage_row) { rowIndex = startRow + TempImage_row + 2; } #region 每个缺陷对应一张图 已屏蔽 //foreach (var defectReort in dataReport.List_DefectData) //{ // if (rowIndex>= 32757) // { // sheetContinuedIndexEachStrip++; // sheet = workbook.CreateSheet(inspectionDataReportMatrix.FrameName + " " + ExcelText.Continued + sheetContinuedIndexEachStrip.ToString()); // sheetIndex = workbook.GetSheetIndex(sheet); // sheetPatriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); // rowIndex = 0; // } // defectCountInFrame++; // sheet.CreateRow(rowIndex); // sheet.CreateRow(rowIndex + 1); // HSSFCell cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(0); // cell.SetCellValue(string.Format("{0}-{1}", dataReport.RowIndex + 1, dataReport.ColumnIndex + 1)); // cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1); // cell.SetCellValue(list_DefectTypeInfoReport[defectReort.DefectTypeIndex - 1].DefectType); // cell = (HSSFCell)sheet.GetRow(rowIndex + 1).CreateCell(1); // cell.SetCellValue(defectReort.ErrorDetail); // rowIndex += 2; // sheet.CreateRow(rowIndex); // cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1); // if (File.Exists(defectReort.ImageTempPath)) // { // byte[] bytes = File.ReadAllBytes(defectReort.ImageTempPath); // int index = workbook.AddPicture(bytes, PictureType.JPEG); // System.Drawing.Image image; // using (MemoryStream ms = new MemoryStream(bytes)) // { // image = System.Drawing.Image.FromStream(ms); // } // double imageWidth = image.Width; // double imageHeight = image.Height; // image.Dispose(); // double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex); // double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96; // int imageInCellColumns = (int)(imageWidth / cellWidth); // int imageInCellRows = (int)(imageHeight / cellHeight); // double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024; // double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256; // // //HSSFClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows); // //commentAnchor.AnchorType = AnchorType.MoveDontResize; // //HSSFComment comment = (HSSFComment)sheetPatriarch.CreateCellComment(commentAnchor); // //comment.SetBackgroundImage(index); // //cell.CellComment = (comment); // HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 1, rowIndex, imageInCellColumns + 1, rowIndex + imageInCellRows); // sheetPatriarch.CreatePicture(anchor, index); // if (!hasRecordLink) // { // NPOI.SS.Util.CellReference cellReference = new NPOI.SS.Util.CellReference(rowIndex, 1); // dataReport.ExcelDefectImageLink = string.Format("'{0}'!{1}", sheet.SheetName, cellReference.FormatAsString()); // hasRecordLink = true; // } // rowIndex += (int)Math.Ceiling(imageHeight / cellHeight) + 1; // } // else // { // cell.SetCellValue("图片不存在"); // } //} #endregion } if (defectCountInFrame == 0) { workbook.RemoveSheetAt(sheetIndex); } } #endregion #region MapOperator sheet = workbook.GetSheet(ExcelText.SheetName_MapOperator); if (ReportModel != 1) { sheet.DefaultColumnWidth = 1; } //sheet = workbook.GetSheetAt(1); rowIndex = 0; //1、产品编号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.ProductCode, summaryInfo.ProductCode); //2、批次号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Lot, summaryInfo.Lot); //3、设备号 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Machine, summaryInfo.Machine); //4、操作员 CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.AIOperator, summaryInfo.AIOperator); rowIndex++; //5、检测结果样例 Dictionary <InspectionResult, ICellStyle> dict_result_style = new Dictionary <InspectionResult, ICellStyle>(); ICellStyle exampleStyle = workbook.CreateCellStyle(); exampleStyle.BorderBottom = BorderStyle.Thin; exampleStyle.BorderTop = BorderStyle.Thin; exampleStyle.BorderLeft = BorderStyle.Thin; exampleStyle.BorderRight = BorderStyle.Thin; exampleStyle.FillPattern = FillPattern.SolidForeground; exampleStyle.Alignment = HorizontalAlignment.Center; exampleStyle.FillForegroundColor = (short)ExcelColors.Green; dict_result_style.Add(InspectionResult.OK, exampleStyle); CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.OK), exampleStyle); exampleStyle = workbook.CreateCellStyle(); exampleStyle.BorderBottom = BorderStyle.Thin; exampleStyle.BorderTop = BorderStyle.Thin; exampleStyle.BorderLeft = BorderStyle.Thin; exampleStyle.BorderRight = BorderStyle.Thin; exampleStyle.FillPattern = FillPattern.SolidForeground; exampleStyle.Alignment = HorizontalAlignment.Center; exampleStyle.FillForegroundColor = (short)ExcelColors.Red; dict_result_style.Add(InspectionResult.NG, exampleStyle); CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.NG), exampleStyle); exampleStyle = workbook.CreateCellStyle(); exampleStyle.BorderBottom = BorderStyle.Thin; exampleStyle.BorderTop = BorderStyle.Thin; exampleStyle.BorderLeft = BorderStyle.Thin; exampleStyle.BorderRight = BorderStyle.Thin; exampleStyle.FillPattern = FillPattern.SolidForeground; exampleStyle.Alignment = HorizontalAlignment.Center; exampleStyle.FillForegroundColor = (short)ExcelColors.Yellow; dict_result_style.Add(InspectionResult.N2K, exampleStyle); CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.N2K), exampleStyle); exampleStyle = workbook.CreateCellStyle(); exampleStyle.BorderBottom = BorderStyle.Thin; exampleStyle.BorderTop = BorderStyle.Thin; exampleStyle.BorderLeft = BorderStyle.Thin; exampleStyle.BorderRight = BorderStyle.Thin; exampleStyle.FillPattern = FillPattern.SolidForeground; exampleStyle.Alignment = HorizontalAlignment.Center; exampleStyle.FillForegroundColor = (short)ExcelColors.Orange; dict_result_style.Add(InspectionResult.K2N, exampleStyle); CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.K2N), exampleStyle); exampleStyle = workbook.CreateCellStyle(); exampleStyle.BorderBottom = BorderStyle.Thin; exampleStyle.BorderTop = BorderStyle.Thin; exampleStyle.BorderLeft = BorderStyle.Thin; exampleStyle.BorderRight = BorderStyle.Thin; exampleStyle.FillPattern = FillPattern.SolidForeground; exampleStyle.Alignment = HorizontalAlignment.Center; exampleStyle.FillForegroundColor = (short)ExcelColors.SkyBlue; dict_result_style.Add(InspectionResult.SKIP, exampleStyle); CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.SKIP), exampleStyle); rowIndex++; //6、分盘绘制图谱 HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); ICellStyle centerAlignmentStyle = workbook.CreateCellStyle(); centerAlignmentStyle.Alignment = HorizontalAlignment.Center; int sheetContinuedIndex = 0; foreach (Matrix <InspectionDataReport> inspectionDataReportMatrix in list_InspectionDataReportMatrix) { if (inspectionDataReportMatrix.ColumnCount < 254) { //创建单元格 for (int i = 0; i < inspectionDataReportMatrix.RowCount + 1; i++) { IRow createRow = sheet.CreateRow(rowIndex + i); for (int j = 0; j < inspectionDataReportMatrix.ColumnCount + 2; j++) { createRow.CreateCell(j); } } //赋值条号 sheet.GetRow(rowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", ExcelText.Strip, inspectionDataReportMatrix.FrameName)); for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++) { sheet.GetRow(rowIndex + i).GetCell(1).SetCellValue((i + 1).ToString()); } for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++) { ICell cell = sheet.GetRow(rowIndex + inspectionDataReportMatrix.RowCount).GetCell(i + 2); cell.SetCellValue((i + 1).ToString()); cell.CellStyle = centerAlignmentStyle; } //创建二维码列表 if (ReportModel == 1) { code2drowIndex = rowIndex + inspectionDataReportMatrix.RowCount + 2; //创建单元格 for (int i = 0; i < inspectionDataReportMatrix.RowCount + 1; i++) { IRow createRow = sheet.CreateRow(code2drowIndex + i); for (int j = 0; j < inspectionDataReportMatrix.ColumnCount + 2; j++) { createRow.CreateCell(j); } } //赋值二维码 sheet.GetRow(code2drowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", "二维码", inspectionDataReportMatrix.FrameName)); for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++) { sheet.GetRow(code2drowIndex + i).GetCell(1).SetCellValue((i + 1).ToString()); } //for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++) //{ // ICell cell = sheet.GetRow(code2drowIndex + inspectionDataReportMatrix.RowCount).GetCell(i + 2); // //cell.SetCellValue((i + 1).ToString()); // cell.CellStyle = centerAlignmentStyle; //} foreach (InspectionDataReport data1 in inspectionDataReportMatrix) { if (data1 == null) { continue; } ICell cell; if (inspectionDataReportMatrix.ColumnCount < 254) { cell = sheet.GetRow(code2drowIndex + data1.RowIndex).GetCell(data1.ColumnIndex + 2); } else { cell = sheet.GetRow(code2drowIndex + data1.ColumnIndex).GetCell(data1.RowIndex + 2); } cell.SetCellValue(data1.Code2D); } } } else { if (rowIndex + inspectionDataReportMatrix.ColumnCount + 1 >= 32757) { sheetContinuedIndex++; sheet = workbook.CreateSheet(ExcelText.SheetName_MapOperator + " " + ExcelText.Continued + sheetContinuedIndex.ToString()); rowIndex = 0; } for (int i = 0; i < inspectionDataReportMatrix.ColumnCount + 1; i++) { IRow createRow = sheet.CreateRow(rowIndex + i); for (int j = 0; j < inspectionDataReportMatrix.RowCount + 2; j++) { createRow.CreateCell(j); } } sheet.GetRow(rowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", ExcelText.Strip, inspectionDataReportMatrix.FrameName)); for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++) { sheet.GetRow(rowIndex + i).GetCell(1).SetCellValue((i + 1).ToString()); } for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++) { ICell cell = sheet.GetRow(rowIndex + inspectionDataReportMatrix.ColumnCount).GetCell(i + 2); cell.SetCellValue((i + 1).ToString()); cell.CellStyle = centerAlignmentStyle; } } foreach (InspectionDataReport data in inspectionDataReportMatrix) { if (data == null) { continue; } ICell cell; if (inspectionDataReportMatrix.ColumnCount < 254) { cell = sheet.GetRow(rowIndex + data.RowIndex).GetCell(data.ColumnIndex + 2); } else { cell = sheet.GetRow(rowIndex + data.ColumnIndex).GetCell(data.RowIndex + 2); } cell.CellStyle = dict_result_style[data.InspectionResult]; if (data.InspectionResult == InspectionResult.NG) { //string[] defectTypes = new string[data.List_DefectData.Count]; //for (int i = 0; i < data.List_DefectData.Count; i++) //{ // defectTypes[i] = data.List_DefectData[i].DefectTypeIndex.ToString(); //} string[] noRepeatDefectTyoes = new string[data.Priority_DetectType.Count]; for (int i = 0; i < data.Priority_DetectType.Count; i++) { noRepeatDefectTyoes[i] = data.Priority_DetectType[i].ToString(); } //string[] noRepeatDefectTyoes = data.List_DefectData.Select(d => d.DefectTypeIndex.ToString()).Distinct().ToArray(); cell.SetCellValue(string.Join(";", noRepeatDefectTyoes)); } if (data.InspectionResult == InspectionResult.NG || data.InspectionResult == InspectionResult.N2K || data.InspectionResult == InspectionResult.K2N) { if (data.ExcelDefectImageLink == null) { continue; } HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document); link.Address = data.ExcelDefectImageLink; cell.Hyperlink = link; } #region 对NG图 以及 N2K图添加批注 鼠标悬停可以显示图片 已屏蔽 //if (data.InspectionResult == InspectionResults.NG || data.InspectionResult == InspectionResults.N2K) //{ // //int defectCount = data.List_DefectData.Count; // //System.Drawing.Image[] images = new System.Drawing.Image[defectCount]; // System.Drawing.Image[] images = new System.Drawing.Image[data.List_GeneralImageTempPath.Count]; // double imageMaxWidth = 0; // double imageTotalHeight = 0; // //for (int i = 0; i < defectCount; i++) // //{ // // if (!File.Exists(data.List_DefectData[i].ImageTempPath)) break; // // byte[] bytesOfImage = File.ReadAllBytes(data.List_DefectData[i].ImageTempPath); // // using (MemoryStream ms = new MemoryStream(bytesOfImage)) // // { // // images[i] = System.Drawing.Image.FromStream(ms); // // } // // if (images[i].Width > imageMaxWidth) // // { // // imageMaxWidth = images[i].Width; // // } // // imageTotalHeight += images[i].Height; // //} // for (int i = 0; i < images.Count(); i++) // { // if (!File.Exists(data.List_GeneralImageTempPath[i])) break; // byte[] bytesOfImage = File.ReadAllBytes(data.List_GeneralImageTempPath[i]); // using (MemoryStream ms = new MemoryStream(bytesOfImage)) // { // images[i] = System.Drawing.Image.FromStream(ms); // } // if (images[i].Width > imageMaxWidth) // { // imageMaxWidth = images[i].Width; // } // imageTotalHeight += images[i].Height; // } // if (imageMaxWidth == 0 || imageTotalHeight == 0) continue; // Bitmap concatImage = new Bitmap((int)imageMaxWidth, (int)imageTotalHeight); // concatImage.SetResolution(300, 300); // Graphics g = Graphics.FromImage(concatImage); // float drawStartHeight = 0; // for (int i = 0; i < images.Count(); i++) // { // g.DrawImage(images[i], 0, drawStartHeight); // drawStartHeight += images[i].Height; // images[i].Dispose(); // } // byte[] bytesOfConcatImage; // using (MemoryStream ms = new MemoryStream()) // { // concatImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg); // bytesOfConcatImage = ms.ToArray(); // } // int index = workbook.AddPicture(bytesOfConcatImage, PictureType.JPEG); // double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex); // //EXCEL列高度的单位是磅,Apache POI的行高度单位是缇(twip) // //DPI = 1英寸内可显示的像素点个数。通常电脑屏幕是96DPI, IPhone4s的屏幕是326DPI, 普通激光黑白打印机是400DPI // //要计算Excel的行高,就先把它行转换到英寸,再乘小DPI就可以得到像素 // //像素 = (Excel的行高度 / 72) * DPI // double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96; // int imageInCellColumns = (int)(imageMaxWidth / cellWidth); // int imageInCellRows = (int)(imageTotalHeight / cellHeight); // double offsetX = (imageMaxWidth - cellWidth * imageInCellColumns) / cellWidth * 1024; // double offsetY = (imageTotalHeight - cellHeight * imageInCellRows) / cellHeight * 256; // IClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows); // commentAnchor.AnchorType = AnchorType.MoveDontResize; // HSSFComment comment = (HSSFComment)patriarch.CreateCellComment(commentAnchor); // comment.SetBackgroundImage(index); // cell.CellComment = comment; // HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document); // link.Address = data.ExcelDefectImageLink; // cell.Hyperlink = link; // //NPOI.SS.Util.CellReference cr = new NPOI.SS.Util.CellReference("A1"); // //ICellStyle hlink_style = hssfworkbook.CreateCellStyle(); // //IFont hlink_font = hssfworkbook.CreateFont(); // //hlink_font.Underline = FontUnderlineType.Single; // //hlink_font.Color = HSSFColor.Blue.Index; // //hlink_style.SetFont(hlink_font); // //cell.CellStyle = (hlink_style); // //HSSFCell picIndexCell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(picColumnIndex); // //picIndexCell.SetCellValue(string.Format("{0}-{1}", data.Row + 1, data.Column + 1)); // //picIndexCell.CellStyle = centerAlignmentStyle; // //if (!data.PicturePath.Equals(string.Empty)) // //{ // // if (File.Exists(data.PicturePath)) // // { // // byte[] bytes = File.ReadAllBytes(data.PicturePath); // // int index = workbook.AddPicture(bytes, PictureType.JPEG); // // System.Drawing.Image image; // // using (MemoryStream ms = new MemoryStream(bytes)) // // { // // image = System.Drawing.Image.FromStream(ms); // // } // // double imageWidth = image.Width; // // double imageHeight = image.Height; // // image.Dispose(); // // double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex); // // //EXCEL列高度的单位是磅,Apache POI的行高度单位是缇(twip) // // //DPI = 1英寸内可显示的像素点个数。通常电脑屏幕是96DPI, IPhone4s的屏幕是326DPI, 普通激光黑白打印机是400DPI // // //要计算Excel的行高,就先把它行转换到英寸,再乘小DPI就可以得到像素 // // //像素 = (Excel的行高度 / 72) * DPI // // double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96; // // int imageInCellColumns = (int)(imageWidth / cellWidth); // // int imageInCellRows = (int)(imageHeight / cellHeight); // // double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024; // // double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256; // // HSSFClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows); // // commentAnchor.AnchorType = AnchorType.MoveDontResize; // // HSSFComment comment = (HSSFComment)patriarch.CreateCellComment(commentAnchor); // // comment.SetBackgroundImage(index); // // cell.CellComment = (comment); // // //HSSFClientAnchor anchor = new HSSFClientAnchor(100, 0, 923, 0, picColumnIndex, rowIndex + 1, picColumnIndex, rowIndex + dieDataSQLMatrix.RowCount); // // //patriarch.CreatePicture(anchor, index); // // } // //} // //else // //{ // // cell = (HSSFCell)sheet.GetRow(rowIndex + 1).CreateCell(picColumnIndex); // // cell.SetCellValue("图片不存在"); // // cell.CellStyle = centerAlignmentStyle; // //} // //picColumnIndex++; //} #endregion } if (inspectionDataReportMatrix.ColumnCount < 254) { if (ReportModel == 1) { rowIndex = code2drowIndex; } rowIndex += inspectionDataReportMatrix.RowCount + 2; } else { rowIndex += inspectionDataReportMatrix.ColumnCount + 2; } } sheet.SetColumnWidth(0, 20 * 256); #endregion #region UDD sheet = workbook.GetSheet(ExcelText.SheetName_UDD); //sheet = workbook.GetSheetAt(2); rowIndex = 0; foreach (DefectTypeInfoReport defectTypeInfoReport in list_DefectTypeInfoReport) { CreateOneRowTwoColumnCells(sheet, ref rowIndex, defectTypeInfoReport.Index.ToString(), defectTypeInfoReport.DefectType); } #endregion using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); return(ms); } }
public float GetColumnWidthInPixels(int columnIndex) { return(_sheet.GetColumnWidthInPixels(columnIndex)); }