public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Create directory if it is not already present. bool IsExists = System.IO.Directory.Exists(dataDir); if (!IsExists) { System.IO.Directory.CreateDirectory(dataDir); } // Instantiate a new Workbook. Workbook workbook = new Workbook(); // Get the first worksheet in the book. Worksheet WS = workbook.Worksheets[0]; // Create a range of cells. Aspose.Cells.Range range = WS.Cells.CreateRange(1, 1, 1, 18); // Name the range. range.Name = "MyRange"; // Declare a style object. Style stl; // Create/add the style object. stl = workbook.CreateStyle(); // Specify some Font settings. stl.Font.Name = "Arial"; stl.Font.IsBold = true; // Set the font text color stl.Font.Color = Color.Red; // To Set the fill color of the range, you may use ForegroundColor with // Solid Pattern setting. stl.ForegroundColor = Color.Yellow; stl.Pattern = BackgroundType.Solid; // Create a StyleFlag object. StyleFlag flg = new StyleFlag(); // Make the corresponding attributes ON. flg.Font = true; flg.CellShading = true; // Apply the style to the range. range.ApplyStyle(stl, flg); // Save the excel file. workbook.Save(dataDir + "rangestyles.out.xls"); // ExEnd:1 }
/// <summary> /// Applies this style container to the specified range. /// </summary> /// <param name="range">The range.</param> /// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception> public void ApplyToRange( Range range) { if (range == null) { throw new ArgumentNullException(nameof(range)); } range.ApplyStyle(this.Style, this.StyleFlag); }
//Quang Huy 2014-02-25 /// <summary> /// tô nền cho range /// </summary> /// <param name="range"></param> /// <param name="color"></param> private void SetRangeBgColor(Aspose.Cells.Range range, System.Drawing.Color?color = null) { System.Drawing.Color c = color ?? System.Drawing.Color.White; //Aspose.Cells.Style s = range.Style ?? range[0, 0].GetStyle(); Aspose.Cells.Style s = range[0, 0].GetStyle(); s.BackgroundColor = c; s.Update(); range.ApplyStyle(s, (new StyleFlag() { All = true })); }
public async Task <IActionResult> ExportExcel(string receive_Date) { var data = await _serviceCompare.GetCompare(receive_Date); var count = data.Count; var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\CompareReport.xlsx"); WorkbookDesigner designer = new WorkbookDesigner(); designer.Workbook = new Workbook(path); Worksheet worksheet = designer.Workbook.Worksheets[0]; designer.SetDataSource("result", data); designer.Process(); // ----------------Add style excel------------------------// Style stl = designer.Workbook.CreateStyle(); stl.ForegroundColor = Color.FromArgb(210, 105, 30); stl.Pattern = BackgroundType.Solid; StyleFlag flg = new StyleFlag(); flg.Font = true; flg.CellShading = true; flg.Alignments = true; for (int i = 17; i < count + 17; i++) { var checkCell1 = worksheet.Cells["K" + i].Value.ToString(); var checkCell2 = worksheet.Cells["O" + i].Value.ToString(); Aspose.Cells.Range range = worksheet.Cells.CreateRange(i - 1, 0, 1, 15); if (checkCell1 == "0" || checkCell2 == "0") { range.ApplyStyle(stl, flg); } } //--------------------End add Style-------------------------// // for (int i = 16; i < count + 16; i++) // { // worksheet.Cells["H"+ i].PutValue(receive_Date); // } worksheet.Cells["E2"].PutValue(data[0].Freeze_Date); MemoryStream stream = new MemoryStream(); designer.Workbook.Save(stream, SaveFormat.Xlsx); byte[] result = stream.ToArray(); return(File(result, "application/xlsx", "Excel" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx")); }
public static void Run() { // Instantiate a new Workbook. Workbook workbook = new Workbook(); // Get the first worksheet in the book. Worksheet WS = workbook.Worksheets[0]; // Create a range of cells. Aspose.Cells.Range range = WS.Cells.CreateRange(1, 1, 5, 5); // Name the range. range.Name = "MyRange"; // Declare a style object. Style stl; // Create/add the style object. stl = workbook.CreateStyle(); // Specify some Font settings. stl.Font.Name = "Arial"; stl.Font.IsBold = true; // Set the font text color stl.Font.Color = Color.Red; // To Set the fill color of the range, you may use ForegroundColor with // Solid Pattern setting. stl.ForegroundColor = Color.Yellow; stl.Pattern = BackgroundType.Solid; // Create a StyleFlag object. StyleFlag flg = new StyleFlag(); // Make the corresponding attributes ON. flg.Font = true; flg.CellShading = true; // Apply the style to the range. range.ApplyStyle(stl, flg); // Save the excel file. workbook.Save(outputDir + "outputFormatRanges1.xlsx"); Console.WriteLine("FormatRanges1 executed successfully."); }
public Workbook GetAllBooksExcel() { //Instantiate License class and call its SetLicense method to use the license string licPath = @"C:\ProjectBookClub\Aspose.Total.lic"; Aspose.Cells.License lic = new Aspose.Cells.License(); lic.SetLicense(licPath); DataSet dataset = new DataSet(); string bkConn = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["BookClubConnString"].ConnectionString; using (SqlConnection conn = new SqlConnection(bkConn)) { SqlCommand cmd = new SqlCommand("_sp_GetAllBooksExcel", conn); cmd.CommandType = CommandType.StoredProcedure; conn.Open(); //DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dataset); } DataTable books = dataset.Tables[0]; Workbook workBook = new Workbook(); workBook.Worksheets.Clear(); // Format the header row Worksheet workSheet = workBook.Worksheets.Add("Books"); workSheet.Cells.ImportDataTable(books, true, "A1"); Aspose.Cells.Range range = workSheet.Cells.CreateRange("A1", "I1"); Aspose.Cells.Style headerStyle = workBook.CreateStyle(); headerStyle.Font.Name = "Calibri"; headerStyle.Font.IsBold = true; headerStyle.Font.Size = 13; StyleFlag flg = new StyleFlag(); flg.Font = true; range.ApplyStyle(headerStyle, flg); //Format font for the rest of the sheet Range range1 = workSheet.Cells.CreateRange("A2", "I100"); Style style1 = workBook.CreateStyle(); style1.Font.Name = "Calibri"; style1.Font.Size = 12; StyleFlag flg1 = new StyleFlag(); flg1.Font = true; range1.ApplyStyle(style1, flg); // Title workSheet.Cells.SetColumnWidth(0, 40); // Author workSheet.Cells.SetColumnWidth(1, 25); // Date Read - set date format for this column Aspose.Cells.Style style = workBook.CreateStyle(); style.Number = 14; StyleFlag flag = new StyleFlag(); flag.NumberFormat = true; //workSheet.Cells.SetColumnWidth(7, 16); //workSheet.Cells.Columns[7].ApplyStyle(style, flag); workSheet.Cells.SetColumnWidth(2, 12); workSheet.Cells.Columns[2].ApplyStyle(style, flag); // Main Characters workSheet.Cells.SetColumnWidth(3, 40); // Notes workSheet.Cells.SetColumnWidth(4, 40); // Book Club workSheet.Cells.SetColumnWidth(5, 20); // Book Information workSheet.Cells.SetColumnWidth(6, 40); // Stars workSheet.Cells.SetColumnWidth(7, 15); // Genre workSheet.Cells.SetColumnWidth(8, 15); return(workBook); }