.ashx datatable轉excel

標籤：path   try   rownumber   amount   mss   string   銷售   rtu   web   

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>
/// 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("匯總表");

Dictionary<string, string> columNames = getColumnName();//列明

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("片區商務資訊匯總表");

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>
/// 列名
/// </summary>
/// <returns></returns>
private Dictionary<string, string> getColumnName()
{

Dictionary<string, string> nameList = new Dictionary<string, string>();
nameList.Add("xuhao", "序號");
nameList.Add("CrmArea_Name", "片區");
nameList.Add("CrmClient_SearchName", "線索搜集人");
nameList.Add("CrmClient_CluesTime", "線索擷取時間");
nameList.Add("CrmClient_Name", "客戶全稱");
nameList.Add("CrmTrade_Name", "行業");
nameList.Add("CrmClient_Trench", "渠道來源");
nameList.Add("ProvinceName", "省");
nameList.Add("CityName", "市");
nameList.Add("CrmClient_Address", "客戶地址");
nameList.Add("CrmClientConactPersonName", "客戶連絡人");
nameList.Add("CrmClient_ProductName", "產品名稱");
nameList.Add("CrmProductType_Name", "產品屬性");
nameList.Add("CrmLogisticsModel_Name", "物流模式");
nameList.Add("CrmClient_LogisticsFees", "年物流費用（萬）");
nameList.Add("CrmClient_IsMatched", "與我司業務是否匹配");
nameList.Add("CrmClient_IsTradeTop", "行業TOP");
nameList.Add("CrmClient_IsCompanyTop", "公司TOP");
nameList.Add("CrmClient_IsAreaTop", "片區TOP");
nameList.Add("CrmClient_IsProvinceTop", "省級TOP");
nameList.Add("CrmOppportunity_Code", "機會點編號");
nameList.Add("CrmOppportunity_Name", "機會點名稱");
nameList.Add("oppoChengquhao", "機會點所屬城市區號");
nameList.Add("CrmOppportunity_CreatedDateTime", "機會點擷取時間");
nameList.Add("CrmOppportunity_Analysis", "機會點分析");
nameList.Add("CrmOppportunity_PurchaseDp", "物流供應商採購部");
nameList.Add("CrmOppportunityPersonName", "業務對接人及職位");
nameList.Add("CrmOppportunity_Amount", "預估簽單金額（萬）");
nameList.Add("CrmOppportunity_SignDate", "預計簽約時間");
nameList.Add("CrmOppportunity_ProblemsAndDeve", "進展與問題");
nameList.Add("CrmProject_Code", "項目編號");
nameList.Add("CrmProject_CreatorName", "項目申請人");
nameList.Add("CrmProjectClass_Name", "項目層級");
nameList.Add("CrmProject_IsCrossArea", "是否跨地區項目");
nameList.Add("CrmProject_EstimatedCost", "客戶物流費用預算（總）（萬）");
nameList.Add("CrmProject_WillGetAmount", "我司參與的物流費用預算（萬）");
nameList.Add("CrmProject_EstimateStart", "客戶預算開始執行時間");
nameList.Add("CrmProject_EstimateEnd", "客戶預算結束執行時間");
nameList.Add("CrmProject_TenderTime", "招標時間");
nameList.Add("CrmProject_DescriptionOfWay", "線路劃分描述");
nameList.Add("CrmProject_ManagerName", "專案經理");
nameList.Add("CrmProjectPersonName", "項目群組成員");
nameList.Add("lixiangfenxi", "立項分析會");//以下時間是同一個欄位
nameList.Add("xiangmuqidong", "項目啟動會");
nameList.Add("xiangmufenxi", "項目分析會");
nameList.Add("chanpinyufangan", "產品與解決方案決策會");
nameList.Add("toubiaoshangwu", "投標與商務決策會");
nameList.Add("xiaoshouxianmu", "銷售項目總結會");
nameList.Add("xiangmujiaojie", "項目交接會");
nameList.Add("hetongjiaofu", "合約交付總結會");
nameList.Add("gongsikaocha", "公司考察");
nameList.Add("jiaoliuhuibao", "交流彙報");
nameList.Add("gaocengbaifang", "高層拜訪");
nameList.Add("zhanhui", "展會/論壇/活動邀請");
nameList.Add("yangbandian", "樣板點參觀");
nameList.Add("CrmBid_Result", "投標結果");
nameList.Add("CrmBid_Amount", "合約金額（萬）");
nameList.Add("bidhetongdate", "合約期限");
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 bll.GetOverviewDriveAllInformation(builder.ToString());
}

public IRow createCells(IRow ir)
{
for (int i = 0; i < 75; i++)
{
ir.CreateCell(i);
}
return ir;
}
public bool IsReusable
{
get
{
return false;
}
}
}
}

.ashx datatable轉excel