Free Support Forum - aspose.com

How to write data to the macros disabled excel

Hi,

Please send the code to write the data to the macros disabled excel template using aspose.cells.dll

Note: Already tried with protect and unprotect methods of workbook class. but its not working.


Thanks,
Shekar Reddy

Hi,

I am not sure about your issue. Please give us more details. Could you paste your runnable code here and also attach your template file. We will check your issue soon.

Also, please check the topics under the section here on how to add data to the cells:
Working with Data

Also, check the topics on how to protect / unprotect the worksheets:
Security Features

Thank you.

Hi Amjad Sahi,

The below is the code to write to excel.

public string WriteToExcell(System.Data.DataTable newtable, string batchNo)
{

string strJournalFilePath = Common.JournalFilePath;
string JournalTemplatePath = Common.JournalTemplatePath;
if (!System.IO.Directory.Exists(strJournalFilePath))
{
System.IO.Directory.CreateDirectory(strJournalFilePath);
}
// string fileName1 = System.DateTime.Now.Day.ToString() + “-” + System.DateTime.Now.Month.ToString() + “-” + System.DateTime.Now.Year.ToString();

string newFilePath = strJournalFilePath + “Journal” + “_” + batchNo + “.xls”;

File.Copy(JournalTemplatePath + “JournalTemplate.xls”, newFilePath);

Worksheet wSheet;
Workbook wbook = new Workbook();
Worksheets wsheets = null;
wSheet = null;

Cell objRange = null;
int row = 10, col = 2;
try
{
wbook.Open(newFilePath);
wsheets = wbook.Worksheets;
wSheet = wsheets[0];
int j = col;
int count = newtable.Columns.Count - 2;
#region InsertEmptyRows
int intCountTemp = 0;
foreach (DataRow dataRow in newtable.Rows)
{
intCountTemp++;
if (intCountTemp != 1)//inserting empty rows. As there exists by default 1 row…Need to insert rows from the next row.
{
wSheet.Cells.InsertRow(11);
}
}
#endregion

foreach (DataRow dataRow in newtable.Rows)
{
int k = col;
for (int i = 0; i < count; i++)
{
objRange = (Cell)wSheet.Cells[row, k];
objRange.PutValue(dataRow[i].ToString());
k++;
}
row++;
}
wbook.Save(newFilePath);
}
catch (Exception ex)
{

throw ex;
}
finally
{
objRange = null;

}
return newFilePath;
}




The same code is working for normal excel file. Please find the attached excel template.


Thanks,
Shekar Reddy

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and try the latest version:
Aspose.Cells for .NET (Latest Version)
and see if it works fine.

I tried to write some values in your source file and see they are being written properly. I have attached the output file and the screenshot for your reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\JournalTemplate.xls”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[“Journal 1”];


for (int col = 1; col < 20; col++)

{

worksheet.Cells[9, col].PutValue(“some value”);

}


workbook.Save(filePath + “.out.xls”, SaveFormat.Excel97To2003);


Screenshot:

Hi,

Thanks for reply…

Got the soultion with 5+ version only. The problem is not with macros , due to taking sheet index here wSheet = wsheets[0], bcoz there are hidden sheets will be there.

If i use like this wSheet = wsheets[“Journal 1”]; its working.



Thank You.
Shekar Reddy

Shekar Reddy:
Hi,

Thanks for reply..

Got the soultion with 5+ version only. The problem is not with macros , due to taking sheet index here wSheet = wsheets[0], bcoz there are hidden sheets will be there.

If i use like this wSheet = wsheets["Journal 1"]; its working.



Thank You.
Shekar Reddy

Hi,

Thanks for your feedback.

Yes, the problem was occurring because of hidden worksheet, it was inserting values in some other worksheet.

Initially, when I used 0 index, I did not see any output but when I used the sheet name, I saw the output values. Your required sheet is not placed at 0 index, it has some other index so sheet name works fine.

Thanks for confirming the solution at your end.