how to add checkbox control to Excel Cell dynamically through C# Code on export button click with SQL table Data - c#

In a C# application, I created a button export to Excel. The issue that I am having is that I need to Add Remove column at last as checkbox at the time of exporting excel Report. I have done some searching on google. and found some code, but it's adding CheckBox to entire sheet, not for specific cell.. and also I am not able to see remaining columns and data.. please help
find the following code
thanks in advance
dtRequests.AcceptChanges();
string strFilename = "notificationMessage Report_" + DateTime.Now.ToString("dd/MM/yyyy") + ".xls";
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", strFilename));
Response.ContentType = "application/ms-excel";
string str = string.Empty;
foreach (DataColumn dtcol in dtRequests.Columns)
{
string columnName = string.Empty;
columnName = dtcol.ColumnName;
switch (columnName)
{
case "GroupName":
columnName = "Group Name";
break;
case "NotificationMessage":
columnName = "Notification Message";
break;
case "PartNumber":
columnName = "Part number";
break;
case "region":
columnName = "Region";
break;
case "Remove":
columnName = "Remove";
break;
default:
break;
}
if (!string.IsNullOrEmpty(columnName))
{
Response.Write(str + columnName);
str = "\t";
}
}
Response.Write("\n");
foreach (DataRow dr in dtRequests.Rows)
{
str = "";
for (int j = 0; j < dtRequests.Columns.Count; j++)
{
switch (dtRequests.Columns[j].ColumnName)
{
case "Remove":
str = str+"<html><body><input type='checkbox' value='TRUE'></body></html>";
break;
default:
str = str + Convert.ToString(dr[j]);
break;
}
Response.Write(str);
str = "\t";
}
Response.Write("\n");
}
Response.End();

Related

Export gridview data into CSV file

I have a gridview control in ASP.Net 2.0 and i need to export this gridview data into CSV file.
I have bind this gridview with the dataset.After binding the dataset to the gridview i have done some changes in the gridview data like if I got 0 in dataset then i show 0 as "Started" in the gridview and if i got 1 in the dataset then I show 1 as "Not Started" in the gridview.
So, i can't use dataset directly for exporting. What i need is..i want the code (in c#) that export my gridview data(not dataset's data) into CSV file.
Try following code, i used it already many times. It will export the data directly from gridview to csv file specified in the code.
protected void btnExportCSV_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.csv");
Response.Charset = "";
Response.ContentType = "application/text";
GridView1.AllowPaging = false;
GridView1.DataBind();
StringBuilder sb = new StringBuilder();
for (int k = 0; k < GridView1.Columns.Count; k++)
{
//add separator
sb.Append(GridView1.Columns[k].HeaderText + ',');
}
//append new line
sb.Append("\r\n");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
for (int k = 0; k < GridView1.Columns.Count; k++)
{
//add separator
sb.Append(GridView1.Rows[i].Cells[k].Text + ',');
}
//append new line
sb.Append("\r\n");
}
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();
}
for more information visit Here
Hope it will help you
First thanks to Devjosh for the good answer which I modified to work with gridviews that have AutoGenerateColumns=true and AllowSorting=true. Plus I stripped out any returned commas from the data to make sure csv file was not corrupted.
private void ExportReport()
{
// set the resulting file attachment name to the name of the report...
string fileName = "test";
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".csv");
Response.Charset = "";
Response.ContentType = "application/text";
System.Text.StringBuilder sb = new System.Text.StringBuilder();
// Get the header row text form the sortable columns
LinkButton headerLink = new LinkButton();
string headerText = string.Empty;
for (int k = 0; k < gvReport.HeaderRow.Cells.Count; k++)
{
//add separator
headerLink = gvReport.HeaderRow.Cells[k].Controls[0] as LinkButton;
headerText = headerLink.Text;
sb.Append(headerText + ",");
}
//append new line
sb.Append("\r\n");
for (int i = 0; i < gvReport.Rows.Count; i++)
{
for (int k = 0; k < gvReport.HeaderRow.Cells.Count; k++)
{
//add separator and strip "," values from returned content...
sb.Append(gvReport.Rows[i].Cells[k].Text.Replace(",", "") + ",");
}
//append new line
sb.Append("\r\n");
}
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();
}
private void ExportGridToCSV()
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=Employee.csv");
Response.Charset = "";
Response.ContentType = "application/text";
GridEmployee.AllowPaging = false;
GridEmployee.DataBind();
StringBuilder columnbind = new StringBuilder();
for (int k = 0; k < GridEmployee.Columns.Count; k++)
{
columnbind.Append(GridEmployee.Columns[k].HeaderText + ',');
}
columnbind.Append("\r\n");
for (int i = 0; i < GridEmployee.Rows.Count; i++)
{
for (int k = 0; k < GridEmployee.Columns.Count; k++)
{
columnbind.Append(GridEmployee.Rows[i].Cells[k].Text + ',');
}
columnbind.Append("\r\n");
}
Response.Output.Write(columnbind.ToString());
Response.Flush();
Response.End();
}
Just call this method in a button Click event.
For more Code, click the link
Export gridview data into CSV file
I hope this helps you. Thanks.

