//Задать фигуру
public void SetShape(Shape shape)
{
Shape = shape;
if (shape.Type == MsoShapeType.msoGroup)
{
var parsing = new ShapeViewParsing();
Node = (ProgNode)parsing.Parse(Shape, Formula);
ErrMess = parsing.Keeper.ErrMess;
}
}
/// <summary>
/// 导出Excel设置
/// </summary>
/// <param name="source"></param>
public bool ExportExcel(ResShowReport source, string dirName, string excelFileName)
{
bool success = false;
//找到对应打印机才转换
if (!SetDefaultPrinterByConfig())
{
Console.WriteLine("打印机恢复默认设置失败");
return(false);
}
else
{
Console.WriteLine("打印机恢复默认设置成功");
}
Microsoft.Office.Interop.Excel.Application xlApp = null;
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
//单元格对象
Microsoft.Office.Interop.Excel.Range range = null;
//用于存储excel进程id
int processId = 0;
try
{
if (source == null || source.patrol_header == null || source.patrol_detail_list == null)
{
return(success);
}
System.Diagnostics.Process[] psf = null;
System.Diagnostics.Process[] psa = null;
//创建excel之前的列表
lock (this._locker)
{
psf = System.Diagnostics.Process.GetProcessesByName("excel");
xlApp = new Microsoft.Office.Interop.Excel.Application();
//创建excel之后的列表
psa = System.Diagnostics.Process.GetProcessesByName("excel");
}
//取得进程id
for (int i = 0; i < psa.Length; i++)
{
//从旧集合中找出不同的
bool isexist = false;
for (int j = 0; j < psf.Length; j++)
{
if (psf[j].Id == psa[i].Id)
{
isexist = true;
break;
}
}
//新创建的excel进程
if (!isexist)
{
processId = psa[i].Id;
break;
}
}
if (xlApp == null)
{
Console.WriteLine("Excel程序打开失败！");
return(success);
}
xlApp.DisplayAlerts = false;
xlApp.Visible = true;
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
workbooks = xlApp.Workbooks;
//Excel保存路径
string strdir = String.Empty;
string strurl = String.Empty;
string strpdf = String.Empty;
if (dirName != null && dirName != String.Empty)
{
strurl = CommonInfo.ExcelUrl + "\\" + excelFileName + CommonInfo.ExcelExtend;
strpdf = CommonInfo.ExcelUrl + "\\" + excelFileName + CommonInfo.PDFExtend;
strdir = CommonInfo.ExcelUrl + "\\" + dirName;
}
else
{
//未完成特巡报告书
return(success);
}
//复制模板
if (System.IO.File.Exists(CommonInfo.ExcelTemplateFile))
{
if (!System.IO.Directory.Exists(strdir))
{
System.IO.Directory.CreateDirectory(strdir);
}
//模板文件存在,那么复制模板文件到Excel导出文件夹
System.IO.File.Copy(CommonInfo.ExcelTemplateFile, strurl, true);
}
workbook = xlApp.Workbooks.Open(strurl);//workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
////必须在excel的visible为true时遍历进程 不然获取不到窗口标题名字标题,名字默认唯一
//System.Diagnostics.Process[] ps = System.Diagnostics.Process.GetProcessesByName("excel");
//string strMainWindowTitle = "Microsoft Excel - " + workbook.Name;
//foreach (System.Diagnostics.Process item in ps)
//{
// Console.WriteLine("进程Id：" + item.MainWindowTitle);
// if (strMainWindowTitle == item.MainWindowTitle)
// {
// Console.WriteLine("关闭进程：" + item.Id);
// processId = item.Id;
// }
//}
#region 配置信息
//计算可用宽度
float OffSetX = 20F;
float OffSetY = 10F;
float ImageMargin = 60;//左右图片之间的间隔
//开始单元格坐标和结束单元格坐标
range = worksheet.get_Range("StartCell");
double StartX = range.Left;
double StartY = range.Top;
int StartRowIndex = range.Row;
int StartColumnIndex = range.Column;
range = worksheet.get_Range("EndCell");
double EndX = range.Left;
double EndY = range.Top;
int EndColumnIndex = range.Column;
range = worksheet.get_Range("PrintBottom");//下一页的开始行
double BottomX = range.Left;
double BottomY = range.Top;
int EndRowIndex = range.Row;
//图片开始坐标
double ImageStartX = OffSetX;
double ImageStartY = StartY;
int ImageStartRow = range.Row;
range = worksheet.get_Range("Title"); //下一页的开始行
double HeadTitleHeight = range.Height; //标题行高度
double ContainerWidth = Convert.ToDouble((EndX - ImageMargin - OffSetX * 2).ToString("0.00")); //容器的可用总宽度
double ContainerHeight = Convert.ToDouble((BottomY - range.Top - OffSetY * 2 - HeadTitleHeight).ToString("0.00")); //容器的可用总高度
//计算图片的长和宽以及间隙
float ImageRowsPerPage = 3; //一页打印3行
float ImageColumnsPerPage = 2; //一行打印2列
float TitleHeight = 14; //说明文字高度
float RemarksHeight = 36; //备注文字高度
float ImageWidth = Convert.ToSingle(((ContainerWidth - ImageMargin) / 2).ToString("0.00")); //图片宽度
float ImageHeight = Convert.ToSingle((ContainerHeight / ImageRowsPerPage - TitleHeight * 2 - RemarksHeight - 15.0F).ToString("0.00")); //图片高度
//调整图片为正方形,取两者中小的那个作为基准,打印会重新设置图片的宽高所以需要按比例显示
if (ImageWidth - ImageHeight > 0.001)
{
ImageWidth = ImageHeight;
}
else
{
ImageHeight = ImageWidth;
}
//重新定位图片坐标 使得图片居中显示
OffSetX = Convert.ToSingle((EndX - ImageMargin) / 2 - ImageWidth);
double ImageLeftX = OffSetX; //左区域图片X坐标
double ImageLeftY = ImageStartY; //左区域图片Y坐标
double ImageRightX = Convert.ToDouble((EndX - OffSetX - ImageWidth).ToString("0.00")); //右区域图片X坐标
double ImageRightY = ImageLeftY; //右区域图片Y坐标
int RowsCount = EndRowIndex - StartRowIndex; //一页区域跨域的行数量
//动态控制坐标变量
float px = 0;
float py = 0;
List <Microsoft.Office.Interop.Excel.Range> listPages = new List <Microsoft.Office.Interop.Excel.Range>();
//所有的图片
List <Microsoft.Office.Interop.Excel.Shape> listShapes = new List <Microsoft.Office.Interop.Excel.Shape>();
////所有的说明文本框
//List<Microsoft.Office.Interop.Excel.Shape> listTitleShapes = new List<Microsoft.Office.Interop.Excel.Shape>();
////所有的备注信息
//List<Microsoft.Office.Interop.Excel.Shape> listRemarkShapes = new List<Microsoft.Office.Interop.Excel.Shape>();
#endregion
#region 第一页头部数据
PatrolHeaderInfo header = source.patrol_header;
//编号
range = worksheet.get_Range(RangeNames.ReportNo.ToString());
range.FormulaR1C1 = header.patrol_no;
//发行方
range = worksheet.get_Range(RangeNames.CompanyName.ToString());
range.FormulaR1C1 = header.company_name;
//发行日
range = worksheet.get_Range(RangeNames.PublishDate.ToString());
range.FormulaR1C1 = header.publish_date;
//二维码图片位置
range = worksheet.get_Range(RangeNames.Two.ToString());
range.FormulaR1C1 = String.Empty;
//添加图片
worksheet.Shapes.AddPicture(CommonInfo.TwoFile, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, range.Left, range.Top, 64F, 64F);//Convert.ToSingle(range.Width * 2 + 5), Convert.ToSingle(range.Height * 3 + 5));
//客户
range = worksheet.get_Range(RangeNames.Customer.ToString());
range.FormulaR1C1 = "尊敬的 " + header.contactor_name + " 先生/女士/公司：";
range.get_Characters(7, header.contactor_name.Length).Font.Size = 9;
//检查概要
range = worksheet.get_Range(RangeNames.Remarks.ToString());
//range.ShrinkToFit = true;//缩小字体
range.FormulaR1C1 = header.remarks;
//机型代码
range = worksheet.get_Range(RangeNames.MachineType.ToString());
range.FormulaR1C1 = header.machine_type + ":" + header.machine_name;
//机号
range = worksheet.get_Range(RangeNames.MachineNo.ToString());
range.FormulaR1C1 = header.machine_no;
//当月运转小时(HR)
range = worksheet.get_Range(RangeNames.WorkedTimes.ToString());
range.FormulaR1C1 = header.worked_times;
//施工地点
range = worksheet.get_Range(RangeNames.WorkAddress.ToString());
range.FormulaR1C1 = header.province + "" + header.city + "" + header.address;
//作业人
range = worksheet.get_Range(RangeNames.WorkMan.ToString());
range.FormulaR1C1 = header.reporter_name;
//作业日期
range = worksheet.get_Range(RangeNames.WorkDate.ToString());
range.FormulaR1C1 = header.report_date.Substring(0, 4) + "/" + header.report_date.Substring(4, 2) + "/" + header.report_date.Substring(6, 2);
//机主
range = worksheet.get_Range(RangeNames.Owner.ToString());
range.FormulaR1C1 = header.contactor_name;
//机主电话
range = worksheet.get_Range(RangeNames.OwnerTel.ToString());
range.FormulaR1C1 = header.contactor_phone;
//现场联系人(管理员、顾客、操作手、其他)
range = worksheet.get_Range(RangeNames.OperatorTypeName.ToString());
range.FormulaR1C1 = "现场联系人(" + header.operator_type_name + ")";
//现场联系人名称
range = worksheet.get_Range(RangeNames.OperatorName.ToString());
range.FormulaR1C1 = header.operator_name;
//现场联系人(管理员、顾客、操作手、其他)电话
range = worksheet.get_Range(RangeNames.OperatorTypeTel.ToString());
range.FormulaR1C1 = "(" + header.operator_type_name + ")电话";
//电话内容
range = worksheet.get_Range(RangeNames.OperatorTel.ToString());
range.FormulaR1C1 = header.operator_phone;
#endregion
#region 外观和铭牌信息
//初始化
px = 0;
py = 0;
//三张图片的间隔 (20171211修改只显示整机外观一张图片)
float startPosition = Convert.ToSingle((EndX - ImageWidth) / 3); //30f; //左图起始位置
float imageMargin = 10F; //Convert.ToSingle((EndX - ImageWidth * 3 - startPosition * 2) / 2);
//单独控制长宽
float decodeWidth = Convert.ToSingle(ImageWidth + startPosition);
float decodeHeight = Convert.ToSingle(ImageHeight + 50F);
//外观图片单独处理
List <Microsoft.Office.Interop.Excel.Shape> listDecode = new List <Microsoft.Office.Interop.Excel.Shape>();
//电话内容
//左边外观、右边铭牌
foreach (PatrolDetailInfo item in source.facade_list)
{
if (item.location_code == "SP0001")
{
//外观
//左边区域图片
range = worksheet.get_Range(RangeNames.Decode.ToString());
px = startPosition;
py = Convert.ToSingle(range.Top + 2.0F);
}
else if (item.location_code == "SP0002")
{
//铭牌
//中间区域图片
range = worksheet.get_Range(RangeNames.NamePlate.ToString());
px = Convert.ToSingle(startPosition + ImageWidth + imageMargin);
py = Convert.ToSingle(range.Top + 2.0F);
}
else
{
//铭牌
//右边区域图片
range = worksheet.get_Range(RangeNames.NamePlate.ToString());
px = Convert.ToSingle(startPosition + (ImageWidth + imageMargin) * 2);
py = Convert.ToSingle(range.Top + 2.0F);
}
//添加图片
Microsoft.Office.Interop.Excel.Shape shape = worksheet.Shapes.AddPicture(CommonInfo.ImageSaveUrl + "\\" + item.pic_url, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, px, py, decodeWidth, decodeHeight);
//图片固定大小和位置 不随单元格变化而变化
shape.Placement = Microsoft.Office.Interop.Excel.XlPlacement.xlFreeFloating;
//shape.IncrementLeft(0.75F);
//shape.ScaleWidth(0.9999F, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
shape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
shape.Line.ForeColor.ObjectThemeColor = Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorText1;
shape.Line.ForeColor.TintAndShade = 0;
shape.Line.Transparency = 0;
//改成一张图片后大小不一样不加入调整控制
////加入到控制集合中用于设置视图变化导致长宽不一致问题
//listShapes.Add(shape);
listDecode.Add(shape);
////补齐数组
//listShapes.Add(shape);
//listShapes.Add(shape);
#region 整机外观不显示此信息
////点检部位和具体位置
//shape = worksheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, px, py + ImageHeight, ImageWidth, TitleHeight);
////图片固定大小和位置 不随单元格变化而变化
//shape.Placement = Microsoft.Office.Interop.Excel.XlPlacement.xlFreeFloating;
////文字居中显示
//shape.TextFrame2.TextRange.ParagraphFormat.Alignment = Microsoft.Office.Core.MsoParagraphAlignment.msoAlignCenter;
//shape.TextFrame2.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
//shape.TextFrame2.TextRange.Font.Size = 10;
//shape.TextFrame2.VerticalAnchor = Microsoft.Office.Core.MsoVerticalAnchor.msoAnchorMiddle;
//if (item.question_level == PatrolEntity.QuestionLevel_Emergency)
//{
// //点检状态为《需修理/更换》或者问题程度为紧急字体变红色
// shape.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = 255;
//}
//shape.TextFrame2.TextRange.Characters.Text = item.spot_code_name;
//shape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
//shape.Line.ForeColor.ObjectThemeColor = Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorText1;
//shape.Line.ForeColor.TintAndShade = 0;
//shape.Line.Transparency = 0;
////加入到控制集合中用于设置视图变化导致长宽不一致问题
//listShapes.Add(shape);
////问题程度和状态
//shape = worksheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, px, py + ImageHeight + TitleHeight, ImageWidth, TitleHeight);
////图片固定大小和位置 不随单元格变化而变化
//shape.Placement = Microsoft.Office.Interop.Excel.XlPlacement.xlFreeFloating;
////文字居中显示
//shape.TextFrame2.TextRange.ParagraphFormat.Alignment = Microsoft.Office.Core.MsoParagraphAlignment.msoAlignCenter;
//shape.TextFrame2.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
//shape.TextFrame2.TextRange.Font.Size = 10;
//shape.TextFrame2.VerticalAnchor = Microsoft.Office.Core.MsoVerticalAnchor.msoAnchorMiddle;
//if (item.question_level == PatrolEntity.QuestionLevel_Emergency)
//{
// //点检状态为《需修理/更换》或者问题程度为紧急字体变红色
// shape.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = 255;
//}
//shape.TextFrame2.TextRange.Characters.Text = item.question_level_name + ":" + item.status_name;
//shape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
//shape.Line.ForeColor.ObjectThemeColor = Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorText1;
//shape.Line.ForeColor.TintAndShade = 0;
//shape.Line.Transparency = 0;
////加入到控制集合中用于设置视图变化导致长宽不一致问题
//listShapes.Add(shape);
#endregion
//添加备注文字
shape = worksheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, px, py + decodeHeight, decodeWidth, RemarksHeight);
//图片固定大小和位置 不随单元格变化而变化
shape.Placement = Microsoft.Office.Interop.Excel.XlPlacement.xlFreeFloating;
//文字居左显示
shape.TextFrame2.TextRange.ParagraphFormat.Alignment = Microsoft.Office.Core.MsoParagraphAlignment.msoAlignLeft;
shape.TextFrame2.TextRange.ParagraphFormat.BaselineAlignment = Microsoft.Office.Core.MsoBaselineAlignment.msoBaselineAlignTop;
shape.TextFrame2.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
shape.TextFrame2.TextRange.Font.Size = 8;
//shape.TextFrame2.MarginLeft = 0;
//shape.TextFrame2.MarginRight = 0;
//shape.TextFrame2.MarginBottom = 0;
//shape.TextFrame2.MarginTop = 0;
shape.TextFrame2.VerticalAnchor = Microsoft.Office.Core.MsoVerticalAnchor.msoAnchorTop;
shape.TextFrame2.TextRange.Characters.Text = item.remarks;
shape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
shape.Line.ForeColor.ObjectThemeColor = Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorText1;
shape.Line.ForeColor.TintAndShade = 0;
shape.Line.Transparency = 0;
//加入到控制集合中用于设置视图变化导致长宽不一致问题
listDecode.Add(shape);
}
//设置第一个分页符位置
range = worksheet.get_Range("StartCell");
listPages.Add(range);
worksheet.HPageBreaks.Add(range);
#endregion
#region 点检部位信息
List <PatrolDetailInfo> detailList = source.patrol_detail_list;
int pageIndex = 1;
//遍历所有明细
//for (int i = 0; i < detailList.Count; i++)
for (int i = 0; i < detailList.Count; i++)
{
//每页标题部分
//range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[StartRowIndex + 1, StartColumnIndex];
range = worksheet.Range[worksheet.Cells[StartRowIndex + 3, StartColumnIndex], worksheet.Cells[StartRowIndex + 3, StartColumnIndex + 4]];
range.MergeCells = true;
//range.Interior.Color = 65535;//背景颜色黄色
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
//range.FormulaR1C1 = "检查" + detailList[i].location_code_name + "明细";
range.FormulaR1C1 = "<各部位点检情况>";
range.Font.Color = -13434727;
range.Font.Bold = true;
range.RowHeight = HeadTitleHeight;
//每页图片左边开始区域坐标
range = worksheet.Range[worksheet.Cells[StartRowIndex, StartColumnIndex], worksheet.Cells[StartRowIndex + 1, EndColumnIndex - 1]];
float logoY = Convert.ToSingle(range.Top + 5.0F);
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[StartRowIndex + 3, StartColumnIndex];
float logoHeight = Convert.ToSingle(range.Top - logoY - 3.0F);
//添加logo图片
worksheet.Shapes.AddPicture(CommonInfo.ExcelLogoFile, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, logoY, Convert.ToSingle(EndX - 1.0F), logoHeight);
ImageLeftX = ImageLeftX;
ImageLeftY = range.Top;
//右边区域只要同步更新Y坐标即可
ImageRightY = range.Top;
//每页输出6张图片
for (int j = 0; j < ImageColumnsPerPage * ImageRowsPerPage; j++)
{
if (j % 2 == 0)
{
//左边区域图片
px = Convert.ToSingle(OffSetX.ToString("0.00"));
py = Convert.ToSingle((ImageLeftY + 8.0F + HeadTitleHeight).ToString("0.00"));
//Y坐标定位下一个左边区域
ImageLeftY += Convert.ToDouble((ImageHeight + TitleHeight * 2 + RemarksHeight + 10F).ToString("0.00"));
}
else
{
//右边区域图片
px = Convert.ToSingle(ImageRightX.ToString("0.00"));
py = Convert.ToSingle((ImageRightY + 8.0F + HeadTitleHeight).ToString("0.00"));
//Y坐标定位下一个左边区域
ImageRightY += Convert.ToDouble((ImageHeight + TitleHeight * 2 + RemarksHeight + 10F).ToString("0.00"));
}
//添加图片
Microsoft.Office.Interop.Excel.Shape shape = worksheet.Shapes.AddPicture(CommonInfo.ImageSaveUrl + "\\" + detailList[i].pic_url, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Convert.ToSingle((px).ToString("0.00")), py, ImageWidth, ImageHeight);
//shape.IncrementLeft(0.75F);
//shape.ScaleWidth(1.0F, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
//shape.ScaleHeight(1.0F, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
//图片固定大小和位置 不随单元格变化而变化
shape.Placement = Microsoft.Office.Interop.Excel.XlPlacement.xlFreeFloating;
shape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
shape.Line.ForeColor.ObjectThemeColor = Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorText1;
shape.Line.ForeColor.TintAndShade = 0;
shape.Line.Transparency = 0;
shape.Line.Style = Microsoft.Office.Core.MsoLineStyle.msoLineSingle;
//加入到控制集合中用于设置视图变化导致长宽不一致问题
listShapes.Add(shape);
//添加点检部位和具体位置
shape = worksheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, px, Convert.ToSingle((py + ImageHeight).ToString("0.00")), ImageWidth, TitleHeight + TitleHeight);
//图片固定大小和位置 不随单元格变化而变化
shape.Placement = Microsoft.Office.Interop.Excel.XlPlacement.xlFreeFloating;
//文字居中显示
shape.Width = ImageWidth;
shape.TextFrame2.TextRange.ParagraphFormat.Alignment = Microsoft.Office.Core.MsoParagraphAlignment.msoAlignLeft;
shape.TextFrame2.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
shape.TextFrame2.TextRange.Font.Size = 8;
if (detailList[i].question_level == PatrolEntity.QuestionLevel_Emergency)
{
//点检状态为《需修理/更换》或者问题程度为紧急字体变红色
shape.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = 255;
}
shape.TextFrame2.VerticalAnchor = Microsoft.Office.Core.MsoVerticalAnchor.msoAnchorMiddle;
shape.TextFrame2.TextRange.Characters.Text = detailList[i].location_code_name + "-" + detailList[i].spot_code_name + Environment.NewLine + detailList[i].status_name + ":" + detailList[i].question_level_name;
shape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
shape.Line.ForeColor.ObjectThemeColor = Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorText1;
shape.Line.ForeColor.TintAndShade = 0;
shape.Line.Transparency = 0;
//加入到控制集合中用于设置视图变化导致长宽不一致问题
listShapes.Add(shape);
////添加问题程度和状态
//shape = worksheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, px, Convert.ToSingle((py + ImageHeight + TitleHeight).ToString("0.00")), ImageWidth, TitleHeight);
////图片固定大小和位置 不随单元格变化而变化
//shape.Placement = Microsoft.Office.Interop.Excel.XlPlacement.xlFreeFloating;
////文字居中显示
//shape.Width = ImageWidth;
//shape.TextFrame2.TextRange.ParagraphFormat.Alignment = Microsoft.Office.Core.MsoParagraphAlignment.msoAlignLeft;
//shape.TextFrame2.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
//shape.TextFrame2.TextRange.Font.Size = 8;
//if (detailList[i].question_level == PatrolEntity.QuestionLevel_Emergency)
//{
// //点检状态为《需修理/更换》或者问题程度为紧急字体变红色
// shape.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = 255;
//}
//shape.TextFrame2.VerticalAnchor = Microsoft.Office.Core.MsoVerticalAnchor.msoAnchorMiddle;
//shape.TextFrame2.TextRange.Characters.Text = detailList[i].status_name +":" + detailList[i].question_level_name;
//shape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
//shape.Line.ForeColor.ObjectThemeColor = Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorText1;
//shape.Line.ForeColor.TintAndShade = 0;
//shape.Line.Transparency = 0;
////加入到控制集合中用于设置视图变化导致长宽不一致问题
//listShapes.Add(shape);
//添加备注文字
shape = worksheet.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, px, Convert.ToSingle((py + ImageHeight + TitleHeight + TitleHeight).ToString("0.00")), ImageWidth, RemarksHeight);
//图片固定大小和位置 不随单元格变化而变化
shape.Placement = Microsoft.Office.Interop.Excel.XlPlacement.xlFreeFloating;
//文字居左显示
shape.Width = ImageWidth;
shape.TextFrame2.TextRange.ParagraphFormat.Alignment = Microsoft.Office.Core.MsoParagraphAlignment.msoAlignLeft;
//shape.TextFrame2.TextRange.ParagraphFormat.BaselineAlignment = Microsoft.Office.Core.MsoBaselineAlignment.msoBaselineAlignTop;
shape.TextFrame2.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
shape.TextFrame2.TextRange.Font.Size = 8;
//shape.TextFrame2.MarginLeft = 0;
//shape.TextFrame2.MarginRight = 0;
shape.TextFrame2.MarginBottom = 0;
shape.TextFrame2.MarginTop = 0;
shape.TextFrame2.VerticalAnchor = Microsoft.Office.Core.MsoVerticalAnchor.msoAnchorTop;
shape.TextFrame2.TextRange.Characters.Text = detailList[i].remarks;
shape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
shape.Line.ForeColor.ObjectThemeColor = Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorText1;
shape.Line.ForeColor.TintAndShade = 0;
shape.Line.Transparency = 0;
//加入到控制集合中用于设置视图变化导致长宽不一致问题
listShapes.Add(shape);
//索引同步更新
if (i >= detailList.Count - 1 || j == ImageColumnsPerPage * ImageRowsPerPage - 1)
{
//到记录最后一条,退出 i值减一
break;
}
else
{
i++;
}
}
//重新定位到下一页行位置
//获取开始行所在位置 总行数包含当前行所以要减一
StartRowIndex += RowsCount;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[StartRowIndex, StartColumnIndex];
//设置分页符从第二个开始设置 第一个不用设置
worksheet.HPageBreaks.Add(range);
listPages.Add(range);
//自增1
pageIndex++;
}
#endregion
//打印设置 设置页眉显示图片 已放弃
//worksheet.PageSetup.LeftHeaderPicture.Filename = @"C:\Users\liuchengyun.DOMAIN\Desktop\header.bmp";
//worksheet.PageSetup.CenterHeaderPicture.Filename = @"C:\Users\liuchengyun.DOMAIN\Desktop\header.bmp";
//worksheet.PageSetup.RightHeaderPicture.Filename = @"C:\Users\liuchengyun.DOMAIN\Desktop\header.bmp";
//worksheet.PageSetup.LeftHeader = "&G";
//worksheet.PageSetup.CenterHeader = "&G";
//worksheet.PageSetup.RightHeader = "&G";
////设置页脚
worksheet.PageSetup.LeftFooter = "";
worksheet.PageSetup.CenterFooter = "&\"Arial,加粗\"&14©" + header.company_name + "版权所有";
worksheet.PageSetup.RightFooter = "第 &P 页，共 &N 页";
//worksheet.PageSetup.LeftMargin = xlApp.Application.InchesToPoints(0);
//worksheet.PageSetup.RightMargin = xlApp.Application.InchesToPoints(0);
//worksheet.PageSetup.TopMargin = xlApp.Application.InchesToPoints(0);
//worksheet.PageSetup.BottomMargin = xlApp.Application.InchesToPoints(0);
//worksheet.PageSetup.HeaderMargin = xlApp.Application.InchesToPoints(0);
//worksheet.PageSetup.FooterMargin = xlApp.Application.InchesToPoints(0);
worksheet.PageSetup.PrintArea = "";//"$A$1:$U$" + StartRowIndex;//重设打印区域
//worksheet.PageSetup.PrintQuality = 600;
//worksheet.PageSetup.CenterHorizontally = true;
//worksheet.PageSetup.CenterVertically = true;
//worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
//double pp = xlApp.Application.CentimetersToPoints(21);
//如果没有点检明细那么不要设置只有一页
//动态设置分页符
try
{
if (source.patrol_detail_list.Count > 0)
{
xlApp.ActiveWindow.View = Microsoft.Office.Interop.Excel.XlWindowView.xlPageBreakPreview;
//分页符设置
for (int i = 1; i <= listPages.Count; i++)
{
if (i <= worksheet.HPageBreaks.Count && listPages.Count >= worksheet.HPageBreaks.Count)
{
worksheet.HPageBreaks.Item[i].Location = listPages[i - 1];
}
}
}
xlApp.ActiveWindow.View = Microsoft.Office.Interop.Excel.XlWindowView.xlPageLayoutView;
for (int j = 0; j < listDecode.Count; j++)
{
//外观图片备注宽度控制
listShapes[j].Width = decodeWidth;
}
//图片长宽设置
for (int i = 0; i < listShapes.Count; i = i + 3)
{
//图片保证正方形
listShapes[i].Width = ImageWidth;
listShapes[i].Height = ImageWidth;
//文本框保持宽度和图片一致高度不动
listShapes[i + 1].Width = ImageWidth;
//listShapes[i+1].Height = ImageWidth;
listShapes[i + 2].Width = ImageWidth;
//listShapes[i+2].Height = ImageWidth;
}
}
catch (Exception e)
{
Console.WriteLine("分页错误：" + e.Message);
MyLog.Loger.Error("设置分页符错误:" + e.Message);
}
//保存文件
xlApp.ActiveWindow.View = Microsoft.Office.Interop.Excel.XlWindowView.xlPageBreakPreview;
//xlApp.Visible = true;
workbook.Save();
workbook.Saved = true;
//所有处理都完成
//转换成pdf文件保存
worksheet.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
strpdf,
Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard);
//worksheet.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
// strpdf,
// Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard,
// true,
// true,
// Type.Missing,
// Type.Missing,
// false,
// Type.Missing
// );
success = true;
}
catch (Exception ex)
{
Console.WriteLine("导出错误：" + ex.Message);
MyLog.Loger.Error("导出Excel错误:" + ex.Message);
}
finally
{
try
{
//if (range != null)
//{
// System.Runtime.InteropServices.Marshal.FinalReleaseComObject(range);
// Console.WriteLine("Range关闭");
// range = null;
//}
//if (worksheet != null)
//{
// System.Runtime.InteropServices.Marshal.FinalReleaseComObject(worksheet);
// Console.WriteLine("Sheet关闭");
// worksheet = null;
//}
//if (workbook != null)
//{
// workbook.Close(false);
// System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
// Console.WriteLine("workbook关闭");
// workbook = null;
//}
//if (workbooks != null)
//{
// System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbooks);
// Console.WriteLine("workbooks关闭");
// workbooks = null;
//}
}
catch (Exception exc)
{
Console.WriteLine("Excel关闭错误：" + exc.Message);
MyLog.Loger.Error("Excel关闭错误:" + exc.Message);
success = false;
}
finally {
if (xlApp != null)
{
//关闭Excel进程
KillSpecialExcel(xlApp, processId);
xlApp = null;
Console.WriteLine("Excel结束所有");
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
return(success);
}
static int Main(string[] args)
{
if (args.Length < 2)
{
return(0);
}
// EXCELファイルパス
string filePath = args[0];
// EXCELファイルフルパス
string fullPath = System.IO.Path.GetFullPath(filePath);
// ファイルが無い場合終了
if (!System.IO.File.Exists(fullPath))
{
return(0);
}
// シート名
string strWorksheetName = "";
// フォント名
string fontName = args[1];
//末端の改行文字を取り除く
fontName.Trim();
Microsoft.Office.Interop.Excel.Application xlApp = null;
Microsoft.Office.Interop.Excel.Workbooks xlBooks = null;
Microsoft.Office.Interop.Excel.Workbook xlBook = null;
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.Worksheet xlSheet = null;
Microsoft.Office.Interop.Excel.Range xlRange = null;
Microsoft.Office.Interop.Excel.Font xlFont = null;
Microsoft.Office.Interop.Excel.Shapes xlShapes = null;
Microsoft.Office.Interop.Excel.Shape xlShape = null;
Microsoft.Office.Interop.Excel.TextFrame2 xlTextFrame2 = null;
Microsoft.Office.Core.TextRange2 xlTextRange2 = null;
Microsoft.Office.Core.Font2 xlFont2 = null;
try
{
// EXCEL起動
xlApp = new Microsoft.Office.Interop.Excel.Application();
// EXCELは非表示にする
xlApp.Visible = false;
// 保存確認なしにする
xlApp.DisplayAlerts = false;
// Workbook開く
xlBooks = xlApp.Workbooks;
xlBook = xlBooks.Open(fullPath);
// Worksheet開く
xlSheets = xlBook.Sheets;
for (int ii = 1; ii <= xlSheets.Count; ++ii)
{
xlSheet = xlSheets.Item[ii];
strWorksheetName = xlSheet.Name;
// セルのフォントを変更する
xlRange = xlSheet.UsedRange;
xlFont = xlRange.Font;
xlFont.Name = fontName;
if (xlFont != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlFont);
xlFont = null;
}
if (xlRange != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange);
xlRange = null;
}
// 図形のフォントを変更する
xlShapes = xlSheet.Shapes;
for (int jj = 1; jj <= xlShapes.Count; ++jj)
{
xlShape = xlShapes.Item(jj);
xlTextFrame2 = xlShape.TextFrame2;
if (xlTextFrame2.HasText == Microsoft.Office.Core.MsoTriState.msoTrue)
{
xlTextRange2 = xlTextFrame2.TextRange;
xlFont2 = xlTextRange2.Font;
// 右から左へ記述するフォントの設定(例:アラビア語)
xlFont2.NameComplexScript = fontName;
// 全角フォントの設定(例:日本語)
xlFont2.NameFarEast = fontName;
// 半角フォントの設定(例:英語)
xlFont2.Name = fontName;
xlRange = xlShape.TopLeftCell;
System.Console.WriteLine(xlRange.Address + " [" + xlShape.Name + "]" + "のフォントを変更しました。");
if (xlRange != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange);
xlRange = null;
}
if (xlFont2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlFont2);
xlFont2 = null;
}
if (xlTextRange2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlTextRange2);
xlTextRange2 = null;
}
}
if (xlTextFrame2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlTextFrame2);
xlTextFrame2 = null;
}
if (xlShape != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlShape);
xlShape = null;
}
}
if (xlShapes != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlShapes);
xlShapes = null;
}
if (xlSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
xlSheet = null;
}
System.Console.WriteLine("処理が終了しました。シート名:" + strWorksheetName);
}
if (xlSheets != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets);
xlSheets = null;
}
// Workbook保存
xlBook.Save();
System.Console.WriteLine("処理が終了しました。ファイル名:" + xlBook.Name);
}
catch
{
System.Console.WriteLine("ランタイムエラーが発生しました。シート名:" + strWorksheetName);
}
finally
{
if (xlFont2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlFont2);
xlFont2 = null;
}
if (xlTextRange2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlTextRange2);
xlTextRange2 = null;
}
if (xlTextFrame2 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlTextFrame2);
xlTextFrame2 = null;
}
if (xlShape != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlShape);
xlShape = null;
}
if (xlShapes != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlShapes);
xlShapes = null;
}
if (xlFont != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlFont);
xlFont = null;
}
if (xlRange != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange);
xlRange = null;
}
if (xlSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
xlSheet = null;
}
if (xlSheets != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets);
xlSheets = null;
}
if (xlBook != null)
{
xlBook.Close(false);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
xlBook = null;
}
if (xlBooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks);
xlBooks = null;
}
if (xlApp != null)
{
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
}
return(0);
}
public ReportShapeDebug(Shape shape, string formula)
{
Formula = formula;
SetShape(shape);
}
private void btnExport_Click(object sender, RoutedEventArgs e)
{
int idx = dbFile.LastIndexOf(@"\");
Microsoft.Win32.SaveFileDialog dlgSaveDiagram = new Microsoft.Win32.SaveFileDialog();
dlgSaveDiagram.Filter = "Excel xlsx |*.xlsx;";
dlgSaveDiagram.Title = "Export Report";
if (dlgSaveDiagram.ShowDialog() == true)
{
string filePath = dlgSaveDiagram.FileName;
string vsd = AppDomain.CurrentDomain.BaseDirectory.ToString() + "SimTech-PRV_DataSheet_Model.xlsx";
System.IO.File.Copy(vsd, filePath);
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, Type.Missing, false, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets.get_Item(1);
xlWorkSheet.Cells[5][15] = "test";
int count = 0;
int row1 = 14;
int note1 = 42;
int row2 = 74;
int note2 = 102;
int row3 = 134;
int note3 = 162;
for (int i = 1; i < mainTab.Items.Count; i++)
{
TabItem ti = (TabItem)mainTab.Items[i];
if (ti.Visibility == Visibility.Visible)
{
string num = ti.Name.Remove(0, 2);
UC_CaseStudy uc = (UC_CaseStudy)ti.FindName("uc" + num);
int col = count % 5;
if (count <= 4)
{
xlWorkSheet.Cells[5 + col * 2][row1] = ti.Header.ToString();
xlWorkSheet.Cells[5 + col * 2][row1 + 1] = 16;
if (ti.Header.ToString().Contains("Fire"))
{
xlWorkSheet.Cells[5 + col * 2][row1 + 1] = 21;
}
xlWorkSheet.Cells[5 + col * 2][row1 + 2] = uc.txtReliefPress.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 3] = uc.txtReliefTemp.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 4] = uc.txtReliefRate.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 5] = uc.txtReliefMW.Text;
xlWorkSheet.Cells[5 + col * 2][row1 + 6] = Compressibility;
xlWorkSheet.Cells[5 + col * 2][row1 + 7] = CpCv;
xlWorkSheet.Cells[3][note1 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note2 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note3 + count] = uc.txtDescription.Text;
}
else if (count >= 5 && count <= 9)
{
xlWorkSheet.Cells[5 + col * 2][row2] = ti.Header.ToString();
xlWorkSheet.Cells[5 + col * 2][row2 + 1] = 16;
if (ti.Header.ToString().Contains("Fire"))
{
xlWorkSheet.Cells[5 + col * 2][row2 + 1] = 21;
}
xlWorkSheet.Cells[5 + col * 2][row2 + 2] = uc.txtReliefPress.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 3] = uc.txtReliefTemp.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 4] = uc.txtReliefRate.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 5] = uc.txtReliefMW.Text;
xlWorkSheet.Cells[5 + col * 2][row2 + 6] = Compressibility;
xlWorkSheet.Cells[5 + col * 2][row2 + 7] = CpCv;
xlWorkSheet.Cells[3][note1 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note2 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note3 + count] = uc.txtDescription.Text;
}
else
{
xlWorkSheet.Cells[5 + col * 2][row3] = ti.Header.ToString();
xlWorkSheet.Cells[5 + col * 2][row3 + 1] = 16;
if (ti.Header.ToString().Contains("Fire"))
{
xlWorkSheet.Cells[5 + col * 2][row3 + 1] = 21;
}
xlWorkSheet.Cells[5 + col * 2][row3 + 2] = uc.txtReliefPress.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 3] = uc.txtReliefTemp.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 4] = uc.txtReliefRate.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 5] = uc.txtReliefMW.Text;
xlWorkSheet.Cells[5 + col * 2][row3 + 6] = Compressibility;
xlWorkSheet.Cells[5 + col * 2][row3 + 7] = CpCv;
xlWorkSheet.Cells[3][note1 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note2 + count] = uc.txtDescription.Text;
xlWorkSheet.Cells[3][note3 + count] = uc.txtDescription.Text;
}
count++;
}
}
if (count <= 5)
{
Microsoft.Office.Interop.Excel.Range r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[3][181]];
r.UnMerge();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[14][181]];
r.Clear();
Microsoft.Office.Interop.Excel.Shape pic = xlWorkSheet.Shapes.Item(3) as Microsoft.Office.Interop.Excel.Shape;
pic.Delete();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][62], xlWorkSheet.Cells[3][121]];
r.UnMerge();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][62], xlWorkSheet.Cells[14][121]];
r.Clear();
pic = xlWorkSheet.Shapes.Item(2) as Microsoft.Office.Interop.Excel.Shape;
pic.Delete();
}
else if (count <= 10)
{
Microsoft.Office.Interop.Excel.Range r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[3][181]];
r.UnMerge();
r = xlWorkSheet.Range[xlWorkSheet.Cells[2][122], xlWorkSheet.Cells[14][181]];
r.Clear();
Microsoft.Office.Interop.Excel.Shape pic = xlWorkSheet.Shapes.Item(3) as Microsoft.Office.Interop.Excel.Shape;
pic.Delete();
}
xlWorkBook.Save();
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}