什么是NPOI

該項目是位于http://poi.apache.org/的POI Java項目的.NET版本。POI是一個開源項目，可以幫助您讀取/寫入xls，doc，ppt文件。它有著廣泛的應用。本文給大家介紹ASP.Net MVC利用NPOI導入導出Excel的問題。

因近期項目遇到所以記錄一下：

首先導出Excel：

首先引用NPOI包

（Action一定要用FileResult）

/// <summary>
/// 批量導出本校第一批派位學生
/// </summary>
/// <returns></returns>
public FileResult ExportStu2()
{
    string schoolname = "401";
    //創(chuàng)建Excel文件的對象
    NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
    //添加一個sheet
    NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
    //獲取list數(shù)據(jù)
    List<TB_STUDENTINFOModel> listRainInfo = m_BLL.GetSchoolListAATQ(schoolname);
    //給sheet1添加第一行的頭部標題
    NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
    row1.CreateCell(0).SetCellValue("電腦號");
    row1.CreateCell(1).SetCellValue("姓名");
    //將數(shù)據(jù)逐步寫入sheet1各個行
    for (int i = 0; i < listRainInfo.Count; i++)
    {
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].ST_CODE.ToString());
rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].ST_NAME.ToString());
    }
    // 寫入到客戶端 
    System.IO.MemoryStream ms = new System.IO.MemoryStream();
    book.Write(ms);
    ms.Seek(0, SeekOrigin.Begin);
    return File(ms, "application/vnd.ms-excel", "第一批電腦派位生名冊.xls");
}

前臺直接寫就可實現(xiàn)：

@Html.ActionLink("點擊導出名冊", "ExportStu2")

下面說一下導入：

首先說一些前臺吧，mvc上傳注意必須加new { enctype = "multipart/form-data" }：

<td>
2、@using(@Html.BeginForm("ImportStu", "ProSchool", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
 <text>選擇上傳文件：（工作表名為“Sheet1”，“電腦號”在A1單元格。）</text>
<input name="file" type="file" id="file" />
<input type="submit" name="Upload" value="批量導入第一批電腦派位名冊" />
 }
 </td>

后臺實現(xiàn)：只傳路徑得出DataTable：

/// <summary>
/// Excel導入
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public DataTable ImportExcelFile(string filePath)
{
    HSSFWorkbook hssfworkbook;
    #region//初始化信息
    try
    {
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
    hssfworkbook = new HSSFWorkbook(file);
}
    }
    catch (Exception e)
    {
throw e;
    }
    #endregion
    using (NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0))
    {
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);//第一行為標題行
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
    table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
    IRow row = sheet.GetRow(i);
    DataRow dataRow = table.NewRow();
    if (row != null)
    {
for (int j = row.FirstCellNum; j < cellCount; j++)
{
    if (row.GetCell(j) != null)
dataRow[j] = GetCellValue(row.GetCell(j));
}
    }
    table.Rows.Add(dataRow);
}
return table;
    }
    
}

補充一個類

/// <summary>
/// 根據(jù)Excel列類型獲取列的值
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
    if (cell == null)
return string.Empty;
    switch (cell.CellType)
    {
case CellType.BLANK:
    return string.Empty;
case CellType.BOOLEAN:
    return cell.BooleanCellValue.ToString();
case CellType.ERROR:
    return cell.ErrorCellValue.ToString();
case CellType.NUMERIC:
case CellType.Unknown:
default:
    return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
case CellType.STRING:
    return cell.StringCellValue;
case CellType.FORMULA:
    try
    {
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
    }
    catch
    {
return cell.NumericCellValue.ToString();
    }
    }
}

得到DataTable后，就想怎么操作就怎么操作了

到此這篇關于ASP.Net MVC利用NPOI導入導出Excel的示例代碼的文章就介紹到這了,更多相關ASP.Net MVC導入導出Excel內容請搜索以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持！