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