public ICellStyle Cellstyle()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
IFont font = book.CreateFont();
font.FontName = "宋体";
//font.setFontHeightInPoints((short)10);
font.FontHeightInPoints = 10;
// 普通单元格样式
ICellStyle style = book.CreateCellStyle();
style.SetFont(font);
//style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
//style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
//style.setWrapText(true);
//style.setLeftBorderColor(HSSFColor.BLACK.index);
//style.setBorderLeft((short)1);
//style.setRightBorderColor(HSSFColor.BLACK.index);
//style.setBorderRight((short)1);
//style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
//style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
//style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．
return(style);
}
/// <summary>
/// 设置字体样式
/// </summary>
/// <param name="r">行标</param>
/// <param name="c">列标</param>
/// <param name="size">字体大小，0为默认</param>
/// <param name="f">字体样式（‘B’加粗，‘I’斜体）</param>
/// <param name="color">字体颜色('R'红,'B'蓝,'G'绿,'Y'黄,'P'粉,'O'橙,'W'白)</param>
public void SetCellFont(int r, int c, int size, char f, char color)
{
if (r <= osheet.LastRowNum)
{
IRow row = osheet.GetRow(r);
if (row != null)
{
if (c <= row.LastCellNum)
{
ICell cell = row.GetCell(c);
ICellStyle style = cell.CellStyle;
//新建一个字体样式对象
IFont font = obook.CreateFont();
//设置字体大小
if (size > 0)
{
font.FontHeightInPoints = Convert.ToInt16(size);
}
switch (f)
{
case 'B':
{
//设置字体加粗样式
font.IsBold = true;
}
break;
case 'I':
{
//设置字体加粗样式
font.IsItalic = true;
}
break;
}
switch (color)
{
case 'R': { font.Color = HSSFColor.Red.Index; } break;
case 'B': { font.Color = HSSFColor.Blue.Index; } break;
case 'G': { font.Color = HSSFColor.Green.Index; } break;
case 'Y': { font.Color = HSSFColor.Yellow.Index; } break;
case 'P': { font.Color = HSSFColor.Pink.Index; } break;
case 'O': { font.Color = HSSFColor.Orange.Index; } break;
case 'W': { font.Color = HSSFColor.White.Index; } break;
}
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//将新的样式赋给单元格
cell.CellStyle = style;
}
}
}
}
public FileResult Export(Pagination rq, AdminCredential User)
{
#region 报表导出
var report = service.Get(rq.vid).Data;
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = (short)24;// NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
style.FillPattern = FillPattern.SolidForeground;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont(); //创建一个字体样式对象
font.FontHeightInPoints = 10; //字体大小
font.FontName = "宋体"; //和excel里面的字体对应
font.Boldweight = short.MaxValue; //字体加粗
font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
style.SetFont(font); //将字体样式赋给样式对象
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet(report.Name);
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.Height = 430;
var FieldList = JsonConvert.DeserializeObject <List <ViewFieldModel> >(service.GetReportColumns(rq.vid.Value));
int i = 0;
foreach (var item in FieldList)
{
ICell cell = row1.CreateCell(i);
cell.SetCellValue(item.Title);
cell.CellStyle = style;
i++;
}
rq.Filter = Session[rq.vid.ToString() + "Filter"].ToString();
DataTable dt = service.GetExportReportData(rq, User);
//将数据逐步写入sheet1各个行
for (int z = 0; z < dt.Rows.Count; z++)
{
var row = dt.Rows[z];
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(z + 1);
int c = 0;
foreach (var item in FieldList)
{
rowtemp.CreateCell(c++).SetCellValue(row[item.Field].ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", report.Name + ".xls"));
#endregion
}
void CreateExcel(object sender)
{
try
{
string filePath = Application.StartupPath + "\\DailyPOSSales.xls";
if (System.IO.File.Exists(filePath))
{
File.Delete(filePath);
}
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Daily POS Sales");
//第1行
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICell fistCell = row.CreateCell(0);
fistCell.SetCellValue("Daily POS Sales");
ICellStyle firstRowStyle = workbook.CreateCellStyle();
firstRowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
IFont firstRowFont = workbook.CreateFont();
firstRowFont.FontName = "Tahoma";
firstRowFont.FontHeightInPoints = 20;
firstRowFont.Color = HSSFColor.Blue.Index;
firstRowFont.Boldweight = short.MaxValue;
firstRowStyle.SetFont(firstRowFont);
fistCell.CellStyle = firstRowStyle;
row.HeightInPoints = 25;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 15));
using (FileStream fs = File.OpenWrite(filePath))
{
workbook.Write(fs);
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
//this.Invoke(new Action(() =>
//{
// (sender as Button).Enabled = true;
//}));
}
}
public static byte[] getExcelData(DataTable t
, GridColumns cols)
{
var workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
var sheet = workbook.CreateSheet("data");
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
var font1 = workbook.CreateFont();
font1.IsBold = true;
for (int i = 0; i < cols.Count; i++)
{
var col = cols[i];
var cell = row.CreateCell(i);
cell.SetCellValue(col.title);
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.SetFont(font1);
sheet.SetColumnWidth(i, 5500);
}
foreach (DataRow r in t.Rows)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
for (int iCol = 0; iCol < cols.Count; iCol++)
{
var col = cols[iCol];
var cell = row.CreateCell(iCol);
cell.SetCellValue(r[col.datafield].ToString());
//cell.CellStyle.GetFont(workbook).IsBold = false;
}
}
using (var fileData = new System.IO.MemoryStream())
{
workbook.Write(fileData);
return(g.ConvertStreamToByteArray(fileData));
}
}
///// <summary>
///// 樣式的值是否改變
///// </summary>
//internal bool StyleValueIsChange
//{
// get { return _styleValueIsChange; }
// set{_styleValueIsChange =value;}
//}
#endregion
#region 方法
internal ICellStyle GetCellStyle(NPOI.HSSF.UserModel.HSSFWorkbook workBook)
{
if (workBook == null)
{
throw new ArgumentNullException("workBook參數不能為NULL。");
}
if (_cellStyle == null || _styleValueIsChange)
{
_cellStyle = workBook.CreateCellStyle();
IFont font = workBook.FindFont((short)_fontBold, _fontColor, (short)(_fontSize * 20), _fontName, _isItalic, _isStrikeout, _fontTypeOffset, (byte)_underline);
if (font == null)
{
font = workBook.CreateFont();
font.FontName = this._fontName;
font.Boldweight = (short)this._fontBold;
font.FontHeightInPoints = (short)this._fontSize;
font.Underline = (byte)this.UnderLine;
font.IsItalic = _isItalic;
font.Color = _fontColor;
font.IsStrikeout = _isStrikeout;
font.TypeOffset = _fontTypeOffset;
}
_cellStyle.SetFont(font);
IDataFormat format = workBook.CreateDataFormat();
_cellStyle.BorderBottom = this._borderBottom;
_cellStyle.BorderLeft = this._borderLeft;
_cellStyle.BorderRight = this._borderRight;
_cellStyle.BorderTop = this._borderTop;
_cellStyle.Alignment = this._alignment;
_cellStyle.VerticalAlignment = this.VerticalAlignment;
if (!string.IsNullOrEmpty(_dataFormart))
{
_cellStyle.DataFormat = format.GetFormat(this._dataFormart);
}
_cellStyle.WrapText = this._warpText;
_styleValueIsChange = false;
}
return(_cellStyle);
}
public static void ExportList <T>(IEnumerable <T> dataList, string fileName, string[] columnNames,
Action <T, Action <object> > writer)
{
using (var book = new NPOI.HSSF.UserModel.HSSFWorkbook())
{
var sheet = book.CreateSheet();
var headRow = sheet.CreateRow(0);
for (int i = 0; i < columnNames.Length; i++)
{
var cell = headRow.CreateCell(i);
cell.SetCellValue(columnNames[i]);
var font = book.CreateFont();
font.Boldweight = NPOI.HSSF.UserModel.HSSFFont.BOLDWEIGHT_BOLD;
cell.RichStringCellValue.ApplyFont(font);
}
//开始写入内容
var rowIndex = 1;
foreach (T item in dataList)
{
var row = sheet.CreateRow(rowIndex++);
var colIndex = 0;
writer(item, (target) =>
{
row.CreateCell(colIndex++).SetCellValue(target == null ? "" : target.ToString());
});
}
//设置每列的宽度
for (int colIndex = 0; colIndex < columnNames.Length; colIndex++)
{
sheet.AutoSizeColumn(colIndex);
sheet.SetColumnWidth(colIndex, sheet.GetColumnWidth(colIndex) + 2100);
}
ExportFile(book, fileName);
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
var userList = UserBLL.SearchList(new UserSearchInfo {
UserName = UserName.Text, ProviderNo = ProviderNo.Text, UserType = (int)UserType.Provider
});
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
sheet.DefaultColumnWidth = 18;
sheet.CreateFreezePane(0, 1, 0, 1);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue("供应商编号");
row.CreateCell(1).SetCellValue("用户名");
row.CreateCell(2).SetCellValue("电子邮箱");
row.CreateCell(3).SetCellValue("公司名称");
row.CreateCell(4).SetCellValue("对公银行开户行");
row.CreateCell(5).SetCellValue("税务号");
row.CreateCell(6).SetCellValue("法人代表");
row.CreateCell(7).SetCellValue("联系人");
row.CreateCell(8).SetCellValue("联系电话");
row.CreateCell(9).SetCellValue("传真");
row.CreateCell(10).SetCellValue("经营品牌");
row.CreateCell(11).SetCellValue("经营品类");
row.CreateCell(12).SetCellValue("结算方式");
row.CreateCell(13).SetCellValue("结算周期");
row.CreateCell(14).SetCellValue("物流配送");
row.CreateCell(15).SetCellValue("售后服务");
row.CreateCell(16).SetCellValue("退换货保障");
row.CreateCell(17).SetCellValue("详细地址");
//设置表头格式
var headFont = book.CreateFont();
headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headFont.FontHeightInPoints = 10;
var headStyle = book.CreateCellStyle();
headStyle.SetFont(headFont);
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
foreach (var cell in row.Cells)
{
cell.CellStyle = headStyle;
}
foreach (var user in userList)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(userList.IndexOf(user) + 1);
dataRow.CreateCell(0).SetCellValue(user.ProviderNo);
dataRow.CreateCell(1).SetCellValue(user.UserName);
dataRow.CreateCell(2).SetCellValue(user.Email);
dataRow.CreateCell(3).SetCellValue(user.ProviderName);
dataRow.CreateCell(4).SetCellValue(user.ProviderBankNo);
dataRow.CreateCell(5).SetCellValue(user.ProviderTaxRegistration);
dataRow.CreateCell(6).SetCellValue(user.ProviderCorporate);
dataRow.CreateCell(7).SetCellValue(user.ProviderLinker);
dataRow.CreateCell(8).SetCellValue(user.ProviderLinkerTel);
dataRow.CreateCell(9).SetCellValue(user.ProviderFax);
dataRow.CreateCell(10).SetCellValue(user.ProviderOperateBrand);
dataRow.CreateCell(11).SetCellValue(user.ProviderOperateClass);
dataRow.CreateCell(12).SetCellValue(user.ProviderAccount);
dataRow.CreateCell(13).SetCellValue(user.ProviderAccountCycle);
dataRow.CreateCell(14).SetCellValue(user.ProviderShipping);
dataRow.CreateCell(15).SetCellValue(user.ProviderService);
dataRow.CreateCell(16).SetCellValue(user.ProviderEnsure);
dataRow.CreateCell(17).SetCellValue(RegionBLL.RegionNameList(user.RegionId) + ", " + user.ProviderAddress);
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
Response.End();
}
protected void ExportButton_Click(object sender, EventArgs e)
{
//if (Cookies.Admin.GetAdminName(true).ToLower() != "admin") {
// //只有admin才有权限导出会员数据
// ScriptHelper.Alert("没有权限");
//}
//else
//{
UserSearchInfo userSearch = new UserSearchInfo();
userSearch.UserName = RequestHelper.GetQueryString <string>("UserName");
userSearch.Mobile = RequestHelper.GetQueryString <string>("Mobile");
userSearch.Status = RequestHelper.GetQueryString <int>("Status");
userSearch.StartRegisterDate = RequestHelper.GetQueryString <DateTime>("StartRegisterDate");
userSearch.EndRegisterDate = ShopCommon.SearchEndDate(RequestHelper.GetQueryString <DateTime>("EndRegisterDate"));
UserName.Text = userSearch.UserName;
Mobile.Text = userSearch.Mobile;
StartRegisterDate.Text = RequestHelper.GetQueryString <string>("StartRegisterDate");
EndRegisterDate.Text = RequestHelper.GetQueryString <string>("EndRegisterDate");
status = userSearch.Status;
var userList = UserBLL.SearchList(new UserSearchInfo
{
UserName = userSearch.UserName,
CardNo = userSearch.CardNo,
Status = userSearch.Status
});
var data = userList.Take(1000).ToList();
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
sheet.DefaultColumnWidth = 18;
sheet.CreateFreezePane(0, 1, 0, 1);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue("用户名");
row.CreateCell(1).SetCellValue("性别");
row.CreateCell(2).SetCellValue("手机");
row.CreateCell(3).SetCellValue("邮箱");
row.CreateCell(4).SetCellValue("固定电话");
row.CreateCell(5).SetCellValue("QQ");
row.CreateCell(6).SetCellValue("生日");
row.CreateCell(7).SetCellValue("所在地");
row.CreateCell(8).SetCellValue("注册时间");
row.CreateCell(9).SetCellValue("登录次数");
row.CreateCell(10).SetCellValue("最近登录");
row.CreateCell(11).SetCellValue("会员状态");
//设置表头格式
var headFont = book.CreateFont();
headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headFont.FontHeightInPoints = 10;
var headStyle = book.CreateCellStyle();
headStyle.SetFont(headFont);
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
foreach (var cell in row.Cells)
{
cell.CellStyle = headStyle;
}
foreach (var entity in data)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(data.IndexOf(entity) + 1);
dataRow.CreateCell(0).SetCellValue(entity.UserName);
dataRow.CreateCell(1).SetCellValue(entity.Sex == 1 ? "男" : entity.Sex == 2 ? "女" : "保密");
dataRow.CreateCell(2).SetCellValue(entity.Mobile);
dataRow.CreateCell(3).SetCellValue(entity.Mobile);
dataRow.CreateCell(4).SetCellValue(entity.Tel);
dataRow.CreateCell(5).SetCellValue(entity.QQ);
dataRow.CreateCell(6).SetCellValue(entity.Birthday);
dataRow.CreateCell(7).SetCellValue(RegionBLL.ReadCityName(entity.RegionId) + entity.Address);
dataRow.CreateCell(8).SetCellValue(entity.RegisterDate.ToString());
dataRow.CreateCell(9).SetCellValue(entity.LoginTimes);
dataRow.CreateCell(10).SetCellValue(entity.LastLoginDate.ToString());
dataRow.CreateCell(11).SetCellValue(entity.Status == 1 ? "未验证" : entity.Status == 2 ? "正常" : "冻结");
var style = book.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
foreach (var cell in dataRow.Cells)
{
cell.CellStyle = style;
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
Response.End();
//}
}
protected void ExportButton_Click(object sender, EventArgs e)
{
ProductSearchInfo productSearch = new ProductSearchInfo();
productSearch.IsSale = (int)BoolType.True;
productSearch.Name = ShopCommon.ConvertToT <string>(Name.Text);
productSearch.ClassId = ShopCommon.ConvertToT <string>(ClassID.Text);
productSearch.BrandId = ShopCommon.ConvertToT <int>(BrandID.Text);
productSearch.StorageAnalyse = ShopCommon.ConvertToT <int>(StorageAnalyse.Text);
var data = ProductBLL.SearchList(1, 1000, productSearch, ref Count);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
sheet.DefaultColumnWidth = 18;
sheet.CreateFreezePane(0, 1, 0, 1);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue("商品名称");
row.CreateCell(1).SetCellValue("剩余库存量");
row.CreateCell(2).SetCellValue("库存下限");
row.CreateCell(3).SetCellValue("库存上限");
//设置表头格式
var headFont = book.CreateFont();
headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headFont.FontHeightInPoints = 10;
var headStyle = book.CreateCellStyle();
headStyle.SetFont(headFont);
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
foreach (var cell in row.Cells)
{
cell.CellStyle = headStyle;
}
foreach (var entity in data)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(data.IndexOf(entity) + 1);
dataRow.CreateCell(0).SetCellValue(entity.Name);
dataRow.CreateCell(1).SetCellValue(ShowStorageCount(entity.TotalStorageCount - entity.SendCount, entity.ImportActualStorageCount));
dataRow.CreateCell(2).SetCellValue(Convert.ToString(entity.LowerCount));
dataRow.CreateCell(3).SetCellValue(Convert.ToString(entity.UpperCount));
var style = book.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
foreach (var cell in dataRow.Cells)
{
cell.CellStyle = style;
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
Response.End();
}
private NPOI.HSSF.UserModel.HSSFSheet CreateWorkSheet(NPOI.HSSF.UserModel.HSSFWorkbook workbook, DataTable dataTable)
{
if (workbook != null)
{
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet(dataTable.TableName.Replace("$", ""));
//NPOI.HSSF.UserModel.HSSFRow chineseHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
int columnCount = 0;
//System.Globalization.CultureInfo chineseCI = new System.Globalization.CultureInfo("zh-cht");
foreach (DataColumn headercolumn in dataTable.Columns)
{
headercolumn.ColumnName = headercolumn.ColumnName.Trim();
if (!headercolumn.ColumnName.EndsWith("TimeCardRecordID", StringComparison.CurrentCultureIgnoreCase))
{
string columnName = headercolumn.ColumnName;
//string chineseColumnName = HROne.Common.WebUtility.GetLocalizedString(columnName, chineseCI);
//if (columnName.Equals(chineseColumnName))
// chineseColumnName = string.Empty;
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
cell.SetCellValue(columnName);
//cell = (NPOI.HSSF.UserModel.HSSFCell)chineseHeaderRow.CreateCell(columnCount); //new ExcelLibrary.SpreadSheet.Cell(headercolumn.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
//cell.SetCellValue(chineseColumnName);
//headerRow.SetCell(columnCount,cell);
//worksheet.Cells[0, columnCount] = cell;//new ExcelLibrary.SpreadSheet.Cell(column.ColumnName, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
columnCount++;
}
}
//worksheet.Cells.Rows.Add(0, headerRow);
int rowCount = 0;
NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; //900;
NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
NPOI.HSSF.UserModel.HSSFCellStyle ManualAdjustCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//ManualInputDateCellStyle.CloneStyleFrom(dateCellStyle);
ManualAdjustCellStyle.SetFont(boldFont);
//NPOI.HSSF.UserModel.HSSFCellStyle numericCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//numericCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.00"); ;
//NPOI.HSSF.UserModel.HSSFCellStyle integerCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//integerCellStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0"); ;
foreach (DataRow row in dataTable.Rows)
{
rowCount++;
columnCount = 0;
// ExcelLibrary.SpreadSheet.Row detailRow = new ExcelLibrary.SpreadSheet.Row();
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
foreach (DataColumn column in dataTable.Columns)
{
if (!column.ColumnName.EndsWith("TimeCardRecordID", StringComparison.CurrentCultureIgnoreCase))
{
//ExcelLibrary.SpreadSheet.Cell cell =new ExcelLibrary.SpreadSheet.Cell(string.Empty, new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty));
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(columnCount);
if (column.DataType.Equals(typeof(string)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty);
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
cell.SetCellValue(row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
// Override style to bold if manual adjust
if (cell.StringCellValue != string.Empty)
{
if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordDateTime.ToString("HH:mm").Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_WORKOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHOUT_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else if (column.ColumnName.Equals(HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_LOCATION))
{
ETimeCardRecord timeCard = new ETimeCardRecord();
try
{
timeCard.TimeCardRecordID = (int)row[HROne.Import.ImportAttendanceRecordProcess.FIELD_LUNCHIN_TIMECARDID];
}
catch { }
if (ETimeCardRecord.db.select(dbConn, timeCard))
{
if (!timeCard.TimeCardRecordLocation.Equals(cell.StringCellValue))
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
else
{
cell.CellStyle = ManualAdjustCellStyle;
}
}
}
}
else if (column.DataType.Equals(typeof(double)) || column.DataType.Equals(typeof(float)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00");
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]);
if (row[column] != System.DBNull.Value)
{
double value = Convert.ToDouble(row[column].ToString());
if (value.Equals(double.NaN))
{
cell.SetCellValue(string.Empty);
}
else
{
cell.SetCellValue(value);
}
}
//cell.CellStyle = numericCellStyle;
}
else if (column.DataType.Equals(typeof(int)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Number, "0.00");
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column]);
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue(Convert.ToDouble(row[column].ToString()));
}
//cell.CellStyle = integerCellStyle;
}
else if (column.DataType.Equals(typeof(DateTime)))
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.DateTime, "yyyy-MM-dd");
//if (row[column] == System.DBNull.Value)
// cell.Value = string.Empty;
//else
// cell.Value = (DateTime)row[column];
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue((DateTime)row[column]);
}
cell.CellStyle = dateCellStyle;
}
else
{
//cell.Format = new ExcelLibrary.SpreadSheet.CellFormat(ExcelLibrary.SpreadSheet.CellFormatType.Text, string.Empty);
//cell.Value = (row[column] == System.DBNull.Value ? string.Empty : row[column].ToString());
if (row[column] != System.DBNull.Value)
{
cell.SetCellValue(row[column].ToString());
}
}
//worksheet.Cells[rowCount, columnCount] = cell;
columnCount++;
}
}
// worksheet.Cells.Rows.Add(rowCount, detailRow);
}
//workbook.Worksheets.Add(worksheet);
return(worksheet);
}
else
{
return(null);
}
}
private void createWorkSheetPeriodRow(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet,
DateTime periodFromDate, DateTime periodToDate)
{
// create header styles
NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleLeft = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
NPOI.HSSF.UserModel.HSSFFont HeaderFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
HeaderFont.Boldweight = 900;
HeaderFont.FontHeightInPoints = 16;
HeaderStyleLeft.SetFont(HeaderFont);
NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
HeaderStyleCenter.CloneStyleFrom(HeaderStyleLeft);
HeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleRight = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
HeaderStyleRight.CloneStyleFrom(HeaderStyleLeft);
HeaderStyleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
// Create header row
NPOI.HSSF.UserModel.HSSFRow HeaderRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0);
NPOI.HSSF.UserModel.HSSFCell HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(5);
HeaderCell.SetCellValue(periodFromDate.ToString("dd-MMM-yy"));
HeaderCell.CellStyle = HeaderStyleRight;
HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(6);
HeaderCell.SetCellValue("~");
HeaderCell.CellStyle = HeaderStyleCenter;
HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(7);
HeaderCell.SetCellValue(periodToDate.ToString("dd-MMM-yy"));
HeaderCell.CellStyle = HeaderStyleLeft;
}
private void 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>
/// 返回与EasyUI的datagrid相适应的分页数据
/// <para>此方法将直接返回分页结果</para>
/// </summary>
/// <typeparam name="T">结果集类型</typeparam>
/// <param name="result">分页结果集</param>
/// <returns>执行结果</returns>
//protected ActionResult PagerDirectly<T>(PagedResult<T> result)
//{
// return Json(new { total = result.TotalItems, rows = result.Items }, JsonRequestBehavior.AllowGet);
//}
///// <summary>
///// 根据存储过程返回查询数据
///// </summary>
///// <param name="procedureName"></param>
///// <param name="filters"></param>
///// <returns></returns>
//public IList<IDictionary<string, object>> GetDataByProcedure(string procedureName, List<MyFilter> filters = null)
//{
// filters = filters == null ? new List<MyFilter>() : filters;
// var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["LandSystem"].ConnectionString);
// var cmd = new OracleCommand(procedureName, conn);
// var paramsList = ModelQuery.Default.CreateSQLQuery(string.Format("SELECT T.ARGUMENT_NAME FROM user_arguments T WHERE T.OBJECT_NAME='{0}' ORDER BY T.POSITION ASC", procedureName)).List<string>();
// cmd.CommandType = CommandType.StoredProcedure;
// //var paramers = new HashSet<DbParameter>();
// foreach (var item in paramsList)
// {
// var filter = filters.Where(x => x.property.ToUpper() == item).FirstOrDefault();
// if (filter == null)
// {
// continue;
// }
// if (OracelTypeDic.ContainsKey(filter.dataType))
// {
// var param = new OracleParameter()
// {
// OracleDbType = OracelTypeDic[filter.dataType],
// ParameterName = filter.property,
// Direction = ParameterDirection.Input
// };
// if (filter.value == null)
// {
// param.Value = DBNull.Value;
// }
// else if (filter.dataType == "date")
// {
// param.Value = DateTime.Parse(filter.value);
// }
// else
// {
// param.Value = filter.value;
// }
// cmd.Parameters.Add(param);
// }
// }
// var paramer1 = new OracleParameter()
// {
// OracleDbType = OracleDbType.RefCursor,
// ParameterName = "DATATABLE",
// Direction = ParameterDirection.Output
// };
// cmd.Parameters.Add(paramer1);
// var da = new OracleDataAdapter(cmd);
// da.SelectCommand = cmd;
// DataSet ds = new DataSet("dt");
// da.Fill(ds, "dt");
// return ds.Tables[0].ToIDictionaryList();
//}
///// <summary>
///// 根据存储过程返回查询数据
///// </summary>
///// <param name="procedureName"></param>
///// <param name="filters"></param>
///// <returns></returns>
//public IList<IDictionary<string, object>> GetPagedByProcedure(string procedureName, int page, int rows, out int total, List<MyFilter> filters = null)
//{
// filters = filters == null ? new List<MyFilter>() : filters;
// filters.Add(new MyFilter() { property = "PAGE", value = page.ToString(), dataType = "number" });
// filters.Add(new MyFilter() { property = "ROWS", value = rows.ToString(), dataType = "number" });
// var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["LandSystem"].ConnectionString);
// var cmd = new OracleCommand(procedureName, conn);
// var paramsList = ModelQuery.Default.CreateSQLQuery(string.Format("SELECT T.ARGUMENT_NAME FROM user_arguments T WHERE T.OBJECT_NAME='{0}' ORDER BY T.POSITION ASC", procedureName)).List<string>();
// cmd.CommandType = CommandType.StoredProcedure;
// foreach (var item in paramsList)
// {
// var filter = filters.Where(x => x.property.ToUpper() == item).FirstOrDefault();
// if (filter == null)
// {
// continue;
// }
// if (OracelTypeDic.ContainsKey(filter.dataType))
// {
// var param = new OracleParameter()
// {
// OracleDbType = OracelTypeDic[filter.dataType],
// ParameterName = filter.property,
// Direction = ParameterDirection.Input
// };
// if (filter.value == null)
// {
// param.Value = DBNull.Value;
// }
// else if (filter.dataType == "date")
// {
// param.Value = DateTime.Parse(filter.value);
// }
// else
// {
// param.Value = filter.value;
// }
// cmd.Parameters.Add(param);
// }
// }
// var paramer1 = new OracleParameter()
// {
// OracleDbType = OracleDbType.RefCursor,
// ParameterName = "DATATABLE",
// Direction = ParameterDirection.Output
// };
// cmd.Parameters.Add(paramer1);
// var paramer2 = new OracleParameter()
// {
// OracleDbType = OracleDbType.Int64,
// ParameterName = "TOTAL",
// Direction = ParameterDirection.Output
// };
// cmd.Parameters.Add(paramer2);
// DataSet ds = new DataSet("dt");
// total = 0;
// try
// {
// conn.Open();
// cmd.ExecuteNonQuery();
// var da = new OracleDataAdapter();
// da.Fill(ds, "dt", paramer1.Value as Oracle.ManagedDataAccess.Types.OracleRefCursor);
// total = int.Parse(paramer2.Value.ToString());
// }
// catch (Exception ex)
// {
// }
// finally
// {
// conn.Close();
// }
// return ds.Tables[0].ToIDictionaryList();
//}
///// <summary>
///// OracleDbType类型转换
///// </summary>
//private static Dictionary<string, OracleDbType> OracelTypeDic = new Dictionary<string, OracleDbType>
//{
// { "varchar", OracleDbType.Varchar2 },
// { "nvarchar", OracleDbType.NVarchar2 },
// { "number", OracleDbType.Int64 },
// { "date", OracleDbType.Date },
// { "long", OracleDbType.Long }
//};
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="data">数据集</param>
/// <param name="columns">列</param>
/// <param name="sheetName">导出的Excel文件名称</param>
/// <returns></returns>
public FileResult ExportExcel(List <Dictionary <string, object> > data, List <Column> columns, string sheetName = "导出Excel")
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
columns = columns.Where(x => x.hidden != true && !string.IsNullOrEmpty(x.title)).ToList();
var headerStyle = book.CreateCellStyle();
var headerFont = book.CreateFont();
headerFont.IsBold = true;
headerFont.FontHeightInPoints = 12;
headerFont.FontName = "宋体";
headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.SetFont(headerFont);
for (var i = 0; i < columns.Count; i++)
{
var cell = row1.CreateCell(i);
cell.SetCellValue(columns[i].title);
sheet1.SetColumnWidth(i, columns[i].width * 40);
cell.CellStyle = headerStyle;
}
var contentStyle = book.CreateCellStyle();
contentStyle.CloneStyleFrom(headerStyle);
var contentFont = book.CreateFont();
contentFont.FontHeightInPoints = 12;
contentFont.FontName = "宋体";
contentStyle.SetFont(contentFont);
for (int i = 0; i < data.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (var j = 0; j < columns.Count; j++)
{
//var property = data[i].GetType().GetProperty(columns[j].field);
var value = "";
if (data[i].ContainsKey(columns[j].field) && data[i][columns[j].field] != null)
{
value = data[i][columns[j].field].ToString();
}
var cell = rowtemp.CreateCell(j);
cell.SetCellValue(value);
cell.CellStyle = contentStyle;
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", sheetName + ".xls"));
}
/// <summary>
/// 获取EXCEL数组
/// </summary>
/// <param name="dt"></param>
public static byte[] GetExcelFileByte(DataSet ds)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//设置样式
NPOI.SS.UserModel.ICellStyle style1 = book.CreateCellStyle();
NPOI.SS.UserModel.IFont font1 = book.CreateFont();
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
font1.IsBold = true;
style1.SetFont(font1);
NPOI.SS.UserModel.ICellStyle style2 = book.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
byte[] fileByte = null;
for (int k = 0; k < ds.Tables.Count; k++)
{
try
{
DataTable dt = ds.Tables[k];
if (null != dt && dt.Rows.Count > 0)
{
if (dt.TableName == null || dt.TableName == "")
{
dt.TableName = "Sheet1";
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
sheet.DefaultColumnWidth = 20;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
row.Cells[i].CellStyle = style1;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
row2.Cells[j].CellStyle = style2;
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
try
{
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
fileByte = ms.GetBuffer();
//using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
//{
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
//}
}
}
catch (Exception)
{
throw;
}
finally
{
if (null != book)
{
book.Close();
book = null;
}
}
return(fileByte);
}
protected void ExportButton_Click(object sender, EventArgs e)
{
UserSearchInfo userSearch = new UserSearchInfo();
userSearch.UserName = ShopCommon.ConvertToT <string>(UserName.Text);
userSearch.Sex = ShopCommon.ConvertToT <int>(Sex.Text);
DateTime startDate = ShopCommon.ConvertToT <DateTime>(StartDate.Text);
DateTime endDate = ShopCommon.SearchEndDate(ShopCommon.ConvertToT <DateTime>(EndDate.Text));
string userConsumeType = ShopCommon.ConvertToT <string>(UserConsumeType.Text);
userConsumeType = (userConsumeType == string.Empty) ? "OrderCount" : userConsumeType;
var data = UserBLL.StatisticsUserConsume(1, 1000, userSearch, ref Count, userConsumeType, startDate, endDate);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
sheet.DefaultColumnWidth = 18;
sheet.CreateFreezePane(0, 1, 0, 1);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue("用户");
row.CreateCell(1).SetCellValue("性别");
row.CreateCell(2).SetCellValue("订单次数");
row.CreateCell(3).SetCellValue("订单金额");
//设置表头格式
var headFont = book.CreateFont();
headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headFont.FontHeightInPoints = 10;
var headStyle = book.CreateCellStyle();
headStyle.SetFont(headFont);
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
foreach (var cell in row.Cells)
{
cell.CellStyle = headStyle;
}
foreach (DataRow dr in data.Rows)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(data.Rows.IndexOf(dr) + 1);
dataRow.CreateCell(0).SetCellValue(Convert.ToString(dr["UserName"]));
dataRow.CreateCell(1).SetCellValue(EnumHelper.ReadEnumChineseName <SexType>(Convert.ToInt32(dr["Sex"])));
dataRow.CreateCell(2).SetCellValue(Convert.ToString(dr["OrderCount"]));
dataRow.CreateCell(3).SetCellValue(Convert.ToString(dr["OrderMoney"]));
var style = book.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
foreach (var cell in dataRow.Cells)
{
cell.CellStyle = style;
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
Response.End();
}
public string DownLoadExcel()
{
var data = new List <Users>();
//List<CounterMan> data = Newtonsoft.Json.JsonConvert.DeserializeObject<List<CounterMan>>(jsonData);
//2、创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook excel = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet = excel.CreateSheet("Sheet1");
//给sheet1添加标题行
NPOI.SS.UserModel.IRow head = sheet.CreateRow(0);
head.CreateCell(0).SetCellValue("姓名");
head.CreateCell(1).SetCellValue("手机号");
head.CreateCell(2).SetCellValue("角色");
head.CreateCell(3).SetCellValue("所属区县");
head.CreateCell(4).SetCellValue("所属网格");
head.CreateCell(5).SetCellValue("岗位");
ICellStyle HeadercellStyle = excel.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = excel.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//将数据逐步写入sheet1各个行
for (int i = 0; i < data.Count; i++)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(data[i].Name);
row.CreateCell(1).SetCellValue(data[i].Mobile);
row.CreateCell(2).SetCellValue(data[i].Roles.RoleName);
row.CreateCell(3).SetCellValue(data[i].Areas);
row.CreateCell(4).SetCellValue(data[i].Grids);
row.CreateCell(5).SetCellValue(data[i].Post);
sheet.AutoSizeColumn(i);//自适应行
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
excel.Write(ms);
//ms.Seek(0, SeekOrigin.Begin);
ms.Flush();
var fileName = "Excel_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls";
//將生成的文件保存到服務器的臨時目錄里
var path = HttpContext.Current.Server.MapPath("~/UpFiles/");
string fullPath = Path.Combine(path, fileName);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
using (FileStream fs = new FileStream(fullPath, FileMode.OpenOrCreate, FileAccess.Write))
{
byte[] data1 = ms.ToArray();
fs.Write(data1, 0, data1.Length);
fs.Flush();
data1 = null;
}
var errorMessage = "you can return the errors in here!";
//返回生成的文件名
//return Newtonsoft.Json.JsonConvert.SerializeObject(new { fileName = fileName, errorMessage = errorMessage });
return(fileName);
}
public void GenerateExcelReport(string exportFileName)
{
DataSet.Payroll_KTPFundStatement dataSet = CreateDataSource();
int lastRowIndex = 0;
// Set column style
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("KTPF Contribution Report");
// Date format
NPOI.HSSF.UserModel.HSSFDataFormat format = (NPOI.HSSF.UserModel.HSSFDataFormat)workbook.CreateDataFormat();
NPOI.HSSF.UserModel.HSSFCellStyle dateCellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
dateCellStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
dateCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
dateCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
dateCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
dateCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Numeric format
NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-");
numericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
numericStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
numericStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
numericStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// String left format
NPOI.HSSF.UserModel.HSSFCellStyle stringLeftStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
stringLeftStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
stringLeftStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringLeftStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringLeftStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// String center format
NPOI.HSSF.UserModel.HSSFCellStyle stringCenterStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
stringCenterStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
stringCenterStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringCenterStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
stringCenterStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 0 style
NPOI.HSSF.UserModel.HSSFCellStyle column0Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column0Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
column0Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
column0Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column0Style.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 10, 11 style
NPOI.HSSF.UserModel.HSSFCellStyle column10To11Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column10To11Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
column10To11Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column10To11Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 4 style
NPOI.HSSF.UserModel.HSSFCellStyle column4Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column4Style.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-");
column4Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
column4Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column4Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Column 8 style
NPOI.HSSF.UserModel.HSSFCellStyle column8Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
column8Style.DataFormat = format.GetFormat("yyyy-MM-dd");
column8Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
column8Style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
column8Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
column8Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DOTTED;
// Align right
NPOI.HSSF.UserModel.HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
// Bottom border
NPOI.HSSF.UserModel.HSSFCellStyle bottomBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
bottomBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
// Bold style
NPOI.HSSF.UserModel.HSSFCellStyle boldStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
NPOI.SS.UserModel.IFont boldFont = workbook.CreateFont();
boldFont.Boldweight = 700;
boldStyle.SetFont(boldFont);
// Header Border
NPOI.HSSF.UserModel.HSSFCellStyle headerBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
headerBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
headerBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
headerBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
headerBorderStyle.SetFont(boldFont);
headerBorderStyle.WrapText = true;
headerBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// Header Left Border
NPOI.HSSF.UserModel.HSSFCellStyle headerLeftBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
headerLeftBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
headerLeftBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
headerLeftBorderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
headerLeftBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.DOTTED;
headerLeftBorderStyle.SetFont(boldFont);
headerLeftBorderStyle.WrapText = true;
headerLeftBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// Header Right Border
NPOI.HSSF.UserModel.HSSFCellStyle headerRightBorderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
headerRightBorderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
headerRightBorderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
headerRightBorderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
headerRightBorderStyle.SetFont(boldFont);
headerRightBorderStyle.WrapText = true;
headerRightBorderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// Total numeric format
NPOI.HSSF.UserModel.HSSFCellStyle totalNumericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
totalNumericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00);-");
totalNumericStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
totalNumericStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
// Grey color
NPOI.HSSF.UserModel.HSSFCellStyle grey25Style = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
grey25Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
grey25Style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
grey25Style.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
grey25Style.SetFont(boldFont);
// Yellow color
NPOI.HSSF.UserModel.HSSFCellStyle yellowStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
yellowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
yellowStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index;
yellowStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
yellowStyle.SetFont(boldFont);
// Set column width
worksheet.SetColumnWidth(0, 12 * 256);
worksheet.SetColumnWidth(1, 20 * 256);
worksheet.SetColumnWidth(2, 15 * 256);
worksheet.SetColumnWidth(3, 15 * 256);
worksheet.SetColumnWidth(4, 15 * 256);
worksheet.SetColumnWidth(5, 7 * 256);
worksheet.SetColumnWidth(6, 15 * 256);
worksheet.SetColumnWidth(7, 15 * 256);
worksheet.SetColumnWidth(8, 15 * 256);
worksheet.SetColumnWidth(9, 15 * 256);
worksheet.SetColumnWidth(10, 15 * 256);
worksheet.SetColumnWidth(11, 25 * 256);
// Set column title
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(0);
NPOI.HSSF.UserModel.HSSFCell headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
headerCell.SetCellValue("KTPF Contribution Report");
headerCell.CellStyle = boldStyle;
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(1);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
if (dataSet.ORSOPlan.Rows.Count > 0)
{
Payroll_KTPFundStatement.ORSOPlanRow m_orsoPlan = (Payroll_KTPFundStatement.ORSOPlanRow)dataSet.ORSOPlan.Rows[0];
headerCell.SetCellValue(m_orsoPlan.ORSOPlanCompanyName);
}
headerCell.CellStyle = boldStyle;
string m_reportPeriod = "";
if (_payPeriodFr.Month != _payPeriodTo.Month)
{
m_reportPeriod = _payPeriodFr.ToString("dd MMMM yyyy") + " - " + _payPeriodTo.ToString("dd MMMM yyyy");
}
else
{
m_reportPeriod = _payPeriodFr.ToString("MMMM yyyy");
}
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(2);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
headerCell.SetCellValue(m_reportPeriod);
headerCell.CellStyle = boldStyle;
// Merge header
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(3);
// Merge cell from 5-8
NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 5, (short)8);
worksheet.AddMergedRegion(cellRangeAddress);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5);
headerCell.SetCellValue("For New Joiner");
headerCell.CellStyle = yellowStyle;
((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
// Merge cell from 9-10
cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(3, (short)3, 9, (short)10);
worksheet.AddMergedRegion(cellRangeAddress);
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9);
headerCell.SetCellValue("For Resigned Staff");
headerCell.CellStyle = grey25Style;
((NPOI.HSSF.UserModel.HSSFSheet)worksheet).SetEnclosedBorderOfRegion(cellRangeAddress, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
headerRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(4);
headerRow.HeightInPoints = 40;
// column A
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(0);
headerCell.SetCellValue("Member ID");
headerCell.CellStyle = headerLeftBorderStyle;
// column B
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(1);
headerCell.SetCellValue("Employee Name");
headerCell.CellStyle = headerBorderStyle;
// column C
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(2);
headerCell.SetCellValue("Basic Salary");
headerCell.CellStyle = headerBorderStyle;
// column D
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(3);
headerCell.SetCellValue("KTPF Contribution");
headerCell.CellStyle = headerBorderStyle;
// column E
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(4);
headerCell.SetCellValue("Employer MPF Contribution");
headerCell.CellStyle = headerRightBorderStyle;
// column F
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(5);
headerCell.SetCellValue("Sex");
headerCell.CellStyle = headerBorderStyle;
// column G
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(6);
headerCell.SetCellValue("Date Of Birth");
headerCell.CellStyle = headerBorderStyle;
// column H
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(7);
headerCell.SetCellValue("Date Join");
headerCell.CellStyle = headerBorderStyle;
// column I
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(8);
headerCell.SetCellValue("Effective Date");
headerCell.CellStyle = headerRightBorderStyle;
// column J
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(9);
headerCell.SetCellValue("Termination Date");
headerCell.CellStyle = headerBorderStyle;
// column K
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(10);
headerCell.SetCellValue("Termination Mode");
headerCell.CellStyle = headerRightBorderStyle;
// column L
headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(11);
headerCell.SetCellValue("Remarks");
headerCell.CellStyle = headerRightBorderStyle;
// Create total
int length = dataSet.ExistingMember.Rows.Count + 5;
NPOI.HSSF.UserModel.HSSFRow totalRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + length);
NPOI.HSSF.UserModel.HSSFCell totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(0);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(5);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(6);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(7);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(8);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(9);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(10);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(11);
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(1);
totalCell.SetCellValue("Total");
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(2);
totalCell.SetCellFormula("SUM(C5:C" + totalRow.RowNum.ToString("0") + ")");
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(3);
totalCell.SetCellFormula("SUM(D5:D" + totalRow.RowNum.ToString("0") + ")");
totalCell.CellStyle = totalNumericStyle;
totalCell = (NPOI.HSSF.UserModel.HSSFCell)totalRow.CreateCell(4);
totalCell.SetCellFormula("SUM(E5:E" + totalRow.RowNum.ToString("0") + ")");
totalCell.CellStyle = totalNumericStyle;
int rowLength = 0;
// Set value for every row
dataSet.ExistingMember.DefaultView.Sort = "EmpName";
DataTable m_table = dataSet.ExistingMember.DefaultView.ToTable();
foreach (DataRow m_row in m_table.Rows)
//foreach (DataRow row in tmpDataTable.Rows)
{
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(lastRowIndex + 5);
rowLength++;
if (lastRowIndex == (m_table.Rows.Count))
{
detailRow.RowStyle = bottomBorderStyle;
}
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(0);
cell.SetCellValue(m_row["MemberID"].ToString());
cell.CellStyle = column0Style;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(1);
cell.SetCellValue(m_row["EmpName"].ToString());
cell.CellStyle = stringLeftStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(2);
cell.SetCellValue((double)m_row["RelevantIncome"]);
cell.CellStyle = numericStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(3);
cell.SetCellValue((double)m_row["ER"]);
cell.CellStyle = numericStyle;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(4);
cell.SetCellValue((double)m_row["MpfMCER"]);
cell.CellStyle = column4Style;
DateTime m_periodFrom = (DateTime)m_row["PeriodFrom"];
DateTime m_effDate = (DateTime)m_row["OrsoEffDate"];
{
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(5);
cell.CellStyle = stringCenterStyle;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
cell.SetCellValue(m_row["EmpSex"].ToString());
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(6);
cell.CellStyle = dateCellStyle;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["EmpDOB"]);
}
catch { }
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(7);
cell.CellStyle = dateCellStyle;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["EmpDateJoin"]);
}
catch { }
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(8);
cell.CellStyle = column8Style;
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["OrsoEffDate"]);
}
catch { }
}
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(9);
cell.CellStyle = dateCellStyle;
cell.SetCellValue("");// put something into the cell and so as the border line can be shown
if (m_periodFrom.Year == m_effDate.Year && m_periodFrom.Year == m_effDate.Month)
{
try
{
cell.SetCellValue((DateTime)m_row["LastEmploymentDate"]);
}
catch { }
}
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(10);
cell.SetCellValue(m_row["TermCode"].ToString());
cell.CellStyle = column10To11Style;
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(11);
//cell.SetCellValue(row[FIELD_REMARKS].ToString());
cell.CellStyle = column10To11Style;
lastRowIndex++;
}
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
}
private void GenerateExcelReport(DataSet dataSet, string exportFileName)
{
//ExcelLibrary.SpreadSheet.Worksheet execlWorksheet = null;
DataView tmpView = new DataView(dataSet.Tables["Hierarchy"]);
tmpView.Sort = "CompanyID, PayGroupID, HElementID Desc,CostCenterID";
DataTable sortedHierarchyTable = tmpView.ToTable();
int curringCompanyID = 0;
string currentCompany = string.Empty;
int recordCount = 0;
int lastRowIndex = 0, lastColumnIndex = 0;
//ExcelLibrary.SpreadSheet.Workbook excelWorkbook = new ExcelLibrary.SpreadSheet.Workbook();
//execlWorksheet = new ExcelLibrary.SpreadSheet.Worksheet("CostAllocation");
//excelWorkbook.Worksheets.Add(execlWorksheet);
NPOI.HSSF.UserModel.HSSFWorkbook excelWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet excelWorksheet = (NPOI.HSSF.UserModel.HSSFSheet)excelWorkbook.CreateSheet("CostAllocation");
NPOI.HSSF.UserModel.HSSFRow headerRow = null;
NPOI.HSSF.UserModel.HSSFFont headerFont = (NPOI.HSSF.UserModel.HSSFFont)excelWorkbook.CreateFont();
headerFont.Boldweight = 1;
NPOI.HSSF.UserModel.HSSFCellStyle headerStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle();
headerStyle.SetFont(headerFont);
//headerStyle.BorderBottom = NPOI.SS.UserModel.CellBorderType.THICK;
NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle();
numericStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00");
NPOI.HSSF.UserModel.HSSFFont subTotalFont = (NPOI.HSSF.UserModel.HSSFFont)excelWorkbook.CreateFont();
subTotalFont.Boldweight = 1;
NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)excelWorkbook.CreateCellStyle();
subTotalStyle.SetFont(subTotalFont);
//subTotalStyle.BorderTop = NPOI.SS.UserModel.CellBorderType.THICK;
subTotalStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("#,##0.00");
foreach (DataRow row in sortedHierarchyTable.Rows)
{
if (curringCompanyID != (int)row["CompanyID"])
{
if (recordCount > 0)
{
lastRowIndex++;
AddSubTotalRow(excelWorksheet, currentCompany, recordCount, lastRowIndex, lastColumnIndex, headerRow, subTotalStyle);
recordCount = 0;
lastRowIndex++;
}
//else
{
//execlWorksheet.Cells[lastRowIndex, 0].Value = row["Company"].ToString();
//lastRowIndex += FIELD_HEADER_ROW;
//execlWorksheet.Cells[lastRowIndex, PAYROLL_GRUOP_COLUMN].Value = "Payroll Group";
//execlWorksheet.Cells[lastRowIndex, COST_CENTER_COLUMN].Value = "Cost Ctr";
//execlWorksheet.Cells[lastRowIndex, HIERARCHY_COLUMN].Value = "Hierarchy";
NPOI.HSSF.UserModel.HSSFRow companyRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex);
companyRow.CreateCell(0).SetCellValue(row["Company"].ToString());
lastRowIndex += FIELD_HEADER_ROW;
headerRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex);
headerRow.CreateCell(PAYROLL_GRUOP_COLUMN).SetCellValue("Payroll Group");
headerRow.CreateCell(COST_CENTER_COLUMN).SetCellValue("Cost Ctr");
headerRow.CreateCell(HIERARCHY_COLUMN).SetCellValue("Hierarchy");
}
curringCompanyID = (int)row["CompanyID"];
currentCompany = row["Company"].ToString();
}
lastRowIndex++;
recordCount++;
//lastRowIndex = FIELD_HEADER_ROW + recordCount;
//execlWorksheet.Cells[lastRowIndex, PAYROLL_GRUOP_COLUMN].Value = row["PayrollGroupDesc"];
//execlWorksheet.Cells[lastRowIndex, COST_CENTER_COLUMN].Value = row["CostCenterDesc"];
//execlWorksheet.Cells[lastRowIndex, HIERARCHY_COLUMN].Value = row["HierarchyDesc"];
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)excelWorksheet.CreateRow(lastRowIndex);
detailRow.CreateCell(PAYROLL_GRUOP_COLUMN).SetCellValue(row["PayrollGroupDesc"].ToString());
detailRow.CreateCell(COST_CENTER_COLUMN).SetCellValue(row["CostCenterDesc"].ToString());
detailRow.CreateCell(HIERARCHY_COLUMN).SetCellValue(row["HierarchyDesc"].ToString());
lastColumnIndex = PAYMENTDETAIL_START_COLUMN - 1;
DataRow[] paymentRows = dataSet.Tables["Payment"].Select("ID=" + row["ID"]);
if (paymentRows.Length > 0)
{
//double netPayment = 0;
DataRow paymentRow = paymentRows[0];
foreach (DataColumn column in paymentRow.Table.Columns)
{
if (!column.ColumnName.Equals("ID", StringComparison.CurrentCultureIgnoreCase))
{
lastColumnIndex++;
if (recordCount == 1)
{
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex].Value = column.ColumnName;
headerRow.CreateCell(lastColumnIndex).SetCellValue(column.ColumnName);
}
if (!paymentRow.IsNull(column))
{
//ExcelLibrary.SpreadSheet.Cell cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex];
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2);
//cell.FormatString = "#,##0.00";
//netPayment += HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2);
NPOI.HSSF.UserModel.HSSFCell dataCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex);
dataCell.CellStyle = numericStyle;
dataCell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)paymentRow[column], 2, 2));
}
}
}
lastColumnIndex++;
if (recordCount == 1)
{
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex].Value = "NET PAYMENT";
headerRow.CreateCell(lastColumnIndex).SetCellValue("NET PAYMENT");
}
//ExcelLibrary.SpreadSheet.Cell netPaymentCell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex];
//netPaymentCell.FormatString = "#,##0.00";
//netPaymentCell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netPayment, 2, 2);
NPOI.HSSF.UserModel.HSSFCell netPaymentCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex);
netPaymentCell.CellStyle = numericStyle;
netPaymentCell.CellFormula = "SUM(" + ToCellString(lastRowIndex, PAYMENTDETAIL_START_COLUMN) + ":" + ToCellString(lastRowIndex, lastColumnIndex - 1) + ")";
}
lastColumnIndex++;
DataRow[] contributionRows = dataSet.Tables["contribution"].Select("ID=" + row["ID"]);
if (contributionRows.Length > 0)
{
DataRow contributionRow = contributionRows[0];
if (recordCount == 1)
{
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 1].Value = "MPF (ER)";
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 2].Value = "VC (ER)";
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 3].Value = "P-FUND (ER)";
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 4].Value = "Total Contribution (ER)";
//execlWorksheet.Cells[FIELD_HEADER_ROW, lastColumnIndex + 6].Value = "Total Contribution (EE)";
headerRow.CreateCell(lastColumnIndex + 1).SetCellValue("MPF (ER)");
headerRow.CreateCell(lastColumnIndex + 2).SetCellValue("VC (ER)");
headerRow.CreateCell(lastColumnIndex + 3).SetCellValue("P-FUND (ER)");
headerRow.CreateCell(lastColumnIndex + 4).SetCellValue("Total Contribution (ER)");
headerRow.CreateCell(lastColumnIndex + 6).SetCellValue("Total Contribution (EE)");
}
//ExcelLibrary.SpreadSheet.Cell cell=execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 1];
//cell.FormatString = "#,##0.00";
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["MCER"], 2, 2);
//cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 2];
//cell.FormatString = "#,##0.00";
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["VCER"], 2, 2);
//cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 3];
//cell.FormatString = "#,##0.00";
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"], 2, 2);
//cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 4];
//cell.FormatString = "#,##0.00";
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"] + (double)contributionRow["MCER"] + (double)contributionRow["VCER"], 2, 2);
//cell = execlWorksheet.Cells[lastRowIndex, lastColumnIndex + 6];
//cell.FormatString = "#,##0.00";
//cell.Value = HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDEE"] + (double)contributionRow["MCEE"] + (double)contributionRow["VCEE"], 2, 2);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 1);
cell.CellStyle = numericStyle;
cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["MCER"], 2, 2));
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 2);
cell.CellStyle = numericStyle;
cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["VCER"], 2, 2));
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 3);
cell.CellStyle = numericStyle;
cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"], 2, 2));
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 4);
cell.CellStyle = numericStyle;
cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDER"] + (double)contributionRow["MCER"] + (double)contributionRow["VCER"], 2, 2));
cell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(lastColumnIndex + 6);
cell.CellStyle = numericStyle;
cell.SetCellValue(HROne.CommonLib.GenericRoundingFunctions.RoundingTo((double)contributionRow["PFUNDEE"] + (double)contributionRow["MCEE"] + (double)contributionRow["VCEE"], 2, 2));
}
lastColumnIndex += 6;
}
lastRowIndex++;
AddSubTotalRow(excelWorksheet, currentCompany, recordCount, lastRowIndex, lastColumnIndex, headerRow, subTotalStyle);
//excelWorkbook.Save(exportFileName);
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
excelWorkbook.Write(file);
file.Close();
}
private void createWorkSheetHeaderRows(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet,
List <EHierarchyLevel> hLevelList, int countDay, DateTime periodFromDate, DateTime periodToDate)
{
// Create column header styles
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
columnHeaderStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
NPOI.HSSF.UserModel.HSSFFont columnHeaderFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
columnHeaderFont.Boldweight = 900;
columnHeaderStyle.SetFont(columnHeaderFont);
columnHeaderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
columnHeaderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
columnHeaderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
columnHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleFirstTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleFirstTop.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleFirstTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;
columnHeaderStyleFirstTop.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleMiddleTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleMiddleTop.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleMiddleTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleLastTop = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleLastTop.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleLastTop.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM;
columnHeaderStyleLastTop.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleFirstBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleFirstBottom.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleFirstBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM;
columnHeaderStyleFirstBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleMiddleBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleMiddleBottom.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleMiddleBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleLastBottom = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleLastBottom.CloneStyleFrom(columnHeaderStyle);
columnHeaderStyleLastBottom.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM;
columnHeaderStyleLastBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM;
// Create column header rows
const int COLUMN_HEADER_ROW = 2;
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow1 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW);
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow2 = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW + 1);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell;
int colpos = 0;
foreach (EHierarchyLevel hLevel in hLevelList)
{
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
columnHeaderCell.SetCellValue(hLevel.HLevelDesc);
columnHeaderCell.CellStyle = columnHeaderStyleFirstTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
columnHeaderCell.SetCellValue(string.Empty);
columnHeaderCell.CellStyle = columnHeaderStyleFirstBottom;
colpos++;
}
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
columnHeaderCell.SetCellValue("Name");
columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
columnHeaderCell.SetCellValue(string.Empty);
columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;
colpos++;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
columnHeaderCell.SetCellValue("Title");
columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
columnHeaderCell.SetCellValue(string.Empty);
columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;
colpos++;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
columnHeaderCell.SetCellValue("Staff No.");
columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
columnHeaderCell.SetCellValue(string.Empty);
columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;
colpos++;
for (int colIdx = 0; colIdx < colpos; colIdx++)
{
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(COLUMN_HEADER_ROW, COLUMN_HEADER_ROW + 1, colIdx, colIdx));
}
for (int dayIdx = 0; dayIdx < countDay; dayIdx++)
{
DateTime currentDate = periodFromDate.AddDays(dayIdx);
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos + dayIdx);
columnHeaderCell.SetCellValue(currentDate.ToString("dd-MMM-yy"));
columnHeaderCell.CellStyle = columnHeaderStyleMiddleTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos + dayIdx);
columnHeaderCell.SetCellValue(currentDate.ToString("ddd"));
columnHeaderCell.CellStyle = columnHeaderStyleMiddleBottom;
}
colpos += countDay;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow1.CreateCell(colpos);
columnHeaderCell.SetCellValue("Signature");
columnHeaderCell.CellStyle = columnHeaderStyleLastTop;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow2.CreateCell(colpos);
columnHeaderCell.SetCellValue(string.Empty);
columnHeaderCell.CellStyle = columnHeaderStyleLastBottom;
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(COLUMN_HEADER_ROW, COLUMN_HEADER_ROW + 1, colpos, colpos));
}
private void 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();
}
public byte[] ExportXls(List <GroupOperation> list, GroupByEnum groupBy)
{
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Plan 1");
int rowNumer = 0;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowNumer);
NPOI.SS.UserModel.ICell cell;
NPOI.SS.UserModel.IFont hFont = workbook.CreateFont();
hFont.FontHeightInPoints = 12;
hFont.FontName = "Arial";
NPOI.SS.UserModel.ICellStyle styleHeader = workbook.CreateCellStyle();
styleHeader.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
styleHeader.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
styleHeader.SetFont(hFont);
NPOI.SS.UserModel.ICellStyle styleDisabled = workbook.CreateCellStyle();
styleDisabled.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
styleDisabled.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
styleDisabled.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
var listColumns = new List <ExportColumnInfo>();
listColumns.Add(new ExportColumnInfo()
{
Name = groupBy.GetDescription()
});
listColumns.Add(new ExportColumnInfo()
{
Name = "Quantidade"
});
listColumns.Add(new ExportColumnInfo()
{
Name = "Preço Médio"
});
for (int i = 0; i < listColumns.Count; i++)
{
cell = row.CreateCell(i);
cell.SetCellValue(listColumns[i].Name);
cell.CellStyle = styleHeader;
}
//---- row
foreach (var item in list)
{
rowNumer++;
row = sheet.CreateRow(rowNumer);
row.CreateCell(0).SetCellValue(item.AccountNumber ?? item.Active ?? item.OperationType);
row.CreateCell(1).SetCellValue(item.Quantity);
row.CreateCell(2).SetCellValue((double)item.AveragePrice);
}
for (int i = 0; i < listColumns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
byte[] byteArray;
using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
{
workbook.Write(stream);
byteArray = stream.ToArray();
}
return(byteArray);
}
protected override void GenerateWorkbookDetail(NPOI.HSSF.UserModel.HSSFWorkbook workBook, System.Data.DataSet dataSet)
{
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.CreateSheet("Payroll Allocation Report - Detail");
ushort rowPos = 0;
DataTable empInfoTable = dataSet.Tables["EmpInfo"];
DataTable paymentTable = dataSet.Tables["payment"];
DataTable costCenterDetailTable = dataSet.Tables["CostCenterDetail"];
DataTable hierarchyTable = dataSet.Tables["hierarchy"];
DataTable payPeriodTable = dataSet.Tables["payPeriod"];
DataTable hierarchyTotalTable = new DataTable("HierarchyTotal"); // use for calculate local hierarchy total
hierarchyTotalTable.Columns.Add("company", typeof(string));
hierarchyTotalTable.Columns.Add("LevelDesc", typeof(string));
hierarchyTotalTable.Columns.Add("payPeriodFr", typeof(DateTime));
hierarchyTotalTable.Columns.Add("ee", typeof(double));
hierarchyTotalTable.Columns.Add("er", typeof(double));
hierarchyTotalTable.Columns.Add("netAmount", typeof(double));
NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workBook.CreateFont();
boldFont.Boldweight = 700;
NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
reportHeaderStyle.SetFont(boldFont);
reportHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
NPOI.HSSF.UserModel.HSSFCellStyle groupHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
groupHeaderStyle.SetFont(boldFont);
NPOI.HSSF.UserModel.HSSFCellStyle monthHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
monthHeaderStyle.SetFont(boldFont);
monthHeaderStyle.DataFormat = workBook.CreateDataFormat().GetFormat("MMM-yyyy");
monthHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
columnHeaderStyle.SetFont(boldFont);
columnHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle detailNumberStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
detailNumberStyle.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00)");
detailNumberStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
NPOI.HSSF.UserModel.HSSFCellStyle detailTextStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
detailTextStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
subTotalStyle.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00)");
subTotalStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
DataRow[] EmpInfoRows = dataSet.Tables["EmpInfo"].Select("", "Company, " + empInfoTable.Columns[hierarchyLevelGroupingFieldName].ColumnName + ", Employee No.");
string currentCompany = "#$%@$@#$"; //string.Empty;
string currentHierarchyGroup = string.Empty;
string currentEmployeeNo = string.Empty;
string tmpCompany = "";
string tmpHierarchy = "";
string tmpEmployeeNo = "";
ushort groupRowCount = 0;
bool sectionEnded = false;
if (bolShowIndividual == true)
{
summaryStart = Convert.ToUInt16(4 + hierarchyTable.Rows.Count);
}
else
{
summaryStart = 1;
}
reportEndCol = Convert.ToUInt16(summaryStart + (payPeriodTable.Rows.Count * 4) - 1);
foreach (DataRow EmpInfoRow in EmpInfoRows)
{
tmpCompany = EmpInfoRow["Company"].ToString();
tmpEmployeeNo = EmpInfoRow["Employee No."].ToString();
tmpHierarchy = EmpInfoRow[hierarchyLevelGroupingFieldName].ToString();
if (EmpInfoRows[0] == EmpInfoRow)
{
currentHierarchyGroup = tmpHierarchy;
currentCompany = tmpCompany;
rowPos = GenerateHeader(workSheet, PeriodFrom, PeriodTo, rowPos, reportHeaderStyle, groupHeaderStyle);
rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle);
rowPos++;
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
}
sectionEnded = (!currentHierarchyGroup.Equals(tmpHierarchy, StringComparison.CurrentCultureIgnoreCase) ||
!currentCompany.Equals(tmpCompany, StringComparison.CurrentCultureIgnoreCase));
if (sectionEnded)
{
if (bolShowIndividual == true)
{
// print hirarchy total if show employee detail
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
rowPos += 2;
}
else
{
// just print hierarchy line (i.e. not subtotal) if employee detail not shown
ushort colPos = 0;
NPOI.HSSF.UserModel.HSSFRow sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)rowPos);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell((int)colPos);
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue(currentHierarchyGroup);
cell.CellStyle = detailTextStyle;
colPos++;
foreach (DataRow m_hiearchyTotalRow in hierarchyTotalTable.Select("LevelDesc = '" + currentHierarchyGroup + "' ", "payPeriodFr"))
{
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["EE"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["netAmount"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["ER"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)sheetRow.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")";
cell.CellStyle = detailNumberStyle;
colPos++;
}
rowPos++;
groupRowCount++;
}
if (!currentCompany.Equals(tmpCompany, StringComparison.CurrentCultureIgnoreCase))
{
if (bolShowIndividual == true)
{
// print company header and column header for new company (in show-employee mode)
rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle);
rowPos++;
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
}
else
{
// print a summary for previous company (in hide employee info mode)
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
rowPos += 2;
rowPos = GenerateCompanyHeader(workSheet, dataSet, tmpCompany, rowPos, groupHeaderStyle);
rowPos++;
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
}
currentCompany = tmpCompany;
currentHierarchyGroup = tmpHierarchy;
groupRowCount = 0;
// print company information for new company
}
else if (!currentHierarchyGroup.Equals(tmpHierarchy, StringComparison.CurrentCultureIgnoreCase))
{
if (bolShowIndividual == true)
{
rowPos = GenerateColumnHeader(workSheet, dataSet, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, monthHeaderStyle, columnHeaderStyle);
groupRowCount = 0;
}
else
{
}
currentHierarchyGroup = tmpHierarchy;
}
hierarchyTotalTable.Clear();
}
if (bolShowIndividual == true)
{
groupRowCount++;
NPOI.HSSF.UserModel.HSSFRow m_sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowPos);
NPOI.HSSF.UserModel.HSSFCell cell;
ushort colPos = 0;
// Employee Number
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Employee No."].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// Employee Name
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Employee Name"].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// Alias
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Alias"].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// Chinese Name
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow["Chinese Name"].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
// hierarchy
foreach (DataRow m_hierarchyRow in hierarchyTable.Rows)
{
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(EmpInfoRow[m_hierarchyRow["LevelDesc"].ToString()].ToString());
cell.CellStyle = detailTextStyle;
colPos++;
}
foreach (DataRow m_payPeriodRow in payPeriodTable.Rows)
{
//DBFilter m_paymentFilter = new DBFilter();
//m_paymentFilter.add(new Match("payPeriodFr", m_payPeriodRow["payPeriodFr"]));
//m_paymentFilter.add(new Match("EmpID", row["EmpID"]));
double m_er = 0;
double m_ee = 0;
double m_netAmount = 0;
foreach (DataRow m_paymentRow in paymentTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " +
"EmpID=" + EmpInfoRow["EmpID"].ToString()))
{
m_ee += (double)m_paymentRow["EE"];
m_er += (double)m_paymentRow["ER"];
m_netAmount += (double)m_paymentRow["netAmount"];
}
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(m_ee);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(m_netAmount);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(m_er);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")";
cell.CellStyle = detailNumberStyle;
colPos++;
}
rowPos++;
}
else
{
foreach (DataRow m_payPeriodRow in payPeriodTable.Rows)
{
if (tmpCompany == "Magazines International (Asia) Limited - SALES" && tmpHierarchy == "Sales Division")
{
double m_trash = 0;
}
double m_er = 0;
double m_ee = 0;
double m_netAmount = 0;
foreach (DataRow m_paymentRow in paymentTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " +
"EmpID=" + EmpInfoRow["EmpID"].ToString()))
{
m_ee += (double)m_paymentRow["EE"];
m_er += (double)m_paymentRow["ER"];
m_netAmount += (double)m_paymentRow["netAmount"];
}
// find from local HierarchyTotal table and add the new employee amounts
DataRow[] m_hierarchyTotalRows = hierarchyTotalTable.Select("payPeriodFr='" + ((DateTime)m_payPeriodRow["payPeriodFr"]).ToString("yyyy-MM-dd") + "' AND " +
// "Company='" + tmpCompany + "' AND " +
"LevelDesc='" + tmpHierarchy + "' ");
if (m_hierarchyTotalRows.Length > 0)
{
m_hierarchyTotalRows[0]["EE"] = (double)m_hierarchyTotalRows[0]["EE"] + m_ee;
m_hierarchyTotalRows[0]["ER"] = (double)m_hierarchyTotalRows[0]["ER"] + m_er;
m_hierarchyTotalRows[0]["netAmount"] = (double)m_hierarchyTotalRows[0]["netAmount"] + m_netAmount;
}
else
{
DataRow m_newHierarchyTotal = hierarchyTotalTable.Rows.Add();
m_newHierarchyTotal["company"] = tmpCompany;
m_newHierarchyTotal["LevelDesc"] = tmpHierarchy;
m_newHierarchyTotal["payPeriodFr"] = (DateTime)m_payPeriodRow["payPeriodFr"];
m_newHierarchyTotal["EE"] = m_ee;
m_newHierarchyTotal["ER"] = m_er;
m_newHierarchyTotal["netAmount"] = m_netAmount;
}
}
}
}
// insert last hierarchy total
if (bolShowIndividual == true)
{
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
}
else
{
NPOI.HSSF.UserModel.HSSFRow m_sheetRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow((int)rowPos);
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(0);
ushort colPos = 0;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue(tmpHierarchy);
cell.CellStyle = detailTextStyle;
colPos++;
foreach (DataRow m_hiearchyTotalRow in hierarchyTotalTable.Select("LevelDesc = '" + tmpHierarchy + "' ", "payPeriodFr"))
{
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["EE"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["netAmount"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.SetCellValue((double)m_hiearchyTotalRow["ER"]);
cell.CellStyle = detailNumberStyle;
colPos++;
cell = (NPOI.HSSF.UserModel.HSSFCell)m_sheetRow.CreateCell(colPos);
cell.CellFormula = "SUM(" + ToCellString(rowPos, colPos - 3) + ":" + ToCellString(rowPos, colPos - 1) + ")";
cell.CellStyle = detailNumberStyle;
colPos++;
}
groupRowCount++;
rowPos++;
rowPos = GenerateHierarchyTotal(workSheet, rowPos, subTotalStyle, groupRowCount);
}
for (int i = 0; i <= reportEndCol; i++)
{
if (i < summaryStart)
{
workSheet.SetColumnWidth(i, 15 * 254);
}
else
{
workSheet.SetColumnWidth(i, 14 * 254);
}
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="savepath">导出文件完整路径（包括文件名）</param>
/// <param name="dt">数据源</param>
/// <param name="widths">列宽集合</param>
public static void ExportExcel(string savePath, DataTable dt, string title, List <int> widths = null)
{
IWorkbook book = null;
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet("数据清单");
var headerIndex = 0;
if (title.Length > 0)
{
//第1行添加标题
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Rows[0].ItemArray.Length - 1));
headerIndex = 1;
}
//设置行高 Height的单位是1/20个点。例：设置高度为30个点
IRow row = sheet.CreateRow(0);
row.Height = 30 * 20;
ICell titleCell = row.CreateCell(0);
titleCell.SetCellValue(title);
titleCell.CellStyle.Alignment = HorizontalAlignment.Left;
titleCell.CellStyle.VerticalAlignment = VerticalAlignment.Center;
IFont titleFont = book.CreateFont();
titleFont.FontHeightInPoints = 11;
titleCell.CellStyle.SetFont(titleFont);
if (widths != null)
{
//设置列宽
for (int i = 0; i < widths.Count; i++)
{
sheet.SetColumnWidth(i, widths[i] * 256); //列宽单位为 1/256个字符
}
}
int index = 0;
// 添加表头
row = sheet.CreateRow(headerIndex);
foreach (DataColumn item in dt.Columns)
{
ICell cell = row.CreateCell(index);
cell.SetCellType(CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + headerIndex + 1); // 添加数据 从第3行开始
foreach (DataColumn item in dt.Columns)
{
ICell cell = row.CreateCell(index);
cell.SetCellType(CellType.String);
cell.SetCellValue(dt.Rows[i][item.ColumnName].ToString());
index++;
}
}
// 写入
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
book = null;
using (FileStream fs = new FileStream(savePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
public static bool DataGridview2Sheet(System.Windows.Forms.DataGridView dataGridView1, string tableName)
{
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet c = wb.CreateSheet(tableName);
List <DataGridViewColumn> ListColumns = new List <DataGridViewColumn>();
foreach (DataGridViewColumn i in dataGridView1.Columns)
{
if (i.Visible == true)
{
ListColumns.Add(i);
}
}
if (dataGridView1.Rows.Count <= 0)
{
return(false);
}
foreach (DataGridViewColumn dc in ListColumns)
{
if (dc.Visible == false)
{
continue;
}
if (dc.ValueType == typeof(int) || dc.ValueType == typeof(decimal) || dc.ValueType == typeof(double))
{
c.SetColumnWidth(dc.Index, 10 * 256);
}
else
{
c.SetColumnWidth(dc.Index, 20 * 256);
}
}
#region 表头
NPOI.SS.UserModel.IRow RowHeader = c.CreateRow(0);
var FirstCell = RowHeader.CreateCell(0);
FirstCell.SetCellValue(BugsBox.Pharmacy.AppClient.Common.AppClientContext.Config.Store.Name + tableName);
NPOI.SS.UserModel.ICellStyle cellstyleHeader = wb.CreateCellStyle();
cellstyleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cellstyleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.SS.UserModel.IFont CellFontHeader = wb.CreateFont();
CellFontHeader.FontName = "微软雅黑";
CellFontHeader.FontHeightInPoints = 16;
cellstyleHeader.SetFont(CellFontHeader);
cellstyleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
FirstCell.CellStyle = cellstyleHeader;
c.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, ListColumns.Count - 1));
#endregion
#region 标题行 居中并且有框线
NPOI.SS.UserModel.ICellStyle CellStyleTitles = wb.CreateCellStyle();
CellStyleTitles.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleTitles.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.SS.UserModel.IRow RowTitle = c.CreateRow(1);
int cindex = 0;
foreach (DataGridViewColumn hc in ListColumns)
{
if (!hc.Visible)
{
continue;
}
NPOI.SS.UserModel.ICell cell = RowTitle.CreateCell(cindex);
cindex++;
cell.CellStyle = CellStyleTitles;
if (!string.IsNullOrEmpty(hc.HeaderText))
{
cell.SetCellValue(hc.HeaderText);
}
}
#endregion
#region 列表 有框线,默认左对齐
NPOI.SS.UserModel.ICellStyle CellStyleLeftAlignmentCell = wb.CreateCellStyle();
CellStyleLeftAlignmentCell.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
CellStyleLeftAlignmentCell.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
foreach (DataGridViewRow i in dataGridView1.Rows)
{
NPOI.SS.UserModel.IRow row = c.CreateRow(i.Index + 2);
cindex = 0;
foreach (DataGridViewCell col in i.Cells)
{
if (!col.Visible)
{
continue;
}
NPOI.SS.UserModel.ICell xcell = row.CreateCell(cindex);
cindex++;
//设置居中对齐，如果是string则左对齐
if (col.ValueType == typeof(string) || col.ValueType == typeof(Guid))
{
xcell.CellStyle = CellStyleLeftAlignmentCell;//默认左对齐的风格
}
else
{
xcell.CellStyle = CellStyleTitles;//默认居中对齐的风格，与标题栏一致
}
if (col.Value == null)
{
continue;
}
if (col.ValueType == typeof(string))
{
xcell.SetCellValue(col.Value.ToString());
}
else
if (col.ValueType == typeof(decimal) || col.ValueType == typeof(int) || col.ValueType == typeof(double))
{
xcell.SetCellValue(double.Parse(col.Value.ToString()));
}
else
{
xcell.SetCellValue(col.Value.ToString());
}
}
}
#endregion
using (SaveFileDialog sfd = new SaveFileDialog())
{
sfd.Filter = "Excel电子表格|*.xls";
sfd.FileName = tableName + DateTime.Now.Ticks.ToString();
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
using (System.IO.FileStream fs = System.IO.File.OpenWrite(sfd.FileName))
{
wb.Write(fs);
MessageBox.Show("导出成功！");
}
}
catch (System.IO.IOException ex)
{
MessageBox.Show("导出失败！\n" + ex.Message);
return(false);
}
}
}
return(true);
}
/// <summary>
/// 导出问卷数据
/// </summary>
/// <param name="list"></param>
public string CreateExcel()
{
list = (from r in _db.korea_record
join emp in _db.korea_employeeMK on new { EId = r.eId.Value } equals new { EId = emp.eId }
join ans in
(
(from a in _db.korea_answer
group a by new
{
a.rId
} into g
select new
{
arid = g.Key.rId.Value,
answer1 = g.Max(p => (p.qId == 1 ? p.aContent : null)),
answer2 = g.Max(p => (p.qId == 2 ? p.aContent : null)),
answer3 = g.Max(p => (p.qId == 3 ? p.aContent : null)),
answer4 = g.Max(p => (p.qId == 4 ? p.aContent : null)),
answer5 = g.Max(p => (p.qId == 5 ? p.aContent : null)),
answer6 = g.Max(p => (p.qId == 6 ? p.aContent : null)),
answer7 = g.Max(p => (p.qId == 7 ? p.aContent : null)),
answer8 = g.Max(p => (p.qId == 8 ? p.aContent : null)),
answer9 = g.Max(p => (p.qId == 9 ? p.aContent : null)),
answer10 = g.Max(p => (p.qId == 10 ? p.aContent : null)),
answer11 = g.Max(p => (p.qId == 11 ? p.aContent : null)),
answer12 = g.Max(p => (p.qId == 12 ? p.aContent : null))
}))on new { RId = r.rId } equals new { RId = ans.arid }
orderby
r.rId
select new RecordModel
{
AnswerId = r.rId,
AnswerName = emp.eName,
AnswerNumber = emp.eNumber,
AnswerPhone = emp.ePhone,
AnswerDuration = r.rDuration.Value,
AnswerTime = r.rEndtime.Value,
AnswerAnswer1 = ans.answer1,
AnswerAnswer2 = ans.answer2,
AnswerAnswer3 = ans.answer3,
AnswerAnswer4 = ans.answer4,
AnswerAnswer5 = ans.answer5,
AnswerAnswer6 = ans.answer6,
AnswerAnswer7 = ans.answer7,
AnswerAnswer8 = ans.answer8,
AnswerAnswer9 = ans.answer9,
AnswerAnswer10 = ans.answer10,
AnswerAnswer11 = ans.answer11,
AnswerAnswer12 = ans.answer12,
}
).ToList();
if (list != null && list.Count > 0) //
{
try
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("问卷数据下载");
//
NPOI.SS.UserModel.IRow rowHeader = sheet.CreateRow(0);
sheet.SetColumnWidth(0, 12 * 256);
sheet.SetColumnWidth(1, 10 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 18 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 25 * 256);
ICellStyle style = book.CreateCellStyle();
HSSFColor color = new HSSFColor.BLACK();
FillPatternType fillPattern = FillPatternType.SOLID_FOREGROUND;//灰色背景
HSSFColor backGround = new HSSFColor.GREY_25_PERCENT();
IFont font = ExportData.GetFontStyle(book, "宋体", color, 11);
//设置单元格的样式：水平对齐居中
style = ExportData.GetCellStyle(book, font, fillPattern, backGround, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
ICell cell1 = rowHeader.CreateCell(0);
cell1.SetCellValue("姓名");
//将新的样式赋给单元格
cell1.CellStyle = style;
ICell cell2 = rowHeader.CreateCell(1);
cell2.SetCellValue("编号");
cell2.CellStyle = style;
ICell cell3 = rowHeader.CreateCell(2);
cell3.SetCellValue("手机号码");
cell3.CellStyle = style;
ICell cell4 = rowHeader.CreateCell(3);
cell4.SetCellValue("第1题");
cell4.CellStyle = style;
ICell cell5 = rowHeader.CreateCell(4);
cell5.SetCellValue("第2题");
cell5.CellStyle = style;
ICell cell6 = rowHeader.CreateCell(5);
cell6.SetCellValue("第3题");
cell6.CellStyle = style;
ICell cell7 = rowHeader.CreateCell(6);
cell7.SetCellValue("第4题");
cell7.CellStyle = style;
ICell cell8 = rowHeader.CreateCell(7);
cell8.SetCellValue("第5题");
cell8.CellStyle = style;
ICell cell9 = rowHeader.CreateCell(8);
cell9.SetCellValue("第6题");
cell9.CellStyle = style;
ICell cell10 = rowHeader.CreateCell(9);
cell10.SetCellValue("第7题");
cell10.CellStyle = style;
ICell cell11 = rowHeader.CreateCell(10);
cell11.SetCellValue("第8题");
cell11.CellStyle = style;
ICell cell12 = rowHeader.CreateCell(11);
cell12.SetCellValue("第9题");
cell12.CellStyle = style;
ICell cell13 = rowHeader.CreateCell(12);
cell13.SetCellValue("第10题");
cell13.CellStyle = style;
ICell cell14 = rowHeader.CreateCell(13);
cell14.SetCellValue("第11题");
cell14.CellStyle = style;
ICell cell15 = rowHeader.CreateCell(14);
cell15.SetCellValue("第12题");
cell15.CellStyle = style;
ICellStyle cellStyle = book.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont rowfont = book.CreateFont();
rowfont.FontName = "宋体";
rowfont.FontHeightInPoints = (short)11;
rowfont.Color = color.GetIndex();
cellStyle.SetFont(rowfont);
//
NPOI.SS.UserModel.IRow row = null;
for (int i = 0; i < list.Count; i++)
{
RecordModel model = list[i];
row = sheet.CreateRow(i + 1);
ICell rowcell1 = row.CreateCell(0);
rowcell1.SetCellValue(model.AnswerName);
rowcell1.CellStyle = cellStyle;
ICell rowcell2 = row.CreateCell(1);
rowcell2.SetCellValue(model.AnswerNumber);
rowcell2.CellStyle = cellStyle;
ICell rowcell3 = row.CreateCell(2);
rowcell3.SetCellValue(model.AnswerPhone);
rowcell3.CellStyle = cellStyle;
ICell rowcell4 = row.CreateCell(3);
rowcell4.SetCellValue(model.AnswerAnswer1);
rowcell4.CellStyle = cellStyle;
ICell rowcell5 = row.CreateCell(4);
rowcell5.SetCellValue(model.AnswerAnswer2);
rowcell5.CellStyle = cellStyle;
ICell rowcell6 = row.CreateCell(5);
rowcell6.SetCellValue(model.AnswerAnswer3);
rowcell6.CellStyle = cellStyle;
ICell rowcell7 = row.CreateCell(6);
rowcell7.SetCellValue(model.AnswerAnswer4);
rowcell7.CellStyle = cellStyle;
ICell rowcell8 = row.CreateCell(7);
rowcell8.SetCellValue(model.AnswerAnswer5);
rowcell8.CellStyle = cellStyle;
ICell rowcell9 = row.CreateCell(8);
rowcell9.SetCellValue(model.AnswerAnswer6);
rowcell9.CellStyle = cellStyle;
ICell rowcell10 = row.CreateCell(9);
rowcell10.SetCellValue(model.AnswerAnswer7);
rowcell10.CellStyle = cellStyle;
ICell rowcell11 = row.CreateCell(10);
rowcell11.SetCellValue(model.AnswerAnswer8);
rowcell11.CellStyle = cellStyle;
ICell rowcell12 = row.CreateCell(11);
rowcell12.SetCellValue(model.AnswerAnswer9);
rowcell12.CellStyle = cellStyle;
ICell rowcell13 = row.CreateCell(12);
rowcell13.SetCellValue(model.AnswerAnswer10);
rowcell13.CellStyle = cellStyle;
ICell rowcell14 = row.CreateCell(13);
rowcell14.SetCellValue(model.AnswerAnswer11);
rowcell14.CellStyle = cellStyle;
ICell rowcell15 = row.CreateCell(14);
rowcell15.SetCellValue(model.AnswerAnswer12);
rowcell15.CellStyle = cellStyle;
}
//
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
//string path = @"C:\Users\DNS\Desktop\导出参会人";
//string path = System.Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\数据导出Excel";
// path = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\数据导出Excel";
string path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Data\\";
DirectoryInfo excelDir = new DirectoryInfo(path);
//如果路径不存在就创建
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = "mk_q" + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//string strDate = DateTime.Now.ToString("yyyy-MM-dd-HH ");
path = excelDir.FullName + fileName;
if (File.Exists(path))
{
File.Delete(path);
}
using (Stream localFile = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
//ms.ToArray()转换为字节数组就是想要的图片源字节
localFile.Write(ms.ToArray(), 0, (int)ms.Length);
}
book = null;
ms.Close();
ms.Dispose();
//
string webAddress = System.Configuration.ConfigurationManager.AppSettings["mkDataPath"].ToString();
return(webAddress + fileName);
}
}
catch (Exception)
{
//Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导出格式错误');</script>");
return("Error1");
}
}
else
{
return("Error2");
}
}
protected override void GenerateWorkbookDetail(NPOI.HSSF.UserModel.HSSFWorkbook workBook, System.Data.DataSet dataSet)
{
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.CreateSheet("Payroll Detail");
ushort rowPos = 0;
DataTable empInfoTable = dataSet.Tables["EmpInfo"];
DataTable paymentTable = dataSet.Tables["payment"];
DataTable costCenterDetailTable = dataSet.Tables["CostCenterDetail"];
NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workBook.CreateFont();
boldFont.Boldweight = 700;
NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
reportHeaderStyle.SetFont(boldFont);
reportHeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.HSSF.UserModel.HSSFCellStyle groupHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
groupHeaderStyle.SetFont(boldFont);
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
columnHeaderStyle.SetFont(boldFont);
columnHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle detailStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
detailStyle.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00)");
NPOI.HSSF.UserModel.HSSFCellStyle subTotalStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
subTotalStyle.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00;(#,##0.00)");
subTotalStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
//rowPos = GenerateHeader(xlsDoc, workSheet, PeriodFrom, PeriodTo, rowPos);
rowPos = GenerateHeader(workSheet, PeriodFrom, PeriodTo, rowPos, reportHeaderStyle, groupHeaderStyle);
DataRow[] rows = dataSet.Tables["EmpInfo"].Select("", "Company, " + empInfoTable.Columns[hierarchyLevelGroupingFieldName].ColumnName + ", Employee Name, Alias");
string currentCompany = string.Empty;
string currentHierarchyGroup = string.Empty;
string currentEmployeeNo = string.Empty;
ushort groupRowCount = 0;
Hashtable groupedTotalCostCenterHash = new Hashtable();
ArrayList subTotalRowList = new ArrayList();
Hashtable paymentHashTable = new Hashtable();
Hashtable companyTotalHashTable = new Hashtable();
double netPayment = 0;
double companyNetPayment = 0;
double employerContribution = 0;
double companyEmployerContribution = 0;
ArrayList companyTotalRowNumList = new ArrayList();
foreach (DataRow row in rows)
{
string tmpCompany = row["Company"].ToString();
string tmpHierarchy = row[hierarchyLevelGroupingFieldName].ToString();
string tmpEmployeeNo = row["Employee No."].ToString();
if (!currentCompany.Equals(tmpCompany, StringComparison.CurrentCultureIgnoreCase) || rows[0] == row)
{
if (rows[0] != row)
{
groupRowCount++;
rowPos = GenerateHierarchyTotal(workSheet, paymentTable, currentHierarchyGroup, currentEmployeeNo, paymentHashTable, netPayment, employerContribution, rowPos, detailStyle);
paymentHashTable = new Hashtable();
rowPos = GenerateCompanyTotal(workSheet, paymentTable, groupRowCount, companyEmployerContribution, rowPos, subTotalStyle);
companyTotalRowNumList.Add(rowPos);
companyTotalHashTable = new Hashtable();
}
groupRowCount = 0;
netPayment = 0;
employerContribution = 0;
currentHierarchyGroup = tmpHierarchy;
currentEmployeeNo = tmpEmployeeNo;
companyNetPayment = 0;
companyEmployerContribution = 0;
currentCompany = tmpCompany;
rowPos = GenerateColumnHeader(workSheet, dataSet, currentCompany, hierarchyLevelGroupingFieldName, rowPos, groupHeaderStyle, columnHeaderStyle);
}
else if (!currentHierarchyGroup.Equals(tmpHierarchy, StringComparison.CurrentCultureIgnoreCase))
{
groupRowCount++;
rowPos = GenerateHierarchyTotal(workSheet, paymentTable, currentHierarchyGroup, currentEmployeeNo, paymentHashTable, netPayment, employerContribution, rowPos, detailStyle);
paymentHashTable = new Hashtable();
netPayment = 0;
employerContribution = 0;
currentHierarchyGroup = tmpHierarchy;
currentEmployeeNo = tmpEmployeeNo;
}
DataRow[] paymentRows = paymentTable.Select("[EmpPayrollID]='" + row["EmpPayrollID"].ToString() + "'");
foreach (DataRow paymentRow in paymentRows)
{
foreach (DataColumn column in paymentTable.Columns)
{
if (column.ColumnName.Equals("EmpPayrollID"))
{
continue;
}
double amount = 0;
double companyTotalAmount = 0;
if (paymentHashTable.ContainsKey(column.ColumnName))
{
amount = (double)paymentHashTable[column.ColumnName];
}
else
{
paymentHashTable.Add(column.ColumnName, amount);
}
if (companyTotalHashTable.ContainsKey(column.ColumnName))
{
companyTotalAmount = (double)companyTotalHashTable[column.ColumnName];
}
else
{
companyTotalHashTable.Add(column.ColumnName, companyTotalAmount);
}
if (!string.IsNullOrEmpty(paymentRow[column.ColumnName].ToString()))
{
amount += (double)paymentRow[column.ColumnName];
companyTotalAmount += (double)paymentRow[column.ColumnName];
netPayment += (double)paymentRow[column.ColumnName];
companyNetPayment += (double)paymentRow[column.ColumnName];
paymentHashTable[column.ColumnName] = amount;
companyTotalHashTable[column.ColumnName] = companyTotalAmount;
}
}
}
if (!row.IsNull("MCER"))
{
double contribution = (double)row["MCER"];
employerContribution += contribution;
companyEmployerContribution += contribution;
}
if (!row.IsNull("VCER"))
{
double contribution = (double)row["VCER"];
employerContribution += contribution;
companyEmployerContribution += contribution;
}
if (!row.IsNull("PFundER"))
{
double contribution = (double)row["PFundER"];
employerContribution += contribution;
companyEmployerContribution += contribution;
}
}
if (paymentHashTable.Count > 0)
{
groupRowCount++;
rowPos = GenerateHierarchyTotal(workSheet, paymentTable, currentHierarchyGroup, currentEmployeeNo, paymentHashTable, netPayment, employerContribution, rowPos, detailStyle);
paymentHashTable = new Hashtable();
netPayment = 0;
employerContribution = 0;
}
if (groupRowCount > 0)
{
rowPos = GenerateCompanyTotal(workSheet, paymentTable, groupRowCount, companyEmployerContribution, rowPos, subTotalStyle);
companyTotalRowNumList.Add(rowPos);
}
if (companyTotalRowNumList.Count > 0)
{
rowPos = GenerateTotal(workSheet, paymentTable, companyTotalRowNumList, rowPos, subTotalStyle);
}
//workSheet.Cells.Merge(1, 1, 1, paymentTable.Columns.Count + 2);
//workSheet.Rows[1].GetCell(1).Font.Bold = true;
//workSheet.Rows[1].GetCell(1).Font.Height = 300;
//workSheet.Rows[1].GetCell(1).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
workSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, paymentTable.Columns.Count + 1));
// checking the number of column to resize for better user view
int resizeColumn = paymentTable.Columns.Count + 1;
if (intHierarchyLevelID == STAFF_LEVEL_ID)
{
resizeColumn++;
}
for (int i = 0; i <= resizeColumn; i++)
{
//org.in2bits.MyXls.ColumnInfo columnInfo = new org.in2bits.MyXls.ColumnInfo(xlsDoc, workSheet);
//workSheet.AddColumnInfo(columnInfo);
//columnInfo.ColumnIndexStart = 0;
//columnInfo.ColumnIndexEnd = (ushort)(paymentTable.Columns.Count + 2);
//columnInfo.Width = 15 * 254;
//columnInfo.Collapsed = true;
workSheet.SetColumnWidth(i, 15 * 254);
}
}
public static MemoryStream RenderToExcel(List<Models.Beans.Contract> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
#region head row
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//row.RowStyle = style;
row.Height = 26 * 20;
CreateCell(row, 0, "存放位置", CellType.String, style);
CreateCell(row, 1, "合同号", CellType.String, style);
CreateCell(row, 2, "序号", CellType.String, style);
CreateCell(row, 3, "项目编号", CellType.String, style);
CreateCell(row, 4, "项目名称", CellType.String, style);
CreateCell(row, 5, "项目负责人", CellType.String, style);
CreateCell(row, 6, "联系方式", CellType.String, style);
CreateCell(row, 7, "分管部门", CellType.String, style);
CreateCell(row, 8, "分包名称", CellType.String, style);
CreateCell(row, 9, "分包预算(万元)", CellType.String, style);
CreateCell(row, 10, "招标编号", CellType.String, style);
CreateCell(row, 11, "招标公司", CellType.String, style);
CreateCell(row, 12, "开标时间", CellType.String, style);
CreateCell(row, 13, "付款方式", CellType.String, style);
CreateCell(row, 14, "中标公司名称", CellType.String, style);
CreateCell(row, 15, "联系人", CellType.String, style);
CreateCell(row, 16, "手机号码", CellType.String, style);
CreateCell(row, 17, "中标金额(万元)", CellType.String, style);
CreateCell(row, 18, "签合同日期", CellType.String, style);
CreateCell(row, 19, "交货时间", CellType.String, style);
CreateCell(row, 20, "验收情况", CellType.String, style);
CreateCell(row, 21, "进度", CellType.String, style);
CreateCell(row, 22, "支付全款", CellType.String, style);
CreateCell(row, 23, "押款", CellType.String, style);
CreateCell(row, 24, "退款", CellType.String, style);
CreateCell(row, 25, "标签", CellType.String, style);
#endregion
if (list != null && list.Count > 0)
{
int rowidx = 0;
foreach (Models.Beans.Contract model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.contractplace);
CreateCell(row, 1, model.contractnum);
CreateCell(row, 2, model.seq);
CreateCell(row, 3, model.projectnum);
CreateCell(row, 4, model.projectname);
CreateCell(row, 5, model.projectmanager);
CreateCell(row, 6, model.tel);
CreateCell(row, 7, model.depart);
CreateCell(row, 8, model.packageName);
CreateCell(row, 9, model.packageBudget);
CreateCell(row, 10, model.tendarNum);
CreateCell(row, 11, model.tendarCompany);
CreateCell(row, 12, model.tendarStartTime);
CreateCell(row, 13, model.paymethod);
CreateCell(row, 14, model.bcompany);
CreateCell(row, 15, model.linker);
CreateCell(row, 16, model.phone);
CreateCell(row, 17, model.money);
CreateCell(row, 18, model.signingdate);
CreateCell(row, 19, model.deliveryTime);
CreateCell(row, 20, model.inspection);
CreateCell(row, 21, model.progress);
CreateCell(row, 22, model.isPayAll);
CreateCell(row, 23, model.isArmoured);
CreateCell(row, 24, model.isRefund);
CreateCell(row, 25, model.contractrfid);
#endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
public byte[] exportToExcel(DataSet source)
{
var workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
var sheet = workbook.CreateSheet("data");
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
var font1 = workbook.CreateFont();
font1.IsBold = true;
for (int i = 0; i < cols.Count; i++)
{
var col = cols[i];
var cell = row.CreateCell(i);
cell.SetCellValue(col.title);
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.SetFont(font1);
sheet.SetColumnWidth(i, 5500);
}
foreach (DataRow r in source.Tables[0].Rows)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
for (int iCol = 0; iCol < cols.Count; iCol++)
{
var col = cols[iCol];
var cell = row.CreateCell(iCol);
string sVal = "";
if (r[col.datafield] is DateTime && col.Format.isEmpty() == false)
{
sVal = r.getDateFormat(col.datafield, col.Format);
}
else
{
sVal = r[col.datafield].ToString();
}
cell.SetCellValue(sVal);
}
}
using (var fileData = new System.IO.MemoryStream())
{
workbook.Write(fileData);
return(g.ConvertStreamToByteArray(fileData));
}
}
public static void ExportFile(DataTable dt, string fileName, List <string> columnNames, int[] mergeCondition, int[] noMergeColumns, int[] wrapColumns, int [] numerberColumns)
{
using (var book = new NPOI.HSSF.UserModel.HSSFWorkbook())
{
var sheet = book.CreateSheet();
var headRow = sheet.CreateRow(0);
for (int i = 0; i < columnNames.Count; i++)
{
var cell = headRow.CreateCell(i);
cell.SetCellValue(columnNames[i]);
var font = book.CreateFont();
font.Boldweight = NPOI.HSSF.UserModel.HSSFFont.BOLDWEIGHT_BOLD;
cell.RichStringCellValue.ApplyFont(font);
}
foreach (var wrapC in wrapColumns)
{
NPOI.HSSF.UserModel.HSSFCellStyle cs = book.CreateCellStyle();
cs.WrapText = true;
sheet.SetDefaultColumnStyle((short)wrapC, cs);
}
//开始写入内容
int RowCount = dt.Rows.Count; //行数
int ColCount = columnNames.Count; //列数
for (int rowIndex = 0; rowIndex < RowCount; rowIndex++)
{
var row = sheet.CreateRow(rowIndex + 1);
for (int colIndex = 0; colIndex < ColCount; colIndex++)
{
var cell = row.CreateCell(colIndex);
if (IsWrapColumn(wrapColumns, colIndex))
{
cell.SetCellValue(dt.Rows[rowIndex][colIndex].ToString().Replace("</br>", "\n"));
}
else
{
cell.SetCellValue(dt.Rows[rowIndex][colIndex].ToString());
}
}
}
for (int rowIndex = 0; rowIndex < RowCount; rowIndex++)
{
for (int colIndex = 0; colIndex < ColCount; colIndex++)
{
if (noMergeColumns.Contains(colIndex))
{
continue;
}
var rowSpan = GetRowSpan(dt, mergeCondition, rowIndex, colIndex);
if (rowSpan <= 1)
{
continue;
}
sheet.AddMergedRegion(new NPOI.HSSF.Util.CellRangeAddress(rowIndex + 1, rowIndex + rowSpan, colIndex, colIndex));
}
}
////设置每列的宽度
//for (int colIndex = 0; colIndex < columnNames.Count; colIndex++)
//{
// sheet.AutoSizeColumn(colIndex);
// sheet.SetColumnWidth(colIndex, sheet.GetColumnWidth(colIndex) + 2100);
//}
ExportFile(book, fileName);
}
}
protected void ExportButton_Click(object sender, EventArgs e)
{
OrderSearchInfo orderSearch = new OrderSearchInfo();
orderSearch.OrderNumber = ShopCommon.ConvertToT <string>(OrderNumber.Text);
orderSearch.OrderStatus = ShopCommon.ConvertToT <int>(OrderStatus.Text);
orderSearch.Consignee = ShopCommon.ConvertToT <string>(Consignee.Text);
orderSearch.StartAddDate = ShopCommon.ConvertToT <DateTime>(StartAddDate.Text);
orderSearch.EndAddDate = ShopCommon.SearchEndDate(ShopCommon.ConvertToT <DateTime>(EndAddDate.Text));
var data = OrderBLL.SearchList(1, 1000, orderSearch, ref Count);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
sheet.DefaultColumnWidth = 18;
sheet.CreateFreezePane(0, 1, 0, 1);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue("订单号");
row.CreateCell(1).SetCellValue("订单金额");
row.CreateCell(2).SetCellValue("类型");
row.CreateCell(3).SetCellValue("收货方式");
row.CreateCell(4).SetCellValue("收货人");
//row.CreateCell(3).SetCellValue("收货地址");
row.CreateCell(5).SetCellValue("订单状态");
row.CreateCell(6).SetCellValue("下单时间");
row.CreateCell(7).SetCellValue("最近操作时间");
//设置表头格式
var headFont = book.CreateFont();
headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headFont.FontHeightInPoints = 10;
var headStyle = book.CreateCellStyle();
headStyle.SetFont(headFont);
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
foreach (var cell in row.Cells)
{
cell.CellStyle = headStyle;
}
//取得订单最后一次操作的时间
var orderActinList = OrderActionBLL.ReadListLastDate(data.Select(k => k.Id).ToArray());
foreach (var entity in data)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(data.IndexOf(entity) + 1);
dataRow.CreateCell(0).SetCellValue(entity.OrderNumber);
dataRow.CreateCell(1).SetCellValue((entity.ProductMoney + entity.ShippingMoney + entity.OtherMoney).ToString());
dataRow.CreateCell(2).SetCellValue(EnumHelper.ReadEnumChineseName <OrderKind>(entity.IsActivity));
dataRow.CreateCell(3).SetCellValue(entity.SelfPick == 1 ? "自提" : "配送");
dataRow.CreateCell(4).SetCellValue(entity.Consignee);
//dataRow.CreateCell(3).SetCellValue(entity.Address);
dataRow.CreateCell(5).SetCellValue(OrderBLL.ReadOrderStatus(entity.OrderStatus, entity.IsDelete));
dataRow.CreateCell(6).SetCellValue(entity.AddDate.ToString());
var orderAction = orderActinList.FirstOrDefault(k => k.OrderId == entity.Id) ?? new OrderActionInfo();
dataRow.CreateCell(7).SetCellValue(orderAction.OrderId > 0 ? orderAction.Date.ToString() : "");
var style = book.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
foreach (var cell in dataRow.Cells)
{
cell.CellStyle = style;
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
Response.End();
}
protected void ExportButton_Click(object sender, EventArgs e)
{
ProductSearchInfo productSearch = new ProductSearchInfo();
OrderSearchInfo orderSearch = new OrderSearchInfo();
productSearch.IsSale = (int)BoolType.True;
productSearch.Name = ShopCommon.ConvertToT <string>(Name.Text);
productSearch.ClassId = ShopCommon.ConvertToT <string>(ClassID.Text);
productSearch.BrandId = ShopCommon.ConvertToT <int>(BrandID.Text);
orderSearch.StartAddDate = ShopCommon.ConvertToT <DateTime>(StartAddDate.Text);
orderSearch.EndAddDate = ShopCommon.SearchEndDate(ShopCommon.ConvertToT <DateTime>(EndAddDate.Text));
orderSearch.UserName = ShopCommon.ConvertToT <string>(UserName.Text);
orderSearch.OrderNumber = ShopCommon.ConvertToT <string>(OrderNumber.Text);
var data = OrderDetailBLL.StatisticsSaleDetail(1, 1000, orderSearch, productSearch, ref Count);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
sheet.DefaultColumnWidth = 18;
sheet.CreateFreezePane(0, 1, 0, 1);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue("时间");
row.CreateCell(1).SetCellValue("单号");
row.CreateCell(2).SetCellValue("商品名称");
row.CreateCell(3).SetCellValue("数量");
row.CreateCell(4).SetCellValue("金额");
row.CreateCell(5).SetCellValue("用户名");
//设置表头格式
var headFont = book.CreateFont();
headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headFont.FontHeightInPoints = 10;
var headStyle = book.CreateCellStyle();
headStyle.SetFont(headFont);
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
foreach (var cell in row.Cells)
{
cell.CellStyle = headStyle;
}
foreach (DataRow dr in data.Rows)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(data.Rows.IndexOf(dr) + 1);
dataRow.CreateCell(0).SetCellValue(Convert.ToString(dr["AddDate"]));
dataRow.CreateCell(1).SetCellValue(Convert.ToString(dr["OrderNumber"]));
dataRow.CreateCell(2).SetCellValue(Convert.ToString(dr["Name"]));
dataRow.CreateCell(3).SetCellValue(Convert.ToString(dr["BuyCount"]));
dataRow.CreateCell(4).SetCellValue(Convert.ToString(dr["Money"]));
dataRow.CreateCell(5).SetCellValue(Convert.ToString(dr["UserName"]));
var style = book.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
foreach (var cell in dataRow.Cells)
{
cell.CellStyle = style;
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
Response.End();
}
public static MemoryStream RenderToExcel(List<Bean.ArchiveBean> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
#region head row
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//row.RowStyle = style;
row.Height = 26 * 20;
CreateCell(row, 0, "序号", CellType.String, style);
CreateCell(row, 1, "责任人", CellType.String, style);
CreateCell(row, 2, "文件题目", CellType.String, style);
CreateCell(row, 3, "页数", CellType.String, style);
CreateCell(row, 4, "编号", CellType.String, style);
CreateCell(row, 5, "备注", CellType.String, style);
#endregion
if (list != null && list.Count > 0)
{
int rowidx = 0;
foreach (Bean.ArchiveBean model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.idx);
CreateCell(row, 1, model.manager);
CreateCell(row, 2, model.title);
CreateCell(row, 3, model.pages);
CreateCell(row, 4, model.number);
CreateCell(row, 5, model.remark);
#endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}