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