ASP.NET uses the open source component NPOI to quickly import and export Execl data
- 2021-01-06 00:31:42
- OfStack
I believe that many children's shoes have developed the import and export function of Execl. In recent products, whether it is the need of background data analysis, or the convenience of front end to meet user management, there are Execl import and export maintenance needs.
HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.ContentType = "application/ms-excel"; You can export html data tables to execl. The problem with this method is that the compatibility of the encoding format is too poor. The main problem with this approach is that the client must have office installed. If you have wps installed, it will not work. The main problem is that the client must have office installed.
With the recent use of NPOI as an open source component in production, the appeal approach immediately feels too Out. Take a look at the power of code 1:
/// <summary>
/// export Execl
/// </summary>
/// <returns></returns>
public FileResult DataExportToExecl()
{
// create Excel Object of file
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); // add 1 a sheet
var _data = CardHelper.GetAllData(); // To obtain list Data, which can also be paged for more efficient performance
// to sheet1 Add the first 1 Row header
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue(" ranking ");
row1.CreateCell(1).SetCellValue("CardID");
row1.CreateCell(2).SetCellValue(" The name ");
row1.CreateCell(3).SetCellValue(" Mobile phone ");
row1.CreateCell(4).SetCellValue(" position ");
row1.CreateCell(5).SetCellValue(" company ");
row1.CreateCell(6).SetCellValue(" Creation time ");
// Write the data step by step sheet1 Each line
for (int i = 0; i < _data.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(i+1);
rowtemp.CreateCell(1).SetCellValue(_data[i].ID);
rowtemp.CreateCell(2).SetCellValue(_data[i].RealName);
rowtemp.CreateCell(3).SetCellValue(_data[i].Cellphone);
rowtemp.CreateCell(4).SetCellValue(string.IsNullOrEmpty(_data[i].WorTitle) ? " There is no " : _data[i].WorTitle);
rowtemp.CreateCell(5).SetCellValue(string.IsNullOrEmpty(_data[i].Company) ? " There is no " : _data[i].Company);
rowtemp.CreateCell(6).SetCellValue(_data[i].CreateDate.ToString());
}
// Write to the client
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", DateTime.Now.ToString("yyyyMMdd") + ".xls");
}
In the front end, you can simply link to the ES32en with hypertext, whether it's plain text or a button, depending on your preference.
/// <summary>
/// The import Execl information
/// </summary>
/// <returns></returns>
private DataView LoadExeclFile()
{
// Virtual generated 1 a DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Phone", typeof(string));
dt.Columns.Add("CID", typeof(string));
dt.Columns.Add("Status", typeof(string));
DataRow newRow = null;
string filepath = @"data.xls"; // Read the local Execl , the current path is the directory where the program resides
HSSFWorkbook wb = new HSSFWorkbook(new FileStream(filepath, FileMode.Open));
HSSFSheet sheet = wb.GetSheet("data") as HSSFSheet; // To obtain execl In the name of data the sheet Tabular data
if (sheet == null)
{
MessageBox.Show(" Check if the file path and file name are incorrect! ");
}
else
{
// Import data
for (int i = 1; i <= sheet.LastRowNum; i++) // Get all the rows
{
IRow row = sheet.GetRow(i); // Reads the current row
if (row != null)
{
newRow = dt.NewRow();
newRow["Name"] = row.GetCell(0).ToString();
newRow["Phone"] = row.GetCell(1).ToString();
newRow["CID"] = row.GetCell(2).ToString();
newRow["Status"] = row.GetCell(3).ToString();
if (IsMobile(row.GetCell(1).ToString()))
{
dt.Rows.Add(newRow);
}
}
}
}
return dt.DefaultView;
}
The Execl import function is to import data into Gridview. If you want to import data into a database or other data storage medium, replace the code with:
/// <summary>
/// The import Execl information
/// </summary>
/// <returns></returns>
private void LoadExeclFile()
{
string filepath = @"data.xls"; // Read the local Execl , the current path is the directory where the program resides
HSSFWorkbook wb = new HSSFWorkbook(new FileStream(filepath, FileMode.Open));
HSSFSheet sheet = wb.GetSheet("data") as HSSFSheet; // To obtain execl In the name of data the sheet Tabular data
if (sheet != null)
{
// Import data
for (int i = 1; i <= sheet.LastRowNum; i++) // Get all the rows
{
IRow row = sheet.GetRow(i); // Reads the current row
if (row != null)
{
InsertDataIntoDB(row.GetCell(0).ToString(),row.GetCell(1).ToString(),row.GetCell(2).ToString(),row.GetCell(3).ToString());
}
}
}
}
After looking at the code, I believe you can sense that the power of NPOI over Execl operation is beyond words. The import and export of appeals is probably the most frequently used Execl operation in common development, and NPOI has basically encapsulated all kinds of methods to the extreme, unless you have a special business scenario, otherwise with a little modification, you can meet your different business needs. Of course, there are still some problems with the appeal scheme, and the solution is generally similar, time for space, or space for time. Such as:
� derived data quantity is big, to get the data and the data processing, will encounter performance issues, serious is out of memory, even if the correct procedure. The solution is very simple, code transformation 1, using paging to get data, export to multiple sheet or divided into multiple execl export, code I am too lazy to write.
The advantages and disadvantages of NPOI are as follows:
Advantages: use super concise, small white also can see clearly. Standalone components, just by reference. No third party dependency is required. Coding flexible control, excellent compatibility. Performance down to use very excellent, source code written is also very beautiful...
Disadvantages: if there is, please tell me!