easyexcel多个sheet导入_NPOI实现多个dt数据导入一个EXCEL的多SHEET | 学步园

///

/// 导出EXCEL

///

/// 所有表名

/// 导出文件名

public static void ExportEasy(string[] sTableNames,string strFileName,string sStartDate,string sEndDate,string sStartHour,string sEndHour)

{

HSSFWorkbook workbook = new HSSFWorkbook();

for (int iTCount = 0; iTCount < sTableNames.Length; iTCount++)

{

DBTables dbs = new DBTables();

string[] sFieldName=new string[3];

List lstFieldName = new List();

lstFieldName = dbs.GetColumnFieldList(sTableNames[iTCount]);

string sSql="";

if (lstFieldName.Contains("结束时间"))

{

sSql = "SELECT * FROM [" + sTableNames[iTCount] + "] WHERE 日期>='" + sStartDate + "' AND 日期<='" + sEndDate + "' AND 结束时间>=" + sStartHour + "AND 结束时间<=" + sEndDate;

}

else

{

sSql = "SELECT * FROM [" + sTableNames[iTCount] + "] WHERE 日期>='" + sStartDate + "' AND 日期<='" + sEndDate + "' AND 时间>=" + sStartHour + "AND 时间<=" + sEndDate;

}

DataSet ds = new DataSet();

string[] s = { sTableNames[iTCount] };

SqlHelper.FillDataset(Common.GetConnect(), CommandType.Text, sSql, ds, s);

DataTable dt = ds.Tables[sTableNames[iTCount]];

HSSFSheet sheet = workbook.CreateSheet(sTableNames[iTCount]);

//填充表头

HSSFRow dataRow = sheet.CreateRow(0);

foreach (DataColumn column in dt.Columns)

{

dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

}

//填充内容

for (int i = 0; i < dt.Rows.Count; i++)

{

dataRow = sheet.CreateRow(i + 1);

for (int j = 0; j < dt.Columns.Count; j++)

{

dataRow.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());

}

}

}

//保存

using (MemoryStream ms = new MemoryStream())

{

using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))

{

workbook.Write(fs);

}

}

}

///

/// 导出EXCEL,可以导出多个sheet

///

/// 原始数据数组类型

/// 路径

public static void ExportEasy(DataTable[] dtSources, string strFileName)

{

HSSFWorkbook workbook = new HSSFWorkbook();

for (int k = 0; k < dtSources.Length; k++)

{

HSSFSheet sheet = workbook.CreateSheet(dtSources[k].TableName.ToString());

//填充表头

HSSFRow dataRow = sheet.CreateRow(0);

foreach (DataColumn column in dtSources[k].Columns)

{

dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

}

//填充内容

for (int i = 0; i < dtSources[k].Rows.Count; i++)

{

dataRow = sheet.CreateRow(i + 1);

for (int j = 0; j < dtSources[k].Columns.Count; j++)

{

dataRow.CreateCell(j).SetCellValue(dtSources[k].Rows[i][j].ToString());

}

}

}

//保存

using (MemoryStream ms = new MemoryStream())

{

using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))

{

workbook.Write(fs);

}

}

}

///

/// 导出单个EXCEL

///

///

///

public static void ExportEasy(DataTable dtSource, string strFileName)

{

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet sheet = workbook.CreateSheet();

//填充表头

HSSFRow dataRow = sheet.CreateRow(0);

foreach (DataColumn column in dtSource.Columns)

{

dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

}

//填充内容

for (int i = 0; i < dtSource.Rows.Count; i++)

{

dataRow = sheet.CreateRow(i + 1);

for (int j = 0; j < dtSource.Columns.Count; j++)

{

dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());

}

}

//保存

using (MemoryStream ms = new MemoryStream())

{

using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))

{

workbook.Write(fs);

}

}

//workbook.Dispose();

}

}