C# 导出 Execl 响应超时

大飞为 2017-11-15 10:30:03
这是我的代码:

[code=csharp] DataToExcel dte = new DataToExcel();
string FilePath = "../Excel/PrizeOrder/";
string filename = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
try
{

List<string> ids = getSelected();

DataTable outdt = BLL.TBPrizeOrderBLL.FindByWhere23(Common.PageValidate.InputText(this.txtCode.Text, 50),
Common.PageValidate.InputText(this.txtName.Text, 50),
this.txtStartTime.Text, this.txtEndTime.Text, this.ddlState.SelectedValue, ids);

string SQLss = BLL.TBPrizeOrderBLL.FindByWhereTwo(Common.PageValidate.InputText(this.txtCode.Text, 50),
Common.PageValidate.InputText(this.txtName.Text, 50),
this.txtStartTime.Text, this.txtEndTime.Text, this.ddlState.SelectedValue, ids);

COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile();

excel.CreateFile(Server.MapPath(FilePath + filename));
excel.PrintGridLines = false;

COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;

double height = 1.7;
excel.SetMargin(ref mt1, ref height);
excel.SetMargin(ref mt2, ref height);
excel.SetMargin(ref mt3, ref height);
excel.SetMargin(ref mt4, ref height);

COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
string font = "宋体";
short fontsize = 10;
excel.SetFont(ref font, ref fontsize, ref ff);

byte b1 = 1,
b2 = 12;
short s3 = 12;
excel.SetColumnWidth(ref b1, ref b2, ref s3);

string header = "页眉";
string footer = "页脚";
excel.SetHeader(ref header);
excel.SetFooter(ref footer);




COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText;
COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0;
COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;

// 报表标题
int cellformat = 1;
// int rowindex = 1,colindex = 3;
// object title = (object)strTitle;
// excel.WriteValue(ref vt, ref cf, ref ca, ref chl,ref rowindex,ref colindex,ref title,ref cellformat);

int rowIndex = 1;//起始行
int colIndex = 0;

string[] titlelist = { "订单号","下单时间","下单人","消耗积分","收货人","联系电话","所属地区","收货地址","订单状态","审核人","审核时间",
"审核描述","备注","快递方式","运费","物流单号","发货人","发货时间","商品编码", "商品名称", "单品积分", "订单数量", "配送数量","商品品牌","商品系列", "实扣积分"};

//取得列标题
foreach (string titleitem in titlelist)
{
colIndex++;
object namestr = (object)titleitem;
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat);
}
DataTable detaldt = BLL.TBPrizeOrderBLL.FindDetail();
//取得表格中的数据
foreach (DataRow row in outdt.Rows)
{

DataRow[] ddt = detaldt.Select("PrizeOrder_ID="+Convert.ToInt32(row["id"]));



foreach (DataRow drow in ddt)
{
rowIndex++;
colIndex = 0;
//订单号
colIndex++;
object str = (object)row["OrderCode"].ToString();
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
//订单时间
colIndex++;
str = row["OrderTime"].ToString();
try
{
str = (object)(Convert.ToDateTime(row["OrderTime"].ToString())).ToString("yyyy-MM-dd");
}
catch (Exception)
{
}
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);

//下单人
colIndex++;
str = (object)row["RealName"].ToString();
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);




...全文
220 7 点赞 打赏 收藏 举报
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
mirrorspace 2017-11-17
复杂啊.使用NPOI NPOI
  • 打赏
  • 举报
回复
hxm_admin 2017-11-17
http://blog.csdn.net/hxm_admin/article/details/47130215
  • 打赏
  • 举报
回复
by_封爱 2017-11-15
使用npoi导出..... 这里
  • 打赏
  • 举报
回复
大飞为 2017-11-15
删减部分导出项
  • 打赏
  • 举报
