Creating a report is a necessity in almost everywhere now a days as report is the formatted result of database queries and contains useful data for decision-making and analysis.
We can use dummy data to populate a table and show report of that data in word
After clicking on the Generate button, the library will create a word file with the above choosen version, generated document contains table with columns describing the information about it in rows.
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Text;
using OfficeComponent.Word;
using System.Data;
namespace OfficeComponent.Samples
{
class NorthwindReportExample : WordExampleBase
{
public NorthwindReportExample(string commonDataPath, string outputDir)
: base(commonDataPath, outputDir)
{
}
public NorthwindReportExample(string commonDataPath, string outputDir, string xmlFile) : base(commonDataPath, outputDir, xmlFile)
{
}
public override string Execute()
{
// Retrieve the customer list
DataTable table = GetNorthwindDataSet().Tables[0];
// Creating a new document.
WordDocument document = new WordDocument();
// Adding a new section to the document.
Section section = document.AddSection();
section.PageSetup.PageSize = new SizeF(695, 842);
Paragraph paragraph = section.AddParagraph();
paragraph.ParagraphFormat.BeforeSpacing = 20f;
//Format the heading.
TextRange text = paragraph.AppendText("Northwind Report");
text.CharacterFormat.Bold = true;
text.CharacterFormat.FontName = "Arial";
text.CharacterFormat.FontSize = 14.0f;
text.CharacterFormat.TextColor = Color.Black;
paragraph.ParagraphFormat.HorizontalAlignment = OfficeComponent.Word.HorizontalAlignment.Center;
paragraph = section.AddParagraph();
paragraph.ParagraphFormat.BeforeSpacing = 18f;
//Create a new table
TextBody textBody = section.Body;
Table docTable = textBody.AddTable();
//Set the format for rows
RowFormat format = new RowFormat();
format.Borders.BorderLineStyle = OfficeComponent.Word.BorderStyle.Single;
format.Borders.LineWidth = 1.0F;
format.CellSpacing = 0;
format.Borders.Color = Color.Black;
//Initialize number of rows and cloumns.
docTable.ResetCells(table.Rows.Count + 1, table.Columns.Count, format, 84);
//Repeat the header.
docTable.Rows[0].IsHeader = true;
string colName;
//Format the header rows
for (int c = 0; c <= table.Columns.Count - 1; c++)
{
string[] Cols = table.Columns[c].ColumnName.Split('|');
colName = Cols[Cols.Length - 1];
TextRange theadertext = docTable.Rows[0].Cells[c].AddParagraph().AppendText(colName);
theadertext.CharacterFormat.FontSize = 12f;
theadertext.CharacterFormat.FontName = "Arial";
theadertext.CharacterFormat.Bold = true;
theadertext.CharacterFormat.TextColor = Color.White;
docTable.Rows[0].Cells[c].CellFormat.BackColor = Color.FromArgb(49, 146, 255);
docTable.Rows[0].Cells[c].CellFormat.Borders.Color = Color.Black;
docTable.Rows[0].Cells[c].CellFormat.Borders.BorderLineStyle = OfficeComponent.Word.BorderStyle.Single;
docTable.Rows[0].Cells[c].CellFormat.Borders.LineWidth = 1.0f;
docTable.Rows[0].Cells[c].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
}
//Format the table body rows
for (int r = 0; r <= table.Rows.Count - 1; r++)
{
for (int c = 0; c <= table.Columns.Count - 1; c++)
{
string Value = table.Rows[r][c].ToString();
TextRange theadertext = docTable.Rows[r + 1].Cells[c].AddParagraph().AppendText(Value);
theadertext.CharacterFormat.FontSize = 10;
theadertext.CharacterFormat.FontName = "Arial";
docTable.Rows[r + 1].Cells[c].CellFormat.BackColor = ((r & 1) == 0) ? Color.FromArgb(225, 225, 225) : Color.FromArgb(255, 255, 255);
docTable.Rows[r + 1].Cells[c].CellFormat.Borders.Color = Color.Black;
docTable.Rows[r + 1].Cells[c].CellFormat.Borders.BorderLineStyle = OfficeComponent.Word.BorderStyle.Single;
docTable.Rows[r + 1].Cells[c].CellFormat.Borders.LineWidth = 0.5f;
docTable.Rows[r + 1].Cells[c].CellFormat.VerticalAlignment = VerticalAlignment.Middle;
}
}
// Add a footer paragraph text to the document.
Paragraph footerPar = new Paragraph(document);
// Add text.
footerPar.AppendText("Copyright OfficeComponent. 2007 - " + DateTime.Now.Year);
// Add page and Number of pages field to the document.
footerPar.AppendText(" Page ");
footerPar.AppendField("Page", FieldType.FieldPage);
section.HeadersFooters.Footer.Paragraphs.Add(footerPar);
string fileName = Path.Combine(OutputDir, this.GetType().Name + "_" + Guid.NewGuid().ToString() + GetExtension(SaveAsFormat));
// Save the document.
document.Save(fileName, SaveAsFormat);
// Close the document.
document.Close();
return fileName;
}
#region Customers
Random random = new Random(Environment.TickCount);
private string GetRandomAddress()
{
string[] list = new string[]
{
"Avda. de la Constitucion 2222",
"Mataderos 2312",
"120 Hanover Sq.",
"Berguvsvagen 8",
"Forsterstr. 57",
"24, place Klober",
"C/ Araquil, 67",
"12, rue des Bouchers",
"23 Tsawassen Blvd.",
"Fauntleroy Circus",
};
return list[random.Next(list.Length)];
}
private string GetRandomName()
{
string[] list = new string[]
{
"Maria Anders",
"Ana Trujillo",
"Antonio Moreno",
"Thomas Hardy",
"Christina Berglund",
"Hanna Moos",
"Frederique Citeaux",
"Martin Sommer",
"Laurence Lebihan",
"Elizabeth Lincoln",
"Victoria Ashworth",
"Francisco Chang",
"Yang Wang",
};
return list[random.Next(list.Length)];
}
private string GetRandomCompanyName()
{
string[] list = new string[]
{
"Alfreds Futterkiste",
"Around the Horn",
"Berglunds snabbkop",
"Blauer See Delikatessen",
"Bon app'",
"Bottom-Dollar Markets",
"B's Beverages",
"Consolidated Holdings",
"Ernst Handel",
"Folies gourmandes",
"Galerea del gastranomo",
"Hanari Carnes",
"Island Trading",
};
return list[random.Next(list.Length)];
}
private string GetRandonCity()
{
string[] list = new string[]
{
"Cork",
"Cowes",
"Versailles",
"Vancouver",
"Walla Walla",
"Frankfurt a.M.",
"San Francisco",
"Barquisimeto",
"Portland",
"Bergamo",
"Bruxelles",
"Montreal",
"Leipzig",
"London",
};
return list[random.Next(list.Length)];
}
private string GetRandomCountry()
{
string[] list = new string[]
{
"Germany",
"Venezuela",
"USA",
"Canada",
"Belgium",
"Italy",
"Argentina",
"UK",
"France",
"Mexico",
"Austria",
"Portugal",
"Brazil",
"Switzerland",
};
return list[random.Next(list.Length)];
}
private string GetRandomPostalCode()
{
string[] list = new string[]
{
"PO31 7PJ",
"14776",
"78000",
"V3F 2K1",
"99362",
"94117",
"3508",
"24100",
"B-1180",
"SW7 1RZ",
"99508",
"50739",
"05033",
"02389-673",
};
return list[random.Next(list.Length)];
}
///
/// Get fictitious customer list.
///
///
private DataSet GetNorthwindDataSet()
{
DataSet dataSet = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("CustomerID", typeof(int));
dt.Columns.Add("CompanyName", typeof(string));
dt.Columns.Add("ContactName", typeof(string));
dt.Columns.Add("Address", typeof(string));
dt.Columns.Add("City", typeof(string));
dt.Columns.Add("PostalCode", typeof(string));
dt.Columns.Add("Country", typeof(string));
for (int i = 0; i < 50; i++)
{
dt.Rows.Add(1000 + i, GetRandomCompanyName(), GetRandomName(), GetRandomAddress(), GetRandonCity(), GetRandomPostalCode(), GetRandomCountry());
}
dataSet.Tables.Add(dt);
return dataSet;
}
#endregion
}
}
Imports System.Drawing.Imaging
Imports System.IO
Imports System.Text
Imports OfficeComponent.Word
Namespace OfficeComponent.Samples
Friend Class NorthwindReportExample
Inherits WordExampleBase
Public Sub New(ByVal commonDataPath As String, ByVal outputDir As String)
MyBase.New(commonDataPath, outputDir)
End Sub
Public Sub New(ByVal commonDataPath As String, ByVal outputDir As String, ByVal xmlFile As String)
MyBase.New(commonDataPath, outputDir, xmlFile)
End Sub
Public Overrides Function Execute() As String
' Retrieve the customer list
Dim table As DataTable = GetNorthwindDataSet().Tables(0)
' Creating a new document.
Dim document As New WordDocument()
' Adding a new section to the document.
Dim section As Section = document.AddSection()
section.PageSetup.PageSize = New SizeF(695, 842)
Dim paragraph As Paragraph = section.AddParagraph()
paragraph.ParagraphFormat.BeforeSpacing = 20F
'Format the heading.
Dim text As TextRange = paragraph.AppendText("Northwind Report")
text.CharacterFormat.Bold = True
text.CharacterFormat.FontName = "Arial"
text.CharacterFormat.FontSize = 14.0F
text.CharacterFormat.TextColor = Color.Black
paragraph.ParagraphFormat.HorizontalAlignment = OfficeComponent.Word.HorizontalAlignment.Center
paragraph = section.AddParagraph()
paragraph.ParagraphFormat.BeforeSpacing = 18F
'Create a new table
Dim textBody As TextBody = section.Body
Dim docTable As Table = textBody.AddTable()
'Set the format for rows
Dim format As New RowFormat()
format.Borders.BorderLineStyle = OfficeComponent.Word.BorderStyle.Single
format.Borders.LineWidth = 1.0F
format.CellSpacing = 0
format.Borders.Color = Color.Black
'Initialize number of rows and cloumns.
docTable.ResetCells(table.Rows.Count + 1, table.Columns.Count, format, 84)
'Repeat the header.
docTable.Rows(0).IsHeader = True
Dim colName As String
'Format the header rows
For c As Integer = 0 To table.Columns.Count - 1
Dim Cols() As String = table.Columns(c).ColumnName.Split("|"c)
colName = Cols(Cols.Length - 1)
Dim theadertext As TextRange = docTable.Rows(0).Cells(c).AddParagraph().AppendText(colName)
theadertext.CharacterFormat.FontSize = 12F
theadertext.CharacterFormat.FontName = "Arial"
theadertext.CharacterFormat.Bold = True
theadertext.CharacterFormat.TextColor = Color.White
docTable.Rows(0).Cells(c).CellFormat.BackColor = Color.FromArgb(49, 146, 255)
docTable.Rows(0).Cells(c).CellFormat.Borders.Color = Color.Black
docTable.Rows(0).Cells(c).CellFormat.Borders.BorderLineStyle = OfficeComponent.Word.BorderStyle.Single
docTable.Rows(0).Cells(c).CellFormat.Borders.LineWidth = 1.0F
docTable.Rows(0).Cells(c).CellFormat.VerticalAlignment = VerticalAlignment.Middle
Next c
'Format the table body rows
For r As Integer = 0 To table.Rows.Count - 1
For c As Integer = 0 To table.Columns.Count - 1
Dim Value As String = table.Rows(r)(c).ToString()
Dim theadertext As TextRange = docTable.Rows(r + 1).Cells(c).AddParagraph().AppendText(Value)
theadertext.CharacterFormat.FontSize = 10
theadertext.CharacterFormat.FontName = "Arial"
If (r And 1) = 0 Then
docTable.Rows(r + 1).Cells(c).CellFormat.BackColor = Color.FromArgb(225, 225, 225)
Else
docTable.Rows(r + 1).Cells(c).CellFormat.BackColor = Color.FromArgb(255, 255, 255)
End If
docTable.Rows(r + 1).Cells(c).CellFormat.Borders.Color = Color.Black
docTable.Rows(r + 1).Cells(c).CellFormat.Borders.BorderLineStyle = OfficeComponent.Word.BorderStyle.Single
docTable.Rows(r + 1).Cells(c).CellFormat.Borders.LineWidth = 0.5F
docTable.Rows(r + 1).Cells(c).CellFormat.VerticalAlignment = VerticalAlignment.Middle
Next c
Next r
' Add a footer paragraph text to the document.
Dim footerPar As New Paragraph(document)
' Add text.
footerPar.AppendText("Copyright OfficeComponent. 2007 - " & Date.Now.Year)
' Add page and Number of pages field to the document.
footerPar.AppendText(" Page ")
footerPar.AppendField("Page", FieldType.FieldPage)
section.HeadersFooters.Footer.Paragraphs.Add(footerPar)
Dim fileName As String = Path.Combine(OutputDir, Me.GetType().Name & "_" & Guid.NewGuid().ToString() & GetExtension(SaveAsFormat))
' Save the document.
document.Save(fileName, SaveAsFormat)
' Close the document.
document.Close()
Return fileName
End Function
#Region "Customers"
Private random As New Random(Environment.TickCount)
Private Function GetRandomAddress() As String
Dim list() As String = { "Avda. de la Constitucion 2222", "Mataderos 2312", "120 Hanover Sq.", "Berguvsvagen 8", "Forsterstr. 57", "24, place Klober", "C/ Araquil, 67", "12, rue des Bouchers", "23 Tsawassen Blvd.", "Fauntleroy Circus" }
Return list(random.Next(list.Length))
End Function
Private Function GetRandomName() As String
Dim list() As String = { "Maria Anders", "Ana Trujillo", "Antonio Moreno", "Thomas Hardy", "Christina Berglund", "Hanna Moos", "Frederique Citeaux", "Martin Sommer", "Laurence Lebihan", "Elizabeth Lincoln", "Victoria Ashworth", "Francisco Chang", "Yang Wang" }
Return list(random.Next(list.Length))
End Function
Private Function GetRandomCompanyName() As String
Dim list() As String = { "Alfreds Futterkiste", "Around the Horn", "Berglunds snabbkop", "Blauer See Delikatessen", "Bon app'", "Bottom-Dollar Markets", "B's Beverages", "Consolidated Holdings", "Ernst Handel", "Folies gourmandes", "Galerea del gastranomo", "Hanari Carnes", "Island Trading" }
Return list(random.Next(list.Length))
End Function
Private Function GetRandonCity() As String
Dim list() As String = { "Cork", "Cowes", "Versailles", "Vancouver", "Walla Walla", "Frankfurt a.M.", "San Francisco", "Barquisimeto", "Portland", "Bergamo", "Bruxelles", "Montreal", "Leipzig", "London" }
Return list(random.Next(list.Length))
End Function
Private Function GetRandomCountry() As String
Dim list() As String = { "Germany", "Venezuela", "USA", "Canada", "Belgium", "Italy", "Argentina", "UK", "France", "Mexico", "Austria", "Portugal", "Brazil", "Switzerland" }
Return list(random.Next(list.Length))
End Function
Private Function GetRandomPostalCode() As String
Dim list() As String = { "PO31 7PJ", "14776", "78000", "V3F 2K1", "99362", "94117", "3508", "24100", "B-1180", "SW7 1RZ", "99508", "50739", "05033", "02389-673" }
Return list(random.Next(list.Length))
End Function
'''
''' Get fictitious customer list.
'''
'''
Private Function GetNorthwindDataSet() As DataSet
Dim dataSet As New DataSet()
Dim dt As New DataTable()
dt.Columns.Add("CustomerID", GetType(Integer))
dt.Columns.Add("CompanyName", GetType(String))
dt.Columns.Add("ContactName", GetType(String))
dt.Columns.Add("Address", GetType(String))
dt.Columns.Add("City", GetType(String))
dt.Columns.Add("PostalCode", GetType(String))
dt.Columns.Add("Country", GetType(String))
For i As Integer = 0 To 49
dt.Rows.Add(1000 + i, GetRandomCompanyName(), GetRandomName(), GetRandomAddress(), GetRandonCity(), GetRandomPostalCode(), GetRandomCountry())
Next i
dataSet.Tables.Add(dt)
Return dataSet
End Function
#End Region
End Class
End Namespace