private static void PresentResult(Query query, Report report, int profileCounter, int cellOffset)
        {
            // This method shall include a verification of empty columns. If the user has selected more than one profile
            // the second query shall be presented on the right of the first query result in Excel.

            Microsoft.Office.Interop.Excel.Application currentApp = GA_Excel2007.Globals.ThisAddIn.Application;
            Worksheet activeSheet = currentApp.ActiveSheet as Worksheet;

            if (currentApp.ActiveSheet != null)
            {
                int dataLength = 2;
                if (report.Data != null)
                {
                    dataLength = report.Data.GetLength(1);
                }

                int activeRow    = currentApp.ActiveCell.Row;
                int activeColumn = currentApp.ActiveCell.Column + cellOffset;

                /*if (currentApp.ActiveCell.Cells.Value2 != null)
                 * {
                 *      activeColumn = ActiveColumn(profileCounter, dataLength);
                 * }*/


                object[] queryInformation = GetQueryInformation(query, report, profileCounter);

                int infoRows = queryInformation.GetLength(0);

                if (report.Data != null)
                {
                    dataLength = report.Data.GetLength(1);
                    Range dataRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows + 1, activeColumn],
                                                           activeSheet.Cells[activeRow + infoRows + report.Data.GetLength(0), activeColumn + report.Data.GetLength(1) - 1]);
                    dataRange.Value2 = report.Data;

                    Range headerRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows, activeColumn],
                                                             activeSheet.Cells[activeRow + infoRows, activeColumn + report.Headers.GetLength(1) - 1]);
                    headerRange.Value2    = report.Headers;
                    headerRange.Font.Bold = true;
                }

                Range queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn],
                                                                   activeSheet.Cells[activeRow, activeColumn + dataLength - 1]);
                queryInformationRange.Font.Italic    = true;
                queryInformationRange.MergeCells     = true;
                queryInformationRange.Borders.Weight = XlBorderWeight.xlThin;

                queryInformationRange.Value2 = queryInformation;
                //int height = (int)queryInformationRange.Height; queryInformationRange.RowHeight = height;
            }
        }
Exemple #2
0
        // These examples are in Named and Optional, main topic. Can be deleted here.
        static void TestExcelCom()
        {
            var excelApp = new Microsoft.Office.Interop.Excel.Application();
            var myFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatAccounting1;

            // Using named and optional arguments, you can supply arguments for
            // only the parameters for which you do not want to use the default
            // value, and omit arguments for the other parameters. In the following
            // call, a value is sent for only one parameter.
            excelApp.get_Range("A1", "B4").AutoFormat(Format: myFormat);

            // In Visual C# 2008 and earlier versions, you need to supply an argument for
            // every parameter. The following call supplies a value for the first
            // parameter, and sends a placeholder value for the other six. The
            // default values are used for those parameters.
            excelApp.get_Range("A1", "B4").AutoFormat(myFormat, Type.Missing,
                                                      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        }
Exemple #3
0
        //Función para exportar a excel
        private void exportarAExcel(DataGridView dgvCierre)
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

            excel.Application.Workbooks.Add(true);

            int iIndiceColumna = 0;

            excel.Columns.ColumnWidth = 20;
            excel.Cells[1, 1]         = "INFORME DE VENTAS POR CATEGORIAS";
            excel.Cells[2, 1]         = "DESDE " + sfechaInicio + " A " + sfechaFin;

            foreach (DataGridViewColumn col in dgvCierre.Columns)
            {
                iIndiceColumna++;
                if (iIndiceColumna == 1)
                {
                    excel.Cells[1, iIndiceColumna].ColumnWidth = 40;
                }

                excel.Cells[5, iIndiceColumna] = col.HeaderText;
                excel.Cells[5, iIndiceColumna].Interior.Color = Color.Yellow;
            }

            int iIndiceFila = 5;

            foreach (DataGridViewRow row in dgvCierre.Rows)
            {
                iIndiceFila++;

                iIndiceColumna = 0;

                foreach (DataGridViewColumn col in dgvCierre.Columns)
                {
                    iIndiceColumna++;
                    excel.Cells[iIndiceFila + 1, iIndiceColumna] = row.Cells[col.Name].Value;
                    excel.Cells[iIndiceFila + 1, iIndiceColumna].BorderAround();
                }
            }

            excel.get_Range("A5", "C5").BorderAround();
            excel.Visible = true;
        }
