/// <summary>
/// A named range
/// </summary>
/// <param name="name">The name</param>
/// <param name="nameSheet">The sheet containing the name. null if its a global name</param>
/// <param name="sheet">Sheet where the address points</param>
/// <param name="address">The address</param>
public ExcelNamedRange(string name, ExcelWorksheet nameSheet , ExcelWorksheet sheet, string address) :
base(sheet, address)
{
Name = name;
_sheet = nameSheet;
}
private void SetMonthData(Models.Student student, ExcelWorksheet worksheet)
{
int m = 0, t = 0, w = 0, th = 0, f = 0;
foreach (var b in student.Behaviors) {
{
switch (b.TimeRecorded.DayOfWeek.ToString()) {
case "Monday": m++;
break;
case "Tuesday": t++;
break;
case "Wednesday": w++;
break;
case "Thursday": th++;
break;
case "Friday": f++;
break;
}
}
worksheet.Cells[2, 5].Value = m;
worksheet.Cells[2, 6].Value = t;
worksheet.Cells[2, 7].Value = w;
worksheet.Cells[2, 8].Value = th;
worksheet.Cells[2, 9].Value = f;
}
}
public RangeDataValidation(ExcelWorksheet worksheet, string address)
{
Require.Argument(worksheet).IsNotNull("worksheet");
Require.Argument(address).IsNotNullOrEmpty("address");
_worksheet = worksheet;
_address = address;
}
private static void ApplyColumnsAutoWidth(ExcelWorksheet ws)
{
int columnCount = ws.CalculateMaxUsedColumns();
for (int i = 0; i < columnCount; i++)
ws.Columns[i].AutoFit();
}
public void Import(ExcelWorksheet sheet, List<EntityMetadata> emds, IOrganizationService service)
{
var rmds = new List<OneToManyRelationshipMetadata>();
foreach (var row in sheet.Rows.Where(r => r.Index != 0).OrderBy(r => r.Index))
{
var rmd = rmds.FirstOrDefault(r => r.MetadataId == new Guid(row.Cells[1].Value.ToString()));
if (rmd == null)
{
var currentEntity = emds.FirstOrDefault(e => e.LogicalName == row.Cells[0].Value.ToString());
if (currentEntity == null)
{
var request = new RetrieveEntityRequest
{
LogicalName = row.Cells[0].Value.ToString(),
EntityFilters = EntityFilters.Relationships
};
var response = ((RetrieveEntityResponse) service.Execute(request));
currentEntity = response.EntityMetadata;
emds.Add(currentEntity);
}
rmd =
currentEntity.OneToManyRelationships.FirstOrDefault(
r => r.SchemaName == row.Cells[2].Value.ToString());
if (rmd == null)
{
rmd =
currentEntity.ManyToOneRelationships.FirstOrDefault(
r => r.SchemaName == row.Cells[2].Value.ToString());
}
rmds.Add(rmd);
}
int columnIndex = 4;
rmd.AssociatedMenuConfiguration.Label = new Label();
while (row.Cells[columnIndex].Value != null)
{
rmd.AssociatedMenuConfiguration.Label.LocalizedLabels.Add(
new LocalizedLabel(row.Cells[columnIndex].Value.ToString(),
int.Parse(sheet.Cells[0, columnIndex].Value.ToString())));
columnIndex++;
}
}
foreach (var rmd in rmds)
{
var request = new UpdateRelationshipRequest
{
Relationship = rmd,
};
service.Execute(request);
}
}
/// <summary>
/// Creates a new instance of the ExcelColumn class.
/// For internal use only!
/// </summary>
/// <param name="Worksheet"></param>
/// <param name="col"></param>
protected internal ExcelColumn(ExcelWorksheet Worksheet, int col)
{
_worksheet = Worksheet;
_columnMin = col;
_columnMax = col;
_width = _worksheet.DefaultColWidth;
}
private void SetHourData(Models.Student student, ExcelWorksheet worksheet)
{
var collector = new System.Collections.Generic.Dictionary<SpreadsheetExport.Key, int>();
foreach (var b in student.Behaviors) {
var key = new SpreadsheetExport.Key { DayOfWeek = b.TimeRecorded.DayOfWeek.ToString(), Hour = b.TimeRecorded.Hour };
if (collector.ContainsKey(key))
collector[key] += 1;
else
collector.Add(key, 1);
}
foreach (var key in collector.Keys) {
var value = key.Hour - 2;
switch (key.DayOfWeek.ToString()) {
case "Monday": worksheet.Cells[5, value].Value = collector[key];
break;
case "Tuesday": worksheet.Cells[6, value].Value = collector[key];
break;
case "Wednesday": worksheet.Cells[7, value].Value = collector[key];
break;
case "Thursday": worksheet.Cells[8, value].Value = collector[key];
break;
case "Friday": worksheet.Cells[9, value].Value = collector[key];
break;
default:
break;
}
}
}
internal ExcelRange(ExcelWorksheet sheet, int fromRow, int fromCol, int toRow, int toCol)
: base(sheet)
{
_fromRow = fromRow;
_fromCol = fromCol;
_toRow = toRow;
_toCol = toCol;
}
/****************************************************************************************/
#region Constructors
/// <summary>
/// Initialize the <see cref="ExcelConditionalFormattingRule"/>
/// </summary>
/// <param name="type"></param>
/// <param name="address"></param>
/// <param name="priority">Used also as the cfRule unique key</param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingRule(
eExcelConditionalFormattingRuleType type,
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
namespaceManager,
itemElementNode)
{
Require.Argument(address).IsNotNull("address");
// While MSDN states that 1 is the "highest priority," it also defines this
// field as W3C XML Schema int, which would allow values less than 1. Excel
// itself will, on occasion, use a value of 0, so this check will allow a 0.
Require.Argument(priority).IsInRange(0, int.MaxValue, "priority");
Require.Argument(worksheet).IsNotNull("worksheet");
_type = type;
_worksheet = worksheet;
SchemaNodeOrder = _worksheet.SchemaNodeOrder;
if (itemElementNode == null)
{
// Create/Get the <cfRule> inside <conditionalFormatting>
itemElementNode = CreateComplexNode(
_worksheet.WorksheetXml.DocumentElement,
string.Format(
"{0}[{1}='{2}']/{1}='{2}'/{3}[{4}='{5}']/{4}='{5}'",
//{0}
ExcelConditionalFormattingConstants.Paths.ConditionalFormatting,
// {1}
ExcelConditionalFormattingConstants.Paths.SqrefAttribute,
// {2}
address.AddressSpaceSeparated, //CF node don't what to have comma between multi addresses, use space instead.
// {3}
ExcelConditionalFormattingConstants.Paths.CfRule,
//{4}
ExcelConditionalFormattingConstants.Paths.PriorityAttribute,
//{5}
priority));
}
// Point to <cfRule>
TopNode = itemElementNode;
Address = address;
Priority = priority;
Type = type;
if (DxfId >= 0)
{
worksheet.Workbook.Styles.Dxfs[DxfId].AllowChange = true; //This Id is referenced by CF, so we can use it when we save.
_style = worksheet.Workbook.Styles.Dxfs[DxfId].Clone(); //Clone, so it can be altered without effecting other dxf styles
}
}
internal ExcelCommentCollection(ExcelPackage pck, ExcelWorksheet ws, XmlNamespaceManager ns)
{
CommentXml = new XmlDocument();
CommentXml.PreserveWhitespace = false;
NameSpaceManager=ns;
Worksheet=ws;
CreateXml(pck);
AddCommentsFromXml();
}
internal ExcelCell(ExcelWorksheet worksheet, string cellAddress)
{
_worksheet = worksheet;
GetRowColFromAddress(cellAddress, out _row, out _col);
if (_col < worksheet._minCol) worksheet._minCol = _col;
if (_col > worksheet._maxCol) worksheet._maxCol = _col;
_sharedFormulaID = int.MinValue;
IsRichText = false;
}
/// <summary>
/// Creates new object of type SheetControl and imports data from ExcelWorksheet if available
/// </summary>
/// <param name="worksheet">Optional. Worksheet to import</param>
public SheetControl(ExcelWorksheet worksheet = null)
{
InitializeComponent();
// Assign argument
this.worksheet = worksheet;
ReloadSheet();
}
internal static DependencyChain Create(ExcelWorksheet ws, string Formula, ExcelCalculationOption options)
{
ws.CheckSheetType();
var depChain = new DependencyChain();
GetChain(depChain, ws.Workbook.FormulaParser.Lexer, ws, Formula, options);
return depChain;
}
/// <summary>
///
/// </summary>
/// <param name="address"></param>
/// <param name="priority"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingThreeColorScale(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.ThreeColorScale,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode == null)
{
// Create the <colorScale> node inside the <cfRule> node
var colorScaleNode = CreateComplexNode(
Node,
ExcelConditionalFormattingConstants.Paths.ColorScale);
// LowValue default
LowValue = new ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition.Low,
eExcelConditionalFormattingValueObjectType.Min,
ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoLowValue),
eExcelConditionalFormattingRuleType.ThreeColorScale,
address,
priority,
worksheet,
NameSpaceManager);
// MiddleValue default
MiddleValue = new ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition.Middle,
eExcelConditionalFormattingValueObjectType.Percent,
ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoMiddleValue),
50,
string.Empty,
eExcelConditionalFormattingRuleType.ThreeColorScale,
address,
priority,
worksheet,
NameSpaceManager);
// HighValue default
HighValue = new ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition.High,
eExcelConditionalFormattingValueObjectType.Max,
ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoHighValue),
eExcelConditionalFormattingRuleType.ThreeColorScale,
address,
priority,
worksheet,
NameSpaceManager);
}
}
private static void GetWorksheetNames(ExcelWorksheet ws, DependencyChain depChain, ExcelCalculationOption options)
{
foreach (var name in ws.Names)
{
if (!string.IsNullOrEmpty(name.NameFormula))
{
GetChain(depChain, ws.Workbook.FormulaParser.Lexer, name, options);
}
}
}
private static void CreateHeader(ExcelWorksheet ws)
{
var headerStyle = CreateHeaderStyle();
for (int i = 0; i <= 4; i++)
{
ws.Cells[0, i].Value = HeaderValues[i];
ws.Cells[0, i].Style = headerStyle;
}
}
public RangeConditionalFormatting(
ExcelWorksheet worksheet,
ExcelAddress address)
{
Require.Argument(worksheet).IsNotNull("worksheet");
Require.Argument(address).IsNotNull("address");
_worksheet = worksheet;
_address = address;
}
private static TableDescriptor GetTableDescriptor(ExcelWorksheet worksheet, ContentLoadType type)
{
var countOfKeys = 2;
if (type.HasFlag(ContentLoadType.Column))
{
countOfKeys++;
}
var descriptor = TableDescriptor.GetReadDynamic(worksheet.Name, new TableKeyDescriptor((new[] {1, 2, 3}).Take(countOfKeys).ToArray()), 2);
return descriptor;
}
public RangeInfo(ExcelWorksheet ws, int fromRow, int fromCol, int toRow, int toCol)
{
_ws = ws;
_fromRow = fromRow;
_fromCol = fromCol;
_toRow = toRow;
_toCol = toCol;
_address = new ExcelAddressBase(_fromRow, _fromCol, _toRow, _toCol);
_address._ws = ws.Name;
_values = new CellsStoreEnumerator<object>(ws._values, _fromRow, _fromCol, _toRow, _toCol);
_cell = new CellInfo(_ws, _values);
}
internal ExcelVmlDrawingCommentCollection(ExcelPackage pck, ExcelWorksheet ws, Uri uri) :
base(pck, ws,uri)
{
if (uri == null)
{
VmlDrawingXml.LoadXml(CreateVmlDrawings());
_drawings = new RangeCollection(new List<IRangeID>());
}
else
{
AddDrawingsFromXml(ws);
}
}
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
internal ExcelConditionalFormattingDuplicateValues(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode)
: this(
address,
priority,
worksheet,
itemElementNode,
null)
{
}
/// <summary>
/// Initialize the <see cref="ExcelConditionalFormattingRule"/>
/// </summary>
/// <param name="type"></param>
/// <param name="address"></param>
/// <param name="priority">Used also as the cfRule unique key</param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingRule(
eExcelConditionalFormattingRuleType type,
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(namespaceManager,
itemElementNode)
{
Require.Argument(address).IsNotNull("address");
Require.Argument(priority).IsInRange(1, int.MaxValue, "priority");
Require.Argument(worksheet).IsNotNull("worksheet");
_type = type;
_worksheet = worksheet;
SchemaNodeOrder = _worksheet.SchemaNodeOrder;
if (itemElementNode == null)
{
// Create/Get the <cfRule> inside <conditionalFormatting>
itemElementNode = CreateComplexNode(
_worksheet.WorksheetXml.DocumentElement,
string.Format(
"{0}[{1}='{2}']/{1}='{2}'/{3}[{4}='{5}']/{4}='{5}'",
//{0}
ExcelConditionalFormattingConstants.Paths.ConditionalFormatting,
// {1}
ExcelConditionalFormattingConstants.Paths.SqrefAttribute,
// {2}
address.Address,
// {3}
ExcelConditionalFormattingConstants.Paths.CfRule,
//{4}
ExcelConditionalFormattingConstants.Paths.PriorityAttribute,
//{5}
priority));
}
// Point to <cfRule>
TopNode = itemElementNode;
Address = address;
Priority = priority;
Type = type;
if (DxfId >= 0)
{
worksheet.Workbook.Styles.Dxfs[DxfId].AllowChange = true; //This Id is referenced by CF, so we can use it when we save.
_style = worksheet.Workbook.Styles.Dxfs[DxfId].Clone(); //Clone, so it can be altered without effecting other dxf styles
}
}
private void SetInitialLabels(Models.Student student, ExcelWorksheet worksheet)
{
worksheet.Cells[0, 0].Value = "Name";
worksheet.Cells[1, 0].Value = student.FirstName + " " + student.LastName;
worksheet.Columns[0].AutoFit();
worksheet.Cells[0, 1].Value = "Behavior";
worksheet.Cells[1, 1].Value = student.BehaviorName;
worksheet.Columns[1].AutoFit();
worksheet.Cells[0, 2].Value = "Teacher";
worksheet.Cells[1, 2].Value = student.Teacher;
worksheet.Columns[2].AutoFit();
worksheet.Cells[1, 4].Value = "Weekly";
worksheet.Cells[2, 4].Value = "Total";
}
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingUniqueValues(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.UniqueValues,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
}
public void op_GetEnumerator()
{
var file = new DirectoryInfo(Environment.CurrentDirectory)
.ToDirectory("Spreadsheets")
.ToFile("NameValue.xls");
var sheet = new ExcelWorksheet(file)
{
Title = "Sheet1$"
};
foreach (var entry in sheet)
{
Assert.Equal("value", entry["name"]);
}
}
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingExpression(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.Expression,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
Formula = string.Empty;
}
internal ExcelVmlDrawingPictureCollection(ExcelPackage pck, ExcelWorksheet ws, Uri uri) :
base(pck, ws, uri)
{
_pck = pck;
_ws = ws;
if (uri == null)
{
VmlDrawingXml.LoadXml(CreateVmlDrawings());
_images = new List<ExcelVmlDrawingPicture>();
}
else
{
AddDrawingsFromXml();
}
}
private static int GetColumnCount(TableDescriptor descriptor, ExcelWorksheet worksheet)
{
var columnCount = descriptor.ColumnCount;
if (!descriptor.DynamicColumnCount)
{
return columnCount;
}
columnCount = 1;
while (!string.IsNullOrEmpty(worksheet.Cell(descriptor.HeaderRow, columnCount).Value))
{
columnCount++;
}
columnCount--;
return columnCount;
}
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingTimePeriodGroup(
eExcelConditionalFormattingRuleType type,
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(type,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
}
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingAboveAverage(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.AboveAverage,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
AboveAverage = true;
EqualAverage = false;
}
protected void AddHeader(ExcelWorksheet sheet, int columnIndex, string headerText)
{
sheet.Cells[1, columnIndex].Value = headerText;
sheet.Cells[1, columnIndex].Style.Font.Bold = true;
}
private void Init()
{
WindowTitle = "SZRFUI20200827";
MessageStr = "";
HomePageVisibility = "Visible";
AlarmReportFormPageVisibility = "Collapsed";
StatusPLC = true;
StatusRobot = true;
StatusTester = true;
BigDataEditIsReadOnly = true;
BigDataPeramEdit = "Edit";
TestStation = Inifile.INIGetStringValue(iniParameterPath, "System", "TestStation", "NA");
Supplier = Inifile.INIGetStringValue(iniParameterPath, "System", "Supplier", "NA");
MachineNumber = Inifile.INIGetStringValue(iniParameterPath, "System", "MachineNumber", "NA");
ProgramName = Inifile.INIGetStringValue(iniParameterPath, "System", "ProgramName", "NA");
WARNVER = Inifile.INIGetStringValue(iniParameterPath, "System", "WARNVER", "NA");
LastBanci = Inifile.INIGetStringValue(iniParameterPath, "Summary", "LastBanci", "null");
h3u.ModbusTCP_Client.StartConnect();
epsonRC90.checkIOReceiveNet();
epsonRC90.IORevAnalysis();
Task.Run(() => { PLCRun(); });
tcpServer.StartTCPListen();
UIRun();
#region 加载报警记录
try
{
using (StreamReader reader = new StreamReader(System.IO.Path.Combine(System.Environment.CurrentDirectory, "AlarmReportForm.json")))
{
string json = reader.ReadToEnd();
AlarmReportForm = JsonConvert.DeserializeObject <ObservableCollection <AlarmReportFormViewModel> >(json);
}
}
catch (Exception ex)
{
AlarmReportForm = new ObservableCollection <AlarmReportFormViewModel>();
WriteToJson(AlarmReportForm, System.IO.Path.Combine(System.Environment.CurrentDirectory, "AlarmReportForm.json"));
AddMessage(ex.Message);
}
#endregion
#region 报警文档
try
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
string alarmExcelPath = Path.Combine(System.Environment.CurrentDirectory, "RF上料机报警.xlsx");
if (File.Exists(alarmExcelPath))
{
FileInfo existingFile = new FileInfo(alarmExcelPath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
// get the first worksheet in the workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
for (int i = 1; i <= worksheet.Dimension.End.Row; i++)
{
AlarmData ad = new AlarmData();
ad.Code = worksheet.Cells["A" + i.ToString()].Value == null ? "Null" : worksheet.Cells["A" + i.ToString()].Value.ToString();
ad.Content = worksheet.Cells["B" + i.ToString()].Value == null ? "Null" : worksheet.Cells["B" + i.ToString()].Value.ToString();
ad.Type = worksheet.Cells["C" + i.ToString()].Value == null ? "Null" : worksheet.Cells["C" + i.ToString()].Value.ToString();
ad.Start = DateTime.Now;
ad.End = DateTime.Now;
ad.State = false;
AlarmList.Add(ad);
}
AddMessage("读取到" + worksheet.Dimension.End.Row.ToString() + "条报警");
}
}
else
{
AddMessage("VPP报警.xlsx 文件不存在");
}
}
catch (Exception ex)
{
AddMessage(ex.Message);
}
#endregion
AddMessage("软件加载完成");
}
private void ButtonStart_Click(object sender, EventArgs e)
{
ExcelTextFormat format = new ExcelTextFormat
{
Delimiter = '\t',
Culture = new CultureInfo(Thread.CurrentThread.CurrentCulture.ToString())
{
DateTimeFormat = { ShortDatePattern = "yyyy-mm-dd" }
},
Encoding = new UTF8Encoding()
};
//read the CSV file from disk
FileInfo file = new FileInfo(textBoxFileName.Text);
//create a new Excel package
using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a WorkSheet
ExcelWorksheet worksheetAll = excelPackage.Workbook.Worksheets.Add("wszystkie");
//load the CSV data into cell A1
worksheetAll.Cells["A1"].LoadFromText(file, format);
int columnsCount = worksheetAll.Dimension.Columns;
List <string> numeryKOntroli = new List <string>();
int rowsCounter = 1;
do
{
numeryKOntroli.Add(worksheetAll.Cells["A" + rowsCounter].Value.ToString());
rowsCounter++;
} while (worksheetAll.Cells["A" + rowsCounter].Value != null);
numeryKOntroli = numeryKOntroli.Distinct().ToList(); // unikalna lista kontroli
foreach (string numerKontroli in numeryKOntroli)
{
ExcelWorksheet arkusz = excelPackage.Workbook.Worksheets.Add(numerKontroli);
int destRow = 1;
rowsCounter = 1;
do
{
if (worksheetAll.Cells[rowsCounter, 1].Value.ToString() == numerKontroli)
{
worksheetAll.Cells[rowsCounter, 1, rowsCounter, columnsCount].Copy(arkusz.Cells[destRow++, 1]);
}
rowsCounter++;
} while (worksheetAll.Cells["A" + rowsCounter].Value != null);
arkusz.Cells.AutoFitColumns();
}
excelPackage.SaveAs(new FileInfo(Path.Combine(Path.GetDirectoryName(textBoxFileName.Text) ?? throw new InvalidOperationException(), Path.GetFileNameWithoutExtension(textBoxFileName.Text) + ".xlsx")));
}
Process.Start(Path.Combine(Path.GetDirectoryName(textBoxFileName.Text) ?? throw new InvalidOperationException(), Path.GetFileNameWithoutExtension(textBoxFileName.Text) + ".xlsx"));
MessageBox.Show("Koniec", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/// <summary>
/// Write object data to XLSX worksheet
/// </summary>
/// <param name="worksheet">Data worksheet</param>
/// <param name="row">Row index</param>
/// <param name="exportImportUseDropdownlistsForAssociatedEntities">Indicating whether need create dropdown list for export</param>
/// <param name="cellOffset">Cell offset</param>
/// <param name="fWorksheet">Filters worksheet</param>
public void WriteToXlsx(ExcelWorksheet worksheet, int row, bool exportImportUseDropdownlistsForAssociatedEntities, int cellOffset = 0, ExcelWorksheet fWorksheet = null)
{
if (CurrentObject == null)
{
return;
}
foreach (var prop in _properties.Values)
{
var cell = worksheet.Cells[row, prop.PropertyOrderPosition + cellOffset];
if (prop.IsDropDownCell)
{
var dropDownElements = prop.GetDropDownElements();
if (!dropDownElements.Any())
{
cell.Value = string.Empty;
continue;
}
cell.Value = prop.GetItemText(prop.GetProperty(CurrentObject));
if (!exportImportUseDropdownlistsForAssociatedEntities)
{
continue;
}
var validator = cell.DataValidation.AddListDataValidation();
validator.AllowBlank = prop.AllowBlank;
if (fWorksheet == null)
{
continue;
}
var fRow = 1;
foreach (var dropDownElement in dropDownElements)
{
var fCell = fWorksheet.Cells[fRow++, prop.PropertyOrderPosition];
if (fCell.Value != null && fCell.Value.ToString() == dropDownElement)
{
break;
}
fCell.Value = dropDownElement;
}
validator.Formula.ExcelFormula = string.Format("{0}!{1}:{2}", fWorksheet.Name, fWorksheet.Cells[1, prop.PropertyOrderPosition].Address, fWorksheet.Cells[dropDownElements.Length, prop.PropertyOrderPosition].Address);
}
else
{
cell.Value = prop.GetProperty(CurrentObject);
}
}
}
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
StepTiming stepTimingFunction = new StepTiming();
stepTimingFunction.JobFileName = programOptions.OutputJobFilePath;
stepTimingFunction.StepName = jobConfiguration.Status.ToString();
stepTimingFunction.StepID = (int)jobConfiguration.Status;
stepTimingFunction.StartTime = DateTime.Now;
stepTimingFunction.NumEntities = jobConfiguration.Target.Count;
this.DisplayJobStepStartingStatus(jobConfiguration);
FilePathMap = new FilePathMap(programOptions, jobConfiguration);
if (this.ShouldExecute(jobConfiguration) == false)
{
return(true);
}
try
{
loggerConsole.Info("Prepare Events and Health Rule Violations Report File");
#region Prepare the report package
// Prepare package
ExcelPackage excelReport = new ExcelPackage();
excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version);
excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Events and Health Rule Violations Report";
excelReport.Workbook.Properties.Subject = programOptions.JobName;
excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To);
#endregion
#region Parameters sheet
// Parameters sheet
ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS);
var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle");
hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single;
hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks);
fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Events and Health Rule Violations Report");
#endregion
#region TOC sheet
// Navigation sheet with link to other sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC);
#endregion
#region Entity sheets and their associated pivots
// Entity sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENTS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EVENTS_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Duration";
sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[3, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENTS_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 7, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENT_DETAILS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EVENT_DETAILS_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENT_DETAILS_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EVENT_DETAILS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_RULE_VIOLATIONS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_RULE_VIOLATIONS_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_RULE_VIOLATIONS_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_RULE_VIOLATIONS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_AUDIT_EVENTS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_AUDIT_EVENTS_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Duration";
sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", AUDIT_SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[3, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_AUDIT_EVENTS_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_AUDIT_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1);
sheet = excelReport.Workbook.Worksheets.Add(AUDIT_SHEET_EVENTS_TIMELINE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_AUDIT_EVENTS);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_NOTIFICATIONS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
#endregion
#region Report file variables
ExcelRangeBase range = null;
ExcelTable table = null;
#endregion
loggerConsole.Info("Fill Events and Health Rule Violations Report File");
#region Controllers
loggerConsole.Info("List of Controllers");
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications
loggerConsole.Info("List of Applications");
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationEventsSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Events
loggerConsole.Info("List of Events");
sheet = excelReport.Workbook.Worksheets[SHEET_EVENTS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationEventsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Event Details
sheet = excelReport.Workbook.Worksheets[SHEET_EVENT_DETAILS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationEventDetailsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Health Rule Violation Events
loggerConsole.Info("List of Health Rule Violation Events");
sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_RULE_VIOLATIONS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationHealthRuleViolationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Audit Events
loggerConsole.Info("List of Audit Events");
sheet = excelReport.Workbook.Worksheets[SHEET_AUDIT_EVENTS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.AuditEventsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Notifications
loggerConsole.Info("List of Notifications");
sheet = excelReport.Workbook.Worksheets[SHEET_NOTIFICATIONS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.NotificationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
loggerConsole.Info("Finalize Events and Health Rule Violations Report File");
#region Controllers sheet
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_CONTROLLERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 25;
sheet.Column(table.Columns["Version"].Position + 1).Width = 15;
}
#endregion
#region Applications
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_APPLICATIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
adjustColumnsOfEntityRowTableInMetricReport(APMApplication.ENTITY_TYPE, sheet, table);
ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEvents"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEvents"].Position + 1);
var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsInfo"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsInfo"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsWarning"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsWarning"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsError"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsError"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolations"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolations"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolationsWarning"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolationsWarning"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolationsCritical"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolationsCritical"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
}
#endregion
#region Events
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_EVENTS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_EVENTS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EventID"].Position + 1).Width = 10;
sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20;
sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["Summary"].Position + 1).Width = 35;
sheet.Column(table.Columns["Type"].Position + 1).Width = 20;
sheet.Column(table.Columns["SubType"].Position + 1).Width = 20;
sheet.Column(table.Columns["TierName"].Position + 1).Width = 20;
sheet.Column(table.Columns["NodeName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTName"].Position + 1).Width = 20;
sheet.Column(table.Columns["TriggeredEntityType"].Position + 1).Width = 20;
sheet.Column(table.Columns["TriggeredEntityName"].Position + 1).Width = 20;
sheet = excelReport.Workbook.Worksheets[SHEET_EVENTS_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_EVENTS_TYPE);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "Type");
addRowFieldToPivot(pivot, "SubType");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addRowFieldToPivot(pivot, "NodeName");
addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending);
addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_EVENTS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet.Column(6).Width = 20;
sheet.Column(7).Width = 20;
sheet = excelReport.Workbook.Worksheets[SHEET_EVENTS_TIMELINE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1], range, PIVOT_EVENTS_TIMELINE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ApplicationName");
addFilterFieldToPivot(pivot, "TierName");
addFilterFieldToPivot(pivot, "BTName");
addFilterFieldToPivot(pivot, "TriggeredEntityName");
addFilterFieldToPivot(pivot, "ApplicationName");
ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]);
fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes);
fieldR.Compact = false;
fieldR.Outline = false;
addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending);
addColumnFieldToPivot(pivot, "Type", eSortType.Ascending);
addColumnFieldToPivot(pivot, "SubType", eSortType.Ascending);
addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count);
chart = sheet.Drawings.AddChart(GRAPH_EVENTS_TIMELINE, eChartType.Line, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
}
#endregion
#region Event Details
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_EVENT_DETAILS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_EVENT_DETAILS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EventID"].Position + 1).Width = 10;
sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20;
sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["Summary"].Position + 1).Width = 35;
sheet.Column(table.Columns["Type"].Position + 1).Width = 20;
sheet.Column(table.Columns["SubType"].Position + 1).Width = 20;
sheet.Column(table.Columns["DetailAction"].Position + 1).Width = 15;
sheet.Column(table.Columns["DetailName"].Position + 1).Width = 15;
sheet.Column(table.Columns["DetailValue"].Position + 1).Width = 25;
sheet = excelReport.Workbook.Worksheets[SHEET_EVENT_DETAILS_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_EVENT_DETAILS_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "Severity");
addFilterFieldToPivot(pivot, "DetailAction");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "Type");
addRowFieldToPivot(pivot, "DetailName");
addColumnFieldToPivot(pivot, "DataType", eSortType.Ascending);
addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_EVENT_DETAILS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
}
#endregion
#region Health Rule Violation Events
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_RULE_VIOLATIONS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_HEALTH_RULE_VIOLATION_EVENTS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EventID"].Position + 1).Width = 10;
sheet.Column(table.Columns["From"].Position + 1).Width = 25;
sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["To"].Position + 1).Width = 25;
sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["HealthRuleName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20;
sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_RULE_VIOLATIONS_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_HEALTH_RULE_VIOLATION_EVENTS_TYPE);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "Status");
addRowFieldToPivot(pivot, "HealthRuleName");
addRowFieldToPivot(pivot, "EntityType");
addRowFieldToPivot(pivot, "EntityName");
addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending);
addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_HEALTH_RULE_VIOLATION_EVENTS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet.Column(6).Width = 20;
}
#endregion
#region Audit Events
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_AUDIT_EVENTS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_AUDIT_EVENTS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["Username"].Position + 1).Width = 20;
sheet.Column(table.Columns["LoginType"].Position + 1).Width = 15;
sheet.Column(table.Columns["Action"].Position + 1).Width = 15;
sheet.Column(table.Columns["EntityName"].Position + 1).Width = 30;
sheet.Column(table.Columns["EntityType"].Position + 1).Width = 20;
sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20;
sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20;
sheet = excelReport.Workbook.Worksheets[SHEET_AUDIT_EVENTS_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_AUDIT_EVENTS_TYPE);
setDefaultPivotTableSettings(pivot);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "Action");
addRowFieldToPivot(pivot, "EntityType");
addRowFieldToPivot(pivot, "EntityName");
addColumnFieldToPivot(pivot, "LoginType", eSortType.Ascending);
addColumnFieldToPivot(pivot, "UserName", eSortType.Ascending);
addDataFieldToPivot(pivot, "EntityID", DataFieldFunctions.Count);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_AUDIT_EVENTS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 30;
sheet = excelReport.Workbook.Worksheets[AUDIT_SHEET_EVENTS_TIMELINE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_AUDIT_EVENTS_TIMELINE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "UserName");
ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]);
fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes);
fieldR.Compact = false;
fieldR.Outline = false;
addColumnFieldToPivot(pivot, "Action", eSortType.Ascending);
addColumnFieldToPivot(pivot, "EntityType", eSortType.Ascending);
addColumnFieldToPivot(pivot, "EntityName", eSortType.Ascending);
addDataFieldToPivot(pivot, "EntityID", DataFieldFunctions.Count);
chart = sheet.Drawings.AddChart(GRAPH_AUDIT_EVENTS_TIMELINE, eChartType.Line, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
}
#endregion
#region Notifications
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_NOTIFICATIONS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_NOTIFICATIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["EventID"].Position + 1).Width = 10;
sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20;
sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["Summary"].Position + 1).Width = 35;
sheet.Column(table.Columns["Type"].Position + 1).Width = 20;
sheet.Column(table.Columns["SubType"].Position + 1).Width = 20;
sheet.Column(table.Columns["TierName"].Position + 1).Width = 20;
sheet.Column(table.Columns["NodeName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTName"].Position + 1).Width = 20;
sheet.Column(table.Columns["TriggeredEntityType"].Position + 1).Width = 20;
sheet.Column(table.Columns["TriggeredEntityName"].Position + 1).Width = 20;
}
#endregion
#region TOC sheet
// TOC sheet again
sheet = excelReport.Workbook.Worksheets[SHEET_TOC];
fillTableOfContentsSheet(sheet, excelReport);
#endregion
#region Save file
FileIOHelper.CreateFolder(FilePathMap.ReportFolderPath());
string reportFilePath = FilePathMap.EventsAndHealthRuleViolationsExcelReportFilePath(jobConfiguration.Input.TimeRange);
logger.Info("Saving Excel report {0}", reportFilePath);
loggerConsole.Info("Saving Excel report {0}", reportFilePath);
try
{
// Save full report Excel files
excelReport.SaveAs(new FileInfo(reportFilePath));
}
catch (InvalidOperationException ex)
{
logger.Warn("Unable to save Excel file {0}", reportFilePath);
logger.Warn(ex);
loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath);
}
#endregion
return(true);
}
catch (Exception ex)
{
logger.Error(ex);
loggerConsole.Error(ex);
return(false);
}
finally
{
stopWatch.Stop();
this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch);
stepTimingFunction.EndTime = DateTime.Now;
stepTimingFunction.Duration = stopWatch.Elapsed;
stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds;
List <StepTiming> stepTimings = new List <StepTiming>(1);
stepTimings.Add(stepTimingFunction);
FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true);
}
}
public string SetAndCheckExcelContent(List <FlowChartImportDetailDTO> detailDTOList, bool isEdit, ExcelWorksheet worksheet, int iRow, int totalRows, FlowChartMasterDTO newMaster, string FlowChart_Version_Comment, CustomUserInfoVM userInfo)
{
string errorInfo = string.Empty;
//内容样式设置
var propertiesContent = FlowchartImportCommon.GetContentColumn();
//获取所有厂别
//不考虑跨厂区权限，他们不会操作只会浏览，默认取第一个,通过三级权限获取四级
var plantAPI = "FlowChart/QueryAllFunctionPlantsAPI?optypeUID=" + userInfo.OrgInfo.First().OPType_OrganizationUID;
//HttpResponseMessage message = APIHelper.APIPostAsync(null,plantAPI);
HttpResponseMessage message = APIHelper.APIGetAsync(plantAPI);
var jsonPlants = message.Content.ReadAsStringAsync().Result;
var functionPlants = JsonConvert.DeserializeObject <List <SystemFunctionPlantDTO> >(jsonPlants);
//从第四行开始读取
iRow = iRow + 2;
//绑定序号 //制程序号 //厂别 //阶层 //WIP数量
int Binding_Seq, Process_Seq, System_FunPlant_UID, Product_Stage, WIP, NullWip, Current_WH_QTY;
//DRI //场地 //工站名稱 //厂别 //颜色 //工站說明 //检测设定 //是否分楼栋 //返工设定 //对应返修站点
string DRI, Place, Process, plantName, Color, Process_Desc, Check_Setting, Location_Flag, Rework_Setting, RelatedRepairBingdingSeq;
//用来测试返工的填写是否正确
List <int> idList = new List <int>();
//这是真正的excel行数，totalRows的行数是错误的
int realTotalRows = 0;
var col = worksheet.SelectedRange[1, 2];
for (var i = iRow; i <= totalRows; i++)
{
//绑定序号
Binding_Seq = Convert.ToInt32(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "绑定序号")].Value);
//制程序号
Process_Seq = Convert.ToInt32(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "制程序号")].Value);
//循环结束,防止有空行
if (Process_Seq == 0)
{
break;
}
DRI = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "DRI")].Value);
if (string.IsNullOrWhiteSpace(DRI))
{
//跳出循环
errorInfo = string.Format("第[{0}]行DRI不能为空", i);
return(errorInfo);
}
Place = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "场地")].Value);
if (string.IsNullOrWhiteSpace(Place))
{
//跳出循环
errorInfo = string.Format("第[{0}]行场地不能为空", i);
return(errorInfo);
}
//接收空白字符,Y,N
Location_Flag = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "是否分楼栋")].Value);
if (string.IsNullOrWhiteSpace(Location_Flag))
{
worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "是否分楼栋")].Value = "N";
}
else if (Location_Flag.ToUpper() != "Y" && Location_Flag.ToUpper() != "N")
{
//跳出循环
errorInfo = string.Format("第[{0}]行是否分楼栋只能为Y,N 或空白字符", i);
return(errorInfo);
}
Process = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "工站名稱")].Value);
if (string.IsNullOrWhiteSpace(Process))
{
//跳出循环
errorInfo = string.Format("第[{0}]行工站名稱不能为空", i);
return(errorInfo);
}
else
{
Process = Process.Trim(); //去掉前后空格
}
plantName = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "厂别")].Value) ?? string.Empty;
if (string.IsNullOrWhiteSpace(plantName))
{
//跳出循环
errorInfo = string.Format("第[{0}]行厂别不能为空", i);
return(errorInfo);
}
var hasItem = functionPlants.FirstOrDefault(m => m.FunPlant.ToLower() == plantName.ToLower().Trim());
if (hasItem != null)
{
System_FunPlant_UID = hasItem.System_FunPlant_UID;
}
else
{
//跳出循环
errorInfo = string.Format("厂别[{0}]输入不正确", plantName);
return(errorInfo);
}
Product_Stage = Convert.ToInt32(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "阶层")].Value);
Color = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "颜色")].Value) ?? string.Empty;
Process_Desc = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "工站說明")].Value);
Rework_Setting = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "返工设定")].Value);
RelatedRepairBingdingSeq = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "对应修复站点")].Value);
WIP = Convert.ToInt32(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "WIP")].Value);
NullWip = Convert.ToInt32(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "不可用WIP")].Value);
Current_WH_QTY = Convert.ToInt32(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "当前库存数")].Value);
string Data_Source = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "数据来源")].Value);
var Is_Synchronous = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "是否同步")].Value);
bool bool_Is_Synchronous = false;
try
{
bool_Is_Synchronous = Convert.ToBoolean(Is_Synchronous == "Y" ? true : false);
}
catch
{
errorInfo = string.Format("第{0}行是否同步[{1}]输入错误,请输入Y或N", i, Is_Synchronous);
return(errorInfo);
}
if (!string.IsNullOrEmpty(Rework_Setting) && Rework_Setting.ToLower() == "rework")
{
if (string.IsNullOrWhiteSpace(RelatedRepairBingdingSeq))
{
errorInfo = string.Format("返工设定为Rework时，第[{0}]行对应修复站点不能为空", i);
return(errorInfo);
}
var ids = RelatedRepairBingdingSeq.Split(',').ToList();
var intIdList = ids.Select <string, int>(x => Convert.ToInt32(x));
//把所有id都添加进队列里面后面一起判断
idList.AddRange(intIdList);
}
//检测站点赋值
var CheckTemp = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, ExcelHelper.GetColumnIndex(propertiesContent, "检测设定")].Value);
if (!string.IsNullOrWhiteSpace(CheckTemp))
{
CheckTemp = CheckTemp.Trim();
}
switch (CheckTemp)
{
case StructConstants.IsQAProcessType.InspectText: //IPQC全检
Check_Setting = StructConstants.IsQAProcessType.InspectKey;
break;
case StructConstants.IsQAProcessType.PollingText: //IPQC巡检
Check_Setting = StructConstants.IsQAProcessType.PollingKey;
break;
case StructConstants.IsQAProcessType.InspectOQCText: //OQC检测
Check_Setting = StructConstants.IsQAProcessType.InspectOQCKey;
break;
case StructConstants.IsQAProcessType.InspectAssembleText: //组装检测
Check_Setting = StructConstants.IsQAProcessType.InspectAssembleKey;
break;
case StructConstants.IsQAProcessType.AssembleOQCText: //组装&OQC检测
Check_Setting = StructConstants.IsQAProcessType.AssembleOQCKey;
break;
default:
Check_Setting = string.Empty;
break;
}
#region 添加FlowChartDetail
FlowChartImportDetailDTO detailDTOItem = new FlowChartImportDetailDTO();
FlowChartDetailDTO newDetailDtoItem = new FlowChartDetailDTO()
{
FlowChart_Master_UID = newMaster.FlowChart_Master_UID,
System_FunPlant_UID = System_FunPlant_UID,
Binding_Seq = Binding_Seq,
Process_Seq = Process_Seq.ToString(),
DRI = DRI,
Place = Place,
Location_Flag = (Location_Flag == "Y" || Location_Flag == "y") ? true : false,
Process = Process,
Product_Stage = Product_Stage,
Color = Color,
Process_Desc = Process_Desc,
FlowChart_Version_Comment = FlowChart_Version_Comment,
Rework_Flag = Rework_Setting,
IsQAProcess = Check_Setting,
Modified_UID = newMaster.Modified_UID,
Modified_Date = newMaster.Modified_Date,
BeginTime = DateTime.Now,
//存到临时表里面的数据所以要加1,存到正式表里面的数据不用加1
FlowChart_Version = isEdit ? newMaster.FlowChart_Version + 1 : newMaster.FlowChart_Version,
WIP_QTY = WIP,
NullWip = NullWip,
Current_WH_QTY = Current_WH_QTY,
Data_Source = Data_Source,
Is_Synchronous = bool_Is_Synchronous
};
if (Rework_Setting == "Rework")
{
newDetailDtoItem.RelatedRepairBindingSeq = RelatedRepairBingdingSeq;
}
else
{
newDetailDtoItem.RelatedRepairBindingSeq = null;
}
detailDTOItem.FlowChartDetailDTO = newDetailDtoItem;
//detailDTOItem.FlowChartMgDataDTO = newMgDataDtoItem;
detailDTOList.Add(detailDTOItem);
#endregion
//行数加1
realTotalRows++;
}
var bindingSeqCount = detailDTOList.GroupBy(m => m.FlowChartDetailDTO.Binding_Seq).Select(m => m.Key).Count();
//var processSeqCount = detailDTOList.GroupBy(m => m.FlowChartDetailDTO.Process_Seq).Select(m => m.Key).Count();
var rowCount = realTotalRows;
if (bindingSeqCount != rowCount)
{
errorInfo = "绑定序号有重复项,请修改";
return(errorInfo);
}
//判断设定的返工信息是否正确
var bindSeqList = detailDTOList.Select(m => m.FlowChartDetailDTO.Binding_Seq).ToList();
var notExistId = idList.Distinct().ToList().Except(bindSeqList).ToList();
if (notExistId.Count() > 0)
{
errorInfo = string.Format("对应修复站点的绑定序号:{0},设定有问题", notExistId.First());
return(errorInfo);
}
return(errorInfo);
}
public void PrepareSubAreas(ExcelWorksheet sheet, IOrganizationService service)
{
OnLog(new LogEventArgs($"Reading {sheet.Name}"));
GetSiteMaps(service);
foreach (var siteMap in siteMaps.Entities)
{
var siteMapDoc = new XmlDocument();
siteMapDoc.LoadXml(siteMap["sitemapxml"].ToString());
var rowsCount = sheet.Dimension.Rows;
var cellsCount = sheet.Dimension.Columns;
for (var rowI = 1; rowI < rowsCount; rowI++)
{
if (ZeroBasedSheet.Cell(sheet, rowI, 0).Value == null)
{
break;
}
if (ZeroBasedSheet.Cell(sheet, rowI, 1).Value.ToString() != siteMap.Id.ToString())
{
continue;
}
if (!siteMapsToTranslate.Contains(siteMap.Id))
{
siteMapsToTranslate.Add(siteMap.Id);
}
var areaId = ZeroBasedSheet.Cell(sheet, rowI, 2).Value.ToString();
var groupId = ZeroBasedSheet.Cell(sheet, rowI, 3).Value.ToString();
var subAreaId = ZeroBasedSheet.Cell(sheet, rowI, 4).Value.ToString();
var subAreaNode = siteMapDoc.SelectSingleNode(
"SiteMap/Area[@Id='" + areaId + "']/Group[@Id='" + groupId + "']/SubArea[@Id='" + subAreaId +
"']");
if (subAreaNode == null)
{
throw new Exception("Unable to find group with id " + groupId + " in area " + areaId);
}
var columnIndex = 6;
while (columnIndex < cellsCount)
{
if (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
var lcid = ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString();
var label = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();
if (ZeroBasedSheet.Cell(sheet, rowI, 5).Value.ToString() == "Title")
{
UpdateXmlNode(subAreaNode, "Titles", "Title", lcid, label);
}
else
{
UpdateXmlNode(subAreaNode, "Descriptions", "Description", lcid, label);
}
}
columnIndex++;
}
}
siteMap["sitemapxml"] = siteMapDoc.OuterXml;
}
}
public ExcelPackage ExportAsExcelInspection(int id)
{
var beehive = this.beehiveService.GetBeehiveById <BeehiveDataExcelExportModel>(id);
var inspection = this.inspectionService.GetAllBeehiveInspections <InspectionDataExcelExportModel>(id).FirstOrDefault();
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report");
ws.Cells["A1"].Value = $"Дата на прегледа:";
ws.Cells["B1"].Value = DateTime.Now.ToString("dd-MM-yyyy");
ws.Cells["A2"].Value = "Вид на прегледа:";
ws.Cells["B2"].Value = this.enumerationMethodsService.GetDisplayName(inspection.InspectionType);
ws.Cells["A3"].Value = "Роил ли се е:";
ws.Cells["B3"].Value = inspection.Swarmed ? "Да" : "Не";
ws.Cells["A4"].Value = "Сила на кошера:";
ws.Cells["B4"].Value = this.enumerationMethodsService.GetDisplayName(inspection.BeehivePower);
ws.Cells["D2"].Value = $"Пчелин №:";
ws.Cells["E2"].Value = $"{beehive.ApiaryNumber}";
ws.Cells["D3"].Value = $"Кошер №:";
ws.Cells["E3"].Value = $"{beehive.Number}";
ws.Cells["A4:B4"].Merge = true;
ws.Cells["A4"].Value = $"Дата: {string.Format("{0:dd-MM-yyyy} {0:H:mm}", DateTimeOffset.Now)}";
ws.Cells["A6:B6"].Merge = true;
ws.Cells["A6:B6"].Style.Font.Bold = true;
ws.Cells["A6:B6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A6"].Value = "Основна информация:";
ws.Cells["A7"].Value = "Предприети действия:";
ws.Cells["B7"].Value = this.enumerationMethodsService.GetDisplayName(inspection.BeeActivity);
ws.Cells["A8"].Value = "Маса на кошера(кг.):";
ws.Cells["B8"].Value = inspection.Weight;
ws.Cells["A9"].Value = "Температура на кошера(t°):";
ws.Cells["B9"].Value = inspection.HiveTemperature;
ws.Cells["A10"].Value = "Влажност на кошера(%):";
ws.Cells["B10"].Value = inspection.HiveHumidity;
if (!inspection.IncludeQueenSection)
{
for (int i = 12; i < 17; i++)
{
ws.Row(i).Hidden = true;
}
}
ws.Cells["A12:B12"].Merge = true;
ws.Cells["A12:B12"].Style.Font.Bold = true;
ws.Cells["A12:B12"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A12"].Value = "Секция майка";
ws.Cells["A13"].Value = "Забелязана майка:";
ws.Cells["B13"].Value = inspection.QueenSeen;
ws.Cells["A14"].Value = "Маточници:";
ws.Cells["B14"].Value = this.enumerationMethodsService.GetDisplayName(inspection.QueenCells);
ws.Cells["A15"].Value = "Работен статус на майката:";
ws.Cells["B15"].Value = this.enumerationMethodsService.GetDisplayName(inspection.QueenWorkingStatus);
ws.Cells["A16"].Value = "Сила на майката:";
ws.Cells["B16"].Value = this.enumerationMethodsService.GetDisplayName(inspection.QueenPowerStatus);
if (!inspection.IncludeBrood)
{
for (int i = 18; i < 22; i++)
{
ws.Row(i).Hidden = true;
}
}
ws.Cells["A18:B18"].Merge = true;
ws.Cells["A18:B18"].Style.Font.Bold = true;
ws.Cells["A18:B18"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A18"].Value = "Секция пило";
ws.Cells["A19"].Value = "Яйца:";
ws.Cells["B19"].Value = inspection.Eggs;
ws.Cells["A20"].Value = "Запечатано:";
ws.Cells["B20"].Value = inspection.ClappedBrood;
ws.Cells["A21"].Value = "Излюпващо се:";
ws.Cells["B21"].Value = inspection.UnclappedBrood;
if (!inspection.IncludeFramesWith)
{
for (int i = 23; i < 28; i++)
{
ws.Row(i).Hidden = true;
}
}
ws.Cells["A23:B23"].Merge = true;
ws.Cells["A23:B23"].Style.Font.Bold = true;
ws.Cells["A23:B23"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A23"].Value = "Секция пити";
ws.Cells["A24"].Value = "С пчели:";
ws.Cells["B24"].Value = inspection.FramesWithBees;
ws.Cells["A25"].Value = "С пило:";
ws.Cells["B25"].Value = inspection.FramesWithBrood;
ws.Cells["A26"].Value = "С мед:";
ws.Cells["B26"].Value = inspection.FramesWithHoney;
ws.Cells["A27"].Value = "С Прашец:";
ws.Cells["B27"].Value = inspection.FramesWithPollen;
if (!inspection.IncludeActivity)
{
for (int i = 29; i < 35; i++)
{
ws.Row(i).Hidden = true;
}
}
ws.Cells["A29:B29"].Merge = true;
ws.Cells["A29:B29"].Style.Font.Bold = true;
ws.Cells["A29:B29"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A29"].Value = "Секция активност";
ws.Cells["A30"].Value = "Активност на пчелите:";
ws.Cells["B30"].Value = this.enumerationMethodsService.GetDisplayName(inspection.BeeActivity);
ws.Cells["A31"].Value = "Ориентационни полети:";
ws.Cells["B31"].Value = this.enumerationMethodsService.GetDisplayName(inspection.OrientationActivity);
ws.Cells["A32"].Value = "Принос на прашец:";
ws.Cells["B32"].Value = this.enumerationMethodsService.GetDisplayName(inspection.PollenActivity);
ws.Cells["A33"].Value = "Принос на мед:";
ws.Cells["B33"].Value = this.enumerationMethodsService.GetDisplayName(inspection.ForragingActivity);
ws.Cells["A34"].Value = "Пчели в минута:";
ws.Cells["B34"].Value = inspection.BeesPerMinute;
if (!inspection.IncludeStorage)
{
for (int i = 36; i < 39; i++)
{
ws.Row(i).Hidden = true;
}
}
ws.Cells["A36:B36"].Merge = true;
ws.Cells["A36:B36"].Style.Font.Bold = true;
ws.Cells["A36:B36"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A36"].Value = "Секция запаси";
ws.Cells["A37"].Value = "От мед:";
ws.Cells["B37"].Value = this.enumerationMethodsService.GetDisplayName(inspection.StoredHoney);
ws.Cells["A38"].Value = "От прашец:";
ws.Cells["B38"].Value = this.enumerationMethodsService.GetDisplayName(inspection.StoredPollen);
if (!inspection.IncludeSpottedProblem)
{
for (int i = 40; i < 45; i++)
{
ws.Row(i).Hidden = true;
}
}
ws.Cells["A40:B40"].Merge = true;
ws.Cells["A40:B40"].Style.Font.Bold = true;
ws.Cells["A40:B40"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A40"].Value = "Секция проблеми";
ws.Cells["A41"].Value = "Проблем:";
ws.Cells["B41"].Value = inspection.Disease;
ws.Cells["A42"].Value = "Третиране с:";
ws.Cells["B42"].Value = inspection.Treatment;
ws.Cells["A43"].Value = "Вредители:";
ws.Cells["B43"].Value = inspection.Pests;
ws.Cells["A44"].Value = "Хищници:";
ws.Cells["B44"].Value = inspection.Predators;
if (!inspection.IncludeWeatherInfo)
{
for (int i = 46; i < 50; i++)
{
ws.Row(i).Hidden = true;
}
}
ws.Cells["A46:B46"].Merge = true;
ws.Cells["A46:B46"].Style.Font.Bold = true;
ws.Cells["A46:B46"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A46"].Value = "Секция метеорологични";
ws.Cells["A47"].Value = "Условия";
ws.Cells["B47"].Value = inspection.Conditions;
ws.Cells["A48"].Value = "Температура(t°):";
ws.Cells["B48"].Value = inspection.WeatherTemperature;
ws.Cells["A49"].Value = "Влажност(%):";
ws.Cells["B49"].Value = inspection.WeatherHumidity;
ws.Cells["A51:B51"].Merge = true;
ws.Cells["A51:B51"].Style.Font.Bold = true;
ws.Cells["A51:B51"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A51"].Value = "Бележка";
ws.Cells["A52:B52"].Merge = true;
ws.Cells["A52:B52"].Style.Font.Bold = true;
ws.Cells["A52:B52"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A52:B52"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A52"].Value = inspection.Note;
ws.Cells["B:B"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells["A:AZ"].AutoFitColumns();
return(pck);
}
/// <summary>
/// Xuất ra file excel
/// </summary>
/// <param name="dsInput">DataSet có data muốn xuất</param>
/// <param name="filename">Tên file</param>
/// <param name="SheetName">Tên Sheet trên file Excel</param>
/// <param name="filetype">Loại file muốn xuất</param>
public static void ToExcel(DataSet dsInput, string filename, string SheetName, FileType filetype)
{
DataTable da = dsInput.Tables[0];
// Create excel file.
ExcelFile ef = new ExcelFile();
string[] ListSheetName = SheetName.Trim().Split(';');
int i = 0;
foreach (DataTable table in dsInput.Tables)
{
ExcelWorksheet ws = ef.Worksheets.Add(ListSheetName[i]);
ws.InsertDataTable(table, 0, 0, true);
i++;
//foreach (ExcelColumn cl in ws.Columns)
//{
// cl.AutoFit();
//}
}
HttpResponse Response = HttpContext.Current.Response;
Response.Clear();
// Stream file to browser, in required type.
switch (filetype.GetStringValue())
{
case "XLS":
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename.Trim());
ef.SaveXls(Response.OutputStream);
break;
case "XLSX":
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename.Replace("XLSX", "XLS").Trim());
ef.SaveXls(Response.OutputStream);
break;
case "CSV":
Response.ContentType = "text/csv";
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename.Trim());
ef.SaveCsv(Response.OutputStream, CsvType.CommaDelimited);
break;
//case "XLSX":
// Response.ContentType = "application/vnd.openxmlformats";
// Response.AddHeader("Content-Disposition", "attachment; filename=" + filename.Trim());
// // With XLSX it is a bit more complicated as MS Packaging API can't write
// // directly to Response.OutputStream. Therefore we use temporary MemoryStream.
// MemoryStream ms = new MemoryStream();
// ef.SaveXlsx(ms);
// ms.WriteTo(Response.OutputStream);
// break;
case "ODS":
Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet";
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename.Trim());
ef.SaveOds(Response.OutputStream);
break;
case "HTML":
Response.ContentType = "text/html";
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename.Trim());
XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, new UTF8Encoding(false));
ef.SaveHtml(writer, null, true);
writer.Close();
break;
}
Response.End();
}
public MainWindowViewModel()
{
#region 初始化参数
Version = "20201011";
MessageStr = "";
HomePageVisibility = "Visible";
AlarmPageVisibility = "Collapsed";
try
{
using (StreamReader reader = new StreamReader(System.IO.Path.Combine(System.Environment.CurrentDirectory, "MachineStateA.json")))
{
string json = reader.ReadToEnd();
MachineStateA = JsonConvert.DeserializeObject <MachineStateViewModel>(json);
}
}
catch (Exception ex)
{
MachineStateA = new MachineStateViewModel()
{
DaiLiao = 0,
YangBen = 0,
TesterAlarm = 0,
Down = 0,
UploaderAlarm = 0,
Run = 0
};
WriteToJson(MachineStateA, System.IO.Path.Combine(System.Environment.CurrentDirectory, "MachineStateA.json"));
AddMessage(ex.Message);
}
AlarmRecord = new ObservableCollection <AlarmRecordViewModel>();
try
{
if (!Directory.Exists(@"D:\报警记录"))
{
Directory.CreateDirectory(@"D:\报警记录");
}
DataTable dt;
Csvfile.csv2dt(System.IO.Path.Combine(@"D:\报警记录", "AlarmRecord" + GetBanci() + ".csv"), 1, out dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
AlarmRecordViewModel newrow = new AlarmRecordViewModel
{
Time = Convert.ToDateTime(dt.Rows[i][0]),
Code = (string)dt.Rows[i][1],
Content = (string)dt.Rows[i][2]
};
AlarmRecord.Add(newrow);
}
}
catch (Exception ex)
{
AddMessage(ex.Message);
}
dispatcherTimer.Interval = new TimeSpan(0, 0, 1);
dispatcherTimer.Tick += DispatcherTimer_Tick;
dispatcherTimer.Start();
LastBanci = Inifile.INIGetStringValue(iniParameterPath, "Summary", "LastBanci", "null");
#endregion
// If you use EPPlus in a noncommercial context
// according to the Polyform Noncommercial license:
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
#region 报警文档
try
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
string alarmExcelPath = Path.Combine(System.Environment.CurrentDirectory, "报警.xlsx");
if (File.Exists(alarmExcelPath))
{
FileInfo existingFile = new FileInfo(alarmExcelPath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
// get the first worksheet in the workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
for (int i = 1; i <= worksheet.Dimension.End.Row; i++)
{
AlarmData ad = new AlarmData();
ad.Code = worksheet.Cells["A" + i.ToString()].Value == null ? "Null" : worksheet.Cells["A" + i.ToString()].Value.ToString();
ad.Content = worksheet.Cells["B" + i.ToString()].Value == null ? "Null" : worksheet.Cells["B" + i.ToString()].Value.ToString();
ad.Type = worksheet.Cells["C" + i.ToString()].Value == null ? "Null" : worksheet.Cells["C" + i.ToString()].Value.ToString();
ad.Start = DateTime.Now;
ad.End = DateTime.Now;
ad.State = false;
AlarmList.Add(ad);
}
AddMessage("读取到" + worksheet.Dimension.End.Row.ToString() + "条报警");
}
}
else
{
AddMessage("VPP报警.xlsx 文件不存在");
}
}
catch (Exception ex)
{
AddMessage(ex.Message);
}
#endregion
AppLoadedEventCommand = new DelegateCommand(new Action(this.AppLoadedEventCommandExecute));
OperateButtonCommand = new DelegateCommand <object>(new Action <object>(this.OperateButtonCommandExecute));
MenuActionCommand = new DelegateCommand <object>(new Action <object>(this.MenuActionCommandExecute));
}
public IActionResult ExportExcel(int billId)
{
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string sFileName = $"Bill_{billId}.xlsx";
// Template File
string templateDocument = Path.Combine(sWebRootFolder, "templates", "BillTemplate.xlsx");
string url = $"{Request.Scheme}://{Request.Host}/{"export-files"}/{sFileName}";
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, "export-files", sFileName));
if (file.Exists)
{
file.Delete();
file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
}
using (FileStream templateDocumentStream = System.IO.File.OpenRead(templateDocument))
{
using (ExcelPackage package = new ExcelPackage(templateDocumentStream))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets["TEDUOrder"];
// Data Acces, load order header data.
var billDetail = _billService.GetDetail(billId);
// Insert customer data into template
worksheet.Cells[4, 1].Value = "Customer Name: " + billDetail.CustomerName;
worksheet.Cells[5, 1].Value = "Address: " + billDetail.CustomerAddress;
worksheet.Cells[6, 1].Value = "Phone: " + billDetail.CustomerMobile;
// Start Row for Detail Rows
int rowIndex = 9;
// load order details
var orderDetails = _billService.GetBillDetails(billId);
int count = 1;
foreach (var orderDetail in orderDetails)
{
// Cell 1, Carton Count
worksheet.Cells[rowIndex, 1].Value = count.ToString();
// Cell 2, Order Number (Outline around columns 2-7 make it look like 1 column)
worksheet.Cells[rowIndex, 2].Value = orderDetail.Product.Name;
// Cell 8, Weight in LBS (convert KG to LBS, and rounding to whole number)
worksheet.Cells[rowIndex, 3].Value = orderDetail.Quantity.ToString();
worksheet.Cells[rowIndex, 4].Value = orderDetail.Price.ToString("N0");
worksheet.Cells[rowIndex, 5].Value = (orderDetail.Price * orderDetail.Quantity).ToString("N0");
// Increment Row Counter
rowIndex++;
count++;
}
decimal total = (decimal)(orderDetails.Sum(x => x.Quantity * x.Price));
worksheet.Cells[24, 5].Value = total.ToString("N0");
var numberWord = "Total amount (by word): " + TextHelper.ToString(total);
worksheet.Cells[26, 1].Value = numberWord;
var billDate = billDetail.DateCreated;
worksheet.Cells[28, 3].Value = billDate.Day + ", " + billDate.Month + ", " + billDate.Year;
package.SaveAs(file); //Save the workbook.
}
}
return(new OkObjectResult(url));
}
static ReportHelper()
{
excelPackage = new ExcelPackage(fileInfo);
worksheet = excelPackage.Workbook.Worksheets.Add("Report");
}
//#EXPORT EXCEL
public void ExportExcel()
{
if (sesion == null)
{
sesion = SessionDB.start(Request, Response, false, db);
}
try
{
System.Data.DataTable tbl = new System.Data.DataTable();
tbl.Columns.Add("Contrato", typeof(string));
tbl.Columns.Add("Sede", typeof(string));
tbl.Columns.Add("Periodo", typeof(string));
tbl.Columns.Add("Esquema", typeof(string));
tbl.Columns.Add("Tipo contrato", typeof(string));
tbl.Columns.Add("No. Pagos", typeof(string));
tbl.Columns.Add("Fec. entrega de contrato", typeof(string));
tbl.Columns.Add("Monto", typeof(string));
tbl.Columns.Add("IVA", typeof(string));
tbl.Columns.Add("IVA Ret", typeof(string));
tbl.Columns.Add("ISR Ret", typeof(string));
List <string> condition = new List <string>();
string IDSIU = sesion.vdata["IDSIU"];
string ID_PERSONA = sesion.vdata["ID_PERSONA"];
condition.Add("ID_PERSONA = '" + ID_PERSONA + "'");
if (Request.Params["Periodo"] != "" && Request.Params["Periodo"] != "null")
{
condition.Add("PERIODO = '" + Request.Params["Periodo"] + "'");
}
if (Request.Params["Sede"] != "" && Request.Params["Sede"] != "null")
{
condition.Add("CVE_SEDE = '" + Request.Params["Sede"] + "'");
}
string TABLECONDICIONSQL = string.Join <string>(" AND ", condition);
string union = "";
if (condition.Count > 0)
{
union = " WHERE ";
}
string sql = "SELECT * FROM VENTREGA_CONTRATOS " + union + " " + TABLECONDICIONSQL;
ResultSet res = db.getTable(sql);
while (res.Next())
{
tbl.Rows.Add(res.Get("CVE_CONTRATO")
, res.Get("CVE_SEDE")
, res.Get("PERIODO")
, res.Get("ESQUEMA")
, res.Get("TIPODEPAGO")
, res.Get("NUMPAGOS")
, res.Get("FECHADECONTRATO")
, res.Get("MONTO")
, res.Get("MONTO_IVA")
, res.Get("MONTO_IVARET")
, res.Get("MONTO_ISRRET"));
}
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Contratos");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true);
//ws.Cells["A1:B1"].AutoFitColumns();
// ws.Column(1).Width = 20;
// ws.Column(2).Width = 80;
//Format the header for column 1-3
using (ExcelRange rng = ws.Cells["A1:K1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
}
//Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//Write it back to the client
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=Contratos.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
}
Log.write(this, "Start", LOG.CONSULTA, "Exporta Excel Contratos", sesion);
}
catch (Exception e)
{
ViewBag.Notification = Notification.Error(e.Message);
Log.write(this, "Start", LOG.ERROR, "Exporta Excel Catalogo Esquemas" + e.Message, sesion);
}
}
public ExcelPackage ExportAsExcelBeehive(string userId, int?id)
{
IEnumerable <BeehiveDataExcelExportModel> beehives =
id.HasValue ?
this.beehiveService.GetBeehivesByApiaryId <BeehiveDataExcelExportModel>(id.Value) :
this.beehiveService.GetAllUserBeehives <BeehiveDataExcelExportModel>(userId);
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report");
ws.Cells["A1:B1"].Merge = true;
ws.Cells["A1"].Value = "Доклад - Кошери";
ws.Cells["A2:B2"].Merge = true;
ws.Cells["A2"].Value = $"Дата: {string.Format("{0:dd-MM-yyyy} {0:H:mm}", DateTimeOffset.Now)}";
ws.Cells["A4"].Value = "Номер на кошера";
ws.Cells["B4"].Value = "Създаден на";
ws.Cells["C4"].Value = "Номер на пчелин";
ws.Cells["D4"].Value = "Подвижен";
ws.Cells["E4"].Value = "Сила";
ws.Cells["F4"].Value = "Система";
ws.Cells["G4"].Value = "Тип";
ws.Cells["H4"].Value = "Прашецоуловител";
ws.Cells["I4"].Value = "Решетка за прополис";
ws.Cells["J4"].Value = "Кралица";
ws.Cells["K4"].Value = "Кралица-Цвят";
ws.Cells["L4"].Value = "Кралица-Дата на придаване";
ws.Cells["M4"].Value = "Кралица-Произход";
ws.Cells["N4"].Value = "Кралица-Вид";
ws.Cells["O4"].Value = "Кралица-Порода";
ws.Cells["P4"].Value = "Кралица-Нрав";
ws.Cells["Q4"].Value = "Кралица-Хигиенни навици";
ws.Cells["A4:Q4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["A4:Q4"].Style.Fill.BackgroundColor.SetColor(1, 183, 225, 205);
ws.Cells["A4:Q4"].Style.Font.Color.SetColor(Color.White);
ws.Cells["A4:Q4"].Style.Font.Bold = true;
int rowIndex = 5;
foreach (var beehive in beehives)
{
ws.Cells[$"A{rowIndex}"].Value = beehive.Number;
ws.Cells[$"B{rowIndex}"].Value = beehive.Date.ToString("dd-MM-yyyy");
ws.Cells[$"C{rowIndex}"].Value = beehive.ApiaryNumber;
ws.Cells[$"D{rowIndex}"].Value = beehive.IsItMovable == true ? "✓ ": string.Empty;
ws.Cells[$"D{rowIndex}"].Style.Font.Bold = true;
ws.Cells[$"E{rowIndex}"].Value = this.enumerationMethodsService.GetDisplayName(beehive.BeehivePower);
ws.Cells[$"F{rowIndex}"].Value = this.enumerationMethodsService.GetDisplayName(beehive.BeehiveSystem);
ws.Cells[$"G{rowIndex}"].Value = this.enumerationMethodsService.GetDisplayName(beehive.BeehiveType);
ws.Cells[$"H{rowIndex}"].Value = beehive.HasPolenCatcher == true ? "Да" : "Не";
ws.Cells[$"I{rowIndex}"].Value = beehive.HasPropolisCatcher == true ? "Да" : "Не";
if (beehive.HasQueen)
{
ws.Cells[$"J{rowIndex}"].Value = beehive.HasQueen ? "Да" : "Не";
ws.Cells[$"K{rowIndex}"].Value = this.enumerationMethodsService.GetDisplayName(beehive.QueenColor);
ws.Cells[$"L{rowIndex}"].Value = beehive.QueenGivingDate.Value.ToString("dd-MM-yyyy");
ws.Cells[$"M{rowIndex}"].Value = beehive.QueenOrigin;
ws.Cells[$"N{rowIndex}"].Value = this.enumerationMethodsService.GetDisplayName(beehive.QueenQueenType);
ws.Cells[$"O{rowIndex}"].Value = this.enumerationMethodsService.GetDisplayName(beehive.QueenBreed);
ws.Cells[$"P{rowIndex}"].Value = beehive.QueenTemperament;
ws.Cells[$"Q{rowIndex}"].Value = beehive.QueenHygenicHabits;
}
rowIndex++;
}
ws.Cells["A:AZ"].AutoFitColumns();
return(pck);
}
public string getMayorizadoExcelFile(ReporteEstadoResultado reporte)
{
string tmpFile = "Mayorizado_" + Utils.Global.GenerateName(10) + ".xlsx";
//@"d:\ShiolFramework\tmp"
DirectoryInfo outputDir = new DirectoryInfo(ShiolConfiguration.Instance.Config.DirPlantillas);
FileInfo newFile = new FileInfo(Path.Combine(outputDir.FullName, tmpFile));
if (newFile.Exists)
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(Path.Combine(outputDir.FullName, tmpFile));
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Mayorizado");
//Add the headers
int col = 1;
int init_row = 4, row = 4;
foreach (Column column in reporte.columns)
{
if (column.Header.ToUpper() != "TODOS")
{
worksheet.Cells[row, col++].Value = column.Header;
}
}
worksheet.Cells[row, col++].Value = "Adjustments \n (+) &(-)";
worksheet.Cells[row, col++].Value = "Consolidado Total";
worksheet.Cells[row, col++].Value = "Bdg 2017 TTD";
worksheet.Cells[row, col++].Value = "Exe 2016 TTD";
worksheet.Cells[row, col++].Value = "Var % Exe vs Bgd 2017";
worksheet.Cells[row, col++].Value = "Var % Exe 2017 vs 2016";
worksheet.Cells[row, col++].Value = "Unit Analysis";
worksheet.Cells[row, col++].Value = "% of Sales";
worksheet.Cells[row, col++].Value = "Unit Analysis";
worksheet.Cells[row, col++].Value = "% of Sales";
/*
* worksheet.Cells[1, col++].Value = "";
* worksheet.Cells[1, col++].Value = "";
* worksheet.Cells[1, col++].Value = "";
* worksheet.Cells[1, col++].Value = "";
*/
//worksheet.Cells["A1:J1"].Style.Fill.PatternType = ExcelFillStyle.LightGray;
worksheet.Cells["A" + row + ":" + "R" + row].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
//ws.Cells["B24"].Style.Font.Bold = true;
//ws.Cells["B24:X24"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
//worksheet.Cells["A4:G4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
//worksheet.Cells["A4:G4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
//worksheet.Cells[startRow, 1, row - 1, 1].StyleName = "HyperLink";
//worksheet.Cells[startRow, 4, row - 1, 6].Style.Numberformat.Format = "[$$-409]#,##0";
//worksheet.Cells[startRow, 7, row - 1, 7].Style.Numberformat.Format = "0%";
//worksheet.Cells[startRow, 7, row - 1, 7].FormulaR1C1 = "=IF(RC[-2]=0,0,RC[-1]/RC[-2])";
int numberWidth = 15;
worksheet.Column(1).Width = 45;
worksheet.Column(2).Width = numberWidth;
worksheet.Column(3).Width = numberWidth;
worksheet.Column(4).Width = numberWidth;
worksheet.Column(5).Width = numberWidth;
worksheet.Column(6).Width = numberWidth;
worksheet.Column(7).Width = numberWidth;
worksheet.Column(8).Width = numberWidth;
worksheet.Column(9).Width = numberWidth;
row++;
foreach (ReporteEstadoResultadoGroup group in reporte.groups)
{
worksheet.Cells["A" + row].Value = group.Descripcion;
// worksheet.Cells[row, 2].Value = group.Todos;
worksheet.Cells[row, 2].Value = group.H1;
worksheet.Cells[row, 3].Value = group.H2;
worksheet.Cells[row, 4].Value = group.H3;
worksheet.Cells[row, 5].Value = group.H4;
worksheet.Cells[row, 6].Value = group.H5;
worksheet.Cells[row, 7].Value = group.H6;
worksheet.Cells[row, 8].Value = group.H7;
worksheet.Cells["B" + row + ":" + "J" + row].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells["A" + row + ":" + "J" + row].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
worksheet.Cells["B" + row + ":" + "J" + row].Style.Font.Bold = true;
//Consolidado
worksheet.Cells["J" + row].Formula = "=SUM(B" + row + ":" + "H" + row + ")";
row++;
foreach (ReporteEstadoResultadoSubGroup sgroup in group.subGroups)
{
worksheet.Cells[row, 1].Value = " " + sgroup.Descripcion;
//worksheet.Cells[row, 2].Value = sgroup.Todos;
worksheet.Cells[row, 2].Value = sgroup.H1;
worksheet.Cells[row, 3].Value = sgroup.H2;
worksheet.Cells[row, 4].Value = sgroup.H3;
worksheet.Cells[row, 5].Value = sgroup.H4;
worksheet.Cells[row, 6].Value = sgroup.H5;
worksheet.Cells[row, 7].Value = sgroup.H6;
worksheet.Cells[row, 8].Value = sgroup.H7;
worksheet.Cells["B" + row + ":" + "J" + row].Style.Numberformat.Format = "#,##0.00";
worksheet.Cells["B" + row + ":" + "J" + row].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
worksheet.Cells["B" + row + ":" + "J" + row].Style.Font.Bold = true;
//Consolidado
worksheet.Cells["J" + row].Formula = "=SUM(B" + row + ":" + "H" + row + ")";
row++;
int rowsg = row;
foreach (ReporteEstadoResultadoSubGroupChild sgroupc in sgroup.subGroupChilds)
{
worksheet.Cells[row, 1].Value = " " + " " + sgroupc.Descripcion;
//worksheet.Cells[row, 2].Value = sgroupc.Todos;
worksheet.Cells[row, 2].Value = sgroupc.H1;
worksheet.Cells[row, 3].Value = sgroupc.H2;
worksheet.Cells[row, 4].Value = sgroupc.H3;
worksheet.Cells[row, 5].Value = sgroupc.H4;
worksheet.Cells[row, 6].Value = sgroupc.H5;
worksheet.Cells[row, 7].Value = sgroupc.H6;
worksheet.Cells[row, 8].Value = sgroupc.H7;
worksheet.Cells["B" + row + ":" + "J" + row].Style.Numberformat.Format = "#,##0.00";
//Consolidado
worksheet.Cells["J" + row].Formula = "=SUM(B" + row + ":" + "H" + row + ")";
row++;
}
worksheet.Cells["B" + rowsg + ":" + "J" + (row - 1)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
}
}
worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells
worksheet.Cells["A" + init_row + ":" + "I" + (row - 1)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
//Add a formula for the value-column
// worksheet.Cells["E2:E4"].Formula = "C2*D2";
/*
* //Ok now format the values;
* using (var range = worksheet.Cells[1, 1, 1, 5])
* {
* range.Style.Font.Bold = true;
* range.Style.Fill.PatternType = ExcelFillStyle.Solid;
* range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
* range.Style.Font.Color.SetColor(Color.White);
* }
*
* worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
* worksheet.Cells["A5:E5"].Style.Font.Bold = true;
*
* worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 3, 4, 3).Address);
* worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
* worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";
*
* //Create an autofilter for the range
* worksheet.Cells["A1:E4"].AutoFilter = true;
*
* worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@"; //Format as text
*
* //There is actually no need to calculate, Excel will do it for you, but in some cases it might be useful.
* //For example if you link to this workbook from another workbook or you will open the workbook in a program that hasn't a calculation engine or
* //you want to use the result of a formula in your program.
* worksheet.Calculate();
*
* worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells
*
* // lets set the header text
* worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
* // add the page number to the footer plus the total number of pages
* worksheet.HeaderFooter.OddFooter.RightAlignedText =
* string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
* // add the sheet name to the footer
* worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
* // add the file path to the footer
* worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
*
* worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
* worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];
*
* // Change the sheet view to show it in page layout mode
* worksheet.View.PageLayoutView = true;
*/
// set some document properties
package.Workbook.Properties.Title = "Mayorizado";
package.Workbook.Properties.Author = "Alvaro Medina";
package.Workbook.Properties.Comments = "Mayorización...";
// set some extended property values
package.Workbook.Properties.Company = "Shiol Sistemas";
// set some custom property values
package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Alvaro Medina");
package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "SHG Sistemas");
// save our new workbook and we are done!
package.Save();
}
return(newFile.FullName);
}
/// <summary>
/// Datatable top Excel
/// </summary>
/// <param name="Baslik">Excel Başlık metni</param>
/// <param name="dt">Datatable</param>
/// <param name="t">Tema</param>
/// <param name="toplam">Footer satırına toplamları alınacak sütunların listesi, null ise footer olmaz</param>
/// <returns></returns>
public byte[] datatableToExcel(string[] Baslik, DataTable dt, Tema t = Tema.Mavi, int[] toplam = null)
{
try
{
byte[] r;
Color arkaplan = Color.FromArgb(219, 229, 241);
switch (t)
{
case Tema.Mavi:
arkaplan = Color.FromArgb(219, 229, 241);
break;
case Tema.Yeşil:
arkaplan = Color.FromArgb(234, 241, 221);
break;
case Tema.Kırmızı:
arkaplan = Color.FromArgb(242, 221, 220);
break;
}
using (ExcelPackage excel = new ExcelPackage())
{
ExcelWorksheet ws = excel.Workbook.Worksheets.Add("Sayfa");
ws.View.ShowGridLines = false;
for (int i = 1; i <= Baslik.Length; i++)
{
ws.Cells[i + 1, 1, i + 1, dt.Columns.Count].Merge = true;
ws.Cells[i + 1, 1].Value = Baslik[i - 1];
ws.Cells[i + 1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//ws.Column(i).Style.HorizontalAlignment = (ExcelHorizontalAlignment)ExcelVerticalAlignment.Center;
ws.Cells[i + 1, 1].Style.Font.Size = 12;
ws.Cells[i + 1, 1].Style.Font.Bold = true;
}//Başlıkları kolon uzunluğu kadar başıkları ekliyoruz.
ws.Cells[1 + Baslik.Length + 1, 1, 1 + Baslik.Length + 1, dt.Columns.Count].Merge = true;
ws.Cells[1 + Baslik.Length + 1, 1].Value = DateTime.Now.ToString("dddd, dd MMMM yyyy hh:mm");
ws.Cells[1 + Baslik.Length + 1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Cells[Baslik.Length + 3, 1].LoadFromDataTable(dt, true);
ws.Row(Baslik.Length + 3).Height = 24;
ws.Row(Baslik.Length + 3).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Row(Baslik.Length + 3).Style.Font.Size = 11;
ws.Row(Baslik.Length + 3).Style.Font.Color.SetColor(Color.Red);
ws.Row(Baslik.Length + 3).Style.Font.Bold = true;
ws.Cells[2, 1, Baslik.Length + 1, dt.Columns.Count].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[2, 1, 1 + Baslik.Length, dt.Columns.Count].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[2, 1, 1 + Baslik.Length, dt.Columns.Count].Style.Fill.BackgroundColor.SetColor(arkaplan);
ws.Cells[Baslik.Length + 3, 1, Baslik.Length + 3, dt.Columns.Count].Style.Border.Top.Style = ExcelBorderStyle.Thin;
ws.Cells[Baslik.Length + 3, 1, Baslik.Length + 3, dt.Columns.Count].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
ws.Cells[1 + Baslik.Length + 1 + dt.Rows.Count + 1, 1, 1 + Baslik.Length + 1 + dt.Rows.Count + 1, dt.Columns.Count].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
ws.Cells[Baslik.Length + 3, 1, Baslik.Length + 3, dt.Columns.Count].Style.Border.Top.Color.SetColor(Color.Black);
ws.Cells[Baslik.Length + 3, 1, Baslik.Length + 3, dt.Columns.Count].Style.Border.Bottom.Color.SetColor(Color.Black);
ws.Cells[1 + Baslik.Length + 1 + dt.Rows.Count + 1, 1, 1 + Baslik.Length + 1 + dt.Rows.Count + 1, dt.Columns.Count].Style.Border.Bottom.Color.SetColor(Color.Black);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i % 2 == 0)
{
ws.Cells[Baslik.Length + 4 + i, 1, Baslik.Length + 4 + i, dt.Columns.Count].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[Baslik.Length + 4 + i, 1, Baslik.Length + 4 + i, dt.Columns.Count].Style.Fill.BackgroundColor.SetColor(arkaplan);
}
}//dataTable'ın arkaplan renginin uygulama bölümü
for (int i = 1; i <= dt.Columns.Count; i++)
{
ws.Column(i).AutoFit(8.32);
ws.Column(i).Style.WrapText = false;
if (dt.Columns[i - 1].DataType == typeof(int))
{
ws.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
if (dt.Columns[i - 1].DataType == typeof(double) || dt.Columns[i - 1].DataType == typeof(decimal))
{
ws.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Column(i).Style.Numberformat.Format = "#,##0.00";
}
}//integer değerlerin ortalanıp, double ve decimal değerlerinin sağa yaslanıp formatlanma bölümü
if (toplam != null)
{
Array.Sort(toplam);//Diziyi küçükten büyüğe doğru sıraladık.
if (toplam[0] != 1)
{
ws.Cells[1 + Baslik.Length + 1 + dt.Rows.Count + 2, 1, 1 + Baslik.Length + 1 + dt.Rows.Count + 2, toplam[0] - 1].Merge = true;
ws.Cells[1 + Baslik.Length + 1 + dt.Rows.Count + 2, 1, 1 + Baslik.Length + 1 + dt.Rows.Count + 2, 1].Value = "TOPLAM";
ws.Cells[1 + Baslik.Length + 1 + dt.Rows.Count + 2, 1, 1 + Baslik.Length + 1 + dt.Rows.Count + 2, 1].Style.Font.Bold = true;
ws.Cells[1 + Baslik.Length + 1 + dt.Rows.Count + 2, 1, 1 + Baslik.Length + 1 + dt.Rows.Count + 2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
for (int i = 0; i < toplam.Count(); i++)
{
double sum = 0;
for (int j = 0; j < dt.Rows.Count; j++)
{
if (dt.Columns[toplam[i] - 1].DataType == typeof(double) || dt.Columns[toplam[i] - 1].DataType == typeof(decimal))
{
sum += Convert.ToDouble(dt.Rows[j][toplam[i] - 1].ToString());
}
}
ws.Cells[1 + Baslik.Length + 1 + dt.Rows.Count + 2, toplam[i], 1 + Baslik.Length + 1 + dt.Rows.Count + 2, toplam[i]].Value = sum;
}
}//toplam satırını oluşturulup hesaplamala bölümü
r = excel.GetAsByteArray();
}
return(r);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="worksheet"></param>
/// <param name="address"></param>
/// <param name="validationType"></param>
internal ExcelDataValidationInt(ExcelWorksheet worksheet, string address, ExcelDataValidationType validationType)
: base(worksheet, address, validationType)
{
Formula = new ExcelDataValidationFormulaInt(worksheet.NameSpaceManager, TopNode, _formula1Path);
Formula2 = new ExcelDataValidationFormulaInt(worksheet.NameSpaceManager, TopNode, _formula2Path);
}
/// <summary>
/// If null return DateTime.Today
/// </summary>
/// <param name="workSheet"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="isText"></param>
/// <returns></returns>
protected DateTime GetCellValue_DateTime_Full(ExcelWorksheet workSheet, int row, int col, bool isText = true, string format = "")
{
var date = GetCellValue_DateTime(workSheet, row, col, isText, format);
return(date.HasValue ? date.Value : DateTime.Today);
}
public bool UpdateDataInSheet(string fullFileName, string sheetName, DataTable dt)
{
// nếu đường dẫn null hoặc rỗng thì báo không hợp lệ và return hàm
if (string.IsNullOrEmpty(fullFileName))
{
return(false);
}
try
{
using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo(fullFileName)))
{
// đặt tên người tạo file
excelPackage.Workbook.Properties.Author = "VCC2";
// đặt tiêu đề cho file
excelPackage.Workbook.Properties.Title = "VCC2";
ExcelWorksheet _xlWorkSheet = excelPackage.Workbook.Worksheets.FirstOrDefault(x => x.Name == sheetName);
if (_xlWorkSheet == null)
{
_xlWorkSheet = excelPackage.Workbook.Worksheets.Add(sheetName);
}
else
{
//_xlWorkSheet.Cells.Clear();
//DeleteColumns(_xlWorkSheet, 1, _xlWorkSheet.Dimension.End.Column);
DeleteRows(_xlWorkSheet, 2, _xlWorkSheet.Dimension.End.Row);
}
_xlWorkSheet.Cells["A1"].LoadFromDataTable(dt, true);
//Autofit with minimum size for the column.
double minimumSize = 5;
double maximumSize = 50;
//_xlWorkSheet.Protection.AllowSelectLockedCells
//_xlWorkSheet.Select("A1");
_xlWorkSheet.Cells[_xlWorkSheet.Dimension.Address].AutoFitColumns(minimumSize, maximumSize);
_xlWorkSheet.Cells[_xlWorkSheet.Dimension.Address].Style.WrapText = true;
_xlWorkSheet.Cells[_xlWorkSheet.Dimension.Address].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
_xlWorkSheet.Cells[_xlWorkSheet.Dimension.Address].Style.Border.Top.Style = ExcelBorderStyle.Thin;
_xlWorkSheet.Cells[_xlWorkSheet.Dimension.Address].Style.Border.Right.Style = ExcelBorderStyle.Thin;
_xlWorkSheet.Cells[_xlWorkSheet.Dimension.Address].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
_xlWorkSheet.Cells[_xlWorkSheet.Dimension.Address].Style.Border.Left.Style = ExcelBorderStyle.Thin;
_xlWorkSheet.Cells[_xlWorkSheet.Dimension.Address].AutoFilter = true;
//fill row 1 with striped orange background
_xlWorkSheet.Cells[1, 1, 1, _xlWorkSheet.Dimension.End.Column].Style.Fill.PatternType = ExcelFillStyle.Solid;
_xlWorkSheet.Cells[1, 1, 1, _xlWorkSheet.Dimension.End.Column].Style.Fill.BackgroundColor.SetColor(Color.LightGreen);
_xlWorkSheet.Cells[1, 1, 1, _xlWorkSheet.Dimension.End.Column].Style.Font.Bold = true;
_xlWorkSheet.Cells[1, 1, 1, _xlWorkSheet.Dimension.End.Column].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
excelPackage.Save();
return(true);
}
}
catch (DbEntityValidationException ex)
{
string description = "";
foreach (var eve in ex.EntityValidationErrors)
{
Trace.WriteLine($"Entity of type \"{eve.Entry.Entity.GetType().Name}\" in state \"{eve.Entry.State}\" has the following validation error.");
description += ($"Entity of type \"{eve.Entry.Entity.GetType().Name}\" in state \"{eve.Entry.State}\" has the following validation error.\n");
foreach (var ve in eve.ValidationErrors)
{
Trace.WriteLine($"- Property: \"{ve.PropertyName}\", Error: \"{ve.ErrorMessage}\"");
description += ($"- Property: \"{ve.PropertyName}\", Error: \"{ve.ErrorMessage}\"\n");
}
}
LogError(ex, description);
Trace.WriteLine(ex.ToString());
throw;
}
catch (Exception ex)
{
LogError(ex);
Trace.WriteLine(ex.ToString());
throw;
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="worksheet"></param>
/// <param name="address"></param>
/// <param name="validationType"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager">For test purposes</param>
internal ExcelDataValidationInt(ExcelWorksheet worksheet, string address, ExcelDataValidationType validationType, XmlNode itemElementNode, XmlNamespaceManager namespaceManager)
: base(worksheet, address, validationType, itemElementNode, namespaceManager)
{
Formula = new ExcelDataValidationFormulaInt(NameSpaceManager, TopNode, _formula1Path);
Formula2 = new ExcelDataValidationFormulaInt(NameSpaceManager, TopNode, _formula2Path);
}
private static void StyleColumnChart(ExcelWorksheet ws, eBarChartType chartType)
{
//Style 1
AddBar(ws, chartType, "ColumnChartStyle1", 0, 5, ePresetChartStyle.ColumnChartStyle1,
c =>
{
c.Legend.Position = eLegendPosition.Bottom;
c.Axis[0].MajorTickMark = eAxisTickMark.None;
c.Axis[0].MinorTickMark = eAxisTickMark.None;
c.Axis[1].MajorTickMark = eAxisTickMark.None;
c.Axis[1].MinorTickMark = eAxisTickMark.None;
c.GapWidth = 219;
c.Overlap = -27;
});
//Style 2
AddBar(ws, chartType, "ColumnChartStyle2", 0, 18, ePresetChartStyle.ColumnChartStyle2,
c =>
{
c.Legend.Position = eLegendPosition.Top;
});
//Style 3
var chart3 = AddBar(ws, chartType, "ColumnChartStyle3", 0, 31, ePresetChartStyle.ColumnChartStyle3,
c =>
{
c.DataLabel.ShowPercent = true;
c.DataLabel.Position = eLabelPosition.Center;
});
//Style 4
AddBar(ws, chartType, "ColumnChartStyle4", 22, 5, ePresetChartStyle.ColumnChartStyle4,
c =>
{
c.Legend.Position = eLegendPosition.Bottom;
});
//Style 5
AddBar(ws, chartType, "ColumnChartStyle5", 22, 18, ePresetChartStyle.ColumnChartStyle5,
c =>
{
c.Legend.Position = eLegendPosition.Bottom;
});
//Style 6
AddBar(ws, chartType, "ColumnChartStyle6", 22, 31, ePresetChartStyle.ColumnChartStyle6,
c =>
{
});
//Style 7
AddBar(ws, chartType, "ColumnChartStyle7", 44, 5, ePresetChartStyle.ColumnChartStyle7,
c =>
{
c.Legend.Position = eLegendPosition.Bottom;
});
//Style 8
AddBar(ws, chartType, "ColumnChartStyle8", 44, 18, ePresetChartStyle.ColumnChartStyle8,
c =>
{
c.Legend.Position = eLegendPosition.Bottom;
});
//Style 9
AddBar(ws, chartType, "ColumnChartStyle9", 44, 31, ePresetChartStyle.ColumnChartStyle9,
c =>
{
});
//Style 10
AddBar(ws, chartType, "ColumnChartStyle10", 66, 5, ePresetChartStyle.ColumnChartStyle10,
c =>
{
c.Legend.Position = eLegendPosition.Bottom;
});
//Style 11
AddBar(ws, chartType, "ColumnChartStyle11", 66, 18, ePresetChartStyle.ColumnChartStyle11,
c =>
{
c.Legend.Position = eLegendPosition.Bottom;
});
//Style 12
AddBar(ws, chartType, "ColumnChartStyle12", 66, 31, ePresetChartStyle.ColumnChartStyle12,
c =>
{
});
//Style 13
AddBar(ws, chartType, "ColumnChartStyle13", 88, 5, ePresetChartStyle.ColumnChartStyle13,
c =>
{
c.Legend.Position = eLegendPosition.Bottom;
});
//Style 14
AddBar(ws, chartType, "ColumnChartStyle14", 88, 18, ePresetChartStyle.ColumnChartStyle14,
c =>
{
c.Legend.Position = eLegendPosition.Bottom;
});
//Style 15
AddBar(ws, chartType, "ColumnChartStyle15", 88, 31, ePresetChartStyle.ColumnChartStyle15,
c =>
{
});
//Style 16
AddBar(ws, chartType, "ColumnChartStyle16", 110, 5, ePresetChartStyle.ColumnChartStyle16,
c =>
{
});
}
static void Main(string[] args)
{
#region Excel Input
FileInfo InputFile = new FileInfo(@"D:\InputPLASH.xlsx");
List <double> InputPrecipUp = new List <double>();
List <double> InputPrecipDown = new List <double>();
List <double> InputQObs = new List <double>();
List <double> InputEvap = new List <double>();
using (ExcelPackage package = new ExcelPackage(InputFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int ColCount = worksheet.Dimension.End.Column;
int RowCount = worksheet.Dimension.End.Row;
for (int row = 2; row <= RowCount; row++)
{
InputPrecipUp.Add(Convert.ToDouble(worksheet.Cells[row, 2].Value));
InputPrecipDown.Add(Convert.ToDouble(worksheet.Cells[row, 3].Value));
InputQObs.Add(Convert.ToDouble(worksheet.Cells[row, 4].Value));
InputEvap.Add(Convert.ToDouble(worksheet.Cells[row, 5].Value));
//Console.WriteLine("PrecipUp: {0}, PrecipDown: {1}, Evap: {2}, QObos: {3}", Math.Round(InputPrecipUp[row - 2],3), Math.Round(InputPrecipDown[row - 2], 3), Math.Round(InputQObs[row - 2],3), Math.Round(InputEvap[row - 2],3));
}
}
#endregion Excel Input
#region PLASH Simulation
#region Genetic Algorithm
int SimulationLength = InputPrecipUp.Count;
double Timestep = 24;
var chromosome = new FloatingPointChromosome(
new double[] { 0, 0, 2, 24, 0, //Param Upstream
0, 0, 0, 120, 6,
0, 0, 0, 0,
0, 0, 0, 0, //Initial Upstream
0, 0, 2, 24, 0, //Param Downstream
0, 0, 0, 120, 6,
0, 0, 0, 0,
0, 0, 0, 0, //Initial Downstream
12, 0.01 //Param Muskingum
},
new double[] {
10, 10, 10, 240, 300,
0.5, 0.5, 10, 3600, 120,
3, 500, 1, 1,
10, 10, 10, 10,
10, 10, 10, 240, 300,
0.5, 0.5, 10, 3600, 120,
20, 200, 1, 1,
10, 10, 10, 10,
180, 0.5
},
new int[] { 64, 64, 64, 64, 64,
64, 64, 64, 64, 64,
64, 64, 64, 64,
64, 64, 64, 64,
64, 64, 64, 64, 64,
64, 64, 64, 64, 64,
64, 64, 64, 64,
64, 64, 64, 64,
64, 64 },
new int[] { 3, 3, 3, 3, 3,
3, 3, 3, 3, 3,
3, 3, 3, 3,
3, 3, 3, 3,
3, 3, 3, 3, 3,
3, 3, 3, 3, 3,
3, 3, 3, 3,
3, 3, 3, 3,
3, 3 });
var population = new Population(50, 100, chromosome);
var fitness = new FuncFitness((c) =>
{
var fc = c as FloatingPointChromosome;
var values = fc.ToFloatingPoints();
if (values[12] < values[13] || values[30] < values[31])
{
return(double.NegativeInfinity);
}
DateTime[] TimeSeries = new DateTime[SimulationLength];
TimeSeries[0] = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
for (int i = 1; i < TimeSeries.Length; i++)
{
TimeSeries[i] = TimeSeries[0].AddHours(Timestep * i);
}
PLASHInput InputUp = new PLASHInput
{
DTE_Arr_TimeSeries = TimeSeries,
FLT_Arr_PrecipSeries = InputPrecipUp.ToArray(),
FLT_Arr_EPSeries = InputEvap.ToArray(),
FLT_Arr_QtObsSeries = InputQObs.ToArray(),
FLT_Arr_QtUpstream = new double[SimulationLength]
};
PLASHParameters ParamUp = new PLASHParameters
{
FLT_AD = 861.42252,
FLT_AI = 0.02,
FLT_AP = 0.95,
FLT_TimeStep = 24,
FLT_DI = values[0],
FLT_IP = values[1],
FLT_DP = values[2],
FLT_KSup = values[3],
FLT_CS = values[4],
FLT_CC = values[5],
FLT_CR = values[6],
FLT_PP = values[7],
FLT_KSub = values[8],
FLT_KCan = values[9],
FLT_CH = values[10],
FLT_FS = values[11],
FLT_PS = values[12],
FLT_UI = values[13]
};
PLASHInitialConditions Res0Up = new PLASHInitialConditions()
{
RImp0 = values[14],
RInt0 = values[15],
RSup0 = values[16],
RCan0 = values[17]
};
PLASHReservoir ReservoirUp = new PLASHReservoir();
PLASHOutput OutputUp = new PLASHOutput();
PLASH.Run(InputUp, ParamUp, Res0Up, ReservoirUp, OutputUp);
Muskingum Musk = new Muskingum()
{
FLT_K = values[36],
FLT_X = values[37],
FLT_Timestep = Timestep,
FLT_Arr_InputFlow = OutputUp.FLT_Arr_Qt_Calc
};
PLASHInput InputDown = new PLASHInput()
{
DTE_Arr_TimeSeries = TimeSeries,
FLT_Arr_PrecipSeries = InputPrecipDown.ToArray(),
FLT_Arr_EPSeries = InputEvap.ToArray(),
FLT_Arr_QtObsSeries = InputQObs.ToArray(),
FLT_Arr_QtUpstream = Muskingum.ProcessDamping(Musk)
};
PLASHParameters ParamDown = new PLASHParameters
{
FLT_AD = 727.8917, //Watershed Area (km2)
FLT_AI = 0.02, //Impervious Area Fraction (km2/km2)
FLT_AP = 0.95, //Pervious Area Fraction (km2/km2)
FLT_TimeStep = 24,
FLT_DI = values[18], //Maximum Impervious Detention (mm)
FLT_IP = values[19], //Maximum Interception (mm)
FLT_DP = values[20], //Maximum Pervious Detention (mm)
FLT_KSup = values[21], //Surface Reservoir Decay (h)
FLT_CS = values[22], //Soil Saturation Capacity (mm)
FLT_CC = values[23], //Field Capacity (%)
FLT_CR = values[24], //Recharge Capacity (%)
FLT_PP = values[25], //Deep Percolation (mm/h)
FLT_KSub = values[26], //Aquifer Reservoir Decay (d)
FLT_KCan = values[27], //Channel Reservoir Decay (h)
FLT_CH = values[28], //Hydraulic Conductivity (mm/h)
FLT_FS = values[29], //Soil Capilarity Factor (mm)
FLT_PS = values[30], //Soil Porosity (cm3/cm3)
FLT_UI = values[31] //Initial Moisture (cm3/cm3)
};
PLASHInitialConditions Res0Down = new PLASHInitialConditions()
{
RImp0 = values[32],
RInt0 = values[33],
RSup0 = values[34],
RCan0 = values[35]
};
PLASHReservoir ReservoirDown = new PLASHReservoir();
PLASHOutput OutputDown = new PLASHOutput();
PLASH.Run(InputDown, ParamDown, Res0Down, ReservoirDown, OutputDown);
if (ReservoirDown.FLT_Arr_ESSup.Sum() < 30 || ReservoirUp.FLT_Arr_ESSup.Sum() < 30)
{
return(double.NegativeInfinity);
}
//double objectiveNashSut = 1;
//double MeanSimulatedFlow = OutputDown.FLT_Arr_Qt_Calc.Average();
//double NashSutUpper = 0;
//double NashSutLower = 0;
//for(int i = 0; i < OutputDown.FLT_Arr_Qt_Calc.Length; i++)
//{
// NashSutUpper += Math.Pow(OutputDown.FLT_Arr_Qt_Calc[i] - InputDown.FLT_Arr_QtObsSeries[i], 2);
// NashSutLower += Math.Pow(InputDown.FLT_Arr_QtObsSeries[i] - MeanSimulatedFlow, 2);
//}
//objectiveNashSut -= (NashSutUpper / NashSutLower);
double objectiveSquareSum = 0;
for (int i = 0; i < OutputDown.FLT_Arr_Qt_Calc.Length; i++)
{
objectiveSquareSum += Math.Pow(OutputDown.FLT_Arr_Qt_Calc[i] - InputDown.FLT_Arr_QtObsSeries[i], 2);
}
//double objectiveAbsSum = 0;
//for(int i = 0; i < OutputDown.FLT_Arr_Qt_Calc.Length; i++)
//{
// objectiveAbsSum += Math.Abs(OutputDown.FLT_Arr_Qt_Calc[i] - InputDown.FLT_Arr_QtObsSeries[i]);
//}
//return objectiveAbsSum * -1;
return(objectiveSquareSum * -1);
//return objectiveNashSut;
});
var selection = new EliteSelection();
var crossover = new UniformCrossover(0.3f);
var mutation = new FlipBitMutation();
var termination = new FitnessStagnationTermination(250);
var ga = new GeneticAlgorithm(
population,
fitness,
selection,
crossover,
mutation);
ga.Termination = termination;
//Console.WriteLine("Generation: (x1, y1), (x2, y2) = distance");
Console.WriteLine("Genetic algorithm tests");
var latestFitness = 0.0;
ga.GenerationRan += (sender, e) =>
{
var bestChromosome = ga.BestChromosome as FloatingPointChromosome;
var bestFitness = bestChromosome.Fitness.Value;
if (bestFitness != latestFitness)
{
latestFitness = bestFitness;
var phenotype = bestChromosome.ToFloatingPoints();
Console.WriteLine("Generation {0}: {1}",
ga.GenerationsNumber,
bestFitness);
//Console.WriteLine(
// "Generation {0,2}: ({1},{2}),({3},{4}) = {5}",
// ga.GenerationsNumber,
// phenotype[0],
// phenotype[1],
// phenotype[2],
// phenotype[3],
// bestFitness
//);
}
};
ga.Start();
Console.WriteLine("GA Over!");
#endregion Genetic Algorithm
var bestChrom = ga.BestChromosome as FloatingPointChromosome;
var bestVal = bestChrom.ToFloatingPoints();
PLASHInput InputUpstream = new PLASHInput()
{
DTE_Arr_TimeSeries = new DateTime[SimulationLength],
FLT_Arr_PrecipSeries = InputPrecipUp.ToArray(),
FLT_Arr_EPSeries = InputEvap.ToArray(),
FLT_Arr_QtObsSeries = InputQObs.ToArray(),
FLT_Arr_QtUpstream = new double[SimulationLength]
};
PLASHParameters ParametersUpstream = new PLASHParameters()
{
FLT_AD = 861.42252, //Watershed Area (km2)
FLT_AI = 0.02, //Impervious Area Fraction (km2/km2)
FLT_AP = 0.95, //Pervious Area Fraction (km2/km2)
FLT_TimeStep = 24,
//Parameters
FLT_DI = bestVal[0], //Maximum Impervious Detention (mm)
FLT_IP = bestVal[1], //Maximum Interception (mm)
FLT_DP = bestVal[2], //Maximum Pervious Detention (mm)
FLT_KSup = bestVal[3], //Surface Reservoir Decay (h)
FLT_CS = bestVal[4], //Soil Saturation Capacity (mm)
FLT_CC = bestVal[5], //Field Capacity (%)
FLT_CR = bestVal[6], //Recharge Capacity (%)
FLT_PP = bestVal[7], //Deep Percolation (mm/h)
FLT_KSub = bestVal[8], //Aquifer Reservoir Decay (d)
FLT_KCan = bestVal[9], //Channel Reservoir Decay (h)
FLT_CH = bestVal[10], //Hydraulic Conductivity (mm/h)
FLT_FS = bestVal[11], //Soil Capilarity Factor (mm)
FLT_PS = bestVal[12], //Soil Porosity (cm3/cm3)
FLT_UI = bestVal[13] //Initial Moisture (cm3/cm3)
};
InputUpstream.DTE_Arr_TimeSeries[0] = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
for (int i = 1; i < InputUpstream.DTE_Arr_TimeSeries.Length; i++)
{
InputUpstream.DTE_Arr_TimeSeries[i] = InputUpstream.DTE_Arr_TimeSeries[0].AddHours(ParametersUpstream.FLT_TimeStep * i);
}
PLASHReservoir ReservoirUpstream = new PLASHReservoir();
PLASHInitialConditions InitialUpstream = new PLASHInitialConditions()
{
RImp0 = bestVal[14],
RInt0 = bestVal[15],
RSup0 = bestVal[16],
RCan0 = bestVal[17]
};
PLASHOutput OutputUpstream = new PLASHOutput();
PLASH.Run(InputUpstream, ParametersUpstream, InitialUpstream, ReservoirUpstream, OutputUpstream);
PLASHInput InputDownstream = new PLASHInput()
{
DTE_Arr_TimeSeries = new DateTime[SimulationLength],
FLT_Arr_PrecipSeries = InputPrecipDown.ToArray(),
FLT_Arr_EPSeries = InputEvap.ToArray(),
FLT_Arr_QtObsSeries = InputQObs.ToArray()
};
PLASHParameters ParametersDownstream = new PLASHParameters()
{
FLT_AD = 727.8917, //Watershed Area (km2)
FLT_AI = 0.02, //Impervious Area Fraction (km2/km2)
FLT_AP = 0.95, //Pervious Area Fraction (km2/km2)
FLT_TimeStep = 24,
//Parameters
FLT_DI = bestVal[18], //Maximum Impervious Detention (mm)
FLT_IP = bestVal[19], //Maximum Interception (mm)
FLT_DP = bestVal[20], //Maximum Pervious Detention (mm)
FLT_KSup = bestVal[21], //Surface Reservoir Decay (h)
FLT_CS = bestVal[22], //Soil Saturation Capacity (mm)
FLT_CC = bestVal[23], //Field Capacity (%)
FLT_CR = bestVal[24], //Recharge Capacity (%)
FLT_PP = bestVal[25], //Deep Percolation (mm/h)
FLT_KSub = bestVal[26], //Aquifer Reservoir Decay (d)
FLT_KCan = bestVal[27], //Channel Reservoir Decay (h)
FLT_CH = bestVal[28], //Hydraulic Conductivity (mm/h)
FLT_FS = bestVal[29], //Soil Capilarity Factor (mm)
FLT_PS = bestVal[30], //Soil Porosity (cm3/cm3)
FLT_UI = bestVal[31] //Initial Moisture (cm3/cm3)
};
InputDownstream.DTE_Arr_TimeSeries[0] = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
for (int i = 1; i < InputDownstream.DTE_Arr_TimeSeries.Length; i++)
{
InputDownstream.DTE_Arr_TimeSeries[i] = InputDownstream.DTE_Arr_TimeSeries[0].AddHours(ParametersDownstream.FLT_TimeStep * i);
}
PLASHReservoir ReservoirDownstream = new PLASHReservoir();
PLASHInitialConditions InitialDownstream = new PLASHInitialConditions()
{
RImp0 = bestVal[32],
RInt0 = bestVal[33],
RSup0 = bestVal[34],
RCan0 = bestVal[35]
};
PLASHOutput OutputDownstream = new PLASHOutput();
Muskingum DampenedUpstream = new Muskingum()
{
FLT_K = bestVal[36],
FLT_X = bestVal[37],
FLT_Timestep = 24,
FLT_Arr_InputFlow = OutputUpstream.FLT_Arr_Qt_Calc
};
DampenedUpstream.FLT_Arr_OutputFlow = Muskingum.ProcessDamping(DampenedUpstream);
InputDownstream.FLT_Arr_QtUpstream = DampenedUpstream.FLT_Arr_OutputFlow;
PLASH.Run(InputDownstream, ParametersDownstream, InitialDownstream, ReservoirDownstream, OutputDownstream);
Console.ReadKey();
//Console.WriteLine("");
//Console.ReadKey();
#endregion PLASH Simulation
#region Buwo Simulation
List <Buildup_Washoff> UsesUpstream = Buildup_Washoff.BuwoUpstreamList(Timestep, ReservoirUpstream.FLT_Arr_ESSup);
List <Buildup_Washoff> UsesDownstream = Buildup_Washoff.BuwoDownstreamList(Timestep, ReservoirDownstream.FLT_Arr_ESSup);
foreach (Buildup_Washoff Use in UsesUpstream)
{
Buildup_Washoff.fncBuildupWashoffProcess(Use);
}
Buildup_Washoff BuwoUpstream = Buildup_Washoff.AggregateUses(UsesUpstream, 863.178D);
foreach (Buildup_Washoff Use in UsesDownstream)
{
Buildup_Washoff.fncBuildupWashoffProcess(Use);
}
Buildup_Washoff BuwoDownstream = Buildup_Washoff.AggregateUses(UsesDownstream, 729.018D);
Buildup_Washoff Aggregate = Buildup_Washoff.Transport(BuwoUpstream, BuwoDownstream);
#endregion Buwo Simulation
//#region Excel Output
using (ExcelPackage excel = new ExcelPackage())
{
excel.Workbook.Worksheets.Add("Param_PLASHUp");
excel.Workbook.Worksheets.Add("Param_PLASHDown");
excel.Workbook.Worksheets.Add("PLASHReservoirUp");
excel.Workbook.Worksheets.Add("PLASHReservoirDown");
excel.Workbook.Worksheets.Add("PLASHInitialUp");
excel.Workbook.Worksheets.Add("PLASHInitialDown");
excel.Workbook.Worksheets.Add("Results_PLASHUp");
excel.Workbook.Worksheets.Add("Results_PLASHDown");
excel.Workbook.Worksheets.Add("Param_Muskingum");
excel.Workbook.Worksheets.Add("Results_Muskingum");
excel.Workbook.Worksheets.Add("Results_BuWo");
#region Parameters
var HeaderRowPLASHParam = new List <string[]>()
{
new string[]
{
"DI", "IP", "DP", "KSup", "CS", "CC", "CR", "PP", "Ksub", "KCan", "CH", "FS", "PS", "UI"
}
};
string headerRangePLASHParam = "A1:" + Char.ConvertFromUtf32(HeaderRowPLASHParam[0].Length + 64) + 1;
var worksheet = excel.Workbook.Worksheets["Param_PLASHUp"];
worksheet.Cells[headerRangePLASHParam].LoadFromArrays(HeaderRowPLASHParam);
List <object[]> cellDataPLASHParamUP = new List <object[]>();
cellDataPLASHParamUP.Add(new object[]
{
ParametersUpstream.FLT_DI, ParametersUpstream.FLT_IP, ParametersUpstream.FLT_DP, ParametersUpstream.FLT_KSup, ParametersUpstream.FLT_CS,
ParametersUpstream.FLT_CC, ParametersUpstream.FLT_CR, ParametersUpstream.FLT_PP, ParametersUpstream.FLT_KSub, ParametersUpstream.FLT_KCan,
ParametersUpstream.FLT_CH, ParametersUpstream.FLT_FS, ParametersUpstream.FLT_PS, ParametersUpstream.FLT_UI
});
worksheet.Cells[2, 1].LoadFromArrays(cellDataPLASHParamUP);
worksheet = excel.Workbook.Worksheets["Param_PLASHDown"];
worksheet.Cells[headerRangePLASHParam].LoadFromArrays(HeaderRowPLASHParam);
List <object[]> cellDataPLASHParamDown = new List <object[]>();
cellDataPLASHParamDown.Add(new object[]
{
ParametersDownstream.FLT_DI, ParametersDownstream.FLT_IP, ParametersDownstream.FLT_DP, ParametersDownstream.FLT_KSup, ParametersDownstream.FLT_CS,
ParametersDownstream.FLT_CC, ParametersDownstream.FLT_CR, ParametersDownstream.FLT_PP, ParametersDownstream.FLT_KSub, ParametersDownstream.FLT_KCan,
ParametersDownstream.FLT_CH, ParametersDownstream.FLT_FS, ParametersDownstream.FLT_PS, ParametersDownstream.FLT_UI
});
worksheet.Cells[2, 1].LoadFromArrays(cellDataPLASHParamDown);
#endregion Parameters
#region Reservoir
var HeaderRowPLASHreservoir = new List <string[]>()
{
new string[]
{
"ImpRes", "ImpEvap", "ImpFlow",
"IntRes", "IntEvap", "IntFlow",
"SurfRes", "SurfEvap", "SurfFlow",
"Inf", "InfCum", "IAE", "TP", "IAEAdim", "TPAdim",
"SoilRes", "SoilEvap", "SoilUpFlow", "SoilDownFlow",
"AquiRes", "AquiPerc", "AquiFlow",
"ChanRes", "ChanEvap", "ChanUpFlow", "ChanDownFlow"
}
};
string HeaderRangePLASHReservoir = "A1:" + Char.ConvertFromUtf32(HeaderRowPLASHreservoir[0].Length + 64) + 1;
worksheet = excel.Workbook.Worksheets["PLASHReservoirUp"];
worksheet.Cells[HeaderRangePLASHReservoir].LoadFromArrays(HeaderRowPLASHreservoir);
List <object[]> cellDataPLASHResUp = new List <object[]>();
for (int i = 0; i < SimulationLength; i++)
{
cellDataPLASHResUp.Add(new object[]
{
ReservoirUpstream.FLT_Arr_RImp[i], ReservoirUpstream.FLT_Arr_ERImp[i], ReservoirUpstream.FLT_Arr_ESImp[i],
ReservoirUpstream.FLT_Arr_RInt[i], ReservoirUpstream.FLT_Arr_ERInt[i], ReservoirUpstream.FLT_Arr_ESInt[i],
ReservoirUpstream.FLT_Arr_RSup[i], ReservoirUpstream.FLT_Arr_ERSup[i], ReservoirUpstream.FLT_Arr_ESSup[i],
ReservoirUpstream.FLT_Arr_Infiltration[i], ReservoirUpstream.FLT_Arr_Infiltration_Cumulative[i], ReservoirUpstream.FLT_Arr_IAE[i], ReservoirUpstream.FLT_Arr_TP[i], ReservoirUpstream.FLT_Arr_IAEAdim[i], ReservoirUpstream.FLT_Arr_TPAdim[i],
ReservoirUpstream.FLT_Arr_RSol[i], ReservoirUpstream.FLT_Arr_ERSol[i], ReservoirUpstream.FLT_Arr_EESol[i], ReservoirUpstream.FLT_Arr_ESSol[i],
ReservoirUpstream.FLT_Arr_RSub[i], ReservoirUpstream.FLT_Arr_PPSub[i], ReservoirUpstream.FLT_Arr_EESub[i],
ReservoirUpstream.FLT_Arr_RCan[i], ReservoirUpstream.FLT_Arr_ERCan[i], ReservoirUpstream.FLT_Arr_EECan[i], ReservoirUpstream.FLT_ARR_ESCan[i]
});
}
worksheet.Cells[2, 1].LoadFromArrays(cellDataPLASHResUp);
worksheet = excel.Workbook.Worksheets["PLASHReservoirDown"];
worksheet.Cells[HeaderRangePLASHReservoir].LoadFromArrays(HeaderRowPLASHreservoir);
List <object[]> cellDataPLASHResDown = new List <object[]>();
for (int i = 0; i < SimulationLength; i++)
{
cellDataPLASHResDown.Add(new object[]
{
ReservoirDownstream.FLT_Arr_RImp[i], ReservoirDownstream.FLT_Arr_ERImp[i], ReservoirDownstream.FLT_Arr_ESImp[i],
ReservoirDownstream.FLT_Arr_RInt[i], ReservoirDownstream.FLT_Arr_ERInt[i], ReservoirDownstream.FLT_Arr_ESInt[i],
ReservoirDownstream.FLT_Arr_RSup[i], ReservoirDownstream.FLT_Arr_ERSup[i], ReservoirDownstream.FLT_Arr_ESSup[i],
ReservoirDownstream.FLT_Arr_Infiltration[i], ReservoirDownstream.FLT_Arr_Infiltration_Cumulative[i], ReservoirDownstream.FLT_Arr_IAE[i], ReservoirDownstream.FLT_Arr_TP[i], ReservoirDownstream.FLT_Arr_IAEAdim[i], ReservoirDownstream.FLT_Arr_TPAdim[i],
ReservoirDownstream.FLT_Arr_RSol[i], ReservoirDownstream.FLT_Arr_ERSol[i], ReservoirDownstream.FLT_Arr_EESol[i], ReservoirDownstream.FLT_Arr_ESSol[i],
ReservoirDownstream.FLT_Arr_RSub[i], ReservoirDownstream.FLT_Arr_PPSub[i], ReservoirDownstream.FLT_Arr_EESub[i],
ReservoirDownstream.FLT_Arr_RCan[i], ReservoirDownstream.FLT_Arr_ERCan[i], ReservoirDownstream.FLT_Arr_EECan[i], ReservoirDownstream.FLT_ARR_ESCan[i]
});
}
worksheet.Cells[2, 1].LoadFromArrays(cellDataPLASHResDown);
#endregion Reservoir
#region Initial Conditions
var HeaderRowPLASHInitial = new List <string[]>()
{
new string[] { "RImp0", "RInt0", "RSup0", "RCan0" }
};
string headerRangePLASHInitial = "A1:" + Char.ConvertFromUtf32(HeaderRowPLASHInitial[0].Length + 64) + 1;
worksheet = excel.Workbook.Worksheets["PLASHInitialUp"];
worksheet.Cells[headerRangePLASHInitial].LoadFromArrays(HeaderRowPLASHInitial);
List <object[]> cellDataPLASHInitialUp = new List <object[]>();
cellDataPLASHInitialUp.Add(new object[] { InitialUpstream.RImp0, InitialUpstream.RInt0, InitialUpstream.RSup0, InitialUpstream.RCan0 });
worksheet.Cells[2, 1].LoadFromArrays(cellDataPLASHInitialUp);
worksheet = excel.Workbook.Worksheets["PLASHInitialDown"];
worksheet.Cells[headerRangePLASHInitial].LoadFromArrays(HeaderRowPLASHInitial);
List <object[]> cellDataPLASHInitialDown = new List <object[]>();
cellDataPLASHInitialDown.Add(new object[] { InitialDownstream.RImp0, InitialDownstream.RInt0, InitialDownstream.RSup0, InitialDownstream.RCan0 });
worksheet.Cells[2, 1].LoadFromArrays(cellDataPLASHInitialDown);
#endregion Initial Conditions
#region Results
//PLASH Upstream
var HeaderRowPLASH = new List <string[]>()
{
new string[] { "Precipitation", "Evapotranspiration", "Observed Flow",
"Impervious Reservoir", "Interception Reservoir", "Surface Reservoir", "Soil Reservoir", "Aquifer Reservoir", "Channel Reservoir",
"Calculated Basic Flow", "Calculated Surface Flow", "Calculated Total Flow" },
};
string headerRangePLASH = "A1:" + Char.ConvertFromUtf32(HeaderRowPLASH[0].Length + 64) + 1;
worksheet = excel.Workbook.Worksheets["Results_PLASHUp"];
worksheet.Cells[headerRangePLASH].LoadFromArrays(HeaderRowPLASH);
List <object[]> cellDataPLASHUp = new List <object[]>();
for (int i = 0; i < SimulationLength; i++)
{
cellDataPLASHUp.Add(new object[] { InputUpstream.FLT_Arr_PrecipSeries[i], InputUpstream.FLT_Arr_EPSeries[i], InputUpstream.FLT_Arr_QtObsSeries[i],
ReservoirUpstream.FLT_Arr_RImp[i], ReservoirUpstream.FLT_Arr_RInt[i], ReservoirUpstream.FLT_Arr_RSup[i], ReservoirUpstream.FLT_Arr_RSol[i], ReservoirUpstream.FLT_Arr_RSub[i], ReservoirUpstream.FLT_Arr_RCan[i],
OutputUpstream.FLT_Arr_QBas_Calc[i], OutputUpstream.FLT_Arr_QSup_Calc[i], OutputUpstream.FLT_Arr_Qt_Calc[i] });
}
worksheet.Cells[2, 1].LoadFromArrays(cellDataPLASHUp);
//PLASH Downstream
worksheet = excel.Workbook.Worksheets["Results_PLASHDown"];
worksheet.Cells[headerRangePLASH].LoadFromArrays(HeaderRowPLASH);
List <object[]> cellDataPLASHDown = new List <object[]>();
for (int i = 0; i < SimulationLength; i++)
{
cellDataPLASHDown.Add(new object[] { InputDownstream.FLT_Arr_PrecipSeries[i], InputDownstream.FLT_Arr_EPSeries[i], InputDownstream.FLT_Arr_QtObsSeries[i],
ReservoirDownstream.FLT_Arr_RImp[i], ReservoirDownstream.FLT_Arr_RInt[i], ReservoirDownstream.FLT_Arr_RSup[i], ReservoirDownstream.FLT_Arr_RSol[i], ReservoirDownstream.FLT_Arr_RSub[i], ReservoirDownstream.FLT_Arr_RCan[i],
OutputDownstream.FLT_Arr_QBas_Calc[i], OutputDownstream.FLT_Arr_QSup_Calc[i], OutputDownstream.FLT_Arr_Qt_Calc[i] });
}
worksheet.Cells[2, 1].LoadFromArrays(cellDataPLASHDown);
#endregion Results
#region Muskingum Parameters
//Muskingum
var HeaderRowMuskingumParam = new List <string[]>()
{
new string[] { "K", "X" },
};
string headerRangeMuskingumParam = "A1:" + Char.ConvertFromUtf32(HeaderRowMuskingumParam[0].Length + 64) + 1;
worksheet = excel.Workbook.Worksheets["Param_Muskingum"];
worksheet.Cells[headerRangeMuskingumParam].LoadFromArrays(HeaderRowMuskingumParam);
List <object[]> cellDataMuskingumParam = new List <object[]>();
cellDataMuskingumParam.Add(new object[] { DampenedUpstream.FLT_K, DampenedUpstream.FLT_X });
worksheet.Cells[2, 1].LoadFromArrays(cellDataMuskingumParam);
#endregion Muskingum Parameters
#region Muskingum
var HeaderRowMuskingum = new List <string[]>()
{
new string[] { "Upstream Flow", "Downstream Flow" },
};
string headerRangeMuskingum = "A1:" + Char.ConvertFromUtf32(HeaderRowMuskingum[0].Length + 64) + 1;
worksheet = excel.Workbook.Worksheets["Results_Muskingum"];
worksheet.Cells[headerRangeMuskingum].LoadFromArrays(HeaderRowMuskingum);
List <object[]> cellDataMuskingum = new List <object[]>();
for (int i = 0; i < SimulationLength; i++)
{
cellDataMuskingum.Add(new object[] { DampenedUpstream.FLT_Arr_InputFlow[i], DampenedUpstream.FLT_Arr_OutputFlow[i] });
}
worksheet.Cells[2, 1].LoadFromArrays(cellDataMuskingum);
#endregion Muskingum
#region BuWo
//Buwo
var HeaderRow2 = new List <string[]>()
{
new string[] { "Precipitation", "Surface Flow", "Buildup", "Effective Washoff" },
};
string headerRange2 = "A1:" + Char.ConvertFromUtf32(HeaderRow2[0].Length + 64) + 1;
worksheet = excel.Workbook.Worksheets["Results_BuWo"];
worksheet.Cells[headerRange2].LoadFromArrays(HeaderRow2);
List <object[]> cellDataBuwo = new List <object[]>();
for (int i = 0; i < SimulationLength; i++)
{
cellDataBuwo.Add(new object[] { InputUpstream.FLT_Arr_PrecipSeries[i], ReservoirDownstream.FLT_Arr_ESSup[i], Aggregate.FLT_Arr_Buildup[i], Aggregate.FLT_Arr_EffectiveWashoff[i] });
}
worksheet.Cells[2, 1].LoadFromArrays(cellDataBuwo);
#endregion BuWo
FileInfo excelFile = new FileInfo(@"D:\dataGA.xlsx");
excel.SaveAs(excelFile);
}
////Console.WriteLine("Excel processed");
//#endregion Excel Output
//Console.ReadKey();
}
public void RunLinqSample()
{
Console.WriteLine("Now open sample 7 again and perform some Linq queries...");
Console.WriteLine();
FileInfo existingFile = new FileInfo(Path.Combine("bin", "sample7.xlsx"));
using (ExcelPackage package = new ExcelPackage(existingFile))
{
ExcelWorksheet sheet = package.Workbook.Worksheets[1];
//Select all cells in column d between 9990 and 10000
var query1 = (from cell in sheet.Cells["d:d"] where cell.Value is double && (double)cell.Value >= 9990 && (double)cell.Value <= 10000 select cell);
Console.WriteLine("Print all cells with value between 9990 and 10000 in column D ...");
Console.WriteLine();
int count = 0;
foreach (var cell in query1)
{
Console.WriteLine("Cell {0} has value {1:N0}", cell.Address, cell.Value);
count++;
}
Console.WriteLine("{0} cells found ...", count);
Console.WriteLine();
//Select all bold cells
Console.WriteLine("Now get all bold cells from the entire sheet...");
var query2 = (from cell in sheet.Cells[sheet.Dimension.Address] where cell.Style.Font.Bold select cell);
//If you have a clue where the data is, specify a smaller range in the cells indexer to get better performance (for example "1:1,65536:65536" here)
count = 0;
foreach (var cell in query2)
{
if (!string.IsNullOrEmpty(cell.Formula))
{
Console.WriteLine("Cell {0} is bold and has a formula of {1:N0}", cell.Address, cell.Formula);
}
else
{
Console.WriteLine("Cell {0} is bold and has a value of {1:N0}", cell.Address, cell.Value);
}
count++;
}
//Here we use more than one column in the where clause. We start by searching column D, then use the Offset method to check the value of column C.
var query3 = (from cell in sheet.Cells["d:d"]
where cell.Value is double &&
(double)cell.Value >= 9500 && (double)cell.Value <= 10000 &&
cell.Offset(0, -1).GetValue <DateTime>().Year == DateTime.Today.Year + 1
select cell);
Console.WriteLine();
Console.WriteLine("Print all cells with a value between 9500 and 10000 in column D and the year of Column C is {0} ...", DateTime.Today.Year + 1);
Console.WriteLine();
count = 0;
foreach (var cell in query3) //The cells returned here will all be in column D, since that is the address in the indexer. Use the Offset method to print any other cells from the same row.
{
Console.WriteLine("Cell {0} has value {1:N0} Date is {2:d}", cell.Address, cell.Value, cell.Offset(0, -1).GetValue <DateTime>());
count++;
}
}
}
/// <summary>
/// EXCEL 明細作成処理
/// </summary>
/// <param name="ws">ExcelWorksheet</param>
/// <param name="kakotora">結果データテーブル</returns>
/// <remarks></remarks>
public void CreateFollowList_Meisai(ExcelWorksheet ws, DataTable kakotora)
{
// 明細を編集する
for (int i = 0; i < kakotora.Rows.Count; i++)
{
// No.
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFNO_CLM].Value =
kakotora.Rows[i]["ROWID"].ToString();
// 部品
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFBUHIN_CLM].Value =
kakotora.Rows[i]["BUHIN_NAME"].ToString();
// マスタの現象
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFGENSYO_CLM].Value =
kakotora.Rows[i]["BUNRUI_GENSYO_NAME"].ToString();
// マスタの制御系要因
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFSEIGYOFACTOR_CLM].Value =
kakotora.Rows[i]["SEIGYO_FACTOR_NAME"].ToString();
// 進捗
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFSINCHOKU_CLM].Value =
kakotora.Rows[i]["FOLLOW_INFO"].ToString();
// 項目管理No.
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFKANRINO_CLM].Value =
kakotora.Rows[i]["KOUMOKU_KANRI_NO"].ToString();
// 項目
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFKOUMOKU_CLM].Value =
kakotora.Rows[i]["KOUMOKU"].ToString();
// 原因
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFGENIN_CLM].Value =
kakotora.Rows[i]["GENIN"].ToString();
// 対策
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFTAISAKU_CLM].Value =
kakotora.Rows[i]["TAISAKU"].ToString();
// 開発時の流出要因
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFHAKKEN_CLM].Value =
kakotora.Rows[i]["KAIHATU_MIHAKKEN_RIYU"].ToString();
// 確認の観点
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFKANTEN_CLM].Value =
kakotora.Rows[i]["SQB_KANTEN"].ToString();
// 再発防止策（設計面）
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFSBOUSISAKU_CLM].Value =
kakotora.Rows[i]["SAIHATU_SEKKEI"].ToString();
// 再発防止策（評価面）
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFHBOUSISAKU_CLM].Value =
kakotora.Rows[i]["SAIHATU_HYOUKA"].ToString();
// 資料No.一覧
string strSiryo = null;
int flg = 0;
if (kakotora.Rows[i]["SIRYOU_NO1"].ToString().Trim() != "")
{
if (flg == 0)
{
strSiryo += kakotora.Rows[i]["SIRYOU_NO1"].ToString().Trim();
flg = 1;
}
else
{
strSiryo += "\n" + kakotora.Rows[i]["SIRYOU_NO1"].ToString().Trim();
}
}
if (kakotora.Rows[i]["SIRYOU_NO2"].ToString().Trim() != "")
{
if (flg == 0)
{
strSiryo += kakotora.Rows[i]["SIRYOU_NO2"].ToString().Trim();
flg = 1;
}
else
{
strSiryo += "\n" + kakotora.Rows[i]["SIRYOU_NO2"].ToString().Trim();
}
}
if (kakotora.Rows[i]["SIRYOU_NO3"].ToString().Trim() != "")
{
if (flg == 0)
{
strSiryo += kakotora.Rows[i]["SIRYOU_NO3"].ToString().Trim();
flg = 1;
}
else
{
strSiryo += "\n" + kakotora.Rows[i]["SIRYOU_NO3"].ToString().Trim();
}
}
if (kakotora.Rows[i]["SIRYOU_NO4"].ToString().Trim() != "")
{
if (flg == 0)
{
strSiryo += kakotora.Rows[i]["SIRYOU_NO4"].ToString().Trim();
flg = 1;
}
else
{
strSiryo += "\n" + kakotora.Rows[i]["SIRYOU_NO4"].ToString().Trim();
}
}
if (kakotora.Rows[i]["SIRYOU_NO5"].ToString().Trim() != "")
{
if (flg == 0)
{
strSiryo += kakotora.Rows[i]["SIRYOU_NO5"].ToString().Trim();
flg = 1;
}
else
{
strSiryo += "\n" + kakotora.Rows[i]["SIRYOU_NO5"].ToString().Trim();
}
}
if (kakotora.Rows[i]["KANREN_KANRI_NO"].ToString().Trim() != "")
{
if (flg == 0)
{
strSiryo += kakotora.Rows[i]["KANREN_KANRI_NO"].ToString().Trim();
flg = 1;
}
else
{
strSiryo += "\n" + kakotora.Rows[i]["KANREN_KANRI_NO"].ToString().Trim();
}
}
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFSIRYO_CLM].Value =
strSiryo;
// 部署
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFBUSYO_CLM].Value =
kakotora.Rows[i]["KA_CODE"].ToString();
// 適用有無<SQB>
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFSQB_CLM].Value =
kakotora.Rows[i]["TEKIYO_SQB"].ToString();
// 適用有無<設計>
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFSHYOUKA_CLM].Value =
kakotora.Rows[i]["TEKIYO_SEKKEI"].ToString();
// ﾋｱﾘﾝｸﾞ要望
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFHEARING_CLM].Value =
kakotora.Rows[i]["HEARING"].ToString();
// 進度
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFSINDO_CLM].Value =
kakotora.Rows[i]["SINDO"].ToString();
// 対応内容
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFKAITO_CLM].Value =
kakotora.Rows[i]["TAIOU_NAIYO"].ToString();
// 重要度ランク
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFRANK_CLM].Value =
kakotora.Rows[i]["RANK"].ToString();
// 再発案件
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFSAIHATU_CLM].Value =
kakotora.Rows[i]["SAIHATU"].ToString();
// RSC項目
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFRSC_CLM].Value =
kakotora.Rows[i]["RSC"].ToString();
// 主務部署
ws.Cells[Def.DefFMEISAISTART_ROW + i, Def.DefFSYUMU_CLM].Value =
kakotora.Rows[i]["SYUMU"].ToString();
String grey = "OFF";
// 類似は背景色設定
if (kakotora.Rows[i]["SAIHATU"].ToString().Trim() == Def.DefRUIJI)
{
grey = "ON";
}
// 書式の設定
CreateFollowList_Syosiki(ws, i, grey);
}
}
public ActionResult Export_To_Excel(List<UserModel> model)
{
using (var package = new ExcelPackage())
{
package.Workbook.Worksheets.Add(string.Format("{0:yyyy-MM-dd}", DateTime.Now));
ExcelWorksheet ws = package.Workbook.Worksheets[1];
ws.Name = "User List"; //Setting Sheet's name
ws.Cells.Style.Font.Size = 12; //Default font size for whole sheet
ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
//Merging cells and create a center heading for out table
ws.Cells[1, 1].Value = "List of Users"; // Heading Name
ws.Cells[1, 1].Style.Font.Size = 22;
ws.Cells[1, 1, 1, 10].Merge = true; //Merge columns start and end range
ws.Cells[1, 1, 1, 10].Style.Font.Bold = true; //Font should be bold
ws.Cells[1, 1, 1, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center
int row_index = 2;
// header
List<string> ws_header = new List<string>();
ws_header.Add("");
ws_header.Add("ID");
ws_header.Add("Full Name");
ws_header.Add("First Name");
ws_header.Add("Last Name");
ws_header.Add("Username");
ws_header.Add("Email");
ws_header.Add("Role");
ws_header.Add("Active");
ws_header.Add("Date Create");
ws_header.Add("Birth date");
ws_header.Add("Country");
ws_header.Add("Gender");
ws_header.Add("Phone");
ws_header.Add("Address");
ws_header.Add("Zip Code");
ws_header.Add("City");
ws_header.Add("State");
for (int i = 1; i < ws_header.Count; i++)
{
ws.Cells[2, i].Value = ws_header[i];
ws.Cells[2, i].Style.Font.Bold = true;
ws.Cells[2, i].Style.Font.Size = 14;
}
// export data, first we need to group it by company
// get company list in model
var group_level1 = model.GroupBy(m => m.Status);
foreach (var item_level1 in group_level1)
{
var firstcol_groupby = item_level1.First().Status;
row_index++;
if (firstcol_groupby)
ws.Cells[row_index, 1].Value = "Active Users";
else
ws.Cells[row_index, 1].Value = "Not Active Users";
ws.Cells[row_index, 1].Style.Font.Bold = true;
ws.Cells[row_index, 1].Style.Font.Size = 11;
ws.Cells[row_index, 1, row_index, 10].Merge = true; //Merge columns start and end range
// list record in detail
int row_id = 0;
var data = item_level1.OrderBy(m => m.DataCreate);
foreach (var item in data) // list all item in each company
{
row_id++;
row_index++;
var col_index = 1;
// ID
ws.Cells[row_index, col_index].Value = row_id;
// Name
col_index++;
ws.Cells[row_index, col_index].Value = item.FullName;
// first name
col_index++;
ws.Cells[row_index, col_index].Value = item.FirstName;
// last name
col_index++;
ws.Cells[row_index, col_index].Value = item.LastName;
// username
col_index++;
ws.Cells[row_index, col_index].Value = item.UserName;
// email
col_index++;
ws.Cells[row_index, col_index].Value = item.Email;
// Role
col_index++;
ws.Cells[row_index, col_index].Value = String.Join(", ", item.RoleName.ToArray());
// Active Status
col_index++;
ws.Cells[row_index, col_index].Value = item.Status;
// date create
col_index++;
ws.Cells[row_index, col_index].Value = string.Format("{0:MM/dd/yyyy HH:mm:ss}", item.DataCreate);
// birth day
col_index++;
ws.Cells[row_index, col_index].Value = string.Format("{0:MM/dd/yyyy HH:mm:ss}", item.BirthDate);
// country
col_index++;
ws.Cells[row_index, col_index].Value = item.Country;
// gender
col_index++;
ws.Cells[row_index, col_index].Value = item.Gender;
// phone
col_index++;
ws.Cells[row_index, col_index].Value = item.Phone;
// address
col_index++;
ws.Cells[row_index, col_index].Value = item.Address;
// zip code
col_index++;
ws.Cells[row_index, col_index].Value = item.Zipcode;
col_index++;
ws.Cells[row_index, col_index].Value = item.MaHC;
} // end record detail
row_index++;
} // end level 1
//// footer total
row_index++;
ws.Cells[row_index, 2].Value = "Total";
ws.Cells[row_index, 2].Style.Font.Bold = true;
ws.Cells[row_index, 2].Style.Font.Italic = true;
ws.Cells[row_index, 2].Style.Font.Size = 11;
ws.Cells[row_index, 2, row_index, 3].Merge = true; //Merge columns start and end range
ws.Cells[row_index, 5].Value = "Active Users";
ws.Cells[row_index, 5].Style.Font.Bold = true;
ws.Cells[row_index, 5].Style.Font.Size = 11;
ws.Cells[row_index, 6].Value = model.Count(m => m.Status);
ws.Cells[row_index, 7].Value = "Not Active Users";
ws.Cells[row_index, 7].Style.Font.Bold = true;
ws.Cells[row_index, 7].Style.Font.Size = 11;
ws.Cells[row_index, 8].Value = model.Count(m => m.Status == false);
// freeze data
ws.View.FreezePanes(3, 2);
// auto adjust the columns width for all columns
for (int k = 1; k < ws_header.Count + 2; k++)
ws.Column(k).AutoFit();
//var chart = ws.Drawings.AddChart("chart1", eChartType.AreaStacked);
////Set position and size
//chart.SetPosition(0, 630);
//chart.SetSize(800, 600);
//// Add the data series.
//var series = chart.Series.Add(ws.Cells["A2:A46"], ws.Cells["B2:B46"]);
var memoryStream = package.GetAsByteArray();
var fileName = string.Format("UsersManagement-Filter-{0:yyyy-MM-dd-HH-mm-ss}.xlsx", DateTime.Now);
// mimetype from http://stackoverflow.com/questions/4212861/what-is-a-correct-mime-type-for-docx-pptx-etc
return base.File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
}
}
