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