Format Worksheet Cells in a Workbook

Data Formatting

Formatting is used to distinguish between different types of information and to display data clearly.

A format represents a style and is defined as a set of characteristics, such as fonts and font sizes, number formats, cell borders, cell shading, indentation, alignment and text orientation. Borders provide further ways to highlight information. A border is a line drawn around a cell or a group of cells.

Number formats also make data more meaningful. By applying different number formats, you can change the appearance of numbers without changing the number behind the appearance.

Aspose.Cells for Python via .NET provides lets you draw borders around cells and ranges easily. It also lets you apply fonts and shade cells. The component is efficient enough that you can format a complete row or column, set alignments, wrap and rotate text in cells. Aspose.Cells for Python via .NET further supports all number formats supported by Microsoft Excel.

This article shows how to create a console application in Visual Studio.Net that generates an annual sales report. The workbook is created from scratch, then data is inserted and the worksheet is formatted. We show how to create a simple console application which creates an Excel workbook (you can also use a template file), insert sales data into the first worksheet, format the data and save an Excel file.

Process

Below are the steps involved how to create a spreadsheet and format different cells in different rows and columns of a worksheet.

  1. Download and install Aspose.Cells.
  2. Add the following code to the project folder.
from aspose.cells import BackgroundType, BorderType, CellBorderType, StyleFlag, TextAlignmentType, Workbook
from aspose.pydrawing import Color
# Create a new Workbook.
workbook = 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.change_palette(Color.from_argb(155, 204, 255), 55)
workbook.change_palette(Color.from_argb(0, 51, 105), 54)
workbook.change_palette(Color.from_argb(250, 250, 200), 53)
workbook.change_palette(Color.from_argb(124, 199, 72), 52)
# Obtain the cells of the first worksheet.
cells = workbook.worksheets[0].cells
# Input the title on B1 cell.
cells.get("B1").put_value("Western Product Sales 2006")
# Insert some column headings in the second row.
cell = cells.get("B2")
cell.put_value("January")
cell = cells.get("C2")
cell.put_value("February")
cell = cells.get("D2")
cell.put_value("March")
cell = cells.get("E2")
cell.put_value("April")
cell = cells.get("F2")
cell.put_value("May")
cell = cells.get("G2")
cell.put_value("June")
cell = cells.get("H2")
cell.put_value("July")
cell = cells.get("I2")
cell.put_value("August")
cell = cells.get("J2")
cell.put_value("September")
cell = cells.get("K2")
cell.put_value("October")
cell = cells.get("L2")
cell.put_value("November")
cell = cells.get("M2")
cell.put_value("December")
cell = cells.get("N2")
cell.put_value("Total")
# Insert product names.
cells.get("A3").put_value("Biscuits")
cells.get("A4").put_value("Coffee")
cells.get("A5").put_value("Tofu")
cells.get("A6").put_value("Ikura")
cells.get("A7").put_value("Choclade")
cells.get("A8").put_value("Maxilaku")
cells.get("A9").put_value("Scones")
cells.get("A10").put_value("Sauce")
cells.get("A11").put_value("Syrup")
cells.get("A12").put_value("Spegesild")
cells.get("A13").put_value("Filo Mix")
cells.get("A14").put_value("Pears")
cells.get("A15").put_value("Konbu")
cells.get("A16").put_value("Kaviar")
cells.get("A17").put_value("Zaanse")
cells.get("A18").put_value("Cabrales")
cells.get("A19").put_value("Gnocchi")
cells.get("A20").put_value("Wimmers")
cells.get("A21").put_value("Breads")
cells.get("A22").put_value("Lager")
cells.get("A23").put_value("Gravad")
cells.get("A24").put_value("Telino")
cells.get("A25").put_value("Pavlova")
cells.get("A26").put_value("Total")
# Input porduct sales data (B3:M25).
cells.get("B3").put_value(5000)
cells.get("C3").put_value(4500)
cells.get("D3").put_value(6010)
cells.get("E3").put_value(7230)
cells.get("F3").put_value(5400)
cells.get("G3").put_value(5030)
cells.get("H3").put_value(3000)
cells.get("I3").put_value(6000)
cells.get("J3").put_value(9000)
cells.get("K3").put_value(3300)
cells.get("L3").put_value(2500)
cells.get("M3").put_value(5510)
cells.get("B4").put_value(4000)
cells.get("C4").put_value(2500)
cells.get("D4").put_value(6000)
cells.get("E4").put_value(5300)
cells.get("F4").put_value(7400)
cells.get("G4").put_value(7030)
cells.get("H4").put_value(4000)
cells.get("I4").put_value(4000)
cells.get("J4").put_value(5500)
cells.get("K4").put_value(4500)
cells.get("L4").put_value(2500)
cells.get("M4").put_value(2510)
cells.get("B5").put_value(2000)
cells.get("C5").put_value(1500)
cells.get("D5").put_value(3000)
cells.get("E5").put_value(2500)
cells.get("F5").put_value(3400)
cells.get("G5").put_value(4030)
cells.get("H5").put_value(2000)
cells.get("I5").put_value(2000)
cells.get("J5").put_value(1500)
cells.get("K5").put_value(2200)
cells.get("L5").put_value(2100)
cells.get("M5").put_value(2310)
cells.get("B6").put_value(1000)
cells.get("C6").put_value(1300)
cells.get("D6").put_value(2000)
cells.get("E6").put_value(2600)
cells.get("F6").put_value(5400)
cells.get("G6").put_value(2030)
cells.get("H6").put_value(2100)
cells.get("I6").put_value(4000)
cells.get("J6").put_value(6500)
cells.get("K6").put_value(5600)
cells.get("L6").put_value(3300)
cells.get("M6").put_value(5110)
cells.get("B7").put_value(3000)
cells.get("C7").put_value(3500)
cells.get("D7").put_value(1000)
cells.get("E7").put_value(4500)
cells.get("F7").put_value(5400)
cells.get("G7").put_value(2030)
cells.get("H7").put_value(3000)
cells.get("I7").put_value(3000)
cells.get("J7").put_value(4500)
cells.get("K7").put_value(6000)
cells.get("L7").put_value(3000)
cells.get("M7").put_value(3000)
cells.get("B8").put_value(5000)
cells.get("C8").put_value(5500)
cells.get("D8").put_value(5000)
cells.get("E8").put_value(5500)
cells.get("F8").put_value(5400)
cells.get("G8").put_value(5030)
cells.get("H8").put_value(5000)
cells.get("I8").put_value(2500)
cells.get("J8").put_value(5500)
cells.get("K8").put_value(5200)
cells.get("L8").put_value(5500)
cells.get("M8").put_value(2510)
cells.get("B9").put_value(4100)
cells.get("C9").put_value(1500)
cells.get("D9").put_value(1000)
cells.get("E9").put_value(2300)
cells.get("F9").put_value(3300)
cells.get("G9").put_value(4030)
cells.get("H9").put_value(5000)
cells.get("I9").put_value(6000)
cells.get("J9").put_value(3500)
cells.get("K9").put_value(4300)
cells.get("L9").put_value(2300)
cells.get("M9").put_value(2110)
cells.get("B10").put_value(2000)
cells.get("C10").put_value(2300)
cells.get("D10").put_value(3000)
cells.get("E10").put_value(3300)
cells.get("F10").put_value(3400)
cells.get("G10").put_value(3030)
cells.get("H10").put_value(3000)
cells.get("I10").put_value(3000)
cells.get("J10").put_value(3500)
cells.get("K10").put_value(3500)
cells.get("L10").put_value(3500)
cells.get("M10").put_value(3510)
cells.get("B11").put_value(4400)
cells.get("C11").put_value(4500)
cells.get("D11").put_value(4000)
cells.get("E11").put_value(4300)
cells.get("F11").put_value(4400)
cells.get("G11").put_value(4030)
cells.get("H11").put_value(5000)
cells.get("I11").put_value(5000)
cells.get("J11").put_value(4500)
cells.get("K11").put_value(4400)
cells.get("L11").put_value(4400)
cells.get("M11").put_value(4510)
cells.get("B12").put_value(2000)
cells.get("C12").put_value(1500)
cells.get("D12").put_value(3000)
cells.get("E12").put_value(2300)
cells.get("F12").put_value(3400)
cells.get("G12").put_value(3030)
cells.get("H12").put_value(3000)
cells.get("I12").put_value(3000)
cells.get("J12").put_value(2500)
cells.get("K12").put_value(2500)
cells.get("L12").put_value(1500)
cells.get("M12").put_value(5110)
cells.get("B13").put_value(4000)
cells.get("C13").put_value(1400)
cells.get("D13").put_value(1400)
cells.get("E13").put_value(3300)
cells.get("F13").put_value(3300)
cells.get("G13").put_value(3730)
cells.get("H13").put_value(3800)
cells.get("I13").put_value(3600)
cells.get("J13").put_value(2600)
cells.get("K13").put_value(4600)
cells.get("L13").put_value(1400)
cells.get("M13").put_value(2660)
cells.get("B14").put_value(3000)
cells.get("C14").put_value(3500)
cells.get("D14").put_value(3333)
cells.get("E14").put_value(2330)
cells.get("F14").put_value(3430)
cells.get("G14").put_value(3040)
cells.get("H14").put_value(3040)
cells.get("I14").put_value(3030)
cells.get("J14").put_value(1509)
cells.get("K14").put_value(4503)
cells.get("L14").put_value(1503)
cells.get("M14").put_value(3113)
cells.get("B15").put_value(2010)
cells.get("C15").put_value(1520)
cells.get("D15").put_value(3030)
cells.get("E15").put_value(2320)
cells.get("F15").put_value(3410)
cells.get("G15").put_value(3000)
cells.get("H15").put_value(3000)
cells.get("I15").put_value(3020)
cells.get("J15").put_value(2520)
cells.get("K15").put_value(2520)
cells.get("L15").put_value(1520)
cells.get("M15").put_value(5120)
cells.get("B16").put_value(2220)
cells.get("C16").put_value(1200)
cells.get("D16").put_value(3220)
cells.get("E16").put_value(1320)
cells.get("F16").put_value(1400)
cells.get("G16").put_value(1030)
cells.get("H16").put_value(3200)
cells.get("I16").put_value(3020)
cells.get("J16").put_value(2100)
cells.get("K16").put_value(2100)
cells.get("L16").put_value(1100)
cells.get("M16").put_value(5210)
cells.get("B17").put_value(1444)
cells.get("C17").put_value(1540)
cells.get("D17").put_value(3040)
cells.get("E17").put_value(2340)
cells.get("F17").put_value(1440)
cells.get("G17").put_value(1030)
cells.get("H17").put_value(3000)
cells.get("I17").put_value(4000)
cells.get("J17").put_value(4500)
cells.get("K17").put_value(2500)
cells.get("L17").put_value(4500)
cells.get("M17").put_value(5550)
cells.get("B18").put_value(4000)
cells.get("C18").put_value(5500)
cells.get("D18").put_value(3000)
cells.get("E18").put_value(3300)
cells.get("F18").put_value(3330)
cells.get("G18").put_value(5330)
cells.get("H18").put_value(3400)
cells.get("I18").put_value(3040)
cells.get("J18").put_value(2540)
cells.get("K18").put_value(4500)
cells.get("L18").put_value(4500)
cells.get("M18").put_value(2110)
cells.get("B19").put_value(2000)
cells.get("C19").put_value(2500)
cells.get("D19").put_value(3200)
cells.get("E19").put_value(3200)
cells.get("F19").put_value(2330)
cells.get("G19").put_value(5230)
cells.get("H19").put_value(2400)
cells.get("I19").put_value(3240)
cells.get("J19").put_value(2240)
cells.get("K19").put_value(4300)
cells.get("L19").put_value(4100)
cells.get("M19").put_value(2310)
cells.get("B20").put_value(7000)
cells.get("C20").put_value(8500)
cells.get("D20").put_value(8000)
cells.get("E20").put_value(5300)
cells.get("F20").put_value(6330)
cells.get("G20").put_value(7330)
cells.get("H20").put_value(3600)
cells.get("I20").put_value(3940)
cells.get("J20").put_value(2940)
cells.get("K20").put_value(4600)
cells.get("L20").put_value(6500)
cells.get("M20").put_value(8710)
cells.get("B21").put_value(4000)
cells.get("C21").put_value(4500)
cells.get("D21").put_value(2000)
cells.get("E21").put_value(2200)
cells.get("F21").put_value(2000)
cells.get("G21").put_value(3000)
cells.get("H21").put_value(3000)
cells.get("I21").put_value(3000)
cells.get("J21").put_value(4330)
cells.get("K21").put_value(4420)
cells.get("L21").put_value(4500)
cells.get("M21").put_value(1330)
cells.get("B22").put_value(2050)
cells.get("C22").put_value(3520)
cells.get("D22").put_value(1030)
cells.get("E22").put_value(2000)
cells.get("F22").put_value(3000)
cells.get("G22").put_value(2000)
cells.get("H22").put_value(2010)
cells.get("I22").put_value(2210)
cells.get("J22").put_value(2230)
cells.get("K22").put_value(4240)
cells.get("L22").put_value(3330)
cells.get("M22").put_value(2000)
cells.get("B23").put_value(1222)
cells.get("C23").put_value(3000)
cells.get("D23").put_value(3020)
cells.get("E23").put_value(2770)
cells.get("F23").put_value(3011)
cells.get("G23").put_value(2000)
cells.get("H23").put_value(6000)
cells.get("I23").put_value(9000)
cells.get("J23").put_value(4000)
cells.get("K23").put_value(2000)
cells.get("L23").put_value(5000)
cells.get("M23").put_value(6333)
cells.get("B24").put_value(1000)
cells.get("C24").put_value(2000)
cells.get("D24").put_value(1000)
cells.get("E24").put_value(1300)
cells.get("F24").put_value(1330)
cells.get("G24").put_value(1390)
cells.get("H24").put_value(1600)
cells.get("I24").put_value(1900)
cells.get("J24").put_value(1400)
cells.get("K24").put_value(1650)
cells.get("L24").put_value(1520)
cells.get("M24").put_value(1910)
cells.get("B25").put_value(2000)
cells.get("C25").put_value(6600)
cells.get("D25").put_value(3300)
cells.get("E25").put_value(8300)
cells.get("F25").put_value(2000)
cells.get("G25").put_value(3000)
cells.get("H25").put_value(6000)
cells.get("I25").put_value(4000)
cells.get("J25").put_value(7000)
cells.get("K25").put_value(2000)
cells.get("L25").put_value(5000)
cells.get("M25").put_value(5500)
# Add Monthwise Summary formulas.
cells.get("B26").formula = "=SUM(B3:B25)"
cells.get("C26").formula = "=SUM(C3:C25)"
cells.get("D26").formula = "=SUM(D3:D25)"
cells.get("E26").formula = "=SUM(E3:E25)"
cells.get("F26").formula = "=SUM(F3:F25)"
cells.get("G26").formula = "=SUM(G3:G25)"
cells.get("H26").formula = "=SUM(H3:H25)"
cells.get("I26").formula = "=SUM(I3:I25)"
cells.get("J26").formula = "=SUM(J3:J25)"
cells.get("K26").formula = "=SUM(K3:K25)"
cells.get("L26").formula = "=SUM(L3:L25)"
cells.get("M26").formula = "=SUM(M3:M25)"
# Add Productwise Summary formulas.
cells.get("N3").formula = "=SUM(B3:M3)"
cells.get("N4").formula = "=SUM(B4:M4)"
cells.get("N5").formula = "=SUM(B5:M5)"
cells.get("N6").formula = "=SUM(B6:M6)"
cells.get("N7").formula = "=SUM(B7:M7)"
cells.get("N8").formula = "=SUM(B8:M8)"
cells.get("N9").formula = "=SUM(B9:M9)"
cells.get("N10").formula = "=SUM(B10:M10)"
cells.get("N11").formula = "=SUM(B11:M11)"
cells.get("N12").formula = "=SUM(B12:M12)"
cells.get("N13").formula = "=SUM(B13:M13)"
cells.get("N14").formula = "=SUM(B14:M14)"
cells.get("N15").formula = "=SUM(B15:M15)"
cells.get("N16").formula = "=SUM(B16:M16)"
cells.get("N17").formula = "=SUM(B17:M17)"
cells.get("N18").formula = "=SUM(B18:M18)"
cells.get("N19").formula = "=SUM(B19:M19)"
cells.get("N20").formula = "=SUM(B20:M20)"
cells.get("N21").formula = "=SUM(B21:M21)"
cells.get("N22").formula = "=SUM(B22:M22)"
cells.get("N23").formula = "=SUM(B23:M23)"
cells.get("N24").formula = "=SUM(B24:M24)"
cells.get("N25").formula = "=SUM(B25:M25)"
# Add Grand Total.
cells.get("N26").formula = "=SUM(N3:N25)"
# Define a style object adding a new style to the collection list.
stl0 = workbook.create_style()
# Set a custom shading color of the cells.
stl0.foreground_color = Color.from_argb(155, 204, 255)
stl0.pattern = BackgroundType.SOLID
stl0.font.name = "Trebuchet MS"
stl0.font.size = 18
stl0.font.color = Color.maroon
stl0.font.is_bold = True
stl0.font.is_italic = True
# Define a style flag struct.
flag = StyleFlag()
flag.cell_shading = True
flag.font_name = True
flag.font_size = True
flag.font_color = True
flag.font_bold = True
flag.font_italic = True
# Get the first row in the first worksheet.
row = workbook.worksheets[0].cells.rows[0]
# Apply the style to it.
row.apply_style(stl0, flag)
# Obtain the cells of the first worksheet.
cells = workbook.worksheets[0].cells
# Set the height of the first row.
cells.set_row_height(0, 30)
# Define a style object adding a new style to the collection list.
stl1 = workbook.create_style()
# Set the rotation angle of the text.
stl1.rotation_angle = 45
# Set the custom fill color of the cells.
stl1.foreground_color = Color.from_argb(0, 51, 105)
stl1.pattern = BackgroundType.SOLID
stl1.borders.get(BorderType.LEFT_BORDER).line_style = CellBorderType.THIN
stl1.borders.get(BorderType.LEFT_BORDER).color = Color.white
stl1.horizontal_alignment = TextAlignmentType.CENTER
stl1.vertical_alignment = TextAlignmentType.CENTER
stl1.font.name = "Times New Roman"
stl1.font.size = 10
stl1.font.color = Color.white
stl1.font.is_bold = True
# Set a style flag struct.
flag = StyleFlag()
flag.left_border = True
flag.rotation = True
flag.cell_shading = True
flag.horizontal_alignment = True
flag.vertical_alignment = True
flag.font_name = True
flag.font_size = True
flag.font_color = True
flag.font_bold = True
row = workbook.worksheets[0].cells.rows[1]
# Apply the style to it.
row.apply_style(stl1, flag)
# Set the height of the second row.
cells.set_row_height(1, 48)
# Define a style object adding a new style to the collection list.
stl2 = workbook.create_style()
# Set the custom cell shading color.
stl2.foreground_color = Color.from_argb(155, 204, 255)
stl2.pattern = BackgroundType.SOLID
stl2.font.name = "Trebuchet MS"
stl2.font.color = Color.maroon
stl2.font.size = 10
flag = StyleFlag()
flag.cell_shading = True
flag.font_name = True
flag.font_color = True
flag.font_size = True
# Get the first column in the first worksheet.
col = workbook.worksheets[0].cells.columns[0]
# Apply the style to it.
col.apply_style(stl2, flag)
# Define a style object adding a new style to the collection list.
stl3 = workbook.create_style()
# Set the custom cell filling color.
stl3.foreground_color = Color.from_argb(124, 199, 72)
stl3.pattern = BackgroundType.SOLID
cells.get("A2").set_style(stl3)
# Define a style object adding a new style to the collection list.
stl4 = workbook.create_style()
# Set the custom font text color.
stl4.font.color = Color.from_argb(0, 51, 105)
stl4.borders.get(BorderType.BOTTOM_BORDER).line_style = CellBorderType.THIN
stl4.borders.get(BorderType.BOTTOM_BORDER).color = Color.from_argb(124, 199, 72)
stl4.foreground_color = Color.white
stl4.pattern = BackgroundType.SOLID
# Set custom number format.
stl4.custom = "$#,##0.0"
# Set a style flag struct.
flag = StyleFlag()
flag.font_color = True
flag.cell_shading = True
flag.number_format = True
flag.bottom_border = True
# Define a style object adding a new style to the collection list.
stl5 = workbook.create_style()
stl5.borders.get(BorderType.BOTTOM_BORDER).line_style = CellBorderType.THIN
stl5.borders.get(BorderType.BOTTOM_BORDER).color = Color.from_argb(124, 199, 72)
stl5.foreground_color = Color.from_argb(250, 250, 200)
stl5.pattern = BackgroundType.SOLID
# Set custom number format.
stl5.custom = "$#,##0.0"
stl5.font.color = Color.maroon
# Create a named range of cells (B3:M25)in the first worksheet.
range = workbook.worksheets[0].cells.create_range("B3", "M25")
# Name the range.
range.name = "MyRange"
# Apply the style to cells in the named range.
range.apply_style(stl4, flag)
# Apply different style to alternative rows in the range.
for i in range(22 + 1):
for j in range(12):
if i % 2 == 0:
range.get(i, j).set_style(stl5)
# Define a style object adding a new style to the collection list.
stl6 = workbook.create_style()
# Set the custom fill color of the cells.
stl6.foreground_color = Color.from_argb(0, 51, 105)
stl6.pattern = BackgroundType.SOLID
stl6.font.name = "Arial"
stl6.font.size = 10
stl6.font.color = Color.white
stl6.font.is_bold = True
# Set the custom number format.
stl6.custom = "$#,##0.0"
# Set the style flag struct.
flag = StyleFlag()
flag.cell_shading = True
flag.font_name = True
flag.font_size = True
flag.font_color = True
flag.font_bold = True
flag.number_format = True
# Get the 26th row in the first worksheet which produces totals.
row = workbook.worksheets[0].cells.rows[25]
# Apply the style to it.
row.apply_style(stl6, flag)
# Now apply this style to those cells (N3:N25) which has productwise sales totals.
for i in range(2, 25):
cells.get(i, 13).set_style(stl6)
# Set N column's width to fit the contents.
workbook.worksheets[0].cells.set_column_width(13, 9.33)
# Get the first worksheet in the book.
worksheet = workbook.worksheets[0]
# Name the worksheet.
worksheet.name = "Sales Report"
dataDir = RunExamples.GetDataDir(".")
filename = dataDir + "FormatWorksheet.xls"
# Save the excel file.
workbook.save(filename)