前台页面:
@using (Html.BeginForm("DataInToExcel", "Data", FormMethod.Post, new { id = "GForm" }))
{
<div class="Main_Put">
<div class="mainHead">当前位置:数据导出</div>
<div style="padding-left:15px; margin-top:5px;">
<table>
<tr>
<td>物料编号:</td>
<td>@Html.TextBox("MaterialID")</td>
<td>大类名称:</td>
<td>@Html.DropDownList("FirstType",null, new { Style = "width:155px;" })</td>
</tr>
<tr>
<td>中类名称:</td>
<td>@Html.DropDownList("SecondType", null, new { Style = "width:155px;" })</td>
<td>小类名称:</td>
<td>@Html.DropDownList("ThirdType", null, new { Style = "width:155px;" })</td>
<td>物料长描述:</td>
<td>@Html.DropDownList("MaterialDesc", null, new { Style = "width:155px;" })</td>
<td><input type="button" value=" 查 询 " οnclick="Search()" /></td>
</tr>
</table>
</div>
<div style=" margin-top:5px; padding-left:20px;">
<div id="bor" style="width:100%;height:50%; padding-top:5px;">
<table id="list" class="scroll" style="width:100%;"></table>
<div id="pager" class="scroll" style="text-align:center;width:100%;"> </div>
</div>
</div>
<div style=" margin-top:5px; padding-left:20px; width:1050px;">
<p>
<input type="submit" value=" 导 出 " style="float:right; width:80px; height:30px;" />
</p>
</div>
</div>
}
控制器:
#region//物料数据的【导出】操作
public FileResult DataInToExcel()
{
string strWhere = "";
if (Request["MaterialID"] != null)
{
if (Request["MaterialID"].ToString() != "")
{
if (strWhere != "") strWhere += " and ";
strWhere += "MaterialID like '%" + Request["MaterialID"].ToString() + "%'";
}
}
if (Request["FirstType"] != null)
{
if (Request["FirstType"].ToString() != "")
{
if (strWhere != "") strWhere += " and ";
strWhere += "FirsTypeText =( " +
" select Text " +
" from tk_ConfigFirstType " +
" Where ID = '" + Request["FirstType"].ToString() + "' " +
" )";
}
}
if (Request["SecondType"] != null)
{
if (Request["SecondType"].ToString() != "")
{
if (strWhere != "") strWhere += " and ";
strWhere += "SecondTypeText=( " +
" select Text" +
" from tk_ConfigSecondType" +
" where ID = '" + Request["SecondType"].ToString() + "' " +
" )";
}
}
if (Request["ThirdType"] != null)
{
if (Request["ThirdType"].ToString() != "")
{
if (strWhere != "") strWhere += " and ";
strWhere += "ThirdTypeText=(" +
" select Text" +
" from tk_ConfigThirdType" +
" where ID = '" + Request["ThirdType"].ToString() + "' " +
" )";
}
}
if (Request["MaterialDesc"] != null)
{
if (Request["MaterialDesc"].ToString() != "")
{
if (strWhere != "") strWhere += " and ";
strWhere += "MaterialID like '%" + Request["MaterialDesc"].ToString() + "%'";
}
}
string strErr = "";
DataTable data = Data.GetDataByWhere(strWhere, "0", ref strErr);
if (data != null)
{
string strCols = "物料编码-3000,大类名称-3000,中类名称-5000,物料名称-5000,";
strCols += "小类名称-5000,物料小类-5000,规格-3000,型号-3000,质量标准-3000,物料长描述-9000";
System.IO.MemoryStream stream = ExcelHelper.ExportDataTableToExcel(data, "Material物料数据表", strCols.Split(','));
stream.Seek(0, System.IO.SeekOrigin.Begin);
return File(stream, "application/vnd.ms-excel", "MaterialData.xls");
}
else
return null;
}
#endregion
BLL:
public static DataTable GetDataByWhere(string a_strWhere, string a_strType, ref string a_strErr)
{
DataTable dtInfo = DataPro.GetDataByWhere(a_strWhere, a_strType, ref a_strErr);
if (dtInfo == null) return null;
if (dtInfo.Rows.Count == 0) return null;
return dtInfo;
}
DAL:
public static DataTable GetDataByWhere(string a_strWhere, string a_strType, ref string a_strErr)
{
string strSql = "";
try
{
if (a_strWhere != "")
{
strSql = " Select MaterialID,FirsTypeText,SecondTypeText,MaterialText,ThirdTypeText,ThirdTypeID,MaterialSpec,MaterialType,QualStandard,MaterialDesc " +
" From tk_MaterialBasic" +
" Where " + a_strWhere;
}
else
{
strSql = " Select MaterialID,FirsTypeText,SecondTypeText,MaterialText,ThirdTypeText,ThirdTypeID,MaterialSpec,MaterialType,QualStandard,MaterialDesc " +
" From tk_MaterialBasic";
}
DataTable dtInfo = SQLBase.FillTable(strSql);
return dtInfo;
}
catch (Exception ex)
{
a_strErr = ex.Message;
return null;
}
}