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()        {        }        /// </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);           }       }

C# 匯出Excel