使用NPOI将Excel文件的数据导入数据库

来源：互联网发布：云计算大会编辑：程序博客网时间：2024/05/29 10:26

选择文件表单以窗口形式弹出:

<article class="page-container">
<form action="" method="POST" class="form form-horizontal" id="uploadForm" enctype="multipart/form-data">
<input type="file" name="excelFile" id="excelFile" /><br />
<label id="fileName"></label><br />
<label id="uploadInfo"></label>
<div style="font-size: 14px;">各列在表格中对应的位置</div>
<div class="text-c">
<span class="select-box inline" style="width: 20%;">
<select class="select checkSelectedValue" name="namePosition" style="width: 100%;"><option value="">姓名</option>
<option value="0">A列</option>
<option value="1">B列</option>
<option value="2">C列</option>
<option value="3">D列</option>
<option value="4">E列</option>
<option value="5">F列</option>
<option value="6">G列</option>
</select>
</span>
<span class="select-box inline" style="width: 20%;">
<select class="select checkSelectedValue" name="genderPosition" style="width: 100%;">
<option value="">性别</option>
<option value="0">A列</option>
<option value="1">B列</option>
<option value="2">C列</option>
<option value="3">D列</option>
<option value="4">E列</option>
<option value="5">F列</option>
<option value="6">G列</option>
</select>
</span>
<span class="select-box inline" style="width: 20%;">
<select class="select checkSelectedValue" name="numberPosition" style="width: 100%;">
<option value="">学号/工号</option>
<option value="0">A列</option>
<option value="1">B列</option>
<option value="2">C列</option>
<option value="3">D列</option>
<option value="4">E列</option>
<option value="5">F列</option>
<option value="6">G列</option>
</select>
</span>
<span class="select-box inline" style="width: 20%;">
<select class="select checkSelectedValue" name="phonePosition" style="width: 100%;">
<option value="">电话</option>
<option value="0">A列</option>
<option value="1">B列</option>
<option value="2">C列</option>
<option value="3">D列</option>
<option value="4">E列</option>
<option value="5">F列</option>
<option value="6">G列</option>
</select>
</span>
</div>
<div class="row cl" style="float: right; margin-top: 40px; margin-right: 20px;">
<div>
<input type="button" class="btn btn-primary radius" value="  提交  " onclick="checkUploadData()" />
</div>
</div>
</form>
</article>

</div>

//上传Excel文件表单验证
function checkUploadData() {
//获取上传文件内容
var file = document.getElementById('excelFile').files[0];
//判断控件中是否存在文件内容
if (file==null) {
layer.alert('请选择文件', { icon: 2, title: '提示', closeBtn: 0 });
return;
}
//获取文件名称
var fileName = file.name;
//获取文件类型名称
var fileTypeName = fileName.substr(fileName.lastIndexOf('.'), fileName.length);
//这里限定上传文件文件类型必须为.xlsx，如果文件类型不符，提示错误信息
if (fileTypeName==='.xlsx'||fileTypeName==='.xls') {
//计算文件大小
var fileSize = 0;
//如果文件大小大于1024字节X1024字节，则显示文件大小单位为MB，否则为KB
if (fileSize>1024*1024) {
fileSize = Math.round(file.size * 100 / (1024 * 1024)) / 100;
if (fileSize>10) {
layer.alert('请上传小于10MB的文件', { icon: 2, title: '提示', closeBtn: 0 });
return;
}
fileSize = fileSize.toString() + 'MB';
} else {
fileSize = (Math.round(file.size * 100 / 1024) / 100).toString() + 'KB';
}
//将文件名和文件大小显示在前端label文本中
document.getElementById('fileName').innerHTML = "<span style='color:blue'>文件名:" + fileName + ",大小:" + fileSize + "</span>";
//获取form数据
var formData = new FormData($('#uploadForm')[0]);
//调用后台action方法，将form数据传递给后台处理。contentType必须设置为false,否则chrome和firefox不兼容
if ($('.checkSelectedValue').val() == "" || $(".checkSelectedValue").val() == null) {
layer.alert('请选择Excel表格中各列数据对应的位置', { icon: 2, title: '提示', closeBtn: 0 });
return;
}

$.ajax({
url: '@Url.Action("PostExcelData","UserInfo")',
type: 'POST',
data: formData,
async: false,
cache: false,
contentType: false,
processData: false,
success: function(data) {
//上传成功后控件内容清空,并显示上传成功信息
document.getElementById('excelFile').value = null;
document.getElementById('uploadInfo').innerHTML = "<span style='color:red'>" + data + "</span>";
$('#tt').datagrid('load');
},
error: function(data) {
//上传失败时显示上传失败的信息
document.getElementById('uploadInfo').innerHTML = "<span style='color:red'>" + data + "</span>";
}
});
} else {
layer.alert('请上传Excel文件!', { icon: 2, title: '提示', closeBtn: 0 });
//将错误信息显示到前端label文本中
document.getElementById('fileName').innerHTML = "<span style='color:Red'>错误提示:上传文件应该是.xlsx后缀而不应该是" + fileTypeName + ",请重新选择文件</span>";
}
}

#region 上传Excel用户表

public ActionResult PostExcelData()
{
string info = "";
string namePosition = Request["namePosition"];
string genderPosition = Request["genderPosition"];
string numberPosition = Request["numberPosition"];
string phonePosition = Request["phonePosition"];
if (string.IsNullOrEmpty(namePosition) || string.IsNullOrEmpty(genderPosition) || string.IsNullOrEmpty(numberPosition) || string.IsNullOrEmpty(phonePosition))
{
return Content("请选择Excel表格中各列数据对应的位置!");
}
int nameIndex = int.Parse(namePosition);
int genderIndex = int.Parse(genderPosition);
int numberIndex = int.Parse(numberPosition);
int phoneIndex = int.Parse(phonePosition);
try
{
//获取客户端上传的文件的集合
HttpFileCollection files = System.Web.HttpContext.Current.Request.Files;
//判断文件是否存在
if (files.Count > 0)
{
//获取文件中的第一个文件(每次上传一个文件)
HttpPostedFile file = files[0];
//定义文件存放的目标路径
string targetDir = System.Web.HttpContext.Current.Server.MapPath("~/FileUpload/Product");
//创建目标路径
Directory.CreateDirectory(targetDir);
//文件将保存的完整路径
string path = Path.Combine(targetDir, Guid.NewGuid().ToString() + file.FileName);
//保存上传的文件到指定路径中
file.SaveAs(path);
info = "上传成功";

//提取数据
DataTable dt = ExcelToDataTable(path, true);
System.IO.File.Delete(path);
if (!UserInfoService.AddUsersByExcel(dt, nameIndex, genderIndex, numberIndex, phoneIndex))
{
info = "添加到数据库中出错";
}
}
else
{
info = "上传失败!";
}
}
catch
{
info = "上传失败!";
}
return Content(info);
}

//使用NPOI读取Excel文件信息,获取一个datatable对象
public DataTable ExcelToDataTable(string fileName, bool isFirstRowColumn)
{
IWorkbook workbook = null;
DataTable data = new DataTable();
try
{
ISheet sheet = null;
FileStream fs = null;
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) //2007
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) //2003
{
workbook = new HSSFWorkbook(fs);
}
sheet = workbook.GetSheetAt(0);

if (sheet != null)
{
int startRow = 0;
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号即总的列数

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)
{
return null;
}
}
#endregion

阅读全文

0 0