How to write data in excel templates in AX7

Finance and Operations/AX Developer Forum
1

Hi,

i am writing data in to excel in ax 7 by using following code

MemoryStream memoryStream = new MemoryStream();

using (var package = new ExcelPackage(memoryStream))
{

// Looping through query results.

var currentRow = 5;
int row ;

var worksheets = package.get_Workbook().get_Worksheets();

var Worksheet = worksheets.Add(“Timesheet”);

var cells = Worksheet.get_Cells();

OfficeOpenXml.ExcelRange cell = cells.get_Item(currentRow, 3);
System.String value = “From Date”;
cell.set_Value(value);
cell.AutoFitColumns();

Style.ExcelFont style1 = cell.Style.Font;
style1.Bold = true;
style1.Size = 12;

cell = cells.get_Item(currentRow, 4);
cell.set_Value(fromDate);
cell.AutoFitColumns();
Style.ExcelFont styleFd = cell.Style.Font;
styleFd.Bold = true;

value = “To Date”;
cell = cells.get_Item(currentRow, 7);
cell.set_Value(value);
cell.AutoFitColumns();

Style.ExcelFont style2 = cell.Style.Font;
style2.Bold = true;
style2.Size = 12;

cell = cells.get_Item(currentRow, 8);
cell.set_Value(toDate);
cell.AutoFitColumns();
Style.ExcelFont styleTD = cell.Style.Font;
styleTD.Bold = true;

row = currentRow+2;

value = “Sr No”;
cell = cells.get_Item(row,3);
cell.set_Value(value);
// cell.AutoFitColumns();

Style.ExcelFont style3 = cell.Style.Font;
style3.Bold = true;

value = “Consultant”;
cell = cells.get_Item(row,4);
cell.set_Value(value);
// cell.AutoFitColumns();

Style.ExcelFont style4 = cell.Style.Font;
style4.Bold = true;

value = “Client”;
cell = cells.get_Item(row,5);
cell.set_Value(value);
cell.AutoFitColumns();

Style.ExcelFont style5 = cell.Style.Font;
style5.Bold = true;

value = “Date”;
cell = cells.get_Item(row,6);
cell.set_Value(value);
cell.AutoFitColumns();

Style.ExcelFont style6 = cell.Style.Font;
style6.Bold = true;

value = “Task Description”;
cell = cells.get_Item(row,7);
cell.set_Value(value);
cell.AutoFitColumns();

Style.ExcelFont style7 = cell.Style.Font;
style7.Bold = true;

value = “Duration in hrs”;
cell = cells.get_Item(row,8);
cell.set_Value(value);
cell.AutoFitColumns();

Style.ExcelFont style8 = cell.Style.Font;
style8.Bold = true;

//super();
select * from resourceview
where resourceview.Worker == woreker
&& resourceview.ResourceCompanyId == curExt();

while(queryRun.next())

{

tsTimeSheetTable = queryRun.get(tableNum(TSTimesheetTable));

// if (tsTimeSheetTable.Resource == resourceview.RecId || resourceview.RecId == 0)
// {

while select * from tsTimesheetTrans
join tsTimesheetLine
where tsTimesheetTrans.TimesheetNbr == tsTimeSheetTable.TimesheetNbr
&& (tsTimeSheetTable.Resource == resourceview.RecId || woreker == 0)
&& tsTimesheetTrans.TSTimesheetLineWeek == tsTimesheetLine.RecId
// && (tsTimesheetLine.Resource == resourceview.RecId || resourceview.recid ==0)
// && (tsTimesheetLine.ProjId == woreker || woreker == “”)

&& tsTimesheetTrans.TransDate >= fromDate
&& tsTimesheetTrans.TransDate <= toDate

{

Row++;
counter++;

cell = cells.get_Item(Row, 3);
cell.set_Value(counter);

cell = cells.get_Item(Row, 4);
cell.set_Value(TSTimesheetTable::find(tsTimesheetTrans.TimesheetNbr).resourceName());
//cell.AutoFitColumns();
cell = cells.get_Item(Row, 5);
cell.set_Value(tsTimesheetLine.projName());
cell.AutoFitColumns();

cell = cells.get_Item(Row, 6);
cell.set_Value(tsTimesheetTrans.TransDate);
// cell.AutoFitColumns();
cell = cells.get_Item(Row, 7);
cell.set_Value(tsTimesheetLine.TaskDesc);
//cell.AutoFitColumns();

cell = cells.get_Item(Row, 8);
cell.set_Value(tsTimesheetTrans.Hours);

}

// }

}

package.Save();

file::SendFileToUser(memoryStream, “WeeklyTimesheet.xlsx”);

}

I want to use existing excel template. How to do this ? how to open excel file in ax 7 and write data into it.?