機車物語

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;

using System.Web.UI;

using System.IO;

///<summary>

/// csExcel 的摘要描述

///</summary>

public class csExcel

{

   ///<summary>

   ///取得數字的style(style有限制數量)

   ///</summary>

   ///<param name="tBook"></param>

   ///<returns></returns>

   public static ICellStyle getStyleNumber(HSSFWorkbook tBook)

   {

       ICellStyle tStyle = tBook.CreateCellStyle();

       tStyle.DataFormat = tBook.CreateDataFormat().GetFormat("#,##0");

       return tStyle;

   }

   ///<summary>

   ///取得百分比的style(style有限制數量)

   ///</summary>

   ///<param name="tBook"></param>

   ///<returns></returns>

   public static ICellStyle getStylePercent(HSSFWorkbook tBook)

   {

       ICellStyle tStyle = tBook.CreateCellStyle();

       tStyle.DataFormat = tBook.CreateDataFormat().GetFormat("0.00%");

       return tStyle;

   }

   ///<summary>

   ///設定千分符

   ///</summary>

   ///<param name="tBook"></param>

   ///<param name="tCell"></param>

   ///<param name="tValue"></param>

   public static void setCellNumber(HSSFWorkbook tBook, ICell tCell, ICellStyle tStyle, object tValue)

   {

       tCell.SetCellType(CellType.NUMERIC);

       tCell.CellStyle = tStyle;

       tCell.SetCellValue(Convert.ToDouble(tValue));

   }

   ///<summary>

   ///字串格式

   ///</summary>

   ///<param name="tBook"></param>

   ///<param name="tCell"></param>

   ///<param name="tValue"></param>

   public static void setCellString(ICell tCell, object tValue)

   {

       tCell.SetCellType(CellType.STRING);

       tCell.SetCellValue(tValue.ToString());

   }

   ///<summary>

   ///百分比格式

   ///</summary>

   ///<param name="tBook"></param>

   ///<param name="tCell"></param>

   ///<param name="tValue"></param>

   public static void setCellPercent(HSSFWorkbook tBook, ICell tCell, ICellStyle tStyle, object tValue)

   {

       tCell.CellStyle = tStyle;

       tCell.SetCellValue(Convert.ToDouble(tValue) / 100);

   }

   //設定抬頭

   public static ISheet setTitle(HSSFWorkbook tBook, string tSheetName, List<string> tList)

   {

       var tSheet = tBook.CreateSheet(tSheetName);

       int tC = 0;

       tSheet.CreateFreezePane(0, 1, 0, 1);

       ICellStyle tStyle = tBook.CreateCellStyle();

       tStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;

       tStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;

       tStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;

       tStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;

       var rowTitle = tSheet.CreateRow(0);

       foreach (var i in tList)

       {

           var tCell = rowTitle.CreateCell(tC);

           tCell.SetCellValue(i);

           tCell.CellStyle = tStyle;

           tC++;

       }

       return tSheet;

   }

   //設定下載

   public static void setDown(Page tP, HSSFWorkbook tBook, string tName)

   {

       using (MemoryStream ms = new MemoryStream())

       {

           tP.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + tP.Server.UrlEncode(tName + "_") + DateTime.Now.ToString("yyyy.MM.dd HH-mm-ss") + ".xls"));

           tBook.Write(ms);

            tP.Response.BinaryWrite(ms.ToArray());

           tBook = null;

       }

   }

}