512 lines
18 KiB
C#
512 lines
18 KiB
C#
using NPOI.HPSF;
|
|
using NPOI.HSSF.UserModel;
|
|
using NPOI.SS.UserModel;
|
|
using System;
|
|
using System.Data;
|
|
using System.Data.OleDb;
|
|
using System.Diagnostics;
|
|
using System.IO;
|
|
using System.Runtime.InteropServices;
|
|
using System.Windows.Forms;
|
|
|
|
namespace NGToolsPC
|
|
{
|
|
public partial class frmPrint : Form
|
|
{
|
|
public frmPrint()
|
|
{
|
|
|
|
MySqlCon = new System.Data.OleDb.OleDbConnection(mldPublic.strConCNG);
|
|
InitializeComponent();
|
|
}
|
|
public System.Data.OleDb.OleDbConnection MySqlCon; // VBConversions Note: Initial value cannot be assigned here since it is non-static. Assignment has been moved to the class constructors.
|
|
DataTable dtJdjl = new DataTable();
|
|
public string Jsrq = "";
|
|
|
|
private void button1_Click(object sender, EventArgs e)
|
|
{
|
|
getReport(int.Parse(ListBox3.SelectedValue.ToString()));
|
|
}
|
|
|
|
private void frmPrint_Load(object sender, EventArgs e)
|
|
{
|
|
|
|
dateTimePicker1.Format = DateTimePickerFormat.Custom;
|
|
dateTimePicker1.CustomFormat = "yyyy-MM-dd";
|
|
dateTimePicker2.Format = DateTimePickerFormat.Custom;
|
|
dateTimePicker2.CustomFormat = "yyyy-MM-dd";
|
|
dateTimePicker1.Value = DateTime.Now.AddMonths(-1);
|
|
dateTimePicker2.Value = DateTime.Now;
|
|
|
|
|
|
|
|
|
|
|
|
DataTable dtZhan = new DataTable();
|
|
OleDbDataAdapter ZhanDap = new OleDbDataAdapter();
|
|
string strSQL = "";
|
|
strSQL = "select * from 生产厂家 order by ID";
|
|
dtZhan = new DataTable();
|
|
ZhanDap = new System.Data.OleDb.OleDbDataAdapter();
|
|
ZhanDap.SelectCommand = new OleDbCommand(strSQL, MySqlCon);
|
|
ZhanDap.Fill(dtZhan);
|
|
ListBox1.DisplayMember = "厂家名称";
|
|
ListBox1.ValueMember = "ID";
|
|
ListBox1.DataSource = dtZhan;
|
|
ListBox1.SelectedIndex = 0;
|
|
|
|
|
|
|
|
if (Jsrq == "")
|
|
{
|
|
checkBox1.Checked = true;
|
|
|
|
}
|
|
else
|
|
{
|
|
checkBox1_CheckedChanged(sender, e);
|
|
getReportSs(Jsrq);
|
|
}
|
|
|
|
|
|
}
|
|
private void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
OleDbDataAdapter jldDap = new OleDbDataAdapter();
|
|
string strSQL = "";
|
|
strSQL = "select * from 仪表信息 where 厂家ID=" + ListBox1.SelectedValue.ToString();
|
|
DataTable dtJld = new DataTable();
|
|
jldDap = new System.Data.OleDb.OleDbDataAdapter();
|
|
jldDap.SelectCommand = new OleDbCommand(strSQL, MySqlCon);
|
|
jldDap.Fill(dtJld);
|
|
ListBox2.DisplayMember = "仪表编号";
|
|
ListBox2.ValueMember = "ID";
|
|
ListBox2.DataSource = dtJld;
|
|
if (dtJld.Rows.Count > 0)
|
|
{
|
|
ListBox2.SelectedIndex = 0;
|
|
}
|
|
else
|
|
{
|
|
|
|
}
|
|
axFramerControl1.Close();
|
|
}
|
|
private void ListBox2_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
String strJldID = ListBox2.SelectedValue.ToString();
|
|
string strSQL = "select * from 计算历史记录 where (计量点ID=" + strJldID + ") and format(计算日期,'yyyy-mm-dd') >#" + dateTimePicker1.Value + "# and format(计算日期,'yyyy-mm-dd')<=#" + dateTimePicker2.Value + "# order by ID desc";
|
|
//string strSQL = "select * from 计算历史记录";
|
|
dtJdjl.Clear();
|
|
OleDbDataAdapter jldDap = new System.Data.OleDb.OleDbDataAdapter();
|
|
jldDap.SelectCommand = new OleDbCommand(strSQL, MySqlCon);
|
|
try
|
|
{
|
|
jldDap.Fill(dtJdjl);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
ListBox3.DisplayMember = "计算日期";
|
|
ListBox3.ValueMember = "ID";
|
|
ListBox3.DataSource = dtJdjl;
|
|
|
|
this.Text = dtJdjl.Rows.Count.ToString();
|
|
|
|
axFramerControl1.Close();
|
|
}
|
|
private void getReport(int ID)
|
|
{
|
|
try
|
|
{
|
|
DataRow[] dataDr = dtJdjl.Select("ID=" + ID);
|
|
|
|
string[] flowStr = (dataDr[0]["计量点资料"].ToString().Split('~'));
|
|
string[] NGStr = (dataDr[0]["计算结果"].ToString().Split('~'));
|
|
|
|
|
|
string outPutFilePath = Application.StartupPath + "\\CalResult\\报表" + DateTime.Parse(dataDr[0]["计算日期"].ToString()).ToString("yyyyMMddhhmmss") + ".xls"; ;
|
|
try
|
|
{
|
|
if (File.Exists(outPutFilePath))
|
|
{
|
|
axFramerControl1.Close();
|
|
Process[] process = Process.GetProcessesByName("EXCEL");
|
|
foreach (Process p in process)
|
|
{
|
|
if (!p.HasExited) // 如果程序没有关闭，结束程序
|
|
{
|
|
p.Kill();
|
|
p.WaitForExit();
|
|
}
|
|
}
|
|
process = Process.GetProcessesByName("et");
|
|
foreach (Process p in process)
|
|
{
|
|
if (!p.HasExited) // 如果程序没有关闭，结束程序
|
|
{
|
|
p.Kill();
|
|
p.WaitForExit();
|
|
}
|
|
}
|
|
|
|
axFramerControl1.Open(outPutFilePath, true, null, null, null);
|
|
return;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
DialogResult dr = MessageBox.Show(ex.Message, "提示");
|
|
|
|
}
|
|
|
|
string fileName = "";
|
|
//取得报表模板文件路径
|
|
string reportModelPath = Application.StartupPath + "\\原始记录模版.xls";
|
|
//导出报表文件名
|
|
string tablename = "原始记录";
|
|
fileName = string.Format("{0}-{1}.xls", tablename, DateTime.Now.ToString("yyyyMMddhhmmss"));
|
|
|
|
HSSFWorkbook hssfworkbook = InitializeWorkbook(reportModelPath);
|
|
|
|
|
|
if (null == hssfworkbook)
|
|
{ return; }
|
|
|
|
// 被检表原始记录表格
|
|
ISheet modelSheet = hssfworkbook.GetSheet("Sheet3");
|
|
// 单元格格式
|
|
ICellStyle CenterStyle = hssfworkbook.CreateCellStyle();
|
|
CenterStyle.WrapText = true;//设置换行这个要先设置
|
|
CenterStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
|
|
if (null == modelSheet)
|
|
{ return; }
|
|
|
|
//检定记录信息
|
|
IRow tmpRow = modelSheet.GetRow(0);
|
|
ICell tmpCell0 = tmpRow.GetCell(0);
|
|
ICell tmpCell1 = tmpRow.GetCell(0);
|
|
try
|
|
{
|
|
for (int i = 0; i < flowStr.Length; i++)
|
|
{
|
|
tmpRow = modelSheet.GetRow(i);
|
|
string[] strTemp = flowStr[i].Split(':');
|
|
|
|
tmpCell0 = tmpRow.GetCell(0);
|
|
tmpCell0.SetCellValue(strTemp[0] == "" ? "/" : strTemp[0]);
|
|
|
|
tmpCell1 = tmpRow.GetCell(1);
|
|
tmpCell1.SetCellValue(strTemp[1] == "" ? "/" : strTemp[1]);
|
|
|
|
|
|
|
|
}
|
|
|
|
tmpRow = modelSheet.GetRow(flowStr.Length);
|
|
tmpCell0 = tmpRow.GetCell(0);
|
|
tmpCell0.SetCellValue("检定日期");
|
|
|
|
tmpCell1 = tmpRow.GetCell(1);
|
|
tmpCell1.SetCellValue(dataDr[0]["计算日期"].ToString());
|
|
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
|
|
modelSheet.ForceFormulaRecalculation = true;
|
|
hssfworkbook.SetActiveSheet(0);
|
|
WriteToExcelWithNPOI(outPutFilePath, hssfworkbook);
|
|
|
|
|
|
|
|
|
|
hssfworkbook.Clear();
|
|
hssfworkbook.Close();
|
|
|
|
axFramerControl1.Open(outPutFilePath, true, null, null, null);
|
|
|
|
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
|
|
}
|
|
}
|
|
private void getReportSs(string jsrq)
|
|
{
|
|
try
|
|
{
|
|
string strSQL = "select * from 计算历史记录 where 计算日期=#" + jsrq + "#";
|
|
//string strSQL = "select * from 计算历史记录";
|
|
dtJdjl.Clear();
|
|
OleDbDataAdapter jldDap = new System.Data.OleDb.OleDbDataAdapter();
|
|
jldDap.SelectCommand = new OleDbCommand(strSQL, MySqlCon);
|
|
try
|
|
{
|
|
jldDap.Fill(dtJdjl);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
|
|
if (dtJdjl.Rows.Count > 0)
|
|
{
|
|
|
|
|
|
|
|
string[] flowStr = (dtJdjl.Rows[0]["计量点资料"].ToString().Split('~'));
|
|
string[] NGStr = (dtJdjl.Rows[0]["计算结果"].ToString().Split('~'));
|
|
|
|
|
|
string outPutFilePath = Application.StartupPath + "\\CalResult\\报表" + DateTime.Parse(dtJdjl.Rows[0]["计算日期"].ToString()).ToString("yyyyMMddhhmmss") + ".xls"; ;
|
|
try
|
|
{
|
|
if (File.Exists(outPutFilePath))
|
|
{
|
|
axFramerControl1.Close();
|
|
Process[] process = Process.GetProcessesByName("EXCEL");
|
|
foreach (Process p in process)
|
|
{
|
|
if (!p.HasExited) // 如果程序没有关闭，结束程序
|
|
{
|
|
p.Kill();
|
|
p.WaitForExit();
|
|
}
|
|
}
|
|
process = Process.GetProcessesByName("et");
|
|
foreach (Process p in process)
|
|
{
|
|
if (!p.HasExited) // 如果程序没有关闭，结束程序
|
|
{
|
|
p.Kill();
|
|
p.WaitForExit();
|
|
}
|
|
}
|
|
|
|
axFramerControl1.Open(outPutFilePath, true, null, null, null);
|
|
return;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
DialogResult dr = MessageBox.Show(ex.Message, "提示");
|
|
|
|
}
|
|
|
|
string fileName = "";
|
|
//取得报表模板文件路径
|
|
string reportModelPath = Application.StartupPath + "\\原始记录模版.xls";
|
|
//导出报表文件名
|
|
string tablename = "原始记录";
|
|
fileName = string.Format("{0}-{1}.xls", tablename, DateTime.Now.ToString("yyyyMMddhhmmss"));
|
|
|
|
HSSFWorkbook hssfworkbook = InitializeWorkbook(reportModelPath);
|
|
|
|
|
|
if (null == hssfworkbook)
|
|
{ return; }
|
|
|
|
// 被检表原始记录表格
|
|
ISheet modelSheet = hssfworkbook.GetSheet("Sheet3");
|
|
// 单元格格式
|
|
ICellStyle CenterStyle = hssfworkbook.CreateCellStyle();
|
|
CenterStyle.WrapText = true;//设置换行这个要先设置
|
|
CenterStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
|
|
if (null == modelSheet)
|
|
{ return; }
|
|
|
|
//检定记录信息
|
|
IRow tmpRow = modelSheet.GetRow(0);
|
|
ICell tmpCell0 = tmpRow.GetCell(0);
|
|
ICell tmpCell1 = tmpRow.GetCell(0);
|
|
try
|
|
{
|
|
for (int i = 0; i < flowStr.Length; i++)
|
|
{
|
|
tmpRow = modelSheet.GetRow(i);
|
|
string[] strTemp = flowStr[i].Split(':');
|
|
|
|
tmpCell0 = tmpRow.GetCell(0);
|
|
tmpCell0.SetCellValue(strTemp[0] == "" ? "/" : strTemp[0]);
|
|
|
|
tmpCell1 = tmpRow.GetCell(1);
|
|
tmpCell1.SetCellValue(strTemp[1] == "" ? "/" : strTemp[1]);
|
|
|
|
|
|
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
|
|
try
|
|
{
|
|
|
|
tmpRow = modelSheet.GetRow(flowStr.Length);
|
|
tmpCell0 = tmpRow.GetCell(0);
|
|
tmpCell0.SetCellValue("检定日期");
|
|
|
|
tmpCell1 = tmpRow.GetCell(1);
|
|
tmpCell1.SetCellValue(dtJdjl.Rows[0]["计算日期"].ToString());
|
|
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
|
|
modelSheet.ForceFormulaRecalculation = true;
|
|
hssfworkbook.SetActiveSheet(0);
|
|
WriteToExcelWithNPOI(outPutFilePath, hssfworkbook);
|
|
hssfworkbook.Clear();
|
|
hssfworkbook.Close();
|
|
axFramerControl1.Open(outPutFilePath, true, null, null, null);
|
|
|
|
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
|
|
}
|
|
}
|
|
|
|
private void ListBox3_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
|
|
try
|
|
{
|
|
DataRow[] dataDr = dtJdjl.Select("ID=" + ListBox3.SelectedValue);
|
|
string flowStr = dataDr[0]["计量点资料"].ToString();
|
|
string NGStr = dataDr[0]["计算结果"].ToString();
|
|
string outPutFilePath = Application.StartupPath + "\\CalResult\\报表" + DateTime.Parse(dataDr[0]["计算日期"].ToString()).ToString("yyyyMMddhhmmss") + ".xls"; ;
|
|
try
|
|
{
|
|
if (File.Exists(outPutFilePath))
|
|
{
|
|
//axFramerControl1.Close();
|
|
Process[] process = Process.GetProcessesByName("EXCEL");
|
|
foreach (Process p in process)
|
|
{
|
|
if (!p.HasExited) // 如果程序没有关闭，结束程序
|
|
{
|
|
p.Kill();
|
|
p.WaitForExit();
|
|
}
|
|
}
|
|
process = Process.GetProcessesByName("et");
|
|
foreach (Process p in process)
|
|
{
|
|
if (!p.HasExited) // 如果程序没有关闭，结束程序
|
|
{
|
|
p.Kill();
|
|
p.WaitForExit();
|
|
}
|
|
}
|
|
|
|
axFramerControl1.Open(outPutFilePath, true, null, null, null);
|
|
return;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
DialogResult dr = MessageBox.Show(ex.Message, "提示");
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
}
|
|
/// <summary>
|
|
/// 把工作簿写到本地文件
|
|
/// </summary>
|
|
private void WriteToExcelWithNPOI(string sNewFileName, HSSFWorkbook tempWB)
|
|
{
|
|
FileStream file = new FileStream(sNewFileName, FileMode.Create);
|
|
tempWB.Write(file);
|
|
file.Close();
|
|
}
|
|
private HSSFWorkbook InitializeWorkbook(string sNewFileName)
|
|
{
|
|
HSSFWorkbook tempWk;
|
|
FileStream file = new FileStream(sNewFileName, FileMode.Open, FileAccess.Read);
|
|
if (null == file)
|
|
{ return null; }
|
|
tempWk = new HSSFWorkbook(file);
|
|
if (null == tempWk)
|
|
{ return null; }
|
|
//create a entry of DocumentSummaryInformation
|
|
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
|
|
dsi.Company = "test";
|
|
tempWk.DocumentSummaryInformation = dsi;
|
|
|
|
//create a entry of SummaryInformation
|
|
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
|
|
si.Subject = "test";
|
|
tempWk.SummaryInformation = si;
|
|
return tempWk;
|
|
}
|
|
//将Byte转换为结构体类型
|
|
public static object ByteToStruct(byte[] bytes, Type type)
|
|
{
|
|
int size = Marshal.SizeOf(type);
|
|
if (size > bytes.Length)
|
|
{
|
|
return null;
|
|
}
|
|
//分配结构体内存空间
|
|
IntPtr structPtr = Marshal.AllocHGlobal(size);
|
|
//将byte数组拷贝到分配好的内存空间
|
|
Marshal.Copy(bytes, 0, structPtr, size);
|
|
//将内存空间转换为目标结构体
|
|
try
|
|
{
|
|
object obj = Marshal.PtrToStructure(structPtr, type);
|
|
//释放内存空间
|
|
Marshal.FreeHGlobal(structPtr);
|
|
return obj;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return null;
|
|
|
|
}
|
|
|
|
}
|
|
private void checkBox1_CheckedChanged(object sender, EventArgs e)
|
|
{
|
|
splitContainer2.Panel1Collapsed = !checkBox1.Checked;
|
|
label3.Visible = checkBox1.Checked;
|
|
label4.Visible = checkBox1.Checked;
|
|
dateTimePicker1.Visible = checkBox1.Checked;
|
|
dateTimePicker2.Visible = checkBox1.Checked;
|
|
button1.Visible = checkBox1.Checked;
|
|
}
|
|
|
|
private void splitContainer3_Panel2_Paint(object sender, PaintEventArgs e)
|
|
{
|
|
|
|
}
|
|
}
|
|
}
|