C# NPOI导出dataset----Excel绘制Chart图表

仅限XLSX 2007以后版本（2007之前版本不支持）

1、判断文件夹是否存在，不存在则创建

//Application.StartupPath当前项目根目录
if (!Directory.Exists(Application.StartupPath + @"\Excel"))
{
//创建文件夹
Directory.CreateDirectory(Application.StartupPath + @"\Excel");
}

2、安装NPOI

3、创建Excel工作簿

string time = DateTime.Now.ToString("yyyyMMddHHmmss");

IWorkbook workbook = new XSSFWorkbook();//创建工作簿
ISheet sheet1 = null;//表实例
string path = Application.StartupPath + @"\Excel\分析文件" + time + ".xlsx";//保存路径

4、循环导出Dataset将每个datatable创建新的sheet页

int count = ds.Tables.Count;
DataTable dt = null;
for (int s = 0; s < count; s++)
{
dt = ds.Tables[s];
//创建新的sheet
sheet1 = workbook.CreateSheet(dt.TableName);

//将DataSet导出为Excel
if (dt != null && dt.Rows.Count > 0)
{
int rowCount = dt.Rows.Count;//行数
int columnCount = dt.Columns.Count;//列数

//设置列头
IRow row = sheet1.CreateRow(0);//excel第一行设为列头
for (int c = 0; c < columnCount; c++)
{
ICell cell = row.CreateCell(c);
cell.SetCellValue(dt.Columns[c].ColumnName + s);
}
//设置每行每列的单元格,
for (int i = 0; i < rowCount; i++)
{
row = sheet1.CreateRow(i + 1);
for (int j = 0; j < columnCount; j++)
{
ICell cell = row.CreateCell(j);//excel第二行开始写入数据
if (dt.Rows[i][j].ToString().Length > 0)
{
cell.SetCellValue((double)Convert.ToDecimal((dt.Rows[i][j])));
}
else
{
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
}

//绘制chart统计图

CreateChart(sheet1, rowCount, dt.TableName);

}
}

5、向文件写入数据

using (FileStream fs = File.Open(path, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
fs.Close();
}

绘制折线图

static void CreateChart(ISheet sheet, int colcount, string tablename)
{

IDrawing drawing = sheet.CreateDrawingPatriarch();
//锚点（第10-23列第2-25行）
IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, 10, 2, 23, 25);
IChart chart = drawing.CreateChart(anchor) as XSSFChart;

chart.SetTitle("成绩统计");

//生成图例
var legend = chart.GetOrCreateLegend();
//表体位置
legend.Position = LegendPosition.Bottom;

//图表
var data = chart.ChartDataFactory.CreateScatterChartData<double, double>(); //散点图

// X轴.
var bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
bottomAxis.IsVisible = true; //默认为true 不显示设置为fase 显示坐标轴

//Y轴
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = (AxisCrosses.AutoZero);
leftAxis.IsVisible = true; //设置显示坐标轴

//数据源
IChartDataSource<double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, colcount, 0, 0));
IChartDataSource<double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, colcount, 1, 1));
IChartDataSource<double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, colcount, 2, 2));
IChartDataSource<double> ys3 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, colcount, 3, 3));
IChartDataSource<double> ys4 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, colcount, 4, 4));

//数据系列
var s1 = data.AddSeries(xs, ys1);
s1.SetTitle("语文分数");
var s2 = data.AddSeries(xs, ys2);
s2.SetTitle("数学分数");
var s3 = data.AddSeries(xs, ys3);
s3.SetTitle("英语分数");
chart.Plot(data, bottomAxis, leftAxis);

}