public void exportToExcel(System.Windows.Controls.DataGrid grid, string fileName = "Excel") { try { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = fileName + ".xls"; if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK) { // Copy DataGridView results to clipboard copyAlltoClipboard(grid); 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 Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column D as text before pasting results, this was required for my data Microsoft.Office.Interop.Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.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 Microsoft.Office.Interop.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, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.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.Clipboard.Clear(); // roomsGrid.ClearSelection(); // Open the newly saved excel file if (File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } } } catch (Exception e) { System.Windows.MessageBox.Show(e.ToString()); } }
private void OutputAsExcelFile() { //将datagridView中的数据导出到一张表中 DataTable tempTable = this.exporeDataToTable(this.dataGridView1); //导出信息到Excel表 Microsoft.Office.Interop.Excel.ApplicationClass myExcel; Microsoft.Office.Interop.Excel.Workbooks myWorkBooks; Microsoft.Office.Interop.Excel.Workbook myWorkBook; Microsoft.Office.Interop.Excel.Worksheet myWorkSheet; char myColumns; Microsoft.Office.Interop.Excel.Range myRange; object[,] myData = new object[500, 35]; int i, j;//j代表行,i代表列 myExcel = new Microsoft.Office.Interop.Excel.ApplicationClass(); //显示EXCEL myExcel.Visible = true; if (myExcel == null) { MessageBox.Show("本地Excel程序无法启动!请检查您的Microsoft Office正确安装并能正常使用", "提示"); return; } myWorkBooks = myExcel.Workbooks; myWorkBook = myWorkBooks.Add(System.Reflection.Missing.Value); myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets[1]; myColumns = (char)(tempTable.Columns.Count + 64); //设置列 myRange = myWorkSheet.get_Range("A4", myColumns.ToString() + "5"); //设置列宽 int count = 0; //设置列名 foreach (DataColumn myNewColumn in tempTable.Columns) { myData[0, count] = myNewColumn.ColumnName; count = count + 1; } //输出datagridview中的数据记录并放在一个二维数组中 j = 1; foreach (DataRow myRow in tempTable.Rows) //循环行 { for (i = 0; i < tempTable.Columns.Count; i++) //循环列 { myData[j, i] = myRow[i].ToString(); } j++; } //将二维数组中的数据写到Excel中 myRange = myRange.get_Resize(tempTable.Rows.Count + 1, tempTable.Columns.Count);//创建列和行 myRange.Value2 = myData; myRange.EntireColumn.AutoFit(); myRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //删除前方空白三行 Microsoft.Office.Interop.Excel.Range deleteRng = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.Rows[1, System.Type.Missing]; deleteRng.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); Microsoft.Office.Interop.Excel.Range deleteRng1 = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.Rows[1, System.Type.Missing]; deleteRng1.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); Microsoft.Office.Interop.Excel.Range deleteRng2 = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.Rows[1, System.Type.Missing]; deleteRng2.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); }
static public void DeleteManager(int code) { int j = 0; for (int i = 0; i < dataManagers.Rows.Count; i++) { if (Convert.ToInt32(dataManagers[0, i].Value) == code) { j = i; dataManagers.Rows.RemoveAt(i); break; } } string filename = "E:\\Shop\\Данные\\Managers.xlsx"; Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook; Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet; ExcelWorkBook = ExcelApp.Workbooks.Open(filename); ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1); Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)ExcelWorkSheet.Rows[j + 1]; rg.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); ExcelWorkBook.Save(); ExcelApp.Quit(); }
public void TransferringDatagridviewtoExcel(DataGridView dataGridView, string FileName) { try { DialogResult dialogResult = MessageBox.Show("Kayıtları Excel'e Aktarılsın mı?", "Excel Aktarma", MessageBoxButtons.YesNo, MessageBoxIcon.Information); if (dialogResult == DialogResult.Yes) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = FileName + ".xls"; if (sfd.ShowDialog() == DialogResult.OK) { copyAlltoClipboard(dataGridView); object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application(); xlexcel.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); Microsoft.Office.Interop.Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; rng.NumberFormat = "@"; Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); Microsoft.Office.Interop.Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); xlWorkBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlexcel.DisplayAlerts = true; xlWorkBook.Close(true, misValue, misValue); xlexcel.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); Clipboard.Clear(); dataGridView.ClearSelection(); if (File.Exists(sfd.FileName)) { System.Diagnostics.Process.Start(sfd.FileName); } } } } catch (Exception) { MessageBox.Show("Excel'e Aktarılma Sırasında Hata Oluştu."); } }
/// <summary> /// 在工作表中删除行 /// </summary> /// <param name="sheet">当前工作表</param> /// <param name="rowIndex">欲删除的行索引</param> public void DeleteRows(Microsoft.Office.Interop.Excel.Worksheet sheet, int rowIndex) { try { Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[rowIndex, Type.Missing]; range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); range = null; } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } }
private void shrani_tabelo_bt_Click(object sender, EventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel Documents (*.xls)|*.xls"; sfd.FileName = "tabela_ahp_metode.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 Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column D as text before pasting results, this was required for my data Microsoft.Office.Interop.Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1]; CR.Select(); xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true); Microsoft.Office.Interop.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, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.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); } } }
/// <summary> /// 删除行 /// </summary> /// <param name="sheetIndex"></param> /// <param name="rowIndex"></param> /// <param name="count"></param> public void DeleteRows(int rowIndex, int count) { try { for (int i = 0; i < count; i++) { range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[rowIndex, this.missing]; range.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlDown); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } }
public void DeleteEmptyRows(Microsoft.Office.Interop.Excel.Worksheet sh) { Microsoft.Office.Interop.Excel.Range currentRow = null; var rows = sh.Rows; var currentIndex = sh.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell).Row; var contentFound = false; while (!contentFound && currentIndex > 0) { currentRow = rows[currentIndex]; if (excelapp.WorksheetFunction.CountA(currentRow) == 0) { currentRow.Delete(); } //else contentFound = true; Marshal.FinalReleaseComObject(currentRow); currentIndex--; } }
private void button2_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); ds.ReadXml(Application.StartupPath + "\\ExcelBindingXml.xml"); Microsoft.Office.Interop.Excel.Application m_objExcel = null; Microsoft.Office.Interop.Excel._Workbook m_objBook = null; Microsoft.Office.Interop.Excel.Sheets m_objSheets = null; Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null; Microsoft.Office.Interop.Excel.Range m_objRange = null; object m_objOpt = System.Reflection.Missing.Value; try { m_objExcel = new Microsoft.Office.Interop.Excel.Application(); m_objBook = m_objExcel.Workbooks.Open(Application.StartupPath + "\\ExcelTemplate.xlsx", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1)); int maxRow = m_objSheet.UsedRange.Rows.Count; int maxCol = m_objSheet.UsedRange.Columns.Count; int TitleRow = 0; for (int excelrow = 1; excelrow <= maxRow; excelrow++) { for (int col = 0; col < maxCol; col++) { string excelColName = ExcelColNumberToColText(col); m_objRange = m_objSheet.get_Range(excelColName + excelrow.ToString(), m_objOpt); m_objRange.Text.ToString().Contains("$"); TitleRow = excelrow; break; } } for (int excelrow = 1; excelrow <= ds.Tables[0].Rows.Count; excelrow++) { DataRow dr = ds.Tables[0].Rows[excelrow - 1]; for (int col = 0; col < maxCol; col++) { string excelColName = ExcelColNumberToColText(col); m_objRange = m_objSheet.get_Range(excelColName + TitleRow.ToString(), m_objOpt); //Microsoft.Office.Interop.Excel.Range item_objRange = m_objSheet.get_Range(excelColName + (maxRow + excelrow).ToString(), m_objOpt); if (m_objRange.Text.ToString().Replace("$", "") == ds.Tables[0].Columns[col].ColumnName) { m_objSheet.Cells[maxRow + excelrow, col + 1].value = dr[col].ToString(); m_objSheet.Cells[maxRow + excelrow, col + 1].Style = m_objRange.Style; Console.WriteLine(m_objSheet.Cells[maxRow + excelrow, col + 1].value + ":" + dr[col].ToString()); //item_objRange.Value2 = dr[col].ToString(); } } } Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)m_objSheet.Rows[TitleRow, m_objOpt]; range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); m_objExcel.DisplayAlerts = false; m_objBook.SaveAs(Application.StartupPath + "\\ExcelBindingXml.xlsx", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { m_objBook.Close(m_objOpt, m_objOpt, m_objOpt); m_objExcel.Workbooks.Close(); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); m_objBook = null; m_objExcel = null; GC.Collect(); } }
/// <summary> /// 删除行 /// </summary> /// <param name="sheet">worksheet</param> /// <param name="rowIndex">行数</param> public static void DeleteRows(this Microsoft.Office.Interop.Excel.Worksheet sheet, int rowIndex) { Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[rowIndex, Type.Missing]; range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); }
private void btnPrint_Click(object sender, EventArgs e) { int customer_index = 0; customer_index = dataGridView1.Columns["Customer"].Index; for (int i = 0; i < dataGridView1.Rows.Count; i++) { string temp = dataGridView1.Rows[i].Cells[customer_index].Value.ToString(); temp = temp.Trim(); dataGridView1.Rows[i].Cells[customer_index].Value = temp; } string FileName = @"C:\temp\temp.xls"; // Copy DataGridView results to clipboard dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText; dataGridView1.SelectAll(); DataObject dataObj = dataGridView1.GetClipboardContent(); if (dataObj != null) { Clipboard.SetDataObject(dataObj); } 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 Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // Format column D as text before pasting results, this was required for my data Microsoft.Office.Interop.Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells; rng.NumberFormat = "@"; // Paste clipboard results to worksheet range Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.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 Microsoft.Office.Interop.Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells; delRng.Delete(Type.Missing); xlWorkSheet.get_Range("A1").Select(); Microsoft.Office.Interop.Excel.Worksheet ws = xlexcel.ActiveWorkbook.Worksheets[1]; Microsoft.Office.Interop.Excel.Range range = ws.UsedRange; //ws.Columns.ClearFormats(); //ws.Rows.ClearFormats(); //range.EntireColumn.AutoFit(); //range.EntireRow.AutoFit(); xlWorkSheet.Range["A1:H1"].Interior.Color = System.Drawing.Color.LightSkyBlue; xlWorkSheet.Columns[2].ColumnWidth = 98.14; xlWorkSheet.Columns[2].WrapText = true; xlWorkSheet.Range["H1:H300"].NumberFormat = "£#,###,###.00"; ws.Columns.AutoFit(); ws.Rows.AutoFit(); range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; range.Borders.Color = ColorTranslator.ToOle(Color.Black); // Save the excel file under the captured location from the SaveFileDialog xlWorkBook.SaveAs(FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.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(FileName)) { System.Diagnostics.Process.Start(FileName); } Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks workbooks; Microsoft.Office.Interop.Excel.Workbook excelBook; //app = null; //app = new Excel.Application(); // create a new instance excelApp.DisplayAlerts = false; //turn off annoying alerts that make me want to cryyyy uint processID = 0; workbooks = excelApp.Workbooks; excelBook = workbooks.Add(FileName); Microsoft.Office.Interop.Excel.Sheets sheets = excelBook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)(sheets[1]); //Range.Rows.AutoFit(); //Range.Columns.AutoFit(); }