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