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 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; } } }
public static void ChangeFormat(DataColumn column, string drValue, HSSFCell newCell, HSSFCellStyle contentDateStyle) { switch (column.DataType.ToString()) { case "System.String": //字符串类型 string result = drValue; newCell.SetCellValue(result); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = contentDateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } }
/* package */ static void SetCellValue(HSSFCell cell, String text) { cell.SetCellValue(new HSSFRichTextString(text)); }
/// <summary> /// Looks for text in the cell that should be unicode, like alpha; and provides the /// unicode version of it. /// </summary> /// <param name="cell">The cell to check for unicode values</param> /// <returns>transalted to unicode</returns> public static HSSFCell TranslateUnicodeValues(HSSFCell cell) { String s = cell.RichStringCellValue.String; bool foundUnicode = false; String lowerCaseStr = s.ToLower(); for (int i = 0; i < unicodeMappings.Length; i++) { UnicodeMapping entry = unicodeMappings[i]; String key = entry.entityName; if (lowerCaseStr.IndexOf(key) != -1) { s = s.Replace(key, entry.resolvedValue); foundUnicode = true; } } if (foundUnicode) { cell.SetCellValue(new HSSFRichTextString(s)); } return cell; }
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; }
protected void WriteCellTypeValue(string Value, HSSFCell cell, bool hasStyle) { cell.SetCellValue(Value); }
protected void WriteCellTypeValue(DateTime Value, HSSFCell cell, bool hasStyle) { cell.SetCellType(CellType.NUMERIC); if(!hasStyle) cell.CellStyle = _dateStyle; cell.SetCellValue(Value); }
private NPOI.HSSF.UserModel.HSSFSheet CreateWorkSheet(NPOI.HSSF.UserModel.HSSFWorkbook workbook, DataTable dataTable) { if (workbook != null) { NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet(dataTable.TableName.Replace("$", "")); //NPOI.HSSF.UserModel.HSSFRow chineseHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0); NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0); int columnCount = 0; //System.Globalization.CultureInfo chineseCI = new System.Globalization.CultureInfo("zh-cht"); foreach (DataColumn headercolumn in dataTable.Columns) { headercolumn.ColumnName = headercolumn.ColumnName.Trim(); if (!headercolumn.ColumnName.EndsWith("TimeCardRecordID", StringComparison.CurrentCultureIgnoreCase)) { string columnName = headercolumn.ColumnName; //string chineseColumnName = HROne.Common.WebUtility.GetLocalizedString(columnName, chineseCI); //if (columnName.Equals(chineseColumnName)) // chineseColumnName = string.Empty; 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(columnName); //cell = (NPOI.HSSF.UserModel.HSSFCell)chineseHeaderRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty)); //cell.SetCellValue(chineseColumnName); //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.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat(); NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont(); boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; //900; NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); NPOI.HSSF.UserModel.HSSFCellStyle ManualAdjustCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); //ManualInputDateCellStyle.CloneStyleFrom(dateCellStyle); ManualAdjustCellStyle.SetFont(boldFont); //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) { if (!column.ColumnName.EndsWith("TimeCardRecordID", StringComparison.CurrentCultureIgnoreCase)) { //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()); // Override style to bold if manual adjust if (cell.StringCellValue != string.Empty) { if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN)) { ETimeCardRecord timeCard = new ETimeCardRecord(); try { timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_TIMECARDID]; } catch { } if (ETimeCardRecord.db.select(dbConn, timeCard)) { if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue)) { cell.CellStyle = ManualAdjustCellStyle; } } else { cell.CellStyle = ManualAdjustCellStyle; } } else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT)) { ETimeCardRecord timeCard = new ETimeCardRecord(); try { timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_TIMECARDID]; } catch { } if (ETimeCardRecord.db.select(dbConn, timeCard)) { if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue)) { cell.CellStyle = ManualAdjustCellStyle; } } else { cell.CellStyle = ManualAdjustCellStyle; } } else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT)) { ETimeCardRecord timeCard = new ETimeCardRecord(); try { timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_TIMECARDID]; } catch { } if (ETimeCardRecord.db.select(dbConn, timeCard)) { if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue)) { cell.CellStyle = ManualAdjustCellStyle; } } else { cell.CellStyle = ManualAdjustCellStyle; } } else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN)) { ETimeCardRecord timeCard = new ETimeCardRecord(); try { timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_TIMECARDID]; } catch { } if (ETimeCardRecord.db.select(dbConn, timeCard)) { if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue)) { cell.CellStyle = ManualAdjustCellStyle; } } else { cell.CellStyle = ManualAdjustCellStyle; } } else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_LOCATION)) { ETimeCardRecord timeCard = new ETimeCardRecord(); try { timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_TIMECARDID]; } catch { } if (ETimeCardRecord.db.select(dbConn, timeCard)) { if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue)) { cell.CellStyle = ManualAdjustCellStyle; } } else { cell.CellStyle = ManualAdjustCellStyle; } } else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_LOCATION)) { ETimeCardRecord timeCard = new ETimeCardRecord(); try { timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_TIMECARDID]; } catch { } if (ETimeCardRecord.db.select(dbConn, timeCard)) { if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue)) { cell.CellStyle = ManualAdjustCellStyle; } } else { cell.CellStyle = ManualAdjustCellStyle; } } else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_LOCATION)) { ETimeCardRecord timeCard = new ETimeCardRecord(); try { timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_TIMECARDID]; } catch { } if (ETimeCardRecord.db.select(dbConn, timeCard)) { if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue)) { cell.CellStyle = ManualAdjustCellStyle; } } else { cell.CellStyle = ManualAdjustCellStyle; } } else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_LOCATION)) { ETimeCardRecord timeCard = new ETimeCardRecord(); try { timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_TIMECARDID]; } catch { } if (ETimeCardRecord.db.select(dbConn, timeCard)) { if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue)) { cell.CellStyle = ManualAdjustCellStyle; } } else { cell.CellStyle = ManualAdjustCellStyle; } } } } 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); } }
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 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"); } }
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 static void SetCellValue(HSSFCell cell, CellValue cv) { int cellType = cv.CellType; switch (cellType) { case HSSFCell.CELL_TYPE_BOOLEAN: cell.SetCellValue(cv.BooleanValue); break; case HSSFCell.CELL_TYPE_ERROR: cell.SetCellErrorValue((byte)cv.ErrorValue); break; case HSSFCell.CELL_TYPE_NUMERIC: cell.SetCellValue(cv.NumberValue); break; case HSSFCell.CELL_TYPE_STRING: cell.SetCellValue(cv.RichTextStringValue); break; //case HSSFCell.CELL_TYPE_BLANK: //// never happens - blanks eventually get translated to zero //case HSSFCell.CELL_TYPE_FORMULA: //// this will never happen, we have already evaluated the formula default: throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")"); } }
private void GenerateRosterTableData(ArrayList EmpInfoList, int year, int month) { DateTime dateStart = new DateTime(year, month, 1); DateTime dateEnd = new DateTime(year, month, DateTime.DaysInMonth(year, month)); string exportFileName = System.IO.Path.GetTempFileName(); System.IO.File.Delete(exportFileName); exportFileName += ".xls"; const string FIELD_EMP_NO = "Emp. No"; const int COLUMN_HEADER_ROW = 2; NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.CreateSheet("RosterTable"); NPOI.HSSF.UserModel.HSSFCellStyle upperLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); upperLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle bottomLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); bottomLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle leftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); leftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle rightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); rightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle upperLeftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); upperLeftLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; upperLeftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle bottomLeftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); bottomLeftLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; bottomLeftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle upperRightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); upperRightLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; upperRightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; NPOI.HSSF.UserModel.HSSFCellStyle bottomRightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); bottomRightLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; bottomRightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; workSheet.CreateRow(0).CreateCell(0).SetCellValue("Year"); workSheet.GetRow(0).CreateCell(1).SetCellValue(year); workSheet.CreateRow(1).CreateCell(0).SetCellValue("Month"); workSheet.GetRow(1).CreateCell(1).SetCellValue(month); NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW); DBFilter hLevelFilter = new DBFilter(); hLevelFilter.add("HLevelSeqNo", true); int HIERARCHYLEVEL_COLUMN = 0; ArrayList hLevelList = EHierarchyLevel.db.select(dbConn, hLevelFilter); for (int levelIndex = 0; levelIndex < hLevelList.Count; levelIndex++) { EHierarchyLevel hLevel = (EHierarchyLevel)hLevelList[levelIndex]; headerRow.CreateCell(HIERARCHYLEVEL_COLUMN + levelIndex).SetCellValue(hLevel.HLevelDesc); } int POSITION_COLUMN = headerRow.LastCellNum; headerRow.CreateCell(POSITION_COLUMN).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Position")); int EMPNO_COLUMN = headerRow.LastCellNum; headerRow.CreateCell(EMPNO_COLUMN).SetCellValue(FIELD_EMP_NO); headerRow.CreateCell(EMPNO_COLUMN + 1).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Name")); headerRow.CreateCell(EMPNO_COLUMN + 2).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Alias")); headerRow.CreateCell(EMPNO_COLUMN + 3).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Chinese Name")); NPOI.HSSF.UserModel.HSSFCellStyle sundayStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); NPOI.HSSF.UserModel.HSSFFont sundayFont = (NPOI.HSSF.UserModel.HSSFFont)workBook.CreateFont(); sundayFont.Color = NPOI.HSSF.Util.HSSFColor.RED.index; sundayStyle.SetFont(sundayFont); Hashtable styleList = new Hashtable(); ArrayList availableRosterClientList = new ArrayList(); ArrayList availableRosterClientSiteList = new ArrayList(); #region Create Column Header int ROSTER_DETAIL_COLUMN = headerRow.LastCellNum; for (int i = 1; i <= DateTime.DaysInMonth(year, month); i++) { //workSheet.Cells.Add(HEADER_ROW, ROSTAER_DETAIL_COLUMN + i - 1,i); NPOI.HSSF.UserModel.HSSFCell headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(ROSTER_DETAIL_COLUMN + i - 1); headerCell.SetCellValue(i); if (new DateTime(year, month, i).DayOfWeek == DayOfWeek.Sunday) { headerCell.CellStyle = sundayStyle; } } #endregion #region Create Employee Roster Detail int recordCount = 0; foreach (EEmpPersonalInfo empInfo in EmpInfoList) { if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { recordCount++; //workSheet.Cells.Add(HEADER_ROW + recordCount, 1,empInfo.EmpNo); //workSheet.Cells.Add(HEADER_ROW + recordCount, 2,empInfo.EmpEngFullName); NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW + recordCount); EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, dateEnd, empInfo.EmpID); if (empPos != null) { for (int levelIndex = 0; levelIndex < hLevelList.Count; levelIndex++) { EHierarchyLevel hLevel = (EHierarchyLevel)hLevelList[levelIndex]; DBFilter empHierarchyFilter = new DBFilter(); empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID)); empHierarchyFilter.add(new Match("HLevelID", hLevel.HLevelID)); ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter); if (empHierarchyList.Count > 0) { EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0]; EHierarchyElement hElement = new EHierarchyElement(); hElement.HElementID = empHierarchy.HElementID; if (EHierarchyElement.db.select(dbConn, hElement)) { detailRow.CreateCell(HIERARCHYLEVEL_COLUMN + levelIndex).SetCellValue(hElement.HElementDesc); } } } EPosition position = new EPosition(); position.PositionID = empPos.PositionID; if (EPosition.db.select(dbConn, position)) { detailRow.CreateCell(POSITION_COLUMN).SetCellValue(position.PositionDesc); } } detailRow.CreateCell(EMPNO_COLUMN).SetCellValue(empInfo.EmpNo); detailRow.CreateCell(EMPNO_COLUMN + 1).SetCellValue(empInfo.EmpEngFullName); detailRow.CreateCell(EMPNO_COLUMN + 2).SetCellValue(empInfo.EmpAlias); detailRow.CreateCell(EMPNO_COLUMN + 3).SetCellValue(empInfo.EmpChiFullName); DBFilter rosterTableFilter = new DBFilter(); rosterTableFilter.add(new Match("EmpID", empInfo.EmpID)); rosterTableFilter.add(new Match("RosterTableDate", ">=", dateStart)); rosterTableFilter.add(new Match("RosterTableDate", "<=", dateEnd)); ArrayList rosterTableList = ERosterTable.db.select(dbConn, rosterTableFilter); foreach (ERosterTable rosterTable in rosterTableList) { ERosterCode rosterCode = new ERosterCode(); rosterCode.RosterCodeID = rosterTable.RosterCodeID; if (ERosterCode.db.select(dbConn, rosterCode)) { string value = string.Empty; //if (workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellExists ((ushort)(ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1)) ) // value = workSheet.Rows[(ushort)(HEADER_ROW+ recordCount)].CellAtCol( (ushort)(ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1)).Value.ToString(); //if (string.IsNullOrEmpty(value)) // workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1,rosterCode.RosterCode); //else // workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1,value + "|" + rosterCode.RosterCode); int cellColIndex = ROSTER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1; NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.GetCell(cellColIndex); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(cellColIndex); } else { value = rosterCell.StringCellValue; } string rosterCodeValue = rosterCode.RosterCode; if (!rosterTable.RosterTableOverrideInTime.Ticks.Equals(0) || !rosterTable.RosterTableOverrideOutTime.Ticks.Equals(0)) { DateTime inTime = rosterCode.RosterCodeInTime; DateTime outTime = rosterCode.RosterCodeOutTime; if (!rosterTable.RosterTableOverrideInTime.Ticks.Equals(0)) { inTime = rosterTable.RosterTableOverrideInTime; } if (!rosterTable.RosterTableOverrideOutTime.Ticks.Equals(0)) { outTime = rosterTable.RosterTableOverrideOutTime; } rosterCodeValue += "(" + inTime.ToString("HHmm") + "~" + outTime.ToString("HHmm") + ")"; } if (string.IsNullOrEmpty(value)) { rosterCell.SetCellValue(rosterCodeValue); //if (!string.IsNullOrEmpty(rosterCode.RosterCodeColorCode)) //{ // //System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(rosterCode.RosterCodeColorCode); // //System.Drawing.Color fontcolor = WebUtils.ComputeTextColor(color); // //rosterCell.CellStyle.FillForegroundColor = workBook.GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).GetIndex(); // //rosterCell.CellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; // //rosterCell.CellStyle.FillBackgroundColor = workBook.GetCustomPalette().FindSimilarColor(fontcolor.R, fontcolor.G, fontcolor.B).GetIndex(); // string styleCode = "RosterCode" + "_" + rosterCode.RosterCode; // if (styleList.Contains(styleCode)) // rosterCell.CellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)styleList[styleCode]; // else // { // NPOI.HSSF.UserModel.HSSFCellStyle rosterCodeStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle(); // System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(rosterCode.RosterCodeColorCode); // System.Drawing.Color fontcolor = WebUtils.ComputeTextColor(color); // rosterCodeStyle.FillForegroundColor = workBook.GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).GetIndex(); // rosterCodeStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; // rosterCodeStyle.FillBackgroundColor = workBook.GetCustomPalette().FindSimilarColor(fontcolor.R, fontcolor.G, fontcolor.B).GetIndex(); // styleList.Add(styleCode, rosterCodeStyle); // rosterCell.CellStyle = rosterCodeStyle; // } //} } else { rosterCell.SetCellValue(value + "|" + rosterCodeValue); //rosterCell.CellStyle=workBook.GetCellStyleAt(0); } } } for (DateTime dateIndex = dateStart; dateIndex <= dateEnd; dateIndex = dateIndex.AddDays(1)) { string value = string.Empty; //if (workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellExists((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)) ) // value = workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellAtCol((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)).Value.ToString(); int cellColIndex = ROSTER_DETAIL_COLUMN + dateIndex.Day - 1; NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.GetCell(cellColIndex); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(cellColIndex); } else { value = rosterCell.StringCellValue; } if (string.IsNullOrEmpty(value)) { EEmpPositionInfo empPosInfo = AppUtils.GetLastPositionInfo(dbConn, dateIndex, empInfo.EmpID); if (empPosInfo != null) { if ((empPosInfo.EmpPosEffTo.Ticks.Equals(0) || dateIndex <= empPosInfo.EmpPosEffTo)) { EWorkHourPattern workPattern = new EWorkHourPattern(); workPattern.WorkHourPatternID = empPosInfo.WorkHourPatternID; if (EWorkHourPattern.db.select(dbConn, workPattern)) { ERosterCode rosterCode = new ERosterCode(); rosterCode.RosterCodeID = workPattern.GetDefaultRosterCodeID(dbConn, dateIndex); if (ERosterCode.db.select(dbConn, rosterCode)) { //workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1, rosterCode.RosterCode); rosterCell.SetCellValue(rosterCode.RosterCode); } } DBFilter empRosterTableGroupListFilter = new DBFilter(); empRosterTableGroupListFilter.add(new Match("EmpID", empInfo.EmpID)); empRosterTableGroupListFilter.add(new Match("empRosterTableGroupEffFr", "<=", dateIndex)); OR orEmpPosEffToTerms = new OR(); orEmpPosEffToTerms.add(new Match("empRosterTableGroupEffTo", ">=", dateIndex)); orEmpPosEffToTerms.add(new NullTerm("empRosterTableGroupEffTo")); empRosterTableGroupListFilter.add(orEmpPosEffToTerms); ArrayList empRosterTableGroupList = EEmpRosterTableGroup.db.select(dbConn, empRosterTableGroupListFilter); foreach (EEmpRosterTableGroup empRosterTableGroup in empRosterTableGroupList) { ERosterTableGroup rosterTableGroup = new ERosterTableGroup(); rosterTableGroup.RosterTableGroupID = empRosterTableGroup.RosterTableGroupID; if (ERosterTableGroup.db.select(dbConn, rosterTableGroup)) { if (rosterTableGroup.RosterClientSiteID > 0) { if (!availableRosterClientSiteList.Contains(rosterTableGroup.RosterClientSiteID)) { availableRosterClientSiteList.Add(rosterTableGroup.RosterClientSiteID); } } else if (rosterTableGroup.RosterClientID > 0) { if (!availableRosterClientList.Contains(rosterTableGroup.RosterClientID)) { availableRosterClientList.Add(rosterTableGroup.RosterClientID); } } } } } } } } DBFilter leaveAppFilter = new DBFilter(); leaveAppFilter.add(new Match("EmpID", empInfo.EmpID)); leaveAppFilter.add(new Match("LeaveAppDateTo", ">=", dateStart)); leaveAppFilter.add(new Match("LeaveAppDateFrom", "<=", dateEnd)); ArrayList leaveAppList = ELeaveApplication.db.select(dbConn, leaveAppFilter); foreach (ELeaveApplication leaveApp in leaveAppList) { ELeaveCode leaveCode = new ELeaveCode(); leaveCode.LeaveCodeID = leaveApp.LeaveCodeID; if (ELeaveCode.db.select(dbConn, leaveCode)) { //if (leaveCode.LeaveCodeColorCode.Length == 6) //{ // try // { // int red = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(0, 2), System.Globalization.NumberStyles.AllowHexSpecifier); // int green = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(2, 2), System.Globalization.NumberStyles.AllowHexSpecifier); // int blue = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(4, 2), System.Globalization.NumberStyles.AllowHexSpecifier); // string Color = System.Drawing.Color.FromArgb(red, green, blue).ToKnownColor().ToString(); // for (DateTime dateIndex = leaveApp.LeaveAppDateFrom; dateIndex <= leaveApp.LeaveAppDateTo; dateIndex = dateIndex.AddDays(1)) // { // org.in2bits.MyXls.Cell cell = workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellAtCol((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)); // //cell.Pattern = 1; // //cell.PatternColor = org.in2bits.MyXls.Colors.Yellow; // } // } // catch // { // } //} } } } } #endregion #region Create Roster Code Liet OR orRosterCodeTerm = new OR(); foreach (int rosterClientID in availableRosterClientList) { AND andRosterCodeTerms = new AND(); orRosterCodeTerm.add(new Match("RosterClientID", rosterClientID)); } foreach (int rosterClientSiteID in availableRosterClientSiteList) { AND andRosterCodeTerms = new AND(); orRosterCodeTerm.add(new Match("RosterClientSiteID", rosterClientSiteID)); } orRosterCodeTerm.add(new Match("RosterClientID", 0)); DBFilter rosterCodeListFilter = new DBFilter(); rosterCodeListFilter.add(orRosterCodeTerm); rosterCodeListFilter.add("RosterCode", true); ArrayList rosterCodeList = ERosterCode.db.select(dbConn, rosterCodeListFilter); int ROSTER_CODE_START_ROW = COLUMN_HEADER_ROW + recordCount + 5; int rosterCodeCount = 0; int maxColumnCount = 3; int columnCellWidth = 9; int maxRowCount = (int)(rosterCodeList.Count / maxColumnCount) + (rosterCodeList.Count % maxColumnCount == 0 ? 0 : 1); foreach (ERosterCode rosterCode in rosterCodeList) { int currentRowNum = rosterCodeCount % maxRowCount; int currentColumnNum = (rosterCodeCount / maxRowCount) * columnCellWidth; rosterCodeCount++; NPOI.HSSF.UserModel.HSSFRow rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(ROSTER_CODE_START_ROW + currentRowNum); if (rosterCodeRow == null) { rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(ROSTER_CODE_START_ROW + currentRowNum); } NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN + currentColumnNum); rosterCell.SetCellValue(rosterCode.RosterCode); rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN + currentColumnNum + 1); rosterCell.SetCellValue(rosterCode.RosterCodeDesc); if (rosterCodeCount.Equals(1)) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN - 1); rosterCell.SetCellValue("Code:"); } } for (int rowIdx = ROSTER_CODE_START_ROW - 1; rowIdx < ROSTER_CODE_START_ROW + maxRowCount + 1; rowIdx++) { NPOI.HSSF.UserModel.HSSFRow rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(rowIdx); if (rosterCodeRow == null) { rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowIdx); } if (rowIdx == ROSTER_CODE_START_ROW - 1) { for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++) { NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx); } if (colIdx == ROSTER_DETAIL_COLUMN - 1) { rosterCell.CellStyle = upperLeftLineStyle; } else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1) { rosterCell.CellStyle = upperRightLineStyle; } else { rosterCell.CellStyle = upperLineStyle; } } } else if (rowIdx == ROSTER_CODE_START_ROW + maxRowCount) { for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++) { NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx); } if (colIdx == ROSTER_DETAIL_COLUMN - 1) { rosterCell.CellStyle = bottomLeftLineStyle; } else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1) { rosterCell.CellStyle = bottomRightLineStyle; } else { rosterCell.CellStyle = bottomLineStyle; } } } else { for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++) { NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx); if (rosterCell == null) { rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx); } if (colIdx == ROSTER_DETAIL_COLUMN - 1) { rosterCell.CellStyle = leftLineStyle; } else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1) { rosterCell.CellStyle = rightLineStyle; } //else // rosterCell.CellStyle = bottomLineStyle; } } } #endregion //doc.FileName = exportFileName; //doc.Save(); System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create); workBook.Write(file); file.Close(); WebUtils.TransmitFile(Response, exportFileName, "RosterTable_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true); Response.End(); }
private static void CopyCell(HSSFCell oldCell, HSSFCell newCell, IDictionary<Int32, HSSFCellStyle> styleMap, Dictionary<short, short> paletteMap, Boolean keepFormulas) { if (styleMap != null) { if (oldCell.CellStyle != null) { if (oldCell.Sheet.Workbook == newCell.Sheet.Workbook) { newCell.CellStyle = oldCell.CellStyle; } else { int styleHashCode = oldCell.CellStyle.GetHashCode(); if (styleMap.ContainsKey(styleHashCode)) { newCell.CellStyle = styleMap[styleHashCode]; } else { HSSFCellStyle newCellStyle = (HSSFCellStyle)newCell.Sheet.Workbook.CreateCellStyle(); newCellStyle.CloneStyleFrom(oldCell.CellStyle); RemapCellStyle(newCellStyle, paletteMap); //Clone copies as-is, we need to remap colors manually newCell.CellStyle = newCellStyle; //Clone of cell style always clones the font. This makes my life easier IFont theFont = newCellStyle.GetFont(newCell.Sheet.Workbook); if (theFont.Color > 0 && paletteMap.ContainsKey(theFont.Color)) { theFont.Color = paletteMap[theFont.Color]; //Remap font color } styleMap.Add(styleHashCode, newCellStyle); } } } else { newCell.CellStyle = null; } } switch (oldCell.CellType) { case NPOI.SS.UserModel.CellType.STRING: newCell.SetCellValue(oldCell.StringCellValue); break; case NPOI.SS.UserModel.CellType.NUMERIC: newCell.SetCellValue(oldCell.NumericCellValue); break; case NPOI.SS.UserModel.CellType.BLANK: newCell.SetCellType(NPOI.SS.UserModel.CellType.BLANK); break; case NPOI.SS.UserModel.CellType.BOOLEAN: newCell.SetCellValue(oldCell.BooleanCellValue); break; case NPOI.SS.UserModel.CellType.ERROR: newCell.SetCellValue(oldCell.ErrorCellValue); break; case NPOI.SS.UserModel.CellType.FORMULA: if (keepFormulas) { newCell.SetCellType(CellType.FORMULA); newCell.CellFormula = oldCell.CellFormula; } else { try { newCell.SetCellType(CellType.NUMERIC); newCell.SetCellValue(oldCell.NumericCellValue); } catch (Exception ex) { try { newCell.SetCellType(CellType.STRING); newCell.SetCellValue(oldCell.StringCellValue); } catch (Exception exInner) { } } } break; default: break; } }
protected void WriteCellTypeValue(double Value, HSSFCell cell, bool hasStyle) { cell.SetCellType(CellType.NUMERIC); cell.SetCellValue(Value); }
static void FillContent(DataRow row, DataColumn column, HSSFRow xlsRow, HSSFCell newCell) { string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 string result = drValue; newCell.SetCellValue(result); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } }
public static void CopyCell(HSSFCell oldCell, HSSFCell newCell, IDictionary<Int32, HSSFCellStyle> styleMap, Dictionary<short, short> paletteMap, Boolean keepFormulas) { if (styleMap != null) { if (oldCell.CellStyle != null) { if (oldCell.Sheet.Workbook == newCell.Sheet.Workbook) { newCell.CellStyle = oldCell.CellStyle; } else { int styleHashCode = oldCell.CellStyle.GetHashCode(); if (styleMap.ContainsKey(styleHashCode)) { newCell.CellStyle = styleMap[styleHashCode]; } else { HSSFCellStyle newCellStyle = (HSSFCellStyle)newCell.Sheet.Workbook.CreateCellStyle(); newCellStyle.CloneStyleFrom(oldCell.CellStyle); RemapCellStyle(newCellStyle, paletteMap); //Clone copies as-is, we need to remap colors manually newCell.CellStyle = newCellStyle; //Clone of cell style always clones the font. This makes my life easier IFont theFont = newCellStyle.GetFont(newCell.Sheet.Workbook); if (theFont.Color > 0 && paletteMap.ContainsKey(theFont.Color)) { theFont.Color = paletteMap[theFont.Color]; //Remap font color } styleMap.Add(styleHashCode, newCellStyle); } } } else { newCell.CellStyle = null; } } switch (oldCell.CellType) { case CellType.String: HSSFRichTextString rts= oldCell.RichStringCellValue as HSSFRichTextString; newCell.SetCellValue(rts); if(rts!=null) { for (int j = 0; j < rts.NumFormattingRuns; j++) { short fontIndex = rts.GetFontOfFormattingRun(j); int startIndex = rts.GetIndexOfFormattingRun(j); int endIndex = 0; if (j + 1 == rts.NumFormattingRuns) { endIndex = rts.Length; } else { endIndex = rts.GetIndexOfFormattingRun(j+1); } FontRecord fr = newCell.BoundWorkbook.CreateNewFont(); fr.CloneStyleFrom(oldCell.BoundWorkbook.GetFontRecordAt(fontIndex)); HSSFFont font = new HSSFFont((short)(newCell.BoundWorkbook.GetFontIndex(fr)), fr); newCell.RichStringCellValue.ApplyFont(startIndex,endIndex, font); } } break; case CellType.Numeric: newCell.SetCellValue(oldCell.NumericCellValue); break; case CellType.Blank: newCell.SetCellType(CellType.Blank); break; case CellType.Boolean: newCell.SetCellValue(oldCell.BooleanCellValue); break; case CellType.Error: newCell.SetCellValue(oldCell.ErrorCellValue); break; case CellType.Formula: if (keepFormulas) { newCell.SetCellType(CellType.Formula); newCell.CellFormula = oldCell.CellFormula; } else { try { newCell.SetCellType(CellType.Numeric); newCell.SetCellValue(oldCell.NumericCellValue); } catch (Exception) { newCell.SetCellType(CellType.String); newCell.SetCellValue(oldCell.ToString()); } } break; default: break; } }