static HSSFWorkbook hssfworkbook;
#region 查询
protected void Button2_Click(object sender, EventArgs e)
{
if (txtDate1.Text == "")
{
Response.Write("<script>alert('开始时间不能为空!');</script>");
return;
}
if (txtDate2.Text == "")
{
Response.Write("<script>alert('结束时间不能为空!');</script>");
return;
}
if (!ShowPic())//没有数据
{
return;
}
chart1.SaveImage(this.Server.MapPath("images/StatisticsTrasfergz_Task1.jpg"), ChartImageFormat.Jpeg);
chart2.SaveImage(this.Server.MapPath("images/StatisticsTrasfergz_Task2.jpg"), ChartImageFormat.Jpeg);
ShowPic();
//chart2.Series[0]["PieLabelStyle"] = "Outside";//将文字移到外侧
//chart2.Series[0]["PieLineColor"] = "Black";//绘制黑色的连线。
//chart2.Series[0].Points.DataBindXY(xData, yData);
//chart2.Series[0].ChartType = SeriesChartType.Pie;
//chart2.Series[0].IsVisibleInLegend = true;
//chart2.Series[0].LegendText = "#VALX #VAL 个";
//chart2.Series[0].Label = "#PERCENT -- #VALY";
}
private bool ShowPic()
{
string date1 = txtDate1.Text + " 00:00:00";
string date2 = txtDate2.Text + " 23:59:59";
List<string> xData = new List<string>() { "待处理", "已归档" };
DB db = new DB();
string sql = "select count1,count2 from (select count(1) as 'count1' from dbo.Trasfergz_Task where btime between '" + date1 + "' and '" + date2 + "' and state in ('已归档')) t1,"
+ "(select count(1) as count2 from dbo.Trasfergz_Task where btime between '" + date1 + "' and '" + date2 + "' and state not in ('已归档','已作废')) t2";
DataTable dt = db.getTable(sql);
List<int> yData = new List<int>();//待处理的总数、归档的总数
List<int> yData2 = new List<int>();//待处理的总数、归档的百分百
if (dt.Rows.Count == 0)//没有数据
{
Response.Write("<script>alert('该时间段没有数据!');</script>");
return false;
}
yData.Add(int.Parse(dt.Rows[0]["count1"].ToString()));
yData.Add(int.Parse(dt.Rows[0]["count2"].ToString()));
double count = double.Parse(dt.Rows[0]["count1"].ToString()) + double.Parse(dt.Rows[0]["count2"].ToString());
double per1 = double.Parse(dt.Rows[0]["count1"].ToString()) / count;
double per2 = double.Parse(dt.Rows[0]["count2"].ToString()) / count;
int b1 = int.Parse((Math.Round(per1, 2) * 100).ToString());//保留两位小数
int b2 = int.Parse((Math.Round(per2, 2) * 100).ToString());//保留两位小数
yData2.Add(b1);
yData2.Add(b2);
btnOut.Visible = true;
chart1.Visible = true;
chart2.Visible = true;
chart1.Series[0]["PieLabelStyle"] = "Outside";//将文字移到外侧
chart1.Series[0]["PieLineColor"] = "Black";//绘制黑色的连线。
chart1.Series[0].Points.DataBindXY(xData, yData);
chart1.Series[0].ChartType = SeriesChartType.Pie;
chart1.Series[0].IsVisibleInLegend = true;
chart1.Series[0].LegendText = "#VALX #VAL 个";//显示单位
// chart1.Series[0].Label = "#PERCENT -- #VALY";//显示百分百
chart2.Series[0]["PieLabelStyle"] = "Outside";//将文字移到外侧
//chart2.Series[0]["PieLabelStyle"] = "Inside";//将文字移到内侧
chart2.Series[0]["PieLineColor"] = "Black";//绘制黑色的连线。
chart2.Series[0].Points.DataBindXY(xData, yData2);
chart2.Series[0].ChartType = SeriesChartType.Pie;
chart2.Series[0].IsVisibleInLegend = true;
chart2.Series[0].LegendText = "#VALX #VAL %";
// chart2.Series[0].Label = "#VAL";
//chart2.Series[0].IsVisibleInLegend = true;
//chart2.Series[0].Label = "#PERCENT -- #VALY";
return true;
}
#endregion
#region 导出数据
protected void btnOut_Click(object sender, EventArgs e)
{
保存图片
//chart1.SaveImage(this.Server.MapPath("images/TongJiGL.jpg"), ChartImageFormat.Jpeg);
Button2.Enabled = false;
InitializeWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("PictureSheet");//创建Excel中表单名称
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(500, 200, 0, 0, 2, 3, 10,18);//最后两个参数调节图片在Excel中大小.3:图片首部的行数,18图片底部的行数,2表示图片离最左边的行数
anchor.AnchorType = 2;
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(this.Server.MapPath("images/StatisticsTrasfergz_Task1.jpg"), hssfworkbook));
anchor = new HSSFClientAnchor(500, 200,0, 0,2,25,10,41);//图片的起始为25行,结束为41行。2表示离最左边2行
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
HSSFPicture picture2 = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(this.Server.MapPath("images/StatisticsTrasfergz_Task2.jpg"), hssfworkbook));
// picture.Resize();//保存图片原始大小
// picture2.Resize();
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
picture2.LineStyle = LineStyle.DashDotGel;
WriteToFile();
ShowPic();
Button2.Enabled = true;
}
#endregion
void WriteToFile()
{
//Write the stream data of workbook to the root directory
try
{
FileStream file = new FileStream(@"C:\Users\Administrator\Desktop\StatisticsTrasfergz_Task.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}
static void InitializeWorkbook()
{
hssfworkbook = new HSSFWorkbook();
//create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
}
public static int LoadImage(string path, HSSFWorkbook wb)
{
FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
byte[] buffer = new byte[file.Length];
file.Read(buffer, 0, (int)file.Length);
return wb.AddPicture(buffer, PictureType.JPEG);
}