/// <summary>
/// 下载Excel
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
public static string DownloadExcel(DataTable dataTable)
{
if (dataTable == null || dataTable.Rows.Count == 0)
{
throw new Exception("Data is Null !");
}
try
{
using (MemoryStream ms = new MemoryStream())
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "sheet1"
};
sheets.Append(sheet);
// Get the sheetData cell table.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
UInt32Value rowIndex = 1;
Row headerTitle = new Row()
{
RowIndex = rowIndex
};
List <string> columnNames = new List <string>();
foreach (DataColumn dtColumn in dataTable.Columns)
{
Cell cell = new Cell();
cell.DataType = new EnumValue <CellValues>(CellValues.String);
cell.CellValue = new CellValue(dtColumn.ColumnName);
headerTitle.Append(cell);
columnNames.Add(dtColumn.ColumnName);
}
sheetData.Append(headerTitle);
foreach (DataRow dtRow in dataTable.Rows)
{
rowIndex++;
// Add a row to the sheetData.
Row row = new Row()
{
RowIndex = rowIndex
};
foreach (string columnname in columnNames)
{
Cell cell = new Cell();
cell.DataType = new EnumValue <CellValues>(CellValues.String);
if (dataTable.Columns[columnname].Caption == Constants.DecryptColoumn)
{
cell.CellValue = new CellValue(WuYao.AesDecrypt(Cast.ConToString(dtRow[columnname])));
}
else
{
cell.CellValue = new CellValue(Cast.ConToString(dtRow[columnname]));
}
row.Append(cell);
}
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
}
return(Convert.ToBase64String(ms.GetBuffer()));
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 创建账号
/// </summary>
/// <param name="receive"></param>
/// <param name="verifycode"></param>
/// <returns></returns>
public string CreateUser(string receive, string verifycode)
{
try
{
string valid = ValidReceiveVerifyCode(receive, Constants.CodeTypeRegister, verifycode);
if (!string.IsNullOrEmpty(valid))
{
return(valid);
}
string account = string.Empty;
string password = Rand.Str(8);
DataTable dtEmail = _sql.Query("SELECT UserInfoId FROM UserInfo WHERE Email = @email", new Dictionary <string, object> {
{ "@email", receive }
});
if (dtEmail != null && dtEmail.Rows.Count > 0)
{
return("当前邮箱账号密码已发送，请检查邮箱！");
}
DataTable dtAccount = null;
do
{
account = Rand.Number(8);
dtAccount = _sql.Query("SELECT UserInfoId FROM UserInfo WHERE Account = @account", new Dictionary <string, object> {
{ "@account", account }
});
} while (dtAccount != null && dtAccount.Rows.Count > 0);
_sql.OpenDb();
UserInfo user = new UserInfo();
user.Account = account;
user.Password = WuYao.GetMd5(password + Constants.PasswordSalt);
user.Email = receive;
Guid userId = _sql.Create(user);
DataTable dtRole = _sql.Query("SELECT RoleInfoId FROM RoleInfo WHERE RoleCode = @code", new Dictionary <string, object> {
{ "@code", RoleKey.JCQX }
});
if (dtRole != null && dtRole.Rows.Count > 0)
{
UserInRole ur = new UserInRole();
ur.UserInfoId = userId;
ur.UserCode = account;
ur.RoleCode = RoleKey.JCQX;
ur.RoleInfoId = Guid.Parse(Cast.ConToString(dtRole.Rows[0]["RoleInfoId"]));
_sql.Create(ur);
}
if (receive.Contains("@"))
{
EmailHelper.SendEmailByQQ(receive, "淮安市三轮车开黑网站-注册账号", string.Format("账号：{0} \n 密码：{1}", account, password), Constants.CodeTypeRegister);
return("账号密码已发送至注册邮箱！");
}
else
{
return("");
}
}
catch (Exception ex)
{
_log.Error(ex.Message, ex);
throw ex;
}
finally
{
_sql.CloseDb();
}
}
private void LoadXml()
{
XmlDocument document = new XmlDocument();
document.Load(configfile);
XmlNodeList list = document.SelectNodes("//set");
if (list == null || list.Count <= 0)
{
return;
}
foreach (XmlNode node in list)
{
if (node.Attributes == null)
{
continue;
}
XmlAttribute nameAttribute = node.Attributes["name"];
XmlAttribute valueAttribute = node.Attributes["value"];
if (nameAttribute != null)
{
switch (nameAttribute.Value)
{
#region Common
case "RS_ConnectString":
RS_ConnectString = valueAttribute.InnerText;
break;
case "AES_Key":
AES_Key = valueAttribute.Value;
break;
case "AES_Iv":
AES_Iv = valueAttribute.Value;
break;
case "MSSQL":
MSSQL = valueAttribute.Value;
break;
case "MSSQL_Readonly":
MSSQL_Readonly = valueAttribute.Value;
break;
case "MYSQL":
MYSQL = valueAttribute.Value;
break;
case "SystemAccount":
SystemAccount = valueAttribute.Value;
break;
case "OriginServer":
OriginServer = valueAttribute.Value;
break;
case "Public_Key":
Public_Key = valueAttribute.Value;
break;
case "Private_Key":
Private_Key = valueAttribute.Value;
break;
case "Email_QQ_Host":
Email_QQ_Host = valueAttribute.Value;
break;
case "Email_QQ_Account":
Email_QQ_Account = valueAttribute.Value;
break;
case "Email_QQ_Password":
Email_QQ_Password = valueAttribute.Value;
break;
case "Job_Assembly":
Job_Assembly = valueAttribute.Value;
break;
case "Upload_Type":
Upload_Type = valueAttribute.Value;
break;
case "Upload_Url":
Upload_Url = valueAttribute.Value;
break;
case "Upload_MaxCount":
Upload_MaxCount = Cast.ConToInt(valueAttribute.Value);
break;
case "Upload_MaxLength":
Upload_MaxLength = Cast.ConToInt(valueAttribute.Value);
break;
#endregion
#region ALIPAY
case "ALIPAY_URL":
ALIPAY_URL = node.InnerText;
break;
case "ALIPAY_APPID":
ALIPAY_APPID = valueAttribute.Value;
break;
case "ALIPAY_APP_PRIVATE_KEY":
ALIPAY_APP_PRIVATE_KEY = valueAttribute.Value;
break;
case "ALIPAY_APP_PUBLIC_KEY":
ALIPAY_APP_PUBLIC_KEY = valueAttribute.Value;
break;
case "ALIPAY_SIGN_TYPE":
ALIPAY_SIGN_TYPE = valueAttribute.Value;
break;
case "ALIPAY_FORMAT":
ALIPAY_FORMAT = valueAttribute.Value;
break;
case "ALIPAY_CHARSET":
ALIPAY_CHARSET = valueAttribute.Value;
break;
#endregion
#region Security
case "Token_Key":
Token_Key = valueAttribute.Value;
break;
case "Password_Rule_IsOn":
Password_Rule_IsOn = Cast.ConToBoolean(valueAttribute.Value);
break;
case "Password_Rule_MinLength":
Password_Rule_MinLength = Cast.ConToInt(valueAttribute.Value);
break;
case "Password_Rule_MaxLength":
Password_Rule_MaxLength = Cast.ConToInt(valueAttribute.Value);
break;
case "Password_Rule_UpperAndLower":
Password_Rule_UpperAndLower = Cast.ConToBoolean(valueAttribute.Value);
break;
#endregion
}
}
}
}
