NPOI使用

會使用這個元件的主要原因是因為要在一個Excel檔裡建立多個sheet的資料，底下是整理出的範例

using System;
using System.Web;
using System.IO;
using NPOI;
using NPOI.HSSF;
using NPOI.HSSF.Util;
using NPOI.HSSF.Model;
using NPOI.HSSF.UserModel;

public class filedownload : IHttpHandler
{
public void ProcessRequest (HttpContext context)
{
    MemoryStream ms = new MemoryStream();
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.CreateSheet("sample");
    HSSFCell cell = null;
    HSSFCellStyle borderStyle = null;
    HSSFCellStyle colorStyle = null;
    HSSFCellStyle fontStyle = null;
    HSSFCellStyle heightStyle = null;
    HSSFCellStyle spanStyle = null;
    HSSFCellStyle wrapStyle = null;
    HSSFFont font = null;

    borderStyle = workbook.CreateCellStyle();
    colorStyle = workbook.CreateCellStyle();
    fontStyle = workbook.CreateCellStyle();
    heightStyle = workbook.CreateCellStyle();
    spanStyle = workbook.CreateCellStyle();
    wrapStyle = workbook.CreateCellStyle();

    //Style設定    
    borderStyle.BorderTop = HSSFCellStyle.BORDER_THIN;
    borderStyle.BorderLeft = HSSFCellStyle.BORDER_THIN;
    borderStyle.BorderBottom = HSSFCellStyle.BORDER_THIN;
    borderStyle.BorderRight = HSSFCellStyle.BORDER_THIN;
    colorStyle.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
    colorStyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
    fontStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
    fontStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
    heightStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
    heightStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
    spanStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
    spanStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
    wrapStyle.WrapText = true;

    //指定紙張大小 A3=8, A4=9, Letter=1
    sheet.PrintSetup.PaperSize = 9;

    //指定直式或橫式 true=橫式 false=直式
    sheet.PrintSetup.Landscape = true;

    //藏隱格線
    sheet.DisplayGridlines = false;

    //設定欄寬 
    sheet.SetColumnWidth(0, 24 * 256);
    sheet.SetColumnWidth(1, 24 * 256);

    //指定列高    
    cell = sheet.CreateRow(0).CreateCell(0);
    cell.SetCellValue("指定列高");
    cell.CellStyle = heightStyle;
    sheet.GetRow(0).HeightInPoints = 50;

    //字型大小
    font = workbook.CreateFont();
    font.FontHeightInPoints = 14;
    font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
    fontStyle.SetFont(font);
    cell = sheet.CreateRow(1).CreateCell(0);
    cell.CellStyle = fontStyle;
    cell.SetCellValue("字型大小14粗體");

    //合併儲存格
    cell = sheet.CreateRow(2).CreateCell(0);
    cell.SetCellValue("合併儲存格");
    cell.CellStyle = spanStyle;
    sheet.AddMergedRegion(new Region(2, 0, 3, 1));

    //Wrap
    cell = sheet.CreateRow(4).CreateCell(0);
    cell.SetCellValue(string.Format("換行{0}測試", System.Environment.NewLine));
    cell.CellStyle = wrapStyle;

    //增加邊框
    cell = sheet.CreateRow(5).CreateCell(1);
    cell.SetCellValue("邊框                  ");
    cell.CellStyle = borderStyle;

    //背景
    cell = sheet.CreateRow(6).CreateCell(0);
    cell.SetCellValue("背景");
    cell.CellStyle = colorStyle;

    //插入分頁
    sheet.SetRowBreak(cell.RowIndex);

    //下一頁資料
    cell = sheet.CreateRow(7).CreateCell(0);
    cell.SetCellValue("下一頁資料");

    workbook.Write(ms);    

    context.Response.ContentType = "application/vnd.ms-excel";
    context.Response.AddHeader("Content-Disposition", "attachment; filename=myExcel.xls");
    context.Response.BinaryWrite(ms.ToArray());
}

public bool IsReusable
{
    get
    {
        return false;
    }
}
}

