//打印功能
public int print(bool isShow)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\LDPE\SOP-MFG-102-R02A 生产退料记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[wb.Worksheets.Count];
// 修改Sheet中某行某列的值
my = printValue(my, wb);
if (isShow)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
bool isPrint = true;
//false->打印
try
{
// 设置该进程是否可见
//oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet
// 直接用默认打印机打印该Sheet
my.PrintOut();
}
catch
{ isPrint = false; }
finally
{
if (isPrint)
{
//写日志
string log = "=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + "：" + mySystem.Parameter.userName + " 打印文档\n";
dt记录.Rows[0]["日志"] = dt记录.Rows[0]["日志"].ToString() + log;
bs记录.EndEdit();
da记录.Update((DataTable)bs记录.DataSource);
}
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
my = null;
}
return(pageCount);
}
}
public int print(bool b)
{
int label_打印成功 = 1;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
string dir = System.IO.Directory.GetCurrentDirectory();
dir += "./../../xls/库存/表7退货产品评审单.xlsx";
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(dir);
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 修改Sheet中某行某列的值
fill_excel(my);
my.PageSetup.RightFooter = "&P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码
if (b)
{
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = 0;
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch
{
label_打印成功 = 0;
}
finally
{
if (1 == label_打印成功)
{
bsOuter.EndEdit();
daOuter.Update((DataTable)bsOuter.DataSource);
}
// 关闭文件，false表示不保存
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
return(pageCount);
}
}
//打印功能
public int print(bool isShow)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\订单\表93. PALL AUSTAR采购订单.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 修改Sheet中某行某列的值
fill_excel(my, wb);
//"生产指令-步骤序号- 表序号 /&P"
my.PageSetup.RightFooter = "&P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码
if (isShow)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = 0;
bool isPrint = true;
//false->打印
try
{
// 设置该进程是否可见
//oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet
// 直接用默认打印机打印该Sheet
my.PrintOut();
}
catch
{ isPrint = false; }
finally
{
if (isPrint)
{
bsOuter.EndEdit();
daOuter.Update((DataTable)bsOuter.DataSource);
}
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
return(pageCount);
}
}
public int print(bool preview)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\Extrusion\B\SOP-MFG-301-R12 吹膜工序物料平衡记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[3];
// 设置该进程是否可见
//oXL.Visible = true;
// 修改Sheet中某行某列的值
my.Cells[3, 2].Value = dtOuter.Rows[0]["生产指令"]; //lbl生产指令.Text.ToString();
my.Cells[3, 4].Value = dtOuter.Rows[0]["生产日期"]; //lbl生产开始时间.Text.ToString();
my.Cells[6, 1].Value = dtOuter.Rows[0]["成品重量合计"]; //txb成品重量合计.Text;
my.Cells[6, 2].Value = dtOuter.Rows[0]["废品量合计"]; //txb废品量合计.Text;
my.Cells[6, 3].Value = dtOuter.Rows[0]["领料量"]; //txb领料量.Text;
my.Cells[6, 4].Value = dtOuter.Rows[0]["重量比成品率"]; //txb重量比成品率.Text;
my.Cells[6, 5].Value = dtOuter.Rows[0]["物料平衡"]; //txb物料平衡.Text;
my.Cells[7, 2].Value = dtOuter.Rows[0]["备注"];
my.Cells[8, 1].Value = "记录员/日期：" + dtOuter.Rows[0]["记录员"] + " " + Convert.ToDateTime(dtOuter.Rows[0]["记录日期"]).ToString("yyyy年MM月dd日");
my.Cells[8, 4].Value = dtOuter.Rows[0]["审核员"] + Convert.ToDateTime(dtOuter.Rows[0]["审核日期"]).ToString("yyyy年MM月dd日");
if (preview)
{
// 让这个Sheet为被选中状态
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
return(0);
}
else
{
//footprint
my.PageSetup.RightFooter = __生产指令 + "-12-001 &P/" + wb.ActiveSheet.PageSetup.Pages.Count;
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch { }
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
wb = null;
my = null;
return(pageCount);
}
}
public void print(bool isShow)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\PTV\13 SOP-MFG-305-R04A PTV生产台帐.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 修改Sheet中某行某列的值
my = printValue(my, wb);
if (isShow)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
}
else
{
bool isPrint = true;
//false->打印
try
{
// 设置该进程是否可见
//oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet
// 直接用默认打印机打印该Sheet
my.PrintOut();
}
catch
{ isPrint = false; }
finally
{
if (isPrint)
{
//写日志
//string log = "=====================================\n";
//log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + "：" + mySystem.Parameter.userName + " 打印文档\n";
//dtOuter.Rows[0]["日志"] = dtOuter.Rows[0]["日志"].ToString() + log;
//bsOuter.EndEdit();
//daOuter.Update((DataTable)bsOuter.DataSource);
}
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
}
}
public int print(bool b)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
string dir = System.IO.Directory.GetCurrentDirectory();
dir += "./../../xls/Extrusion/B/SOP-MFG-301-R02A 吹膜生产日报表.xlsx";
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(dir);
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 修改Sheet中某行某列的值
fill_excel(my);
my.PageSetup.RightFooter = dt_prodinstr.Rows[0]["生产指令"] + "-02-001 &P/" + wb.ActiveSheet.PageSetup.Pages.Count;
if (b)
{
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = 0;
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch
{ }
finally
{
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
return(pageCount);
}
}
//输入用户姓名是否合法
//private bool input_Judge()
//{
// //int index = dataGridView1.Rows.Count - 1;
// //string str登记员=dt台帐.Rows[index]["登记员"].ToString();
// //string str审核员 = dt台帐.Rows[index]["审核员"].ToString();
// //if (mySystem.Parameter.NametoID(str登记员) <= 0 || mySystem.Parameter.NametoID(str审核员)<=0)
// //{
// // return false;
// //}
// //else
// // return true;
//}
//打印功能
public int print(bool isShow)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\miejun\SOP-MFG-106-R03A 辐照灭菌台帐.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[2];
if (isShow)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 修改Sheet中某行某列的值
my = printValue(my, wb);
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
//false->打印
// 设置该进程是否可见
oXL.Visible = false;
// 修改Sheet中某行某列的值
my = printValue(my, wb);
try
{
// 直接用默认打印机打印该Sheet
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch
{ }
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
return(pageCount);
}
}
//打印功能
public void print(bool isShow)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\Extrusion\D\SOP-MFG-403-R02A 吹膜机组换模芯检查表.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[wb.Worksheets.Count];
if (isShow)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 修改Sheet中某行某列的值
my = printValue(my);
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
}
else
{
//false->打印
// 设置该进程是否可见
oXL.Visible = false;
// 修改Sheet中某行某列的值
my = printValue(my);
// 直接用默认打印机打印该Sheet
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
}
}
/// <summary>
/// 打印
/// </summary>
private void printInfo()
{
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
DirectoryInfo Dir = new DirectoryInfo(".");
m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Register.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
// 号牌种类
m_objRange = m_objSheet.get_Range("D11", oMissing);
m_objRange.Value = this.category.Text;
// 号牌号码
m_objRange = m_objSheet.get_Range("H11", oMissing);
m_objRange.Value = "辽B" + this.license.Text;
// 品牌型号
m_objRange = m_objSheet.get_Range("D12", oMissing);
m_objRange.Value = this.brand.Text;
//车辆识别代号
m_objRange = m_objSheet.get_Range("H12", oMissing);
m_objRange.Value = this.vin.Text;
// 机动车所有人姓名/名称
m_objRange = m_objSheet.get_Range("E3", oMissing);
m_objRange.Value = this.ownerName.Text;
// 机动车所有人邮寄地址
m_objRange = m_objSheet.get_Range("E4", oMissing);
m_objRange.Value = this.ownerAddress.Text;
// 机动车所有人邮政编码
m_objRange = m_objSheet.get_Range("I3", oMissing);
m_objRange.Value = this.ownerPostcode.Text;
// 机动车所有人固定电话
m_objRange = m_objSheet.get_Range("I5", oMissing);
m_objRange.Value = this.ownerPhone.Text;
// 机动车所有人移动电话
m_objRange = m_objSheet.get_Range("E5", oMissing);
m_objRange.Value = this.ownerMobile.Text;
// 省（自治县、直辖市)
m_objRange = m_objSheet.get_Range("D10", oMissing);
m_objRange.Value = " " + this.province.Text + " " + this.department.Text;
// 代理人姓名、名称
m_objRange = m_objSheet.get_Range("E6", oMissing);
m_objRange.Value = this.agentName.Text;
// 代理人联系电话
m_objRange = m_objSheet.get_Range("H6", oMissing);
m_objRange.Value = this.agentPhone.Text;
// 办理日期
m_objRange = m_objSheet.get_Range("F18", oMissing);
m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day;
m_objExcel.DisplayAlerts = false;
m_objBook.Save();
m_objSheet.PrintOut();
}
catch (Exception ex)
{
// 输出异常信息
MessageBox.Show("打印失败,异常信息为：" + ex.Message);
}
finally
{
if (m_objBook != null)
{
m_objBook.Close(oMissing, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
}
if (m_objExcel != null)
{
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
public int print(bool preview)
{
int pageCount = 0;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\BPVBag\SOP-MFG-306-R09A 原材料分装记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[2];
// 设置该进程是否可见
//oXL.Visible = true;
// 修改Sheet中某行某列的值
//EVERY SHEET CONTAINS 20 RECORDS
int rowStartAt = 5;
int rowNumPerSheet = 19;
int rowNumTotal = dt记录详情.Rows.Count;
for (int i = 0; i < (rowNumTotal > rowNumPerSheet ? rowNumPerSheet : rowNumTotal); i++)
{
my.Cells[i + rowStartAt, 1].Value = dt记录详情.Rows[i]["序号"];
my.Cells[i + rowStartAt, 2].Value = dt记录详情.Rows[i]["物料代码"];
my.Cells[i + rowStartAt, 3].Value = dt记录详情.Rows[i]["物料批号"];
my.Cells[i + rowStartAt, 4].Value = dt记录详情.Rows[i]["分装前规格"];
my.Cells[i + rowStartAt, 5].Value = dt记录详情.Rows[i]["分装前数量"];
my.Cells[i + rowStartAt, 6].Value = dt记录详情.Rows[i]["分装后规格"];
my.Cells[i + rowStartAt, 7].Value = dt记录详情.Rows[i]["分装后数量"];
my.Cells[i + rowStartAt, 8].Value = dt记录详情.Rows[i]["外观"];
my.Cells[i + rowStartAt, 9].Value = dt记录详情.Rows[i]["数量偏差"];
my.Cells[i + rowStartAt, 10].Value = dt记录详情.Rows[i]["判定"];
my.Cells[i + rowStartAt, 11].Value = dt记录详情.Rows[i]["备注"];
}
//THIS PART HAVE TO INSERT NOEW BETWEEN THE HEAD AND BOTTM
if (rowNumTotal > rowNumPerSheet)
{
for (int i = rowNumPerSheet; i < rowNumTotal; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[rowStartAt + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
my.Cells[i + rowStartAt, 1].Value = dt记录详情.Rows[i]["序号"];
my.Cells[i + rowStartAt, 2].Value = dt记录详情.Rows[i]["物料代码"];
my.Cells[i + rowStartAt, 3].Value = dt记录详情.Rows[i]["物料批号"];
my.Cells[i + rowStartAt, 4].Value = dt记录详情.Rows[i]["分装前规格"];
my.Cells[i + rowStartAt, 5].Value = dt记录详情.Rows[i]["分装前数量"];
my.Cells[i + rowStartAt, 6].Value = dt记录详情.Rows[i]["分装后规格"];
my.Cells[i + rowStartAt, 7].Value = dt记录详情.Rows[i]["分装后数量"];
my.Cells[i + rowStartAt, 8].Value = dt记录详情.Rows[i]["外观"];
my.Cells[i + rowStartAt, 9].Value = dt记录详情.Rows[i]["数量偏差"];
my.Cells[i + rowStartAt, 10].Value = dt记录详情.Rows[i]["判定"];
my.Cells[i + rowStartAt, 11].Value = dt记录详情.Rows[i]["备注"];
}
}
Microsoft.Office.Interop.Excel.Range range1 = (Microsoft.Office.Interop.Excel.Range)my.Rows[rowStartAt + rowNumTotal, Type.Missing];
range1.EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
//THE BOTTOM HAVE TO CHANGE LOCATE ACCORDING TO THE ROWS NUMBER IN DT.
int varOffset = (rowNumTotal > rowNumPerSheet) ? rowNumTotal - rowNumPerSheet - 1 : 0;
my.Cells[25 + varOffset, 1].Value = "备注： \n" + dt记录.Rows[0]["备注"];
if (preview)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
}
else
{
//add footer
my.PageSetup.RightFooter = Instruction + "-10-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;; // &P 是页码
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
}
catch { }
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
my = null;
wb = null;
}
return(pageCount);
}
public int print(bool b)
{
int pageCount = 0;
int label_打印成功 = 1;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\PTV\12 SOP-MFG-110-R01A 清场记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 设置该进程是否可见
//oXL.Visible = true;
// 修改Sheet中某行某列的值
fill_excel(my);
//通过_id查找生产指令id，从而查找生产指令编码
int instrid = Convert.ToInt32(dtOuter.Rows[0]["生产指令ID"].ToString());
SqlDataAdapter da = new SqlDataAdapter("select * from 生产指令 where ID=" + instrid, mySystem.Parameter.conn);
DataTable dt = new DataTable();
da.Fill(dt);
string instruction = dt.Rows[0]["生产指令编号"].ToString();
my.PageSetup.RightFooter = instruction + "-" + "12" + "-" + find_indexofprint(instrid).ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;; // &P 是页码
if (b)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
return(0);
}
else
{
// 让这个Sheet为被选中状态
//my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch
{
label_打印成功 = 0;
}
finally
{
if (1 == label_打印成功)
{
string log = "\n=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + ":" + mySystem.Parameter.userName + " 完成打印\n";
dtOuter.Rows[0]["日志"] = dtOuter.Rows[0]["日志"].ToString() + log;
bsOuter.EndEdit();
daOuter.Update((DataTable)bsOuter.DataSource);
}
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
my = null;
wb = null;
}
return(pageCount);
}
}
/// <summary>
/// this function is responsbily by pool 2017-11-03
/// </summary>
/// <param name="b"></param>
public int print(bool b)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\CSBag\SOP-MFG-109-R01A 产品内包装记录.xlsx");
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\PTV\9 SOP-MFG-413-R01A 圆口焊接机运行记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[wb.Worksheets.Count];
// 修改Sheet中某行某列的值
fill_excel(my, wb);
//"生产指令-步骤序号- 表序号 /&P"
my.PageSetup.RightFooter = Instruction + "-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码
if (b)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = 0;
bool isPrint = true;
//false->打印
try
{
// 设置该进程是否可见
//oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet
// 直接用默认打印机打印该Sheet
my.PrintOut();
}
catch
{ isPrint = false; }
finally
{
if (isPrint)
{
//写日志
string log = "=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + "：" + mySystem.Parameter.userName + " 打印文档\n";
dt记录.Rows[0]["日志"] = dt记录.Rows[0]["日志"].ToString() + log;
bs记录.EndEdit();
da记录.Update((DataTable)bs记录.DataSource);
}
// 关闭文件，false表示不保存
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
return(pageCount);
}
}
public int print(bool preview)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\PTV\14 SOP-MFG-108-R01A 制袋岗位交接班记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 设置该进程是否可见
//oXL.Visible = true;
// 修改Sheet中某行某列的值
string text1 = "白班异常情况处理：";
string text2 = "夜班异常情况处理：";
int rowStartAt = 6;
int rowNumPerSheet = 9;
int rowNumTotal = dtInner.Rows.Count;
my.Cells[3, 1].Value = "生产指令编号：" + dtOuter.Rows[0]["生产指令编号"]; // lbl生产指令编号.Text;
my.Cells[3, 4].Value = "生产日期：" + Convert.ToDateTime(dtOuter.Rows[0]["生产日期"]).ToString("yyyy年MM月dd日"); //dtp生产日期.Value.ToString("yyyy年MM月dd日");
int[] columnIndex = { 1, 2, 4, 5, 6 };
for (int i = 0; i < rowNumPerSheet; i++)
{
for (int j = 0; j < columnIndex.Length; j++)
{
my.Cells[i + rowStartAt, columnIndex[j]].Value = "";
}
}
//judge whether new rows should be inserted
if (rowNumTotal > rowNumPerSheet)
{
for (int i = 0; i < rowNumTotal - rowNumPerSheet; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[rowStartAt + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
}
}
for (int i = 0; i < dtInner.Rows.Count; i++)
{
my.Cells[i + 6, 1].Value = dtInner.Rows[i]["序号"];
my.Cells[i + 6, 2].Value = dtInner.Rows[i]["确认项目"];
my.Cells[i + 6, 4].Value = dtInner.Rows[i]["确认结果白班"];
my.Cells[i + 6, 5].Value = dtInner.Rows[i]["确认结果夜班"];
}
int iterMidFlag = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(Math.Max(rowNumPerSheet, rowNumTotal) / 2)) + rowStartAt - 1);
my.Cells[4, 6].Value = text1;
my.Cells[rowStartAt, 6].Value = dtOuter.Rows[0]["白班异常情况处理"]; //txb白班异常情况处理.Text;
my.Cells[iterMidFlag - 1, 6].Value = "交班人：" + dtOuter.Rows[0]["白班交班员"].ToString() + " 接班人：" + dtOuter.Rows[0]["夜班接班员"].ToString() + " 时间：" + Convert.ToDateTime(dtOuter.Rows[0]["白班交接班时间"]).ToString("yyyy年MM月dd日");
my.Cells[iterMidFlag, 6].Value = text2;
my.Cells[iterMidFlag + 1, 6].Value = dtOuter.Rows[0]["夜班异常情况处理"]; //txb夜班异常情况处理.Text;
my.Cells[Math.Max(rowNumPerSheet, rowNumTotal) + rowStartAt - 1, 6].Value = "交班人：" + dtOuter.Rows[0]["夜班交班员"].ToString() + " 接班人：" + dtOuter.Rows[0]["白班接班员"].ToString() + " 时间：" + Convert.ToDateTime(dtOuter.Rows[0]["夜班交接班时间"]).ToString("yyyy年MM月dd日");
my.PageSetup.RightFooter = __生产指令编号 + "-" + "14" + "-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count; ; // &P 是页码
if (preview)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
return 0;
}
else
{
// 让这个Sheet为被选中状态
//my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch { }
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
my = null;
wb = null;
return pageCount;
}
}
/// <summary>
/// 打印交易票
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void printBtn_Click(object sender, EventArgs e)
{
try
{
vehicle = VehicleDao.GetBySerial(this.serial.Text);
vehicle.Isprinted = true;
vehicle.Isgrant = false;
VehicleDao.UpdateVehicle(vehicle);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
DirectoryInfo Dir = new DirectoryInfo(".");
m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Input.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
// 开票日期
m_objRange = m_objSheet.get_Range("B1", oMissing);
m_objRange.Value = DateTime.ParseExact(VehicleDao.GetCurrentDate(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture).ToString("yyyy/MM/dd");
// 开票人
m_objRange = m_objSheet.get_Range("K19", oMissing);
m_objRange.Value = LoginForm.user.UsersName;
// 买 方 单 位 /个人
m_objRange = m_objSheet.get_Range("C5", oMissing);
m_objRange.Value = this.currentName.Text;
// 单位代码/身份证号码
m_objRange = m_objSheet.get_Range("K5", oMissing);
m_objRange.Value = this.currentId.Text;
// 买方单位/个人住址
m_objRange = m_objSheet.get_Range("C6", oMissing);
m_objRange.Value = this.currentAddress.Text;
// 电话
m_objRange = m_objSheet.get_Range("L6", oMissing);
m_objRange.Value = this.currentPhone.Text;
// 卖 方 单 位/ 个人
m_objRange = m_objSheet.get_Range("C7", oMissing);
m_objRange.Value = this.originName.Text;
// 单位代码/身份证号码
m_objRange = m_objSheet.get_Range("K7", oMissing);
m_objRange.Value = this.originId.Text;
// 卖方单位/个人住址
m_objRange = m_objSheet.get_Range("C8", oMissing);
m_objRange.Value = this.originAddress.Text;
// 电话
m_objRange = m_objSheet.get_Range("L8", oMissing);
m_objRange.Value = this.originPhone.Text;
// 车 牌 照 号
m_objRange = m_objSheet.get_Range("C9", oMissing);
m_objRange.Value = "辽B." + this.license.Text;
// 登记证号
m_objRange = m_objSheet.get_Range("E9", oMissing);
m_objRange.Value = this.certificate.Text;
// 车 辆 类 型
m_objRange = m_objSheet.get_Range("L9", oMissing);
m_objRange.Value = this.vehicleType.Text;
// 车架号/车辆识别代码
m_objRange = m_objSheet.get_Range("C10", oMissing);
m_objRange.Value = this.vin.Text;
// 厂牌型号
m_objRange = m_objSheet.get_Range("E10", oMissing);
m_objRange.Value = this.brand.Text;
// 转入地车辆管理所名称
m_objRange = m_objSheet.get_Range("L10", oMissing);
m_objRange.Value = this.department.Text;
// 车价 合 计（大写）
m_objRange = m_objSheet.get_Range("C11", oMissing);
m_objRange.Value = this.transactions.Text;
// 车价 合 计（小写）
m_objRange = m_objSheet.get_Range("L11", oMissing);
m_objRange.Value = this.transactions.Text;
m_objExcel.DisplayAlerts = false;
m_objSheet.PrintOut();
}
catch (Exception ex)
{
MessageBox.Show("打印失败，请检查打印机设置。错误信息：" + ex.Message);
}
finally
{
if (m_objBook != null)
{
m_objBook.Close(oMissing, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
}
if (m_objExcel != null)
{
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
this.resetControlContent();
this.setControlReadOnly(true);
this.serial.Focus();
this.serial.Select(this.serial.Text.Length, 0);
}
/// <summary>
/// 打印
/// </summary>
private void printInfo()
{
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
DirectoryInfo Dir = new DirectoryInfo(".");
m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "/Templete/Alter.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
// 号牌种类
m_objRange = m_objSheet.get_Range("D2", oMissing);
m_objRange.Value = this.category.Text;
// 号牌号码
m_objRange = m_objSheet.get_Range("H2", oMissing);
m_objRange.Value = "辽B" + this.license.Text;
// 变更机动车所有人姓名/名称
m_objRange = m_objSheet.get_Range("D4", oMissing);
m_objRange.Value = this.ownerName.Text;
//共同所有的机动车变更所有人
m_objRange = m_objSheet.get_Range("D5", oMissing);
m_objRange.Value = this.owner.Text;
// 住所在车辆管理所辖区内迁移
m_objRange = m_objSheet.get_Range("D6", oMissing);
m_objRange.Value = this.newAddress.Text;
// 邮寄地址
m_objRange = m_objSheet.get_Range("D7", oMissing);
m_objRange.Value = " " + this.postAddress.Text;
// 邮政编码
m_objRange = m_objSheet.get_Range("D8", oMissing);
m_objRange.Value = " " + this.postcode.Text;
// 电子信箱
m_objRange = m_objSheet.get_Range("D9", oMissing);
m_objRange.Value = " " + this.email.Text;
// 固定电话
m_objRange = m_objSheet.get_Range("H8", oMissing);
m_objRange.Value = " " + this.phone.Text;
// 移动电话
m_objRange = m_objSheet.get_Range("H9", oMissing);
m_objRange.Value = " " + this.mobile.Text;
// 省（自治县、直辖市)
m_objRange = m_objSheet.get_Range("D10", oMissing);
m_objRange.Value = " " + this.province.Text;
// 车辆管理所
m_objRange = m_objSheet.get_Range("H10", oMissing);
m_objRange.Value = this.department.Text;
// 变更后的信息
m_objRange = m_objSheet.get_Range("D15", oMissing);
m_objRange.Value = this.information.Text;
// 代理人姓名、名称
m_objRange = m_objSheet.get_Range("C21", oMissing);
m_objRange.Value = this.agentName.Text;
// 代理人邮寄地址
m_objRange = m_objSheet.get_Range("C22", oMissing);
m_objRange.Value = this.agentAddress.Text;
// 代理人邮政编码
m_objRange = m_objSheet.get_Range("C23", oMissing);
m_objRange.Value = this.agentPostcode.Text;
// 代理人联系电话
m_objRange = m_objSheet.get_Range("F23", oMissing);
m_objRange.Value = this.agentPhone.Text;
// 代理人电子信箱
m_objRange = m_objSheet.get_Range("C24", oMissing);
m_objRange.Value = this.agentEmail.Text;
// 经办人电话
m_objRange = m_objSheet.get_Range("C25", oMissing);
m_objRange.Value = this.handlerName.Text;
// 经办人联系电话
m_objRange = m_objSheet.get_Range("F25", oMissing);
m_objRange.Value = this.handlerPhone.Text;
// 办理日期
m_objRange = m_objSheet.get_Range("H20", oMissing);
m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day + " ";
m_objRange = m_objSheet.get_Range("H25", oMissing);
m_objRange.Value = DateTime.Today.Year + " " + DateTime.Today.Month + " " + DateTime.Today.Day + " ";;
m_objExcel.DisplayAlerts = false;
m_objBook.Save();
m_objSheet.PrintOut();
}
catch (Exception ex)
{
// 输出异常信息
MessageBox.Show("打印失败,异常信息为：" + ex.Message);
}
finally
{
if (m_objBook != null)
{
m_objBook.Close(oMissing, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
}
if (m_objExcel != null)
{
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
/// <summary>
/// 打印交易票
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void printBtn_Click(object sender, EventArgs e)
{
if (!this.ValidateChildren(ValidationConstraints.Enabled & ValidationConstraints.TabStop & ValidationConstraints.Selectable))
{
MessageBox.Show("控件内容不合法");
return;
}
Object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
DirectoryInfo Dir = new DirectoryInfo(".");
m_objBook = m_objExcel.Workbooks.Open(Dir.FullName + "\\input.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
// 开票日期
m_objRange = m_objSheet.get_Range("B1", oMissing);
m_objRange.Value = VehicleDao.GetCurrentDate();
// 买 方 单 位 /个人
m_objRange = m_objSheet.get_Range("C5", oMissing);
m_objRange.Value = this.currentName.Text;
// 单位代码/身份证号码
m_objRange = m_objSheet.get_Range("K5", oMissing);
m_objRange.Value = this.currentId.Text;
// 买方单位/个人住址
m_objRange = m_objSheet.get_Range("C6", oMissing);
m_objRange.Value = this.currentAddress.Text;
// 电话
m_objRange = m_objSheet.get_Range("L6", oMissing);
m_objRange.Value = this.currentPhone.Text;
// 卖 方 单 位/ 个人
m_objRange = m_objSheet.get_Range("C7", oMissing);
m_objRange.Value = this.originName.Text;
// 单位代码/身份证号码
m_objRange = m_objSheet.get_Range("K7", oMissing);
m_objRange.Value = this.originId.Text;
// 卖方单位/个人住址
m_objRange = m_objSheet.get_Range("C8", oMissing);
m_objRange.Value = this.originAddress.Text;
// 电话
m_objRange = m_objSheet.get_Range("L8", oMissing);
m_objRange.Value = this.originPhone.Text;
// 车 牌 照 号
m_objRange = m_objSheet.get_Range("C9", oMissing);
m_objRange.Value = "辽B." + this.license.Text;
// 登记证号
m_objRange = m_objSheet.get_Range("E9", oMissing);
m_objRange.Value = this.certificate.Text;
// 车 辆 类 型
m_objRange = m_objSheet.get_Range("L9", oMissing);
m_objRange.Value = this.vehicleType.Text;
// 车架号/车辆识别代码
m_objRange = m_objSheet.get_Range("C10", oMissing);
m_objRange.Value = this.vin.Text;
// 厂牌型号
m_objRange = m_objSheet.get_Range("E10", oMissing);
m_objRange.Value = this.brand.Text;
// 转入地车辆管理所名称
m_objRange = m_objSheet.get_Range("L10", oMissing);
m_objRange.Value = this.department.Text;
// 车价 合 计（大写）
m_objRange = m_objSheet.get_Range("C11", oMissing);
m_objRange.Value = this.transactions.Text;
// 车价 合 计（小写）
m_objRange = m_objSheet.get_Range("L11", oMissing);
m_objRange.Value = this.transactions.Text;
m_objExcel.DisplayAlerts = false;
m_objSheet.PrintOut();
}
catch (Exception ex)
{
MessageBox.Show("打印失败，请检查打印机设置。错误代码：" + ex.Message);
}
finally
{
if (m_objBook != null)
{
m_objBook.Close(oMissing, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
}
if (m_objExcel != null)
{
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
public int print(bool preview)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\Extrusion\C\SOP-MFG-301-R07 吹膜供料系统运行记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[3];
// 设置该进程是否可见
//oXL.Visible = true;
// 修改Sheet中某行某列的值
my.Cells[3, 1].Value = "生产指令：" + __生产指令编号;
//my.Cells[5, 1].Value = __生产日期.ToString("yyyy年MM月dd日") + " " + __班次;
//my.Cells[5, 9].Value = "";
int ind = 0;//偏移
if (dataGridView1.Rows.Count > 15)
{
//在第10行插入
for (int i = 0; i < dataGridView1.Rows.Count - 15; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[10, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
ind = dataGridView1.Rows.Count - 15;
}
for (int i = 0; i < dtInner.Rows.Count; i++)
{
my.Cells[5 + i, 1].Value = Convert.ToDateTime(dtInner.Rows[i]["检查时间"]).ToString("yyyy/MM/dd");
my.Cells[5 + i, 2].Value = Convert.ToDateTime(dtInner.Rows[i]["检查时间"]).ToString("HH:mm:ss");
my.Cells[5 + i, 3].Value = dtInner.Rows[i]["班次"].ToString();
my.Cells[5 + i, 4].Value = dtInner.Rows[i]["电机工作是否正常"];
my.Cells[5 + i, 5].Value = dtInner.Rows[i]["气动阀工作是否正常"];
my.Cells[5 + i, 6].Value = dtInner.Rows[i]["供料运行是否正常"];
my.Cells[5 + i, 7].Value = dtInner.Rows[i]["有无警报显示"];
my.Cells[5 + i, 8].Value = dtInner.Rows[i]["是否解除警报"];
my.Cells[5 + i, 9].Value = dtInner.Rows[i]["检查员备注"];
my.Cells[5 + i, 10].Value = dtInner.Rows[i]["检查员"];
my.Cells[5 + i, 11].Value = dtInner.Rows[i]["审核员"];
}
//my.Cells[5, 9].Value = dtOuter.Rows[0]["审核员"];
// 让这个Sheet为被选中状态
my.PageSetup.RightFooter = __生产指令编号 + "-07-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;; // &P 是页码
if (preview)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
return(0);
}
else
{
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch { }
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
wb = null;
my = null;
return(pageCount);
}
}
/// <summary>
/// this print function has been updated by pool on 2017-11-01
/// but hasn't been tested on printer
/// this edition of function automacally insert new rows according to settings
/// </summary>
/// <param name="preview"></param>
public int print(bool preview)
{
int pageCount = 0;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\BPVBag\14 SOP-MFG-502-R01A BPV袋体生产记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[5];
// 设置该进程是否可见
oXL.Visible = true;
// 修改Sheet中某行某列的值
int rowStartAt = 6;
my.Cells[3, 1].Value = "产品代码：" + dt记录.Rows[0]["产品代码"];
my.Cells[3, 4].Value = "产品批号：" + dt记录.Rows[0]["产品批号"];
my.Cells[3, 9].Value = "生产指令：" + getInsFromID(Convert.ToInt32(dt记录.Rows[0]["生产指令ID"]));
int rowNumPerSheet = 15;
int rowNumTotal = dt记录详情.Rows.Count;
if (rowNumTotal > rowNumPerSheet)
{
for (int i = 0; i < rowNumTotal - rowNumPerSheet; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[rowStartAt + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
}
}
for (int i = 0; i < rowNumTotal; i++)
{
my.Cells[i + rowStartAt, 1].Value = dt记录详情.Rows[i]["序号"];
my.Cells[i + rowStartAt, 2].Value = Convert.ToDateTime(dt记录详情.Rows[i]["生产时间"]).ToString("MM/dd HH:mm");
my.Cells[i + rowStartAt, 2].Font.Size = 11;
my.Cells[i + rowStartAt, 3].Value = "";
my.Cells[i + rowStartAt, 3].Value = dt记录详情.Rows[i]["切片尺寸长"];
my.Cells[i + rowStartAt, 4].Value = dt记录详情.Rows[i]["切片尺寸宽"];
my.Cells[i + rowStartAt, 7].Value = dt记录详情.Rows[i]["袋体外观检查"];
my.Cells[i + rowStartAt, 5].Value = dt记录详情.Rows[i]["袋体尺寸确认"];
my.Cells[i + rowStartAt, 6].Value = dt记录详情.Rows[i]["热封线检查"];
my.Cells[i + rowStartAt, 8].Value = dt记录详情.Rows[i]["合格品数量"];
my.Cells[i + rowStartAt, 9].Value = dt记录详情.Rows[i]["不良品数量"];
my.Cells[i + rowStartAt, 10].Value = dt记录详情.Rows[i]["操作员"];
my.Cells[i + rowStartAt, 11].Value = dt记录详情.Rows[i]["审核员"];
}
int varOffset = (rowNumTotal > rowNumPerSheet) ? rowNumTotal - rowNumPerSheet : 0;
my.Cells[21 + varOffset, 8].Value = dt记录.Rows[0]["合格品数量"];
my.Cells[21 + varOffset, 9].Value = dt记录.Rows[0]["不良品数量"];
if (preview)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
}
else
{
//add footer
my.PageSetup.RightFooter = Instruction + "-10-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;; // &P 是页码
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
}
catch { }
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
my = null;
wb = null;
}
return(pageCount);
}
public int print(bool preview)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\CSBag\SOP-MFG-108-R01A 制袋岗位交接班记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 设置该进程是否可见
//oXL.Visible = true;
// 修改Sheet中某行某列的值
my.Cells[3, 1].Value = "生产指令编号：" + dtOuter.Rows[0]["生产指令编号"]; // lbl生产指令编号.Text;
my.Cells[3, 4].Value = "生产日期：" + Convert.ToDateTime(dtOuter.Rows[0]["生产日期"]).ToString("yyyy年MM月dd日"); //dtp生产日期.Value.ToString("yyyy年MM月dd日");
//插入新行
int ind = 0;
if (dtInner.Rows.Count > 9)
{
for (int i = 0; i < dtInner.Rows.Count - 9; i++)
{
//在第9行插入
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[9 + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
ind = dtInner.Rows.Count - 9;
}
for (int i = 0; i < dtInner.Rows.Count; i++)
{
my.Cells[i + 6, 1].Value = dtInner.Rows[i]["序号"];
my.Cells[i + 6, 2].Value = dtInner.Rows[i]["确认项目"];
my.Cells[i + 6, 4].Value = dtInner.Rows[i]["确认结果白班"];
my.Cells[i + 6, 5].Value = dtInner.Rows[i]["确认结果夜班"];
}
my.Cells[4, 6].Value = "白班异常情况处理："; //txb白班异常情况处理.Text;
my.Cells[5, 6].Value = dtOuter.Rows[0]["白班异常情况处理"]; //txb白班异常情况处理.Text;
int mid = 3 + (11 + ind) / 2;
my.Cells[mid, 6].Value = "交班人：" + dtOuter.Rows[0]["白班交班员"].ToString() + " 接班人：" + dtOuter.Rows[0]["夜班接班员"].ToString() + " 时间：" + Convert.ToDateTime(dtOuter.Rows[0]["白班交接班时间"]).ToString("yyyy年MM月dd日");
my.Cells[mid + 1, 6].Value = "夜班异常情况处理："; //txb夜班异常情况处理.Text;
my.Cells[mid + 2, 6].Value = dtOuter.Rows[0]["夜班异常情况处理"]; //txb夜班异常情况处理.Text;
my.Cells[14 + ind, 6].Value = "交班人：" + dtOuter.Rows[0]["夜班交班员"].ToString() + " 接班人：" + dtOuter.Rows[0]["白班接班员"].ToString() + " 时间：" + Convert.ToDateTime(dtOuter.Rows[0]["夜班交接班时间"]).ToString("yyyy年MM月dd日");
my.PageSetup.RightFooter = __生产指令编号 + "-" + "14" + "-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;; // &P 是页码
if (preview)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
return(0);
}
else
{
// 让这个Sheet为被选中状态
//my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch { }
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
my = null;
wb = null;
return(pageCount);
}
}
public int print(bool preview)
{
int pageCount = 0;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\BPVBag\SOP-MFG-110-R01A 清场记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 设置该进程是否可见
//oXL.Visible = true;
// 修改Sheet中某行某列的值
int rowStartAt = 5;
my.Cells[3, 1].Value = "生产指令编号：" + dtOuter.Rows[0]["生产指令编号"];
my.Cells[3, 1].Value = "产品代码/规格：" + dtOuter.Rows[0]["产品代码"];
my.Cells[3, 5].Value = "生产批号：" + dtOuter.Rows[0]["生产批号"];
my.Cells[3, 7].Value = "生产日期：" + Convert.ToDateTime(dtOuter.Rows[0]["生产日期"]).ToString("yyyy年MM月dd日") + "生产班次：" + dtOuter.Rows[0]["生产班次"];
//EVERY SHEET CONTAINS 14 RECORDS
int rowNumPerSheet = 13;
int rowNumTotal = dtInner.Rows.Count;
for (int i = 0; i < (rowNumTotal > rowNumPerSheet ? rowNumPerSheet : rowNumTotal); i++)
{
my.Cells[i + rowStartAt, 1].Value = dtInner.Rows[i]["序号"];
my.Cells[i + rowStartAt, 2].Value = dtInner.Rows[i]["清场项目"];
//my.Cells[i + rowStartAt, 2].Value = Convert.ToDateTime(dt记录详情.Rows[i]["生产日期时间"]).ToString("MM/dd HH:mm");
//my.Cells[i + rowStartAt, 2].Font.Size = 11;
my.Cells[i + rowStartAt, 3].Value = dtInner.Rows[i]["清场要点"];
my.Cells[i + rowStartAt, 6].Value = dtInner.Rows[i]["清洁操作"];
}
//THIS PART HAVE TO INSERT NOEW BETWEEN THE HEAD AND BOTTM
if (rowNumTotal > rowNumPerSheet)
{
for (int i = rowNumPerSheet; i < rowNumTotal; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[rowStartAt + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
my.Cells[i + rowStartAt, 1].Value = dtInner.Rows[i]["序号"];
my.Cells[i + rowStartAt, 2].Value = dtInner.Rows[i]["清场项目"];
//my.Cells[i + rowStartAt, 2].Value = Convert.ToDateTime(dt记录详情.Rows[i]["生产日期时间"]).ToString("MM/dd HH:mm");
//my.Cells[i + rowStartAt, 2].Font.Size = 11;
my.Cells[i + rowStartAt, 3].Value = dtInner.Rows[i]["清场要点"];
my.Cells[i + rowStartAt, 6].Value = dtInner.Rows[i]["清洁操作"];
}
}
Microsoft.Office.Interop.Excel.Range range1 = (Microsoft.Office.Interop.Excel.Range)my.Rows[rowStartAt + rowNumTotal, Type.Missing];
range1.EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
//THE BOTTOM HAVE TO CHANGE LOCATE ACCORDING TO THE ROWS NUMBER IN DT.
int varOffset = (rowNumTotal > rowNumPerSheet) ? rowNumTotal - rowNumPerSheet - 1 : 0;
my.Cells[19 + varOffset, 1].Value = "备注:\n" + dtOuter.Rows[0]["备注"];
my.Cells[5, 7].Value = dtOuter.Rows[0]["操作员"];
my.Cells[5, 8].Value = dtOuter.Rows[0]["检查结果"];
my.Cells[5, 9].Value = dtOuter.Rows[0]["审核员"];
if (preview)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
}
else
{
//add footer
my.PageSetup.RightFooter = CODE + "-10-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;; // &P 是页码
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
}
catch { }
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
my = null;
wb = null;
}
return(pageCount);
}
public int print(bool b)
{
int pageCount = 0;
int label_打印成功 = 1;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\PTV\2 SOP-MFG-305-R03A PTV生产开机确认表.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 设置该进程是否可见
//oXL.Visible = true;
// 修改Sheet中某行某列的值
fill_excel(my);
my.PageSetup.RightFooter = Instruction + "-" + "2" + "-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;; // &P 是页码
if (b)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
return(pageCount);
}
else
{
// 让这个Sheet为被选中状态
//my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch
{
label_打印成功 = 0;
}
finally
{
if (1 == label_打印成功)
{
string log = "\n=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + ":" + mySystem.Parameter.userName + " 完成打印\n";
dt记录.Rows[0]["日志"] = dt记录.Rows[0]["日志"].ToString() + log;
bs记录.EndEdit();
da记录.Update((DataTable)bs记录.DataSource);
}
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
my = null;
wb = null;
}
return(pageCount);
}
}
/// <summary>
/// thsi function has been updated by pool on 2017-11-07
/// </summary>
/// <param name="preview"></param>
public int print(bool preview)
{
int pageCount = 0;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\BPVBag\18 SOP-MFG-306-R08A 关键尺寸确认记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[4];
// 设置该进程是否可见
//oXL.Visible = true;
// 修改Sheet中某行某列的值
my.Cells[3, 11].Value = "生产指令：\n" + lbl生产指令编号.Text;
my.Cells[3, 1].Value = "产品代码：" + tb产品代码.Text;
my.Cells[3, 5].Value = "产品批号：" + tb产品批号.Text;
my.Cells[3, 8].Value = "生产日期：" + dtp生产日期.Value.ToString("yyyy年MM月dd日");
my.Cells[4, 1].Value = "判定依据：关键尺寸要求v " + dt记录.Rows[0]["关键尺寸要求1"].ToString() + " ± " + dt记录.Rows[0]["关键尺寸要求2"].ToString() + " mm";
my.Cells[4, 8].Value = "合格产品数量：" + dt记录.Rows[0]["合格产品数量"].ToString() + " 个，不良品数量：" + dt记录.Rows[0]["不良品数量"].ToString() + " 个。";
int rowStartAt = 6;
int rowNumPerSheet = 20;
int rowNumTotal = dt记录详情.Rows.Count;
int line = Convert.ToInt32(System.Math.Ceiling(Convert.ToDouble(rowNumTotal / 4)));
if (line > rowNumPerSheet)
{
for (int i = 0; i < line - rowNumPerSheet; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[rowStartAt + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
}
}
//EVERY SHEET CONTAINS 15 RECORDS
for (int i = 0; i < line; i++)
{
for (int j = 0; j < 4; j++)
{
my.Cells[i + rowStartAt, 3 * j + 1].Value = dt记录详情.Rows[4 * i + j]["序号"];
my.Cells[i + rowStartAt, 3 * j + 2].Value = dt记录详情.Rows[4 * i + j]["实测值"];
my.Cells[i + rowStartAt, 3 * j + 3].Value = dt记录详情.Rows[4 * i + j]["判定"];
}
}
//my.Cells[32, 1].Value = "合格产品数量： " + tb合格产品数量.Text + " 个，不良品数量： " + tb不良品数量.Text + " 个。";
int varOffset = (line > rowNumPerSheet) ? line - rowNumPerSheet : 0;
my.Cells[26 + varOffset, 1].Value = "备注：\n" + tb备注.Text;
my.Cells[27 + varOffset, 1].Value = "操作人/日期： " + tb操作员.Text + dtp操作日期.Value.ToString("yyyy年MM月dd日");
my.Cells[27 + varOffset, 8].Value = "QC复核/日期： " + tb审核员.Text + dtp审核日期.Value.ToString("yyyy年MM月dd日");
if (preview)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
}
else
{
//add footer
my.PageSetup.RightFooter = Instruction + "-10-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;; // &P 是页码
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
}
catch { }
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
my = null;
wb = null;
}
return(pageCount);
}
/// <summary>
/// this function has been updated to the latest documents by pool on 2017-11-01
/// in this edition, 生产操作员 in database acts as 生产人员,with no check button added;
/// </summary>
/// <param name="preview"></param>
public int print(bool preview)
{
int pageCount = 0;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\BPVBag\17 SOP-MFG-306-R05A BPV装配确认记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[5];
// 设置该进程是否可见
oXL.Visible = true;
int rowStartAt = 5;
// 修改Sheet中某行某列的值
my.Cells[3, 1].Value = "生产指令编号：\n" + dt记录.Rows[0]["生产指令编号"];
my.Cells[3, 3].Value = "产品代码/规格：" + dt记录.Rows[0]["成品代码"];
my.Cells[3, 7].Value = "产品批号：" + dt记录.Rows[0]["产品批号"];
my.Cells[3, 9].Value = "生产日期：" + Convert.ToDateTime(dt记录.Rows[0]["生产日期"]).ToString("yyyy年MM月dd日");
//EVERY SHEET CONTAINS 14 RECORDS
int rowNumPerSheet = 14;
int rowNumTotal = dt记录详情.Rows.Count;
if (rowNumTotal > rowNumPerSheet)
{
for (int i = 0; i < rowNumTotal - rowNumPerSheet; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[rowStartAt + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
}
}
for (int i = 0; i < rowNumTotal; i++)
{
my.Cells[i + rowStartAt, 1].Value = dt记录详情.Rows[i]["序号"];
//this line cannot find a cloumn responsible to inner table, and 完成时间 is borrowd for temporaty
my.Cells[i + rowStartAt, 2].Value = dt记录详情.Rows[i]["完成时间"];
my.Cells[i + rowStartAt, 3].Value = dt记录详情.Rows[i]["产品代码"];
//my.Cells[i + rowStartAt, 3].Value = dt记录详情.Rows[i]["组件数量"];
my.Cells[i + rowStartAt, 4].Value = dt记录详情.Rows[i]["合格数量只"];
my.Cells[i + rowStartAt, 5].Value = dt记录详情.Rows[i]["不良品数量只"];
my.Cells[i + rowStartAt, 6].Value = dt记录详情.Rows[i]["质量检查判定"];
my.Cells[i + rowStartAt, 7].Value = dt记录详情.Rows[i]["备注"];
my.Cells[i + rowStartAt, 8].Value = dt记录详情.Rows[i]["生产操作员"].ToString(); // +dt记录详情.Rows[i]["完成时间"].ToString();
my.Cells[i + rowStartAt, 9].Value = dt记录详情.Rows[i]["QC人员"].ToString(); // +dt记录详情.Rows[i]["确认时间"].ToString();
}
//THE BOTTOM HAVE TO CHANGE LOCATE ACCORDING TO THE ROWS NUMBER IN DT.
int varOffset = (rowNumTotal > rowNumPerSheet) ? rowNumTotal - rowNumPerSheet : 0;
//my.Cells[12 + varOffset, 1].Value = "备注： " + dt记录.Rows[0]["备注"];
if (preview)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
}
else
{
//add footer
my.PageSetup.RightFooter = Instruction + "-10-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;; // &P 是页码
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
}
catch { }
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
my = null;
wb = null;
}
return(pageCount);
}
/// <summary>
/// this print function has been updated by pool on 2017-11-01
/// but hasn't been tested on printer
/// this edition of function automacally insert new rows according to settings
/// </summary>
/// <param name="preview"></param>
public int print(bool preview)
{
int pageCount = 0;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//System.IO.Directory.GetCurrentDirectory;
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\BPVBag\9 SOP-MFG-417-R01A 2D袋体与船型接口热合记录.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[2];
// 设置该进程是否可见
//oXL.Visible = true;
// 修改Sheet中某行某列的值
int rowStartAt = 6;
my.Cells[3, 1].Value = "产品代码：" + dt记录.Rows[0]["产品代码"];
my.Cells[3, 5].Value = "产品批号：" + dt记录.Rows[0]["产品批号"];
my.Cells[3, 10].Value = "生产指令编号：" + dt记录.Rows[0]["生产指令编号"];
//my.Cells[3, 10].Value = "生产日期：" + Convert.ToDateTime(dt记录.Rows[0]["生产日期"]).ToString("yyyy年MM月dd日");
//EVERY SHEET CONTAINS 15 RECORDS
int rowNumPerSheet = 15;
int rowNumTotal = dt记录详情.Rows.Count;
if (rowNumTotal > rowNumPerSheet)
{
for (int i = 0; i < rowNumTotal - rowNumPerSheet; i++)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)my.Rows[rowStartAt + i, Type.Missing];
range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown,
Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
}
}
for (int i = 0; i < rowNumTotal; i++)
{
my.Cells[i + rowStartAt, 1].Value = dt记录详情.Rows[i]["序号"];
my.Cells[i + rowStartAt, 2].Value = Convert.ToDateTime(dt记录详情.Rows[i]["生产时间"]).ToString("MM/dd HH:mm");
my.Cells[i + rowStartAt, 2].Font.Size = 11;
my.Cells[i + rowStartAt, 3].Value = dt记录详情.Rows[i]["焊接温度上"];
my.Cells[i + rowStartAt, 4].Value = dt记录详情.Rows[i]["焊接温度下"];
my.Cells[i + rowStartAt, 5].Value = dt记录详情.Rows[i]["焊接时间"];
my.Cells[i + rowStartAt, 6].Value = dt记录详情.Rows[i]["焊接压力"];
my.Cells[i + rowStartAt, 7].Value = dt记录详情.Rows[i]["管组件1"];
my.Cells[i + rowStartAt, 8].Value = dt记录详情.Rows[i]["管组件2"];
my.Cells[i + rowStartAt, 9].Value = dt记录详情.Rows[i]["管组件3"];
my.Cells[i + rowStartAt, 10].Value = dt记录详情.Rows[i]["合格产品数量"];
my.Cells[i + rowStartAt, 11].Value = dt记录详情.Rows[i]["操作员"];
my.Cells[i + rowStartAt, 12].Value = dt记录详情.Rows[i]["备注"];
}
//THE BOTTOM HAVE TO CHANGE LOCATE ACCORDING TO THE ROWS NUMBER IN DT.
int varOffset = (rowNumTotal > rowNumPerSheet) ? rowNumTotal - rowNumPerSheet : 0;
my.Cells[21 + varOffset, 10].Value = dt记录.Rows[0]["合格品数量"];
my.Cells[21 + varOffset, 11].Value = dt记录.Rows[0]["不良品数量"];
//my.Cells[20 + varOffset, 6].Value = "合格品数量：" + dt记录.Rows[0]["合格品数量"] + "只\n\n不良品数量：" + dt记录.Rows[0]["不良品数量"] + "只";
//my.Cells[20 + varOffset, 9].Value = "审核员： " + dt记录.Rows[0]["审核员"] + "\n日期： " + Convert.ToDateTime(dt记录.Rows[0]["审核日期"]).ToString("yyyy年MM月dd日");
if (preview)
{
my.Select();
oXL.Visible = true; //加上这一行 就相当于预览功能
}
else
{
//add footer
my.PageSetup.RightFooter = Instruction + "-10-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;; // &P 是页码
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
}
catch { }
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
oXL = null;
my = null;
wb = null;
}
return(pageCount);
}
public int print(bool b)
{
int label_打印成功 = 1;
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
string dir = System.IO.Directory.GetCurrentDirectory();
dir += "./../../xls/LDPE/8 SOP-MFG-110-R01A 清场记录.xlsx";
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(dir);
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[1];
// 修改Sheet中某行某列的值
fill_excel(my);
//"生产指令-步骤序号- 表序号 /&P"
int sheetnum;
SqlDataAdapter da = new SqlDataAdapter("select ID from 清场记录" + " where 生产指令ID=" + ID.ToString(), mySystem.Parameter.conn);
DataTable dt = new DataTable("temp");
da.Fill(dt);
List <Int32> sheetList = new List <Int32>();
for (int i = 0; i < dt.Rows.Count; i++)
{
sheetList.Add(Convert.ToInt32(dt.Rows[i]["ID"].ToString()));
}
sheetnum = sheetList.IndexOf(Convert.ToInt32(dtOuter.Rows[0]["ID"])) + 1;
my.PageSetup.RightFooter = CODE + "-" + sheetnum.ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码
if (b)
{
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 直接用默认打印机打印该Sheet
try
{
my.PrintOut(); // oXL.Visible=false 就会直接打印该Sheet
}
catch
{
label_打印成功 = 0;
}
finally
{
if (1 == label_打印成功)
{
string log = "\n=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色 + ":" + mySystem.Parameter.userName + " 完成打印\n";
dtOuter.Rows[0]["日志"] = dtOuter.Rows[0]["日志"].ToString() + log;
bsOuter.EndEdit();
daOuter.Update((DataTable)bsOuter.DataSource);
}
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
my = null;
}
return(pageCount);
}
}
protected bool printExcel(string excelPath, string printerName)
{
bool flag = true;
try
{
if (CheckExcelInstalled.isExcelInstalled())
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(excelPath);
//excel.Visible = true;
Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Worksheets["Sheet1"];
try
{
paintPicByExcel(ws);
//打印方向
if (_PageSetup.Orientation == 1)
{
ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
}
//边距
if (_PageSetup.TopMargin != 0)
{
ws.PageSetup.TopMargin = _PageSetup.TopMargin;
}
if (_PageSetup.BottomMargin != 0)
{
ws.PageSetup.BottomMargin = _PageSetup.BottomMargin;
}
if (_PageSetup.LeftMargin != 0)
{
ws.PageSetup.LeftMargin = _PageSetup.LeftMargin;
}
if (_PageSetup.RightMargin != 0)
{
ws.PageSetup.RightMargin = _PageSetup.RightMargin;
}
ws.PrintOut(1, 2, 1, false, printerName, false, false, missing);
workbook.Saved = true;
}
catch (Exception e)
{
flag = false;
}
finally
{
workbook.Close(missing, missing, missing);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
}
else
{
ET.Application et = new ET.Application();
ET._Workbook ewb = et.Workbooks.Add(excelPath);
//et.Visible = true;
ET._Worksheet ews = (ET._Worksheet)ewb.Worksheets["Sheet1"];
try
{
paintPicByEt(ews);
if (_PageSetup.Orientation == 1)
{
ews.PageSetup.Orientation = ET.XlPageOrientation.xlLandscape;
}
//边距
if (_PageSetup.TopMargin != 0)
{
ews.PageSetup.TopMargin = _PageSetup.TopMargin;
}
if (_PageSetup.BottomMargin != 0)
{
ews.PageSetup.BottomMargin = _PageSetup.BottomMargin;
}
if (_PageSetup.LeftMargin != 0)
{
ews.PageSetup.LeftMargin = _PageSetup.LeftMargin;
}
if (_PageSetup.RightMargin != 0)
{
ews.PageSetup.RightMargin = _PageSetup.RightMargin;
}
ews.PrintOut(1, 1, 1, false, printerName, false, false, missing, false, 1, 1, 0, 0, false, ET.ETPaperTray.etPrinterDefaultBin, false, ET.ETPaperOrder.etPrinterRepeat);
ewb.Saved = true;
}
catch (Exception e)
{
flag = false;
}
finally
{
ewb.Close(missing, missing, missing);
et.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ews);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ewb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(et);
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
flag = false;
}
return(flag);
}
//打印功能
public int print(bool isShow)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(@"D:\excel\SOP-MFG-301-R04 吹膜机组开机前确认表_何.xlsx");
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\Extrusion\C\SOP-MFG-301-R04 吹膜机组开机前确认表.xlsx");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[wb.Worksheets.Count];
// 修改Sheet中某行某列的值
SqlDataAdapter da = new SqlDataAdapter("select 生产指令信息表.生产指令编号 as 生产指令编号 from 吹膜机组开机前确认表,生产指令信息表 where 生产指令信息表.ID=吹膜机组开机前确认表.生产指令ID and 吹膜机组开机前确认表.生产指令ID=" + InstruID, mySystem.Parameter.conn);
DataTable dt = new DataTable();
da.Fill(dt);
string zhiling = "";
if (dt.Rows.Count > 0)
{
zhiling = dt.Rows[0]["生产指令编号"].ToString();
}
my = printValue(my, wb, zhiling);
if (isShow)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = 0;
bool isPrint = true;
//false->打印
try
{
// 设置该进程是否可见
//oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet
// 直接用默认打印机打印该Sheet
my.PrintOut();
}
catch
{ isPrint = false; }
finally
{
if (isPrint)
{
//写日志
string log = "=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + "：" + mySystem.Parameter.userName + " 打印文档\n";
dt记录.Rows[0]["日志"] = dt记录.Rows[0]["日志"].ToString() + log;
bs记录.EndEdit();
if (!mySystem.Parameter.isSqlOk)
{
da记录.Update((DataTable)bs记录.DataSource);
}
else
{
da记录_sql.Update((DataTable)bs记录.DataSource);
}
}
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
// 关闭文件，false表示不保存
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
return(pageCount);
}
}