899 lines
37 KiB
C#
899 lines
37 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Linq;
|
||
using System.Data;
|
||
using System.Data.SqlClient;
|
||
using System.IO;
|
||
using AspNet = System.Web.UI.WebControls;
|
||
using System.Text;
|
||
using BLL;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.SS.Util;
|
||
using NPOI.XSSF.UserModel;
|
||
|
||
namespace FineUIPro.Web.WelderManage
|
||
{
|
||
public partial class WelderManage : PageBase
|
||
{
|
||
//定义变量
|
||
/// <summary>
|
||
/// 上传预设的虚拟路径
|
||
/// </summary>
|
||
private string initPath = Const.ExcelUrl;
|
||
/// <summary>
|
||
/// 错误集合
|
||
/// </summary>
|
||
public static string errorInfos = string.Empty;
|
||
|
||
#region 加载
|
||
/// <summary>
|
||
/// 加载页面
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void Page_Load(object sender, EventArgs e)
|
||
{
|
||
if (!IsPostBack)
|
||
{
|
||
this.ddlPageSize.SelectedValue = Grid1.PageSize.ToString();
|
||
// 绑定表格
|
||
this.BindGrid();
|
||
}
|
||
}
|
||
|
||
#region 绑定数据
|
||
/// <summary>
|
||
/// 绑定数据
|
||
/// </summary>
|
||
private void BindGrid()
|
||
{
|
||
string strSql = @"SELECT Welder.WelderId, Welder.WelderCode, Welder.WelderName, Welder.UnitId,Welder.Birthday,
|
||
DATEDIFF(YEAR,Welder.Birthday,GETDATE()) AS Age,
|
||
(CASE WHEN Welder.Sex=1 THEN '男' ELSE '女' END) AS Sex,
|
||
Welder.IdentityCard, Welder.CertificateNum, Welder.CertificateValidity,
|
||
Welder.WelderLevel, Welder.Remark,Unit.UnitName,
|
||
(CASE WHEN Welder.IsOnDuty=1 THEN '是' ELSE '否' END) AS IsOnDuty
|
||
FROM Welder_Welder AS Welder
|
||
LEFT JOIN Base_Unit AS Unit ON Unit.UnitId = Welder.UnitId WHERE 1=1";
|
||
|
||
List<SqlParameter> parms = new List<SqlParameter>();
|
||
|
||
if (!string.IsNullOrEmpty(this.txtWelderCode.Text))
|
||
{
|
||
strSql += " and Welder.WelderCode LIKE @WelderCode";
|
||
parms.Add(new SqlParameter("@WelderCode", "%" + this.txtWelderCode.Text.Trim() + "%"));
|
||
}
|
||
if (!string.IsNullOrEmpty(this.txtWelderName.Text))
|
||
{
|
||
strSql += " and Welder.WelderName LIKE @WelderName";
|
||
parms.Add(new SqlParameter("@WelderName", "%" + this.txtWelderName.Text.Trim() + "%"));
|
||
}
|
||
SqlParameter[] parameter = parms.ToArray();
|
||
DataTable dt = SQLHelper.GetDataTableRunText(strSql, parameter);
|
||
|
||
Grid1.RecordCount = dt.Rows.Count;
|
||
var table = this.GetPagedDataTable(Grid1, dt);
|
||
|
||
Grid1.DataSource = table;
|
||
Grid1.DataBind();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 改变索引事件
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void Grid1_PageIndexChange(object sender, GridPageEventArgs e)
|
||
{
|
||
BindGrid();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 分页下拉选择事件
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
|
||
{
|
||
Grid1.PageSize = Convert.ToInt32(ddlPageSize.SelectedValue);
|
||
BindGrid();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 排序
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void Grid1_Sort(object sender, FineUIPro.GridSortEventArgs e)
|
||
{
|
||
BindGrid();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 关闭弹出窗口
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void Window1_Close(object sender, EventArgs e)
|
||
{
|
||
BindGrid();
|
||
}
|
||
#endregion
|
||
#endregion
|
||
|
||
#region 增加按钮事件
|
||
/// <summary>
|
||
/// 增加按钮事件
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void btnNew_Click(object sender, EventArgs e)
|
||
{
|
||
if (GetButtonPower(Const.BtnAdd))
|
||
{
|
||
PageContext.RegisterStartupScript(Window1.GetShowReference(String.Format("WelderManageEdit.aspx", "新增 - ")));
|
||
}
|
||
else
|
||
{
|
||
Alert.ShowInTop(Resources.Lan.NoPrivilegePrompt, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 编辑
|
||
/// <summary>
|
||
/// 双击事件
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void Grid1_RowDoubleClick(object sender, GridRowClickEventArgs e)
|
||
{
|
||
this.EditData();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 右键编辑事件
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void btnMenuEdit_Click(object sender, EventArgs e)
|
||
{
|
||
this.EditData();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 编辑数据方法
|
||
/// </summary>
|
||
private void EditData()
|
||
{
|
||
|
||
if (Grid1.SelectedRowIndexArray.Length == 0)
|
||
{
|
||
Alert.ShowInTop(Resources.Lan.SelectLeastOneRecord, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
|
||
////双击事件 编辑权限有：编辑页面，无：查看页面 或者状态是完成时查看页面
|
||
if (GetButtonPower(Const.BtnModify))
|
||
{
|
||
PageContext.RegisterStartupScript(Window1.GetShowReference(String.Format("WelderManageEdit.aspx?WelderId={0}", Grid1.SelectedRowID, "编辑 - ")));
|
||
}
|
||
else if (GetButtonPower(Const.BtnSee))
|
||
{
|
||
PageContext.RegisterStartupScript(Window1.GetShowReference(String.Format("WelderManageView.aspx?WelderId={0}", Grid1.SelectedRowID, "查看 - ")));
|
||
}
|
||
else
|
||
{
|
||
ShowNotify(Resources.Lan.NoPrivilegePrompt, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 删除
|
||
/// <summary>
|
||
/// 右键删除事件
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void btnMenuDelete_Click(object sender, EventArgs e)
|
||
{
|
||
if (GetButtonPower(Const.BtnDelete))
|
||
{
|
||
if (Grid1.SelectedRowIndexArray.Length > 0)
|
||
{
|
||
string strShowNotify = string.Empty;
|
||
foreach (int rowIndex in Grid1.SelectedRowIndexArray)
|
||
{
|
||
string rowID = Grid1.DataKeys[rowIndex][0].ToString();
|
||
var welder = BLL.WelderService.GetWelderById(rowID);
|
||
if (welder != null)
|
||
{
|
||
string cont = judgementDelete(rowID);
|
||
if (string.IsNullOrEmpty(cont))
|
||
{
|
||
BLL.WelderService.DeleteWelderById(rowID);
|
||
BLL.Sys_LogService.AddLog(Const.System_1, this.CurrUser.LoginProjectId, this.CurrUser.UserId, Const.WelderManageMenuId, Const.BtnDelete, rowID);
|
||
}
|
||
else
|
||
{
|
||
strShowNotify += Resources.Lan.WelderManage + "：" + welder.WelderCode + cont;
|
||
}
|
||
}
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(strShowNotify))
|
||
{
|
||
Alert.ShowInTop(strShowNotify, MessageBoxIcon.Warning);
|
||
}
|
||
else
|
||
{
|
||
BindGrid();
|
||
ShowNotify(Resources.Lan.DeletedSuccessfully, MessageBoxIcon.Success);
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
Alert.ShowInTop(Resources.Lan.NoPrivilegePrompt, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
}
|
||
|
||
#region 判断是否可删除
|
||
/// <summary>
|
||
/// 判断是否可以删除
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
private string judgementDelete(string id)
|
||
{
|
||
string content = string.Empty;
|
||
if (Funs.DB.Welder_ProjectWelder.FirstOrDefault(x => x.WelderId == id) != null)
|
||
{
|
||
content += "已在【项目焊工】中使用，不能删除！";
|
||
}
|
||
|
||
if (Funs.DB.Pipeline_WeldJoint.FirstOrDefault(x => x.BackingWelderId == id) != null)
|
||
{
|
||
content += "已在【焊接信息】中使用，不能删除！";
|
||
}
|
||
|
||
return content;
|
||
}
|
||
#endregion
|
||
|
||
#endregion
|
||
|
||
#region 查询
|
||
/// <summary>
|
||
/// 查询
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void btnQuery_Click(object sender, EventArgs e)
|
||
{
|
||
this.BindGrid();
|
||
}
|
||
#endregion
|
||
|
||
#region 查看按钮
|
||
/// <summary>
|
||
/// 查看按钮
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void btnView_Click(object sender, EventArgs e)
|
||
{
|
||
if (GetButtonPower(Const.BtnSee))
|
||
{
|
||
PageContext.RegisterStartupScript(Window1.GetShowReference(String.Format("WelderManageView.aspx?WelderId={0}", Grid1.SelectedRowID, "查看 - ")));
|
||
}
|
||
else
|
||
{
|
||
ShowNotify(Resources.Lan.NoPrivilegePrompt, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 格式化字符串
|
||
/// <summary>
|
||
/// 获取性别
|
||
/// </summary>
|
||
/// <param name="sex"></param>
|
||
/// <returns></returns>
|
||
protected string ConvertSex(object sex)
|
||
{
|
||
if (sex != null)
|
||
{
|
||
if (sex.ToString() == "1")
|
||
{
|
||
return Resources.Lan.Male;
|
||
}
|
||
else if (sex.ToString() == "2")
|
||
{
|
||
return Resources.Lan.Female;
|
||
}
|
||
}
|
||
return null;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取是否在岗
|
||
/// </summary>
|
||
/// <param name="isOnduty"></param>
|
||
/// <returns></returns>
|
||
protected string ConvertIsOnDuty(object isOnduty)
|
||
{
|
||
if (isOnduty != null)
|
||
{
|
||
if (Convert.ToBoolean(isOnduty) == true)
|
||
{
|
||
return Resources.Lan.Yes;
|
||
}
|
||
else if (Convert.ToBoolean(isOnduty) == false)
|
||
{
|
||
return Resources.Lan.No;
|
||
}
|
||
}
|
||
return null;
|
||
}
|
||
#endregion
|
||
|
||
#region 打印按钮
|
||
/// <summary>
|
||
/// 打印按钮
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void btnPrint_Click(object sender, EventArgs e)
|
||
{
|
||
if (GetButtonPower(Const.BtnPrint))
|
||
{
|
||
PageContext.RegisterStartupScript(Window3.GetShowReference(String.Format("WelderPrint.aspx?WelderId={0}", Grid1.SelectedRowID, "打印 - ")));
|
||
}
|
||
else
|
||
{
|
||
ShowNotify(Resources.Lan.NoPrivilegePrompt, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 导出按钮
|
||
/// 导出按钮
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void btnOut_Click(object sender, EventArgs e)
|
||
{
|
||
string rootPath = Server.MapPath("~/") + Const.ExcelUrl;
|
||
//导出文件
|
||
string filePath = rootPath + DateTime.Now.ToString("yyyyMMddhhmmss") + "\\";
|
||
if (!Directory.Exists(filePath))
|
||
{
|
||
Directory.CreateDirectory(filePath);
|
||
}
|
||
string ReportFileName = filePath + "焊工基本信息.xlsx";
|
||
|
||
string strSql = @"SELECT Welder.WelderId, Welder.WelderCode, Welder.WelderName, Welder.UnitId,
|
||
Welder.Birthday,(CASE WHEN Welder.Sex=1 THEN '男' ELSE '女' END) AS Sex,
|
||
Welder.IdentityCard, Welder.CertificateNum, Welder.CertificateValidity,
|
||
Welder.WelderLevel, Welder.Remark,Unit.UnitName,Welder.IsOnDuty
|
||
FROM Welder_Welder AS Welder LEFT JOIN Base_Unit AS Unit ON Unit.UnitId = Welder.UnitId
|
||
WHERE 1=1 ";
|
||
DataTable dt = SQLHelper.RunSqlGetTable(strSql);
|
||
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
int rowIndex = 0;
|
||
XSSFWorkbook hssfworkbook = new XSSFWorkbook();
|
||
var style = CommonPrint.SetExcelStyle(hssfworkbook, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, VerticalAlignment.Center, HorizontalAlignment.Center, 10, true, false);
|
||
XSSFSheet ws = (XSSFSheet)hssfworkbook.CreateSheet("焊工基本信息");
|
||
|
||
#region 列宽
|
||
|
||
ws.SetColumnWidth(0, 6 * 256);
|
||
ws.SetColumnWidth(1, 10 * 256);
|
||
ws.SetColumnWidth(2, 20 * 256);
|
||
ws.SetColumnWidth(3, 12 * 256);
|
||
ws.SetColumnWidth(4, 19 * 256);
|
||
ws.SetColumnWidth(5, 20 * 256);
|
||
ws.SetColumnWidth(6, 27 * 256);
|
||
ws.SetColumnWidth(7, 12 * 256);
|
||
ws.SetColumnWidth(8, 11 * 256);
|
||
ws.SetColumnWidth(9, 11 * 256);
|
||
ws.SetColumnWidth(10, 14 * 256);
|
||
ws.SetColumnWidth(11, 18 * 256);
|
||
|
||
#endregion
|
||
|
||
#region 头部
|
||
|
||
ws = ExcelCreateRow(ws, hssfworkbook, rowIndex, rowIndex + 3, style, 0, 11, true);
|
||
|
||
//行0
|
||
CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 0, 11);
|
||
ws.AddMergedRegion(region);
|
||
ws.GetRow(rowIndex).GetCell(0).SetCellValue("巴斯夫（广东）一体化丁辛醇（OXO）\r\n焊工信息登记表");
|
||
ws.GetRow(rowIndex).GetCell(0).CellStyle = CommonPrint.SetExcelStyle(hssfworkbook, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, VerticalAlignment.Center, HorizontalAlignment.Center, 16);
|
||
|
||
//行1
|
||
region = new CellRangeAddress(rowIndex + 1, rowIndex + 1, 0, 11);
|
||
ws.AddMergedRegion(region);
|
||
ws.GetRow(rowIndex + 1).GetCell(0).SetCellValue($"时间：{DateTime.Now.ToString("yyyy-MM-dd")}");
|
||
ws.GetRow(rowIndex + 1).GetCell(0).CellStyle = CommonPrint.SetExcelStyle(hssfworkbook, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, VerticalAlignment.Center, HorizontalAlignment.Right, 10);
|
||
|
||
//行3
|
||
ws.GetRow(rowIndex + 2).GetCell(0).SetCellValue("序号");
|
||
ws.GetRow(rowIndex + 2).GetCell(1).SetCellValue("焊工姓名");
|
||
ws.GetRow(rowIndex + 2).GetCell(2).SetCellValue("考试中心焊工号");
|
||
ws.GetRow(rowIndex + 2).GetCell(3).SetCellValue("CC11焊工号");
|
||
ws.GetRow(rowIndex + 2).GetCell(4).SetCellValue("身份证号");
|
||
ws.GetRow(rowIndex + 2).GetCell(5).SetCellValue("焊工证号");
|
||
ws.GetRow(rowIndex + 2).GetCell(6).SetCellValue("合格项目");
|
||
ws.GetRow(rowIndex + 2).GetCell(7).SetCellValue("考试时间");
|
||
ws.GetRow(rowIndex + 2).GetCell(8).SetCellValue("考试项目");
|
||
ws.GetRow(rowIndex + 2).GetCell(9).SetCellValue("是否合格");
|
||
ws.GetRow(rowIndex + 2).GetCell(10).SetCellValue("在场状态");
|
||
ws.GetRow(rowIndex + 2).GetCell(11).SetCellValue("备注");
|
||
|
||
#endregion
|
||
|
||
#region 表格
|
||
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
rowIndex += 3;
|
||
var welderIds = GetColumnValues<string>(dt, "WelderId");
|
||
var weldlerList = Funs.DB.Welder_WelderQualify.Where(x => welderIds.Contains(x.WelderId)).ToList();
|
||
for (int i = 0; i < dt.Rows.Count; i++)
|
||
{
|
||
int num = 0;
|
||
var itemWelders = weldlerList.Where(x => x.WelderId == dt.Rows[i]["WelderId"].ToString()).ToList();
|
||
if (itemWelders.Count > 0)
|
||
{
|
||
num = itemWelders.Count - 1;
|
||
ws = ExcelCreateRow(ws, hssfworkbook, rowIndex, rowIndex + num, style, 0, 11);
|
||
if (num > 0)
|
||
{
|
||
//合并单元格
|
||
region = new CellRangeAddress(rowIndex, rowIndex + num, 0, 0);
|
||
ws.AddMergedRegion(region);
|
||
region = new CellRangeAddress(rowIndex, rowIndex + num, 1, 1);
|
||
ws.AddMergedRegion(region);
|
||
region = new CellRangeAddress(rowIndex, rowIndex + num, 2, 2);
|
||
ws.AddMergedRegion(region);
|
||
region = new CellRangeAddress(rowIndex, rowIndex + num, 3, 3);
|
||
ws.AddMergedRegion(region);
|
||
region = new CellRangeAddress(rowIndex, rowIndex + num, 4, 4);
|
||
ws.AddMergedRegion(region);
|
||
region = new CellRangeAddress(rowIndex, rowIndex + num, 5, 5);
|
||
ws.AddMergedRegion(region);
|
||
region = new CellRangeAddress(rowIndex, rowIndex + num, 10, 10);
|
||
ws.AddMergedRegion(region);
|
||
region = new CellRangeAddress(rowIndex, rowIndex + num, 11, 11);
|
||
ws.AddMergedRegion(region);
|
||
}
|
||
int j = 0;
|
||
foreach (var item in itemWelders)
|
||
{
|
||
int datanum = rowIndex + j;
|
||
//合格项目
|
||
ws.GetRow(datanum).GetCell(6).SetCellValue(item.QualifiedProjectCode);
|
||
//考试时间
|
||
ws.GetRow(datanum).GetCell(7).SetCellValue(item.CheckDate != null ? item.CheckDate.Value.ToString("yyyy-MM-dd") : string.Empty);
|
||
//考试项目
|
||
ws.GetRow(datanum).GetCell(8).SetCellValue(item.ExamProject);
|
||
//是否合格
|
||
string passStr = "否";
|
||
if (item.IsPass != null)
|
||
{
|
||
passStr = item.IsPass.Value ? "是" : "否";
|
||
}
|
||
ws.GetRow(datanum).GetCell(9).SetCellValue(passStr);
|
||
j++;
|
||
}
|
||
}
|
||
else
|
||
{
|
||
ws = ExcelCreateRow(ws, hssfworkbook, rowIndex, rowIndex, style, 0, 11);
|
||
}
|
||
//序号
|
||
ws.GetRow(rowIndex).GetCell(0).SetCellValue(i + 1);
|
||
//焊工姓名
|
||
ws.GetRow(rowIndex).GetCell(1).SetCellValue(dt.Rows[i]["WelderName"].ToString());
|
||
//考试中心焊工号
|
||
ws.GetRow(rowIndex).GetCell(2).SetCellValue(dt.Rows[i]["WelderLevel"].ToString());
|
||
//CC7焊工号
|
||
ws.GetRow(rowIndex).GetCell(3).SetCellValue(dt.Rows[i]["WelderCode"].ToString());
|
||
//身份证号
|
||
ws.GetRow(rowIndex).GetCell(4).SetCellValue(dt.Rows[i]["IdentityCard"].ToString());
|
||
//焊工证号
|
||
ws.GetRow(rowIndex).GetCell(5).SetCellValue(dt.Rows[i]["CertificateNum"].ToString());
|
||
//在场状态
|
||
string isOnDutyStr = string.Empty;
|
||
if (dt.Rows[i]["IsOnDuty"] != null)
|
||
{
|
||
isOnDutyStr = dt.Rows[i]["IsOnDuty"].ToString() == "1" ? "在场" : "离场";
|
||
}
|
||
ws.GetRow(rowIndex).GetCell(10).SetCellValue(isOnDutyStr);
|
||
//备注
|
||
ws.GetRow(rowIndex).GetCell(11).SetCellValue(dt.Rows[i]["Remark"].ToString());
|
||
rowIndex += 1 + num;
|
||
}
|
||
|
||
}
|
||
#endregion
|
||
|
||
ws.SetMargin(MarginType.LeftMargin, 0.3);
|
||
ws.SetMargin(MarginType.RightMargin, 0.2);
|
||
ws.PrintSetup.Landscape = true;
|
||
ws.PrintSetup.PaperSize = 9;
|
||
ws.ForceFormulaRecalculation = true;
|
||
using (FileStream filess = System.IO.File.OpenWrite(ReportFileName))
|
||
{
|
||
hssfworkbook.Write(filess);
|
||
}
|
||
FileInfo filet = new FileInfo(ReportFileName);
|
||
Response.Clear();
|
||
Response.Charset = "GB2312";
|
||
Response.ContentEncoding = System.Text.Encoding.UTF8;
|
||
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
|
||
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("焊工基本信息.xlsx"));
|
||
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
|
||
Response.AddHeader("Content-Length", filet.Length.ToString());
|
||
// 指定返回的是一个不能被客户端读取的流，必须被下载
|
||
Response.ContentType = "application/ms-excel";
|
||
// 把文件流发送到客户端
|
||
Response.WriteFile(filet.FullName);
|
||
// 停止页面的执行
|
||
Response.End();
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 导出方法
|
||
/// </summary>
|
||
/// <param name="grid"></param>
|
||
/// <returns></returns>
|
||
private string GetGridTableHtml(Grid grid)
|
||
{
|
||
StringBuilder sb = new StringBuilder();
|
||
grid.PageSize = 10000;
|
||
BindGrid();
|
||
this.Grid1.Columns[9].Hidden = true;
|
||
this.Grid1.Columns[10].Hidden = true;
|
||
sb.Append("<meta http-equiv=\"content-type\" content=\"application/excel; charset=UTF-8\"/>");
|
||
sb.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
|
||
sb.Append("<tr>");
|
||
foreach (GridColumn column in grid.Columns)
|
||
{
|
||
sb.AppendFormat("<td>{0}</td>", column.HeaderText);
|
||
}
|
||
sb.Append("</tr>");
|
||
foreach (GridRow row in grid.Rows)
|
||
{
|
||
|
||
sb.Append("<tr>");
|
||
foreach (GridColumn column in grid.Columns)
|
||
{
|
||
string html = row.Values[column.ColumnIndex].ToString();
|
||
|
||
sb.AppendFormat("<td>{0}</td>", html);
|
||
|
||
}
|
||
sb.Append("</tr>");
|
||
}
|
||
|
||
sb.Append("</table>");
|
||
|
||
return sb.ToString();
|
||
}
|
||
#endregion
|
||
|
||
#region 模板下载
|
||
/// <summary>
|
||
/// 模板下载
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void btnDownLoad_Click(object sender, EventArgs e)
|
||
{
|
||
string rootPath = Server.MapPath("~/");
|
||
string uploadfilepath = rootPath + Const.WelderInfoTemplateUrl;
|
||
string filePath = Const.WelderInfoTemplateUrl;
|
||
string fileName = Path.GetFileName(filePath);
|
||
FileInfo info = new FileInfo(uploadfilepath);
|
||
long fileSize = info.Length;
|
||
Response.ClearContent();
|
||
Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
|
||
Response.ContentType = "excel/plain";
|
||
Response.ContentEncoding = System.Text.Encoding.UTF8;
|
||
Response.AddHeader("Content-Length", fileSize.ToString().Trim());
|
||
Response.TransmitFile(uploadfilepath, 0, fileSize);
|
||
Response.End();
|
||
//PageContext.RegisterStartupScript(Confirm.GetShowReference("确定要下载焊工信息导入模板?", String.Empty, MessageBoxIcon.Question, PageManager1.GetCustomEventReference(false, "Confirm_OK"), PageManager1.GetCustomEventReference("Confirm_Cancel")));
|
||
}
|
||
|
||
/// <summary>
|
||
/// 下载导入模板
|
||
/// </summary>
|
||
/// <param name="sender"></param>
|
||
/// <param name="e"></param>
|
||
protected void PageManager1_CustomEvent(object sender, CustomEventArgs e)
|
||
{
|
||
if (e.EventArgument == "Confirm_OK")
|
||
{
|
||
string rootPath = Server.MapPath("~/");
|
||
string uploadfilepath = rootPath + Const.WelderInfoTemplateUrl;
|
||
string filePath = Const.WelderInfoTemplateUrl;
|
||
string fileName = Path.GetFileName(filePath);
|
||
FileInfo info = new FileInfo(uploadfilepath);
|
||
long fileSize = info.Length;
|
||
Response.ClearContent();
|
||
Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
|
||
Response.ContentType = "excel/plain";
|
||
Response.ContentEncoding = System.Text.Encoding.UTF8;
|
||
Response.AddHeader("Content-Length", fileSize.ToString().Trim());
|
||
Response.TransmitFile(uploadfilepath, 0, fileSize);
|
||
Response.End();
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
protected void btnImport_Click(object sender, EventArgs e)
|
||
{
|
||
string message = string.Empty;
|
||
errorInfos = string.Empty;
|
||
try
|
||
{
|
||
if (this.fileUpload.HasFile == false)
|
||
{
|
||
ShowNotify("请选择Excel文件!", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
string IsXls = Path.GetExtension(this.fileUpload.FileName).ToString().Trim().ToLower();
|
||
if (IsXls != ".xls" && IsXls != ".xlsx")
|
||
{
|
||
ShowNotify("只能选择Excel文件!", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
|
||
string rootPath = Server.MapPath("~/");
|
||
string initFullPath = rootPath + initPath;
|
||
if (!Directory.Exists(initFullPath))
|
||
{
|
||
Directory.CreateDirectory(initFullPath);
|
||
}
|
||
//指定上传文件名称
|
||
this.hidFileName.Text = BLL.Funs.GetNewFileName() + IsXls;
|
||
//上传文件路径
|
||
string filePath = initFullPath + this.hidFileName.Text;
|
||
//文件上传服务器
|
||
this.fileUpload.PostedFile.SaveAs(filePath);
|
||
//文件上传服务器后的名称
|
||
string fileName = rootPath + initPath + this.hidFileName.Text;
|
||
//读取Excel
|
||
DataSet ds = NPOIHelper.ExcelToDataSet(fileName, out errorInfos, true);
|
||
//验证Excel读取是否有误
|
||
if (!string.IsNullOrEmpty(errorInfos))
|
||
{
|
||
ShowNotify(errorInfos, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
|
||
if (ds.Tables.Count > 0)
|
||
{
|
||
List<Model.Welder_Welder> welderList = new List<Model.Welder_Welder>();
|
||
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
||
{
|
||
Model.Welder_Welder welder = new Model.Welder_Welder();
|
||
#region 数据验证
|
||
string welderId = SQLHelper.GetNewID(typeof(Model.Welder_Welder));
|
||
welder.WelderId = welderId;
|
||
|
||
if (ds.Tables[0].Rows[i]["焊工姓名"] != null && !string.IsNullOrEmpty(ds.Tables[0].Rows[i]["焊工姓名"].ToString()))
|
||
{
|
||
welder.WelderName = ds.Tables[0].Rows[i]["焊工姓名"].ToString();
|
||
}
|
||
else
|
||
{
|
||
errorInfos += (i + 2) + "行, 焊工姓名不能为空!</br>";
|
||
}
|
||
|
||
string insId = string.Empty;
|
||
if (ds.Tables[0].Rows[i]["焊工号"] != null && !string.IsNullOrEmpty(ds.Tables[0].Rows[i]["焊工号"].ToString()))
|
||
{
|
||
if (!BLL.WelderService.IsExisWelderCode(welderId, ds.Tables[0].Rows[i]["焊工号"].ToString()))
|
||
{
|
||
welder.WelderCode = ds.Tables[0].Rows[i]["焊工号"].ToString();
|
||
}
|
||
else
|
||
{
|
||
errorInfos += (i + 2) + "行, 焊工号已存在!</br>";
|
||
}
|
||
|
||
}
|
||
else
|
||
{
|
||
errorInfos += (i + 2) + "行, 焊工号不能为空!</br>";
|
||
}
|
||
|
||
if (ds.Tables[0].Rows[i]["所属单位"] != null && !string.IsNullOrEmpty(ds.Tables[0].Rows[i]["所属单位"].ToString()))
|
||
{
|
||
var unit = from x in Funs.DB.Base_Unit
|
||
where x.UnitName == ds.Tables[0].Rows[i]["所属单位"].ToString()
|
||
select x;
|
||
|
||
if (unit.Count() > 0)
|
||
{
|
||
welder.UnitId = unit.First().UnitId;
|
||
}
|
||
else
|
||
{
|
||
errorInfos += (i + 2) + "行, 所属单位不存在!</br>";
|
||
}
|
||
}
|
||
else
|
||
{
|
||
errorInfos += (i + 2) + "行, 所属单位不能为空!</br>";
|
||
}
|
||
|
||
if (ds.Tables[0].Rows[i]["性别"] != null && !string.IsNullOrEmpty(ds.Tables[0].Rows[i]["性别"].ToString()))
|
||
{
|
||
if (ds.Tables[0].Rows[i]["性别"].ToString() == "男")
|
||
{
|
||
welder.Sex = "1";
|
||
}
|
||
else if (ds.Tables[0].Rows[i]["性别"].ToString() == "女")
|
||
{
|
||
welder.Sex = "2";
|
||
}
|
||
else
|
||
{
|
||
errorInfos += (i + 2) + "行, 性别填写不规范，请填写男或女!</br>";
|
||
}
|
||
}
|
||
|
||
if (ds.Tables[0].Rows[i]["出生日期"] != null && !string.IsNullOrEmpty(ds.Tables[0].Rows[i]["出生日期"].ToString()))
|
||
{
|
||
try
|
||
{
|
||
DateTime b = Convert.ToDateTime(ds.Tables[0].Rows[i]["出生日期"].ToString());
|
||
welder.Birthday = b;
|
||
}
|
||
catch
|
||
{
|
||
errorInfos += (i + 2) + "行,出生日期格式不正确!</br>";
|
||
}
|
||
|
||
}
|
||
|
||
if (ds.Tables[0].Rows[i]["身份证号"] != null && !string.IsNullOrEmpty(ds.Tables[0].Rows[i]["身份证号"].ToString()))
|
||
{
|
||
if (ds.Tables[0].Rows[i]["身份证号"].ToString().Length == 18)
|
||
{
|
||
welder.IdentityCard = ds.Tables[0].Rows[i]["身份证号"].ToString();
|
||
welder.CertificateNum = ds.Tables[0].Rows[i]["身份证号"].ToString();
|
||
}
|
||
else
|
||
{
|
||
errorInfos += (i + 2) + "行,身份证号格式不正确!</br>";
|
||
}
|
||
}
|
||
else
|
||
{
|
||
errorInfos += (i + 2) + "行,身份证号不能为空!</br>";
|
||
}
|
||
|
||
if (ds.Tables[0].Rows[i]["备注"] != null && !string.IsNullOrEmpty(ds.Tables[0].Rows[i]["备注"].ToString()))
|
||
{
|
||
welder.Remark = ds.Tables[0].Rows[i]["备注"].ToString();
|
||
}
|
||
|
||
welder.IsOnDuty = true;
|
||
#endregion
|
||
|
||
welderList.Add(welder);
|
||
}
|
||
|
||
// 数据验证错误，返回
|
||
if (!string.IsNullOrEmpty(errorInfos))
|
||
{
|
||
ShowNotify(errorInfos, MessageBoxIcon.Warning, 10000);
|
||
return;
|
||
}
|
||
else
|
||
{
|
||
if (welderList.Count > 0)
|
||
{
|
||
Funs.DB.Welder_Welder.InsertAllOnSubmit(welderList);
|
||
Funs.DB.SubmitChanges();
|
||
ShowNotify("焊工信息导入成功!", MessageBoxIcon.Success);
|
||
this.BindGrid();
|
||
}
|
||
|
||
//foreach (var item in welderList)
|
||
//{
|
||
// BLL.WelderService.AddWelder(item);
|
||
//}
|
||
|
||
//PageContext.RegisterStartupScript(ActiveWindow.GetHidePostBackReference());
|
||
|
||
}
|
||
}
|
||
|
||
else
|
||
{
|
||
ShowAlert("没有数据!", MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
ShowAlert("'" + ex.Message + "'", MessageBoxIcon.Warning);
|
||
}
|
||
}
|
||
|
||
#region 获取按钮权限
|
||
/// <summary>
|
||
/// 获取按钮权限
|
||
/// </summary>
|
||
/// <param name="button"></param>
|
||
/// <returns></returns>
|
||
private bool GetButtonPower(string button)
|
||
{
|
||
return BLL.CommonService.GetAllButtonPowerList(this.CurrUser.LoginProjectId, this.CurrUser.UserId, BLL.Const.WelderManageMenuId, button);
|
||
}
|
||
#endregion
|
||
|
||
#region 私有方法
|
||
|
||
/// <summary>
|
||
/// 获取某一列的所有值
|
||
/// </summary>
|
||
/// <typeparam name="T">列数据类型</typeparam>
|
||
/// <param name="dtSource">数据表</param>
|
||
/// <param name="filedName">列名</param>
|
||
/// <returns></returns>
|
||
public static List<T> GetColumnValues<T>(DataTable dtSource, string filedName)
|
||
{
|
||
return (from r in dtSource.AsEnumerable() select r.Field<T>(filedName)).ToList<T>();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 行和列
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
private XSSFSheet ExcelCreateRow(XSSFSheet ws, XSSFWorkbook hssfworkbook, int sRows, int eRows, ICellStyle style, int cStart, int cEnd, bool istitle = false)
|
||
{
|
||
for (int i = sRows; i <= eRows; i++)
|
||
{
|
||
ws.CreateRow(i);
|
||
if (istitle)
|
||
{
|
||
ws.GetRow(i).HeightInPoints =
|
||
i == sRows ? 49.75f :
|
||
i == (sRows + 1) ? 13.75f :
|
||
38f;
|
||
}
|
||
else
|
||
{
|
||
ws.GetRow(i).HeightInPoints = 38f;
|
||
}
|
||
for (int j = cStart; j <= cEnd; j++)
|
||
{
|
||
ws.GetRow(i).CreateCell(j);
|
||
ws.GetRow(i).GetCell(j).CellStyle = style;
|
||
}
|
||
}
|
||
return ws;
|
||
}
|
||
|
||
#endregion
|
||
|
||
}
|
||
} |