private static void CreateSalesReport(string filename)
{
// Create a new Workbook.
Workbook workbook = new Workbook();
/*
* Note: Since Excel color palette has 56 colors on it.
* The colors are indexed 0-55.
* Please check: http:// Www.aspose.com/Products/Aspose.Cells/Api/Aspose.Cells.Workbook.ChangePalette.html
* If a color is not present on the palette, we have to add it
* To the palette, so that we may use.
* Add a few custom colors to the palette.
*/
workbook.ChangePalette(Color.FromArgb(155, 204, 255), 55);
workbook.ChangePalette(Color.FromArgb(0, 51, 105), 54);
workbook.ChangePalette(Color.FromArgb(250, 250, 200), 53);
workbook.ChangePalette(Color.FromArgb(124, 199, 72), 52);
CreateReportData(workbook);
CreateCellsFormatting(workbook);
// Get the first worksheet in the book.
Worksheet worksheet = workbook.Worksheets[0];
// Name the worksheet.
worksheet.Name = "Sales Report";
// Save the excel file.
workbook.Save(filename);
}
public byte[] GenerateReport(LoanOffer loanOffer, bool isExcel, bool isShowDetails, string header)
{
var worksheet = _workbook.Worksheets[_workbook.Worksheets.ActiveSheetIndex];
worksheet.Name = "Loan Offer";
int row = 6;
int column = 1;
_workbook.ChangePalette(Color.FromArgb(197, 197, 197), 55);
_workbook.ChangePalette(Color.FromArgb(221, 221, 221), 54);
_workbook.ChangePalette(Color.FromArgb(123, 178, 36), 53);
HeaderReportGenerator.CreateHeader(worksheet, header, column - 1, column, 7);
worksheet.Cells.SetColumnWidth(0, 1);
worksheet.Cells.SetColumnWidth(1, 16);
worksheet.Cells.SetColumnWidth(2, 15);
CreateXlsHeader(worksheet, row, column);
var i = 0;
foreach (var item in loanOffer.Schedule)
{
row++;
i++;
worksheet.Cells[row, column].PutValue(item.Date.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture));
worksheet.Cells[row, column + 1].PutValue("£" + FormattingUtils.FormatMiddle(item.LoanRepayment));
worksheet.Cells[row, column + 2].PutValue("£" + FormattingUtils.FormatMiddle(item.Interest));
worksheet.Cells[row, column + 3].PutValue(FormattingUtils.FormatMiddle(item.InterestRate * 100) + "%");
var fee = loanOffer.SetupFee > 0 && i == 1 ? loanOffer.SetupFee : 0;
if (item.Fees > 0)
{
fee += item.Fees;
}
var res = fee != 0 ? "£" + FormattingUtils.FormatMiddle(fee) : "-";
var res1 = loanOffer.SetupFee > 0 && i == 1 ? "*" : string.Empty;
worksheet.Cells[row, column + 4].PutValue(res + res1);
//worksheet.Cells.Merge(row, column + 5, 1, 3);
worksheet.Cells[row, column + 5].PutValue("£" + FormattingUtils.FormatMiddle(item.AmountDue));
SetCellStyle(worksheet, row, column, false);
}
row = CreateTotalBlock(loanOffer, row, column, worksheet);
if (loanOffer.Details.IsModified)
{
row++;
worksheet.Cells.Merge(row, column, 1, 7);
worksheet.Cells[row, column].PutValue("Offer was manually modified");
worksheet.Cells[row, column].Style.Font.IsBold = true;
}
if (isShowDetails)
{
CreateDetails(loanOffer.Details, row, column, worksheet);
}
return(ConvertFormat(_workbook, isExcel ? FileFormatType.Excel2003 : FileFormatType.Pdf));
}
static void Main(string[] args)
{
Workbook workbook = new Workbook(); // Creating a Workbook object
workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[0];
//Accessing cell from the worksheet
Cell cell = worksheet.Cells["B2"];
Style style = cell.GetStyle();
//Setting the foreground color to yellow
style.BackgroundColor = Color.Yellow;
//Setting the background pattern to vertical stripe
style.Pattern = BackgroundType.VerticalStripe;
//Saving the modified style to the "B2" cell.
cell.SetStyle(style);
// === Setting Foreground ===
//Adding custom color to the palette at 55th index
Color color = Color.FromArgb(212, 213, 0);
workbook.ChangePalette(color, 55);
//Accessing cell from the worksheet
cell = worksheet.Cells["B3"];
//Adding some value to the cell
cell.PutValue("Hello Aspose!");
workbook.Save("test.xlsx", SaveFormat.Xlsx); //Workbooks can be saved in many formats
}
static void Main(string[] args)
{
Workbook workbook = new Workbook(); // Creating a Workbook object
workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[0];
//Accessing cell from the worksheet
Cell cell = worksheet.Cells["B2"];
Style style = cell.GetStyle();
//Setting the foreground color to yellow
style.BackgroundColor = Color.Yellow;
//Setting the background pattern to vertical stripe
style.Pattern = BackgroundType.VerticalStripe;
//Saving the modified style to the "B2" cell.
cell.SetStyle(style);
// === Setting Foreground ===
//Adding custom color to the palette at 55th index
Color color = Color.FromArgb(212, 213, 0);
workbook.ChangePalette(color, 55);
//Accessing cell from the worksheet
cell = worksheet.Cells["B3"];
//Adding some value to the cell
cell.PutValue("Hello Aspose!");
workbook.Save("test.xlsx", SaveFormat.Xlsx); //Workbooks can be saved in many formats
}
private static Color GetColor(Color color, Workbook workBook)
{
var firstColorPalleteIndex = workBook.Colors.Length;
if (!_registeredColors.ContainsKey(color))
{
firstColorPalleteIndex = firstColorPalleteIndex - 1;
_registeredColors.Add(color, firstColorPalleteIndex);
workBook.ChangePalette(color, firstColorPalleteIndex);
}
return(workBook.GetMatchingColor(color));
}
private static void CreateSalesReport(string filename)
{
/*
* Uncomment the code below when you have purchased license
* for Aspose.Cells. You need to deploy the license in the
* same folder as your executable, alternatively you can add
* the license file as an embedded resource to your project.
*/
Aspose.Cells.License cellsLicense = new
Aspose.Cells.License();
cellsLicense.SetLicense("Aspose.Cells.lic");
// Create a new Workbook.
Workbook workbook = new Workbook();
/*
* Note: Since Excel color palette has 56 colors on it.
* The colors are indexed 0-55.
* Please check: http:// Www.aspose.com/Products/Aspose.Cells/Api/Aspose.Cells.Workbook.ChangePalette.html
* If a color is not present on the palette, we have to add it
* To the palette, so that we may use.
* Add a few custom colors to the palette.
*/
workbook.ChangePalette(Color.FromArgb(155, 204, 255), 55);
workbook.ChangePalette(Color.FromArgb(0, 51, 105), 54);
workbook.ChangePalette(Color.FromArgb(250, 250, 200), 53);
workbook.ChangePalette(Color.FromArgb(124, 199, 72), 52);
CreateReportData(workbook);
CreateCellsFormatting(workbook);
// Get the first worksheet in the book.
Worksheet worksheet = workbook.Worksheets[0];
// Name the worksheet.
worksheet.Name = "Sales Report";
// Save the excel file.
workbook.Save(filename);
}
private static void CreateSalesReport(string filename)
{
// Uncomment the code below when you have purchased license
// for Aspose.Cells. You need to deploy the license in the
// same folder as your executable, alternatively you can add
// the license file as an embedded resource to your project.
//
// // Set license for Aspose.Cells
// Aspose.Cells.License cellsLicense = new
// Aspose.Cells.License();
// cellsLicense.SetLicense("Aspose.Cells.lic");
//Create a new Workbook.
Workbook workbook = new Workbook();
//Note: Since Excel color palette has 56 colors on it.
//The colors are indexed 0-55.
//Please check: http://www.aspose.com/Products/Aspose.Cells/Api/Aspose.Cells.Workbook.ChangePalette.html
//If a color is not present on the palette, we have to add it
//to the palette, so that we may use.
//Add a few custom colors to the palette.
workbook.ChangePalette(Color.FromArgb(155, 204, 255), 55);
workbook.ChangePalette(Color.FromArgb(0, 51, 105), 54);
workbook.ChangePalette(Color.FromArgb(250, 250, 200), 53);
workbook.ChangePalette(Color.FromArgb(124, 199, 72), 52);
CreateReportData(workbook);
CreateCellsFormatting(workbook);
//Get the first worksheet in the book.
Worksheet worksheet = workbook.Worksheets[0];
//Name the worksheet.
worksheet.Name = "Sales Report";
//Save the excel file.
workbook.Save(filename);
}
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);
}
// Instantiating an Workbook object
Workbook workbook = new Workbook();
// Adding Orchid color to the palette at 55th index
workbook.ChangePalette(Color.Orchid, 55);
// Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
// Accessing the "A1" cell from the worksheet
Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Hello Aspose!");
// Defining new Style object
Style styleObject = workbook.CreateStyle();
// Setting the Orchid (custom) color to the font
styleObject.Font.Color = Color.Orchid;
// Applying the style to the cell
cell.SetStyle(styleObject);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Auto);
// ExEnd:1
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Adding Orchid color to the palette at 55th index
workbook.ChangePalette(Color.Orchid, 55);
//Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
//Accessing the "A1" cell from the worksheet
Cell cell = worksheet.Cells["A1"];
//Adding some value to the "A1" cell
cell.PutValue("Hello Aspose!");
//Defining new Style object
Style styleObject = workbook.Styles[workbook.Styles.Add()];
//Setting the Orchid (custom) color to the font
styleObject.Font.Color = Color.Orchid;
//Applying the style to the cell
cell.SetStyle(styleObject);
//Saving the Excel file
workbook.Save(dataDir + "book1.xls", SaveFormat.Auto);
}
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);
// Instantiating an Workbook object
Workbook workbook = new Workbook();
// Adding Orchid color to the palette at 55th index
workbook.ChangePalette(Color.Orchid, 55);
// Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
// Accessing the "A1" cell from the worksheet
Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Hello Aspose!");
// Defining new Style object
Style styleObject = workbook.CreateStyle();
// Setting the Orchid (custom) color to the font
styleObject.Font.Color = Color.Orchid;
// Applying the style to the cell
cell.SetStyle(styleObject);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Auto);
// ExEnd:1
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Adding Orchid color to the palette at 55th index
workbook.ChangePalette(Color.Orchid, 55);
//Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
//Accessing the "A1" cell from the worksheet
Cell cell = worksheet.Cells["A1"];
//Adding some value to the "A1" cell
cell.PutValue("Hello Aspose!");
//Defining new Style object
Style styleObject = workbook.Styles[workbook.Styles.Add()];
//Setting the Orchid (custom) color to the font
styleObject.Font.Color = Color.Orchid;
//Applying the style to the cell
cell.SetStyle(styleObject);
//Saving the Excel file
workbook.Save(dataDir + "book1.xls", SaveFormat.Auto);
}
public Workbook CreateCatalog()
{
try
{
DBInit();
}
catch
{
}
//Open a template file
string designerFile = MapPath("~/Designer/Northwind.xls");
Workbook workbook = new Workbook(designerFile);
ReadCategory();
//Create a new datatable
DataTable dataTable2 = new DataTable();
//Get a worksheet
Worksheet sheet = workbook.Worksheets["Sheet2"];
//Name the sheet
sheet.Name = "Catalog";
//Get the worksheet cells
Cells cells = sheet.Cells;
int currentRow = 55;
//Add LightGray color to color palette
workbook.ChangePalette(Color.LightGray, 55);
//Get the workbook's styles collection
StyleCollection styles = workbook.Styles;
//Set CategoryName style with formatting attributes
int styleIndex = styles.Add();
Style styleCategoryName = styles[styleIndex];
styleCategoryName.Font.Size = 14;
styleCategoryName.Font.Color = Color.Blue;
styleCategoryName.Font.IsBold = true;
styleCategoryName.Font.Name = "Times New Roman";
//Set Description style with formatting attributes
styleIndex = styles.Add();
Style styleDescription = styles[styleIndex];
styleDescription.Font.Name = "Times New Roman";
styleDescription.Font.Color = Color.Blue;
styleDescription.Font.IsItalic = true;
//Set ProductName style with formatting attributes
styleIndex = styles.Add();
Style styleProductName = styles[styleIndex];
styleProductName.Font.IsBold = true;
//Set Title style with formatting attributes
styleIndex = styles.Add();
Style styleTitle = styles[styleIndex];
styleTitle.Font.IsBold = true;
styleTitle.Font.IsItalic = true;
styleTitle.ForegroundColor = Color.LightGray;
styleIndex = styles.Add();
Style styleNumber = styles[styleIndex];
styleNumber.Font.Name = "Times New Roman";
styleNumber.Number = 8;
//Create the styleflag struct
StyleFlag styleflag = new StyleFlag();
styleflag.All = true;
//Get the horizontal page breaks collection
HorizontalPageBreakCollection hPageBreaks = sheet.HorizontalPageBreaks;
//Specify SQL for the command
string cmd = "SELECT ProductName, ProductID, QuantityPerUnit, " +
"UnitPrice FROM Products";
for (int i = 0; i < this.dataTable1.Rows.Count; i++)
{
currentRow += 2;
cells.SetRowHeight(currentRow, 20);
cells[currentRow, 1].SetStyle(styleCategoryName);
DataRow categoriesRow = this.dataTable1.Rows[i];
//Write CategoryName
cells[currentRow, 1].PutValue((string)categoriesRow["CategoryName"]);
//Write Description
currentRow++;
cells[currentRow, 1].PutValue((string)categoriesRow["Description"]);
cells[currentRow, 1].SetStyle(styleDescription);
dataTable2.Clear();
//Execuate command and fill the datatable
try
{
this.oleDbDataAdapter2 = new OleDbDataAdapter();
string cmdText = cmd + " where categoryid = "
+ categoriesRow["CategoryID"].ToString();
this.oleDbDataAdapter2.SelectCommand = new OleDbCommand(cmdText, this.oleDbConnection1);
this.oleDbConnection1.Open();
oleDbDataAdapter2.Fill(dataTable2);
}
catch
{
}
finally
{
oleDbDataAdapter2.Dispose();
this.oleDbConnection1.Close();
}
currentRow += 2;
//Import the datatable to the sheet
cells.ImportDataTable(dataTable2, true, currentRow, 1);
//Create a range
Range range = cells.CreateRange(currentRow, 1, 1, 4);
//Apply style to the range
range.ApplyStyle(styleTitle, styleflag);
//Create a range
range = cells.CreateRange(currentRow + 1, 1, dataTable2.Rows.Count, 1);
//Apply style to the range
range.ApplyStyle(styleProductName, styleflag);
//Create a range
range = cells.CreateRange(currentRow + 1, 4, dataTable2.Rows.Count, 1);
//Apply style to the range
range.ApplyStyle(styleNumber, styleflag);
currentRow += dataTable2.Rows.Count;
//Apply horizontal page breaks
hPageBreaks.Add(currentRow, 0);
}
//Remove the unnecessary worksheets in the workbook
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
sheet = workbook.Worksheets[i];
if (sheet.Name != "Catalog")
{
workbook.Worksheets.RemoveAt(i);
i--;
}
}
//Return the generated workbook
return workbook;
}
public Workbook CreateCatalog()
{
//Open a template file
string designerFile = MapPath("~/App_Data/xls/Northwind.xls");
Workbook workbook = new Workbook(designerFile);
productsList = productDao.GetAll();
CategoryList = categoryDao.GetAll();
//Create a new datatable
DataTable dataTable2 = new DataTable();
//Get a worksheet
Worksheet sheet = workbook.Worksheets["Sheet2"];
//Name the sheet
sheet.Name = "Catalog";
//Get the worksheet cells
Aspose.Cells.Cells cells = sheet.Cells;
int currentRow = 55;
//Add LightGray color to color palette
workbook.ChangePalette(Color.LightGray, 55);
//Get the workbook's styles collection
StyleCollection styles = workbook.Styles;
//Set CategoryName style with formatting attributes
int styleIndex = styles.Add();
Style styleCategoryName = styles[styleIndex];
styleCategoryName.Font.Size = 14;
styleCategoryName.Font.Color = Color.Blue;
styleCategoryName.Font.IsBold = true;
styleCategoryName.Font.Name = "Times New Roman";
//Set Description style with formatting attributes
styleIndex = styles.Add();
Style styleDescription = styles[styleIndex];
styleDescription.Font.Name = "Times New Roman";
styleDescription.Font.Color = Color.Blue;
styleDescription.Font.IsItalic = true;
//Set ProductName style with formatting attributes
styleIndex = styles.Add();
Style styleProductName = styles[styleIndex];
styleProductName.Font.IsBold = true;
//Set Title style with formatting attributes
styleIndex = styles.Add();
Style styleTitle = styles[styleIndex];
styleTitle.Font.IsBold = true;
styleTitle.Font.IsItalic = true;
styleTitle.ForegroundColor = Color.LightGray;
styleIndex = styles.Add();
Style styleNumber = styles[styleIndex];
styleNumber.Font.Name = "Times New Roman";
styleNumber.Number = 8;
//Create the styleflag struct
StyleFlag styleflag = new StyleFlag();
styleflag.All = true;
//Get the horizontal page breaks collection
HorizontalPageBreakCollection hPageBreaks = sheet.HorizontalPageBreaks;
DataTable dataTable1 = ConvertCategoriesToDataTable(CategoryList);
for (int i = 0; i < dataTable1.Rows.Count; i++)
{
currentRow += 2;
cells.SetRowHeight(currentRow, 20);
cells[currentRow, 1].SetStyle(styleCategoryName);
DataRow categoriesRow = dataTable1.Rows[i];
//Write CategoryName
cells[currentRow, 1].PutValue((string)categoriesRow["CategoryName"]);
//Write Description
currentRow++;
cells[currentRow, 1].PutValue((string)categoriesRow["Description"]);
cells[currentRow, 1].SetStyle(styleDescription);
dataTable2.Clear();
dataTable2 = GetProductsByCateGoryID(Convert.ToInt32(categoriesRow["CategoryID"].ToString()));
currentRow += 2;
//Import the datatable to the sheet
cells.ImportDataTable(dataTable2, true, currentRow, 1);
//Create a range
Range range = cells.CreateRange(currentRow, 1, 1, 4);
//Apply style to the range
range.ApplyStyle(styleTitle, styleflag);
//Create a range
range = cells.CreateRange(currentRow + 1, 1, dataTable2.Rows.Count, 1);
//Apply style to the range
range.ApplyStyle(styleProductName, styleflag);
//Create a range
range = cells.CreateRange(currentRow + 1, 4, dataTable2.Rows.Count, 1);
//Apply style to the range
range.ApplyStyle(styleNumber, styleflag);
currentRow += dataTable2.Rows.Count;
//Apply horizontal page breaks
hPageBreaks.Add(currentRow, 0);
}
//Remove the unnecessary worksheets in the workbook
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
sheet = workbook.Worksheets[i];
if (sheet.Name != "Catalog")
{
workbook.Worksheets.RemoveAt(i);
i--;
}
}
//Return the generated workbook
return(workbook);
}
private void GenerateChartSheet(Workbook workbook, DataSet ds)
{
//Generate the second chart sheet
int sheetIndex = workbook.Worksheets.Add(SheetType.Chart);
Worksheet sheet2 = workbook.Worksheets[sheetIndex];
//Name the sheet
sheet2.Name = "Pareto Chart";
//Set chart index
int chartIndex = sheet2.Charts.Add(ChartType.Column, 0, 0, 0, 0);
//Get chart type
Chart chart = sheet2.Charts[chartIndex];
//Set chart title text
chart.Title.Text = "Cost Center";
//Set chart title font
chart.Title.TextFont.IsBold = true;
chart.Title.TextFont.Size = 16;
//Set series
string series = "Cost Data!B2:B" + (ds.Tables[0].Rows.Count + 1);
//Series add in chart
chart.NSeries.Add(series, true);
//Set series name
chart.NSeries[0].Name = "Annual Cost";
//Set category
chart.NSeries.CategoryData = "Cost Data!A2:A" + (ds.Tables[0].Rows.Count + 1);
//Legend not shown
chart.ShowLegend = false;
//Set chart style
workbook.ChangePalette(Color.FromArgb(255, 255, 200), 53);
//Set plot area foreground color
chart.PlotArea.Area.ForegroundColor = Color.FromArgb(255, 255, 200);
//Set major grid line color
workbook.ChangePalette(Color.FromArgb(121, 117, 200), 54);
chart.CategoryAxis.MajorGridLines.Color = Color.FromArgb(121, 117, 200);
//Set series each point color
for (int i = 0; i < chart.NSeries[0].Points.Count; i++)
{
workbook.ChangePalette(Color.FromArgb(10, 100, 180), 55);
chart.NSeries[0].Points[i].Area.ForegroundColor = Color.FromArgb(10, 100, 180);
workbook.ChangePalette(Color.FromArgb(255, 255, 200), 53);
chart.NSeries[0].Points[i].Border.Color = Color.FromArgb(255, 255, 200);
}
}
private void GenerateDataSheet(Workbook workbook, DataSet ds)
{
//Write data to first data sheet
Worksheet sheet1 = workbook.Worksheets[0];
//Name the sheet
sheet1.Name = "Cost Data";
//Write sheet1 cells data to cells object
Cells cells = sheet1.Cells;
//Import data into cells
cells.ImportDataTable(ds.Tables[0], true, 0, 0, ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count);
//Set header style with specific formatting attributes
StyleCollection styles = workbook.Styles;
//Set style index
int styleIndex = styles.Add();
//Set style attribute using style index
Style style = styles[styleIndex];
//Set font size
style.Font.Size = 10;
//Set font color to white
style.Font.Color = Color.White;
//Set font to bold
style.Font.IsBold = true;
//Set font name to Verdana
style.Font.Name = "Verdana";
//Locked style
style.IsLocked = true;
//Set vertical alignment
style.VerticalAlignment = TextAlignmentType.Center;
//Set horizontal alignment
style.HorizontalAlignment = TextAlignmentType.Left;
//Set indent level
style.IndentLevel = 1;
//Set top, bottom, left and right borders style
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
//Change the palette for the spreadsheet in the specified index
workbook.ChangePalette(Color.FromArgb(10, 100, 180), 50);
//Change foreground color
style.ForegroundColor = Color.FromArgb(10, 100, 180);
//Set background style pattern
style.Pattern = BackgroundType.Solid;
//Set first two column's widths and set the height of the first row
cells.SetColumnWidth(0, 25);
cells.SetColumnWidth(1, 18);
cells.SetRowHeight(0, 30);
//Apply the style to A1 cell
cells[0, 0].SetStyle(style);
//Add a new style
styleIndex = styles.Add();
Style style1 = styles[styleIndex];
//Copy above created style to it
style1.Copy(style);
//Set horizontal alignment and indentation
style1.HorizontalAlignment = TextAlignmentType.Right;
style1.IndentLevel = 0;
//Apply the style to B1 cell
cells[0, 1].SetStyle(style1);
//Set current row to 1
int currentRow = 1;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//Set row height and color
cells.SetRowHeight(currentRow, 20);
Color color = Color.FromArgb(255, 255, 255);
//Change palette color of workbook
workbook.ChangePalette(color, 51);
//Change color of even number rows
if (currentRow % 2 == 0)
{
//Set color
color = Color.FromArgb(250, 250, 200);
//Change palette color of workbook
workbook.ChangePalette(color, 52);
}
//Set style for the first column cells
styleIndex = styles.Add();
//Set style attribute using style index
Style styleCell1 = styles[styleIndex];
//Set font size
styleCell1.Font.Size = 10;
//Set font name
styleCell1.Font.Name = "Arial";
//Set horizontal alignment
styleCell1.HorizontalAlignment = TextAlignmentType.Left;
//Set vertical alignment
styleCell1.VerticalAlignment = TextAlignmentType.Center;
//Set indenting level
styleCell1.IndentLevel = 1;
//Set top, bottom, left and right borders style
styleCell1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleCell1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Dashed;
styleCell1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.None;
styleCell1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.None;
//Check for last row
if (currentRow == ds.Tables[0].Rows.Count)
{
//Set bottom border style of last row
styleCell1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
}
//Set foreground color
styleCell1.ForegroundColor = color;
//Set background pattern style
styleCell1.Pattern = BackgroundType.Solid;
//Apply style to current row in first column
cells[currentRow, 0].SetStyle(styleCell1);
//Set style for the second column cells
styleIndex = styles.Add();
//Set style attribute using style index
Style styleCell2 = styles[styleIndex];
//Copy previous style in new attribute
styleCell2.Copy(styleCell1);
//Set left and right border style
styleCell2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.None;
styleCell2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
//Set horizontal text alignment
styleCell2.HorizontalAlignment = TextAlignmentType.Right;
//Set indent level
styleCell2.IndentLevel = 0;
//Set number format
styleCell2.Custom = "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)";
//Apply style to current row in second column
cells[currentRow, 1].SetStyle(styleCell2);
//Add 1 in current row count
currentRow++;
}
}
public Workbook CreateWorkBook()
{
var book = new Workbook();
book.Worksheets.Clear();
var borderColor = Color.FromArgb(79, 129, 189);
var textColor = Color.FromArgb(0, 0, 0);
var headerBackgroundColor = Color.FromArgb(79, 129, 189);
var headerTextColor = Color.FromArgb(255, 255, 255);
var oddBackgroundColor = Color.FromArgb(220, 230, 241);
var evenBackgroundColor = Color.FromArgb(255, 255, 255);
book.ChangePalette(borderColor, 50);
book.ChangePalette(textColor, 51);
book.ChangePalette(headerBackgroundColor, 52);
book.ChangePalette(headerTextColor, 53);
book.ChangePalette(oddBackgroundColor, 54);
book.ChangePalette(evenBackgroundColor, 55);
book.DefaultStyle.Font.Name = "Tahoma";
book.DefaultStyle.Font.Size = 10;
book.DefaultStyle.Font.Color = textColor;
ApplyBorder(book.DefaultStyle, borderColor, CellBorderType.Thin);
var style = book.Styles[book.Styles.Add()];
style.Name = "HeaderStyle";
style.Font.IsBold = true;
style.Pattern = BackgroundType.Solid;
style.Font.Color = headerTextColor;
style.ForegroundColor = headerBackgroundColor;
style.Pattern = BackgroundType.Solid;
style.HorizontalAlignment = TextAlignmentType.Center;
ApplyBorder(style, borderColor, CellBorderType.Thin);
for (var i = 0; i < Columns.Length; i++)
{
style = book.Styles[book.Styles.Add()];
OddStyles[i] = style;
style.Name = "OddStyle" + Columns[i];
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = oddBackgroundColor;
style.Pattern = BackgroundType.Solid;
style.HorizontalAlignment = ApplyAlign(Fields[i].TextAlign, Fields[i].FieldType);
ApplyBorder(style, borderColor, CellBorderType.Thin);
ApplyFormat(style, Fields[i], ref ListSources[i]);
}
for (var i = 0; i < Columns.Length; i++)
{
style = book.Styles[book.Styles.Add()];
EvenStyles[i] = style;
style.Name = "EvenStyle" + Columns[i];
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = evenBackgroundColor;
style.Pattern = BackgroundType.Solid;
style.HorizontalAlignment = ApplyAlign(Fields[i].TextAlign, Fields[i].FieldType);
ApplyBorder(style, borderColor, CellBorderType.Thin);
ApplyFormat(style, Fields[i], ref ListSources[i]);
}
return(book);
}
private void SetInvoiceStyles(Workbook workbook)
{
//Add LightBlue and DarkBlue colors to color palette
workbook.ChangePalette(Color.LightBlue, 54);
workbook.ChangePalette(Color.DarkBlue, 55);
//Create a style with specific formatting attributes
Style style;
int styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 12;
style.Font.IsBold = true;
style.Font.Color = Color.White;
style.ForegroundColor = Color.LightBlue;
style.Pattern = BackgroundType.Solid;
style.HorizontalAlignment = TextAlignmentType.Center;
style.Name = "Font12Center";
//Create a style with specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 12;
style.Font.IsBold = true;
style.Font.Color = Color.White;
style.ForegroundColor = Color.LightBlue;
style.Pattern = BackgroundType.Solid;
style.HorizontalAlignment = TextAlignmentType.Left;
style.Name = "Font12Left";
//Create a style with specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 12;
style.Font.IsBold = true;
style.Font.Color = Color.White;
style.ForegroundColor = Color.LightBlue;
style.Pattern = BackgroundType.Solid;
style.HorizontalAlignment = TextAlignmentType.Right;
style.Name = "Font12Right";
//Create a style with specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Number = 7;
style.Name = "Number7";
//Create a style with specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Number = 9;
style.Name = "Number9";
//Create a style with specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.HorizontalAlignment = TextAlignmentType.Center;
style.Name = "Center";
//Create a style with specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 16;
style.Font.IsBold = true;
style.Font.Color = Color.DarkBlue;
style.Name = "Darkblue";
//Create a style with specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 12;
style.Font.IsBold = true;
style.Font.Color = Color.DarkBlue;
style.Name = "Darkblue12";
//Create a style with specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.IsItalic = true;
style.Font.Color = Color.DarkBlue;
style.Name = "DarkblueItalic";
//Create a style with specific formatting attributes
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Borders[BorderType.BottomBorder].Color = Color.Black;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
style.Name = "BlackMedium";
}