Exemple #4
0
        //生成Excel
        private bool ExportToExcel(DataTable dt)
        {
            bool result = false;
            this.UseWaitCursor = true;
            if (dt.Rows.Count > 65536)
            {
                MessageBox.Show("數據記錄太多(最多不能超過65536條),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return result;
            }
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                labMsg.Text = "Excel正在匯出中…………";

                if (xlApp == null)
                {
                    throw new Exception("无法创建Excel对象,可能您的机器未安装Excel");
                }
                Excel.Workbooks workbooks = xlApp.Workbooks;
                Excel.Workbook workbook = workbooks.Add(true);
                Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
                worksheet.Name = "過膠機生產日報表";

                //worksheet.QueryTables.Add

                #region worksheet的格式
                worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印
                worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印
                worksheet.PageSetup.Zoom = false; //設置頁面縮放比例  Zoom必須設為False FitToPagesWide才有效
                worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁
                worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中
                worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102);
                worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787);
                worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787);
                worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787);
                worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126);
                worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126);
                xlApp.Visible = false;
                #endregion

                #region 列名
                #region 循環填充列名,很慢,已經REMARK
                //for (i = 1; i <= dt.Columns.Count - 1; i++)
                //{
                //    if (i == 11 || i == 12 || i == 13)
                //    {
                //        MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
                //        xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Substring(8, 3);
                //    }
                //    else if (i == 14 || i == 15 || i == 16 || i == 17)
                //    {
                //        MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
                //        xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim().Substring(8);
                //    }
                //    else if (i >= 23 && i <= 31)
                //    {
                //        MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
                //        xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim();
                //    }
                //    else
                //    {
                //        MergeColumn(xlApp, xlApp.Cells[RowIndex, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
                //        xlApp.Cells[RowIndex, i] = dt.Columns[i].ColumnName;
                //    }
                //}
                #endregion
                int RowIndex = 2;
                MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 30], "過膠機生產日報表", 16, 65535, 0); //大標題
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 30], "常用物料使用情況", 12, 65535, 0);
                RowIndex = RowIndex + 1;
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "上工序次品數", 12, 65535, 7);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "上工序廢品數", 12, 65535, 7);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "累計數量", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "級別", 12, 65535, 5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 23], "物料編號", 12, 65535, 12);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "物料名稱", 12, 65535, 35);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "採購單號", 12, 65535, 12);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "生產批次", 12, 65535, 12);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "實際用量", 12, 65535, 8.5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "單位", 12, 65535, 5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "寬度(mm)", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "長度(m)", 12, 65535, 9);
                #endregion

                #region 填充數據 向Excel中逐行逐列写入表格中的数据
                RowIndex = RowIndex + 1; //數據開始的行號
                int ExcelCurrectRow = RowIndex;  //當前行
                int dtRowNO;
                int realRowNO = 0;//dt實際行
                int dtKeyRowNO = RowIndex;//NO:4
                int megreCount = 0;
                int dtMetrialRowNO = 23;//物料編號所在列
                int tempRow;
                int tempCol;
                #region 較慢的填充數據方法 已經REMARK
                //for (int row = 0; row < dt.Rows.Count; row++)
                //{
                //    //DataRow[] rows = dt.Select(dt.Rows[row][0].ToString());
                //    for (int col = 1; col < dt.Columns.Count; col++)
                //    {
                //        if (row > 0)  //班次內工程ID相等 col22之後不合併
                //        {
                //            if (dt.Rows[row - 1][0].ToString() == dt.Rows[row][0].ToString() && col < 22)
                //            {
                //                CellStyle(xlApp, xlApp.Cells[currectRow, col], xlApp.Cells[RowIndex, col], 10, -4142, true);
                //            }
                //            else
                //            {
                //                CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false);
                //                xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString();
                //                if (col < 22) currectRow = RowIndex;
                //            }
                //        }
                //        else
                //        {
                //            CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false);
                //            xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString();
                //        }
                //    }
                //    RowIndex++;
                //    //if (RowIndex == 15) goto range; // for 測試
                //}
                #endregion

                DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" });
                for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++)
                {
                    DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' ");
                    megreCount = currectKeyRow.Length;
                    for (tempRow = 0; tempRow < megreCount; tempRow++)
                    {
                        if (tempRow == 0)
                        {
                            for (tempCol = 1; tempCol < dtMetrialRowNO; tempCol++) //先合併物料編號之前的單元格
                            {
                                CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true);
                            }
                            realRowNO += megreCount;
                        }
                        for (tempCol = dtMetrialRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料
                        {
                            CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false);
                        }
                        dtKeyRowNO++;
                    }
                    //dtKeyRowNO++;
                    //if (realRowNO > dt.Rows.Count) break;

                    /*
                     *  DataTable pjsf_iPJSFID = dt.DefaultView.ToTable(true, new string[] { "pjsf_iPJSFID" });
                List<Thread> threads = new List<Thread>();
                for (dtRowNO = 0; dtRowNO < pjsf_iPJSFID.Rows.Count; dtRowNO++)
                {
                     */

                }

                #endregion

                #region 設置Sheet邊框
                //range:
                Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]);
                SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                #endregion

                Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]);
                DataFormat.Font.Name = "楷体";
                DataFormat.Font.Size = 10;
                Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]);
                IntFormat.NumberFormatLocal = "0.00";

                #region savecode
                Object missing = Missing.Value;
                SaveFileDialog saveForm = new SaveFileDialog();
                saveForm.Filter = "Execl files (*.xls)|*.xls";
                saveForm.FilterIndex = 0;
                saveForm.FileName = "過膠機生產日報表";
                if (saveForm.ShowDialog() == DialogResult.OK)
                {
                    string fileName = saveForm.FileName;
                    workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                                     missing, missing, missing, missing, missing);

                }
                #endregion

                result = true;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                labMsg.Text = "";
                this.UseWaitCursor = false;
                xlApp.Quit();
                IntPtr t = new IntPtr(xlApp.Hwnd);          //杀死进程的好方法
                int k = 0;
                GetWindowThreadProcessId(t, out k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                p.Kill();
                System.GC.Collect();
            }
            labMsg.Text = "";
            return result;
        }
        private void btnToExcel_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application excel = null;
            try
            {
                //DataGridView沒有資料就不執行
                if (this.dgvShowData.Rows.Count <= 1)
                {
                    MessageBox.Show("沒有可滙出的資料!", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    return;
                }
                //new 出一個Excel
                excel = new Microsoft.Office.Interop.Excel.Application();
                //看的到Excel在工作
                excel.Visible = true;
                //新增加一工作簿
                excel.Application.Workbooks.Add(true);

                //寫入欄位名稱
                for (int i = 0; i < dgvShowData.Columns.Count; i++)
                {
                    excel.Cells[1, i + 1] = dgvShowData.Columns[i].HeaderText;
                }
                //把DataGridView資料寫到Excel
                for (int i = 0; i < dgvShowData.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dgvShowData.Columns.Count; j++)
                    {
                        if (dgvShowData[j, i].ValueType == typeof(string))
                        {
                            excel.Cells[i + 2, j + 1] = "'" + dgvShowData[j, i].Value.ToString();
                        }
                        else
                        {
                            excel.Cells[i + 2, j + 1] = dgvShowData[j, i].Value.ToString();
                        }
                    }
                }
                //設定滙出後,欄位寛度自動配合資料調整
                excel.Cells.EntireRow.AutoFit(); //自動調整列高
                excel.Cells.EntireColumn.AutoFit();
                excel.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
                excel.get_Range("A1").Font.Color = Color.White;
                excel.get_Range("A1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("B1").Font.Color = Color.White;
                excel.get_Range("B1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("C1").Font.Color = Color.White;
                excel.get_Range("C1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("D1").Font.Color = Color.White;
                excel.get_Range("D1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("E1").Font.Color = Color.White;
                excel.get_Range("E1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("F1").Font.Color = Color.White;
                excel.get_Range("F1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("G1").Font.Color = Color.White;
                excel.get_Range("G1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("H1").Font.Color = Color.White;
                excel.get_Range("H1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("I1").Font.Color = Color.White;
                excel.get_Range("I1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("J1").Font.Color = Color.White;
                excel.get_Range("J1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("K1").Font.Color = Color.White;
                excel.get_Range("K1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("L1").Font.Color = Color.White;
                excel.get_Range("L1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("M1").Font.Color = Color.White;
                excel.get_Range("M1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("N1").Font.Color = Color.White;
                excel.get_Range("N1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("O1").Font.Color = Color.White;
                excel.get_Range("O1").Interior.Color = Color.DodgerBlue;

                //設置禁止彈出覆蓋或儲存的彈跳視窗
                excel.DisplayAlerts = false;
                excel.AlertBeforeOverwriting = false;
                MessageBox.Show("已成功輸出至Excel!", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //釋放資源
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
                GC.Collect();
            }
        }
        private static void PresentResultUpdate(Query query, Report report, int profileCounter)
        {
            Microsoft.Office.Interop.Excel.Application currentApp = GA_Excel2007.Globals.ThisAddIn.Application;
            Worksheet activeSheet = currentApp.ActiveSheet as Worksheet;

            if (currentApp.ActiveSheet != null)
            {
                int    activeColumn  = query.Column;
                int    activeRow     = query.Row;
                string addressLocal  = "";
                string addressHelper = "";

                object[] queryInformation = GetQueryInformation(query, report, profileCounter);

                int infoRows   = queryInformation.GetLength(0);
                int dataLength = dataLength = report.Data.GetLength(1);

                Range queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn],
                                                                   activeSheet.Cells[activeRow, activeColumn + dataLength - 1]);

                //
                addressLocal = queryInformationRange.get_Address().Replace("$", "");
                string[] adLocal = addressLocal.Split(':');

                // Looks after other reports in the same column.
                foreach (string address in _addressQueries)
                {
                    addressHelper = address.Replace("$", "");
                    // If there exist a report above this report then check where that report's data range ends.
                    if (addressHelper.First().Equals(adLocal[0].First()))
                    {
                        addressHelper = addressHelper.Substring(1, addressHelper.Length - 1);
                        int addressHelperInt = Int32.Parse(addressHelper);
                        int addressLocalInt  = Int32.Parse(adLocal[0].Substring(1, adLocal[0].Length - 1));

                        if (addressHelperInt > addressLocalInt)
                        {
                            activeRow             = addressHelperInt + 2;
                            queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn],
                                                                         activeSheet.Cells[activeRow, activeColumn + dataLength - 1]);
                        }
                    }
                }

                ((Style)queryInformationRange.Style).WrapText = false;
                queryInformationRange.Font.Italic             = true;
                queryInformationRange.MergeCells     = true;
                queryInformationRange.Borders.Weight = XlBorderWeight.xlThin;
                queryInformationRange.Value2         = queryInformation;

                if (report.Data != null)
                {
                    dataLength = report.Data.GetLength(1);
                    Range dataRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows + 1, activeColumn],
                                                           activeSheet.Cells[activeRow + infoRows + report.Data.GetLength(0), activeColumn + report.Data.GetLength(1) - 1]);
                    dataRange.Value2 = report.Data;
                    // Saves the last cell in this particular data range.
                    string endDataRow = "";
                    endDataRow = adLocal[0].First() + (activeRow + infoRows + report.Data.GetLength(0)).ToString();
                    _addressQueries.Add(endDataRow);
                    Range headerRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows, activeColumn],
                                                             activeSheet.Cells[activeRow + infoRows, activeColumn + report.Headers.GetLength(1) - 1]);
                    headerRange.Value2    = report.Headers;
                    headerRange.Font.Bold = true;
                }
            }
        }
Exemple #7
0
        private void btnSendToExcel_Click(object sender, EventArgs e)
        {
            Excel.Application excel = null;
            try
            {
                //DataGridView沒有資料就不執行
                if (this.dgvReferOrderAll.Rows.Count <= 1)
                {
                    MessageBox.Show("沒有可滙出的資料!", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    return;
                }
                string Date = DateTime.Now.ToString("yyyy-MM-dd");
                //設定滙出後的存檔路徑(儲存在桌面)
                string SaveFilePath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) +
                    @"\BentoReport_" + Date + ".xls";
                //new 出一個Excel
                excel = new Microsoft.Office.Interop.Excel.Application();
                //看的到Excel在工作
                excel.Visible = false;
                //新增加一工作簿
                excel.Application.Workbooks.Add(true);

                PGB pgb = new PGB();
                pgb.progressBar1.Minimum = 0;
                pgb.progressBar1.Maximum = dgvReferOrderAll.Rows.Count - 1;
                pgb.progressBar1.Step = 1;
                pgb.progressBar1.Value = 0;
                pgb.progressBar1.ForeColor = Color.Pink;
                pgb.progressBar1.Style = ProgressBarStyle.Continuous;
                pgb.Show();

                //寫入欄位名稱
                for (int i = 0; i < dgvReferOrderAll.Columns.Count; i++)
                {
                    excel.Cells[1, i + 1] = dgvReferOrderAll.Columns[i].HeaderText;
                }
                //把DataGridView資料寫到Excel
                for (int i = 0; i < dgvReferOrderAll.Rows.Count - 1; i++)
                {
                    pgb.progressBar1.Value++;
                    for (int j = 0; j < dgvReferOrderAll.Columns.Count; j++)
                    {
                        if (dgvReferOrderAll[j, i].ValueType == typeof(string))
                        {
                            excel.Cells[i + 2, j + 1] = "'" + dgvReferOrderAll[j, i].Value.ToString();
                        }
                        else
                        {
                            excel.Cells[i + 2, j + 1] = dgvReferOrderAll[j, i].Value.ToString();
                        }
                    }
                    //設定欄位靠右
                    excel.get_Range("A" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.
                        xlHAlignRight;
                    excel.get_Range("C" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.
                        xlHAlignRight;
                    //設定欄位顏色
                    excel.get_Range("A" + (i + 2)).Interior.Color = Color.Pink;
                    excel.get_Range("B" + (i + 2)).Interior.Color = Color.Pink;
                    excel.get_Range("C" + (i + 2)).Interior.Color = Color.Pink;
                    excel.get_Range("D" + (i + 2)).Interior.Color = Color.Pink;
                    //設定欄位框線
                    excel.get_Range("A" + (i + 2)).Borders.LineStyle = 1;
                    excel.get_Range("B" + (i + 2)).Borders.LineStyle = 1;
                    excel.get_Range("C" + (i + 2)).Borders.LineStyle = 1;
                    excel.get_Range("D" + (i + 2)).Borders.LineStyle = 1;
                }
                /*
                先將DataGridView的Rows總數給變數aa,以利後續透過aa+1的方式來新增要加入的資料
                (將DataGridView資料轉至Excel後,在Rows下方插入TextBox.Text)
                */
                int aa = dgvReferOrderAll.Rows.Count + 1;
                excel.Cells[aa, 1] = "[葷]數量:";
                excel.Cells[aa, 2] = txtTotalOrder0.Text;
                excel.Cells[aa, 3] = "[素]數量:";
                excel.Cells[aa, 4] = txtTotalOrder1.Text;
                //設定欄位靠右
                excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                //設定欄位顏色
                excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid;
                excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple;
                excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
                excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
                //設定欄位字體顏色
                excel.get_Range("A" + aa).Font.Color = Color.Snow;
                excel.get_Range("B" + aa).Font.Color = Color.Snow;
                excel.get_Range("C" + aa).Font.Color = Color.Snow;
                excel.get_Range("D" + aa).Font.Color = Color.Snow;
                //設定欄位字體為粗體
                excel.get_Range("A" + aa).Font.Bold = true;
                excel.get_Range("B" + aa).Font.Bold = true;
                excel.get_Range("C" + aa).Font.Bold = true;
                excel.get_Range("D" + aa).Font.Bold = true;
                aa = aa + 1;
                excel.Cells[aa, 1] = "[葷]金額:";
                excel.Cells[aa, 2] = txtTotalPrice0.Text;
                excel.Cells[aa, 3] = "[素]金額:";
                excel.Cells[aa, 4] = txtTotalPrice1.Text;
                excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid;
                excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple;
                excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
                excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
                excel.get_Range("A" + aa).Font.Color = Color.Snow;
                excel.get_Range("B" + aa).Font.Color = Color.Snow;
                excel.get_Range("C" + aa).Font.Color = Color.Snow;
                excel.get_Range("D" + aa).Font.Color = Color.Snow;
                excel.get_Range("A" + aa).Font.Bold = true;
                excel.get_Range("B" + aa).Font.Bold = true;
                excel.get_Range("C" + aa).Font.Bold = true;
                excel.get_Range("D" + aa).Font.Bold = true;
                aa = aa + 1;
                excel.Cells[aa, 3] = "總金額:";
                excel.Cells[aa, 4] = txtTotalPriceAll.Text;
                excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
                excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
                excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
                excel.get_Range("C" + aa).Font.Color = Color.Snow;
                excel.get_Range("D" + aa).Font.Color = Color.Snow;
                excel.get_Range("C" + aa).Font.Bold = true;
                excel.get_Range("D" + aa).Font.Bold = true;
                //設定滙出後,欄位寛度自動配合資料調整
                excel.Cells.EntireRow.AutoFit();
                //自動調整列高
                excel.Cells.EntireColumn.AutoFit();
                //將所有欄位做垂直置中
                excel.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
                //將A1~D1的欄位做水平置中
                excel.get_Range("A1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                excel.get_Range("B1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                excel.get_Range("C1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                excel.get_Range("D1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                //設定A1~D1欄位框線
                excel.get_Range("A1").Borders.LineStyle = 1;
                excel.get_Range("B1").Borders.LineStyle = 1;
                excel.get_Range("C1").Borders.LineStyle = 1;
                excel.get_Range("D1").Borders.LineStyle = 1;
                excel.get_Range("A1").Font.Color = Color.White;
                excel.get_Range("A1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("B1").Font.Color = Color.White;
                excel.get_Range("B1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("C1").Font.Color = Color.White;
                excel.get_Range("C1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("D1").Font.Color = Color.White;
                excel.get_Range("D1").Interior.Color = Color.DodgerBlue;
                /*
                excel.get_Range("E1").Font.Color = Color.White;
                excel.get_Range("E1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("F1").Font.Color = Color.White;
                excel.get_Range("F1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("G1").Font.Color = Color.White;
                excel.get_Range("G1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("H1").Font.Color = Color.White;
                excel.get_Range("H1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("I1").Font.Color = Color.White;
                excel.get_Range("I1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("J1").Font.Color = Color.White;
                excel.get_Range("J1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("K1").Font.Color = Color.White;
                excel.get_Range("K1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("L1").Font.Color = Color.White;
                excel.get_Range("L1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("M1").Font.Color = Color.White;
                excel.get_Range("M1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("N1").Font.Color = Color.White;
                excel.get_Range("N1").Interior.Color = Color.DodgerBlue;
                excel.get_Range("O1").Font.Color = Color.White;
                excel.get_Range("O1").Interior.Color = Color.DodgerBlue;
                */

                //設置禁止彈出覆蓋或儲存的彈跳視窗
                excel.DisplayAlerts = false;
                excel.AlertBeforeOverwriting = false;
                //將檔案儲存到SaveFile指定的位置
                excel.ActiveWorkbook.SaveCopyAs(SaveFilePath);
                pgb.Close();
                MessageBox.Show("已成功滙出Excel檔!" + Environment.NewLine + "檔案儲存在您電腦的桌面,檔名:BentoReport_" +
                    Date + ".xls", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            //關閉工作簿和結束Excel程式
            excel.Workbooks.Close();
            excel.Quit();
            //釋放資源
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            excel = null;
            GC.Collect();
        }
Exemple #8
0
        private void button2_Click(object sender, EventArgs e)
        {
            Excel.Application ex = new Microsoft.Office.Interop.Excel.Application();
            ex.Visible             = true;
            ex.SheetsInNewWorkbook = 12;
            Excel.Workbook workBook = ex.Workbooks.Add(Type.Missing);
            ex.DisplayAlerts = false;
            var currentYear1 = DateTime.Now.Year;

            for (int i = 0; i < 12; i++)
            {
                Excel.Worksheet sheet = (Excel.Worksheet)ex.Worksheets.get_Item(i + 1);
                sheet.StandardWidth = 3;
                sheet.Name          = months[i];
                sheet.Cells[1, 1]   = String.Format("Номер отеля | День");
                if (months[i] == "01" || months[i] == "03" || months[i] == "05" || months[i] == "07" || months[i] == "08" || months[i] == "10" || months[i] == "12")
                {
                    for (int j = 2; j < 33; j++)
                    {
                        sheet.Cells[j] = String.Format("{0}", j - 1);
                    }
                    for (int j = 2; j < 17; j++)
                    {
                        sheet.Cells[j, 1] = String.Format("{0}", j - 1);
                    }
                }
                if (months[i] == "02")
                {
                    for (int j = 2; j < 31; j++)
                    {
                        sheet.Cells[j] = String.Format("{0}", j - 1);
                    }
                    for (int j = 2; j < 17; j++)
                    {
                        sheet.Cells[j, 1] = String.Format("{0}", j - 1);
                    }
                }
                if (months[i] == "04" || months[i] == "06" || months[i] == "09" || months[i] == "11")
                {
                    for (int j = 2; j < 32; j++)
                    {
                        sheet.Cells[j] = String.Format("{0}", j - 1);
                    }
                    for (int j = 2; j < 17; j++)
                    {
                        sheet.Cells[j, 1] = String.Format("{0}", j - 1);
                    }
                }
            }
            SQLiteConnection sql = new SQLiteConnection(@"Data Source=D:\VS\Diplom\Guest.sqlite");

            sql.Open();
            int           count = 0;
            SQLiteCommand sc4   = new SQLiteCommand("select count (*) as '" + count + "'from Гости", sql); //кол-во строк в таблице

            count = Convert.ToInt32(sc4.ExecuteScalar());

            int[]         b    = new int[count]; //получение id
            int           o    = 0;
            SQLiteCommand sc3  = new SQLiteCommand("select id from Гости", sql);
            var           mas1 = sc3.ExecuteReader();

            while (mas1.Read())
            {
                b[o] = Convert.ToInt32(mas1["id"]);
                o++;
            }
            string Date1, Date2, Number = "";

            for (int j = 0; j < count; j++)
            {
                SQLiteCommand sc  = new SQLiteCommand("select Дата_Заезда from Гости where id='" + b[j] + "'", sql);
                SQLiteCommand sc1 = new SQLiteCommand("select Дата_Выезда from Гости where id='" + b[j] + "'", sql);
                SQLiteCommand sc2 = new SQLiteCommand("select Номер_Комнаты from Гости where id= '" + b[j] + "'", sql);
                Date1  = sc.ExecuteScalar().ToString();
                Date2  = sc1.ExecuteScalar().ToString();
                Number = sc2.ExecuteScalar().ToString();
                var currentYear = DateTime.Now.Date;

                string[] words = Date1.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries); //разделение даты
                string   Day = words[0]; string Month = words[1]; string Year = words[2];

                words = Date2.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
                string          Day1 = words[0]; string Month1 = words[1]; string Year1 = words[2];
                Excel.Worksheet sheet = (Excel.Worksheet)ex.Worksheets.get_Item(Month);
                if (listBox2.SelectedItem.ToString() == currentYear1.ToString())
                {
                    if (Month == Month1)
                    {
                        ((Excel.Worksheet)ex.Application.ActiveWorkbook.Sheets[Month]).Select();
                        Excel.Range range2 = ex.get_Range(ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day) + 1], ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day1) + 1]);
                        range2.Interior.Color = ColorTranslator.ToOle(Color.Blue);
                    }
                    else
                    {
                        string h = Day1;
                        for (int a = Convert.ToInt32(Month); a <= Convert.ToInt32(Month1); a++)
                        {
                            if (a != Convert.ToInt32(Month1))
                            {
                                if (a == 01 || a == 03 || a == 05 || a == 07 || a == 08 || a == 10 || a == 12)
                                {
                                    Day1 = "31";
                                }
                                else if (a == 02)
                                {
                                    Day1 = "29";
                                }
                                else if (a == 04 || a == 06 || a == 09 || a == 11)
                                {
                                    Day1 = "30";
                                }
                            }
                            ((Excel.Worksheet)ex.Application.ActiveWorkbook.Sheets[a]).Select();
                            Excel.Range range2 = ex.get_Range(ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day) + 1], ex.Cells[Convert.ToInt32(Number) + 1, Convert.ToInt32(Day1) + 1]);
                            range2.Interior.Color = ColorTranslator.ToOle(Color.Blue);
                            Day1 = h;
                        }
                    }
                }
            }
            sql.Close();
        }
Exemple #9
0
        // Клик по кнопке составить расписание
        private void createSchedule_Click(object sender, RoutedEventArgs e)
        {
            this.IsEnabled = false;
            this.Cursor    = Cursors.Wait;

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            if (excel == null)
            {
                MessageBox.Show("EXCEL не может быть запущен. Убедитесь, что у вас установлен пакет MS Office.", "Excel Error", MessageBoxButton.OK, MessageBoxImage.Error);
                return;
            }
            //excel.Visible = true;
            Excel.Workbook  wb = excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
            ws.Name = "Расписание";

            LessonsSchedule[] rings = context.lessonsSchedule
                                      .OrderBy(lesson => lesson.numberLesson)
                                      .ToArray();
            int column = 2;

            for (int row = 2; row < (App.days.Length) * rings.Length;)
            {
                foreach (LessonsSchedule ring in rings)
                {
                    ws.Cells[column, 2] = ring.numberLesson + " пара" + "\n" + ring.beginTime + " - " + ring.endTime;
                    column++;
                }

                excel.get_Range(String.Format("A{0}:A{1}", row, column - 1), Type.Missing).Merge(Type.Missing);
                ws.Cells[row, 1] = App.days[(row - 2) / rings.Length];

                row += rings.Length;
            }



            // Список всех преподователей из таблицы правил
            int[] teachers = context.regulation
                             .Join(
                context.teacher,
                reg => reg.idTeacher,
                teach => teach.id,
                (reg, teach) => new { reg.idTeacher, teach.lastname }
                )
                             .Distinct()
                             .OrderBy(x => x.lastname)
                             .Select(x => x.idTeacher)
                             .ToArray();

            //Количество недель в семестре
            int weeksCount;

            int    lectures;                 // Количество часов на лекции
            int    practices;                // Количество часов на практику
            int    laboratorys;              // КОличествво часов на лабораторные
            int    maxLessons;               // Максимальное число пар за день
            int    currentTeacherNumber = 2; // Номер преподователя, нужен для заполнения excel
            string lessonType;               // Тип лекции (л. пр. лаб.)

            List <Regulation> deleteReg   = new List <Regulation>();
            List <int>        tempLessons = new List <int>();


            foreach (int teacherID in teachers)
            {
                ///////////////////\\\\\\\\\\\\\\\\\\\\
                System.Diagnostics.Debug.WriteLine("------------------------------------------------------------------>");
                ///////////////////\\\\\\\\\\\\\\\\\\\\

                currentTeacherNumber++;

                // Текущий преподователь
                Teacher currentTeacher = context.teacher
                                         .Where(id => id.id == teacherID)
                                         .Single();

                ///////////////////\\\\\\\\\\\\\\\\\\\\
                System.Diagnostics.Debug.WriteLine("Преподователь:  ID = {0},  Фамилия = {1}", currentTeacher.id, currentTeacher.lastname);
                ///////////////////\\\\\\\\\\\\\\\\\\\\

                ws.Cells[1, currentTeacherNumber] = currentTeacher.lastname;

                // Все правила текущего преподователя, отсортированных по дням недели
                List <Regulation> teacherFromReg = context.regulation
                                                   .Where(id => id.idTeacher == teacherID)
                                                   .OrderBy(d => d.day)
                                                   .ToList();

                // Рабочий план текущего преподователя
                WorkPlan[] workPlan = context.workPlan
                                      .Where(id => id.idTeacher == teacherID)
                                      .ToArray();

                foreach (WorkPlan plan in workPlan)
                {
                    // Количество дней в семестре
                    TimeSpan daysOfSemester = DateTime.Parse(plan.endDate) - DateTime.Parse(plan.beginDate);

                    //Количество недель в семестре
                    weeksCount = daysOfSemester.Days / 7;

                    // Количество часов на лекции в неделю у данного преподователя на данный предмет
                    lectures = plan.lecturesTime / weeksCount;

                    // Количество часов на практику в неделю у данного преподователя на данный предмет
                    practices = plan.practiceTime / weeksCount;

                    // Количество часов на лабораторные в неделю у данного преподователя на данный предмет
                    laboratorys = plan.laboratoryTime / weeksCount;

                    ///////////////////\\\\\\\\\\\\\\\\\\\\ 
                    System.Diagnostics.Debug.WriteLine("Количество недель в семестре {0}", weeksCount);
                    System.Diagnostics.Debug.WriteLine("Рабочий план НА СЕМЕСТР:  ID = {0},  Лекций = {1},  Практик = {2},  Лаб = {3}", plan.id, plan.lecturesTime, plan.practiceTime, plan.laboratoryTime);
                    System.Diagnostics.Debug.WriteLine("Рабочий план НА НЕДЕЛЮ:   ID = {0},  Лекций = {1},  Практик = {2},  Лаб = {3}", plan.id, lectures, practices, laboratorys);
                    ///////////////////\\\\\\\\\\\\\\\\\\\\ 

                    // Удаляем использованные правила
                    foreach (Regulation del in deleteReg)
                    {
                        teacherFromReg.Remove(del);
                    }
                    deleteReg.Clear();


                    foreach (Regulation reg in teacherFromReg)
                    {
                        maxLessons = reg.maxLesson;

                        ///////////////////\\\\\\\\\\\\\\\\\\\\
                        System.Diagnostics.Debug.WriteLine("");
                        System.Diagnostics.Debug.WriteLine("Правило:  ID = {0},  День недели = {1},  Занятия = {2},  Макс. занятий = {3}", reg.id, App.days[reg.day], reg.lessons, reg.maxLesson);
                        ///////////////////\\\\\\\\\\\\\\\\\\\\

                        List <int> lessons = reg.lessons.Split(',')
                                             .Select(lesson => int.Parse(lesson))
                                             .ToList();
                        tempLessons.Clear();

                        foreach (int lesson in lessons)
                        {
                            // Условие выхода из цикла
                            if (maxLessons == 0)
                            {
                                // Добавляем запись правила в список, для удаления на следующей итерации
                                deleteReg.Add(reg);
                                break;
                            }

                            // Уменьшаем количество часов на занятия
                            if (lectures > 0)
                            {
                                lectures--; lessonType = " л.";
                            }
                            else if (practices > 0)
                            {
                                practices--; lessonType = " пр.";
                            }
                            else if (laboratorys > 0)
                            {
                                laboratorys--; lessonType = " лаб.";
                            }
                            else
                            {
                                break;
                            }

                            maxLessons--;

                            tempLessons.Add(lesson);

                            // Возвращаем название предмета и его id
                            var subject = context.workPlan
                                          .Where(currentPlan => currentPlan.id == plan.id)
                                          .Join(
                                context.subject,
                                currentPlan => currentPlan.idSubject,
                                sub => sub.id,
                                (currentPlan, sub) => new { sub.subjectName, sub.id }
                                )
                                          .Single();

                            // По id предмета возвращаем все аудитории, в которых он преподаётся
                            string[] classes = context.subjectClasses
                                               .Join(
                                context.classroom,
                                sc => new { k1 = sc.idSubject, k2 = sc.idClassroom },
                                cl => new { k1 = subject.id, k2 = cl.id },
                                (sc, cl) => cl.className
                                )
                                               .ToArray();

                            ws.Cells[reg.day * rings.Length + 1 + lesson, currentTeacherNumber] = subject.subjectName + lessonType + "\n" + classes[new Random().Next(classes.Length)];

                            ////////////////lessons.Remove(lessons[lesson]);
                        }

                        ///////////////////\\\\\\\\\\\\\\\\\\\\
                        System.Diagnostics.Debug.WriteLine("Остаток НА НЕДЕЛЮ:  Лекций: = {0},  Практических =  {1},  Лабораторных = {2}", lectures, practices, laboratorys);
                        ///////////////////\\\\\\\\\\\\\\\\\\\\
                        System.Diagnostics.Debug.WriteLine("Расписание: предмет = {0},  День недели = {1},  Занятия = {2},  Осталось свободных занятий = {3}",
                                                           context.subject.Where(id => id.id == plan.idSubject).Select(n => n.subjectName).Single(),
                                                           App.days[reg.day],
                                                           String.Join(",", tempLessons
                                                                       .Select(x => x)
                                                                       .OrderBy(x => x)
                                                                       .ToArray()),
                                                           maxLessons);
                        ///////////////////\\\\\\\\\\\\\\\\\\\\ 
                    }
                }
            }
            /// ------- Цикл по составлению рассписания кончился ------------


            /// Стили для excel

            // Преподаватели
            var teachersRange = ws.Range["C1", String.Format("{0}1", Convert.ToChar(67 + teachers.Length))];  // где 67 - код из ASCII буквы C

            teachersRange.Orientation         = 90;
            teachersRange.WrapText            = false;
            teachersRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            teachersRange.VerticalAlignment   = Excel.XlVAlign.xlVAlignBottom;
            teachersRange.Font.Size           = 16;
            teachersRange.Font.Name           = "Arial";

            // Дни недели
            var daysRange = ws.Range["A2", String.Format("A{0}", rings.Length * App.days.Length)];

            daysRange.Orientation         = 90;
            daysRange.WrapText            = false;
            daysRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            daysRange.VerticalAlignment   = Excel.XlVAlign.xlVAlignCenter;
            daysRange.Font.Size           = 16;
            daysRange.Font.Name           = "Arial";

            // Номера занятий
            var lessonRange = ws.Range["B2", String.Format("B{0}", rings.Length * App.days.Length + 1)];

            lessonRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            lessonRange.VerticalAlignment   = Excel.XlVAlign.xlVAlignCenter;
            lessonRange.Font.Size           = 12;
            lessonRange.ColumnWidth         = 15;

            // Расписание
            var scheduleRange = ws.Range["C2", String.Format("{0}{1}", Convert.ToChar(67 + teachers.Length), rings.Length * App.days.Length + 1)];

            scheduleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            scheduleRange.VerticalAlignment   = Excel.XlVAlign.xlVAlignCenter;
            scheduleRange.Font.Size           = 12;
            scheduleRange.WrapText            = true;
            scheduleRange.Rows.AutoFit();
            scheduleRange.ColumnWidth = 25;


            SaveFileDialog saveExcelFile = new SaveFileDialog();

            saveExcelFile.FileName   = "Расписание";
            saveExcelFile.DefaultExt = ".xlsx";
            saveExcelFile.Filter     = "Книга Excel 2007 (*.xlsx)|*.xlsx|Книга Excel 2003 (*.xls)|*.xls";

            // Сохранение файла и закрытие excel
            if ((bool)saveExcelFile.ShowDialog())
            {
                wb.SaveAs(saveExcelFile.FileName);

                wb.Close(true);
                excel.Quit();
                excel.DisplayAlerts = false;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

                Process.Start(saveExcelFile.FileName);
            }

            this.IsEnabled = true;
            this.Cursor    = null;
        }
Exemple #10
0
        //生成Excel
        private bool ExportToExcel(DataTable dt)
        {
            bool result = false;
            if (dt.Rows.Count > 65536)
            {
                MessageBox.Show("數據記錄太多(最多不能超過65536條),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return result;
            }
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                labMsg.Text = "Excel正在匯出中…………";

                if (xlApp == null)
                {
                    throw new Exception("无法创建Excel对象,可能您的机器未安装Excel");
                }
                Excel.Workbooks workbooks = xlApp.Workbooks;
                Excel.Workbook workbook = workbooks.Add(true);
                Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
                worksheet.Name = "過膠機品質自檢日報表";

                worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印
                worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印
                worksheet.PageSetup.Zoom = false; //設置頁面縮放比例  Zoom必須設為False FitToPagesWide才有效
                worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁
                worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中

                worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102);
                worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787);
                worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787);
                worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787);
                worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126);
                worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126);
                xlApp.Visible = false;

                #region 列名

                int RowIndex = 2;
                MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 32], "過膠機品質自檢日報表", 16, 65535, 0); //大標題
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 32], "當班品檢情況", 12, 65535, 0);
                RowIndex = RowIndex + 1;
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "累計數量", 12, 65535, 9);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "車速", 12, 65535, 5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "溫度", 12, 65535, 5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "壓力", 12, 65535, 5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 23], xlApp.Cells[RowIndex, 23], "當班品檢總次數", 12, 65535, 10);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "第( )次品檢", 12, 65535, 11);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "打卡數量", 12, 65535, 11);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "抽檢數量", 12, 65535, 9.5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "品檢問題", 12, 65535, 18);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "次品數量", 12, 65535, 11);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "廢品數量", 12, 65535, 9.5);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "問題處理辦法", 12, 65535, 24);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 31], xlApp.Cells[RowIndex, 31], "品檢開始時間", 12, 65535, 24);
                MergeColumn(xlApp, xlApp.Cells[RowIndex, 32], xlApp.Cells[RowIndex, 32], "品檢結束時間", 12, 65535, 24);
                #endregion

                #region 填充數據 向Excel中逐行逐列写入表格中的数据
                RowIndex = RowIndex + 1; //數據開始的行號
                int ExcelCurrectRow = RowIndex;  //當前行
                int dtRowNO;
                int realRowNO = 0;//dt實際行
                int dtKeyRowNO = RowIndex;//NO:4
                int megreCount = 0;
                int dtProblemRowNO = 24;//物料編號所在列
                int tempRow;
                int tempCol;
                DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" });
                for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++)
                {
                    DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' ");
                    megreCount = currectKeyRow.Length;
                    for (tempRow = 0; tempRow < megreCount; tempRow++)
                    {
                        if (tempRow == 0)
                        {
                            for (tempCol = 1; tempCol < dtProblemRowNO; tempCol++) //先合併物料編號之前的單元格
                            {
                                CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true);
                            }
                            realRowNO += megreCount;
                        }
                        for (tempCol = dtProblemRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料
                        {
                            CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false);
                        }
                        dtKeyRowNO++;
                    }

                }
                #endregion

                #region 設置Sheet邊框
                //range:
                Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]);
                SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                #endregion

                Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]);
                DataFormat.Font.Name = "楷体";
                DataFormat.Font.Size = 10;
                Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]);
                IntFormat.NumberFormatLocal = "0.00";

                #region savecode
                Object missing = Missing.Value;
                SaveFileDialog saveForm = new SaveFileDialog();
                saveForm.Filter = "Execl files (*.xls)|*.xls";
                saveForm.FilterIndex = 0;
                saveForm.FileName = "過膠機品質自檢日報表";
                if (saveForm.ShowDialog() == DialogResult.OK)
                {
                    string fileName = saveForm.FileName;
                    workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                                     missing, missing, missing, missing, missing);

                }
                #endregion

                result = true;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                labMsg.Text = "";
                xlApp.Quit();
                IntPtr t = new IntPtr(xlApp.Hwnd);          //杀死进程的好方法
                int k = 0;
                GetWindowThreadProcessId(t, out k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                p.Kill();
                System.GC.Collect();
            }
            labMsg.Text = "";

            return result;
        }
