#region 导出相关
[PostBack("Name")]
[PostBack("selectYearMonth")]
[GridPostBack(GridPostBackMode.CheckedRows, true, "dgList")]
protected void btnExport_Click(MiniButton sender, WuhanIns.Web.MiniUI.Core.AjaxActionEventArgs e)
{
try
{
HrStaffBaseItem[] detailItem = e.GetRequestParam<HrStaffBaseItem[]>("dgList");
if (detailItem.Count() <= 0)
{
JSAlert("请至少选择一条记录!");
return;
}
string[] ids = detailItem.Select(d => d.Id).ToArray();
//在服务端自动创建一个文件夹
string FileBaseDir = "ExportExcel";//写死一个excel临时存储文件夹名称
string fileBasePath = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, FileBaseDir);
//清空指定目录
DirectoryInfo directory = new DirectoryInfo(fileBasePath);
if (directory.Exists)
{
directory.Delete(true);
}
DirectoryInfo newDirInfo = Directory.CreateDirectory(fileBasePath);//在指定路径创建所有目录和子目录
if (newDirInfo.Exists)
{
string strYearMonth = e.GetRequestParam<string>("selectYearMonth");
DateTime? selectYearMonth = null;
if (!string.IsNullOrEmpty(strYearMonth))
{
selectYearMonth = GetDateTime("selectYearMonth", e);
}
string projectid = e.GetRequestParam<string>("Project_Id");
string subcontractor_id = e.GetRequestParam<string>("subcontractor_id");
string Name = JsonUtil.DecodeKeyValue(e.GetRequestParam("Name")).Value.Value.Trim();
DataTable dt = HrStaffBaseManager.GetTableExportSource(ids, selectYearMonth, projectid, subcontractor_id, Name);//根据条件获取导出数据源
//DataView dv = dt.DefaultView;
//string[] ids = detailItem.Select(d => d.Id).ToArray();
dv.RowFilter = "id in (" + ids + ")";
//dv.RowFilter = "id in ('000e0b42-165a-4b45-92dd-48a9dc1aaaaa')";
//DataTable dtNew = dv.ToTable();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
if (dr["性别"] != null && dr["性别"].ToString() != "")
{
int curEnum = Convert.ToInt32(dr["性别"].ToString());
dr["性别"] = ((性别)curEnum).ToString();
}
if (dr["工种"] != null && dr["工种"].ToString() != "")
{
int curEnum = Convert.ToInt32(dr["工种"].ToString());
dr["工种"] = ((工种)curEnum).ToString();
}
if (dr["学历"] != null && dr["学历"].ToString() != "")
{
int curEnum = Convert.ToInt32(dr["学历"].ToString());
dr["学历"] = ((学历)curEnum).ToString();
}
if (dr["是否贫困县"] != null && dr["是否贫困县"].ToString() != "")
{
int curEnum = Convert.ToInt32(dr["是否贫困县"].ToString());
dr["是否贫困县"] = ((是否)curEnum).ToString();
}
if (dr["发放方式"] != null && dr["发放方式"].ToString() != "")
{
int curEnum = Convert.ToInt32(dr["发放方式"].ToString());
dr["发放方式"] = ((EPC发放方式)curEnum).ToString();
}
if (dr["出生年月"] != null && dr["出生年月"].ToString() != "")
{
dr["出生年月"] = Convert.ToDateTime(dr["出生年月"].ToString()).ToString("yyyy-MM-dd").ToString();
}
if (dr["护照有效期至"] != null && dr["护照有效期至"].ToString() != "")
{
dr["护照有效期至"] = Convert.ToDateTime(dr["护照有效期至"].ToString()).ToString("yyyy-MM-dd").ToString();
}
if (dr["办理日期"] != null && dr["办理日期"].ToString() != "")
{
dr["办理日期"] = Convert.ToDateTime(dr["办理日期"].ToString()).ToString("yyyy-MM-dd").ToString();
}
if (dr["到期时间"] != null && dr["到期时间"].ToString() != "")
{
dr["到期时间"] = Convert.ToDateTime(dr["到期时间"].ToString()).ToString("yyyy-MM-dd").ToString();
}
if (dr["证书有效期"] != null && dr["证书有效期"].ToString() != "")
{
dr["证书有效期"] = Convert.ToDateTime(dr["证书有效期"].ToString()).ToString("yyyy-MM-dd").ToString();
}
if (dr["保险有效期"] != null && dr["保险有效期"].ToString() != "")
{
dr["保险有效期"] = Convert.ToDateTime(dr["保险有效期"].ToString()).ToString("yyyy-MM-dd").ToString();
}
if (dr["特殊工种"] != null && dr["特殊工种"].ToString() != "")
{
int curEnum = Convert.ToInt32(dr["特殊工种"].ToString());
dr["特殊工种"] = ((是否)curEnum).ToString();
}
if (dr["购买保险"] != null && dr["购买保险"].ToString() != "")
{
int curEnum = Convert.ToInt32(dr["购买保险"].ToString());
dr["购买保险"] = ((是否)curEnum).ToString();
}
}
}
DateTime curTime = DateTime.Now;
//string excelName = string.Format("劳务人员台帐明细({0}年{1}月{2}日{3}时{4}分{5}秒).xls", curTime.Year, curTime.Month, curTime.Day, curTime.Hour, curTime.Minute, curTime.Second);//导出excel文件名称
string excelName = string.Format("劳务人员台帐明细({0}).xls", curTime.ToString("yyyyMMddHHmmss"));//导出excel文件名称
//string excelName = string.Format("劳务人员台帐明细({0}年{1}月{2}日{3}时{4}分{5}秒).xlsx", curTime.Year, curTime.Month, curTime.Day, curTime.Hour, curTime.Minute, curTime.Second);//导出excel文件名称
string excelFullName = Path.Combine(fileBasePath, excelName);//将两个字符串组合成一个路径
//ExcelRender.RenderToExcel(dt, excelFullName);//DataTable转换成Excel文档流，并保存到文件
RenderToExcel(dt, excelFullName);//DataTable转换成Excel文档流，并保存到文件
//下载该excel文件至pc端
Session["FileFullPath"] = excelFullName;
if (File.Exists(excelFullName))
{
this.JSShowWindow("../../Handler/DownLoadFile.ashx", false);
}
}
}
catch
{
}
}
private DateTime GetDateTime(string datePickerId, AjaxEventArgs e)
{
//string str = e.GetRequestParam(datePickerId);
//KeyValuePair<string, string> data = new KeyValuePair<string, string>();
data = JsonUtil.DecodeKeyValue(str).Value;
//data = JsonUtil.DecodeKeyValue(str.Trim('"')).Value;
//string timeStr = data.Value;
//if (!string.IsNullOrEmpty(timeStr))
//{
// return DateTime.Parse(timeStr);
//}
//return NoneValue.NaDateTime;
string str = e.GetRequestParam(datePickerId);
if (!string.IsNullOrEmpty(str))
{
return DateTime.Parse(str.Trim('"'));
}
return NoneValue.NaDateTime;
}
#endregion
/// <summary>
/// 根据条件获取导出数据源
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="qbsIdList"></param>
/// <returns></returns>
public DataTable GetTableExportSource(string[] ids, DateTime? selectYearMonth, string project_id, string subcontractor_id, string Name)
{
// string sql = @" select
// hsb.name 姓名,
// hsb.identity_card 身份证号码,
// decode(hsb.sex, 0, '男', 1, '女', '') 性别,
// hsb.birthday 出生年月,
// hsb.age 年龄,
// hsb.anmelden_location_number 户口所在地,
// hsb.position 工种,
// hsb.educational_level 学历,
// hsb.seniority 工作年限,
// hsb.ispoor 是否贫困县,
// hsb.address 家庭地址,
// hsb.emergency_contact 紧急联系人,
// hsb.emergency_contact_tel 紧急联系人电话,
// hsb.contact_relationship 关系,
// sb.subcontractor_name 所属分包商,
// decode(hsb.pay_type, 0, '项目部代发', 1, '分包商自行发放', '') 发放方式,
// hsb.bank_account 银行卡号
// from
// hr_staff_base hsb LEFT JOIN subcontractor_base sb ON hsb.subcontractor_id = sb.id
// where 1=1
// and (hsb.deleted =0 OR hsb.deleted is null)
// and hsb.project_id = :W_project_id
// and hsb.subcontractor_id = :W_subcontractor_id ";
string sql = @" select
hsb.NAME 姓名,
hsb.identity_card 身份证号码,
cast( hsb.sex AS CHAR ) 性别,
cast( hsb.birthday AS CHAR ) 出生年月,
hsb.age 年龄,
hsb.anmelden_location_number 户口所在地,
cast( hsb.position AS CHAR ) 工种,
cast( hsb.educational_level AS CHAR ) 学历,
hsb.seniority 工作年限,
cast( hsb.ispoor AS CHAR ) 是否贫困县,
hsb.address 家庭地址,
hsb.emergency_contact 紧急联系人,
hsb.emergency_contact_tel 紧急联系人电话,
hsb.contact_relationship 关系,
sb.subcontractor_name 所属分包商,
cast( hsb.pay_type AS CHAR ) 发放方式,
hsb.bank_account 银行卡号,
hsb.passport_number 护照号码,
hsb.passport_type 护照类型,
hsb.visanumber 签证编号,
cast( hsb.hz_period AS CHAR ) 护照有效期至,
cast( hsb.handling_date AS CHAR ) 办理日期,
cast( hsb.due_date AS CHAR ) 到期时间,
cast( hsb.is_operation AS CHAR ) 特殊工种,
hsb.special_cer_code 特种作业证编号,
cast( hsb.special_cer_time AS CHAR ) 证书有效期,
cast( hsb.insurance AS CHAR ) 购买保险,
hsb.insurance_type 保险类型,
cast( hsb.insurance_time AS CHAR ) 保险有效期
from
hr_staff_base hsb LEFT JOIN subcontractor_base sb ON hsb.subcontractor_id = sb.id
where 1=1
and (hsb.deleted =0 OR hsb.deleted is null)
and hsb.project_id = :W_project_id ";
List<MySqlParameter> listParam = new List<MySqlParameter>();
MySqlParameter parm1 = new MySqlParameter();
parm1.MySqlDbType = MySqlDbType.String;
parm1.ParameterName = ":W_project_id";
parm1.Value = project_id;
listParam.Add(parm1);
if (!string.IsNullOrEmpty(subcontractor_id))
{
sql += " and hsb.subcontractor_id = :W_subcontractor_id ";
MySqlParameter parm2 = new MySqlParameter();
parm2.MySqlDbType = MySqlDbType.String;
parm2.ParameterName = ":W_subcontractor_id";
parm2.Value = subcontractor_id;
listParam.Add(parm2);
}
if (ids.Length > 0)
{
sql += " and hsb.id in (" + DBUtil.ArrayToSQLIn(ids) + ") ";
}
if (selectYearMonth != null)
{
sql += " and (hsb.coming_date <= :W_COMING_DATE and (hsb.out_date is null or hsb.out_date >= :W_OUT_DATE)) ";
MySqlParameter parm3 = new MySqlParameter();
parm3.MySqlDbType = MySqlDbType.DateTime;
parm3.ParameterName = ":W_COMING_DATE";
parm3.Value = selectYearMonth;
listParam.Add(parm3);
MySqlParameter parm4 = new MySqlParameter();
parm4.MySqlDbType = MySqlDbType.DateTime;
parm4.ParameterName = ":W_OUT_DATE";
parm4.Value = selectYearMonth;
listParam.Add(parm4);
}
if (!string.IsNullOrEmpty(Name))
{
//sql += " and hsb.name = :W_Name ";
sql += " and hsb.name like :W_Name ";
MySqlParameter parm5 = new MySqlParameter();
parm5.MySqlDbType = MySqlDbType.String;
parm5.ParameterName = ":W_Name";
parm5.Value = "%" + Name + "%";
listParam.Add(parm5);
}
sql += @" order by hsb.name ";
DataTable dt = DBHelper.Context.FromSql(sql).AddParameter(listParam.ToArray()).ToDataTable();
return dt;
}
#region datatable导出Excel公共方法
/// <summary>
/// DataTable转换成Excel文档流，并保存到文件
/// </summary>
/// <param name="table"></param>
/// <param name="fileName">保存的路径</param>
public static void RenderToExcel(DataTable table, string fileName)
{
using (MemoryStream ms = RenderToExcel(table))
{
SaveToFile(ms, fileName);
}
}
/// <summary>
/// DataTable转换成Excel文档流
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static MemoryStream RenderToExcel(DataTable table)
{
#region
MemoryStream ms = new MemoryStream();
using (table)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
#region 设置单元格为下拉框并限制输入值
SetCellDropdownlist(sheet, 2, IdTextItem.ToItems(typeof(Model.性别)).Select(d => d.Text).ToArray());//性别
SetCellDropdownlist(sheet, 6, IdTextItem.ToItems(typeof(Model.工种)).Select(d => d.Text).ToArray());//工种
SetCellDropdownlist(sheet, 7, IdTextItem.ToItems(typeof(Model.学历)).Select(d => d.Text).ToArray());//学历
SetCellDropdownlist(sheet, 9, IdTextItem.ToItems(typeof(Model.是否)).Select(d => d.Text).ToArray());//是否贫困县
SetCellDropdownlist(sheet, 15, IdTextItem.ToItems(typeof(Model.EPC发放方式)).Select(d => d.Text).ToArray());//发放方式
SetCellDropdownlist(sheet, 23, IdTextItem.ToItems(typeof(Model.是否)).Select(d => d.Text).ToArray());//特殊工种
SetCellDropdownlist(sheet, 26, IdTextItem.ToItems(typeof(Model.是否)).Select(d => d.Text).ToArray());//购买保险
#endregion
// handling header.
foreach (DataColumn column in table.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
// handling value.
int rowIndex = 1;
foreach (DataRow row in table.Rows)//遍历行数据
{
IRow dataRow = sheet.CreateRow(rowIndex);//创建行
IDataFormat dataformat = workbook.CreateDataFormat();//创建单元格格式对象
foreach (DataColumn column in table.Columns)//遍历列数据
{
//dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());//创建单元格并赋值
#region 设置单元格是否可编辑样式
ICellStyle cellstyleLock = workbook.CreateCellStyle();//创建单元格样式对象
if (column.ToString() == "所属分包商")
{
cellstyleLock.IsLocked = true;//单元格上锁，单元格不可编辑
}
else
{
cellstyleLock.IsLocked = false;//单元格解除锁定，单元格可编辑
cellstyleLock.DataFormat = dataformat.GetFormat("text");//设置单元格格式，当前格式为文本
}
sheet.SetDefaultColumnStyle(column.Ordinal, cellstyleLock);//设置单元格样式
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());//创建单元格并赋值
#endregion
}
rowIndex++;
}
sheet.ProtectSheet("MD5");//设置sheet受保护，使IsLocked属性生效
AutoSizeColumns(sheet);//自动设置Excel列宽
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return ms;
#endregion
}
/// <summary>
/// 自动设置Excel列宽
/// </summary>
/// <param name="sheet">Excel表</param>
private static void AutoSizeColumns(ISheet sheet)
{
if (sheet.PhysicalNumberOfRows > 0)
{
IRow headerRow = sheet.GetRow(0);
for (int i = 0, l = headerRow.LastCellNum; i < l; i++)
{
sheet.AutoSizeColumn(i);
}
}
}
/// <summary>
/// 保存Excel文档流到文件
/// </summary>
/// <param name="ms">Excel文档流</param>
/// <param name="fileName">文件名</param>
private static void SaveToFile(MemoryStream ms, string fileName)
{
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
data = null;
}
}
/// <summary>
/// 设置单元格为下拉框并限制输入值
/// </summary>
/// <param name="sheet"></param>
private static void SetCellDropdownlist(ISheet sheet, int columnIndex, string[] listVals)
{
//设置生成下拉框的行和列(开始行，结束行，开始列，结束列)
//当开始列=结束列时，仅当前列设置下拉框
var cellRegions = new CellRangeAddressList(0, 65535, columnIndex, columnIndex);
//var cellRegions = new CellRangeAddressList(0, 65535, 0, 0);
//设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(listVals);
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
}
#endregion
599
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
