. NET exports (exports) data to an Excel file

In the winformweb webprogram, data in the datagridview datasetis exported as Excel files. The following example shows how to export data from the DataGridView datagriinto an Excel file in the WinForm program. The method for exporting data from the DataSet to an Excel file is similar to this one, I will not introduce it here.
[Csharp]
Private void DataGridViewToExcel (DataGridView dgv)
{
SaveFileDialog sfDialog = new SaveFileDialog (); // Save the exported excel dialog box
SfDialog. Filter = "Excel Files (*. xls) | *. xls"; // The file Filter can only save Files of the .xls type.
SfDialog. CheckFileExists = false; // If the User specifies whether a file name that does not exist is prompted
SfDialog. CheckPathExists = false; // If the User specifies whether a nonexistent path is prompted
SfDialog. FilterIndex = 0;
SfDialog. RestoreDirectory = true;
SfDialog. CreatePrompt = true; // if the file does not exist, the system prompts you to create a new file.
SfDialog. Title = "Save As an Excel file! ";
If (sfDialog. ShowDialog () = DialogResult. OK)
{
Stream saveStream = sfDialog. OpenFile (); // open the excel file to be saved
StreamWriter sw = new StreamWriter (saveStream, Encoding. GetEncoding (-0); // insert characters to the stream in a specific Encoding, GetEncoding (-0)
The code page identifier of the preferred code. -Or-0
String columnTitle = "";
Try
{
For (int I = 0; I <dgv. ColumnCount; I ++)
{
If (I> 0)
ColumnTitle + = "\ t ";
ColumnTitle + = dgv. Columns [I]. HeaderText;
}
Sw. WriteLine (columnTitle); // write the title row
For (int j = 0; j <dgv. RowCount; j ++)
{
String columnValue = "";
For (int k = 0; k <dgv. ColumnCount; k ++)
{
If (k> 0)
ColumnValue + = "\ t ";
If (dgv. Rows [j]. Cells [k]. Value. ToString () = "")
{
ColumnValue + = "";
}
Else
ColumnValue + = dgv. Rows [j]. Cells [k]. Value. ToString (). Trim ();
}
Sw. WriteLine (columnValue); // write information to an excel file one by one
}
Sw. Close ();
SaveStream. Close ();
}
Catch (Exception e)
{
MessageBox. Show (e. Message );
}
}
}
The following shows how to export data to an Excel file in Asp.net. Here is a simple demonstration, so there is not much layout, and there is only one button on the page, the event processing in the button is as follows:
[Csharp]
Protected void btndstoexcel_Click (object sender, EventArgs e)
{
Using (SqlConnection sqlConn = new SqlConnection ("Initial Catalog = Manager; Integrated Security = true; data source = ."))
{
SqlConn. Open ();
SqlCommand sqlCmd = new SqlCommand ("select * from associator", sqlConn );
SqlDataAdapter sdAdapter = new SqlDataAdapter (sqlCmd );
DataSet ds = new DataSet ();
SdAdapter. Fill (ds, "Result"); // connect to the database and read the information into DataSet.
Response. ContentEncoding = Encoding. GetEncoding ("GB2312"); // you can specify the Encoding format.
Response. ContentType = "application/vnd. ms-excel"; // set the input type to an Excel file and specify that the returned stream cannot be read by the client and must be downloaded.
Response. AddHeader ("Content-Disposition", "attachment?filename=test.xls"); // save the file as test.xls
String columnHeader = ""; // Save the header character
String columnContent = ""; // Save the data content of each row
DataTable dsTable = ds. Tables ["Result"];
For (int I = 0; I <dsTable. Columns. Count; I ++)
{
If (I = dsTable. Columns. Count-1)
ColumnHeader + = dsTable. Columns [I]. Caption. ToString () + "\ n"; // line feed is required when the top column is the last column
Else
ColumnHeader + = dsTable. Columns [I]. Caption. ToString () + "\ t ";
}
Response. Write (columnHeader );
// Add the data information of each row
Foreach (DataRow dr in dsTable. Rows)
{
For (int j = 0; j <dsTable. Columns. Count; j ++)
{
If (j = dsTable. Columns. Count-1)
ColumnContent + = dr [j] + "\ n"; // line feed when the top column is the last column
Else
ColumnContent + = dr [j] + "\ t ";
}
Response. Write (columnContent );
ColumnContent = "";
}
Response. End ();
}
}
After data export, let's talk about data import. Here we show how to read data from an Excel file. The code is very simple and I will not talk about it too much here. I hope to inspire you.

[Csharp]
Private void ExcelToDataGridView (DataGridView dgv)
{
OpenFileDialog dlg = new OpenFileDialog ();
Dlg. Filter = "Execl files (*. xls) | *. xls ";
Dlg. CheckFileExists = false;
Dlg. CheckPathExists = false;
Dlg. FilterIndex = 0;
Dlg. RestoreDirectory = true;
Dlg. Title = "importing Excel file data to DataSet ";
Dlg. ShowDialog ();
 
DataSet ds = new DataSet ();
String strConn = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ dlg. fileName. trim () + "; Extended Properties = 'excel 8.0; HDR = False; IMEX = 1 '";
Using (OleDbConnection OleConn = new OleDbConnection (strConn ))
{
OleConn. Open ();
String SQL = "SELECT * FROM [Sheet1 $]";
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter (SQL, OleConn );
OleDaExcel. Fill (ds );
OleConn. Close ();
}
Dgv. DataSource = ds. Tables [0]. DefaultView;
}
Now, we are here to demonstrate how to export data to Excel in different windows and web environments and read data from the Excel file in WinForm, it mainly focuses on the import and export of Excel data, and there are also a lot of places to be used at ordinary times. I hope to inspire you. Of course there are other methods, so I will not talk about it here, here is just a simple and commonly used method. If you are interested, you can study it by yourself.