asp.net how to export Excel and pictures in cells:

ASP tutorial. NET How to export Excel and pictures in cells:

There are two ways to export excel in the ASP.net tutorial, which is to store the exported files under a server folder, and then export the file addresses to the browser, one to the file output stream directly to the browser. When response output, T-delimited data, when exporting Excel, is equivalent to a column, n is equivalent to wrapping.
1, the entire HTML output Excel

This method prints all the content in HTML, such as buttons, tables, pictures, and so on to excel.

Response.Clear ();
Response.buffer= true;
Response.appendheader ("Content-disposition", "Attachment;filename=" +datetime.now.tostring ("YYYYMMDD") + ". xls");
Response.contentencoding=system.text.encoding.utf8;
Response.ContentType = "application/vnd.ms-excel";
This.enableviewstate = false;

Here we take advantage of the ContentType property, its default property is text/html, then output to hypertext, that is, our common Web page format to the client, if changed to Ms-excel will output Excel format, that is, in the format of the spreadsheet output to the client, Then the browser will prompt you to download the save. The ContentType properties also include: Image/jpeg;text/html;image/gif;vnd.ms-excel/msword. Similarly, we can export (export) pictures, Word documents, and so on. This property is also used for the following methods.

2. Export data from the DataGrid control to Excel

Although the above method realizes the function of export, it also guides all the output information in the HTML such as button, page box and so on. What we typically export is data, data on the DataGrid control.

System.Web.UI.Control Ctl=this.datagrid1;
DataGrid1 is the control you drag and drop on the form
Httpcontext.current.response.appendheader ("Content-disposition", "Attachment;filename=excel.xls");
Httpcontext.current.response.charset = "Utf-8";
Httpcontext.current.response.contentencoding =system.text.encoding.default;
Httpcontext.current.response.contenttype = "Application/ms-excel";
Ctl.page.enableviewstate =false;
System.IO.StringWriter tw = new System.IO.StringWriter ();
System.Web.UI.HtmlTextWriter HW = new System.Web.UI.HtmlTextWriter (TW);
Ctl.rendercontrol (HW);
Httpcontext.current.response.write (Tw.tostring ());
Httpcontext.current.response.end ();

If your DataGrid uses pagination, it exports the information from the current page, which is the information displayed in the DataGrid. Instead of all the information for your SELECT statement.

For ease of use, write the following methods:

public void Dgtoexcel (System.Web.UI.Control ctl)   
  {
   HttpContext . Current.response.appendheader ("Content-disposition", "Attachment;filename=excel.xls");
   httpcontext.current.response.charset = "Utf-8";    
   Httpcontext.current.response.contentencoding =system.text.encoding.default;
   httpcontext.current.response.contenttype = "Application/ms-excel";
   ctl.page.enableviewstate =false;   
   system.io.stringwriter  TW = new System.IO.StringWriter ();
   system.web.ui.htmltextwriter hw = new System.Web.UI.HtmlTextWriter (TW);
   Ctl.rendercontrol (HW);
   Httpcontext.current.response.write (tw.tostring ());
   httpcontext.current.response.end ();
 }
   Usage: dgtoexcel (DATAGRID1);
  

3. Export the data in the DataSet to Excel

With the above idea, is to be exported in the information, output (response) client, so that it can be exported. Then it is OK to export the data in the dataset, that is, to response the rows in the dataset to the HTTP stream in ms-excel format. Note: The parameter DS should be a dataset populated with a datasheet with a full name, including a suffix name, such as Excel2006.xls