Export data from Gridview to Excel in asp.net

I am Having Error while Exporting data from Gridview to Excel in asp.net
Firebug shows that error is
"Error: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled.
Details: Error parsing near '
and my code is
EditUser _editUser = new EditUser();
string s = Request.QueryString["UserName"];
DataTable _dsLeaveDetails1 = new DataTable();
_dsLeaveDetails1 = _editUser.GetUserWiseECO(s.ToLower(), "Earned Comp Off");
if (_dsLeaveDetails1.Rows.Count > 0)
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "MonthlyLeaveReport.xls"));
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
sample.AllowPaging = false;
sample.DataSource = _dsLeaveDetails1;
sample.DataBind();
//Change the Header Row back to white color
sample.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Applying stlye to gridview header cells
for (int i = 0; i < sample.HeaderRow.Cells.Count; i++)
{
sample.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
}
int j = 1;
//This loop is used to apply stlye to cells based on particular row
foreach (GridViewRow gvrow in sample.Rows)
{
gvrow.BackColor = Color.White;
if (j <= sample.Rows.Count)
{
if (j % 2 != 0)
{
for (int k = 0; k < gvrow.Cells.Count; k++)
{
gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
}
}
}
j++;
}
sample.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "alert('No records Found!!!');", true);
}
This is the complete code I'm using.
First override this method:
public override void VerifyRenderingInServerForm(Control control)
{
}
And use this method to export to Excel:
private void ExportToExcel()
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=name.xls");
Response.Charset = "utf-8";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
//To Export all pages
gridview.AllowPaging = false;
gridview.HeaderRow.BackColor = Color.White;
foreach (TableCell cell in gridview.HeaderRow.Cells)
{
cell.BackColor = Color.FromName("#4091A4");
cell.ForeColor = Color.White;
foreach (Control control in cell.Controls)
{
if ((control.GetType().Name == "DataControlLinkButton") ||
(control.GetType().Name == "DataControlLinkButton"))
{
cell.Controls.Add(new Literal { Text = (control as LinkButton).Text });
cell.Controls.Remove(control);
}
}
}
foreach (GridViewRow row in gridview.Rows)
{
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{
List<Control> controls = new List<Control>();
foreach (Control control in cell.Controls)
{
controls.Add(control);
}
cell.CssClass = "textmode";
foreach (Control control in controls)
{
switch (control.GetType().Name)
{
case "HyperLink":
cell.Controls.Add(new Literal { Text = (control as HyperLink).Text });
break;
case "TextBox":
cell.Controls.Add(new Literal { Text = (control as TextBox).Text });
break;
case "DataControlLinkButton":
case "LinkButton":
cell.Controls.Add(new Literal { Text = (control as LinkButton).Text });
break;
case "CheckBox":
cell.Controls.Add(new Literal { Text = (control as CheckBox).Text });
break;
case "RadioButton":
cell.Controls.Add(new Literal { Text = (control as RadioButton).Text });
break;
}
cell.Controls.Remove(control);
}
if (row.RowIndex % 2 == 0)
{
cell.BackColor = gridview.AlternatingRowStyle.BackColor;
}
else
{
cell.BackColor = gridview.RowStyle.BackColor;
}
cell.HorizontalAlign = HorizontalAlign.Center;
}
}
gridview.RenderControl(hw);
//style to format numbers to string
string style = #"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}

