/// <summary> /// 在Excel格式的LOT卡表中插入单体图图片并设置页面属性 /// </summary> /// <param name="xlSheet">Excel格式的LOT卡表</param> /// <param name="imgName">图片全路径</param> /// <param name="strError">返回的错误</param> /// <returns></returns> internal static bool InsertDanTiTuImageToExcelSheet( Excel.Worksheet xlSheet, string imgName, out string strError ) { //默认没有错误 strError = string.Empty; try { //取得最大列数 int maxColNum = 15; //检测最大行 int maxRowNum = xlSheet.Range["A65536"].End[Excel.XlDirection.xlUp].Row; for (int i = 2; i < 16; i++) { int maxRowNum2 = ((Excel.Range)xlSheet.Cells[65536, i]).End[Excel.XlDirection.xlUp].Row; if (maxRowNum < maxRowNum2) { maxRowNum = maxRowNum2; } } //删除多余的行 ((Excel.Range)xlSheet.Rows[(maxRowNum + 1) + ":65536"]).Delete(Shift: Excel.XlDirection.xlUp); //删除多余的行 ((Excel.Range)xlSheet.Rows[(maxRowNum + 1) + ":65536"]).Delete(Shift: Excel.XlDirection.xlUp); //图片是否存在 if (!File.Exists(imgName)) { throw new Exception(string.Format("未找到生成的单体图图片{0}!", imgName)); } //插入图片 xlSheet.Range["A" + (maxRowNum + 1)].Select(); var pic = ((Excel.Pictures)((Excel._Worksheet)xlSheet).Pictures()).Insert(imgName); //取得列A-O的总宽度 double dTotalColWidth = 0d; for (int i = 1; i < maxColNum + 1; i++) { dTotalColWidth += Convert.ToDouble(((Excel.Range)xlSheet.Cells[1, i]).ColumnWidth); } //取得全部行的总高度 double dTotalRowHeight = 0d; for (int i = 1; i < maxRowNum + 1; i++) { dTotalRowHeight += Convert.ToDouble(((Excel.Range)xlSheet.Cells[i, 1]).RowHeight); } //宽度缩放比例 int iZoomWidth = Convert.ToInt32(Math.Floor(89d / dTotalColWidth * 100)); //高度缩放比例 int iZoomHeight = Convert.ToInt32(Math.Floor(790d / dTotalRowHeight * 100)); //取小的缩放比例 int iZoom = Math.Min(iZoomWidth, iZoomHeight); //检测是否高度未使用完则找到第一页最后一行 if (iZoom < iZoomHeight) { //待插入的行数 int iCountRow = Convert.ToInt32(Math.Floor(790d - dTotalRowHeight * iZoom / 100) / 10) + 1; //检测行数是否大于0 if (iCountRow > 0) { //需要下移的连续行字符串 string strRowNums = string.Format("{0}:{1}", maxRowNum + 1, maxRowNum + iCountRow - 1); //插入多行 ((Excel.Range)xlSheet.Rows[strRowNums]).Insert(Shift: Excel.XlDirection.xlDown); //每行的行高 double dRowHeight = 10d / (iZoom / 100d); //设置插入行行高 ((Excel.Range)xlSheet.Rows[strRowNums]).RowHeight = dRowHeight; //第一页行高 dTotalRowHeight += dRowHeight; //最大行号 maxRowNum += iCountRow; } } //原图片长宽 double dPicHeight = pic.Height; double dPicWidth = pic.Width; //按第一页总高度度得到新的图片长宽 double dPicHeightNew = dTotalRowHeight; double dPicWidthNew = dPicWidth / dPicHeight * dPicHeightNew; //宽度大于第一页宽度按照第一页总宽度得到图片长宽 if (dPicWidthNew > dTotalColWidth) { dPicWidthNew = dTotalColWidth; dPicHeightNew = dPicHeight / dPicWidth * dPicWidthNew; } //图片按比例设置新的长宽 pic.Height = dPicHeightNew * 6.3d; pic.Width = dPicWidthNew * 6.3d; //设置打印区域 xlSheet.PageSetup.PrintArea = "$A:$O"; //设置打印比例 xlSheet.PageSetup.Zoom = iZoom; //设置页边距 xlSheet.PageSetup.LeftMargin = xlSheet.Application.InchesToPoints(0.25d); xlSheet.PageSetup.RightMargin = xlSheet.Application.InchesToPoints(0.19d); xlSheet.PageSetup.TopMargin = xlSheet.Application.InchesToPoints(0.19d); xlSheet.PageSetup.BottomMargin = xlSheet.Application.InchesToPoints(0.19d); //总行数 maxRowNum = xlSheet.Range["A65536"].End[Excel.XlDirection.xlUp].Row; ; for (int i = 2; i < 16; i++) { int maxRowNum2 = ((Excel.Range)xlSheet.Cells[65536, i]).End[Excel.XlDirection.xlUp].Row; if (maxRowNum < maxRowNum2) { maxRowNum = maxRowNum2; } } xlSheet.Select(); xlSheet.PageSetup.PrintArea = "$A:$O"; xlSheet.PageSetup.FitToPagesWide = 1; xlSheet.PageSetup.FitToPagesTall = 100; xlSheet.ResetAllPageBreaks(); xlSheet.Application.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview; xlSheet.HPageBreaks[1].Location = xlSheet.Range["A" + (maxRowNum + 1)]; xlSheet.PageSetup.PrintTitleRows = ""; xlSheet.PageSetup.PrintTitleColumns = ""; //直接返回成功 return true; } catch (Exception ex) { strError = ex.Message; //直接返回失败 return false; } }
/// <summary> /// This method wil figure out the optimal paper size to use and sets it /// </summary> /// <param name="worksheet"></param> /// <param name="printArea"></param> private static void SetWorkSheetPaperSize(ExcelInterop._Worksheet worksheet, string printArea) { var pageSetup = worksheet.PageSetup; var pages = pageSetup.Pages; pageSetup.PrintArea = printArea; pageSetup.LeftHeader = worksheet.Name; var pageCount = pages.Count; if (pageCount == 1) return; try { pageSetup.Order = ExcelInterop.XlOrder.xlOverThenDown; foreach (var paperSize in PaperSizes) { var exitfor = false; pageSetup.PaperSize = paperSize.PaperSize; pageSetup.Orientation = paperSize.Orientation; worksheet.ResetAllPageBreaks(); foreach (var zoomRatio in ZoomRatios) { // Yes these page counts look lame, but so is Excel 2010 in not updating // the pages collection otherwise. We need to call the count methods to // make this code work pageSetup.Zoom = zoomRatio; // ReSharper disable once RedundantAssignment pageCount = pages.Count; if (CountVerticalPageBreaks(worksheet.VPageBreaks) == 0) { exitfor = true; break; } } if (exitfor) break; } } finally { Marshal.ReleaseComObject(pages); Marshal.ReleaseComObject(pageSetup); } }
private static void FormatSheetForSet1(Excel.Worksheet sheet, SetItem obj) { // formatting All sheet sheet.PageSetup.PrintGridlines = false; ; sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait; sheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; sheet.PageSetup.RightFooter = "Дата: &DD Стр &PP из &NN"; sheet.PageSetup.RightHeader = "Исследование " + obj.Project + ", сет № " + obj.Set + " - " + obj.TestMethod + " - " + obj.AB; sheet.PageSetup.Zoom = false; sheet.PageSetup.LeftHeader = "НИИ Антимикробной химиотерапии"; sheet.PageSetup.TopMargin = 50; sheet.PageSetup.BottomMargin = 50; sheet.PageSetup.HeaderMargin = 20; sheet.PageSetup.FooterMargin = 20; sheet.PageSetup.RightMargin = 10; sheet.PageSetup.LeftMargin = 50; sheet.PageSetup.Order = Excel.XlOrder.xlOverThenDown; //// Foramatting test method //sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, obj.MICList.Count + 5]].Merge(); //FormatHeaderText1(sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, 3]]); //// Formatting Set Number //sheet.Range[sheet.Cells[3, 1], sheet.Cells[3, 4]].Merge(); //FormatHeaderText1(sheet.Range[sheet.Cells[3, 1], sheet.Cells[3, 1]]); //// Formatting Set Number //sheet.Range[sheet.Cells[3, 5], sheet.Cells[3, obj.MICList.Count + 5]].Merge(); //FormatHeaderText1(sheet.Range[sheet.Cells[3, 5], sheet.Cells[3, obj.MICList.Count + 5]]); //Formatting table with MO FormatTableCells1(sheet.Range[sheet.Cells[1, 1], sheet.Cells[1 + obj.MOList.Count, obj.MICList.Count + 5]]); sheet.PageSetup.Zoom = false; sheet.PageSetup.FitToPagesWide = 1; // sheet.PageSetup.FitToPagesTall = 0; //Formatting Control MO Header sheet.Range[sheet.Cells[2 + obj.MOList.Count, 1], sheet.Cells[2 + obj.MOList.Count, obj.MICList.Count + 5]].Merge(); sheet.Range[sheet.Cells[2 + obj.MOList.Count, 1], sheet.Cells[2 + obj.MOList.Count, obj.MICList.Count + 5]].RowHeight = 15; FormatHeaderControlMOText1( sheet.Range[ sheet.Cells[2 + obj.MOList.Count, 1], sheet.Cells[2 + obj.MOList.Count, obj.MICList.Count + 5]]); // Formatting table with control MO FormatTableCells1(sheet.Range[sheet.Cells[1 + obj.MOList.Count + 1, 1], sheet.Cells[1 + obj.MOList.Count + 1 + obj.ControlMOList.Count, obj.MICList.Count + 5]]); FormatHeaderControlMOText1(sheet.Range[ sheet.Cells[3 + obj.MOList.Count, 2], sheet.Cells[1 + obj.MOList.Count + obj.ControlMOList.Count, 4]]); //Formatting Top Row sheet.Range[sheet.Cells[1, 1], sheet.Cells[5, obj.MICList.Count + 5]].ColumnWidth = 6; //Formatting Left Columns sheet.Range[sheet.Cells[1, 1], sheet.Cells[5 + obj.MOList.Count, 1]].ColumnWidth = 6; sheet.Range[sheet.Cells[1, 2], sheet.Cells[5 + obj.MOList.Count, 2]].ColumnWidth = 8; sheet.Range[sheet.Cells[1, 3], sheet.Cells[5 + obj.MOList.Count, 3]].ColumnWidth = 8; sheet.Range[sheet.Cells[1, 4], sheet.Cells[5 + obj.MOList.Count, 4]].ColumnWidth = 14; //Formatting Right Columns sheet.Range[sheet.Cells[1, obj.MICList.Count + 5], sheet.Cells[1 + obj.MOList.Count, obj.MICList.Count + 5]].ColumnWidth = 8; sheet.Cells[obj.MOList.Count + obj.ControlMOList.Count + 3, 2] = "Проверил:"; // Разбиваем на две части if (obj.MOList.Count > 48) { sheet.ResetAllPageBreaks(); // sheet.DisplayPageBreaks = true; sheet.HPageBreaks.Add(sheet.Cells[50, 1]); } }