public static void PasteFromExcel(PowerPoint.Table pptTable)
{
string clipboardText = Clipboard.GetText();
char[] newlineChar = { '\n' };
char[] tabChar = { '\t' };
int selectedTableRow = 0;
int selectedTableCol = 0;
// Get the currently selected row and column
for (int iRow = 1; iRow <= pptTable.Rows.Count; iRow++)
{
for (int iCol = 1; iCol <= pptTable.Columns.Count; iCol++)
{
if (pptTable.Cell(iRow, iCol).Selected)
{
selectedTableRow = iRow;
selectedTableCol = iCol;
// Return from the nested loop
goto nextStep;
}
}
}
nextStep:
// Take out '\r' and extra newline char from clipboar data
clipboardText = clipboardText.Replace("\r", "").TrimEnd(newlineChar);
// Get number of rows and cols in clipboard
int clipboardRowCount = clipboardText.Split(newlineChar).Count();
int clipboardColCount = clipboardText.Split(newlineChar)[0].Split(tabChar).Count();
// Check that the data will fit into the table given the currently selected cell
if (selectedTableRow + clipboardRowCount - 1 > pptTable.Rows.Count ||
selectedTableCol + clipboardColCount - 1 > pptTable.Columns.Count)
{
System.Windows.Forms.MessageBox.Show(
"Your copied data would not fit into this table.\nPlease make table bigger or select another cell.");
return;
}
// Create the multidimensional data array for clipboard data
//string[,] clipboardTable = new string[clipboardRowCount, clipboardColCount];
// Insert clipboard data into the table
string[] clipboardRows = clipboardText.Split(newlineChar);
string[] clipboardCells;
for (int iRow = 0; iRow < clipboardRowCount; iRow++)
{
clipboardCells = clipboardRows[iRow].Split(tabChar);
for (int iCol = 0; iCol < clipboardColCount; iCol++)
{
pptTable.Cell(selectedTableRow + iRow, selectedTableCol + iCol).Shape.TextFrame.TextRange.Text =
clipboardCells[iCol];
}
}
}
private void DrawChart(List <string> dateseries, PowerPointInterop.Table table)
{
#region copy
var chartShape = slide.Shapes.AddChart2(Type: XlChartType.xlColumnClustered, Left: 500, Top: 120, Width: 400.0f, Height: 200.0f);
var chart = chartShape.Chart;
chart.ShowDataLabelsOverMaximum = true;
//input data
chart.ChartData.Activate();
ExcelInterop.Workbook workbook = chart.ChartData.Workbook;
ExcelInterop.Worksheet sheet = chart.ChartData.Workbook.Worksheets["Sheet1"];
sheet.Cells.Clear();
ExcelInterop.Range range;
object[] objHeaders = { "时间", "项目库", "维护库" };
range = sheet.get_Range("A1", "C1");
range.Value = objHeaders;
int num = dateseries.Count();
var data = new object[num, 3];
foreach (int n in Enumerable.Range(0, num))
{
data[n, 0] = dateseries[n];
data[n, 1] = Convert.ToInt32(table.Cell(3 + n, 2).Shape.TextFrame.TextRange.Text);
data[n, 2] = Convert.ToInt32(table.Cell(3 + n, 4).Shape.TextFrame.TextRange.Text);
}
range = sheet.get_Range("A2", "C" + (num + 1));
range.Value = data;
//sheet.get_Range("B1").Value = title;
chart.SetSourceData("'Sheet1'!$A$2:$C$" + (num + 1));
chart.SeriesCollection(1).Name = "项目库";
chart.SeriesCollection(2).Name = "维护库";
chart.HasTitle = true;
chart.ApplyDataLabels(PowerPointInterop.XlDataLabelsType.xlDataLabelsShowValue);
chart.ChartTitle.Text = "项目库和维护库BUG个数分布";
workbook.Close();
#endregion copy
}
public async Task <bool> WriteData(String strID)
{
PowerPoint.Shapes shapes = Globals.ThisAddIn.Application.ActivePresentation.Slides[1].Shapes;
try
{
JObject jsonData = await RequestData.GetPredictData(strID);
JObject dataObj = jsonData["data"].ToObject <JObject>();
PowerPoint.Shape shape = PPTAPI.getShape(shapes, "Label_1");
if (shape != null)
{
shape.TextFrame.TextRange.Text = dataObj.Value <String>("labelInfo");
}
shape = PPTAPI.getShape(shapes, "DateLabel");
if (shape != null)
{
String strText = shape.TextFrame.TextRange.Text;
strText = strText.Replace("{Date}", dataObj.Value <String>("date"));
shape.TextFrame.TextRange.Text = strText;
}
shape = PPTAPI.getShape(shapes, "Table_1");
if (shape != null)
{
PowerPoint.Table table = shape.Table;
shape = PPTAPI.getShape(shapes, "Chart_1");
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray array = dataObj.Value <JArray>("salesData");
List <PredictData> predicts = CoreAPI.Deserialize <List <PredictData> >(array.ToString());
int col = 2;
char colStart = 'B';
foreach (PredictData predict in predicts)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = predict.Month;
table.Cell(row, col).Shape.TextFrame.TextRange.Text = predict.Sales;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = predict.Sales;
table.Cell(row, col).Shape.TextFrame.TextRange.Text = predict.Ratio;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = predict.Ratio;
col++;
colStart++;
}
}
shape = PPTAPI.getShape(shapes, "Image_1");
if (shape != null)
{
String strUrl = dataObj.Value <String>("imageUrl");
if (!String.IsNullOrEmpty(strUrl))
{
string strPath = Request.HttpDownload(strUrl).Result;
shapes.AddPicture(strPath, Microsoft.Office.Core.MsoTriState.msoCTrue, Microsoft.Office.Core.MsoTriState.msoCTrue,
shape.Left, shape.Top, shape.Width, shape.Height);
shape.Delete();
}
}
}
catch
{
}
return(true);
}
public async Task <bool> WriteDataFor164()
{
try
{
JObject jsonData = await RequestData.GetPredictData("164");
if (jsonData.Value <String>("code").Equals("200") && jsonData.Value <JObject>("data") != null)
{
JObject dataObj = jsonData["data"].ToObject <JObject>();
string mbnrStr = dataObj.Value <String>("mbnr");
JArray mbnrArr = JsonConvert.DeserializeObject <JArray>(mbnrStr);
foreach (JObject jObject in mbnrArr)
{
if (jObject.Value <String>("index").Equals("3"))
{
PowerPoint.Shapes shapes = Globals.ThisAddIn.Application.ActivePresentation.Slides[3].Shapes;
JObject tableObj = jObject.Value <JObject>("table");
PowerPoint.Shape shape = PPTAPI.getShape(shapes, "table_1");
if (shape != null)
{
PowerPoint.Table table = shape.Table;
shape = PPTAPI.getShape(shapes, "chart_1");
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb1");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
table.Cell(row, col).Shape.TextFrame.TextRange.Text = tb1[i].Value <String>("c2");;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c2");;
table.Cell(row, col).Shape.TextFrame.TextRange.Text = tb1[i].Value <String>("c3");;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c3");;
col++;
colStart++;
}
}
}
else if (jObject.Value <String>("index").Equals("4"))
{
PowerPoint.Shapes shapes = Globals.ThisAddIn.Application.ActivePresentation.Slides[4].Shapes;
JObject tableObj = jObject.Value <JObject>("table");
PowerPoint.Shape shape = PPTAPI.getShape(shapes, "chart_1");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb1");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c2");;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c3");;
col++;
colStart++;
}
}
shape = PPTAPI.getShape(shapes, "chart_2");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb2");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
string[] lsArr = tb1[i].Value <String>("c2").Split(',');
int length = lsArr.Length;
if (length < 5)
{
Array.Resize(ref lsArr, 5);
}
for (int j = length; j < 5; j++)
{
lsArr[j] = "0.0%";
}
ws.Range[step].Value = lsArr[0];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[1];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[2];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[3];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[4];
col++;
colStart++;
}
}
}
else if (jObject.Value <String>("index").Equals("5"))
{
PowerPoint.Shapes shapes = Globals.ThisAddIn.Application.ActivePresentation.Slides[5].Shapes;
JObject tableObj = jObject.Value <JObject>("table");
PowerPoint.Shape shape = PPTAPI.getShape(shapes, "chart_1");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb1");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c2");;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c3");;
col++;
colStart++;
}
}
shape = PPTAPI.getShape(shapes, "chart_2");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb2");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
string[] lsArr = tb1[i].Value <String>("c2").Split(',');
int length = lsArr.Length;
if (length < 5)
{
Array.Resize(ref lsArr, 5);
}
for (int j = length; j < 5; j++)
{
lsArr[j] = "0.0%";
}
ws.Range[step].Value = lsArr[0];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[1];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[2];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[3];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[4];
col++;
colStart++;
}
}
}
else if (jObject.Value <String>("index").Equals("6"))
{
PowerPoint.Shapes shapes = Globals.ThisAddIn.Application.ActivePresentation.Slides[6].Shapes;
JObject tableObj = jObject.Value <JObject>("table");
PowerPoint.Shape shape = PPTAPI.getShape(shapes, "chart_1");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb1");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c2");;
row++;
step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c3");;
col++;
colStart++;
}
}
shape = PPTAPI.getShape(shapes, "chart_2");
if (shape != null)
{
PowerPoint.Chart chart = shape.Chart;
var ws = chart.ChartData.Workbook.Worksheets[1];
JArray tb1 = tableObj.Value <JArray>("tb2");
int col = 2;
char colStart = 'B';
for (int i = 0; i < tb1.Count(); i++)
{
int row = 1;
String step = colStart + row.ToString();
ws.Range[step].Value = tb1[i].Value <String>("c1");
row++;
step = colStart + row.ToString();
string[] lsArr = tb1[i].Value <String>("c2").Split(',');
int length = lsArr.Length;
if (length < 5)
{
Array.Resize(ref lsArr, 5);
}
for (int j = length; j < 5; j++)
{
lsArr[j] = "0.0%";
}
ws.Range[step].Value = lsArr[0];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[1];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[2];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[3];
row++;
step = colStart + row.ToString();
ws.Range[step].Value = lsArr[4];
col++;
colStart++;
}
}
}
}
}
else
{
return(false);
}
}
catch
{
}
return(true);
}