How do I Export GridView to Excel?

I am exporting a GridView to an Excel file, but when I open the file, first I get an error regarding the fact that the format type and extension don't match, and when I open it the whole page is brought into the Excel file, not just the grid view.
enter code here
protected void ExportToExcel(object sender, EventArgs e)
{
try
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
//To Export all pages
gridCustomer.AllowPaging = false;
//this.gridCustInfoBind(1);
gridCustAllInfoBind(1);
gridCustomer.HeaderRow.BackColor = Color.White;
foreach (TableCell cell in gridCustomer.HeaderRow.Cells)
{
cell.BackColor = gridCustomer.HeaderStyle.BackColor;
}
foreach (GridViewRow row in gridCustomer.Rows)
{
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{
if (row.RowIndex % 2 == 0)
{
cell.BackColor = gridCustomer.AlternatingRowStyle.BackColor;
}
else
{
cell.BackColor = gridCustomer.RowStyle.BackColor;
}
cell.CssClass = "textmode";
}
}
gridCustomer.RenderControl(hw);
//style to format numbers to string
string style = #"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
catch (Exception ex)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", "alert('Exception Message: " + ex.Message.Replace("'", "").Replace("\"", "") + "');", true);
}
}
Take a look on doodlereport nuget package, I think it will work for you.
the below code will convert your datatable to Excel, so you can use it instead:
public void CreateExcel(DataTable dt, string path)
{
try
{
if (File.Exists(path))
File.Delete(path);
FileInfo newFile = new FileInfo(path);
using (ExcelPackage pck = new ExcelPackage(newFile))
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Query Result");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
//ws.Cells["A1"].LoadFromDataTable(dt, true);
//ws.Cells["A1"].AutoFitColumns();
int columnNumber = 0;
foreach (DataColumn dc in dt.Columns)
{
columnNumber++;
ws.Cells[1, columnNumber].Value = dc.ColumnName;
}
//Adding data of each row.
int rowNumber = 0;
foreach (DataRow rw in dt.Rows)
{
rowNumber++;
columnNumber = 0;
foreach (DataColumn dc in dt.Columns)
{
columnNumber++;
//Formating columns based on data types
if (rw[dc.ColumnName].GetType().ToString() == "System.Int32" || rw[dc.ColumnName].GetType().ToString() == "System.Double")
ws.Cells[rowNumber + 1, columnNumber].Style.Numberformat.Format = "0";
else if (rw[dc.ColumnName].GetType().ToString() == "System.DateTime")
ws.Cells[rowNumber + 1, columnNumber].Style.Numberformat.Format = "yyyy-mm-dd hh:mm:ss";
else if (rw[dc.ColumnName].GetType().ToString() == "System.Decimal")
ws.Cells[rowNumber + 1, columnNumber].Style.Numberformat.Format = "0.00";
ws.Cells[rowNumber + 1, columnNumber].Value = rw[dc.ColumnName];
}
}
//Format the header for columns
using (ExcelRange rng = ws.Cells["A1:Z1"])
{
rng.Style.Font.Bold = true;
}
//Format Cells
using (ExcelRange col = ws.Cells[2, 1, dt.Rows.Count + 1, dt.Columns.Count + 1])
{
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
col.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
col.AutoFitColumns();
}
//Saving the excel sheet
pck.Save();
}
}
catch (Exception ex)
{
}
}
Use Linq2Excel. Its an opensource tool and available on Nuget.
https://github.com/paulyoder/LinqToExcel

Export GridView to comma delimited .txt file

