/// <summary>
/// excel转换为table
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void EXCELTOTABLE(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
try
{
DataTable dt = new DataTable();
HttpPostedFile _upfile = context.Request.Files["upFile"];
string headrow = context.Request["headrow"] ?? "0";//头部开始行下标
if (_upfile == null)
{
msg.ErrorMsg = "请选择要上传的文件 ";
}
else
{
string fileName = _upfile.FileName; /*获取文件名： C:\Documents and Settings\Administrator\桌面\123.jpg*/
string suffix = fileName.Substring(fileName.LastIndexOf(".") + 1).ToLower(); /*获取后缀名并转为小写： jpg*/
int bytes = _upfile.ContentLength; //获取文件的字节大小
if (suffix == "xls" || suffix == "xlsx")
{
IWorkbook workbook = null;
Stream stream = _upfile.InputStream;
if (suffix == "xlsx") // 2007版本
{
workbook = new XSSFWorkbook(stream);
}
else if (suffix == "xls") // 2003版本
{
workbook = new HSSFWorkbook(stream);
}
//获取excel的第一个sheet
ISheet sheet = workbook.GetSheetAt(0);
//获取sheet的第一行
IRow headerRow = sheet.GetRow(int.Parse(headrow));
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
if (rowCount <= int.Parse(headrow))
{
msg.ErrorMsg = "文件中无数据! ";
}
else
{
List <JH_Auth_ExtendDataB.IMPORTYZ> yz = new List <JH_Auth_ExtendDataB.IMPORTYZ>();
yz = new JH_Auth_ExtendDataB().GetTable(P1, UserInfo.QYinfo.ComId); //获取字段
string str1 = string.Empty; //验证字段是否包含列名
//列名
for (int i = 0; i < cellCount; i++)
{
string strlm = headerRow.GetCell(i).ToString().Trim();
if (yz.Count > 0)
{
#region 字段是否包含列名验证
var l = yz.Where(p => p.Name == strlm);//验证字段是否包含列名
if (l.Count() == 0)
{
if (string.IsNullOrEmpty(str1))
{
str1 = "文件中的【" + strlm + "】";
}
else
{
str1 = str1 + "、【" + strlm + "】";
}
strlm = strlm + "<span style='color:red;'>不会导入</span>";
}
#endregion
}
dt.Columns.Add(strlm);//添加列名
}
if (!string.IsNullOrEmpty(str1))
{
str1 = str1 + " 不属于当前导入的字段!<br>";
}
dt.Columns.Add("status", Type.GetType("System.String"));
string str2 = string.Empty;//验证必填字段是否存在
#region 必填字段在文件中存不存在验证
foreach (var v in yz.Where(p => p.IsNull == 1))
{
if (!dt.Columns.Contains(v.Name))
{
if (string.IsNullOrEmpty(str2))
{
str2 = "当前导入的必填字段：【" + v.Name + "】";
}
else
{
str2 = str2 + "、【" + v.Name + "】";
}
}
}
if (!string.IsNullOrEmpty(str2))
{
str2 = str2 + " 在文件中不存在!<br>";
}
#endregion
string str3 = string.Empty; //验证必填字段是否有值
string str4 = string.Empty; //验证字段是否重复
string str5 = string.Empty; //验证字段是否存在
for (int i = (sheet.FirstRowNum + int.Parse(headrow) + 1); i <= sheet.LastRowNum; i++)
{
string str31 = string.Empty;
string str41 = string.Empty;
string str42 = string.Empty;
string str51 = string.Empty;
DataRow dr = dt.NewRow();
bool bl = false;
IRow row = sheet.GetRow(i);
dr["status"] = "0";
for (int j = row.FirstCellNum; j < cellCount; j++)
{
string strsj = row.GetCell(j) != null?row.GetCell(j).ToString().Trim() : "";
if (strsj != "")
{
bl = true;
}
foreach (var v in yz.Where(p => p.Name == headerRow.GetCell(j).ToString().Trim()))
{
if (strsj == "")
{
#region 必填字段验证
if (v.IsNull == 1)
{
//strsj = "<span style='color:red;'>必填</span>";
if (string.IsNullOrEmpty(str31))
{
str31 = "第" + (i + 1) + "行的必填字段：【" + v.Name + "】";
}
else
{
str31 = str31 + "、【" + v.Name + "】";
}
dr["status"] = "2";
}
#endregion
}
else
{
#region 长度验证
if (v.Length != 0)
{
if (Encoding.Default.GetBytes(strsj).Length > v.Length)
{
strsj = strsj + "<span style='color:red;'>长度不能超过" + v.Length + "</span>";
dr["status"] = "2";
}
}
#endregion
#region 重复验证
if (!string.IsNullOrEmpty(v.IsRepeat))
{
#region 与现有数据比较是否重复
string[] strRS = v.IsRepeat.Split('|');
var cf = new JH_Auth_UserB().GetDTByCommand("select * from " + strRS[0] + " where " + strRS[1] + "= '" + strsj + "' and ComId='" + UserInfo.QYinfo.ComId + "'");
if (cf.Rows.Count > 0)
{
if (string.IsNullOrEmpty(str41))
{
str41 = "第" + (i + 1) + "行的字段：【" + v.Name + "】" + strsj;
}
else
{
str41 = str41 + "、【" + v.Name + "】:" + strsj;
}
dr["status"] = "2";
}
#endregion
#region 与Excel中数据比较是否重复
DataRow[] drs = dt.Select(headerRow.GetCell(j).ToString().Trim() + "='" + strsj + "'");
if (drs.Length > 0)
{
if (string.IsNullOrEmpty(str42))
{
str42 = "第" + (i + 1) + "行的字段：【" + v.Name + "】" + strsj;
}
else
{
str42 = str42 + "、【" + v.Name + "】" + strsj;
}
dr["status"] = "2";
}
#endregion
}
#endregion
#region 存在验证
if (!string.IsNullOrEmpty(v.IsExist))
{
string[] strES = v.IsExist.Split('|');
var cz = new JH_Auth_UserB().GetDTByCommand("select * from " + strES[0] + " where " + strES[1] + "= '" + strsj + "' and ComId='" + UserInfo.QYinfo.ComId + "'");
if (cz.Rows.Count == 0)
{
if (string.IsNullOrEmpty(str51))
{
str51 = "第" + (i + 1) + "行的字段：【" + v.Name + "】" + strsj;
}
else
{
str51 = str51 + "、【" + v.Name + "】" + strsj;
}
dr["status"] = "2";
}
}
#endregion
}
}
dr[j] = strsj;
}
if (!string.IsNullOrEmpty(str31))
{
str31 = str31 + " 不能为空！<br>";
str3 = str3 + str31;
}
if (!string.IsNullOrEmpty(str41))
{
str41 = str41 + " 已经存在！<br>";
str4 = str4 + str41;
}
if (!string.IsNullOrEmpty(str42))
{
str42 = str42 + " 在文件中已经存在！<br>";
str4 = str4 + str42;
}
if (!string.IsNullOrEmpty(str51))
{
str51 = str51 + " 不存在！<br>";
str5 = str5 + str51;
}
if (bl)
{
dt.Rows.Add(dr);
}
}
if (string.IsNullOrEmpty(str2) && string.IsNullOrEmpty(str3) && string.IsNullOrEmpty(str4) && string.IsNullOrEmpty(str5))
{
msg.Result = dt;
}
msg.Result1 = str1 + str2 + str3 + str4 + str5;
}
sheet = null;
workbook = null;
}
else
{
msg.ErrorMsg = "请上传excel文件 ";
}
}
}
catch (Exception ex)
{
//msg.ErrorMsg = ex.ToString();
msg.ErrorMsg = "导入失败！";
}
}
/// <summary>
/// 导出模板excel
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void EXPORTTOEXCEL(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
try
{
List <JH_Auth_ExtendDataB.IMPORTYZ> yz = new List <JH_Auth_ExtendDataB.IMPORTYZ>();
yz = new JH_Auth_ExtendDataB().GetTable(P1, UserInfo.QYinfo.ComId);//获取字段
if (yz.Count > 0)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = BorderStyle.Thin;
HeadercellStyle.BorderLeft = BorderStyle.Thin;
HeadercellStyle.BorderRight = BorderStyle.Thin;
HeadercellStyle.BorderTop = BorderStyle.Thin;
HeadercellStyle.Alignment = HorizontalAlignment.Center;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
HeadercellStyle.FillPattern = FillPattern.SolidForeground;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (var l in yz)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(l.Name);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
string strDataJson = new JH_Auth_ExtendDataB().GetExcelData(P1);
if (strDataJson != "")
{
string[] strs = strDataJson.Split(',');
//建立内容行
int iCellIndex = 0;
IRow DataRow = sheet.CreateRow(1);
for (int i = 0; i < strs.Length; i++)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(strs[i]);
cell.CellStyle = cellStyle;
iCellIndex++;
}
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
object curContext = object.Current;
string strName = string.Empty;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("CRM_" + strName + "_模板文件.xls", Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
workbook = null;
ms.Close();
ms.Dispose();
}
}
}
catch
{
msg.ErrorMsg = "导入失败！";
}
}
public ActionResult <string> EXPORTWORD()
{
var context = _accessor.HttpContext;
var tokenHeader = context.Request.Cookies["szhlcode"].ToString().Replace("Bearer ", "");
TokenModelJWT tokenModel = JwtHelper.SerializeJWT(tokenHeader);
JH_Auth_UserB.UserInfo UserInfo = new JH_Auth_UserB().GetUserInfo(10334, tokenModel.UserName);
string P1 = context.Request.Query["P1"].ToString();
string P2 = context.Request.Query["P2"].ToString();
int pdid = 0;
int.TryParse(P1, out pdid);
int piid = 0;
int.TryParse(P2, out piid);
Yan_WF_PD PD = new Yan_WF_PDB().GetEntity(d => d.ID == pdid && d.ComId == UserInfo.User.ComId);
Yan_WF_PI PI = new Yan_WF_PIB().GetEntity(d => d.ID == piid && d.ComId == UserInfo.User.ComId);
if (PD.ExportFile == null)
{
return("");
}
int fileID = int.Parse(PD.ExportFile);
FT_File MBFile = new FT_FileB().GetEntities(d => d.ID == fileID).FirstOrDefault();
Dictionary <string, string> dictSource = new Dictionary <string, string>();
List <JH_Auth_ExtendMode> ExtendModes = new List <JH_Auth_ExtendMode>();
ExtendModes = new JH_Auth_ExtendModeB().GetEntities(D => D.ComId == UserInfo.User.ComId && D.PDID == pdid).ToList();
foreach (JH_Auth_ExtendMode item in ExtendModes)
{
string strValue = new JH_Auth_ExtendDataB().GetFiledValue(item.TableFiledColumn, pdid, piid);
dictSource.Add("qj_" + item.TableFiledColumn, strValue);
}
dictSource.Add("qj_CRUser", PI.CRUserName);
dictSource.Add("qj_BranchName", PI.BranchName);
dictSource.Add("qj_CRDate", PI.CRDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));
dictSource.Add("qj_PINUM", PI.ID.ToString());
List <Yan_WF_TI> tiModels = new Yan_WF_TIB().GetEntities(d => d.PIID == piid).ToList();
for (int i = 0; i < tiModels.Count; i++)
{
dictSource.Add("qj_Task" + i + ".TaskUser", new JH_Auth_UserB().GetUserRealName(UserInfo.User.ComId.Value, tiModels[i].TaskUserID));
dictSource.Add("qj_Task" + i + ".TaskUserView", tiModels[i].TaskUserView);
if (tiModels[i].EndTime != null)
{
dictSource.Add("qj_Task" + i + ".EndTime", tiModels[i].EndTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
string strFileUrl = UserInfo.QYinfo.FileServerUrl + "/" + UserInfo.QYinfo.QYCode + "/document/" + MBFile.zyid;
Aspose.Words.Document doc = new Aspose.Words.Document(strFileUrl);
//使用文本方式替换
foreach (string name in dictSource.Keys)
{
doc.Range.Replace(name, dictSource[name]);
}
#region 使用书签替换模式
#endregion
string Filepath = hostingEnv.WebRootPath + "/Export/";
string strFileName = PD.ProcessName + DateTime.Now.ToString("yyMMddHHss") + ".doc";
doc.Save(Filepath + strFileName, Aspose.Words.Saving.DocSaveOptions.CreateSaveOptions(SaveFormat.Doc));
return(File("~/excels/report.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "report.xlsx"));
}
