我想大家一定會遇到要把資料匯出成 Excel 的需求. 以現有的工具, 大家想到作法大概都是先把資料放到一個 datatable 之中, 後叫用 UW.ExcelPOI.DTToExcelAndWriteToClient 就結束了.

前兩天遇到一個需求, 輸出的 Excel 要加上表頭, 如下圖



於是乎把  UW.ExcelPOI.DTToExcelAndWriteToClient 做了一些擴充, (其實應該說是幫 DTToWorkSheet 做了擴充), 過程如下.

1. 需求: 一個可以快速填入欄位的 Sub (method or function)
A. 每一個 Cell 可以設定內容(文字), 字型大小, 跨欄數, 對齊方式. (其它的未來再來擴充, 例如顔色).
B. 每一個 Row 由 Cell 組成, 由左到右.
C. 一次可以填多個 Row

2. 實作:
A. 先定義 Cell

    Public Class Cell

        Public Content As String

        Public Colspan As Int32 = 1

        Public Alignment As NPOI.SS.UserModel.HorizontalAlignment

        Public FontHeightInPoints As Int32 = 0



        Sub New(Content As String, Optional Colspan As Int32 = 1,

                Optional Alignment As NPOI.SS.UserModel.HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.General,

                Optional FontHeightInPoints As Int32 = 0)

            Me.Content = Content

            Me.Colspan = Colspan

            Me.Alignment = Alignment

            Me.FontHeightInPoints = FontHeightInPoints

        End Sub

    End Class

B. Row 的格式: 我想最直的覺的就是 List(of Cell) 了吧.

C. 多個 Row 的表示法: List(Of List(Of Cell))

D. 來把 Cell 填入 WorkSheet  吧, 

Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)

共有三個參數: WS  和 ltRows 應該不用解釋了. 最後一個 StartRow 用來指定插入資料的開始 Row.

E.  完整程式碼: (程式碼不看沒關係, 但要跳到 F. 重點講解哦)

Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)

        Dim WR As HSSFRow

        If ltRows IsNot Nothing Then

            For Each ltRow As List(Of Cell) In ltRows

                WR = WS.CreateRow(StartRow)

                Dim C As Int32 = 0

                For Each cell As Cell In ltRow

                    Dim ic As NPOI.SS.UserModel.ICell = WR.CreateCell(C)

                    ic.SetCellValue(cell.Content)



                    Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()

                    cs.Alignment = cell.Alignment

                    If cell.FontHeightInPoints > 0 Then

                        Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()

                        oFont.FontHeightInPoints = cell.FontHeightInPoints

                        cs.SetFont(oFont)

                    End If



                    ic.CellStyle = cs



                    If cell.Colspan > 1 Then

                        WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))

                        C += cell.Colspan - 1

                    End If

                    C += 1

                Next



                StartRow += 1

            Next

        End If

    End Sub

F. 重點講解:
這個 function 在實作時有兩個卡點:
1. 如何合併欄: 

WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))

2. 如何設定字型大小和對齊方式:

                    Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()

                    cs.Alignment = cell.Alignment

                    If cell.FontHeightInPoints > 0 Then

                        Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()

                        oFont.FontHeightInPoints = cell.FontHeightInPoints

                        cs.SetFont(oFont)

                    End If



                    ic.CellStyle = cs

這裡有件有有趣的事, 我一開始是這樣寫的.

ic.CellStyle.Alignment = cell.Alignment

結果是整個 WorkSheet 的對齊方式都被改了. 我猜當 WorkSheet 初建立時, CellStyle 都是用同一個. 所以改任一個 cell 的 CellStyle 會同時改到所有 cell 的.

