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));
}
