C# 與 Excel (7) - 凍結視窗

很多 Excel 報表為了清楚每一欄的定義
都會使用到凍結視窗功能
所以這邊就來介紹一下凍結視窗的語法

Application ExcelApp = new Application();
ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ExcelSheet = (Worksheet)ExcelApp.Workbooks[1].Worksheets[1];

// 1.先選定某一個儲存格當作範圍

Range rangeFreezePoint = ExcelSheet.get_Range("A2", System.Type.Missing);

// 2.選定此範圍所在的 Sheet

rangeFreezePoint.Select();

// 3.針對被選定的 Sheet 進行凍結視窗

ExcelApp.ActiveWindow.FreezePanes = true;

選定的儲存格和凍結視窗的結果關係如下 :

黑粗線就是凍結視窗的分隔線
綠色背景就是選定的儲存格

C# 與 Excel (6) - 範圍相關設定

針對 Excel Range 相關的設定, 一次來個大雜繪~

// 設定儲存格格式
Range rangeA1toA3 = ExcelSheet.get_Range("A1","A3");
rangeA1toA3.NumberFormatLocal = "#,##0;[紅色](#,##0);0";

// 設定儲存格框線 
/* xlHairline : 虛線       */
/* xlThick : 粗線條       */
/* xlMedium : 中等線條  */
/* xlThin : 細線條        */
rangeA1toA3.Borders.Weight = XlBorderWeight.xlThin;

// 儲定範圍外框 , 請參考[MSDN]
/* Excel 的色碼和一般色碼不同 , 一般是 RGB , 這邊要用 BGR */
rangeA1toA3.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, 0x000000); 

// 設定背景顏色
/* Excel 的色碼和一般色碼不同 , 一般是 RGB , 這邊要用 BGR */ 
rangeA1toA3.Interior.Color = 0x99FFFF;

// 設定字型
rangeA1toA3.Font.Size = 12;  // 字型大小為12pt
/* Excel 的色碼和一般色碼不同 , 一般是 RGB , 這邊要用 BGR */ 
rangeA1toA3.Font.Color = 0x0000FF;  // 字型顏色為紅色
rangeA1toA3.Font.Bold = true;  // 字型為粗體

// 合併儲存格
rangeA1toA3.MergeCells = true;

C# 與 Excel (5) - 輸入儲存格公式

今天來介紹一下如何在儲存格中輸入公式

我用過的有二種方式

第一種是選定範圍後再設定公式

這種的好處是, 它有明確的屬性可以設定

// A1 = 10

ExcelSheet.Cells[1,1] = 10;

// A2 = 25

ExcelSheet.Cells[2,1] = 25;

/*** A3 = A1 + A2 ***/

// 先選定 A3 範圍

Range rangeA3 = ExcelSheet.get_Range("A3", System.Type.Missing);

// 設定一 : 使用 Formula 屬性設定公式

rangeA3.Formula = "=SUM(A1 : A2)";  //也可以用 "=A1+A2"

// 設定二 : 使用 FormulaR1C1 屬性設定公式

// 有關 R1C1 請參考[Office Online>關於公式中的參照>R1C1 參照樣式]
rangeA3.FormulaR1C1 = "=SUM(R[-2]C : R[-1]C)";

// B1 = 100 , B2 = 25

ExcelSheet.Cells[1,2] = 100;
ExcelSheet.Cells[2,2] = 25;

// C1 = 42 , C2 = 7

ExcelSheet.Cells[1,3] = 42;
ExcelSheet.Cells[2,3] = 7;

// 設定 B3 = B1 / B2 , C3 = C1 / C2

Range rangeB3toC3 = ExcelSheet.get_Range("B3" , "C3");
rangeB3toC3.FormulaR1C1 = "=R[-2]C / R[-1]C";

第二種是直接針對儲存格設定公式

// D1 = 25 , E1 = 4

ExcelSheet.Cells[1,4] = 25;
ExcelSheet.Cells[1,5] = 4;

// F1 = D1 * E1

ExcelSheet.Cells[1,6] = "=D1 * E1";

//D2 = 58 , E2 = 42

ExcelSheet.Cells[2,4] = 58;
ExcelSheet.Cells[2,5] = 42;

// F2 = D2 - E2

ExcelSheet.Cells[2,6] = "=RC[-2] - RC[-1]";

依需求不同, 可以選用不同的方式輸入公式

C# 與 Excel (4) - 隱藏儲存格

接著來介紹一下如何隱藏儲存格

第一種方式 : 針對特定(一或多)行/列隱藏

// 隱藏A行

