private void btn_ExportExcel_Click(object sender, EventArgs e)
{
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
CExportExcel.HVCenterAlign(worksheet, 1, 1, dataGridView1.Rows.Count + 1, dataGridView1.ColumnCount + 1);
//写入标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "导出成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
}
private void Export_Excel_Click(object sender, EventArgs e)
{
//导出excel
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
string date = DateTime.Now.ToString("yyyy-MM-dd");
CExportExcel.MergeCells(worksheet, 1, 1, 1, 7, date + " 生产情况");
CExportExcel.MergeCells(worksheet, 2, 1, 22, 1, "白班");
CExportExcel.MergeCells(worksheet, 23, 1, 42, 1, "晚班");
worksheet.Cells[2, 2] = "机器号";
worksheet.Cells[2, 3] = "开车时刻";
worksheet.Cells[2, 4] = "停车时刻";
worksheet.Cells[2, 5] = "效率";
worksheet.Cells[2, 6] = "产量";
worksheet.Cells[2, 7] = "开车时间间隔";
worksheet.Cells[2, 8] = "停车时间间隔";
worksheet.Cells[2, 9] = "运行时长";
worksheet.Cells[2, 10] = "停车时长";
for (int i = 1; i <= 20; i++)
{
worksheet.Cells[i + 2, 2] = "M" + i.ToString();
worksheet.Cells[i + 22, 2] = "M" + i.ToString();
}
CExportExcel.HVCenterAlign(worksheet, 2, 2, 42, 10);
//CExportExcel.ExportExcel();
string curdate = dateTimePicker1.Text.ToString();
string beforedate = dateTimePicker1.Value.AddDays(-1).ToString("yyyy-MM-dd");
for (int machine = 1; machine <= 20; machine++)
{
string sql = "select Speed1,Date from " + "M" + machine.ToString() + " where Date between" + "'" + beforedate + " 07:30:00" + "'"
+ " and " + "'" + beforedate + " 19:30:00" + "'";
string nightsql = "select Speed1,Date from " + "M" + machine.ToString() + " where Date between" + "'" + beforedate + " 19:30:00" + "'"
+ " and " + "'" + curdate + " 07:30:00" + "'";
//string sql = "select Speed1,Date from " + "M" + machine.ToString() + " where Date between" + "'" + curdate + " 07:30:00" + "'"
// + " and " + "'" + curdate + " 19:30:00" + "'";
//string nightsql = "select Speed1,Date from " + "M" + machine.ToString() + " where Date between" + "'" + curdate + " 19:30:00" + "'"
// + " and " + "'" + curdate + " 20:30:00" + "'";
DataSet ds = myDatabase.getDataSet(sql, "CS");
DataSet nightds = myDatabase.getDataSet(nightsql, "nightCS");
int count;
int nightcount;
count = ds.Tables[0].Rows.Count;
nightcount = nightds.Tables[0].Rows.Count;
//白班
int[] speed1 = new int[count];
string[] Date = new string[count];
//晚班
int[] nightspeed1 = new int[nightcount];
string[] nightDate = new string[nightcount];
int i = 0, d = 0;
int i1 = 0, d1 = 0;
//效率
double dayEfficiency = 0.0;
double nightEfficiency = 0.0;
foreach (DataRow row in ds.Tables[0].Rows) //遍历所有行
{
speed1[i++] = int.Parse(row[0].ToString()); //row[0]表示第一列
Date[d++] = DateTime.Parse(row[1].ToString()).ToString("HH:mm");
if (speed1[i - 1] != 0)
{
dayEfficiency++;
}
}
if (count == 0)
{
worksheet.Cells[machine + 2, 5] = "";
}
else
{
worksheet.Cells[machine + 2, 5] = (dayEfficiency / count).ToString("P");
worksheet.Cells[machine + 2, 9] = (dayEfficiency / count * 12).ToString("f2");
worksheet.Cells[machine + 2, 10] = (12 - dayEfficiency / count * 12).ToString("f2");
}
foreach (DataRow row in nightds.Tables[0].Rows) //遍历所有行
{
nightspeed1[i1++] = int.Parse(row[0].ToString()); //row[0]表示第一列
nightDate[d1++] = DateTime.Parse(row[1].ToString()).ToString("HH:mm");
if (nightspeed1[i1 - 1] != 0)
{
nightEfficiency++;
}
}
if (nightcount == 0)
{
worksheet.Cells[machine + 22, 5] = "";
}
else
{
worksheet.Cells[machine + 22, 5] = (nightEfficiency / nightcount).ToString("P");
worksheet.Cells[machine + 22, 9] = (nightEfficiency / nightcount * 12).ToString("f2");
worksheet.Cells[machine + 22, 10] = (12 - nightEfficiency / nightcount * 12).ToString("f2");
}
//////////////////////////////////////////////////////////////////////////////////////
}
MyDatabase.mysqlConn.Close();
MyDatabase.mysqlConn.Dispose();
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (saveFileName != null)
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (System.Exception ex)
{
MessageBox.Show("导出文件可能出错" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("导出Excel成功", "提示", MessageBoxButtons.OK);
}