/// <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 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 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");
}