Range rangeColumnA = (Range)ExcelSheet.Columns["A",System.Type.Missing];
rangeColumnA.Hidden = true;

// 隱藏C~D行

Range rangeColumnCtoD = (Range)ExcelSheet.Columns["C:D", System.Type.Missing];
rangeColumnCtoD.Hidden = true;

// 隱藏第3列

Range rangeRow3 = (Range)ExcelSheet.Rows[3, System.Type.Missing];
rangeRow3.Hidden = true;

// 隱藏第5~7列

Range rangeRow5to7 = (Range)ExcelSheet.Rows["5:7", System.Type.Missing];
rangeRow5to7.Hidden = true;

第二種方式 : 針對特定範圍, 選擇隱藏其範圍的行或列

// 設定 A1 ~ D4 範圍
Range rangeA1toD4 = ExcelSheet.get_Range("A1", "D4");
// 針對行隱藏 : A~D行就會被隱藏
rangeA1toD4.Columns.Hidden = true;
// 針對列隱藏 : 1~4列就會被隱藏
rangeA1toD4.Rows.Hidden = true;

這邊要注意一件事 :
就是隱藏儲存格之後, 不能再針對這些隱藏的行/列調整欄寬或列高(包含自動欄寬)
因為 Excel 的隱藏儲存格只是將這些儲存格的欄寬(列高)設為 0
如果調整了這些儲存格的欄寬(列高)
那這些儲存格就又會再度『出現』了

Database 分頁

之前試了一下怎麼用 SQL Statement 做分頁
試出了下面的方式, 所以記錄一下

[Oracle DB]
PageSize就是一頁的資料筆數
如果希望一頁有10筆資料, PageSize 就代入10

SELECT *
FROM (
   SELECT
   FLOOR((ROWNUM-1)/PageSize)+1 AS PAGE
   ,A.*
   FROM OracleTable AS A
)
WHERE PAGE = 1

[SQL Server 2005]
UpdateDate是用來排序列出資料編號的欄位
PageSize就是一頁的資料筆數
如果希望一頁有20筆資料, PageSize 就代入20

SELECT *
FROM (
   SELECT
   (ROW_NUMBER() OVER (ORDER BY UpdateDate)-1)/PageSize+1 AS Page
   ,A.*
   FROM SQLTable AS A
)
WHERE Page = 1

C# 與 Excel (3) - 輸入儲存格內容值

今天來介紹一下怎麼在 Excel 儲存格中輸入值
不過先插一個之前一直忘記介紹的『如何命名活頁簿』

Worksheet ExcelSheet = (Worksheet)ExcelApp.Workbooks[1].Worksheets[1];
ExcelSheet.Name = "測試用活頁簿";

很簡單吧 ?!

接下來進入主題~

就我知道的輸入儲存格內容值有兩種方式

1.直接指定特定儲存格輸入值

/*Cells第一維代表列, 也就是1, 2, 3...                             */

/*第二維代表行, 也就是 A, B, C... 用數字代表就是 1, 2, 3... */

ExcelSheet.Cells[2, 1] = "A2";

2.選定一個範圍, 再輸入內容值

// 首先要先選定一個範圍, 可以直接指明開始和結束的儲存格

Range rangeA1toB2 = ExcelSheet.get_Range("A1","B2");

// 也可以用上面介紹的儲存格表示法取代

Range rangeA4toA5 = ExcelSheet.get_Range(ExcelSheet.Cells[4,1], ExcelSheet.Cells[5,1]);

// 也可以只選一個儲存格

Range rangeC1 = ExcelSheet.get_Range("C1", System.Type.Missing);

// 也可以用上面介紹的儲存格表示法取代

// 但是如果只取一欄, 就要把開始和結束的儲存格都設同一個儲存格

Range rangeD2 = ExcelSheet.get_Range(ExcelSheet.Cells[2,4], ExcelSheet.Cells[2,4]);

// 再針對範圍給值

rangeA1toB2.Value2 = "A1~B2";
rangeA4toA5.Value2 = "A4~A5";
rangeC1.Value2 = "C1";
rangeD2.Value2 = "D2";

C# 與 Excel (2) - 儲存 Excel

接著來介紹一下儲存 Excel 的語法

// 定義 Excel 儲存的路徑

// 以程式執行檔 (.exe)  所在資料夾作為儲存路徑

string strCurrentPath = Directory.GetCurrentDirectory() + "\\";

// 定義 Excel 儲存的檔名

string ExcelFileName = "新增Microsoft Excel 工作表.xls";

/* 檢查檔案是否已存在, 若已存在則先刪除舊檔       */