使用NPOI要注意的地方是如果你要設定每個儲存格不同的儲格格式，需要建立多個HSSFCellStyle並且將它指派給儲存格


*相關參考


NPOI ( Web C+ + + + - IT技術文章 )


NPOI 1.2教程（目录） - tonyqus.cn - 博客园


NPOI - Discussions - New line in cell


[C#]NPOI匯出Excel遇到資料換行的問題 - gipi的學習筆記-我的職場觀念、IT部落格- 點部落

利用T-SQL去除字串最後一個逗號

DECLARE @str varchar(20)
SET @str = 'A,B,C,D,E,'

SELECT @str

--將字串反轉
SET @str = REVERSE(@str)
SELECT @str

--去除逗號
SET @str = CASE WHEN CHARINDEX(',', @str) = 1 THEN STUFF(@str, 1, 1, '') ELSE @str END
SELECT @str

--將字串反轉
SET @str = REVERSE(@str)
SELECT @str

那上面的語法與一般我們利用SUBSTRING(@str, 1, LEN(@str) - 1)有什麼不同?我們利用上面的語法時我們可以不用考慮字串變數的值(如NULL)及長度，改用SUBSTRING(@str, 1, LEN(@str) - 1)時要注意字串變數的長度另外還是要判斷最後一碼是否為逗號。如果讓筆者選擇利用T-SQL或程式來去除最後一個逗號，筆者會傾向程式。

保存DataTable

在window form的程式中，DataSet或DataTable的存續在window form程式設計師眼裡視之理所當然，但是在web form就不是這般，因為web的本質在使用者發出要求(Request)，web server依據要求處理後再回應(Response)給使用者，二者之間的關係在工作結束後就中斷了，這些 In memory物件(DataSet、DataTable)就會消失，如果要保留DataSet、DataTable可以利用Session或ViewState來保留，下面介紹利用ViewState使DataTable得以存續

protected override void LoadViewState(object savedState)

{

base.LoadViewState(savedState);

if (ViewState["_dt"] != null)

{

dt = (DataTable)ViewState["_dt"];

}

}

protected override object SaveViewState()

{

ViewState["_dt"] = dt;

return base.SaveViewState();

}

上面的程式片段將利用DataTable存入ViewState及ViewState明確轉型為DataTable，令DataTable得以存續，不過在使用時至少要考慮一些問題

1)系統頻寛

2)系統Loading

因為是存在ViewState所以對於使用者與Server往來的流量就會增加，另外使用者的瀏覽器在解析時也會較慢，還有在ViewState轉型成DataTable也會耗用資源，以上都是在使用時要考量的。

ADO.NET-DataRowState說明

每一個DataRow物件的狀態可能為以下五種其中一種

1)Added

2)Deleted

3)Detached

4)Modified

5)Unchanged

其中Added、Deleted、Modified會對資料來源產生異動。

Added： 對應SqlDataAdapter的InsertCommand

Deleted: 對應SqlDataAdapter的DeleteCommand

Modified: 對應SqlDataAdapter的UpdateCommand

Added狀態 是DataRow加入至DataTable,尚未呼叫AcceptChanges

Deleted狀態 是DataRow利用Delete方法刪除資料列

Modified狀態 是DataRow修改，尚未呼叫AcceptChanges

當SqlDataAdapter物件呼叫Update方法時就是利用DataTable中每一筆DataRow的狀態去對應適合的Command物件

ADO.NET-SqlDataAdapter簡介(1)

在ADO.NET中，SqlDataAdapter所扮演的角色，其實是SQL Server(資料來源)與In memory資料庫(DataSet)的橋樑，SqlDataAdapter有四個屬性分別對應查詢、新增、修改、刪除的SqlCommand物件

1) SelectCommand屬性，功用為自資料來源擷取資料至DataSet裡的資料表
2) InsertCommand屬性，回寫資料來源前需設定
3) UpdateCommand屬性，回寫資料來源前需設定
4) DeleteCommand屬性，回寫資料來源前需設定

範例如下
*因篇幅關係，大部份程式碼略過

//SelectCommand結果填入DataTable

//cmd做為SelectCommand屬性

