首页 > 代码库 > C# 导出Excel
C# 导出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 = http://www.mamicode.com/row[column].ToString();"color: blue;">if(drValue =http://www.mamicode.com/= null || drValue =http://www.mamicode.com/= "") { 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 = http://www.mamicode.com/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); } }
C# 导出Excel
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。