public ActionResult <string> EXPORTYG()
{
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);
JObject JsonData = new JObject();
string P1 = context.Request.Query["P1"].ToString();
string P2 = context.Request.Query["P2"].ToString();
string pagecount = context.Request.Query["pagecount"].ToString();
JsonData.Add("P1", P1);
JsonData.Add("P2", P2);
JsonData.Add("pagecount", pagecount);
new AuthManage().GETUSERBYCODENEWPAGE(JsonData, Model, P1, P2, UserInfo);
DataTable dt = Model.Result;
string sqlCol = "ID,UserOrder|序号,DeptName|部门,RoomCode|房间号,UserName|账号,UserRealName|姓名,Sex|性别,mobphone|手机,QQ|QQ,weixinCard|微信,mailbox|邮箱,telphone|座机,ROLENAME|职务,Usersign|职责,UserGW|岗位,IDCard|身份证,HomeAddress|家庭住址";
DataTable dtResult = dt.DelTableCol(sqlCol);
HSSFWorkbook workbook = new HSSFWorkbook();
workbook = CommonHelp.ExportToExcel(dtResult);
var stream = new NPOIMemoryStream();
workbook.Write(stream);
stream.Flush();
stream.Position = 0;
return(File(stream, "application/ms-excel", string.Format("{0}.xls", "员工_导出文件_" + DateTime.Now.Ticks)));
}
public static void ToExcelWeb(string fileName, XSSFWorkbook workbook)
{
NPOIMemoryStream ms = new NPOIMemoryStream();
try
{
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
HttpContext curContext = HttpContext.Current;
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
long fileSize = ms.Length;
curContext.Response.AddHeader("Content-Length", fileSize.ToString());
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
}
catch (Exception ex)
{
return;
}
finally
{
if (ms != null)
{
ms.Dispose();
}
}
}
/// <summary>
/// 获取业务员所有运单导出数据
/// </summary>
/// <param name="manager"></param>
/// <returns></returns>
public ResultBase <MemoryStream> ExportSalemanAllWaybills(Dictionary <string, string> conditions)
{
List <WaybillExportWithPriceModel> data = salemanManager.GetSalemanAllWaybills(conditions).GetAwaiter().GetResult();
MemoryStream stream = new NPOIMemoryStream(false);
ExcelHelper.WriteExcel(stream, "业务员运单", data);
return(ResultBase <MemoryStream> .Sucess(stream));
}
/// <summary>
/// 获取业务员所有店铺导出数据
/// </summary>
/// <param name="manager"></param>
/// <returns></returns>
public ResultBase <MemoryStream> ExportSalemanAllShops(int salemanId, out string salemanName)
{
salemanName = "";
List <ExportShopsModel> data = salemanManager.GetSalemanAllShops(salemanId).GetAwaiter().GetResult();
if (data != null && data.Count > 0)
{
salemanName = data.First().SalemanName;
}
MemoryStream stream = new NPOIMemoryStream(false);
ExcelHelper.WriteExcel(stream, "业务员店铺", data);
return(ResultBase <MemoryStream> .Sucess(stream));
}
public ActionResult <string> EXPORTBDTJDATA()
{
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);
JObject JsonData = new JObject();
string P1 = context.Request.Query["P1"].ToString();
string P2 = context.Request.Query["P2"].ToString();
string sdate = context.Request.Query["sdate"].ToString();
string edate = context.Request.Query["edate"].ToString();
JsonData.Add("P1", P1);
JsonData.Add("P2", P2);
JsonData.Add("sdate", sdate);
JsonData.Add("edate", edate);
new FORMBIManage().GETBDTJDATA(JsonData, Model, P1, P2, UserInfo);
DataTable dt = Model.Result;
string sqlCol = "CRUserName|发起人,CRDate|发起时间,BranchName|所在部门,";
DataTable dtResult = dt.DelTableCol(sqlCol);
HSSFWorkbook workbook = new HSSFWorkbook();
workbook = CommonHelp.ExportToExcel(dtResult);
var stream = new NPOIMemoryStream();
workbook.Write(stream);
stream.Flush();
stream.Position = 0;
return(File(stream, "application/ms-excel", string.Format("{0}.xls", "导出文件_" + DateTime.Now.Ticks)));
}
public static Stream DataTableToExcel(string fileName, DataTable data,
List <ExcelColumnFormat> colFmt,
string cols,
Func <object, string, object> tranfunc = null,
bool isColumnWritten = true, string sheetName = "Sheet1", string tableTitle = null)
{
if (data.Rows.Count == 0)
{
return(null);
} //data为空，直接退出
int i = 0;
int j = 0;
int count = 0;
IWorkbook workbook = null;
ISheet sheet = null;
NPOIMemoryStream ms = null;
try
{
//转换对齐
Func <System.Drawing.ContentAlignment?, HorizontalAlignment> _transHAlignment = alignment =>
{
switch (alignment)
{
case System.Drawing.ContentAlignment.BottomRight:
case System.Drawing.ContentAlignment.MiddleRight:
case System.Drawing.ContentAlignment.TopRight:
return(HorizontalAlignment.Right);
case System.Drawing.ContentAlignment.BottomCenter:
case System.Drawing.ContentAlignment.MiddleCenter:
case System.Drawing.ContentAlignment.TopCenter:
return(HorizontalAlignment.Center);
}
return(HorizontalAlignment.Left);
};
//设置值的格式
Action <IRow, int, object, string> _val = (row, columnIndex, v, colCode) =>
{
string rlt = "";
string vstr = Ass.P.PStr(v);
string tv = null;
if (tranfunc != null)
{
tv = Ass.P.PStr(tranfunc(v, colCode));
}
ExcelColumnFormat fmt = null;
if (tv != null && tv != vstr)
{
rlt = tv;
}
else if (colFmt.Any(m => m.ColumnNameCode == colCode))
{
rlt = vstr;
string v0 = "";
fmt = colFmt.Find(m => m.ColumnNameCode == colCode);
if (v != null && fmt.ToStringFormat.IsNotEmpty())
{
v0 = string.Format("{0:" + fmt.ToStringFormat + "}", v);
if (v0 != vstr)
{
rlt = v0;
}
}
}
else
{
rlt = vstr;
}
//设置值
var cell = row.CreateCell(j);
var t = rlt.GetStringType();
double dbval = 0; DateTime dtval;
if (double.TryParse(rlt, out dbval))
{
cell.SetCellValue(dbval);
}
else if (DateTime.TryParse(rlt, out dtval))
{
cell.SetCellValue(rlt);
}
else
{
cell.SetCellValue(rlt);
}
};
using (ms = new NPOIMemoryStream())
{
if (!GetWorkbook(fileName, out workbook))
{
return(null);
}
List <string> colList = cols.IsEmpty()?new List <string>(): (cols + "").Split(',').ToList();//获取列名集合
sheet = workbook.CreateSheet(sheetName);
//表标题
if (tableTitle.IsNotEmpty())
{
IRow row = sheet.CreateRow(count++);
ICell cell = row.CreateCell(0);
XSSFCellStyle ztStyle = (XSSFCellStyle)workbook.CreateCellStyle();
IFont ztFont = workbook.CreateFont();
ztFont.FontHeightInPoints = 14;
ztFont.Underline = FontUnderlineType.DoubleAccounting;
ztStyle.SetFont(ztFont);
ztStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle = ztStyle;
cell.SetCellValue(tableTitle);
var colnum = colList.Count > 0 ? colList.Count : data.Columns.Count; //列数
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, colnum - 1)); //合并单元格
}
Dictionary <int, ICellStyle> _columeCellStyles = new Dictionary <int, ICellStyle>();
//写入DataTable的列名
if (isColumnWritten == true)
{
IRow row = sheet.CreateRow(count++);
if (colList.Count > 0) //设定了输出的列
{
for (j = 0; j < colList.Count(); j++)
{
var key = colList[j];
var keyname = colFmt.Any(m => m.ColumnNameCode == key) ? colFmt.Find(m => m.ColumnNameCode == key).ColumnName : key;
var cell = row.CreateCell(j);
var cellfmt = colFmt.Find(m => m.ColumnNameCode == key);
cell.SetCellValue(keyname);
//设置对齐与数据格式
var cStyle = workbook.CreateCellStyle(); bool bfmt = false;
if (cellfmt.Alignment != null)
{
bfmt = true;
cStyle.Alignment = _transHAlignment(cellfmt.Alignment);
}
if (cellfmt.DataFormat.IsNotEmpty())
{
bfmt = true;
short?dfmt = null; IDataFormat format = workbook.CreateDataFormat();
switch (cellfmt.DataFormat)
{
case "Price": dfmt = format.GetFormat("#,##0.00"); break;
case "Date": dfmt = HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd"); break;
case "DateTime": dfmt = 0x16; break; // HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd hh:mm:ss"); break;
case "Id": dfmt = HSSFDataFormat.GetBuiltinFormat("@"); break;
}
cStyle.DataFormat = dfmt.Value;
}
if (bfmt)
{
_columeCellStyles.Add(j, cStyle);
}
//设置列宽
if (cellfmt.ColumnWidth.HasValue)
{
sheet.SetColumnWidth(j, (int)((cellfmt.ColumnWidth.Value + 0.72) * 256));
}
}
}
else
{
for (j = 0; j < data.Columns.Count; j++)
{
string key = data.Columns[j].ColumnName;
if (colFmt.Any(m => m.ColumnNameCode == key))//改列名为中文
{
var cell = row.CreateCell(j);
var cellfmt = colFmt.Find(m => m.ColumnNameCode == key);
cell.SetCellValue(cellfmt.ColumnName);
//设置对齐
if (cellfmt.Alignment != null)
{
sheet.GetColumnStyle(j).Alignment = _transHAlignment(cellfmt.Alignment);
}
//设置列宽
if (cellfmt.ColumnWidth.HasValue)
{
sheet.SetColumnWidth(j, cellfmt.ColumnWidth.Value);
}
}
else
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
}
}
}
//写入数据
if (colList.Count > 0)
{
for (i = 0; i < data.Rows.Count; i++, count++)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < colList.Count; j++)
{
_val(row, j, data.Rows[i][colList[j]], colList[j]);
}
}
}
else
{
for (i = 0; i < data.Rows.Count; i++, count++)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; j++)
{
_val(row, j, data.Rows[i][j], data.Columns[j].ColumnName);
}
}
}
//设置列格式
foreach (int colIndex in _columeCellStyles.Keys)
{
sheet.SetDefaultColumnStyle(colIndex, _columeCellStyles[colIndex]);
}
workbook.Write(ms); //写入到excel
ms.Flush();
ms.Position = 0;
return(ms);
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Exception: " + ex.Message);
return(null);
}
}
public async Task <IActionResult> GenerateBlankTemplateAsync()
{
var TheStream = new NPOIMemoryStream {
AllowClose = false
};
var IntegerFields = new List <string> {
"*** Surveyor Number", "DeedVolume", "DeedPage", "AutomatedFileNumber"
};
var FileName = $"PLSO_Upload_Template {DateTime.Now.ToString("yy-MMdd")}-{DateTime.Now.Ticks}.xlsx";
try {
var Columns = await excelTemplateRepo.GetTemplateColumnsAsync();
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PLSO Record Import");
IsBold = CreateBoldStyle(workbook);
IsBlue = CreateBlueStyle(workbook);
IsValidation = CreateValidationStyle(workbook);
IsInteger = CreateIntegerStyle(workbook);
int RowIndex = 0;
IRow row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
sheet1.SetColumnWidth(col.ColumnIndex - 1, (col.ColumnWidth * 256));
if (col.IsRequired)
{
Cell.CellStyle = IsBold;
}
Cell.SetCellType(CellType.String);
Cell.SetCellValue(col.DisplayName);
} // foreach of the columns on the Display Name row
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
Cell.SetCellType(CellType.String);
Cell.SetCellValue(col.ExampleData);
Cell.CellStyle = IsBlue;
} // foreach of the columns on the Example Data row
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
Cell.SetCellType(CellType.String);
Cell.SetCellValue((col.IsRequired ? "REQUIRED: " : "") + col.Validation);
Cell.CellStyle = IsValidation;
Cell.CellStyle.WrapText = true;
} // foreach of the columns on the Validation row
for (var index = 1; index < 6; index++)
{
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
if (col.IsRequired)
{
Cell.CellStyle = IsBold;
}
if (col.FieldName == "SurveyDate")
{
IDataFormat dataFormat = workbook.CreateDataFormat();
Cell.CellStyle.DataFormat = dataFormat.GetFormat("M/d/yyyy");
}
else if (IntegerFields.Contains(col.FieldName))
{
Cell.SetCellType(CellType.Numeric);
Cell.CellStyle = IsInteger;
}
else
{
Cell.SetCellType(CellType.String);
}
} // foreach of the columns on the Display Name row
} // for 5 rows add formatting and make the cell bold if required
XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
workbook.Write(TheStream);
} catch (Exception e) {
TheStream.Dispose();
logger.LogError(1, e, "Unable to export Blank Template to Excel");
}
TheStream.Seek(0, SeekOrigin.Begin);
TheStream.AllowClose = true;
return(File(TheStream, "application/vnd.ms-excel", FileName));
}
public FileStreamResult ProcessExcelFile(Stream stream)
{
var size = stream.Length;
var Errors = new List <ParsingError>();
var NewRecords = new List <Record>();
var TotalErrors = 0;
var LatLongTypeID = 2; // enumProps.GetDBID(LocationTypes.LatLong);
IWorkbook wb = WorkbookFactory.Create(stream);
try {
IsError = CreateErrorStyle(wb);
IsComment = CreateCommentStyle(wb);
ISheet sheet = wb.GetSheetAt(0);
// Loop through each Row
// Loop through each Column in the current row
var UseDate = DateTime.UtcNow;
// Row is zero based, skip row 0 (title row), 1 = Example, 2 = Validation
for (var r = 3; r <= sheet.LastRowNum; r++)
{
IRow row = sheet.GetRow(r);
var staged = new Record {
UploadedByID = UsersID,
UploadedDate = UseDate,
Active = false,
};
var RowErrors = new List <ParsingError>();
for (var c = 2; c <= 24; c++) // c = 2 (Column C to start), 24 (Column Y to end)
// # - Req - Title - Validation
// -----------------------------------------------
// A - No - Parsing Errors - None
// B - No - Comments - None
// C - Yes - Map Image Name - 5 digits only
// D - Yes - City, Village - None (max 50)
// E - Yes - County - Minimum length = 5 (max 50)
// F - Yes - Defunct Twp - None (max 50)
// G - No - Lot Number - Numbers only, seperated by comma's, no spaces
// H - No - Section - "
// I - No - Tract - None
// J - No - Range - None
// K - Yes - Survey Date - MM/DD/YYYY
// L - Yes - Surveyor Name - None
// M - No - Surveyor No. - Digits only
// N - Yes - Address - Full address
// O - No - Cross Street - None
// P - No - Parcel Numbers - ###-##-###,###-##-###...
// Q - No - Volume - Digits only
// R - No - Page - "
// S - No - AFN... - None
// T - No - Subdivision - None
// U - No - Subd. Lot - Digits only, seperated by commas
// V - No - Survey Name - None
// W - No - Location - Latitude on Longitude seperated by commas
// X - No - Client - None
// Y - No - Notes - None
{
var pCell = new ParsingCell(row.GetCell(c), r, c);
if (c == 2)
{
// Column C: Map Image Name
var Check = RequiredString(pCell, 15, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.MapImageName = Check.Result;
}
}
else if (c == 3)
{
// Column D: City, Village, Township
var Check = RequiredString(pCell, 25, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.CityVillageTownship = Check.Result;
}
}
else if (c == 4)
{
// Column E: County
var Check = RequiredString(pCell, 15, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.County = Check.Result;
}
}
else if (c == 5)
{
// Column F: Defunct Township
var Check = RequiredString(pCell, 20, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.DefunctTownship = Check.Result.Replace("township", "");
}
}
else if (c == 6)
{
// Column G: Lot Numbers
var Check = DelimetedDigitsOnly(pCell, ',', 15, 0, true);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.LotNumbers = Check.Result;
}
}
else if (c == 7)
{
// Column H: Section
var Check = DelimetedDigitsOnly(pCell, ',', 15, 0, true);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Section = Check.Result;
}
}
else if (c == 8)
{
// Column I: Tract
var Check = NonRequiredString(pCell, 15);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Tract = Check.Result;
}
}
else if (c == 9)
{
// Column J: Range
var Check = NonRequiredString(pCell, 15);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Range = Check.Result;
}
}
else if (c == 10)
{
// Column K: Survey Date
var Check = RequiredDate(pCell);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.SurveyDate = DateTime.Parse(Check.Result);
}
}
else if (c == 11)
{
// Column L: Surveyor Name
var Check = RequiredString(pCell, 25, 5);
// TODO: This Name needs to be transmuted to an actual ApplicationUser ID
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.SurveyorName = Check.Result;
// TODO: Lookup the name in the security.AspNetUser table
// If found, add the ID to the staged.SurveyorID field
}
}
else if (c == 12)
{
// Column M: Surveyor Number
var Check = NonRequiredDigitsOnly(pCell, 5, 2);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.SurveyorNumber = Check.Result;
}
}
else if (c == 13)
{
// Column N: Address
var Check = RequiredString(pCell, 100, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Address = Check.Result;
}
}
else if (c == 14)
{
// Column O: Cross Street Name
var Check = NonRequiredString(pCell, 30);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.CrossStreet = Check.Result;
}
}
else if (c == 15)
{
// Column P: Parcel Numbers
var Check = DelimetedDigitsOnly(pCell, new List <char> {
',', '-'
}, 30);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.ParcelNumbers = Check.Result;
}
}
else if (c == 16)
{
// Column Q: Volume
var Check = NonRequiredDigitsOnly(pCell, 8, 1);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
if (int.TryParse(Check.Result, out int value))
{
staged.DeedVolume = value; // TODO: This needs to be a TryParse so we can handle overflows to long
}
}
// TODO: Idealy, the method (in this case) NonRequiredDigitsOnly will return a Null or push an Error
}
else if (c == 17)
{
// Column R: Page
var Check = NonRequiredDigitsOnly(pCell, 6, 1);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else if (int.TryParse(Check.Result, out int value))
{
staged.DeedPage = value; // TODO: This needs to be a TryParse so we can handle overflows to long
}
}
else if (c == 18)
{
// Column S: AFN
var Check = NonRequiredString(pCell, 18, 3);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.AutomatedFileNumber = Check.Result;
}
}
else if (c == 19)
{
// Column T: Subdivision
var Check = NonRequiredString(pCell, 50, 5);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Subdivision = Check.Result;
}
}
else if (c == 20)
{
// Column U: Subdivision-Sublot
var Check = DelimetedDigitsOnly(pCell, ',', 10, 0, true);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Sublot = Check.Result;
}
}
else if (c == 21)
{
// Column V: Survey Name
var Check = NonRequiredString(pCell, 50);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.SurveyName = Check.Result;
}
}
else if (c == 22)
{
// Column W: Location
var Check = DelimetedDigitsOnly(pCell, new List <char> {
',', '.', '-'
}, 30, truncateSpaces: true);
int Parts = string.IsNullOrWhiteSpace(Check.Result) ? 0 : Check.Result.Split(',').Count();
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else if ((Parts == 1) || (Parts > 2))
{
RowErrors.Add(new ParsingError(pCell, "Invalid Format for Location"));
}
else if (!string.IsNullOrWhiteSpace(Check.Result))
{
var Loc = new Location {
LocationTypeID = LatLongTypeID,
Latitude = Convert.ToDecimal(Check.Result.Split(',')[0]),
Longitude = Convert.ToDecimal(Check.Result.Split(',')[1])
};
staged.Location = Loc;
}
}
else if (c == 23)
{
// Column X: Client
var Check = NonRequiredString(pCell, 50);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.ClientName = Check.Result;
}
}
else if (c == 24)
{
// Column Y: Notes
var Check = NonRequiredString(pCell, 2000);
if (Check.InError)
{
RowErrors.AddRange(Check.Errors);
}
else
{
staged.Notes = Check.Result; // notes
}
}
} // foreach of the Columns
var CommentCell = row.GetCell(0) ?? row.CreateCell(0);
if (RowErrors.Count > 0)
{
CommentCell.SetCellValue(new XSSFRichTextString($"{RowErrors.Count} Errors:\n{string.Join("\n", RowErrors.Select(x => $"[{ColumnIndexToLetter(x.Cell.Column + 1)}] {x.Message}"))}"));
CommentCell.CellStyle = IsComment;
foreach (var error in RowErrors)
{
var cell = error.Cell.Cell == null?row.CreateCell(error.Cell.Column) : row.GetCell(error.Cell.Column);
cell.CellStyle = IsError;
}
}
else
{
NewRecords.Add(staged); // Only add the record if the row is valid
CommentCell.SetCellValue("Validation: OK");
}
Errors.AddRange(RowErrors);
if (RowErrors.Count > 0)
{
TotalErrors += RowErrors.Count;
}
else
{
// The row has validated successfully, check to see if it exists
var HasChodeMatches = DataContext.Records
.Where(x => x.HashCode == staged.GetHashCode())
.Select(x => x.ID)
.ToList();
if (HasChodeMatches.Count > 0)
{
// Hash code found, should be already in the database
// Add to validation column
TotalErrors++;
var MatchIDs = "";
foreach (var match in HasChodeMatches)
{
MatchIDs += $"{match},";
}
CommentCell.SetCellValue($"Validation: OK - Found {HasChodeMatches.Count} Potential hashcode matches - IDs: {MatchIDs}");
}
else
{
var Matches = DataContext.Records
.Where(x =>
x.MapImageName == staged.MapImageName &&
x.CityVillageTownship == staged.CityVillageTownship &&
x.State == staged.State &&
x.County == staged.County &&
x.DefunctTownship == staged.DefunctTownship &&
x.SurveyDate == staged.SurveyDate &&
x.Address == staged.Address
)
.Select(x => x.ID)
.ToList();
if (Matches.Count > 0)
{
TotalErrors++;
var MatchIDs = "";
foreach (var match in Matches)
{
MatchIDs += $"{match},";
}
CommentCell.SetCellValue($"Validation: OK - Found {Matches.Count} Potential field matches - IDs: {MatchIDs}");
}
}
}
} // foreach of the Rows
} catch (Exception e) {
Console.WriteLine($"Exceiption: {e.Message} - {e.StackTrace}");
}
if (TotalErrors > 0)
{
Console.WriteLine($"There were a total of {TotalErrors} errors.");
}
else
{
int WriteErrors = 0;
var bob = WriteErrors;
Console.WriteLine($"Saving {NewRecords.Count} Records to the database.");
ISheet sheet = wb.GetSheetAt(0);
for (int index = 0; index < NewRecords.Count; index++)
{
IRow row = sheet.GetRow(3 + index);
var r = NewRecords[index];
r.HashCode = r.GetHashCode();
try {
DataContext.Records.Add(r);
DataContext.SaveChanges(1);
row.GetCell(0).SetCellValue($"{row.GetCell(0).StringCellValue}, Save: OK - {r.ID}");
} catch (Exception e) {
row.GetCell(0).SetCellValue($"{row.GetCell(0).StringCellValue}, Save: Failed - {(e.InnerException != null ? e.InnerException.Message : e.Message)}");
WriteErrors++;
}
} // foreach of the Records
if (WriteErrors > 0)
{
Console.WriteLine($"There were a total of {WriteErrors} Write errors.");
}
}
var TheStream = new NPOIMemoryStream {
AllowClose = false
};
try {
wb.Write(TheStream);
} catch (Exception e) {
TheStream.Dispose();
logger.LogError(1, e, "Unable to export Blank Template to Excel");
}
TheStream.Seek(0, SeekOrigin.Begin);
TheStream.AllowClose = true;
return(File(TheStream, "application/vnd.ms-excel", $"{DateTime.Now.ToString("yy-MMdd")}-Testing.xlsx"));
}
public FileResult exportMapping(string s, string d)
{
//If a source or destination is missing return an empty text file
if (s == "-1" || d == "-1")
{
var ms = new MemoryStream();
TextWriter tw = new StreamWriter(ms);
tw.Write("Invalid Source or Destination selection");
tw.Flush();
ms.Position = 0;
var mt = "text/plain";
return(File(ms, mt, "invalid.txt"));
}
IWorkbook workbook = new XSSFWorkbook();
var sheetMapped = workbook.CreateSheet("Mapped");
var sheetUnMapped = workbook.CreateSheet("Unmapped");
JObject ouMap = GetOUMapping(s, d);
JArray mapped = (JArray)ouMap.GetValue("mapped");
JArray unmapped = (JArray)ouMap.GetValue("unmapped");
JObject serverSource = DataFactory.GetServer(s, directory);
JObject serverDestination = DataFactory.GetServer(d, directory);
string soureName = serverSource.GetValue("name").ToString();
string destinationName = serverDestination.GetValue("name").ToString();
int rowIndex = 0;
sheetMapped.CreateRow(rowIndex);
sheetMapped.GetRow(rowIndex).CreateCell(0).SetCellValue("Source ID");
sheetMapped.GetRow(rowIndex).CreateCell(1).SetCellValue("Source Name");
sheetMapped.GetRow(rowIndex).CreateCell(2).SetCellValue("Destination ID");
sheetMapped.GetRow(rowIndex).CreateCell(3).SetCellValue("Destination Name");
rowIndex++;
foreach (JObject m in mapped)
{
sheetMapped.CreateRow(rowIndex);
sheetMapped.GetRow(rowIndex).CreateCell(0).SetCellValue(m.GetValue("sourceID").ToString());
sheetMapped.GetRow(rowIndex).CreateCell(1).SetCellValue(m.GetValue("srcName").ToString());
sheetMapped.GetRow(rowIndex).CreateCell(2).SetCellValue(m.GetValue("destID").ToString());
sheetMapped.GetRow(rowIndex).CreateCell(3).SetCellValue(m.GetValue("destName").ToString());
rowIndex++;
}
//Reset the row count for the next sheet
rowIndex = 0;
sheetUnMapped.CreateRow(rowIndex);
sheetUnMapped.GetRow(rowIndex).CreateCell(0).SetCellValue("Source ID");
sheetUnMapped.GetRow(rowIndex).CreateCell(1).SetCellValue("Source Name");
sheetUnMapped.GetRow(rowIndex).CreateCell(2).SetCellValue("Destination ID");
sheetUnMapped.GetRow(rowIndex).CreateCell(3).SetCellValue("Destination Name");
rowIndex++;
foreach (JObject um in unmapped)
{
var type = um.GetValue("type").ToString();
if (type == "Source")
{
sheetUnMapped.CreateRow(rowIndex);
sheetUnMapped.GetRow(rowIndex).CreateCell(0).SetCellValue(um.GetValue("id").ToString());
sheetUnMapped.GetRow(rowIndex).CreateCell(1).SetCellValue(um.GetValue("displayName").ToString());
rowIndex++;
}
}
rowIndex = 1;
foreach (JObject um in unmapped)
{
var type = um.GetValue("type").ToString();
if (type == "Destination")
{
if (sheetUnMapped.GetRow(rowIndex) == null)
{
sheetUnMapped.CreateRow(rowIndex);
}
sheetUnMapped.GetRow(rowIndex).CreateCell(2).SetCellValue(um.GetValue("id").ToString());
sheetUnMapped.GetRow(rowIndex).CreateCell(3).SetCellValue(um.GetValue("displayName").ToString());
rowIndex++;
}
}
NPOIMemoryStream stream = new NPOIMemoryStream();
stream.AllowClose = false;
workbook.Write(stream);
stream.Flush();
stream.Seek(0, SeekOrigin.Begin);
stream.Position = 0;
stream.AllowClose = true;
var mimeType = "application/vnd.ms-excel";
return(File(stream, mimeType, $"OU Mapping {soureName}-{destinationName}-{DateTime.Now}.xlsx"));;
}
