protected ushort GenerateHeader(NPOI.HSSF.UserModel.HSSFSheet workSheet, DateTime PeriodFrom, DateTime PeriodTo, ushort RowPos, NPOI.HSSF.UserModel.HSSFCellStyle ReportHeaderStyle, NPOI.HSSF.UserModel.HSSFCellStyle ReportSubHeaderStyle)
{
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)workSheet.CreateRow((int)RowPos).CreateCell(0);
//workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex+5));
cell.SetCellValue("Payroll Allocation Report - Detail");
cell.CellStyle = ReportHeaderStyle;
RowPos++;
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos);
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0);
cell.SetCellValue("From");
cell.CellStyle = ReportSubHeaderStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(1);
cell.SetCellValue(PeriodFrom.ToString("yyyy-MM-dd"));
cell.CellStyle = ReportSubHeaderStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(2);
cell.SetCellValue("To");
cell.CellStyle = ReportSubHeaderStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(3);
cell.SetCellValue(PeriodTo.ToString("yyyy-MM-dd"));
cell.CellStyle = ReportSubHeaderStyle;
RowPos += 2;
return(RowPos);
}
protected ushort GenerateHierarchyTotal(NPOI.HSSF.UserModel.HSSFSheet workSheet, ushort rowPos, NPOI.HSSF.UserModel.HSSFCellStyle style, ushort groupRowCount)
{
if (groupRowCount <= 0)
{
return(rowPos);
}
//workSheet.Cells.Add(rowPos, colPos, currentHierarchyGroup);
ushort colPos; Convert.ToUInt16(summaryStart - Convert.ToUInt16(1));
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowPos);
NPOI.HSSF.UserModel.HSSFCell cell;
for (colPos = 0; colPos < summaryStart - Convert.ToUInt16(1); colPos++)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.CellStyle = style;
}
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.SetCellValue("Total:");
cell.CellStyle = style;
colPos++;
for (; colPos <= reportEndCol; colPos++)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos - groupRowCount, colPos) + ":" + ToCellString(rowPos - Convert.ToUInt16(1), colPos) + ")";
cell.CellStyle = style;
}
rowPos++;
return(rowPos);
}
private void createWorkSheetPeriodRow(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet,
DateTime periodFromDate, DateTime periodToDate)
{
// create header styles
NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleLeft = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
NPOI.HSSF.UserModel.HSSFFont HeaderFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
HeaderFont.Boldweight = 900;
HeaderFont.FontHeightInPoints = 16;
HeaderStyleLeft.SetFont(HeaderFont);
NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
HeaderStyleCenter.CloneStyleFrom(HeaderStyleLeft);
HeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleRight = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
HeaderStyleRight.CloneStyleFrom(HeaderStyleLeft);
HeaderStyleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
// Create header row
NPOI.HSSF.UserModel.HSSFRow HeaderRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0);
NPOI.HSSF.UserModel.HSSFCell HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(5);
HeaderCell.SetCellValue(periodFromDate.ToString("dd-MMM-yy"));
HeaderCell.CellStyle = HeaderStyleRight;
HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(6);
HeaderCell.SetCellValue("~");
HeaderCell.CellStyle = HeaderStyleCenter;
HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(7);
HeaderCell.SetCellValue(periodToDate.ToString("dd-MMM-yy"));
HeaderCell.CellStyle = HeaderStyleLeft;
}
//private ushort GenerateHeader(org.in2bits.MyXls.XlsDocument xlsDoc, org.in2bits.MyXls.Worksheet workSheet, DateTime PeriodFrom, DateTime PeriodTo, ushort RowPos)
protected ushort GenerateHeader(NPOI.HSSF.UserModel.HSSFSheet workSheet, DateTime PeriodFrom, DateTime PeriodTo, ushort RowPos, NPOI.HSSF.UserModel.HSSFCellStyle ReportHeaderStyle, NPOI.HSSF.UserModel.HSSFCellStyle ReportSubHeaderStyle)
{
//RowPos++;
//workSheet.Cells.Add((int)RowPos, 1, "Payment Allocation Report");
//workSheet.Rows[RowPos].GetCell(1).Font.Bold = true;
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)workSheet.CreateRow((int)RowPos).CreateCell(0);
cell.SetCellValue("Payment Allocation Report");
cell.CellStyle = ReportHeaderStyle;
RowPos++;
//workSheet.Cells.Add((int)RowPos, 1, "From");
//workSheet.Rows[RowPos].GetCell(1).Font.Bold = true;
//workSheet.Cells.Add((int)RowPos, 2, PeriodFrom.ToString("yyyy-MM-dd"));
//workSheet.Rows[RowPos].GetCell(2).Font.Bold = true;
//workSheet.Cells.Add((int)RowPos, 3, "To");
//workSheet.Rows[RowPos].GetCell(3).Font.Bold = true;
//workSheet.Cells.Add((int)RowPos, 4, PeriodTo.ToString("yyyy-MM-dd"));
//workSheet.Rows[RowPos].GetCell(4).Font.Bold = true;
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos);
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0);
cell.SetCellValue("From");
cell.CellStyle = ReportSubHeaderStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(1);
cell.SetCellValue(PeriodFrom.ToString("yyyy-MM-dd"));
cell.CellStyle = ReportSubHeaderStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(2);
cell.SetCellValue("To");
cell.CellStyle = ReportSubHeaderStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(3);
cell.SetCellValue(PeriodTo.ToString("yyyy-MM-dd"));
cell.CellStyle = ReportSubHeaderStyle;
return(RowPos);
}
public override FileInfo GenerateBankFile()
{
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(System.IO.File.OpenRead(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ICBCBankFile.xls")));
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0);
int rowCount = 10;
foreach (GenericBankFileDetail bankFileDetail in BankFileDetails)
{
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(rowCount);
if (row == null)
{
row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowCount);
}
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(0);
if (cell == null)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0);
}
cell.SetCellValue(bankFileDetail.EmpNo);
cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(1);
if (cell == null)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(1);
}
cell.SetCellValue(bankFileDetail.EmpBankAccountHolderName);
cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(2);
if (cell == null)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(2);
}
cell.SetCellValue(bankFileDetail.BankCode + bankFileDetail.BranchCode + bankFileDetail.AccountNo);
cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(3);
if (cell == null)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(3);
}
cell.SetCellValue(bankFileDetail.Amount);
rowCount++;
}
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workBook.Write(file);
file.Close();
return(new FileInfo(exportFileName));
}
protected ushort GenerateCompanyTotal(NPOI.HSSF.UserModel.HSSFSheet workSheet, DataTable paymentTable, int RecordCount, double companyEmployerTotal, ushort rowPos, NPOI.HSSF.UserModel.HSSFCellStyle style)
{
//org.in2bits.MyXls.XF xf = xlsDoc.NewXF();
//xf.TopLineStyle = (ushort)1;
rowPos++;
ushort colPos = 0;
//workSheet.Cells.Add(rowPos, colPos, "Total", xf);
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowPos);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.SetCellValue("Total");
cell.CellStyle = style;
//**** Start 2013/11/22, Ricky So, adding Employee No. to excel output
// Employee NO. has been added, and a empty cell is needed in grand total level
if (intHierarchyLevelID == STAFF_LEVEL_ID)
{
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.CellStyle = style;
}
//**** End 2013/11/22, Ricky So, adding Employee No. to excel output
foreach (DataColumn column in paymentTable.Columns)
{
if (column.ColumnName.Equals("EmpPayrollID"))
{
continue;
}
colPos++;
//workSheet.Cells.Add(rowPos, colPos, Math.Round((double)companyTotalHashTable[column.ColumnName], 2), xf);
//workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)";
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos - RecordCount, colPos) + ":" + ToCellString(rowPos - 1, colPos) + ")";
cell.CellStyle = style;
}
colPos++;
//workSheet.Cells.Add(rowPos, colPos, Math.Round(companyNetPayment, 2), xf);
//workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)";
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos - RecordCount, colPos) + ":" + ToCellString(rowPos - 1, colPos) + ")";
cell.CellStyle = style;
colPos++;
//workSheet.Cells.Add(rowPos, colPos, Math.Round(companyEmployerTotal, 2), xf);
//workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)";
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos - RecordCount, colPos) + ":" + ToCellString(rowPos - 1, colPos) + ")";
cell.CellStyle = style;
return(rowPos);
}
// public ushort GenerateHierarchyTotal(org.in2bits.MyXls.XlsDocument xlsDoc, org.in2bits.MyXls.Worksheet workSheet, DataTable paymentTable, string currentHierarchyGroup, Hashtable paymentHashTable, double netPayment, double employerContribution, ushort rowPos)
protected ushort GenerateHierarchyTotal(NPOI.HSSF.UserModel.HSSFSheet workSheet, DataTable paymentTable, string currentHierarchyGroup, string currentEmployeeNo, Hashtable paymentHashTable, double netPayment, double employerContribution, ushort rowPos, NPOI.HSSF.UserModel.HSSFCellStyle style)
{
rowPos++;
ushort colPos = 0;
//workSheet.Cells.Add(rowPos, colPos, currentHierarchyGroup);
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowPos);
NPOI.HSSF.UserModel.HSSFCell cell;
//**** Start 2013/11/22, Ricky So, adding Employee No. to excel output
// write employee Number
if (intHierarchyLevelID == STAFF_LEVEL_ID)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.SetCellValue(currentEmployeeNo);
cell.CellStyle = style;
colPos++;
}
//**** End 2013/11/22, Ricky So, adding Employee No. to excel output
// write grouping value
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.SetCellValue(currentHierarchyGroup);
cell.CellStyle = style;
foreach (DataColumn column in paymentTable.Columns)
{
if (column.ColumnName.Equals("EmpPayrollID"))
{
continue;
}
colPos++;
//workSheet.Cells.Add(rowPos, colPos, Math.Round((double)paymentHashTable[column.ColumnName], 2));
//workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)";
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.SetCellValue(Math.Round((double)paymentHashTable[column.ColumnName], 2));
cell.CellStyle = style;
}
colPos++;
//workSheet.Cells.Add(rowPos, colPos, Math.Round(netPayment, 2));
//workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)";
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos, 1) + ":" + ToCellString(rowPos, colPos - 1) + ")";
cell.CellStyle = style;
colPos++;
//workSheet.Cells.Add(rowPos, colPos, Math.Round(employerContribution, 2));
//workSheet.Rows[rowPos].CellAtCol(colPos).Format = "#,##0.00;(#,##0.00)";
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(colPos);
cell.SetCellValue(Math.Round(employerContribution, 2));
cell.CellStyle = style;
return(rowPos);
}
private void SetWorkSheetColumnWidth(NPOI.HSSF.UserModel.HSSFSheet workSheet, int countDay)
{
workSheet.SetColumnWidth(0, 4000);
workSheet.SetColumnWidth(1, 4000);
workSheet.SetColumnWidth(2, 9000);
workSheet.SetColumnWidth(3, 4000);
workSheet.SetColumnWidth(4, 2000);
for (int dayIdx = 0; dayIdx < countDay; dayIdx++)
{
workSheet.SetColumnWidth(3 + dayIdx, 3000);
}
workSheet.SetColumnWidth(3 + countDay, 3000);
}
private static DataTable ExcelInput(string FilePath)
{
//第一行一般为标题行。
DataTable table = new DataTable();
//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档，然后关闭文件
FileStream fsRead = File.OpenRead(FilePath);
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fsRead);
fsRead.Close();
//获取excel的第一个sheet
NPOI.HSSF.UserModel.HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0);
//获取Excel的最大行数
int rowsCount = sheet.PhysicalNumberOfRows;
//为保证Table布局与Excel一样，这里应该取所有行中的最大列数（需要遍历整个Sheet）。
//为少一交全Excel遍历，提高性能，我们可以人为把第0行的列数调整至所有行中的最大列数。
//int colsCount = sheet.GetRow(1).PhysicalNumberOfCells;
//for (int i = 0; i < colsCount; i++)
//{
// table.Columns.Add(i.ToString());
//}
//for (int x = 0; x < rowsCount; x++)
//{
// DataRow dr = table.NewRow();
// for (int y = 0; y < colsCount; y++)
// {
// dr[y] = sheet.GetRow(x).GetCell(y).ToString();
// }
// table.Rows.Add(dr);
//}
//dataGridView标题
table.Columns.Add("原始值");
table.Columns.Add("修正值");
//取模板中第6列,从第二行开始循环取值
for (int x = 2; x < rowsCount; x++)
{
DataRow dr = table.NewRow();
dr[0] = sheet.GetRow(x).GetCell(6).ToString();
//第二列填入修改后的值
dr[1] = getValueFromHashmap(sheet.GetRow(x).GetCell(6).ToString().Substring(0, 32)) + sheet.GetRow(x).GetCell(6).ToString().Substring(32, sheet.GetRow(x).GetCell(6).ToString().Length - 32);
table.Rows.Add(dr);
}
sheet = null;
workbook = null;
return(table);
}
protected ushort GenerateCompanyHeader(NPOI.HSSF.UserModel.HSSFSheet workSheet, System.Data.DataSet dataSet,
string CompanyHeader, ushort RowPos,
NPOI.HSSF.UserModel.HSSFCellStyle GroupHeaderStyle)
{
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0);
cell.SetCellValue("Company");
cell.CellStyle = GroupHeaderStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(1);
cell.SetCellValue(CompanyHeader);
cell.CellStyle = GroupHeaderStyle;
RowPos++;
return(RowPos);
}
private void buttonUpdateAndSaveFile_Click(object sender, EventArgs e)
{
if (!datasource.Equals("") && !excelsource.Equals(""))
{
//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档，然后关闭文件
FileStream fsRead = File.OpenRead(excelsource);
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fsRead);
fsRead.Close();
//获取excel的第一个sheet
NPOI.HSSF.UserModel.HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0);
//获取Excel的最大行数
int rowsCount = sheet.PhysicalNumberOfRows;
for (int x = 2; x < rowsCount; x++)
{
sheet.GetRow(x).GetCell(6).SetCellValue(getValueFromHashmap(sheet.GetRow(x).GetCell(6).ToString().Substring(0, 32)) + sheet.GetRow(x).GetCell(6).ToString().Substring(32, sheet.GetRow(x).GetCell(6).ToString().Length - 32));
}
//把编辑过后的工作薄重新保存为excel文件
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "All files (*.*)|*.*|xls files (*.xls)|*.xls";
dialog.FilterIndex = 2;
dialog.RestoreDirectory = true;
dialog.ShowDialog();
if (!string.IsNullOrEmpty(dialog.FileName))
{
FileStream fsWrite = File.Create(@dialog.FileName);
workbook.Write(fsWrite);
fsWrite.Close();
}
}
else
{
MessageBox.Show("都不加载下数据就搞？重头来过！");
datasource = "";
excelsource = "";
textBoxExcelUrl.Text = "";
textBoxDbUrl.Text = "";
}
}
/// <summary>
/// 读取excel2003的图片
/// </summary>
/// <param name="sheet">当前sheet对象</param>
/// <returns></returns>
private Dictionary <string, Byte[]> getSheetPictrues03(NPOI.HSSF.UserModel.HSSFSheet sheet)
{
Dictionary <string, Byte[]> sheetIndexPicMap = new Dictionary <string, Byte[]>();
System.Collections.IList pictures = sheet.Workbook.GetAllPictures();
if (pictures.Count > 0)
{
NPOI.HSSF.UserModel.HSSFPatriarch hp = (NPOI.HSSF.UserModel.HSSFPatriarch)sheet.DrawingPatriarch;
foreach (NPOI.HSSF.UserModel.HSSFShape hs in hp.Children)
{
NPOI.HSSF.UserModel.HSSFClientAnchor anchor = (NPOI.HSSF.UserModel.HSSFClientAnchor)hs.Anchor;
NPOI.HSSF.UserModel.HSSFPicture pic = (NPOI.HSSF.UserModel.HSSFPicture)hs;
NPOI.HSSF.UserModel.HSSFPictureData picData = (NPOI.HSSF.UserModel.HSSFPictureData)pic.PictureData;
string pciname = anchor.Row1.ToString() + "." + picData.SuggestFileExtension();
sheetIndexPicMap.Add(pciname, picData.Data);
}
}
return(sheetIndexPicMap);
}
private void AddSubTotalRow(NPOI.HSSF.UserModel.HSSFSheet excelWorksheet, string currentCompany, int recordCount, int lastRowIndex, int lastColumnIndex, NPOI.HSSF.UserModel.HSSFRow headerRow, NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle)
{
//execlWorksheet.Cells[lastRowIndex, PAYMENTDETAIL_START_COLUMN - 1].Value = "Subtotal - " + currentCompany;
NPOI.HSSF.UserModel.HSSFRow subTotalRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex);
NPOI.HSSF.UserModel.HSSFCell subTotalCell = (NPOI.HSSF.UserModel.HSSFCell)subTotalRow.CreateCell(0);
subTotalCell.SetCellValue("Subtotal - " + currentCompany);
subTotalCell.CellStyle = subTotalStyle;
excelWorksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(lastRowIndex, lastRowIndex, 0, PAYMENTDETAIL_START_COLUMN - 1));
for (int iCol = PAYMENTDETAIL_START_COLUMN; iCol <= lastColumnIndex; iCol++)
{
//double totalPayment = 0;
//bool IsTotalPaymentNull = true;
//for (int iRow = lastRowIndex - recordCount; iRow < lastRowIndex; iRow++)
//{
// ExcelLibrary.SpreadSheet.Cell cell = execlWorksheet.Cells[iRow, iCol];
// if (cell.Value is double)
// {
// IsTotalPaymentNull = false;
// totalPayment += (double)cell.Value;
// }
//}
//if (!IsTotalPaymentNull)
//{
// ExcelLibrary.SpreadSheet.Cell totalCell = execlWorksheet.Cells[lastRowIndex, iCol];
// totalCell.FormatString = "#,##0.00";
// totalCell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(totalPayment, 2, 2);
//}
if (headerRow.GetCell(iCol) != null)
{
subTotalCell = (NPOI.HSSF.UserModel.HSSFCell)subTotalRow.CreateCell(iCol);
subTotalCell.CellFormula = "SUM(" + ToCellString(lastRowIndex - recordCount, iCol) + ":" + ToCellString(lastRowIndex - 1, iCol) + ")";
subTotalCell.CellStyle = subTotalStyle;
}
}
}
//private ExcelLibrary.SpreadSheet.Worksheet CreateWorkSheet(DataTable dataTable)
private NPOI.HSSF.UserModel.HSSFSheet CreateWorkSheet(DataTable dataTable)
{
if (workbook != null)
{
NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
NPOI.HSSF.UserModel.HSSFCellStyle numericCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
numericCellStyle.DataFormat = format.GetFormat("0.00##");
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet(dataTable.TableName.Replace("$", ""));
//ExcelLibrary.SpreadSheet.Row headerRow = new ExcelLibrary.SpreadSheet.Row();
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
int columnCount = 0;
foreach (DataColumn headercolumn in dataTable.Columns)
{
headercolumn.ColumnName = headercolumn.ColumnName.Trim();
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
cell.SetCellValue(headercolumn.ColumnName);
//headerRow.SetCell(columnCount,cell);
//worksheet.Cells[0, columnCount] = cell;//new ExcelLibrary.SpreadSheet.Cell(column.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
columnCount++;
}
//worksheet.Cells.Rows.Add(0, headerRow);
int rowCount = 0;
//NPOI.HSSF.UserModel.HSSFCellStyle numericCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//numericCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.00"); ;
//NPOI.HSSF.UserModel.HSSFCellStyle integerCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//integerCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0"); ;
foreach (DataRow row in dataTable.Rows)
{
rowCount++;
columnCount = 0;
// ExcelLibrary.SpreadSheet.Row detailRow = new ExcelLibrary.SpreadSheet.Row();
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
foreach (DataColumn column in dataTable.Columns)
{
//ExcelLibrary.SpreadSheet.Cell cell =new ExcelLibrary.SpreadSheet.Cell(string.Empty, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(columnCount);
if (column.DataType.Equals(typeof(string)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty);
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
cell.SetCellValue(row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
}
else if (column.DataType.Equals(typeof(double)) || column.DataType.Equals(typeof(float)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00");
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]);
if (row[column] != System.DBNull.Value)
{
double value = Convert.ToDouble(row[column].ToString());
if (value.Equals(double.NaN))
{
cell.SetCellValue(string.Empty);
}
else
{
cell.SetCellValue(value);
}
}
cell.CellStyle = numericCellStyle;
}
else if (column.DataType.Equals(typeof(int)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00");
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]);
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue(Convert.ToDouble(row[column].ToString()));
}
//cell.CellStyle = integerCellStyle;
}
else if (column.DataType.Equals(typeof(DateTime)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.DateTime, "yyyy-MM-dd");
//if (row[column] == System.DBNull.Value)
// cell.Value = string.Empty;
//else
// cell.Value = (DateTime)row[column];
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue((DateTime)row[column]);
}
cell.CellStyle = dateCellStyle;
}
else
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty);
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue(row[column].ToString());
}
}
//worksheet.Cells[rowCount, columnCount] = cell;
columnCount++;
}
// worksheet.Cells.Rows.Add(rowCount, detailRow);
}
//workbook.Worksheets.Add(worksheet);
return(worksheet);
}
else
{
return(null);
}
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
const string FIELD_COMPANY = "Company";
const string FIELD_POSITION = "Position";
const string FIELD_PAYROLLGROUP = "Payroll Group";
const string FIELD_EMPNO = "EmployeeID";
const string FIELD_EMPENGFULLNAME = "English Name";
const string FIELD_CHINESENAME = "¤¤¤å©m¦W";
const string FIELD_HKID = @"HKID/Passport";
const string FIELD_PERIODFROM = "From";
const string FIELD_PERIODTO = "To";
const string FIELD_WAGESWORK = "Wages Paid";
const string FIELD_WORKHOURTOTAL = "Total Working Hours";
const string FIELD_RESTDAYTOTAL = "No. of Rest Day";
const string FIELD_STATUTORYHOLIDAYTOTAL = "No. of SH";
const string FIELD_FULLPAIDLEAVETOTAL = "No. of Full Paid Leave";
const string FIELD_NONFULLPAIDLEAVETOTAL = "Non-Full Paid Leave";
ArrayList list = new ArrayList();
foreach (RepeaterItem i in Repeater.Items)
{
CheckBox cb = (CheckBox)i.FindControl("ItemSelect");
if (cb.Checked)
{
EEmpPersonalInfo o = (EEmpPersonalInfo)EEmpPersonalInfo.db.createObject();
WebFormUtils.GetKeys(EEmpPersonalInfo.db, o, cb);
list.Add(o);
}
}
ArrayList payPeriodList = Payroll_ConfirmedPeriod_List1.GetSelectedBaseObjectList();
if (list.Count > 0 && payPeriodList.Count > 0)
{
//const string PAYMENTCODE_PREFIX = "[StatutoryMinimumWageSummary] ";
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
//System.IO.File.Copy(Server.MapPath("~/template/HistoryList_Template.xls"), exportFileName, true);
HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName);
DataSet dataSet = new DataSet(); //export.GetDataSet();
DataTable dataTable = new DataTable("Payroll$");
dataSet.Tables.Add(dataTable);
dataTable.Columns.Add(FIELD_COMPANY, typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
dataTable.Columns.Add(FIELD_POSITION, typeof(string));
dataTable.Columns.Add(FIELD_PAYROLLGROUP, typeof(string));
dataTable.Columns.Add(FIELD_EMPNO, typeof(string));
dataTable.Columns.Add(FIELD_EMPENGFULLNAME, typeof(string));
dataTable.Columns.Add(FIELD_CHINESENAME, typeof(string));
dataTable.Columns.Add(FIELD_HKID, typeof(string));
dataTable.Columns.Add(FIELD_PERIODFROM, typeof(DateTime));
dataTable.Columns.Add(FIELD_PERIODTO, typeof(DateTime));
dataTable.Columns.Add(FIELD_WAGESWORK, typeof(double));
dataTable.Columns.Add(FIELD_WORKHOURTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_RESTDAYTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_STATUTORYHOLIDAYTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_FULLPAIDLEAVETOTAL, typeof(double));
dataTable.Columns.Add(FIELD_NONFULLPAIDLEAVETOTAL, typeof(double));
int firstSummaryColumnPos = dataTable.Columns.Count;
int firstDetailColumnPos = dataTable.Columns.Count;
foreach (EPayrollPeriod payPeriod in payPeriodList)
{
if (EPayrollPeriod.db.select(dbConn, payPeriod))
{
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = payPeriod.PayGroupID;
EPayrollGroup.db.select(dbConn, payrollGroup);
foreach (EEmpPersonalInfo empInfo in list)
{
EEmpPersonalInfo.db.select(dbConn, empInfo);
EEmpTermination empTerm = EEmpTermination.GetObjectByEmpID(dbConn, empInfo.EmpID);
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("ep.EmpID", empInfo.EmpID));
empPayrollFilter.add(new Match("ep.PayPeriodID", payPeriod.PayPeriodID));
if (PayrollStatus.SelectedValue.Equals("T"))
{
empPayrollFilter.add(new Match("ep.EmpPayStatus", "=", "T"));
}
else
{
empPayrollFilter.add(new Match("ep.EmpPayStatus", "<>", "T"));
}
DataRow row = dataTable.NewRow();
row[FIELD_EMPNO] = empInfo.EmpNo;
row[FIELD_EMPENGFULLNAME] = empInfo.EmpEngFullName;
row[FIELD_CHINESENAME] = empInfo.EmpChiFullName;
row[FIELD_HKID] = empInfo.EmpHKID;
row[FIELD_PERIODFROM] = payPeriod.PayPeriodFr;
row[FIELD_PERIODTO] = payPeriod.PayPeriodTo;
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
if (empPos != null)
{
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row[FIELD_COMPANY] = company.CompanyCode;
}
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
row[FIELD_POSITION] = position.PositionDesc;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
EPayrollGroup curentPayGroup = new EPayrollGroup();
curentPayGroup.PayGroupID = empPos.PayGroupID;
if (EPayrollGroup.db.select(dbConn, curentPayGroup))
{
row[FIELD_PAYROLLGROUP] = curentPayGroup.PayGroupDesc;
}
}
double netAmount = 0, releventIncome = 0, nonRelevantIncome = 0, taxableAmount = 0, nonTaxableAmount = 0;
double mcER = 0, mcEE = 0;
double vcER = 0, vcEE = 0;
double pFundER = 0, pFundEE = 0;
double wagesByWork = 0;
double wagesByRest = 0;
double fullPaidLeaveDays = 0;
double nonFullPaidLeaveDays = 0;
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
paymentRecordFilter.add(new Match("PayRecStatus", "A"));
ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter);
foreach (EPaymentRecord paymentRecord in paymentRecords)
{
EPaymentCode payCode = new EPaymentCode();
payCode.PaymentCodeID = paymentRecord.PaymentCodeID;
EPaymentCode.db.select(dbConn, payCode);
// Always Use Payment Code Description for grouping payment code with same description
//string fieldName = PAYMENTCODE_PREFIX + payCode.PaymentCodeDesc;
//if (dataTable.Columns[fieldName] == null)
// dataTable.Columns.Add(new DataColumn(fieldName, typeof(double)));
//if (row[fieldName] == null || row[fieldName] == DBNull.Value)
// row[fieldName] = 0;
//row[fieldName] = (double)row[fieldName] + paymentRecord.PayRecActAmount;
netAmount += paymentRecord.PayRecActAmount;
if (payCode.PaymentCodeIsMPF)
{
releventIncome += paymentRecord.PayRecActAmount;
}
else
{
nonRelevantIncome += paymentRecord.PayRecActAmount;
}
DBFilter taxPaymentMapFilter = new DBFilter();
taxPaymentMapFilter.add(new Match("PaymentCodeID", paymentRecord.PaymentCodeID));
if (ETaxPaymentMap.db.count(dbConn, taxPaymentMapFilter) > 0)
{
taxableAmount += paymentRecord.PayRecActAmount;
}
else
{
nonTaxableAmount += paymentRecord.PayRecActAmount;
}
if (payCode.PaymentCodeIsWages)
{
if (paymentRecord.PayRecIsRestDayPayment)
{
wagesByRest += paymentRecord.PayRecActAmount;
}
else
{
wagesByWork += paymentRecord.PayRecActAmount;
}
}
}
DBFilter mpfRecordFilter = new DBFilter();
mpfRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter);
foreach (EMPFRecord mpfRecord in mpfRecords)
{
vcER += mpfRecord.MPFRecActVCER;
mcER += +mpfRecord.MPFRecActMCER;
vcEE += mpfRecord.MPFRecActVCEE;
mcEE += mpfRecord.MPFRecActMCEE;
}
ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter);
foreach (EORSORecord orsoRecord in orsoRecords)
{
pFundER += orsoRecord.ORSORecActER;
pFundEE += orsoRecord.ORSORecActEE;
}
row[FIELD_WAGESWORK] = wagesByWork;
DBFilter workingSummaryFilter = new DBFilter();
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr));
if (empTerm != null)
{
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo));
}
else
{
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo));
}
workingSummaryFilter.add(new Match("EmpID", empInfo.EmpID));
ArrayList empWorkingSummaryList = EEmpWorkingSummary.db.select(dbConn, workingSummaryFilter);
double workHourTotal = 0, restDayTotal = 0;
foreach (EEmpWorkingSummary empWorkSummary in empWorkingSummaryList)
{
workHourTotal += empWorkSummary.EmpWorkingSummaryTotalWorkingHours;
restDayTotal += empWorkSummary.EmpWorkingSummaryRestDayEntitled;
}
row[FIELD_WORKHOURTOTAL] = workHourTotal;
row[FIELD_RESTDAYTOTAL] = restDayTotal;
DBFilter statutoryHolidayFilter = new DBFilter();
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr));
if (empTerm != null)
{
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo));
}
else
{
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo));
}
ArrayList statutoryHolidayList = EStatutoryHoliday.db.select(dbConn, statutoryHolidayFilter);
double restDayCount = 0;
foreach (EStatutoryHoliday statutoryHoliday in statutoryHolidayList)
{
restDayCount++;
}
row[FIELD_STATUTORYHOLIDAYTOTAL] = restDayCount;
DBFilter LeaveAppEmpPayrollFilter = new DBFilter();
LeaveAppEmpPayrollFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
ArrayList LeaveAppEmpPayrollLists = ELeaveApplication.db.select(dbConn, LeaveAppEmpPayrollFilter);
foreach (ELeaveApplication leaveApp in LeaveAppEmpPayrollLists)
{
ELeaveCode leaveCode = new ELeaveCode();
leaveCode.LeaveCodeID = leaveApp.LeaveCodeID;
if (ELeaveCode.db.select(dbConn, leaveCode))
{
if (leaveCode.LeaveCodePayRatio >= 1)
{
fullPaidLeaveDays += leaveApp.LeaveAppDays;
}
else
{
nonFullPaidLeaveDays += leaveApp.LeaveAppDays;
}
}
}
row[FIELD_FULLPAIDLEAVETOTAL] = fullPaidLeaveDays;
row[FIELD_NONFULLPAIDLEAVETOTAL] = nonFullPaidLeaveDays;
dataTable.Rows.Add(row);
}
}
}
//DBFilter paymentCodeFilter = new DBFilter();
//paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false);
//paymentCodeFilter.add("PaymentCode", false);
//ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter);
//foreach (EPaymentCode paymentCode in paymentCodeList)
//{
// if (dataTable.Columns.Contains(PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc))
// {
// DataColumn paymentColumn = dataTable.Columns[PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc];
// paymentColumn.SetOrdinal(firstDetailColumnPos);
// if (!dataTable.Columns.Contains(paymentCode.PaymentCodeDesc))
// paymentColumn.ColumnName = paymentCode.PaymentCodeDesc;
// else
// {
// Console.Write("System reserved payment column is used");
// }
// }
//}
//for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++)
// dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1);
export.Update(dataSet);
System.IO.FileStream excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open);
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelfileStream);
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0);
workSheet.ShiftRows(workSheet.FirstRowNum, workSheet.LastRowNum, 1);
NPOI.HSSF.UserModel.HSSFRow excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(0);
if (excelRow == null)
{
excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0);
}
NPOI.HSSF.UserModel.HSSFCell excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.GetCell(0);
if (excelCell == null)
{
excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.CreateCell(0);
}
excelCell.SetCellValue("Statutory Minimum Wage Summary Report");
excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open);
workbook.Write(excelfileStream);
excelfileStream.Close();
WebUtils.TransmitFile(Response, exportFileName, "StatutoryMinimumWageSummary_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
return;
}
else
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.addError("Employee not selected");
}
}
protected System.IO.FileInfo GenerateExcelFile()
{
DateTime periodFromDate;
DateTime periodToDate;
if (DateTime.TryParse(PeriodFrDate.Value, out periodFromDate) && DateTime.TryParse(PeriodToDate.Value, out periodToDate))
{
int countDay = periodToDate.AddDays(1).Subtract(periodFromDate).Days;// get days from periodFromDate to periodToDate
List <EHierarchyLevel> hLevelList = GetHierarchyLevelList(dbConn);
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet();
SetWorkSheetColumnWidth(workSheet, countDay);
createWorkSheetPeriodRow(workbook, workSheet, periodFromDate, periodToDate);
createWorkSheetHeaderRows(workbook, workSheet, hLevelList, countDay, periodFromDate, periodToDate);
// load data detail start
ArrayList list = LoadRosterTableGroup(periodFromDate, periodToDate, CurID);
if (list.Count >= 1)
{
selectedRosterTableGroupID = ((ERosterTableGroup)list[0]).RosterTableGroupID;
BuildCommonRosterCodeAndTermsFilter();
Dictionary <int, List <DateTime> > staffRosterDateList = LoadEmployeeList(periodFromDate, periodToDate);
int RowPos = 0;
foreach (int empID in staffRosterDateList.Keys)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empID;
if (!EEmpPersonalInfo.db.select(dbConn, empInfo))
{
continue;
}
string positionTitle = string.Empty;
List <string> hElementDescList = new List <string>();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, periodFromDate, empID);
if (empPos != null)
{
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
positionTitle = position.PositionDesc;
}
foreach (EHierarchyLevel hLevel in hLevelList)
{
string hElementDesc = string.Empty;
DBFilter empHierarchy1Filter = new DBFilter();
empHierarchy1Filter.add(new Match("EmpPosID", empPos.EmpPosID));
empHierarchy1Filter.add(new Match("HLevelID", hLevel.HLevelID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchy1Filter);
if (empHierarchyList.Count > 0)
{
EHierarchyElement hElement = new EHierarchyElement();
hElement.HElementID = ((EEmpHierarchy)empHierarchyList[0]).HElementID;
if (EHierarchyElement.db.select(dbConn, hElement))
{
hElementDesc = hElement.HElementDesc;
}
}
hElementDescList.Add(hElementDesc);
}
}
createWorkSheetDetailRows(workbook, workSheet, RowPos, hElementDescList, empInfo, positionTitle, countDay, periodFromDate, empID);
RowPos += 2;
}
}
// load data detail end
string strTmpFolder = HROne.Common.Folder.GetOrCreateSessionTempFolder(Session.SessionID).FullName;
string strTmpFile = System.IO.Path.Combine(strTmpFolder, AppUtils.ServerDateTime().ToString("~yyyyMMddHHmmss_AttendanceTimeCardRecordReport.xls"));
System.IO.Stream fileoutputstream = new System.IO.FileStream(strTmpFile, System.IO.FileMode.OpenOrCreate);
workbook.Write(fileoutputstream);
fileoutputstream.Close();
return(new System.IO.FileInfo(strTmpFile));
}
return(null);
}
private void createWorkSheetHeaderRows(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet,
List <EHierarchyLevel> hLevelList, int countDay, DateTime periodFromDate, DateTime periodToDate)
{
// Create column header styles
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
columnHeaderStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
NPOI.HSSF.UserModel.HSSFFont columnHeaderFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
columnHeaderFont.Boldweight = 900;
columnHeaderStyle.SetFont(columnHeaderFont);
columnHeaderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
columnHeaderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
columnHeaderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
columnHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleFirstTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleFirstTop.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleFirstTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;
columnHeaderStyleFirstTop.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleMiddleTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleMiddleTop.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleMiddleTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleLastTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleLastTop.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleLastTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;
columnHeaderStyleLastTop.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleFirstBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleFirstBottom.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleFirstBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;
columnHeaderStyleFirstBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleMiddleBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleMiddleBottom.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleMiddleBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleLastBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleLastBottom.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleLastBottom.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;
columnHeaderStyleLastBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;
// Create column header rows
const int COLUMN_HEADER_ROW = 2;
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow1 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW);
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow2 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW + 1);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell;
int colpos = 0;
foreach (EHierarchyLevel hLevel in hLevelList)
{
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
columnHeaderCell.SetCellValue(hLevel.HLevelDesc);
columnHeaderCell.CellStyle = columnHeaderStyleFirstTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
columnHeaderCell.SetCellValue(string.Empty);
columnHeaderCell.CellStyle = columnHeaderStyleFirstBottom;
colpos++;
}
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
columnHeaderCell.SetCellValue("Name");
columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
columnHeaderCell.SetCellValue(string.Empty);
columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;
colpos++;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
columnHeaderCell.SetCellValue("Title");
columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
columnHeaderCell.SetCellValue(string.Empty);
columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;
colpos++;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
columnHeaderCell.SetCellValue("Staff No.");
columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
columnHeaderCell.SetCellValue(string.Empty);
columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;
colpos++;
for (int colIdx = 0; colIdx < colpos; colIdx++)
{
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(COLUMN_HEADER_ROW, COLUMN_HEADER_ROW + 1, colIdx, colIdx));
}
for (int dayIdx = 0; dayIdx < countDay; dayIdx++)
{
DateTime currentDate = periodFromDate.AddDays(dayIdx);
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos + dayIdx);
columnHeaderCell.SetCellValue(currentDate.ToString("dd-MMM-yy"));
columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos + dayIdx);
columnHeaderCell.SetCellValue(currentDate.ToString("ddd"));
columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;
}
colpos += countDay;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
columnHeaderCell.SetCellValue("Signature");
columnHeaderCell.CellStyle = columnHeaderStyleLastTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
columnHeaderCell.SetCellValue(string.Empty);
columnHeaderCell.CellStyle = columnHeaderStyleLastBottom;
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(COLUMN_HEADER_ROW, COLUMN_HEADER_ROW + 1, colpos, colpos));
}
private void GenerateExcelReport(DataSet dataSet, string exportFileName)
{
//ExcelLibrary.SpreadSheet.Worksheet execlWorksheet = null;
DataView tmpView = new DataView(dataSet.Tables["Hierarchy"]);
tmpView.Sort = "CompanyID, PayGroupID, HElementID Desc,CostCenterID";
DataTable sortedHierarchyTable = tmpView.ToTable();
int curringCompanyID = 0;
string currentCompany = string.Empty;
int recordCount = 0;
int lastRowIndex = 0, lastColumnIndex = 0;
//ExcelLibrary.SpreadSheet.Workbook excelWorkbook = new ExcelLibrary.SpreadSheet.Workbook();
//execlWorksheet = new ExcelLibrary.SpreadSheet.Worksheet("CostAllocation");
//excelWorkbook.Worksheets.Add(execlWorksheet);
NPOI.HSSF.UserModel.HSSFWorkbook excelWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet excelWorksheet = (NPOI.HSSF.UserModel.HSSFSheet)excelWorkbook.CreateSheet("CostAllocation");
NPOI.HSSF.UserModel.HSSFRow headerRow = null;
NPOI.HSSF.UserModel.HSSFFont headerFont = (NPOI.HSSF.UserModel.HSSFFont)excelWorkbook.CreateFont();
headerFont.Boldweight = 1;
NPOI.HSSF.UserModel.HSSFCellStyle headerStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle();
headerStyle.SetFont(headerFont);
//headerStyle.BorderBottom = NPOI.SS.UserModel.CellBorderType.THICK;
NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle();
numericStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00");
NPOI.HSSF.UserModel.HSSFFont subTotalFont = (NPOI.HSSF.UserModel.HSSFFont)excelWorkbook.CreateFont();
subTotalFont.Boldweight = 1;
NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle();
subTotalStyle.SetFont(subTotalFont);
//subTotalStyle.BorderTop = NPOI.SS.UserModel.CellBorderType.THICK;
subTotalStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00");
foreach (DataRow row in sortedHierarchyTable.Rows)
{
if (curringCompanyID != (int)row["CompanyID"])
{
if (recordCount > 0)
{
lastRowIndex++;
AddSubTotalRow(excelWorksheet, currentCompany, recordCount, lastRowIndex, lastColumnIndex, headerRow, subTotalStyle);
recordCount = 0;
lastRowIndex++;
}
//else
{
//execlWorksheet.Cells[lastRowIndex, 0].Value = row["Company"].ToString();
//lastRowIndex += FIELD_HEADER_ROW;
//execlWorksheet.Cells[lastRowIndex, PAYROLL_GRUOP_COLUMN].Value = "Payroll Group";
//execlWorksheet.Cells[lastRowIndex, COST_CENTER_COLUMN].Value = "Cost Ctr";
//execlWorksheet.Cells[lastRowIndex, HIERARCHY_COLUMN].Value = "Hierarchy";
NPOI.HSSF.UserModel.HSSFRow companyRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex);
companyRow.CreateCell(0).SetCellValue(row["Company"].ToString());
lastRowIndex += FIELD_HEADER_ROW;
headerRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex);
headerRow.CreateCell(PAYROLL_GRUOP_COLUMN).SetCellValue("Payroll Group");
headerRow.CreateCell(COST_CENTER_COLUMN).SetCellValue("Cost Ctr");
headerRow.CreateCell(HIERARCHY_COLUMN).SetCellValue("Hierarchy");
}
curringCompanyID = (int)row["CompanyID"];
currentCompany = row["Company"].ToString();
}
lastRowIndex++;
recordCount++;
//lastRowIndex = FIELD_HEADER_ROW + recordCount;
//execlWorksheet.Cells[lastRowIndex, PAYROLL_GRUOP_COLUMN].Value = row["PayrollGroupDesc"];
//execlWorksheet.Cells[lastRowIndex, COST_CENTER_COLUMN].Value = row["CostCenterDesc"];
//execlWorksheet.Cells[lastRowIndex, HIERARCHY_COLUMN].Value = row["HierarchyDesc"];
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex);
detailRow.CreateCell(PAYROLL_GRUOP_COLUMN).SetCellValue(row["PayrollGroupDesc"].ToString());
detailRow.CreateCell(COST_CENTER_COLUMN).SetCellValue(row["CostCenterDesc"].ToString());
detailRow.CreateCell(HIERARCHY_COLUMN).SetCellValue(row["HierarchyDesc"].ToString());
lastColumnIndex = PAYMENTDETAIL_START_COLUMN - 1;
DataRow[] paymentRows = dataSet.Tables["Payment"].Select("ID=" + row["ID"]);
if (paymentRows.Length > 0)
{
//double netPayment = 0;
DataRow paymentRow = paymentRows[0];
foreach (DataColumn column in paymentRow.Table.Columns)
{
if (!column.ColumnName.Equals("ID", StringComparison.CurrentCultureIgnoreCase))
{
lastColumnIndex++;
if (recordCount == 1)
{
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex].Value = column.ColumnName;
headerRow.CreateCell(lastColumnIndex).SetCellValue(column.ColumnName);
}
if (!paymentRow.IsNull(column))
{
//ExcelLibrary.SpreadSheet.Cell cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex];
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2);
//cell.FormatString = "#,##0.00";
//netPayment += HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2);
NPOI.HSSF.UserModel.HSSFCell dataCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex);
dataCell.CellStyle = numericStyle;
dataCell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2));
}
}
}
lastColumnIndex++;
if (recordCount == 1)
{
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex].Value = "NET PAYMENT";
headerRow.CreateCell(lastColumnIndex).SetCellValue("NET PAYMENT");
}
//ExcelLibrary.SpreadSheet.Cell netPaymentCell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex];
//netPaymentCell.FormatString = "#,##0.00";
//netPaymentCell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netPayment, 2, 2);
NPOI.HSSF.UserModel.HSSFCell netPaymentCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex);
netPaymentCell.CellStyle = numericStyle;
netPaymentCell.CellFormula = "SUM(" + ToCellString(lastRowIndex, PAYMENTDETAIL_START_COLUMN) + ":" + ToCellString(lastRowIndex, lastColumnIndex - 1) + ")";
}
lastColumnIndex++;
DataRow[] contributionRows = dataSet.Tables["contribution"].Select("ID=" + row["ID"]);
if (contributionRows.Length > 0)
{
DataRow contributionRow = contributionRows[0];
if (recordCount == 1)
{
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 1].Value = "MPF (ER)";
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 2].Value = "VC (ER)";
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 3].Value = "P-FUND (ER)";
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 4].Value = "Total Contribution (ER)";
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 6].Value = "Total Contribution (EE)";
headerRow.CreateCell(lastColumnIndex + 1).SetCellValue("MPF (ER)");
headerRow.CreateCell(lastColumnIndex + 2).SetCellValue("VC (ER)");
headerRow.CreateCell(lastColumnIndex + 3).SetCellValue("P-FUND (ER)");
headerRow.CreateCell(lastColumnIndex + 4).SetCellValue("Total Contribution (ER)");
headerRow.CreateCell(lastColumnIndex + 6).SetCellValue("Total Contribution (EE)");
}
//ExcelLibrary.SpreadSheet.Cell cell=execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 1];
//cell.FormatString = "#,##0.00";
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["MCER"], 2, 2);
//cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 2];
//cell.FormatString = "#,##0.00";
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["VCER"], 2, 2);
//cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 3];
//cell.FormatString = "#,##0.00";
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"], 2, 2);
//cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 4];
//cell.FormatString = "#,##0.00";
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"] + (double)contributionRow["MCER"] + (double)contributionRow["VCER"], 2, 2);
//cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 6];
//cell.FormatString = "#,##0.00";
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDEE"] + (double)contributionRow["MCEE"] + (double)contributionRow["VCEE"], 2, 2);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 1);
cell.CellStyle = numericStyle;
cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["MCER"], 2, 2));
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 2);
cell.CellStyle = numericStyle;
cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["VCER"], 2, 2));
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 3);
cell.CellStyle = numericStyle;
cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"], 2, 2));
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 4);
cell.CellStyle = numericStyle;
cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"] + (double)contributionRow["MCER"] + (double)contributionRow["VCER"], 2, 2));
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 6);
cell.CellStyle = numericStyle;
cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDEE"] + (double)contributionRow["MCEE"] + (double)contributionRow["VCEE"], 2, 2));
}
lastColumnIndex += 6;
}
lastRowIndex++;
AddSubTotalRow(excelWorksheet, currentCompany, recordCount, lastRowIndex, lastColumnIndex, headerRow, subTotalStyle);
//excelWorkbook.Save(exportFileName);
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
excelWorkbook.Write(file);
file.Close();
}
private void GenerateReport(ArrayList empList, DateTime PeriodFrom, DateTime PeriodTo, string SortBy)
{
DataTable dataTable = new DataTable();
dataTable.Columns.Add("EmpID", typeof(int));
dataTable.Columns.Add("EmpNo", typeof(string));
dataTable.Columns.Add("EmpName", typeof(string));
dataTable.Columns.Add("EmpAlias", typeof(string));
dataTable.Columns.Add("Company", typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
dataTable.Columns.Add("Position", typeof(string));
dataTable.Columns.Add("TrainingSeminarID", typeof(int));
dataTable.Columns.Add("TrainingCourseName", typeof(string));
dataTable.Columns.Add("TrainingSeminarDesc", typeof(string));
dataTable.Columns.Add("TrainingSeminarDateFrom", typeof(DateTime));
dataTable.Columns.Add("TrainingSeminarDateTo", typeof(DateTime));
dataTable.Columns.Add("TrainingSeminarDuration", typeof(double));
dataTable.Columns.Add("TrainingSeminarDurationUnit", typeof(string));
dataTable.Columns.Add("TrainingSeminarTrainer", typeof(string));
foreach (EEmpPersonalInfo empInfo in empList)
{
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AppUtils.ServerDateTime().Date, empInfo.EmpID);
ECompany company = new ECompany();
EPosition position = new EPosition();
ArrayList empHierarchyList = new ArrayList();
if (empPos != null)
{
company.CompanyID = empPos.CompanyID;
ECompany.db.select(dbConn, company);
//row["Company"] = company.CompanyCode;
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
//foreach (EEmpHierarchy empHierarchy in empHierarchyList)
//{
// EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
// if (hierarchyLevel != null)
// {
// EHierarchyElement hierarchyElement = new EHierarchyElement();
// hierarchyElement.HElementID = empHierarchy.HElementID;
// if (EHierarchyElement.db.select(dbConn, hierarchyElement))
// row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
// }
//}
position.PositionID = empPos.PositionID;
EPosition.db.select(dbConn, position);
}
DBFilter empTrainingSeminar = new DBFilter();
empTrainingSeminar.add(new Match("EmpID", empInfo.EmpID));
OR orTrainingCourse = null;
foreach (ListItem item in TrainingCourseList.Items)
{
if (item.Selected)
{
if (orTrainingCourse == null)
{
orTrainingCourse = new OR();
}
orTrainingCourse.add(new Match("te.TrainingCourseID", item.Value));
}
}
DBFilter trainingSeminarFilter = new DBFilter();
if (!PeriodFrom.Ticks.Equals(0))
{
trainingSeminarFilter.add(new Match("TrainingSeminarDateFrom", ">=", PeriodFrom));
}
if (!PeriodTo.Ticks.Equals(0))
{
trainingSeminarFilter.add(new Match("TrainingSeminarDateTo", "<=", PeriodTo));
}
if (orTrainingCourse != null)
{
trainingSeminarFilter.add(orTrainingCourse);
}
empTrainingSeminar.add(new IN("TrainingSeminarID", "Select TrainingSeminarID from " + ETrainingSeminar.db.dbclass.tableName + " te", trainingSeminarFilter));
ArrayList empTrainingSeminarList = EEmpTrainingEnroll.db.select(dbConn, empTrainingSeminar);
foreach (EEmpTrainingEnroll empTrainingEnroll in empTrainingSeminarList)
{
ETrainingSeminar trainingSeminar = new ETrainingSeminar();
trainingSeminar.TrainingSeminarID = empTrainingEnroll.TrainingSeminarID;
if (ETrainingSeminar.db.select(dbConn, trainingSeminar))
{
DataRow row = dataTable.NewRow();
row["EmpID"] = empInfo.EmpID;
row["EmpNo"] = empInfo.EmpNo;
row["EmpName"] = empInfo.EmpEngFullName;
row["EmpAlias"] = empInfo.EmpAlias;
row["Company"] = company.CompanyCode;
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
row["Position"] = position.PositionDesc;
row["TrainingSeminarID"] = trainingSeminar.TrainingSeminarID;
ETrainingCourse trainingCourse = new ETrainingCourse();
trainingCourse.TrainingCourseID = trainingSeminar.TrainingCourseID;
if (ETrainingCourse.db.select(dbConn, trainingCourse))
{
row["TrainingCourseName"] = trainingCourse.TrainingCourseName;
}
else
{
row["TrainingCourseName"] = string.Empty;
}
row["TrainingSeminarDesc"] = trainingSeminar.TrainingSeminarDesc == null ? string.Empty : trainingSeminar.TrainingSeminarDesc;
row["TrainingSeminarDateFrom"] = trainingSeminar.TrainingSeminarDateFrom;
row["TrainingSeminarDateTo"] = trainingSeminar.TrainingSeminarDateTo;
row["TrainingSeminarDuration"] = trainingSeminar.TrainingSeminarDuration;
if (trainingSeminar.TrainingSeminarDurationUnit.Equals("H"))
{
row["TrainingSeminarDurationUnit"] = "Hour(s)";
}
else
{
row["TrainingSeminarDurationUnit"] = trainingSeminar.TrainingSeminarDurationUnit;
}
row["TrainingSeminarTrainer"] = trainingSeminar.TrainingSeminarTrainer;
dataTable.Rows.Add(row);
}
}
}
}
//org.in2bits.MyXls.XlsDocument document = new org.in2bits.MyXls.XlsDocument();
//org.in2bits.MyXls.Worksheet worksheet = document.Workbook.Worksheets.Add("training report");
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("training report");
NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
reportHeaderStyle.SetFont(boldFont);
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleCenter.SetFont(boldFont);
columnHeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
//NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");
int rowCount = 0;
//worksheet.Cells.Add(rowCount, (ushort)1, "Training Report").Font.Bold = true;
NPOI.HSSF.UserModel.HSSFCell reportHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)worksheet.CreateRow(rowCount).CreateCell(0);
reportHeaderCell.SetCellValue("Training Report");
reportHeaderCell.CellStyle = reportHeaderStyle;
rowCount++;
if (!PeriodFrom.Ticks.Equals(0) && !PeriodTo.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
rowCount++;
}
else if (!PeriodTo.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
rowCount++;
}
else if (!PeriodFrom.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "From: " + PeriodFrom.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("From: " + PeriodFrom.ToString("dd/MM/yyyy"));
rowCount++;
}
if (SortBy.Equals("Date", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName, EmpNo";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
//worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
//worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
//worksheet.Cells.Merge(rowCount, rowCount, 4, 5);
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
int currentTrainingSeminarID = 0;
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
if (!currentTrainingSeminarID.Equals((int)row["TrainingSeminarID"]))
{
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
}
currentTrainingSeminarID = ((int)row["TrainingSeminarID"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)3, row["TrainingSeminarDesc"]);
//worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
//worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);
detailRow.CreateCell(1).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(2).SetCellValue(row["TrainingSeminarDesc"].ToString());
detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(3).CellStyle = numericStyle;
detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());
colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
}
else if (SortBy.Equals("Position", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "Position, EmpNo, TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName ";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)5, "Company").Font.Bold = true;
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(4);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 4;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
//worksheet.Cells.Add(rowCount, (ushort)colCount + 1, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 2, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 4, "Duration").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 5, string.Empty);
//worksheet.Cells.Merge(rowCount, rowCount, colCount + 4, colCount + 5);
//worksheet.Rows[rowCount].GetCell((ushort)(colCount + 4)).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 6, "Trainer").Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 1);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 2);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 3);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 4);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, colCount + 4, colCount + 5));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 6);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
int currentEmpID = 0;
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
if (!currentEmpID.Equals((int)row["EmpID"]))
{
//worksheet.Cells.Add(rowCount, (ushort)1, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)3, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)4, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["Company"]);
detailRow.CreateCell(0).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(1).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(2).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(3).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(4).SetCellValue(row["Company"].ToString());
colCount = 4;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
currentEmpID = ((int)row["EmpID"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 1, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 2, row["TrainingSeminarDesc"]);
detailRow.CreateCell(colCount + 1).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(colCount + 2).SetCellValue(row["TrainingSeminarDesc"].ToString());
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
//worksheet.Cells.Add(rowCount, (ushort)colCount + 4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 6, row["TrainingSeminarTrainer"]);
detailRow.CreateCell(colCount + 4).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(colCount + 4).CellStyle = numericStyle;
detailRow.CreateCell(colCount + 5).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(colCount + 6).SetCellValue(row["TrainingSeminarTrainer"].ToString());
}
}
else if (SortBy.Equals("Course", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "TrainingCourseName, TrainingSeminarDateFrom, TrainingSeminarDateTo, EmpNo";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
//worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
//worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
//worksheet.Cells.Merge(rowCount, rowCount, 4, 5);
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
//worksheet.Cells.Add(rowCount, (ushort)1, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingSeminarDesc"]);
detailRow.CreateCell(0).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(1).SetCellValue(row["TrainingSeminarDesc"].ToString());
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
//worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
//worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);
detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(3).CellStyle = numericStyle;
detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());
colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
}
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
//document.FileName = exportFileName;
//document.Save();
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
string filename = "TrainingReport_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls";
WebUtils.TransmitFile(Response, exportFileName, filename, true);
return;
}
public static DataSet parse(string ExcelFilePath, string ZipPassword, string FirstColumnName)
{
//OleDbConnection cnnxls = null;
DataSet ds = new DataSet();
if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".zip", StringComparison.CurrentCultureIgnoreCase))
{
string strTmpFolder = ExcelFilePath + ".dir";
try
{
zip.ExtractAll(ExcelFilePath, strTmpFolder, ZipPassword);
System.IO.DirectoryInfo rootDir = new System.IO.DirectoryInfo(strTmpFolder);
foreach (System.IO.FileInfo fileInfo in rootDir.GetFiles("*", System.IO.SearchOption.AllDirectories))
{
ds.Merge(parse(fileInfo.FullName, ZipPassword, FirstColumnName));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
System.IO.Directory.Delete(strTmpFolder, true);
}
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strTmpFolder + ";Extended Properties=\"text;HDR=YES;IMEX=1;FMT=Delimited;\"";
//cnnxls = new OleDbConnection(strConn);
//cnnxls.Open();
}
else if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".csv", StringComparison.CurrentCultureIgnoreCase))
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(ExcelFilePath);
DataTable table = CSVReader.parse(fileInfo.OpenRead(), true, ",", "\"");
table.TableName = System.IO.Path.GetFileNameWithoutExtension(fileInfo.FullName);
ds.Tables.Add(table);
}
else
{
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(ExcelFilePath, System.IO.FileMode.Open)); // ExcelLibrary.SpreadSheet.Workbook.Load(Filename);
for (int sheetIndex = 0; sheetIndex < workBook.NumberOfSheets; sheetIndex++)
{
if (!workBook.IsSheetHidden(sheetIndex))
{
int intHeaderRow = 0;
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(sheetIndex);
NPOI.HSSF.UserModel.HSSFRow headerRow = null; //= (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow);
if (!string.IsNullOrEmpty(FirstColumnName))
{
for (int tmpRowIdx = intHeaderRow; tmpRowIdx <= workSheet.LastRowNum; tmpRowIdx++)
{
headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(tmpRowIdx);
if (headerRow == null)
{
continue;
}
bool columnNameMatch = false;
for (int tmpColumnIndex = 0; tmpColumnIndex <= headerRow.LastCellNum; tmpColumnIndex++)
{
if (headerRow.GetCell(tmpColumnIndex) != null)
{
string columnName = headerRow.GetCell(tmpColumnIndex).ToString().Trim();
if (FirstColumnName.Equals(columnName))
{
intHeaderRow = tmpRowIdx;
columnNameMatch = true;
break;
}
}
}
if (columnNameMatch)
{
break;
}
}
}
else
{
headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow);
}
if (headerRow == null)
{
continue;
}
string tableName = workSheet.SheetName.Trim();
DataTable table = new DataTable(tableName);
int intColumnIndex = 0;
while (intColumnIndex <= headerRow.LastCellNum)
{
if (headerRow.GetCell(intColumnIndex) != null)
{
string columnName = headerRow.GetCell(intColumnIndex).ToString().Trim();
if (string.IsNullOrEmpty(columnName))
{
columnName = "Column_" + intColumnIndex;
}
if (table.Columns.Contains(columnName))
{
columnName = "Column_" + intColumnIndex;
}
table.Columns.Add(columnName, typeof(string));
// resign new value of column name to Excel for below part of import
headerRow.GetCell(intColumnIndex).SetCellValue(columnName);
}
intColumnIndex++;
}
int rowCount = 1;
while (intHeaderRow + rowCount <= workSheet.LastRowNum)
{
int colCount = 0;
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow + rowCount);
if (row == null)
{
rowCount++;
continue;
}
DataRow dataRow = table.NewRow();
while (colCount <= headerRow.LastCellNum)
{
if (headerRow.GetCell(colCount) != null)
{
string columnName = headerRow.GetCell(colCount).ToString();
if (table.Columns.Contains(columnName))
{
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(colCount);
if (cell != null)
{
if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.FORMULA))
{
NPOI.HSSF.UserModel.HSSFFormulaEvaluator e = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workBook);
cell = (NPOI.HSSF.UserModel.HSSFCell)e.EvaluateInCell(cell);
}
string fieldValue = cell.ToString();
if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC))
{
string format = string.Empty;
//bool IsBuildinformat = false;
// Not sure whether workBook.CreateDataFormat().GetFormat(index) can obtain all the build-in format
try
{
format = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat(cell.CellStyle.DataFormat);
//IsBuildinformat = true;
}
catch
{
format = workBook.CreateDataFormat().GetFormat(cell.CellStyle.DataFormat);
}
// [h]:mm:ss handle NOT support
int midBlanketStartPos = format.IndexOf('[');
while (midBlanketStartPos >= 0)
{
int midBlanketEndPos = format.IndexOf(']', midBlanketStartPos);
format = format.Substring(0, midBlanketStartPos) + format.Substring(midBlanketStartPos + 1, midBlanketEndPos - midBlanketStartPos - 1) + format.Substring(midBlanketEndPos + 1);
midBlanketStartPos = format.IndexOf('[');
}
if (format.IndexOf("y", StringComparison.CurrentCultureIgnoreCase) >= 0 || format.IndexOf("d", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
DateTime date = cell.DateCellValue;
if (date.TimeOfDay.TotalSeconds > 0)
{
fieldValue = date.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
fieldValue = date.ToString("yyyy-MM-dd");
}
}
}
else if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
DateTime date = cell.DateCellValue;
// default date of "Time Only" field is 1899-12-31
if (!date.Date.Ticks.Equals(new DateTime(1899, 12, 31).Ticks))
{
fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
fieldValue = cell.DateCellValue.ToString("HH:mm:ss");
}
}
else
{
fieldValue = cell.NumericCellValue.ToString();
}
}
dataRow[columnName] = fieldValue;
}
}
}
colCount++;
}
table.Rows.Add(dataRow);
rowCount++;
}
ds.Tables.Add(table);
}
}
//string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES;\"";
//cnnxls = new OleDbConnection(strConn);
//try
//{
// cnnxls.Open();
//}
//catch
//{
// cnnxls.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES;\"";
// cnnxls.Open();
//}
//DataTable schemaTable = cnnxls.GetSchema("Tables");
//foreach (DataRow schemaRow in schemaTable.Rows)
//{
// string tableName = schemaRow["Table_Name"].ToString().Trim();
// if (tableName.EndsWith("$"))
// {
// OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + tableName + "]", cnnxls);
// try
// {
// //DataTable[] tables = oda.FillSchema(ds, SchemaType.Mapped);//
// //tables[0].TableName = schemaRow["Table_Name"].ToString().Replace("$", "").Replace("#csv", "");
// //if (tables[0].Columns.Contains("Emp No*"))
// // tables[0].Columns["Emp No*"].DataType = typeof(string);
// //OleDbDataReader dr = oda.SelectCommand.ExecuteReader();
// //while (dr.Read())
// //{
// // DataRow row = tables[0].NewRow();
// // for (int i = 0; i < tables[0].Columns.Count; i++)
// // row[i] = dr[i];
// // tables[0].Rows.Add(row);
// //}
// //// oda.Fill(tables[0]);
// //if (ds.Tables.Contains(tableName) && tableName.ToString().EndsWith("$"))
// // ds.Tables.Remove(tableName);
// string actualTableName = tableName.Substring(0, tableName.Length - 1);
// if (!ds.Tables.Contains(actualTableName))
// oda.Fill(ds, actualTableName);
// }
// catch
// {
// // unknown error caused by hidden sheet
// }
// // oda.Fill(ds);
// }
//}
//cnnxls.Close();
}
foreach (DataTable tempTable in ds.Tables)
{
for (int rowIdx = tempTable.Rows.Count - 1; rowIdx >= 0; rowIdx--)
{
DataRow row = tempTable.Rows[rowIdx];
bool isEmptyRow = true;
foreach (DataColumn tempColumn in tempTable.Columns)
{
if (!row.IsNull(tempColumn))
{
if (!string.IsNullOrEmpty(row[tempColumn].ToString().Trim()))
{
isEmptyRow = false;
break;
}
}
}
if (isEmptyRow)
{
tempTable.Rows.Remove(row);
}
else
{
break;
}
}
}
foreach (DataTable tempTable in ds.Tables)
{
foreach (DataColumn tempColumn in tempTable.Columns)
{
string tempColumnName = tempColumn.ColumnName;
tempColumnName = tempColumnName.Trim().Replace("*", "");
tempColumnName = tempColumnName.Trim().Replace("#", "");
tempColumn.ColumnName = tempColumnName;
}
}
return(ds);
}
private void createWorkSheetDetailRows(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet, int RowPos,
List <string> hElementDescList, EEmpPersonalInfo empInfo, string positionTitle, int countDay, DateTime periodFromDate, int empID)
{
// Create column detail styles
NPOI.HSSF.UserModel.HSSFCellStyle detailStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
detailStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
detailStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
detailStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle detailStyleCenterAlignment = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
detailStyleCenterAlignment.CloneStyleFrom(detailStyle);
detailStyleCenterAlignment.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.HSSF.UserModel.HSSFCellStyle detailStyleFirst = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
detailStyleFirst.CloneStyleFrom(detailStyle);
detailStyleFirst.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;
NPOI.HSSF.UserModel.HSSFCellStyle detailStyleLast = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
detailStyleLast.CloneStyleFrom(detailStyle);
detailStyleLast.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;
// Create column detail rows
const int DETAIL_START_ROW = 4;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(DETAIL_START_ROW + RowPos);
NPOI.HSSF.UserModel.HSSFRow detailRow2 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(DETAIL_START_ROW + RowPos + 1); // second row of record
NPOI.HSSF.UserModel.HSSFCell detailCell;
NPOI.HSSF.UserModel.HSSFCell detailCell2;
int colpos = 0;
foreach (string hElementDesc in hElementDescList)
{
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos));
detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos);
detailCell.SetCellValue(hElementDesc);
detailCell.CellStyle = detailStyleFirst;
detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos);
detailCell2.SetCellValue(hElementDesc);
detailCell2.CellStyle = detailStyleFirst;
colpos++;
}
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos));
detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos);
detailCell.SetCellValue(empInfo.EmpEngFullName);
detailCell.CellStyle = detailStyleFirst;
detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos);
detailCell2.SetCellValue(empInfo.EmpEngFullName);
detailCell2.CellStyle = detailStyleFirst;
colpos++;
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos));
detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos);
detailCell.SetCellValue(positionTitle);
detailCell.CellStyle = detailStyle;
detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos);
detailCell2.SetCellValue(positionTitle);
detailCell2.CellStyle = detailStyle;
colpos++;
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos));
detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos);
detailCell.SetCellValue(empInfo.EmpNo);
detailCell.CellStyle = detailStyle;
detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos);
detailCell2.SetCellValue(empInfo.EmpNo);
detailCell2.CellStyle = detailStyle;
colpos++;
for (int dayIdx = 0; dayIdx < countDay; dayIdx++)
{
//first line of employee record
detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos + dayIdx);
detailCell.CellStyle = detailStyleCenterAlignment;
// second line of employee data
detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos + dayIdx);
detailCell2.CellStyle = detailStyleCenterAlignment;
string currentTimeSpanRangeStr = getCurrentTimeSpanRangeStr(periodFromDate, dayIdx, empID);
if (currentTimeSpanRangeStr != null)
{
detailCell.SetCellValue(currentTimeSpanRangeStr);
}
else
{
string currentRosterCodeDesc = getCurrentRosterCodeDesc(periodFromDate, dayIdx, empID);
if (currentRosterCodeDesc != null)
{
detailCell.SetCellValue(currentRosterCodeDesc);
}
}
string s = getCurrentLeaveCodeDesc(periodFromDate, dayIdx, empID);
if (s != "")
{
detailCell2.SetCellValue(s);
}
}
colpos += countDay;
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(DETAIL_START_ROW + RowPos, DETAIL_START_ROW + RowPos + 1, colpos, colpos));
detailCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(colpos);
detailCell.SetCellValue(string.Empty);
detailCell.CellStyle = detailStyleLast;
detailCell2 = (NPOI.HSSF.UserModel.HSSFCell)detailRow2.CreateCell(colpos);
detailCell2.SetCellValue(string.Empty);
detailCell2.CellStyle = detailStyleLast;
}
public ActionResult ExceFill()
{
//创建一个ExCel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//获取集合的数据
List <ZhengZaiShangKeBiao> LIst = ShangKeBiaoBLL.GetAllData().OrderBy(p => p.RiQi).ToList();
//添加1个Sheet
NPOI.HSSF.UserModel.HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)book.CreateSheet("Sheet1");
//给sheet添加第一行的头部标题；获取工作表的第一行
NPOI.HSSF.UserModel.HSSFRow row1 = (NPOI.HSSF.UserModel.HSSFRow)sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("班级名称");
row1.CreateCell(1).SetCellValue("教室");
row1.CreateCell(2).SetCellValue("时间");
row1.CreateCell(3).SetCellValue("时间段");
row1.CreateCell(4).SetCellValue("阶段");
row1.CreateCell(5).SetCellValue("教员名称");
row1.CreateCell(6).SetCellValue("课程");
//将数据逐步写入到Sheet各个行
for (int i = 0; i < LIst.Count; i++)
{
//根据班级名查询出班级阶段
//int c;
//if (LIst[i].JiaoYuanMingChen == "未安排")
//{
// c = 0;
//}
//else
//{
// c = Convert.ToInt32(LIst[i].JiaoYuanBianHao);
//}
//var a = yuan.GetAllData().Where(p => p.ID == c).ToList();
var ClassJD = BanJiBiaoBLL.GetAllData().Where(p => p.BanJiMing == LIst[i].ClassName).First();
//var JD = JiaoXueJieDuanBLL.GetAllData().Where(p => p.ID == ClassJD.JieDuanID).First();
//继续构造excel的数据行
NPOI.HSSF.UserModel.HSSFRow rowtemp = (NPOI.HSSF.UserModel.HSSFRow)sheet.CreateRow(i + 1);
var Class = KeChengJiHuaBiaoBLL.GetAllData().Where(p => p.BanJiMing == LIst[i].ClassName).First();
//var JD = JiaoXueJieDuanBLL.GetAllData().Where(p => p.ID == Class.KaiSheJiaoXueJieDuan).First();
rowtemp.CreateCell(0).SetCellValue(LIst[i].ClassName.ToString());
rowtemp.CreateCell(1).SetCellValue(LIst[i].ZiYuanMingChen.ToString());
rowtemp.CreateCell(2).SetCellValue(Convert.ToDateTime(LIst[i].RiQi).ToString("yyyy-MM-dd"));
rowtemp.CreateCell(3).SetCellValue(LIst[i].ShiJianDuan.ToString());
rowtemp.CreateCell(4).SetCellValue(1);
rowtemp.CreateCell(5).SetCellValue(LIst[i].JiaoYuanMingChen);
rowtemp.CreateCell(6).SetCellValue(LIst[i].KeChengMingChen.ToString());
//继续添加列数据
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
//Excel写入到内存中
book.Write(ms);
//从0位置读到结束
ms.Seek(0, System.IO.SeekOrigin.Begin);
Message();
//读取内存流中的二进制
byte[] bytes = ms.ToArray();
ms.Close();
ms.Dispose();
OutputCilent(bytes);
return(Json(new { errorNo = "0", errorInfo = "查询成功" }, JsonRequestBehavior.AllowGet));
}
public void GenerateExcelReport(string exportFileName)
{
DataSet.Payroll_KTPFundStatement dataSet = CreateDataSource();
int lastRowIndex = 0;
// Set column style
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("KTPF Contribution Report");
// Date format
NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
dateCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
dateCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
dateCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
dateCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Numeric format
NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-");
numericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
numericStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
numericStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
numericStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// String left format
NPOI.HSSF.UserModel.HSSFCellStyle stringLeftStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
stringLeftStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
stringLeftStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringLeftStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringLeftStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// String center format
NPOI.HSSF.UserModel.HSSFCellStyle stringCenterStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
stringCenterStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
stringCenterStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringCenterStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringCenterStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 0 style
NPOI.HSSF.UserModel.HSSFCellStyle column0Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column0Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
column0Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
column0Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column0Style.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 10, 11 style
NPOI.HSSF.UserModel.HSSFCellStyle column10To11Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column10To11Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
column10To11Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column10To11Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 4 style
NPOI.HSSF.UserModel.HSSFCellStyle column4Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column4Style.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-");
column4Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
column4Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column4Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 8 style
NPOI.HSSF.UserModel.HSSFCellStyle column8Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column8Style.DataFormat = format.GetFormat("yyyy-MM-dd");
column8Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
column8Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
column8Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column8Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Align right
NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
// Bottom border
NPOI.HSSF.UserModel.HSSFCellStyle bottomBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
bottomBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
// Bold style
NPOI.HSSF.UserModel.HSSFCellStyle boldStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont boldFont = workbook.CreateFont();
boldFont.Boldweight = 700;
boldStyle.SetFont(boldFont);
// Header Border
NPOI.HSSF.UserModel.HSSFCellStyle headerBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
headerBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
headerBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
headerBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
headerBorderStyle.SetFont(boldFont);
headerBorderStyle.WrapText = true;
headerBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// Header Left Border
NPOI.HSSF.UserModel.HSSFCellStyle headerLeftBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
headerLeftBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
headerLeftBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
headerLeftBorderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
headerLeftBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
headerLeftBorderStyle.SetFont(boldFont);
headerLeftBorderStyle.WrapText = true;
headerLeftBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// Header Right Border
NPOI.HSSF.UserModel.HSSFCellStyle headerRightBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
headerRightBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
headerRightBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
headerRightBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
headerRightBorderStyle.SetFont(boldFont);
headerRightBorderStyle.WrapText = true;
headerRightBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// Total numeric format
NPOI.HSSF.UserModel.HSSFCellStyle totalNumericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
totalNumericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-");
totalNumericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
totalNumericStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
// Grey color
NPOI.HSSF.UserModel.HSSFCellStyle grey25Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
grey25Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
grey25Style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
grey25Style.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
grey25Style.SetFont(boldFont);
// Yellow color
NPOI.HSSF.UserModel.HSSFCellStyle yellowStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
yellowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
yellowStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index;
yellowStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
yellowStyle.SetFont(boldFont);
// Set column width
worksheet.SetColumnWidth(0, 12 * 256);
worksheet.SetColumnWidth(1, 20 * 256);
worksheet.SetColumnWidth(2, 15 * 256);
worksheet.SetColumnWidth(3, 15 * 256);
worksheet.SetColumnWidth(4, 15 * 256);
worksheet.SetColumnWidth(5, 7 * 256);
worksheet.SetColumnWidth(6, 15 * 256);
worksheet.SetColumnWidth(7, 15 * 256);
worksheet.SetColumnWidth(8, 15 * 256);
worksheet.SetColumnWidth(9, 15 * 256);
worksheet.SetColumnWidth(10, 15 * 256);
worksheet.SetColumnWidth(11, 25 * 256);
// Set column title
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
NPOI.HSSF.UserModel.HSSFCell headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
headerCell.SetCellValue("KTPF Contribution Report");
headerCell.CellStyle = boldStyle;
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(1);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
if (dataSet.ORSOPlan.Rows.Count > 0)
{
Payroll_KTPFundStatement.ORSOPlanRow m_orsoPlan = (Payroll_KTPFundStatement.ORSOPlanRow)dataSet.ORSOPlan.Rows[0];
headerCell.SetCellValue(m_orsoPlan.ORSOPlanCompanyName);
}
headerCell.CellStyle = boldStyle;
string m_reportPeriod = "";
if (_payPeriodFr.Month != _payPeriodTo.Month)
{
m_reportPeriod = _payPeriodFr.ToString("dd MMMM yyyy") + " - " + _payPeriodTo.ToString("dd MMMM yyyy");
}
else
{
m_reportPeriod = _payPeriodFr.ToString("MMMM yyyy");
}
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(2);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
headerCell.SetCellValue(m_reportPeriod);
headerCell.CellStyle = boldStyle;
// Merge header
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(3);
// Merge cell from 5-8
NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 5, (short)8);
worksheet.AddMergedRegion(cellRangeAddress);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5);
headerCell.SetCellValue("For New Joiner");
headerCell.CellStyle = yellowStyle;
((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
// Merge cell from 9-10
cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 9, (short)10);
worksheet.AddMergedRegion(cellRangeAddress);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9);
headerCell.SetCellValue("For Resigned Staff");
headerCell.CellStyle = grey25Style;
((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(4);
headerRow.HeightInPoints = 40;
// column A
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
headerCell.SetCellValue("Member ID");
headerCell.CellStyle = headerLeftBorderStyle;
// column B
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(1);
headerCell.SetCellValue("Employee Name");
headerCell.CellStyle = headerBorderStyle;
// column C
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(2);
headerCell.SetCellValue("Basic Salary");
headerCell.CellStyle = headerBorderStyle;
// column D
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(3);
headerCell.SetCellValue("KTPF Contribution");
headerCell.CellStyle = headerBorderStyle;
// column E
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(4);
headerCell.SetCellValue("Employer MPF Contribution");
headerCell.CellStyle = headerRightBorderStyle;
// column F
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5);
headerCell.SetCellValue("Sex");
headerCell.CellStyle = headerBorderStyle;
// column G
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(6);
headerCell.SetCellValue("Date Of Birth");
headerCell.CellStyle = headerBorderStyle;
// column H
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(7);
headerCell.SetCellValue("Date Join");
headerCell.CellStyle = headerBorderStyle;
// column I
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(8);
headerCell.SetCellValue("Effective Date");
headerCell.CellStyle = headerRightBorderStyle;
// column J
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9);
headerCell.SetCellValue("Termination Date");
headerCell.CellStyle = headerBorderStyle;
// column K
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(10);
headerCell.SetCellValue("Termination Mode");
headerCell.CellStyle = headerRightBorderStyle;
// column L
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(11);
headerCell.SetCellValue("Remarks");
headerCell.CellStyle = headerRightBorderStyle;
// Create total
int length = dataSet.ExistingMember.Rows.Count + 5;
NPOI.HSSF.UserModel.HSSFRow totalRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + length);
NPOI.HSSF.UserModel.HSSFCell totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(0);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(5);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(6);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(7);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(8);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(9);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(10);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(11);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(1);
totalCell.SetCellValue("Total");
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(2);
totalCell.SetCellFormula("SUM(C5:C" + totalRow.RowNum.ToString("0") + ")");
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(3);
totalCell.SetCellFormula("SUM(D5:D" + totalRow.RowNum.ToString("0") + ")");
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(4);
totalCell.SetCellFormula("SUM(E5:E" + totalRow.RowNum.ToString("0") + ")");
totalCell.CellStyle = totalNumericStyle;
int rowLength = 0;
// Set value for every row
dataSet.ExistingMember.DefaultView.Sort = "EmpName";
DataTable m_table = dataSet.ExistingMember.DefaultView.ToTable();
foreach (DataRow m_row in m_table.Rows)
//foreach (DataRow row in tmpDataTable.Rows)
{
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 5);
rowLength++;
if (lastRowIndex == (m_table.Rows.Count))
{
detailRow.RowStyle = bottomBorderStyle;
}
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(0);
cell.SetCellValue(m_row["MemberID"].ToString());
cell.CellStyle = column0Style;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(1);
cell.SetCellValue(m_row["EmpName"].ToString());
cell.CellStyle = stringLeftStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(2);
cell.SetCellValue((double)m_row["RelevantIncome"]);
cell.CellStyle = numericStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(3);
cell.SetCellValue((double)m_row["ER"]);
cell.CellStyle = numericStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(4);
cell.SetCellValue((double)m_row["MpfMCER"]);
cell.CellStyle = column4Style;
DateTime m_periodFrom = (DateTime)m_row["PeriodFrom"];
DateTime m_effDate = (DateTime)m_row["OrsoEffDate"];
{
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(5);
cell.CellStyle = stringCenterStyle;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
cell.SetCellValue(m_row["EmpSex"].ToString());
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(6);
cell.CellStyle = dateCellStyle;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["EmpDOB"]);
}
catch { }
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(7);
cell.CellStyle = dateCellStyle;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["EmpDateJoin"]);
}
catch { }
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8);
cell.CellStyle = column8Style;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["OrsoEffDate"]);
}
catch { }
}
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9);
cell.CellStyle = dateCellStyle;
cell.SetCellValue("");// put something into the cell and so as the border line can be shown
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["LastEmploymentDate"]);
}
catch { }
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(10);
cell.SetCellValue(m_row["TermCode"].ToString());
cell.CellStyle = column10To11Style;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11);
//cell.SetCellValue(row[FIELD_REMARKS].ToString());
cell.CellStyle = column10To11Style;
lastRowIndex++;
}
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
}
private void button13_Click(object sender, EventArgs e)
{
//清空报表
report1.ClearReport();
//设置报表为非只读
report1.ReadOnly = false;
//报表停止公式计算，这样速度快点
report1.CalcFormula(false);
//报表停止绘制
report1.AllowDraw(false);
string fileName = Application.StartupPath + "\\Test.xls";
NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook;
try
{
this.Cursor = Cursors.WaitCursor;
using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream);
NPOI.HSSF.UserModel.HSSFSheet sheet = hssfworkbook.GetSheetAt(0);
if (sheet.LastRowNum == 0)
{
this.Cursor = Cursors.Arrow;
return;
}
else
{
//产生列
NPOI.HSSF.UserModel.HSSFRow row = sheet.GetRow(0);
//report1.AddColumn(row.Cells.Count);
report1.AddColumn(row.LastCellNum);
}
int rowIndex = 0;
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
//增加行
rowIndex = report1.AddRow(Gscr.Band.Detail);
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)rows.Current;
for (int i = 0; i < row.LastCellNum; i++)
{
Gscr.Cell cellRpt = report1[rowIndex, i];
NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(i);
if (cell == null)
{
cellRpt.Value = null;
}
else
{
switch (cell.CellType)
{
case NPOI.HSSF.UserModel.HSSFCellType.BLANK:
cellRpt.Value = null;
break;
case NPOI.HSSF.UserModel.HSSFCellType.BOOLEAN:
cellRpt.Value = cell.BooleanCellValue;
break;
case NPOI.HSSF.UserModel.HSSFCellType.NUMERIC:
cellRpt.Value = cell.ToString();
break;
case NPOI.HSSF.UserModel.HSSFCellType.STRING:
cellRpt.Value = cell.StringCellValue;
break;
case NPOI.HSSF.UserModel.HSSFCellType.ERROR:
cellRpt.Value = cell.ErrorCellValue;
break;
default:
break;
}
}
}
}
}
//报表恢复公式计算
report1.CalcFormula(true);
//报表恢复绘制
report1.AllowDraw(true);
this.Cursor = Cursors.Arrow;
MessageBox.Show("成功读取Excel文件【" + fileName + " 】。",
"提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception err)
{
report1.CalcFormula(true);
report1.AllowDraw(true);
this.Cursor = Cursors.Arrow;
MessageBox.Show("读取Excel文件失败。" + err.Message,
"提示", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
}
public void GenerateExcelReport(string _filename)
{
// F&V MPF Remittance Statement is composed of MPF and PFund data. So the current MPF Remittance Statement can only achieve 1/2 of their needs.
if (PayPeriodFr.Ticks != 0 && PayPeriodTo.Ticks != 0 && EmpList != null)
{
// get MPF part of the remmittance statement
MPFFile.GenericMPFFile mpfFile = new MPFFile.GenericMPFFile(dbConn);
mpfFile.LoadMPFFileDetail(EmpList, MPFPlanID, PayPeriodFr, PayPeriodTo);
System.Data.DataSet dataSet = mpfFile.CreateRemittanceStatementDataSet();
string _schemeNo = "";
string _companyName = "";
DataTable m_table = null;
NPOI.HSSF.UserModel.HSSFSheet m_worksheet = null;
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
if (this.MPFPlanID > 0)
{
EMPFPlan _mpfPlan = EMPFPlan.GetObject(dbConn, this.MPFPlanID);
if (_mpfPlan != null)
{
EMPFScheme _mpfScheme = EMPFScheme.GetObject(dbConn, _mpfPlan.MPFSchemeID);
if (_mpfScheme != null)
{
_schemeNo = _mpfScheme.MPFSchemeCode;
}
_companyName = _mpfPlan.MPFPlanCompanyName;
}
m_table = ConvertData(dataSet.Tables["NewJoinMember"], "", 1);
m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-NewJoinMember");
WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1);
m_table = ConvertData(dataSet.Tables["ExistingMember"], "IsBackPayRecord = false", 1);
m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-ExistingEmployee");
WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1);
m_table = ConvertData(dataSet.Tables["ExistingMember"], "IsBackPayRecord = true", 1);
m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-TerminatedEmployee");
WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1);
}
if (this.ORSOPlanID > 0)
{
EORSOPlan _orsoPlan = EORSOPlan.GetObject(dbConn, ORSOPlanID);
if (_orsoPlan != null)
{
_schemeNo = _orsoPlan.ORSOPlanSchemeNo;
_companyName = _orsoPlan.ORSOPlanCompanyName;
}
DataTable _orsoStaffTable = LoadORSODetail();
m_table = ConvertData(_orsoStaffTable, "IsBackPayRecord = false", 2);
m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("2-ExistingEmployee");
WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 2);
m_table = ConvertData(_orsoStaffTable, "IsBackPayRecord = true", 2);
m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("2-TerminatedEmployee");
WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 2);
}
// get ORSO part of remittance statement
System.IO.FileStream file = new System.IO.FileStream(_filename, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
}
}
protected override void GenerateWorkbookDetail(NPOI.HSSF.UserModel.HSSFWorkbook workBook, System.Data.DataSet dataSet)
{
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.CreateSheet("Payroll Allocation Report - Detail");
ushort rowPos = 0;
DataTable empInfoTable = dataSet.Tables["EmpInfo"];
DataTable paymentTable = dataSet.Tables["payment"];
DataTable costCenterDetailTable = dataSet.Tables["CostCenterDetail"];
DataTable hierarchyTable = dataSet.Tables["hierarchy"];
DataTable payPeriodTable = dataSet.Tables["payPeriod"];
DataTable hierarchyTotalTable = new DataTable("HierarchyTotal"); // use for calculate local hierarchy total
hierarchyTotalTable.Columns.Add("company", typeof(string));
hierarchyTotalTable.Columns.Add("LevelDesc", typeof(string));
hierarchyTotalTable.Columns.Add("payPeriodFr", typeof(DateTime));
hierarchyTotalTable.Columns.Add("ee", typeof(double));
hierarchyTotalTable.Columns.Add("er", typeof(double));
hierarchyTotalTable.Columns.Add("netAmount", typeof(double));
NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workBook.CreateFont();
boldFont.Boldweight = 700;
NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
reportHeaderStyle.SetFont(boldFont);
reportHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
NPOI.HSSF.UserModel.HSSFCellStyle groupHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
groupHeaderStyle.SetFont(boldFont);
NPOI.HSSF.UserModel.HSSFCellStyle monthHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
monthHeaderStyle.SetFont(boldFont);
monthHeaderStyle.DataFormat = workBook.CreateDataFormat().GetFormat("MMM-yyyy");
monthHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
columnHeaderStyle.SetFont(boldFont);
columnHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle detailNumberStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
detailNumberStyle.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00)");
detailNumberStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
NPOI.HSSF.UserModel.HSSFCellStyle detailTextStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
detailTextStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
subTotalStyle.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00)");
subTotalStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
DataRow[] EmpInfoRows = dataSet.Tables["EmpInfo"].Select("", "Company, " + empInfoTable.Columns[hierarchyLevelGroupingFieldName].ColumnName + ", Employee No.");
string currentCompany = "#$%@$@#$"; //string.Empty;
string currentHierarchyGroup = string.Empty;
string currentEmployeeNo = string.Empty;
string tmpCompany = "";
string tmpHierarchy = "";
string tmpEmployeeNo = "";
ushort groupRowCount = 0;
bool sectionEnded = false;
if (bolShowIndividual == true)
{
summaryStart = Convert.ToUInt16(4 + hierarchyTable.Rows.Count);
}
else
{
summaryStart = 1;
}
reportEndCol = Convert.ToUInt16(summaryStart + (payPeriodTable.Rows.Count * 4) - 1);
foreach (DataRow EmpInfoRow in EmpInfoRows)
{
tmpCompany = EmpInfoRow["Company"].ToString();
tmpEmployeeNo = EmpInfoRow["Employee No."].ToString();
tmpHierarchy = EmpInfoRow[hierarchyLevelGroupingFieldName].ToString();
if (EmpInfoRows[0] == EmpInfoRow)
{
currentHierarchyGroup = tmpHierarchy;
currentCompany = tmpCompany;
rowPos = GenerateHeader(workSheet, PeriodFrom, PeriodTo, rowPos, reportHeaderStyle, groupHeaderStyle);
rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle);
rowPos++;
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
}
sectionEnded = (!currentHierarchyGroup.Equals(tmpHierarchy, StringComparison.CurrentCultureIgnoreCase) ||
!currentCompany.Equals(tmpCompany, StringComparison.CurrentCultureIgnoreCase));
if (sectionEnded)
{
if (bolShowIndividual == true)
{
// print hirarchy total if show employee detail
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
rowPos += 2;
}
else
{
// just print hierarchy line (i.e. not subtotal) if employee detail not shown
ushort colPos = 0;
NPOI.HSSF.UserModel.HSSFRow sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)rowPos);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell((int)colPos);
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue(currentHierarchyGroup);
cell.CellStyle = detailTextStyle;
colPos++;
foreach (DataRow m_hiearchyTotalRow in hierarchyTotalTable.Select("LevelDesc = '" + currentHierarchyGroup + "' ", "payPeriodFr"))
{
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["EE"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["netAmount"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["ER"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")";
cell.CellStyle = detailNumberStyle;
colPos++;
}
rowPos++;
groupRowCount++;
}
if (!currentCompany.Equals(tmpCompany, StringComparison.CurrentCultureIgnoreCase))
{
if (bolShowIndividual == true)
{
// print company header and column header for new company (in show-employee mode)
rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle);
rowPos++;
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
}
else
{
// print a summary for previous company (in hide employee info mode)
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
rowPos += 2;
rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle);
rowPos++;
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
}
currentCompany = tmpCompany;
currentHierarchyGroup = tmpHierarchy;
groupRowCount = 0;
// print company information for new company
}
else if (!currentHierarchyGroup.Equals(tmpHierarchy, StringComparison.CurrentCultureIgnoreCase))
{
if (bolShowIndividual == true)
{
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
groupRowCount = 0;
}
else
{
}
currentHierarchyGroup = tmpHierarchy;
}
hierarchyTotalTable.Clear();
}
if (bolShowIndividual == true)
{
groupRowCount++;
NPOI.HSSF.UserModel.HSSFRow m_sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowPos);
NPOI.HSSF.UserModel.HSSFCell cell;
ushort colPos = 0;
// Employee Number
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Employee No."].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// Employee Name
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Employee Name"].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// Alias
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Alias"].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// Chinese Name
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Chinese Name"].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// hierarchy
foreach (DataRow m_hierarchyRow in hierarchyTable.Rows)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow[m_hierarchyRow["LevelDesc"].ToString()].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
}
foreach (DataRow m_payPeriodRow in payPeriodTable.Rows)
{
//DBFilter m_paymentFilter = new DBFilter();
//m_paymentFilter.add(new Match("payPeriodFr", m_payPeriodRow["payPeriodFr"]));
//m_paymentFilter.add(new Match("EmpID", row["EmpID"]));
double m_er = 0;
double m_ee = 0;
double m_netAmount = 0;
foreach (DataRow m_paymentRow in paymentTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " +
"EmpID=" + EmpInfoRow["EmpID"].ToString()))
{
m_ee += (double)m_paymentRow["EE"];
m_er += (double)m_paymentRow["ER"];
m_netAmount += (double)m_paymentRow["netAmount"];
}
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(m_ee);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(m_netAmount);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(m_er);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")";
cell.CellStyle = detailNumberStyle;
colPos++;
}
rowPos++;
}
else
{
foreach (DataRow m_payPeriodRow in payPeriodTable.Rows)
{
if (tmpCompany == "Magazines International (Asia) Limited - SALES" && tmpHierarchy == "Sales Division")
{
double m_trash = 0;
}
double m_er = 0;
double m_ee = 0;
double m_netAmount = 0;
foreach (DataRow m_paymentRow in paymentTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " +
"EmpID=" + EmpInfoRow["EmpID"].ToString()))
{
m_ee += (double)m_paymentRow["EE"];
m_er += (double)m_paymentRow["ER"];
m_netAmount += (double)m_paymentRow["netAmount"];
}
// find from local HierarchyTotal table and add the new employee amounts
DataRow[] m_hierarchyTotalRows = hierarchyTotalTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " +
// "Company='" + tmpCompany + "' AND " +
"LevelDesc='" + tmpHierarchy + "' ");
if (m_hierarchyTotalRows.Length > 0)
{
m_hierarchyTotalRows[0]["EE"] = (double)m_hierarchyTotalRows[0]["EE"] + m_ee;
m_hierarchyTotalRows[0]["ER"] = (double)m_hierarchyTotalRows[0]["ER"] + m_er;
m_hierarchyTotalRows[0]["netAmount"] = (double)m_hierarchyTotalRows[0]["netAmount"] + m_netAmount;
}
else
{
DataRow m_newHierarchyTotal = hierarchyTotalTable.Rows.Add();
m_newHierarchyTotal["company"] = tmpCompany;
m_newHierarchyTotal["LevelDesc"] = tmpHierarchy;
m_newHierarchyTotal["payPeriodFr"] = (DateTime)m_payPeriodRow["payPeriodFr"];
m_newHierarchyTotal["EE"] = m_ee;
m_newHierarchyTotal["ER"] = m_er;
m_newHierarchyTotal["netAmount"] = m_netAmount;
}
}
}
}
// insert last hierarchy total
if (bolShowIndividual == true)
{
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
}
else
{
NPOI.HSSF.UserModel.HSSFRow m_sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)rowPos);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(0);
ushort colPos = 0;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(tmpHierarchy);
cell.CellStyle = detailTextStyle;
colPos++;
foreach (DataRow m_hiearchyTotalRow in hierarchyTotalTable.Select("LevelDesc = '" + tmpHierarchy + "' ", "payPeriodFr"))
{
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["EE"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["netAmount"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["ER"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")";
cell.CellStyle = detailNumberStyle;
colPos++;
}
groupRowCount++;
rowPos++;
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
}
for (int i = 0; i <= reportEndCol; i++)
{
if (i < summaryStart)
{
workSheet.SetColumnWidth(i, 15 * 254);
}
else
{
workSheet.SetColumnWidth(i, 14 * 254);
}
}
}
private void GenerateExcelReport(DataTable tmpDataTable, string exportFileName)
{
int columnCount = 0;
int lastRowIndex = 0;
// Set column style
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
dateCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("AverageCostCentreExport");
NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
numericStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00");
numericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
// Set column width
worksheet.SetColumnWidth(0, 40 * 256);
worksheet.SetColumnWidth(2, 20 * 256);
worksheet.SetColumnWidth(5, 15 * 256);
worksheet.SetColumnWidth(7, 15 * 256);
worksheet.SetColumnWidth(8, 15 * 256);
worksheet.SetColumnWidth(9, 15 * 256);
worksheet.SetColumnWidth(10, 12 * 256);
// Set column title
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex);
headerRow.CreateCell(0).SetCellValue("Average Cost Centre Export");
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 1);
headerRow.CreateCell(0).SetCellValue(peroid);
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 3);
foreach (DataColumn headercolumn in tmpDataTable.Columns)
{
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount);
cell.SetCellValue(headercolumn.ColumnName);
if (columnCount == 11)
{
cell.CellStyle = style;
}
columnCount++;
}
// Set value for every row
foreach (DataRow row in tmpDataTable.Rows)
{
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 4);
detailRow.CreateCell(0).SetCellValue(row[FIELD_COMPANY].ToString());
detailRow.CreateCell(1).SetCellValue(row[FIELD_DIVISION].ToString());
detailRow.CreateCell(2).SetCellValue(row[FIELD_DEPARTMENT].ToString());
detailRow.CreateCell(3).SetCellValue(row[FIELD_SECTION].ToString());
detailRow.CreateCell(4).SetCellValue(row[FIELD_EMP_NO].ToString());
detailRow.CreateCell(5).SetCellValue(row[FIELD_EMP_NAME].ToString());
detailRow.CreateCell(6).SetCellValue(row[FIELD_ALIAS].ToString());
detailRow.CreateCell(7).SetCellValue(row[FIELD_POSITION].ToString());
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8);
cell.SetCellValue((DateTime)row[FIELD_FROM]);
cell.CellStyle = dateCellStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9);
cell.SetCellValue((DateTime)row[FIELD_TO]);
cell.CellStyle = dateCellStyle;
detailRow.CreateCell(10).SetCellValue(row[FIELD_COST_CENTER].ToString());
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11);
cell.SetCellValue((double)row[FIELD_PERCENTAGE]);
cell.CellStyle = numericStyle;
lastRowIndex++;
}
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
}
protected ushort GenerateColumnHeader(NPOI.HSSF.UserModel.HSSFSheet workSheet, System.Data.DataSet dataSet,
string HierarchyHeader, ushort RowPos,
NPOI.HSSF.UserModel.HSSFCellStyle GroupHeaderStyle,
NPOI.HSSF.UserModel.HSSFCellStyle MonthHeaderStyle,
NPOI.HSSF.UserModel.HSSFCellStyle ColumnHeaderStyle)
{
DataTable paymentTable = dataSet.Tables["payment"];
DataTable hierarchyTable = dataSet.Tables["hierarchy"];
DataTable payPeriodTable = dataSet.Tables["payPeriod"];
NPOI.HSSF.UserModel.HSSFRow monthRow;
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow;
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(0);
//workSheet.Cells.Add((int)RowPos, 1, HierarchyHeader, xf);
monthRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos);
RowPos++;
columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)RowPos);
ushort colPos = 0;
if (bolShowIndividual == true)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("Employee No.");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("English Name");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("Alias");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("Chinese Name");
cell.CellStyle = ColumnHeaderStyle;
foreach (DataRow hierarchyRow in hierarchyTable.Rows)
{
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue(hierarchyRow["LevelDesc"].ToString());
cell.CellStyle = ColumnHeaderStyle;
}
}
else
{
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue(HierarchyHeader);
cell.CellStyle = ColumnHeaderStyle;
}
summaryStart = Convert.ToUInt16(colPos + Convert.ToUInt16(1));
foreach (DataRow payPeriodRow in payPeriodTable.Rows)
{
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)monthRow.CreateCell(colPos);
cell.SetCellValue(DateTime.Parse(payPeriodRow["payPeriodFr"].ToString()).ToString("dd-MM-yyyy"));
cell.CellStyle = MonthHeaderStyle;
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(monthRow.RowNum, monthRow.RowNum, colPos, colPos + 3));
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("MPF EE");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("Net Payment");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("MPF ER");
cell.CellStyle = ColumnHeaderStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colPos);
cell.SetCellValue("Total");
cell.CellStyle = ColumnHeaderStyle;
}
RowPos++;
return(RowPos);
}
public override DataTable UploadToTempDatabase(string Filename, int UserID, string ZipPassword)
{
ClearTempTable();
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(Filename, System.IO.FileMode.Open)); // ExcelLibrary.SpreadSheet.Workbook.Load(Filename);
NPOI.HSSF.UserModel.HSSFSheet workSheet = null;
//foreach (ExcelLibrary.SpreadSheet.Worksheet tmpWorkSheet in workBook.Worksheets)
//{
// if (tmpWorkSheet.Name.Trim().Equals("RosterTable"))
// {
// workSheet = tmpWorkSheet;
// break;
// }
//}
try
{
workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheet("RosterTable");
if (workSheet == null)
{
workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0);
}
}
catch
{
if (workSheet == null)
{
workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(0);
}
}
if (workSheet.GetRow(ROW_YEAR).GetCell(0).StringCellValue.Trim().StartsWith("Year", StringComparison.CurrentCultureIgnoreCase))
{
if (workSheet.GetRow(ROW_YEAR).GetCell(1).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC))
{
m_importYear = Convert.ToInt32(workSheet.GetRow(ROW_YEAR).GetCell(1).NumericCellValue);
}
else
{
errors.addError("Invalid Year", null);
}
}
if (workSheet.GetRow(ROW_MONTH).GetCell(0).StringCellValue.Trim().StartsWith("Month", StringComparison.CurrentCultureIgnoreCase))
{
if (workSheet.GetRow(ROW_MONTH).GetCell(1).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC))
{
m_importMonth = Convert.ToInt32(workSheet.GetRow(ROW_MONTH).GetCell(1).NumericCellValue);
}
else
{
errors.addError("Invalid Month", null);
}
//if (!int.TryParse(workSheet.GetRow(1).GetCell(1).StringCellValue.Trim(), out m_importMonth))
// errors.addError("Invalid Month", null);
}
if (errors.List.Count > 0)
{
throw (new HRImportException(errors.Message()));
}
int intHeaterRow = ROW_CALENDAR_HEADER;
int intEmpColumn = 0;
ArrayList results = new ArrayList();
int rowCount = 1;
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaterRow);
while (intEmpColumn <= headerRow.LastCellNum)
{
if (headerRow.GetCell(intEmpColumn) != null)
{
if (headerRow.GetCell(intEmpColumn).StringCellValue.Trim().Equals(FIELD_EMP_NO, StringComparison.CurrentCultureIgnoreCase))
{
break;
}
}
intEmpColumn++;
}
if (intEmpColumn > headerRow.LastCellNum)
{
// do exception
}
int intEmptyEmpNoCount = 0;
try
{
while (intHeaterRow + rowCount <= workSheet.LastRowNum)
{
int EmpID = 0;
string EmpNo = string.Empty;
int colCount = 0;
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaterRow + rowCount);
if (row == null)
{
rowCount++;
intEmptyEmpNoCount++;
continue;
}
if (row.GetCell(intEmpColumn) == null)
{
rowCount++;
intEmptyEmpNoCount++;
continue;
}
EmpNo = row.GetCell(intEmpColumn).ToString().Trim();
if (string.IsNullOrEmpty(EmpNo))
{
rowCount++;
intEmptyEmpNoCount++;
continue;
}
intEmptyEmpNoCount = 0;
EmpID = Import.Parse.GetEmpID(dbConn, EmpNo, UserID);
if (EmpID < 0)
{
errors.addError(ImportErrorMessage.ERROR_ACCESS_DENIED_EMP_NO, new string[] { EmpNo, rowCount.ToString() });
}
else if (EmpID == 0)
{
errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() });
}
colCount = intEmptyEmpNoCount + 1;
int lastImportDay = 0;
int intCurrentMonth = m_importMonth;
int intCurrentYear = m_importYear;
while (colCount < headerRow.LastCellNum)
{
if (headerRow.GetCell(colCount) == null)
{
if (lastImportDay <= 0)
{
colCount++;
continue;
}
else
{
break;
}
}
if (headerRow.GetCell(colCount).CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC))
{
int day = Convert.ToInt32(headerRow.GetCell(colCount).NumericCellValue);
if (day > 0 && day <= DateTime.DaysInMonth(intCurrentYear, intCurrentMonth))
{
if (lastImportDay > day)
{
intCurrentMonth++;
if (intCurrentMonth > 12)
{
intCurrentMonth = 1;
intCurrentYear++;
}
}
lastImportDay = day;
string RosterCode;
if (row.GetCell(colCount) != null)
{
RosterCode = row.GetCell(colCount).ToString().Trim();
}
else
{
RosterCode = string.Empty;
}
EUploadRosterTable uploadRosterTable = new EUploadRosterTable();
uploadRosterTable.EmpID = EmpID;
uploadRosterTable.RosterTableDate = new DateTime(intCurrentYear, intCurrentMonth, day);
DateTime inTime, outTime;
uploadRosterTable.RosterCodeID = Import.Parse.GetRosterCodeID(dbConn, RosterCode, out inTime, out outTime);
if (!inTime.Ticks.Equals(0))
{
uploadRosterTable.RosterTableOverrideInTime = inTime;
}
if (!outTime.Ticks.Equals(0))
{
uploadRosterTable.RosterTableOverrideOutTime = outTime;
}
uploadRosterTable.SessionID = m_SessionID;
uploadRosterTable.TransactionDate = UploadDateTime;
uploadRosterTable.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE;
if (uploadRosterTable.RosterCodeID <= 0 && !string.IsNullOrEmpty(RosterCode))
{
errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { RosterCode, EmpNo, rowCount.ToString() });
}
else
{
EUploadRosterTable.db.insert(dbConn, uploadRosterTable);
}
}
}
colCount++;
}
rowCount++;
}
}
catch (Exception e)
{
errors.addError(e.Message, null);
}
if (errors.List.Count > 0)
{
ClearTempTable();
throw (new HRImportException(errors.Message()));
}
return(GetImportDataFromTempDatabase(null));
//org.in2bits.MyXls.XlsDocument xlsDoc = new org.in2bits.MyXls.XlsDocument(Filename);
//org.in2bits.MyXls.Worksheet workSheet;
//try
//{
// workSheet = xlsDoc.Workbook.Worksheets["RosterTable"];
//}
//catch
//{
// workSheet = xlsDoc.Workbook.Worksheets[0];
//}
//if (workSheet.Rows[1].GetCell(1).Value.ToString().Trim().Equals("Year", StringComparison.CurrentCultureIgnoreCase))
//{
// if (!int.TryParse(workSheet.Rows[1].CellAtCol(2).Value.ToString().Trim(), out m_importYear))
// errors.addError("Invalid Year", null);
//}
//if (workSheet.Rows[2].GetCell(1).Value.ToString().Trim().Equals("Month", StringComparison.CurrentCultureIgnoreCase))
//{
// if (!int.TryParse(workSheet.Rows[2].CellAtCol(2).Value.ToString().Trim(), out m_importMonth))
// errors.addError("Invalid Month", null);
//}
//if (errors.List.Count > 0)
//{
// throw (new HRImportException(errors.Message()));
//}
//ushort intHeaderRow = 3;
//ArrayList results = new ArrayList();
//ushort rowCount = 1;
//try
//{
// while (intHeaderRow + rowCount <= workSheet.Rows.MaxRow)
// {
// int EmpID = 0;
// string EmpNo = string.Empty;
// ushort colCount = 1;
// org.in2bits.MyXls.Row row = workSheet.Rows[(ushort)(intHeaderRow + rowCount)];
// while (colCount <= row.MaxCellCol)
// {
// if (workSheet.Rows[intHeaderRow].CellAtCol(colCount).Value.ToString().Trim().Equals(FIELD_EMP_NO, StringComparison.CurrentCultureIgnoreCase))
// {
// EmpNo = row.CellAtCol(colCount).Value.ToString().Trim();
// EmpID = Import.Parse.GetEmpID(dbConn, EmpNo, UserID);
// if (EmpID <= 0)
// errors.addError(ImportErrorMessage.ERROR_INVALID_EMP_NO, new string[] { EmpNo, rowCount.ToString() });
// break;
// }
// colCount++;
// }
// colCount = 1;
// while (colCount <= workSheet.Rows[(ushort)(intHeaderRow + rowCount)].MaxCellCol)
// {
// if (workSheet.Cells .Rows[(ushort)(intHeaderRow)].CellExists(colCount))
// {
// string RosterCode = string.Empty;
// if (workSheet.Rows[(ushort)(intHeaderRow + rowCount)].CellExists(colCount))
// if (workSheet.Rows[(ushort)(intHeaderRow + rowCount)].CellAtCol(colCount).Value != null)
// RosterCode = row.CellAtCol(colCount).Value.ToString().Trim();
// int day = 0;
// if (int.TryParse(workSheet.Rows[intHeaderRow].GetCell(colCount).Value.ToString().Trim(), out day))
// {
// if (day > 0 && day <= DateTime.DaysInMonth(m_importYear, m_importMonth))
// {
// EUploadRosterTable uploadRosterTable = new EUploadRosterTable();
// uploadRosterTable.EmpID = EmpID;
// uploadRosterTable.RosterTableDate = new DateTime(m_importYear, m_importMonth, day);
// uploadRosterTable.RosterCodeID = Import.Parse.*ID(dbConn, RosterCode);
// uploadRosterTable.SessionID = m_SessionID;
// uploadRosterTable.TransactionDate = UploadDateTime;
// uploadRosterTable.ImportActionStatus = ImportDBObject.ImportActionEnum.UPDATE;
// if (uploadRosterTable.RosterCodeID <= 0 && !string.IsNullOrEmpty(RosterCode))
// {
// errors.addError(ImportErrorMessage.ERROR_INVALID_FIELD_VALUE, new string[] { RosterCode, EmpNo, rowCount.ToString() });
// }
// else
// EUploadRosterTable.db.insert(dbConn, uploadRosterTable);
// }
// }
// }
// colCount++;
// }
// rowCount++;
// }
//}
//catch (Exception e)
//{
// errors.addError(e.Message, null);
//}
//if (errors.List.Count > 0)
//{
// ClearTempTable();
// throw (new HRImportException(errors.Message()));
//}
//return GetImportDataFromTempDatabase(null);
}
public ActionResult ExportMsg()
{
List <String> list = new List <String>();
List <String> listName = new List <String>();
#region 初始化数据
//得到选中项 这是比较笨的方法，有机会要改进哈
if (Request.Form["StuNum"] != null)
{
list.Add("StuNum"); listName.Add("学号");
}
if (Request.Form["StuName"] != null)
{
list.Add("StuName"); listName.Add("姓名");
}
if (Request.Form["Gender"] != null)
{
list.Add("Gender"); listName.Add("性别");
}
if (Request.Form["QQNum"] != null)
{
list.Add("QQNum"); listName.Add("QQ号");
}
if (Request.Form["Email"] != null)
{
list.Add("Email"); listName.Add("电子邮箱");
}
if (Request.Form["Birthday"] != null)
{
list.Add("Birthday"); listName.Add("生日");
}
if (Request.Form["Class"] != null)
{
list.Add("Class"); listName.Add("班级");
}
if (Request.Form["TelephoneNumber"] != null)
{
list.Add("TelephoneNumber"); listName.Add("电话号码");
}
if (Request.Form["Department"] != null)
{
list.Add("Department"); listName.Add("部门");
}
if (Request.Form["TechnicalGuideNumber"] != null)
{
list.Add("TechnicalGuideNumber"); listName.Add("技术指导号");
}
if (Request.Form["JoinTime"] != null)
{
list.Add("JoinTime"); listName.Add("加入时间");
}
if (Request.Form["Major"] != null)
{
list.Add("Major"); listName.Add("主修");
}
if (Request.Form["Counseloer"] != null)
{
list.Add("Counseloer"); listName.Add("辅导员");
}
if (Request.Form["HeadTeacher"] != null)
{
list.Add("HeadTeacher"); listName.Add("班主任");
}
if (Request.Form["UndergraduateTutor"] != null)
{
list.Add("UndergraduateTutor"); listName.Add("毕业导师");
}
if (Request.Form["HomPhoneNumber"] != null)
{
list.Add("HomPhoneNumber"); listName.Add("家庭电话");
}
if (Request.Form["FamilyAddress"] != null)
{
list.Add("FamilyAddress"); listName.Add("家庭地址");
}
if (Request.Form["Sign"] != null)
{
list.Add("Sign"); listName.Add("个性签名");
}
int depart = 0;
if (!string.IsNullOrEmpty((Request.Form["depart"])))
{
depart = Convert.ToInt32(Request.Form["depart"]);
}
if (list.Count == 0)
{
return(Content("<script>alert('您没有选择任何项喔~~！');window.location='/PersonalManger/CheckMember/index'</script>"));
}
DataTable dt = OperateContext.Current.BLLSession.IMemberInformationBLL.GetPartData(list, depart);
//如果选择了部门那么导出时要将数字转为部门名
int count = 0;
if (Request.Form["Department"] != null)
{
for (int i = 0; i < list.Count; i++)
{
if (list[i] == "Department")
{
count = i;
}
}
}
#endregion
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet sheet = workbook.CreateSheet("第一页") as NPOI.HSSF.UserModel.HSSFSheet;
NPOI.HSSF.UserModel.HSSFRow hr = sheet.CreateRow(0) as NPOI.HSSF.UserModel.HSSFRow;
for (int i = 0; i < listName.Count; i++)
{
hr.CreateCell(i).SetCellValue(listName[i]);
}
for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
NPOI.HSSF.UserModel.HSSFRow hro = sheet.CreateRow(rowIndex + 1) as NPOI.HSSF.UserModel.HSSFRow;
for (int colIndex = 0; colIndex < listName.Count; colIndex++)
{
string str = "";
if (colIndex == count && count != 0)
{
if (dt.Rows[rowIndex][colIndex].ToString() == "10001")
{
str = "NET应用开发部";
}
if (dt.Rows[rowIndex][colIndex].ToString() == "10002")
{
str = "安卓应用开发部";
}
if (dt.Rows[rowIndex][colIndex].ToString() == "10003")
{
str = "硬件编程技术部";
}
if (dt.Rows[rowIndex][colIndex].ToString() == "10004")
{
str = "系统编程技术部";
}
if (dt.Rows[rowIndex][colIndex].ToString() == "10007")
{
str = "暂未加入部门";
}
hro.CreateCell(colIndex).SetCellValue(str);
}
else
{
hro.CreateCell(colIndex).SetCellValue(dt.Rows[rowIndex][colIndex].ToString());
}
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel"));
}