I'm exporting some data from a GridView to a .txt file.
This is the code:
private void ExportGridToExcel()
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
string FileName = "Export" + DateTime.Now + ".txt";
StringBuilder strbldr = new StringBuilder();
Response.ContentType = "application/text";
Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
Response.ContentEncoding = System.Text.Encoding.Unicode;
Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
Response.Cache.SetCacheability(HttpCacheability.NoCache);
for (int i = 0; i < GridView1.Columns.Count; i++)
{
//separting header columns text with comma operator
strbldr.Append(GridView1.Columns[i].HeaderText + ',');
}
//appending new line for gridview header row
strbldr.Append("\n");
for (int j = 0; j < GridView1.Rows.Count; j++)
{
for (int k = 0; k < GridView1.Columns.Count; k++)
{
//separating gridview columns with comma
strbldr.Append(GridView1.Rows[j].Cells[k].Text + ',');
strbldr.Replace("<", "<");
strbldr.Replace(">", ">");
}
//appending new line for gridview rows
strbldr.Append("\n");
}
GridView1.AllowPaging = false;
Response.Output.Write(strbldr.ToString());
Response.End();
}
protected void Button3_Click(object sender, EventArgs e)
{
ExportGridToExcel();
}
This works, however I need to remove all html tags in the export as the above code seems to add <p> tags to the different columns? Anybody know how I can do this?
You could use utility function based on regex to remove html tags:
public string RemoveHtmlTags(string source)
{
return Regex.Replace(source, "<.*?>", "");
}
This will replace all tags like "<b>" or "<span/>" with empty string.

How can I remove “Number Stored as Text” prompt upon Export To Excel using EPPLUS?

I have export my gridview data to excel using EPPLUS, but however for a few columns I have this green tag "Number Stored as Text" prompt after opening the excel file that was exported.
How can I code in my export to excel codes to change the datatype/convert to number for a specific column starting from the 2nd row of a specified column since the 1st row is header?
I have updated your code here.
Check the added code..
EDITED
I have change the variable name for your DataTable assuming the thing what you are doing.. GridView1 is a GridView UI Control and you put the DataTable Name as GridView1, the code might be confuse on what is GridView1
protected void EXPORT_BUTTON_Click(object sender, EventArgs e)
{
// Added Code
int parseValue;
bool isInt;
ExcelPackage package = new ExcelPackage();
ExcelWorksheet Grid = package.Workbook.Worksheets.Add("ORSA ASSESSMENTS");
DataTable dt = new DataTable();
for (int i = 0; i < dt.Columns.Count; i++)
{
// Update Type
dt.Columns.Add("column" + i.ToString(), typeof(int));
}
foreach (GridViewRow row in GridView1.Rows)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < GridView1.Columns.Count; j++)
{
row.Cells[j].Text = row.Cells[j].Text.Replace(" ", " ");
// Added Code
isInt = int.TryParse(row.Cells[j].Text.Trim(), out parseValue);
// Added Code
if (isInt)
dr["column" + j.ToString()] = parseValue;
}
dt.Rows.Add(dr);
}
Grid.Cells["A1"].LoadFromDataTable(dt, true);
using (ExcelRange rng = Grid.Cells["A1:Z1"])
{
rng.Style.Font.Bold = true;
}
Grid.Cells[ORSA.Dimension.Address].AutoFitColumns();
var FolderPath = ServerName + DirectoryLocation + DirectoryFolder + ExportsFolder;
var filename = ExcelName + #"_" + ".xlsx";
var filepath = new FileInfo(Path.Combine(FolderPath, filename));
Response.Clear();
package.SaveAs(filepath);
Response.AddHeader("content-disposition", "attachment; filename=" + filename + ";");
Response.Charset = "";
Response.ContentType = "application/vnd.xlsx";
Response.TransmitFile(filepath.FullName);
Response.End();
}
This is the sample i mocked up and is a working example
protected void Page_Load(object sender, EventArgs e)
{
// Check
if (!IsPostBack)
{
DataTable dt = new DataTable();
// Create Column
for(int i = 0; i < 5; i++)
dt.Columns.Add("column" + i, typeof(int));
for (int i = 0; i < 10; i++)
dt.Rows.Add(i, i+1, i+2, i+3, i+4);
GenerateExcel(dt);
}
}
private void GenerateExcel(DataTable dt)
{
using (ExcelPackage pkg = new ExcelPackage())
{
ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("Sheet1");
ws.Cells[1, 1].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light18);
pkg.SaveAs(new FileInfo("C:\\Test.xlsx"));
}
}
You should save you string as an int not a string. Whenever you want your cell to be as number, just cast your cell input as int.
That means, when populating your data, be sure to populate it as int.
I had similar problems. As Ordel Eraki said: do not stringify the value:
workSheet.Cells[1, 1].Value = 2; // Value is object
workSheet.Cells[1, 1].Value = 2.ToString()

Resources