Custom Data Merging

In this demo, a spreadsheet template is loaded into the Bootstrap Spreadsheet and programmatically populated with data taken from an XML file. The template displays the predefined report layout and contains specific aggregate formulas. The data is retrieved from an XML file and merged with the corresponding worksheet cells into an integrated report. As a result, a worksheet represents a complete report calculated automatically.

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
AL
AM
AN
AO
AP
AQ
AR
AS
AT
AU
AV
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
NAME:
DEPARTMENT:
Date
Account
Transport
Fuel
Phone
Entertainment
Misc.
Total
1/16/2013
212340
$ 130.00
$12.42
$ 8.00
$ -
$ 19.20
$ 469.62
2/17/2013
289043
$ -
$16.62
$ 6.80
$ -
$ 29.35
$ 347.77
1/18/2013
212340
$ 10.00
$ -
$ 2.89
$ 90.00
$ 12.30
$ 173.19
12/19/2013
216049
$ 30.00
$ -
$ 9.79
$ 120.00
$110.20
$ 329.99
2/20/2013
292352
$ 150.00
$10.48
$ 9.32
$ -
$ 59.89
$ 570.19
4/21/2013
567384
$ 30.00
$20.14
$ 5.56
$ -
$ 30.70
$ 425.40
078-05-1120
Nick Ellison
Meals
Hotel
Description
$ 250.00
Business trip
$ 50.00
Business trip
$ 45.00
$ 250.00
Holiday
$ -
$ 58.00
$ -
Holiday
$ 60.00
Business trip
$ 45.00
$ 295.50
$ 289.00
$ 50.00
Business trip
HR-manager
Tom Nilson
HR
9547320
PURPOSE:
PAY PERIOD:
534084310
HR-Conference
SSN:
EXPENSE REPORT
from 1/16/2013 to 12/19/2013
STATEMENT NUMBER:
EMPLOYEE INFORMATION:
POSITION:
EMPLOYEE ID:
MANAGER:
5/22/2013
890733
$ 70.00
$15.07
$ 14.05
$ -
$100.93
$ 594.05
11/3/2013
578292
$ -
$33.42
$ 2.00
$ -
$320.20
$ 764.62
11/3/2013
199123
$ 90.00
$13.82
$ 2.60
$ -
$ 23.00
$ 528.42
7/25/2013
423509
$ -
$37.50
$ 2.04
$ 104.06
$ 19.20
$ 222.80
3/26/2013
543288
$ 90.00
$14.20
$ 0.25
$ 60.40
$ 12.00
$ 246.85
10/27/2013
457382
$ 125.00
$16.00
$ 14.00
$ -
$ 35.59
$ 440.59
8/28/2013
584839
$ -
$10.04
$ 12.04
$ -
$ 30.00
$ 297.23
4/29/2013
483922
$ -
$56.50
$ 12.45
$ -
$ 60.00
$ 388.95
10/30/2013
890763
$ 125.00
$ 1.42
$ 1.05
$ -
$148.28
$ 551.20
$ 850.00
$257.63
$102.84
$ 374.46
Subtotal:
$ 6,350.87
APPROVED:
NOTES:
Advances:
TOTAL:
$6,350.87
$ 50.00
$ 349.00
Business trip
Holiday
$ -
$ 60.00
$ -
Holiday
$ 70.00
$ 45.00
$ 205.00
Business trip
$ 40.00
Business trip
$ 205.15
$ 55.45
$ 220.00
Business trip
$2,966.65
$ 788.45
Business trip
$ 205.00
$ 55.00
$ 349.00
$ 45.00
Business trip
$ 349.00
$ 60.00
Business trip
$1,010.84
<dx:BootstrapSpreadsheet ID="SpreadsheetCustomDataMerging" runat="server" ActiveTabIndex="0" ShowConfirmOnLosingChanges="false"
    OnLoad="SpreadsheetCustomDataMerging_Load">
</dx:BootstrapSpreadsheet>
int StartRowIndex = 10,
    MaxReportRowsCount = 15;
