private void RenderExcelTemplate(DataTable dt, string fileId)
{
var dataPath = Server.MapPath(ResolveUrl("Templates"));
var fileName = (string)(Services.File.GetFilenameFromFileId(fileId));
var xls = new XlsFile(fileName);
// save current sheet reference for later
var activeSheetSaved = (string)xls.ActiveSheetByName;
xls.ActiveSheetByName = ReportExtra.DataSheetName;
// handle header row
if (ReportExtra.ContainsHeaderRow)
{
// keep header row
xls.DeleteRange(new TXlsCellRange(2, 1, FlxConsts.Max_Rows + 1, FlxConsts.Max_Columns + 1), TFlxInsertMode.NoneDown);
}
else
{
// clear sheet
xls.ClearSheet();
// headings
for (var col = 0; col <= dt.Columns.Count - 1; col++)
{
xls.SetCellFromString(1, col + 1, dt.Columns[col].ColumnName);
}
}
// update data
for (var row = 0; row <= dt.Rows.Count - 1; row++)
{
for (var col = 0; col <= dt.Columns.Count - 1; col++)
{
xls.SetCellValue(row + 2, col + 1, dt.Rows[row][col]);
}
}
xls.ActiveSheetByName = activeSheetSaved;
// update range
//Dim range As TXlsNamedRange = New TXlsNamedRange()
//range.Name = "DEPTDATA"
//range.RangeFormula = String.Format("='{0}'!$A$1:$H${1}", xls.ActiveSheetByName, dt.Rows.Count + 1)
//xls.SetNamedRange(range)
// determine file extension
var fileExtension = "xls";
if (fileName.EndsWith("xlsx"))
{
fileExtension = "xlsx";
}
// stream to user
using (var ms = new MemoryStream())
{
xls.Save(ms);
var details = new ExportDetails();
details.Data = null;
details.Filename = ReportExtra.OutputFileName.Replace("[TICKS]", DateTime.Now.Ticks.ToString()) + "." + fileExtension;
details.Disposition = ReportExtra.DispositionType;
// write tmp file
var filePath = Server.MapPath(ResolveUrl(string.Format("{0}.dat", Guid.NewGuid().ToString())));
var fs = File.OpenWrite(filePath);
fs.Write(ms.GetBuffer(), 0, Convert.ToInt32(ms.Length));
fs.Close();
details.BinaryFilename = filePath;
Session[Export.EXPORT_KEY] = details;
if (Request.ServerVariables["HTTP_USER_AGENT"].Contains("ipad") || Request.ServerVariables["HTTP_USER_AGENT"].Contains("iphone"))
{
//' no iframe for iphone, ipad
Response.Redirect(string.Format("{0}?ModuleId={1}&TabId={2}", ResolveUrl("~/DesktopModules/DNNStuff - SQLViewPro/Export.aspx"), State.ModuleId, State.TabId));
}
else
{
Controls.Add(new LiteralControl(string.Format("<iframe style=\'display:none\' scrolling=\'auto\' src=\'{0}?ModuleId={1}&TabId={2}\'></iframe>", ResolveUrl("~/DesktopModules/DNNStuff - SQLViewPro/Export.aspx"), State.ModuleId, State.TabId)));
}
}
}