在.net中使用NPOI导入导出

NPOI导入

前台:

@using (Html.BeginForm("ImportExcel", "Admin", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<input type="submit" value="NPOI" />
}

后台：

public ActionResult ImportExcel()
{

string FileName;
string savePath;
string fileEx;
HttpPostedFileBase file = Request.Files["files"];
string filename = Path.GetFileName(file.FileName);
fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
string path = AppDomain.CurrentDomain.BaseDirectory + "/Uploads/OutExcel/";
savePath = Path.Combine(path, FileName);
file.SaveAs(savePath);
DataTable dt = GetExcelDataTable(savePath);
return View();
}

//获取Table
public static DataTable GetExcelDataTable(string filePath)

{
HSSFWorkbook Workbook;

DataTable table = new DataTable();

try

{

using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))

{

//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式

string fileExt = Path.GetExtension(filePath).ToLower();

if (fileExt == ".xls")

{

Workbook = new HSSFWorkbook(fileStream);
}
else if (fileExt == ".xlsx")
{
Workbook = new HSSFWorkbook(fileStream);

}

else

{

Workbook = null;

}

}

}

catch (Exception ex)

{

throw ex;

}


//定位在第一个sheet
HSSFSheet sheet = (HSSFSheet)Workbook.GetSheetAt(0);

//第一行为标题行

HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);

int cellCount = headerRow.LastCellNum;

int rowCount = sheet.LastRowNum;


//循环添加标题列

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++)

{
HSSFRow row = (HSSFRow)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((HSSFCell)row.GetCell(j));

}

}

}

table.Rows.Add(dataRow);

}

return table;

}

private static string GetCellValue(HSSFCell 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();

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();

}

}

}

NPOI导出

public void OutExcel()
{
//1.创建工作簿对象
HSSFWorkbook work = new HSSFWorkbook();
//2.创建工作表
work.CreateSheet("sheet1");
//获取名称为Sheet1的工作表
HSSFSheet sheet = (HSSFSheet)work.GetSheet("sheet1");
//3.创建行row
sheet.CreateRow(0);
//获取Sheet1工作表的首行
HSSFRow Row = (HSSFRow)sheet.GetRow(0);
Row.CreateCell(0).SetCellValue("图书ID");
Row.CreateCell(1).SetCellValue("图书类别名");
Row.CreateCell(2).SetCellValue("图书备注");
//获取数据
List<BookType> list = bktBll.GetBookType();
for (int i = 0; i < list.Count(); i++)
{
//sheet.CreateRow(i);
HSSFRow rows = (HSSFRow)sheet.CreateRow(i + 1);
rows.CreateCell(0).SetCellValue(list[i].BTID);
rows.CreateCell(1).SetCellValue(list[i].BTName);
rows.CreateCell(2).SetCellValue(list[i].Remark);
}

//4.创建流对象并设置存储Excel文件的路径
//MemoryStream ms = new MemoryStream();
//work.Write(ms);

//Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("WS" + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));

//Response.BinaryWrite(ms.ToArray());

//Response.Flush();

//Response.End();
//work = null;

//ms.Close();

//ms.Dispose();
//保存到硬盘
string path = AppDomain.CurrentDomain.BaseDirectory + "/Uploads/OutExcel/" + "信息.xls";
using (FileStream stream = new FileStream(path, FileMode.Create))
{
work.Write(stream);
}
//发送到浏览器
return File(new FileStream(path, FileMode.Open), "application/ms-excel", "信息.xls");
}