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); } }
/// <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); } }
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; } }