webApi 导入Excel

/// <summary>
/// 导入
/// </summary>
/// <param name="organizationId"></param>
/// <returns></returns>
[HttpPost, Route("ImportExcel")]
public V_ResultModel ImportExcel(int organizationId)
{
var result = new V_ResultModel();
try
{
HttpPostedFile file = HttpContext.Current.Request.Files[0];
string FileName;
string savePath;
if (file == null || file.ContentLength <= 0)
{
result.success = false;
result.data = "文件为空!";
return result;
}
else
{
string filename = Path.GetFileName(file.FileName);
int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
string FileType = ".xls,.xlsx";//定义上传文件的类型字符串

FileName = DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
if (!FileType.Contains(fileEx))
{
result.success = false;
result.data = "文件类型不对,只能导入xls和xlsx格式的文件!";
return result;
}
if (filesize >= Maxsize)
{
result.success = false;
result.data = "上传文件超过4M,不能上传!";
return result;
}
string path = AppDomain.CurrentDomain.BaseDirectory + "uploads/excel/";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
savePath = Path.Combine(path, FileName);
file.SaveAs(savePath);

string strConn;
if (fileEx == ".xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
}
else
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
}

OleDbConnection conn = new OleDbConnection(strConn);
try
{
conn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
conn.Close();
DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();

var sqlList = new List<string>();
for (int i = 0; i < table.Rows.Count; i++)
{
try
{
var equipment = new Equipment()
{
Num = table.Rows[i][0].ToString(),
Name = "智能床传感器",
Type = 0,
BindStatus = 0,
OrganizationId = organizationId
};

sqlList.Add(string.Format("insert into Equipment (Num, Name,Type,BindStatus,OrganizationId) values ('{0}','{1}',{2},{3},{4})", equipment.Num, equipment.Name, equipment.Type, equipment.BindStatus, equipment.OrganizationId));
if (sqlList.Count == 10)
{
var sqlCommand = string.Join(";", sqlList);
_equipmentService.BatchSave(sqlCommand);
sqlList.Clear();
}
}
catch
{

}
}
if (sqlList.Any())
{
_equipmentService.BatchSave(string.Join(";", sqlList));
}
result.success = true;
result.data = "导入成功!";
return result;
}
catch
{
conn.Close();
result.success = false;
result.data = "导入失败!";
return result;
}
}
}
catch (Exception ex)
{
LoggingFactory.GetLogger().Log(ex.ToString());
result.success = false;
result.data = "导入失败!";
return result;
}
}

上一篇:MySQL profiling的使用


下一篇:62-django-无名有名分组反向解析、路由分发、名称空间、伪静态、pycharm虚拟环境、django版本区别、视图层之三板斧、JsonResponse、form表单上传文件、FBV与CBV