打开XML Excel-创建BarChart [英] Open XML Excel-Create BarChart

问题描述

大家好，
我已经使用Open XML生成了Excel，并输入了一些数据.现在，我想根据这些数据创建一个条形图.但是我不知道如何继续.任何人都可以为此共享一个代码示例吗?提前非常感谢.
以下是我使用的代码(此部分没有任何错误):

Hi folks,
I have generated an Excel using Open XML and input some data. Now I want to create a Bar Chart based on these data; however I have no idea how to continue. Can anyone share a code sample for that? Thanks very much in advance.
Below is the codes I used(this part without any error):

using System;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Drawing.Charts;

namespace openxmlExcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {


        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("D:\\test.xls", SpreadsheetDocumentType.Workbook))
            {

                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                ////workbookpart.Workbook = new Workbook();

                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                ////worksheetPart.Worksheet = new Worksheet(new SheetData());
                string relld = workbookpart.GetIdOfPart(worksheetPart);
                Workbook workbook = new Workbook();
                FileVersion fileVersion = new FileVersion { ApplicationName = "MS Excel" };
                Worksheet worksheet = new Worksheet();
                SheetData sheetData = new SheetData();
            
                Row contentRow1 = CreateTextRow(4,"Market1","Market2","Market3");
                sheetData.AppendChild(contentRow1);
                Row contentRow2 = CreateContentRow(5,"2009",120,90,110);
                sheetData.AppendChild(contentRow2);
                Row contentRow3 = CreateContentRow(6,"2010",152,69,100);
                sheetData.AppendChild(contentRow3);
                Row contentRow4 = CreateContentRow(7,"2011",163,65,54);
                sheetData.AppendChild(contentRow4);
                Row contentRow5 = CreateContentRow(8,"2012",120,35,56);
                sheetData.AppendChild(contentRow5);                
                worksheet.Append(sheetData);                                           
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();
                Sheets sheets=new Sheets();
                Sheet sheet = new Sheet {Name="Sheet-chart",SheetId=1,Id=relld};
                sheets.Append(sheet);              
                workbook.Append(fileVersion);
                workbook.Append(sheets);               
                spreadsheetDocument.WorkbookPart.Workbook = workbook;
                spreadsheetDocument.WorkbookPart.Workbook.Save();
                spreadsheetDocument.Close();              
                System.Diagnostics.Process.Start("D:\\test.xls");

            }
        }

        private OpenXmlElement[] Formula(SheetData sheetData)
        {
            throw new NotImplementedException();
        }
            
             
        private static Row CreateTextRow(UInt32 index,string header1, string header2,string header3)
        {
            Row r = new Row { RowIndex =index };
            Cell cell1 = CreateTextCell("D", index, header1);
            Cell cell2 = CreateTextCell("E", index, header2);
            Cell cell3 = CreateTextCell("F", index, header3);
            r.Append(cell1);
            r.Append(cell2);
            r.Append(cell3);
            return r;
        }
        private static Row CreateContentRow(UInt32 index,string years,int sale1,int sale2,int sale3)
        {

            Row r=new Row{RowIndex=index};
            Cell cell1 = CreateTextCell( "C", index,years);
            Cell cell2 = CreateNumberCell("D", index, sale1);
            Cell cell3 = CreateNumberCell("E",index,sale2);
            Cell cell4 = CreateNumberCell("F",index,sale3);           
            r.Append(cell1);
            r.Append(cell2);
            r.Append(cell3);
            r.Append(cell4);     

            return r;

        }

        private static Cell CreateTextCell(string  header, UInt32 index, string text)
        {

            Cell c = new Cell { DataType = CellValues.InlineString,CellReference=header+index };

            InlineString istring = new InlineString();

            Text t = new Text { Text = text };

            istring.Append(t);

            c.Append(istring);

            return c;

        }

        private static Cell CreateNumberCell(string  header, UInt32 index, int number)
        {

            Cell c = new Cell {CellReference=header+index};

            CellValue v = new CellValue { Text = number.ToString() };

            c.Append(v);

            return c;

        }     
 
    }
}

请帮忙...

最好的问候
Gerxy

Please Help...

Best Regards
Gerxy

推荐答案

ChartSpace对象是图表创建中的主要对象. Chart对象及其许多属性将在以后的时间点添加.

每当您使用Office Open Xml时，请在系统中安装Open XML SDK 2.0生产率工具.在excel中手动创建图表.打开生产力工具"，然后打开包含图表的Excel文件.右键单击并查看图表的反射代码".尝试理解它，并尝试按照此处遵循的类似方法创建通用代码.

ChartSpace object is the main object in chart creation. Chart object and many properties to it are added later point of time.

Whenever you are dealing with Office Open Xml please have Open XML SDK 2.0 Productivity Tool installed in your system. Create a chart in excel manually. Open up the Productivity Tool and open the excel file which contains the chart. Right click and see the Reflected code for your chart. Try to understand it and try to create your generic code following similar approach followed there.

我发现Open XML SDK生产率工具对于此类事情很有用.您可以创建一个包含条形图或其他内容的文档，然后使用该工具将其打开并反映代码，然后对其进行修改以适合需要.对于包含条形图的Excel文件，它应具有以下内容:

I found that the Open XML SDK Productivity Tool is useful for this kind of thing. You can create a document containing a bar chart or whatever then open it using the tool and reflect the code, then modify it to suit. For an Excel file containing a barchart it should have something like this:

C.ChartSpace chartSpace1 = new C.ChartSpace();
chartSpace1.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
chartSpace1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
chartSpace1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
C.Date1904 date19041 = new C.Date1904(){ Val = false };
C.EditingLanguage editingLanguage1 = new C.EditingLanguage(){ Val = "en-AU" };
C.RoundedCorners roundedCorners1 = new C.RoundedCorners(){ Val = false };

C.BarChart barChart1 = new C.BarChart();
C.BarDirection barDirection1 = new C.BarDirection(){ Val = C.BarDirectionValues.Bar };
C.BarGrouping barGrouping1 = new C.BarGrouping(){ Val = C.BarGroupingValues.Clustered };
C.VaryColors varyColors1 = new C.VaryColors(){ Val = false };
C.BarChartSeries barChartSeries1 = new C.BarChartSeries();
C.Index index1 = new C.Index(){ Val = (UInt32Value)0U };
C.Order order1 = new C.Order(){ Val = (UInt32Value)0U };
C.InvertIfNegative invertIfNegative1 = new C.InvertIfNegative(){ Val = false };
C.Values values1 = new C.Values();
C.NumberReference numberReference1 = new C.NumberReference();
C.Formula formula1 = new C.Formula();
formula1.Text = "Sheet1!

A

这篇关于打开XML Excel-创建BarChart的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！