Three methods for exporting Asp.net dview to excel [test]

# Region datagridview data is displayed in Excel
/// <Summary>
/// Open Excel and export the data in the datagridview control to excel
/// </Summary>
/// <Param name = "dgv"> datagridview object </param>
/// <Param name = "isshowexcle"> whether to display the Excel page </param>
/// <Remarks>
/// Add com "Microsoft Excel 11.0 Object Library"
/// Using Excel = Microsoft. Office. InterOP. Excel;
/// </Remarks>
/// <Returns> </returns>
Public bool datagridviewshowtoexcel (datagridview dgv, bool isshowexcle)
{
If (dgv. Rows. Count = 0)
Return false;
// Create an Excel Object
Excel. Application Excel = new excel. Application ();
Excel. application. workbooks. Add (true );
Excel. Visible = isshowexcle;
// Generate the field name
For (INT I = 0; I <dgv. columncount; I ++)
{
Excel. cells [1, I + 1] = dgv. Columns [I]. headertext;
}
// Fill in data
For (INT I = 0; I <dgv. rowcount-1; I ++)
{
For (Int J = 0; j <dgv. columncount; j ++)
{
If (dgv [J, I]. valuetype = typeof (string ))
{
Excel. cells [I + 2, J + 1] = "'" + dgv [J, I]. value. tostring ();
}
Else
{
Excel. cells [I + 2, J + 1] = dgv [J, I]. value. tostring ();
}
}
}
Return true;
}
# Endregion

# region dategridview export data to an Excel file in CSV format
///

