项目先安装好NPOI依赖包,依赖完成后开始代码实现。
其实整个过程也不麻烦,就是把文件写流再转成DataTable形式,实在数据导入到数据库.我这里使用的IFormFile类。前端使用的lay-ui自带的文件上传,代码:
- 准备工作
- .开始
layui.use(['upload', 'element', 'layer', 'form', 'laydate'], function () {
var $ = layui.jquery
, upload = layui.upload;
var layer = layui.layer;
//指定允许上传的文件类型
upload.render({
elem: '#test1'
, url: '/YuanQiShouSi/Import'
, accept: 'file' //普通文件
, done: function (res) {
layer.msg(res.message);
}
});
})
后面方法直接写在控制器的:
/// <summary>
/// excel数据导入到数据库(mapcoderela表)
/// </summary>
/// <param name="excelfile"></param>
/// <returns></returns>
[HttpPost]
public FuncResult Import()
{
FuncResult result = new FuncResult() { IsSuccess=true };
try
{
var excelfile = Request.Form.Files[0];
List<MapCodeRela> mapcoderelalist = new List<MapCodeRela>();
if (excelfile.Length > 0)
{
DataTable dt = new DataTable();
string strMsg;
string region = excelfile.FileName;//文件名即为区域名
//利用IFormFile里面的OpenReadStream()方法直接读取文件流
dt = ExcelHelper.ExcelToDatatable(excelfile.OpenReadStream(), Path.GetExtension(excelfile.FileName), out strMsg);
if (!string.IsNullOrEmpty(strMsg))
{
result.IsSuccess = false;
result.Message = strMsg;
}
if (dt.Rows.Count > 0)
{
//需要导入到数据库的数据
mapcoderelalist = JsonConvert.DeserializeObject<List<MapCodeRela>>(JsonConvert.SerializeObject(dt));
//查看当前区域是否有数据,若有区域,则先新原来数据写入mapcoderelahistory表,再清除掉写入新的数据
var handelResult = mapCodelRelaBll.WriteData(mapcoderelalist,region.Split('.')[0]);
if (!handelResult.IsSuccess )
{
return handelResult;
}
}
else
{
result.IsSuccess = false;
result.Message = "excel无数据!";
}
}
return result;
}
catch (Exception ex)
{
result.IsSuccess = false;
result.Message = ex.Message;
return result;
}
}
将文件以流形式读出来,再通过方法转换成DataTable:
/// <summary>
/// 将Excel单表转为Datatable
/// </summary>
/// <param name="stream"></param>
/// <param name="fileType"></param>
/// <param name="strMsg"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static DataTable ExcelToDatatable(Stream stream, string fileType, out string strMsg, string sheetName = null)
{
strMsg = "";
DataTable dt = new DataTable();
ISheet sheet = null;
IWorkbook workbook = null;
try
{
#region 判断excel版本
//2007以上版本excel
if (fileType == ".xlsx")
{
workbook = new XSSFWorkbook(stream);
}
//2007以下版本excel
else if (fileType == ".xls")
{
workbook = new HSSFWorkbook(stream);
}
else
{
throw new Exception("传入的不是Excel文件!");
}
#endregion
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum;
for (int i = firstRow.FirstCellNum; i < cellCount; i++)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue.Trim();
if (!string.IsNullOrEmpty(cellValue))
{
DataColumn dataColumn = new DataColumn(cellValue);
dt.Columns.Add(dataColumn);
}
}
}
DataRow dataRow = null;
//遍历行
for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
{
IRow row = sheet.GetRow(j);
dataRow = dt.NewRow();
if (row == null || row.FirstCellNum < 0)
{
continue;
}
//遍历列
for (int i = row.FirstCellNum; i < cellCount; i++)
{
ICell cellData = row.GetCell(i);
if (cellData != null && cellData.ToString() != "")
{
//判断是否为数字型,必须加这个判断不然下面的日期判断会异常
if (cellData.CellType == CellType.Numeric)
{
//判断是否日期类型
if (DateUtil.IsCellDateFormatted(cellData))
{
dataRow[i] = cellData.DateCellValue;
}
else
{
dataRow[i] = cellData.ToString().Trim();
}
}
else
{
dataRow[i] = cellData.ToString().Trim();
}
}
else
{
dataRow[i] = Guid.NewGuid().ToString();
}
}
dt.Rows.Add(dataRow);
}
}
else
{
throw new Exception("没有获取到Excel中的数据表!");
}
}
catch (Exception ex)
{
workbook = new HSSFWorkbook(stream);
strMsg = ex.Message;
}
return dt;
}
================================================================================================
捣鼓了下从局域网共享文件夹访问Excel导入,先和局域网文件地址建立连接,再从目录拿文件,其他的和上面的一样。建立连接:
public static bool connectState(string path, string userName, string passWord)
{
bool Flag = false;
Process proc = new Process();
try
{
proc.StartInfo.FileName = "cmd.exe";
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardInput = true;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.CreateNoWindow = true;
proc.Start();
string dosLine = "net use " + path + " " + passWord + " /user:" + userName;
proc.StandardInput.WriteLine(dosLine);
proc.StandardInput.WriteLine("exit");
while (!proc.HasExited)
{
proc.WaitForExit(1000);
}
string errormsg = proc.StandardError.ReadToEnd();
proc.StandardError.Close();
if (string.IsNullOrEmpty(errormsg))
{
Flag = true;
}
else
{
throw new Exception(errormsg);
}
}
catch (Exception ex)
{
//throw ex;
}
finally
{
proc.Close();
proc.Dispose();
}
return Flag;
}
访问方法:
//直接用IP也可以
status = connectState(@"\\192.168.1.189", @"Administrator\CqingShared", "001");