Asp.Net Core 使用NPOI 读取EXCEL

发布：2023/3/24 22:02:06作者：管理员来源：本站浏览次数：395

1.添加依赖 DotNetCore.NPOI

2.读取Excel类库

using System;

using System.Collections.Generic;

using System.Linq;

using System.Threading.Tasks;

using System.IO;

using System.Reflection;

using System.Collections;

using System.Data;

using NPOI.SS.UserModel;

namespace SVW_ReOnLine.BLL.Common

{

/// <summary>

/// Summary description for OfficeHelper

/// </summary>

public class OfficeHelper

{

/// <summary>

/// 将excel文件内容读取到DataTable数据表中

/// </summary>

/// <param name="fileName">文件完整路径名</param>

/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>

/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>

/// <returns>DataTable数据表</returns>

public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)

{

//定义要返回的datatable对象

DataTable data = new DataTable();

//excel工作表

ISheet sheet = null;

//数据开始行(排除标题行)

int startRow = 0;

try

{

if (!File.Exists(fileName))

{

return null;

}

//根据指定路径读取文件

FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);

//根据文件流创建excel数据结构

IWorkbook workbook = WorkbookFactory.Create(fs);

//IWorkbook workbook = new HSSFWorkbook(fs);

//如果有指定工作表名称

if (!string.IsNullOrEmpty(sheetName))

{

sheet = workbook.GetSheet(sheetName);

//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet

if (sheet == null)

{

sheet = workbook.GetSheetAt(0);

}

else

{

//如果没有指定的sheetName，则尝试获取第一个sheet

sheet = workbook.GetSheetAt(0);

}

if (sheet != null)

{

IRow firstRow = sheet.GetRow(0);

//一行最后一个cell的编号即总的列数

int cellCount = firstRow.LastCellNum;

//如果第一行是标题列名

if (isFirstRowColumn)

{

for (int i = firstRow.FirstCellNum; i < cellCount; ++i)

{

ICell cell = firstRow.GetCell(i);

if (cell != null)

{

string cellValue = cell.StringCellValue;

if (cellValue != null)

{

DataColumn column = new DataColumn(cellValue);

data.Columns.Add(column);

}

startRow = sheet.FirstRowNum + 1;

}

else

{

startRow = sheet.FirstRowNum;

}

//最后一列的标号

int rowCount = sheet.LastRowNum;

for (int i = startRow; i <= rowCount; ++i)

{

IRow row = sheet.GetRow(i);

if (row == null) continue; //没有数据的行默认是null

DataRow dataRow = data.NewRow();

for (int j = row.FirstCellNum; j < cellCount; ++j)

{

if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null

dataRow[j] = row.GetCell(j).ToString();

}

data.Rows.Add(dataRow);

}

return data;

}

catch (Exception ex)

{

throw ex;

}

/// <summary>

/// 将文件流读取到DataTable数据表中

/// </summary>

/// <param name="fileStream">文件流</param>

/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>

/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>

/// <returns>DataTable数据表</returns>

public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)

{

//定义要返回的datatable对象

DataTable data = new DataTable();

//excel工作表

ISheet sheet = null;

//数据开始行(排除标题行)

int startRow = 0;

try

{

//根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构

IWorkbook workbook = WorkbookFactory.Create(fileStream);

//如果有指定工作表名称

if (!string.IsNullOrEmpty(sheetName))

{

sheet = workbook.GetSheet(sheetName);

//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet

if (sheet == null)

{

sheet = workbook.GetSheetAt(0);

}

else

{

//如果没有指定的sheetName，则尝试获取第一个sheet

sheet = workbook.GetSheetAt(0);

}

if (sheet != null)

{

IRow firstRow = sheet.GetRow(0);

//一行最后一个cell的编号即总的列数

int cellCount = firstRow.LastCellNum;

//如果第一行是标题列名

if (isFirstRowColumn)

{

for (int i = firstRow.FirstCellNum; i < cellCount; ++i)

{

ICell cell = firstRow.GetCell(i);

if (cell != null)

{

string cellValue = cell.StringCellValue;

if (cellValue != null)

{

DataColumn column = new DataColumn(cellValue);

data.Columns.Add(column);

}

startRow = sheet.FirstRowNum + 1;

}

else

{

startRow = sheet.FirstRowNum;

}

//最后一列的标号

int rowCount = sheet.LastRowNum;

for (int i = startRow; i <= rowCount; ++i)

{

IRow row = sheet.GetRow(i);

if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null

DataRow dataRow = data.NewRow();

for (int j = row.FirstCellNum; j < cellCount; ++j)

{

//同理，没有数据的单元格都默认是null

ICell cell = row.GetCell(j);

if (cell != null)

{

if (cell.CellType == CellType.Numeric)

{

//判断是否日期类型

if (DateUtil.IsCellDateFormatted(cell))

{

dataRow[j] = row.GetCell(j).DateCellValue;

}

else

{

dataRow[j] = row.GetCell(j).ToString().Trim();

}

else

{

dataRow[j] = row.GetCell(j).ToString().Trim();

}

data.Rows.Add(dataRow);

}

return data;

}

catch (Exception ex)

{

throw ex;

}

3.控制器端Controller

public class UploadController : Controller

{

private ILog log = LogManager.GetLogger(Startup.repository.Name, typeof(UploadController));

private IHostingEnvironment hostingEnv;

public UploadController(IHostingEnvironment env)

{

this.hostingEnv = env;

}

[HttpPost]

public JsonResult UploadFilesAjax()

{

JArray lst = new JArray();

JObject jsonResult = new JObject();

int result = 0;

//long size = 0;

string fileNamePath = string.Empty;

string RequestPath = string.Empty;

try

{

//string domurl = @"http://dy.dt1.biz/upload/";

string domurl = @"http://localhost:57663/upload/";

var files = Request.Form.Files;

foreach (var file in files)

{

string fileName = DateTime.Now.ToString("MMddHHmmss") + file.FileName;

fileNamePath = Path.Combine("upload", fileName);

string serverUrl = Request.HttpContext.Connection.LocalIpAddress.ToStringExt() + ":" + Request.HttpContext.Connection.LocalPort.ToStringExt() + @"/upload/DeviceImage.jpg";

log.Info("客户端IP地址2：" + serverUrl);

RequestPath = domurl + fileName;

//size += file.Length;

//保存文件

//物理路径

string SavePath=Path.Combine(hostingEnv.WebRootPath, fileNamePath);

using (FileStream fs = new FileStream(SavePath, FileMode.CreateNew))

{

file.CopyTo(fs);

fs.Flush();

result = 1;

}

DataTable dt= OfficeHelper.ReadExcelToDataTable(SavePath);

for (int i = 0; i < dt.Rows.Count; i++)

{

JObject obj = new JObject();

for (int j = 0; j < dt.Columns.Count; j++)

{

obj.Add("col"+j.ToString(), dt.Rows[i][j].ToStringExt());

}

lst.Add(obj);

}

//string url = Request.HttpContext.Connection.RemoteIpAddress.ToStringExt()+":"+ Request.HttpContext.Connection.RemotePort.ToStringExt();

jsonResult.Add("Result", result);

jsonResult.Add("ImageUrl", RequestPath);

jsonResult.Add("Data", lst);

}

catch (Exception ex)

{

log.Error("上传文件错误信息列表错误：" + ex.ToStringExt());

jsonResult.Add("Result", result);

}

return Json(jsonResult);

}

4. index.html

<!DOCTYPE html>

<html>

<head>

<style>

.panel {

width: 375px;

margin: 15px auto;

}

#submit {

width: 100%;

}

img {

width: 100%;

}

.breadcrumb {

margin-bottom: 10px;

}

@media screen and (max-width: 480px) {

.panel {

width: 100%;

margin: 0;

border: 0;

box-shadow: none;

}

</style>

</head>

<body>

</div>

$("#uploadExcel").click(function (evt) {

var fileUpload = $("#files").get(0);

var files = fileUpload.files;

var data = new FormData();

for (var i = 0; i < files.length; i++) {

data.append(files[i].name, files[i]);

}

$.ajax({

type: "POST",

url: "api/Upload/UploadFilesAjax",

contentType: false,

processData: false,

data: data,

success: function (message) {

//var Msg = eval('(' + message + ')');

alert("【Result】:" + message.Result + ";【ImageUrl】:" + message.ImageUrl);

error: function () {

alert("There was error uploading files!");

}

});

</script>

</body>

</html>

　JAVA使用HSSFWorkbook导出、操作excel

.NET Core使用NPOI将Excel中的数据批量导入到MySQL　

C#，.net,winform导入Excel功能以及下载Excel文件到本地，并使用SqlBulkCopy把DataTable类型的数据写入到sqlserver数据库中 .NET Core控制台应用读取appsettings.json配置文件 asp.net mvc和.net core动态路由cms经常用到 asp.net core 登录或注册自动跳转到内部地址ReturnUrl asp.net core http post请求读取body中数据 asp.Net Core中三种依赖注入区别 ASP.NET Core 中的分部视图和视图组件 C# asp.net core 将字符串数组或列表转换成特定字符分隔的字符串 asp.net core导入导出生成excel文件 asp.net core EF core 关键多表关联，加载相关数据

柏港建站平台

柏港 NPOI

Asp.Net Core 使用NPOI 读取EXCEL

柏港为您推荐了一些相关资讯