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;
}
/// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLS(XLS.HSSFCell cell)
{
if (cell == null)
{
return(null);
}
switch (cell.CellType)
{
case CellType.BLANK: //BLANK:
return(null);
case CellType.BOOLEAN: //BOOLEAN:
return(cell.BooleanCellValue);
case CellType.NUMERIC: //NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
return(cell.DateCellValue.ToString("yyyy-MM-dd"));
}
return(cell.NumericCellValue);
case CellType.STRING: //STRING:
return(cell.StringCellValue.Trim());
case CellType.ERROR: //ERROR:
return(cell.ErrorCellValue);
case CellType.FORMULA: //FORMULA:
default:
return(cell.NumericCellValue);
}
}
private ExcelCell ResolveCell(Npoi.HSSFCell native)
{
if (!_cells.ContainsKey(native))
{
_cells.Add(native, new NpoiExcelCell(native));
}
return(_cells[native]);
}
public void SetUp()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
HSSFSheet sheet = wb.CreateSheet("new sheet") as HSSFSheet;
cell11 = sheet.CreateRow(0).CreateCell(0) as HSSFCell;
cell11.SetCellType(CellType.Formula);
Evaluator = new HSSFFormulaEvaluator(wb);
}
finally
{
//wb.Close();
}
}
/// <summary>
/// Check if a cell Contains a date
/// Since dates are stored internally in Excel as double values
/// we infer it Is a date if it Is formatted as such.
/// </summary>
/// <param name="cell">The cell.</param>
/// <returns>
/// <c>true</c> if [is cell date formatted] [the specified cell]; otherwise, <c>false</c>.
/// </returns>
public static bool IsCellDateFormatted(HSSFCell cell)
{
if (cell == null)
{
return(false);
}
bool bDate = false;
double d = cell.NumericCellValue;
if (HSSFDateUtil.IsValidExcelDate(d))
{
HSSFCellStyle style = cell.CellStyle;
int i = style.DataFormat;
String f = style.GetDataFormatString(cell.BoundWorkbook);
bDate = IsADateFormat(i, f);
}
return(bDate);
}
private static void SetCellType(HSSFCell cell, CellValue cv)
{
int cellType = cv.CellType;
switch (cellType)
{
case HSSFCell.CELL_TYPE_BOOLEAN:
case HSSFCell.CELL_TYPE_ERROR:
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_STRING:
cell.SetCellType(cellType);
return;
case HSSFCell.CELL_TYPE_BLANK:
// never happens - blanks eventually get translated to zero
break;
case HSSFCell.CELL_TYPE_FORMULA:
// this will never happen, we have already evaluated the formula
break;
}
throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")");
}
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;
}
}
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;
}
}
}
/// <summary>Gets the cell located at a specific address.</summary>
/// <param name="row">The 0-based row.</param>
/// <param name="col">The 0-based column.</param>
/// <returns>The cell at the specified location.</returns>
public override ExcelCell CellAt(int row, int col)
{
var dataRow = this._sheet.GetRow(row);
if (dataRow == null)
{
dataRow = this._sheet.CreateRow(row);
}
if (col > dataRow.LastCellNum)
{
dataRow.CreateCell(col);
}
Npoi.HSSFCell cell = dataRow.GetCell(col) as Npoi.HSSFCell;
if (cell == null)
{
cell = (Npoi.HSSFCell)dataRow.CreateCell(col);
}
return(ResolveCell(cell));
}
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);
}
}
/**
* Returns the Formatted value of an Excel date as a <tt>String</tt> based
* on the cell's <c>DataFormat</c>. i.e. "Thursday, January 02, 2003"
* , "01/02/2003" , "02-Jan" , etc.
*
* @param cell The cell
* @return a Formatted date string
*/
private String GetFormattedDateString(HSSFCell cell)
{
FormatBase dateFormat = GetFormat(cell);
DateTime d = cell.DateCellValue;
if (dateFormat != null)
{
return dateFormat.Format(d);
}
return d.ToString();
}
/**
* Create and return a FormatBase based on the FormatBase string from a cell's
* style. If the pattern cannot be Parsed, return a default pattern.
*
* @param cell The Excel cell
* @return A FormatBase representing the excel FormatBase. May return null.
*/
public FormatBase CreateFormat(HSSFCell cell)
{
int formatIndex = cell.CellStyle.DataFormat;
String formatStr = cell.CellStyle.DataFormatString;
return CreateFormat(cell.NumericCellValue, formatIndex, formatStr);
}
/**
* Returns a default FormatBase for a cell.
* @param cell The cell
* @return a default FormatBase
*/
public FormatBase GetDefaultFormat(HSSFCell cell)
{
return(GetDefaultFormat(cell.NumericCellValue));
}
/// <summary>
/// Use this to create new cells within the row and return it.
/// The cell that is returned is a CELL_TYPE_BLANK. The type can be changed
/// either through calling setCellValue or setCellType.
/// </summary>
/// <param name="columnIndex">the column number this cell represents</param>
/// <param name="type">a high level representation of the created cell.</param>
/// <returns></returns>
public ICell CreateCell(int columnIndex, CellType type)
{
short shortCellNum = (short)columnIndex;
if (columnIndex > 0x7FFF)
{
shortCellNum = (short)(0xffff - columnIndex);
}
ICell cell = new HSSFCell(book, sheet, RowNum, (short)columnIndex, type);
AddCell(cell);
sheet.Sheet.AddValueRecord(RowNum, ((HSSFCell)cell).CellValueRecord);
return cell;
}
public void TestHSSFCell1()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
row.CreateCell(0);
HSSFCell cell = new HSSFCell(wb, sheet, 0, (short)0);
Assert.IsNotNull(cell);
}
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");
}
}
/// <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 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 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();
}
/// <summary>
/// Check if a cell Contains a date
/// Since dates are stored internally in Excel as double values
/// we infer it Is a date if it Is formatted as such.
/// </summary>
/// <param name="cell">The cell.</param>
/// <returns>
/// <c>true</c> if [is cell date formatted] [the specified cell]; otherwise, <c>false</c>.
/// </returns>
public static bool IsCellDateFormatted(HSSFCell cell)
{
if (cell == null) return false;
bool bDate = false;
double d = cell.NumericCellValue;
if (HSSFDateUtil.IsValidExcelDate(d))
{
HSSFCellStyle style = cell.CellStyle;
int i = style.DataFormat;
String f = style.GetDataFormatString(cell.BoundWorkbook);
bDate = IsADateFormat(i, f);
}
return bDate;
}
/// <summary>
/// Create a high level Cell object from an existing low level record. Should
/// only be called from HSSFSheet or HSSFRow itself.
/// </summary>
/// <param name="cell">The low level cell to Create the high level representation from</param>
/// <returns> the low level record passed in</returns>
public Cell CreateCellFromRecord(CellValueRecordInterface cell)
{
Cell hcell = new HSSFCell(book, sheet, cell);
AddCell(hcell);
int colIx = cell.Column;
if (row.IsEmpty)
{
row.FirstCol=(colIx);
row.LastCol=(colIx + 1);
}
else
{
if (colIx < row.FirstCol)
{
row.FirstCol = (colIx);
}
else if (colIx > row.LastCol)
{
row.LastCol = (colIx + 1);
}
else
{
// added cell is within first and last cells
}
}
return hcell;
}
/// <summary>
/// Use this to create new cells within the row and return it.
/// The cell that is returned is a CELL_TYPE_BLANK. The type can be changed
/// either through calling setCellValue or setCellType.
/// </summary>
/// <param name="columnIndex">the column number this cell represents</param>
/// <param name="type">a high level representation of the created cell.</param>
/// <returns></returns>
public NPOI.SS.UserModel.Cell CreateCell(int columnIndex, NPOI.SS.UserModel.CellType type)
{
Cell cell = new HSSFCell(book, sheet, RowNum, (short)columnIndex, type);
AddCell(cell);
sheet.Sheet.AddValueRecord(RowNum, ((HSSFCell)cell).CellValueRecord);
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;
}
/// <summary>
/// Take a cell, and apply a font to it
/// </summary>
/// <param name="cell">the cell to Set the alignment for</param>
/// <param name="workbook">The workbook that is being worked with.</param>
/// <param name="font">The HSSFFont that you want to Set...</param>
public static void SetFont(HSSFCell cell, HSSFWorkbook workbook, HSSFFont font)
{
SetCellStyleProperty(cell, workbook, FONT, font);
}
/// <summary>Default constructor</summary>
/// <param name="cell">NPOI native encapsulation of the cell.</param>
public NpoiExcelCell(Npoi.HSSFCell cell)
{
_cell = cell;
}
/// <summary>
/// Check if a cell Contains a date, Checking only for internal
/// excel date formats.
/// As Excel stores a great many of its dates in "non-internal"
/// date formats, you will not normally want to use this method.
/// </summary>
/// <param name="cell">The cell.</param>
/// <returns>
/// <c>true</c> if [is cell internal date formatted] [the specified cell]; otherwise, <c>false</c>.
/// </returns>
public static bool IsCellInternalDateFormatted(HSSFCell cell)
{
if (cell == null) return false;
bool bDate = false;
double d = cell.NumericCellValue;
if (HSSFDateUtil.IsValidExcelDate(d))
{
HSSFCellStyle style = cell.CellStyle;
int i = style.DataFormat;
bDate = IsInternalDateFormat(i);
}
return bDate;
}
/// <summary>Default constructor</summary>
/// <param name="cell">NPOI native encapsulation of the cell.</param>
public NpoiExcelCell(Npoi.HSSFCell cell)
{
_cell = cell;
}
/// <summary>
/// Take a cell, and align it.
/// </summary>
/// <param name="cell">the cell to Set the alignment for</param>
/// <param name="workbook">The workbook that is being worked with.</param>
/// <param name="align">the column alignment to use.</param>
public static void SetAlignment(HSSFCell cell, HSSFWorkbook workbook, short align)
{
SetCellStyleProperty(cell, workbook, ALIGNMENT, align);
}
/// <summary>
/// Goes through the Wokrbook, optimising the cell styles
/// by removing duplicate ones and ones that aren't used.
/// For best results, optimise the fonts via a call to
/// OptimiseFonts(HSSFWorkbook) first
/// </summary>
/// <param name="workbook">The workbook in which to optimise the cell styles</param>
public static void OptimiseCellStyles(HSSFWorkbook workbook)
{
// Where each style has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumExFormats];
bool[] isUsed = new bool[newPos.Length];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
isUsed[i] = false;
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each style record, so we can do deletes
// without Getting confused
ExtendedFormatRecord[] xfrs = new ExtendedFormatRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
xfrs[i] = workbook.Workbook.GetExFormatAt(i);
}
// Loop over each style, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
ExtendedFormatRecord xfCheck = workbook.Workbook.GetExFormatAt(j);
if (xfCheck.Equals(xfrs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
// If we got a duplicate, mark the one we're keeping as used
if (earlierDuplicate != -1)
{
isUsed[earlierDuplicate] = true;
}
}
// Loop over all the cells in the file, and identify any user defined
// styles aren't actually being used (don't touch built-in ones)
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
foreach (IRow row in s)
{
foreach (ICell cellI in row)
{
HSSFCell cell = (HSSFCell)cellI;
short oldXf = cell.CellValueRecord.XFIndex;
isUsed[oldXf] = true;
}
}
}
// Mark any that aren't used as needing zapping
for (int i = 21; i < isUsed.Length; i++)
{
if (!isUsed[i])
{
// Un-used style, can be removed
zapRecords[i] = true;
newPos[i] = 0;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only work on user added ones, which come after 20
for (int i = 21; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j])
{
newPosition--;
}
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user style records
// removing by index, because removing by object may delete
// styles we did not intend to (the ones that _were_ duplicated and not the duplicates)
int max = newPos.Length;
int removed = 0; // to adjust index after deletion
for (int i = 21; i < max; i++)
{
if (zapRecords[i + removed])
{
workbook.Workbook.RemoveExFormatRecord(i);
i--;
max--;
removed++;
}
}
// Finally, update the cells to point at their new extended format records
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
HSSFSheet s = (HSSFSheet)workbook.GetSheetAt(sheetNum);
//IEnumerator rIt = s.GetRowEnumerator();
//while (rIt.MoveNext())
foreach (IRow row in s)
{
//HSSFRow row = (HSSFRow)rIt.Current;
//IEnumerator cIt = row.GetEnumerator();
//while (cIt.MoveNext())
foreach (ICell cell in row)
{
//ICell cell = (HSSFCell)cIt.Current;
short oldXf = ((HSSFCell)cell).CellValueRecord.XFIndex;
NPOI.SS.UserModel.ICellStyle newStyle = workbook.GetCellStyleAt(
newPos[oldXf]
);
cell.CellStyle = (newStyle);
}
}
}
}
/**
* This method attempt to find an already existing HSSFCellStyle that matches
* what you want the style to be. If it does not find the style, then it
* Creates a new one. If it does Create a new one, then it applies the
* propertyName and propertyValue to the style. This is necessary because
* Excel has an upper limit on the number of Styles that it supports.
*
*@param workbook The workbook that is being worked with.
*@param propertyName The name of the property that is to be
* changed.
*@param propertyValue The value of the property that is to be
* changed.
*@param cell The cell that needs it's style changes
*@exception NestableException Thrown if an error happens.
*/
public static void SetCellStyleProperty(HSSFCell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue)
{
HSSFCellStyle originalStyle = cell.CellStyle;
HSSFCellStyle newStyle = null;
Hashtable values = GetFormatProperties(originalStyle);
values[propertyName] = propertyValue;
// index seems like what index the cellstyle is in the list of styles for a workbook.
// not good to compare on!
short numberCellStyles = workbook.NumCellStyles;
for (short i = 0; i < numberCellStyles; i++)
{
HSSFCellStyle wbStyle = workbook.GetCellStyleAt(i);
Hashtable wbStyleMap = GetFormatProperties(wbStyle);
if (wbStyleMap.Equals(values))
{
newStyle = wbStyle;
break;
}
}
if (newStyle == null)
{
newStyle = workbook.CreateCellStyle();
SetFormatProperties(newStyle, workbook, values);
}
cell.CellStyle = (newStyle);
}
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();
}
/* package */
static void SetCellValue(HSSFCell cell, String text)
{
cell.SetCellValue(new HSSFRichTextString(text));
}
/**
*
* Returns the Formatted value of a cell as a <tt>String</tt> regardless
* of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
* cell value will be Formatted using a default FormatBase.
*
* When passed a null or blank cell, this method will return an empty
* String (""). Formulas in formula type cells will not be evaluated.
*
*
* @param cell The cell
* @return the Formatted cell value as a String
*/
public String FormatCellValue(HSSFCell cell)
{
return FormatCellValue(cell, null);
}
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;
}
}
/**
*
* Returns the Formatted value of a cell as a <tt>String</tt> regardless
* of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
* cell value will be Formatted using a default FormatBase.
*
* When passed a null or blank cell, this method will return an empty
* String (""). Formula cells will be evaluated using the given
* {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
* evaluator is null, then the formula String will be returned. The caller
* is responsible for setting the currentRow on the evaluator
*
*
* @param cell The cell (can be null)
* @param evaluator The HSSFFormulaEvaluator (can be null)
* @return a string value of the cell
*/
public String FormatCellValue(HSSFCell cell, HSSFFormulaEvaluator evaluator)
{
if (cell == null)
{
return "";
}
int cellType = cell.CellType;
if (evaluator != null && cellType == HSSFCell.CELL_TYPE_FORMULA)
{
try
{
cellType = evaluator.EvaluateFormulaCell(cell);
}
catch (Exception e)
{
throw new Exception("Did you forGet to set the current" +
" row on the HSSFFormulaEvaluator?", e);
}
}
switch (cellType)
{
case HSSFCell.CELL_TYPE_BLANK:
return "";
case HSSFCell.CELL_TYPE_FORMULA:
// should only occur if evaluator is null
return cell.CellFormula;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
return GetFormattedDateString(cell);
}
return GetFormattedNumberString(cell);
case HSSFCell.CELL_TYPE_STRING:
return cell.RichStringCellValue.String;
case HSSFCell.CELL_TYPE_BOOLEAN:
return cell.BooleanCellValue?"TRUE":"FALSE";
case HSSFCell.CELL_TYPE_ERROR:
return NPOI.HSSF.Record.Formula.Eval.ErrorEval.GetText(cell.ErrorCellValue);
}
throw new Exception("Unexpected celltype (" + cellType + ")");
}
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;
}
}
protected bool IsTextEmpty(HSSFCell cell)
{
string value;
switch (cell.CellType)
{
case CellType.STRING:
// XXX: enrich
value = cell.RichStringCellValue.String;
break;
case CellType.FORMULA:
switch (cell.CachedFormulaResultType)
{
case CellType.STRING:
HSSFRichTextString str = cell.RichStringCellValue as HSSFRichTextString;
if (str == null || str.Length <= 0)
return false;
value = str.ToString();
break;
case CellType.NUMERIC:
HSSFCellStyle style = cell.CellStyle as HSSFCellStyle;
if (style == null)
{
return false;
}
value = (_formatter.FormatRawCellContents(cell.NumericCellValue, style.DataFormat, style.GetDataFormatString()));
break;
case CellType.BOOLEAN:
value = cell.BooleanCellValue.ToString();
break;
case CellType.ERROR:
value = ErrorEval.GetText(cell.ErrorCellValue);
break;
default:
value = string.Empty;
break;
}
break;
case CellType.BLANK:
value = string.Empty;
break;
case CellType.NUMERIC:
value = _formatter.FormatCellValue(cell);
break;
case CellType.BOOLEAN:
value = cell.BooleanCellValue.ToString();
break;
case CellType.ERROR:
value = ErrorEval.GetText(cell.ErrorCellValue);
break;
default:
return true;
}
return string.IsNullOrEmpty(value);
}
/// <summary>
/// Create a high level Cell object from an existing low level record. Should
/// only be called from HSSFSheet or HSSFRow itself.
/// </summary>
/// <param name="cell">The low level cell to Create the high level representation from</param>
/// <returns> the low level record passed in</returns>
public ICell CreateCellFromRecord(CellValueRecordInterface cell)
{
ICell hcell = new HSSFCell(book, sheet, cell);
AddCell(hcell);
int colIx = cell.Column;
if (row.IsEmpty)
{
row.FirstCol=(colIx);
row.LastCol=(colIx + 1);
}
else
{
if (colIx < row.FirstCol)
{
row.FirstCol = (colIx);
}
else if (colIx > row.LastCol)
{
row.LastCol = (colIx + 1);
}
else
{
// added cell is within first and last cells
}
}
// TODO - RowRecord column boundaries need to be updated for cell comments too
return hcell;
}
protected bool ProcessCell(HSSFCell cell, XmlElement tableCellElement,
int normalWidthPx, int maxSpannedWidthPx, float normalHeightPt)
{
HSSFCellStyle cellStyle = cell.CellStyle as HSSFCellStyle;
string value;
switch (cell.CellType)
{
case CellType.STRING:
// XXX: enrich
value = cell.RichStringCellValue.String;
break;
case CellType.FORMULA:
switch (cell.CachedFormulaResultType)
{
case CellType.STRING:
HSSFRichTextString str = cell.RichStringCellValue as HSSFRichTextString;
if (str != null && str.Length > 0)
{
value = (str.String);
}
else
{
value = string.Empty;
}
break;
case CellType.NUMERIC:
HSSFCellStyle style = cellStyle;
if (style == null)
{
value = cell.NumericCellValue.ToString();
}
else
{
value = (_formatter.FormatRawCellContents(cell.NumericCellValue, style.DataFormat, style.GetDataFormatString()));
}
break;
case CellType.BOOLEAN:
value = cell.BooleanCellValue.ToString();
break;
case CellType.ERROR:
value = ErrorEval.GetText(cell.ErrorCellValue);
break;
default:
logger.Log(POILogger.WARN, "Unexpected cell cachedFormulaResultType (" + cell.CachedFormulaResultType.ToString() + ")");
value = string.Empty;
break;
}
break;
case CellType.BLANK:
value = string.Empty;
break;
case CellType.NUMERIC:
value = _formatter.FormatCellValue(cell);
break;
case CellType.BOOLEAN:
value = cell.BooleanCellValue.ToString();
break;
case CellType.ERROR:
value = ErrorEval.GetText(cell.ErrorCellValue);
break;
default:
logger.Log(POILogger.WARN, "Unexpected cell type (" + cell.CellType.ToString() + ")");
return true;
}
bool noText = string.IsNullOrEmpty(value);
bool wrapInDivs = !noText && UseDivsToSpan && !cellStyle.WrapText;
short cellStyleIndex = cellStyle.Index;
if (cellStyleIndex != 0)
{
HSSFWorkbook workbook = cell.Row.Sheet.Workbook as HSSFWorkbook;
string mainCssClass = GetStyleClassName(workbook, cellStyle);
if (wrapInDivs)
{
tableCellElement.SetAttribute("class", mainCssClass + " "
+ cssClassContainerCell);
}
else
{
tableCellElement.SetAttribute("class", mainCssClass);
}
if (noText)
{
/*
* if cell style is defined (like borders, etc.) but cell text
* is empty, add " " to output, so browser won't collapse
* and ignore cell
*/
value = "\u00A0"; //“ ”全角空格
}
}
if (OutputLeadingSpacesAsNonBreaking && value.StartsWith(" "))
{
StringBuilder builder = new StringBuilder();
for (int c = 0; c < value.Length; c++)
{
if (value[c] != ' ')
break;
builder.Append('\u00a0');
}
if (value.Length != builder.Length)
builder.Append(value.Substring(builder.Length));
value = builder.ToString();
}
XmlText text = htmlDocumentFacade.CreateText(value);
if (wrapInDivs)
{
XmlElement outerDiv = htmlDocumentFacade.CreateBlock();
outerDiv.SetAttribute("class", this.cssClassContainerDiv);
XmlElement innerDiv = htmlDocumentFacade.CreateBlock();
StringBuilder innerDivStyle = new StringBuilder();
innerDivStyle.Append("position:absolute;min-width:");
innerDivStyle.Append(normalWidthPx);
innerDivStyle.Append("px;");
if (maxSpannedWidthPx != int.MaxValue)
{
innerDivStyle.Append("max-width:");
innerDivStyle.Append(maxSpannedWidthPx);
innerDivStyle.Append("px;");
}
innerDivStyle.Append("overflow:hidden;max-height:");
innerDivStyle.Append(normalHeightPt);
innerDivStyle.Append("pt;white-space:nowrap;");
ExcelToHtmlUtils.AppendAlign(innerDivStyle, cellStyle.Alignment);
htmlDocumentFacade.AddStyleClass(outerDiv, "d", innerDivStyle.ToString());
innerDiv.AppendChild(text);
outerDiv.AppendChild(innerDiv);
tableCellElement.AppendChild(outerDiv);
}
else
{
tableCellElement.AppendChild(text);
}
return string.IsNullOrEmpty(value) && cellStyleIndex == 0;
}
/**
* Return a FormatBase for the given cell if one exists, otherwise try to
* Create one. This method will return <c>null</c> if the any of the
* following is true:
* <ul>
* <li>the cell's style is null</li>
* <li>the style's data FormatBase string is null or empty</li>
* <li>the FormatBase string cannot be recognized as either a number or date</li>
* </ul>
*
* @param cell The cell to retrieve a FormatBase for
* @return A FormatBase for the FormatBase String
*/
private FormatBase GetFormat(HSSFCell cell)
{
if (cell.CellStyle == null)
{
return null;
}
int formatIndex = cell.CellStyle.DataFormat;
String formatStr = cell.CellStyle.DataFormatString;
if (formatStr == null || formatStr.Trim().Length == 0)
{
return null;
}
return GetFormat(cell.NumericCellValue, formatIndex, formatStr);
}
protected void WriteCellTypeValue(double Value, HSSFCell cell, bool hasStyle)
{
cell.SetCellType(CellType.NUMERIC);
cell.SetCellValue(Value);
}
/**
* Returns a default FormatBase for a cell.
* @param cell The cell
* @return a default FormatBase
*/
public FormatBase GetDefaultFormat(HSSFCell cell)
{
return GetDefaultFormat(cell.NumericCellValue);
}
protected void WriteCellTypeValue(DateTime Value, HSSFCell cell, bool hasStyle)
{
cell.SetCellType(CellType.NUMERIC);
if(!hasStyle)
cell.CellStyle = _dateStyle;
cell.SetCellValue(Value);
}
/**
* Returns the Formatted value of an Excel number as a <tt>String</tt>
* based on the cell's <c>DataFormat</c>. Supported Formats include
* currency, percents, decimals, phone number, SSN, etc.:
* "61.54%", "$100.00", "(800) 555-1234".
*
* @param cell The cell
* @return a Formatted number string
*/
private String GetFormattedNumberString(HSSFCell cell)
{
FormatBase numberFormat = GetFormat(cell);
double d = cell.NumericCellValue;
if (numberFormat == null)
{
return d.ToString();
}
return numberFormat.Format(d);
}
protected void WriteCellTypeValue(string Value, HSSFCell cell, bool hasStyle)
{
cell.SetCellValue(Value);
}