Ejemplo n.º 1
0
        public void ExportCallQualityData(string formPath, string savePath, string fileFormat, DataTable dt, string contragent, string ocinyvach, string ocinyvanyi, string callType, string opDate, string criticalError, string filePath, string result)
        {
            try
            {
                Cursor.Current = Cursors.WaitCursor;
                app            = new Excel.Application();
                Excel._Workbook workbook = app.Workbooks.Open(formPath);
                //Excel._Worksheet worksheet = null;
                Excel._Worksheet worksheet = workbook.Sheets[1];
                worksheet      = workbook.ActiveSheet;
                worksheet.Name = "Оцінка якості";

                worksheet.Cells.Replace("{OpDateTime}", opDate);
                worksheet.Cells.Replace("{Ochinuvanyy}", ocinyvanyi);
                worksheet.Cells.Replace("{CallType}", callType);
                worksheet.Cells.Replace("{Contragent}", contragent);
                worksheet.Cells.Replace("{FilePath}", filePath);
                worksheet.Cells.Replace("{Ocinyvach}", ocinyvach);
                worksheet.Cells.Replace("{CriticalError}", criticalError);
                worksheet.Cells.Replace("{Result}", result);
                int startRow = 14;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i < dt.Rows.Count - 1)
                    {
                        worksheet.Rows[(startRow + i) + ":" + (startRow + i)].Copy();
                        worksheet.Rows[(startRow + i + 1) + ":" + (startRow + i + 1)].Insert();
                    }
                    worksheet.Range["B" + (startRow + i)].Value = dt.Rows[i][0].ToString();
                    worksheet.Range["D" + (startRow + i)].Value = dt.Rows[i][1].ToString();
                    worksheet.Range["E" + (startRow + i)].Value = dt.Rows[i][2].ToString();
                }
                worksheet.Rows[7 + ":" + (dt.Rows.Count - 1)].EntireRow().AutoFit();
                if (fileFormat.Contains("xlsx"))
                {
                    workbook.SaveAs(savePath);
                    app.Visible = true;
                }
                else if (fileFormat.Contains("pdf"))
                {
                    workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, savePath);
                    workbook.Close(false);
                    app.Quit();
                    System.Diagnostics.Process.Start(savePath);
                }
                Cursor.Current = Cursors.Default;
                //MessageBox.Show("Дані вивантажено!" + Environment.NewLine + savePath, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                if (app.Workbooks.Count == 1)
                {
                    app.Quit();
                }
                app = null;
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Saves the current workbook.
        /// </summary>
        /// <param name="location">The location where the workbook is to be saved.</param>
        /// <param name="format">The file format to save to.</param>
        protected void SaveWorkbook(string location, ExcelFileFormat format = ExcelFileFormat.Original)
        {
            // Get the save file format for the current document.
            object paramExportFormat = GetExcelFormat(format);

            if (paramExportFormat is Excel.XlFixedFormatType)
            {
                // Export the workbook
                _workbook.ExportAsFixedFormat(
                    (Excel.XlFixedFormatType)paramExportFormat,
                    location,
                    _refMissing,
                    _refMissing,
                    _refMissing,
                    _refMissing,
                    _refMissing,
                    _refMissing,
                    _refMissing);
            }
            else
            {
                // Save the workbook
                _workbook.SaveAs(
                    location,
                    paramExportFormat,
                    _refMissing,
                    _refMissing,
                    _refMissing,
                    _refMissing,
                    Excel.XlSaveAsAccessMode.xlNoChange,
                    _refMissing,
                    _refMissing,
                    _refMissing,
                    _refMissing,
                    _refMissing);
            }
        }
Ejemplo n.º 3
0
        private void SaveToExcel()
        {
            //создаём объект Excel
            Excel._Application excel     = new Excel.Application();
            Excel._Workbook    workbook  = excel.Workbooks.Add(Type.Missing);
            Excel._Worksheet   worksheet = null;

            try
            {
                worksheet      = workbook.ActiveSheet;
                worksheet.Name = "ЭСПО";
                int cellRowIndex    = 1;
                int cellColumnIndex = 1;

                //Обработка по каждой строке и столбцу
                for (int i = 0; i < DG1.Rows.Count; i++)
                {
                    for (int j = 0; j < DG1.Columns.Count; j++)
                    {
                        if (DG1.Columns[j].Visible)
                        {
                            //DG1.Columns[j].Selected = true;
                            //DG1.
                            cellColumnIndex = DG1.Columns[j].DisplayIndex + 1;

                            // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                            //Если строка Excel=1 то пишем туда заголовок столбца
                            if (cellRowIndex == 1)
                            {
                                worksheet.Cells[cellRowIndex, cellColumnIndex] = DG1.Columns[j].HeaderText;
                                (worksheet.Cells[cellRowIndex, cellColumnIndex] as Excel.Range).BorderAround(Excel.XlLineStyle.xlContinuous);
                            }

                            int cellRowIndexPlus1 = cellRowIndex + 1;
                            (worksheet.Cells[cellRowIndexPlus1, cellColumnIndex] as Excel.Range).NumberFormat = "@";
                            worksheet.Cells[cellRowIndexPlus1, cellColumnIndex] = DG1.Rows[i].Cells[j].Value.ToString();

                            //DG1.column

                            //цвет
                            if (DG1.Rows[i].Cells[j].Style.BackColor.Name != "0")
                            {
                                (worksheet.Cells[cellRowIndexPlus1, cellColumnIndex] as Excel.Range).Interior.Color = DG1.Rows[i].Cells[j].Style.BackColor;
                            }

                            //бордюры
                            (worksheet.Cells[cellRowIndexPlus1, cellColumnIndex] as Excel.Range).BorderAround(Excel.XlLineStyle.xlContinuous);
                            cellColumnIndex++;
                        }
                        else
                        {
                            continue;
                        }
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }

                worksheet.PageSetup.Orientation       = Excel.XlPageOrientation.xlLandscape;
                worksheet.PageSetup.LeftMargin        = 5;
                worksheet.PageSetup.RightMargin       = 5;
                worksheet.PageSetup.BottomMargin      = 5;
                worksheet.PageSetup.PrintTitleColumns = "$A:$B";
                //Ширина столбца по содержимому
                worksheet.Columns.EntireColumn.AutoFit();



                ////Getting the location and file name of the excel to save from user.
                //SaveFileDialog saveDialog = new SaveFileDialog();
                string   FileAddr = @"Reports\ЭСПО.pdf";
                FileInfo file     = new FileInfo(FileAddr);
                //if (!File.Exists(FileExcel))
                //    File.Create(FileExcel);

                //workbook.SaveAs(FileExcel);


                workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, file.FullName);
                //saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                //saveDialog.FilterIndex = 2;

                //if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                //{
                //    workbook.SaveAs(saveDialog.FileName);
                //    MessageBox.Show("Export Successful");
                //}
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message + "\n" + ex.ToString());
            }
            finally
            {
                excel.DisplayAlerts = false;
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }