需要添加DBHelper和Excel以及引用其他的导入导出文件
/// <summary>
/// 导出方法
/// </summary>
/// <param name="tablename"></param>
/// <returns></returns>
public FileResult DaoChu1(string tablename)
{
return DaoChu(tablename, "biaoName", "biaoSex", "biaoAge");
}
/// <summary>
/// 导出功能
/// </summary>
/// <returns></returns>
public FileResult DaoChu(string tablename, string cloum1, string cloum2, string cloum3)
{
string SqlStr = "SELECT biaoName,biaoSex,biaoAge from " + tablename + " WHERE 1=1";//想要获取那些数据就查询那些数据
var dt = DBHelper.GetDataTable(SqlStr);
dt.Columns[cloum1].ColumnName = "名字";
dt.Columns[cloum2].ColumnName = "性别";
dt.Columns[cloum3].ColumnName = "年龄";
var name = DateTime.Now.ToString("yyyyMMddHHmmssfff");
var path = Excel.SaveExcel(dt, name, "头像");
return File(path, "text/plain", name + ".xlsx");
}
/// <summary>
/// 导入方法
/// </summary>
/// <returns></returns>
public void UploadFile(string table)
{
UpLoadExcel(table);
}
public void UpLoadExcel(string tablename)//必须与数据库中的字段相同 上传
{
for (int i = 0; i < Request.Files.Count; i++)
{
var item = Request.Files[i];
var FileName = item.FileName; //文件名
var SubName = FileName.Substring(FileName.LastIndexOf(".")); //扩展名
IWorkbook wb = null;
if (SubName.Equals(".xlsx"))
{
wb = new XSSFWorkbook(item.InputStream);
}
else if (SubName.Equals(".xls"))
{
wb = new HSSFWorkbook(item.InputStream);
}
ISheet sh = wb.GetSheetAt(0); //获取表
IRow rows = sh.GetRow(1); //获取行
for (int k = 1; k <= sh.LastRowNum; k++) //循环行
{
rows = sh.GetRow(k);
string sql = string.Format("insert into " + tablename + " values ( ");
//string sql1 = string.Format("insert into Preson values ('{0}','{1}','{2}')");
for (int j = 0; j < rows.LastCellNum; j++) //循环列
{
try
{
string value = rows.GetCell(j).ToString();
sql += string.Format("'" + value.ToString() + "',");
}
catch
{
}
}
sql = sql.Substring(0, sql.Length - 1) + ")";
DBHelper.ExecuteNonQuery(sql);
}
}
Response.Write("<script>alert('导入成功!');location.href='/Home/Show'</script>");
}
<div>//Csml
<form action="/Home/UploadFile?table=biao" method="post" enctype="multipart/form-data">
<table>
<tr>
<td><input type="file" id="Excel" name="Excel" class="btn-primary" /></td>
<td> </td>
<td><input type="submit" class="btn-primary" value="文件导入" /></td>
</tr>
</table>
</form>
<a href="/Home/DaoChu1?tablename=biao">导出</a>
</div>
C#可实现简单导入导出原转载请说明
最新推荐文章于 2020-08-08 14:55:59 发布