G. 使用方式:

        Dim ltHeader As New List(Of List(Of UW.ExcelPOI.Cell))

        Dim ltLine As New List(Of UW.ExcelPOI.Cell)

        ltLine.Add(New UW.ExcelPOI.Cell(DB.SysConfig.SYSTEM_NAME & "應收明細表", 16,

                                        NPOI.SS.UserModel.HorizontalAlignment.Center, 28))

        ltHeader.Add(ltLine)



        '第二行

        ltLine = New List(Of UW.ExcelPOI.Cell)

        ltLine.Add(New UW.ExcelPOI.Cell("期間: " & Me.txtbl_date_s.Text & " ~ " & Me.txtbl_date_e.Text, 10,

                                        NPOI.SS.UserModel.HorizontalAlignment.Left, 20))

        ltLine.Add(New UW.ExcelPOI.Cell("製表日期: " & Now.ToString("yyyy-MM-dd"), 6,

                                        NPOI.SS.UserModel.HorizontalAlignment.Right, 20))

        ltHeader.Add(ltLine)



        UW.ExcelPOI.DTToExcelAndWriteToClient(newdt, ltHeader:=ltHeader)

由於工作排程往往有數十個甚至百來個 因此需要一個方法能快速備份及移轉到其他 server 的方法
查了一下，好像也只能這樣做

---- 使用指令把全部排程匯出 ----
參考網址
https://msdn.microsoft.com/en-us/library/windows/desktop/bb736357%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396

schtasks /query /XML > all_tasks.xml  
schtasks /query /FO CSV /V >sched_tasks.csv

** xml 比較有用,可以用來匯入到其他server排程，但還要額外處理才能匯入
** csv 只是用來看看目前有哪些排程 可以用來做報表看看
** 這些是全部排程，還要特別處理把 Microsoft 及其他軟體建的排程移除

--------------------------------------------------------------

----如何匯入工作排程到其他 server --------

** 需再寫程式把 all_tasks.xml 拆解成所有排程的單一 xml
   並且生成指令 bat

** 注意 xml 必須是 UTF-16 (unicode) 
   因為 "schtasks /query /XML > all_tasks.xml"產生 xml 是 ansi 

參考網址
https://serverfault.com/questions/325569/how-do-i-import-multiple-tasks-from-a-xml-in-windows-server-2008
** /TN "排程名稱" --- 排程名稱可以是路徑名 
D:\>schtasks.exe /create /TN "\2016JOB\Global\CountryMonthlyStats" /XML "D:\one_task.xml"
** 下面程式碼只是下面程式碼只是參考 還沒實作過
=====================================

var taskXML = new XmlDocument();
taskXML.Load(@"d:\temp\schedtasksBackup.xml");
var batbody = new StringBuilder();

XmlNodeList tasks = taskXML.DocumentElement.GetElementsByTagName("Task");

string strFileName = "d:\\temp\\Task";

for (int i = 0; i < tasks.Count; i++) {
    string onetaskXML = tasks[i].OuterXml;

    //Create the New File. With a little more extra effort
    //you can get the name from a comment above the task -> 應該把註解裡的名稱抓出
    XmlWriter xw = XmlWriter.Create(strFileName + "_" + (i+1) + ".xml");
    batbody.AppendLine(string.Format("schtasks.exe /create /TN \"{0}\" /XML \"{1}\"", "Task " + (i+1) + " Name", strFileName + "_" + (i+1) + ".xml"));

    //Write the XML
    xw.WriteRaw(onetaskXML.ToString());
    xw.Close();

    // Write a bat to import all the tasks
    var batfile = new System.IO.StreamWriter("d:\\temp\\importAllTasks.bat");
    batfile.WriteLine(batbody.ToString());
    batfile.Close();

}
=====================================

#微軟的排程備份真是有夠爛沒有辦法用介面一鍵搞定

比較特別的地方，都在下面的程式碼後面的註解
​

Response.Write(Convert.ToInt32("94"));

Response.Write("<br/>");

Response.Write(Convert.ToInt32(94.5)); //會四捨六入五成雙

Response.Write("<br/>");

Response.Write((94.5).ToString("N0")); //會四捨五入

Response.Write("<br/>");

Response.Write((95.5).ToString("N0")); //會四捨五入

Response.Write("<br/>");

Response.Write(Convert.ToInt32(null)); //會 return 0

Response.Write("<br/>");

Response.Write(Convert.ToInt32("94.55")); //會有 exception 要先轉成 double 之類的數值

Response.Write("<br/>");

