public static void SetBorder(bool left, bool top, bool right, bool bottom, BorderWeight leftBorderWeight, BorderWeight topBorderWeight, BorderWeight rightBorderWeight, BorderWeight bottomBorderWeight)
{
if (left)
{
s_range.Columns.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
s_range.Columns.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = ToMsExcelBorderWeight(leftBorderWeight);
}
if (top)
{
s_range.Columns.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
s_range.Columns.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = ToMsExcelBorderWeight(topBorderWeight);
}
if (right)
{
s_range.Columns.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
s_range.Columns.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = ToMsExcelBorderWeight(rightBorderWeight);
}
if (bottom)
{
s_range.Columns.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
s_range.Columns.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = ToMsExcelBorderWeight(bottomBorderWeight);
}
}
public static IRange SetBorders(this IRange range, LineStyle style, BorderWeight weight, Color color)
{
range.Borders.LineStyle = style;
range.Borders.Weight = weight;
range.Borders.Color = color;
range.Style.IncludeBorder = true;
return(range);
}
public override BorderBase DeepCloneWithWeight(BorderWeight weight)
{
var result = new OuterBorder(
weight,
this.Style.DeepClone(),
this.Color.DeepClone(),
this.Sides.DeepClone());
return(result);
}
private static Excel.XlBorderWeight ToMsExcelBorderWeight(BorderWeight weight)
{
switch (weight)
{
case BorderWeight.Medium: return(Excel.XlBorderWeight.xlMedium);
case BorderWeight.Thick: return(Excel.XlBorderWeight.xlThick);
case BorderWeight.Thin: return(Excel.XlBorderWeight.xlThin);
default: return(Excel.XlBorderWeight.xlMedium);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="InnerBorder"/> class.
/// </summary>
/// <param name="weight">The weight of the border.</param>
/// <param name="style">The style of the border.</param>
/// <param name="color">The color of the border.</param>
/// <param name="edges">The edges to apply the border to.</param>
public InnerBorder(
BorderWeight weight,
BorderStyle style,
Color color,
InnerBorderEdges edges)
: base(weight, style, color)
{
if (edges == InnerBorderEdges.None)
{
throw new ArgumentOutOfRangeException(Invariant($"{nameof(edges)} is {nameof(InnerBorderEdges.None)}."));
}
this.Edges = edges;
}
/// <summary>
/// Initializes a new instance of the <see cref="OuterBorder"/> class.
/// </summary>
/// <param name="weight">The weight of the border.</param>
/// <param name="style">The style of the border.</param>
/// <param name="color">The color of the border.</param>
/// <param name="sides">The sides to apply the border to.</param>
public OuterBorder(
BorderWeight weight,
BorderStyle style,
Color color,
OuterBorderSides sides)
: base(weight, style, color)
{
if (sides == OuterBorderSides.None)
{
throw new ArgumentOutOfRangeException(Invariant($"{nameof(sides)} is {nameof(OuterBorderSides.None)}."));
}
this.Sides = sides;
}
/// <summary>
/// Initializes a new instance of the <see cref="BorderBase"/> class.
/// </summary>
/// <param name="weight">The weight of the border.</param>
/// <param name="style">The style of the border.</param>
/// <param name="color">The color of the border.</param>
protected BorderBase(
BorderWeight weight,
BorderStyle style,
Color color)
{
if (weight == BorderWeight.Unknown)
{
throw new ArgumentOutOfRangeException(Invariant($"{nameof(weight)} is {nameof(BorderWeight.Unknown)}."));
}
if (style == BorderStyle.Unknown)
{
throw new ArgumentOutOfRangeException(Invariant($"{nameof(style)} is {nameof(BorderStyle.Unknown)}."));
}
this.Weight = weight;
this.Style = style;
this.Color = color;
}
public void GenerateAccountingReport(Month fromMonth, Month toMonth, int year, out string message)
{
message = null;
bool success = true;
try
{
ExcelUtility.CreateExcelDocument();
ExcelUtility.SetGridVisibility(false);
ExcelUtility.Write(5, 2, 7, 2, "NO", null, "Tahoma", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 8.33);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
ExcelUtility.Write(5, 3, 7, 3, "KETERANGAN", null, "Tahoma", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 98.33);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
List <Month> months = MonthUtility.GetMonths();
int column = 4;
Dictionary <int, int> columnReferences = new Dictionary <int, int>();
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
columnReferences[j] = column;
Month month = months.Find(p => p.Index == j);
ExcelUtility.Write(5, column, 7, column, month.Name.ToUpper(), null, "Tahoma", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 16.67);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
column++;
}
ExcelUtility.Write(5, column, 7, column, "JUMLAH", null, "Tahoma", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 16.67);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
ExcelUtility.Write(9, 3, "PENDAPATAN CLUSTER", null, "Tahoma", 12, true, true);
List <ClusterDomain> clusters = DaoFactory.ClusterDao.GetAllClusters();
List <IncomeClusterDomain> incomeClusters = DaoFactory.IncomeClusterDao.GetIncomeClusters(fromMonth.Index, toMonth.Index, year);
List <int> clusterIds = new List <int>();
foreach (IncomeClusterDomain incomeCluster in incomeClusters)
{
if (!clusterIds.Contains(incomeCluster.ClusterId))
{
clusterIds.Add(incomeCluster.ClusterId);
}
}
int rowIndex = 10;
foreach (int clusterId in clusterIds)
{
ClusterDomain cluster = clusters.Find(p => p.Id == clusterId);
if (cluster == null)
{
continue;
}
List <IncomeClusterDomain> result = incomeClusters.FindAll(p => p.ClusterId == clusterId);
if (result == null || result.Count == 0)
{
continue;
}
ExcelUtility.Write(rowIndex, 2, (rowIndex - 9).ToString(), "@", "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(rowIndex, 3, "IPL WARGA " + cluster.ClusterName, null, "Tahoma", 12, true, false);
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
List <IncomeClusterDomain> currentMonthIncomeClusters = result.FindAll(p => p.Month == j);
if (currentMonthIncomeClusters == null || currentMonthIncomeClusters.Count == 0)
{
continue;
}
double amount = 0;
foreach (IncomeClusterDomain income in currentMonthIncomeClusters)
{
amount += income.Amount;
}
ExcelUtility.Write(rowIndex, columnReferences[j], amount, "#,##0", "Tahoma", 12, true, false);
}
rowIndex++;
}
List <IncomeDomain> incomes = DaoFactory.IncomeDao.GetIncomes(fromMonth.Index, toMonth.Index, year);
List <int> incomeSourceIds = new List <int>();
foreach (IncomeDomain income in incomes)
{
if (!incomeSourceIds.Contains(income.IncomeSourceId))
{
incomeSourceIds.Add(income.IncomeSourceId);
}
}
List <IncomeSourceDomain> incomeSources = DaoFactory.IncomeSourceDao.GetAllIncomeSources();
foreach (int incomeSourceId in incomeSourceIds)
{
IncomeSourceDomain incomeSource = incomeSources.Find(p => p.Id == incomeSourceId);
if (incomeSource == null)
{
continue;
}
ExcelUtility.Write(rowIndex, 2, (rowIndex - 9).ToString(), "@", "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(rowIndex, 3, incomeSource.Description, null, "Tahoma", 12, true, false);
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
List <IncomeDomain> currentMonthIncomes = incomes.FindAll(p => p.Month == j && p.IncomeSourceId == incomeSourceId);
if (currentMonthIncomes == null || currentMonthIncomes.Count == 0)
{
continue;
}
double amount = 0;
foreach (IncomeDomain income in currentMonthIncomes)
{
amount += income.Amount;
}
ExcelUtility.Write(rowIndex, columnReferences[j], amount, "#,##0", "Tahoma", 12, true, false);
}
rowIndex++;
}
for (int i = 10; i < rowIndex; i++)
{
ExcelUtility.Write(i, column, "=SUM(" + ExcelUtility.GetExcelCellName(4, i) + ":" + ExcelUtility.GetExcelCellName(column - 1, i), "#,##0", "Tahoma", 12, true, false);
}
rowIndex++;
ExcelUtility.Write(rowIndex, 3, "SUB TOTAL PENDAPATAN", null, "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
int columnIndex = 4;
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
ExcelUtility.Write(rowIndex, columnIndex, "=SUM(" + ExcelUtility.GetExcelCellName(columnIndex, 10) + ":" + ExcelUtility.GetExcelCellName(columnIndex, rowIndex - 2), "#,##0", "Tahoma", 12, true, false);
columnIndex++;
}
ExcelUtility.Write(rowIndex, columnIndex, "=SUM(" + ExcelUtility.GetExcelCellName(columnIndex, 10) + ":" + ExcelUtility.GetExcelCellName(columnIndex, rowIndex - 2), "#,##0", "Tahoma", 12, true, false);
ExcelUtility.Write(rowIndex + 2, 3, "PENGELUARAN CLUSTER", null, "Tahoma", 12, true, true);
rowIndex += 4;
int startExpenseRowIndex = rowIndex;
List <ExpenseDomain> expenses = DaoFactory.ExpenseDao.GetExpenses(fromMonth.Index, toMonth.Index, year);
List <ExpenseDomain> condensedExpenses = new List <ExpenseDomain>();
foreach (ExpenseDomain expense in expenses)
{
ExpenseDomain result = condensedExpenses.Find(p => p.Description.ToLower().Trim() == expense.Description.ToLower().Trim() && p.Month == expense.Month);
if (result == null)
{
condensedExpenses.Add(expense);
}
else
{
result.Amount += expense.Amount;
}
}
Dictionary <string, int> rowReferences = new Dictionary <string, int>();
foreach (ExpenseDomain expense in condensedExpenses)
{
if (rowReferences.ContainsKey(expense.Description.ToLower().Trim()))
{
ExcelUtility.Write(rowReferences[expense.Description.ToLower().Trim()], columnReferences[expense.Month], expense.Amount, "#,##0", "Tahoma", 12, true, false);
}
else
{
ExcelUtility.Write(rowIndex, 2, (rowIndex - startExpenseRowIndex + 1).ToString(), null, "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(rowIndex, 3, expense.Description, null, "Tahoma", 12, true, false);
ExcelUtility.Write(rowIndex, columnReferences[expense.Month], expense.Amount, "#,##0", "Tahoma", 12, true, false);
rowReferences[expense.Description.ToLower().Trim()] = rowIndex;
rowIndex++;
}
}
for (int i = startExpenseRowIndex; i < rowIndex; i++)
{
ExcelUtility.Write(i, column, "=SUM(" + ExcelUtility.GetExcelCellName(4, i) + ":" + ExcelUtility.GetExcelCellName(column - 1, i), "#,##0", "Tahoma", 12, true, false);
}
rowIndex++;
ExcelUtility.Write(rowIndex, 3, "SUB TOTAL PENGELUARAN", null, "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
columnIndex = 4;
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
ExcelUtility.Write(rowIndex, columnIndex, "=SUM(" + ExcelUtility.GetExcelCellName(columnIndex, startExpenseRowIndex) + ":" + ExcelUtility.GetExcelCellName(columnIndex, rowIndex - 2), "#,##0", "Tahoma", 12, true, false);
columnIndex++;
}
ExcelUtility.Write(rowIndex, columnIndex, "=SUM(" + ExcelUtility.GetExcelCellName(columnIndex, startExpenseRowIndex) + ":" + ExcelUtility.GetExcelCellName(columnIndex, rowIndex - 2), "#,##0", "Tahoma", 12, true, false);
rowIndex++;
ExcelUtility.Write(rowIndex, 3, "SALDO BULANAN", null, "Tahoma", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
for (int i = 4; i <= columnIndex; i++)
{
ExcelUtility.Write(rowIndex, i, "=" + ExcelUtility.GetExcelCellName(i, startExpenseRowIndex - 4) + "-" + ExcelUtility.GetExcelCellName(i, rowIndex - 1), "#,##0", "Tahoma", 12, true, false);
}
ExcelUtility.Write(0, 2, 0, column, "LAPORAN KEUANGAN", null, "Tahoma", 14, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(1, 2, 1, column, "WARGA CLUSTER ARGA PADMA NIRWANA", null, "Tahoma", 16, true, true, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
string monthText = fromMonth.Name;
if (toMonth.Index != fromMonth.Index)
{
monthText += " - " + toMonth.Name;
}
ExcelUtility.Write(2, 2, 2, column, "PERIODE BULAN " + monthText + " " + year.ToString(), null, "Tahoma", 14, false, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
for (int i = 8; i <= rowIndex; i++)
{
for (int j = 2; j <= column; j++)
{
ExcelUtility.SetCurrentCell(i, j);
BorderWeight topWeight = BorderWeight.Thin;
BorderWeight bottomWeight = BorderWeight.Thin;
if (i == startExpenseRowIndex - 4 || i == rowIndex - 1 || i == rowIndex)
{
topWeight = BorderWeight.Thick;
bottomWeight = BorderWeight.Thick;
}
else if (i == startExpenseRowIndex - 3)
{
topWeight = BorderWeight.Thick;
}
ExcelUtility.SetBorder(true, i > 8, true, true, BorderWeight.Thick, topWeight, BorderWeight.Thick, bottomWeight);
}
}
ExcelUtility.SetCurrentCell(startExpenseRowIndex - 4, 2, startExpenseRowIndex - 4, column);
ExcelUtility.SetPropertyValue(RangeProperty.CellColor, System.Drawing.Color.FromArgb(141, 180, 226));
ExcelUtility.SetCurrentCell(rowIndex - 1, 2, rowIndex - 1, column);
ExcelUtility.SetPropertyValue(RangeProperty.CellColor, System.Drawing.Color.Yellow);
ExcelUtility.SetCurrentCell(rowIndex, 2, rowIndex, column);
ExcelUtility.SetPropertyValue(RangeProperty.CellColor, System.Drawing.Color.FromArgb(141, 180, 226));
}
catch (Exception ex)
{
message = ex.Message;
success = false;
}
finally
{
if (success)
{
ExcelUtility.DisplayExcelDocument();
}
else
{
ExcelUtility.CloseExcelDocument();
}
}
}
public StyleBorder(BorderSide side, System.Drawing.Color color, LineStyle style, BorderWeight border)
{
this.Side = side;
this.Color = color;
this.Style = style;
this.Border = border;
}
public StyleBorder(BorderSide side, System.Drawing.Color color, BorderWeight border) : this(side, color, LineStyle.xlContinuous, border)
{
}
public virtual BorderBase DeepCloneWithWeight(BorderWeight weight)
{
throw new NotImplementedException("This method should be abstract. It was generated as virtual so that you aren't forced to override it when you create a new model that derives from this model. It will be overridden in the generated designer file.");
}
public void GenerateBalanceReport(Month fromMonth, Month toMonth, int year, out string message)
{
message = null;
string fontName = "Calibri";
bool success = true;
try
{
ExcelUtility.CreateExcelDocument();
ExcelUtility.SetGridVisibility(false);
ExcelUtility.Write(0, 0, 0, 4, "LAPORAN KEUANGAN", null, fontName, 20, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 25.8);
ExcelUtility.Write(1, 0, 1, 4, "WARGA CLUSTER ARGA PADMA NIRWANA", null, fontName, 22, true, true, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 28.8);
string monthText = fromMonth.Name;
if (toMonth.Index != fromMonth.Index)
{
monthText += " - " + toMonth.Name;
}
ExcelUtility.Write(2, 0, 2, 4, "PERIODE BULAN " + monthText + " " + year.ToString(), null, fontName, 14, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 18);
ExcelUtility.Write(3, 4, DateTime.Today, "dd MMM yyyy", fontName, 14, false, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Right);
string[] columnNames = new string[] { "NO", "KETERANGAN", "PEMASUKAN", "PENGELUARAN", "SALDO" };
double[] columnWidths = new double[] { 5.67, 77.11, 20.56, 20.56, 20.56 };
for (int i = 0; i < columnNames.Length; i++)
{
ExcelUtility.Write(4, i, columnNames[i], null, fontName, 14, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, columnWidths[i]);
ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 51);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellColor, System.Drawing.Color.FromArgb(183, 222, 223));
}
ExcelUtility.Write(6, 1, "Saldo akhir tahun " + (year - 1).ToString(), null, fontName, 14, false, false);
ExcelUtility.Write(6, 4, 0, "#,##0", fontName, 14, true, false);
List <IncomeClusterDomain> lastYearIncomeCluster = DaoFactory.IncomeClusterDao.GetIncomeClusters(1, 12, (year - 1));
List <IncomeClusterDomain> incomeClusters = DaoFactory.IncomeClusterDao.GetIncomeClusters(fromMonth.Index, toMonth.Index, year);
List <ClusterDomain> clusters = DaoFactory.ClusterDao.GetAllClusters();
List <Month> months = MonthUtility.GetMonths();
List <int> incomeRows = new List <int>();
int rowIndex = 7;
int lastRowIndex = 6;
int startRowIndex = 7;
bool collectStartRow = true;
for (int i = 0; i < clusters.Count; i++)
{
List <IncomeClusterDomain> lastYearCurrentClusterIncomes = lastYearIncomeCluster.FindAll(p => p.ClusterId == clusters[i].Id);
List <IncomeClusterDomain> currentClusterIncomes = incomeClusters.FindAll(p => p.ClusterId == clusters[i].Id);
if ((lastYearCurrentClusterIncomes == null || lastYearCurrentClusterIncomes.Count == 0) &&
(currentClusterIncomes == null || currentClusterIncomes.Count == 0))
{
continue;
}
ExcelUtility.Write(rowIndex++, 1, clusters[i].ClusterName, null, fontName, 14, true, false);
if (collectStartRow)
{
startRowIndex = rowIndex;
collectStartRow = false;
}
double amount = 0;
foreach (IncomeClusterDomain incomeCluster in lastYearCurrentClusterIncomes)
{
amount += incomeCluster.Amount;
}
ExcelUtility.Write(rowIndex, 0, "1", "@", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 1, "Setoran IPL Cluster " + clusters[i].ClusterName + " Tahun " + (year - 1).ToString(), "@", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 2, amount, "#,##0", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(4, lastRowIndex) + "+" + ExcelUtility.GetExcelCellName(2, rowIndex), "#,##0", fontName, 14, false, false);
lastRowIndex = rowIndex;
rowIndex++;
int count = 2;
bool printSubTotal = false;
for (int month = 1; month <= 12; month++)
{
List <IncomeClusterDomain> result = currentClusterIncomes.FindAll(p => p.Month == month);
if (result == null || result.Count == 0)
{
continue;
}
if (!printSubTotal)
{
printSubTotal = true;
}
double totalIncome = 0;
foreach (IncomeClusterDomain row in result)
{
totalIncome += row.Amount;
}
Month m = months.Find(p => p.Index == month);
ExcelUtility.Write(rowIndex, 0, count.ToString(), "@", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 1, "Setoran IPL Cluster " + clusters[i].ClusterName + " Bulan " + m.Name + " Tahun " + year.ToString(), "@", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 2, totalIncome, "#,##0", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(4, lastRowIndex) + "+" + ExcelUtility.GetExcelCellName(2, rowIndex), "#,##0", fontName, 14, false, false);
lastRowIndex = rowIndex;
rowIndex++;
count++;
}
if (printSubTotal)
{
ExcelUtility.Write(rowIndex, 1, "Sub Total Cluster " + clusters[i].ClusterName, "@", fontName, 14, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(rowIndex, 2, "=SUM(" + ExcelUtility.GetExcelCellName(2, startRowIndex) + ":" + ExcelUtility.GetExcelCellName(2, rowIndex - 1) + ")", "#,##0", fontName, 14, true, false);
incomeRows.Add(rowIndex);
rowIndex++;
}
}
List <IncomeDomain> incomes = DaoFactory.IncomeDao.GetIncomes(fromMonth.Index, toMonth.Index, year);
if (incomes != null && incomes.Count > 0)
{
List <IncomeSourceDomain> incomeSources = DaoFactory.IncomeSourceDao.GetAllIncomeSources();
Dictionary <int, double> totalIncomes = new Dictionary <int, double>();
foreach (IncomeDomain income in incomes)
{
if (totalIncomes.ContainsKey(income.IncomeSourceId))
{
totalIncomes[income.IncomeSourceId] += income.Amount;
}
else
{
totalIncomes[income.IncomeSourceId] = income.Amount;
}
}
ExcelUtility.Write(rowIndex++, 1, "PEMASUKAN", "@", fontName, 14, true, false);
int count = 1;
startRowIndex = rowIndex;
foreach (KeyValuePair <int, double> pair in totalIncomes)
{
IncomeSourceDomain source = incomeSources.Find(p => p.Id == pair.Key);
ExcelUtility.Write(rowIndex, 0, count.ToString(), "@", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 1, source.Description + " selama tahun " + year.ToString(), "@", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 2, pair.Value, "#,##0", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(4, lastRowIndex) + "+" + ExcelUtility.GetExcelCellName(2, rowIndex), "#,##0", fontName, 14, false, false);
lastRowIndex = rowIndex;
rowIndex++;
count++;
}
ExcelUtility.Write(rowIndex, 1, "Sub Total", null, fontName, 14, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(rowIndex, 2, "=SUM(" + ExcelUtility.GetExcelCellName(2, startRowIndex) + ":" + ExcelUtility.GetExcelCellName(2, rowIndex - 1) + ")", "#,##0", fontName, 14, true, false);
incomeRows.Add(rowIndex);
rowIndex++;
}
List <ExpenseDomain> expenses = DaoFactory.ExpenseDao.GetExpenses(fromMonth.Index, toMonth.Index, year);
int startExpenseRow = 0;
if (expenses != null && expenses.Count > 0)
{
ExcelUtility.Write(rowIndex++, 1, "PENGELUARAN", "@", fontName, 14, true, false);
startExpenseRow = rowIndex;
int countExpense = 1;
for (int month = 1; month <= 12; month++)
{
List <ExpenseDomain> result = expenses.FindAll(p => p.Month == month);
if (result == null || result.Count == 0)
{
continue;
}
double totalExpense = 0;
foreach (ExpenseDomain row in result)
{
totalExpense += row.Amount;
}
Month m = months.Find(p => p.Index == month);
ExcelUtility.Write(rowIndex, 0, countExpense.ToString(), "@", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 1, "Pengeluaran selama bulan " + m.Name + " " + year.ToString(), "@", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 3, totalExpense, "#,##0", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(4, lastRowIndex) + "-" + ExcelUtility.GetExcelCellName(3, rowIndex), "#,##0", fontName, 14, false, false);
lastRowIndex = rowIndex;
rowIndex++;
countExpense++;
}
}
rowIndex += 2;
ExcelUtility.Write(rowIndex, 1, "Saldo", "@", fontName, 14, true, false);
string[] cellNames = new string[incomeRows.Count];
for (int x = 0; x < incomeRows.Count; x++)
{
cellNames[x] = ExcelUtility.GetExcelCellName(2, incomeRows[x]);
}
ExcelUtility.Write(rowIndex, 2, "=" + String.Join("+", cellNames), "#,##0", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 3, "=SUM(" + ExcelUtility.GetExcelCellName(3, startExpenseRow) + ":" + ExcelUtility.GetExcelCellName(3, rowIndex - 3) + ")", "#,##0", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(2, rowIndex) + "-" + ExcelUtility.GetExcelCellName(3, rowIndex) + "+" + ExcelUtility.GetExcelCellName(3, 6), "#,##0", fontName, 14, false, false);
rowIndex++;
ExcelUtility.Write(rowIndex, 1, "Saldo di buku tabungan tahun " + (year - 1).ToString(), "@", fontName, 14, true, false);
ExcelUtility.Write(rowIndex, 4, "0", "#,##0", fontName, 14, false, false);
rowIndex++;
ExcelUtility.Write(rowIndex, 1, "Cash on Hand", "@", fontName, 14, true, false);
ExcelUtility.Write(rowIndex, 4, "=" + ExcelUtility.GetExcelCellName(4, rowIndex - 2) + "-" + ExcelUtility.GetExcelCellName(4, rowIndex - 1), "#,##0", fontName, 14, false, false);
rowIndex += 2;
ExcelUtility.Write(rowIndex, 1, "Dibuat Oleh,", "@", fontName, 14, false, false);
ExcelUtility.Write(rowIndex + 6, 1, "Bendahara", "@", fontName, 14, false, false);
ExcelUtility.Write(rowIndex, 3, "Mengetahui,", "@", fontName, 14, false, false);
ExcelUtility.Write(rowIndex + 6, 3, "Ketua Cluster", "@", fontName, 14, false, false);
for (int i = 4; i <= rowIndex - 2; i++)
{
for (int j = 0; j <= 4; j++)
{
BorderWeight topBorderWeight = BorderWeight.Thin;
BorderWeight leftBorderWeight = BorderWeight.Thin;
BorderWeight rightBorderWeight = BorderWeight.Thin;
BorderWeight bottomBorderWeight = BorderWeight.Thin;
if (i == 4 || i == rowIndex - 4 || i == rowIndex - 3 || i == rowIndex - 2)
{
topBorderWeight = BorderWeight.Thick;
}
if (i == rowIndex - 2)
{
bottomBorderWeight = BorderWeight.Thick;
}
if (j == 0)
{
leftBorderWeight = BorderWeight.Thick;
}
if (j == 4)
{
rightBorderWeight = BorderWeight.Thick;
}
ExcelUtility.SetCurrentCell(i, j);
ExcelUtility.SetBorder(true, true, true, true, leftBorderWeight, topBorderWeight, rightBorderWeight, bottomBorderWeight);
}
}
ExcelUtility.SetCurrentCell(rowIndex - 1, 0, rowIndex + 7, 0);
ExcelUtility.SetBorder(true, false, false, false, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
ExcelUtility.SetCurrentCell(rowIndex - 1, 4, rowIndex + 7, 4);
ExcelUtility.SetBorder(false, false, true, false, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
ExcelUtility.SetCurrentCell(rowIndex + 7, 0, rowIndex + 7, 4);
ExcelUtility.SetBorder(false, false, false, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick);
}
catch (Exception ex)
{
message = ex.Message;
success = false;
}
finally
{
if (success)
{
ExcelUtility.DisplayExcelDocument();
}
else
{
ExcelUtility.CloseExcelDocument();
}
}
}
/// <summary>
/// To set border style
/// </summary>
/// <param name="border"></param>
/// <param name="lineStyle"></param>
/// <param name="borderWeight"></param>
/// <param name="borderColor"></param>
internal static void SetBorderStyle(ref SpreadsheetGear.IBorder border, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
border.LineStyle = lineStyle;
border.Weight = borderWeight;
border.Color = borderColor;
}
/// <summary>
/// To set cell's border .
/// </summary>
/// <param name="workbook">Instance of workbook</param>
/// <param name="worksheetIndex">worksheet index</param>
///<param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
/// <param name="borderIndex">Index of border like top, left ,..etc</param>
internal static void SetCellBorder(IWorkbook workbook, int worksheetIndex, int rowIndex, int colIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor, BordersIndex borderIndex)
{
SpreadsheetGear.IRange Cell;
SpreadsheetGear.IBorder CellBorder;
try
{
// Get a reference to a cell.
Cell = workbook.Worksheets[worksheetIndex].Cells[rowIndex, colIndex];
// Get specified border
CellBorder = Cell.Borders[borderIndex];
// set border's properties
ExcelHelper.SetBorderStyle(ref CellBorder, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
}
/// <summary>
/// To set cell's border .
/// </summary>
/// <param name="worksheetIndex">worksheet index</param>
///<param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
public void SetCellBorder(int worksheetIndex, int rowIndex, int colIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
try
{
ExcelHelper.SetCellBorder(this.Workbook, worksheetIndex, rowIndex, colIndex, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// throw ex;
}
}
/// <summary>
/// To set border of a range.
/// </summary>
/// <param name="workbook"> Instance of workbook</param>
/// <param name="worksheetIndex">worksheet index </param>
/// <param name="startRowIndex">starting row index</param>
/// <param name="startColIndex">starting column index</param>
/// <param name="endRowIndex">last row index</param>
/// <param name="endColIndex">last column index</param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
internal static void SetRangeBorders(IWorkbook workbook, int worksheetIndex, int startRowIndex, int startColIndex, int endRowIndex, int endColIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
try
{
//get range
IRange Range = workbook.Worksheets[worksheetIndex].Cells[startRowIndex, startColIndex, endRowIndex, endColIndex];
// set border's properties
ExcelHelper.SetBorders(Range, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// To set cell's border .
/// </summary>
/// <param name="workbook">Instance of workbook</param>
/// <param name="worksheetIndex">worksheet index</param>
///<param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
internal static void SetCellBorder(IWorkbook workbook, int worksheetIndex, int rowIndex, int colIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
SpreadsheetGear.IRange Cell;
try
{
// Get a reference to a cell.
Cell = workbook.Worksheets[worksheetIndex].Cells[rowIndex, colIndex];
// set border's properties
ExcelHelper.SetBorders(Cell.Range, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// To set cell's border .
/// </summary>
///<param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
public void SetCellBorder(int rowIndex, int colIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
try
{
ExcelHelper.SetCellBorder(this.Workbook,rowIndex,colIndex, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
}
/// <summary>
/// To set border of a range.
/// </summary>
/// <param name="worksheetIndex">worksheet index </param>
/// <param name="startRowIndex">starting row index</param>
/// <param name="startColIndex">starting column index</param>
/// <param name="endRowIndex">last row index</param>
/// <param name="endColIndex">last column index</param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
public void SetRangeBorders(int worksheetIndex, int startRowIndex, int startColIndex, int endRowIndex, int endColIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
try
{
ExcelHelper.SetRangeBorders(this.Workbook, worksheetIndex, startRowIndex, startColIndex, endRowIndex, endColIndex, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
}
/// <summary>
/// 绘制指定单元格的边框
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="isDrawTop">是否画上外框</param>
/// <param name="isDrawBottom">是否画下外框</param>
/// <param name="isDrawLeft">是否画左外框</param>
/// <param name="isDrawRight">是否画右外框</param>
/// <param name="isDrawHInside">是否画水平内框</param>
/// <param name="isDrawVInside">是否画垂直内框</param>
/// <param name="isDrawDown">是否画斜向下线</param>
/// <param name="isDrawUp">是否画斜向上线</param>
/// <param name="lineStyle">线类型</param>
/// <param name="borderWeight">线粗细</param>
/// <param name="color">线颜色</param>
public void CellsDrawFrame(int startRow, int startColumn, int endRow, int endColumn,
bool isDrawTop, bool isDrawBottom, bool isDrawLeft, bool isDrawRight,
bool isDrawHInside, bool isDrawVInside, bool isDrawDiagonalDown, bool isDrawDiagonalUp,
LineStyle lineStyle, BorderWeight borderWeight, ColorIndex color)
{
//获取画边框的单元格
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
//清除所有边框
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = LineStyle.无;
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = LineStyle.无;
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = LineStyle.无;
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = LineStyle.无;
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = LineStyle.无;
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = LineStyle.无;
range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = LineStyle.无;
range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = LineStyle.无;
//以下是按参数画边框
if (isDrawTop)
{
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeTop].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeTop].ColorIndex = color;
}
if (isDrawBottom)
{
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeBottom].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeBottom].ColorIndex = color;
}
if (isDrawLeft)
{
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeLeft].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeLeft].ColorIndex = color;
}
if (isDrawRight)
{
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeRight].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeRight].ColorIndex = color;
}
if (isDrawVInside)
{
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlInsideVertical].Weight = borderWeight;
range.Borders[XlBordersIndex.xlInsideVertical].ColorIndex = color;
}
if (isDrawHInside)
{
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = borderWeight;
range.Borders[XlBordersIndex.xlInsideHorizontal].ColorIndex = color;
}
if (isDrawDiagonalDown)
{
range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlDiagonalDown].Weight = borderWeight;
range.Borders[XlBordersIndex.xlDiagonalDown].ColorIndex = color;
}
if (isDrawDiagonalUp)
{
range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlDiagonalUp].Weight = borderWeight;
range.Borders[XlBordersIndex.xlDiagonalUp].ColorIndex = color;
}
}
/// <summary>
/// To set border.
/// </summary>
/// <param name="workbook">Instance of IWorkbook</param>
/// <param name="cellAddress">Address of cell</param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
internal static void SetBorders(IRange range, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
SpreadsheetGear.IBorder TopBorder;
SpreadsheetGear.IBorder BottomBorder;
SpreadsheetGear.IBorder LeftBorder;
SpreadsheetGear.IBorder RightBorder;
try
{
// Get a reference to all the borders of the range.
//top border
TopBorder = range.Borders[BordersIndex.EdgeTop];
// Set the top border Linestyle, Weight, and Color.
ExcelHelper.SetBorderStyle(ref TopBorder, lineStyle, borderWeight, borderColor);
//bottom border
BottomBorder = range.Borders[BordersIndex.EdgeBottom];
// Set the bottom border Linestyle, Weight, and Color.
ExcelHelper.SetBorderStyle(ref BottomBorder, lineStyle, borderWeight, borderColor);
//left border
LeftBorder = range.Borders[BordersIndex.EdgeLeft];
// Set the LeftBorder Linestyle, Weight, and Color.
ExcelHelper.SetBorderStyle(ref LeftBorder, lineStyle, borderWeight, borderColor);
//Right border
RightBorder = range.Borders[BordersIndex.EdgeRight];
// Set the right border Linestyle, Weight, and Color.
ExcelHelper.SetBorderStyle(ref RightBorder, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
}
/// <summary>
/// To set border of a range.
/// </summary>
/// <param name="workbook"> Instance of workbook</param>
/// <param name="worksheetIndex">worksheet index </param>
/// <param name="startRowIndex">starting row index</param>
/// <param name="startColIndex">starting column index</param>
/// <param name="endRowIndex">last row index</param>
/// <param name="endColIndex">last column index</param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
/// <param name="borderIndex">Index like top , left, botton, etc</param>
internal static void SetRangeBorders(IWorkbook workbook, int worksheetIndex, int startRowIndex, int startColIndex, int endRowIndex, int endColIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor, BordersIndex borderIndex)
{
SpreadsheetGear.IBorder RangeBorder;
try
{
//get range
IRange Range = workbook.Worksheets[worksheetIndex].Cells[startRowIndex, startColIndex, endRowIndex, endColIndex];
RangeBorder = Range.Borders[borderIndex];
// set border's properties
ExcelHelper.SetBorderStyle(ref RangeBorder, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
}
/// <summary>
/// To set cell's border .
/// </summary>
/// <param name="workbook">Instance of workbook</param>
///<param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
internal static void SetCellBorder(IWorkbook workbook, int rowIndex, int colIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
SpreadsheetGear.IRange Cell;
try
{
// Get a reference to a cell.
Cell = workbook.ActiveWorksheet.Cells[rowIndex, colIndex];
// set border's properties
ExcelHelper.SetBorders(Cell.Range, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
}
/// <summary>
/// To set border of a range.
/// </summary>
/// <param name="worksheetIndex">worksheet index </param>
/// <param name="startRowIndex">starting row index</param>
/// <param name="startColIndex">starting column index</param>
/// <param name="endRowIndex">last row index</param>
/// <param name="endColIndex">last column index</param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
/// <param name="borderIndex">Index like top , left, botton, etc</param>
public void SetRangeBorder(int worksheetIndex, int startRowIndex, int startColIndex, int endRowIndex, int endColIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor, BordersIndex borderIndex)
{
try
{
ExcelHelper.SetRangeBorders(this.Workbook, worksheetIndex, startRowIndex, startColIndex, endRowIndex, endColIndex, lineStyle, borderWeight, borderColor, borderIndex);
}
catch (Exception ex)
{
// throw ex;
}
}
/// <summary>
/// To set range border of a active worksheet.
/// </summary>
/// <param name="workbook"> Instance of workbook</param>
/// <param name="startRowIndex">starting row index</param>
/// <param name="startColIndex">starting column index</param>
/// <param name="endRowIndex">last row index</param>
/// <param name="endColIndex">last column index</param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
internal static void SetRangeBorders(IWorkbook workbook, int startRowIndex, int startColIndex, int endRowIndex, int endColIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
try
{
//get range
IRange Range = workbook.ActiveWorksheet.Cells[startRowIndex, startColIndex, endRowIndex, endColIndex];
// set border's properties
ExcelHelper.SetBorders(Range, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
}
/// <summary>
/// To set range border of a active worksheet.
/// </summary>
/// <param name="startRowIndex">starting row index</param>
/// <param name="startColIndex">starting column index</param>
/// <param name="endRowIndex">last row index</param>
/// <param name="endColIndex">last column index</param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
public void SetRangeBorders(int startRowIndex, int startColIndex, int endRowIndex, int endColIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
try
{
ExcelHelper.SetRangeBorders(this.Workbook, startRowIndex, startColIndex, endRowIndex, endColIndex, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// ExceptionFacade.ThrowException(ex);
}
}
public void GenerateIncomeClusterReport(ClusterDomain cluster, Month fromMonth, int fromYear, Month toMonth, int toYear, out string message)
{
message = null;
bool success = true;
try
{
ExcelUtility.CreateExcelDocument();
ExcelUtility.SetGridVisibility(false);
ExcelUtility.Write(4, 1, 5, 1, "No.", null, "Arial", 12, false, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 5.89);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thin);
ExcelUtility.Write(4, 2, 5, 2, "Nama Pemilik/Penghuni Rumah", null, "Arial", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 37.22);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thin);
ExcelUtility.Write(4, 3, 4, 4, "Alamat", null, "Arial", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thin);
ExcelUtility.Write(5, 3, 5, 3, "Blok", null, "Arial", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 5.78);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin);
ExcelUtility.Write(5, 4, 5, 4, "No.", null, "Arial", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 5.78);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin);
ExcelUtility.Write(4, 5, 5, 5, "No. Telpon", null, "Arial", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 11.89);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thin);
List <Month> months = MonthUtility.GetMonths();
int column = 6;
Dictionary <int, Dictionary <int, int> > columnReferences = new Dictionary <int, Dictionary <int, int> >();
for (int i = fromYear; i <= toYear; i++)
{
columnReferences[i] = new Dictionary <int, int>();
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
columnReferences[i][j] = column;
Month month = months.Find(p => p.Index == j);
ExcelUtility.Write(5, column, month.ShortName.ToUpper(), null, "Arial", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 11.33);
ExcelUtility.SetBorder(j > fromMonth.Index, true, j < toMonth.Index, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin);
ExcelUtility.SetCurrentCell(4, column);
ExcelUtility.SetBorder(false, true, false, true, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thin);
column++;
}
}
ExcelUtility.Write(4, column, 5, column, "TOTAL", null, "Arial", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.CellWidth, 11.89);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thick, BorderWeight.Thin);
List <IncomeClusterDomain> incomeClusters = DaoFactory.IncomeClusterDao.GetIncomeClusters(cluster, fromMonth.Index, fromYear, toMonth.Index, toYear);
incomeClusters.Sort((p1, p2) =>
{
if (p1.AddressBlock.CompareTo(p2.AddressBlock) != 0)
{
return(p1.AddressBlock.CompareTo(p2.AddressBlock));
}
else
{
return(p1.AddressNumber.CompareTo(p2.AddressNumber));
}
});
Dictionary <string, int> detailsIndices = new Dictionary <string, int>();
int currentRow = 6;
for (int i = 0; i < incomeClusters.Count; i++)
{
IncomeClusterDomain incomeCluster = incomeClusters[i];
string index = string.Join(";", new string[] { incomeCluster.OccupantName, incomeCluster.AddressBlock, incomeCluster.AddressNumber });
if (detailsIndices.ContainsKey(index))
{
ExcelUtility.Write(detailsIndices[index], columnReferences[incomeCluster.Year][incomeCluster.Month], incomeCluster.Amount, "#,##0", "Arial", 12, true, false);
}
else
{
ExcelUtility.Write(currentRow, 1, (currentRow - 5).ToString(), null, "Arial", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin);
ExcelUtility.Write(currentRow, 2, incomeCluster.OccupantName, null, "Arial", 12, true, false);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin);
ExcelUtility.Write(currentRow, 3, incomeCluster.AddressBlock, null, "Arial", 12, true, false);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(currentRow, 4, incomeCluster.AddressNumber, null, "Arial", 12, true, false);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thin);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.Write(currentRow, 5, incomeCluster.PhoneNumber, null, "Arial", 12, true, false);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin);
ExcelUtility.Write(currentRow, columnReferences[incomeCluster.Year][incomeCluster.Month], incomeCluster.Amount, "#,##0", "Arial", 12, true, false);
detailsIndices[index] = currentRow;
currentRow++;
}
}
for (int rowIndex = 6; rowIndex < currentRow; rowIndex++)
{
int columnIndex = 6;
for (int i = fromYear; i <= toYear; i++)
{
for (int j = fromMonth.Index; j <= toMonth.Index; j++)
{
ExcelUtility.SetCurrentCell(rowIndex, columnIndex);
ExcelUtility.SetBorder(columnIndex > 6, true, true, columnIndex < column, BorderWeight.Thin, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin);
columnIndex++;
}
}
}
foreach (KeyValuePair <string, int> detailsIndex in detailsIndices)
{
ExcelUtility.Write(detailsIndex.Value, column, "=SUM(" + ExcelUtility.GetExcelCellName(6, detailsIndex.Value) + ":" + ExcelUtility.GetExcelCellName(column - 1, detailsIndex.Value), "#,##0", "Arial", 12, true, false);
ExcelUtility.SetBorder(true, true, true, true, BorderWeight.Thick, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thin);
}
for (int columnIndex = 1; columnIndex <= column; columnIndex++)
{
ExcelUtility.SetCurrentCell(currentRow, columnIndex);
BorderWeight leftBorderWeight = BorderWeight.Thin;
if (columnIndex == 1 || columnIndex == 6 || columnIndex == column)
{
leftBorderWeight = BorderWeight.Thick;
}
ExcelUtility.SetBorder(true, false, true, true, leftBorderWeight, BorderWeight.Thin, columnIndex == column ? BorderWeight.Thick : BorderWeight.Thin, BorderWeight.Thick);
}
for (int columnIndex = 1; columnIndex <= column; columnIndex++)
{
ExcelUtility.SetCurrentCell(currentRow + 1, columnIndex);
BorderWeight leftBorderWeight = BorderWeight.Thick;
if (columnIndex == 2 || columnIndex == 3 || columnIndex == 4 || columnIndex == 5)
{
leftBorderWeight = BorderWeight.Thin;
}
ExcelUtility.SetBorder(true, false, columnIndex == column, true, leftBorderWeight, BorderWeight.Thin, BorderWeight.Thick, BorderWeight.Thick);
}
ExcelUtility.Write(currentRow + 1, 5, "TOTAL", null, "Arial", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.VerticalAlignment, VAlignment.Center);
for (int i = 6; i <= column; i++)
{
ExcelUtility.Write(currentRow + 1, i, "=SUM(" + ExcelUtility.GetExcelCellName(i, 6) + ":" + ExcelUtility.GetExcelCellName(i, currentRow - 1), "#,##0", "Arial", 12, true, false);
}
ExcelUtility.Write(0, 1, 0, column, "YAYASAN WARGA ARGA PADMA NIRWANA BOGOR", null, "Arial", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 23.3);
ExcelUtility.Write(1, 1, 1, column, "Rekapitulasi Iuran Pengelolaan Lingkungan ( I P L )", null, "Arial", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 23.3);
ExcelUtility.Write(2, 1, 2, column, "Cluster: " + cluster.ClusterName, null, "Arial", 12, true, false, true);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Center);
ExcelUtility.SetPropertyValue(RangeProperty.RowHeight, 23.3);
ExcelUtility.Write(3, column, DateTime.Today.ToString("dd MMMM yyyy"), null, "Arial", 12, true, false);
ExcelUtility.SetPropertyValue(RangeProperty.HorizontalAlignment, HAlignment.Right);
}
catch (Exception ex)
{
message = ex.Message;
success = false;
}
finally
{
if (success)
{
ExcelUtility.DisplayExcelDocument();
}
else
{
ExcelUtility.CloseExcelDocument();
}
}
}
/// <summary>
/// To set cell's border .
/// </summary>
///<param name="rowIndex"></param>
/// <param name="colIndex"></param>
/// <param name="lineStyle">line style for border</param>
/// <param name="borderWeight">weight like thick ,thin ,etc.</param>
/// <param name="borderColor">color for border</param>
public void SetCellBorder(int rowIndex, int colIndex, LineStyle lineStyle, BorderWeight borderWeight, System.Drawing.Color borderColor)
{
try
{
ExcelHelper.SetCellBorder(this.Workbook,rowIndex,colIndex, lineStyle, borderWeight, borderColor);
}
catch (Exception ex)
{
// ExceptionFacade.ThrowException(ex);
}
}
public void BorderAround(LineStyle? linestyle = null, BorderWeight? borderweight = null, ColorIndex? colourindex = null)
{
InternalObject.GetType().InvokeMember("BorderAround", System.Reflection.BindingFlags.InvokeMethod, null, InternalObject, new object[] {linestyle, borderweight, colourindex });
}
/// <summary>
/// 绘制指定单元格的边框
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="isDrawTop">是否画上外框</param>
/// <param name="isDrawBottom">是否画下外框</param>
/// <param name="isDrawLeft">是否画左外框</param>
/// <param name="isDrawRight">是否画右外框</param>
/// <param name="isDrawHInside">是否画水平内框</param>
/// <param name="isDrawVInside">是否画垂直内框</param>
/// <param name="isDrawDown">是否画斜向下线</param>
/// <param name="isDrawUp">是否画斜向上线</param>
/// <param name="lineStyle">线类型</param>
/// <param name="borderWeight">线粗细</param>
/// <param name="color">线颜色</param>
public void CellsDrawFrame(int startRow, int startColumn, int endRow, int endColumn,
bool isDrawTop, bool isDrawBottom, bool isDrawLeft, bool isDrawRight,
bool isDrawHInside, bool isDrawVInside, bool isDrawDiagonalDown, bool isDrawDiagonalUp,
LineStyle lineStyle, BorderWeight borderWeight, ColorIndex color)
{
//获取画边框的单元格
Excel.Range range = myExcel.get_Range(myExcel.Cells[startRow, startColumn], myExcel.Cells[endRow, endColumn]);
//清除所有边框
range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = LineStyle.无;
range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = LineStyle.无;
range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = LineStyle.无;
range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = LineStyle.无;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = LineStyle.无;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = LineStyle.无;
range.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = LineStyle.无;
range.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = LineStyle.无;
//以下是按参数画边框
if (isDrawTop)
{
range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = lineStyle;
range.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = borderWeight;
range.Borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex = color;
}
if (isDrawBottom)
{
range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = lineStyle;
range.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = borderWeight;
range.Borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = color;
}
if (isDrawLeft)
{
range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = lineStyle;
range.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = borderWeight;
range.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = color;
}
if (isDrawRight)
{
range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = lineStyle;
range.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = borderWeight;
range.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = color;
}
if (isDrawVInside)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = lineStyle;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = borderWeight;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = color;
}
if (isDrawHInside)
{
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = lineStyle;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = borderWeight;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = color;
}
if (isDrawDiagonalDown)
{
range.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = lineStyle;
range.Borders[Excel.XlBordersIndex.xlDiagonalDown].Weight = borderWeight;
range.Borders[Excel.XlBordersIndex.xlDiagonalDown].ColorIndex = color;
}
if (isDrawDiagonalUp)
{
range.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = lineStyle;
range.Borders[Excel.XlBordersIndex.xlDiagonalUp].Weight = borderWeight;
range.Borders[Excel.XlBordersIndex.xlDiagonalUp].ColorIndex = color;
}
}
