ASP.NET中使用開源組件NPOI快速導(dǎo)入導(dǎo)出Execl數(shù)據(jù)

/// summary>
/// 導(dǎo)出Execl
/// /summary>
/// returns>/returns>
public FileResult DataExportToExecl()
{
  //創(chuàng)建Excel文件的對象
  NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
  NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一個sheet

  var _data = CardHelper.GetAllData(); //獲取list數(shù)據(jù)，也可以分頁獲取數(shù)據(jù)，以獲得更高效的性能
  //給sheet1添加第一行的頭部標(biāo)題
  NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
  row1.CreateCell(0).SetCellValue("排名");
  row1.CreateCell(1).SetCellValue("CardID");
  row1.CreateCell(2).SetCellValue("姓名");
  row1.CreateCell(3).SetCellValue("手機(jī)");
  row1.CreateCell(4).SetCellValue("職位");
  row1.CreateCell(5).SetCellValue("所在公司");
  row1.CreateCell(6).SetCellValue("創(chuàng)建時間");

  //將數(shù)據(jù)逐步寫入sheet1各個行
  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) ? "無" : _data[i].WorTitle);
      rowtemp.CreateCell(5).SetCellValue(string.IsNullOrEmpty(_data[i].Company) ? "無" : _data[i].Company);
      rowtemp.CreateCell(6).SetCellValue(_data[i].CreateDate.ToString());               
  }
  // 寫入到客戶端
  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");
}

/// summary>
/// 導(dǎo)入Execl信息
/// /summary>
/// returns>/returns>
private DataView LoadExeclFile()
{
   //虛擬生成一個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";　　//讀取本地Execl，當(dāng)前路徑是該程序的所在目錄
   HSSFWorkbook wb = new HSSFWorkbook(new FileStream(filepath, FileMode.Open));
   HSSFSheet sheet = wb.GetSheet("data") as HSSFSheet; //獲取execl中名字為data的sheet表格數(shù)據(jù)

   if (sheet == null)
   {
        MessageBox.Show("檢查是否文件路徑和文件名稱有誤！");
   }
   else
   {
        //導(dǎo)入數(shù)據(jù)
        for (int i = 1; i = sheet.LastRowNum; i++) //獲得所有行數(shù)
        {
             IRow row = sheet.GetRow(i); //讀取當(dāng)前行數(shù)據(jù)
             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;
}

/// summary>
/// 導(dǎo)入Execl信息
/// /summary>
/// returns>/returns>
 private void LoadExeclFile()
{
    string filepath = @"data.xls";　　//讀取本地Execl，當(dāng)前路徑是該程序的所在目錄
    HSSFWorkbook wb = new HSSFWorkbook(new FileStream(filepath, FileMode.Open));
    HSSFSheet sheet = wb.GetSheet("data") as HSSFSheet; //獲取execl中名字為data的sheet表格數(shù)據(jù)

    if (sheet != null)
    {
        //導(dǎo)入數(shù)據(jù)
        for (int i = 1; i = sheet.LastRowNum; i++) //獲得所有行數(shù)
        {
               IRow row = sheet.GetRow(i); //讀取當(dāng)前行數(shù)據(jù)
               if (row != null)
               {
                   InsertDataIntoDB(row.GetCell(0).ToString(),row.GetCell(1).ToString(),row.GetCell(2).ToString(),row.GetCell(3).ToString());
               }　　
　　　　　} 　　　
　　　}　
}