public void Format(IXLWorksheet worksheet, ScenarioOutline scenarioOutline, ref int row)
{
int originalRow = row;
worksheet.Cell(row++, "B").Value = scenarioOutline.Name;
worksheet.Cell(row++, "C").Value = scenarioOutline.Description;
var results = this.testResults.GetScenarioOutlineResult(scenarioOutline);
if (this.configuration.HasTestResults && (results != TestResult.Inconclusive))
{
worksheet.Cell(originalRow, "B").Style.Fill.SetBackgroundColor(results == TestResult.Passed
? XLColor.AppleGreen
: XLColor.CandyAppleRed);
}
foreach (Step step in scenarioOutline.Steps)
{
this.excelStepFormatter.Format(worksheet, step, ref row);
}
row++;
foreach (var example in scenarioOutline.Examples)
{
worksheet.Cell(row++, "B").Value = "Examples";
worksheet.Cell(row, "C").Value = example.Description;
this.excelTableFormatter.Format(worksheet, example.TableArgument, ref row);
}
}
public void Format(IXLWorksheet worksheet, Table table, ref int row)
{
int startRow = row;
int headerColumn = TableStartColumn;
foreach (string cell in table.HeaderRow)
{
worksheet.Cell(row, headerColumn).Style.Font.SetBold();
worksheet.Cell(row, headerColumn).Style.Font.SetItalic();
worksheet.Cell(row, headerColumn).Style.Fill.SetBackgroundColor(XLColor.AliceBlue);
worksheet.Cell(row, headerColumn++).Value = cell;
}
row++;
foreach (TableRow dataRow in table.DataRows)
{
int dataColumn = TableStartColumn;
foreach (string cell in dataRow)
{
worksheet.Cell(row, dataColumn++).Value = cell;
}
row++;
}
int lastRow = row - 1;
int lastColumn = headerColumn - 1;
worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.TopBorder =
XLBorderStyleValues.Thin;
worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.LeftBorder =
XLBorderStyleValues.Thin;
worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.BottomBorder =
XLBorderStyleValues.Thin;
worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.RightBorder =
XLBorderStyleValues.Thin;
}
private void MakeHeaders(IXLWorksheet sheet, TestSuite suite)
{
int column = 1;
for (; column <= deep; column++)
{
string header = string.Empty;
for (int i = 1; i < column; i++)
header += "Sub-";
sheet.Cell(row, column).Value = header + "Category";
sheet.Column(column).Width = 20.86;
}
sheet.Cell(row, deep + 1).Value = "Name";
sheet.Column(deep + 1).Width = 30;
sheet.Cell(row, deep + 2).Value = "Id";
sheet.Column(deep + 2).Width = 13;
sheet.Cell(row, deep + 3).Value = "Summary";
sheet.Column(deep + 3).Width = 42;
sheet.Cell(row, deep + 4).Value = "Preconditions";
sheet.Column(deep + 4).Width = 56.43;
sheet.Cell(row, deep + 5).Value = "Actions";
sheet.Column(deep + 5).Width = 100;
sheet.Cell(row, deep + 6).Value = "Expected Results";
sheet.Column(deep + 6).Width = 100;
row++;
}
private IEnumerable <Rekap> CellsColumnToList(IXLWorksheet worksheet, string sepCol, string trfCol, string gopCol)
{
var list = new List <Rekap>();
var e = 0;
var i = 1;
while (e <= 3)
{
i++;
var sep = worksheet?.Cell($"{sepCol}{i}")?.Value?.ToString();
if (!string.IsNullOrWhiteSpace(sep))
{
var rekap = new Rekap
{
Id = i,
Sep = sep,
Tarif = worksheet?.Cell($"{trfCol}{i}")?.Value?.ToString(),
Grouping = worksheet?.Cell($"{gopCol}{i}")?.Value?.ToString(),
};
list.Add(rekap);
}
else
{
e++;
}
}
return(list);
}
public void Format(IXLWorksheet worksheet, Feature feature)
{
worksheet.Cell("A1").Style.Font.SetBold();
worksheet.Cell("A1").Value = feature.Name;
worksheet.Cell("B2").Value = feature.Description;
worksheet.Cell("B2").Style.Alignment.WrapText = false;
var results = testResults.GetFeatureResult(feature);
if (configuration.HasTestResults && results.WasExecuted)
{
worksheet.Cell("A1").Style.Fill.SetBackgroundColor(results.WasSuccessful
? XLColor.AppleGreen
: XLColor.CandyAppleRed);
}
int row = 4;
foreach (IFeatureElement featureElement in feature.FeatureElements)
{
var scenario = featureElement as Scenario;
if (scenario != null)
{
excelScenarioFormatter.Format(worksheet, scenario, ref row);
}
var scenarioOutline = featureElement as ScenarioOutline;
if (scenarioOutline != null)
{
excelScenarioOutlineFormatter.Format(worksheet, scenarioOutline, ref row);
}
row++;
}
}
private IEnumerable <Misc> CellsColumnToList(IXLWorksheet worksheet, int columnId)
{
var list = new List <Misc>();
var e = 0;
var row = 1;
while (e <= 3)
{
row++;
var miscId = worksheet?.Cell(row, columnId)?.Value?.ToString();
if (int.TryParse(miscId, out int id))
{
// reset error row
e = 0;
list.Add(new Misc
{
Id = id,
Row = row,
});
}
else
{
// increment error row
e++;
}
}
return(list);
}
public void Format(IXLWorksheet worksheet, Step step, ref int row)
{
worksheet.Cell(row, "C").Style.Font.SetBold();
worksheet.Cell(row, "C").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
worksheet.Cell(row, "C").Value = step.NativeKeyword;
worksheet.Cell(row++, "D").Value = step.Name;
if (step.TableArgument != null)
{
this.excelTableFormatter.Format(worksheet, step.TableArgument, ref row);
}
if (!string.IsNullOrEmpty(step.DocStringArgument))
{
this.excelDocumentStringFormatter.Format(worksheet, step.DocStringArgument, ref row);
}
}
public void AddColumn(string sheetName, string colName)
{
worksheet = workbook.Worksheet(sheetName);
if(worksheet.LastColumnUsed()!=null)
worksheet.LastColumnUsed().ColumnRight().Cell(1).Value = colName;
else
worksheet.Cell("A1").Value = colName;
Save();
}
public void Format(IXLWorksheet worksheet, string documentString, ref int row)
{
string[] documentStringLines = documentString.Split(new[] {"\n", "\r"},
StringSplitOptions.RemoveEmptyEntries);
foreach (string line in documentStringLines)
{
worksheet.Cell(row++, 4).Value = line;
}
}
int Estrai_Template_2017(IXLWorksheet wsAnno, int lastRowNumber, IXLWorksheet dati, int riga)
{
var laura = wsAnno.Range("B1", "F" + lastRowNumber).Rows().Select(Movimento.TryParse2017);
var valerio = wsAnno.Range("G1", "L" + lastRowNumber).Rows().Select(Movimento.TryParse2017);
var comune = wsAnno.Range("L1", "R" + lastRowNumber).Rows().Select(Movimento.TryParse2017);
var movimenti = laura.Concat(valerio).Concat(comune).Where(r => r != null).ToArray();
foreach (var movimento in movimenti)
{
dati.Cell(riga, "A").Value = movimento.Data;
dati.Cell(riga, "B").Value = movimento.Categoria;
dati.Cell(riga, "C").Value = movimento.Per;
dati.Cell(riga, "D").Value = movimento.Descrizione;
dati.Cell(riga, "E").Value = movimento.Spesa;
riga++;
}
return riga;
}
public void Format(IXLWorksheet worksheet, Step step, ref int row)
{
// Add comments
if (step.Comments.Any(o => o.Type == CommentType.StepComment))
{
foreach (var comment in step.Comments.Where(o => o.Type == CommentType.StepComment))
{
worksheet.Cell(row, "C").Style.Font.SetItalic();
worksheet.Cell(row, "C").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
worksheet.Cell(row, "C").Value = comment.Text;
row++;
}
}
worksheet.Cell(row, "C").Style.Font.SetBold();
worksheet.Cell(row, "C").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
worksheet.Cell(row, "C").Value = step.NativeKeyword;
worksheet.Cell(row++, "D").Value = step.Name;
if (step.Comments.Any(o => o.Type == CommentType.AfterLastStepComment))
{
foreach (var comment in step.Comments.Where(o => o.Type == CommentType.AfterLastStepComment))
{
worksheet.Cell(row, "C").Style.Font.SetItalic();
worksheet.Cell(row, "C").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
worksheet.Cell(row, "C").Value = comment.Text;
row++;
}
}
if (step.TableArgument != null)
{
this.excelTableFormatter.Format(worksheet, step.TableArgument, ref row);
}
if (!string.IsNullOrEmpty(step.DocStringArgument))
{
this.excelDocumentStringFormatter.Format(worksheet, step.DocStringArgument, ref row);
}
}
public void Format(IXLWorksheet worksheet, Scenario scenario, ref int row)
{
int originalRow = row;
worksheet.Cell(row, "B").Style.Font.SetBold();
worksheet.Cell(row++, "B").Value = scenario.Name;
worksheet.Cell(row++, "C").Value = scenario.Description;
var results = this.testResults.GetScenarioResult(scenario);
if (this.configuration.HasTestResults && results.WasExecuted)
{
worksheet.Cell(originalRow, "B").Style.Fill.SetBackgroundColor(
results.WasSuccessful
? XLColor.AppleGreen
: XLColor.CandyAppleRed);
}
foreach (Step step in scenario.Steps)
{
this.excelStepFormatter.Format(worksheet, step, ref row);
}
}
public void Format(IXLWorksheet worksheet, ScenarioOutline scenarioOutline, ref int row)
{
int originalRow = row;
worksheet.Cell(row++, "B").Value = scenarioOutline.Name;
worksheet.Cell(row++, "C").Value = scenarioOutline.Description;
var results = testResults.GetScenarioOutlineResult(scenarioOutline);
if (configuration.HasTestResults && results.WasExecuted)
{
worksheet.Cell(originalRow, "B").Style.Fill.SetBackgroundColor(results.WasSuccessful
? XLColor.AppleGreen
: XLColor.CandyAppleRed);
}
foreach (Step step in scenarioOutline.Steps)
{
excelStepFormatter.Format(worksheet, step, ref row);
}
row++;
worksheet.Cell(row++, "B").Value = "Examples";
excelTableFormatter.Format(worksheet, scenarioOutline.Example.TableArgument, ref row);
}
private void _LoadColumns()
{
_columnIndexes = new Dictionary <string, int>();
var firstRow = Dimension.FirstRow;
for (int i = Dimension.FirstColumn; i <= Dimension.LastColumn; ++i)
{
var value = _worksheet?.Cell(firstRow, i)?.GetValue <string>();
if (!string.IsNullOrWhiteSpace(value) && !_columnIndexes.ContainsKey(value))
{
_columnIndexes.Add(value, i);
}
}
}
public void Format(IXLWorksheet worksheet, Feature feature)
{
worksheet.Cell("A1").Style.Font.SetBold();
worksheet.Cell("A1").Value = feature.Name;
if (feature.Description.Length <= short.MaxValue)
{
worksheet.Cell("B2").Value = feature.Description;
}
else
{
var description = feature.Description.Substring(0, short.MaxValue);
Log.Warn("The description of feature {0} was truncated because of cell size limitations in Excel.", feature.Name);
worksheet.Cell("B2").Value = description;
}
worksheet.Cell("B2").Style.Alignment.WrapText = false;
var results = this.testResults.GetFeatureResult(feature);
if (this.configuration.HasTestResults && results != TestResult.Inconclusive)
{
worksheet.Cell("A1").Style.Fill.SetBackgroundColor(results == TestResult.Passed
? XLColor.AppleGreen
: XLColor.CandyAppleRed);
}
var featureElementsToRender = new List<IFeatureElement>();
if (feature.Background != null)
{
featureElementsToRender.Add(feature.Background);
}
featureElementsToRender.AddRange(feature.FeatureElements);
var row = 4;
foreach (var featureElement in featureElementsToRender)
{
var scenario = featureElement as Scenario;
if (scenario != null)
{
this.excelScenarioFormatter.Format(worksheet, scenario, ref row);
}
var scenarioOutline = featureElement as ScenarioOutline;
if (scenarioOutline != null)
{
this.excelScenarioOutlineFormatter.Format(worksheet, scenarioOutline, ref row);
}
row++;
}
}
public void Format(IXLWorksheet worksheet, Feature feature)
{
worksheet.Cell("A1").Style.Font.SetBold();
worksheet.Cell("A1").Value = feature.Name;
if (feature.Tags != null && feature.Tags.Count != 0)
{
worksheet.Cell("B2").Value = "Tags:";
worksheet.Cell("C2").Value = String.Join(", ", feature.Tags);
worksheet.Range("B2:C2").Style.Font.Italic = true;
worksheet.Range("B2:C2").Style.Font.FontColor = XLColor.DavysGrey;
}
if (feature.Description.Length <= short.MaxValue)
{
worksheet.Cell("B3").Value = feature.Description;
}
else
{
var description = feature.Description.Substring(0, short.MaxValue);
Log.Warn("The description of feature {0} was truncated because of cell size limitations in Excel.", feature.Name);
worksheet.Cell("B3").Value = description;
}
worksheet.Cell("B3").Style.Alignment.WrapText = false;
var results = this.testResults.GetFeatureResult(feature);
if (this.configuration.HasTestResults && results != TestResult.Inconclusive)
{
worksheet.Cell("A1").Style.Fill.SetBackgroundColor(results == TestResult.Passed
? XLColor.AppleGreen
: XLColor.CandyAppleRed);
}
var featureElementsToRender = new List <IFeatureElement>();
if (feature.Background != null)
{
featureElementsToRender.Add(feature.Background);
}
featureElementsToRender.AddRange(feature.FeatureElements);
var row = 5;
foreach (var featureElement in featureElementsToRender)
{
var scenario = featureElement as Scenario;
if (scenario != null)
{
this.excelScenarioFormatter.Format(worksheet, scenario, ref row);
}
var scenarioOutline = featureElement as ScenarioOutline;
if (scenarioOutline != null)
{
this.excelScenarioOutlineFormatter.Format(worksheet, scenarioOutline, ref row);
}
row++;
}
}
private static void SetOxidesTable(IXLWorksheet ws)
{
var mainOxides = Model.OxidesResultModel.First().OxidesResult.Select(x => x.Oxide.Formula);
var oxides = Model.Oxides.Where(x => !mainOxides.Contains(x.Composition.Formula));
var countInsert = oxides.Count() - 3;
if (countInsert > 0)
{
ws.Column("AV").InsertColumnsAfter(oxides.Count() - 3);//44 - 51 template column
ws.Range(5, 44, 8, 51 + countInsert).Merge();
ws.Range(18, 44, 18, 50 + countInsert).Merge();
}
var currentCol = 48;
foreach (var oxide in oxides)
{
var currentCell = ws.Cell(9, currentCol);
currentCell.Style.Border.RightBorder = XLBorderStyleValues.Thin;
currentCell.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
SetChemicalFormula(currentCell.RichText, oxide.Composition.Formula);
var currentResCell = ws.Cell(19, currentCol);
SetChemicalFormula(currentResCell.RichText, oxide.Composition.Formula);
currentCol++;
}
}
private static void SetResLiquidOxides(
IXLWorksheet ws,
OxidesByTemperatureModel model,
int currentRow,
int currentColumn)
{
currentRow = currentRow + Model.OxidesResultModel.Count + 9;
ws.Cell(currentRow, currentColumn - 1).Value = model.Temperature;
float sum = 0;
foreach (var oxide in model.OxidesResult)
{
sum += oxide.Percentage;
ws.Cell(currentRow, currentColumn).Value = oxide.Percentage;
currentColumn++;
}
ws.Cell(currentRow, currentColumn).Value = model.OxidesResult.Sum(x => x.Percentage);
}
private static void SetDataBySystem(IXLWorksheet ws, float systemModule, PhaseSystemModel systemOrig, PhaseSystemModel systemCalc, int currentRow, ref int currentColumn)
{
ws.Cell(currentRow, currentColumn).Value = systemModule;
currentColumn++;
ws.Cell(currentRow, currentColumn).Value = systemOrig.SumLiquid;
currentColumn++;
SetPhasesData(ws, systemOrig.Phases, currentRow, ref currentColumn);
ws.Cell(currentRow, currentColumn).Value = systemCalc.SumLiquid;
currentColumn++;
SetPhasesData(ws, systemCalc.Phases, currentRow, ref currentColumn);
SetOxidesBySystem(ws, systemCalc.SolidSumOxides, currentRow, ref currentColumn);
}
private static void SetOriginalOxides(
IXLWorksheet ws,
OxidesByTemperatureModel model,
int currentRow,
ref int currentColumn)
{
foreach (var oxide in model.OxidesResult)
{
ws.Cell(currentRow, currentColumn).Value =
Model.Oxides.First(x => x.Composition.Formula == oxide.Oxide.Formula).Percentage;
currentColumn++;
}
}
private void WriteDirectoryCell(IXLWorksheet worksheet, ref int row, int column, string directoryName)
{
worksheet.Cell(row, column).Style.Font.Bold = true;
worksheet.Cell(row++, column).Value = directoryName;
}
private static void CreateHeader(IXLWorksheet ws)
{
//var systems = new List<string>() { "CaO-Al2O3-SiO2", "К2O-Al2O3-SiO2" };
//var firstSystemPhases = new List<string>() { "A", "NAS2", "NAS6", "S", "A3S2" };
////ws.Column("J").InsertColumnsAfter(3);
//var cell = ws.Cell("B5");//.Value = "CaO-Al2O3-SiO2";
//SetChemicalFormula(cell.RichText, "Na2O-Al2O3-SiO2");
//ws.Cell("A10").Value = Model.NameOfMaterial;
SetChemicalFormula(ws.Cell("B5").RichText, Model.FirstSystemName);
SetChemicalFormula(ws.Cell("T5").RichText, Model.SecondSystemName);
SetChemicalFormula(ws.Cell("D7").RichText, string.Format(moduleHeaderTemplateString, Model.FirstSystemOxide));
SetChemicalFormula(ws.Cell("T7").RichText, string.Format(moduleHeaderTemplateString, Model.SecondSystemOxide));
var firstSystemOxides = Model.ResultTemperaturesModels.First().FirstSystem.SolidSumOxides.Select(x => x.Oxide.Formula).ToList();
var secondSystemOxides = Model.ResultTemperaturesModels.First().SecondSystem.SolidSumOxides.Select(x => x.Oxide.Formula).ToList();
SetChemicalFormula(ws.Cell("Q9").RichText, firstSystemOxides[0]);
SetChemicalFormula(ws.Cell("R9").RichText, firstSystemOxides[1]);
SetChemicalFormula(ws.Cell("S9").RichText, firstSystemOxides[2]);
SetChemicalFormula(ws.Cell("AG9").RichText, secondSystemOxides[0]);
SetChemicalFormula(ws.Cell("AH9").RichText, secondSystemOxides[1]);
SetChemicalFormula(ws.Cell("AI9").RichText, secondSystemOxides[2]);
var oxidesForCalculation = Model.OxidesResultModel.First().OxidesResult.Select(x=>x.Oxide.Formula).ToList();
SetChemicalFormula(ws.Cell("AJ9").RichText, oxidesForCalculation[0]);
SetChemicalFormula(ws.Cell("AK9").RichText, oxidesForCalculation[1]);
SetChemicalFormula(ws.Cell("AL9").RichText, oxidesForCalculation[2]);
SetChemicalFormula(ws.Cell("AM9").RichText, oxidesForCalculation[3]);
SetChemicalFormula(ws.Cell("AN9").RichText, oxidesForCalculation[0]);
SetChemicalFormula(ws.Cell("AO9").RichText, oxidesForCalculation[1]);
SetChemicalFormula(ws.Cell("AP9").RichText, oxidesForCalculation[2]);
SetChemicalFormula(ws.Cell("AQ9").RichText, oxidesForCalculation[3]);
SetChemicalFormula(ws.Cell("AR9").RichText, oxidesForCalculation[0]);
SetChemicalFormula(ws.Cell("AS9").RichText, oxidesForCalculation[1]);
SetChemicalFormula(ws.Cell("AT9").RichText, oxidesForCalculation[2]);
SetChemicalFormula(ws.Cell("AU9").RichText, oxidesForCalculation[3]);
SetChemicalFormula(ws.Cell("AR19").RichText, oxidesForCalculation[0]);
SetChemicalFormula(ws.Cell("AS19").RichText, oxidesForCalculation[1]);
SetChemicalFormula(ws.Cell("AT19").RichText, oxidesForCalculation[2]);
SetChemicalFormula(ws.Cell("AU19").RichText, oxidesForCalculation[3]);
SetOxidesTable(ws);
SetSystemsPhasesHeader(ws);
//ws.Column("J").InsertColumnsBefore(3);
//var a = ws.Cell("F7");
//ws.Range(7, 6, 8, 13).Merge();
//ws.Range(5, 2, 6, 22).Merge();
}
private static void SetSolidPhaseOxide(
IXLWorksheet ws,
OxidesByTemperatureModel model,
int currentRow,
ref int currentColumn)
{
foreach (var oxide in model.OxidesResult)
{
ws.Cell(currentRow, currentColumn).Value = oxide.Percentage;
currentColumn++;
}
}
private static void CreateBiolab(IXLWorksheet sheet,
Tuple <BiolabNota, BiolabRetorno> biolab)
{
sheet.Cell("B4").Value = "Biolab";
sheet.Cell("B4").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("C4").Value = "Pharmalink";
sheet.Cell("C4").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("D4").Value = biolab.Item2.QuantidadePendencias;
sheet.Cell("D4").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("E4").Value = biolab.Item2.DataPrimeiraPendencia;
sheet.Cell("E4").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("F4").Value = biolab.Item1.QuantidadePendencias;
sheet.Cell("F4").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("G4").Value = biolab.Item1.DataPrimeiraPendencia;
sheet.Cell("G4").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("H4").Value = "-";
sheet.Cell("H4").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("I4").Value = "-";
sheet.Cell("I4").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
public bool ImportExcelData(string oper, string filePath, ref ValidationErrors errors)
{
bool rtn = true;
var targetFile = new FileInfo(filePath);
if (!targetFile.Exists)
{
errors.Add("导入的数据文件不存在");
return(false);
}
var excelFile = new ExcelQueryFactory(filePath);
using (XLWorkbook wb = new XLWorkbook(filePath))
{
//第一个Sheet
using (IXLWorksheet wws = wb.Worksheets.First())
{
//对应列头
excelFile.AddMapping <WMS_POModel>(x => x.PO, "采购订单(必输)");
excelFile.AddMapping <WMS_POModel>(x => x.PODate, "采购日期(必输格式:YYYY-MM-DD)");
excelFile.AddMapping <WMS_POModel>(x => x.SupplierShortName, "供应商简称(必输)");
excelFile.AddMapping <WMS_POModel>(x => x.PartCode, "物料编码(必输)");
excelFile.AddMapping <WMS_POModel>(x => x.QTY, "数量(必输)");
excelFile.AddMapping <WMS_POModel>(x => x.PlanDate, "计划到货日期");
excelFile.AddMapping <WMS_POModel>(x => x.POType, "采购订单类型");
//excelFile.AddMapping<WMS_POModel>(x => x.Status, "状态");
excelFile.AddMapping <WMS_POModel>(x => x.Remark, "说明");
//excelFile.AddMapping<WMS_POModel>(x => x.Attr1, "");
//excelFile.AddMapping<WMS_POModel>(x => x.Attr2, "");
//excelFile.AddMapping<WMS_POModel>(x => x.Attr3, "");
//excelFile.AddMapping<WMS_POModel>(x => x.Attr4, "");
//excelFile.AddMapping<WMS_POModel>(x => x.Attr5, "");
//excelFile.AddMapping<WMS_POModel>(x => x.CreatePerson, "创建人");
//excelFile.AddMapping<WMS_POModel>(x => x.CreateTime, "创建时间");
//excelFile.AddMapping<WMS_POModel>(x => x.ModifyPerson, "修改人");
//excelFile.AddMapping<WMS_POModel>(x => x.ModifyTime, "修改时间");
//SheetName，第一个Sheet
var excelContent = excelFile.Worksheet <WMS_POModel>(0);
//开启事务
using (DBContainer db = new DBContainer())
{
var tran = db.Database.BeginTransaction(); //开启事务
int rowIndex = 0;
//检查数据正确性
foreach (var row in excelContent)
{
rowIndex += 1;
string errorMessage = String.Empty;
var model = new WMS_POModel();
model.Id = row.Id;
model.PO = row.PO;
model.PODate = row.PODate;
model.SupplierShortName = row.SupplierShortName;
model.PartCode = row.PartCode;
model.QTY = row.QTY;
model.PlanDate = row.PlanDate;
model.POType = row.POType;
model.Status = "有效";
model.Remark = row.Remark;
//model.Attr1 = row.Attr1;
//model.Attr2 = row.Attr2;
//model.Attr3 = row.Attr3;
//model.Attr4 = row.Attr4;
//model.Attr5 = row.Attr5;
//model.CreatePerson = row.CreatePerson;
//model.CreateTime = row.CreateTime;
//model.ModifyPerson = row.ModifyPerson;
//model.ModifyTime = row.ModifyTime;
if (!String.IsNullOrEmpty(errorMessage))
{
rtn = false;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//执行额外的数据校验
try
{
AdditionalCheckExcelData(db, ref model);
}
catch (Exception ex)
{
rtn = false;
errorMessage = ex.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//写入数据库
WMS_PO entity = new WMS_PO();
entity.Id = model.Id;
entity.PO = model.PO;
entity.PODate = model.PODate;
entity.SupplierId = model.SupplierId;
entity.PartId = model.PartId;
entity.QTY = model.QTY;
entity.PlanDate = model.PlanDate;
entity.POType = model.POType;
entity.Status = "有效";
entity.Remark = model.Remark;
entity.Attr1 = model.Attr1;
entity.Attr2 = model.Attr2;
entity.Attr3 = model.Attr3;
entity.Attr4 = model.Attr4;
entity.Attr5 = model.Attr5;
entity.CreatePerson = oper;
entity.CreateTime = DateTime.Now;
entity.ModifyPerson = oper;
entity.ModifyTime = DateTime.Now;
db.WMS_PO.Add(entity);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
rtn = false;
//将当前报错的entity状态改为分离，类似EF的回滚（忽略之前的Add操作）
db.Entry(entity).State = System.Data.Entity.EntityState.Detached;
errorMessage = ex.InnerException.InnerException.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
}
}
if (rtn)
{
tran.Commit(); //必须调用Commit()，不然数据不会保存
}
else
{
tran.Rollback(); //出错就回滚
}
}
}
wb.Save();
}
return(rtn);
}
private static void CreateAche(IXLWorksheet sheet,
Tuple <AcheNota, AcheRetorno> ache)
{
sheet.Cell("B2").Value = "Aché";
sheet.Cell("B2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("C2").Value = "Visão";
sheet.Cell("C2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("D2").Value = ache.Item2.QuantidadePendencias;
sheet.Cell("D2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("E2").Value = ache.Item2.DataPrimeiraPendencia;
sheet.Cell("E2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("F2").Value = ache.Item1.QuantidadePendencias;
sheet.Cell("F2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("G2").Value = ache.Item1.DataPrimeiraPendencia;
sheet.Cell("G2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("H2").Value = "-";
sheet.Cell("H2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("I2").Value = "-";
sheet.Cell("I2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
/// <summary>
/// 野菜の一覧を作成します
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
// Workbookを作成する
using (XLWorkbook workbook = new XLWorkbook())
{
// Worksheetを追加
IXLWorksheet worksheet = workbook.AddWorksheet("Sheet1");
int row = 1;
// 1行目にヘッダー部を出力
worksheet.Cell(row, 1).SetValue("No");
worksheet.Cell(row, 2).SetValue("Yasai Name");
worksheet.Cell(row, 3).SetValue("Price");
row++;
// 2行目以降に野菜一覧作成
worksheet.Cell(row, 1).SetValue(row - 1);
worksheet.Cell(row, 2).SetValue("Carrot");
worksheet.Cell(row, 3).SetValue(120);
row++;
worksheet.Cell(row, 1).SetValue(row - 1);
worksheet.Cell(row, 2).SetValue("Tomato");
worksheet.Cell(row, 3).SetValue(220);
row++;
worksheet.Cell(row, 1).SetValue(row - 1);
worksheet.Cell(row, 2).SetValue("Cabbage");
worksheet.Cell(row, 3).SetValue(100);
//出力した表の内側に罫線を引く
worksheet.Range(1, 1, row, 3).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
//出力した表の外側に罫線を引く
worksheet.Range(1, 1, row, 3).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
// ヘッダー(1行目)に背景色を付ける
worksheet.Range(1, 1, 1, 3).Style.Fill.BackgroundColor = XLColor.SkyBlue;
// 名前をつけてブックを保存
workbook.SaveAs("ClosedXMLSample.xlsx");
}
}
public bool ImportExcelData(string oper, string filePath, ref ValidationErrors errors)
{
bool rtn = true;
var targetFile = new FileInfo(filePath);
if (!targetFile.Exists)
{
errors.Add("导入的数据文件不存在");
return(false);
}
var excelFile = new ExcelQueryFactory(filePath);
using (XLWorkbook wb = new XLWorkbook(filePath))
{
//第一个Sheet
using (IXLWorksheet wws = wb.Worksheets.First())
{
//对应列头
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.HeadId, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.PartId, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.SnapshootQty, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.InvId, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.SubInvId, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.Remark, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.Attr1, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.Attr2, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.Attr3, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.Attr4, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.Attr5, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.CreatePerson, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.CreateTime, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.ModifyPerson, "");
excelFile.AddMapping <WMS_Inv_History_DModel>(x => x.ModifyTime, "");
//SheetName，第一个Sheet
var excelContent = excelFile.Worksheet <WMS_Inv_History_DModel>(0);
//开启事务
using (DBContainer db = new DBContainer())
{
var tran = db.Database.BeginTransaction(); //开启事务
int rowIndex = 0;
//检查数据正确性
foreach (var row in excelContent)
{
rowIndex += 1;
string errorMessage = String.Empty;
var model = new WMS_Inv_History_DModel();
model.Id = row.Id;
model.HeadId = row.HeadId;
model.PartId = row.PartId;
model.SnapshootQty = row.SnapshootQty;
model.InvId = row.InvId;
model.SubInvId = row.SubInvId;
model.Remark = row.Remark;
model.Attr1 = row.Attr1;
model.Attr2 = row.Attr2;
model.Attr3 = row.Attr3;
model.Attr4 = row.Attr4;
model.Attr5 = row.Attr5;
model.CreatePerson = row.CreatePerson;
model.CreateTime = row.CreateTime;
model.ModifyPerson = row.ModifyPerson;
model.ModifyTime = row.ModifyTime;
if (!String.IsNullOrEmpty(errorMessage))
{
rtn = false;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//执行额外的数据校验
try
{
AdditionalCheckExcelData(ref model);
}
catch (Exception ex)
{
rtn = false;
errorMessage = ex.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//写入数据库
WMS_Inv_History_D entity = new WMS_Inv_History_D();
entity.Id = model.Id;
entity.HeadId = model.HeadId;
entity.PartId = model.PartId;
entity.SnapshootQty = model.SnapshootQty;
entity.InvId = model.InvId;
entity.SubInvId = model.SubInvId;
entity.Remark = model.Remark;
entity.Attr1 = model.Attr1;
entity.Attr2 = model.Attr2;
entity.Attr3 = model.Attr3;
entity.Attr4 = model.Attr4;
entity.Attr5 = model.Attr5;
entity.CreatePerson = model.CreatePerson;
entity.CreateTime = model.CreateTime;
entity.ModifyPerson = model.ModifyPerson;
entity.ModifyTime = model.ModifyTime;
entity.CreatePerson = oper;
entity.CreateTime = DateTime.Now;
entity.ModifyPerson = oper;
entity.ModifyTime = DateTime.Now;
db.WMS_Inv_History_D.Add(entity);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
rtn = false;
//将当前报错的entity状态改为分离，类似EF的回滚（忽略之前的Add操作）
db.Entry(entity).State = System.Data.Entity.EntityState.Detached;
errorMessage = ex.InnerException.InnerException.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
}
}
if (rtn)
{
tran.Commit(); //必须调用Commit()，不然数据不会保存
}
else
{
tran.Rollback(); //出错就回滚
}
}
}
wb.Save();
}
return(rtn);
}
public ActionResult Export()
{
JObject jo = new JObject();
var hasData = !_context.Students.Count().Equals(0);
if (!hasData)
{
jo.Add("Msg", hasData.ToString());
return(Content(JsonConvert.SerializeObject(jo), "application/json"));
}
else
{
var exportFileName = string.Concat(
"Students_",
DateTime.Now.ToString("yyyyMMddHHmmss"),
".xlsx");
var exportData = new ExportExcelResult
{
SheetName = "outputfile",
FileName = exportFileName,
ExportData = _context.Students.ToList()
};
//_exportExcelResult.ExecuteResult(exportData);
try
{
var workbook = new XLWorkbook();
IXLWorksheet worksheet = workbook.Worksheets.Add("outputfile");
//worksheet.Cell(1, 1).Value = "Id";
worksheet.Cell(1, 1).Value = "LastName";
worksheet.Cell(1, 2).Value = "FirstMidName";
worksheet.Cell(1, 3).Value = "EnrollmentDate";
for (int index = 1; index <= exportData.ExportData.Count; index++)
{
//worksheet.Cell(index + 1, 1).Value = exportData.ExportData[index - 1].ID;
worksheet.Cell(index + 1, 1).Value = exportData.ExportData[index - 1].LastName;
worksheet.Cell(index + 1, 2).Value = exportData.ExportData[index - 1].FirstMidName;
worksheet.Cell(index + 1, 3).Value = exportData.ExportData[index - 1].EnrollmentDate;
}
var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
var content = stream.ToArray();
return(File(content, contentType, exportData.FileName));
}
//workbook.SaveAs(@"C:\Users\v-ivowan\Desktop\" + dt.FileName);
}
catch (Exception ex)
{
throw (ex);
}
//return RedirectToAction(nameof(Index));
}
//var exportSpource = this.GetExportData();
//var dt = JsonConvert.DeserializeObject<Student>(exportSpource.ToString());
}
private static void CreateHyperaPharma(IXLWorksheet sheet,
Tuple <HyperaPharmaNota, HyperaPharmaRetorno> hyperaPharma)
{
sheet.Cell("B6").Value = "Hypera";
sheet.Cell("B6").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("C6").Value = "Pharmalink";
sheet.Cell("C6").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("D6").Value = hyperaPharma.Item2.QuantidadePendencias;
sheet.Cell("D6").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("E6").Value = hyperaPharma.Item2.DataPrimeiraPendencia;
sheet.Cell("E6").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("F6").Value = hyperaPharma.Item1.QuantidadePendencias;
sheet.Cell("F6").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("G6").Value = hyperaPharma.Item1.DataPrimeiraPendencia;
sheet.Cell("G6").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("H6").Value = "-";
sheet.Cell("H6").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("I6").Value = "-";
sheet.Cell("I6").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
private static void CreateSanofi(IXLWorksheet sheet,
Tuple <SanofiNota, SanofiRetorno> sanofi)
{
sheet.Cell("B9").Value = "Sanofi";
sheet.Cell("B9").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("C9").Value = "Fidelize";
sheet.Cell("C9").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("D9").Value = sanofi.Item2.QuantidadePendencias;
sheet.Cell("D9").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("E9").Value = sanofi.Item2.DataPrimeiraPendencia;
sheet.Cell("E9").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("F9").Value = sanofi.Item1.QuantidadePendencias;
sheet.Cell("F9").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("G9").Value = sanofi.Item1.DataPrimeiraPendencia;
sheet.Cell("G9").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("H9").Value = "-";
sheet.Cell("H9").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("I9").Value = "-";
sheet.Cell("I9").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
private void WriteFileCell(IXLWorksheet worksheet, ref int row, int column, IXLWorksheet featureWorksheet)
{
worksheet.Cell(row, column).Value = featureWorksheet.Cell("A1").Value;
worksheet.Cell(row++, column).Hyperlink = new XLHyperlink(featureWorksheet.Name + "!A1");
}
private static void CreateSandoz(IXLWorksheet sheet,
Tuple <SandozNota, SandozRetorno> sandoz)
{
sheet.Cell("B8").Value = "Sandoz";
sheet.Cell("B8").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("C8").Value = "Pharmalink";
sheet.Cell("C8").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("D8").Value = sandoz.Item2.QuantidadePendencias;
sheet.Cell("D8").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("E8").Value = sandoz.Item2.DataPrimeiraPendencia;
sheet.Cell("E8").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("F8").Value = sandoz.Item1.QuantidadePendencias;
sheet.Cell("F8").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("G8").Value = sandoz.Item1.DataPrimeiraPendencia;
sheet.Cell("G8").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("H8").Value = "-";
sheet.Cell("H8").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("I8").Value = "-";
sheet.Cell("I8").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
private static void CreateBoehringer(IXLWorksheet sheet,
Tuple <BoehringerNota, BoehringerRetorno> boehringer)
{
sheet.Cell("B5").Value = "Boehringe";
sheet.Cell("B5").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("C5").Value = "Fidelize";
sheet.Cell("C5").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("D5").Value = boehringer.Item2.QuantidadePendencias;
sheet.Cell("D5").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("E5").Value = boehringer.Item2.DataPrimeiraPendencia;
sheet.Cell("E5").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("F5").Value = boehringer.Item1.QuantidadePendencias;
sheet.Cell("F5").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("G5").Value = boehringer.Item1.DataPrimeiraPendencia;
sheet.Cell("G5").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("H5").Value = "-";
sheet.Cell("H5").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("I5").Value = "-";
sheet.Cell("I5").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
private void AddTestColumnNumbers(IXLWorksheet ws)
{
ws.Cell("A1").SetValue(1.30).Style.Fill.SetBackgroundColor(XLColor.LightGreen);
ws.Cell("A2").SetValue(1.15).Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise);
ws.Cell("A3").SetValue(1230).Style.Fill.SetBackgroundColor(XLColor.BurlyWood);
ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray);
ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon);
ws.Cell("A6").SetValue(9).Style.Fill.SetBackgroundColor(XLColor.DodgerBlue);
ws.Cell("A7").SetValue(4.30).Style.Fill.SetBackgroundColor(XLColor.IndianRed);
ws.Cell("A8").SetValue(4.15).Style.Fill.SetBackgroundColor(XLColor.DeepPink);
}
private static void SetData(IXLWorksheet ws)
{
var currentRow = 10;
for (int i = 0; i < Model.ResultTemperaturesModels.Count; i++)
{
var currentColumn = 2;
ws.Cell(currentRow, currentColumn).Value = Model.ResultTemperaturesModels[i].Temperature;
currentColumn++;
ws.Cell(currentRow, currentColumn).Value = Model.SumR2O;
currentColumn++;
SetDataBySystem(
ws,
Model.FirstSystemModule,
Model.TemperaturesModels[i].FirstSystem,
Model.ResultTemperaturesModels[i].FirstSystem,
currentRow,
ref currentColumn);
SetDataBySystem(
ws,
Model.SecondSystemModule,
Model.TemperaturesModels[i].SecondSystem,
Model.ResultTemperaturesModels[i].SecondSystem,
currentRow,
ref currentColumn);
SetSolidPhaseOxide(ws, Model.OxidesResultModel[i], currentRow, ref currentColumn);
SetOriginalOxides(ws, Model.OxidesResultModel[i], currentRow, ref currentColumn);
SetLiquidOxides(ws, Model.OxidesInMelt[i], currentRow, currentColumn);
SetResLiquidOxides(ws, Model.OxidesInMeltRes[i], currentRow, currentColumn);
currentRow++;
}
}
private void AddTestColumnTimeSpans(IXLWorksheet ws)
{
ws.Cell("A1").SetValue(new TimeSpan(0, 12, 35, 21)).Style.Fill.SetBackgroundColor(XLColor.LightGreen);
ws.Cell("A2").SetValue(new TimeSpan(45, 1, 15)).Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise);
ws.Cell("A3").SetValue(new TimeSpan(1, 1, 12, 30)).Style.Fill.SetBackgroundColor(XLColor.BurlyWood);
ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray);
ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon);
ws.Cell("A6").SetValue(new TimeSpan(0, 12, 15)).Style.Fill.SetBackgroundColor(XLColor.DodgerBlue);
ws.Cell("A7").SetValue(new TimeSpan(1, 4, 30)).Style.Fill.SetBackgroundColor(XLColor.IndianRed);
ws.Cell("A8").SetValue(new TimeSpan(1, 4, 15)).Style.Fill.SetBackgroundColor(XLColor.DeepPink);
}
private static void SetLiquidOxides(
IXLWorksheet ws,
OxidesByTemperatureModel model,
int currentRow,
int currentColumn)
{
float sum = 0;
foreach (var oxide in model.OxidesResult)
{
sum += oxide.Percentage;
ws.Cell(currentRow, currentColumn).Value = oxide.Percentage;
currentColumn++;
}
ws.Cell(currentRow, currentColumn).Value = sum;
}
private void AddTestTable(IXLWorksheet ws)
{
ws.Cell("A1").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.LightGreen);
ws.Cell("A2").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise);
ws.Cell("A3").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.BurlyWood);
ws.Cell("A4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray);
ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon);
ws.Cell("A6").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue);
ws.Cell("A7").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.IndianRed);
ws.Cell("A8").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.DeepPink);
ws.Cell("B1").SetValue("").Style.Fill.SetBackgroundColor(XLColor.LightGreen);
ws.Cell("B2").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise);
ws.Cell("B3").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.BurlyWood);
ws.Cell("B4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray);
ws.Cell("B5").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon);
ws.Cell("B6").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue);
ws.Cell("B7").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.IndianRed);
ws.Cell("B8").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DeepPink);
ws.Cell("C1").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.LightGreen);
ws.Cell("C2").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise);
ws.Cell("C3").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.BurlyWood);
ws.Cell("C4").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkGray);
ws.Cell("C5").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon);
ws.Cell("C6").SetValue("b").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue);
ws.Cell("C7").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.IndianRed);
ws.Cell("C8").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DeepPink);
}
private static void SetOxidesBySystem(
IXLWorksheet ws,
List<OxideResultModel> oxides,
int currentRow,
ref int currentColumn)
{
foreach (var oxide in oxides)
{
ws.Cell(currentRow, currentColumn).Value = oxide.Percentage;
currentColumn++;
}
}
public void ConfigureCell(IXLWorksheet worksheet, SurveyType surveyType)
{
#region SetUp
int numberOfCells = 0;
int detailColumns = 1; // Inicialized in one because the last column
switch (surveyType)
{
case SurveyType.CareTeam:
detailColumns += 5;
numberOfCells = QuestionKey.CareTeamSurvey.NumberOfKeys() + detailColumns;
break;
case SurveyType.PCP:
detailColumns += 7;
numberOfCells = QuestionKey.PCPSurvey.NumberOfKeys() + detailColumns;
break;
case SurveyType.Specialist:
detailColumns += 7;
numberOfCells = QuestionKey.SpecialistSurvey.NumberOfKeys() + detailColumns;
break;
}
#endregion
#region Headers
for (int x = 1; x <= numberOfCells; x++)
{
worksheet.Cell(1, x).Style.Font.Bold = true;
worksheet.Cell(1, x).Style.Alignment.WrapText = true;
worksheet.Cell(1, x).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(1, x).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(1, x).Style.Fill.BackgroundColor = XLColor.LightCyan;
worksheet.Cell(1, x).Style.Font.FontSize = 11;
worksheet.Cell(1, x).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, x).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, x).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(1, x).Style.Border.RightBorder = XLBorderStyleValues.Thin;
if (x > detailColumns)
{
if (x == numberOfCells)
worksheet.Column(x).Width = 55;
else
worksheet.Column(x).Width = 30;
}
else
worksheet.Column(x).Width = 20;
}
#endregion
#region Rows
for (int x = 1; x <= numberOfCells; x++)
{
worksheet.Cell(2, x).Style.Alignment.WrapText = true;
worksheet.Cell(2, x).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(2, x).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
worksheet.Cell(2, x).Style.Font.FontSize = 11;
worksheet.Cell(2, x).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2, x).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2, x).Style.Border.TopBorder = XLBorderStyleValues.Thin;
worksheet.Cell(2, x).Style.Border.RightBorder = XLBorderStyleValues.Thin;
}
#endregion
}
private static void SetPhasesData(IXLWorksheet ws, List<PhaseModel> phases, int currentRow, ref int currentColumn)
{
foreach (var phase in phases)
{
ws.Cell(currentRow, currentColumn).Value = phase.Percentage;
currentColumn++;
}
}
private static void CreateHyperaRunning(IXLWorksheet sheet,
Tuple <HyperaRunningNota, HyperaRunningRetorno> hyperaRunning)
{
sheet.Cell("B7").Value = "Hypera";
sheet.Cell("B7").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("C7").Value = "Running";
sheet.Cell("C7").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("D7").Value = hyperaRunning.Item2.QuantidadePendencias;
sheet.Cell("D7").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("E7").Value = hyperaRunning.Item2.DataPrimeiraPendencia;
sheet.Cell("E7").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("F7").Value = hyperaRunning.Item1.QuantidadePendencias;
sheet.Cell("F7").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("G7").Value = hyperaRunning.Item1.DataPrimeiraPendencia;
sheet.Cell("G7").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("H7").Value = "-";
sheet.Cell("H7").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("I7").Value = "-";
sheet.Cell("I7").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
private static void SetRows(IXLWorksheet ws)
{
ws.Cell(10, 1).Value = Model.NameOfMaterial;
ws.Cell(20, 43).Value = Model.NameOfMaterial;
var rowCount = Model.ResultTemperaturesModels.Count;
if (rowCount > 1)
{
ws.Row(20).InsertRowsBelow(rowCount - 1);
ws.Range(20, 43, 19 + rowCount, 44).Merge();
ws.Row(10).InsertRowsBelow(rowCount - 1);
ws.Range(10, 1, 9 + rowCount, 1).Merge();
}
}
public bool ImportExcelData(string oper, string filePath, ref ValidationErrors errors)
{
bool rtn = true;
var targetFile = new FileInfo(filePath);
if (!targetFile.Exists)
{
errors.Add("导入的数据文件不存在");
return(false);
}
var excelFile = new ExcelQueryFactory(filePath);
using (XLWorkbook wb = new XLWorkbook(filePath))
{
//第一个Sheet
using (IXLWorksheet wws = wb.Worksheets.First())
{
//对应列头
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.FeedBillNum, "投料单号（业务）(必输)");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ReleaseBillNum, "投料单号（系统）");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.Department, "投料部门");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.AssemblyPartCode, "总成物料");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.SubAssemblyPartCode, "投料物料(必输)");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.Lot, "批次号(格式：YYYY-MM-DD)");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.FeedQty, "投料数量(必输)");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.BoxQty, "箱数");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.Capacity, "体积");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.InvName, "库房");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.SubInvId, "子库存");
excelFile.AddMapping <WMS_Feed_ListModel>(x => x.Remark, "备注");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.PrintStaus, "打印状态");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.PrintDate, "打印时间");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.PrintMan, "打印人");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ConfirmStatus, "确认状态");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ConfirmMan, "确认人");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ConfirmDate, "确认时间");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.Attr1, "");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.Attr2, "");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.Attr3, "");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.Attr4, "");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.Attr5, "");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.CreatePerson, "创建人");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.CreateTime, "创建时间");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ModifyPerson, "修改人");
//excelFile.AddMapping<WMS_Feed_ListModel>(x => x.ModifyTime, "修改时间");
//SheetName，第一个Sheet
var excelContent = excelFile.Worksheet <WMS_Feed_ListModel>(0);
//开启事务
using (DBContainer db = new DBContainer())
{
var tran = db.Database.BeginTransaction(); //开启事务
int rowIndex = 0;
//检查数据正确性
foreach (var row in excelContent)
{
rowIndex += 1;
string errorMessage = String.Empty;
var model = new WMS_Feed_ListModel();
model.Id = row.Id;
model.FeedBillNum = row.FeedBillNum;
//把系统单据号与业务单据号赋一样的值
model.ReleaseBillNum = row.FeedBillNum;
model.Department = row.Department;
model.AssemblyPartCode = row.AssemblyPartCode;
model.SubAssemblyPartCode = row.SubAssemblyPartCode;
model.FeedQty = row.FeedQty;
model.BoxQty = row.BoxQty;
model.Capacity = row.Capacity;
model.InvName = row.InvName;
model.Lot = row.Lot;
//model.SubInvId = row.SubInvId;
model.Remark = row.Remark;
//默认已打印
model.PrintStaus = "已打印";
//model.PrintDate = row.PrintDate;
//model.PrintMan = row.PrintMan;
//model.ConfirmStatus = row.ConfirmStatus;
//model.ConfirmMan = row.ConfirmMan;
//model.ConfirmDate = row.ConfirmDate;
//model.Attr1 = row.Attr1;
//model.Attr2 = row.Attr2;
//model.Attr3 = row.Attr3;
//model.Attr4 = row.Attr4;
//model.Attr5 = row.Attr5;
//model.CreatePerson = row.CreatePerson;
//model.CreateTime = row.CreateTime;
//model.ModifyPerson = row.ModifyPerson;
//model.ModifyTime = row.ModifyTime;
if (!String.IsNullOrEmpty(errorMessage))
{
rtn = false;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//执行额外的数据校验
try
{
AdditionalCheckExcelData(db, ref model);
}
catch (Exception ex)
{
rtn = false;
errorMessage = ex.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
continue;
}
//写入数据库
WMS_Feed_List entity = new WMS_Feed_List();
entity.Id = model.Id;
entity.FeedBillNum = model.FeedBillNum;
entity.ReleaseBillNum = model.ReleaseBillNum;
//entity.ReleaseBillNum = "TL" + DateTime.Now.ToString("yyyyMMddHHmmssff");打印时生成
entity.Department = model.Department;
entity.AssemblyPartId = model.AssemblyPartId;
entity.SubAssemblyPartId = model.SubAssemblyPartId;
entity.FeedQty = model.FeedQty;
entity.BoxQty = model.BoxQty;
entity.Capacity = model.Capacity;
entity.InvId = model.InvId;
entity.Lot = model.Lot;
//entity.SubInvId = model.SubInvId;
entity.Remark = model.Remark;
entity.PrintStaus = "已打印";
//entity.PrintDate = model.PrintDate;
//entity.PrintMan = model.PrintMan;
entity.ConfirmStatus = "未确认";
//entity.ConfirmMan = model.ConfirmMan;
//entity.ConfirmDate = model.ConfirmDate;
//entity.Attr1 = model.Attr1;
//entity.Attr2 = model.Attr2;
//entity.Attr3 = model.Attr3;
//entity.Attr4 = model.Attr4;
//entity.Attr5 = model.Attr5;
//entity.CreatePerson = model.CreatePerson;
//entity.CreateTime = model.CreateTime;
//entity.ModifyPerson = model.ModifyPerson;
//entity.ModifyTime = model.ModifyTime;
entity.CreatePerson = oper;
entity.CreateTime = DateTime.Now;
entity.ModifyPerson = oper;
entity.ModifyTime = DateTime.Now;
db.WMS_Feed_List.Add(entity);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
rtn = false;
//将当前报错的entity状态改为分离，类似EF的回滚（忽略之前的Add操作）
db.Entry(entity).State = System.Data.Entity.EntityState.Detached;
errorMessage = ex.InnerException.InnerException.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, excelFile.GetColumnNames("Sheet1").Count()).Value = errorMessage;
}
}
if (rtn)
{
tran.Commit(); //必须调用Commit()，不然数据不会保存
}
else
{
tran.Rollback(); //出错就回滚
}
}
}
wb.Save();
}
return(rtn);
}
private static void SetSystemsPhasesHeader(IXLWorksheet ws)
{
var startColumnFirstSystemOrig = 6;
var startColumnFirstSystemCalc = 12;
var countPhases = 5;
var startColumnSecondSystemOrig = 22;
var startColumnSecondSystemCalc = 28;
var firstSystemPhasesCount = Model.FirstSystem.Phases.Count;
var secondSystemPhasesCount = Model.SecondSystem.Phases.Count;
var countToInsert = secondSystemPhasesCount - countPhases;
if (countToInsert > 0)
{
ws.Column(startColumnSecondSystemCalc).InsertColumnsAfter(countToInsert);
ws.Range(7, startColumnSecondSystemCalc, 8, startColumnSecondSystemCalc + countToInsert + countPhases - 1).Merge();
ws.Column(startColumnSecondSystemOrig).InsertColumnsAfter(countToInsert);
ws.Range(7, startColumnSecondSystemOrig, 8, startColumnSecondSystemOrig + countToInsert + countPhases - 1).Merge();
ws.Range(5, 20, 6, 35 + countToInsert + countToInsert).Merge();
}
countToInsert = firstSystemPhasesCount - countPhases;
if (countToInsert > 0)
{
ws.Column(startColumnFirstSystemCalc).InsertColumnsAfter(countToInsert);
ws.Range(7, startColumnFirstSystemCalc, 8, startColumnFirstSystemCalc + countToInsert + countPhases - 1).Merge();
ws.Column(startColumnFirstSystemOrig).InsertColumnsAfter(countToInsert);
ws.Range(7, startColumnFirstSystemOrig, 8, startColumnFirstSystemOrig + countToInsert + countPhases - 1).Merge();
ws.Range(5, 2, 6, 19 + countToInsert + countToInsert).Merge();
}
var firstSystemPhases = Model.FirstSystem.Phases.Select(x => x.Formula);
var secondSystemPhases = Model.SecondSystem.Phases.Select(x => x.Formula);
var currentCol = 6;
foreach (var phase in firstSystemPhases)
{
SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase);
currentCol++;
}
currentCol++;
foreach (var phase in firstSystemPhases)
{
SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase);
currentCol++;
}
currentCol += 5;
foreach (var phase in secondSystemPhases)
{
SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase);
currentCol++;
}
currentCol++;
foreach (var phase in secondSystemPhases)
{
SetChemicalFormula(ws.Cell(9, currentCol).RichText, phase);
currentCol++;
}
}
private static void CreateAstrazeneca(IXLWorksheet sheet,
Tuple <AstrazenecaNota, AstrazenecaRetorno> astrazeneca)
{
sheet.Cell("B3").Value = "Astrazeneca";
sheet.Cell("B3").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("C3").Value = "Fidelize";
sheet.Cell("C3").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
sheet.Cell("D3").Value = astrazeneca.Item2.QuantidadePendencias;
sheet.Cell("D3").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("E3").Value = astrazeneca.Item2.DataPrimeiraPendencia;
sheet.Cell("E3").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("F3").Value = astrazeneca.Item1.QuantidadePendencias;
sheet.Cell("F3").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("G3").Value = astrazeneca.Item1.DataPrimeiraPendencia;
sheet.Cell("G3").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("H3").Value = "-";
sheet.Cell("H3").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
sheet.Cell("I3").Value = "-";
sheet.Cell("I3").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