Exemple #11
0
        public ActionResult DownLoadFile()
        {
            try
            {
                #region Exal
                byte[]  bytes;
                string  fileName = "", contentType = "", PaperPath = "";
                DataSet dswordchargeinrow = new DataSet();
                KeystoneProject.Buisness_Logic.Master.Bl_Services Bl_obj = new Buisness_Logic.Master.Bl_Services();
                Services AddServiceMod = new Services();
                AddServiceMod.StoreAllServices  = Bl_obj.GetAllServicesExcelData();
                AddServiceMod.dsServicesCharges = Bl_obj.GetAllWardName();

                Microsoft.Office.Interop.Excel._Application app       = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook    workbook  = app.Workbooks.Add(Type.Missing);
                Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;
                Excel.Range chartRange;
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;

                app.get_Range("A2", "S2").Locked = true;

                string WardNAme;
                //worksheet.Name = "Exported from Keystone";
                int count = 0;
                for (int i = 1; i < AddServiceMod.StoreAllServices.Tables[0].Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = AddServiceMod.StoreAllServices.Tables[0].Columns[i - 1].ColumnName;

                    count = i;
                }
                int m = 0;
                for (int word = 0; word < AddServiceMod.dsServicesCharges.Tables[0].Rows.Count; word++)
                {
                    worksheet.Cells[1, count + word] = AddServiceMod.dsServicesCharges.Tables[0].Rows[m]["WardName"];
                    // worksheet.get_Range(worksheet.Cells[1, word], worksheet.Cells[4, 3]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange);
                    WardNAme = AddServiceMod.dsServicesCharges.Tables[0].Rows[m]["WardName"].ToString() + ",";
                    m++;
                }

                for (int i = 0; i < AddServiceMod.StoreAllServices.Tables[0].Rows.Count; i++)
                {
                    for (int j = 0; j < AddServiceMod.StoreAllServices.Tables[0].Columns.Count; j++)
                    {
                        // if (j < 2)
                        //  {
                        worksheet.Cells[i + 2, j + 1] = AddServiceMod.StoreAllServices.Tables[0].Rows[i][j].ToString();
                        // }

                        if (AddServiceMod.StoreAllServices.Tables[0].Rows[i]["ServiceID"].ToString() != "")
                        {
                            dswordchargeinrow = Bl_obj.SelectServiceChargesbyID(Convert.ToInt16(AddServiceMod.StoreAllServices.Tables[0].Rows[i]["ServiceID"]));

                            if (dswordchargeinrow.Tables[0].Rows.Count > 0)
                            {
                                foreach (DataRow drcharge in AddServiceMod.dsServicesCharges.Tables[0].Rows)
                                {
                                    int charge = 0;
                                    foreach (DataRow dr in dswordchargeinrow.Tables[0].Rows)
                                    {
                                        string WardName  = drcharge["WardName"].ToString();
                                        string WardName1 = dr["WardName"].ToString();
                                        if (drcharge["WardName"].ToString() == dr["WardName"].ToString())
                                        {
                                            worksheet.Cells[i + 2, charge + 7] = dr["GeneralCharges"];
                                        }
                                        charge++;
                                    }
                                }
                            }
                        }
                    }
                }
                #endregion
                Random obj      = new Random();
                int    a        = obj.Next(1, 300);
                string DateCode = "Service_Excel_Data" + Bl_obj.ReffrenceExcel();
                fileName = Server.MapPath("~/") + "Service_Excel/" + DateCode;

                workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                workbook.Close(true, Type.Missing, Type.Missing);

                string Path     = fileName + ".xlsx";
                string filename = System.IO.Path.GetFileName(DateCode + ".xlsx");

                // string fullpath = System.IO.Path.Combine(Path, fileName);
                contentType = MimeMapping.GetMimeMapping(Path);
                File(Path, contentType, filename);
                // File(Path, contentType, filename);
                return(new JsonResult {
                    Data = "Done", JsonRequestBehavior = JsonRequestBehavior.AllowGet
                });

                //fileName = "Service_Excel_Data" + Bl_obj.ReffrenceCode()+".xlsx";
            }
            catch (Exception ex)
            {
                throw;
            }



            //      return File(FileById.FileContent, "/MRDFiles/pdf", FileById.FileName);
        }