Maxwell Tech Note

由於NPOI無法做到excel加密的功能， 所以在網路上找了一下，發現有兩個元件可以用 1. Microsoft.Office.Interop.Excel 2. Excel Jetcell .NET Microsoft.Office.Interop.Excel 是標準的MS套件，但使用上不是很好用，而且並不支援asp.net的環境使用 所以最後選了 Excel Jetcell .NET  作為使用的套件 用法很簡單 ExcelWorkbook Wbook = new ExcelWorkbook(); //Add new worksheet to workbook. Wbook.Worksheets.Add("Sheet1"); Wbook.Worksheets[0].Cells["A1"].Value = "sample XSL writing"; Wbook.Options.Password = "123";  //Set password //Write .xls file. MemoryStream ms = Wbook.WriteXLS(); string FileName = "test.xls"; return File(ms.ToArray(), "application/vnd.ms-excel", FileName);

開啟excel出現錯誤訊息 「已移除的部分: /xl/sharedStrings.xml 部分具有 XML 錯誤。  元素結束標記中的名稱必須和開始標記中的元素型別相符。」 主要原因是excel檔案中的資料是用xml存放 有因為不明的原因xml的tag不match導致的 其實xlsx的結構是 把xml的資料zip起來 所以如果把xlsx的檔案zip解開會出現 資料夾的結構 在從中間找到 /xl/sharedStrings.xml 用純文字的編輯器打開 要找出沒有match的標記是相當困難的 不過可以先用搜尋的方式去找出數量不match的tag 例如我的這個資料的結構是大都是 xxxx 但搜尋 有301個 搜尋/si>   --->確有300個 所以看來就是少了一個 然後再用取代的方式把固定的term 就可以找出單獨缺少的位置 再把缺的tag補上 存回資料

asp.net MVC 要匯出Excel有幾種方式 這邊介紹兩種 1.運用Gridview Dim grid As New GridView() grid.DataSource = srObjList 'obj List or Datatable grid.DataBind() Response.ClearContent() Response.AddHeader("Content-Type", "application/vnd.ms-excel") Response.AddHeader("content-disposition", "attachment; filename=Totalinventory.xls") Response.ContentType = "application/excel" Response.Charset = "big5" Dim sw As New System.IO.StringWriter() Dim htw As New HtmlTextWriter(sw) grid.RenderControl(htw) Response.Write(sw.ToString()) Response.End() return view()   2.使用寫好的view.asp 在controaller中 Response.ClearContent() Response.AddHeader("Content-Type", "application/vnd.ms-excel") Response.AddHeader("content-disposition", "attachment; filename=ExcelFile.xls") Response.ContentType = "application/excel" Response.Charset = "big5" Return View(...

在asp.net要產生excel可以用很方便的原件NPOI， 要怎麼使用這邊有個範例 請先去 NPOI Project 下載最新的dll reference NPOI 並且import Imports NPOI.HSSF.UserModel Imports NPOI.SS.UserModel Imports NPOI.SS.Util Imports NPOI.HSSF.Util Imports NPOI.POIFS.FileSystem Imports NPOI.HPSF 建立excel的 workbook和第一個sheet Dim workbook As New HSSFWorkbook() Dim sheet As HSSFSheet = workbook.CreateSheet("Sheet") 開始寫入Row資料 Dim rowIndex As Integer = 0 Dim row As HSSFRow = sheet.CreateRow(rowIndex) row.CreateCell(0).SetCellValue("Name") row.CreateCell(1).SetCellValue("Phone") row.CreateCell(2).SetCellValue("Addess") row.CreateCell(3).SetCellValue("gender") row.CreateCell(4).SetCellValue("Email") rowIndex = +1 寫入檔案 Dim fileName As String = Server.MapPath(".") + "/test.xls" Using fileData = New FileStream(fileName, FileMode.Create) workbook.Write(fileData) End Using 如果要讓使用者下載可以用下面的code Using exportData = New MemoryStream() wor...

使用Excel突然遇到沒辦法在 儲存格中貼上公式的狀況， 連選擇性貼上也都失效。 到網路一查才知道 原因居然不是office本身的問題 而是 skype造成的 如果有安裝skype跟skype browser Plug-in， 當你開啟browser時，又再打開excel 會造成程式衝突 導致excel的的貼上功能不能使用 這個時候只要關閉瀏覽器 excel貼上功能就能正常了

在ASP.net要用Gridview匯出excel在之前的文章中有提到過 是很簡單的一個方法 但是匯出來的excel有些全都是數字的文字儲存格都會被視為數字所以開頭的0會被去掉 尤其是向手機號碼這種資料，更是麻煩 對於這個問題，只要在程式碼中加上 Dim strStyle As String = "<style>td{mso-number-format:\@;}</style>" 然後再輸出的StringWriter 設定 WriteLine(strStyle) 這樣就行了 完整的Code如下 Dim strExportFilename As String = "ExportedData" Response.Clear()   Response.AddHeader("content-disposition", "attachment;filename=" + strExportFilename + ".xls")   Response.Cache.SetCacheability(HttpCacheability.NoCache)   Response.ContentType = "application/vnd.xls"   Response.Charset = "big5"   Dim strStyle As String = "<style>td{mso-number-format:\@;}</style>"   Dim stringWrite As New System.IO.StringWriter   Dim htmlWrite As New HtmlTextWriter(stringWrite)   stringWrite.WriteLine(strStyle)   GridView1.RenderControl(htmlWrite)   Response.Write(stringWrite.ToString().Replace("<div>", ""...

GridView匯出轉成Excel 可以下面的code就可以匯出excel Protected Sub BtnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnExcel.Click Dim strExportFilename As String = "ExportedData" Response.Clear() Response.AddHeader("content-disposition", "attachment;filename=" + strExportFilename + ".xls") Response.Cache.SetCacheability(HttpCacheability.NoCache) Response.ContentType = "application/vnd.xls" Response.Charset = "big5" Dim stringWrite As New System.IO.StringWriter Dim htmlWrite As New HtmlTextWriter(stringWrite) GridView1.RenderControl(htmlWrite) Response.Write(stringWrite.ToString().Replace("<div>", "").Replace("</div>", "")) Response.End() End Sub Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control) ' 解決Error:型別 'GridView' 的控制項 'GridView1' 必須置於有 runat=server 的表單標記之中。  End Sub ...

The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine. When running 64-bit windows and there are no MS Access drivers that run 64 bit.So to resolve it you need to change the build configuration to x86 found in the programs properties. Then click on Build and change the Platform target from Any CPU to x86.Recompile your program , it works like charm. http://ybbest.wordpress.com/2009/07/22/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/

用產生xml的方式 gene 標頭用 xmlTextWriter 產生Excel 會在Excel 2003無法開啟 所以改用response.write產生 Response.Charset = "UTF8" Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8") Response.AddHeader("content-disposition", "attachment; filename=" & filename & ".xls") Response.ContentType = "application/vnd.ms-excel" Response.Write("<?xml version='1.0'?>") Response.Write("<?mso-application progid='Excel.Sheet() '?>") Response.Write("<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'") Response.Write(" xmlns:o='urn:schemas-microsoft-com:office:office'") Response.Write(" xmlns:x='urn:schemas-microsoft-com:office:excel'") Response.Write(" xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'") Response.Write(" xmlns:html='http://www.w3.org/TR/REC-html40'>") 每個Sheet開頭 Respon...