public static string ExportStaticResult(string fileName, IStatResult result, masExcelDrawStatType drawType, string title, int colOffset, bool hasLegend, string xName, string yName)
{
try
{
using (StatResultToChartInExcelFile excelControl = new StatResultToChartInExcelFile())
{
excelControl.Init(drawType);
excelControl.Add(title, result, true, colOffset, false, xName, yName);
if (!fileName.ToUpper().EndsWith(".XLSX"))
{
fileName += ".XLSX";
}
excelControl.SaveFile(fileName);
}
}
catch (Exception)
{
using (StatResultToTxtFile txtControl = new StatResultToTxtFile())
{
if (!fileName.ToUpper().EndsWith(".TXT"))
{
fileName += ".TXT";
}
txtControl.WriteResultToTxt(title + "\n");
txtControl.WriteResultToTxt("统计日期：" + DateTime.Today.Date.ToShortDateString() + "\n");
txtControl.WriteResultToTxt(result);
bool isSave = txtControl.SaveFile(fileName);
if (!isSave)
{
return(String.Empty);
}
}
}
return(fileName);
}
public void Add(bool singleFile, string windowText, IStatResult result, int startCol, int displayCol, bool isTotal, int totalAll, int statImage, byte baseCol, int chartType)
{
_singleFile = singleFile;
_windowText = windowText;
_result = result;
_displayCol = displayCol;
_isTotal = isTotal;
_totalAll = totalAll;
_statImage = statImage;
_needLoad = true;
_appendType = enumAppendExcelType.DisplayColAllTotal;
_startCol = startCol;
_baseCol = baseCol;
_masExcelDrawStatType = chartType == 1 ? masExcelDrawStatType.xlColumnClustered : masExcelDrawStatType.xlLine;
Display();
}
/// <summary>
/// 默认:masExcelDrawStatType.xl3DColumn
/// </summary>
/// <param name="masExcelDrawStatType"></param>
public void SetExcelDrawStatType(masExcelDrawStatType masExcelDrawStatType)
{
_masExcelDrawStatType = masExcelDrawStatType;
}
private XlChartType GetXlCharTypeFromDrawStatType(masExcelDrawStatType charType)
{
switch (charType)
{
case masExcelDrawStatType.xl3DArea:
return(XlChartType.xl3DArea);
case masExcelDrawStatType.xl3DAreaStacked:
return(XlChartType.xl3DAreaStacked);
case masExcelDrawStatType.xl3DAreaStacked100:
return(XlChartType.xl3DAreaStacked100);
case masExcelDrawStatType.xl3DBarClustered:
return(XlChartType.xl3DBarClustered);
case masExcelDrawStatType.xl3DBarStacked:
return(XlChartType.xl3DBarStacked);
case masExcelDrawStatType.xl3DBarStacked100:
return(XlChartType.xl3DBarStacked100);
case masExcelDrawStatType.xl3DColumn:
return(XlChartType.xl3DColumn);
case masExcelDrawStatType.xl3DColumnClustered:
return(XlChartType.xl3DColumnClustered);
case masExcelDrawStatType.xl3DColumnStacked:
return(XlChartType.xl3DColumnStacked);
case masExcelDrawStatType.xl3DColumnStacked100:
return(XlChartType.xl3DColumnStacked100);
case masExcelDrawStatType.xl3DLine:
return(XlChartType.xl3DLine);
case masExcelDrawStatType.xl3DPie:
return(XlChartType.xl3DPie);
case masExcelDrawStatType.xl3DPieExploded:
return(XlChartType.xl3DPieExploded);
case masExcelDrawStatType.xlArea:
return(XlChartType.xlArea);
case masExcelDrawStatType.xlAreaStacked:
return(XlChartType.xlAreaStacked);
case masExcelDrawStatType.xlAreaStacked100:
return(XlChartType.xlAreaStacked100);
case masExcelDrawStatType.xlBarClustered:
return(XlChartType.xlBarClustered);
case masExcelDrawStatType.xlBarOfPie:
return(XlChartType.xlBarOfPie);
case masExcelDrawStatType.xlBarStacked:
return(XlChartType.xlBarStacked);
case masExcelDrawStatType.xlBarStacked100:
return(XlChartType.xlBarStacked100);
case masExcelDrawStatType.xlBubble:
return(XlChartType.xlBubble);
case masExcelDrawStatType.xlBubble3DEffect:
return(XlChartType.xlBubble3DEffect);
case masExcelDrawStatType.xlColumnClustered:
return(XlChartType.xlColumnClustered);
case masExcelDrawStatType.xlColumnStacked:
return(XlChartType.xlColumnStacked);
case masExcelDrawStatType.xlColumnStacked100:
return(XlChartType.xlColumnStacked100);
case masExcelDrawStatType.xlConeBarClustered:
return(XlChartType.xlConeBarClustered);
case masExcelDrawStatType.xlConeBarStacked:
return(XlChartType.xlConeBarStacked);
case masExcelDrawStatType.xlConeBarStacked100:
return(XlChartType.xlConeBarStacked100);
case masExcelDrawStatType.xlConeCol:
return(XlChartType.xlConeCol);
case masExcelDrawStatType.xlConeColClustered:
return(XlChartType.xlConeColClustered);
case masExcelDrawStatType.xlConeColStacked:
return(XlChartType.xlConeColStacked);
case masExcelDrawStatType.xlConeColStacked100:
return(XlChartType.xlConeColStacked100);
case masExcelDrawStatType.xlCylinderBarClustered:
return(XlChartType.xlCylinderBarClustered);
case masExcelDrawStatType.xlCylinderBarStacked:
return(XlChartType.xlCylinderBarStacked);
case masExcelDrawStatType.xlCylinderBarStacked100:
return(XlChartType.xlCylinderBarStacked100);
case masExcelDrawStatType.xlCylinderCol:
return(XlChartType.xlCylinderCol);
case masExcelDrawStatType.xlCylinderColClustered:
return(XlChartType.xlCylinderColClustered);
case masExcelDrawStatType.xlCylinderColStacked:
return(XlChartType.xlCylinderColStacked);
case masExcelDrawStatType.xlCylinderColStacked100:
return(XlChartType.xlCylinderColStacked100);
case masExcelDrawStatType.xlDoughnut:
return(XlChartType.xlDoughnut);
case masExcelDrawStatType.xlDoughnutExploded:
return(XlChartType.xlDoughnutExploded);
case masExcelDrawStatType.xlLine:
return(XlChartType.xlLine);
case masExcelDrawStatType.xlLineMarkers:
return(XlChartType.xlLineMarkers);
case masExcelDrawStatType.xlLineMarkersStacked:
return(XlChartType.xlLineMarkersStacked);
case masExcelDrawStatType.xlLineMarkersStacked100:
return(XlChartType.xlLineMarkersStacked100);
case masExcelDrawStatType.xlLineStacked:
return(XlChartType.xlLineStacked);
case masExcelDrawStatType.xlLineStacked100:
return(XlChartType.xlLineStacked100);
case masExcelDrawStatType.xlPie:
return(XlChartType.xlPie);
case masExcelDrawStatType.xlPieExploded:
return(XlChartType.xlPieExploded);
case masExcelDrawStatType.xlPieOfPie:
return(XlChartType.xlPieOfPie);
case masExcelDrawStatType.xlPyramidBarClustered:
return(XlChartType.xlPyramidBarClustered);
case masExcelDrawStatType.xlPyramidBarStacked:
return(XlChartType.xlPyramidBarStacked);
case masExcelDrawStatType.xlPyramidBarStacked100:
return(XlChartType.xlPyramidBarStacked100);
case masExcelDrawStatType.xlPyramidCol:
return(XlChartType.xlPyramidCol);
case masExcelDrawStatType.xlPyramidColClustered:
return(XlChartType.xlPyramidColClustered);
case masExcelDrawStatType.xlPyramidColStacked:
return(XlChartType.xlPyramidColStacked);
case masExcelDrawStatType.xlPyramidColStacked100:
return(XlChartType.xlPyramidColStacked100);
case masExcelDrawStatType.xlRadar:
return(XlChartType.xlRadar);
case masExcelDrawStatType.xlRadarFilled:
return(XlChartType.xlRadarFilled);
case masExcelDrawStatType.xlRadarMarkers:
return(XlChartType.xlRadarMarkers);
case masExcelDrawStatType.xlStockHLC:
return(XlChartType.xlStockHLC);
case masExcelDrawStatType.xlStockOHLC:
return(XlChartType.xlStockOHLC);
case masExcelDrawStatType.xlStockVHLC:
return(XlChartType.xlStockVHLC);
case masExcelDrawStatType.xlStockVOHLC:
return(XlChartType.xlStockVOHLC);
case masExcelDrawStatType.xlSurface:
return(XlChartType.xlSurface);
case masExcelDrawStatType.xlSurfaceTopView:
return(XlChartType.xlSurfaceTopView);
case masExcelDrawStatType.xlSurfaceTopViewWireframe:
return(XlChartType.xlSurfaceTopViewWireframe);
case masExcelDrawStatType.xlSurfaceWireframe:
return(XlChartType.xlSurfaceWireframe);
case masExcelDrawStatType.xlXYScatter:
return(XlChartType.xlXYScatter);
case masExcelDrawStatType.xlXYScatterLines:
return(XlChartType.xlXYScatterLines);
case masExcelDrawStatType.xlXYScatterLinesNoMarkers:
return(XlChartType.xlXYScatterLinesNoMarkers);
case masExcelDrawStatType.xlXYScatterSmooth:
return(XlChartType.xlXYScatterSmooth);
case masExcelDrawStatType.xlXYScatterSmoothNoMarkers:
return(XlChartType.xlXYScatterSmoothNoMarkers);
default:
return(XlChartType.xl3DArea);
}
}
public void DrawStat(int dataBRow, int dataBCol, int dataERow, int dataECol, string workSheetName, string title, string xName, string yName, masExcelDrawStatType charType, bool displayDataLabel, bool isUnion, int unionBRow, int unionBCol, byte baseCol)
{
bool addNewSheet = false;
Excel.Worksheet xlSheet = null;
foreach (Worksheet sheet in workbook.Worksheets)
{
if (sheet.Name == workSheetName)
{
xlSheet = sheet;
break;
}
}
if (xlSheet == null)
{
xlSheet = AddNewSheet(workSheetName);
addNewSheet = true;
}
if (xlSheet == null)
{
MsgBox.ShowInfo("Excel添加新工作薄失败!");
return;
}
Range range = null;
if (baseCol != 1)
{
range = worksheet.get_Range(worksheet.Cells[dataERow, dataECol] as Excel.Range, worksheet.Cells[dataBRow, dataBCol] as Excel.Range);
}
else
{
range = worksheet.get_Range(worksheet.Cells[dataERow, dataECol] as Excel.Range, worksheet.Cells[dataBRow, dataBCol + 1] as Excel.Range);
}
int rangCount = range.Rows.Count;
if (isUnion)
{
if (baseCol != 1)
{
range = xlApp.Union(worksheet.get_Range(worksheet.Cells[unionBRow, dataECol] as Excel.Range, worksheet.Cells[unionBRow, unionBCol] as Excel.Range), range);
}
else
{
range = xlApp.Union(worksheet.get_Range(worksheet.Cells[unionBRow, dataECol] as Excel.Range, worksheet.Cells[unionBRow, dataBCol + 1] as Excel.Range), range);
range = xlApp.Union(worksheet.get_Range(worksheet.Cells[unionBRow, unionBCol] as Excel.Range, worksheet.Cells[unionBRow, unionBCol] as Excel.Range), range);
range = xlApp.Union(worksheet.get_Range(worksheet.Cells[dataERow, unionBCol] as Excel.Range, worksheet.Cells[dataBRow, unionBCol] as Excel.Range), range);
}
rangCount += 1;
}
Excel.ChartObjects chartObjects = (Excel.ChartObjects)xlSheet.ChartObjects(Type.Missing);
Excel.ChartObject chartObj = chartObjects.Add(5, 300 * _count + 20, (dataECol - dataBCol) * GetWidth() * (rangCount - 1) + 200, 250);
Excel.Chart xlChart = chartObj.Chart;
XlChartType xlCharType = GetXlCharTypeFromDrawStatType(charType);
xlChart.ChartWizard(range, xlCharType, Type.Missing, baseCol == 1 ? Excel.XlRowCol.xlColumns : Excel.XlRowCol.xlRows,
1, 1, true, title, xName, yName, "");
if (displayDataLabel)
{
for (int i = 0; i < rangCount - 1; i++)
{
((Excel.Series)xlChart.SeriesCollection(i + 1)).ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue,
missing, missing, missing);
}
}
if (!addNewSheet)
{
_count++;
}
}
public void DrawStat(int dataBRow, int dataBCol, int dataERow, int dataECol, string workSheetName, string title, string xName, string yName, masExcelDrawStatType charType, bool displayDataLabel, bool hasLegend)
{
DrawStat(dataBRow, dataBCol, dataERow, dataECol, workSheetName, title, xName, yName, charType, displayDataLabel, (byte)1, hasLegend);
}
public void DrawStat(int dataBRow, int dataBCol, int dataERow, int dataECol, string workSheetName, string title, string xName, string yName, masExcelDrawStatType charType, bool displayDataLabel, byte baseCol, bool hasLegend)
{
Excel.Worksheet xlSheet = null;
foreach (Worksheet sheet in workbook.Worksheets)
{
if (sheet.Name == workSheetName)
{
xlSheet = sheet;
break;
}
}
if (xlSheet == null)
{
xlSheet = AddNewSheet(workSheetName);
}
if (xlSheet == null)
{
//MsgBox.ShowInfo("Excel添加新工作薄失败!");
return;
}
Excel.ChartObjects chartObjects = (Excel.ChartObjects)xlSheet.ChartObjects(Type.Missing);
Excel.ChartObject chartObj = chartObjects.Add(5, 300 * _count + 20, (dataECol - dataBCol) * GetWidth() * (dataERow - dataBRow) + 200, 250);
Excel.Chart xlChart = chartObj.Chart;
XlChartType xlCharType = GetXlCharTypeFromDrawStatType(charType);
Range range = worksheet.get_Range(worksheet.Cells[dataERow, dataECol] as Excel.Range, worksheet.Cells[dataBRow, dataBCol] as Excel.Range);
xlChart.ChartWizard(range, xlCharType, Type.Missing, baseCol == 1 ? Excel.XlRowCol.xlColumns : Excel.XlRowCol.xlRows,
1, 1, hasLegend, title, xName, yName, "");
//xlChart.SetSourceData(range, Excel.XlRowCol.xlColumns);
//xlChart.ChartTitle.Text = title;
//xlChart.ChartTitle.Caption
//xlChart.HasLegend = true;
if (displayDataLabel)
{
for (int i = 0; i < dataERow - dataBRow; i++)
{
((Excel.Series)xlChart.SeriesCollection(i + 1)).ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue,
missing, missing, missing);
}
}
_count++;
}
public void Init(masExcelDrawStatType drawType)
{
_masExcelDrawStatType = drawType;
CreateExcelWnd();
}