Monday, June 27, 2005

A Poem of Purpose Driven Life

In a book titled "Purpose Driven Life", Rick Warren included a Russel Kelfer's poem like below:

You are who you are for a reason
You're part of an intricate plan
You're a precious and perfect unique design,
Called God's special woman or man

You look like you look for a reason
Our God made no mistake
He knit you together within the womb
You're just what he wanted to make

The parents you had were the ones he chose,
And no matter how you may feel,
They were custom-designed with God's plan in mind,
And they bear the Master's seal

No, that trauma you faced was not easy
And God wept that it hurt you so;
But it was allowed to shape your heart
So that into his likeness you'd grow

You are who you are for a reason,
You've been formed by the Master's rod
You are who you are, beloved,
Because there is a GOD!

Then, I'll translated it into Indonesian:
Kau ada sebagaimana kau ada karena suatu alasan
Kau adalah bagian dari suatu rencana yang rumit
Kau adalah suatu rancangan unik yang berharga dan sempurna
Disebut pria atau wanita khusus kepunyaan Allah

Kau terlihat seperti kau sekarang karena suatu alasan
Allah kita tidak pernah membuat kesalahan
Dia merajutmu menyatu dalam kandungan
Kau tepat seperti apa yang ingin Dia ciptakan

Orang tua yang kau miliki adalah pilihanNya,
Dan tidak peduli bagaimana perasaanmu,
Mereka secara khusus dibuat dengan pertimbangan rencana Allah
Dan mereka memikul meterai Tuhan

Tidak, trauma yang kau hadapi tidaklah mudah
Dan Allah menangis atas semua yang begitu menyakitimu
Tapi hal itu ditujukan untuk membentuk hatimu
Supaya kau bertumbuh menjadi serupa denganNya

Kau ada sebagaimana kau ada karena suatu alasan,
Kau telah dibentuk dengan tongkat Tuhan
Kau adalah kau sekarang, saudara kekasih,
Karena ada Allah!

Friday, June 3, 2005

Excel Report with Aspose.Excel

Another way to get reports done beside using Crystal Reports Disclaimer: I'm not putting all my codes here, such as querying database and return the result as DataSet

The engine: ExcelReport.cs
using System;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
using Aspose.Excel;

namespace Dallanube.HumanCapital.Report
{
 /// 
 /// Summary description for ExcelReport.
 /// 
 public class ExcelReport
 {
  public Excel CreateExcel(String fileName)
  {
   String strDir=Directory.GetParent(Application.ExecutablePath).ToString();
   /// If you have purchased a License, set license like this: 
   String strLicense =  strDir + "\\Aspose.Excel.lic";    
   Excel.SetLicense(strLicense);

   Excel objExcel = new Excel();
   
   string strDesignerFile = strDir + "\\" + fileName + ".xls";
   objExcel.Open(strDesignerFile);
   
   SetStyles(objExcel);
   return objExcel;
  }

  public void SaveExcel(Excel excel)
  {
   using (SaveFileDialog objFile = new SaveFileDialog())
   {
    objFile.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
    objFile.DefaultExt = "xls";

    if (objFile.ShowDialog() == DialogResult.OK)
    {
     try
     {
      excel.Save(objFile.FileName, FileFormatType.Default);
      ProcessStartInfo psi = new ProcessStartInfo(objFile.FileName); 
      Process.Start(psi); 
     }
     catch(Exception ex)
     {
      MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
    }
   }
  }

  private void SetStyles(Excel excel)
  {
   int intStyleIndex = excel.Styles.Add();
   Style objStyle = excel.Styles[intStyleIndex];
   objStyle.Font.IsBold = true;
   objStyle.Font.Size = 10;
   objStyle.HorizontalAlignment = TextAlignmentType.Left;
   objStyle.Name = "Bold";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Font.IsBold = true;
   objStyle.Font.Size = 10;
   objStyle.HorizontalAlignment = TextAlignmentType.Left;
   objStyle.ForegroundColor = Color.LightGray;
   objStyle.Name = "Header";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Font.IsBold = false;
   objStyle.Font.Size = 10;
   objStyle.HorizontalAlignment = TextAlignmentType.Right;
   objStyle.ForegroundColor = Color.LightGray;
   objStyle.Name = "Gray";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Borders[BorderType.TopBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.BottomBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;
   objStyle.Name = "Box";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.TopBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.BottomBorder].LineStyle=CellBorderType.Thin;
   objStyle.Name = "TopBottomLeftBorder";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.TopBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.BottomBorder].LineStyle=CellBorderType.Thin;
   objStyle.Name = "TopBottomRightBorder";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.TopBorder].LineStyle=CellBorderType.Thin;
   objStyle.HorizontalAlignment = TextAlignmentType.Center;
   objStyle.Name = "TopLeftBorder";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.BottomBorder].LineStyle=CellBorderType.Thin;
   objStyle.HorizontalAlignment = TextAlignmentType.Center;
   objStyle.Name = "BottomLeftBorder";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.TopBorder].LineStyle=CellBorderType.Thin;
   objStyle.Name = "TopRightBorder";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.BottomBorder].LineStyle=CellBorderType.Thin;
   objStyle.Name = "BottomRightBorder";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Borders[BorderType.TopBorder].LineStyle=CellBorderType.Thin;
   objStyle.Name = "TopBorder";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Borders[BorderType.TopBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.BottomBorder].LineStyle=CellBorderType.Thin;
   objStyle.Name = "TopBottomBorder";

   intStyleIndex = excel.Styles.Add();
   objStyle = excel.Styles[intStyleIndex];
   objStyle.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
   objStyle.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;
   objStyle.Name = "LeftRightBorder";
  }
 }
}


