public void Delete(DeleteShiftDirection deleteShiftDirection) { AssertNotDisposed(); Excel.Range _range = _GetRange(); _range.Delete(EnumConvert.ConvertDeleteShiftDirection(deleteShiftDirection)); Marshal.ReleaseComObject(_range); }
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); } } }
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 = "C:\\Users\\Angela\\Documents\\visual studio 2015\\Projects\\turfirm\\turfirm\\bin\\Debug\\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(); }
//deleting range in a certain range public void deleteCells(int r) { try { Console.WriteLine("trying to delete correct range"); //building range for delete string rangeToDel = "A4:"; if (r < 4) { Console.WriteLine("index too small!"); return; } //building string string lastRow = r.ToString(); rangeToDel = rangeToDel + "R" + lastRow; Console.WriteLine("Range: " + rangeToDel); //get the correct range Excel.Range toDel = xWorksheet.get_Range(rangeToDel); Console.WriteLine("got range object"); //delete range and shift cells up toDel.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); Console.WriteLine("Range deleted"); } catch (Exception ex) { Console.WriteLine(ex.Message); Console.WriteLine("Error trying to delete range"); } }
/// <summary> /// 删除一行 /// </summary> /// <param name="CellRowID">第一个要删除行的索引位置,删除后其原有行上移</param> /// <param name="RowNum">要删除行的个数</param> public void DeleteRow(int CellRowID, int RowNum) { if (CellRowID <= 0) { throw new Exception("行索引超出范围!"); } if (RowNum <= 0) { throw new Exception("插入行数无效!"); } try { excelRange = (Excel.Range)excelWorkSheet.Rows[CellRowID, Missing.Value]; for (int i = 0; i < RowNum; i++) { excelRange.Delete(Excel.XlDirection.xlUp); } excelRange = null; } catch (Exception e) { CloseExcelApplication(); throw new Exception(e.Message); } }
static void OpenFile() { string[] excelFiles = Directory.GetFiles(Environment.CurrentDirectory + "\\Enrolments\\", "*.xls", SearchOption.AllDirectories); foreach (var file in excelFiles) { var excelApp = new Excel.Application(); excelApp.Visible = false; Excel.Workbook book = excelApp.Workbooks.Open(file); Excel.Worksheet sheet = book.ActiveSheet; var cellValue = (string)(sheet.Cells[1, 1] as Excel.Range).Value; if (cellValue.Contains("LEARNER DETAIL REPORT")) { // Delete three rows from the worksheet starting from the 10th row. Excel.Range range = sheet.get_Range("A1:A5", Type.Missing).EntireRow; range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); book.Save(); } book.Close(); excelApp.Quit(); } }
public void Delete() { AssertNotDisposed(); Excel.Range _range = _GetRange(); _range.Delete(); Marshal.ReleaseComObject(_range); }
/// <summary> /// 保存发货清单excel文件 /// </summary> /// <param name="objProject"></param> /// <param name="dgvCeilingPackingList"></param> /// <returns></returns> public bool ExecSaveCeilingPackingList(Project objProject, DataGridView dgv) { Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); string excelBookPath = Environment.CurrentDirectory + "\\CeilingPackingList.xlsx"; Workbook workBook = excelApp.Workbooks.Add(excelBookPath); Microsoft.Office.Interop.Excel.Worksheet workSheet = excelApp.Worksheets[1]; //将区域添加到字典中并计数 Dictionary <string, int> locationList = new Dictionary <string, int>(); for (int i = 0; i < dgv.RowCount; i++) { if (locationList.ContainsKey(dgv.Rows[i].Cells["Location"].Value.ToString())) { locationList[dgv.Rows[i].Cells["Location"].Value.ToString()] += 1; } else { locationList.Add(dgv.Rows[i].Cells["Location"].Value.ToString(), 1); } } int startRow = 0; int endRow = 0; int countRow = 0; foreach (var item in locationList) { endRow = startRow + item.Value; workSheet.Cells[1, 1] = objProject.ODPNo + "-天花烟罩发货清单(Ceiling Hood Packing List)-" + item.Key; workSheet.Cells[2, 3] = objProject.ProjectName; workSheet.Cells[3, 3] = DateTime.Now.ToShortDateString(); workSheet.Cells[4, 3] = dgv.Rows[1].Cells["UserAccount"].Value; FillCeilingPackingListDate(workSheet, dgv, startRow, endRow); //预览 //excelApp.Visible = true; //excelApp.Sheets.PrintPreview(true); //打印 //workSheet.PrintOutEx(); //另存为 string excelPath = @"D:\MyProjects\" + objProject.ODPNo; if (!Directory.Exists(excelPath)) { Directory.CreateDirectory(excelPath); } workBook.SaveAs(excelPath + @"\" + objProject.ODPNo + "-" + item.Key + "天花烟罩发货清单.xlsx", XlFileFormat.xlOpenXMLWorkbook); Microsoft.Office.Interop.Excel.Range range = workSheet.Rows["7:" + (item.Value + 7), Missing.Value]; range.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlDown); startRow = endRow; } KillProcess(excelApp); excelApp = null; //对象置空 GC.Collect(); //垃圾回收机制 return(true); }
/// <summary> /// This method deletes the Invisible trash from a given excel File /// </summary> /// <param name="pathToExcelFile"></param> /// <returns>String with confirmation Text</returns> public string EraseExcelInvisibleTrash(string pathToExcelFile) { //I create an instance of a Microsoft Excel Application Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application(); //Set visible to false to let the app work "behind".. a.k.a not OPEN the excel application myExcel.Visible = false; //Using the excel application instance, I open the book with the path to my excel file as parameter Microsoft.Office.Interop.Excel.Workbook workbook = myExcel.Workbooks.Open(pathToExcelFile); //Later I use a Worksheet Instance to get the actual sheet in the Excel File Worksheet worksheet = myExcel.ActiveSheet; //In this worksheet, in the Name property we can find the name of the Excel active Worksheet //variable to Hold the last used row int lastUsedRow = 0; //variable to hold the last used column int lastUsedColumn = 0; //Find the real last row used in the excel file lastUsedRow = worksheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row; // Find the last real column lastUsedColumn = worksheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByColumns, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column; //This variable holds the last available row/column in an Excel File: XFD 1048576 string lastColumnAvailable = "XFD1048576"; //with this lines, Im creating both ranges, the horizontal and vertical to basically have the "empty ranges of the excel" Microsoft.Office.Interop.Excel.Range horizontalEmptyRange = worksheet.Range[ColumnNumberToName(lastUsedColumn + 1) + "1", lastColumnAvailable]; Microsoft.Office.Interop.Excel.Range verticalEmptyRange = worksheet.Range["A" + lastUsedRow + 1, lastColumnAvailable]; //I delete both empty ranges to clear the computational trash the excel files holds, so its "clean" and can be exported to a database without data type conversion problems horizontalEmptyRange.Delete(XlDeleteShiftDirection.xlShiftUp); verticalEmptyRange.Delete(XlDeleteShiftDirection.xlShiftUp); //I change the status of saved to true workbook.Saved = true; //next i save the workbook workbook.Save(); //We close our workbook at the end of our process workbook.Close(); myExcel.Quit(); //As a sideNote, Excel InteropServices don't release the object in memory, there's an instance of Excel still running //We use Marchal Final Release to release the object and close the Excel Process Marshal.FinalReleaseComObject(worksheet); Marshal.FinalReleaseComObject(workbook); Marshal.FinalReleaseComObject(myExcel); string resultado = "Invisible Trash Removed"; return(resultado); }
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!"; } }
bool removeRowByNumber(int targetRow) { Excel.Range xlRows = xlWorksheet.Rows; Excel.Range xlRow = (Excel.Range)xlRows[targetRow, System.Reflection.Missing.Value]; xlRow.Delete(); return(true); }
bool removeColumnByNumber(int targetColumn) { Excel.Range xlColumns = xlWorksheet.Columns; Excel.Range xlColumn = (Excel.Range)xlColumns[targetColumn, System.Reflection.Missing.Value]; xlColumn.Delete(); return(true); }
// Вставка текста в ячейки книги // string[] columns: // 0 - мин. дата по таблице (Книга покупок) // 1 - макс. дата по таблице (Книга покупок) // 2 - ГлавБух (Книга покупок) // 3 - Покупатель (Книга покупок) // 4 - ИНН и КПП в формате "ИНН / КПП" (Книга покупок) // 5 - Продавец (Книга продаж) // 6 - ИНН и КПП в формате "ИНН / КПП" (Книга продаж) // 7 - Данные компании формата "Имя, ИНН, КПП, Адрес" (Прайс-лист) // 8 - Реквизиты компании формата "Имя банка, р/c, к/c" (Счет - накладная) // 9 - ФИО менеджера (Счет - накладная) // 10 - Номер операции (Счет - накладная) // 11 - Данные компании формата "Имя, Адрес" (ТОРГ-12) // 12 - Кол-во записей (ТОРГ-12) // 13 - Данные контрагента формата "Имя, Адрес, Реквизиты" (ТОРГ-12) // 14 - Автосалон (ТОРГ-12) // 15 - ФИО менеджера (ТОРГ-12) // 16 - Выбранная дата с (Отчет по продажам) // 17 - Выбранная дата по (Отчет по продажам) // 18 - Данные продавца формата "Имя, ИНН, КПП, Адрес" (Счет - накладная) // 19 - Реквизиты продавца формата "Имя банка, р/c, к/c" (Счет - накладная) // 20 - Данные контрагента формата "Имя, ИНН, КПП, Адрес" (Счет - накладная) // 21 - Реквизиты контрагента формата "Имя банка, р/с, к/с"(Счет - накладная) // 22 - ФИО кассира (Товарный чек) // 23 - Имя компании (ТОРГ-13) // 24 - Из автосалона (ТОРГ-13) // 25 - В автосалон (ТОРГ-13) // 26 - isPurchase // Быстро экспортирует данные, но не сохраняет форматирование при сдвиге, так что нужно: // 1) Сохранять форматирование автоматически или сделать ручное форматирование, + (Сделал ручное форматирование) // 2) Сделать подсчет суммы в конце таблицы у каждого отдельного файла, + (Метод готов, осталось заполнить) // 3) Сделать возможность печати всех документов из одного метода, + (Почти готово, осталось форматирование) // 4) Заполнить одну таблицу множеством данных (10 тыс. - 50 тыс. строк) для показа на защите диплома. --- // 5) Полное форматирование, --- // 6) Закрытие Excel чтобы он не оставался в памяти. --- // // Файлы в которых проходит суммирование: // 1) Книга покупок.xlsx + // 2) Книга продаж.xlsx // 3) Отчет по продажам.xlsx // 4) Счет - накладная.xlsx // 5) Товарный чек.xlsx // 6) ТОРГ-12 (Товарная накладная).xlsx // 7) ТОРГ-13 (Перемещение).xlsx public static void ExportToExcel(Form form, DataGridView dgv, int fRow, int fColumn, string nameFile, bool getSum, string[] columns) { string baseDirectory = System.AppDomain.CurrentDomain.BaseDirectory; string path = baseDirectory + "doc//" + nameFile; int firstColumn = fColumn; // Первый столбец int firstRow = fRow; // Первая строка int lastRow = firstRow + dgv.RowCount; // Последняя строка int lastColumn = firstColumn + dgv.ColumnCount; // Последний столбец Excel.Application xl = new Excel.Application(); // Создаем экземпляр Excel'а try { Excel.Workbook wb = (Excel.Workbook)xl.Workbooks.Open(path, false, false); // Открываем файл Excel Excel.Worksheet ws = (Excel.Worksheet)wb.ActiveSheet; // Указываем ссылку на активный лист файла Excel form.Cursor = Cursors.WaitCursor; // Заполняем массив данными object[,] rc = new object[dgv.RowCount, dgv.ColumnCount]; for (int i = 0; i < dgv.RowCount; i++) { for (int j = 0; j < dgv.ColumnCount; j++) { rc[i, j] = dgv.Rows[i].Cells[j].Value; } } // Вставка текста в ячейки шаблона pasteTextToExcel(ws, nameFile, columns, true); // Добавляем пустые строки со сдвигом вниз Excel.Range insertColumn = ws.Range[ws.Cells[firstRow, firstColumn], ws.Cells[lastRow - 2, lastColumn - 1]]; insertColumn.Insert(Excel.XlDirection.xlDown); // Вставляем текст из массива в Excel Excel.Range pasteText = ws.Range[ws.Cells[firstRow, firstColumn], ws.Cells[lastRow - 1, lastColumn - 1]]; pasteText.Font.Bold = false; pasteText.Font.Name = "Arial"; pasteText.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, rc); GetSum(ws, firstRow, lastRow, getSum, nameFile); // Форматирование документа // Удаляем последнюю пустую строку со сдвигом вверх Excel.Range deleteColumn = ws.Range[ws.Cells[lastRow - 1, firstColumn], ws.Cells[lastRow - 1, lastColumn]]; deleteColumn.Delete(Excel.XlDirection.xlUp); form.Cursor = Cursors.Default; xl.Visible = true; // Делаем книгу Excel видимой xl.UserControl = true; // Делаем книгу Excel активной } catch (Exception ex) { MessageBox.Show(ex.Message + "\n" + ex, "Ошибка"); } }
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); } } }
/// <summary> /// CheckForHeaders ensures the first row has been remove so headers are now in the top row. /// </summary> /// <param name="newbornWorksheet">Worksheet named Newborns_3</param> private static void CheckforHeaders(Excel.Worksheet newbornWorksheet) { Excel.Range cells = newbornWorksheet.Range["A1"]; if (cells.Value.ToString() == HeaderToRemove) { Excel.Range toDel = cells.EntireRow; toDel.Delete(); } }
/// <summary> /// Удаление пустой строки /// </summary> /// <param name="colRange">столбец в excel</param> /// <param name="row">номер строки</param> private void deleteNullRow(Excel.Range colRange, int row) { Excel.Range rangeCol = (Excel.Range)m_wrkSheet.Columns[1]; while (Convert.ToString(((Excel.Range)rangeCol.Cells[row]).Value) == "") { Excel.Range rangeRow = (Excel.Range)m_wrkSheet.Rows[row]; rangeRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); } }
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); } } }
/// <summary> /// Remove staff from the excel file database /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void removeBTN_Click(object sender, EventArgs e) { Cursor.Current = Cursors.WaitCursor; //Get the selected index int selected = this.employeeComboBox.SelectedIndex; //If we are in the first one we throw a error message if (selected == 0) { MetroMessageBox.Show(this, "Please select a employee!", "Invalid Employee", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { //get the name from the selected position Excel.Range nameToDelete = (Excel.Range)dataBaseSheet.get_Range("A" + (selected + 1), "A" + (selected + 1)); //save it for now string oldName = nameToDelete.Value2.ToString(); //Delete and move it up nameToDelete.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); //Save the data masterlog.DisplayAlerts = false; masterWorkBook.Save(); //Display the sucessful message MetroMessageBox.Show(this, oldName + " was successfully added. ", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Information); nameToDelete = null; //Get the last row number int lastRow = dataBaseSheet.UsedRange.Rows.Count; //Refresh the employee combo box. employeeRange = dataBaseSheet.get_Range("A1", "A" + (lastRow)); //Convert to an array employeeArray = (System.Array)employeeRange.Cells.Value2; //Update the combo box employeeComboBox.Items.Clear(); foreach (object s in employeeArray) { if (s != null) { this.employeeComboBox.Items.Add(s.ToString()); } } //Select the default value to display this.employeeComboBox.SelectedIndex = 0; employeeRange = null; } Cursor.Current = Cursors.Default; }
private void DeleteExcelLine(Excel.Worksheet xlsSheet, int sline, int eline) { Excel.Range xlsAreaRng = null; Excel.Range xlsRngS = null; Excel.Range xlsRngE = null; xlsRngS = xlsSheet.Cells[sline, 1]; xlsRngE = xlsSheet.Cells[eline, 100]; xlsAreaRng = xlsSheet.get_Range(xlsRngS, xlsRngE); xlsAreaRng.Delete(); }
/// <summary> /// 删除指定区域行,若针对单个单元格进行删除,则设置结束结束单元格为空 /// </summary> /// <param name="beginCell">开始单元格</param> /// <param name="endCell">结束单元格</param> public void deleteRow(string beginCell, string endCell) { Excel.Range range = null; if (string.IsNullOrEmpty(endCell)) { range = sheet.get_Range(beginCell, miss); } else { range = sheet.get_Range(beginCell, endCell); } range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); }
public void DeleteRange(int startRow, int startColumn, int endRow, int endColumn, bool IsDeleteEntireRow) { excel.Range range = App.get_Range(App.Cells[startRow, startColumn], App.Cells[endRow, endColumn]); range.Select(); if (IsDeleteEntireRow) { range.EntireRow.Delete(excel.XlDeleteShiftDirection.xlShiftUp); //是否整行删除 } else { range.Delete(excel.XlDeleteShiftDirection.xlShiftUp); } }
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); }
private static void CreateWS(Excel.Workbook wb, List <InvoiceDPD> Invoices) { string tmpFile = Path.GetTempFileName(); File.WriteAllBytes(tmpFile, Properties.Resources.DPD); Excel.Workbook wbT = Globals.ThisAddIn.Application.Workbooks.Add(tmpFile); Excel.Worksheet wsT = wbT.Worksheets["Отчет Филуэт"]; int totalSheets = wb.Worksheets.Count; wsT.Copy(After: wb.Worksheets[totalSheets]); Excel.Worksheet ws = wb.Worksheets[wsT.Name]; wbT.Close(); File.Delete(tmpFile); List <InvoiceDPD> _invoices = new List <InvoiceDPD>(); _invoices = Invoices.OrderBy(i => i.Branch).ThenBy(i => i.OrderDate).ToList(); int row = 2; Excel.Range rng = ws.Rows[row].EntireRow; foreach (InvoiceDPD inv in _invoices) { rng = ws.Rows[row].EntireRow; rng.Copy(ws.Rows[row + 1]); //rng.Insert(Excel.XlInsertShiftDirection.xlShiftDown,true); ws.Cells[row, 1].Value2 = inv.Branch; ws.Cells[row, 2].Value2 = inv.InvoiceNo; ws.Cells[row, 3].Value2 = inv.OrderNo; ws.Cells[row, 4].Value2 = inv.OrderDate; ws.Cells[row, 5].Value2 = inv.BoxQty; ws.Cells[row, 6].Value2 = inv.Weight; ws.Cells[row, 7].Value2 = inv.DeliveryCost; ws.Cells[row, 8].Value2 = inv.DeliveryCostVAT; ws.Cells[row, 9].Value2 = inv.PickCost; ws.Cells[row, 10].Value2 = inv.PickCostVAT; ws.Cells[row, 11].Value2 = inv.DeliveryCost + inv.PickCost; ws.Cells[row, 12].Value2 = inv.DeliveryCostVAT + inv.PickCostVAT; row++; } rng = ws.Rows[row].EntireRow; rng.Delete(); }
/// <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 = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; range.Delete(Excel.XlDirection.xlDown); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } }
private static bool PostImport_02(Excel.Worksheet ws, DataTable dt) { int row = 2; bool res = true; RuPostQueryAPI api = new RuPostQueryAPI(); try { Excel.Range rng = ws.Rows[row].EntireRow; foreach (DataRow drow in dt.Rows) { rng = ws.Rows[row].EntireRow; rng.Copy(ws.Rows[row + 1]); ws.Cells[row, 1].Value2 = drow["InputDate"]; ws.Cells[row, 2].Value2 = drow["ExecuteDate"]; ws.Cells[row, 3].Value2 = drow["OrderCode"]; ws.Cells[row, 5].Value2 = drow["Post_index"]; ws.Cells[row, 6].Value2 = drow["Post_region"]; ws.Cells[row, 7].Value2 = drow["Post_place"]; int boxid = 0; bool rr = int.TryParse(drow["boxPostID"].ToString(), out boxid); if (boxid != 0) { PostSearch ps = api.SearchRPO(drow["boxPostID"].ToString()); if (ps != null) { ws.Cells[row, 4].Value2 = ps.Barcode; ws.Cells[row, 12].Value2 = ps.HumanOperationName; ws.Cells[row, 11].Value2 = ps.LastOperDate; ws.Cells[row, 9].Value2 = ps.TotalRateWoVat / 100; } } row++; formProgress.SetProgress(row - 2, 0, dt.Rows.Count); } rng = ws.Rows[row].EntireRow; rng.Delete(); } catch (Exception ex) { formProgress.SetLog("Error: " + ex.Message); res = false; } return(res); }
public void DeleteScheduleRow(DataGridView dataGridView) { FileInfo _file = new FileInfo(@"schedule/schedule2.xlsx"); Excel.Application ObjExcel = new Excel.Application(); //Открываем книгу Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(_file.FullName); //Выбираем таблицу(лист) Excel.Worksheet ObjWorkSheet; ObjWorkSheet = (Excel.Worksheet)ObjWorkBook.Sheets[1]; int ind = dataGridView.SelectedCells[0].OwningRow.Index; Excel.Range rg = (Excel.Range)ObjWorkSheet.Rows[ind + 1]; rg.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); ObjWorkBook.Save(); ObjExcel.Quit(); }
private void DeleteWordFromExcel(WordData word) { Excel.Range wordCell = null; foreach (Excel.Worksheet sheet in dictionary.excelObj.Sheets) { Excel.Range temp = sheet.Cells.Find(word.word); if (temp != null) { wordCell = temp; break; } } if (wordCell != null) { Excel.Range wordRow = wordCell.EntireRow; wordRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); } }
private static void LvOrclStock_BulkOrderMismatch_FillExcel(Excel.Worksheet ws, DataSet ds) { if (ds == null || ds.Tables.Count != 2) { MessageBox.Show("Нет данных"); return; } DataTable dtHeader = ds.Tables[0]; DataTable dtDetail = ds.Tables[1]; ws.Cells[1, 2].Value2 = dtHeader.Rows[0]["OrderNo"]; ws.Cells[2, 2].Value2 = dtHeader.Rows[0]["InputDate"]; ws.Cells[3, 2].Value2 = dtHeader.Rows[0]["ShipDate"]; if (ds.Tables[1].Rows.Count == 0) { MessageBox.Show("Расхождений не обнаружено"); return; } int row = 6; Excel.Range rngDetail = ws.Rows[row].EntireRow; foreach (DataRow drow in dtDetail.Rows) { rngDetail = ws.Rows[row].EntireRow; rngDetail.Copy(ws.Rows[row + 1]); ws.Cells[row, 1].Value2 = drow["StockCode"]; ws.Cells[row, 2].Value2 = drow["SellCode"]; ws.Cells[row, 3].Value2 = drow["Lot"]; ws.Cells[row, 4].Value2 = drow["Qty"]; ws.Cells[row, 5].Value2 = drow["System"]; if (drow["System"].ToString().Equals("Oracle")) { ws.Rows[row].EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.MintCream); } row++; } rngDetail = ws.Rows[row].EntireRow; rngDetail.Delete(); }
private void bDelSubTC_Click(object sender, EventArgs e) { if (!initTestcaseBox()) { return; } DialogResult rst = MessageBox.Show( "删除后不可恢复,是否继续?", "注意!", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk); if (DialogResult.No == rst) { return; } Excel.Worksheet sheet = app.ActiveWorkbook.ActiveSheet as Excel.Worksheet; try { int rows = sheet.UsedRange.Rows.Count; for (int i = rows; i > 1; i--) { Excel.Range tmprange = sheet.Range["A" + i, Type.Missing].EntireRow; Array values = (Array)tmprange.Cells.Value2; Object c1 = values.GetValue(1, 1); Object c2 = values.GetValue(1, 2); if (c1 == null && c2 == null) { continue; } if (c1 == null) { tmprange.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); } } } catch (Exception ex) { this.AppendLine(ex.StackTrace); } }
private static void LvOrclStock_StockBulkMismatch_FillExcel(Excel.Worksheet ws, DataSet ds) { if (ds == null || ds.Tables.Count != 2) { MessageBox.Show("Нет данных"); return; } DataTable dtHeader = ds.Tables[0]; DataTable dtDetail = ds.Tables[1]; ws.Cells[1, 2].Value2 = dtHeader.Rows[0]["StockOrclDateTime"]; ws.Cells[2, 2].Value2 = dtHeader.Rows[0]["Today"]; if (ds.Tables[1].Rows.Count == 0) { MessageBox.Show("Расхождений не обнаружено"); return; } int row = 5; Excel.Range rngDetail = ws.Rows[row].EntireRow; foreach (DataRow drow in dtDetail.Rows) { rngDetail = ws.Rows[row].EntireRow; rngDetail.Copy(ws.Rows[row + 1]); ws.Cells[row, 1].Value2 = drow["LVLocations"]; ws.Cells[row, 2].Value2 = drow["OrclLocator"]; ws.Cells[row, 3].Value2 = drow["StockCode"]; ws.Cells[row, 4].Value2 = drow["SellCode"]; ws.Cells[row, 5].Value2 = drow["Lot"]; ws.Cells[row, 6].Value2 = drow["ExpDate"]; ws.Cells[row, 7].Value2 = drow["OrclQty"]; ws.Cells[row, 8].Value2 = drow["LvQty"]; row++; } rngDetail = ws.Rows[row].EntireRow; rngDetail.Delete(); }
/// <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 = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; range.Delete(Excel.XlDirection.xlDown); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } }
/// <summary> /// 删除行 /// </summary> /// <param name="sheetIndex"></param> /// <param name="rowIndex"></param> /// <param name="count"></param> public void DeleteRows(int sheetIndex, int rowIndex, int count) { if (sheetIndex > this.WorkSheetCount) { this.KillExcelProcess(); throw new Exception("索引超出范围,WorkSheet索引不能大于WorkSheet数量!"); } try { workSheet = (Excel.Worksheet)workBook.Worksheets[sheetIndex]; range = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; for (int i = 0; i < count; i++) { range.Delete(Excel.XlDirection.xlDown); } } catch (Exception e) { this.KillExcelProcess(); throw e; } }
/// <summary> /// 删除行 /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void DeleteRows(int rowIndex, int count) { try { for (int n = 1; n <= this.WorkSheetCount; n++) { workSheet = (Excel.Worksheet)workBook.Worksheets[n]; range = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; for (int i = 0; i < count; i++) { range.Delete(Excel.XlDirection.xlDown); } } } catch (Exception e) { this.KillExcelProcess(); throw e; } }