// common method. Add \ t between columns and output one row at a time, this file is actually a CSV file, but it can be opened as an Excel file by default.
///
///
// using system. io;
//
//
private void datagridviewtoexcel (datagridview dgv)
{< br> savefiledialog DLG = new savefiledialog ();
DLG. filter = "execl files (*. XLS) | *. xls ";
DLG. filterindex = 0;
DLG. restoredirectory = true;
DLG. createprompt = true;
DLG. title = "Save As an Excel file";

If (DLG. showdialog () = dialogresult. OK)
{< br> stream mystream;
mystream = DLG. openfile ();
streamwriter Sw = new streamwriter (mystream, system. text. encoding. getencoding (-0);
string columntitle = "";
try
{
// write Column Title
for (INT I = 0; I {< br> if (I> 0)
{< br> columntitle + = "\ t ";
}< br> columntitle + = dgv. columns [I]. headertext;
}< br> SW. writeline (columntitle);

// write column content
for (Int J = 0; j {< br> string columnvalue = "";
for (int K = 0; k {< br> If (k> 0)
{< br> columnvalue + = "\ t ";
}< br> If (dgv. rows [J]. cells [K]. value = NULL)
columnvalue + = "";
else
columnvalue + = dgv. rows [J]. cells [K]. value. tostring (). trim ();
}< br> SW. writeline (columnvalue);
}< br> SW. close ();
mystream. close ();
}< br> catch (exception e)
{< br> MessageBox. show (E. tostring ();
}< br> finally
{< br> SW. close ();
mystream. close ();
}< BR >#endregion

# Region datagridview exported to excel, which has a certain degree of attention
/// <Summary>
/// Method to export data from the datagridview to an Excel file
/// </Summary>
/// <Remarks>
/// Add com "Microsoft Excel 11.0 Object Library"
/// Using Excel = Microsoft. Office. InterOP. Excel;
/// Using system. reflection;
/// </Remarks>
/// <Param name = "dgv"> datagridview </param>
Public static void datagridviewtoexcel (datagridview dgv)
{

# Region verification operability

// Statement save dialog box
Savefiledialog DLG = new savefiledialog ();
// Silent file suffix
DLG. defaultext = "xls ";
// File Suffix List
DLG. Filter = "Excel file (*. xls) | *. xls ";
// The Silent path is the current path of the system
DLG. initialdirectory = directory. getcurrentdirectory ();
// Open the Save dialog box.
If (DLG. showdialog () = dialogresult. Cancel) return;
// Return file path
String filenamestring = DLG. filename;
// Verify whether strfilename is null or the value is invalid
If (filenamestring. Trim () = "")
{Return ;}
// Define the number of rows and columns of table data
Int rowscount = dgv. Rows. count;
Int colscount = dgv. Columns. count;
// The number of rows must be greater than 0
If (rowscount <= 0)
{
MessageBox. Show ("no data is available for storage", "prompt", messageboxbuttons. OK, messageboxicon. information );
Return;
}

// The number of columns must be greater than 0
If (colscount <= 0)
{
MessageBox. Show ("no data is available for storage", "prompt", messageboxbuttons. OK, messageboxicon. information );
Return;
}

// The number of rows cannot exceed 65536
If (rowscount> 65536)
{
MessageBox. Show ("too many data records (up to 65536 records), cannot be saved", "prompt", messageboxbuttons. OK, messageboxicon. information );
Return;
}

// The number of Columns cannot exceed 255
If (colscount> 255)
{
MessageBox. Show ("too many data records, cannot be saved", "prompt", messageboxbuttons. OK, messageboxicon. information );
Return;
}

// verify whether a file named after filenamestring exists. If yes, delete it.
fileinfo file = new fileinfo (filenamestring);
If (file. exists)
{< br> try
{< br> file. delete ();
}< br> catch (Exception error)
{< br> MessageBox. show (error. message, "failed to delete", messageboxbuttons. OK, messageboxicon. warning);
return;
}< BR ># endregion
excel. application objexcel = NULL;
excel. workbook objworkbook = NULL;
excel. worksheet objsheet = NULL;
try
{< br> // declarative object
objexcel = new Microsoft. office. interOP. excel. application ();
objworkbook = objexcel. workbooks. add (missing. value);
objsheet = (Excel. worksheet) objworkbook. activesheet;
// set EXCEL to invisible
objexcel. visible = false;

// Write the table header to the Excel file
Int displaycolumnscount = 1;
For (INT I = 0; I <= dgv. columncount-1; I ++)
{
If (dgv. Columns [I]. Visible = true)
{
Objexcel. cells [1, displaycolumnscount] = dgv. Columns [I]. headertext. Trim ();
Displaycolumnscount ++;
}
}
// Set the progress bar
// Tempprogressbar. Refresh ();
// Tempprogressbar. Visible = true;
// Tempprogressbar. Minimum = 1;
// Tempprogressbar. Maximum = dgv. rowcount;
// Tempprogressbar. Step = 1;
// Write data in the table row by row to excel
For (int row = 0; row <= dgv. rowcount-1; row ++)
{
// Tempprogressbar. extends mstep ();

Displaycolumnscount = 1;
For (INT Col = 0; Col <colscount; Col ++)
{
If (dgv. Columns [col]. Visible = true)
{
Try
{
Objexcel. cells [row + 2, displaycolumnscount] = dgv. Rows [row]. cells [col]. value. tostring (). Trim ();
Displaycolumnscount ++;
}
Catch (exception)
{

}

}
}
}
// Hide the progress bar
// Tempprogressbar. Visible = false;
// Save the file
Objworkbook. saveas (filenamestring, missing. Value, missing. value,
Missing. Value, Excel. xlsaveasaccessmode. xlshared, missing. Value, missing. Value, missing. value,
Missing. Value, missing. value );
}
Catch (Exception error)
{
MessageBox. Show (error. message, "warning", messageboxbuttons. OK, messageboxicon. Warning );
Return;
}
Finally
{
// Close the Excel application
If (objworkbook! = NULL) objworkbook. Close (missing. Value, missing. Value, missing. value );
If (objexcel. workbooks! = NULL) objexcel. workbooks. Close ();
If (objexcel! = NULL) objexcel. Quit ();

Objsheet = NULL;
Objworkbook = NULL;
Objexcel = NULL;
}
MessageBox. Show (filenamestring + "\ n exported! "," Prompt ", messageboxbuttons. OK, messageboxicon. information );

}

# Endregion