. ashx DataTable to Excel

Using System;
Using System.Collections;
Using System.Collections.Generic;
Using System.Data;
Using System.Linq;
Using System.Text;
Using System.Web;
Using System.IO;
Using Newtonsoft.json;
Using System.Reflection;
Using System.Runtime.InteropServices;
Using Npoi. HSSF. Usermodel;
Using Npoi. Ss. Usermodel;
Using Npoi. Ss. Util;
Namespace Web.handler
{
<summary>
Summary description of Driveoverviewexcelnew
</summary>
public class Driveoverviewexcelnew:ihttphandler
{

public void ProcessRequest (HttpContext context)
{
String strpath = Createexcelfilebynpoi (context);
String Strjson = "[{\" result\ ": \" 1\ ", \" data\ ":" + Strpath + "}]";
Context. Response.ContentType = "Text/plain";
Context. Response.Write (strpath);
}

public string Createexcelfilebynpoi (HttpContext context)
{
Hssfworkbook wk = new Hssfworkbook ();
Isheet TB = wk. Createsheet ("Summary table");

dictionary<string, string> columnames = getColumnName ();//List

System.Data.DataTable dt = getData (context);

var data = (from T in dt. AsEnumerable ()
Select New
{
crmclient_id = t["crmclient_id"],
Crmarea_name = t["Crmarea_name"],
Crmclient_searchname = t["Crmclient_searchname"],
Crmclient_cluestime = t["Crmclient_cluestime"],
Crmclient_name = t["Crmclient_name"],
Crmtrade_name = t["Crmtrade_name"],
Crmclient_trench = t["Crmclient_trench"],
Provincename = t["Provincename"],
CityName = t["CityName"],
crmclient_address = t["Crmclient_address"],
Crmclientconactpersonname = t["Crmclientconactpersonname"],
Crmclient_productname = t["Crmclient_productname"],
Crmproducttype_name = t["Crmproducttype_name"],
Crmlogisticsmodel_name = t["Crmlogisticsmodel_name"],
crmclient_istradetop=t["Crmclient_istradetop"],
crmclient_ismatched = t["crmclient_ismatched"],
Crmclient_iscompanytop = t["Crmclient_iscompanytop"],
Crmclient_isareatop = t["Crmclient_isareatop"],
Crmclient_isprovincetop = t["Crmclient_isprovincetop"],
Crmoppportunity_code = t["Crmoppportunity_code"],
Crmoppportunity_name = t["Crmoppportunity_name"],
Oppochengquhao = t["Oppochengquhao"],
Crmoppportunity_createddatetime = t["Crmoppportunity_createddatetime"],
Crmoppportunity_analysis = t["Crmoppportunity_analysis"],
CRMOPPPORTUNITY_PURCHASEDP = t["CRMOPPPORTUNITY_PURCHASEDP"],
Crmoppportunitypersonname = t["Crmoppportunitypersonname"],
Crmclient_logisticsfees = t["Crmclient_logisticsfees"],
Crmoppportunity_amount = t["Crmoppportunity_amount"],
Crmoppportunity_signdate = t["Crmoppportunity_signdate"],
Crmoppportunity_problemsanddeve = t["Crmoppportunity_problemsanddeve"],
Crmproject_code = t["Crmproject_code"],
Crmproject_creatorname = t["Crmproject_creatorname"],
Crmprojectclass_name = t["Crmprojectclass_name"],
Crmproject_iscrossarea = t["Crmproject_iscrossarea"],
Crmproject_estimatedcost = t["Crmproject_estimatedcost"],
Crmproject_willgetamount = t["Crmproject_willgetamount"],
Crmproject_estimatestart = t["Crmproject_estimatestart"],
Crmproject_estimateend = t["Crmproject_estimateend"],
Crmproject_tendertime = t["Crmproject_tendertime"],
Crmproject_descriptionofway = t["Crmproject_descriptionofway"],
Crmproject_managername = t["Crmproject_managername"],
Crmprojectpersonname = t["Crmprojectpersonname"],
Lixiangfenxi = t["Lixiangfenxi"],
Xiangmuqidong = t["Xiangmuqidong"],
Xiangmufenxi = t["Xiangmufenxi"],
Chanpinyufangan = t["Chanpinyufangan"],
Toubiaoshangwu = t["Toubiaoshangwu"],
Xiaoshouxianmu = t["Xiaoshouxianmu"],
Xiangmujiaojie = t["Xiangmujiaojie"],
Hetongjiaofu = t["Hetongjiaofu"],
Gongsikaocha = t["Gongsikaocha"],
Jiaoliuhuibao = t["Jiaoliuhuibao"],
Gaocengbaifang = t["Gaocengbaifang"],
Yangbandian = t["Yangbandian"],
Crmbid_result = t["Crmbid_result"],
Crmbid_amount = t["Crmbid_amount"],
Bidhetongdate = t["Bidhetongdate"]
});
int indexrownumber = 0;
int maxrow = 0;

IRow row0 = tb. CreateRow (0);
if (row0. Cells.count <= 0) createcells (row0);
Tb. Addmergedregion (New cellrangeaddress (0, 0, 0, 74));

Icell cell = row0. Getcell (0);
Cell. Setcellvalue ("Summary sheet of business information in the area");

IRow row1 = tb. CreateRow (1);
if (row1. Cells.count <= 0) createcells (row1);
for (int xuhao = 0; Xuhao < ColumNames.Keys.Count (); xuhao++)
{
Icell cell = Row1. Createcell (Xuhao);

Row1. Cells[xuhao]. Setcellvalue (Columnames[columnames.keys.toarray () [Xuhao]]);
}

int indexrow = 0;

foreach (var indexclient in data)
{
indexrow++;
if (Indexrownumber = = 0)
indexrownumber++;
Else
{
Indexrownumber = indexrownumber + maxrow + 1;
MaxRow = 0;
}
IRow Rown = tb. CreateRow (Indexrownumber + 1);
if (rowN.Cells.Count <= 0) createcells (rown);

Rown.cells[0]. Setcellvalue (Indexrow.tostring ());
ROWN.CELLS[1]. Setcellvalue (IndexClient.CrmArea_Name.ToString ());
ROWN.CELLS[2]. Setcellvalue (IndexClient.CrmClient_SearchName.ToString ());
ROWN.CELLS[3]. Setcellvalue (IndexClient.CrmClient_CluesTime.ToString ());
ROWN.CELLS[4]. Setcellvalue (IndexClient.CrmClient_Name.ToString ());
ROWN.CELLS[5]. Setcellvalue (IndexClient.CrmTrade_Name.ToString ());
ROWN.CELLS[6]. Setcellvalue (IndexClient.CrmClient_Trench.ToString ());
ROWN.CELLS[7]. Setcellvalue (IndexClient.ProvinceName.ToString ());
ROWN.CELLS[8]. Setcellvalue (IndexClient.CityName.ToString ());
ROWN.CELLS[9]. Setcellvalue (IndexClient.CrmClient_Address.ToString ());
ROWN.CELLS[10]. Setcellvalue (IndexClient.CrmClientConactPersonName.ToString ());
ROWN.CELLS[11]. Setcellvalue (IndexClient.CrmClient_ProductName.ToString ());
ROWN.CELLS[12]. Setcellvalue (IndexClient.CrmProductType_Name.ToString ());
ROWN.CELLS[13]. Setcellvalue (IndexClient.CrmLogisticsModel_Name.ToString ());
ROWN.CELLS[14]. Setcellvalue (IndexClient.CrmClient_IsMatched.ToString ());
ROWN.CELLS[15]. Setcellvalue (IndexClient.CrmClient_IsTradeTop.ToString ());
ROWN.CELLS[16]. Setcellvalue (IndexClient.CrmClient_IsCompanyTop.ToString ());
ROWN.CELLS[17]. Setcellvalue (IndexClient.CrmClient_IsAreaTop.ToString ());
ROWN.CELLS[18]. Setcellvalue (IndexClient.CrmClient_IsProvinceTop.ToString ());
ROWN.CELLS[19]. Setcellvalue (IndexClient.CrmOppportunity_Code.ToString ());
ROWN.CELLS[20]. Setcellvalue (IndexClient.CrmOppportunity_Name.ToString ());
ROWN.CELLS[21]. Setcellvalue (IndexClient.oppoChengquhao.ToString ());
ROWN.CELLS[22]. Setcellvalue (IndexClient.CrmOppportunity_CreatedDateTime.ToString ());
ROWN.CELLS[23]. Setcellvalue (IndexClient.CrmOppportunity_Analysis.ToString ());
ROWN.CELLS[24]. Setcellvalue (IndexClient.CrmOppportunity_PurchaseDp.ToString ());
ROWN.CELLS[25]. Setcellvalue (IndexClient.CrmOppportunityPersonName.ToString ());
ROWN.CELLS[26]. Setcellvalue (IndexClient.CrmClient_LogisticsFees.ToString ());
ROWN.CELLS[27]. Setcellvalue (IndexClient.CrmOppportunity_Amount.ToString ());
ROWN.CELLS[28]. Setcellvalue (IndexClient.CrmOppportunity_SignDate.ToString ());
ROWN.CELLS[29]. Setcellvalue (IndexClient.CrmOppportunity_ProblemsAndDeve.ToString ());
ROWN.CELLS[30]. Setcellvalue (IndexClient.CrmProject_Code.ToString ());
ROWN.CELLS[31]. Setcellvalue (IndexClient.CrmProject_CreatorName.ToString ());
ROWN.CELLS[32]. Setcellvalue (IndexClient.CrmProjectClass_Name.ToString ());
ROWN.CELLS[33]. Setcellvalue (IndexClient.CrmProject_IsCrossArea.ToString ());
ROWN.CELLS[34]. Setcellvalue (IndexClient.CrmProject_EstimatedCost.ToString ());
ROWN.CELLS[35]. Setcellvalue (IndexClient.CrmProject_WillGetAmount.ToString ());
ROWN.CELLS[36]. Setcellvalue (IndexClient.CrmProject_EstimateStart.ToString ());
ROWN.CELLS[37]. Setcellvalue (IndexClient.CrmProject_EstimateEnd.ToString ());
ROWN.CELLS[38]. Setcellvalue (IndexClient.CrmProject_TenderTime.ToString ());
ROWN.CELLS[39]. Setcellvalue (IndexClient.CrmProject_DescriptionOfWay.ToString ());
ROWN.CELLS[40]. Setcellvalue (IndexClient.CrmProject_ManagerName.ToString ());
ROWN.CELLS[41]. Setcellvalue (IndexClient.CrmProjectPersonName.ToString ());
ROWN.CELLS[42]. Setcellvalue (IndexClient.xiangmuqidong.ToString ());
ROWN.CELLS[43]. Setcellvalue (IndexClient.xiangmufenxi.ToString ());
ROWN.CELLS[44]. Setcellvalue (IndexClient.chanpinyufangan.ToString ());
ROWN.CELLS[45]. Setcellvalue (IndexClient.toubiaoshangwu.ToString ());
ROWN.CELLS[46]. Setcellvalue (IndexClient.xiaoshouxianmu.ToString ());
ROWN.CELLS[47]. Setcellvalue (IndexClient.xiangmujiaojie.ToString ());
ROWN.CELLS[48]. Setcellvalue (IndexClient.hetongjiaofu.ToString ());
ROWN.CELLS[49]. Setcellvalue (IndexClient.gongsikaocha.ToString ());
ROWN.CELLS[50]. Setcellvalue (IndexClient.jiaoliuhuibao.ToString ());
ROWN.CELLS[51]. Setcellvalue (IndexClient.gaocengbaifang.ToString ());
ROWN.CELLS[52]. Setcellvalue (IndexClient.yangbandian.ToString ());
ROWN.CELLS[53]. Setcellvalue (IndexClient.yangbandian.ToString ());
ROWN.CELLS[54]. Setcellvalue (IndexClient.CrmBid_Result.ToString ());
ROWN.CELLS[55]. Setcellvalue (IndexClient.CrmBid_Amount.ToString ());
ROWN.CELLS[56]. Setcellvalue (IndexClient.bidhetongdate.ToString ());
}
if (TB! = NULL)
{
String excelfolder = "~/outputerror/opportunity/" + DateTime.Now.ToString ("YYYYMMDDHHMMSS") + ". xls";
String excelfolder = "~/outputerror/opportunity/bb.xls";
String FilePath = context. Server.MapPath (Excelfolder);
if (! File.exists (FilePath))
{
FileStream fs = File.create (FilePath);
Wk. Write (FS);
Wk. Close ();
Fs. Close ();
Fs. Dispose ();

}
return excelfolder;
}

Return "";
}
<summary>
Column Name
</summary>
<returns></returns>
Private dictionary<string, string> getColumnName ()
{

dictionary<string, string> namelist = new dictionary<string, string> ();
Namelist.add ("Xuhao", "serial number");
Namelist.add ("Crmarea_name", "slice area");
Namelist.add ("Crmclient_searchname", "lead collection Person");
Namelist.add ("Crmclient_cluestime", "Lead acquisition Time");
Namelist.add ("Crmclient_name", "Customer full name");
Namelist.add ("Crmtrade_name", "industry");
Namelist.add ("Crmclient_trench", "channel Source");
Namelist.add ("Provincename", "province");
Namelist.add ("CityName", "City");
Namelist.add ("Crmclient_address", "Customer Address");
Namelist.add ("Crmclientconactpersonname", "Customer Contact");
Namelist.add ("Crmclient_productname", "Product Name");
Namelist.add ("Crmproducttype_name", "Product Attributes");
Namelist.add ("Crmlogisticsmodel_name", "logistics Mode");
Namelist.add ("Crmclient_logisticsfees", "annual logistics Cost (million)");
Namelist.add ("crmclient_ismatched", "match with our business");
Namelist.add ("Crmclient_istradetop", "top of the industry");
Namelist.add ("Crmclient_iscompanytop", "Company Top");
Namelist.add ("Crmclient_isareatop", "top of the Slice");
Namelist.add ("Crmclient_isprovincetop", "Provincial Top");
Namelist.add ("Crmoppportunity_code", "Opportunity point number");
Namelist.add ("Crmoppportunity_name", "Opportunity Point Name");
Namelist.add ("Oppochengquhao", "City area code of Opportunity Point");
Namelist.add ("Crmoppportunity_createddatetime", "Opportunity Point Acquisition Time");
Namelist.add ("Crmoppportunity_analysis", "Opportunity Point Analysis");
Namelist.add ("CRMOPPPORTUNITY_PURCHASEDP", "Logistics supplier Purchasing Department");
Namelist.add ("Crmoppportunitypersonname", "Business docking person and position");
Namelist.add ("Crmoppportunity_amount", "estimate the amount of the bill (million)");
Namelist.add ("Crmoppportunity_signdate", "expected signing Time");
Namelist.add ("Crmoppportunity_problemsanddeve", "Progress and Problems");
Namelist.add ("Crmproject_code", "project Number");
Namelist.add ("Crmproject_creatorname", "Project Applicant");
Namelist.add ("Crmprojectclass_name", "project Level");
Namelist.add ("Crmproject_iscrossarea", "cross-regional project");
Namelist.add ("Crmproject_estimatedcost", "Customer logistics cost Budget (total) (million)");
Namelist.add ("Crmproject_willgetamount", "Our participation in the logistics cost budget (million)");
Namelist.add ("Crmproject_estimatestart", "Client budget start Execution time");
Namelist.add ("Crmproject_estimateend", "Client budget End Execution Time");
Namelist.add ("Crmproject_tendertime", "Tender Time");
Namelist.add ("Crmproject_descriptionofway", "line Division description");
Namelist.add ("Crmproject_managername", "project manager");
Namelist.add ("Crmprojectpersonname", "project team member");
Namelist.add ("Lixiangfenxi", "Project Analysis Meeting");//The following time is the same field
Namelist.add ("Xiangmuqidong", "Project Initiation Meeting");
Namelist.add ("Xiangmufenxi", "Project Analysis Meeting");
Namelist.add ("Chanpinyufangan", "products and solutions decision-making");
Namelist.add ("Toubiaoshangwu", "bidding and business decision-making");
Namelist.add ("Xiaoshouxianmu", "Sales project summary Meeting");
Namelist.add ("Xiangmujiaojie", "Project handover Meeting");
Namelist.add ("Hetongjiaofu", "Contract delivery summary");
Namelist.add ("Gongsikaocha", "Company Inspection");
Namelist.add ("Jiaoliuhuibao", "Communication Report");
Namelist.add ("Gaocengbaifang", "high-level visit");
Namelist.add ("Zhanhui", "Exhibition/Forum/Event Invitation");
Namelist.add ("Yangbandian", "sample site Visit");
Namelist.add ("Crmbid_result", "bid result");
Namelist.add ("Crmbid_amount", "Contract Amount (million)");
Namelist.add ("Bidhetongdate", "contract term");
return namelist;
}

Private System.Data.DataTable GetData (HttpContext context)
{

Bll. Crmclient BLL = new BLL. Crmclient ();
Hashtable Hashtable_clientid = new Hashtable ();
for (int i = 0; I < context. Request.Form.Count; i++)
{
Hashtable_clientid. ADD (i, Context. Request.form[i]. ToString ());
}

StringBuilder builder = new StringBuilder ();
String fengefu = "";
foreach (DictionaryEntry de in Hashtable_clientid)
{
Builder. Append (FENGEFU);
Builder. Append ("'");
Builder. Append (DE. Value.tostring ());
Builder. Append ("'");
Fengefu = ",";
}

Return to the BLL. Getoverviewdriveallinformation (builder. ToString ());
}

Public IRow createcells (IRow ir)
{
for (int i = 0; i <; i++)
{
ir. Createcell (i);
}
return IR;
}
public bool IsReusable
{
Get
{
return false;
}
}
}
}

. ashx DataTable to Excel