protected void btnImport_Click(object sender, EventArgs e)
{
DataTable dt = GetExportData();
string strSaveUrl = "..\\Excel";
if (!Directory.Exists(Server.MapPath(strSaveUrl)))
{
Directory.CreateDirectory(Server.MapPath(strSaveUrl));
}
//文件名
string strFileName = "goods_info_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
string strAllFileName = Server.MapPath(strSaveUrl + "\\" + strFileName);
FileStream objFs = new FileStream(strAllFileName, FileMode.Create, FileAccess.Write);
StreamWriter objSw = new StreamWriter(objFs, System.Text.Encoding.GetEncoding("gb2312"));
objSw.WriteLine(@" " + "\t" + "商品编号" + "\t" + "商品新编码" + "\t" + "商品名称(中)" + "\t" + "商品名称(日)" + "\t" + "销售单价" + "\t" + "成本单价" + "\t" + "库存数量" + "\t" + "成本金额" + "\t" + "ITEM编号" + "\t" + "ITEM名称" + "\t" + "品牌" + "\t" + "媒体" + "\t" + "季节区分" + "\t" + "大类" + "\t" + "小类" + "\t" + "颜色" + "\t" + "供应商编号" + "\t" + "供应商名称" + "\t" + "设计师" + "\t" + "洗涤说明" + "\t" + "成分" + "\t" + "产地" + "\t" + "执行标准" + "\t" + "上市年限" + "\t" + "安全技术类别" + "\t" + "成分");//
//objSw.WriteLine(@" " );
int num = 1;
string strCATA = "";//季节
string ItemName = "";//ItemName
string MakerName = "";//供应商名称
//库存数量
string goodsCount = "";
//成本金额
string strCostAmount = "";
string strGD_SALE_PRC = "";
string strGD_COST_PRC = "";
Regex regex = new Regex("\\s+ ");
foreach (DataRow dr in dt.Rows)
{
#region 判断
//季节
if (dr["GD_USE_PERIOD"].ToString() != "000000000000" && !string.IsNullOrEmpty(dr["GD_USE_PERIOD"].ToString()))
{
StringBuilder sb = new StringBuilder();
for (int i = 1; i < dr["GD_USE_PERIOD"].ToString().Length; i++)
{
if (dr["GD_USE_PERIOD"].ToString()[i].ToString() == "1")
{
sb.Append(i + 1 + ",");
}
}
sb.Remove(sb.Length - 1, 1);
strCATA = sb.ToString();
}
else
{
strCATA = "";
}
//库存数量
goodsCount = ItemBrules.GetStockNum(dr["GD_CD"].ToString()).Rows[0][0].ToString();
//成本金额
if (!string.IsNullOrEmpty(goodsCount))
{
if (dr["GD_COST_PRC"].ToString() != "0.00000")
{
strCostAmount = Convert.ToDecimal(Convert.ToDecimal(dr["GD_COST_PRC"]) * Convert.ToInt32(goodsCount)).ToString("0.00");
}
else
{
strCostAmount = "0";
}
}
else
{
strCostAmount = "0";
}
//ItemName
//ItemName = ItemBrules.GetItemName(dr["GD_CAT_CD"].ToString()).Rows[0][0].ToString();
if (!string.IsNullOrEmpty(dr["GD_CAT_CD"].ToString()))
{
DataTable dtItemName = ItemBrules.GetItemName(dr["GD_CAT_CD"].ToString());
if (dtItemName.Rows.Count > 0)
{
ItemName = dtItemName.Rows[0][0].ToString();
}
else
{
ItemName = "";
}
}
//供应商名称
if (!string.IsNullOrEmpty(dr["GD_MK_CD"].ToString()))
{
DataTable dtMakeName = ItemBrules.GetMakerName(dr["GD_MK_CD"].ToString());
if (dtMakeName.Rows.Count > 0)
{
MakerName = dtMakeName.Rows[0][0].ToString();
}
}
else
{
MakerName = "";
}
//if (dr["GD_SALE_PRC"] != DBNull.Value)
//{
// strGD_SALE_PRC = regex.Replace(Convert.ToDecimal(dr["GD_SALE_PRC"]).ToString(), " ");
//}
if (dr["GD_COST_PRC"] != DBNull.Value)
{
strGD_COST_PRC = regex.Replace(Convert.ToDecimal(dr["GD_COST_PRC"]).ToString("0.00"), " ");
}
#endregion //string GD_COST_PRC = dr["GD_COST_PRC"]==null?"0":dr["GD_COST_PRC"].ToString(); Convert.ToDecimal(GD_COST_PRC).ToString("0.00")
//string MakerName = ItemBrules.GetMakerName(dr["GD_MK_CD"].ToString()).Rows[0][0].ToString();
objSw.WriteLine(num++ + "\t" + regex.Replace(dr["GD_CD"].ToString(), "") + "\t" + regex.Replace(dr["GD_NEW_CD"].ToString(), "") + "\t" + regex.Replace(dr["GD_NAME_CN"].ToString(), "") + "\t" + regex.Replace(dr["GD_NAME_JP"].ToString(), "") + "\t" + regex.Replace(Convert.ToDecimal(dr["GD_SALE_PRC"]).ToString(), " ") + "\t" + strGD_COST_PRC + "\t" + goodsCount + "\t" + strCostAmount + "\t" + regex.Replace(dr["GD_CAT_CD"].ToString(), " ") + "\t" + regex.Replace(ItemName, " ") + "\t" + regex.Replace(dr["GD_BRAND_CN"].ToString(), " ") + "\t" + regex.Replace(dr["GD_CATA_CD"].ToString(), " ") + "\t" + regex.Replace(strCATA, " ") + "\t" + regex.Replace(dr["GD_CLASS"].ToString(), " ") + "\t" + regex.Replace(dr["GD_SUB_CLASS"].ToString(), " ") + "\t" + regex.Replace(dr["GD_COLOR"].ToString(), " ") + "\t" + regex.Replace(dr["GD_MK_CD"].ToString(), " ") + "\t" + regex.Replace(MakerName, " ") + "\t" + regex.Replace(dr["GD_DS_NAME"].ToString(), " ") + "\t" + regex.Replace(dr["GD_SPEC2"].ToString(), " ") + "\t" + regex.Replace(dr["GD_SPEC1"].ToString(), " ") + "\t" + regex.Replace(dr["GD_PROD"].ToString(), " ") + "\t" + regex.Replace(dr["GD_STAND"].ToString(), " ") + "\t" + regex.Replace(dr["GD_YEAR"].ToString(), " ") + "\t" + regex.Replace(dr["SAFE_SEC_TYPE"].ToString(), " ") + "\t" + regex.Replace(dr["GD_MODEL"].ToString(), " "));//
}
objSw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(strFileName));
Response.ContentType = "application/ms-excel";//指定返回的是一个不能被客户端读取的流,必须被下载
Response.WriteFile(strAllFileName); //把文件流发送到客户端
Response.End();
<httpRuntime maxRequestLength="99999" useFullyQualifiedRedirectUrl="true" executionTimeout="150" />
maxRequestLength是设置导出数据大小的,此时是99.999M,系统默认是4M。
当导出时,系统把数据写在Excel里并保存在服务器上,然后客户端下载此文件