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);
}
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);
}