ASP.NET EXCEL導入、身分証明書、携帯電話番号長さ検査データ検査
58277 ワード
--%>
#region
///
///
///
///
///
protected void btnBathAdd_Click(MiniButton sender, AjaxActionEventArgs e)
{
this.wpWin.JSShowAtPos(XAlign.center, YAlign.middle);
fileUpload.JSClear();
UploadeEntity item = new UploadeEntity();
fileForm.JSSetData(item);
}
//
protected void lbExportModule_Click(WuhanIns.Web.MiniUI.LinkButton sender, AjaxActionEventArgs e)
{
WriteToExcel();
this.JSCallMethod("downTemplate", "");
}
//
protected void btnBathImport_Click(MiniButton sender, AjaxActionEventArgs e)
{
//
this.fileUpload.JSStartUpload();
}
[FormPostBack("fileForm")]
protected void fileUpload_CustomAction(MiniControl sender, AjaxCustomEventArgs e)
{
UploadeEntity FormData = e.GetRequestParam("fileForm");
string fileName = "";
if (FormData != null)
{
fileName = FormData.fileName;
}
if (e.ActionType == "uploadsuccess")
{
//
UploadeEntity item = new UploadeEntity();
item.ErrMsg = " ！";
if (Session["FILE_UP_ERR_MGS"] != null && Session["FILE_UP_ERR_MGS"].ToString() != "")
{
item.ErrMsg = Session["FILE_UP_ERR_MGS"].ToString();
}
fileForm.JSSetData(item);
dgList.JSLoadData();
}
else if (e.ActionType == "uploaderror")
{
string strMgs = " ！";
this.JSAlert(strMgs);
}
else if (e.ActionType == "uploadcomplete")
{
//
this.JSShowTips(" ！");
}
}
///
/// Excel 【1、 excel datatable 2、 datatable 3、 db 】
///
///
///
protected void fileUpload_Upload(WuhanIns.Web.MiniUI.FileUpload sender, FileUploadEventArgs e)
{
#region
StringBuilder strErr = new StringBuilder();
//IDbConnection sqlCon = DBUtil.GetDbConnection();
//IDbTransaction sqlTrans = sqlCon.BeginTransaction();
try
{
Session["FILE_UP_ERR_MGS"] = "";
if (string.IsNullOrEmpty(e.PostFile.FileName)) return;
HttpPostedFile httpFile = e.PostFile;
string curProjectId = EntityIdSessionConfig.GetCurEntityIdSession(this, EntityIdSessionConfig.Project_Id);
//string curProjectId = e.GetRequestParam("Project_Id");
string strAddress = Server.MapPath("TempFiles");
if (!Directory.Exists(strAddress)) //
{
Directory.CreateDirectory(strAddress);
}
//
string descFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + httpFile.FileName;
string strFullAddress = Server.MapPath("TempFiles") + "\\" + descFileName;
httpFile.SaveAs(strFullAddress);// excel
DataSet ds = ExcelInput(strFullAddress);//Excel DataTable
//
List> htSuppliers = GetSuppliers();
//
//Hashtable ht = GetDept();
// ，
//Hashtable htWork = GetProjectWork();
//
IdTextItem[] payWaylist = IdTextItem.ToItems(typeof(EPC ));
//
IdTextItem[] positionNamelist = IdTextItem.ToItems(typeof( ));
// hashtable，
List> sfzhtWork = GetIdCardHt();
int irowIndex = 1;
//
string deptid = UserContext.CurrentUser.DeptId;
string deptName = UserContext.CurrentUser.DeptName;
//
List idCardList = new List();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
#region datatable
irowIndex++;
#region ，
string identityCard = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
if (string.IsNullOrEmpty(identityCard))
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
if (identityCard.Length != 15 && identityCard.Length != 18)
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
if ((identityCard.Length == 15 && !CheckIDCard15(identityCard)) || (identityCard.Length == 18 && !CheckIDCard18(identityCard)))
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
else
{
//
if (idCardList.Contains(identityCard))
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
else
{
idCardList.Add(identityCard);
}
}
HrStaffBase curData = HrStaffBaseManager.GetByIdCard(identityCard); //
//if (curData != null)
//{
// curData.DataState = InsSysLib.Core.DataState.Modify;
//}
//else
//{
// curData = new HrStaffBase();
// curData.DataState = InsSysLib.Core.DataState.New;
// curData.Id = Guid.NewGuid().ToString();
// curData.IdentityCard = identityCard;
//}
if (curData == null)
{
strErr.Append(" " + irowIndex + " ， ， ！\r
");
//continue;
}
else
{
if (curData.IdentityCard.Length == 15)
{
string birth = curData.IdentityCard.Substring(6, 6).Insert(4, "-").Insert(2, "-");
curData.Birthday = StrHelper.ParseDate(birth, DateTime.Now);//
}
if (curData.IdentityCard.Length == 18)
{
string birthday = curData.IdentityCard.Substring(6, 4) + "-" + curData.IdentityCard.Substring(10, 2) + "-" + curData.IdentityCard.Substring(12, 2);
curData.Birthday = StrHelper.ParseDate(birthday, DateTime.Now);//
}
if (curData.Birthday.HasValue)
curData.Age = DateTime.Now.Year - curData.Birthday.Value.Year;//
if (curData.Age == 0 || curData.Age == null)
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
if (string.IsNullOrEmpty(dr[" "].ToString()))
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
else
{
curData.Name = dr[" "] == DBNull.Value ? "" : dr[" "].ToString();
}
if (string.IsNullOrEmpty(dr[" "].ToString()))
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
else
{
curData.Sex = dr[" "] == DBNull.Value ? (int) . : (int)Enum.Parse(typeof( ), dr[" "].ToString());
}
if (!string.IsNullOrEmpty(curData.IdentityCard) && sfzhtWork.Count(x => x.Item2.Contains(dr[" "].ToString().Trim())) > 0)
{
curData.Id = sfzhtWork.FirstOrDefault(x => x.Item2.Contains(dr[" "].ToString().Trim())) == null ? ""
: sfzhtWork.FirstOrDefault(x => x.Item2.Contains(dr[" "].ToString().Trim())).Item1;
}
curData.AnmeldenLocationNumber = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
if (string.IsNullOrEmpty(curData.AnmeldenLocationNumber))
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
if (string.IsNullOrEmpty(dr[" "].ToString()))
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
else
{
curData.EducationalLevel = dr[" "] == DBNull.Value ? (int) . : (int)Enum.Parse(typeof( ), dr[" "].ToString());
}
//
curData.ProjectId = curProjectId;
if (String.IsNullOrEmpty(curData.SubcontractorId))
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
else
{
//
curData.SubcontractorId = dr[" "] == DBNull.Value ? "" : htSuppliers.Count(x => x.Item2 == dr[" "].ToString()) > 0 ? htSuppliers.FirstOrDefault(x => x.Item2 == dr[" "].ToString()).Item1 : "";
}
//else
//{
// // ， continue
// var isblacklist = htSuppliers.FirstOrDefault(x => x.Item2 == dr[" "].ToString()).Item3;
// if (isblacklist == "1")
// {
// strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
// continue;
// }
//}
//
//curData.SignDeptId = dr[" "] == DBNull.Value ? "" : htSuppliers.Count(x => x.Item2 == dr[" "].ToString()) > 0 ? htSuppliers.FirstOrDefault(x => x.Item2 == dr[" "].ToString()).Item1 : "";
//if (String.IsNullOrEmpty(curData.Sign_dept_id))
//{
// strErr.Append(" " + irowIndex + " 【 】 ！\r
");
// continue;
//}
curData.PositionName = dr[" "] == DBNull.Value ? "" : dr[" "].ToString();
if (!string.IsNullOrEmpty(curData.PositionName))
{
curData.Position = ((int)Enum.Parse(typeof( ), dr[" "].ToString())).ToString();
}
//curData.Position = dr[" "] == DBNull.Value ? "" : htWork.ContainsKey(dr[" "].ToString()) ? htWork[dr[" "].ToString()].ToString() : "";
//curData.Position = dr[" "] == DBNull.Value ? "" : ((int)Enum.Parse(typeof( ), dr[" "].ToString())).ToString();
if (string.IsNullOrEmpty(curData.Position))
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
curData.Seniority = dr[" "] == DBNull.Value ? DBUtil.INVALID_ID : StrHelper.ParseInt(dr[" "].ToString(), DBUtil.INVALID_ID);
int outintvalue;
if (!string.IsNullOrEmpty(dr[" "].ToString()))
{
if (!Int32.TryParse(dr[" "].ToString(), out outintvalue))
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
//continue;
}
if (outintvalue < 0)
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
//continue;
}
}
//curData.ComingDate = DateTime.Now;//
//curData.Coming_date = dr[" "] == DBNull.Value ? DBUtil.INVALID_DATE : StrHelper.ParseDate(dr[" "].ToString(), DBUtil.INVALID_DATE);
//DateTime outdate;
//if (!string.IsNullOrEmpty(dr[" "].ToString()))
//{
// if (!DateTime.TryParse(dr[" "].ToString(), out outdate))
// {
// strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
// continue;
// }
//}
//else
//{
// strErr.Append(" " + irowIndex + " 【 】 ！\r
");
// continue;
//}
if (payWaylist.Count(x => x.Text == dr[" "].ToString()) > 0)
{
curData.PayType = Convert.ToInt32(payWaylist.FirstOrDefault(x => x.Text == dr[" "].ToString()).Id);
}
//curData.BankAccount = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
if (string.IsNullOrEmpty(dr[" "].ToString()))
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
else
{
if (CheckBankAccount(dr[" "].ToString().Trim()))
{
curData.BankAccount = dr[" "].ToString().Trim();
}
else
{
strErr.Append(" " + irowIndex + " 【 】 ， 16 19 ！\r
");
//continue;
}
}
if (!string.IsNullOrEmpty(dr[" "].ToString()))
{
if (dr[" "].ToString() == " " || dr[" "].ToString() == " ")
{
curData.Ispoor = string.IsNullOrEmpty(dr[" "].ToString()) ? DBUtil.INVALID_ID : (int)Enum.Parse(typeof(InsSysLib.Core. ), dr[" "].ToString());
}
else
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
//continue;
}
}
curData.Address = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
curData.EmergencyContact = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
if (string.IsNullOrEmpty(dr[" "].ToString()))
{
strErr.Append(" " + irowIndex + " 【 】 ！\r
");
//continue;
}
else
{
if (CheckContactTel(dr[" "].ToString().Trim()))
{
curData.EmergencyContactTel = dr[" "].ToString().Trim();
}
else
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
//continue;
}
}
curData.ContactRelationship = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
//curData.Occupational = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
#region
curData.PassportNumber = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
curData.PassportType = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
curData.Visanumber = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
curData.HzPeriod = dr[" "] == DBNull.Value ? DBUtil.INVALID_DATE : StrHelper.ParseDate(dr[" "].ToString(), DBUtil.INVALID_DATE);
DateTime HzPeriod;
if (!string.IsNullOrEmpty(dr[" "].ToString()))
{
if (!DateTime.TryParse(dr[" "].ToString(), out HzPeriod))
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
}
}
curData.HandlingDate = dr[" "] == DBNull.Value ? DBUtil.INVALID_DATE : StrHelper.ParseDate(dr[" "].ToString(), DBUtil.INVALID_DATE);
DateTime HandlingDate;
if (!string.IsNullOrEmpty(dr[" "].ToString()))
{
if (!DateTime.TryParse(dr[" "].ToString(), out HandlingDate))
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
}
}
curData.DueDate = dr[" "] == DBNull.Value ? DBUtil.INVALID_DATE : StrHelper.ParseDate(dr[" "].ToString(), DBUtil.INVALID_DATE);
DateTime DueDate;
if (!string.IsNullOrEmpty(dr[" "].ToString()))
{
if (!DateTime.TryParse(dr[" "].ToString(), out DueDate))
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
}
}
#endregion
#region /
curData.SpecialCerCode = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
curData.SpecialCerTime = dr[" "] == DBNull.Value ? DBUtil.INVALID_DATE : StrHelper.ParseDate(dr[" "].ToString(), DBUtil.INVALID_DATE);
DateTime SpecialCerTime;
if (!string.IsNullOrEmpty(dr[" "].ToString()))
{
if (!DateTime.TryParse(dr[" "].ToString(), out SpecialCerTime))
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
}
}
if (!string.IsNullOrEmpty(dr[" "].ToString()))
{
if (dr[" "].ToString() == " " || dr[" "].ToString() == " ")
{
curData.IsOperation = string.IsNullOrEmpty(dr[" "].ToString()) ? DBUtil.INVALID_ID : (int)Enum.Parse(typeof(InsSysLib.Core. ), dr[" "].ToString());
if (dr[" "].ToString() == " ")
{
if (string.IsNullOrEmpty(dr[" "].ToString()))
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
}
if (string.IsNullOrEmpty(dr[" "].ToString()))
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
}
}
}
else
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
}
}
curData.InsuranceType = dr[" "] == DBNull.Value ? "" : dr[" "].ToString().Trim();
curData.InsuranceTime = dr[" "] == DBNull.Value ? DBUtil.INVALID_DATE : StrHelper.ParseDate(dr[" "].ToString(), DBUtil.INVALID_DATE);
DateTime InsuranceTime;
if (!string.IsNullOrEmpty(dr[" "].ToString()))
{
if (!DateTime.TryParse(dr[" "].ToString(), out InsuranceTime))
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
}
}
if (!string.IsNullOrEmpty(dr[" "].ToString()))
{
if (dr[" "].ToString() == " " || dr[" "].ToString() == " ")
{
curData.Insurance = string.IsNullOrEmpty(dr[" "].ToString()) ? DBUtil.INVALID_ID : (int)Enum.Parse(typeof(InsSysLib.Core. ), dr[" "].ToString());
if (dr[" "].ToString() == " ")
{
if (string.IsNullOrEmpty(dr[" "].ToString()))
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
}
if (string.IsNullOrEmpty(dr[" "].ToString()))
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
}
}
}
else
{
strErr.Append(" " + irowIndex + " 【 】 ， ！\r
");
}
}
#endregion
curData.DataState = InsSysLib.Core.DataState.Modify;
curData.CurState = (int)EPC . ;
}
#endregion
if (!string.IsNullOrEmpty(strErr.ToString()))
{
continue;
}
HrStaffBaseManager.Save(curData); // db
//if (!string.IsNullOrEmpty(curData.Id))
//{
// HrStaffBaseManager.Service.Modify(curData.Id, curData, sqlTrans);//
//}
//else
//{
// HrStaffBaseManager.Service.Save(curData, sqlTrans);//
//}
#endregion
}
}
File.Delete(strFullAddress);// excel ，
Session["FILE_UP_ERR_MGS"] = strErr.ToString();
//sqlTrans.Commit();
}
catch (Exception ex)
{
//sqlTrans.Rollback();
Session["FILE_UP_ERR_MGS"] = ex.Message;
}
finally
{
//sqlCon.Close();
}
#endregion
}
#region 18 15
///
/// 18
///
///
///
private bool CheckIDCard18(string idNumber)
{
long n = 0;
if (long.TryParse(idNumber.Remove(17), out n) == false
|| n < Math.Pow(10, 16) || long.TryParse(idNumber.Replace('x', '0').Replace('X', '0'), out n) == false)
{
return false;//
}
string address = "11x22x35x44x53x12x23x36x45x54x13x31x37x46x61x14x32x41x50x62x15x33x42x51x63x21x34x43x52x64x65x71x81x82x91";
if (address.IndexOf(idNumber.Remove(2)) == -1)
{
return false;//
}
string birth = idNumber.Substring(6, 8).Insert(6, "-").Insert(4, "-");
DateTime time = new DateTime();
if (DateTime.TryParse(birth, out time) == false)
{
return false;//
}
string[] arrVarifyCode = ("1,0,x,9,8,7,6,5,4,3,2").Split(',');
string[] Wi = ("7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2").Split(',');
char[] Ai = idNumber.Remove(17).ToCharArray();
int sum = 0;
for (int i = 0; i < 17; i++)
{
sum += int.Parse(Wi[i]) * int.Parse(Ai[i].ToString());
}
int y = -1;
Math.DivRem(sum, 11, out y);
if (arrVarifyCode[y] != idNumber.Substring(17, 1).ToLower())
{
return false;//
}
return true;// GB11643-1999
}
///
/// 15
///
///
///
private bool CheckIDCard15(string Id)
{
long n = 0; if (long.TryParse(Id, out n) == false || n < Math.Pow(10, 14))
{
return false;//
}
string address = "11x22x35x44x53x12x23x36x45x54x13x31x37x46x61x14x32x41x50x62x15x33x42x51x63x21x34x43x52x64x65x71x81x82x91";
if (address.IndexOf(Id.Remove(2)) == -1)
{
return false;//
}
string birth = Id.Substring(6, 6).Insert(4, "-").Insert(2, "-"); DateTime time = new DateTime();
if (DateTime.TryParse(birth, out time) == false)
{
return false;//
}
return true;// 15
}
#endregion
#region 16 19
private bool CheckBankAccount(string BankAccount)
{
bool result = false;
//string regex = @"^\d{16}|\d{19}$";//16 19
string regex = @"^([0-9]{16}|[0-9]{19})$";//16 19
Match m = Regex.Match(BankAccount, regex);
if (m.Success)
{
result = true;
}
return result;
}
private bool CheckContactTel(string ContactTel)
{
bool result = false;
bool result1 = false;
bool result2 = false;
//https://c.runoob.com/front-end/854
string regex1 = @"^(13[0-9]|14[5|7]|15[0|1|2|3|4|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])\d{8}$";//
string regex2 = @"\d{3}-\d{8}|\d{4}-\d{7}";// (0511-4405222、021-87888822)
Match m1 = Regex.Match(ContactTel, regex1);
if (m1.Success)
{
result1 = true;
}
Match m2 = Regex.Match(ContactTel, regex2);
if (m2.Success)
{
result2 = true;
}
if (result1 || result2)
{
result = true;
}
return result;
}
#endregion
///
/// Excel DataTable
///
///
/// DataTable
public static DataSet ExcelInput(string FilePath)
{
#region xlsx
//DataSet ds = new DataSet();
excel XSSFWorkbook， excel
//xlsx
//XSSFWorkbook workbook = new XSSFWorkbook(File.Open(FilePath, FileMode.Open));
//XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
xls
HSSFWorkbook workbook = new HSSFWorkbook(File.Open(FilePath, FileMode.Open));
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
//DataTable table = new DataTable();
//table.TableName = sheet.SheetName;
excel sheet
Excel
//int rowsCount = sheet.PhysicalNumberOfRows;// Excel
Table Excel ， （ Sheet）。
Excel ， ， 0 。
int colsCount = sheet.GetRow(1).PhysicalNumberOfCells;// Excel
//int colsCount = 17;// Excel
//for (int i = 0; i < colsCount; i++)
//{
// table.Columns.Add(sheet.GetRow(1).GetCell(i).ToString());
//}
//for (int x = 2; x < rowsCount; x++)
//{
// DataRow dr = table.NewRow();
// if (colsCount >= 1)
// {
// for (int y = 0; y < colsCount; y++)
// {
// if (sheet.GetRow(x).GetCell(y) != null)
// dr[y] = sheet.GetRow(x).GetCell(y).ToString();
// else
// dr[y] = null;
// }
// }
// table.Rows.Add(dr);
//}
//ds.Tables.Add(table);
//return ds;
#endregion
#region xls ， xls ， xlsx
DataSet ds = new DataSet();
// excel XSSFWorkbook， excel
xlsx
//XSSFWorkbook workbook = new XSSFWorkbook(File.Open(FilePath, FileMode.Open));
//XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
//xls
HSSFWorkbook workbook = new HSSFWorkbook(File.Open(FilePath, FileMode.Open));
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
DataTable table = new DataTable();
table.TableName = sheet.SheetName;
// excel sheet
// Excel
int rowsCount = sheet.PhysicalNumberOfRows;// Excel
// Table Excel ， （ Sheet）。
// Excel ， ， 0 。
//int colsCount = sheet.GetRow(1).PhysicalNumberOfCells;// Excel
int colsCount = 29;// Excel 17
for (int i = 0; i < colsCount; i++)
{
//table.Columns.Add(sheet.GetRow(1).GetCell(i).ToString());
table.Columns.Add(sheet.GetRow(0).GetCell(i).ToString());//
}
//for (int x = 2; x < rowsCount; x++)
for (int x = rowsCount - 1; x < rowsCount; x++)// ，
{
DataRow dr = table.NewRow();
if (colsCount >= 1)
{
for (int y = 0; y < colsCount; y++)//
{
//if (sheet.GetRow(x).GetCell(y) != null)
// dr[y] = sheet.GetRow(x).GetCell(y).ToString();
//else
// dr[y] = null;
if (sheet.GetRow(x).GetCell(y) != null)
{
ICell cellItem = sheet.GetRow(x).GetCell(y);//
if (cellItem.CellType == NPOI.SS.UserModel.CellType.Numeric && DateUtil.IsCellDateFormatted(cellItem))// Numeric， ，
{
//dr[y] = cellItem.DateCellValue.ToString("yyyy/MM/dd");
dr[y] = cellItem.DateCellValue.ToString("yyyy-MM-dd");
}
else
{
dr[y] = sheet.GetRow(x).GetCell(y).ToString();
}
}
else
dr[y] = null;
}
}
table.Rows.Add(dr);
}
ds.Tables.Add(table);
return ds;
#endregion
}
private XSSFWorkbook xssfworkbook;
///
/// excel
///
public void DownloadExcel()
{
string fileName = " .xlsx";
string sServerPath = Server.MapPath("TempFiles") + "\\" + fileName;//Server.MapPath(fileName);
FileInfo file = new FileInfo(sServerPath);
FileStream myFile = File.OpenRead(sServerPath);
byte[] byteData = new byte[myFile.Length];
myFile.Read(byteData, 0, (int)myFile.Length);
// byte[] byteData = data.Data;
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.ClearContent();
response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(file.Name));
response.AddHeader("Content-Length", byteData.Length.ToString());
response.AddHeader("Content-Transfer-Encoding", "binary");
//response.ContentType = contentType;
response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
response.BinaryWrite(byteData);
response.Flush();
response.End();
//File.Delete(sServerPath);
}
///
/// Excel
///
public void WriteToExcel()
{
if (!Directory.Exists(Server.MapPath("TempFiles")))
{
Directory.CreateDirectory(Server.MapPath("TempFiles"));
}
// Excel
string sDownLoadServerPath = Server.MapPath("TempFiles") + "\\" + " .xlsx";
// Excel
string fileName = "TempFiles/ 1.xlsx";
string sServerPath = Server.MapPath(fileName);
FileStream myFile = File.OpenRead(sServerPath);
FileStream file = new FileStream(sServerPath, FileMode.Open, FileAccess.Read);
xssfworkbook = new XSSFWorkbook(file);
ISheet sheet = xssfworkbook.GetSheet(" ");
IRow row = null;
// list
//
//var personList = CommonTypeCache.ListCommonTypeByDefCode(ConstuctionCommonType. , true);
//
//var gangweilist = new List();
//List entities = RangeCache.QueryProjectWorkerForCache(null, new OrderByParams("Inner_Code"));
//foreach (CacheObject entity in entities)
//{
// gangweilist.Add(new TreeListItem { Id = entity.Id, Text = "[" + entity.OuterCode + "]" + entity.Name });
//}
//
var gongzhonglist = IdTextItem.ToItems(typeof( ));
//var personStatelist = IdTextItem.ToItems(typeof(EPC ));
//
var Isfalselist = IdTextItem.ToItems(typeof( ));
//
var Issexlist = IdTextItem.ToItems(typeof( ));
//
var studylist = IdTextItem.ToItems(typeof( ));
//
var payWaylist = IdTextItem.ToItems(typeof(EPC ));
//
//var deptlist = new List();
//
List ht = GetSupplierIdTextList();
//string sCompanyId = UserContext.CurrentUser.CompanyId;
//OrgQueryParams queryParam = new OrgQueryParams();
//queryParam.OrgPartType = OrgPartEnum. ;
//queryParam.IsRecursive = true;
//List _items = SystemServiceFactory.Service.GetOrgAncestors(sCompanyId);
//foreach (TreeListItem item in _items)
//{
// if (string.IsNullOrEmpty(item.ParentId))
// queryParam.RootOrgId = item.Id;
//}
//queryParam.OrgType = OrgTypeEnum. ;
//queryParam.AppendRootNode = true;
//deptlist = SystemServiceFactory.Service.GetOrganizationList(UserContext.CurrentUser.CurrentAppID, queryParam);
//List items1 = SystemServiceFactory.Service.GetAllUpperOutCompy(sCompanyId, true);
//deptlist.AddRange(items1);
var maxCountList = new List{ //gangweilist.Count(),//personList.Count(),deptlist.Count, personStatelist.Count(),
gongzhonglist.Count(),Isfalselist.Count(), studylist.Count(), ht.Count,payWaylist.Count() };
var maxNum = Convert.ToInt32(maxCountList.OrderByDescending(x => x).FirstOrDefault().ToString());
for (int i = 0; i < maxNum; i++)
{
row = sheet.CreateRow(i + 1);
var countindex = i;
row.CreateCell(0).SetCellValue(ht.Count > countindex ? (ht[countindex] == null ? "" : ht[countindex].Text) : "");
row.CreateCell(1).SetCellValue(gongzhonglist.Count() > countindex ? (gongzhonglist[countindex] == null ? "" : gongzhonglist[countindex].Text) : "");
row.CreateCell(2).SetCellValue(Issexlist.Count() > countindex ? (Issexlist[countindex] == null ? "" : Issexlist[countindex].Text) : "");
row.CreateCell(3).SetCellValue(payWaylist.Count() > countindex ? (payWaylist[countindex] == null ? "" : payWaylist[countindex].Text) : "");
row.CreateCell(4).SetCellValue(studylist.Count() > countindex ? (studylist[countindex] == null ? "" : studylist[countindex].Text) : "");
//row.CreateCell(5).SetCellValue(studylist.Count() > countindex ? (studylist[countindex] == null ? "" : studylist[countindex].Text) : "");
//row.CreateCell(6).SetCellValue(Issexlist.Count() > countindex ? (Issexlist[countindex] == null ? "" : Issexlist[countindex].Text) : "");
//row.CreateCell(7).SetCellValue(deptlist.Count > countindex ? (deptlist[countindex] == null ? "" : deptlist[countindex].Text) : "");
//rowCount++;
}
sheet.ForceFormulaRecalculation = true;
file = new FileStream(sDownLoadServerPath, FileMode.Create);
xssfworkbook.Write(file);
file.Close();
file.Dispose();
}
/
/ Excel
/
/
//public void InitWorkbook(FileStream stream)
//{
// xssfworkbook = new HSSFWorkbook(stream);
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "Ins lnc";
// xssfworkbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Subject = " ";
// xssfworkbook.SummaryInformation = si;
//}
///
///
///
/// sheet
///
/// sheet
///
///
///
public void SetChongeSource(ref ISheet sheet, int colIndex, string sSheetName, string letter, int startRowIndex, int endRowIndex, string dicRange)
{
CellRangeAddressList rangeList = new CellRangeAddressList(1, 1000, colIndex, colIndex);
HSSFName range = (HSSFName)xssfworkbook.CreateName();
range.RefersToFormula = sSheetName + "!$" + letter + "$" + startRowIndex + ":$" + letter + "$" + endRowIndex;
range.NameName = dicRange;
DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(dicRange);
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);
((HSSFSheet)sheet).AddValidationData(dataValidation);
}
///
/// sheet
///
/// sheet
/// sheet
/// sheet
public void InitSheet(ref ISheet currentSheet, int sheetIndex, string sSheetName)
{
try
{
currentSheet = xssfworkbook.GetSheetAt(sheetIndex);
}
catch { }
if (currentSheet != null)
{
xssfworkbook.RemoveSheetAt(sheetIndex);
}
currentSheet = xssfworkbook.CreateSheet(sSheetName);
xssfworkbook.SetSheetHidden(sheetIndex, true);
}
#region
//
protected Hashtable GetDept()
{
string sCompanyId = UserContext.CurrentUser.CompanyId;
List items = new List();
OrgQueryParams queryParam = new OrgQueryParams();
queryParam.OrgPartType = OrgPartEnum. ;
queryParam.IsRecursive = true;
List _items = SystemServiceFactory.Service.GetOrgAncestors(sCompanyId);
foreach (TreeListItem item in _items)
{
if (string.IsNullOrEmpty(item.ParentId))
queryParam.RootOrgId = item.Id;
}
queryParam.OrgType = OrgTypeEnum. ;
queryParam.AppendRootNode = true;
items = SystemServiceFactory.Service.GetOrganizationList(UserContext.CurrentUser.CurrentAppID, queryParam);
List items1 = SystemServiceFactory.Service.GetAllUpperOutCompy(sCompanyId, true);
items.AddRange(items1);
Hashtable ht = new Hashtable();
int d = 0;
foreach (TreeListItem t in items)
{
if (t.Text != null && !ht.ContainsKey(t.Text))
{
ht.Add(t.Text, t.Id);
}
}
return ht;
}
//
private Hashtable GetCommonType()
{
TreeListItem[] listItems = CommonTypeCache.ListCommonTypeByDefCode(ConstuctionCommonType. , false); // (ConstuctionCommonType. );
Hashtable ht = new Hashtable();
foreach (TreeListItem t in listItems)
{
if (t.Text != null && !ht.ContainsKey(t.Text))
{
ht.Add(t.Text, t.Id);
}
}
return ht;
}
//
//private Hashtable GetProjectWork()
//{
// List entities = RangeCache.QueryProjectWorkerForCache(null, new OrderByParams("Inner_Code"));
// Hashtable ht = new Hashtable();
// foreach (CacheObject entity in entities)
// {
// if (entity.Name != null && !ht.ContainsKey(entity.Name))
// {
// ht.Add("[" + entity.OuterCode + "]" + entity.Name, entity.Id);
// }
// }
// return ht;
//}
//id
private List> GetIdCardHt()
{
//HrStaffBaseParams ps = new HrStaffBaseParams();
//ps.Deleted = 0;
//ps.Project_id = string.IsNullOrEmpty(curProjectId) ? "-1" : curProjectId; 。
var idcardlist = new List>();
var HrStaffBase = HrStaffBaseManager.Find(true);//(ps, null, null);
foreach (HrStaffBase entity in HrStaffBase)
{
if (entity.IdentityCard != null)
{
var item = new Tuple(entity.Id, entity.IdentityCard);
idcardlist.Add(item);
}
}
return idcardlist;
}
//
private Hashtable GetEnumData(Type type)
{
Hashtable ht = new Hashtable();
foreach (int myCode in Enum.GetValues(type))
{
string strName = Enum.GetName(type, myCode);//
string strVaule = myCode.ToString();//
if (!ht.ContainsKey(strName))
{
ht.Add(strName, strVaule);
}
}
return ht;
}
// （ ）
private Hashtable GetEnumData2(Type type)
{
Hashtable ht = new Hashtable();
foreach (int myCode in Enum.GetValues(type))
{
string strName = Enum.GetName(type, myCode);//
string strVaule = myCode.ToString();//
if (!ht.ContainsKey(strName))
{
ht.Add(strVaule, strName);
}
}
return ht;
}
///
///
///
///
private List> GetSuppliers()
{
#region
//List> ht = new List>();
SupplierBaseInfoParams supplierPs = new SupplierBaseInfoParams();
supplierPs.Sup_type = (int) . ;
string sProjectId = EntityIdSessionConfig.GetCurEntityIdSession(EntityIdSessionConfig.Project_Id);
if (!AppConfigUtil.isEnterprise)
supplierPs.Project_id = sProjectId;
else
{
SqlComplexParam sql = new SqlComplexParam(" t.PROJECT_ID IS NULL");
supplierPs.AppendChild(sql);
}
//List baseInfos = SupplierBaseInfoManager.Find(true);// Service.QueryIDOsByParams(supplierPs, null, null);
//if (baseInfos != null && baseInfos.Count() > 0)
//{
// foreach (SupplierBaseInfo entity in baseInfos)
// {
// ht.Add(new Tuple(entity.Id, entity.Supplier_name, entity.Isblacklist.ToString()));
// }
//}
//return ht;
#endregion
#region
List> ht = new List>();
List baseInfos = HrSubcontractorManager.Find(true);
if (baseInfos != null && baseInfos.Count() > 0)
{
foreach (SubcontractorBase entity in baseInfos)
{
ht.Add(new Tuple(entity.Id, entity.SubcontractorName, entity.Isblacklist.ToString()));
}
}
return ht;
#endregion
}
///
///
///
///
private List GetSupplierIdTextList()
{
#region
//List list = new List();
SupplierBaseInfoParams supplierPs = new SupplierBaseInfoParams();
supplierPs.Sup_type = (int) . ;
//string sProjectId = EntityIdSessionConfig.GetCurEntityIdSession(EntityIdSessionConfig.Project_Id);
if (!AppConfigUtil.isEnterprise)
supplierPs.Project_id = sProjectId;
else
{
SqlComplexParam sql = new SqlComplexParam(" t.PROJECT_ID IS NULL");
supplierPs.AppendChild(sql);
}
//List baseInfos = SupplierBaseInfoManager.GetSuppliersByType(sProjectId, (int)InsSysLib.Core. . );//.Service.QueryIDOsByParams(supplierPs, null, null);
//if (baseInfos != null && baseInfos.Count() > 0)
//{
// foreach (SupplierBaseInfo entity in baseInfos)
// {
// list.Add(new IdTextItem { Id = entity.Id, Text = entity.Supplier_name });
// }
//}
//return list;
#endregion
#region
List list = new List();
string sProjectId = EntityIdSessionConfig.GetCurEntityIdSession(EntityIdSessionConfig.Project_Id);
List baseInfos = HrSubcontractorManager.Find(true);
if (baseInfos != null && baseInfos.Count() > 0)
{
foreach (SubcontractorBase entity in baseInfos)
{
list.Add(new IdTextItem { Id = entity.Id, Text = entity.SubcontractorName });
}
}
return list;
#endregion
}
/
/ ( )
/
/ 。 ：
/
/ sheet
//public int SetCellDataSource2Sort(string datasourcename, Hashtable ht, int colindex, string sheetname)
//{
// //
// ISheet Sheet2 = xssfworkbook.GetSheet(sheetname);
// if (Sheet2 == null)
// {
// Sheet2 = xssfworkbook.CreateSheet();
// }
// // 。 ：
// IRow row = Sheet2.GetRow(0);
// if (row == null)
// {
// row = Sheet2.CreateRow(0);
// }
// row.CreateCell(colindex).SetCellValue(datasourcename);
// // 。
// IRow row2 = Sheet2.GetRow(1);
// if (row2 == null)
// {
// row2 = Sheet2.CreateRow(1);
// }
// row2.CreateCell(colindex).SetCellValue("");
// int i = 2;
// ArrayList alv = new ArrayList(ht.Keys);
// alv.Sort();
// foreach (object obj in alv)
// {
// IRow curRow = Sheet2.GetRow(i);
// if (curRow == null)
// {
// curRow = Sheet2.CreateRow(i);
// }
// curRow.CreateCell(colindex).SetCellValue(ht[obj].ToString());
// i++;
// }
// return ht.Count + 2;
//}
#endregion
#endregion