0. Preparatory work1. Download and introduce Aspose.cellsDownload Aspose Cellsand introducing using Aspose.cells The following example uses the. NET 3.0 version of Aspose Cells, which compiles the environment VS2013For specific download and introduction methods see:http://www.cnblogs.com/moonache/p/4991459.html1. Use Aspose to convert a DataTable to Excel1. CodeThe following code is used to convert the DataTable DT to an Excel file and exist under the path directory
/// <summary>///DataTable to Excel file/// </summary>/// <param name= "DT" ></param>/// <param name= "path" ></param>/// <returns></returns> Public Static BOOLExportexcelwithaspose (DataTable DT,stringpath) { if(dt! =NULL) { Try{Aspose.Cells.Workbook Workbook=NewAspose.Cells.Workbook (); Aspose.Cells.Worksheet Cellsheet= Workbook. worksheets[0]; //add a style to headAspose.Cells.Style Headstyle =workbook. Styles[workbook. Styles.add ()]; //Set CenterHeadstyle.horizontalalignment =Aspose.Cells.TextAlignmentType.Center; //Set Background colorHeadstyle.foregroundcolor = System.Drawing.Color.FromArgb (215,236,241); Headstyle.pattern=Backgroundtype.solid; HeadStyle.Font.Size= A; HeadStyle.Font.Name="Song Body"; HeadStyle.Font.IsBold=true; //add a style to a cellAspose.Cells.Style CellStyle =workbook. Styles[workbook. Styles.add ()]; //Set CenterCellstyle.horizontalalignment =Aspose.Cells.TextAlignmentType.Center; Cellstyle.pattern=Backgroundtype.solid; CellStyle.Font.Size= A; CellStyle.Font.Name="Song Body"; //sets the width of columns from 0 to the column width unit is the characterCellSheet.Cells.SetColumnWidth (1, +); CellSheet.Cells.SetColumnWidth (5, A); CellSheet.Cells.SetColumnWidth (7,Ten); CellSheet.Cells.SetColumnWidth (8, -); CellSheet.Cells.SetColumnWidth (9, -); intRowIndex =0; intColindex =0; intColCount =dt. Columns.count; intRowCount =dt. Rows.Count; //Head column name processing for(inti =0; i < ColCount; i++) {cellsheet.cells[rowindex, Colindex]. Putvalue (dt. Columns[i]. ColumnName); Cellsheet.cells[rowindex, Colindex]. SetStyle (Headstyle); Colindex++; } RowIndex++; //Cell other cells processing for(inti =0; i < RowCount; i++) {Colindex=0; for(intj =0; J < ColCount; J + +) {cellsheet.cells[rowindex, Colindex]. Putvalue (dt. ROWS[I][J]. ToString ()); Cellsheet.cells[rowindex, Colindex]. SetStyle (CellStyle); Colindex++; } RowIndex++; } cellsheet.autofitcolumns (); //column width automatically matches when column widths are too long to shrinkPath =Path.GetFullPath (Path); //workbook. Save (path,saveformat.csv); workbook. Save (path); return true; } Catch(Exception e) {Throw NewException ("failed to export Excel"+e.message); } } Else { return false; }}
The following code is used to output Excel files directly on the page for users to download
Wondertools.exportexcelwithaspose (templatedt,filepath);//to provide Excel downloadsHttpResponse _response =httpcontext.current.response;_response.clear (); _response.clearheaders (); _response.buffer=false; _response.contenttype="Application/x-excel"; _response.appendheader ("content-disposition","attachment;filename=template.xlsx"); _response.writefile (fileinfo.fullname); _response.flush (); _response.end () ;
PS:more Excel format settings: http://blog.sina.com.cn/s/blog_6438b0e50101cfer.html Official manual:Http://www.aspose.com/docs/display/cellsnet/Introduction+of+Aspose.Cells+for+.NET From for notes (Wiz)
Use Aspose to turn a DataTable into Excel