(C#)MyXlsでExcelレポートを生成
6844 ワード
MyXlsはC#のオープンソースプロジェクトで、aspに応用できる.Netまたは.Netアプリケーション上.マイクロソフトが公開したExcleドキュメントフォーマットファイル(BIFF)に基づいて、Excel versions 97-2007をサポートするバイナリ形式で直接excelドキュメントを生成します.これは、サーバにofficeをインストールすることなく、データベースに格納されているデータをexcle形式で出力できることを意味します.これは多くのプロジェクトにとって役に立ちます.現在、MyXlsでは、テキストの色、テキストのサイズ、フォント、単位格子の枠線、下色、列幅、行の高さ、セルのマージ、複数のsheetページなどの機能を含むセル(cell)のフォーマットが実現されています.現在、MyXlsではexcelドキュメント内のオブジェクト(テキストボックス、ボタンなど)の生成はサポートされていません.MyXlsのホームページでは、excelファイルの読み取り機能が実現するとしていますが、個人的には読み取り機能の使い道はそれほど多くないと思います.Webサイトおよびダウンロード:http://myxls.in2bits.org/----------------------------------------------------------------------------------------------------------------------------------------------これは私がテストしたサンプルコードprivate void button 2_です.Click(object sender,EventArgs e){xlsGridview(ds,“Data”);//xlsGridviewメソッドを呼び出してExcelレポートを生成する}private void xlsGridview(DataSet ds,string xlsName){XlsDocument xls=new XlsDocument(); xls.FileName = xlsName; int rowIndex = 1; int colIndex = 0; DataTable table = ds.Tables[0]; Worksheet sheet = xls.Workbook.Worksheets.Add("Hello World Sheet");//ステータスバータイトル名Cells cells=sheet.Cells;foreach(DataColumn col in table.Columns){colIndex+;//sheet.cells.AddValue Cell(1,colIndex,col.ColumnName);//XLSタイトル行cells.Add(1,colIndex,col.ColumnName); } foreach (DataRow row in table.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in table.Columns) {colIndex++;//sheet.cells.AddValue Cell(rowIndex,colIndex,row[col.ColumnName].ToString();//xlsテーブルにデータを追加//cell=cells.AddValue Cell(rowIndex,colIndex,Convert.ToDouble(row[col.ColumnName].ToString());//デジタルCell cell=cells.Addに変換(rowIndex, colIndex, row[col.ColumnName].ToString());//もしあなたのデータベースのデータがすべて数字であれば、変換したほうがいいです.そうでなければ、Excelにインポートするのは文字列で表示されます. //cell.Font.FontFamily = FontFamilies.Roman;//フォント//cell.Font.Bold = true;//フォントは太字です.Font.Weight = FontWeight.Bold; } } xls.Save(); }----------------------------------------------------------------------------------------------------------------------------------------------OverviewMyXLS is a .NET 2.0 library that writes and reads native Excel files quickly and easily, including formatting and multiple sheets. Generate Excel files for ASP.NET sites or .NET applications. Doesn't require Excel on the server or any licensing. Compatible with Excelversions 97 and up. Features * Pure .NET code - no P/Invokes, Interop assemblies, or Excel COM automation behind the scenes. * No other dependencies - add and reference the MyXls dll in your project and go! * Fast! (we will be adding some performance metrics) * Lightweight! The dll is only ~100 KB * Free to use, no licensing restrictions. * Open Source Project * The files generated are 100% compatible with Excel versions 97 and up. Files are BIFF8, which is Excel 97-2003's native format, and fully forward compatible with Excel 2007. * Create any number of Worksheets * Name Worksheets * Write values to any cell on any Worksheet - values don't have to be in contiguous ranges * Supports a good portion of Excel's formatting capabilities (bold, underline, italic, rotation, etc) * Supports writing Metadata - values displayed in Excel's File->Properties dialog box
Configuring Your Project
Download the binaries. Add a reference to Add a using statement to reference the MyXls namespace
Edit
Writing an Excel Document
Configuring Your Project
MyXls is easy to add to your project.
org.in2bits.MyXls.dll
in your project using org.in2bits.MyXls;
Edit
Writing an Excel Document
Edit
Basic Example
This example creates a new Excel file, adds a named worksheet, and puts a value into a cell in that worksheet. Very basic but it shows how in six lines of code you can use MyXls to create Excel files. XlsDocument doc = new XlsDocument(); doc.FileName = "HelloWorld.xls"; Worksheet sheet = doc.Workbook.Worksheets.Add("Hello World Sheet"); Cell cell = sheet.Cells.Add(1, 1, "Hello!"); cell.Font.Weight = FontWeight.Bold;
doc.Save();
Edit
Web Application Support
MyXls has built in support for web applications. Using the XlsDocument.Send()
method you can easily send an Excel spreadsheet back to the client. // Send the document back as an attachement // Same as doc.Send(XlsDocument.SendMethods.Attachment); doc.Send();
// or use // send the document back within the page doc.Send(XlsDocument.SendMethods.Inline);
Edit
Reading an Excel Document
Read support is still relatively experimental. However, MyXls should be able to read all values (even formulas! though you can't write formulas with it yet!) and formatting. It will not read any OLE objects, Pivot Tables, Charts/Graphs, VBA modules, etc. -- it will simply ignore them. It supports only Excel versions 97 thru 2003/XP - 2007's .xlsx XML format is not supported. However, you can try the below to see if it will work for you, and please submit a bug if you have any problems beyond that.Edit
Basic Example
This example reads various values (including formula results) from an example Excel file, BlankBudgetWorksheet.xls. This does does not show retrieving formatting (font size, borders, etc.), but you should get the basic idea of how to reference the cells on which you would read those other properties: XlsDocument xls = new XlsDocument(@"c:/Path/To/BlankBudgetWorksheet.xls", null); //read in the Excel file
string worksheet1Name = xls.Workbook.Worksheets0.Name; //Worksheet 1 name : "Budget" string worksheet2Name = xls.Workbook.Worksheets1.Name; //Worksheet 2 name : "Income" string worksheet3Name = xls.Workbook.Worksheets2.Name; //Worksheet 3 name : "Expenses"
Worksheet sheet = xls.Workbook.Worksheets0; //get a reference to a Worksheet
string cellH6HyperlinkText = (string)sheet.Rows6.CellAtCol(8).Value; //Cell H6 hyperlink text : "See reverse for instructions and guidelines" string cellJ7Value = (string)sheet.Rows7.CellAtCol(10).Value; //Cell J7 value : "Budget Plan" string cellG28Value = (string)sheet.Rows28.CellAtCol(7).Value; //Cell G28 value : "Administrative Support (12% of Revenue)" long cellC10Value = (long)sheet.Rows10.CellAtCol(3).Value; //Cell C10 value : 6801 string cellF10FormulaResultValue = (string)sheet.Rows10.CellAtCol(6).Value; //Cell F10 Formula result value : "- 20"
doc.Save();
// or use // send the document back within the page doc.Send(XlsDocument.SendMethods.Inline);
Read support is still relatively experimental. However, MyXls should be able to read all values (even formulas! though you can't write formulas with it yet!) and formatting. It will not read any OLE objects, Pivot Tables, Charts/Graphs, VBA modules, etc. -- it will simply ignore them. It supports only Excel versions 97 thru 2003/XP - 2007's .xlsx XML format is not supported. However, you can try the below to see if it will work for you, and please submit a bug if you have any problems beyond that.Edit
Basic Example
This example reads various values (including formula results) from an example Excel file, BlankBudgetWorksheet.xls. This does does not show retrieving formatting (font size, borders, etc.), but you should get the basic idea of how to reference the cells on which you would read those other properties:
XlsDocument xls = new XlsDocument(@"c:/Path/To/BlankBudgetWorksheet.xls", null); //read in the Excel file
string worksheet1Name = xls.Workbook.Worksheets0.Name; //Worksheet 1 name : "Budget" string worksheet2Name = xls.Workbook.Worksheets1.Name; //Worksheet 2 name : "Income" string worksheet3Name = xls.Workbook.Worksheets2.Name; //Worksheet 3 name : "Expenses"
Worksheet sheet = xls.Workbook.Worksheets0; //get a reference to a Worksheet
string cellH6HyperlinkText = (string)sheet.Rows6.CellAtCol(8).Value; //Cell H6 hyperlink text : "See reverse for instructions and guidelines" string cellJ7Value = (string)sheet.Rows7.CellAtCol(10).Value; //Cell J7 value : "Budget Plan" string cellG28Value = (string)sheet.Rows28.CellAtCol(7).Value; //Cell G28 value : "Administrative Support (12% of Revenue)" long cellC10Value = (long)sheet.Rows10.CellAtCol(3).Value; //Cell C10 value : 6801 string cellF10FormulaResultValue = (string)sheet.Rows10.CellAtCol(6).Value; //Cell F10 Formula result value : "- 20"