private void button11_Click(object sender, EventArgs e)
{
string filePath = @"e:\工作簿1.xlsx";
Microsoft.Office.Interop.Excel.Application eApp = new Microsoft.Office.Interop.Excel.Application();
eApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbook workBook = eApp.Workbooks.Open(filePath, AddToMru: false);
ChartObjects exCharts = workBook.Sheets[1].ChartObjects();
ChartObject xlChartObj = exCharts.Item(1);
string name = xlChartObj.Chart.Name;
string actSheetName = workBook.ActiveSheet.Name;
MessageBox.Show(xlChartObj.Name);
MessageBox.Show(name.Substring(actSheetName.Length));
//ChartObjects exCharts2 = workBook.Sheets[2].ChartObjects();
//ChartObject xlChartObj2 = exCharts2.Item(1);
//string name2 = xlChartObj2.Chart.Name;
//MessageBox.Show(xlChartObj2.Name);
//MessageBox.Show(name2);
//MessageBox.Show(xlChartObj2.Chart.CodeName);
workBook.Close();
eApp.Quit();
}
/// <summary>
/// re modify the chart with numbers of row changed
/// </summary>
/// <param name="numRows"></param>
/// <returns></returns>
public bool ModifyChart(int numRows)
{
try
{
object misValue = System.Reflection.Missing.Value;
// assign fomular for each sheet
for (int i = 2; i <= xlWorkBook.Worksheets.Count; i++)// sheet
{
xlWorkSheet = xlWorkBook.Worksheets[i];
// delete old chart
ChartObjects objs = xlWorkSheet.ChartObjects();
int chartCount = objs.Count;
for (int j = 2; j <= chartCount; j++)
{
ChartObject currChartObj = objs.Item(j - 1);
Microsoft.Office.Interop.Excel.Range chartRange;
Microsoft.Office.Interop.Excel.Chart chartPage = currChartObj.Chart;
chartRange = xlWorkSheet.Range[String.Format("A1:A{0},{1}1:{1}{0}", numRows + "", (char)((int)'A' + j - 1))];
chartPage.SetSourceData(chartRange, misValue);
}
}
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
/// <summary>
/// Selects all charts on the sheet.
/// </summary>
/// <returns>True if the sheet is a chart or contains embedded
/// charts; false if not.</returns>
public bool SelectCharts()
{
if (IsChart)
{
Logger.Info("SelectCharts: Is a chart sheet");
// Cast to _Chart to prevent compile-time warning
// about ambiguity of method and event name.
((_Chart)Chart).Select(true);
return(true);
}
else
{
ChartObjects cos = Worksheet.ChartObjects();
if (cos.Count > 0)
{
// Select first chart object to replace current selection.
// Remember that Excel collections are 1 based!
Logger.Info("SelectCharts: Selecting first chart; total: {0}", cos.Count);
ChartObject chart = cos.Item(1);
chart.Select(true);
Logger.Info("SelectCharts: Adding other charts to selection");
for (int i = 1; i <= cos.Count; i++)
{
chart = cos.Item(i);
chart.Select(false);
Bovender.ComHelpers.ReleaseComObject(chart);
}
Bovender.ComHelpers.ReleaseComObject(cos);
return(true);
}
else
{
Logger.Info("SelectCharts: No chart objects found, returning false");
return(false);
}
}
}
private void button10_Click(object sender, EventArgs e)
{
string filePath = @"e:\333.xlsx";
Microsoft.Office.Interop.Excel.Application eApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBook = eApp.Workbooks.Open(filePath, AddToMru: false);
ChartObjects exCharts = (ChartObjects)workBook.Sheets[1].ChartObjects(Type.Missing);
ChartObject xlChartObj = (ChartObject)exCharts.Item(1);
string name = xlChartObj.Name;
MessageBox.Show(name);
MessageBox.Show(xlChartObj.Chart.Name);
workBook.Close();
eApp.Quit();
}
public static ChartObject getChart(Workbook xlWorkBook, String name)
{
foreach (Worksheet sheet in xlWorkBook.Sheets)
{
ChartObjects chartObjects = (ChartObjects)(sheet.ChartObjects(Type.Missing));
for (int i = 1; i <= chartObjects.Count; i++)
{
ChartObject oChartObject = (ChartObject)(chartObjects.Item(i));
if (name.Equals(oChartObject.Name))
{
return(oChartObject);
}
}
}
return(null);
}
public Image copyChart()
{
// ChartObjects chartObjects = sheet.ChartObjects();
//xlWorkbook.Charts.Select("Retenção_PDS");
//System.Windows.Forms.IDataObject data = System.Windows.Forms.Clipboard.GetDataObject();
//xlWorkbook.ActiveChart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);
//
ChartObjects charts = (ChartObjects)sheet.ChartObjects(Type.Missing);
charts.Select("Retenção_PDS");
ChartObject chart = charts.Item(1);
System.Windows.Forms.IDataObject data = System.Windows.Forms.Clipboard.GetDataObject();
chart.Chart.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);
Image t = (Image)data.GetData(System.Windows.Forms.DataFormats.Bitmap, true);
return(t);
}
private void ExportSheetChartItems(Worksheet worksheet)
{
// Must use an index-based for loop here.
// A foreach loop caused lots of 0x800a03ec errors from Excel
// (for whatever reason).
ChartObjects cos = worksheet.ChartObjects();
Logger.Info("ExportSheetChartItems: {0} object(s)", cos.Count);
for (int i = 1; i <= cos.Count; i++)
{
Logger.Info("ExportSheetChartItems: [{0}]", i);
dynamic item = cos.Item(i);
item.Select();
ExportSelection(worksheet);
Bovender.ComHelpers.ReleaseComObject((object)item);
if (IsCancellationRequested)
{
break;
}
}
Bovender.ComHelpers.ReleaseComObject(cos);
}
private int EvaluateWorkbook(Workbook workbook, ProcessingProgress pp)
{
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.CurrentOperationName = "Evaluating document";
pp.CurrentOperationTotalElements = workbook.Sheets.Count;
pp.CurrentOperationElement = 0;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
int numberOfOperations = 0;
//for every sheet
for (int i = 1; i <= workbook.Sheets.Count; i++)
{
Worksheet worksheet = workbook.Sheets[i];
//check if chart generation is wanted
if (DocumentService.CHART_HORIZONTAL_FACES > 0)
{
//get charts
ChartObjects chartObjects = worksheet.ChartObjects(Type.Missing);
//for every chart
for (int j = 1; j <= chartObjects.Count; j++)
{
numberOfOperations++;
//current chart
Microsoft.Office.Interop.Excel.Chart chart = chartObjects.Item(j).Chart;
int chartType = GetChartType(chart);
//does not support rotation (it's plain)
if (chartType == 0)
{
//if horizontal faces number is 0, then no chart will be outputed
if (DocumentService.CHART_HORIZONTAL_FACES > 0)
{
numberOfOperations++;
}
}
else
{
//for every horizontal face
for (int k = 0; k < DocumentService.CHART_HORIZONTAL_FACES; k++)
{
numberOfOperations++;
//for every vertical face
for (int l = 0; l < DocumentService.CHART_VERTICAL_FACES; l++)
{
numberOfOperations++;
}
//some chart types, like 3D pie, does not support elevation less than 0
if (SupportsNegativeElevation(chart))
{
//for every vertical face
for (int m = 0; m < DocumentService.CHART_VERTICAL_FACES; m++)
{
numberOfOperations++;
}
}
}
}
}
}
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.CurrentOperationElement = i;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
}
return(numberOfOperations);
}
/// <summary>
/// parse an excel document and build a kinesis document model
/// </summary>
/// <param name="path">full path of the excel document</param>
/// <returns>equivalent kinesis document model</returns>
public Document ParseNewDocumentCharts(String path, ProcessingProgress pp, Document document)
{
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationName = "All Document Charts";
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
//directory where all the data will be saved
String folderName = document.Location;
String documentPath = System.IO.Path.Combine(DocumentService.TEMP_DIRECTORY, folderName);
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.CurrentOperationName = "Opening MS Office";
pp.CurrentOperationTotalElements = 1;
pp.CurrentOperationElement = 0;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
//open the given excel document
Workbook workbook = excelApplication.Workbooks.Open(path, ReadOnly: true);
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.CurrentOperationElement = 1;
pp.OverallOperationTotalElements = EvaluateWorkbook(workbook, pp);
pp.OverallOperationElement = 0;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
//for every sheet
for (int i = 1; i <= workbook.Sheets.Count; i++)
{
Worksheet worksheet = workbook.Sheets[i];
//create a new page
KineSis.ContentManagement.Model.Page page = document.Pages[i - 1];
//check if chart generation is wanted
if (DocumentService.CHART_HORIZONTAL_FACES > 0)
{
//get charts
ChartObjects chartObjects = worksheet.ChartObjects(Type.Missing);
//create directory for charts
String chartPath = System.IO.Path.Combine(documentPath, "charts");
System.IO.Directory.CreateDirectory(chartPath);
//for every chart
for (int j = 1; j <= chartObjects.Count; j++)
{
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.CurrentOperationName = "Page " + i + " / Chart " + j + " of " + chartObjects.Count;
pp.CurrentOperationTotalElements = EvaluateChart(chartObjects.Item(j).Chart);
pp.CurrentOperationElement = 0;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
KineSis.ContentManagement.Model.Chart mChart = new KineSis.ContentManagement.Model.Chart();
//current chart
Microsoft.Office.Interop.Excel.Chart chart = chartObjects.Item(j).Chart;
mChart.SetThumbnailUrl(GenerateThumbnail(chart, chartPath + DD + i + _ + j + "_thumb"));
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationElement++;
pp.CurrentOperationElement++;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
if (DocumentService.FORCE_CHART_SIZE)
{
chart.ChartArea.Height = ((float)DocumentService.CHART_WIDTH * chart.ChartArea.Height) / chart.ChartArea.Width;
chart.ChartArea.Width = DocumentService.CHART_WIDTH;
}
int chartType = GetChartType(chart);
//start from 0 point
chart.Rotation = 0;
int horizontalAngle = 0;
//depending on how many horizontal faces are required, calculate the angle between them
if (DocumentService.CHART_HORIZONTAL_FACES > 0)
{
horizontalAngle = 360 / DocumentService.CHART_HORIZONTAL_FACES;
}
int verticalAngle = 0;
//depending on how many vertical faces are required for a horizontal face, celaculate the angle between them, excluding the vertical face at 90 degrees
if (DocumentService.CHART_VERTICAL_FACES > 0)
{
verticalAngle = 90 / (DocumentService.CHART_VERTICAL_FACES + 1);
}
if (chart.HasTitle)
{
mChart.Title = chart.ChartTitle.Caption;
}
else
{
mChart.Title = chart.Name;
}
//does not support rotation (it's plain)
if (chartType == 0)
{
//if horizontal faces number is 0, then no chart will be outputed
if (DocumentService.CHART_HORIZONTAL_FACES > 0)
{
ChartHorizontalView hView = new ChartHorizontalView();
//draw chart face as image
chart.Export(chartPath + DD + i + _ + j + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
//add to hView
hView.ImageUrl = chartPath + DD + i + _ + j + DocumentService.IMAGE_EXTENSION;
//add to views
mChart.Views.Add(hView);
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationElement++;
pp.CurrentOperationElement++;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
}
}
else
{
//for every horizontal face
for (int k = 0; k < DocumentService.CHART_HORIZONTAL_FACES; k++)
{
ChartHorizontalView hView = new ChartHorizontalView();
//reset elevation
chart.Elevation = 0;
//export face as image
chart.Export(chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
//set bitmap to view
hView.ImageUrl = chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationElement++;
pp.CurrentOperationElement++;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
//for every vertical face
for (int l = 0; l < DocumentService.CHART_VERTICAL_FACES; l++)
{
ChartVerticalView vView = new ChartVerticalView();
//increse elevation
chart.Elevation += verticalAngle;
//export face as image
chart.Export(chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
//set bitmap to view
vView.ImageUrl = chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION;
//add vertical view to horizontal UP list
hView.Up.Add(vView);
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationElement++;
pp.CurrentOperationElement++;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
}
//some chart types, like 3D pie, does not support elevation less than 0
if (SupportsNegativeElevation(chart))
{
//reset elevation
chart.Elevation = 0;
//for every vertical face
for (int m = 0; m < DocumentService.CHART_VERTICAL_FACES; m++)
{
ChartVerticalView vView = new ChartVerticalView();
//decrease elevation
chart.Elevation -= verticalAngle;
//export face as image
chart.Export(chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION, DocumentService.IMAGE_FILTER, false);
//set bitmap to vertical view
vView.ImageUrl = chartPath + DD + i + _ + j + _ + chart.Rotation + _ + chart.Elevation + DocumentService.IMAGE_EXTENSION;
//add vertical view to horizontal view DOWN list
hView.Down.Add(vView);
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
pp.OverallOperationElement++;
pp.CurrentOperationElement++;
//~~~~~~~~~~~~~progress~~~~~~~~~~~~~//
}
}
//increase horizontal angle in order to get the next horizontal view
chart.Rotation += horizontalAngle;
//add horizontal view to the chat's views list
mChart.Views.Add(hView);
}
}
//add chart to page
page.Charts.Add(mChart);
}
}
}
//close workbook without saving any possible changes (this way the "Are you sure?" or "Save changes?" dialogs will be supressed)
workbook.Close(SaveChanges: false);
CloseOfficeApplication();
//return the built document
return(document);
}
