首先要添加NPOI.dll文件
然后添加类：NPOIHelper.cs
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Text;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using NPOI.DDF;
using NPOI.SS.UserModel;
using NPOI.SS.Util; namespace WeixinService.Bll
{
public class NPOIHelper
{
public NPOIHelper()
{ } /// </summary>
/// <param name="dt"> 数据源</param>
/// <returns>stream</returns>
public static Stream StreamData(DataTable dt, string sheetname, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = null; HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽
int[] arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
} sheet = (HSSFSheet)workbook.CreateSheet(sheetname);
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dt.Columns.Count - 1));
headerRow = null;
//headerRow.Dispose();
}
#endregion #region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); //遍历datatable，将datatable的列名赋予sheet
headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
headerRow = null;
}
#endregion int index = 2; //表头和列头已经占用一行，所以从2开始
foreach (DataRow row in dt.Rows)
{
HSSFRow datarow = (HSSFRow)sheet.CreateRow(index); foreach (DataColumn column in dt.Columns)
{
// row.CreateCell(column.Ordinal).SetCellValue(datarow[column].ToString()); //遍历并将值放入sheet HSSFCell newCell = (HSSFCell)datarow.CreateCell(column.Ordinal); // 实例化cell string drValue = row[column].ToString();
if (drValue == null || drValue == "")
{
newCell.SetCellValue("");
continue;
} switch (column.DataType.ToString())
{
case "System.String"://字符串类型
case "System.DateTime"://日期类型
newCell.SetCellValue(drValue);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
case "System.Float":
case "System.Single":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break; case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
index++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//headerrow = null;
//workbook = null;
workbook.Dispose();
return ms;
} public void SetColWidth()
{ } /// <summary>
/// Datatable数据填充如excel
/// </summary>
/// <param name="filename">excel文件名</param>
/// <param name="dt"> 数据源</param>
/// <param name="Response"> response响应</param>
/// <param name="headerStr"> 表头标题</param>
public static void DataTableToExcel(string filename, DataTable dt, string sheetname, HttpResponse Response, string headerStr)
{
MemoryStream ms = StreamData(dt, sheetname, headerStr) as MemoryStream; //as MemoryStream as用作转换,此处可以省略
try
{ Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = Encoding.UTF8;
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename + ".xls"));
Response.AddHeader("content-length", ms.Length.ToString());
Byte[] data = ms.ToArray(); //文件写入采用二进制流的方式。所以此处要转换为字节数组
Response.BinaryWrite(data);
}
catch
{
Response.Clear();
Response.ClearHeaders();
Response.Write("<script language=javascript>alert( '导出Excel错误'); </script>");
}
Response.Flush();
Response.Close();
Response.End();
ms = null;
} }
} 最后就是利用这个类来使用了：
/// <summary>
/// 导出查询数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportQueryExcel_Click(object sender, EventArgs e)
{
var kssj = Request.Params["kssj"];
var jssj = Request.Params["jssj"];
var hh = Request.Params["hh"];
try
{
var dataTable = _userRegDal.QueryUserReg(kssj, jssj, hh);
dataTable.Columns[0].ColumnName = "用户号";
dataTable.Columns[1].ColumnName = "联系电话";
dataTable.Columns[2].ColumnName = "绑定时间";
NPOIHelper.DataTableToExcel("用户绑定查询数据", dataTable, "数据表", Response, "用户绑定查询数据");
}
catch (Exception ex)
{
Log.Debug("方法名:Button1_Click,错误原因:" + ex.Message);
}
} /// <summary>
/// 导出全部数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportAllExcel_Click(object sender, EventArgs e)
{
try
{
var dataTable = _userRegDal.QueryUserRegAll();
dataTable.Columns[0].ColumnName = "用户号";
dataTable.Columns[1].ColumnName = "联系电话";
dataTable.Columns[2].ColumnName = "绑定时间";
NPOIHelper.DataTableToExcel("用户绑定全部数据", dataTable, "数据表", Response, "用户绑定全部数据");
}
catch (Exception ex)
{
Log.Debug("方法名:Button2_Click,错误原因:" + ex.Message);
}
} 以上是针对后台下载文件，现在针对前台下载文件，主要结合JQuery,如下 ：
$('#frmExport').attr('action', '/AjaxSwitchManage/ExportSwitch.cspx?exptInfo=' + arrdata);
$('#frmExport').submit();
return false;
[Action]
public object ExportSwitch(string exptInfo)
{ string[] arrdata = exptInfo.Split(','); string id = arrdata[];
string switchName = arrdata[];
string switchMac = arrdata[];
string switchIp = arrdata[];
string areaId = arrdata[];
if (areaId == "")
{
areaId = id;
} string[] titles = { "交换机名称", "型号", "MAC地址", "IP地址", "端口数", "是否控制", "控制方式", "是否已配置", "管理方式", "普通用户", "管理用户", "区域名" };
string[] columns = { "Name", "Type", "Mac", "Ip", "PortCount", "CtrlEnable", "CtrlMode", "HasConfiged", "UseType", "User", "AdminUser", "AreaName" };
int[] widths = { , , , , , , , , , , , };
var list = SwitchBLL.GetSwitchBySearchList(id, new SwitchInfoSearchInfo { AreaId = int.Parse(areaId), SwitchName = switchName, SwitchMac = switchMac, SwitchIp = switchIp });
NPOIHelper<SwitchBasicInfo> npoi = new NPOIHelper<SwitchBasicInfo>(titles, columns, widths, list);
try
{
using (MemoryStream ms = npoi.CommonToExcel() as MemoryStream)
{
string filename = HttpUtility.UrlEncode("交换机基本信息" + ".xls");
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", ("attachment;filename=" + filename));
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.Flush();
} }
catch (Exception ex)
{
throw new Exception("导出excel失败：" + ex.Message);
}
return "";
}