async void OutputPPT()
{
double limit = 0;
if (!double.TryParse(filterThresholdTextBox.Text, out limit))
{
toolStripStatusLabel.Text = "请输入正确的数字";
return;
}
SaveFileDialog fd = new SaveFileDialog();
fd.InitialDirectory = "d:/";
fd.Title = "请选择输出PPT的文件路径";
fd.Filter = "PowerPoint files(*.pptx)|*.pptx;";
if (fd.ShowDialog() == DialogResult.OK)
{
outputPPTFilename = fd.FileName;
}
else
{
return;
}
toolStripStatusLabel.Text = "正在过滤数据";
sp.RunFilter(ShiftingReport.GetLessThanFilter(limit));
sp.RunFilter(ShiftingReport.nameFilter);
toolStripStatusLabel.Text = "正在导出PPT";
ReportToChart tc = new ReportToChart(outputPPTFilename);
await tc.OutputAsync(sp, outputPPTFilename);
}
public Task <int> OutputAsync(ShiftingReport report, string filename)
{
return(Task.Run <int>(
() =>
{
Output(report, filename);
return 1;
}));
}
private void openDataExcelButton_Click(object sender, EventArgs e)
{
OpenFileDialog fd = new OpenFileDialog();
fd.Multiselect = false;
fd.CheckFileExists = true;
fd.RestoreDirectory = true;
fd.InitialDirectory = "d:/粲然的程序";
if (fd.ShowDialog() == DialogResult.OK)
{
sp = new ShiftingReport(fd.FileName);
PrepareData();
}
;
}
async void OutputExcel()
{
SaveFileDialog fd = new SaveFileDialog();
fd.InitialDirectory = "d:/";
fd.Title = "请选择输出Excel的文件路径";
fd.Filter = "PowerPoint files(*.xlsx)|*.xlsx;";
if (fd.ShowDialog() == DialogResult.OK)
{
double limit = 0;
if (!double.TryParse(filterThresholdTextBox.Text, out limit))
{
toolStripStatusLabel.Text = "请输入正确的数字";
return;
}
toolStripStatusLabel.Text = "正在过滤数据";
sp.RunFilter(ShiftingReport.GetLessThanFilter(limit));
sp.RunFilter(ShiftingReport.nameFilter);
toolStripStatusLabel.Text = "正在导出Excel";
await sp.ExportExcelGroupByRegionAsync("d:/test.xlsx");
toolStripStatusLabel.Text = "导出成功";
}
}
public void Output(ShiftingReport report, string filename)
{
ppt = new PowerPoint.Application();
ppt.Visible = Office.MsoTriState.msoTrue;
PowerPoint.Presentation targetPres = ppt.Presentations.Add();
targetPres.SaveAs(filename);
targetPres.PageSetup.SlideSize = (PowerPoint.PpSlideSizeType.ppSlideSizeOnScreen);
int index = 0;
foreach (string region in report.Regions)
{
List <ReportTable> tables = (from r in report.ReportTables
where r.region == region
select r).ToList();
List <string> hint = new List <string>()
{
"光明畅优", "光明健能", "光明E+", "蒙牛", "伊利",
};
tables.Sort(new Comparison <ReportTable>((x, y) =>
{
int xidx = hint.FindIndex(s => s.Equals(x.vendor));
int yidx = hint.FindIndex(s => s.Equals(y.vendor));
if (xidx < 0)
{
xidx = hint.Count;
hint.Add(x.vendor);
}
if (yidx < 0)
{
yidx = hint.Count;
hint.Add(y.vendor);
}
return(xidx > yidx ? 1 : -1);
}));
foreach (ReportTable table in tables)
{
targetPres.Slides.InsertFromFile(tmplPPTFilename, index * 2, 1, 2);
PowerPoint.Slide slideText = targetPres.Slides[index * 2 + 1];
PowerPoint.Slide slideChart = targetPres.Slides[index * 2 + 2];
ReplaceTextInSlide(slideText, "{region}", table.region);
ReplaceTextInSlide(slideText, "{vendor}", table.vendor);
ReplaceTextInSlide(slideChart, "{region}", table.region);
ReplaceTextInSlide(slideChart, "{vendor}", table.vendor);
BuildChart(table, slideChart.SlideNumber, 2, row =>
{
Dictionary <string, string> dict = new Dictionary <string, string>()
{
{ "shiftingtotal", "品牌转换" },
{ "retainedbuyers", "原有消费者购买增加/减少" },
{ "new/lostbuyers", "购买清单中增加/删除品牌" },
{ "nonbuyers", "新增/流失品类消费者" },
};
string key = row[0].Trim().Replace(" ", "").ToLower();
string value = null;
if (dict.TryGetValue(key, out value))
{
string[] newrow = new string[row.Length];
Array.Copy(row, newrow, row.Length);
newrow[0] = value;
return(newrow);
}
return(null);
});
BuildChart(table, slideChart.SlideNumber, 4, row =>
{
string key = row[0].Trim().Replace(" ", "");
Regex cn = new Regex("[\u4e00-\u9fa5]+");
if (cn.IsMatch(key))
{
string[] newrow = new string[row.Length];
Array.Copy(row, newrow, row.Length);
newrow[0] = key;
return(newrow);
}
return(null);
});
targetPres.Save();
index += 1;
}
}
}
