/// <summary>
/// Explicitly release all com objects
/// </summary>
static void ReleaseAllObjects()
{
if (LogCalls)
{
DebugLog.Message("ReleaseAllObjects");
}
ReleaseObject(XlRange);
ReleaseObject(XlShapeRange);
ReleaseObject(XlShapes);
// ReleaseObject(XlSelection);
// ReleaseObject(XlTemp);
ReleaseObject(XlQueryTables);
ReleaseObject(XlPictures);
ReleaseObject(XlSheet);
ReleaseObject(XlBook);
ReleaseObject(XlSheet2);
ReleaseObject(XlBook2);
ReleaseObject(XlBooks);
XlRange = null;
XlShapeRange = null;
XlShapes = null;
// XlSelection = null;
// XlTemp = null;
XlQueryTables = null;
XlPictures = null;
XlSheet = null;
XlBook = null;
XlSheet2 = null;
XlBook2 = null;
XlBooks = null;
}
/// ﹤summary﹥ //C# 操作Excel常用组件及类
/// 将图片插入到指定的单元格位置。
/// 注意：图片必须是绝对物理路径
/// ﹤/summary﹥
/// ﹤param name="RangeName"﹥单元格名称，例如：B4﹤/param﹥
/// ﹤param name="PicturePath"﹥要插入图片的绝对路径。﹤/param﹥
public void InsertPicture(string RangeName, string PicturePath)
{
m_objRange = m_objSheet.get_Range(RangeName, m_objOpt);
m_objRange.Select();
Excel.Pictures pics = (Excel.Pictures)m_objSheet.Pictures(m_objOpt);
pics.Insert(PicturePath, m_objOpt);
}
//ЗАПИСЬ КАРТИНКИ В ЯЧЕЙКУ
public void AddPicture(string path, string cellAdress)
{
SelectCells(cellAdress, cellAdress);
_p = _xlWorkSheet.Pictures(_misValue) as Excel.Pictures;
_pic = _p.Insert(path, _misValue);
_pic.Left = Convert.ToDouble(_range.Left);
_pic.Top = Convert.ToDouble(_range.Top);
}
//ЗАПИСЬ КАРТИНКИ В ЯЧЕЙКУ
public void AddPicture(string path, string cellAdress)
{
SelectCells(cellAdress, cellAdress);
_p = _xlWorkSheet.Pictures(_misValue) as Excel.Pictures;
_pic = _p.Insert(path, _misValue);
_pic.Left = Convert.ToDouble(_range.Left);
_pic.Top = Convert.ToDouble(_range.Top);
}
public void Insert_Image(string Path, float Left, float Top, float Width, float Heigh)
{
Object MissingValue = System.Reflection.Missing.Value;
Excel.Pictures excelPictures = xlWorksheet3.Pictures(Type.Missing) as Excel.Pictures;
excelPictures.Insert(Path);
excelPictures.ShapeRange.IncrementLeft(200);
excelPictures.ShapeRange.IncrementTop(50);
}
/// <summary>
/// 插入图片
/// </summary>
/// <param name="sheet"> 工作表 </param>
/// <param name="imageFilePath"> 图片的绝对路径 </param>
/// <param name="rowNumber"> 单元格行号 </param>
/// <param name="columnNumber"> 单元格列号 </param>
/// <returns></returns>
public Xls.Picture AddImage(Xls.Worksheet sheet, string imageFilePath, int
rowNumber, int columnNumber)
{
Xls.Range range = this.GetRange(sheet, rowNumber, columnNumber,
rowNumber, columnNumber);
range.Select();
Xls.Pictures pics = sheet.Pictures(_missing) as Xls.Pictures;
Xls.Picture pic = pics.Insert(imageFilePath, _missing);
pic.Left = (double)range.Left;
pic.Top = (double)range.Top;
return(pic);
}
public void Insert_Image(string Path, float Left, float Top, float Width, float Heigh)
{
Object MissingValue = System.Reflection.Missing.Value;
Excel.Pictures excelPictures = xlWorksheet3.Pictures(Type.Missing) as Excel.Pictures;
// excelPictures.Insert(Path);
// xlApp3.ActiveCell.Offset.
xlWorksheet3.Shapes.AddPicture(Path, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, Width, Heigh);
}
public void InsertPicture(int iRowStart, int iColStart, string imgPath, double imgWidth, double imgHeight)
{
object misValue = System.Reflection.Missing.Value;
Excel.Range rangeObj = (Excel.Range)worksheet.Cells[iRowStart, iColStart];
Excel.Pictures imgObjCollection = worksheet.Pictures(misValue) as Excel.Pictures;
Excel.Picture imgObj = null;
imgObj = imgObjCollection.Insert(imgPath, misValue);
imgObj.Left = Convert.ToDouble(rangeObj.Left) + (Convert.ToDouble(rangeObj.Width) - imgWidth) / 2;
imgObj.Top = Convert.ToDouble(rangeObj.Top);
imgObj.Width = imgWidth;
imgObj.Height = imgHeight;
}
/// <summary>
/// Chn 插入图片
/// </summary>
/// <param name="workSheet"></param>
/// <param name="picturePath"></param>
/// <param name="picLeft"></param>
/// <param name="picTop"></param>
/// <returns></returns>
public static Excel.Picture InsertPictureByPicture(this Excel.Worksheet workSheet, string picturePath, float picLeft, float picTop)
{
Size picSize = ImageHelper.GetDimensions(picturePath);
int picWidth = picSize.Width;
int picHeight = picSize.Height;
Excel.Pictures pics = (Excel.Pictures)workSheet.Pictures(Type.Missing);
Excel.Picture pic = pics.Insert(picturePath, Type.Missing);
pic.Left = (double)picLeft;
pic.Top = (double)picTop;
pic.Width = (double)picWidth;
pic.Height = (double)picHeight;
return(pic);
}
/// <summary>
/// 将图片插入到指定的单元格位置。
/// 注意：图片必须是绝对物理路径 /// </summary>
/// <param name="RangeName">单元格名称，例如：B4</param>
/// <param name="PicturePath">要插入图片的绝对路径。</param>
public void InsertPicture(string RangeName, string PicturePath)
{
m_objRange = m_objSheet.get_Range(RangeName, m_objOpt);
m_objRange.Select();
Excel.Pictures pics = (Excel.Pictures)m_objSheet.Pictures(m_objOpt);
//pics.Insert(PicturePath, m_objOpt);
float fL=float.Parse(m_objRange.Left.ToString());
float fT = float.Parse(m_objRange.Top.ToString());
float fW = float.Parse(m_objRange.Width.ToString());
float fH = float.Parse(m_objRange.Height.ToString());
m_objSheet.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue,fL ,fT ,fW ,fH );
//Excel.Picture pic = (Excel.Picture)pics.Item(0);//建立图片集合某一图片对象
//pic.Left = (double)m_objRange.Left;
//pic.Top = (double)m_objRange.Top;
//pic.Height = (double)m_objRange.Height;
//pic.Width = (double)m_objRange.Width;
}
public static void WriteImage(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string personPath, float PictuteWidth, float PictureHeight)
{
if (_excelDoc != null)
{
try
{
MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex];
MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position);
_excelRge.Select();
if (PictuteWidth < 1 || PictureHeight < 1)
{
MSExcel.Pictures pics = (MSExcel.Pictures)_excelSht.Pictures(Missing.Value);
pics.Insert(personPath, Missing.Value);
}
else
{
float PicLeft = Convert.ToSingle(_excelRge.Left);
float PicTop = Convert.ToSingle(_excelRge.Top);
_excelSht.Shapes.AddPicture(personPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight);
}
return;
}
catch (Exception ex)
{
Log.LogHelper.AddLog(@"异常130", ex.Message, true);
Log.LogHelper.AddLog(@"异常131", " " + ex.TargetSite.ToString(), true);
return;
}
}
else
{
Log.LogHelper.AddLog(@"异常32", @"文件没有正常打开，无法读取数据", true);
return;
}
}
private void Extract()
{
Excel.Application application = new Excel.Application {
Visible = false
};
Excel.Workbook workbook = application.Workbooks.Open(textBoxFilePath.Text);
Excel.Worksheet worksheet = workbook.Sheets[1];
Excel.Pictures pics = worksheet.Pictures(Missing.Value) as Excel.Pictures;
if (pics != null)
{
progressBar.Maximum = pics.Count;
for (var i = 1; i <= pics.Count; i++)
{
progressBar.Value = i;
try
{
pics.Item(i).CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlBitmap);
var image = Clipboard.GetImage();
string imageName = worksheet.Cells[pics.Item(i).TopLeftCell.Row, 3].Formula;
image?.Save(Path.Combine(textBoxImagePath.Text, imageName + ".bmp"));
}
catch (Exception)
{
// ignored
}
}
}
workbook.Close();
application.Quit();
MessageBox.Show(@"Image Extraction completed.", Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
Process.Start(textBoxImagePath.Text);
}
public static List <Product> writeProducts(List <Product> products)
{
List <Image> images = new List <Image>();
Excel.Pictures pics = xlws.Pictures(Missing.Value) as Excel.Pictures;
for (int x = 1; x <= pics.Count; x++)
{
pics.Item(x).CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlBitmap);
}
foreach (Excel.Range row in xlws.Rows)
{
if (row.Cells[1] != null)
{
products.Add(new Product(
row.Cells[1].ToString(),
int.Parse(row.Cells[2].ToString()),
int.Parse(row.Cells[3].ToString()),
row.Cells[5].ToString(),
null,
row.Cells[7].ToString()
));
}
}
}
//程序单输出
public bool WritePgmList(string fileName, NCListData exceldata)
{
Microsoft.Office.Interop.Excel.Application xls = new Microsoft.Office.Interop.Excel.Application();
_Workbook book = null;
_Worksheet sheet = null;
try
{
book = xls.Workbooks.Open(fileName, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xls.Visible = false;
xls.DisplayAlerts = true;
sheet = (_Worksheet)book.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range cellRange = sheet.Cells;
NCDoc doc = exceldata.NcDoc;
VWConf vwConf = VWConf.Instance;
int timeTotle = 0;
//Jobs明细输出
List <NCJob> jobs = doc.Jobs;
for (int i = 0; i < jobs.Count; i++)
{
NCJob job = jobs[i];
//程序名
cellRange[i + 23, 1] = job.NC_Job;
//加工内容
cellRange[i + 23, 3] = vwConf.GetProgramDispString(job.NC_Job_Code);
//刀具-直径
cellRange[i + 23, 5] = "D" + job.Tool_D;
//刀具-半径
cellRange[i + 23, 6] = "R" + job.Tool_R;
//刀具-悬长
cellRange[i + 23, 7] = job.TotalToolLength;
//加工角度
cellRange[i + 23, 8] = job.MachineAxis;
//余量
cellRange[i + 23, 11] = job.Stockallow;
//料厚
cellRange[i + 23, 12] = job.Wallthick;
//步距
cellRange[i + 23, 13] = job.Stepover;
//F值
cellRange[i + 23, 14] = job.F;
//刀补
cellRange[i + 23, 15] = job.IsR ? "有" : "";
//理论用时
cellRange[i + 23, 16] = job.NCTime;
timeTotle += job.Time_Seconds;
}
//2-19行基本信息
cellRange[2, 2] = exceldata.ProjectName;
cellRange[2, 5] = exceldata.ModuleName;
cellRange[4, 2] = exceldata.PartName;
cellRange[4, 5] = exceldata.SingleName;
cellRange[6, 2] = exceldata.Procedure;
cellRange[6, 5] = exceldata.LeftRight;
cellRange[8, 2] = DateTime.Now.ToString("yyyy-MM-dd");
cellRange[8, 5] = exceldata.BenchMark;
cellRange[11, 2] = VWUtil.FormatHMS(timeTotle);
cellRange[11, 5] = exceldata.Side;
cellRange[16, 2] = doc.NC_Documentation;
cellRange[18, 2] = doc.PUTNC_Datei;
//图片
Microsoft.Office.Interop.Excel.Range picRange = null;
picRange = sheet.get_Range("R2", Missing.Value);
picRange.Select();
Microsoft.Office.Interop.Excel.Pictures pics = (Microsoft.Office.Interop.Excel.Pictures)sheet.Pictures(Missing.Value);
pics.Delete();
pics.Insert(exceldata.PicturePath, Missing.Value);
book.Save();
book.Close(false, Missing.Value, Missing.Value);
return(true);
}
catch (Exception ex)
{
return(false);
}
finally
{
xls.Quit();
xls = null;
sheet = null;
book = null;
GC.Collect();
}
}
//«јѕ»—№ ј–“»Ќ » ¬ я„≈… ”
public void WritePictureToCell(string path)
{
p = xlWorkSheet.Pictures(misValue) as Excel.Pictures;
pic = p.Insert(path, misValue);
pic.Left = Convert.ToDouble(range.Left);
pic.Top = Convert.ToDouble(range.Top);
}
public void PrintOrder(string path, System.Data.DataTable datatable, int LorR)
{
//Excel模板文件
string strFilePath = path;
if (!File.Exists(strFilePath))
{
throw new Exception("Excel条码模版不存在，无法导出");
}
//定义
Microsoft.Office.Interop.Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
throw new Exception("无法创建Excel对象，可能您的电脑未安装Excel");
}
xlApp.Visible = false;
xlApp.UserControl = true;
xlApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(strFilePath); //目标文件
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
//赋值
System.Data.DataTable dt = datatable;
if (dt.Rows.Count > 0)
{
if (Convert.ToInt32(dt.Rows[0]["LorR"]) == 0)
{
worksheet.Cells[1, 1] = "【左】"; //左座椅还是右
}
else
{
worksheet.Cells[1, 1] = "【右】";
}
string carType = dt.Rows[0]["CarType"].ToString();
worksheet.Cells[2, 2] = carType + "座椅分装单"; //第一行
worksheet.Cells[4, 2] = dt.Rows[0]["ProductNo"].ToString(); //第二行
BLL.T_JISA t_JISA = new T_JISA();
string JISASer = t_JISA.GetJISASer(dt.Rows[0]["ProductNo"].ToString());
worksheet.Cells[5, 2] = JISASer;
worksheet.Cells[6, 2] = dt.Rows[0]["CreateTime"].ToString(); //第二行
//worksheet.Cells[8,2] = dt.Rows[0]["ProductNo"].ToString(); //第二行
worksheet.Cells[11, 2] = dt.Rows[0]["CarModelName"].ToString(); //第二行
worksheet.Cells[13, 2] = dt.Rows[0]["Color"].ToString() + dt.Rows[0]["ColorCode"].ToString(); //第二行
int row = 15;
if (dt.Rows[0]["MasterBarCodeL"].ToString() != "")
{
worksheet.Cells[row++, 2] = "左前";
}
if (dt.Rows[0]["MasterBarCodeR"].ToString() != "")
{
worksheet.Cells[row++, 2] = "右前";
}
if (dt.Rows[0]["MasterBarCodeC"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后座椅整垫";
}
if (dt.Rows[0]["MasterBarCode40"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后背40%";
}
if (dt.Rows[0]["MasterBarCode60"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后背60%";
}
if (dt.Rows[0]["MasterBarCodeB"].ToString() != "")
{
worksheet.Cells[row++, 2] = "后整背";
}
DataMatrix.net.DmtxImageEncoder dataMatix = new DataMatrix.net.DmtxImageEncoder();
string QRCodepath = "";
if (LorR == 0)
{
Bitmap dataMatixCode = dataMatix.EncodeImage(0 + dt.Rows[0]["ProductNo"].ToString(), 15);
QRCodepath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Image\\" + dt.Rows[0]["ProductNo"].ToString();
dataMatixCode.Save(QRCodepath);
}
else if (LorR == 1)
{
Bitmap dataMatixCode = dataMatix.EncodeImage(1 + dt.Rows[0]["ProductNo"].ToString(), 15);
QRCodepath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Image\\" + dt.Rows[0]["ProductNo"].ToString();
dataMatixCode.Save(QRCodepath);
}
Microsoft.Office.Interop.Excel.Range m_objRange = worksheet.get_Range("B6", Type.Missing);
m_objRange.Select();
Excel.Pictures pics = (Excel.Pictures)worksheet.Pictures();
pics.Insert(QRCodepath, m_objRange);
Dictionary <int, string> DictPrinterName = new Dictionary <int, string>();
worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //纸张大小
//worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; 页面横向
worksheet.PageSetup.CenterHorizontally = true; //文字水平居中
xlApp.Visible = true;
System.Windows.Forms.Application.DoEvents();
//开始打印
worksheet.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//打印预览
//worksheet.PrintPreview();
//打印结束后清除Excel内存
workbooks.Close();
xlApp.Application.Quit();
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
GC.Collect();//强行销毁
}
}
//ЗАПИСЬ КАРТИНКИ В ЯЧЕЙКУ
public void WritePictureToCell(string path)
{
_p = _xlWorkSheet.Pictures(_misValue) as Excel.Pictures;
_pic = _p.Insert(path, _misValue);
_pic.Left = Convert.ToDouble(_range.Left);
_pic.Top = Convert.ToDouble(_range.Top);
}
