Asp. Net use Npoi to export Excel, asp. netnpoi

Asp. Net use Npoi to export Excel, asp. netnpoi
Introduction

The Npoi Excel export server can be used without any office components. Npoi was used to export Excel yesterday, and the exported Excel files comply with the specifications, there will be no prompts such as file corruption when opening. However, we still use OleDb for import. In this way, the office components still need to be installed on the server. Do you need to install components and import them as usual?

 

Npoi export/download Excelpublic void NpoiExcel (DataTable dt, string title) {NPOI. HSSF. userModel. HSSFWorkbook book = new NPOI. HSSF. userModel. HSSFWorkbook (); NPOI. SS. userModel. ISheet sheet = book. createSheet ("Sheet1"); NPOI. SS. userModel. IRow headerrow = sheet. createRow (0); ICellStyle style = book. createCellStyle (); style. alignment = HorizontalAlignment. center; style. verticalAlignment = verticalignment. center; for (int I = 0; I <dt. columns. count; I ++) {ICell cell = headerrow. createCell (I); cell. cellStyle = style; cell. setCellValue (dt. columns [I]. columnName);} MemoryStream MS = new MemoryStream (); book. write (MS); Response. addHeader ("Content-Disposition", string. format ("attachment; filename1_1_02.16.xls", HttpUtility. urlEncode (title + "_" + DateTime. now. toString ("yyyy-MM-dd"), System. text. encoding. UTF8); Response. binaryWrite (ms. toArray (); Response. end (); book = null; ms. close (); ms. dispose ();}View Code
Asp. Net Import

The import is still using OleDb. Is there any other way to solve this problem?

/// <Summary> /// connect to Excel to read Excel Data and return the DataSet Data Set /// </summary> /// <param name = "filepath"> Excel server path </param> /// <param name = "tableName"> Excel table name </param> /// <returns> </returns> public static System. data. dataSet ExcelSqlConnection (string filepath, string tableName) {string strCon = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ filepath +"; Extended Properties = 'excel 8.0; HDR = YES; IMEX = 1' "; OleDbConnection ExcelConn = new OleDbConnection (strCon ); try {string strCom = string. format ("SELECT * FROM [Sheet1 $]"); ExcelConn. open (); OleDbDataAdapter myCommand = new OleDbDataAdapter (strCom, ExcelConn); DataSet ds = new DataSet (); myCommand. fill (ds, "[" + tableName + "$]"); ExcelConn. close (); return ds;} catch {ExcelConn. close (); return null ;}}View Code

 

How can I use NPOIdll in ASPnet to export EXCEL files? C # development language is preferred.

You don't need to use this. How fast do you write yourself,
I used this file. It was written in CSV format and may be opened in EXCEL. You can save it as xls.

/// <Summary>
/// Convert the DataTable to a string
/// </Summary>
/// <Param name = "dt"> </param>
/// <Returns> </returns>
Public static string DtToString (DataTable dt)
{
String data = "";
Try
{
Foreach (DataColumn column in dt. Columns)
{
Data + = column. ColumnName + ",";
}
Data + = "\ r \ n ";

// Write data
Foreach (DataRow row in dt. Rows)
{
Foreach (DataColumn column in dt. Columns)
{
String t = row [column]. ToString ();
If (! String. IsNullOrEmpty (t ))
{
T = t. Replace (",","");
T = t. Replace ("\ r ","");
T = t. Replace ("\ n ","");
T = HttpContext. Current. Server. HtmlEncode (t );
Data + = t + ",";
}
Else
Data + = ",";
}
Data + = "\ r \ n ";
}
Data + = "\ r \ n ";
}
Catch {}
Return data;
}

/// <Summary>
/// Convert the string to CSV format for output
/// </Summary>
/// <Param name = "content"> string content & lt ...... remaining full text>

How does Aspnet use npoi to read excel content?

There are examples in NPOI
To reference
Using NPOI;
Using NPOI. HPSF;
Using NPOI. HSSF;
Using NPOI. HSSF. UserModel;
Using NPOI. SS. UserModel;
Using NPOI. POIFS;
Using NPOI. Util;
Then, you can read this information based on the examples in NPOI.
PS. It is best to set all words in EXCEL to "text ".
References: I have used my work