public void Createexcel (DataSet ds,string filename)
{
HttpResponse resp;
RESP = Page.Response;
resp.contentencoding = system.text.encoding.getencoding ("gb2312");
Resp.appendheader ("Content-disposition", "attachment;filename=" +filename);
String Colheaders= "", ls_item= "";

Defines a Table object and a row object, and initializes its value with a dataset.
DataTable Dt=ds.tables[0];
Datarow[] Myrow=dt.select ()//can be similar to Dt.select ("id>10") to achieve data filtering purposes
int i=0;
int cl=dt.columns.count;

Get the column headings of the datasheet, divide the headings by T, and add the carriage return after the last column heading
for (I=0;i {
if (i== (CL-1))//last column, plus n
{
Colheaders +=dt.columns[i].caption.tostring () + "n";
}
Else
{
Colheaders+=dt.columns[i].caption.tostring () + "T";
}

}
Resp.write (colheaders);
Writes the obtained data information to the HTTP output stream

Process data line by row
foreach (DataRow row in Myrow)
{
The current row data is written to the HTTP output stream and the null Ls_item is placed so that the downlink data
for (I=0;i {
if (i== (CL-1))//last column, plus n
{
Ls_item +=row[i].tostring () + "n";
}
Else
{
Ls_item+=row[i].tostring () + "T";
}
}
Resp.write (Ls_item);
Ls_item= "";
}
Resp.end ();
}

First, the method of exporting execl in asp.net:

There are two ways to export excel in ASP.net, which is to store the exported file under a server folder, and then export the file address to the browser, one of which is to direct the file output stream to the browser. When response output, T-delimited data, when exporting Excel, is equivalent to a column, n is equivalent to wrapping.
1, the entire HTML output Excel

This method prints all the content in HTML, such as buttons, tables, pictures, and so on to excel.
Response.Clear ();
Response.buffer= true;
Response.appendheader ("Content-disposition", "Attachment;filename=" +datetime.now.tostring ("YYYYMMDD") + ". xls");
Response.contentencoding=system.text.encoding.utf8;
Response.ContentType = "application/vnd.ms-excel";
This.enableviewstate = false;

Here we take advantage of the ContentType property, its default property is text/html, then output to hypertext, that is, our common Web page format to the client, if changed to Ms-excel will output Excel format, that is, in the format of the spreadsheet output to the client, Then the browser will prompt you to download the save. The ContentType properties also include: Image/jpeg;text/html;image/gif;vnd.ms-excel/msword. Similarly, we can export (export) pictures, Word documents, and so on. This property is also used for the following methods.

2. Export data from the DataGrid control to Excel

Although the above method realizes the function of export, it also guides all the output information in the HTML such as button, page box and so on. What we typically export is data, data on the DataGrid control.
System.Web.UI.Control Ctl=this.datagrid1;
DataGrid1 is the control you drag and drop on the form
Httpcontext.current.response.appendheader ("Content-disposition", "Attachment;filename=excel.xls");
Httpcontext.current.response.charset = "Utf-8";
Httpcontext.current.response.contentencoding =system.text.encoding.default;
Httpcontext.current.response.contenttype = "Application/ms-excel";
Ctl.page.enableviewstate =false;
System.IO.StringWriter tw = new System.IO.StringWriter ();
System.Web.UI.HtmlTextWriter HW = new System.Web.UI.HtmlTextWriter (TW);
Ctl.rendercontrol (HW);
Httpcontext.current.response.write (Tw.tostring ());
Httpcontext.current.response.end ();

If your DataGrid uses pagination, it exports the information from the current page, which is the information displayed in the DataGrid. Instead of all the information for your SELECT statement.

For ease of use, write the following method:
public void Dgtoexcel (System.Web.UI.Control ctl)   
  {
   Httpco Ntext.current.response.appendheader ("Content-disposition", "Attachment;filename=excel.xls");
   httpcontext.current.response.charset = "Utf-8";    
   Httpcontext.current.response.contentencoding =system.text.encoding.default;
   httpcontext.current.response.contenttype = "Application/ms-excel";
   ctl.page.enableviewstate =false;   
   system.io.stringwriter  TW = new System.IO.StringWriter ();
   system.web.ui.htmltextwriter hw = new System.Web.UI.HtmlTextWriter (TW);
   Ctl.rendercontrol (HW);
   Httpcontext.current.response.write (tw.tostring ());
   httpcontext.current.response.end ();
 }
   Usage: dgtoexcel (DATAGRID1);
  
3, export data from a DataSet to Excel

With the above idea, is to be exported in the information, output (response) client, so that it can be exported. Then it is OK to export the data in the dataset, that is, to response the rows in the dataset to the HTTP stream in ms-excel format. Note: The parameter DS should be a dataset populated with a datasheet with a full name, including a suffix name, such as Excel2006.xls

public void Createexcel (DataSet ds,string filename)
{
HttpResponse resp;
RESP = Page.Response;
resp.contentencoding = system.text.encoding.getencoding ("gb2312");
Resp.appendheader ("Content-disposition", "attachment;filename=" +filename);
String Colheaders= "", ls_item= "";

Defines a Table object and a row object, and initializes its value with a dataset.
DataTable Dt=ds.tables[0];
Datarow[] Myrow=dt.select ()//can be similar to Dt.select ("id>10") to achieve data filtering purposes
int i=0;
int cl=dt.columns.count;

Get the column headings of the datasheet, divide the headings by T, and add the carriage return after the last column heading
for (I=0;i {
if (i== (CL-1))//last column, plus n
{
Colheaders +=dt.columns[i].caption.tostring () + "n";
}
Else
{
Colheaders+=dt.columns[i].caption.tostring () + "T";
}

}
Resp.write (colheaders);
Writes the obtained data information to the HTTP output stream

Process data line by row
foreach (DataRow row in Myrow)
{
The current row data is written to the HTTP output stream and the null Ls_item is placed so that the downlink data
for (I=0;i {
if (i== (CL-1))//last column, plus n
{
Ls_item +=row[i].tostring () + "n";
}
Else
{
Ls_item+=row[i].tostring () + "T";
}
}
Resp.write (Ls_item);
Ls_item= "";
}
Resp.end ();
}

For many objects in Excel, such as cell, graph (Shape), chart (chart), and so on, sometimes you need to save them as a picture. It's like a screenshot.
Recently to do an Excel-related project, the project encountered a very abnormal requirements, you need to make some of the objects in Excel to take pictures, such as a cell to set some color after the picture, or to a chart, the report taken as a picture. After a more tortuous experience, and finally finished. Take it out and share it.
To do Excel, you first of course view the COM object model for Excel. The address is here:
http://msdn.microsoft.com/en-us/library/bb149081 (v=office.12). aspx
Here is a description:
Official excel2010 object reference did not find, point in is dead circulation, point to point to just can't find, which God-man found trouble reply to tell.
Excel2003 object model, need to be down to install, more trouble, I also installed after the only know, here is recommended to you through the above Web site to view the EXCEL2007 model on it. In fact, according to Microsoft's compatibility Convention, the difference between excel2010 and excel2003 should be small (yes?). ）。
Then you look at the member of the Range object and expect it to have a ready-made method: http://msdn.microsoft.com/en-us/library/bb225606 (v=office.12). aspx
I looked around, and I didn't find a way of exportimage,saveimage or anything like that. Expressing frustration, but it is understandable.
Despair found a bright spot, a method named CopyPicture.    Look at the method instructions, is to copy the object as a picture to the Clipboard. Oh, a more twisted idea was born, since can be copied to the Clipboard inside, I again from the Clipboard inside the picture out of the line.
OK, that's it.
......
Omit 200 words here (how to create an Excel COM object, how to get to the Range object do not say, do not know their own search, you can reply to questions.) ）
......
After you get the Range object. Calling the CopyPicture method requires two parameters. The first parameter is the XlPictureAppearance enumeration, 1 is copied as the screen looks, and 2 is copied as it is printed. The second parameter is the XlCopyPictureFormat enumeration, 2 indicates that the copy is a bitmap, and 4147 indicates that it is copied into a vector picture.
So I wrote something similar to the following code.

Range cell = excel.activesheet.cells[1,1];
Cell.copypicture (1,2);
Bitmap image = Clipboard.getimage () as bitmap;
if (image!= null)
{
Can do whatever they want here.
}

Some of the limitations of. NET, some old programs (such as the excel2007 encountered here) are copied to the Clipboard data may not be available and need to be used through the local API. I'll go... There is also no word on MSDN to remind you of it.
Landlord is a more diligent person, know this, then do not hurry up

IntPtr hwnd = Excel.hwnd;
try          & nbsp;