C# 或 JQuery导出Excel
首先要添加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[0]; string switchName = arrdata[1]; string switchMac = arrdata[2]; string switchIp = arrdata[3]; string areaId = arrdata[4]; if (areaId == "") { areaId = id; } string[] titles = { "交换机名称", "型号", "MAC地址", "IP地址", "端口数", "是否控制", "控制方式", "是否已配置", "管理方式", "普通用户", "管理用户", "区域名" }; string[] columns = { "Name", "Type", "Mac", "Ip", "PortCount", "CtrlEnable", "CtrlMode", "HasConfiged", "UseType", "User", "AdminUser", "AreaName" }; int[] widths = { 100, 80, 100, 100, 60, 60, 60, 60, 60, 100, 100, 80 }; 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 ""; }
