Export datatable and image to excel

1. Function Description

A. Import the datatable data and the table header into excel. The position can be set at will, for example, B4, A1...

B. Import the image to excel and specify the Excel import location, such as B5, c6...

C. The button is a button and can pop up a dialog box like hyperlink, asking users to export it (open, save, cancel)

 

2. Call class

 

Using system;
Using system. Web;
Using system. IO;
Using system. Data;
Using Excel = Microsoft. Office. InterOP. Excel;

Namespace datatoexcel
{
/// <Summary>
/// Summary of datatoexcel.
/// </Summary>
Public class datatoexcel
{
/// <Summary>
/// Open an operation without a template.
/// </Summary>
Public void open ()
{
This. Open (string. Empty );
}

/// <Summary>
/// Function: Excel applicationProgramOpen
/// </Summary>
/// <Param name = "templatefilepath"> physical path of the template file </param>
Public void open (string templatefilepath)
{
// Open the object
M_objexcel = new excel. Application ();
M_objexcel.visible = false;
M_objexcel.displayalerts = false;

If (m_objexcel.version! = "11.0 ")
{
// MessageBox. Show ("your Excel version is not 11.0 (Office 2003), and the operation may fail. ");
M_objexcel.quit ();
Return;
}

m_objbooks = (Excel. workbooks) m_objexcel.workbooks;
If (templatefilepath. equals (string. empty)
{< br> m_objbook = (Excel. _ workbook) (cost (m_objopt);
}< br> else
{< br> m_objbook = m_objbooks.open (templatefilepath, m_objopt,
m_objopt, m_objopt);
}< br> m_objsheet = (Excel. sheets) m_objbook.worksheets;
m_objsheet = (Excel. _ worksheet) (m_objsheets.get_item (1);
m_objexcel.workbookbeforeclose + = new excel. appevents_workbookbeforecloseeventhandler (m_objexcel_workbookbeforeclose);
}

Private void m_objexcel_workbookbeforeclose (Excel. Workbook m_objbooks, ref bool _ cancel)
{
// MessageBox. Show ("saved! ");
}

/// <Summary>
/// Insert the image to the specified cell position.
/// Note: The image must be an absolute physical path.
/// </Summary>
/// <Param name = "rangename"> cell name, for example, B4 </param>
/// <Param name = "picturepath"> specifies the absolute path of the image to be inserted. </Param>
Public void insertpicture (string rangename, string picturepath)
{
M_objrange = m_objsheet.get_range (rangename, m_objopt );
M_objrange.select ();
Excel. Pictures pics = (Excel. Pictures) m_objsheet.pictures (m_objopt );
Pics. insert (picturepath, m_objopt );
}

/// <Summary>
/// Insert the image to the specified cell position and set the width and height of the image.
/// Note: The image must be an absolute physical path.
/// </Summary>
/// <Param name = "rangename"> cell name, for example, B4 </param>
/// <Param name = "picturepath"> specifies the absolute path of the image to be inserted. </Param>
/// <Param name = "pictutewidth"> the width of the inserted image displayed in Excel. </Param>
/// <Param name = "pictureheight"> the height of the inserted image displayed in Excel. </Param>
Public void insertpicture (string rangename, string picturepath, float pictutewidth, float pictureheight)
{
M_objrange = m_objsheet.get_range (rangename, m_objopt );
M_objrange.select ();
Float picleft, pictop;
Picleft = convert. tosingle (m_objrange.left );
Pictop = convert. tosingle (m_objrange.top );
// Parameter description:
// Image path
// Whether to link to the file
// Whether the image is saved along with the document during insertion
// Coordinate Position of the image in the document (unit: points)
// The width and height of the image (unit: points)
M_objsheet.shapes.addpicture (picturepath, Microsoft. Office. Core. msotristate. msofalse,
Microsoft. Office. Core. msotristate. msotrue, picleft, pictop, pictutewidth, pictureheight );
}

///

// Insert the datatable into the Excel file.
///
/// Insert Location. for example, A1
// inserted Table
Public void datatabletoexcel (string rangename, datatable DT)
{< br> int rowindex = 1;
int colindex = 0;
foreach (datacolumn Col in DT. columns)
{< br> colindex ++;
m_objexcel.cells [rowindex, colindex] = Col. columnname;
}

foreach (datarow row in DT. rows)
{< br> rowindex ++;
for (INT I = 1; I <= DT. columns. count; I ++)
{< br> m_objexcel.cells [rowindex, I] = row [I-1];
}< BR >}< br> //

// Save the Excel file to the specified directory, the directory must exist beforehand, and the file name may not exist.
///
/// full path of the file to be saved.
Public void SaveFile (string outputfilepath)
{< br> values (outputfilepath, m_objopt, m_objopt,
m_objopt, Excel. xlsaveasaccessmode. xlnochange,
m_objopt, m_objopt);

This. close ();
}< BR >///

// close the application
///
private void close ()
{< br> m_objbook.close (false, m_objopt, m_objopt);
m_objexcel.quit ();
}

///

// release the referenced COM object. Note: This process must be executed.
//
Public void dispose ()
{< br> releaseobj (m_objsheets);
releaseobj (m_objbook );
releaseobj (m_objbooks);
releaseobj (m_objexcel);
system. GC. collect ();
system. GC. waitforpendingfinalizers ();
}< br> //
// release the object, internal call
//
//
private void releaseobj (Object O)
{< br> try
{< br> system. runtime. interopservices. marshal. releasecomobject (o);
}< br> catch {}< br> finally {o = NULL ;}< BR >}

/// <Summary>
/// Upload from the Web hosts file
/// </Summary>
/// <Param name = "strfile"> path of the file to be deleted </param>
Public void downloadfile (string strfile)
{
Httpcontext. Current. response. Clear ();
Httpcontext. Current. response. contenttype = "application/octet-stream ";
Httpcontext. current. response. addheader ("content-disposition", "attachment; filename = \" "+ httputility. urlencode (path. getfilename (strfile ). trim () + "\"");
Httpcontext. Current. response. Flush ();

Httpcontext. Current. response. writefile (strfile );
Httpcontext. Current. response. End ();
}

Private excel. Application m_objexcel = NULL;
Private excel. workbooks m_objbooks = NULL;
Private excel. _ workbook m_objbook = NULL;
Private excel. Sheets m_objsheets = NULL;
Private excel. _ worksheet m_objsheet = NULL;
Private excel. Range m_objrange = NULL;
Private object m_objopt = system. reflection. Missing. value;
}
}

 

3. Call example.

Private void btnexport_click (Object sender, system. eventargs E)
{
Datatoexcel DTE = new datatoexcel ();

DTE. open ();

Datatable dt = (datatable) viewstate ["table"];

DTE. datatabletoexcel ("B1", DT );

String txtimge = this.txt IMG. Text. Trim ();

String imag = txtimge. substring (txtimge. indexof ("DnC-"), 16 );

DTE. insertpicture ("B4", server. mappath ("~ /Temp/"+ IMAG ));

DTE. SaveFile (server. mappath ("~ /Temp/ziping.xls "));

DTE. downloadfile (server. mappath ("~ /Temp/ziping.xls "));

DTE. Dispose ();
}

 

I hope it will help you. Please reply to this post! Haha .....