Hi,
I have written a program which writes respective data from xml files to excel sheet, after writing the data, the program will protect the entire excel sheet with a password. So now i should protect entire excel sheet, but except one column. I need help of how to do that.
As per the below code i am reading data like Name, City, Area and Time from .Xml files which are at a path declared in the above code as "Excelpath" and wrtiting the respective data in excel sheet.
I have Protected the excel sheet with password "abcd" but i need to unprotect 5th Column that means the data is written on to first four columns like name, city, time, area. So for 5th column i need to have user editable.
What I have tried:
Excel.Application oXL = null;
Excel.Workbook oWB = null;
Excel.Worksheet excelWorksheet = null;
Excel.Sheets excelSheets = null;
Excel.Worksheet oSheet = null;
XmlDocument doc = new XmlDocument();
log.Info(excelPATH);
bool isItFirstRow = true;
CurrentRow = 8;
try
{
if (!File.Exists(excelPATH))
{
log.Error("The Output Excel File was not available");
return;
}
CheckExcellProcesses();
log.Info("Intializing the EXCEL Library");
log.Info(excelPATH);
oXL = new Excel.ApplicationClass();
oWB = oXL.Workbooks.Open(excelPATH, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
excelSheets = oWB.Worksheets;
excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(1);
oSheet = excelWorksheet;
log.Info(String.Format("Program is writing to {0} File", excelPATH));
string[] requiredXMLfiles = getRequiredXMLFiles(fromDate, toDate, "_Excelsheet.xml");
foreach (string file in requiredXMLfiles)
{
if (File.Exists(file))
{
doc.Load(file);
string OriginalTime = doc.GetElementsByTagName("Time")[0].InnerText;
XmlNodeList bookList = doc.GetElementsByTagName("HYDR.Database");
isItFirstRow = true;
foreach (XmlNode node in bookList)
{
CurrentColumn = 2;
XmlElement bookElement = (XmlElement)node;
string Name = bookElement.GetElementsByTagName("HYDR.Name")[0].InnerText;
string City = bookElement.GetElementsByTagName("HYDR.City")[0].InnerText;
string Area = bookElement.GetElementsByTagName("HYDR.Area")[0].InnerText;
if (isItFirstRow)
{
oSheet.Cells[CurrentRow, CurrentColumn++] = OriginalTime;
oSheet.Cells[CurrentRow, CurrentColumn++] = Name;
oSheet.Cells[CurrentRow, CurrentColumn++] = City;
oSheet.Cells[CurrentRow, CurrentColumn++] = Area;
CurrentRow++;
isItFirstRow = false;
break;
}
else
break;
}
else
{
oSheet.Cells[CurrentRow, CurrentColumn++] = OriginalTime;
oSheet.Cells[CurrentRow, CurrentColumn++] = Name;
oSheet.Cells[CurrentRow, CurrentColumn++] = City;
oSheet.Cells[CurrentRow, CurrentColumn++] = Area;
CurrentRow++;
isItFirstRow = false;
}
}
}
}
}
oSheet._Protect("abcd", true, true, true, true);
oWB.Save();