C# 导出Excel

标签：style   http   io   color   os   ar   使用   java   for   

首先要添加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()
        {

        }


        /// 
        /// 数据源
        /// stream
        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 for 

(int j = 0; 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() { } ///

///

Datatable数据填充如excel /// /// excel文件名 /// 数据源 /// response响应 /// 表头标题 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(""); } Response.Flush(); Response.Close(); Response.End(); ms = null; } } } 最后就是利用这个类来使用了：

/// 

       /// 导出查询数据
       /// 
       /// 
       /// 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);
           }
       }

       /// 

       /// 导出全部数据
       /// 
       /// 
       /// 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

标签：style   http   io   color   os   ar   使用   java   for   

原文地址：http://www.cnblogs.com/zhangwei595806165/p/4054594.html