/* FileInfo 使用前需先 using System.IO;           */

/* Thread 使用前需先 using System.Threading; */

FileInfo fiExcelFile = new FileInfo(ExcelFileName);
if (fiExcelFile.Exists)
{
        // 刪除舊檔
        fiExcelFile.Delete();
        // 因為怕刪檔需要時間, 為避免影響程式進行, 讓程式等待 500 亳秒
        Thread.Sleep(500);
}

// 進行存檔動作 [參考MSDN]

ExcelBook.SaveAs(strCurrentPath + ExcelFileName, System.Type.Missing, System.Type.Missing, System.Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, System.Type.Missing, false, System.Type.Missing, System.Type.Missing, System.Type.Missing);

// 因為後面還有關閉 Excel 動作, 為避免還沒存完檔就被關閉, 先等待 1000 亳秒
Thread.Sleep(1000);

// 關閉 Excel 應用程式
// 為避免 Excel 跳出確認訊息, 將 Workbook.Saved 屬性設為 true [參考MSDN]

ExcelBook.Saved = true;
ExcelApp.Quit();
ExcelApp = null;

/* 在建立 Excel Application 時, 系統就會執行一個 EXCEL.exe */

/* 此時雖然利用 Application.Quit() 關閉 Excel Application    */

/* 但實際上 EXCEL.exe 並不會跟著關閉                            */

/* 所以要用 .NET 的 Garbage Collection 將 EXCEL.exe 關閉 */
/* Garbage Collection 使用前需先 using  System;             */

GC.Collect();
GC.WaitForPendingFinalizers();

C# 與 Excel (1) - 產生 Excel

因為最近都不停在寫 Excel 相關的程式
所以就先寫一系列的 C# 與 Excel 的記錄囉~

要在 C# 中產生 Excel
首先要加入 Excel 元件的參考
(Microsoft Office Excel 必須安裝 .NET 程式設計支援)

再來就要using Excel namespace

using Microsoft.Office.Interop.Excel;

接著就先介紹產生 Excel 的語法
// 建立 Excel 應用程式

Application ExcelApp = new Application();

// 建立 Excel 活頁簿 (Workbook)

ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

/* 一建立活頁簿時, 就會自動產生一頁工作表 (Worksheet) */

/*  所以不用再建立工作表                                        */

// 取得第一個工作表 (Excel裡所有陣列 index 是從 1 開始)

Worksheet ExcelSheet = (Worksheet)ExcelApp.Workbooks[1].Worksheets[1];

// 如果想新增工作表, 可以使用下列語法 [參考MSDN]

ExcelApp.Workbooks[1].Worksheets.Add(System.Type.Missing, ExcelApp.Workbooks[1].Worksheets[1], 1, System.Type.Missing);

在ASP.NET中跳出訊息視窗

搭配Javascript就可以在ASP.NET中跳出訊息視窗

protected void AlertMessage(string ScriptID, string Message)
{
        string strScript = " <script language=\"javascript\">\n";
        strScript += "  alert(\"" + Message + "\");\n";
        strScript += "</script>\n";
        RegisterStartupScript(ScriptID, strScript);
}

● 使用 RegisterStartupScript 會將內容的 Javascript 產生在 </form> 上一行
● 使用 RegisterClientScriptBlock 會將內容的 Javascript 產生在 <form> 下一行

<body>
    <form name="form1" method="post" action="TestRegisterJava.aspx" id="form1">
<div>
<input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="/wEPDwUKMTU5MTA2ODYwOWRkmbCuiO5HQUj5tuT4Q9q+cMWMGBo=" />
</div>

<script language="javascript">
    alert("RegisterClient");
</script>

<div>

    <input type="hidden" name="__EVENTVALIDATION" id="__EVENTVALIDATION" value="/wEWAwK90vPBCQLs0bLrBgKM54rGBjWLoYUoxEu/x64qISF1ZSWFz2B9" />
</div>
    <div>
        <input name="TextBox1" type="text" id="TextBox1" />
        <input type="submit" name="Button1" value="Button" id="Button1" /><br />
        <span id="Label1">Label</span></div>
   
<script language="javascript">
    alert("RegisterStart");
</script>
</form>
</body>

解決除數為 0 的小函數

寫報表常會遇到要計算xxx達成率
就會遇到除數為 0 的情況
每次都要寫一堆 if ...

用個簡單的程式解決就好了

protected double SafeDivide(double Dividend, double Divisor)
{
    return Divisor == 0 ? 0 : Dividend / Divisor;
}

當除數為 0 時, 就傳回 0