1- add reference to the library
Microsoft.Office.Interop.Excel
2- copy past the following funciton to export excel sheet (it saved in temp directory for current user )
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet =(Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["Sheet1"];
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Exported from gridview";
// storing header part in Excel
for (int i = 1; i < grdCity.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = grdCity.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < grdCity.Rows.Count - 1; i++)
{
for (int j = 0; j < grdCity.Columns.Count-1; j++)
{
worksheet.Cells[i + 2, j + 1] = grdCity.Rows[i].Cells[j].Value.ToString();
}
}
string myTempFile = Path.Combine(Path.GetTempPath(), "ratings.xlsx");
// save the application
workbook.SaveCopyAs(myTempFile);// (myTempFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet = null;
workbook = null;
// File.Open(myTempFile, FileMode.Open);
// Exit from the application
app.Quit();
----------------------------------------------------------------------------------
Export DataTable To Excel
public static void ExporttoExcel(DataTable table)
{
var sb = new StringBuilder();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel;charset=windows-1256";
sb.Append(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
sb.Append("<font style='font-size:10.0pt; font-family:Calibri;'>");
sb.Append("<BR><BR><BR>");
//sets the table border, cell spacing, border color, font of the text, background, foreground, font height
sb.Append("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
//am getting my grid's column headers
int columnscount = table.Columns.Count;
for (int j = 0; j < columnscount; j++)
{ //write in new column
sb.Append("<Td>");
//Get column headers and make it as bold in excel columns
sb.Append("<B>");
sb.Append(table.Columns[j].ColumnName.ToString());
sb.Append("</B>");
sb.Append("</Td>");
}
sb.Append("</TR>");
foreach (DataRow row in table.Rows)
{//write in new row
sb.Append("<TR>");
for (int i = 0; i < table.Columns.Count; i++)
{
sb.Append("<Td>");
sb.Append(row[i].ToString());
sb.Append("</Td>");
}
sb.Append("</TR>");
}
sb.Append("</Table>");
sb.Append("</font>");
HttpContext.Current.Response.Write(sb);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
Microsoft.Office.Interop.Excel
2- copy past the following funciton to export excel sheet (it saved in temp directory for current user )
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet =(Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["Sheet1"];
worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Exported from gridview";
// storing header part in Excel
for (int i = 1; i < grdCity.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = grdCity.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < grdCity.Rows.Count - 1; i++)
{
for (int j = 0; j < grdCity.Columns.Count-1; j++)
{
worksheet.Cells[i + 2, j + 1] = grdCity.Rows[i].Cells[j].Value.ToString();
}
}
string myTempFile = Path.Combine(Path.GetTempPath(), "ratings.xlsx");
// save the application
workbook.SaveCopyAs(myTempFile);// (myTempFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet = null;
workbook = null;
// File.Open(myTempFile, FileMode.Open);
// Exit from the application
app.Quit();
----------------------------------------------------------------------------------
Export DataTable To Excel
public static void ExporttoExcel(DataTable table)
{
var sb = new StringBuilder();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel;charset=windows-1256";
sb.Append(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
sb.Append("<font style='font-size:10.0pt; font-family:Calibri;'>");
sb.Append("<BR><BR><BR>");
//sets the table border, cell spacing, border color, font of the text, background, foreground, font height
sb.Append("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
//am getting my grid's column headers
int columnscount = table.Columns.Count;
for (int j = 0; j < columnscount; j++)
{ //write in new column
sb.Append("<Td>");
//Get column headers and make it as bold in excel columns
sb.Append("<B>");
sb.Append(table.Columns[j].ColumnName.ToString());
sb.Append("</B>");
sb.Append("</Td>");
}
sb.Append("</TR>");
foreach (DataRow row in table.Rows)
{//write in new row
sb.Append("<TR>");
for (int i = 0; i < table.Columns.Count; i++)
{
sb.Append("<Td>");
sb.Append(row[i].ToString());
sb.Append("</Td>");
}
sb.Append("</TR>");
}
sb.Append("</Table>");
sb.Append("</font>");
HttpContext.Current.Response.Write(sb);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
No comments:
Post a Comment