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();//强行销毁
        }
Exemple #2
0
        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);
        }