/// <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); }