效果如下:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using TX.DataAccess;
using Microsoft.Office.Interop.Excel;
namespace LT.BC_Routine.FormLibraries
{
public partial class frmOprProcessPrint : Form
{
List<String> parmList;
SqlExecuteMaster sqlcon = new SqlExecuteMaster();
Microsoft.Office.Interop.Excel.Application excel;
Workbook workbook;
Worksheet worksheet;
int flag = 0;
string balanceHead = "";
public frmOprProcessPrint(List<string> list)
{
InitializeComponent();
beginDate.Text = list[3];
endDate.Text = list[3];
parmList = list;
}
private void btnOprPrint_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
flag = 0;
string beginDate = this.beginDate.Value.ToString("yyyy-MM-dd");
string endDate = this.endDate.Value.ToString("yyyy-MM-dd");
string prodId = (string)this.Tag;
string strSql = "EXEC spBC_GetOBProcess @StartDate='" + beginDate + "',@EndDate='" + endDate + "',@ProdId='" + prodId + "'";
System.Data.DataTable dt = sqlcon.GetDataTable(strSql);
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
excel = new Microsoft.Office.Interop.Excel.Application();
excel.SheetsInNewWorkbook = 1;
workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
worksheet = (Worksheet)workbook.Worksheets[1];
DataSetToExcel(dt);
if (dt.Rows.Count > 0)
{
excel.Visible = true;
}
Cursor = Cursors.Default;
}
private void btnEmpOprPrint_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
flag = 0;
string beginDate = this.beginDate.Value.ToString("yyyy-MM-dd");
string endDate = this.endDate.Value.ToString("yyyy-MM-dd");
string prodId = (string)this.Tag;
string strSql = "EXEC spBC_GetOBProcessByEmp @ActionType='query1',@StartDate='" + beginDate + "',@EndDate='" + endDate + "',@ProdId='" + prodId + "'";
System.Data.DataTable dt = sqlcon.GetDataTable(strSql);
DataRowCollection rows = dt.Rows;
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
excel = new Microsoft.Office.Interop.Excel.Application();
excel.SheetsInNewWorkbook = 1;
workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
worksheet = (Worksheet)workbook.Worksheets[1];
string strSql2 = "EXEC spBC_GetOBProcessByEmp @ActionType='query2',@StartDate='" + beginDate + "',@EndDate='" + endDate + "',@ProdId='" + prodId + "'";
System.Data.DataTable dt2 = sqlcon.GetDataTable(strSql2);
balanceHead = dt2.Rows[0][0].ToString();
DataSetToExcelForEmp(dt);
if (dt.Rows.Count > 0)
{
excel.Visible = true;
}
Cursor = Cursors.Default;
}
private void btnBothPrint_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
flag = 1;
string beginDate = this.beginDate.Value.ToString("yyyy-MM-dd");
string endDate = this.endDate.Value.ToString("yyyy-MM-dd");
string prodId = (string)this.Tag;
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
excel = new Microsoft.Office.Interop.Excel.Application();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
excel.SheetsInNewWorkbook = 2;
workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
worksheet = (Worksheet)workbook.Worksheets[1];
//sheet1
string strSql = "EXEC spBC_GetOBProcess @StartDate='" + beginDate + "',@EndDate='" + endDate + "',@ProdId='" + prodId + "'";
System.Data.DataTable dt = sqlcon.GetDataTable(strSql);
DataSetToExcel(dt);
//sheet2
string strSql2 = "EXEC spBC_GetOBProcessByEmp @ActionType='query1',@StartDate='" + beginDate + "',@EndDate='" + endDate + "',@ProdId='" + prodId + "'";
System.Data.DataTable dt2 = sqlcon.GetDataTable(strSql2);
worksheet = (Worksheet)workbook.Sheets.Add(miss, worksheet, miss, miss);
//worksheet = (Worksheet)workbook.Worksheets[2];
string strSql3 = "EXEC spBC_GetOBProcessByEmp @ActionType='query2',@StartDate='" + beginDate + "',@EndDate='" + endDate + "',@ProdId='" + prodId + "'";
System.Data.DataTable dt3 = sqlcon.GetDataTable(strSql3);
balanceHead = dt3.Rows[0][0].ToString();
DataSetToExcelForEmp(dt2);
if (dt.Rows.Count > 0 || dt2.Rows.Count>0)
{
excel.Visible = true;
}
excel.Visible = true;
Cursor = Cursors.Default;
}
public bool DataSetToExcel(System.Data.DataTable dt)
{
System.Data.DataTable dataTable = dt;
int rowNumber = dataTable.Rows.Count;
int columnNumber = dataTable.Columns.Count;
if (rowNumber == 0)
{
MessageBox.Show("没有任何数据可以导入到Excel文件!");
return false;
}
//excel.Microsoft.Office.Interop.Excel.Application.Workbooks.Add(true);
this.setHeadValue(excel, worksheet);
Range range = worksheet.get_Range(worksheet.Cells[2, 6], worksheet.Cells[2, 6]);
//range.ClearContents();
//range.MergeCells = true;
//range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.Value2 = dt.Compute("avg(BalanceQty)", "1=1").ToString();
range.Font.Bold = false;
Object[] columnHead = new Object[] { "序号", "工序名称", "工序名称【柬文】", " 已回收数量", "已回收套数", "差异数" };
for (int j = 0; j < 6; j++)
{
excel.Cells[3, j + 1] = columnHead[j];
}
//填充数据
for (int c = 0; c < rowNumber; c++)
{
if ((c + 2) % 2 == 0)
{
worksheet.get_Range(worksheet.Cells[c + 4, 1], worksheet.Cells[c + 4, 6]).Cells.Interior.Color = System.Drawing.Color.FromArgb(224, 224, 224).ToArgb();
}
for (int j = 0; j < 6; j++)
{
worksheet.Cells[c + 4, j + 1] = dataTable.Rows[c].ItemArray[j];
}
}
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNumber + 3, 6]).Borders.LineStyle
= XlLineStyle.xlContinuous;//添加border
worksheet.Columns.AutoFit();
((Range)worksheet.Columns["F", System.Type.Missing]).ColumnWidth = 13;
return true;
}
public bool DataSetToExcelForEmp(System.Data.DataTable dt)
{
//System.Data.DataTable dataTable = dataSet.Tables[0];
int rowNumber = dt.Rows.Count;
int columnNumber = dt.Columns.Count;
if (rowNumber == 0)
{
MessageBox.Show("没有任何数据可以导入到Excel文件!");
return false;
}
// int BalanceQty = int.Parse(dt.Compute("avg(BalanceQty)", "1=1").ToString());//平衡数
this.setEmpHeadValue(excel, worksheet);
Range range = worksheet.get_Range(worksheet.Cells[2, 11], worksheet.Cells[2, 11]);
range.ClearContents();
range.MergeCells = true;
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.Value2 = balanceHead;
range.Font.Bold = false;
Object[] columnHead = new Object[] { "工号", "序号", "工序名称", "工序名称【柬文】", "数量", "套数", "差异数", "差异秒", " 产出秒", "累计产出秒", "平衡表现" };
for (int j = 0; j < 11; j++)
{
worksheet.Cells[3, j + 1] = columnHead[j];
}
var query = from t in dt.AsEnumerable()
group t by new { EmpNo = t.Field<string>("EmpNo") } into m
select new
{
EmpNo = m.Key.EmpNo,
Num = m.Count(),
AccumulativeQty = m.Sum(p => p.Field<int>("outputSecond")),
PersonalPerformance = m.Sum(p => p.Field<int>("diffSecond"))
};
int lineNum = 0;
foreach (var item in query.ToList())
{
Range range2, range3, range4;
string EmpNo = item.EmpNo;
int AccumulativeQty = item.AccumulativeQty;
int PersonalPerformance = item.PersonalPerformance;
//第一列
range2 = worksheet.get_Range(worksheet.Cells[lineNum + 3 + 1, 1], worksheet.Cells[lineNum + 3 + item.Num, 1]);
range2.ClearContents();//清空要合并的区域
range2.MergeCells = true;
range2.NumberFormat = "@";//设置单元格为文本格式
range2.Value2 = EmpNo;
range2.Font.Size = 14;
range2.VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直居中
DataRow[] dr = dt.Select("EmpNo='" + EmpNo + "'");
System.Data.DataTable dtDetail = ToDataTable(dr);
int diffSecondTotal = 0;
int outputSecondTotal = 0;
//填充数据
for (int c = 0; c < dtDetail.Rows.Count; c++)
{
if ((c + 2) % 2 == 0)
{
worksheet.get_Range(worksheet.Cells[c + lineNum + 4, 2], worksheet.Cells[c + lineNum + 4, 9]).Cells.Interior.Color = System.Drawing.Color.FromArgb(224, 224, 224).ToArgb();
}
worksheet.Cells[c + lineNum + 4, 2] = dtDetail.Rows[c]["OrderNum"].ToString();
worksheet.Cells[c + lineNum + 4, 3] = dtDetail.Rows[c]["OperationName"].ToString();
worksheet.Cells[c + lineNum + 4, 4] = dtDetail.Rows[c]["OperationName_en"].ToString();//
worksheet.Cells[c + lineNum + 4, 5] = dtDetail.Rows[c]["ReceivedQty"].ToString();//回收件数
worksheet.Cells[c + lineNum + 4, 6] = dtDetail.Rows[c]["QtyBySet"].ToString();//套数(回收件数/工序数量)
worksheet.Cells[c + lineNum + 4, 7] = dtDetail.Rows[c]["diffQty"].ToString();//差异数(已回收套数-平衡数)
worksheet.Cells[c + lineNum + 4, 8] = dtDetail.Rows[c]["diffSecond"].ToString(); //差异秒数(差异数*单套秒数)
worksheet.Cells[c + lineNum + 4, 9] = dtDetail.Rows[c]["outputSecond"].ToString();//产出秒数(回收数量*单件秒数)
}
range3 = worksheet.get_Range(worksheet.Cells[lineNum + 3 + 1, 10], worksheet.Cells[lineNum + 3 + item.Num, 10]);
range3.ClearContents();
range3.MergeCells = true;
range3.Value2 = AccumulativeQty;
range3.Font.Size = 14;
range3.Font.Bold = false;
range3.VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直居中
range4 = worksheet.get_Range(worksheet.Cells[lineNum + 3 + 1, 11], worksheet.Cells[lineNum + 3 + item.Num, 11]);
range4.ClearContents();
range4.MergeCells = true;
range4.Value2 = PersonalPerformance;
range4.Font.Bold = false;
range4.VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直居中
range4.Font.Size = 14;
range4.Font.Bold = false;
lineNum += item.Num;
}
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNumber + 3, 11]).Borders.LineStyle
= XlLineStyle.xlContinuous;//添加border
worksheet.Columns.AutoFit();
//((Range)worksheet.Columns["F", System.Type.Missing]).ColumnWidth = 13;
return true;
}
private System.Data.DataTable ToDataTable(DataRow[] rows)
{
if (rows == null || rows.Length == 0) return null;
System.Data.DataTable tmp = rows[0].Table.Clone(); // 复制DataRow的表结构
foreach (DataRow row in rows)
{
tmp.ImportRow(row); // 将DataRow添加到DataTable中
}
return tmp;
}
public void setHeadValue(Microsoft.Office.Interop.Excel.Application excel, Worksheet worksheet)
{
Range range, range2, range3, range4, range5, range6, range7;
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 2]);
range.ClearContents();//清空要合并的区域
range.MergeCells = true;
//range.NumberFormat = "@";//设置单元格为文本格式
range.Value2 = "员工工序进度表";
range.Font.Size = 14;
range.Font.Bold = true;
string beginDate = this.beginDate.Value.ToString("yyyy/MM/dd");
string endDate = this.endDate.Value.ToString("yyyy/MM/dd");
worksheet.Cells[1, 3] = "款号";
worksheet.Cells[1, 4] = parmList[2];
worksheet.Cells[1, 5] = "日期";
worksheet.Cells[1, 6] = beginDate + "\n" + endDate;
worksheet.Cells[2, 1] = "组别";
worksheet.Cells[2, 2] = parmList[0];
worksheet.Cells[2, 3] = "MO";
worksheet.Cells[2, 4] = parmList[1];
worksheet.Cells[2, 5] = "平衡数";
range2 = worksheet.get_Range(worksheet.Cells[1, 3], worksheet.Cells[1, 3]);
range2.HorizontalAlignment = XlHAlign.xlHAlignRight;
range2.Font.Bold = true;
range2.Font.Size = 14;
range3 = worksheet.get_Range(worksheet.Cells[1, 5], worksheet.Cells[1, 5]);
range3.HorizontalAlignment = XlHAlign.xlHAlignRight;
range3.Font.Bold = true;
range4 = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 1]);
range4.HorizontalAlignment = XlHAlign.xlHAlignRight;
range4.Font.Bold = true;
range4.Font.Size = 14;
range5 = worksheet.get_Range(worksheet.Cells[2, 3], worksheet.Cells[2, 3]);
range5.HorizontalAlignment = XlHAlign.xlHAlignRight;
range5.Font.Bold = true;
range5.Font.Size = 14;
range6 = worksheet.get_Range(worksheet.Cells[2, 5], worksheet.Cells[2, 5]);
range6.HorizontalAlignment = XlHAlign.xlHAlignRight;
range6.Font.Bold = true;
range6.Font.Size = 14;
range7 = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 6]);
range7.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range7.Font.Bold = true;
}
public void setEmpHeadValue(Microsoft.Office.Interop.Excel.Application excel, Worksheet worksheet)
{
Range range, range2, range3, range4, range5, range6, range7, range8, range9;
string beginDate = this.beginDate.Value.ToString("yyyy/MM/dd");
string endDate = this.endDate.Value.ToString("yyyy/MM/dd");
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[2, 2]);
range.ClearContents();//清空要合并的区域
range.MergeCells = true;
//range.NumberFormat = "@";//设置单元格为文本格式
range.Value2 = "员工工序进度表";
range.Font.Size = 11;
range.Font.Bold = true;
range2 = worksheet.get_Range(worksheet.Cells[1, 5], worksheet.Cells[1, 7]);
range2.ClearContents();
range2.MergeCells = true;
range2.Value2 = "日期";
range2.HorizontalAlignment = XlHAlign.xlHAlignRight;
range2.Font.Size = 14;
range2.Font.Bold = true;
range3 = worksheet.get_Range(worksheet.Cells[2, 5], worksheet.Cells[2, 7]);
range3.ClearContents();
range3.MergeCells = true;
range3.Value2 = "款号";
range3.HorizontalAlignment = XlHAlign.xlHAlignRight;
range3.Font.Size = 14;
range3.Font.Bold = true;
range3 = worksheet.get_Range(worksheet.Cells[1, 10], worksheet.Cells[1, 11]);
range3.ClearContents();
range3.MergeCells = true;
range3.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range3.Value2 = endDate;
range3.Font.Bold = false;
range4 = worksheet.get_Range(worksheet.Cells[1, 9], worksheet.Cells[1, 9]);
range4.ClearContents();
range4.MergeCells = true;
range4.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range4.Value2 = "'---";
range4.Font.Size = 14;
range4.Font.Bold = true;
range5 = worksheet.get_Range(worksheet.Cells[2, 8], worksheet.Cells[2, 9]);
range5.ClearContents();
range5.MergeCells = true;
range5.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range5.Value2 = parmList[2];
range5.Font.Bold = false;
range6 = worksheet.get_Range(worksheet.Cells[2, 10], worksheet.Cells[2, 10]);
range6.ClearContents();
range6.HorizontalAlignment = XlHAlign.xlHAlignRight;
range6.Value2 = "平衡数";
range6.Font.Size = 14;
range6.Font.Bold = true;
worksheet.Cells[1, 3] = "组别";
worksheet.Cells[1, 4] = parmList[0];// 组别
worksheet.Cells[1, 8] = beginDate;
worksheet.Cells[2, 3] = "MO";
worksheet.Cells[2, 4] = parmList[1]; //MO
worksheet.Cells[2, 10] = "平衡数";
//workbook.worksheet.Cells[2, 11] = "";
range7 = worksheet.get_Range(worksheet.Cells[1, 3], worksheet.Cells[1, 3]);
range7.HorizontalAlignment = XlHAlign.xlHAlignRight;
range7.Font.Bold = true;
range7.Font.Size = 14;
range8 = worksheet.get_Range(worksheet.Cells[2, 3], worksheet.Cells[2, 3]);
range8.HorizontalAlignment = XlHAlign.xlHAlignRight;
range8.Font.Bold = true;
range8.Font.Size = 14;
range9 = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 11]);
range9.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range9.Font.Bold = true;
}
}
}