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";
}
}
}
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;
}
}
}
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;
}
}
}
}
Edited, reason: formatting codes
No comments:
Post a Comment