예제 #1
0
        public SpreadsheetGear.IWorkbook DrawChart(List <ChartModel> columns, List <DataTable> allitem, string sheetname,
                                                   bool OpenSheet = false, bool cellText = false, int cellInt = 0)
        {
            //创建新的workbook
            SpreadsheetGear.IWorkbook   workbook   = Factory.GetWorkbook();
            SpreadsheetGear.IWorksheets worksheets = workbook.Worksheets;
            IWorksheet worksheet = worksheets[0];

            worksheet.Name = sheetname + 1;
            IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
            IShapes shapes = worksheet.Shapes;

            int    high    = 1;
            int    Rowhigh = 17;
            double top     = 0.5;
            double bottom  = 15.5;

            for (int a = 0; a < columns.Count; a++)
            {
                int row    = 0;
                int column = 0;
                int piecol = 0;

                string site = null;


                if (columns[a].series.Count > 0)
                {
                    row    = columns[a].xAxis.Count;
                    column = columns[a].series.Count;
                }
                if (columns[a].pieseries.Count > 0)
                {
                    piecol = columns[a].pieseries.Count;
                }
                //+((17 + row) * j);

                string[,] arrayChart = null;
                if ((columns[a].charttype.ToString()) == "Pie")
                {
                    site       = "A" + Rowhigh + ":B" + (piecol + Rowhigh);
                    arrayChart = new string[piecol + 1, 2];
                    for (int j = 0; j < (piecol + 1); j++)
                    {
                        if (j == 0)
                        {
                            if (!string.IsNullOrEmpty(columns[a].title))
                            {
                                arrayChart[0, 0] = "";
                                arrayChart[0, 1] = columns[a].title;
                            }
                            else
                            {
                                arrayChart[0, 0] = "";
                                arrayChart[0, 1] = "";
                            }
                        }
                        else
                        {
                            for (int i = 0; i < 2; i++)
                            {
                                if (i == 0)
                                {
                                    arrayChart[j, i] = columns[a].pieseries[j - 1].name;
                                }
                                else
                                {
                                    arrayChart[j, i] = columns[a].pieseries[j - 1].y.ToString();
                                }
                            }
                        }
                    }
                }
                else if ((columns[a].charttype.ToString()) == "Radar")
                {
                    if (row < 3)
                    {
                        row = 3;
                    }
                    string aaa = column / 26 == 0 ? ((char)((65 + column))).ToString() : "" + (char)((64 + column / 26)) + (char)((65 + column % 26));
                    site = "A" + Rowhigh + ":" + aaa + (row + Rowhigh);
                    if (column > 25)
                    {
                        arrayChart = new string[(row + 1), (column + 1)];
                    }
                    for (int j = 0; j < row + 1; j++)
                    {
                        if (j == 0)
                        {
                            for (int i = 0; i < column + 1; i++)
                            {
                                if (i == 0)
                                {
                                    arrayChart[j, i] = "";
                                }
                                else
                                {
                                    arrayChart[j, i] = columns[a].series[i - 1].name;
                                }
                            }
                        }
                        else
                        {
                            for (int k = 0; k < column + 1; k++)
                            {
                                if (k == 0)
                                {
                                    if (j > columns[a].xAxis.Count)
                                    {
                                        arrayChart[j, k] = "";
                                    }
                                    else
                                    {
                                        arrayChart[j, k] = columns[a].xAxis[j - 1];
                                    }
                                }
                                else
                                {
                                    if (j > columns[a].xAxis.Count)
                                    {
                                        arrayChart[j, k] = "";
                                    }
                                    else
                                    {
                                        List <double> adata = columns[a].series[k - 1].data;
                                        arrayChart[j, k] = adata[j - 1].ToString();
                                    }
                                }
                            }
                        }
                    }
                }
                else
                {
                    string aaa = column / 26 == 0 ? ((char)((65 + column))).ToString() : "" + (char)((64 + column / 26)) + (char)((65 + column % 26));
                    site       = "A" + Rowhigh + ":" + aaa + (row + Rowhigh);
                    arrayChart = new string[(row + 1), (column + 1)];
                    for (int j = 0; j < row + 1; j++)
                    {
                        if (j == 0)
                        {
                            for (int i = 0; i < column + 1; i++)
                            {
                                if (i == 0)
                                {
                                    arrayChart[j, i] = "";
                                }
                                else
                                {
                                    arrayChart[j, i] = columns[a].series[i - 1].name;
                                }
                            }
                        }
                        else
                        {
                            for (int k = 0; k < column + 1; k++)
                            {
                                if (k == 0)
                                {
                                    arrayChart[j, k] = columns[a].xAxis[j - 1];
                                }
                                else
                                {
                                    List <double> adata = columns[a].series[k - 1].data;
                                    arrayChart[j, k] = adata[j - 1].ToString();
                                }
                            }
                        }
                    }
                }

                double chartleft   = windowInfo.ColumnToPoints(0.15);
                double charttop    = windowInfo.RowToPoints(top);
                double chartright  = windowInfo.ColumnToPoints(7.85);
                double chartbottom = windowInfo.RowToPoints(bottom);

                SpreadsheetGear.Charts.IChart chart =
                    shapes.AddChart(chartleft, charttop, chartright - chartleft, chartbottom - charttop).Chart;
                IRange source = worksheet.Cells[site];
                source.Value = arrayChart;
                chart.SetSourceData(source, RowCol.Columns);

                if ((columns[a].charttype.ToString()) == "Column")
                {
                    chart.ChartType = ChartType.ColumnClustered;
                }
                else if ((columns[a].charttype.ToString()) == "Line")
                {
                    chart.ChartType = ChartType.Line;
                }
                else if ((columns[a].charttype.ToString()) == "Pie")
                {
                    chart.ChartType = ChartType.Pie;
                    ISeries seriesTotal = chart.SeriesCollection[0];

                    seriesTotal.HasDataLabels             = true;
                    seriesTotal.DataLabels.ShowPercentage = true;
                    seriesTotal.DataLabels.ShowValue      = false;
                }
                else if ((columns[a].charttype.ToString()) == "Radar")
                {
                    chart.ChartType = ChartType.Radar;
                }
                else if ((columns[a].charttype.ToString()) == "Bar")
                {
                    chart.ChartType = ChartType.BarClustered;
                }
                else if ((columns[a].charttype.ToString()) == "BarStacked")
                {
                    chart.ChartType = ChartType.BarStacked;
                }

                if (!string.IsNullOrEmpty(columns[a].title))
                {
                    chart.HasTitle             = true;
                    chart.ChartTitle.Text      = columns[a].title;
                    chart.ChartTitle.Font.Size = 12;
                }

                if ((columns[a].charttype.ToString()) == "Pie")
                {
                    Rowhigh += 17 + piecol;
                    top     += 17 + piecol;
                    bottom  += 17 + piecol;
                    high    += 17 + piecol;
                }
                else
                {
                    Rowhigh += 17 + row;
                    top     += 17 + row;
                    bottom  += 17 + row;
                    high    += 17 + row;
                }
            }
            //数据列表处理
            if (allitem.Count > 0)
            {
                if (OpenSheet)
                {
                    for (int c = 0; c < allitem.Count; c++)
                    {
                        if (columns.Count > 0)
                        {
                            worksheets.Add();
                            worksheet      = worksheets[worksheets.Count - 1];
                            worksheet.Name = sheetname + worksheets.Count;
                        }
                        else
                        {
                            if (c > 0)
                            {
                                worksheets.Add();
                                worksheet      = workbook.Worksheets[worksheets.Count - 1];
                                worksheet.Name = sheetname + worksheets.Count;
                            }
                        }
                        string allsite = "A1";
                        IRange cell    = worksheet.Cells[allsite];
                        //cell.CopyFromDataTable(allitem[c], SetDataFlags.None);
                        cell.CopyFromDataTable(allitem[c], SetDataFlags.AllText);

                        worksheet.UsedRange.Columns.AutoFit();
                        //high += allitem[c].Rows.Count;
                    }
                }
                else
                {
                    if (columns.Count > 0)
                    {
                        high += 2;
                    }
                    for (int b = 0; b < allitem.Count; b++)
                    {
                        if (b > 0)
                        {
                            high += 1;
                        }
                        string allsite = "A" + high;
                        IRange cell    = worksheet.Cells[allsite];
                        //cell.CopyFromDataTable(allitem[b], SetDataFlags.None);
                        cell.CopyFromDataTable(allitem[b], SetDataFlags.AllText);
                        if (cellText)
                        {
                            for (int i = 1; i <= allitem[0].Rows.Count; i++)
                            {
                                // cell[i, cellInt].NumberFormat = "@";
                                cell[i, cellInt].NumberFormat = "@";
                            }
                        }

                        worksheet.UsedRange.Columns.AutoFit();
                        high += allitem[b].Rows.Count;
                    }
                }
            }
            return(workbook);
        }
