private void button2_Click(object sender, EventArgs e) { SFD.Filter = "Файлы Excel (*.xls; *.xlsx) | *.xls; *.xlsx"; if (SFD.ShowDialog() == DialogResult.OK) { Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; app.Visible = true; worksheet = workbook.ActiveSheet; worksheet.Name = "Выручка"; worksheet.Cells[1, 1] = "Выручка"; for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) { worksheet.Cells[2, i] = dataGridView1[i - 1, 0].Value; worksheet.Columns[i].ColumnWidth = 30; } for (int i = 1; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { worksheet.Cells[i + 2, j + 1] = dataGridView1[j, i].Value; } } workbook.SaveAs(SFD.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); app.Quit(); } }
private void ThisAddIn_Startup(object sender, System.EventArgs e) { //Access vsto application Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application; //Access workbook Microsoft.Office.Interop.Excel.Workbook workbook = app.ActiveWorkbook; //Access worksheet Microsoft.Office.Interop.Excel.Worksheet m_sheet = workbook.Worksheets[1]; //Access vsto worksheet Microsoft.Office.Tools.Excel.Worksheet sheet = Globals.Factory.GetVstoObject(m_sheet); //Place some text in cell A1 without wrapping Microsoft.Office.Interop.Excel.Range cellA1 = sheet.Cells.get_Range("A1"); cellA1.Value = "Sample Text Unwrapped"; //Place some text in cell A5 with wrapping Microsoft.Office.Interop.Excel.Range cellA5 = sheet.Cells.get_Range("A5"); cellA5.Value = "Sample Text Wrapped"; cellA5.WrapText = true; //Save the workbook workbook.SaveAs("OutputVsto.xlsx"); //Quit the application app.Quit(); }
public bool LimpiaExcel() { exHoja1 = null; exApli = null; exLibro = null; GC.Collect(); return(true); }
private void ThisAddIn_Startup(object sender, System.EventArgs e) { //Document Explorer--------------------------------------------------------- de = new DocumentExplorer(); ctp = this.CustomTaskPanes.Add(de, "Document Explorer"); ctp.VisibleChanged += new EventHandler(ctp_VisibleChanged); Microsoft.Office.Interop.Excel.Application excelApp = Globals.ThisAddIn.Application; //-------------------------------------------------------------------------- }
public Parse_Form(Microsoft.Office.Interop.Excel.Application excel) { _excel0 = excel; _excel1 = excel; _excel2 = excel; _excel3 = excel; _excel4 = excel; InitializeComponent(); refedit1._Excel = _excel0; refedit2._Excel = _excel1; refedit3._Excel = _excel2; refedit4._Excel = _excel3; refedit5._Excel = _excel4; }
private void button2_Click(object sender, EventArgs e) { //получение значения ИТОГО con = ClassSQL.GetConect(); string sc_selectID = "select sum(price) as 'Выручка за период' from ticket t join film_session s on s.session_id = t.session_id and t.payment = 1 and t.date_ticket <= dateadd(month," + znach + ", getdate()) and t.date_ticket >= dateadd(month, -" + znach + ", getdate());"; con.Open(); scom3 = new SqlCommand(sc_selectID, con); itogo = scom3.ExecuteScalar().ToString(); con.Close(); con.Dispose(); SFD.Filter = "Файлы Excel (*.xls; *.xlsx) | *.xls; *.xlsx"; if (SFD.ShowDialog() == DialogResult.OK) { Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; app.Visible = true; worksheet = workbook.ActiveSheet; worksheet.Name = "Выручка"; worksheet.Cells[1, 1] = "Фильм"; worksheet.Cells[1, 2] = "Количество проданных билетов"; worksheet.Cells[1, 3] = "Сумма(в руб.)"; worksheet.Cells[12, 1] = "Итого:"; worksheet.Cells[12, 3] = itogo; for (int i = 1; i < dataGridView1.Columns.Count + 1; i++) { worksheet.Cells[2, i] = dataGridView1[i - 1, 0].Value; worksheet.Columns[i].ColumnWidth = 30; } for (int i = 1; i < dataGridView1.RowCount; i++) { for (int j = 0; j < dataGridView1.ColumnCount; j++) { worksheet.Cells[i + 2, j + 1] = dataGridView1[j, i].Value; } } workbook.SaveAs(SFD.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); app.Quit(); } }
public bool AbreExcel(bool vAdd) { exApli = new Microsoft.Office.Interop.Excel.Application(); exApli.Visible = false; if (_Fichero != "") { if (File.Exists(_Fichero)) { if (vAdd) { exLibro = exApli.Workbooks.Open(_Fichero, vk_update_links, vk_No_read_only, vk_format, vk_password, vk_write_res_password, vk_ignore_read_only_recommend, vk_origin, vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru, vk_local, vk_corrupt_load); } else { File.Delete(_Fichero); exLibro = exApli.Workbooks.Add(vk_missing); } } else { exLibro = exApli.Workbooks.Add(vk_missing); } } else { exLibro = exApli.Workbooks.Add(vk_missing); } //if (_Fichero != "") //{ // exApli.Visible = true; //} //else //{ // exApli.Visible = true; //} exApli.Visible = false; return(true); }
public bool CierraExcel(bool vSave) { if (vSave) { //if (File.Exists(_Fichero)) { File.Delete(_Fichero); } exLibro.SaveAs(_Fichero + ".xls", Excel.XlFileFormat.xlWorkbookNormal, vk_missing, vk_missing, vk_missing, vk_missing, Excel.XlSaveAsAccessMode.xlExclusive, vk_missing, vk_missing, vk_missing, vk_missing, vk_missing); //csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue } exLibro.Close(vSave, vk_missing, vk_missing); exApli.Workbooks.Close(); exApli.Quit(); exHoja1 = null; exApli = null; exLibro = null; GC.Collect(); return(true); }
private void ThisAddIn_Startup(object sender, System.EventArgs e) { //Access vsto application Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application; //Access workbook Microsoft.Office.Interop.Excel.Workbook workbook = app.ActiveWorkbook; //Access worksheet Microsoft.Office.Interop.Excel.Worksheet m_sheet = workbook.Worksheets[1]; //Access vsto worksheet Microsoft.Office.Tools.Excel.Worksheet worksheet = Globals.Factory.GetVstoObject(m_sheet); //Access cells A1, A2, A3 , A4 Microsoft.Office.Interop.Excel.Range cellA1 = worksheet.Range["A1"]; Microsoft.Office.Interop.Excel.Range cellA2 = worksheet.Range["A2"]; Microsoft.Office.Interop.Excel.Range cellA3 = worksheet.Range["A3"]; Microsoft.Office.Interop.Excel.Range cellA4 = worksheet.Range["A4"]; //Set integer values in cells A1, A2 and A3 cellA1.Value = 10; cellA2.Value = 20; cellA3.Value = 30; //Add formula in cell A4 cellA4.Formula = "=Sum(A1:A3)"; //Set the font bold in cell A4 cellA4.Font.Bold = true; //Set the background color to Yellow in cell A4 cellA4.Interior.Color = Excel.XlRgbColor.rgbYellow; //Save the workbook workbook.SaveAs("OutputVsto.xlsx"); //Quit the application app.Quit(); }
private void Exportara_Exel()// Método para exportar a excel. { Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; try { worksheet = workbook.ActiveSheet; worksheet.Name = "Libro1"; int cellRowIndex = 2; //ok int cellColumnIndex = 1; //ok //Pasa por cada fila y lee el valor de cada columna. for (int i = -1; i < dgv_activos.Rows.Count - 0; i++) //Primera y ultima fila { for (int j = 0; j < dgv_activos.Columns.Count - 0; j++) //Columnas lado izquierdo y derecho { // El índice de Excel comienza desde 1,1. Como first Row tendría los encabezados de Columna, agregando una verificación de condición. if (cellRowIndex == 2) { worksheet.Cells[cellRowIndex, cellColumnIndex] = dgv_activos.Columns[j].HeaderText; } else { worksheet.Cells[cellRowIndex, cellColumnIndex] = dgv_activos.Rows[i].Cells[j].Value.ToString(); } cellColumnIndex++; } cellColumnIndex = 1;//ok cellRowIndex++; } excel.Visible = true; } catch (Exception error) { //MessageBox.Show("No se exportó correctamente" + error.Message); } }
private static void ShipToTransCost_01() { Cursor.Current = Cursors.WaitCursor; isShipToTransCost_Running = true; DateTime d = DateTime.Now; CultureInfo provider = CultureInfo.InvariantCulture; Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application; Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet; int lastR = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1; string log = String.Format("Старт...\r\nВсего строк для обработки: {0}\r\n", lastR - 7); int err = 0; try { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); for (int curR = 10; curR <= lastR; curR++) { try { string sql = string.Format("EXEC [dbo].[ShipTo_GetTransportCost] @Code = N'{0}'", ws.Cells[curR, 1].Text.Trim()); using (SqlCommand cmd = new SqlCommand() { CommandType = CommandType.Text, Connection = conn, CommandText = sql }) { var r = cmd.ExecuteScalar(); decimal res = 0; if (decimal.TryParse(r.ToString(), out res)) { ws.Cells[curR, 17].Value = res; } else { ws.Cells[curR, 17].Value = ""; } } } catch { err++; log += String.Format("Ошибка импорта строки {0}\r\n", curR); } if (formProgress.InvokeRequired) { formProgress.BeginInvoke( new System.Action(() => { formProgress.SetProgress(curR, 7, lastR); formProgress.SetLog(log); } )); } } conn.Close(); } } catch (Exception ex) { err++; log += String.Format("Ошибка подключения к БД\r\n {0}\r\n", ex.Message); } DateTime d2 = DateTime.Now; log += String.Format("Импорт завершен.\r\nВремя обработки {0:0} секунд.Количество ошибок: {1}", (d2 - d).TotalSeconds, err); if (formProgress.InvokeRequired) { formProgress.BeginInvoke( new System.Action(() => { formProgress.CloseButtonEnable(true); formProgress.SetLog(log); } )); } Cursor.Current = Cursors.Default; }
private void exportToExcel_Click(DataGridView transcationTableDataGridView) { try { Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; app.Visible = true; worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; worksheet.Name = "Records"; try { /*for (int i = 0; i < transcationTableDataGridView.Columns.Count; i++) * { * worksheet.Cells[1, i + 1] = transcationTableDataGridView.Columns[i].HeaderText; * }*/ for (int i = 0; i < transcationTableDataGridView.Rows.Count; i++) { for (int j = 0; j < transcationTableDataGridView.Columns.Count; j++) { if (transcationTableDataGridView.Rows[i].Cells[j].Value != null && !transcationTableDataGridView.Rows[i].Cells[j].Value.ToString().Equals("")) { worksheet.Cells[i + 1, j + 1] = transcationTableDataGridView.Rows[i].Cells[j].Value.ToString(); /*Excel.Range ColorMeMine= worksheet.Cells[i + 1, j + 1]; * int ind = -1,ptr=0,color; * foreach (var index in transcationTableDataGridView.Rows[i].Cells[j].Value.ToString().findAll(Environment.NewLine)) * { * switch (ptr) { * case 0: color=System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); * break; * case 1: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); * break; * case 2: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange); * break; * case 3: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); * break; * case 4: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue); * break; * case 5: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Cyan); * break; * case 6: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Magenta); * break; * case 7: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gold); * break; * case 8: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Navy); * break; * case 9: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Fuchsia); * break; * case 10: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Coral); * break; * case 11: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Indigo); * break; * case 12: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Lime); * break; * case 13: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Firebrick); * break; * case 14: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Tomato); * break; * default: * color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); * break; * } * ColorMeMine.Characters[ind+1, index].Font.Color = color; * ind = index; * ptr = ptr + 1; * }*/ } else { worksheet.Cells[i + 1, j + 1] = ""; } } } //Getting the location and file name of the excel to save from user. SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveDialog.FilterIndex = 2; if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { app.Quit(); workbook = null; worksheet = null; } } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } }
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType) { Microsoft.Office.Interop.Excel.Application excel; Microsoft.Office.Interop.Excel.Workbook excelworkBook; Microsoft.Office.Interop.Excel.Worksheet excelSheet; Microsoft.Office.Interop.Excel.Range excelCellrange; try { // Start Excel and get Application object. excel = new Microsoft.Office.Interop.Excel.Application(); // for making Excel visible excel.Visible = false; excel.DisplayAlerts = false; // Creation a new Workbook excelworkBook = excel.Workbooks.Add(Type.Missing); // Workk sheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet; excelSheet.Name = worksheetName; excelSheet.Cells[1, 1] = ReporType; excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString(); // loop through each row and add values to our sheet int rowcount = 2; foreach (DataRow datarow in dataTable.Rows) { rowcount += 1; for (int i = 1; i <= dataTable.Columns.Count; i++) { // on the first iteration we add the column headers if (rowcount == 3) { excelSheet.Cells[2, i] = dataTable.Columns[i-1].ColumnName; excelSheet.Cells.Font.Color = System.Drawing.Color.Black; } excelSheet.Cells[rowcount, i] = datarow[i-1].ToString(); //for alternate rows if (rowcount > 3) { if (i == dataTable.Columns.Count) { if (rowcount % 2 == 0) { excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black,false); } } } } } // now we resize the columns excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; excelCellrange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); //now save the workbook and exit Excel excelworkBook.SaveAs(saveAsLocation);; excelworkBook.Close(); excel.Quit(); return true; } catch (Exception ex) { MessageBox.Show(ex.Message); return false; } finally { excelSheet = null; excelCellrange = null; excelworkBook = null; } }
public ExcelHelper() { excelApp = new Microsoft.Office.Interop.Excel.Application(); excelApp.Visible = false; excelApp.DisplayAlerts = false; }
public void AddVBAForExcel(string text, string newText, string excelPath) { VBIDE.VBComponent oModule; Office.CommandBar oCommandBar; Office.CommandBarButton oCommandBarButton; String sCode; Object oMissing = System.Reflection.Missing.Value; Workbooks workbooks = excelApp.Workbooks; Workbook workbook = excelApp.Workbooks.Open(excelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Create a new VBA code module. oModule = workbook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule); sCode = "Function VBAMacro()\r\n" + " Dim Cz As String\r\n" + " Dim Th As String\r\n" + " Cz = \"" + text + "\"\r\n" + " Th = \"" + newText + "\"\r\n" + " Cells.Replace What:=Cz, Replacement:=Th, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False\r\n" + "VBAMacro=4 \r\n" + "end Function "; // Add the VBA macro to the new code module. oModule.CodeModule.AddFromString(sCode); try { // Create a new toolbar and show it to the user. oCommandBar = excelApp.CommandBars.Add("VBAMacroCommandBar", oMissing, oMissing); oCommandBar.Visible = false; // Create a new button on the toolbar. oCommandBarButton = (Office.CommandBarButton)oCommandBar.Controls.Add( Office.MsoControlType.msoControlButton, oMissing, oMissing, oMissing, oMissing); // Assign a macro to the button. oCommandBarButton.OnAction = "VBAMacro"; // Set the caption of the button. oCommandBarButton.Caption = "Call VBAMacro"; // Set the icon on the button to a picture. oCommandBarButton.FaceId = 2151; } catch (Exception eCBError) { //已经存在了 } excelApp.UserControl = true; int strCount = 0; Sheets sheets = workbook.Sheets; ExcelMacroHelper ex = new ExcelMacroHelper(excelApp, workbook); object outStr = null; object[] objs = new object[0]; string cc = ""; foreach (Worksheet sheet in sheets) { string sheetName = sheet.Name; sheet.Activate(); string vbaName = "VBAMacro"; ex.RunExcelMacro(excelPath, vbaName, objs, outStr, false); cc += outStr; } workbook.Save(); workbook.Close(false, Type.Missing, Type.Missing); // Release the variables. oCommandBarButton = null; oCommandBar = null; oModule = null; workbook = null; excelApp = null; // Collect garbage. GC.Collect(); }
private static void ImportSPSR_Step01() { Cursor.Current = Cursors.WaitCursor; isImportSPSR_Running = true; DateTime d = DateTime.Now; CultureInfo provider = CultureInfo.InvariantCulture; Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application; Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet; int lastR = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1; string log = String.Format("Старт...\r\nВсего строк для обработки: {0}\r\n", lastR - 7); int err = 0; for (int curR = 7; curR <= lastR; curR++) { try { Invoice inv = new Invoice(); inv.BoxQty = 1; inv.Date = DateTime.FromOADate(ws.Cells[curR, 1].Value2); inv.InvoiceNo = ws.Cells[curR, 2].Text.Trim(); inv.OrderCode = ws.Cells[curR, 3].Text.Trim(); inv.CityFrom = ws.Cells[curR, 4].Text.Trim().ToUpper(); inv.CityTo = ws.Cells[curR, 5].Text.Trim().ToUpper(); inv.Weight = Convert.ToDecimal(ws.Cells[curR, 6].Value2); inv.VolumeWeight = Convert.ToDecimal(ws.Cells[curR, 7].Value2); inv.Amount = Convert.ToDecimal(ws.Cells[curR, 9].Value2 * 1.18); InsertInvoice(inv, "SPSR"); } catch { err++; log += String.Format("Ошибка импорта строки {0}\r\n", curR); } if (formProgress.InvokeRequired) { formProgress.BeginInvoke( new System.Action(() => { formProgress.SetProgress(curR, 7, lastR); formProgress.SetLog(log); } )); } } DateTime d2 = DateTime.Now; log += String.Format("Импорт завершен.\r\nВремя обработки {0:0} секунд.\r\nКоличество ошибок: {1}", (d2 - d).TotalSeconds, err); if (formProgress.InvokeRequired) { formProgress.BeginInvoke( new System.Action(() => { formProgress.CloseButtonEnable(true); formProgress.SetLog(log); } )); } Cursor.Current = Cursors.Default; }
//Run through all the files in the directory, and find and replace public void find_replace() { string path = ""; // prompt the user to select the folder path using (var fbd = new FolderBrowserDialog()) { DialogResult result = fbd.ShowDialog(); if (result == DialogResult.OK && !string.IsNullOrWhiteSpace(fbd.SelectedPath)) { string[] files = Directory.GetFiles(fbd.SelectedPath); path = fbd.SelectedPath; System.Windows.Forms.MessageBox.Show("Files found: " + files.Length.ToString() + " @ path: " + path, "Message"); } } object m = Type.Missing; // Interaction.Inputbox is a user prompt, the user chooses the string he/she would like to replace string replace = Interaction.InputBox("Type the text you would like to replace", "Find text", "Default", -1, -1); string replacement = Interaction.InputBox("Replace that text with", "Replace text", "Default", -1, -1); var xlApp = new Microsoft.Office.Interop.Excel.Application(); // grab all the excel files at that directory DirectoryInfo d = new DirectoryInfo(@path); FileInfo[] listOfFiles_1 = d.GetFiles("*.xlsx").ToArray(); FileInfo[] listOfFiles_2 = d.GetFiles("*.xls").ToArray(); FileInfo[] listOfFiles_3 = d.GetFiles("*.xlsm").ToArray(); FileInfo[] listOfFiles_4 = d.GetFiles("*.xltx").ToArray(); FileInfo[] listOfFiles_5 = d.GetFiles("*.xltm").ToArray(); FileInfo[] listOfFiles_6 = d.GetFiles("*.xlt").ToArray(); // produce a list of files in an array FileInfo[] listOfFiles = (listOfFiles_1.Concat(listOfFiles_2)).ToArray(); listOfFiles = (listOfFiles.Concat(listOfFiles_3)).ToArray(); listOfFiles = (listOfFiles.Concat(listOfFiles_4)).ToArray(); listOfFiles = (listOfFiles.Concat(listOfFiles_5)).ToArray(); listOfFiles = (listOfFiles.Concat(listOfFiles_6)).ToArray(); xlApp.DisplayAlerts = false; // traverse through each file int count = 0; foreach (FileInfo file in listOfFiles) { var xlWorkBook = xlApp.Workbooks.Open(file.FullName); string file_name = file.FullName.Remove(file.FullName.Length - 5); string file_ext = file.FullName.Substring(file.FullName.Length - 4); // traverse through each worksheet in each file foreach (Excel.Worksheet xlWorkSheet in xlWorkBook.Worksheets) { // get the used range. Excel.Range r = (Excel.Range)xlWorkSheet.UsedRange; Excel.Range first = r.Find(replace, m, m, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, m, m, m); if (first != null) { count++; Excel.Range start = first; do { start.Value = start.Value.Replace(replace, replacement); count++; start = r.FindNext(m); // if file is xltx, has to save as to overwrite the new changes if (file_ext == "xltx") { xlWorkBook.SaveAs(@file_name, Excel.XlFileFormat.xlOpenXMLTemplate, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } // if file is xltm, has to save as to overwrite the new changes else if (file_ext == "xltm") { xlWorkBook.SaveAs(@file_name, Excel.XlFileFormat.xlOpenXMLTemplateMacroEnabled, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } else { xlWorkBook.Save(); } }while (start != first && start != null); } /* * // call the replace method to replace instances. * bool success = (bool)r.Replace( * replace, * replacement, * Excel.XlLookAt.xlPart, * Excel.XlSearchOrder.xlByRows, false, m, m, m); */ } xlWorkBook.Close(); } MessageBox.Show(count + " replacements has been made"); xlApp.Quit(); Marshal.ReleaseComObject(xlApp); }