Posted 2 July 2018, 8:49 pm EST
Below is the code which i am writing to achieve the generation of excel file
_logger.InfoFormat(Constants.GENERATEEXCELSPREADSHEETSTR, MethodBase.GetCurrentMethod().Name, Constants.DIGITIZEDDATACLASSNAME, dtSheetSource.Rows.Count.ToString(), excelDestinationPath, templateName, destinationFileName);
string U_Drivepath = ConfigurationManager.AppSettings[Constants.REPORT_ARCHIVE_PATH].ToString();
string TempFileName = ConfigurationManager.AppSettings["TmpAddacctDatQTPExcelTemplate"].ToString();
string DestFileName = Constants.ADDACCTDATFILENAME;
// Create a new workbook
var workBookTemplate = new C1XLBook();
var TmpWorkBookTemplate = new C1XLBook();
try
{
using (var fs = new FileStream(Server.MapPath(templateName), FileMode.Open, FileAccess.Read))
{
// Load the existing template in C1XLBook
workBookTemplate.Load(fs);
//Close the File Stream
fs.Close();
}
int j = 0;
// Starting inserting the values in work book template sheets's cell.
foreach (DataRow item in dtSheetSource.Rows)
{
int k = 0;
for (k = 0; k < dtSheetSource.Columns.Count; k++)
{
// Add content to the sheet.
workBookTemplate.Sheets[0][j + 2, k].Value = item[k];
}
j++;
}
// Save the workbook
workBookTemplate.Save(Server.MapPath(U_Drivepath) + destinationFileName);
using (var fs = new FileStream(Server.MapPath(TempFileName), FileMode.Open, FileAccess.Read))
{
// Load the existing template in C1XLBook
TmpWorkBookTemplate.Load(fs);
//Close the File Stream
fs.Close();
}
// Save the workbook
TmpWorkBookTemplate.Save(excelDestinationPath + DestFileName);
_logger.InfoFormat(Constants.GENERATEDSHEESAVEPATHTSTR, excelDestinationPath, destinationFileName);
using (var fs = new FileStream(excelDestinationPath + destinationFileName, FileMode.Open, FileAccess.Read))
{
// Create a byte array of file stream length
var temp = new byte[fs.Length];
// Read block of bytes from stream into the byte array
fs.Read(temp, 0, temp.Length);
//Close the File Stream
fs.Close();
}