예제 #2
0
        public SpreadsheetGear.IWorkbook BuildSheet(List <SheetModel> sheets)
        {
            //创建新的workbook
            SpreadsheetGear.IWorkbook   workbook   = Factory.GetWorkbook();
            SpreadsheetGear.IWorksheets worksheets = workbook.Worksheets;
            for (int i = 0; i < sheets.Count; i++)
            {
                int high = 1;
                if (i > 0)
                {
                    worksheets.Add();
                }
                IWorksheet worksheet = worksheets[i];
                worksheet.Name = sheets[i].SheetName == null ? "sheet" + (i + 1) : sheets[i].SheetName;
                IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
                IShapes          shapes         = worksheet.Shapes;
                List <DataModel> dataList       = sheets[i].DataModels;
                for (int j = 0; j < dataList.Count; j++)
                {
                    if (dataList[j].datatype.ToString() != "DataTable")
                    {
                        high += 16;
                        string allsite = "A" + high;
                        IRange cell    = worksheet.Cells[allsite];
                        cell.CopyFromDataTable(dataList[j].Dataseries, SetDataFlags.None);
                        worksheet.UsedRange.Columns.AutoFit();

                        double top         = high - 16.5;
                        double bottom      = high - 1.5;
                        double chartleft   = windowInfo.ColumnToPoints(0.15);
                        double charttop    = windowInfo.RowToPoints(top);
                        double chartright  = windowInfo.ColumnToPoints(7.85);
                        double chartbottom = windowInfo.RowToPoints(bottom);

                        SpreadsheetGear.Charts.IChart chart = shapes.AddChart(chartleft, charttop, chartright - chartleft, chartbottom - charttop).Chart;
                        string site   = ((char)(65 + dataList[j].StartPoint.x)).ToString() + (high + dataList[j].StartPoint.y) + ":" + ((char)(64 + dataList[j].ColumnCount + dataList[j].StartPoint.x)).ToString() + (high + dataList[j].StartPoint.y + dataList[j].RowCount);
                        IRange source = worksheet.Cells[site];
                        chart.SetSourceData(source, RowCol.Columns);
                        switch (dataList[j].datatype.ToString())
                        {
                        case "Column":
                            chart.ChartType = ChartType.ColumnClustered;
                            break;

                        case "Line":
                            chart.ChartType = ChartType.Line;
                            break;

                        case "Radar":
                            chart.ChartType = ChartType.Radar;
                            break;

                        case "Bar":
                            chart.ChartType = ChartType.BarClustered;
                            break;

                        case "BarStacked":
                            chart.ChartType = ChartType.BarStacked;
                            break;

                        case "Pie":
                            chart.ChartType = ChartType.Pie;
                            ISeries seriesTotal = chart.SeriesCollection[0];
                            seriesTotal.HasDataLabels             = true;
                            seriesTotal.DataLabels.ShowPercentage = true;
                            seriesTotal.DataLabels.ShowValue      = false;
                            break;
                        }
                        high += dataList[j].Dataseries.Rows.Count + 1 + sheets[i].space;
                    }
                    else
                    {
                        string allsite = "A" + high;
                        IRange cell    = worksheet.Cells[allsite];
                        //cell.CopyFromDataTable(dataList[j].Dataseries, SetDataFlags.None);
                        cell.CopyFromDataTable(dataList[j].Dataseries, SetDataFlags.AllText);
                        worksheet.UsedRange.Columns.AutoFit();
                        high += dataList[j].Dataseries.Rows.Count + 1 + sheets[i].space;
                    }
                }
            }
            return(workbook);
        }