Excel
Creating Worksheets
With ComponentOne Excel for .NET you can create Microsoft Excel files directly from your application's code.
Features
Description
With ComponentOne Excel for .NET you can create Microsoft Excel files directly from your application's code. The possibilities are endless with an easy-to-manipulate Excel object model exposed in your code. Create Excel files from data that does not directly support Excel exportation like any grid, schedule or chart. Excel for .NET supports formulas, cell styles and Excel formatting. This sample creates a workbook with NorthWind product information using the C1XLBook control. Each product category is placed on a separate worksheet.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 | using C1.C1Excel; using MvcExplorer.Models; using System; using System.Collections; using System.Collections.Generic; using System.Drawing; using System.IO; using System.Linq; using System.Web; using System.Web.Mvc; namespace MvcExplorer.Controllers.Excel { public partial class ExcelController : Controller { // // GET: /Excel/ public ActionResult Index() { return View(); } private C1NWindEntities db = new C1NWindEntities(); XLStyle _styTitle; XLStyle _styHeader; XLStyle _styMoney; XLStyle _styOrder; C1XLBook _c1xl = new C1.C1Excel.C1XLBook(); protected static string TEMP_DIR; public ActionResult GenerateExcel() { TEMP_DIR = Server.MapPath( "~" ) + "\\Temp" ; if (Directory.Exists(TEMP_DIR)) { } else { Directory.CreateDirectory(TEMP_DIR); } string filename = CreateExcelFile(); try { Response.Clear(); Response.Charset = "UTF-8" ; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.AddHeader( "Content-Disposition" , "attachment; filename=" + filename); Response.ContentType = "application/ms-excel" ; Response.TransmitFile(filename); Response.Flush(); System.IO.File.Delete(filename); Response.End(); } catch (Exception ex) { Response.Write(ex.Message); } return View(); } private string CreateExcelFile() { //clear Excel book, remove the single blank sheet _c1xl.Clear(); _c1xl.Sheets.Clear(); _c1xl.DefaultFont = new Font( "Tahoma" , 8); //create Excel styles _styTitle = new XLStyle(_c1xl); _styHeader = new XLStyle(_c1xl); _styMoney = new XLStyle(_c1xl); _styOrder = new XLStyle(_c1xl); //set up styles _styTitle.Font = new Font(_c1xl.DefaultFont.Name, 15, FontStyle.Bold); _styTitle.ForeColor = Color.Blue; _styHeader.Font = new Font(_c1xl.DefaultFont, FontStyle.Bold); _styHeader.ForeColor = Color.White; _styHeader.BackColor = Color.DarkGray; _styMoney.Format = XLStyle.FormatDotNetToXL( "c" ); _styOrder.Font = _styHeader.Font; _styOrder.ForeColor = Color.Red; //create report with one sheet per category List<Category> categories = db.Categories.ToList<Category>(); foreach (Category category in categories) { CreateSheet(category); } //save xls file string uid = System.Guid.NewGuid().ToString(); string filename = Server.MapPath( "~" ) + "\\Temp\\testexcel" + uid + ".xls" ; _c1xl.Save(filename); return filename; } private void CreateSheet(Category category) { //get current category name string catName = category.CategoryName; //add a new worksheet to the workbook //('/' is invalid in sheet names, so replace it with '+') string sheetName = catName.Replace( "/" , " + " ); XLSheet sheet = _c1xl.Sheets.Add(sheetName); //add title to worksheet sheet[0, 0].Value = catName; sheet.Rows[0].Style = _styTitle; // set column widths (in twips) sheet.Columns[0].Width = 300; sheet.Columns[1].Width = 2200; sheet.Columns[2].Width = 1000; sheet.Columns[3].Width = 1600; sheet.Columns[4].Width = 1000; sheet.Columns[5].Width = 1000; sheet.Columns[6].Width = 1000; //add column headers int row = 2; sheet.Rows[row].Style = _styHeader; sheet[row, 1].Value = "Product Name" ; sheet[row, 2].Value = "Unit Price" ; sheet[row, 3].Value = "Qty/Unit" ; sheet[row, 4].Value = "Stock Units" ; sheet[row, 5].Value = "Stock Value" ; sheet[row, 6].Value = "Reorder" ; //loop through products in this category //DataRow[] products = category.GetChildRows("Categories_Products"); List<Product> products = db.Products.Where(pro => pro.CategoryID == category.CategoryID).ToList<Product>(); foreach (Product product in products) { //move on to next row row++; //add row with some data sheet[row, 1].Value = product.ProductName; sheet[row, 2].Value = product.UnitPrice; sheet[row, 3].Value = product.QuantityPerUnit; sheet[row, 4].Value = product.UnitsInStock; //calculate value in stock double valueInStock = Convert.ToDouble(product.UnitPrice) * Convert.ToInt32(product.UnitsInStock); sheet[row, 5].Value = valueInStock; //check reorder level if (Convert.ToInt32(product.UnitsInStock) <= Convert.ToInt32(product.ReorderLevel)) { sheet[row, 6].Value = "<<<" ; sheet[row, 6].Style = _styOrder; } //format money cells sheet[row, 2].Style = _styMoney; sheet[row, 5].Style = _styMoney; } if (products.Count == 0) { row++; sheet[row, 1].Value = "No products in this category" ; } } } } |
1 2 3 4 5 6 7 | @Html .ActionLink(Resources.Excel.Index_GenerateExcel, "GenerateExcel" ) < br /> < img src = "~/Content/images/excelWorkSheets.PNG" /> @section Description{ < p > @Html .Raw(Resources.Excel.Index_Text0)</ p > } |