public static void Exportar_a_Excel(System.Web.HttpResponse Response,
System.Web.UI.WebControls.DataGrid elGrid)
{
string strSampleFile = "Ejm2.xls";
OWC.Spreadsheet objExcel = new OWC.Spreadsheet(); //Referencing Microsoft Office Web Compenents 9.0
//Add the column headers to the excel sheet
int ColumnCount = 1;
for( int i = 0; i < elGrid.Columns.Count; i++)
{
if(elGrid.Columns[i].Visible)
{
objExcel.ActiveSheet.Cells[1, ColumnCount] = elGrid.Columns[i].HeaderText;
((OWC.Range)objExcel.ActiveSheet.Cells[1, ColumnCount]).Font.set_Bold(true);
ColumnCount++;
}
}
objExcel.ActiveSheet.Rows.EntireRow.Font.set_Bold(true);
//Add the values
for(int i = 0; i < elGrid.Items.Count; i++)
{
int col = 1;
for(int j = 0; j < elGrid.Columns.Count; j++)
{
if(elGrid.Columns[j].Visible)
{
string val = elGrid.Items[i].Cells[j].Text;
if(val==null || val=="")
{
if(elGrid.Items[i].Cells[j].Controls[0] is System.Web.UI.WebControls.HyperLink)
{
val = ((System.Web.UI.WebControls.HyperLink)(elGrid.Items[i].Cells[j].Controls[0])).Text;
}
else if(elGrid.Items[i].Cells[j].Controls[1] is System.Web.UI.WebControls.TextBox)
{
val = ((System.Web.UI.WebControls.TextBox)(elGrid.Items[i].Cells[j].Controls[1])).Text;
}
}
val = val.Replace('\r',' ');
val = val.Replace('\n',' ');
val = val.Replace('-','_');
if(val==" ")
val="";
if(elGrid.Columns[j].HeaderText.ToUpper().IndexOf("FECHA") >= 0)
{
((OWC.Range)objExcel.ActiveSheet.Cells[i+2,col]).set_NumberFormat("dd/mm/yyyy");
objExcel.ActiveSheet.Cells[i+2,col] = Convert.ToDateTime(val);
}
else
{
objExcel.ActiveSheet.Cells[i+2,col] = val;
}
col++;
}
}
}
//Autofit the columns to make them look pretty
objExcel.ActiveSheet.Columns.AutoFitColumns();
objExcel.ActiveSheet.Rows.AutoFitColumns();
//Do any formatting you wish.....
//objExcel.ActiveSheet.Rows.EntireRow.Font.set_Bold(true);
//This saves the excel file "strSampleFolder" on the server
string strSampleFolder = "c:\\" + strSampleFile;
objExcel.ActiveSheet.Export(strSampleFolder, OWC.SheetExportActionEnum.ssExportActionNone);
//This opens the download dialoge box and allows the user
//to download the excel sheet from the server
Response.Clear();
//Response.ContentEncoding = System.Text.Encoding.Unicode;
Response.ContentType = "application.x-msexcel"; //"application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment; filename=" + strSampleFile);
Response.Flush();
//This command actually transfers the file
Response.WriteFile(strSampleFolder);
Response.End();
}