標籤:
前言:最近項目裡面有一些對Excel操作的需求,博主想都沒想,NPOI唄,簡單、開源、免費,大家都喜歡!確實,對於一些簡單的Excel匯入、匯出、合併儲存格等,它都沒啥太大的問題,但是這次的需求有兩點是NPOI搞不定的:
- 匯入Excel後,需要切割Excel的Sheet頁,然後每個Sheet頁單獨產生一個PDF檔案。
- 匯出Excel的時候,項目裡面需要將一些資料表格以圖表的形式在Excel裡面展示。
找了一圈資料,對於Excel產生pdf,網上的答案千篇一律:使用COM組件的方式,通過調用伺服器上面的Office組件裡面的東西去轉。這種方式需要在伺服器上面安裝Office,這倒是其次,最重要的是,許可權的問題很頭疼。博主已經按照這種方式實現了,調試的時候沒問題,部署到IIS上面之後又出了各種許可權的問題,好不容易在一台伺服器上面部署成功了,放到另一台伺服器上面按照同樣的方式部署,卻還是提示“拒絕訪問”。博主也是醉了。而對於Excel組建圖表,NPOI暫時沒找到實現方式,COM組件的方式可以,但是實現起來略顯複雜,並且這東西龐大、不太穩定,尤其是咱們大部分人個人電腦上面裝的Office都不是正版,使用起來也很蛋疼。
基於此,經過一番努力,找到了這麼一個第三方組件Spire.XLS。這兩天體驗了一把,使用起來還比較順手,在此來簡單介紹下這個組件的使用吧。
本文原創地址:http://www.cnblogs.com/landeanfen/p/5888973.html
一、組件介紹
Spire.XLS是E-iceblue開發的一套基於企業級的專業Office文檔處理的組件之一,全稱Spire.Office for .NET。旗下有Spire.Doc,Spire XLS,Spire.PDF,Spire.BarCode等多款專業組件,為各種Office文檔在程式處理上提供了很大的方便,官方為各種功能提供了大量的線上api,簡化了使用組件的難度。組件使用時不需要本地Office組件的支援。Spire.Office是一款企業級組件,它提供了收費版本和免費版本兩種層級,一般來說,對於個人的應用,免費版本已足夠用。比如對於上文博主遇到的問題,Spire.XLS組件就提供了很好的實現機制,如果你也遇到了NPOI解決不了的問題,不妨試試這個。
“XLS”是Excel檔案的尾碼之一,顧名思義,Spire.XLS當然就是針對Excel表格處理的組件嘍,本篇,博主將結合上文遇到的問題來看看Spire.XLS組件的強大功能。
二、組件安裝使用
對於組件的安裝,在此還是提供兩種方式:
1、官方下載安裝
。官方下載的安裝包是msi結尾的,安裝時需要選擇支援的VS版本等資訊,軟體的安裝就不做過多說明,有興趣的可以下載試試。
2、Nuget安裝
大家最喜歡的應該還是Nuget方式吧,簡單,方便,並且易於管理。博主也是不太喜歡為了一個組件而去單獨下載一個安裝包。
Spire.XLS也提供了Nuget的方式,只需要搜尋Spire,選擇免費版的組件即可:
安裝完成後自動引用了需要的dll
三、組件功能介紹
關於Excel的一些常用操作,比如取值、賦值、設定儲存格樣式等,這裡就不做過多介紹,無論是Com組件、NPOI還是Aspose,這些都是最基礎的功能。下面就針對上文提出的幾個問題著重說明下。
1、Excel轉PDF(1)COM組件實現思路回顧
關於Excel轉PDF的實現,網上找到的解決方案基本一樣,大致代碼如此:
/// <summary> /// 把Excel檔案轉換成PDF格式檔案 /// </summary> /// <param name="sourcePath">源檔案路徑</param> /// <param name="targetPath">目標檔案路徑</param> /// <returns>true=轉換成功</returns> public bool XLSConvertToPDF(string sourcePath, string targetPath) { Logger.Info("開始轉pdf"); bool result = false; XlFixedFormatType targetType = XlFixedFormatType.xlTypePDF; object missing = Type.Missing; Microsoft.Office.Interop.Excel.Application application = null; Microsoft.Office.Interop.Excel.Workbook workBook = null; try { application = new Application(); application.Interactive = false; object target = targetPath; object type = targetType; workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); application.Interactive = true; workBook.ExportAsFixedFormat(targetType, target, XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing); result = true; } catch(Exception ex) { Logger.Error("excel轉pdf異常,異常資訊:" + ex.Message + "。堆棧資訊:" + ex.StackTrace); result = false; } finally { if (workBook != null) { workBook.Close(true, missing, missing); workBook = null; } if (application != null) { application.Quit(); application = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } return result; }
這個方法需要依賴於本機上面的office Com組件,如果你安裝Office的時候,沒有安裝com組件相關的dll,這個方法也是用不了的,並且還有一個最大的問題就是執行 application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); 這一個方法的時候需要目前使用者有操作Excel Application這個組件的許可權,尤其是部署到IIS上面之後,需要配置一系列的許可權,很是麻煩。
(2)Spire.XLS實現轉換
通過上文,我們知道,Spire.Office提供了Spire.XLS和Spire.PDF兩個組件,那麼他們之間的轉換就簡單了。我們還是類比一個檔案上傳的功能。
前端有一個上傳控制項:
<input type="file" name="txt_file" id="txt_file" class="file-loading" />
後台有一個接收上傳檔案的方法如下:
[HttpPost] public JsonResult UploadFile() { var strRes = string.Empty; var oFile = Request.Files["txt_file"]; Workbook book = new Workbook(); book.LoadFromStream(oFile.InputStream); var strFullName = @"D:\Data\Upload\" + "First" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".pdf"; book.SaveToPdf(strFullName); return Json(new object { }, JsonRequestBehavior.AllowGet); }
就這麼簡單的幾句話即可實現將上傳的Excel轉成PDF檔案。根據源檔案產生Workbook對象,Spire.XLS提供了多種方式,我們最常用的兩種方式如下:
// 根據檔案路徑產生workbook. public void LoadFromFile(string fileName);// 根據檔案流產生workbook. public void LoadFromStream(Stream stream);
2.1、最原始的轉換
原始Excel檔案:
轉換成PDF之後
2.2、不好看?加一個邊框即可。
轉換之後
2.3、自訂轉換的PDF
有些情況下,我們Excel裡面有很多列,導致預設產生的pdf換行問題,這樣將會導致PDF的可讀性很差,這種情況,Spire.XLS為我們提供了自訂轉換PDF的方式,比如可以指定PDF的頁寬,頁高,大小等等屬性。
比如有如下Excel文檔需要轉換成PDF檔案:
如果按照常規的轉換,產生的PDF的寬度不足以顯示Excel的所有列,於是轉換出來的效果這樣:
為瞭解決這種問題,組件為我們提供了如下方法:
[HttpPost] public JsonResult UploadFile() { var strRes = string.Empty; var oFile = Request.Files["txt_file"]; Workbook book = new Workbook(); book.LoadFromStream(oFile.InputStream); var strFullName = @"D:\Data\Upload\" + "First" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".pdf"; PdfDocument pdfDocument = new PdfDocument(); pdfDocument.PageSettings.Orientation = PdfPageOrientation.Landscape; pdfDocument.PageSettings.Width = 1800;//指定PDF的寬度 pdfDocument.PageSettings.Height = 1000;//指定PDF的高度 PdfConverterSettings settings = new PdfConverterSettings(); settings.TemplateDocument = pdfDocument; PdfConverter pdfConverter = new PdfConverter(book); pdfDocument = pdfConverter.Convert(settings); pdfDocument.SaveToFile(strFullName); return Json(new object { }, JsonRequestBehavior.AllowGet); }
這樣就可以正常了,如果你的Excel列更多,可以適當調整寬度和高度。得到的結果如下
還有更多強大的功能大家有興趣可以慢慢探索,官方文檔寫得還算詳細。
2.4、Excel轉其他類型
除了轉為PDF,Spire.XLS還支援轉換為其他類型,比如常見的xml、Image、Html等。如果大家有這方面的需求,可以深究一下。
2、Excel組建圖表2.1、Excel圖表產生原理分析
通過下面一張圖先來看看Excel裡面組建圖表的原理
通過這張圖我們可以看到,Excel組建圖表首先需要當前文檔裡面存在資料表格,然後選中相應的資料表格,最後選擇產生的圖表類型,Excel應用會自動幫你產生相應的資料圖表。
2.2、Spire.XLS產生簡單圖表
知道了上面Excel組建圖表的原理,我們再來看看Spire.XLS組件如何協助我們解決組建圖表的問題。關於組建圖表,Spire.XLS組件提供了很多的選擇,覆蓋了Excel裡面各種內建的圖表類型、統計方法等。下面先來看一個簡單點的例子。
[HttpPost] public JsonResult ExportData() { try { Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; var random = new Random(); var iCellcount = 1; //1.設定表頭 sheet.Range[1, iCellcount++].Text = "部門名稱"; sheet.Range[1, iCellcount++].Text = "部門人數"; var lstDeptName = new List<string>() { "市場部", "策劃部", "公關部", "行政部", "開發部" }; var a = 0; //2.構造表資料 for (var i = 2; i < 7; i++) { iCellcount = 1; sheet.Range[i, iCellcount++].Text = lstDeptName[a++]; sheet.Range[i, iCellcount++].NumberValue = random.Next(1, 100); ; } //3.組建圖表 SetChart(sheet, ExcelChartType.BarClustered);var strFullName = @"D:\Data\Upload\" + "Export" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; book.SaveToFile(strFullName, ExcelVersion.Version2010); } catch (Exception ex) { } return Json(true, JsonRequestBehavior.AllowGet); } private void SetChart(Worksheet sheet, ExcelChartType chartFormat) { //1.設定sheet頁的名稱 sheet.Name = "Chart data"; sheet.GridLinesVisible = false; Chart chart = sheet.Charts.Add(); //2.指定組建圖表的地區 chart.DataRange = sheet.Range["A1:B6"]; chart.SeriesDataFromRange = false; //3.指定圖表的所在位置 chart.LeftColumn = 5; chart.TopRow = 2; chart.RightColumn = 11; chart.BottomRow = 29; chart.ChartType = chartFormat; //4.設定圖表的名稱以及x、y軸的名稱 chart.ChartTitle = "部門資訊"; chart.ChartTitleArea.IsBold = true; chart.ChartTitleArea.Size = 12; chart.PrimaryCategoryAxis.Title = "部門"; chart.PrimaryCategoryAxis.Font.IsBold = true; chart.PrimaryCategoryAxis.TitleArea.IsBold = true; chart.PrimaryValueAxis.Title = "人數"; chart.PrimaryValueAxis.HasMajorGridLines = false; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MinValue = 0; chart.PrimaryValueAxis.TitleArea.IsBold = true; //5.設定圖表的值 Spire.Xls.Charts.ChartSerie cs = chart.Series[0]; cs.CategoryLabels = sheet.Range["A2:A6"]; cs.Values = sheet.Range["B2:B6"]; cs.DataFormat.ShowActiveValue = true; chart.Legend.Position = LegendPositionType.Top; }
通過以上一段代碼得到的Excel內容如下:
代碼釋疑:關於上面的代碼不難,但還是想做些簡單的說明。
- 首先填充表格式資料,Spire.XLS讀寫資料表格使用的是sheet.Range[i, iCellcount++].Text這種方式。值得一提的是這裡的行列索引都是從1開始的。Range除了提供行列索引的方式,還提供了Range["B1"].Text這種方式去讀取值。
- 通過上文Excel組建圖表原理我們知道,出了有資料表格,還得選中組建圖表的地區,上述代碼裡面通過 chart.DataRange = sheet.Range["A1:B6"]; 這一句去指定地區,和Excel裡面的操作方式保持一致。
- 通過 chart.ChartType = chartFormat; 來指定需要產生的圖表類型,Spire.XLS裡面通過一個枚舉類型包含了各種圖表類型。
- 除了上面的這些,組件還支援指定圖表在文檔中的位置、圖表座標的最大值最小值。並且能夠通過
Spire.Xls.Charts.ChartSerie cs = chart.Series[0];cs.CategoryLabels = sheet.Range["A2:A6"];cs.Values = sheet.Range["B2:B6"];
這種方式去指定分類和值的地區,更加符合Excel的操作習慣。當然,如無特殊,這些完全可以不用指定。
2.3、對兩項或者多項進行統計
上面只是一個最簡單的例子,如果要對多列進行統計呢?我們繼續來看這個例子,我們將代碼改成這樣:
[HttpPost] public JsonResult ExportData() { try { Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; var random = new Random(); var iCellcount = 1; //1.設定表頭 sheet.Range[1, iCellcount++].Text = "部門名稱"; sheet.Range[1, iCellcount++].Text = "在職人數"; sheet.Range[1, iCellcount++].Text = "離職人數"; var lstDeptName = new List<string>() { "市場部", "策劃部", "公關部", "行政部", "開發部" }; var a = 0; //2.構造表資料 for (var i = 2; i < 7; i++) { iCellcount = 1; sheet.Range[i, iCellcount++].Text = lstDeptName[a++]; sheet.Range[i, iCellcount++].NumberValue = random.Next(1, 100); sheet.Range[i, iCellcount++].NumberValue = random.Next(1, 100); ; }//3.組建圖表 SetChart(sheet, ExcelChartType.BarClustered); var strFullName = @"D:\Data\Upload\" + "Export" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; book.SaveToFile(strFullName, ExcelVersion.Version2010); } catch (Exception ex){} return Json(true, JsonRequestBehavior.AllowGet); } private void SetChart(Worksheet sheet, ExcelChartType chartFormat) { //1.設定sheet頁的名稱 sheet.Name = "Chart data"; sheet.GridLinesVisible = false; Chart chart = sheet.Charts.Add(); //2.指定組建圖表的地區 chart.DataRange = sheet.Range["A1:C6"]; chart.SeriesDataFromRange = false; //3.指定圖表的所在位置 chart.LeftColumn = 5; chart.TopRow = 2; chart.RightColumn = 11; chart.BottomRow = 29; chart.ChartType = chartFormat; //4.設定圖表的名稱以及x、y軸的名稱 chart.ChartTitle = "部門資訊"; chart.ChartTitleArea.IsBold = true; chart.ChartTitleArea.Size = 12; chart.PrimaryCategoryAxis.Title = "部門"; chart.PrimaryCategoryAxis.Font.IsBold = true; chart.PrimaryCategoryAxis.TitleArea.IsBold = true; chart.PrimaryValueAxis.Title = "人數"; chart.PrimaryValueAxis.HasMajorGridLines = false; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MinValue = 0; chart.PrimaryValueAxis.TitleArea.IsBold = true; //5.設定圖表的值 Spire.Xls.Charts.ChartSerie cs = chart.Series[0]; cs.DataFormat.ShowActiveValue = true; cs.DataFormat.ShowBubble = true; chart.Legend.Position = LegendPositionType.Top; }
得到結果如下:
這裡唯一的變化是資料區域,只要指定我們需要組建圖表的地區是哪部分,Excel會自動進行計算並組建圖表。
2.4、各種類型的圖表展示
上文說過, chart.ChartType = chartFormat; 這一句可以設定圖表的類型,在Spire.XLS裡面定義了一系列的圖表類型:
ExcelChartType
我們來看看一些比較常見的圖表
2.4.1、餅狀圖
ExcelChartType.Pie
ExcelChartType.Pie3D
2.4.2、連線圖
ExcelChartType.Line3D
ExcelChartType.LineStacked
2.4.3、地區圖
2.4.4、雷達圖
2.4.5、圓形柱狀圖
3、其他功能介紹
關於Spire.XLS的其他亮點功能,博主也還在研究,已經知道的一些常用功能比如(1)支援儲存格合并、凍結、注釋;(2)資料庫方式的匯入匯出;(3)Sheet頁的複製、切割、顯示、隱藏等;(4)頁首頁尾的設定;(5)資料的分組、排序;(6)像Excel插入圖片,設定圖片樣式等。這些功能有些已經實現,有些還在研究,等以後有機會再發出來供大家參考。因為篇幅問題,這篇先到這裡吧。
四、總結
以上簡單總結了下Spire.XLS組件幾個特色功能,很好的解決了博主遇到的問題,博主覺得在一定程度上,Spire.XLS組件能擬補NPOI、COM組件的部分不足。還有很多其他特色功能待以後整理之後連帶測試Demo一起發出。如果你也遇到一些其他組件解決不了的問題,不妨試試它,或許會帶給你驚喜。當然,如果本文能夠幫到你,還是希望園友們幫忙推薦,博主下次繼續努力!
本文原創出處:http://www.cnblogs.com/landeanfen/
歡迎各位轉載,但是未經作者本人同意,轉載文章之後必須在文章頁面明顯位置給出作者和原文串連,否則保留追究法律責任的權利
C#組件系列——又一款Excel處理神器Spire.XLS,你值得擁有