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);
}