NPOI is a .NET library for working with Office documents, it can work with Microsoft Excel (.xls and .xlsx) files. NPOI provides the ability to create, read and edit Excel files without installing Microsoft Office. NPOI is based on the .NET version of the Apache POI project.
1. Define the import button
<button id="chuan" type="button" class="btn btn-1g btn-info" onclick="$('#ExcelDaoru').click(); ">
Import seed liquid excel
</button>
<br /><br /><hr /><br /><br />
<input id="ExcelDaoru" type="file" style="display:none;" accept=".xls,.xlsx" />
2. Define the button event
//Import seed liquid information Excel
$('#ExcelDaoru').fileupload({
type: 'POST',
url: '@Url.Action("ExcelDaoru")',
dataType: 'json',
autoUpload: true,
minFileSize: 1,
maxFileSize: 1024 * 1024 * 50,
done: function (e, data) {
if (data. result. isSucced === true) {
alert("Imported successfully!");
//console.log(data.result);
//$('#grid-table').bootstrapTable('refresh');
for (var i = 0; i < data. result. zhongZiYeList. length; i ++ ) {
var $temptr = $("#table-ZhongZiYeMuBan tr:first").clone();
$("#tbody-ZhongZiYe").append($temptr);
$temptr.find(".Shangguanjunzhong").val(data.result.zhongZiYeList[i].Shangguanjunzhong);
$temptr.find(".Shangguanshijian").val(jsonDateFormat(data.result.zhongZiYeList[i].Shangguanshijian)).datetimepicker({
format: 'YYYY-MM-DD'
});
$temptr.find(".Shangguanjunzhongpihao").val(data.result.zhongZiYeList[i].Shangguanjunzhongpihao);
$temptr.find(".Shangguanzhongziyetiji").val(data.result.zhongZiYeList[i].Shangguanzhongziyetiji);
$temptr.find(".Danduokelong").val(data.result.zhongZiYeList[i].Danduokelong);
//$temptr.find(".Yijipingbanhuaxianriqi").val(data.result.zhongZiYeList[i].Yijipingbanhuaxianriqi);
$temptr.find(".Yijipingbanhuaxianriqi").val(jsonDateFormat(data.result.zhongZiYeList[i].Yijipingbanhuaxianriqi)).datetimepicker({
format: 'YYYY-MM-DD'
});
$temptr.find(".Yijipingbanpihao").val(data.result.zhongZiYeList[i].Yijipingbanpihao);
$temptr.find(".Yijipingbanpeiyangwendu").val(data.result.zhongZiYeList[i].Yijipingbanpeiyangwendu);
//$temptr.find(".Yijipingbanpeiyangshijian").val(data.result.zhongZiYeList[i].Yijipingbanpeiyangshijian);
$temptr.find(".Yijipingbanpeiyangshijian").val(data.result.zhongZiYeList[i].Yijipingbanpeiyangshijian);
$temptr.find(".Yijijunluoxingtai").val(data.result.zhongZiYeList[i].Yijijunluoxingtai);
$temptr.find(".Erjipingbanhuaxianriqi").val(jsonDateFormat(data.result.zhongZiYeList[i].Erjipingbanhuaxianriqi)).datetimepicker({
format: 'YYYY-MM-DD'
});
$temptr.find(".Erjipingbanpihao").val(data.result.zhongZiYeList[i].Erjipingbanpihao);
$temptr.find(".Erjipingbanpeiyangwendu").val(data.result.zhongZiYeList[i].Erjipingbanpeiyangwendu);
$temptr.find(".Erjipingbanpeiyangshijian").val(data.result.zhongZiYeList[i].Erjipingbanpeiyangshijian);
$temptr.find(".Erjijunluoxingtai").val(data.result.zhongZiYeList[i].Erjijunluoxingtai);
$temptr.find(".Yijiyaopingpihao").val(data.result.zhongZiYeList[i].Yijiyaopingpihao);
$temptr.find(".Yijiyaopingjiezhongriqi").val(jsonDateFormat(data.result.zhongZiYeList[i].Yijiyaopingjiezhongriqi)).datetimepicker({
format: 'YYYY-MM-DD'
});
$temptr.find(".Yijiyaopingtixi").val(data.result.zhongZiYeList[i].Yijiyaopingtixi);
$temptr.find(".Yijiyaopingjiezhongliang").val(data.result.zhongZiYeList[i].Yijiyaopingjiezhongliang);
$temptr.find(".Yijiyaopingpeiyangtiaojian").val(data.result.zhongZiYeList[i].Yijiyaopingpeiyangtiaojian);
$temptr.find(".Yijiyaopingpeiyangshijian").val(data.result.zhongZiYeList[i].Yijiyaopingpeiyangshijian);
$temptr.find(".YijiyaopingOD").val(data.result.zhongZiYeList[i].YijiyaopingOD);
$temptr.find(".YijiyaopingPH").val(data.result.zhongZiYeList[i].YijiyaopingPH);
$temptr.find(".Yijiyaopingjingjian").val(data.result.zhongZiYeList[i].Yijiyaopingjingjian);
$temptr.find(".Erjiyaopingpihao").val(data.result.zhongZiYeList[i].Erjiyaopingpihao);
$temptr.find(".Erjiyaopingjiezhongriqi").val(jsonDateFormat(data.result.zhongZiYeList[i].Erjiyaopingjiezhongriqi)).datetimepicker({
format: 'YYYY-MM-DD'
});
$temptr.find(".Erjiyaopingtixi").val(data.result.zhongZiYeList[i].Erjiyaopingtixi);
$temptr.find(".Erjiyaopingjiezhongliang").val(data.result.zhongZiYeList[i].Erjiyaopingjiezhongliang);
$temptr.find(".Erjiyaopingpeiyangtiaojian").val(data.result.zhongZiYeList[i].Erjiyaopingpeiyangtiaojian);
$temptr.find(".Erjiyaopingpeiyangshijian").val(data.result.zhongZiYeList[i].Erjiyaopingpeiyangshijian);
$temptr.find(".ErjiyaopingOD").val(data.result.zhongZiYeList[i].ErjiyaopingOD);
$temptr.find(".ErjiyaopingPH").val(data.result.zhongZiYeList[i].ErjiyaopingPH);
$temptr.find(".Erjiyaopingjingjian").val(data.result.zhongZiYeList[i].Erjiyaopingjingjian);
$temptr.find(".Chaojingtai").val(data.result.zhongZiYeList[i].Chaojingtai);
$temptr.find(".Peiyangxiang").val(data.result.zhongZiYeList[i].Peiyangxiang);
$temptr.find(".Yaochuang").val(data.result.zhongZiYeList[i].Yaochuang);
$temptr.find(".Ziguangduji").val(data.result.zhongZiYeList[i].Ziguangduji);
$temptr.find(".Shangguanshiyanguocheng").val(data.result.zhongZiYeList[i].Shangguanshiyanguocheng);
$temptr.find(".Shangguanzhongziyezhibeisop").val(data.result.zhongZiYeList[i].Shangguanzhongziyezhibeisop);
$temptr.find(".Fangguanyangpinsijuntijianceshijian").val(jsonDateFormat(data.result.zhongZiYeList[i].Fangguanyangpinsijuntijianceshijian)).datetimepicker({
format: 'YYYY-MM-DD'
});
$temptr.find(".Fangguanyangpinsijuntijiancejieguo").val(data.result.zhongZiYeList[i].Fangguanyangpinsijuntijiancejieguo);
$temptr.find(".Beizhu").val(data.result.zhongZiYeList[i].Beizhu);
SetXuhao($temptr. parent());
}
}
else {
alert(data. result. errorMessage)
}
},
fail: function (e, data) {
var fileName = data.files[0].name;
console. log(e);
alert(fileName + "Import failed!");
},
always: function (e, data) {
},
});
function jsonDateFormat(jsonDate) {
try {
var date = new Date(parseInt(jsonDate.replace("/Date(", "").replace(")/", ""), 10));
var month = date.getMonth() + 1 < 10 ? "0" + (date.getMonth() + 1) : date.getMonth() + 1;
var day = date.getDate() < 10 ? "0" + date.getDate() : date.getDate();
var hours = date. getHours();
var minutes = date. getMinutes();
var seconds = date. getSeconds();
var milliseconds = date. getMilliseconds();
return date.getFullYear() + "-" + month + "-" + day + " " + hours + ":" + minutes + ":" + seconds + ".\ " + milliseconds;
} catch (ex) {
return "";
}
}
3. ExcelDaoru method
//excel import
[HttpPost]
public JsonResult ExcelDaoru()
{
HttpPostedFileBase file = Request.Files?[0];
string fileName = file?.FileName;
string errorMessage = "";
int zuiDaShangChuanFuJian = 1024 * 1024 * 50; // 100MB
try
{
if (file == null || file. ContentLength <= 0)
throw new Exception("Please upload the file!");
if (file.ContentLength > zuiDaShangChuanFuJian)
{
throw new Exception("The file size cannot exceed " + zuiDaShangChuanFuJian / (1024 * 1024) + "MB");
}
var extension = Path.GetExtension(file.FileName);//Get file extension
if (string.IsNullOrWhiteSpace(extension) || (!new List<string>() { ".xls", ".xlsx" }.Contains(extension.ToLower())))
throw new Exception("Uploading files with this extension is not supported!");
string baoCunMing = Guid.NewGuid().ToString("N") + extension;//Generate a random file name and save it locally and save the file
string xiangDuiBaoCunDir = "~/Upload/ExcelDaoru/" + DateTime.Now.ToString("yyyyMM");
string jueDuiBaoCunDir = Server.MapPath(xiangDuiBaoCunDir);
if (!Directory.Exists(jueDuiBaoCunDir))
Directory.CreateDirectory(jueDuiBaoCunDir);
string jueDuiBaoCunDiZhi = jueDuiBaoCunDir + "/" + baoCunMing;
file.SaveAs(jueDuiBaoCunDiZhi);
DataTable dt = ExcelToDataTable("Sheet1", true, jueDuiBaoCunDiZhi);
List<tbfjsjk_zhongziyepeiyang> zhongZiYeList = new List<tbfjsjk_zhongziyepeiyang>();
//List<tbbg_fujian> fujianList = new List<tbbg_fujian>();
if (dt != null & amp; & amp; dt.Rows.Count > 0)
{
//field
List<string> ColumnsNameList = new List<string>();
foreach (DataColumn dc in dt.Columns)
{
ColumnsNameList.Add(dc.ColumnName);
}
//value
for (int i = 0; i < dt. Rows. Count; i ++ )
{
tbfjsjk_zhongziyepeiyang zhongziyepeiyang = new tbfjsjk_zhongziyepeiyang();
Type t = zhongziyepeiyang. GetType();
PropertyInfo[] PropertyList = t. GetProperties();
foreach (PropertyInfo item in PropertyList)
{
string name = item.Name;
string DisplayName = item.GetCustomAttribute<DisplayNameAttribute>()?.DisplayName;
if (ColumnsNameList. Contains(DisplayName))
{
item.SetValue(zhongziyepeiyang, CheckType(dt.Rows[i][DisplayName], item.PropertyType), null);
}
}
zhongZiYeList.Add(zhongziyepeiyang);
}
}
return Json(new { isSucced = true, errorMessage = "", zhongZiYeList = zhongZiYeList });
}
catch (Exception ex)
{
errorMessage = ex.Message;
}
return Json(new { isSucced = false, errorMessage = string. Format("{0} fail to import! {1}", fileName, errorMessage) });
}
4. ExcelToDataTable method
private DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, string fileName)
{
if (string.IsNullOrEmpty(sheetName))
{
throw new ArgumentNullException(sheetName);
}
if (string. IsNullOrEmpty(fileName))
{
throw new ArgumentNullException(fileName);
}
var data = new DataTable();
IWorkbook workbook = null;
FileStream fs = null;
try
{
fs = new FileStream(fileName, FileMode. Open, FileAccess. Read);
if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
{
workbook = new HSSFWorkbook(fs);
}
ISheet sheet = null;
if (workbook != null)
{
//If the sheet corresponding to the specified sheetName is not found, try to get the first sheet
sheet = workbook. GetSheet(sheetName) workbook. GetSheetAt(0);
}
if (sheet == null) return data;
var firstRow = sheet. GetRow(0);
//The number of the last cell in a row is the total number of columns
int cellCount = firstRow. LastCellNum;
int startRow;
if (isFirstRowColumn)
{
for (int i = firstRow. FirstCellNum; i < cellCount; + + i)
{
var cell = firstRow. GetCell(i);
var cellValue = cell. StringCellValue;
if (cellValue == null) continue;
var column = new DataColumn(cellValue);
data.Columns.Add(column);
}
startRow = sheet. FirstRowNum + 1;
}
else
{
startRow = sheet. FirstRowNum;
}
// label of the last column
var rowCount = sheet. LastRowNum;
for (var i = startRow; i <= rowCount; + + i)
{
var row = sheet. GetRow(i);
// Rows with no data default to null
if (row == null) continue;
var dataRow = data. NewRow();
for (int j = row.FirstCellNum; j < cellCount; + + j)
{
var cell = row. GetCell(j);
// Similarly, cells without data are null by default
if (cell != null)
{
// Check if the value of the cell is empty
string cellValue = cell. ToString();
if (!string.IsNullOrEmpty(cellValue))
{
if (cell.CellType == CellType.Numeric & amp; & amp; DateUtil.IsCellDateFormatted(cell))
{
// If the cell is in date format, convert it to the date format you need
//dataRow[j] = string.Format("{0:yyyy/MM/dd HH:mm:ss}", DateTime.FromOADate(cell.NumericCellValue));
dataRow[j] = string.Format("{0:yyyy-MM-dd}", DateTime.FromOADate(row.GetCell(j).NumericCellValue));
}
else
{
dataRow[j] = cell. ToString();
}
}
else
{
dataRow[j] = DBNull.Value;//null value assignment
}
}
}
data.Rows.Add(dataRow);
}
return data;
}
catch (IOException ioex)
{
throw new IOException(ioex.Message);
}
catch (Exception ex)
{
throw new Exception(nameof(ExcelDaoru), ex);
}
finally
{
if (fs != null)
{
fs.Close();
}
}
}
5. CheckType method
/// <summary>
/// Judgment and conversion of nullable types (*otherwise an error will be reported)
/// </summary>
/// <param name="value">DataReader field value</param>
/// <param name="conversionType">The type of the field</param>
/// <returns></returns>
private static object CheckType(object value, Type conversionType)
{
if (conversionType.IsGenericType & amp; & amp; conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
{
if (value == null)
return null;
NullableConverter nullableConverter = new NullableConverter(conversionType);
conversionType = nullableConverter. UnderlyingType;
}
return Convert. ChangeType(value, conversionType);
}
Note: conversion of date format, and conversion of null values. Otherwise an error will be reported
/Date(1664553600000)/
Solution:
The type of DateTime in NPOI is Numeric. If it is a Numeric type, then use the DateUtil.IsCellDateFormatted(cell) method that comes with .Net to determine whether it is a DateTime type. If both are true, take the DateCellValue attribute of the cell
if ( row.GetCell(j).CellType == CellType.Numeric & amp; & amp; DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = row.GetCell(j).DateCellValue;
}