Mar 27, 2014, 1:38 PM
Excel export from selected gridpanel values in one button
Hey everyone,
I'm currently working on an internship project where I have to export values to an excel file,
based on the selected rows in a GridPanel during the excel export button press.
My environment is Asp.net mvc3 and my view is through the use of Razor.
My current status:
I've tried several ways but none of them really worked. The one that comes closest, and is imo also the worst option, is that I can create an excel file and store it on the server side. Then through the use of a html.actionlink calling a separate download function I can download that file from the server. (Yes, you read that correctly, two separate clicks.) Due to the system being multiple user based I'm assuming I'll risk one of the two following as well:
1. File overwriting if the files use the same name giving some problems during downloading (Imagine 2+ people exporting at the same time).
2. More and more files through unique file ID's (Possibly fixable by deleting the file after downloading but still not the best way imo).
The best solution would be to just stream the file to the user after generating the file, without ever really creating the file server-side (and therefore getting the risk of overwriting during sending or something.) Also, I need it to be in just one button.
At this moment the options I've tried all result in either an unauthorized access exception, some variations of unknown characters / invalid input exceptions and at the moment it also gives me a 200 / OK message without anything happening after just generating (not downloading by actionlink click).
View (getSelectedIDs)
Export the grid to excel
Ajax Export Example
Postback Export Example
Since I'm new to asp.net and razor, and not having worked with MVC for quite a while, this is probably a very simple thing to fix.
However I haven't been able to after quite some hours and the chinese company isn't able to help me either so I was hoping someone could help me out here. The main problem with the first related topic being that I can't figure out how to convert the given code to my own environment / where to put it.
Regards
I'm currently working on an internship project where I have to export values to an excel file,
based on the selected rows in a GridPanel during the excel export button press.
My environment is Asp.net mvc3 and my view is through the use of Razor.
My current status:
- Check if any rows were actually selected [CHECK]
- Get the selected values in the controller [CHECK]
- Use them in retrieving the desired data [CHECK]
- Generate a DataSet object based on that [CHECK]
- Convert the DataSet object into Excel [CHECK]
- Send that excel file back to the user
I've tried several ways but none of them really worked. The one that comes closest, and is imo also the worst option, is that I can create an excel file and store it on the server side. Then through the use of a html.actionlink calling a separate download function I can download that file from the server. (Yes, you read that correctly, two separate clicks.) Due to the system being multiple user based I'm assuming I'll risk one of the two following as well:
1. File overwriting if the files use the same name giving some problems during downloading (Imagine 2+ people exporting at the same time).
2. More and more files through unique file ID's (Possibly fixable by deleting the file after downloading but still not the best way imo).
The best solution would be to just stream the file to the user after generating the file, without ever really creating the file server-side (and therefore getting the risk of overwriting during sending or something.) Also, I need it to be in just one button.
At this moment the options I've tried all result in either an unauthorized access exception, some variations of unknown characters / invalid input exceptions and at the moment it also gives me a 200 / OK message without anything happening after just generating (not downloading by actionlink click).
View (getSelectedIDs)
<script type = "text/javascript">
/** Get the selected values from the grid.
*
*/
var GetSelectedIds = function (targetGridPanel) {
var ids = '';
var selections = targetGridPanel.getSelectionModel().selected;
for (var i = 0; i < selections.length; i++) {
//Comma is used for splitting later - Example string: "3,4,5,7,14,"
ids += selections.items[i].data.CompanyID + ',';
}
//Remove the last comma.
ids = ids.substr(0, ids.length - 1);
return ids;
}
</script>
View (GridPanel)/** Other code */
.ColumnModel(
X.RowNumbererColumn().Width(36).Align(Alignment.Center),
X.Column().Text("ID").DataIndex("CompanyID").Align(Alignment.Left).Hidden(true),
X.Column().Text("公司名称").DataIndex("CompanyNameCN").Align(Alignment.Left),
X.Column().Text("英文名称").DataIndex("CompanyNameEN").Align(Alignment.Left),
X.Column().Text("成立时间").DataIndex("CreateYear").Width(55).Align(Alignment.Left),
X.Column().Text("总部所在地").DataIndex("HeadQuarters").Align(Alignment.Right),
X.Column().Text("股票代码").DataIndex("StockCode").Align(Alignment.Right),
X.Column().Text("公司网站").DataIndex("CompanyWebsite").Align(Alignment.Center),
X.Column().Text("其他情况说明").DataIndex("OtherDesc").Width(180).Align(Alignment.Right),
X.TemplateColumn().Text("图示").DataIndex("Picture").Width(50).Align(Alignment.Left).Template(t => t.Html = "<a id=\"{Picture}\" href=\"#\" onclick=\"showModalDialog('/ReportTypes/ViewImage?ID={CompanyID}', null, 'status:no;center:yes;help:no;dialogWidth:1000px;dialogHeight:700px');\">({Picture})</a>"),
X.TemplateColumn().Text("其他附件").DataIndex("Attachment").Width(55).Align(Alignment.Left).Template(t => t.Html = "<a id=\"{Attachment}\" href=\"#\" onclick=\"showModalDialog('/DownloadPage?c=ReportTypes&a=Attachment&ID={CompanyID}', null, 'status:no;center:yes;help:no;dialogWidth:515px;dialogHeight:250px');\">({Attachment})</a>"),
X.Column().Text("资料来源").DataIndex("DocSource").Align(Alignment.Right),
X.Column().Text("资料整理人").DataIndex("Doc").Width(65).Align(Alignment.Right),
X.Column().Text("录入人").DataIndex("ModifyName").Width(55).Align(Alignment.Right)
/** Some other code */
.SelectionModel(
X.CheckboxSelectionModel()
.RowSpan(1)
.ID("CheckBox")
//.Mode(SelectionMode.Multi)
)
)
.View(X.GridView().StripeRows(true))
.TopBar(
X.Toolbar()
//Excel button
.Items(X.Button()
.ID("btnExcelExport")
.Text("Export")
.Icon(Icon.PageExcel)
//TODO Translate english to chinese in the handler?
.Listeners(l => {
l.Click.Handler = "if(Ext.getCmp('gpCompany').getSelectionModel().hasSelection()) return true; else{ Ext.MessageBox.show({title:'Prompt Message',msg:'No values chosen',icon:Ext.MessageBox.WARNING,buttons:Ext.Msg.OK }); return false; }";
})
.DirectEvents(de =>
{
//NOTE: Extra step to press yes for confirmation. Delete comments if desired anyway.
//de.Click.Confirmation.ConfirmRequest = true;
//de.Click.Confirmation.Title = "Confirmation";
//de.Click.Confirmation.Message = "Would you like to export these files?";
de.Click.Url = Url.Action("GenerateExcelReport");
de.Click.Success = "App.gpCompany.getStore().reload();";
de.Click.ExtraParams.Add(new { selectedIDs = JRawValue.From("GetSelectedIds(App.gpCompany)") } );
})
)
)
Controller(All of my tries, divided over two functions by now) public ActionResult DownloadExcelReport()
{
DirectoryInfo dirInfo = new DirectoryInfo(HostingEnvironment.MapPath("~/Files"));
string filePath = @dirInfo.ToString() + "\\" + "ExcelReport.xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
return new FilePathResult(filePath, contentType);
}
/** Exports selected IDs to excel.
*
*/
[HttpGet]
public ActionResult GenerateExcelReport(string selectedIds)
{
// Get the data that is to be exported as an Excel file
DataSet dsToExport = getCompanyMinesData(selectedIds);
//string fileName = "ExcelExport" + DateTime.Today.Year.ToString() + DateTime.Today.Month.ToString("00") + DateTime.Today.Day.ToString("00") + ".xlsx";
string fileName = "ExcelReport.xlsx";
#region MemoryStream_To_XML_Serialization_Without_Temporary_File
/**
try
{
//Create Memory Stream to store XML Data
MemoryStream ms = new MemoryStream();
dsToExport.WriteXml(ms);
ms.Position = 0; // Due to the direction of serialization.
//Convert Memory Stream to Byte Array
byte[] data = ms.ToArray();
//Creating the Context
Response.Clear();
//Heads up browser, here comes some XML
Response.ContentType = "text/xml; charset=utf-8";
Response.AddHeader("Content-Disposition:",
"attachment;filename=" + HttpUtility.UrlEncode(fileName));
//Set the size of the file so the progress bar reports that correctly
Response.AddHeader("Content-Length", data.Length.ToString());
//Option 1
XmlNode xml =
XslCompiledTransform xtExcel = new XslCompiledTransform();
xtExcel.Load(Server.MapPath(fileName));
xtExcel.Transform(xml, null, this.Response.OutputStream);
this.Response.End();
//Option 2
//Download the file and prompt the user to save
Response.BinaryWrite(data);
Response.End();
ms.Flush();
ms.Close();
}
catch (Exception exc)
{
}
*/
#endregion
#region Writing_File_As_Stream_Without_Temporary_File
/**
// Create an excel file with the dataset
ExportFileCreator.CreateExcelDocumentAsStream(dsToExport, fileName, Response);
*/
#endregion
// Create the file physically on in the Files folder, then find it and return it.
#region Create_File_Locally_Then_Search_And_Send
FileSearcher fileSearcher = new FileSearcher();
DirectoryInfo dirInfo = new DirectoryInfo(HostingEnvironment.MapPath("~/Files"));
ExportFileCreator.CreateExcelDocument(dsToExport, dirInfo.ToString() + "\\" + fileName);
#region Search_For_File_And_Return
/**
List<FileNames> files = fileSearcher.GetFiles(dirInfo);
string filePath = (from f in files
where f.FileName == fileName
select f.FilePath).First();
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Parameters to file are
//1. The File Path on the File Server
//2. The connent type MIME type
//3. The parameter for the file save asked by the browser
return File(filePath, contentType, fileName);
//NOTE Change return value if taking this out of comments.
*/
#endregion
#endregion
return DownloadExcelReport();
}
Related topics that I've found:Export the grid to excel
Ajax Export Example
Postback Export Example
Since I'm new to asp.net and razor, and not having worked with MVC for quite a while, this is probably a very simple thing to fix.
However I haven't been able to after quite some hours and the chinese company isn't able to help me either so I was hoping someone could help me out here. The main problem with the first related topic being that I can't figure out how to convert the given code to my own environment / where to put it.
Regards