Post 的資料好像會變大, 要改 Web.config

<system.web>

    <httpRuntime requestValidationMode="2.0" maxRequestLength="1024000"/>

</system.web>

程式碼如下:

public string UploadFilesToRemoteUrl(string url, string[] files, NameValueCollection formFields = null)

    {

        string boundary = "----------------------------" + DateTime.Now.Ticks.ToString("x");



        HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

        request.ContentType = "multipart/form-data; boundary=" +

                                boundary;

        request.Method = "POST";

        request.KeepAlive = true;



        Stream memStream = new System.IO.MemoryStream();



        var boundarybytes = System.Text.Encoding.ASCII.GetBytes("\r\n--" +

                                                                boundary + "\r\n");

        var endBoundaryBytes = System.Text.Encoding.ASCII.GetBytes("\r\n--" +

                                                                    boundary + "--");





        string formdataTemplate = "\r\n--" + boundary +

                                    "\r\nContent-Disposition: form-data; name=\"{0}\";\r\n\r\n{1}";



        if (formFields != null)

        {

            foreach (string key in formFields.Keys)

            {

                string formitem = string.Format(formdataTemplate, key, formFields[key]);

                byte[] formitembytes = System.Text.Encoding.UTF8.GetBytes(formitem);

                memStream.Write(formitembytes, 0, formitembytes.Length);

            }

        }



        string headerTemplate =

            "Content-Disposition: form-data; name=\"{0}\"; filename=\"{1}\"\r\n" +

            "Content-Type: application/octet-stream\r\n\r\n";



        for (int i = 0; i < files.Length; i++)

        {

            memStream.Write(boundarybytes, 0, boundarybytes.Length);

            var header = string.Format(headerTemplate, "uplTheFile", files[i]);

            var headerbytes = System.Text.Encoding.UTF8.GetBytes(header);



            memStream.Write(headerbytes, 0, headerbytes.Length);



            using (var fileStream = new FileStream(files[i], FileMode.Open, FileAccess.Read))

            {

                var buffer = new byte[1024];

                var bytesRead = 0;

                while ((bytesRead = fileStream.Read(buffer, 0, buffer.Length)) != 0)

                {

                    Response.Write("bytesRead: " + bytesRead.ToString() + "<br>");

                    memStream.Write(buffer, 0, bytesRead);

                }

            }

        }



        memStream.Write(endBoundaryBytes, 0, endBoundaryBytes.Length);

        request.ContentLength = memStream.Length;



        using (Stream requestStream = request.GetRequestStream())

        {

            memStream.Position = 0;

            byte[] tempBuffer = new byte[memStream.Length];

            memStream.Read(tempBuffer, 0, tempBuffer.Length);

            memStream.Close();

            requestStream.Write(tempBuffer, 0, tempBuffer.Length);

        }



        try

        {

            using (var response = request.GetResponse())

            {

                Stream stream2 = response.GetResponseStream();

                StreamReader reader2 = new StreamReader(stream2);

                return reader2.ReadToEnd();

            }

        }

        catch (Exception ex)

        {

            return (ex.ToString());

            throw;

        }

    }

客戶要求
1. 檔案只能放在 Firewall 內的後台用 Web server (Server A).
2. 使用者只能存取 DMZ 的 Web server (Server B).
3. Server B 只能用 HTTP 通過 Firewall 向 Server A 要資料.(i.e.  Server B 不能掛戴 Server A 的目錄成為虛擬目錄)

所以在 Server B 上面建立了一支程式用 HTTP 的方式讀取 Server A 的檔案再寫出去.

例如, http://ServerB/Upload/test.pdf 會讀取 http://ServerA/Upload/test.pdf 再送到 Client 端

namespace WWW.Controllers

{

    public class UploadController : Controller

    {

        // GET: Upload

        public void Index(string Filename)

