/// <summary>
/// evaluation of GST speed only.
/// DO NOT TRY THIS AT HOME!
/// </summary>
public static void PerformTest(string testName)
{
//File.Copy(@"D:\test\MUTEX\CompleteComparison\template-complete.xlsx", @"D:\test\MUTEX\CompleteComparison\template-complete2.xlsx", true);
var cartesianProduct = GetUniqueCartesianProduct(testName);
var results = cartesianProduct.Select(elem => Calculate(testName, elem));
var wb = new WorkBook();
wb.readXLSX(@"test\CompleteComparison\template-complete2.xlsx");
SetActiveSheet(testName, wb);
int i = 0;
try
{
foreach (var elem in results)
{
wb.setEntry(i + 1, 1, string.Format("{0}.0", elem[1]));
i++;
}
}
catch (NullReferenceException ex)
{
Console.WriteLine(ex.ToString());
Console.ReadLine();
Environment.Exit(0);
}
wb.writeXLSX(@"test\CompleteComparison\heyja.xlsx");
}
/// <summary>
/// writes the chart
/// </summary>
/// <param name="wb"></param>
/// <param name="compHistory"></param>
/// <param name="columnOffset"></param>
private void WriteChart(WorkBook wb, ComparisonHistoryModel compHistory, int columnOffset, int sheetIndex)
{
Console.WriteLine("write chart sheetIndex: {0}", sheetIndex);
// transform sheet into chart sheeet
var chartShape = wb.addChartSheet(sheetIndex);
wb.Sheet = sheetIndex;
wb.PrintScaleFitToPage = true;
wb.PrintLandscape = true;
wb.setSheetName(sheetIndex, string.Format("Diagram_{0}", compHistory.Name));
chartShape.ChartType = ChartShape.Scatter;
chartShape.setAxisTitle(ChartShape.XAxis, 0, "Evaluation Run ID");
chartShape.setAxisTitle(ChartShape.YAxis, 0, "Similarity [%]");
ChartFormat tFormat = chartShape.PlotFormat;
tFormat.setLineNone();
chartShape.setSeriesName(0, compHistory.Name);
var format = chartShape.getSeriesFormat(0);
format.MarkerStyle = ChartFormat.MarkerCircle;
chartShape.setSeriesFormat(0, format);
string xFormula = string.Format("data!${0}${1}:${0}${2}", GetDataColumnIndex(columnOffset), 2, compHistory.Data.Count());
chartShape.setSeriesXValueFormula(0, xFormula);
string yFormula = string.Format("data!${0}${1}:${0}${2}", GetDataColumnIndex(columnOffset + 2),2, compHistory.Data.Count());
Console.WriteLine("xFormula: {0}, yFormula: {1}", xFormula, yFormula);
chartShape.setSeriesYValueFormula(0, yFormula);
}
/// <summary>
/// EXCEL转换为动态活动流程的参数
/// </summary>
/// <param name="wb"></param>
/// <param name="client"></param>
/// <returns></returns>
public WfCreateClientDynamicProcessParams ExcelToClient(string processKey, WorkBook wb, ref WfCreateClientDynamicProcessParams client)
{
processKey.NullCheck("processKey");
wb.NullCheck("wb");
DataTable processTable = DocumentHelper.GetRangeValuesAsTable(wb, "Process", "A3");
DataTable matrixTable = DocumentHelper.GetRangeValuesAsTable(wb, "Matrix", "A3");
if (client == null)
client = new WfCreateClientDynamicProcessParams();
client.Key = processKey;
foreach (DataRow row in processTable.Rows)
{
string propertyValue = row[0].ToString();
if (propertyValue.IsNullOrEmpty() || string.Equals(propertyValue, "Key", StringComparison.OrdinalIgnoreCase))
continue;
string dataValue = row[1].ToString(); //dataValue 可以为空
client.Properties.AddOrSetValue(propertyValue, dataValue);
}
client.ActivityMatrix = new WfClientActivityMatrixResourceDescriptor(matrixTable);
return client;
}
private void ExcelExport()
{
var bindingSource = (BindingSource)this.gridView1.DataSource;
var dataTable = ((DataView)bindingSource.List).Table;
WorkBook m_book = new WorkBook();
//Export DataTable.
if (this.gridView1 != null)
{
m_book.ImportDataTable((DataTable)dataTable, true, 1, 1, -1, -1);
}
else
{
MessageBox.Show("Nema podataka za export u excel!", "Error");
return;
}
//Saving the workbook to disk.
m_book.write("C:\\Podaci\\Podaci.xls");
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Želite li pogledati podatke?", "Dokument je kreiran", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("C:\\Podaci\\Podaci.xls");
}
}
private void frmDjecaGodina_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
SendKeys.Send("{TAB}");
e.SuppressKeyPress = true;
}
if (e.KeyCode == Keys.F9)
{
DialogResult result;
result = MessageBox.Show("Želite prijenos podataka u excel?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
if (result == DialogResult.Yes)
{
try
{
DevExpress.XtraGrid.GridControl tablica = dgTransakcije;
if (tablica != null)
{
// Put the selected text on the Clipboard.
//Clipboard.SetDataObject(tablica.DataSource);
WorkBook m_book = new WorkBook();
//Export DataTable.
if (tablica.DataSource != null)
{
m_book.ImportDataTable((DataTable)tablica.DataSource, true, 1, 1, -1, -1);
}
else
{
MessageBox.Show("Nema podataka za export u excel!", "Error");
return;
}
//Saving the workbook to disk.
m_book.write("C:\\Podaci\\Podaci.xls");
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Želite li pogledati podatke?", "Dokument je kreiran",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("C:\\Podaci\\Podaci.xls");
}
}
}
catch
{
MessageBox.Show("Morate selektirati tablicu !!!");
}
}
}
}
protected override MemoryStream CreateContent(IList<ComparisonHistoryModel> data)
{
var wb = new WorkBook();
WriteData(wb, data);
var stream = new MemoryStream();
wb.writeXLSX(stream);
return stream;
}
/// <summary>
///
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
protected override MemoryStream CreateContent(EvaluationRunModel model)
{
var wb = new WorkBook();
WriteData(wb, model);
var stream = new MemoryStream();
wb.writeXLSX(stream);
return stream;
}
private static void FillFileInfo(WorkBook workBook, SOARole role)
{
try
{
workBook.FileDetails.Title = role.Name;
workBook.FileDetails.Subject = role.FullCodeName;
}
catch (System.Exception)
{
}
}
/// <summary>
/// 转换为Excel的WorkSheet
/// </summary>
/// <param name="matrix"></param>
/// <param name="workBook"></param>
/// <returns></returns>
public static WorkSheet ToWorkSheet(this IWfMatrixContainer matrix, WorkBook workBook)
{
matrix.NullCheck("matrix");
WorkSheet sheet = new WorkSheet(workBook, "Matrix");
Row titleRow = new Row(1) { Height = 30d };
titleRow.Style.Fill.SetBackgroundColor(Color.LightGray, ExcelFillStyle.Solid);
titleRow.Style.Font.Size = 20;
sheet.Rows.Add(titleRow);
sheet.Cells[titleRow.Index, 1].Value = "审批矩阵";
CreateMatrixHeaderRow(matrix, sheet);
FillMatrixSheetData(matrix, sheet);
return sheet;
}
private void dgCjenici_KeyDown(object sender, KeyEventArgs e)
{
if (gridView2.RowCount > 0)
{
DialogResult result;
result = MessageBox.Show("Želite prijenos podataka u excel?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
if (result == DialogResult.Yes)
{
var bindingSource = (BindingSource)this.dgCjenici.DataSource;
var dataTable = ((DataView)bindingSource.List).Table;
WorkBook m_book = new WorkBook();
//Export DataTable.
if (this.dgCjenici != null)
{
m_book.ImportDataTable((DataTable)dataTable, true, 1, 1, -1, -1);
}
else
{
MessageBox.Show("Nema podataka za export u excel!", "Error");
return;
}
//Saving the workbook to disk.
//m_book.write("C:\\Podaci\\Podaci.xls");
dgCjenici.ExportToExcelOld(@"c:\Podaci\Podaci.xls");
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Želite li pogledati podatke?", "Dokument je kreiran", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("C:\\Podaci\\Podaci.xls");
}
}
}
}
protected override void WriteData(WorkBook wb, IList<ComparisonHistoryModel> data)
{
wb.insertSheets(1, data.Count);
wb.setSheetName(0, "data");
for (int i = 0; i < data.Count; i++)
{
wb.Sheet = DataSheetIndex;
var compHistory = data[i];
var columnOffset = i*3;
if (!compHistory.Data.Any())
{
Console.WriteLine("no data points for: {0}", compHistory.Name);
continue;
}
Console.WriteLine("Setting column headers: offset: {0}, name: {1}", columnOffset, compHistory.Name);
WriteDataColumns(wb, compHistory, columnOffset);
WriteChart(wb, compHistory, columnOffset, i + 1);
}
/* */
}
public WorkBookView(WorkBook workbook)
{
this._WorkBook = workbook;
this.BookViewID = workbook.Views.GetNextBookViewID();
}
protected void OnDownload(object sender, EventArgs e)
{
WorkBook oWorkbook = new WorkBook();
oWorkbook.setSheetName(0, string.Format("{0} {1} Report", MemberProtect.CurrentUser.GetDataItem("FirstName"), MemberProtect.CurrentUser.GetDataItem("LastName")));
// Merge the title rows for better formatting
RangeStyle rangeStyle = oWorkbook.getRangeStyle(0, 0, 0, 2);
rangeStyle.MergeCells = true;
oWorkbook.setRangeStyle(rangeStyle, 0, 0, 0, 2);
rangeStyle = oWorkbook.getRangeStyle(1, 0, 1, 2);
rangeStyle.MergeCells = true;
oWorkbook.setRangeStyle(rangeStyle, 1, 0, 1, 2);
oWorkbook.setText(0, 0, string.Format("{0} {1} Report", MemberProtect.CurrentUser.GetDataItem("FirstName"), MemberProtect.CurrentUser.GetDataItem("LastName")));
oWorkbook.setText(1, 0, string.Format("Reporting: {0:d} - {1:d}", m_dtFrom.SelectedDate, m_dtTo.SelectedDate));
oWorkbook.setText(3, 0, "Job #");
oWorkbook.setText(3, 1, "Production Order");
oWorkbook.setText(3, 2, "Spot Title");
oWorkbook.setText(3, 3, "Due Date");
oWorkbook.setText(3, 4, "Length");
oWorkbook.setText(3, 5, "Actual Time");
oWorkbook.setText(3, 6, "Fee");
oWorkbook.setText(3, 7, "Fee Type");
oWorkbook.setText(3, 8, "Sent to QC");
oWorkbook.setText(3, 9, "QC Marked Complete");
// Format the title
RangeStyle oRangeStyle = oWorkbook.getRangeStyle();
oRangeStyle = oWorkbook.getRangeStyle();
oRangeStyle.FontSize = 320; // 20 * [actual pixel size]
oRangeStyle.FontBold = true;
oWorkbook.setRangeStyle(oRangeStyle, 0, 0, 0, 0);
// Format the header
oRangeStyle = oWorkbook.getRangeStyle();
oRangeStyle.FontSize = 240;
oRangeStyle.FontBold = true;
oRangeStyle.BottomBorder = 1;
oWorkbook.setRangeStyle(oRangeStyle, 3, 0, 3, 9);
int iRow = 4;
IQueryable<fn_Talent_GetProductionOrdersResult> oResults = GetCompletedJobs();
oResults = oResults.OrderBy(row => row.QCJobMarkedCompletDateTime).ThenBy(row => row.IAProductionOrderID);
foreach(fn_Talent_GetProductionOrdersResult oResult in oResults)
{
IAProductionOrder oIAProductionOrder = DataAccess.IAProductionOrders.SingleOrDefault(row => row.IAProductionOrderID == oResult.IAProductionOrderID);
if(oIAProductionOrder != null)
{
foreach(IASpot oIASPot in oIAProductionOrder.IASpots)
{
oWorkbook.setText(iRow, 0, oResult.JobNumber);
oWorkbook.setText(iRow, 1, oIAProductionOrder.IAJob.Name);
oWorkbook.setText(iRow, 2, oIASPot.Title);
oWorkbook.setText(iRow, 3, oIASPot.DueDateTime.ToString("g"));
oWorkbook.setText(iRow, 4, oIASPot.Length);
//oWorkbook.setText(iRow, 5, oIASPot.LengthActual);
oWorkbook.setText(iRow, 8, oIASPot.CompletedDateTime.ToString("g"));
oWorkbook.setText(iRow, 9, (oResult.QCJobMarkedCompletDateTime != new DateTime(1950, 1, 1)) ? oResult.QCJobMarkedCompletDateTime.ToString("g") : "");
foreach(IASpotFee oIASpotFee in oIASPot.IASpotFees)
{
oWorkbook.setText(iRow, 5, oIASpotFee.LengthActual);
oWorkbook.setNumber(iRow, 6, (double)oIASpotFee.Fee);
oWorkbook.setText(iRow, 7, oIASpotFee.IASpotFeeType.Name);
iRow++;
}
}
iRow++;
}
}
iRow--;
// Add in the total fees value
oWorkbook.setText(iRow, 5, "Total Fees:");
string sumFunction = string.Format("SUM(G5:G{0})", iRow);
oWorkbook.setFormula(iRow, 6, sumFunction);
// Set the fee column to format as money
oRangeStyle = oWorkbook.getRangeStyle();
oRangeStyle.FontSize = 200;
oRangeStyle.FontBold = false;
oRangeStyle.CustomFormat = "$#,##0.00";
oWorkbook.setRangeStyle(oRangeStyle, 4, 6, iRow, 6);
// Format the total fees section
oRangeStyle.FontBold = true;
oRangeStyle.TopBorder = 2;
oWorkbook.setRangeStyle(oRangeStyle, iRow, 5, iRow, 6);
// Auto-size the columns so everything looks nice
oWorkbook.setColWidth(0, 2820);
oWorkbook.setColWidthAutoSize(1, true);
oWorkbook.setColWidthAutoSize(2, true);
oWorkbook.setColWidthAutoSize(3, true);
oWorkbook.setColWidthAutoSize(4, true);
oWorkbook.setColWidthAutoSize(5, true);
oWorkbook.setColWidthAutoSize(6, true);
oWorkbook.setColWidthAutoSize(7, true);
oWorkbook.setColWidthAutoSize(8, true);
oWorkbook.setColWidthAutoSize(9, true);
// Send to client
MemoryStream oMemoryStream = new MemoryStream();
oWorkbook.write(oMemoryStream);
byte[] oExcelBuffer = oMemoryStream.ToArray();
Response.ContentType = "application/octet-stream";
Response.AppendHeader("Content-Disposition", "attachment; filename=TalentReport.xls");
Response.BinaryWrite(oExcelBuffer);
Response.End();
}
private void LerExcel(string name, string caminho)
{
//XLWorkbook wb = new XLWorkbook(caminho);
//IXLWorksheet worksheet = wb.Worksheet(1);
//var row = worksheet.FirstRowUsed().RowBelow();
//foreach (var cell in worksheet.Cells())
//{
// var a = cell.Value;
// var b = cell.GetValue<string>();
//}
WorkBook wb = WorkBook.Load(caminho);
WorkSheet ws = wb.WorkSheets.First();
var table = ws.ToDataTable(true);
DataRow[] rows = table.Select();
IEnumerable <DataRow> ts = from processo in table.AsEnumerable() select processo;
foreach (DataRow dr in ts)
{
var dt = dr.Field <DateTime>(8);
}
foreach (DataRow r in rows)
{
var e = r.Field <Double>(0);
}
foreach (DataRow row in table.Rows)
{
var f = row["CIAS"];
}
foreach (var cell in ws[ws.RangeAddressAsString])
{
var a = cell.Value;
}
//using (var stream = File.Open(caminho, FileMode.Open, FileAccess.Read))
//{
// IExcelDataReader reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream);
// var conf = new ExcelDataSetConfiguration
// {
// ConfigureDataTable = _ => new ExcelDataTableConfiguration
// {
// UseHeaderRow = false
// }
// };
// var dataSet = reader.AsDataSet(conf);
// var dataTable = dataSet.Tables[0];
// for (int i = 0; i < dataTable.Rows.Count; i++)
// {
// for (int j = 0; j < dataTable.Columns.Count; j++)
// {
// var b = dataTable.Rows[i][j];
// }
// }
// reader.Close();
//}
//Excel = new _Excel.Application();
//Wb = Excel.Workbooks.Open($@"{path}\{name}");
//Ws = Wb.Worksheets[1];
//var c = Ws.Cells.Value2;
}
/// <summary>
/// 指定起始单元格，提取Excel文件数据，起始行使用Excel名称定义
/// </summary>
/// <param name="workbook">工作簿对象</param>
/// <param name="workSheetName">工作表名称</param>
/// <param name="beginAddress">开始位置</param>
/// <param name="throwException">数据不存在时是否抛出异常</param>
/// <returns>返回首行创建成TableHeader</returns>
public static DataTable GetRangeValuesAsTable(WorkBook workbook, string workSheetName, string beginAddress, bool throwException = false)
{
workbook.NullCheck("workbook");
workSheetName.CheckStringIsNullOrEmpty("workSheetName");
WorkSheet sheet = workbook.Sheets[workSheetName];
sheet.NullCheck(string.Format("不存在指定的{0}工薄！", workSheetName));
return GetRangeValuesAsTable(sheet, beginAddress, throwException);
}
/// <summary>
/// 导入Open Xml格式的文件
/// </summary>
/// <param name="importStream"></param>
/// <param name="notifier"></param>
private void ImportFromExcel2007(Stream importStream, Action notifier)
{
WorkBook workbook = WorkBook.Load(importStream);
SOARole role = null;
ServiceBrokerContext.Current.SaveContextStates();
try
{
ServiceBrokerContext.Current.UseLocalCache = false;
ServiceBrokerContext.Current.UseServerCache = false;
if (this.AppCodeName.IsNotEmpty() && this.RoleCodeName.IsNotEmpty())
{
role = new SOARole(this.AppCodeName + ":" + this.RoleCodeName);
}
else
{
role = new SOARole(this.Definition)
{
ID = RoleID
}
};
if (NeedValidateSource)
{
CheckImportSource(role, workbook);
}
DataTable dt = DocumentHelper.GetRangeValuesAsTable(workbook, "Matrix", "A3");
role.Rows.Clear();
if (this.Definition == null || this.Definition.Count == 0)
{
role.PropertyDefinitions.FromDataColumns(dt.Columns);
}
role.Rows.FromDataTable(dt.Rows, role.PropertyDefinitions, (dr, sr) =>
{
if (notifier != null)
{
notifier();
}
return(true);
});
//int rowIndex = 0;
//foreach (DataRow row in dt.Rows)
//{
// SOARolePropertyRow mRow = new SOARolePropertyRow(role) { RowNumber = rowIndex };
// foreach (var dimension in this.Definition)
// {
// SOARolePropertyValue mCell = new SOARolePropertyValue(dimension);
// mCell.Value = row[dimension.Name].ToString();
// switch (dimension.Name)
// {
// case "Operator":
// mRow.Operator = row[dimension.Name].ToString();
// break;
// case "OperatorType":
// SOARoleOperatorType opType = SOARoleOperatorType.User;
// Enum.TryParse(row[dimension.Name].ToString(), out opType);
// mRow.OperatorType = opType;
// break;
// default:
// break;
// }
// mRow.Values.Add(mCell);
// }
// if (notifier != null)
// {
// notifier();
// }
// rowIndex++;
// role.Rows.Add(mRow);
//}
//插入记录
SOARolePropertyDefinitionAdapter.Instance.Update(role, role.PropertyDefinitions);
SOARolePropertiesAdapter.Instance.Update(role);
}
finally
{
ServiceBrokerContext.Current.RestoreSavedStates();
}
}
private void frmGlavna_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.F9)
{
DialogResult result;
result = MessageBox.Show("Želite prijenos podataka u excel?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
if (result == DialogResult.Yes)
{
Form activeChild = this.ActiveMdiChild;
// If there is an active child form, find the active control, which
// in this example should be a RichTextBox.
if (activeChild != null)
{
try
{
DevExpress.XtraGrid.GridControl tablica = (DevExpress.XtraGrid.GridControl)activeChild.ActiveControl;
if (tablica != null)
{
// Put the selected text on the Clipboard.
//Clipboard.SetDataObject(tablica.DataSource);
WorkBook m_book = new WorkBook();
//Export DataTable.
if (tablica.DataSource != null)
{
m_book.ImportDataTable((DataTable)tablica.DataSource, true, 1, 1, -1, -1);
}
else
{
MessageBox.Show("Nema podataka za export u excel!", "Error");
return;
}
//Saving the workbook to disk.
m_book.write("C:\\Podaci\\Podaci.xls");
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Želite li pogledati podatke?", "Dokument je kreiran",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("C:\\Podaci\\Podaci.xls");
}
}
}
catch
{
MessageBox.Show("Morate selektirati tablicu !!!");
}
}
}
}
}
private ulong createSheet(string name)
{
var Id = WorkBook.Count > 0 ? WorkBook.Max(x => x.Id) + 1 : 1;
// 向缓存中添加该Id
WorkBook.Add(new WorkBook
{
Id = Id,
Name = name,
//修复创建Sheet的无法找到Root的Bug
Target = $"worksheets/sheet{Id}.xml"
});
// 添加sheetX.xml
var e = ZipArchive.CreateEntry($"xl/worksheets/sheet{Id}.xml", CompressionLevel.Optimal);
using (var stream = e.Open())
using (var sw = new StreamWriter(stream))
{
sw.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<worksheet xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" xmlns:r=""http://schemas.openxmlformats.org/officeDocument/2006/relationships"" xmlns:xdr=""http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"" xmlns:x14=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"" xmlns:mc=""http://schemas.openxmlformats.org/markup-compatibility/2006"">
<sheetPr />
<dimension ref=""A1"" />
<sheetViews>
<sheetView workbookViewId=""0"">
<selection activeCell=""A1"" sqref=""A1"" />
</sheetView>
</sheetViews>
<sheetFormatPr defaultColWidth=""9"" defaultRowHeight=""13.5"" />
<sheetData>
</sheetData>
<pageMargins left=""0.75"" right=""0.75"" top=""1"" bottom=""1"" header=""0.511805555555556"" footer=""0.511805555555556"" />
<headerFooter />
</worksheet>");
}
// 向[Content_Types].xml中添加sheetX.xml
var e2 = ZipArchive.GetEntry("[Content_Types].xml");
using (var stream = e2.Open())
using (var sr = new StreamReader(stream))
{
var xd = new XmlDocument();
xd.LoadXml(sr.ReadToEnd());
var element = xd.CreateElement("Override", xd.DocumentElement.NamespaceURI);
element.SetAttribute("PartName", $"/xl/worksheets/sheet{Id}.xml");
element.SetAttribute("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");
var tmp = xd.GetElementsByTagName("Types")
.Cast <XmlNode>()
.First()
.AppendChild(element);
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
string identifier = "rId";
// 向xl/rels中添加
var e5 = ZipArchive.GetEntry("xl/_rels/workbook.xml.rels");
using (var stream = e5.Open())
using (var sr = new StreamReader(stream))
{
var result = sr.ReadToEnd();
var xd = new XmlDocument();
xd.LoadXml(result);
var relationships = xd.GetElementsByTagName("Relationships")
.Cast <XmlNode>()
.First();
identifier += (relationships.ChildNodes.Count + 1).ToString();
var element = xd.CreateElement("Relationship", xd.DocumentElement.NamespaceURI);
element.SetAttribute("Target", $"worksheets/sheet{Id}.xml");
element.SetAttribute("Type", "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet");
element.SetAttribute("Id", identifier);
relationships.AppendChild(element);
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
// 向workbook.xml添加sheetX.xml
var e3 = ZipArchive.GetEntry("xl/workbook.xml");
using (var stream = e3.Open())
using (var sr = new StreamReader(stream))
{
var xd = new XmlDocument();
xd.LoadXml(sr.ReadToEnd());
var tmp = xd.GetElementsByTagName("sheets")
.Cast <XmlNode>()
.First();
var element = xd.CreateElement("sheet", xd.DocumentElement.NamespaceURI);
tmp.AppendChild(element);
var attr = xd.CreateAttribute("r", "id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
attr.Value = identifier;
element.Attributes.Append(attr);
element.SetAttribute("sheetId", Id.ToString());
element.SetAttribute("name", name);
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
// 向app.xml中添加sheetX.xml
var e4 = ZipArchive.GetEntry("docProps/app.xml");
using (var stream = e4.Open())
using (var sr = new StreamReader(stream))
{
var result = sr.ReadToEnd();
var xd = new XmlDocument();
xd.LoadXml(result);
var element = xd.CreateElement("vt:lpstr", "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes");
element.InnerText = name;
var tmp = xd
.GetElementsByTagName("vt:vector")
.Cast <XmlNode>()
.Single(x => x.Attributes["baseType"].Value == "lpstr");
tmp.AppendChild(element);
tmp.Attributes["size"].Value = (Convert.ToInt32(tmp.Attributes["size"].Value) + 1).ToString();
var tmp2 = xd.GetElementsByTagName("vt:i4")
.Cast <XmlNode>()
.Single();
tmp2.InnerText = (Convert.ToInt32(tmp2.InnerText) + 1).ToString();
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
return(Id);
}
private void ExcelExport()
{
DateTime datumOD = Convert.ToDateTime(dtpOD.Text);
DateTime datumDO = Convert.ToDateTime(dtpDO.Text);
this.pregledNivelacijeTableAdapter.Fill(this.artikliKojiSeMoguNivelirati.pregledNivelacije, datumOD, datumDO);
//veza.ExecuteQuery("select dozvoljenanivelacijazag.dnz_ID, dozvoljenanivelacijazag.dnz_opis, dozvoljenanivelacijazag.dnz_odkada as 'Od kada', dozvoljenanivelacijazag.dnz_dokada as 'Do kada', artikl.AR_SIFRA, artikl.AR_NAZIV, dozvoljenanivelacija.dn_preporucenacijena, poslovnica.po_sifra, poslovnica.po_naziv from dozvoljenanivelacijazag, dozvoljenanivelacija left join artikl on artikl.ar_ID = dozvoljenanivelacija.artikl_AR_ID left join poslovnica on poslovnica.po_id= dozvoljenanivelacija.poslovnica_po_ID where dozvoljenanivelacija.dozvoljenaNivelacijaZag_dnz_id =dozvoljenanivelacijazag.dnz_ID and dozvoljenanivelacijazag.dnz_odkada >= '"+datod+"' and dozvoljenanivelacijazag.dnz_dokada <= '"+datdo+"'", ref podaci);
var bindingSource = (BindingSource)this.dgPomocni.DataSource;
var dataTable = ((DataView)bindingSource.List).Table;
WorkBook m_book = new WorkBook();
//Export DataTable.
if (this.dgPomocni != null)
{
m_book.ImportDataTable((DataTable)dataTable, true, 1, 1, -1, -1);
}
else
{
MessageBox.Show("Nema podataka za export u excel!", "Error");
return;
}
//Saving the workbook to disk.
//m_book.write("C:\\Podaci\\Podaci.xls");
dgPomocni.ExportToExcelOld(@"c:\Podaci\Podaci.xls");
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Želite li pogledati podatke?", "Dokument je kreiran", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("C:\\Podaci\\Podaci.xls");
}
}
private void button1_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Exportar para EXCEL ?", "Exportar", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == (DialogResult.Yes))
{
if (dtgContasReceber.Rows.Count == 0)
{
MessageBox.Show("NÃO A DADOS PARA EXPORTAR", "ATENÇÃO");
}
else
{
SaveFileDialog salvar = new SaveFileDialog(); // novo
Microsoft.Office.Interop.Excel.Application App; // Aplicação Excel
Microsoft.Office.Interop.Excel.Workbook WorkBook; // Pasta
Microsoft.Office.Interop.Excel.Worksheet WorkSheet; // Planilha
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
/* passa as celulas do DataGridView para a Pasta do Excel
* for (i = 0; i <= dgvconsulta.RowCount - 1; i++)
* {
*
*
* for (j = 0; j <= dgvconsulta.ColumnCount - 1; j++)
* {
* DataGridViewCell cell = dgvconsulta[j, i];
* WorkSheet.Cells[i + 1, j + 1] = cell.Value;
* }
* }*/
// passa as celulas do DataGridView para a Pasta do Excel
for (j = 0; j < dtgContasReceber.ColumnCount; j++)
{
WorkSheet.Cells[1, j + 1] = dtgContasReceber.Columns[j].HeaderText;
}
for (i = 1; i <= dtgContasReceber.RowCount; i++)
{
for (j = 0; j < dtgContasReceber.ColumnCount; j++)
{
DataGridViewCell cell = dtgContasReceber[j, i - 1];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
// define algumas propriedades da caixa salvar
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
//salvar.ShowDialog(); // mostra
// salva o arquivo
if (salvar.ShowDialog(this) == DialogResult.OK)
{
WorkBook.SaveAs(salvar.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit(); // encerra o excel
MessageBox.Show("Exportado com sucesso!");
}
else
{
WorkBook.Close(false, misValue, misValue);
App.Quit(); // encerra o excel
}
}
}
}
protected abstract void WriteData(WorkBook wb, IList<ComparisonHistoryModel> data);
/// <summary> /// writes the excel data into the workbook /// </summary> /// <param name="wb"></param> protected abstract void WriteData(WorkBook wb, EvaluationRunModel model);
public static void LoadExcel(ArrayList path)
{
DataTable dt = new DataTable();
DataColumn dc = dt.Columns.Add("studentid", typeof(int));
//dc.Unique = true;
dt.Columns.Add("firstname", typeof(string));
dt.Columns.Add("lastname", typeof(string));
dt.Columns.Add("gpa", typeof(double));
dt.Columns.Add("email", typeof(string));
IWorkbook WorkBook = null;
double cntProgress = 20;
double cntTotal = 0;
try
{
cntTotal = GetTotalCount(path);
for (int i = 0; i < path.Count; i++)
{
string[] checkPath = path[i].ToString().Split('\\');
string[] extSplit = checkPath[(checkPath.Length - 1)].ToString().Split('.');
using (FileStream file = new FileStream(path[i].ToString(), FileMode.Open, FileAccess.Read))
{
if (extSplit[1].ToString().ToUpper().Equals("XLSX"))
{
WorkBook = new XSSFWorkbook(file);
}
else if (extSplit[1].ToString().ToUpper().Equals("XLS"))
{
WorkBook = new HSSFWorkbook(file);
}
var sheet = WorkBook.GetSheet("Report1");
//cntTotal = Convert.ToDouble(sheet.LastRowNum);
Program._MainMenu.lblStatus.InvokeEx(x => x.Text = "Reading Excel Files..");
for (int row = 0; row < sheet.LastRowNum - 1; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
//cntTotal--;
if (row == 0 || row == 1)
{
Program._MainMenu.progressBar.InvokeEx(x => x.Value = Convert.ToInt32(cntProgress));
}
else
{
cntProgress += (Double)(30 / Convert.ToDouble(cntTotal));
Program._MainMenu.progressBar.InvokeEx(x => x.Value = Convert.ToInt32(cntProgress));
}
DataRow dr = dt.NewRow();
try{ dr["studentid"] = Convert.ToInt32(sheet.GetRow(row + 1).GetCell(0).NumericCellValue.ToString()); }
catch { dr["studentid"] = Convert.ToInt32(sheet.GetRow(row + 1).GetCell(0).StringCellValue.ToString()); }
dr["firstname"] = sheet.GetRow(row + 1).GetCell(1).StringCellValue.ToString();
dr["lastname"] = sheet.GetRow(row + 1).GetCell(2).StringCellValue.ToString();
try{ dr["gpa"] = Convert.ToDouble(sheet.GetRow(row + 1).GetCell(3).NumericCellValue.ToString()); }
catch { dr["gpa"] = Convert.ToDouble(sheet.GetRow(row + 1).GetCell(3).StringCellValue.ToString()); }
dr["email"] = sheet.GetRow(row + 1).GetCell(4).StringCellValue.ToString();
dt.Rows.Add(dr);
}
}
}
}
SaveExcelToDb(dt, cntTotal, cntProgress);
}
catch
{
}
}
private void exportarTabela()
{
try
{
progress.Value = progress.Minimum;
btSorteio.Enabled = false;
grSorteio.Visible = false;
grProgress.Visible = true;
grProcesso.Text = "Atualizar Banco de Dados...";
wizardPage1.Refresh();
List <Dados> dadosModelo = new List <Dados>();
dadosModelo.Add(new Dados {
NOME = "NOME", EVENTO = "EVENTO", SORTEADO = "SORT"
});
int cont = 0;
while (cont < dgv.RowCount)
{
dadosModelo.Add(new Dados {
NOME = dgv.Rows[cont].Cells[0].Value.ToString(), EVENTO = dgv.Rows[cont].Cells[1].Value.ToString(), SORTEADO = dgv.Rows[cont].Cells[2].Value.ToString()
});
cont++;
}
BindingSource bs = new BindingSource();
bs.DataSource = dadosModelo;
dgv.DataSource = dadosModelo;
SaveFileDialog salvar = new SaveFileDialog();
Microsoft.Office.Interop.Excel.Application App;
Microsoft.Office.Interop.Excel.Workbook WorkBook;
Microsoft.Office.Interop.Excel.Worksheet WorkSheet;
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
progress.Maximum = dgv.RowCount + 1;
for (i = 0; i <= dgv.RowCount - 1; i++)
{
for (j = 0; j <= dgv.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgv[j, i];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
progress.Value = i;
}
WorkBook.SaveAs(txFile.Text, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit();
progress.Value = progress.Maximum;
MessageBox.Show(null, "O arquivo atualizado foi salvo em:" + '\n' + txFile.Text, "Arquivo Gerado", MessageBoxButtons.OK, MessageBoxIcon.Information);
grProgress.Visible = false;
grSorteio.Visible = true;
btSorteio.Enabled = true;
}
catch (Exception exception)
{
MessageBox.Show(null, exception.ToString(), "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void LadTable(string filePath)
{
//
WorkBook workbook = WorkBook.Load(filePath);
WorkSheet sheet = workbook.WorkSheets.First();
//int cellValue = sheet["A1"].IntValue;
int i = 0;
int j = 0;
foreach (var cell in sheet["A1:G26"])
{
arrayData[i, j] = cell.Text;
//Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
//Console.WriteLine("Cell {0}:{1} has value '{2}'", i,j, cell.Text);
j++;
if (j > 6)
{
j = 0;
i++;
}
}
for (int ii = 0; ii < 26; ii++)
{
FirstdataGridView.Rows.Add(new object[] { arrayData[ii, 0], arrayData[ii, 1], arrayData[ii, 2], arrayData[ii, 3], arrayData[ii, 4], arrayData[ii, 5], arrayData[ii, 6] });
}
//заголовки коррел матрицы
string subText1;
string subText2;
string subText3;
string subText4;
string subText5;
string subText6;
int pos;
string text;
text = arrayData[0, 1];
pos = text.IndexOf('\n');
subText1 = text.Substring(0, pos);
CorreldataGridView.Columns[1].HeaderText = subText1;
text = arrayData[0, 2];
pos = text.IndexOf('\n');
subText2 = text.Substring(0, pos);
CorreldataGridView.Columns[2].HeaderText = subText2;
text = arrayData[0, 3];
pos = text.IndexOf('\n');
subText3 = text.Substring(0, pos);
CorreldataGridView.Columns[3].HeaderText = subText3;
text = arrayData[0, 4];
pos = text.IndexOf('\n');
subText4 = text.Substring(0, pos);
CorreldataGridView.Columns[4].HeaderText = subText4;
text = arrayData[0, 5];
pos = text.IndexOf('\n');
subText5 = text.Substring(0, pos);
CorreldataGridView.Columns[5].HeaderText = subText5;
text = arrayData[0, 6];
pos = text.IndexOf('\n');
subText6 = text.Substring(0, pos);
CorreldataGridView.Columns[6].HeaderText = subText6;
double[,] correlMatrix = new double[6, 7];
//for(int iii = 0; iii < 6; iii++) {
// for (int jjj = 0; jjj < 7; jjj++) {
// correlMatrix[iii, jjj] = iii * jjj;
// Console.WriteLine(correlMatrix[iii, jjj]);
// }
//}
double[] dataColumn1 = new double[25];
double[] dataColumn2 = new double[25];
double[] dataColumn3 = new double[25];
double[] dataColumn4 = new double[25];
double[] dataColumn5 = new double[25];
double[] dataColumn6 = new double[25];
for (int rowi = 0; rowi < 25; rowi++)
{
dataColumn1[rowi] = Convert.ToDouble(arrayData[rowi + 1, 1]);
dataColumn2[rowi] = Convert.ToDouble(arrayData[rowi + 1, 2]);
dataColumn3[rowi] = Convert.ToDouble(arrayData[rowi + 1, 3]);
dataColumn4[rowi] = Convert.ToDouble(arrayData[rowi + 1, 4]);
dataColumn5[rowi] = Convert.ToDouble(arrayData[rowi + 1, 5]);
dataColumn6[rowi] = Convert.ToDouble(arrayData[rowi + 1, 6]);
}
correlMatrix[0, 0] = ComputeCoeff(dataColumn1, dataColumn1);
correlMatrix[0, 1] = ComputeCoeff(dataColumn1, dataColumn2);
correlMatrix[0, 2] = ComputeCoeff(dataColumn1, dataColumn3);
correlMatrix[0, 3] = ComputeCoeff(dataColumn1, dataColumn4);
correlMatrix[0, 4] = ComputeCoeff(dataColumn1, dataColumn5);
correlMatrix[0, 5] = ComputeCoeff(dataColumn1, dataColumn6);
CorreldataGridView.Rows.Add(new object[] { subText1, Math.Round(correlMatrix[0, 0], 2), Math.Round(correlMatrix[0, 1], 2), Math.Round(correlMatrix[0, 2], 2), Math.Round(correlMatrix[0, 3], 2), Math.Round(correlMatrix[0, 4], 2), Math.Round(correlMatrix[0, 5], 2), Math.Round(correlMatrix[0, 6], 2) });
correlMatrix[1, 0] = correlMatrix[0, 1];
correlMatrix[1, 1] = ComputeCoeff(dataColumn2, dataColumn2);
correlMatrix[1, 2] = ComputeCoeff(dataColumn2, dataColumn3);
correlMatrix[1, 3] = ComputeCoeff(dataColumn2, dataColumn4);
correlMatrix[1, 4] = ComputeCoeff(dataColumn2, dataColumn5);
correlMatrix[1, 5] = ComputeCoeff(dataColumn2, dataColumn6);
CorreldataGridView.Rows.Add(new object[] { subText2, Math.Round(correlMatrix[1, 0], 2), Math.Round(correlMatrix[1, 1], 2), Math.Round(correlMatrix[1, 2], 2), Math.Round(correlMatrix[1, 3], 2), Math.Round(correlMatrix[1, 4], 2), Math.Round(correlMatrix[1, 5], 2), Math.Round(correlMatrix[1, 6], 2) });
correlMatrix[2, 0] = correlMatrix[0, 2];
correlMatrix[2, 1] = correlMatrix[1, 2];
correlMatrix[2, 2] = ComputeCoeff(dataColumn3, dataColumn3);
correlMatrix[2, 3] = ComputeCoeff(dataColumn3, dataColumn4);
correlMatrix[2, 4] = ComputeCoeff(dataColumn3, dataColumn5);
correlMatrix[2, 5] = ComputeCoeff(dataColumn3, dataColumn6);
CorreldataGridView.Rows.Add(new object[] { subText3, Math.Round(correlMatrix[2, 0], 2), Math.Round(correlMatrix[2, 1], 2), Math.Round(correlMatrix[2, 2], 2), Math.Round(correlMatrix[2, 3], 2), Math.Round(correlMatrix[2, 4], 2), Math.Round(correlMatrix[2, 5], 2), Math.Round(correlMatrix[2, 6], 2) });
correlMatrix[3, 0] = correlMatrix[0, 3];
correlMatrix[3, 1] = correlMatrix[1, 3];
correlMatrix[3, 2] = correlMatrix[2, 3];
correlMatrix[3, 3] = ComputeCoeff(dataColumn4, dataColumn4);
correlMatrix[3, 4] = ComputeCoeff(dataColumn4, dataColumn5);
correlMatrix[3, 5] = ComputeCoeff(dataColumn4, dataColumn6);
CorreldataGridView.Rows.Add(new object[] { subText4, Math.Round(correlMatrix[3, 0], 2), Math.Round(correlMatrix[3, 1], 2), Math.Round(correlMatrix[3, 2], 2), Math.Round(correlMatrix[3, 3], 2), Math.Round(correlMatrix[3, 4], 2), Math.Round(correlMatrix[3, 5], 2), Math.Round(correlMatrix[3, 6], 2) });
correlMatrix[4, 0] = correlMatrix[0, 4];
correlMatrix[4, 1] = correlMatrix[1, 4];
correlMatrix[4, 2] = correlMatrix[2, 4];
correlMatrix[4, 3] = correlMatrix[3, 4];
correlMatrix[4, 4] = ComputeCoeff(dataColumn5, dataColumn5);
correlMatrix[4, 5] = ComputeCoeff(dataColumn5, dataColumn6);
CorreldataGridView.Rows.Add(new object[] { subText5, Math.Round(correlMatrix[4, 0], 2), Math.Round(correlMatrix[4, 1], 2), Math.Round(correlMatrix[4, 2], 2), Math.Round(correlMatrix[4, 3], 2), Math.Round(correlMatrix[4, 4], 2), Math.Round(correlMatrix[4, 5], 2), Math.Round(correlMatrix[4, 6], 2) });
correlMatrix[5, 0] = correlMatrix[0, 5];
correlMatrix[5, 1] = correlMatrix[1, 5];
correlMatrix[5, 2] = correlMatrix[2, 5];
correlMatrix[5, 3] = correlMatrix[3, 5];
correlMatrix[5, 4] = correlMatrix[4, 5];
correlMatrix[5, 5] = ComputeCoeff(dataColumn6, dataColumn6);
CorreldataGridView.Rows.Add(new object[] { subText5, Math.Round(correlMatrix[5, 0], 2), Math.Round(correlMatrix[5, 1], 2), Math.Round(correlMatrix[5, 2], 2), Math.Round(correlMatrix[5, 3], 2), Math.Round(correlMatrix[5, 4], 2), Math.Round(correlMatrix[5, 5], 2), Math.Round(correlMatrix[5, 6], 2) });
//проход по матрице и установка цветов
for (int dgi = 0; dgi < 6; dgi++)
{
for (int dgj = 1; dgj < 7; dgj++)
{
if (Convert.ToDouble(CorreldataGridView.Rows[dgi].Cells[dgj].Value) == 1)
{
CorreldataGridView.Rows[dgi].Cells[dgj].Style.BackColor = Color.Firebrick;
}
if (Convert.ToDouble(CorreldataGridView.Rows[dgi].Cells[dgj].Value) < 1)
{
CorreldataGridView.Rows[dgi].Cells[dgj].Style.BackColor = Color.ForestGreen;
}
if (Convert.ToDouble(CorreldataGridView.Rows[dgi].Cells[dgj].Value) < 0.9)
{
CorreldataGridView.Rows[dgi].Cells[dgj].Style.BackColor = Color.GreenYellow;
}
if (Convert.ToDouble(CorreldataGridView.Rows[dgi].Cells[dgj].Value) < 0.7)
{
CorreldataGridView.Rows[dgi].Cells[dgj].Style.BackColor = Color.LightPink;
}
if (Convert.ToDouble(CorreldataGridView.Rows[dgi].Cells[dgj].Value) < 0.5)
{
CorreldataGridView.Rows[dgi].Cells[dgj].Style.BackColor = Color.SlateGray;
}
if (Convert.ToDouble(CorreldataGridView.Rows[dgi].Cells[dgj].Value) < 0.3)
{
CorreldataGridView.Rows[dgi].Cells[dgj].Style.BackColor = Color.Tan;
}
}
}
// генерируем глупый текст
// наименьший колинеарн коеф что к чему
// наименьший колинеарн коеф что к чему
// ищем наименьшее и наибольшее
double smallest = 0;
int smallesti = 0;
int smallestj = 0;
double biggest = 0;
int biggesti = 0;
int biggestj = 0;
smallest = correlMatrix[0, 0];
biggest = 0;
for (int cmi = 0; cmi < 6; cmi++)
{
for (int cmj = 0; cmj < 6; cmj++)
{
if (correlMatrix[cmi, cmj] < smallest)
{
smallest = correlMatrix[cmi, cmj];
smallesti = cmi;
smallestj = cmj;
}
if (correlMatrix[cmi, cmj] > biggest && correlMatrix[cmi, cmj] != 1)
{
biggest = correlMatrix[cmi, cmj];
biggesti = cmi;
biggestj = cmj;
}
}
}
string conclusion = "Найменший коефіціент: " + Math.Round(smallest, 2) + " " + getSubText(smallesti, subText1, subText2, subText3, subText4, subText5, subText6) + " до " + getSubText(smallestj, subText1, subText2, subText3, subText4, subText5, subText6);
conclusion += ", та найбыльший " + Math.Round(biggest, 2) + " " + getSubText(biggesti, subText1, subText2, subText3, subText4, subText5, subText6) + " до " + getSubText(biggestj, subText1, subText2, subText3, subText4, subText5, subText6);
textBox1.Text = conclusion;
}
private static void SetActiveSheet(string testName, WorkBook wb)
{
for (int i = 0; i < wb.NumSheets; i++)
{
if (testName == wb.getSheetName(i))
{
Console.WriteLine("Setting active sheet {0}", i);
wb.Sheet = i;
break;
}
}
}
protected void DownloadTemplate_Click(object sender, DirectEventArgs e)
{
try
{
var serverPath = Server.MapPath(relativePath + "/importHumanRecord.xlsx");
var workbook = new WorkBook();
var dataTable = new DataTable();
dataTable.Rows.Add();
dataTable.Rows.Add();
// create header by property name and description
foreach (var prop in typeof(hr_Record).GetProperties().Select((value, index) => new { value, index }))
{
dataTable.Columns.Add();
// get prop description
var attribute = prop.value.GetCustomAttribute(typeof(DescriptionAttribute));
if (attribute != null)
{
dataTable.Rows[0][prop.index] = ((DescriptionAttribute)attribute).Description;
}
dataTable.Rows[1][prop.index] = prop.value.Name;
// set column datatable name
dataTable.Columns[prop.index].ColumnName = prop.value.Name;
}
workbook.ImportDataTable(dataTable, false, 0, 0, dataTable.Rows.Count + 1, dataTable.Columns.Count + 1);
// set header style
RangeStyle range = workbook.getRangeStyle();
range.VerticalAlignment = RangeStyle.VerticalAlignmentCenter;
range.HorizontalAlignment = RangeStyle.HorizontalAlignmentCenter;
range.FontBold = true;
range.FontSize = 11 * 20;
workbook.setRangeStyle(range, 0, 0, 0, workbook.LastCol);
workbook.setSheetName(0, "Thêm mới thông tin hồ sơ");
workbook.insertSheets(1, 1);
workbook.setSheetName(1, "Info");
workbook.SheetHidden = WorkBook.SheetStateHidden;
workbook.Sheet = 0;
foreach (DataColumn col in dataTable.Columns)
{
switch (col.ColumnName)
{
case nameof(hr_Record.DepartmentId):
CreateDropDownExcel("cat_Department", workbook, col);
break;
case nameof(hr_Record.ManagementDepartmentId):
CreateDropDownExcel("cat_Department", workbook, col);
break;
case nameof(hr_Record.Sex):
var validation = workbook.CreateDataValidation();
validation.Type = DataValidation.eUser;
var validateList = "\"Nam,Nữ\"";
validation.Formula1 = validateList;
workbook.setSelection(2, col.Ordinal, 50, col.Ordinal);
workbook.DataValidation = validation;
break;
case nameof(hr_Record.MaritalStatusId):
CreateDropDownExcel("cat_MaritalStatus", workbook, col);
break;
case nameof(hr_Record.ReligionId):
CreateDropDownExcel("cat_Religion", workbook, col);
break;
case nameof(hr_Record.PersonalClassId):
CreateDropDownExcel("cat_PersonalClass", workbook, col);
break;
case nameof(hr_Record.FolkId):
CreateDropDownExcel("cat_Folk", workbook, col);
break;
case nameof(hr_Record.FamilyClassId):
CreateDropDownExcel("cat_FamilyClass", workbook, col);
break;
case nameof(hr_Record.PositionId):
CreateDropDownExcel("cat_Position", workbook, col);
break;
case nameof(hr_Record.JobTitleId):
CreateDropDownExcel("cat_JobTitle", workbook, col);
break;
case nameof(hr_Record.BasicEducationId):
CreateDropDownExcel("cat_BasicEducation", workbook, col);
break;
case nameof(hr_Record.EducationId):
CreateDropDownExcel("cat_Education", workbook, col);
break;
case nameof(hr_Record.PoliticLevelId):
CreateDropDownExcel("cat_PoliticLevel", workbook, col);
break;
case nameof(hr_Record.ManagementLevelId):
CreateDropDownExcel("cat_ManagementLevel", workbook, col);
break;
case nameof(hr_Record.LanguageLevelId):
CreateDropDownExcel("cat_LanguageLevel", workbook, col);
break;
case nameof(hr_Record.ITLevelId):
CreateDropDownExcel("cat_ITLevel", workbook, col);
break;
case nameof(hr_Record.CPVPositionId):
CreateDropDownExcel("cat_CPVPosition", workbook, col);
break;
case nameof(hr_Record.VYUPositionId):
CreateDropDownExcel("cat_VYUPosition", workbook, col);
break;
case nameof(hr_Record.ArmyLevelId):
CreateDropDownExcel("cat_ArmyLevel", workbook, col);
break;
case nameof(hr_Record.HealthStatusId):
CreateDropDownExcel("cat_HealthStatus", workbook, col);
break;
case nameof(hr_Record.FamilyPolicyId):
CreateDropDownExcel("cat_FamilyPolicy", workbook, col);
break;
case nameof(hr_Record.WorkStatusId):
CreateDropDownExcel("cat_WorkStatus", workbook, col);
break;
case nameof(hr_Record.IndustryId):
CreateDropDownExcel("cat_Industry", workbook, col);
break;
default:
break;
}
}
// auto resize columns
for (var i = 0; i < workbook.LastCol; i++)
{
workbook.setColWidthAutoSize(i, true);
}
// hide prop name row
workbook.setRowHidden(1, true);
workbook.writeXLSX(serverPath);
Response.AddHeader("Content-Disposition", "attachment; filename=" + "importHumanRecord.xlsx");
Response.WriteFile(serverPath);
Response.End();
}
catch (Exception ex)
{
Dialog.ShowError(ex.Message);
}
}
public static WorkBook Load(string fromPath)
{
WorkBook newWorkBook = new WorkBook("");
SqliteConnectionStringBuilder connectionString = new SqliteConnectionStringBuilder();
connectionString.DataSource = fromPath;
using (var connection = new SqliteConnection(connectionString.ToString()))
{
connection.Open();
// for now only one exists in each file, if we change that approach, we'll need to update this query
newWorkBook = connection.Query <WorkBook>($@"SELECT [Id], [Name], [Notes] FROM WorkBook").FirstOrDefault();
var signals = connection.Query <Signal>($"SELECT * from Signals WHERE WorkBookId = '{newWorkBook.Id}'");
var windowedSyncFilters = connection.Query <Models.WindowedSyncFilter>($"SELECT * from Filters INNER JOIN WindowedSyncFilterParameters ON FilterId = Filters.Id WHERE WorkBookId = '{newWorkBook.Id}' AND FilterType in ('LOWPASS', 'HIGHPASS')");
var customFilters = connection.Query <Models.CustomFilter>($"SELECT * from Filters WHERE WorkBookId = '{newWorkBook.Id}' AND FilterType = 'CUSTOM'");
var settings = connection.Query <Setting>($"SELECT Key, Value from Settings WHERE WorkBookId = '{newWorkBook.Id}'");
foreach (Signal signal in signals)
{
var signalValues = connection.Query <SignalValue>($"SELECT * from SignalValues WHERE SignalId = {signal.Id} ORDER BY Id ASC");
signal.Samples = new List <double>(signalValues.Count());
foreach (SignalValue value in signalValues)
{
signal.Samples.Add(value.Value);
}
newWorkBook.Signals.Add(signal.Name, signal);
}
foreach (Models.CustomFilter filter in customFilters)
{
var magPhaseValues = connection.Query <MagPhase>($"SELECT Magnitude, Phase FROM MagnitudePhase WHERE FilterId='{filter.Id}' ORDER by Sequence ASC");
List <Tuple <double, double> > valuesInList = new List <Tuple <double, double> >();
foreach (MagPhase value in magPhaseValues)
{
valuesInList.Add(new Tuple <double, double>(value.Magnitude, value.Phase));
}
filter.UpdateMagnitudePhaseList(valuesInList);
}
foreach (Models.WindowedSyncFilter filter in windowedSyncFilters)
{
newWorkBook.WindowedSyncFilters.Add(filter.Name, filter);
}
foreach (Models.CustomFilter filter in customFilters)
{
newWorkBook.CustomFilters.Add(filter.Name, filter);
}
foreach (Setting setting in settings)
{
newWorkBook.Settings.Add(setting.Key, setting.Value);
}
}
return(newWorkBook);
}
/// <summary>
/// 删除Sheet
/// </summary>
/// <param name="Id">Sheet名称对应的Id</param>
public void RemoveSheet(ulong Id)
{
var name = WorkBook.Where(x => x.Id == Id).First().Name;
// 从ExcelStream对象中删除
WorkBook.Remove(WorkBook.Where(x => x.Id == Id).First());
// 从workbook.xml中删除
var e = ZipArchive.GetEntry("xl/workbook.xml");
using (var stream = e.Open())
{
var sr = new StreamReader(stream);
var result = sr.ReadToEnd();
var xd = new XmlDocument();
xd.LoadXml(result);
var tmp = xd
.GetElementsByTagName("sheet")
.Cast <XmlNode>()
.Single(x => x.Attributes["sheetId"].Value == Id.ToString());
tmp.ParentNode.RemoveChild(tmp);
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
// 删除sheetX.xml
var e2 = ZipArchive.GetEntry($"xl/worksheets/sheet{Id}.xml");
e2.Delete();
// 从[Content_Types].xml中删除
var e3 = ZipArchive.GetEntry("[Content_Types].xml");
using (var stream = e3.Open())
{
var sr = new StreamReader(stream);
var result = sr.ReadToEnd();
var xd = new XmlDocument();
xd.LoadXml(result);
var tmp = xd
.GetElementsByTagName("Override")
.Cast <XmlNode>()
.Single(x => x.Attributes["PartName"].Value == $"/xl/worksheets/sheet{Id}.xml");
tmp.ParentNode.RemoveChild(tmp);
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
// 从app.xml中移除
var e4 = ZipArchive.GetEntry("docProps/app.xml");
using (var stream = e4.Open())
using (var sr = new StreamReader(stream))
{
var result = sr.ReadToEnd();
var xd = new XmlDocument();
xd.LoadXml(result);
var tmp = xd
.GetElementsByTagName("vt:lpstr")
.Cast <XmlNode>()
.Single(x => x.InnerText == name);
tmp.ParentNode.Attributes["size"].Value = (Convert.ToInt32(tmp.ParentNode.Attributes["size"].Value) - 1).ToString();
tmp.ParentNode.RemoveChild(tmp);
var tmp2 = xd.GetElementsByTagName("vt:i4")
.Cast <XmlNode>()
.Single();
tmp2.InnerText = (Convert.ToInt32(tmp2.InnerText) - 1).ToString();
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
// 重新整理xl/rels
var e5 = ZipArchive.GetEntry("xl/_rels/workbook.xml.rels");
using (var stream = e5.Open())
using (var sr = new StreamReader(stream))
{
var result = sr.ReadToEnd();
var xd = new XmlDocument();
xd.LoadXml(result);
var relationships = xd.GetElementsByTagName("Relationships")
.Cast <XmlNode>()
.First();
var sheetX = relationships.ChildNodes
.Cast <XmlNode>()
.Single(x => x.Attributes["Target"].Value == $"worksheets/sheet{Id}.xml");
relationships.RemoveChild(sheetX);
stream.Position = 0;
stream.SetLength(0);
xd.Save(stream);
}
}
/// <summary>
/// write
/// </summary>
/// <param name="wb"></param>
/// <param name="compHistory"></param>
/// <param name="columnOffset"></param>
private void WriteDataColumns(WorkBook wb, ComparisonHistoryModel compHistory, int columnOffset)
{
var list = compHistory.Data.ToList();
Console.WriteLine("Data column: {0}, {1}, count: {2}", compHistory.Name, columnOffset, list.Count);
wb.setEntry(0, columnOffset, "EvalRun ID");
wb.setEntry(0, columnOffset + 1, "Label");
wb.setEntry(0, columnOffset + 2, string.Format("Result {0}", compHistory.Name));
for(int i = 0; i < list.Count; i++)
{
var tuple = list[i];
wb.setEntry(i + 1, columnOffset, tuple.EvaluationRunID.ToString(CultureInfo.InvariantCulture));
wb.setEntry(i + 1, columnOffset + 1, tuple.EvaluationRunLabel);
wb.setEntry(i + 1, columnOffset + 2, tuple.Result.ToString(CultureInfo.InvariantCulture));
}
}
private static void CheckImportSource(SOARole role, WorkBook workbook)
{
string roleFullCodeName = GetRoleFullCodeName(role);
if (roleFullCodeName.IsNotEmpty())
{
string excelSource = workbook.FileDetails.Subject;
if (excelSource.IsNotEmpty())
{
if (string.Compare(roleFullCodeName, excelSource, true) != 0)
throw new InvalidOperationException(string.Format("导入的角色矩阵的来源是{0}，和目标{1}不匹配。如果需要强制导入，请去掉\"校验导入的矩阵是否是相同的来源\"选择框",
excelSource, roleFullCodeName));
}
}
}
protected override void WriteData(WorkBook wb, EvaluationRunModel model)
{
throw new NotImplementedException("not supported for this export, please use multi method");
}
private void openFD_FileOk(object sender, CancelEventArgs e)
{
try
{
ASignal.Clear();
KursCurves.Clear();
comboSignalsAprox.Items.Clear();
var wb = new WorkBook();
wb.readXLSX(openFD.FileName);
DataTable dt = wb.ExportDataTable();
double t = 0;
double d = 0.01;
for (int j = 2; j < dt.Columns.Count; j++)
{
string key = dt.Rows[0][j].ToString();
comboSignalsAprox.Items.Add(key);
KursCurves.Add(key, new PointPairList());
t = 0;
for (int i = 1; i < dt.Rows.Count; i++, t += d)
{
object o = dt.Rows[i][j];
KursCurves[key].Add(new PointPair(i, Convert.ToDouble(o)));
}
}
comboSignalsAprox.SelectedIndex = 0;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void gerarModelo()
{
try
{
//GERAR MODELO
List <Dados> dadosModelo = new List <Dados>();
dadosModelo.Add(new Dados {
NOME = "NOME", EVENTO = "EVENTO", SORTEADO = "SORT"
});
dadosModelo.Add(new Dados {
NOME = "MODELO", EVENTO = "EVENTO", SORTEADO = "N"
});
BindingSource bs = new BindingSource();
bs.DataSource = dadosModelo;
dgv.DataSource = dadosModelo;
//SALVAR ARQUIVO
SaveFileDialog salvar = new SaveFileDialog();
Microsoft.Office.Interop.Excel.Application App; //EXCEL
Microsoft.Office.Interop.Excel.Workbook WorkBook; //PASTA
Microsoft.Office.Interop.Excel.Worksheet WorkSheet; //PLANILHA
object misValue = System.Reflection.Missing.Value;
App = new Microsoft.Office.Interop.Excel.Application();
WorkBook = App.Workbooks.Add(misValue);
WorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
//EXPORTAR EXCEL
for (i = 0; i <= dgv.RowCount - 1; i++)
{
for (j = 0; j <= dgv.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgv[j, i];
WorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
salvar.Title = "Exportar para Excel";
salvar.Filter = "Arquivo do Excel *.xls | *.xls";
salvar.ShowDialog();
string caminho = salvar.FileName;
WorkBook.SaveAs(caminho, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
WorkBook.Close(true, misValue, misValue);
App.Quit();
MessageBox.Show(null, "O arquivo gerado foi salvo em:" + '\n' + caminho, "Arquivo Gerado", MessageBoxButtons.OK, MessageBoxIcon.Information);
dgv.DataSource = null;
}
catch (Exception exception)
{
MessageBox.Show(null, exception.ToString(), "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
internal WorkBookStylesWrapper(WorkBook workbook)
{
this._WorkBook = workbook;
// NumberFormatXmlWrapper.AddBuildIn(this.NumberFormats);
}
public AppTheme(WorkBook workBook)
{
this._WorkBook = workBook;
}
public ExcelReader(WorkBook workBook)
{
this._WorkBook = workBook;
}
/// <summary>
/// Persist properties to database
/// </summary>
/// <param name="workBook"></param>
public static bool Update(WorkBook workBook)
{
if (workBook.FilePath == null)
{
throw new Exception("Filepath not set");
}
SqliteConnectionStringBuilder connectionString = new SqliteConnectionStringBuilder();
connectionString.DataSource = workBook.FilePath;
using (SqliteConnection sqlLiteConnection = new SqliteConnection(connectionString.ConnectionString))
{
sqlLiteConnection.Open();
string sql = $@"UPDATE WorkBook SET [Name] = @Name,
[Notes] = @Notes,
[UpdateDT] = datetime('now') WHERE Id=@Id";
using (var transaction = sqlLiteConnection.BeginTransaction())
{
try
{
SqliteCommand cmd = sqlLiteConnection.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@Name", workBook.Name);
cmd.Parameters.AddWithValue("@Notes", workBook.Notes ?? String.Empty);
cmd.Parameters.AddWithValue("@Id", workBook.Id);
cmd.ExecuteNonQuery();
// Save signals
foreach (Signal signal in workBook.Signals.Values)
{
SignalDAL.Create(workBook, signal, sqlLiteConnection);
}
// Save filters
foreach (Models.WindowedSyncFilter filter in workBook.WindowedSyncFilters.Values)
{
FilterDAL.Create(workBook, filter, sqlLiteConnection);
}
foreach (Models.CustomFilter filter in workBook.CustomFilters.Values)
{
FilterDAL.Create(workBook, filter, sqlLiteConnection);
}
foreach (string key in workBook.Settings.Keys)
{
SettingDAL.Create(workBook, key, workBook.Settings[key], sqlLiteConnection);
}
transaction.Commit();
} catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
return(true);
}
