asp.net使用npoi导入导出Excel前台后台代码，刷爆朋友圈！

1,引入npoi,dll。

前端：创建Web页面 创建按钮如下

后端

protected void btnExport_Click(object sender, EventArgs e)

{

try

{

string sql = "实际业务涉及的sql语句 "// 例如sql=select kpsubject as 开票主体,kptype as 开票类型 from talbe;

DataTable dt1 = dal,GetDataBySql(sql);//查询获取数据，用实际业务代码替换

if (dt1 == null || dt1,Rows,Count == 0)

{

Response,Write("");

}

else

{

string fileRpath = Server,MapPath("/Excel/") + DateTime,Now,ToString("yyyyMMddHHssmm") + ",xls";

//注意在根目录下创建Excel文件夹

ExcelHelper,TableToExcel(dt1, fileRpath);

//ExcelHelper 导出帮助类 见步骤四

string name = System,IO,Path,GetFileName(fileRpath);

FileStream files = new FileStream(fileRpath, FileMode,Open, FileAccess,Read, FileShare,Read);

byte[] byteFile = null;

if (files,Length == 0)

{

byteFile = new byte[1];

}

else

{

byteFile = new byte[files,Length];

}

files,Read(byteFile, 0, (int)byteFile,Length);

files,Close();

ExportByWeb(byteFile, "测试导出,xls");//从网站下载excel

}

}

catch (Exception ex)

{

throw ex;

}

}

public static void ExportByWeb(byte[] byteFile,string strFileName)

{

HttpContext curContext = HttpContext,Current;

// 设置编码和附件格式

curContext,Response,ContentType = "application/vnd,ms-excel";

curContext,Response,ContentEncoding = Encoding,UTF8;

curContext,Response,Charset = "";

curContext,Response,AppendHeader("Content-Disposition",

"attachment;filename=" + HttpUtility,UrlEncode(strFileName, Encoding,UTF8));

curContext,Response,BinaryWrite(byteFile);

curContext,Response,End();

}

ExcelHelper 导出Excel 帮助类

public static class ExcelHelper

{

#region 私有方法

///

/// 导入路径(包含文件名与扩展名)

///

public static DataTable ExcelToTable(string file)

{

DataTable dt = new DataTable();

IWorkbook workbook;

string fileExt = Path,GetExtension(file),ToLower();

using (FileStream fs = new FileStream(file, FileMode,Open, FileAccess,Read))

{

//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式

if (fileExt == ",xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ",xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }

if (workbook == null) { return null; }

ISheet sheet = workbook,GetSheetAt(0);

//表头

IRow header = sheet,GetRow(sheet,FirstRowNum);

List columns = new List();

for (int i = 0; i < header,LastCellNum; i++)

{

object obj = GetValueType(header,GetCell(i));

if (obj == null || obj,ToString() == string,Empty)

{

dt,Columns,Add(new DataColumn("Columns" + i,ToString()));

}

else

dt,Columns,Add(new DataColumn(obj,ToString()));

columns,Add(i);

}

//数据

for (int i = sheet,FirstRowNum + 1; i <= sheet,LastRowNum; i++)

{

DataRow dr = dt,NewRow();

bool hasValue = false;

foreach (int j in columns)

{

dr[j] = GetValueType(sheet,GetRow(i),GetCell(j));

if (dr[j] ,= null && dr[j],ToString() ,= string,Empty)

{

hasValue = true;

}

}

if (hasValue)

{

dt,Rows,Add(dr);

}

}

}

return dt;

}

public static DataSet ExcelToDataSet(string file)

{

DataSet ds = new DataSet();

// DataTable dt = new DataTable();

IWorkbook workbook;

string fileExt = Path,GetExtension(file),ToLower();

using (FileStream fs = new FileStream(file, FileMode,Open, FileAccess,Read))

{

//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式

if (fileExt == ",xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ",xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }

if (workbook == null) { return null; }

DataTable[] dts = new DataTable[workbook,NumberOfSheets];

dts[0] = new DataTable();

dts[1] = new DataTable();

for (int j = 0; j < workbook,NumberOfSheets; j++)

{

ISheet sheet = workbook,GetSheetAt(j);

IRow header = sheet,GetRow(sheet,FirstRowNum);

List columns = new List();

for (int i = 0; i < header,LastCellNum; i++)

{

object obj = GetValueType(header,GetCell(i));

if (obj == null || obj,ToString() == string,Empty)

{

dts[j],Columns,Add(new DataColumn("Columns" + i,ToString()));

}

else

dts[j],Columns,Add(new DataColumn(obj,ToString()));

columns,Add(i);

}

//数据

for (int i = sheet,FirstRowNum + 1; i <= sheet,LastRowNum; i++)

{

DataRow dr = dts[j],NewRow();

bool hasValue = false;

foreach (int k in columns)

{

dr[k] = GetValueType(sheet,GetRow(i),GetCell(k));

if (dr[k] ,= null && dr[k],ToString() ,= string,Empty)

{

hasValue = true;

}

}

if (hasValue)

{

dts[j],Rows,Add(dr);

}

}

ds,Tables,Add(dts[j]);

}

//ISheet sheet = workbook,GetSheetAt(0);

//表头

}

return ds;

}

///

///

/// 导出路径(包括文件名与扩展名)

public static void TableToExcel(DataTable dt, string file)

{

IWorkbook workbook;

string fileExt = Path,GetExtension(file),ToLower();

if (fileExt == ",xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ",xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }

if (workbook == null) { return; }

ISheet sheet = string,IsNullOrEmpty(dt,TableName) ? workbook,CreateSheet("Sheet1") : workbook,CreateSheet(dt,TableName);

//表头

IRow row = sheet,CreateRow(0);

for (int i = 0; i < dt,Columns,Count; i++)

{

ICell cell = row,CreateCell(i);

cell,SetCellValue(dt,Columns[i],ColumnName);

}

//数据

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

{

IRow row1 = sheet,CreateRow(i + 1);

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

{

ICell cell = row1,CreateCell(j);

cell,SetCellValue(dt,Rows[i][j],ToString());

}

}

//转为字节数组

MemoryStream stream = new MemoryStream();

workbook,Write(stream);

var buf = stream,ToArray();

//保存为Excel文件

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

{

fs,Write(buf, 0, buf,Length);

fs,Flush();

}

}

///

///

///

private static object GetValueType(ICell cell)

{

if (cell == null)

return null;

switch (cell,CellType)

{

case CellType,Blank: //BLANK:

return null;

case CellType,Boolean: //BOOLEAN:

return cell,BooleanCellValue;

case CellType,Numeric: //NUMERIC:

return cell,NumericCellValue;

case CellType,String: //STRING:

return cell,StringCellValue;

case CellType,Error: //ERROR:

return cell,ErrorCellValue;

case CellType,Formula: //FORMULA:

default:

return "=" + cell,CellFormula;

}

}

#endregion

}