/// <summary>
/// 导出excel中的chart对象到word中
/// </summary>
/// <param name="cht">excel中的chart对象</param>
/// <param name="range">此时word文档中的全局range的位置或者范围</param>
/// <remarks>由局部安全的原则，在进行绘图前，将另起一行，并将段落样式设置为“图片”样式</remarks>
private void Export_ExcelChart(Chart cht, ref Word.Range range)
{
cht.Application.ScreenUpdating = false;
try
{
// 下面复制Chart的操作中，如果监测曲线图所使用的Chart模板有问题，则可能会出错。
Excel.ChartObject chtObj = cht.Parent as Excel.ChartObject;
chtObj.Activate();
chtObj.Copy(); // 或者用 cht.ChartArea.Copy()都可以。
}
catch (Exception ex)
{
MessageBox.Show(
"导出监测曲线图\"" + cht.Application.Caption.ToString() + "\"出错（请检查是否是用户使用的Chart模板有问题），跳过此图的导出。" +
"\r\n" + ex.Message + "\r\n" + "报错位置：" +
ex.TargetSite.Name, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
//刷新excel屏幕
cht.Application.ScreenUpdating = true;
return;
}
//设置word.range的格式
Word.Range with_1 = range;
//新起一行，并设置新段落的段落样式为图片
NewLine(range, ParagraphStyle.picture);
//进行粘贴，下面也可以用：DataType:=23
with_1.PasteSpecial(DataType: Word.WdPasteDataType.wdPasteOLEObject, Placement: Word.WdOLEPlacement.wdInLine);
Word.InlineShape shp = default(Word.InlineShape);
range.Select();
range.Application.Selection.MoveLeft(Unit: Word.WdUnits.wdCharacter, Count: 1,
Extend: Word.WdMovementType.wdExtend);
shp = range.Application.Selection.InlineShapes[1];
//约束图形的宽度，将其限制在word页面的正文宽度之内
WidthRestrain(shp, ContentWidth);
//刷新excel屏幕
cht.Application.ScreenUpdating = true;
}
public void UseCopyPaste()
{
// Declare variables to hold references to PowerPoint objects.
pptNS.Application powerpointApplication = null;
pptNS.Presentation pptPresentation = null;
pptNS.Slide pptSlide = null;
pptNS.ShapeRange shapeRange = null;
// Declare variables to hold references to Excel objects.
xlNS.Application excelApplication = null;
xlNS.Workbook excelWorkBook = null;
xlNS.Worksheet targetSheet = null;
xlNS.ChartObjects chartObjects = null;
xlNS.ChartObject existingChartObject = null;
string paramPresentationPath = System.Windows.Forms.Application.StartupPath + @"\ChartTest.pptx";
string paramWorkbookPath = System.Windows.Forms.Application.StartupPath + @"\ChartData.xlsx";
object paramMissing = Type.Missing;
try
{
// Create an instance of PowerPoint.
powerpointApplication = new pptNS.Application();
// Create an instance Excel.
excelApplication = new xlNS.Application();
// Open the Excel workbook containing the worksheet with the chart data.
excelWorkBook = excelApplication.Workbooks.Open(paramWorkbookPath,
paramMissing, paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing);
// Get the worksheet that contains the chart.
targetSheet =
(xlNS.Worksheet)(excelWorkBook.Worksheets["Quarterly Sales"]);
// Get the ChartObjects collection for the sheet.
chartObjects =
(xlNS.ChartObjects)(targetSheet.ChartObjects(paramMissing));
// Get the chart to copy.
existingChartObject =
(xlNS.ChartObject)(chartObjects.Item("Sales Chart"));
// Create a PowerPoint presentation.
pptPresentation =
powerpointApplication.Presentations.Add(
Microsoft.Office.Core.MsoTriState.msoTrue);
// Add a blank slide to the presentation.
pptSlide =
pptPresentation.Slides.Add(1, pptNS.PpSlideLayout.ppLayoutBlank);
// Copy the chart from the Excel worksheet to the clipboard.
existingChartObject.Copy();
// Paste the chart into the PowerPoint presentation.
shapeRange = pptSlide.Shapes.Paste();
// Position the chart on the slide.
shapeRange.Left = 60;
shapeRange.Top = 100;
// Save the presentation.
pptPresentation.SaveAs(paramPresentationPath, pptNS.PpSaveAsFileType.ppSaveAsOpenXMLPresentation, Microsoft.Office.Core.MsoTriState.msoTrue);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Release the PowerPoint slide object.
shapeRange = null;
pptSlide = null;
// Close and release the Presentation object.
if (pptPresentation != null)
{
pptPresentation.Close();
pptPresentation = null;
}
// Quit PowerPoint and release the ApplicationClass object.
if (powerpointApplication != null)
{
powerpointApplication.Quit();
powerpointApplication = null;
}
// Release the Excel objects.
targetSheet = null;
chartObjects = null;
existingChartObject = null;
// Close and release the Excel Workbook object.
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public void generateResponsesSlides(Office.IRibbonControl control)
{
JObject responses = this.addin.getFormResponses(globalUrl);
string fileName = this.addin.getTitle(globalUrl);
string paramWorkbookPath = @".\" + fileName + ".xlsx";
string paramPowerpointPath = @".\" + fileName + ".pptx";
List <Question> questions = parseResponses(responses);
PowerPoint.CustomLayout layout = this.addin.Application.ActivePresentation.SlideMaster.CustomLayouts[6];
for (int i = 0; i < questions.Count(); i++)
{
//Adicionar novo slide e mudar slide de foco
pptSlide = this.addin.Application.ActivePresentation.Slides.AddSlide(this.addin.currentSlide + 1, layout);
pptSlides.Add(pptSlide);
this.addin.Application.ActiveWindow.Presentation.Slides[this.addin.currentSlide + 1].Select();
this.addin.currentSlide++;
//Output do Titulo da Pergunta
this.addin.Application.ActiveWindow.View.Slide.Shapes.Title.TextFrame.TextRange.Text = questions[i].Title;
Excel.Worksheet dataSheet;
if (i == 0)
{
//Create instance to Excel workbook to work with chart data
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true;
if (!File.Exists(paramWorkbookPath))
{
dataWorkbook = excelApp.Workbooks.Add();
dataWorkbooks.Add(dataWorkbook);
dataWorkbook.Windows[1].WindowState = Excel.XlWindowState.xlMinimized;
//Accessing the data worksheet for chart
dataSheet = ((Excel.Worksheet)dataWorkbook.Worksheets[i + 1]);
dataWorkSheets.Add(dataSheet);
}
else
{
dataWorkbook = excelApp.Workbooks.Open(paramWorkbookPath);
dataWorkbooks.Add(dataWorkbook);
dataWorkbook.Windows[1].WindowState = Excel.XlWindowState.xlMinimized;
//Accessing the data worksheet for chart
dataSheet = ((Excel.Worksheet)dataWorkbook.Worksheets[i + 1]);
dataWorkSheets.Add(dataSheet);
}
}
else
{
dataSheet = dataWorkbook.Worksheets.Add();
dataWorkSheets.Add(dataSheet);
}
//Setting the range of chart
string[] rangeids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string lowerRange = "1";
int upRange = questions[i].Choices.Count() + 1;
string upperRange = upRange.ToString();
lowerRange = "A" + lowerRange;
//TODO TIRAR ESTE "F" HARDCODED
if (questions[i].Type == "GRID")
{
upperRange = "F" + upRange / 2;
int aux = 0;
string categoria1 = questions[i].Choices[0].row;
for (int y = 0; y < questions[i].Choices.Count; y++)
{
if (questions[i].Choices[y].row == categoria1)
{
aux++;
}
}
upperRange = rangeids[aux] + (questions[i].Choices.Count / 2 - 1).ToString();
gridUpperRangeGlobal = upperRange;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = upperRange;
}
else
{
upperRange = "B" + upperRange;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = upperRange;
}
Excel.Range tRange = dataSheet.Cells.get_Range(lowerRange, upperRange);
Excel.ListObject tbl1;
string tableName = "Tabela";
//Applying the set range on chart data table
tableName = tableName + i;
dataSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = tableName;
tbl1 = dataSheet.ListObjects[tableName];
tbl1.Resize(tRange);
//Setting values for categories and respective series data
string option = "A";
string count = "B";
List <string> rows = new List <string>();
if (questions[i].Type == "GRID")
{
int iteraux = 2;
for (var j = 0; j < questions[i].Choices.Count; j++)
{
string optionaux = option + iteraux;
if (!rows.Contains(questions[i].Choices[j].row))
{
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].row;
rows.Add(questions[i].Choices[j].row);
iteraux++;
}
}
string[] columnids = { "A", "B", "C", "D", "E", "F", "G", "H" };
int column_index = 0;
bool series_written = false;
for (int v = 0; v < rows.Count; v++)
{
string categoria = rows[v];
column_index = 0;
for (int u = 0; u < questions[i].Choices.Count; u++)
{
if (questions[i].Choices[u].row == categoria)
{
string celula = columnids[column_index + 1] + (v + 2).ToString();
dataSheet.Cells.get_Range(celula).FormulaR1C1 = questions[i].Choices[u].count.ToString();
if (!series_written)
{
dataSheet.Cells.get_Range(columnids[column_index + 1] + (v + 1).ToString()).FormulaR1C1 = questions[i].Choices[u].option;
}
column_index++;
}
}
series_written = true;
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Try1";
}
else if (questions[i].Type == "SCALE")
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = "- " + questions[i].Choices[j].option.ToString() + " -";
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Categoria";
dataSheet.Cells.get_Range("B1").FormulaR1C1 = "Try1";
}
else
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].option;
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Categoria";
dataSheet.Cells.get_Range("B1").FormulaR1C1 = "Try1";
}
// Insert graphic in Excel
//Output do Gráfico
float width = 500F;
float left = 230F;
if (questions[i].Choices.Count > 6)
{
width = 750F;
left = 130F;
}
else if (questions[i].Choices.Count > 12)
{
width = 1100;
left = 80F;
}
//Save value of last
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)dataSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(50, 150, width, 250);
Excel.Chart chartPage = myChart.Chart;
object paramMissing = Type.Missing;
// Declare variables for the Chart.ChartWizard method.
object paramChartFormat = 1;
object paramCategoryLabels = 0;
object paramSeriesLabels = 0;
bool paramHasLegend = true;
// Create a new chart of the data.
myChart.Chart.ChartWizard(tRange, Excel.XlChartType.xlColumnClustered, paramChartFormat, Excel.XlRowCol.xlRows,
paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramMissing, paramMissing, paramMissing, paramMissing);
chartRange = dataSheet.get_Range(lowerRange, upperRange);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
if (questions[i].Type != "GRID")
{
chartPage.ChartTitle.Delete();
}
Excel.Axis excelaxis = chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
int maxScale = 0;
for (int h = 0; h < questions[i].Choices.Count; h++)
{
if (questions[i].Choices[h].count > maxScale)
{
maxScale = questions[i].Choices[h].count;
}
}
excelaxis.MajorUnit = (int)(maxScale + 10.0) / 5;
excelaxis.MinorUnit = (int)(maxScale + 10.0) / 10;
excelaxis.MinimumScale = 0;
excelaxis.MaximumScale = maxScale + 10.0;
myChart.Copy();
shapeRange = pptSlide.Shapes.Paste();
// Position the chart on the slide.
shapeRange.Left = left;
shapeRange.Top = 160F;
shapeRange.Height = 350F;
shapeRange.Width = width;
//Copy chart to xlsCharts
xlsCharts.Add(myChart);
}
// Save excel file
if (newTryBool == true)
{
//dataWorkbook.Save();
}
else
{
//dataWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing,
//paramMissing, Excel.XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
}
pptSlides[0].Select();
// Save the presentation.
//this.addin.Application.ActivePresentation.SaveAs(paramPowerpointPath, PowerPoint.PpSaveAsFileType.ppSaveAsOpenXMLPresentation, Office.MsoTriState.msoTrue);
}
public void newTry(Office.IRibbonControl control)
{
//Limpar as respostas existentes no Inquérito
//this.addin.deleteResponses(globalUrl);
JObject responses = this.addin.getFormResponses(globalUrl);
List <Question> questions = parseResponses(responses);
int i = 0;
foreach (Excel.ChartObject xlsChart in xlsCharts)
{
xlsChart.Delete();
Excel.Worksheet dataSheet = dataWorkSheets[i];
//Setting the range of chart
string[] rangeids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string lowerRange = "1";
int upRange = questions[i].Choices.Count() + 1;
string upperRange = upRange.ToString();
int lastCellUsed = 0;
//Descobrir o initial range
for (int x = 0; x < 70; x++)
{
if (dataSheet.Cells[2, x + 1].Value != null)
{
lastCellUsed = x;
}
}
lowerRange = rangeids[lastCellUsed + 2] + lowerRange;
lowerRangeGlobal = lowerRange[0].ToString();
if (questions[i].Type == "GRID")
{
int aux = 0;
string categoria1 = questions[i].Choices[0].row;
for (int y = 0; y < questions[i].Choices.Count; y++)
{
if (questions[i].Choices[y].row == categoria1)
{
aux++;
}
}
upperRange = rangeids[aux + lastCellUsed + 2] + (questions[i].Choices.Count / 2 - 1).ToString();
gridUpperRangeGlobal = upperRange[0].ToString();
}
else
{
upperRange = rangeids[lastCellUsed + 3] + upperRange;
upperRangeGlobal = upperRange[0].ToString();
}
Excel.Range tRange = dataSheet.Cells.get_Range(lowerRange, upperRange);
Excel.ListObject tbl1;
string tableName = "Tabela";
//Applying the set range on chart data table
DateTime date1 = DateTime.Now;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = tRange;
tableName = date1.ToString();
dataSheet.Cells.get_Range("A51").FormulaR1C1 = tableName;
dataSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = tableName;
tbl1 = dataSheet.ListObjects[tableName];
tbl1.Resize(tRange);
//Setting values for categories and respective series data
string option = rangeids[lastCellUsed + 2];
string count = rangeids[lastCellUsed + 3];
List <string> rows = new List <string>();
if (questions[i].Type == "GRID")
{
int iteraux = 2;
for (var j = 0; j < questions[i].Choices.Count; j++)
{
string optionaux = option + iteraux;
if (!rows.Contains(questions[i].Choices[j].row))
{
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].row;
rows.Add(questions[i].Choices[j].row);
iteraux++;
}
}
string[] columnids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
int column_index = 0;
bool series_written = false;
for (int v = 0; v < rows.Count; v++)
{
string categoria = rows[v];
column_index = lastCellUsed + 2;
for (int u = 0; u < questions[i].Choices.Count; u++)
{
if (questions[i].Choices[u].row == categoria)
{
string celula = columnids[column_index + 1] + (v + 2).ToString();
dataSheet.Cells.get_Range(celula).FormulaR1C1 = questions[i].Choices[u].count.ToString();
if (!series_written)
{
dataSheet.Cells.get_Range(columnids[column_index + 1] + (v + 1).ToString()).FormulaR1C1 = questions[i].Choices[u].option;
}
column_index++;
}
}
series_written = true;
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
else if (questions[i].Type == "SCALE")
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = "- " + questions[i].Choices[j].option.ToString() + " -";
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Categoria";
string auxUpperRange = upperRange[0] + "1";
dataSheet.Cells.get_Range(auxUpperRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
else
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].option;
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Categoria";
string auxUpperRange = upperRange[0] + "1";
dataSheet.Cells.get_Range(auxUpperRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
// Insert graphic in Excel
//Output do Gráfico
float width = 500F;
float left = 230F;
if (questions[i].Choices.Count > 6)
{
width = 750F;
left = 130F;
}
else if (questions[i].Choices.Count > 12)
{
width = 1100;
left = 80F;
}
//Save value of last
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)dataSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(50, 150, width, 250);
Excel.Chart chartPage = myChart.Chart;
object paramMissing = Type.Missing;
// Declare variables for the Chart.ChartWizard method.
object paramChartFormat = 1;
object paramCategoryLabels = 0;
object paramSeriesLabels = 0;
bool paramHasLegend = true;
// Create a new chart of the data.
myChart.Chart.ChartWizard(tRange, Excel.XlChartType.xlColumnClustered, paramChartFormat, Excel.XlRowCol.xlRows,
paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramMissing, paramMissing, paramMissing, paramMissing);
chartRange = dataSheet.get_Range(lowerRange, upperRange);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis excelaxis = chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
int maxScale = 0;
for (int h = 0; h < questions[i].Choices.Count; h++)
{
if (questions[i].Choices[h].count > maxScale)
{
maxScale = questions[i].Choices[h].count;
}
}
if (questions[i].Type != "GRID")
{
chartPage.ChartTitle.Delete();
}
excelaxis.MajorUnit = (int)(maxScale + 10.0) / 5;
excelaxis.MinorUnit = (int)(maxScale + 10.0) / 10;
excelaxis.MinimumScale = 0;
excelaxis.MaximumScale = maxScale + 10.0;
PowerPoint.Slide currentSlide = this.addin.Application.ActiveWindow.View.Slide;
pptSlides[i].Select();
try
{
myChart.Copy();
}
catch (COMException e)
{
Thread.Sleep(500);
myChart.Copy();
}
shapeRange = pptSlides[i].Shapes.Paste();
PowerPoint.Shape previousGraph = pptSlides[i].Shapes[2];
previousGraph.Delete();
shapeRange.Left = left;
shapeRange.Top = 160F;
shapeRange.Height = 350F;
shapeRange.Width = width;
xlsChartsTemp.Add(myChart);
i++;
}
xlsCharts = xlsChartsTemp;
xlsChartsTemp = new List <Excel.ChartObject>();
newTryBool = true;
newTryCount++;
// dataWorkbook.Save();
}