//dt為DataTable
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);

//回寫資料來源

//insCmd為一個以新增資料的SqlCommand物件

//updateCmd為一個以修改資料的SqlCommand物件

//delCmd為一個以刪除資料的SqlCommand物件

da.InsertCommand = insCmd;

da.UpdateCommand = updateCmd;

da.DeleteCommand = delCmd;

da.Update(dt);

在SqlDataAdapter物件做回寫至資料來源時，會引用DataTable物件，並依據每一筆資料列的DataRowState去對應到適合的SqlDataAdapter Command屬性

T-SQL切割字串另類方法

在SQL Server中並沒有提供類似Split的函數，所以在做字串切割時得自己撰寫自定函數或在Stored Procedure裡撰寫相關T-SQL語法，在這裡介紹一個SQL內建的函數PARSENAME，這個函數原本是提供傳回物件名稱的指定部份。物件的可擷取部份有物件名稱、擁有者名稱、資料庫名稱和伺服器名稱。底下是擷取SQL 2005的線上叢書的程式片段
USE AdventureWorks;
SELECT PARSENAME('AdventureWorks..Contact', 1) AS 'Object Name';
SELECT PARSENAME('AdventureWorks..Contact', 2) AS 'Schema Name';
SELECT PARSENAME('AdventureWorks..Contact', 3) AS 'Database Name;'
SELECT PARSENAME('AdventureWorks..Contact', 4) AS 'Server Name';
GO

結果集
Object Name
------------------------------
Contact

(1 row(s) affected)

Schema Name
------------------------------
(null)

(1 row(s) affected)

Database Name
------------------------------
AdventureWorks

(1 row(s) affected)

Server Name
------------------------------
(null)

(1 row(s) affected)

這個函數可接受的參數有二個，第一個參數是由四個piece所組成，piece與piece之間以.為連結符號，第二個參數是指要取得第幾個piece。現在我們將這個函數引用到其它的地方，下面的T-SQL片段，主要是將IP位地做切割
DECLARE @IP_Address VARCHAR(15)
SET @IP_Address = '192.168.0.1'
SELECT PARSENAME(@IP_Address, 4) AS piece4
SELECT PARSENAME(@IP_Address, 3) AS piece3
SELECT PARSENAME(@IP_Address, 2) AS piece2
SELECT PARSENAME(@IP_Address, 1) AS piece1

結果集
piece4
------------------------------
192

(1 row(s) affected)

結果集
piece3
------------------------------
168

(1 row(s) affected)

結果集
piece2
------------------------------
0

(1 row(s) affected)

結果集
piece1
------------------------------
1

(1 row(s) affected)

使用PARSENAME有幾個限制，第一個是參數1的組成必需小於等於四個piece，也就是說一個piece也行，第二個是參數2的值必需介於1~4的整數，所以當你的字串是由非常多的piece所組成可能就得自己寫自定函數，更詳細的資料請參考SQL2005線上叢書或sqlteam。

WebChart介紹1

在資料呈現上高階主管總是希望一目瞭然，所以很多主管都希望下屬在報告時儘可能用圖形的方式來說明，然而在Visual Studio 2003&2005並沒有內建Chart的控製項(2008有Chart Control，但需要自行下載)，下面是我目前所知道在ASP.NET下常用的Chart元件，但我並不會全部說明

1. WebChart(dotNet 1.1可用，2.0以上未試過，是free元件)
2. OWC
3. Zedgraph(授權)
4. Dundas(付費軟體，目前為Chart元件的領導廠商)
5. Microsoft Chart Controls for .Net 3.5 (安裝至 .NET 3.5 SP1 才能正確使用該元件，控制項核心是微軟跟Dundas公司購買 Dundas Data Visualization 元件)
6. Reporting Service(在ASP.Net嵌入Reporting Service報表檔的url)

接下來的幾天我會陸續介紹WebChart、OWC、Zedgraph、Microsoft Chart Controls for .Net 3.5 ，Dundas的元件是付費所以我不介紹，另外Reporting Service我也不介紹(我不喜歡以嵌入的方式來處理圖形)