        {

            //Create a stream for the file

            Stream stream = null;



            //This controls how many bytes to read at a time and send to the client

            int bytesToRead = 10000;



            // Buffer to read bytes in chunk size specified above

            byte[] buffer = new Byte[bytesToRead];



            string url = "http://admin-dev.nanya.bike.idv.tw/newnanyaback/Upload/" + Filename;



            // The number of bytes read

            try

            {

                //Create a WebRequest to get the file

                HttpWebRequest fileReq = (HttpWebRequest)HttpWebRequest.Create(url);



                //Create a response for this request

                HttpWebResponse fileResp = (HttpWebResponse)fileReq.GetResponse();



                if (fileReq.ContentLength > 0)

                    fileResp.ContentLength = fileReq.ContentLength;



                //Get the Stream returned from the response

                stream = fileResp.GetResponseStream();



                // prepare the response to the client. resp is the client Response

                var resp = HttpContext.Response;



                if (Filename.ToLower().EndsWith(".png") ||

                    Filename.ToLower().EndsWith(".jpg") ||

                    Filename.ToLower().EndsWith(".jpeg") ||

                    Filename.ToLower().EndsWith(".gif")

                    )

                {

                    resp.ContentType = "image";

                }

                else

                {

                    //Indicate the type of data being sent

                    resp.ContentType = "application/octet-stream";



                    //Name the file

                    resp.AddHeader("Content-Disposition", "attachment; filename=\"" + HttpUtility.UrlEncode(Filename, Encoding.UTF8) + "\"");

                }



                resp.AddHeader("Content-Length", fileResp.ContentLength.ToString());



                int length;

                do

                {

                    // Verify that the client is connected.

                    if (resp.IsClientConnected)

                    {

                        // Read data into the buffer.

                        length = stream.Read(buffer, 0, bytesToRead);



                        // and write it out to the response's output stream

                        resp.OutputStream.Write(buffer, 0, length);



                        // Flush the data

                        resp.Flush();



                        //Clear the buffer

                        buffer = new Byte[bytesToRead];

                    }

                    else

                    {

                        // cancel the download if client has disconnected

                        length = -1;

                    }

                } while (length > 0); //Repeat until no data is read

            }

            finally

            {

                if (stream != null)

                {

                    //Close the input stream

                    stream.Close();

                }

            }

        }

    }

}

但不是這樣就好了, 在 RouteConfig.cs 中要加上:
            routes.MapRoute(
                name: "Upload",
                url: "Upload/{filename}",
                defaults: new { controller = "Upload", action = "Index", filename = UrlParameter.Optional }
            );

此外在 Web.Config 中也要加上:

  <system.webServer>
    <handlers>
      <add name="UrlRoutingHandler_Upload"
           type="System.Web.Routing.UrlRoutingHandler, 
               System.Web, Version=4.0.0.0, 
               Culture=neutral, 
               PublicKeyToken=b03f5f7f11d50a3a"
           path="/Upload/*"
           verb="GET"/>
    </handlers>
  </system.webServer>

參考:
http://stackoverflow.com/questions/5596747/download-stream-file-from-url-asp-net

http://blog.darkthread.net/post-2014-12-05-mvc-routing-for-url-with-filename.aspx

看到黑暗有一個關於中文難字的繁簡轉換處理的文章, 先記錄部份在這裡.

http://blog.darkthread.net/post-2015-03-06-strconv-half-full-width-notes.aspx

      var ncrString = toNCR("黑暗執行緒犇ＡＢＣ１２３");
      Debug.WriteLine(ncrString); //黑暗執行緒&#29319;ＡＢＣ１２３
      var convString = Microsoft.VisualBasic.Strings.StrConv(
        ncrString, Microsoft.VisualBasic.VbStrConv.Narrow, 1028);
      Debug.WriteLine(convString); //黑暗执行绪&#29319;ABC123
      var resultString = fromNCR(convString);
      Debug.WriteLine(resultString); //黑暗执行绪犇ABC123


    static string toNCR(string input)
    {  
      StringBuilder sb = new StringBuilder();
      Encoding big5 = Encoding.GetEncoding("big5");
      foreach (char c in input)
      {
        //強迫轉碼成Big5，看會不會變成問號
        string cInBig5 = big5.GetString(big5.GetBytes(new char[] {c}));
        //原來不是問號，轉碼後變問號，判定為難字
        if (c!='?' && cInBig5=="?")
          sb.AppendFormat("&#{0};", Convert.ToInt32(c));
        else
          sb.Append(c);
      }
      return sb.ToString();
    }
 
    static string fromNCR(string input)
    {
      return Regex.Replace(input, "&#(?<ncr>\\d+?);", (m) =>
      {
        return Convert.ToChar(int.Parse(m.Groups["ncr"].Value)).ToString();
      });
    }

