using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Drawing;
namespace xlUpdate
{
class Program
{
static void Main(string[] args)
{
string folderpath = "d:\\Editted_old";
DirectoryInfo di = new DirectoryInfo(folderpath);
FileInfo[] files = di.GetFiles("*.xlsx");
foreach (FileInfo singleFile in files)
{
Excel.Application oExcel = new Excel.Application();
//specify the file name where its actually exist
string filepath = singleFile.FullName;
//pass that to workbook object
Excel.Workbook WB = oExcel.Workbooks.Open(filepath);
object missing = System.Reflection.Missing.Value;
// statement get the workbookname
string ExcelWorkbookname = WB.Name;
// statement get the worksheet count
int worksheetcount = WB.Worksheets.Count;
for (int ws = 1; ws <= worksheetcount; ws++)
{
Excel.Worksheet wks = (Excel.Worksheet)WB.Worksheets[ws];
// statement get the firstworksheetname
int linkCount = wks.UsedRange.Cells.Hyperlinks.Count;
for (int i = 1; i <= linkCount; i++)
{
object index = (object)i;
string currLink = wks.UsedRange.Cells.Hyperlinks[i].Address;
string TTD = wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay;
if (currLink == "https://www.google.co.in")
{
wks.UsedRange.Cells.Hyperlinks[i].Address = "https://www.google.com";
wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay = "Google";
}
else
{
wks.UsedRange.Cells.Hyperlinks[i].Address = "https://www.yahoo.com";
wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay = "Yahoo";
}
}
}
//we are saving the editted file into new folder"Editted" from old folder "Editted_old"
object UpdatedFile = singleFile.FullName.Replace("Editted_old", "Editted");
WB.SaveAs(UpdatedFile, missing,
missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
WB.Close(true, missing, missing);
oExcel.Quit();
}
}
}
}
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Drawing;
namespace xlUpdate
{
class Program
{
static void Main(string[] args)
{
string folderpath = "d:\\Editted_old";
DirectoryInfo di = new DirectoryInfo(folderpath);
FileInfo[] files = di.GetFiles("*.xlsx");
foreach (FileInfo singleFile in files)
{
Excel.Application oExcel = new Excel.Application();
//specify the file name where its actually exist
string filepath = singleFile.FullName;
//pass that to workbook object
Excel.Workbook WB = oExcel.Workbooks.Open(filepath);
object missing = System.Reflection.Missing.Value;
// statement get the workbookname
string ExcelWorkbookname = WB.Name;
// statement get the worksheet count
int worksheetcount = WB.Worksheets.Count;
for (int ws = 1; ws <= worksheetcount; ws++)
{
Excel.Worksheet wks = (Excel.Worksheet)WB.Worksheets[ws];
// statement get the firstworksheetname
int linkCount = wks.UsedRange.Cells.Hyperlinks.Count;
for (int i = 1; i <= linkCount; i++)
{
object index = (object)i;
string currLink = wks.UsedRange.Cells.Hyperlinks[i].Address;
string TTD = wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay;
if (currLink == "https://www.google.co.in")
{
wks.UsedRange.Cells.Hyperlinks[i].Address = "https://www.google.com";
wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay = "Google";
}
else
{
wks.UsedRange.Cells.Hyperlinks[i].Address = "https://www.yahoo.com";
wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay = "Yahoo";
}
}
}
//we are saving the editted file into new folder"Editted" from old folder "Editted_old"
object UpdatedFile = singleFile.FullName.Replace("Editted_old", "Editted");
WB.SaveAs(UpdatedFile, missing,
missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
WB.Close(true, missing, missing);
oExcel.Quit();
}
}
}
}
No comments:
Post a Comment