//原模板文件
string pFromPath = System.AppDomain.CurrentDomain.BaseDirectory + @"Template\电机出厂试验记录表.xls";
//目的文件
string pToPath = System.AppDomain.CurrentDomain.BaseDirectory + @"PrintRecords\" + "产品出库表" + DateTime.Now.ToString("yyyyMMddmmss") + ".xls";
#region 保存出库列表Excel
try
{
//复制模板文件,按时间格式重命名
System.IO.File.Copy(pFromPath, pToPath, true);
Excel.Workbooks objBooks;
Excel.Sheets objSheets;
Excel._Worksheet objSheet;
Excel.Range range;
if (objApp == null)
{
objApp = new Excel.Application();
}
//打开复制的模板文件，得到WorkBook对象
objBooks = objApp.Workbooks;
objBook = objBooks.Open(pToPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//得到WorkSheet对象
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
//设置表名称
objSheet.Name = DateTime.Now.ToString("yyyy-MM-dd") + "电动机出厂试验记录表";
#region 写入数据
//存放从数据库中读取的数据
System.Data.DataTable excelDataTabel;
//读取标准参数
dataAcc.SelectString = "SELECT 电动机型号,额定转矩,额定电压,额定电流,额定转速 FROM 电动机标准参数表 WHERE 电动机型号='" + cboType.Text.Trim() + "' ";
excelDataTabel = dataAcc.GetDataTable();
//写型号
objSheet.Cells[2, 2] = "'" + cboType.Text.Trim();
//string strType, strEDDY, strEDZJ, strEDDL, strEDZS;
//写入表头信息
if (excelDataTabel.Rows.Count > 0)
{
objSheet.Cells[2, 6] = "'" + excelDataTabel.Rows[0]["额定电压"].ToString();
objSheet.Cells[2, 9] = "'" + excelDataTabel.Rows[0]["额定转矩"].ToString();
objSheet.Cells[3, 6] = "'" + excelDataTabel.Rows[0]["额定电流"].ToString();
objSheet.Cells[3, 9] = "'" + excelDataTabel.Rows[0]["额定转速"].ToString();
}
//临时存储电机编号
string[] strNoTemp = strNo.Split(',');
//读取测试记录
strNo = " 电动机编号='" + strNo.Replace(",", "' OR 电动机编号='") + "' ";
dataAcc.SelectString = "SELECT 电动机编号,正转电机电流,反转电机电流,正转电机转速,反转电机转速,门限电压,绝缘电阻 FROM 电动机电气特性表 WHERE 电动机型号='" + cboType.Text.Trim() + "' AND (" + strNo + ") AND 测试类型='验收'";
excelDataTabel = dataAcc.GetDataTable();
//起始行
int top = 6;
//起始列
int left = 1;
//设置标题行
//使标题行在打印时出现在每一页
objSheet.PageSetup.PrintTitleRows = "$1:$" + Convert.ToString(top - 1);
//设置页脚
objSheet.PageSetup.LeftFooter = "实验员：" + frmMain.userInfo[0]["姓名"].ToString(); //左页脚
objSheet.PageSetup.CenterFooter = @"第 &P 页/共 &N 页"; //中页脚
objSheet.PageSetup.RightFooter = "日期：" + DateTime.Now.Date.ToString("yyyy-MM-dd"); //右页脚
int rows = 0; //记录写入的行数
if (excelDataTabel.Rows.Count < 1)
{
//如果不存在相关编号电动机测试记录，写入电动机编号
if (strNoTemp.Length > 0)
{
for (int i = 0; i < strNoTemp.Length; i++)
{
objSheet.Cells[top + i, left] = "'" + Convert.ToString(i + 1); //序号
objSheet.Cells[top + i, left + 1] = "'" + strNoTemp[i]; //电机编号
rows++;
//送显进度
progressBarToExcel.Value = Convert.ToInt32(i / Convert.ToDouble(strNoTemp.Length) * 100);
}
}
}
else
{
//循环写入测试记录
for (int i = 0; i < excelDataTabel.Rows.Count; i++)
{
objSheet.Cells[top + i, left] = "'" + Convert.ToString(i + 1);
objSheet.Cells[top + i, left + 1] = "'" + excelDataTabel.Rows[i]["电动机编号"].ToString();
objSheet.Cells[top + i, left + 2] = "'" + excelDataTabel.Rows[i]["正转电机电流"].ToString();
objSheet.Cells[top + i, left + 3] = "'" + excelDataTabel.Rows[i]["反转电机电流"].ToString();
objSheet.Cells[top + i, left + 4] = "'" + excelDataTabel.Rows[i]["正转电机转速"].ToString();
objSheet.Cells[top + i, left + 5] = "'" + excelDataTabel.Rows[i]["反转电机转速"].ToString();
objSheet.Cells[top + i, left + 6] = "'" + excelDataTabel.Rows[i]["门限电压"].ToString();
objSheet.Cells[top + i, left + 7] = "'" + excelDataTabel.Rows[i]["绝缘电阻"].ToString();
rows++;
//送显进度
progressBarToExcel.Value = Convert.ToInt32(i / Convert.ToDouble(excelDataTabel.Rows.Count) * 100);
}
}
////设置边框
range = objSheet.get_Range(objSheet.Cells[top, left], objSheet.Cells[top + rows - 1, 9]);
//线形
range.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
//宽度
range.Cells.Borders.Weight = 2;
//行高
range.RowHeight = 20;
//字体大小
range.Font.Size = 12;
range = null;
//保存并关闭工作表
objBook.Save();
objBook.Close(Type.Missing, Type.Missing, Type.Missing);
//清除对象
//关闭工作薄
objBooks.Close();
//退出应用程序
objApp.Quit();
objBook = null;
objBooks = null;
objSheet = null;
objSheets = null;
objApp = null;
GC.Collect();
}
catch (Exception ex)
{
MessageBox.Show("生成Excel文件失败，请确保MS Office Excel和数据库连接正常！\n" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
GC.Collect();
this.btnOutput.Enabled = true;
this.Cursor = Cursors.Default;
progressBarToExcel.Visible = false;
progressBarToExcel.Value = 0;
}
#endregion
#endregion
