C# Excel 寫入 NPOI

下載 NPIO:http://npoi.codeplex.com/
參考資料:http://msdn.microsoft.com/zh-tw/ee818993.aspx

Dim Workbook As HSSFWorkbook = New HSSFWorkbook
Dim ms As MemoryStream = New MemoryStream

Dim sheet As HSSFSheet = Workbook.CreateSheet("My Sheet")

for(int i = 0; i < 5; i ++)
{
    sheet.CreateRow(i)
    sheet.GetRow(i).CreateCell(0).SetCellValue( i.ToString())
    sheet.GetRow(i).CreateCell(1).SetCellValue("A" + i.ToString() )
}

Workbook.Write(ms)
Response.AddHeader("Content-Disposition", String.Format("attachment; filename=EmptyWorkbook.xls"))
        Response.BinaryWrite(ms.ToArray())

Workbook = Nothing
ms.Close()
ms.Dispose()

尋找上下一筆資料

SELECT TOP 20 * FROM dbo.Orders

DECLARE @OrderID int
SET @OrderID = 10252

--1.求出目前這筆資料的時間
DECLARE @dt DateTime
SELECT @dt = OrderDate
FROM Orders
WHERE OrderID = @OrderID
--2.使用時間來做比對，再透過排序的方式，來選出上一筆、下一筆資料
SELECT  * FROM
(
-- 找出上一筆資料
SELECT Top 1 OrderID,OrderDate FROM Orders 
WHERE OrderDate >= @dt 
  AND OrderID != @OrderID
ORDER BY OrderDate ASC
)[Previous]
UNION
--3.再將資料表連結起來(此步驟非必要步驟)  
SELECT  * FROM
(
-- 找出下一筆資料
SELECT Top 1 OrderID,OrderDate FROM Orders 
WHERE OrderDate <= @dt 
  AND OrderID != @OrderID
ORDER BY OrderDate DESC
)[Next]

參考資料:http://www.dotblogs.com.tw/dotjum/archive/2008/10/20/5729.aspx