private void ExcelButton_Click(object sender, EventArgs e) { application = new Excel.Application(); int i = 0; workbook = application.Workbooks.Add(); application.Visible = true; foreach (DataGridView d in listdgv) { d.MultiSelect = true; d.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText; d.SelectAll(); DataObject dataObject = d.GetClipboardContent(); if (dataObject != null) { Clipboard.SetDataObject(dataObject); Excel.Worksheet worksheet = workbook.Worksheets.Add(); worksheet.Name = listdgv.Count - i++ + " запрос"; Excel.Range range = (Excel.Range)worksheet.Cells[1, 1]; range.Select(); worksheet.PasteSpecial(range, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); worksheet = null; } } workbook.BeforeClose += Workbook_BeforeClose; }
private void btnExcel_Click(object sender, EventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = "My_Watchlist.xls"; if (sfd.ShowDialog() == DialogResult.OK) { // Copy DataGridView results to clipboard copyAlltoClipboardFromGrid(); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column C as text before pasting results, this was required for my data Excel.Range rng = xlWorkSheet.get_Range("C:C").Cells; rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // Add header text from columns in datagridview to worksheet for (int i = 0; i < gridMovies.Columns.Count; i++) { xlWorkSheet.Cells[1, i + 1] = gridMovies.Columns[i].HeaderText; } // Delete blank column K Excel.Range delRng = xlWorkSheet.get_Range("K:K").Cells; delRng.Delete(Type.Missing); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); gridMovies.ClearSelection(); // Open the newly saved excel file if (File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } } }
private void button2_Click(object sender, EventArgs e) { // Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application(); SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = _fileName + _timeStamp + ".xls"; if (sfd.ShowDialog() == DialogResult.OK) { // Copy DataGridView results to clipboard copyAlltoClipboard(); object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column D as text before pasting results, this was required for my data Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯ // Delete blank column A and select cell A1 Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); dataGridView1.ClearSelection(); // Open the newly saved excel file if (File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } dataGridView1.DataSource = null;//clear datagridview1 label1.Text = "Cleared all data from this view!"; } }
public void exc(DataGridView tablo, bool tumu, string baslik) { if (tablo.RowCount > 0) { try { tablo.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText; if (tumu) { tablo.SelectAll(); } DataObject dataObj = tablo.GetClipboardContent(); if (dataObj != null) { Clipboard.SetDataObject(dataObj); } object misValue = System.Reflection.Missing.Value; XcellApp = new Microsoft.Office.Interop.Excel.Application(); XcellApp.Visible = true; XcellBook = XcellApp.Workbooks.Add(misValue); XcellSheet = (Excel.Worksheet)XcellBook.Worksheets.get_Item(1); Excel.Range alan = (Excel.Range)XcellSheet.Cells[3, 1];//!IMPORTANT alan.Select(); XcellSheet.PasteSpecial(alan, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); Excel.Range tarihalani = XcellSheet.Range[XcellSheet.Cells[1, 1], XcellSheet.Cells[1, tablo.ColumnCount]]; Excel.Range headeralani = XcellSheet.Range[XcellSheet.Cells[3, 1], XcellSheet.Cells[3, tablo.ColumnCount]]; Excel.Range baslikalani = XcellSheet.Range[XcellSheet.Cells[2, 1], XcellSheet.Cells[2, tablo.ColumnCount]]; Excel.Range baslikharicalan = XcellSheet.Range[XcellSheet.Cells[3, 1], XcellSheet.Cells[tablo.RowCount, tablo.ColumnCount]]; //Tasarım tarihalani.Merge(); tarihalani.FormulaR1C1 = "Rapor Tarihi: " + DateTime.Today.ToString("dd/MM/yyyy") + " " + DateTime.Now.ToLongTimeString(); baslikalani.Merge(); baslikalani.Font.Size = 30; baslikalani.HorizontalAlignment = 3; baslikalani.FormulaR1C1 = baslik; baslikalani.Interior.Color = Excel.XlRgbColor.rgbLightGreen; headeralani.Interior.Color = Excel.XlRgbColor.rgbYellow; headeralani.Font.Bold = true; XcellSheet.UsedRange.EntireColumn.VerticalAlignment = 2; baslikharicalan.EntireColumn.HorizontalAlignment = 3; XcellSheet.UsedRange.Borders.Weight = 2; XcellSheet.UsedRange.EntireColumn.RowHeight = 20; baslikharicalan.EntireColumn.AutoFit(); baslikalani.RowHeight = 40; } catch (Exception s) { MessageBox.Show("Hata: " + s, "Hata!", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Tablo boş!", "Hata!", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
private void Button1_Click(object sender, EventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; //Commit sfd.FileName = "MiExport.xls"; if (sfd.ShowDialog() == DialogResult.OK) { // Copy DataGridView al clipboard copyAlltoClipboard(); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false;// Sin esto, obtendrás dos mensajes de confirmación de sobrescritura Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column B siempre será una despues para afectar una anterior Excel.Range rng = xlWorkSheet.get_Range("C:C").Cells; rng.NumberFormat = "0.00000"; // Pegar resultados del portapapeles en el rango de la hoja de trabajo Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); //// Borra la columna en blanco //Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; //delRng.Delete(Type.Missing); //xlWorkSheet.get_Range("A1").Select(); // Guarde el archivo de Excel en la ubicación capturada desde SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); // Borrar la selección del Portapapeles y DataGridView Clipboard.Clear(); Malla.ClearSelection(); // Abra el archivo de Excel recién guardado if (File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } } }
private void btn_Excel_Click(object sender, EventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = "Inventory_Adjustment_Export.xls"; if (sfd.ShowDialog() == DialogResult.OK) { // Copy DataGridView results to clipboard CopyAlltoClipboard(); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column D as text before pasting results, this was required for my data Excel.Range rng = xlWorkSheet.get_Range("A:A").Cells; rng.EntireColumn.NumberFormat = "MM/dd/yyyy";; // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); //// For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯ //// Delete blank column A and select cell A1 //Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; //delRng.Delete(Type.Missing); //xlWorkSheet.get_Range("A1").Select(); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); ReleaseObject(xlWorkSheet); ReleaseObject(xlWorkBook); ReleaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); dgrid_Schedule.ClearSelection(); // Open the newly saved excel file if (File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } } }
private void buttonExport_Click(object sender, EventArgs e) { // Tham khảo link: https://stackoverflow.com/questions/18182029/how-to-export-datagridview-data-instantly-to-excel-on-button-click SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = "Salary_Detail_Export.xls"; if (sfd.ShowDialog() == DialogResult.OK) { // Copy DataGridView results to clipboard copyAlltoClipboard(); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column D as text before pasting results, this was required for my data Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯ // Delete blank column A and select cell A1 Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); dataGridViewSalaryDetail.ClearSelection(); // Open the newly saved excel file if (File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } } }
private void toolStripMenuItem1_Click(object sender, EventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.RestoreDirectory = true; //sfd.InitialDirectory = FolderPath; sfd.FileName = "respiratory_data.xls"; if (sfd.ShowDialog() == DialogResult.OK) { copyAlltoClipboard(); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column D as text before pasting results, this was required for my data //Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; //rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯ // Delete blank column A and select cell A1 Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); // Clear Clipboard and DataGridView selection System.Windows.Forms.Clipboard.Clear(); dataGridView1.ClearSelection(); // Open the newly saved excel file if (File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } } }
private void Button1_Click(object sender, EventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = "Petty Cash Vouchers Report.xls"; if (sfd.ShowDialog() == DialogResult.OK) { // Copy DataGridView results to clipboard copyAlltoClipboard(); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column D as text before pasting results, this was required for my data Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // For some reason column A is always blank in the worksheet. // Delete blank column A and select cell A1 Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); dataGridView1.ClearSelection(); } MessageBox.Show("Your Excel spreadsheet has been successfully exported.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); }
public void ExportToExcel(DataGridView dgv, string fileName) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = fileName + "_" + DateTime.Now.ToString("yyyyMMdd-ffff"); if (sfd.ShowDialog() == DialogResult.OK) { CopyAlltoClipboard(dgv); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); xlWorkSheet.Columns.AutoFit(); xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); RelComObject(xlWorkSheet); RelComObject(xlWorkBook); RelComObject(xlexcel); Clipboard.Clear(); dgv.ClearSelection(); try { if (System.IO.File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } }
/* Utilizes Microsoft.Office.Interop.Excel.dll reference in order to create a temporary Excel document which will contain * the data displayed in dataGridLedger. */ private void btnDescarca_Click(object sender, EventArgs e) { if (dataGridLedger.Rows.Count > 0) { ClipboardCopy(); /* A MissingValue type object (empty object) created which will be passed as a parameter to the Workbook. */ object missValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application(); Excel.Visible = true; /* Creates a new workbook, which becomes the active workbook. */ Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook = Excel.Workbooks.Add(missValue); Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); /* [1, 1] Specifies the location at which Column/Row the pasted data from the dataGridLedger will happen in the Excel file. */ Microsoft.Office.Interop.Excel.Range ColumnRow = (Microsoft.Office.Interop.Excel.Range)ExcelWorkSheet.Cells[1, 1]; ColumnRow.Select(); ExcelWorkSheet.PasteSpecial(ColumnRow, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); } }
private void excelIcon_Click(object sender, EventArgs e) { { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = "قائمة العملاء.xls"; if (sfd.ShowDialog() == DialogResult.OK) { // Copy DataGridView results to clipboard copyAlltoClipboard(); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //make all columns and rows fitting instead of squeezed in int i = 0; for (i = 1; i <= 10; i++) // this will aply it form col 1 to 10 { xlWorkSheet.Columns[i].ColumnWidth = 18; } // Format column D as text before pasting results, this was required for my data Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯ // Delete blank column A and select cell A1 //Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; //delRng.Delete(Type.Missing); // xlWorkSheet.get_Range("A1").Select(); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); dataGridView1.ClearSelection(); MessageBox.Show("تم حفظ قائمة العملاء في المسار المحدد", "تأكيد", MessageBoxButtons.OK, MessageBoxIcon.Information); // Open the newly saved excel file // if (File.Exists(sfd.FileName)) // System.Diagnostics.Process.Start(sfd.FileName); } } }
private void button1_Click_2(object sender, EventArgs e) { try { //Code to check if (txtFilename.TextLength == 0) { MessageBox.Show("Please select a valid file", "Info"); } else if (cboSheet.SelectedIndex == -1) { MessageBox.Show("Please select a Sheet", "Info"); } else if (dataGridView1.Rows.Count == 0) { MessageBox.Show("Please open the Sheet", "Info"); } else { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = "Export.xls"; if (sfd.ShowDialog() == DialogResult.OK) { // Copy DataGridView results to clipboard copyAlltoClipboard(); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column D as text before pasting results, this was required for my data Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯ // Delete blank column A and select cell A1 Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); xlWorkSheet.Columns.AutoFit(); xlWorkSheet.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //xlWorkSheet.ColumnHeadersDefaultCellStyle.BackColor = Color.Orange; // xlWorkSheet.RowHeadersDefaultCellStyle.BackColor = Color.Red; // xlWorkSheet.Columns.Range["B5:J4"].Style.Color = Color.Red; // xlWorkSheet.Columns.hea // rng.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); // var columnHeadingsRange = xlWorkSheet.Range[ // xlWorkSheet.Cells["A1", "G1"]]; // columnHeadingsRange.Interior.Color = Excel.XlRgbColor.rgbLightGoldenrodYellow; // columnHeadingsRange.Style.Font.Bold = true; // dataGridView1.ColumnHeadersDefaultCellStyle.BackColor = Color.Orange; // dataGridView1.RowHeadersDefaultCellStyle.BackColor = Color.Red; //xlWorkSheet.Range["A1", "G1"].Interior.Color = Excel.XlRgbColor.rgbDarkBlue; // xlWorkSheet.Range["A1", "G1"].Font.Color = Excel.XlRgbColor.rgbWhite; // xlWorkSheet.Range["A1:D1"].Style.Color = Color.LightSeaGreen; // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); dataGridView1.ClearSelection(); // Open the newly saved excel file if (File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } //reset application MessageBox.Show("File Exported Successfully", "Info"); txtFilename.Text = ""; cboSheet.Items.Clear(); cboSheet.Text = ""; dataGridView1.DataSource = null; dataGridView1.Refresh(); label3.Text = ""; progressBar1.Value = 0; button3.Enabled = true; button5.Enabled = true; button6.Enabled = true; } } } catch (Exception ex) { //Code here if an error MessageBox.Show("There was a problem that occurred while exporting, Please Try Again.", "Alert"); } }
public void SaveDataToExcel(string fileName) { copyGridtoClipboard(); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet; xlexcel.Visible = false; xlexcel.DisplayAlerts = false; Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // Delete blank column A and select cell A1 Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); Clipboard.Clear(); DataCreationGrid.ClearSelection(); /* try { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = true; Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value); Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1]; int StartCol = 1; int StartRow = 1; //Write Headers for (int col = 0; col < DataCreationGrid.Columns.Count; col++) { Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow, StartCol + col]; myRange.Value2 = DataCreationGrid.Columns[col].HeaderText; } StartRow++; //Write datagridview content for (int row = 0; row < DataCreationGrid.Rows.Count; row++) { for (int col = 0; col < DataCreationGrid.Columns.Count; col++) { try { Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow + row, StartCol + col]; myRange.Value2 = DataCreationGrid[col, row].Value == null ? "" : DataCreationGrid[col, row].Value; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } */ }
public void ExportToExcel(DataGridView dgv, string fileName) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = fileName + "_" + DateTime.Now.ToString("yyyyMMdd-ffff"); if (sfd.ShowDialog() == DialogResult.OK) { // Copy DataGridView results to clipboard CopyAlltoClipboard(dgv); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //// Format column D as text before pasting results, this was required for my data //Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; //rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); //Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; //delRng.Delete(Type.Missing); //xlWorkSheet.get_Range("A1").Select(); xlWorkSheet.Columns.AutoFit(); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); RelComObject(xlWorkSheet); RelComObject(xlWorkBook); RelComObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); dgv.ClearSelection(); // Open the newly saved excel file try { if (System.IO.File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } }
private void btnExportToExcel_Click(object sender, EventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; if (sfd.ShowDialog() == DialogResult.OK) { // Copy DataGridView results to clipboard copyAlltoClipboard(); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // For some reason column A is always blank in the worksheet. //// Delete blank column A and select cell A1 Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); // Delete column "Action" Excel.Range delRng2 = xlWorkSheet.get_Range("E:E").Cells; delRng2.Delete(Type.Missing); // Format header column to BOLD Excel.Range formatRange; formatRange = xlWorkSheet.get_Range("A1"); formatRange.EntireRow.Font.Bold = true; // Dong 1, cot 5 //xlWorkSheet.Cells[1, 5] = "Bold"; // Auto fit each cell ((Excel.Range)xlWorkSheet.Columns[1]).AutoFit(); ((Excel.Range)xlWorkSheet.Columns[2]).AutoFit(); ((Excel.Range)xlWorkSheet.Columns[3]).AutoFit(); ((Excel.Range)xlWorkSheet.Columns[4]).AutoFit(); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); // Clear Clipboard and DataGridView selection Clipboard.Clear(); dgPhoneBook.ClearSelection(); // Open the newly saved excel file if (File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } } }
private void ExportToExcel(object sender) { #region Basis for export operation app = new Excel.Application { Visible = false, SheetsInNewWorkbook = 1, DisplayAlerts = false }; app.WorkbookAfterSave += WorkbookAfterSave; ((DataGridView)sender).SelectAll(); DataObject obj = ((DataGridView)sender).GetClipboardContent(); ((DataGridView)sender).ClearSelection(); if (obj.Equals(null)) { _ = MessageBox.Show( "Export table internal error", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, 0); throw new ArgumentNullException(nameof(obj)); } else { Clipboard.SetDataObject(obj, false, 1, 0); } workbook = app.Workbooks.Add(); worksheet = app.ActiveSheet; worksheet.PasteSpecial( Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); #endregion #region Personalization app.Goto("C1:C26"); app.Selection.Columns.AutoFit(); app.Selection.Rows.Autofit(); //worksheet.Columns.EntireColumn.AutoFit(); worksheet.Name = "Report table"; worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; worksheet.PageSetup.PrintGridlines = true; worksheet.PageSetup.BlackAndWhite = true; worksheet.PageSetup.BottomMargin = 20.0; worksheet.PageSetup.LeftMargin = 20.0; worksheet.PageSetup.RightMargin = 20.0; worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3; worksheet.PageSetup.TopMargin = 20.0; worksheet.Range["A1"].Select(); worksheet.Rows[1].Font.Bold = true; #endregion #region Select save format if (exportFileDialog.ShowDialog().Equals(DialogResult.OK)) { switch (exportFileDialog.FilterIndex) { case 1: workbook.SaveAs(exportFileDialog.FileName, app.DefaultSaveFormat); break; case 2: workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, exportFileDialog.FileName); break; case 3: workbook.SaveAs(exportFileDialog.FileName, Excel.XlFileFormat.xlCSV); break; case 4: workbook.SaveAs(exportFileDialog.FileName, Excel.XlFileFormat.xlCurrentPlatformText); break; case 5: workbook.SaveAs(exportFileDialog.FileName, Excel.XlFileFormat.xlHtml); break; case 6: workbook.SaveAs(exportFileDialog.FileName, Excel.XlFileFormat.xlOpenXMLStrictWorkbook); break; case 7: workbook.SaveAs(exportFileDialog.FileName, Excel.XlFileFormat.xlXMLSpreadsheet); break; case 8: workbook.SaveAs(exportFileDialog.FileName, Excel.XlFileFormat.xlOpenXMLWorkbook); break; default: break; } } #endregion }
//click button to name file and export the data public void buttonExportExcelFile_Click_1(object sender, EventArgs e) { //set cursor as hourglas Cursor.Current = Cursors.WaitCursor; System.Windows.Forms.Application.DoEvents(); //disable the button for the duration of the download this.buttonExportExcelFile.Enabled = false; copyAllStatestoClipboard(); string filename = "qtrreport"; //creating excel application (workbook and worksheets) object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel.Worksheet ws_1 = null; Microsoft.Office.Interop.Excel.Worksheet ws_2 = null; ws_1 = wb.Sheets[1]; ws_1.Name = "States"; //format the columns for (int i = 1; i < dataGridViewStates.Columns.Count + 1; i++) { Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, i]; xlRange.Font.Bold = -1; xlRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; xlRange.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; xlRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)ws_1.Cells[1, 1]; CR.Select(); ws_1.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); ws_1.get_Range("A1").Select(); // autofit the columns ws_1.Columns.AutoFit(); //turn off excel wrap feature ws_1.Rows.WrapText = false; Clipboard.Clear(); copyAllNonStatestoClipboard(); //creating a second sheet in the workbook int count = wb.Worksheets.Count; //loading sheet 2 ws_2 = wb.Worksheets.Add(Type.Missing, wb.Worksheets[count], Type.Missing, Type.Missing); ws_2.Name = "Non States"; //format the columns fro worksheet 2 - Non States for (int i = 1; i < dataGridViewNonStates.Columns.Count + 1; i++) { Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)excel.Cells[1, i]; xlRange.Font.Bold = -1; xlRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; xlRange.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; xlRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } Microsoft.Office.Interop.Excel.Range CR2 = (Microsoft.Office.Interop.Excel.Range)ws_2.Cells[1, 1]; CR2.Select(); ws_2.PasteSpecial(CR2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); ws_2.get_Range("A1").Select(); // autofit the columns ws_2.Columns.AutoFit(); //turn off excel wrap feature ws_2.Rows.WrapText = false; //Activate Sheet 1 (With States) in the Workbook ws_1.Activate(); //Save File if (Directory.Exists("X:\\Public\\Accounting\\04 - FUNCTIONAL AREAS\\FDB\\CathyFDBReport\\")) { //get date string and append it to filename to prevent overwriting file DateTime localDate = DateTime.Now; string localDateString = localDate.ToString("yyyyMMddHHmmss"); excel.ActiveWorkbook.SaveCopyAs("X:\\Public\\Accounting\\04 - FUNCTIONAL AREAS\\FDB\\CathyFDBReport\\" + filename + "_" + localDateString + ".xlsx"); } else { Directory.CreateDirectory("X:\\Public\\Accounting\\04 - FUNCTIONAL AREAS\\FDB\\CathyFDBReport\\"); //get date string and append it to filename to prevent overwriting file DateTime localDate_2 = DateTime.Now; string localDateString_2 = localDate_2.ToString("yyyyMMddHHmmss"); excel.ActiveWorkbook.SaveCopyAs("X:\\Public\\Accounting\\04 - FUNCTIONAL AREAS\\FDB\\CathyFDBReport\\" + filename + "_" + localDateString_2 + ".xlsx"); } excel.ActiveWorkbook.Saved = true; System.Windows.Forms.Application.DoEvents(); //foreach (Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL")) //{ // proc.Kill(); //} //open the directory of the downloaded file string path = @"X:\Public\Accounting\04 - FUNCTIONAL AREAS\FDB\CathyFDBReport\"; System.Diagnostics.Process.Start(path); this.buttonExportExcelFile.Enabled = true; Cursor.Current = Cursors.Default; }
private void btnExportToExcel_Click(object sender, EventArgs e) { TabPage tbSelected = tcMain.SelectedTab; DataGridView dgvToCopy = new DataGridView(); foreach (Control ctMain in tbSelected.Controls) { foreach (Control ctSubLevel1 in ctMain.Controls) { Type type = ctSubLevel1.GetType(); if (ctSubLevel1.GetType().Name == "DataGridView") { dgvToCopy = (DataGridView)ctSubLevel1; } } } dgvToCopy.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText; dgvToCopy.MultiSelect = true; dgvToCopy.SelectAll(); DataObject dataObj = dgvToCopy.GetClipboardContent(); if (dataObj != null) { Clipboard.SetDataObject(dataObj); } SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = tcMain.SelectedTab.Text + ".xls"; if (sfd.ShowDialog() == DialogResult.OK) { object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // Delete blank column A and select cell A1 Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); dgvToCopy.ClearSelection(); } else { Clipboard.Clear(); dgvToCopy.ClearSelection(); } }
private void Save(DataGridView dgv, string path) { try { savingRightNow = true; dgv.MultiSelect = true; // Copy DataGridView results to clipboard dgv.SelectAll(); DataObject dataObj = dgvContent.GetClipboardContent(); if (dataObj != null) { Clipboard.SetDataObject(dataObj); } object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application { DisplayAlerts = false // Without this you will get two confirm overwrite prompts }; Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column as text before pasting results, this was required for my data var rangeColumnsNames = new string[] { "A:A", "B:B", "C:C", "D:D", "E:E", "F:F", "G:G", "H:H", "I:I", "G:G", "K:K", "L:L", "M:M", "N:N", "O:O", "P:P", "Q:Q", "R:R", "S:S", "T:T", "U:U", "V:V", "W:W", "X:X", "Y:Y", "Z:Z" }; if (dgv.Columns.Count > rangeColumnsNames.Length) { throw new Exception(string.Format("Количество столбцов {0} превышает допустимый предел для сохранения {1}." , dgv.Columns.Count, rangeColumnsNames.Length)); } Excel.Range rng = xlWorkSheet.get_Range(rangeColumnsNames[dgv.Columns.Count]).Cells; rng.NumberFormat = "@"; for (int i = 0; i < dgv.Columns.Count; i++) { var column = dgv.Columns[i]; xlWorkSheet.Cells[1, i + 2] = column.HeaderCell.Value.ToString(); } // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[2, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯ // Delete blank column A and select cell A1 Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); // Save the excel file under the captured location from the SaveFileDialog xlexcel.DisplayAlerts = false; xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, true, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing); //xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); ReleaseObject(xlWorkSheet); ReleaseObject(xlWorkBook); ReleaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); dgv.ClearSelection(); dgv.MultiSelect = false; hasUnsavedChanges = false; } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка при сохранении файла.", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { savingRightNow = false; } }
public void CreateExcel(CapitalFrm cform, string filepath, string filename) { object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application() { DisplayAlerts = false }; //Add workbook Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); // Loop through DGVS for (int i = 1; i < 4; ++i) { // Copy DataGridView results to clipboard switch (i) { case 1: //cform.dgvCostView.RowHeadersVisible = false; //No hidden row CopyAlltoClipboard(cform.dgvCostView); break; case 2: CopyAlltoClipboard(cform.dgvFeedView); break; case 3: CopyAlltoClipboard(cform.dgvFinalView); break; } //select sheet Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(i); // Paste clipboard results to worksheet range xlWorkSheet.Activate(); Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); xlWorkSheet.get_Range("A2").Select(); ReleaseObject(xlWorkSheet); ReleaseObject(CR); } // Save the excel file xlWorkBook.SaveAs(Path.Combine(filepath, filename), Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); ReleaseObject(xlWorkBook); ReleaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); cform.dgvFinalView.ClearSelection(); cform.dgvFeedView.ClearSelection(); cform.dgvCostView.ClearSelection(); }
private void finishButton_Click(object sender, EventArgs e) { //create cashflow worksheet string name = "Cash Flow"; bool exists = false; Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; foreach (Worksheet sheets in wb.Worksheets) { if (sheets.Name.Equals(name)) { sheets.Activate(); sheets.Visible = XlSheetVisibility.xlSheetVisible; exists = true; sheets.Cells.ClearContents(); } } if (!exists) { { Worksheet newWorksheet; newWorksheet = (Worksheet)Globals.ThisAddIn.Application.Worksheets.Add(); newWorksheet.Name = name; } } DialogResult dialogResult = MessageBox.Show("Would you like to add a total column?", "Row Totals", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { dataGridView1.Columns.Add("Total", "Total"); dataGridView2.Columns.Add("", ""); dataGridView3.Columns.Add("", ""); dataGridView4.Columns.Add("", ""); dataGridView5.Columns.Add("", ""); int x = dataGridView1.ColumnCount - 1; double total = 0.0; foreach (DataGridViewRow row in dataGridView1.Rows) { try { int y = row.Index; for (int i = 1; i < x; i++) { string a = dataGridView1[i, y].Value.ToString(); double d = Convert.ToDouble(a); total += d; } dataGridView1[x, y].Value = total.ToString(); total = 0; } catch { } } foreach (DataGridViewRow row in dataGridView2.Rows) { int y = row.Index; for (int i = 1; i < x; i++) { total += Convert.ToDouble(dataGridView2[i, y].Value.ToString()); } dataGridView2[x, y].Value = total.ToString(); total = 0; } foreach (DataGridViewRow row in dataGridView3.Rows) { try { int y = row.Index; for (int i = 1; i < x; i++) { total += Convert.ToDouble(dataGridView3[i, y].Value.ToString()); } dataGridView3[x, y].Value = total.ToString(); total = 0; } catch { } } foreach (DataGridViewRow row in dataGridView4.Rows) { int y = row.Index; for (int i = 1; i < x; i++) { total += Convert.ToDouble(dataGridView4[i, y].Value.ToString()); } dataGridView4[x, y].Value = total.ToString(); total = 0; } foreach (DataGridViewRow row in dataGridView5.Rows) { int y = row.Index; for (int i = 1; i < x; i++) { total += Convert.ToDouble(dataGridView5[i, y].Value.ToString()); } dataGridView5[x, y].Value = total.ToString(); total = 0; } } // Excel Heading int range = 1; char c = 'A'; string cellRngLength = ((char)(c + (dataGridView1.ColumnCount - 1))).ToString(); cellrg = cellRngLength + range; Worksheet ws = Globals.ThisAddIn.GetWorksheet(); ws.Cells[1, 1] = "Cash Flow Forecast"; string cellRng = "A" + range; ws.Range[cellRng].Font.Size = 20; ws.Range[cellRng].Font.Bold = true; // Excel Subheading range++; ws.Cells[2, 1] = "Income"; cellRng = "A" + range; ws.Range[cellRng].Font.Size = 14; //Extract Income range++; cellRng = "A" + range; dataGridView1.RowHeadersVisible = false; dataGridView1.SelectAll(); DataObject dataObj = dataGridView1.GetClipboardContent(); if (dataObj != null) { Clipboard.SetDataObject(dataObj); } ws.Range[cellRng].Select(); ws.PasteSpecial(ws.Range[cellRng], Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); dataGridView1.RowHeadersVisible = true; range += dataGridView1.RowCount; cellRng = "A" + range; cellrg = cellRngLength + range; Borders border = ws.Range[cellRng, cellrg].Borders; border[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlDouble; // Extract Total dataGridView2.RowHeadersVisible = false; dataGridView2.SelectAll(); dataObj = dataGridView2.GetClipboardContent(); if (dataObj != null) { Clipboard.SetDataObject(dataObj); } ws.Range[cellRng].Select(); ws.PasteSpecial(ws.Range[cellRng], Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); dataGridView2.RowHeadersVisible = true; // Excel Subheading range++; ws.Cells[range, 1] = "Expenses"; cellRng = "A" + range; ws.Range[cellRng].Font.Size = 14; //Extract Expenses range++; cellRng = "A" + range; dataGridView3.RowHeadersVisible = false; dataGridView3.SelectAll(); dataObj = dataGridView3.GetClipboardContent(); if (dataObj != null) { Clipboard.SetDataObject(dataObj); } ws.Range[cellRng].Select(); ws.PasteSpecial(ws.Range[cellRng], Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); dataGridView3.RowHeadersVisible = true; range += dataGridView3.RowCount - 1; cellRng = "A" + range; cellrg = cellRngLength + range; border = ws.Range[cellRng, cellrg].Borders; border[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlDouble; // Extract Total dataGridView4.RowHeadersVisible = false; dataGridView4.SelectAll(); dataObj = dataGridView4.GetClipboardContent(); if (dataObj != null) { Clipboard.SetDataObject(dataObj); } ws.Range[cellRng].Select(); ws.PasteSpecial(ws.Range[cellRng], Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); dataGridView4.RowHeadersVisible = true; // Excel Subheading range++; ws.Cells[range, 1] = "Total"; cellRng = "A" + range; ws.Range[cellRng].Font.Size = 14; //Extract Total range++; cellRng = "A" + range; dataGridView5.RowHeadersVisible = false; dataGridView5.SelectAll(); dataObj = dataGridView5.GetClipboardContent(); if (dataObj != null) { Clipboard.SetDataObject(dataObj); } ws.Range[cellRng].Select(); ws.PasteSpecial(ws.Range[cellRng], Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); dataGridView5.RowHeadersVisible = true; range++; cellRng = "A" + range; cellrg = cellRngLength + range; ws.Range["B4", cellrg].NumberFormat = ThisAddIn.currency + "#,###.00"; string topR = cellRngLength + "3"; border = ws.Range[topR, cellrg].Borders; border[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous; ws.Range["A1"].EntireColumn.AutoFit(); this.Hide(); }
public static void ExportToExcel(DataGridView A) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = "Harmonogram.xls"; if (sfd.ShowDialog() == DialogResult.OK) { // Copy DataGridView results to clipboard copyAlltoClipboard(A); object misValue = System.Reflection.Missing.Value; Excel.Application xlexcel = new Excel.Application(); xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column D as text before pasting results, this was required for my data //Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; //rng.NumberFormat = "@"; xlWorkSheet.Cells[1, 2] = "Nr"; xlWorkSheet.Cells[1, 3] = "Data Płatności"; xlWorkSheet.Cells[1, 4] = "Saldo"; xlWorkSheet.Cells[1, 5] = "Kapitał"; xlWorkSheet.Cells[1, 6] = "Odsetki"; xlWorkSheet.Cells[1, 7] = "Rata"; // Paste clipboard results to worksheet range Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[2, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯ // Delete blank column A and select cell A1 Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); // Clear Clipboard and DataGridView selection Clipboard.Clear(); A.ClearSelection(); // Open the newly saved excel file if (File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } } }