// 按商品设置系统加价导出Excel
protected void lbn_ImportByProduct_Click(object sender, EventArgs e)
{
string s = selectBywhere();
DataTable dt = logic.priceStructure.listWithProductAutoAdd(selectBywhere(), "categoryid asc,productId asc",Convert.ToInt32(ViewState["dtCount"]),1).Tables[1];
StringBuilder title = new StringBuilder();
title.Append(ddl_BN.SelectedItem.Text.Trim() + "档次");
if (ddlRootCategory.SelectedValue != "-1" && !string.IsNullOrEmpty(ddlRootCategory.SelectedValue))
title.Append("["+ddlRootCategory.SelectedItem.Text.Trim());
if (ddlSubCategory.SelectedValue != "-1" && !string.IsNullOrEmpty(ddlSubCategory.SelectedValue))
title.Append("-"+ddlSubCategory.SelectedItem.Text.Trim()+"]");
title.Append("系统加价体系");
// 导出Excel
ExportFacade exportInstance = new Facade.Excel.ExportFacade();
DataTable newDt = dt.DefaultView.ToTable(true, new string[2] { "parentCategoryId", "parentCategoryName" });
Dictionary<string, string> sheetList = new Dictionary<string, string>();
for (int i = 0; i < newDt.Rows.Count; i++)
{
sheetList.Add(newDt.Rows[i][0].ToString(), newDt.Rows[i][1].ToString());
}
Dictionary<string, string> columns = new Dictionary<string, string>();
columns.Add("categroyName", "小类");
columns.Add("productNo", "商品编号");
columns.Add("productName", "商品名称");
columns.Add("productAutoAdd", "系统加价体系");
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
exportInstance.ExportByWeb("parentCategoryId={0}", sheetList, columns, dt, title.ToString(), title.ToString() + ".xls", formatOptions, "商品系统加价体系");
}
protected void lbtn_Import_Click(object sender, EventArgs e)
{
StringBuilder where = new StringBuilder();
where.Append("1=1");
if (txt_OrderNo.Text.Trim() != "")
where.AppendFormat(" and [orderNo] ='{0}'", txt_OrderNo.Text.Trim());
if (ext_StartInputDt.SelectedDate != DateTime.MinValue)
where.AppendFormat(" and datediff(day,[orderDt],'{0}')<=0", ext_StartInputDt.Value.ToString());
if (ext_EndInputDt.SelectedDate!=DateTime.MinValue)
where.AppendFormat(" and datediff(day,[orderDt],'{0}')>=0", ext_EndInputDt.Value.ToString());
if (drp_Status.SelectedItem.Value != "")
where.AppendFormat(" and [status]='{0}'", drp_Status.SelectedItem.Value);
DataTable dt = logic.order.listByImportExcel(where.ToString() + " order by orderId desc");
dt.Columns["orderNo"].ColumnName = "订单号";
dt.Columns["statusName"].ColumnName = "订单状态";
dt.Columns["amount"].ColumnName = "订单总价";
dt.Columns["orderDt"].ColumnName = "订单日期";
dt.Columns["company"].ColumnName = "采购企业";
dt.Columns["contact"].ColumnName = "收货人";
dt.Columns["contactMobile"].ColumnName = "联系人手机";
dt.Columns["contactPhone"].ColumnName = "联系电话";
dt.Columns["address"].ColumnName = "收货地址";
dt.Columns["note"].ColumnName = "备注";
dt.Columns["serviceNote"].ColumnName = "客服描述";
dt.Columns["adminName"].ColumnName = "操作员";
ExportFacade facadeInstance = new ExportFacade();
facadeInstance.ExportByWeb(dt, "订单数据", "订单数据.xls", null, "订单数据");
}
protected void lbtn_ToExecl_Click(object sender, EventArgs e)
{
int company = logic.customer.companyId;
string startDt = hid_startDt.Value;
string endDt = hid_endDt.Value;
StringBuilder sb = new StringBuilder();
sb.AppendFormat("sellerId={0} ", company);
sb.AppendFormat(" and datediff(day,[updateStatusdt],'{0}')<=0", startDt);
sb.AppendFormat(" and datediff(day,[updateStatusdt],'{0}')>=0", endDt);
DataSet ds = logic.orderItem.listwithPayables(sb.ToString());// logic.orderItem.listwithReceivables(sb.ToString());
DataTable dt = ds.Tables[0];
dt.Columns["proName"].ColumnName = "商品";
dt.Columns["quantity"].ColumnName = "数量";
dt.Columns["sellerprice"].ColumnName = "单价";
dt.Columns["unit"].ColumnName = "单位";
dt.Columns["sellerAmount"].ColumnName = "小计";
dt.Columns["orderNo"].ColumnName = "订单号";
dt.Columns["statusName"].ColumnName = "订单状态";
dt.Columns["updateStatusdt"].ColumnName = "成功日期";
dt.Columns["rbNo"].ColumnName = "供货单号";
String xlsName = Convert.ToDateTime(startDt).ToShortDateString() + "至" + Convert.ToDateTime(endDt).ToShortDateString() + "的应收款";
ExportFacade exportHelper = new ExportFacade();
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(1, FormatStyle.ToFix2);
formatOptions.Add(2, FormatStyle.ToFix2);
formatOptions.Add(4, FormatStyle.ToFix2);
exportHelper.ExportByWeb(dt, xlsName, xlsName + ".xls", formatOptions, xlsName);
}
// 导出数据
protected void linkExport_Click(object sender, EventArgs e)
{
DataTable dt=ViewState["CacheDataTable"] as DataTable;
dt.Columns[0].ColumnName = "日期";
if (radModeM.Checked)
dt.Columns[0].ColumnName = "月份";
dt.Columns[1].ColumnName = "进价";
dt.Columns[2].ColumnName = "出价";
dt.Columns[3].ColumnName = "毛利";
dt.Columns[4].ColumnName = "毛利率";
string title = string.Empty;
if (ddlCompany.SelectedValue!="-1")
{
if (txtStartDt.SelectedDate != DateTime.MinValue && txtEndDt.SelectedDate != DateTime.MinValue && radModeD.Checked)
{
title = string.Format("{0}{1:yyyy-MM-dd}至{2:yyyy-MM-dd}{3}毛利率", ddlCompany.SelectedItem.Text, txtStartDt.SelectedDate, txtEndDt.SelectedDate, (radModeM.Checked ? "月" : "日"));
}
else
{
title = string.Format("{0}{1}年{2}毛利率", ddlCompany.SelectedItem.Text, ddlYear.SelectedItem.Text, (radModeM.Checked ? "月" : "日"));
}
}
else
{
if (txtStartDt.SelectedDate != DateTime.MinValue && txtEndDt.SelectedDate != DateTime.MinValue && radModeD.Checked)
{
title = string.Format("{0:yyyy-MM-dd}至{1:yyyy-MM-dd}平台{2}毛利率",txtStartDt.SelectedDate,txtEndDt.SelectedDate, (radModeM.Checked ? "月" : "日"));
}
else
{
title = string.Format("{0}年平台{1}毛利率", ddlYear.SelectedItem.Text, (radModeM.Checked ? "月" : "日"));
}
}
// 导出Excel
ExportFacade exportInstance = new Facade.Excel.ExportFacade();
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(4, FormatStyle.ToFix2);
exportInstance.ExportByWeb(dt, title, title + ".xls", formatOptions, title);
}
protected void link_ExportToExcel_Click(object sender, EventArgs e)
{
int year = DateTime.Now.Year;
int.TryParse(ddlYear.SelectedValue, out year);
int month = 0;
int.TryParse(ddlMonth.SelectedValue, out month);
string orderByColumn = "t.companyId";
if (ViewState["orderByColumn"] != null)
{
orderByColumn = ViewState["orderByColumn"].ToString();
}
DataTable dt = logic.company.exportCompanyGPMAnalysisToExcel(year, month, orderByColumn);
dt.Columns[0].ColumnName = "企业名称";
dt.Columns[1].ColumnName = "总进价";
dt.Columns[2].ColumnName = "总出价";
dt.Columns[3].ColumnName = "毛利";
dt.Columns[4].ColumnName = "毛利率";
string title = string.Format("采购配送{0}年{1}企业毛利分析", ddlYear.SelectedValue, (month != 0 ? month + "月" : ""));
// 导出Excel
ExportFacade exportInstance = new Facade.Excel.ExportFacade();
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(1, FormatStyle.ToFix2);
formatOptions.Add(2, FormatStyle.ToFix2);
formatOptions.Add(3, FormatStyle.ToFix2);
formatOptions.Add(4, FormatStyle.Percent);
List<int> footerAvgColumns = new List<int>()
{
1,2,3,4
};
List<int> footerSumColumns = new List<int>()
{
1,2,3
};
exportInstance.ExportByWeb(dt, title, title + ".xls", formatOptions, "企业毛利率分析", footerAvgColumns, footerSumColumns);
}
protected void linkExportToExcel_Command(object sender,CommandEventArgs e)
{
DataTable dt = ViewState["CacheDataTable_PPCD"] as DataTable;
DataTable newDt = dt.DefaultView.ToTable(false, new string[] { "categoryName","productName","businessNatureName","unit","companyName","oldSellerPrice","sellerPrice","inputDt"});
newDt.Columns[0].ColumnName = "类别";
newDt.Columns[1].ColumnName="级别";
newDt.Columns[2].ColumnName="商品名称";
newDt.Columns[3].ColumnName="单位";
newDt.Columns[4].ColumnName="供应商";
newDt.Columns[5].ColumnName="原价";
newDt.Columns[6].ColumnName="浮动价";
newDt.Columns[7].ColumnName = "报价时间";
string title = string.Format("{0:yyyy年MM月dd日}价格变动商品清单",(txtInputDt.SelectedDate!=DateTime.MinValue?txtInputDt.SelectedDate:DateTime.Now));
// 导出Excel
ExportFacade exportInstance = new Facade.Excel.ExportFacade();
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
exportInstance.ExportByWeb(newDt, title, title + ".xls", formatOptions, title);
}
protected void lbn_Import_Click(object sender, EventArgs e)
{
DataSet ds = orderItemByWhere();
DataTable dt=ds.Tables[0];
dt.Columns["proName"].ColumnName = "商品";
dt.Columns["quantity"].ColumnName = "数量";
dt.Columns["price"].ColumnName = "单价";
dt.Columns["unit"].ColumnName = "单位";
dt.Columns["amount"].ColumnName = "小计";
dt.Columns["orderNo"].ColumnName = "订单号";
dt.Columns["statusName"].ColumnName = "订单状态";
dt.Columns["updateStatusdt"].ColumnName = "成功日期";
dt.Columns["receiveDt"].ColumnName = "收获日期";
ExportFacade facade = new ExportFacade();
Dictionary<int,FormatStyle> formatOptions=new Dictionary<int,FormatStyle>();
formatOptions.Add(1, FormatStyle.ToFix2);
formatOptions.Add(2, FormatStyle.ToFix2);
formatOptions.Add(4, FormatStyle.ToFix2);
facade.ExportByWeb(dt, "订单数据", "订单数据.xls", formatOptions, "订单数据");
}
// 导出全部商品
protected void lbtn_ToExeclAll_Click(object sender, EventArgs e)
{
DataSet ds = logic.price.getSupplyPriceList();
if (ds.Tables.Count > 1)
{
string title = string.Format("商品当前供应价格列表【{0}】", DateTime.Now.ToShortDateString());
DataTable tb1 = ds.Tables[0];
Dictionary<string, string> sheets = new Dictionary<string, string>();
for (int i = 0; i < tb1.Rows.Count; i++)
{
sheets.Add(tb1.Rows[i][0].ToString(), tb1.Rows[i][1].ToString());
}
DataTable tb2 = ds.Tables[1];
Dictionary<string, string> columns = new Dictionary<string, string>();
columns.Add("productNo", "商品编号");
columns.Add("productName", "商品名称");
columns.Add("unit", "单位");
columns.Add("standard", "规格");
columns.Add("company", "供应商名称");
//columns.Add("sellerPrice", "供应价格");
if (cbo_hasSellerPrice.Checked)
{
columns.Add("sellerPrice", "供应价格");
}
if (cbo_hasPrice.Checked)
{
columns.Add("Price", "系统价格");
}
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(5, FormatStyle.ToFix2);
ExportFacade exportHelper = new ExportFacade();
exportHelper.ExportByWeb("businessNature='{0}'", sheets, columns, tb2, title, title + ".xls", formatOptions, title);
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
if (txtStartDt.SelectedDate != DateTime.MinValue && txtEndDt.SelectedDate != DateTime.MinValue)
{
StringBuilder where = new StringBuilder();
where.AppendFormat("companyId={0}", cboCompany.SelectedItem.Value);
where.AppendFormat(" AND DATEDIFF(DAY,receiveDt,'{0}')<=0 AND DATEDIFF(DAY,receiveDt,'{1}')>=0", txtStartDt.SelectedDate.ToString("yyyy-MM-dd"), txtEndDt.SelectedDate.ToString("yyyy-MM-dd"));
DataSet ds = DAL.DbHelper.ExecuteReDataSet("[dbo].[p_PaymentHistory_ManualExportExcel]", where.ToString());
DataTable dt = ds.Tables[0];
dt.Columns[0].ColumnName = "商品名称";
dt.Columns[1].ColumnName = "数量";
dt.Columns[2].ColumnName = "单价";
dt.Columns[3].ColumnName = "单位";
dt.Columns[4].ColumnName = "小计";
dt.Columns[5].ColumnName = "订单号";
dt.Columns[6].ColumnName = "订单状态";
dt.Columns[7].ColumnName = "交易成功日期";
dt.Columns[8].ColumnName = "收货日期";
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(1, FormatStyle.ToFix2);
formatOptions.Add(2, FormatStyle.ToFix2);
formatOptions.Add(4, FormatStyle.ToFix2);
formatOptions.Add(7, FormatStyle.DateTime);
List<int> footerSumColumns = new List<int>()
{
4
};
List<int> mergeColumnIndexs = new List<int>()
{
5,6,7,8
};
string title = string.Format("{0} {1} 至 {2} 账期订单数据", cboCompany.SelectedItem.Text, txtStartDt.SelectedDate.ToString("yyyy-MM-dd"), txtEndDt.SelectedDate.ToString("yyyy-MM-dd"));
ExportFacade facade = new ExportFacade();
HSSFWorkbook workbook = facade.InitializeWorkbook("杭州农副产品物流网络科技有限公司", "tianzh", "采购配送系统", "财务账单订单数据");
Sheet sheet1 = workbook.CreateSheet("订单详细");
facade.CreateRowHeader(workbook, sheet1, title, dt);
facade.FillRowData(workbook, sheet1, 2, dt, formatOptions, null, footerSumColumns, null);
// 小类汇总
DataTable dt2 = ds.Tables[2];
dt2.Columns[0].ColumnName = "类别";
dt2.Columns[1].ColumnName = "金额";
int startRowIndex = 2 + dt.Rows.Count + 1 + 5;
#region 创建标题行
Row titleRow = sheet1.CreateRow(startRowIndex);
titleRow.HeightInPoints = 25;
titleRow.CreateCell(0).SetCellValue("分类汇总信息");
CellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.CENTER;
Font titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = 20;
titleFont.Boldweight = 700;
titleStyle.SetFont(titleFont);
titleStyle.WrapText = true;
titleRow.GetCell(0).CellStyle = titleStyle;
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
Row headerRow = sheet1.CreateRow(startRowIndex + 1);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
Font font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dt2.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
#endregion
facade.FillRowData(workbook, sheet1, startRowIndex + 2, dt2, null, null, null, null);
facade.ExportByWeb(workbook, title, title + ".xls");
}
}
// 导出当前列表商品
protected void lbtn_ToExecl_Click(object sender, EventArgs e)
{
StringBuilder where = new StringBuilder("1=1");
if (!string.IsNullOrEmpty(drp_CategoryRoot.SelectedValue))
where.AppendFormat(" and parentId={0}", drp_CategoryRoot.SelectedValue);
if (!string.IsNullOrEmpty(drp_CategorySub.SelectedValue))
where.AppendFormat(" and categoryId={0}", drp_CategorySub.SelectedValue);
if (!string.IsNullOrEmpty(ddl_BusinessNature.SelectedValue))
where.AppendFormat(" and businessNature='{0}'", ddl_BusinessNature.SelectedValue);
//如果选了[大类]和[档次]两项,导出某一个大类的Excel,其中的小类按sheet来分类
if (!string.IsNullOrEmpty(drp_CategoryRoot.SelectedValue)&&!string.IsNullOrEmpty(ddl_BusinessNature.SelectedValue))
{
string cols = "categoryId,categoryName,productNo,productName,unit,standard,company";
string bystr = "productId";
if (cbo_hasSellerPrice.Checked)
{
cols += ",sellerPrice";
}
if (cbo_hasPrice.Checked)
{
cols += ",Price";
bystr+=",price";
}
DataTable supplyPriceTab = logic.price.selectSupplyPriceListFilterCols(cols, where.ToString(), bystr);
Dictionary<string, string> sheets = GetCategorySheetList(supplyPriceTab);
string title = string.Format(drp_CategoryRoot.SelectedItem.Text+"商品信息列表【{0}】", DateTime.Now.ToShortDateString());
Dictionary<string, string> columns = new Dictionary<string, string>();
columns.Add("categoryName", "所属小类");
columns.Add("productNo", "商品编号");
columns.Add("productName", "商品名称");
columns.Add("unit", "单位");
columns.Add("standard", "规格");
columns.Add("company", "供应商名称");
if (cbo_hasSellerPrice.Checked)
{
columns.Add("sellerPrice", "供应价格");
}
if (cbo_hasPrice.Checked)
{
columns.Add("Price", "系统价格");
}
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(5, FormatStyle.ToFix2);
ExportFacade exportHelper = new ExportFacade();
exportHelper.ExportByWeb("categoryId='{0}'", sheets, columns, supplyPriceTab, title, title + ".xls", formatOptions, title);
}
else
{
string cols = " productNo,businessNature,businessNatureName,productName,unit,standard,company";
string bystr = "productId";
if (cbo_hasSellerPrice.Checked)
{
cols += ",sellerPrice";
}
if (cbo_hasPrice.Checked)
{
cols += ",Price";
bystr += ",price";
}
DataTable supplyPriceTab = logic.price.selectSupplyPriceListFilterCols(cols, where.ToString(),bystr);
Dictionary<string, string> sheets = GetSheetList(supplyPriceTab);
string title = string.Format("商品当前供应价格列表【{0}】", DateTime.Now.ToShortDateString());
Dictionary<string, string> columns = new Dictionary<string, string>();
columns.Add("productNo", "商品编号");
columns.Add("productName", "商品名称");
columns.Add("unit", "单位");
columns.Add("standard", "规格");
columns.Add("company", "供应商名称");
if (cbo_hasSellerPrice.Checked)
{
columns.Add("sellerPrice", "供应价格");
}
if (cbo_hasPrice.Checked)
{
columns.Add("Price", "系统价格");
}
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(5, FormatStyle.ToFix2);
ExportFacade exportHelper = new ExportFacade();
exportHelper.ExportByWeb("businessNature='{0}'", sheets, columns, supplyPriceTab, title, title + ".xls", formatOptions, title);
}
}
protected void lbn_Import_Click(object sender, EventArgs e)
{
DataTable dt = logic.sysPriceModulus.listByBusinessNature(ddl_businessNature.SelectedValue, Convert.ToInt32(drp_parent.SelectedValue));
dt.Columns.Remove("categoryid");//删除列
dt.Columns.Remove("parentid"); //删除列
dt.Columns["parentCategory"].ColumnName = "大类";
dt.Columns["category"].ColumnName = "小类";
dt.Columns["sysAutoAdd"].ColumnName = "系统加价体系";
string title = ddl_businessNature.SelectedItem.Text.Trim() + "档次的系统加价体系";
ExportFacade exportInstance = new Facade.Excel.ExportFacade();
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
exportInstance.ExportByWeb(dt, title, title + ".xls", formatOptions, title);
}
protected void lbn_Import_Click(object sender, EventArgs e)
{
DataSet ds = orderItemByWhere();
DataTable dt = ds.Tables[0];
dt.Columns[0].ColumnName = "商品名称";
dt.Columns[1].ColumnName = "数量";
dt.Columns[2].ColumnName = "单价";
dt.Columns[3].ColumnName = "单位";
dt.Columns[4].ColumnName = "小计";
dt.Columns[5].ColumnName = "订单号";
dt.Columns[6].ColumnName = "订单状态";
dt.Columns[7].ColumnName = "交易成功日期";
dt.Columns[8].ColumnName = "收货日期";
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(1, FormatStyle.ToFix2);
formatOptions.Add(2, FormatStyle.ToFix2);
formatOptions.Add(4, FormatStyle.ToFix2);
formatOptions.Add(7, FormatStyle.DateTime);
List<int> footerSumColumns = new List<int>() { 4 };
List<int> mergeColumnIndexs = new List<int>() { 5, 6, 7, 8 };
string title = string.Format("{0} {1} 至 {2} 账期订单数据",logic.company.getById(Convert.ToInt32(hid_companyId.Value)).Company, hid_startDt.Value, hid_endDt.Value);
ExportFacade facade = new ExportFacade();
HSSFWorkbook workbook = facade.InitializeWorkbook("杭州农副产品物流网络科技有限公司", "tianzh", "采购配送系统", "财务账单订单数据");
Sheet sheet1 = workbook.CreateSheet("订单详细");
facade.CreateRowHeader(workbook, sheet1, title, dt);
facade.FillRowData(workbook, sheet1, 2, dt, formatOptions, null, footerSumColumns, null);
// 小类汇总
DataTable dt2 = ds.Tables[2];
dt2.Columns[0].ColumnName = "类别";
dt2.Columns[1].ColumnName = "金额";
int startRowIndex=2 + dt.Rows.Count + 1 + 5;
#region 创建标题行
Row titleRow = sheet1.CreateRow(startRowIndex);
titleRow.HeightInPoints = 25;
titleRow.CreateCell(0).SetCellValue("分类汇总信息");
CellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.CENTER;
Font titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = 20;
titleFont.Boldweight = 700;
titleStyle.SetFont(titleFont);
titleStyle.WrapText = true;
titleRow.GetCell(0).CellStyle = titleStyle;
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
Row headerRow = sheet1.CreateRow(startRowIndex+1);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
Font font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dt2.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
#endregion
facade.FillRowData(workbook, sheet1, startRowIndex + 2, dt2, null, null, null, null);
facade.ExportByWeb(workbook, title, title + ".xls");
}
protected void link_Export_Detail_Click(object sender,EventArgs e)
{
StringBuilder outerWhere = new StringBuilder(" 1=1");
if (!string.IsNullOrEmpty(drp_CategoryRoot.SelectedValue))
outerWhere.AppendFormat(" and t1.categoryParentId={0}", drp_CategoryRoot.SelectedValue);
if (!string.IsNullOrEmpty(drp_CategorySub.SelectedValue))
outerWhere.AppendFormat(" and t1.categoryId={0}", drp_CategorySub.SelectedValue);
StringBuilder innerWhere = new StringBuilder(" 1=1 and [status]='4' ");
if (ext_StartInputDt.SelectedDate != DateTime.MinValue)
innerWhere.AppendFormat(" and DATEDIFF(d,[inputDt],'{0}')<=0", ext_StartInputDt.SelectedDate);
if (ext_EndInputDt.SelectedDate != DateTime.MinValue)
innerWhere.AppendFormat(" and DATEDIFF(d,[inputDt],'{0}')>=0", ext_EndInputDt.SelectedDate);
if (drp_Company.SelectedValue.ToString() != "0")
innerWhere.AppendFormat(" and companyId = {0}", Convert.ToInt32(drp_Company.SelectedValue));
DateTime? startDate = null;
DateTime? endDate = null;
if (ext_StartInputDt.SelectedDate != DateTime.MinValue)
startDate = ext_StartInputDt.SelectedDate;
if (ext_EndInputDt.SelectedDate != DateTime.MinValue)
endDate = ext_EndInputDt.SelectedDate;
// 查询数据
//DataTable dt = logic.orderItem.listWithPurchaseInfo(parentid, categoryid, productno, productname, startDate, endDate);
DataTable dt = logic.orderItem.listWithPurchaseInfoOptimize(innerWhere.ToString(), outerWhere.ToString());
dt.Columns["categoryParentName"].ColumnName = "大类";
dt.Columns["categoryName"].ColumnName = "小类";
dt.Columns["productNo"].ColumnName = "商品编号";
dt.Columns["productName"].ColumnName = "商品名称";
dt.Columns["unit"].ColumnName = "单位";
dt.Columns["standard"].ColumnName = "规格";
dt.Columns["sumQuantity"].ColumnName = "总数量";
dt.Columns["sumSellerPrice"].ColumnName = "总进价";
dt.Columns["sumAmount"].ColumnName = "总出价";
dt.Columns["gpm"].ColumnName = "毛利";
StringBuilder title = new StringBuilder();
if (drp_Company.SelectedValue != "0")
title.AppendFormat("{0} ", drp_Company.SelectedItem.Text);
else
title.Append("平台");
if (ext_StartInputDt.SelectedDate != DateTime.MinValue && ext_EndInputDt.SelectedDate != DateTime.MinValue)
title.AppendFormat("{0:yyyy-MM-dd}至{1:yyyy-MM-dd}", ext_StartInputDt.SelectedDate, ext_EndInputDt.SelectedDate);
if (!string.IsNullOrEmpty(drp_CategorySub.SelectedValue))
title.AppendFormat("{0}", drp_CategorySub.SelectedItem.Text);
else if (!string.IsNullOrEmpty(drp_CategoryRoot.SelectedValue))
title.AppendFormat("{0}", drp_CategoryRoot.SelectedItem.Text);
else
title.Append("平台");
title.Append("商品销售统计");
// 导出Excel
ExportFacade exportInstance = new Facade.Excel.ExportFacade();
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(7, FormatStyle.ToFix2);
formatOptions.Add(8, FormatStyle.ToFix2);
formatOptions.Add(9, FormatStyle.ToFix2);
formatOptions.Add(10, FormatStyle.ToFix2);
exportInstance.ExportByWeb(dt, title.ToString(), title.ToString() + ".xls", formatOptions, title.ToString());
}
protected void link_Export_SellerRange_Click(object sender,EventArgs e)
{
DataSet ds = logic.sellerRange.exportBySellerId(companyId);
string companyName=logic.company.getById(companyId).Company;
string title = string.Format("{0}供应范围【{1}】", companyName, DateTime.Now.ToShortDateString());
if (ds.Tables.Count > 1)
{
DataTable tb1 = ds.Tables[0];
Dictionary<string, string> sheets = new Dictionary<string, string>();
for (int i = 0; i < tb1.Rows.Count; i++)
{
sheets.Add(tb1.Rows[i][0].ToString(), tb1.Rows[i][1].ToString());
}
DataTable tb2 = ds.Tables[1];
Dictionary<string, string> columns = new Dictionary<string, string>();
columns.Add("productNo", "商品编号");
columns.Add("businessNatureName", "所属级别");
columns.Add("productName", "商品名称");
columns.Add("unit", "单位");
columns.Add("standard", "规格");
ExportFacade exportHelper = new ExportFacade();
exportHelper.ExportByWeb("categoryId={0}", sheets, columns, tb2, title, title + ".xls", null, title);
}
}
// 导出详细信息
protected void link_Export_Detail_Click(object sender,EventArgs e)
{
DataTable dt = ViewState["CacheDetail"] as DataTable;
if (dt.Rows.Count>0)
{
// 筛选表中部分列
DataTable newTab=dt.DefaultView.ToTable(false, new string[] {"proName","sellerName","sellerPrice","price","inputDt","inputName"});
newTab.Columns[0].ColumnName = "商品名称";
newTab.Columns[1].ColumnName = "供应商名称";
newTab.Columns[2].ColumnName = "供应价";
newTab.Columns[3].ColumnName = "采购价";
newTab.Columns[4].ColumnName = "创建日期";
newTab.Columns[5].ColumnName = "创建人";
string title = lbl_buyerName.Text;
// 导出Excel
ExportFacade exportInstance = new Facade.Excel.ExportFacade();
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(2, FormatStyle.ToFix2);
formatOptions.Add(3, FormatStyle.ToFix2);
exportInstance.ExportByWeb(newTab, title, title + ".xls", formatOptions, title);
}
}
/// <summary>
/// 当前列表导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void lbt_importall_Click(object sender, EventArgs e)
{
string TimeRange="";
if (ext_StartInputDt.SelectedDate != DateTime.MinValue && ext_EndInputDt.SelectedDate != DateTime.MinValue)
{
TimeRange = ext_StartInputDt.SelectedDate.ToShortDateString() + "至" + ext_EndInputDt.SelectedDate.ToShortDateString();
}
else if (ext_StartInputDt.SelectedDate != DateTime.MinValue && ext_EndInputDt.SelectedDate == DateTime.MinValue)
{
TimeRange = ext_StartInputDt.SelectedDate.ToShortDateString() + "至—";
}
else if (ext_StartInputDt.SelectedDate == DateTime.MinValue && ext_EndInputDt.SelectedDate != DateTime.MinValue)
{
TimeRange = "—至" + ext_EndInputDt.SelectedDate.ToShortDateString();
}
else
{
TimeRange = "";
}
DataSet ds = logic.complaint.outputExcel(strWhere());
DataTable dtlist = ds.Tables[0], dtcompany = ds.Tables[1], dtcomplaint = ds.Tables[2], dtcategory = ds.Tables[3], dtdepartment = ds.Tables[4];
dtlist.Columns["complaintdt"].ColumnName = "日期";
dtlist.Columns["buyername"].ColumnName = "客户名称";
dtlist.Columns["productname"].ColumnName = "投诉产品";
dtlist.Columns["complaintname"].ColumnName = "投诉类别";
dtlist.Columns["department"].ColumnName = "责任部门";
dtlist.Columns["responsibler"].ColumnName = "责任人";
dtlist.Columns["sellername"].ColumnName = "责任供应商";
dtlist.Columns["levelname"].ColumnName = "严重级别";
dtlist.Columns["result"].ColumnName = "处理结果";
dtlist.Columns["inputname"].ColumnName = "录入人";
dtlist.Columns["remarks"].ColumnName = "投诉问题详情";
dtlist.Columns.Remove("buyerid");
dtlist.Columns.Remove("sellerid");
ExportFacade facade = new ExportFacade();
HSSFWorkbook workbook = facade.InitializeWorkbook("杭州农副产品物流网络有限公司", logic.sysAdmin.AdminID.ToString(), "采购配送系统", "投诉管理");
Sheet sheet1 = workbook.CreateSheet("投诉详细");
facade.CreateRowHeader(workbook, sheet1, TimeRange + " 投诉列表", dtlist);
facade.FillRowData(workbook, sheet1, 2, dtlist, null, null, null, null);
Sheet sheet2 = workbook.CreateSheet("客户投诉");
facade.CreateRowHeader(workbook, sheet2, TimeRange + " 客户投诉情况", dtcompany);
facade.FillRowData(workbook, sheet2, 2, dtcompany, null, null, null, null);
Sheet sheet3 = workbook.CreateSheet("投诉汇总");
facade.CreateRowHeader(workbook, sheet3, TimeRange + " 投诉问题汇总", dtcomplaint);
facade.FillRowData(workbook, sheet3, 2, dtcomplaint, null, null, null, null);
#region 小类投诉情况
GraphPane graphpane = new GraphPane();
graphpane.Title.Text = "小类投诉情况";
graphpane.Title.FontSpec.Size = 12f;
graphpane.XAxis.Title.Text = "小类";
graphpane.XAxis.Title.FontSpec.Size = 11f;
graphpane.YAxis.Title.Text = ChangeStr("投诉数量");
graphpane.YAxis.Title.FontSpec.Angle = 90;
graphpane.YAxis.Title.FontSpec.Size = 11f;
graphpane.XAxis.IsVisible = true;
graphpane.YAxis.IsVisible = true;
List<string> category=new List<string>();
List<double> cnum = new List<double>();
int maxcnum = 2;
foreach (DataRow dr in dtcategory.Rows)
{
if(Convert.ToInt32( dr[1].ToString())>maxcnum)
maxcnum=Convert.ToInt32( dr[1].ToString());
category.Add(ChangeStr( dr[0].ToString()));
cnum.Add(Convert.ToDouble(dr[1].ToString()));
}
BarItem baritem = graphpane.AddBar(null,null,cnum.ToArray(), Color.Red);
baritem.Bar.Fill = new Fill(Color.Red, Color.White, Color.Red);
BarItem.CreateBarLabels(graphpane, false, "f0");
graphpane.XAxis.Scale.TextLabels = category.ToArray();
graphpane.XAxis.Scale.Max = category.ToArray().Length+1;
graphpane.XAxis.Scale.MajorStep = 1;
graphpane.XAxis.MinorTic.Size = 0;
graphpane.XAxis.MajorTic.Size = 0;
graphpane.XAxis.Cross = 0;
graphpane.XAxis.Scale.FontSpec.Size = 10f;
graphpane.XAxis.Scale.FontSpec.Family = "宋体";
graphpane.XAxis.Type = AxisType.Text;
graphpane.XAxis.MajorTic.IsOutside = false;
graphpane.XAxis.MajorTic.IsOpposite = false;
graphpane.YAxis.Scale.Max = maxcnum+2;
graphpane.YAxis.MinorTic.Size = 0;
graphpane.YAxis.MinorGrid.DashOff = 0;
graphpane.YAxis.Scale.MajorStep = 1;
graphpane.YAxis.MajorTic.IsOpposite = false;
graphpane.YAxis.MajorTic.IsOutside = false;
graphpane.Chart.Fill = new Fill(Color.White, Color.FromArgb(255, 255, 166), 90F);
graphpane.Fill = new Fill(Color.White, Color.FromArgb(250, 250, 255),45.0f);
graphpane.Fill.IsScaled = true;
MemoryStream ms = new MemoryStream();
//zgc.GetImage().Save(ms,System.Drawing.Imaging.ImageFormat.Jpeg);
Bitmap map = graphpane.GetImage(750,550,17);
map.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] picbyte = ms.ToArray();
int index = workbook.AddPicture(picbyte, NPOI.SS.UserModel.PictureType.JPEG);
Sheet sheet4 = workbook.CreateSheet("小类投诉");
facade.CreateRowHeader(workbook, sheet4, TimeRange + " 小类投诉情况", dtcategory);
facade.FillRowData(workbook, sheet4, 2, dtcategory, null, null, null, null);
HSSFPatriarch hssfpatriarch = (HSSFPatriarch)sheet4.CreateDrawingPatriarch();
HSSFClientAnchor hssfanchor = new HSSFClientAnchor(0, 0, 0, 0, 4, 1, 18, 28);
HSSFPicture hssfpic = (HSSFPicture)hssfpatriarch.CreatePicture(hssfanchor, index);
#endregion
#region 部门投诉情况
GraphPane gp2 = new GraphPane();
gp2.Title.Text = "部门投诉情况";
gp2.XAxis.IsVisible = false;
gp2.YAxis.IsVisible = false;
gp2.Title.FontSpec.Size = 12f;
gp2.Fill = new Fill(Color.White);
gp2.Chart.Fill.Type = FillType.None;
gp2.Legend.Position = LegendPos.Float;
gp2.Legend.Location = new Location(0.95f, 0.08f, CoordType.PaneFraction, AlignH.Right, AlignV.Top);
gp2.Legend.FontSpec.Size = 10f;
gp2.Legend.IsHStack = false;
List<double> comnum=new List<double>();
List<string> dname=new List<string>();
foreach(DataRow dr in dtdepartment.Rows )
{
gp2.AddPieSlice(Convert.ToDouble(dr[1].ToString()), GetRandomColor(), 0, dr[0].ToString()+" ("+dr[1].ToString()+")").LabelType=PieLabelType.Percent;
}
Bitmap bitmap = gp2.GetImage(700, 700, 14);
MemoryStream mstream = new MemoryStream();
bitmap.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] buffer = mstream.ToArray();
int picindex = workbook.AddPicture(buffer, NPOI.SS.UserModel.PictureType.JPEG);
Sheet sheet5 = workbook.CreateSheet("部门投诉");
facade.CreateRowHeader(workbook, sheet5, TimeRange + " 责任部门投诉情况", dtdepartment);
facade.FillRowData(workbook, sheet5, 2, dtdepartment, null, null, null, null);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet5.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 4, 1, 15, 34);
HSSFPicture pic = (HSSFPicture)patriarch.CreatePicture(anchor, picindex);
#endregion
facade.ExportByWeb(workbook, TimeRange.ToString() + "采购配送系统投诉统计", TimeRange.ToString() + "采购配送系统投诉统计.xls");
}
// 导出企业周报价
protected void lbl_ExportToExcel_Click(object sender, EventArgs e)
{
// 查询数据
DataTable dt = logic.buyerWeeklyPrice.listWithCompanyWeek(bpwdid);
string title = string.Format("{0}({1}-{2})周报价单", lblWeek.Text, lblStartTime.Text, lblEndTime.Text);
// 导出Excel
ExportFacade exportInstance = new Facade.Excel.ExportFacade();
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
exportInstance.ExportByWeb(dt, title, title + ".xls", formatOptions, title);
}
// 导出Excel
protected void link_Export_ProductSale_Click(object sender, EventArgs e)
{
if (ViewState["ProductStatCacheData"]!=null)
{
StringBuilder title=new StringBuilder();
if (txtStartOrderDt.SelectedDate != DateTime.MinValue && txtEndOrderDt.SelectedDate != DateTime.MinValue)
title.AppendFormat("{0:yyyy-MM-dd}至{1:yyyy-MM-dd}",txtStartOrderDt.SelectedDate,txtEndOrderDt.SelectedDate);
if (!string.IsNullOrEmpty(ddlCategorySub.SelectedValue))
title.AppendFormat("{0}", ddlCategorySub.SelectedItem.Text);
else if (!string.IsNullOrEmpty(ddlCategoryRoot.SelectedValue))
title.AppendFormat("{0}", ddlCategoryRoot.SelectedItem.Text);
else
title.Append("平台");
title.AppendFormat("{0}前{1}名商品",ddlFields.SelectedItem.Text,txtTopNum.Text);
DataTable dt = ViewState["ProductStatCacheData"] as DataTable;
dt.Columns[0].ColumnName = "商品名称";
dt.Columns[1].ColumnName = "订购量";
dt.Columns[2].ColumnName = "订购次数";
dt.Columns[3].ColumnName = "销售总额";
// 导出Excel
ExportFacade exportInstance = new Facade.Excel.ExportFacade();
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(1, FormatStyle.ToFix2);
formatOptions.Add(3, FormatStyle.ToFix2);
exportInstance.ExportByWeb(dt, title.ToString(), title.ToString() + ".xls", formatOptions, title.ToString());
}
}
protected void linkExport_Click(object sender, EventArgs e)
{
DataTable dt = logic.product.listWithPriceByBuyer(companyId);
string fileName = ddlCompany2.SelectedItem.Text + DateTime.Now.ToShortDateString() + "的价格表";
ExportFacade facadeIntance = new ExportFacade();
Dictionary<int, FormatStyle> formatOptions = new Dictionary<int, FormatStyle>();
formatOptions.Add(4, FormatStyle.ToFix2);
facadeIntance.ExportByWeb(dt,fileName,fileName+".xls",formatOptions,fileName);
}