html代码
<div class="panel-body" >
<div class=" row ">
<div class="col-md-2 text-right">
文件
</div>
<div class="col-md-10">
<input type="file" id="csv_file" name="csv_file" style="width:100%;height:30px;" />
<br />
<button id="btnExport" type="button" class="btn btn-default btn-warning btn-sm" οnclick="ImportGoodsDtl('@Html.Raw(FunctionID)','@Html.Raw(InObjectIDValue)','@Html.Raw(Grid)')">
<span class="glyphicon glyphicon-export" aria-hidden="true"></span>导入
</button>
</div>
</div>
</div>
js代码
function ImportGoodsDtl(Function,InbillID,FunctionDtl) {
var files = $('#csv_file').prop('files');
var data = new FormData();
data.append('csv_file', files[0]);
$.ajax({
type: 'POST',
url: "/Sys_ImportBaseData/ImportExeclToData?function=" + Function + "&inBllID=" + InbillID+"",
data: data,
cache: false,
processData: false,
contentType: false,
dataType: "json",
success: function (data) {
....调用成功之后执行的事件
}
}
控制器里面方法
/// <summary>
/// 业务单据的明细导入
/// </summary>
/// <param name="function">模块名称</param>
/// <param name="inBllID">主键值</param>
/// <returns></returns>
public string ImportExeclToData(string function, string inBllID)
{
String result = String.Empty;
HttpPostedFileBase file = Request.Files["csv_file"];//csv_file
/ /ExportHelper是一个类,类里面写入的导入的方法;ImportExeclData是方法
DataTable dt = ExportHelper.ImportExeclData(file, function, inBllID);
//如果存在错误则提示
if (dt.Columns.Contains("Message")) {
JavaScriptSerializer jss = new JavaScriptSerializer();
jss.MaxJsonLength = Int32.MaxValue;
result = jss.Serialize(QConvert.ToString(dt.Rows[0]["Message"]));
return result;
}
return QConvert.DataTableToJson(dt);
}
ExportHelper类里面方法
#region 导入的方法
/// <summary>
///
/// </summary>
/// <param name="file">form表单提交的文件路径</param>
/// <param name="functionID">模块名称</param>
/// <param name="inbillIDValue">主键值</param>
/// <returns></returns>
public static DataTable ImportExeclData(HttpPostedFileBase file,string functionID,string inbillIDValue) {
//返回数据源
DataTable ExeclData = new DataTable("ExeclData");
//判断路径是否为空
if (file == null || file.ContentLength <= 0)
{
DataColumn dc = null;
dc = ExeclData.Columns.Add("Message", Type.GetType("System.String"));
DataRow newRow;
newRow = ExeclData.NewRow();
newRow["Message"] = "请选择要上传的Excel文件";
ExeclData.Rows.Add(newRow);
//return "请选择要上传的Excel文件";
return ExeclData;
}
//获取一个streamfile对象,该对象指向一个上传文件,准备读取改文件的内容
Stream streamfile = file.InputStream;
//获取到文件的名称
string FinName = Path.GetExtension(file.FileName);
if (FinName != ".xls" && FinName != ".xlsx")
{
DataColumn dc = null;
dc = ExeclData.Columns.Add("Message", Type.GetType("System.String"));
DataRow newRow;
newRow = ExeclData.NewRow();
newRow["Message"] = "只能上传Excel文档";
ExeclData.Rows.Add(newRow);
//return "只能上传Excel文档";
return ExeclData;
}
else
{
try
{
if (FinName == ".xls")
{
//创建一个webbook,对应一个Excel文件(用于xls文件导入类)
HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);
ExeclData = ImExportXlS(ExeclData, hssfworkbook);
}
else
{
XSSFWorkbook hssfworkbook = new XSSFWorkbook(streamfile);
ExeclData = ImExportXLSX(ExeclData, hssfworkbook);
}
//从execl返回出来的table在向数据库进添加数据
DataTable dataTable = new DataTable();
//请求数据库,插入数据,并返回最终的结果集
dataTable = InsertTableData(functionID, ExeclData, inbillIDValue);
//返回数据源
return dataTable;
}
catch (Exception ex)
{
DataColumn dc = null;
dc = ExeclData.Columns.Add("Message", Type.GetType("System.String"));
DataRow newRow;
newRow = ExeclData.NewRow();
newRow["Message"] = "导入失败 !" + ex.Message;
ExeclData.Rows.Add(newRow);
//return "导入失败 !" + ex.Message;
return ExeclData;
}
}
}
#endregion
///<summary>
/// 扩展名*.xlsx
/// </summary>
public static DataTable ImExportXLSX(DataTable dt, XSSFWorkbook hssfworkbook)
{
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
dt.Rows.RemoveAt(0);
return dt;
}
///<summary>
/// 扩展名*.xls
/// </summary>
public static DataTable ImExportXlS(DataTable dt, HSSFWorkbook hssfworkbook)
{
// 在webbook中添加一个sheet,对应Excel文件中的sheet,取出第一个工作表,索引是0
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
dt.Rows.RemoveAt(0);
return dt;
}