Asp.Net imports and exports Excel methods using Npoi

asp.net Import and export of Excel files is one of the most common features. This article illustrates Asp.Net uses Npoi to import and export Excel. Share with you for your reference. The specific methods are as follows:

When exporting Excel with Npoi, the server can not install any office components, usually export Excel file with Npoi, the guide Excel also conforms to the specification, there will be no file corruption and other prompts when opening. However, OleDb is still used for the import, which seems to require the office component on the server side.

1. Npoi Export/Download Excel

Specific functional codes are as follows:

public 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 = VerticalAlignment.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; filename={0}.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();
}

2. Asp.Net import Excel

Import is still using ES32en this way, interested friends can try 1 other methods.

Specific functional codes are as follows:

/// <summary>
///  The connection Excel  read Excel data    And return DataSet The data set 
/// </summary>
/// <param name="filepath">Excel Server path </param>
/// <param name="tableName">Excel The name of the table </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;
  }
}

I believe that this article described to everyone's asp.net program design has a definite reference value.