找了兩三篇文章再加一點修正才組出來的,  記錄一下, 要用 iTextSharp 哦.

using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;

---
 
            string oldFile = Server.MapPath("/Content/PDF/300000419_20160929162658862.pdf"); //"oldFile.pdf";
            string newFile = oldFile.Replace(".pdf", "_New11.pdf");

            // open the reader
            PdfReader reader = new PdfReader(oldFile);
            Rectangle size = reader.GetPageSizeWithRotation(1);
            Document document = new Document(size);
            int NumberOfPages = reader.NumberOfPages;

            // open the writer
            FileStream fs = new FileStream(newFile, FileMode.Create, FileAccess.Write);
            PdfWriter writer = PdfWriter.GetInstance(document, fs);
            document.Open();

            // the pdf content
            PdfContentByte cb = writer.DirectContent;

            string text = "Watermark...";

            string windir = Environment.GetEnvironmentVariable("windir");
            Chunk textAsChunk = new Chunk(text, new Font(BaseFont.CreateFont(windir + "\\Fonts\\mingliu.ttc,0", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED), 20, Font.NORMAL, new BaseColor(255,0,0)));

            // create the new page and add it to the pdf
            for (int i = 1; i<= NumberOfPages; i++)
            {
                if(i > 1)
                {
                    document.NewPage();
                }

                ColumnText.ShowTextAligned(cb, Element.ALIGN_LEFT, new Phrase(textAsChunk), 0, 0, 0);

                PdfImportedPage page = writer.GetImportedPage(reader, i);
                cb.AddTemplate(page, 0, 0);
            }

            // close the streams and voilá the file should be changed :)
            document.Close();
            fs.Close();
            writer.Close();
            reader.Close();

            Response.Write(newFile + "<br>");
            Response.Write(NumberOfPages + "<br>");

在做地址的時候用了一個套件(TWZIPCODE)
可以幫你產生下拉霸的郵遞區號選項
很方便(jQuery)
但是在控制他的資料及回傳
又不知道怎樣比較方便直觀了

首先目前也是文字控制的方式是
UWForUNT.WU.ReplaceStringInControl(Me.Page, "#Post_Code#", Post_Code)
直接用 #Post_Code#
再把她取代

直接讓前端讀到值

回傳時 若是用後端控制 不用再傳ajax 或是 塞到別的後端控制項
回傳的方式
UW.WU.GetValueFromQueryStringOrForm("Post_Code")

但是因為之後不讓 Page Load 再次改成預設值
可以加
If Not Me.IsPostBack Then
   UW.WU.GetValueFromQueryStringOrForm("Post_Code")
End If
才不會永遠是預設值
(            $('#twzipcode').twzipcode({
            zipcodeSel: '#Post_Code#', // 此參數會優先於 countySel, districtSel
            countySel: '台北市',
            districtSel: '大安區',
            zipcodeName: 'Post_Code'
            })
)

他如果有包在裡可以直接讀取

另外若是 有一些後端要傳給前端的資料 用response.Write("<script>XXX</script>")
可以直接寫入前端

location.reload()

Chrome 最新的版本 
會把 本來的表單重送，所以我在 
UW.WU.ShowMessageAndRefresh 
 

System.Web.HttpContext.Current.Response.Write("location.reload()" & vbCrLf)

更改為

System.Web.HttpContext.Current.Response.Write("location.href=location.href" & vbCrLf)

 

參考網址：http://blog.miniasp.com/post/2014/06/04/Redirect-to-HTTPS-from-HTTP-using-IIS-URL-Rewrite.aspx