protected Worksheet Worksheet { get { return SpreadsheetCustomDataMerging.Document.Worksheets.ActiveWorksheet; } }
protected void SpreadsheetCustomDataMerging_Load(object sender, EventArgs e) {
    if(!Page.IsPostBack) {
        SpreadsheetCustomDataMerging.Open(Path.Combine(DirectoryManagmentUtils.CurrentDataDirectory, @"Spreadsheet\\ExpenseReport.xlsx"));
        var expenses = LoadExpensesReportData();
        FillGeneralReportInfo(expenses.Info);
        FillReport(expenses.Expenses);
        SpreadsheetCustomDataMerging.Document.Worksheets.ActiveWorksheet.ActiveView.ShowGridlines = false;
        SpreadsheetCustomDataMerging.Document.History.Clear();
    }
}
ExpensesReport LoadExpensesReportData() {
    using(Stream stream = File.OpenRead(HttpContext.Current.Server.MapPath("~/App_Data/Expenses.xml"))) {
        var serializer = new System.Xml.Serialization.XmlSerializer(typeof(ExpensesReport));
        return (ExpensesReport)serializer.Deserialize(stream);
    }
}
void FillGeneralReportInfo(ExpensesInfo info) {
    Worksheet["D2"].Value = info.Purpose;
    Worksheet["D3"].Value = info.StatementNumber;
    Worksheet["C7"].Value = info.EmployeeName;
    Worksheet["C8"].Value = info.EmployeeDepartment;
    Worksheet["G7"].Value = info.EmployeePosition;
    Worksheet["G8"].Value = info.Manager;
    Worksheet["K7"].Value = info.SSN;
    Worksheet["K8"].Value = info.EmployeeID;
}
void FillReport(List<Expense> report) {
    int reportCount = Math.Min(report.Count, MaxReportRowsCount);
    for(int i = 0; i < reportCount; i++) {
        Expense expense = report[i];
        int rowOffset = StartRowIndex + i + 1;
        Worksheet["B" + rowOffset].Value = expense.CreationDate;
        Worksheet["C" + rowOffset].Value = expense.Account;
        Worksheet["D" + rowOffset].Value = expense.Description;
        Worksheet["F" + rowOffset].Value = expense.Hotel;
        Worksheet["H" + rowOffset].Value = expense.Transport;
        Worksheet["I" + rowOffset].Value = expense.Fuel;
        Worksheet["J" + rowOffset].Value = expense.Meals;
        Worksheet["L" + rowOffset].Value = expense.Phone;
        Worksheet["M" + rowOffset].Value = expense.Entertainment;
        Worksheet["N" + rowOffset].Value = expense.Misc;
    }
}
[XmlRoot("Expenses")]
public class ExpensesReport {
    public ExpensesReport() {
        Info = new ExpensesInfo();
        Expenses = new List<Expense>();
    }
    [XmlElement]
    public ExpensesInfo Info { get; set; }
    [XmlElement("Expense")]
    public List<Expense> Expenses { get; set; }
}
public class ExpensesInfo {
    [XmlAttribute]
    public string Purpose { get; set; }
    [XmlAttribute]
    public int StatementNumber { get; set; }
    [XmlAttribute]
    public int EmployeeID { get; set; }
    [XmlAttribute]
    public string EmployeeName { get; set; }
    [XmlAttribute]
    public string EmployeePosition { get; set; }
    [XmlAttribute]
    public string SSN { get; set; }
    [XmlAttribute]
    public string EmployeeDepartment { get; set; }
    [XmlAttribute]
    public string Manager { get; set; }
}
public class Expense {
    [XmlAttribute(AttributeName = "Date")]
    public DateTime CreationDate { get; set; }
    [XmlAttribute]
    public int Account { get; set; }
    [XmlAttribute]
    public string Description { get; set; }
    [XmlAttribute]
    public double Hotel { get; set; }
    [XmlAttribute]
    public double Transport { get; set; }
    [XmlAttribute]
    public double Fuel { get; set; }
    [XmlAttribute]
    public double Meals { get; set; }
    [XmlAttribute]
    public double Phone { get; set; }
    [XmlAttribute]
    public double Misc { get; set; }
    [XmlAttribute]
    public double Entertainment { get; set; }
}
Screen Size
Color Themes
Demo QR Code
Segoe UI