回复
大飞为 2017-11-15
        protected void lbtnExport_Click(object sender, EventArgs e)
        {
            DataToExcel dte = new DataToExcel();
            string FilePath = "../Excel/PrizeOrder/";
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
            try
            {

                List<string> ids = getSelected();
                DataTable outdt = BLL.TBPrizeOrderBLL.FindByWhere23(Common.PageValidate.InputText(this.txtCode.Text, 50),
                 Common.PageValidate.InputText(this.txtName.Text, 50),
                this.txtStartTime.Text, this.txtEndTime.Text, this.ddlState.SelectedValue, ids);
                string  SQLss = BLL.TBPrizeOrderBLL.FindByWhereTwo(Common.PageValidate.InputText(this.txtCode.Text, 50),
                 Common.PageValidate.InputText(this.txtName.Text, 50),
                this.txtStartTime.Text, this.txtEndTime.Text, this.ddlState.SelectedValue, ids);                
                COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile();
                excel.CreateFile(Server.MapPath(FilePath + filename));
                excel.PrintGridLines = false;
                COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
                COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
                COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
                COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;
                double height = 1.7;
                excel.SetMargin(ref mt1, ref height);
                excel.SetMargin(ref mt2, ref height);
                excel.SetMargin(ref mt3, ref height);
                excel.SetMargin(ref mt4, ref height);
                COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
                string font = "宋体";
                short fontsize = 10;
                excel.SetFont(ref font, ref fontsize, ref ff);
                byte b1 = 1,
                    b2 = 12;
                short s3 = 12;
                excel.SetColumnWidth(ref b1, ref b2, ref s3);
                string header = "页眉";
                string footer = "页脚";
                excel.SetHeader(ref header);
                excel.SetFooter(ref footer);
                COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText;
                COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0;
                COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
                COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;
                int cellformat = 1;
                int rowIndex = 1;//起始行
                int colIndex = 0;
                string[] titlelist = { "订单号","下单时间","下单人","消耗积分","收货人","联系电话","所属地区","收货地址","订单状态","审核人","审核时间",
                                     "审核描述","备注","快递方式","运费","物流单号","发货人","发货时间","商品编码", "商品名称", "单品积分", "订单数量", "配送数量","商品品牌","商品系列", "实扣积分"};
                //取得列标题				
                foreach (string titleitem in titlelist)
                {
                    colIndex++;
                    object namestr = (object)titleitem;
                    excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat);
                }
                DataTable detaldt = BLL.TBPrizeOrderBLL.FindDetail();
                //取得表格中的数据			
                foreach (DataRow row in outdt.Rows)
                {
                    DataRow[] ddt = detaldt.Select("PrizeOrder_ID="+Convert.ToInt32(row["id"]));
                    foreach (DataRow drow in ddt)
                    {
                        rowIndex++;
                        colIndex = 0;
                        //订单号
                        colIndex++;
                        object str = (object)row["OrderCode"].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //订单时间
                        colIndex++;
                        str = row["OrderTime"].ToString();
                        try
                        {
                            str = (object)(Convert.ToDateTime(row["OrderTime"].ToString())).ToString("yyyy-MM-dd");
                        }
                        catch (Exception)
                        {
                        }
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //下单人
                        colIndex++;
                        str = (object)row["RealName"].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //消耗积分
                        colIndex++;
                        decimal totalIntegral = 0;
                        for (int i = 0; i < ddt.Length; i++)
                        {
                            if (ddt[i]["DeliveryQty"] != null && ddt[i]["DeliveryQty"] != DBNull.Value && string.IsNullOrEmpty(ddt[i]["DeliveryQty"].ToString()) == false)
                            {
                                decimal qty = Convert.ToDecimal(ddt[i]["DeliveryQty"]);
                                totalIntegral = totalIntegral + (qty * Convert.ToDecimal(ddt[i]["Integral"]));
                                detaldt.Rows[i]["TotalIntegral"] = qty * Convert.ToDecimal(ddt[i]["Integral"]);
                            }
                            else
                            {
                                decimal qty = Convert.ToDecimal(ddt[i]["OrderQty"]); //获取填充子类的id 
                                totalIntegral = totalIntegral + (qty * Convert.ToDecimal(ddt[i]["Integral"]));
                            }
                        }
                        str = (object)totalIntegral;
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //收货人
                        colIndex++;
                        str = (object)row["Receiver"].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //联系电话
                        colIndex++;
                        str = (object)row["Tel"].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //所属地区
                        colIndex++;
                        str = (object)row["Address"].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //收货地址
                        colIndex++;
                        str = (object)row["DetailedAddress"].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //订单状态
                        colIndex++;
                        string state = BLL.GetData.GetOrderState2(row["CheckState"].ToString());
                        str = (object)state;
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //审核人
                        colIndex++;
                        str = (object)row["Emp_Name"].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //审核时间
                        colIndex++;
                        str = row["OrderTime"].ToString();
                        try
                        {
                            str = (object)(Convert.ToDateTime(row["Check_Date"].ToString())).ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        catch (Exception)
                        {
                        }
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //审核描述
                        colIndex++;
                        str = (object)row["CheckDiscription"].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //发货人
                        colIndex++;
                        str = (object)row["Sender"].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        //发货时间时间
                        colIndex++;
                        str = row["SendTime"].ToString();
                        try
                        {
                            str = (object)(Convert.ToDateTime(row["Check_Date"].ToString())).ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        catch (Exception)
                        {
                        }
                    }
                    excel.PrintGridLines = false;
                }
                int ret = excel.CloseFile();
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                //dte.KillExcelProcess();
            }
            finally
            {
                dte.KillExcelProcess();
            }

            if (filename != "")
            {
               

                Response.Redirect(FilePath + "/" + filename, true);
            }

        }
  • 打赏
  • 举报
回复
大飞为 2017-11-15
//消耗积分 colIndex++; decimal totalIntegral = 0; for (int i = 0; i < ddt.Length; i++) { if (ddt[i]["DeliveryQty"] != null && ddt[i]["DeliveryQty"] != DBNull.Value && string.IsNullOrEmpty(ddt[i]["DeliveryQty"].ToString()) == false) { decimal qty = Convert.ToDecimal(ddt[i]["DeliveryQty"]); totalIntegral = totalIntegral + (qty * Convert.ToDecimal(ddt[i]["Integral"])); detaldt.Rows[i]["TotalIntegral"] = qty * Convert.ToDecimal(ddt[i]["Integral"]); } else { decimal qty = Convert.ToDecimal(ddt[i]["OrderQty"]); //获取填充子类的id totalIntegral = totalIntegral + (qty * Convert.ToDecimal(ddt[i]["Integral"])); } } str = (object)totalIntegral; excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //收货人 colIndex++; str = (object)row["Receiver"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //联系电话 colIndex++; str = (object)row["Tel"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //所属地区 colIndex++; str = (object)row["Address"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //收货地址 colIndex++; str = (object)row["DetailedAddress"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //订单状态 colIndex++; string state = BLL.GetData.GetOrderState2(row["CheckState"].ToString()); str = (object)state; excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //审核人 colIndex++; str = (object)row["Emp_Name"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //审核时间 colIndex++; str = row["OrderTime"].ToString(); try { str = (object)(Convert.ToDateTime(row["Check_Date"].ToString())).ToString("yyyy-MM-dd HH:mm:ss"); } catch (Exception) { } excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //审核描述 colIndex++; str = (object)row["CheckDiscription"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //备注 colIndex++; str = (object)row["Remark"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //快递方式 colIndex++; str = (object)row["SendType"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //运费 colIndex++; str = (object)row["Cost"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //物流单号 colIndex++; str = (object)row["TrackingNumber"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //发货人 colIndex++; str = (object)row["Sender"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //发货时间时间 colIndex++; str = row["SendTime"].ToString(); try { str = (object)(Convert.ToDateTime(row["Check_Date"].ToString())).ToString("yyyy-MM-dd HH:mm:ss"); } catch (Exception) { } excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); ////订单明细 //excel.PrintGridLines = true; //rowIndex++; //商品编码 colIndex++; str = (object)drow["PrizeCode"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //商品名称 colIndex++; str = (object)drow["PrizeName"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //单品积分 colIndex++; str = (object)drow["Integral"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //订单数量 colIndex++; str = (object)drow["OrderQty"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //配送数量 colIndex++; str = (object)drow["DeliveryQty"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //商品品牌 colIndex++; str = (object)drow["Barnd"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //商品系列 colIndex++; str = (object)drow["Series"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); //实扣积分 colIndex++; str = (object)drow["TotalIntegral"].ToString(); excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat); } excel.PrintGridLines = false; } int ret = excel.CloseFile(); } catch (Exception ex) { string msg = ex.Message; //dte.KillExcelProcess(); } finally { dte.KillExcelProcess(); } if (filename != "") { Response.Redirect(FilePath + "/" + filename, true); }[/code] 响应超时。导出数据超过5000 就会超时 想用线程在后台挂着写入完成后 导出 但是提示 上下文不
  • 打赏
  • 举报
回复
星空蔚蓝 2017-11-15
响应时间有限制吧,改一下配置呢?
  • 打赏
  • 举报
回复
相关推荐
发帖
.NET社区
加入

5.9w+

社区成员

.NET技术交流专区
申请成为版主
帖子事件
创建了帖子
2017-11-15 10:30
社区公告
暂无公告