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