One example: FrmRepPayPerMonth.cs (yeah, it's a windows form)
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Windows.Forms;

using Aspose.Excel;
using Dallanube.HumanCapital.Impl;

namespace Dallanube.HumanCapital.Report
{
 public class FrmRepPayPerMonth : Dallanube.HumanCapital.Report.FrmBasePerMonth
 {
  private System.ComponentModel.IContainer components = null;

  public FrmRepPayPerMonth()
  {
   // This call is required by the Windows Form Designer.
   InitializeComponent();

   // TODO: Add any initialization after the InitializeComponent call
  }

  /// 
  /// Clean up any resources being used.
  /// 
  protected override void Dispose( bool disposing )
  {
   if( disposing )
   {
    if (components != null) 
    {
     components.Dispose();
    }
   }
   base.Dispose( disposing );
  }

  #region Designer generated code
  /// 
  /// Required method for Designer support - do not modify
  /// the contents of this method with the code editor.
  /// 
  private void InitializeComponent()
  {
   // 
   // FrmRepPayPerMonth
   // 
   this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
   this.ClientSize = new System.Drawing.Size(342, 143);
   this.Name = "FrmRepPayPerMonth";
   this.Load += new System.EventHandler(this.FrmRepPayPerMonth_Load);

  }
  #endregion

  private void FrmRepPayPerMonth_Load(object sender, System.EventArgs e)
  {
   if (!DesignMode)
   {
    this.Text   = GetString("rep.paypermonth");
   }
  }

  private void CreateReport(byte bytMonth, int intYear, ReportParameter parameter)
  {
   ushort intCurrentRow = parameter.FirstRow;
   byte intCurrentColumn = 0;
   
   String strName, strDept;
   DateTime dtmJoined;
   int intJobStatus;
   double dblSalary, dblAllowance, dblOtherAllowance;
   //double dblTotal;
   //double dblSumSalary=0;
   //double dblSumAllowance=0;
   //double dblSumOtherAllowance=0;
   //double dblSumTotal=0;

   String strCellSalaryStart="", strCellSalaryEnd="";
   String strCellOtherAllowanceStart="", strCellOtherAllowanceEnd="";
   String strCellAllowanceStart="", strCellAllowanceEnd="";
   String strCellTotalStart="", strCellTotalEnd="";

   Worksheet objSheet = parameter.ExcelFile.Worksheets["Sheet1"];
   objSheet.Name = parameter.ReportName;
   Cells objCells = objSheet.Cells;

   // set Month/Year
   DateTime dtmPeriod=new DateTime(intYear, bytMonth, 1);
   String strPeriod="PER: " + dtmPeriod.ToString("MMMM yyyy").ToUpper();

   Style objStyleBold = parameter.ExcelFile.Styles["Bold"];
   objCells[intCurrentRow-4, intCurrentColumn].Style=objStyleBold;
   objCells[intCurrentRow-4, intCurrentColumn].PutValue(strPeriod);
   //

   for(int i = 0; i < parameter.Table.Rows.Count; i ++)
   {
    strName    = parameter.Table.Rows[i]["realName"].ToString();
    strDept    = parameter.Table.Rows[i]["deptName"].ToString();
    dtmJoined   = Convert.ToDateTime(parameter.Table.Rows[i]["startDate"]);
    intJobStatus  = Convert.ToInt32(parameter.Table.Rows[i]["jobStatus"]);
    if (intJobStatus==1)
     dblSalary  = Convert.ToDouble(parameter.Table.Rows[i]["salary"]);
    else
     dblSalary  = Convert.ToDouble(parameter.Table.Rows[i]["dailyWages"]);
    dblOtherAllowance = Convert.ToDouble(parameter.Table.Rows[i]["otherAllowance"]);
    dblAllowance  = Convert.ToDouble(parameter.Table.Rows[i]["allowance"]);
    //dblTotal   = dblSalary + dblAllowance + dblOtherAllowance;
    
    //dblSumSalary+= dblSalary;
    //dblSumOtherAllowance+= dblOtherAllowance;
    //dblSumAllowance+= dblAllowance;
    //dblSumTotal += dblTotal;

    objCells[intCurrentRow, intCurrentColumn].PutValue(i+1);
    objCells[intCurrentRow, intCurrentColumn].Style.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
    objCells[intCurrentRow, intCurrentColumn].Style.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;

    objCells[intCurrentRow, (byte)(intCurrentColumn + 1)].PutValue(strName);
    objCells[intCurrentRow, (byte)(intCurrentColumn + 1)].Style.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
    objCells[intCurrentRow, (byte)(intCurrentColumn + 1)].Style.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;

    objCells[intCurrentRow, (byte)(intCurrentColumn + 2)].PutValue(strDept);
    objCells[intCurrentRow, (byte)(intCurrentColumn + 2)].Style.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
    objCells[intCurrentRow, (byte)(intCurrentColumn + 2)].Style.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;

    objCells[intCurrentRow, (byte)(intCurrentColumn + 3)].PutValue(dtmJoined);
    objCells[intCurrentRow, (byte)(intCurrentColumn + 3)].Style.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
    objCells[intCurrentRow, (byte)(intCurrentColumn + 3)].Style.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;

    if (i==0)
    {
     strCellSalaryStart=objCells[intCurrentRow, (byte)(intCurrentColumn + 4)].Name;
     strCellOtherAllowanceStart=objCells[intCurrentRow, (byte)(intCurrentColumn + 5)].Name;
     strCellAllowanceStart=objCells[intCurrentRow, (byte)(intCurrentColumn + 6)].Name;
    }
    if (i==(parameter.Table.Rows.Count-1))
    {
     strCellSalaryEnd=objCells[intCurrentRow, (byte)(intCurrentColumn + 4)].Name;
     strCellOtherAllowanceEnd=objCells[intCurrentRow, (byte)(intCurrentColumn + 5)].Name;
     strCellAllowanceEnd=objCells[intCurrentRow, (byte)(intCurrentColumn + 6)].Name;
    }

    objCells[intCurrentRow, (byte)(intCurrentColumn + 4)].PutValue(dblSalary);
    objCells[intCurrentRow, (byte)(intCurrentColumn + 4)].Style.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
    objCells[intCurrentRow, (byte)(intCurrentColumn + 4)].Style.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;
    strCellTotalStart = objCells[intCurrentRow, (byte)(intCurrentColumn + 4)].Name;
    
    if (dblOtherAllowance>0)
     objCells[intCurrentRow, (byte)(intCurrentColumn + 5)].PutValue(dblOtherAllowance);
    objCells[intCurrentRow, (byte)(intCurrentColumn + 5)].Style.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
    objCells[intCurrentRow, (byte)(intCurrentColumn + 5)].Style.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;

    objCells[intCurrentRow, (byte)(intCurrentColumn + 6)].PutValue(dblAllowance);
    objCells[intCurrentRow, (byte)(intCurrentColumn + 6)].Style.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
    objCells[intCurrentRow, (byte)(intCurrentColumn + 6)].Style.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;
    strCellTotalEnd = objCells[intCurrentRow, (byte)(intCurrentColumn + 6)].Name;

    //objCells[intCurrentRow, (byte)(intCurrentColumn + 7)].PutValue(dblTotal);
    objCells[intCurrentRow, (byte)(intCurrentColumn + 7)].Formula="=SUM("+ strCellTotalStart +":" + strCellTotalEnd +")";
    objCells[intCurrentRow, (byte)(intCurrentColumn + 7)].Style.Borders[BorderType.LeftBorder].LineStyle=CellBorderType.Thin;
    objCells[intCurrentRow, (byte)(intCurrentColumn + 7)].Style.Borders[BorderType.RightBorder].LineStyle=CellBorderType.Thin;

    intCurrentRow++;
   }

   Style objStyle = parameter.ExcelFile.Styles["TopBottomLeftBorder"];
   objCells[intCurrentRow, intCurrentColumn].Style = objStyle;
   
   objStyle = parameter.ExcelFile.Styles["TopBottomBorder"];
   Range objRange = objCells.CreateRange(intCurrentRow, 1, 1, 3);
   objRange.Style = objStyle;
   objCells[intCurrentRow, (byte)(intCurrentColumn+1)].PutValue("Total");
   
   objStyle = parameter.ExcelFile.Styles["Box"];
   objStyle.Custom="#,##0";
   //objCells[intCurrentRow, (byte)(intCurrentColumn+4)].PutValue(dblSumSalary);
   objCells[intCurrentRow, (byte)(intCurrentColumn+4)].Formula="=SUM("+ strCellSalaryStart +":" + strCellSalaryEnd +")";
   objCells[intCurrentRow, (byte)(intCurrentColumn+4)].Style = objStyle;
   strCellTotalStart = objCells[intCurrentRow, (byte)(intCurrentColumn+4)].Name;

   //objCells[intCurrentRow, (byte)(intCurrentColumn+5)].PutValue(dblSumOtherAllowance);
   objCells[intCurrentRow, (byte)(intCurrentColumn+5)].Formula="=SUM("+ strCellOtherAllowanceStart +":" + strCellOtherAllowanceEnd +")";
   objCells[intCurrentRow, (byte)(intCurrentColumn+5)].Style = objStyle;

   //objCells[intCurrentRow, (byte)(intCurrentColumn+6)].PutValue(dblSumAllowance);
   objCells[intCurrentRow, (byte)(intCurrentColumn+6)].Formula="=SUM("+ strCellAllowanceStart +":" + strCellAllowanceEnd +")";
   objCells[intCurrentRow, (byte)(intCurrentColumn+6)].Style = objStyle;
   strCellTotalEnd = objCells[intCurrentRow, (byte)(intCurrentColumn+6)].Name;

   //objCells[intCurrentRow, (byte)(intCurrentColumn+7)].PutValue(dblSumTotal);
   objCells[intCurrentRow, (byte)(intCurrentColumn+7)].Formula="=SUM("+ strCellTotalStart +":" + strCellTotalEnd +")";
   objCells[intCurrentRow, (byte)(intCurrentColumn+7)].Style = objStyle;
  }

  protected override void Print()
  {
   ExcelReport objReport = new ExcelReport();
   int intYear    = Convert.ToInt32(txtYear.Text);
   byte bytMonth   = Convert.ToByte(cboMonth.SelectedValue);
   DataSet ds    = Global.ReportProxy.CreateReportPayPerMonth(bytMonth, intYear);
    
   ReportParameter objParam= new ReportParameter();
   objParam.ExcelFile  = objReport.CreateExcel("RptDallanube01");
   objParam.Table   = ds.Tables[0];
   objParam.ReportName  = GetString("rep.paypermonth");
   objParam.FirstRow  = 7;

   CreateReport(bytMonth, intYear, objParam);
   for(int i = 0; i < objParam.ExcelFile.Worksheets.Count ; i ++)
   {
    Worksheet objSheet = objParam.ExcelFile.Worksheets[i];
    if(objSheet.Name != objParam.ReportName)
    {
     objParam.ExcelFile.Worksheets.RemoveAt(i);
     i --;
    }
   }

   objReport.SaveExcel(objParam.ExcelFile);
   objReport=null;
  }
 }
}

And ReportParameter.cs
using System;
using System.Data;
using Aspose.Excel;

namespace Dallanube.HumanCapital.Report
{
 /// 
 /// Summary description for ReportParameter.
 /// 
 public class ReportParameter
 {
  private Excel m_objExcelFile;
  private DataTable m_dtbTable;
  //private String m_strSourceSheet;
  private String m_strReportName;
  private ushort m_ushFirstRow;

  public Excel ExcelFile
  {
   get
   {
    return m_objExcelFile;
   }
   set
   {
    m_objExcelFile = value;
   }
  }
  public DataTable Table
  {
   get
   {
    return m_dtbTable;
   }
   set
   {
    m_dtbTable = value;
   }
  }
//  public String SourceSheet
//  {
//   get
//   {
//    return m_strSourceSheet;
//   }
//   set
//   {
//    m_strSourceSheet = value;
//   }
//  }
  public String ReportName
  {
   get
   {
    return m_strReportName;
   }
   set
   {
    m_strReportName = value;
   }
  }
  public ushort FirstRow
  {
   get
   {
    return m_ushFirstRow;
   }
   set
   {
    m_ushFirstRow = value;
   }
  }
 }
}
And for sure Aspose.Excel.dll is needed :D

Edited, reason: formatting codes