先去官网：http://npoi.codeplex.com/下载需要引入dll（可以选择.net2.0或者.net4.0的dll），然后在网站中添加引用。
.Net导出代码：
/// <summary>
/// 导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void But_Excel_Click(object sender, EventArgs e)
{
string[] fileName = { "State_title", "Last_url", "State_url", "IsNewUser", "State_time", "IpAddress", "ExtStr1", "ExtStr2", "VisitTime", "VisitWay", "KeyWords", "UserAction", "Remark" };
string[] title = { "受访网页", "来源网页", "受访网页URL", "新老访客", "访问时长", "IP地址", "地域", "访客标识码", "访问时间", "浏览方式", "关键字", "用户行为", "备注" };
DataToExcel.ExportSiteState(fileName, title, "tb_SiteStats", "列表_" + DateTime.Now.ToString("yyy-MM-dd"), strWhere, " VisitTime desc ");
}
/// <summary>
/// add by lwf 2016-07-13 16:22:19
/// </summary>
/// <param name="fileName"></param>
/// <param name="title"></param>
/// <param name="tableName"></param>
/// <param name="sheetName"></param>
/// <param name="strWhere"></param>
/// <param name="sort"></param>
public static void ExportSiteState(string[] fileName, string[] title, string tableName, string sheetName, string strWhere, string sort)
{
string sql = "select ";
if (fileName == null || fileName.Length < )
{
sql += "* ";
}
else
{
foreach (string key in fileName)
{
sql += key + ",";
}
sql = System.Text.RegularExpressions.Regex.Replace(sql, ",$", " ");
}
sql += "from " + tableName;
if (!string.IsNullOrEmpty(strWhere))
{
sql += " where " + strWhere;
}
if (!string.IsNullOrEmpty(sort))
{
sql += " order by " + sort;
} //获取需要导出的数据
DataTable dt = DbHelperSQLServer.Query(sql).Tables[];
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = book.CreateSheet(sheetName);
NPOI.SS.UserModel.Row row = sheet.CreateRow(); //设置列名称和数据
if (title == null || title.Length < )
{
for (int i = ; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = ; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + );
for (int j = ; j < dt.Columns.Count; j++)
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
else
{
for (int i = ; i < title.Length; i++)
{
row.CreateCell(i).SetCellValue(title[i].ToString());
}
for (int i = ; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + );
for (int j = ; j < fileName.Length; j++)
row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Export_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"));
System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
DataTable转换成Excel文档流(导出数据量超出65535条,分sheet)：
using System;
using System.Collections.Generic;
using System.Text;
using Maticsoft.DBUtility;
using System.Collections;
using System.Data;
using System.Web;
using DAL;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;
namespace DAL
{
public class DataToExcel
{/// <summary>
/// DataTable转换成Excel文档流，并输出到客户端
/// </summary>
/// <param name="table"></param>
/// <param name="response"></param>
/// <param name="fileName">输出的文件名</param>
public static void RenderToDataTableToExcel(DataTable table,HttpContext context, string fileName)
{
using (MemoryStream ms = ExportDataTableToExcel(table))
{
RenderToBrowser(ms, context, fileName);
}
} /// <summary>
/// DataTable转换成Excel文档流(导出数据量超出65535条,分sheet)
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static MemoryStream ExportDataTableToExcel(DataTable sourceTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
int dtRowsCount = sourceTable.Rows.Count;
int SheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtRowsCount) / ));
int SheetNum = ;
int rowIndex = ;
int tempIndex = ; //标示
ISheet sheet = workbook.CreateSheet("sheet1" + SheetNum);
for (int i = ; i < dtRowsCount; i++)
{
if (i == || tempIndex == )
{
IRow headerRow = sheet.CreateRow();
foreach (DataColumn column in sourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(tempIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(sourceTable.Rows[i][column].ToString());
}
if (tempIndex == )
{
SheetNum++;
sheet = workbook.CreateSheet("sheet" + SheetNum);//
tempIndex = ;
}
rowIndex++;
tempIndex++;
//AutoSizeColumns(sheet);
}
workbook.Write(ms);
ms.Flush();
ms.Position = ;
sheet = null;
// headerRow = null;
workbook = null;
return ms;
} /// <summary>
/// 输出文件到浏览器
/// </summary>
/// <param name="ms">Excel文档流</param>
/// <param name="context">HTTP上下文</param>
/// <param name="fileName">文件名</param>
private static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
{
if (context.Request.Browser.Browser == "IE")
fileName = HttpUtility.UrlEncode(fileName);
context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
context.Response.BinaryWrite(ms.ToArray());
}
}
}
.NET导入代码：
HSSFWorkbook hssfworkbook;
#region
public DataTable ImportExcelFile(string filePath)
{
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt();
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = ; j < (sheet.GetRow().LastCellNum); j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = ; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.Cell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return dt;
}
#endregion