/// <summary> /// Funzione per il salvataggio dei valori originali del foglio prima di aver salvato le modifiche di incremento. /// </summary> /// <param name="Target">Sheet.</param> /// <param name="tableName">Range.</param> /// <param name="Categoria">Range.</param> public static void SaveOriginValues(Excel.Range Target, string tableName, string Categoria = "") { DefinedNames definedNames = new DefinedNames(Target.Worksheet.Name, DefinedNames.InitType.SaveDB); DataTable dt = Workbook.Repository[tableName]; foreach (Excel.Range r in Target) { string[] parts = definedNames.GetNameByAddress(r.Row, r.Column).Split(Simboli.UNION[0]); string data; if (parts.Length == 4) { data = Date.GetDataFromSuffisso(parts[2], parts[3]); } else { data = Date.GetDataFromSuffisso(parts[2], ""); } if (dt.Rows.Find(new object[] { parts[0], parts[1], data }) == null) { dt.Rows.Add(string.IsNullOrEmpty(Categoria) ? "" : Categoria, parts[0], parts[1], data, r.Value2, r.Comment == null ? "" : r.Comment.Text()); } } }
public static void ScriviStagione(int idStagione) { var sheetPrevisione = Workbook.Sheets.Cast <Excel.Worksheet>() .Where(s => s.Name == "Previsione") .FirstOrDefault(); bool wasProtected = sheetPrevisione.ProtectContents; if (wasProtected) { sheetPrevisione.Unprotect(Workbook.Password); } if (sheetPrevisione != null) { DefinedNames definedNames = new DefinedNames("Previsione"); DateTime dataFine = Workbook.DataAttiva.AddDays(Struct.intervalloGiorni); Range rng = definedNames.Get("CT_TORINO", "STAGIONE", Date.SuffissoDATA1, Date.GetSuffissoOra(1)).Extend(colOffset: Date.GetOreIntervallo(dataFine)); sheetPrevisione.Range[rng.ToString()].Value = idStagione; if (!Simboli.ModificaDati && DataBase.OpenConnection()) { Handler.StoreEdit(sheetPrevisione, sheetPrevisione.Range[rng.ToString()]); DataBase.SalvaModificheDB(); DataBase.CloseConnection(); } } if (wasProtected) { sheetPrevisione.Protect(Workbook.Password); } }
public static Dictionary <String, String> GetDefinedNames(String fileName) { // Given a workbook name, return a dictionary of defined names. // The pairs include the range name and a string representing the range. var returnValue = new Dictionary <String, String>(); // Open the spreadsheet document for read-only access. using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false)) { // Retrieve a reference to the workbook part. var wbPart = document.WorkbookPart; // Retrieve a reference to the defined names collection. DefinedNames definedNames = wbPart.Workbook.DefinedNames; // If there are defined names, add them to the dictionary. if (definedNames != null) { foreach (DefinedName dn in definedNames) { returnValue.Add(dn.Name.Value, dn.Text); } } } return(returnValue); }
public FormIncrementoMI(Excel.Worksheet ws, Excel.Range rng) { InitializeComponent(); this.Text = Simboli.NomeApplicazione + " - Incremento"; _ws = ws; _ws.SelectionChange += ChangeSelectionToIncrement; _definedNames = new DefinedNames(_ws.Name, DefinedNames.InitType.All); // Previene errori in caso di chiusura form non previsti -> errori non gestiti if (Workbook.Repository[MODIFICA] == null) { Workbook.Repository.Add(Workbook.Repository.CreaTabellaModifica(MODIFICA)); } else { Workbook.Repository[MODIFICA].Reset(); } if (Workbook.Repository[ALL_OLD_VALUE] == null) { Workbook.Repository.Add(Workbook.Repository.CreaTabellaRipristinaIncremento(ALL_OLD_VALUE)); } else { Workbook.Repository[ALL_OLD_VALUE].Reset(); } btnRipristina.Enabled = false; btnApplica.Enabled = false; ChangeSelectionToIncrement(Workbook.Application.Selection); }
private DataRow GetEntitaFromRange(Excel.Range rng) { DefinedNames d = _categoriaNomiDefiniti .Where(kv => kv.Value.Sheet == rng.Worksheet.Name) .Select(kv => kv.Value) .FirstOrDefault(); DataRow o = null; if (d != null && d.IsDefined(rng.Row)) { string nome = d.GetNameByAddress(rng.Row, rng.Column); string siglaEntita = nome.Split(Simboli.UNION[0])[0]; DataView categoriaEntita = Workbook.Repository[DataBase.TAB.CATEGORIA_ENTITA].DefaultView; categoriaEntita.RowFilter = "SiglaEntita = '" + siglaEntita + "' AND IdApplicazione = " + Workbook.IdApplicazione; if (categoriaEntita.Count > 0) { o = categoriaEntita[0].Row; } } return(o); }
/// <summary> /// Aggiunge gli adjust necessari all'entità da ottimizzare. /// </summary> /// <param name="siglaEntita">Entità da ottimizzare.</param> protected virtual void AddAdjust(object siglaEntita) { _entitaInformazioni.RowFilter = "SiglaEntita = '" + siglaEntita + "' AND WB <> '0' AND IdApplicazione = " + Workbook.IdApplicazione; foreach (DataRowView info in _entitaInformazioni) { object siglaEntitaInfo = info["SiglaEntitaRif"] is DBNull ? info["SiglaEntita"] : info["SiglaEntitaRif"]; Range rng = _definedNames.Get(siglaEntitaInfo, info["SiglaInformazione"], Date.SuffissoDATA1).Extend(colOffset: Date.GetOreIntervallo(_dataFine)); Workbook.Application.Run("wbAdjust", "'" + _sheet + "'!" + rng.ToString()); for (DateTime giorno = Workbook.DataAttiva; giorno <= _dataFine; giorno = giorno.AddDays(1)) { Range rng1 = new Range(rng.StartRow, _definedNames.GetColFromDate(Date.GetSuffissoData(giorno), Date.GetSuffissoOra(Date.GetOreGiorno(giorno)))); Workbook.Sheets[_sheet].Range[rng1.ToString()].Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium; } if (info["WB"].Equals("2")) { Workbook.Application.Run("WBFREE", DefinedNames.GetName(siglaEntitaInfo, info["SiglaInformazione"]), "'" + _sheet + "'!" + rng.ToString()); } else if (info["WB"].Equals("3")) { Workbook.Application.Run("WBBIN", DefinedNames.GetName(siglaEntitaInfo, info["SiglaInformazione"]), "'" + _sheet + "'!" + rng.ToString()); } } }
private void FormBilanciamento_Shown(object sender, EventArgs e) { if (dgvBilanciamento.DataSource != null) { for (int i = 0; i < dgvBilanciamento.Rows.Count; i++) { DataGridViewComboBoxCell cellCombo = new DataGridViewComboBoxCell(); string siglaEntita = dgvBilanciamento["SiglaEntita", i].Value.ToString(); string siglaInfo = _dtEntitaGradinoDisp.AsEnumerable() .Where(r => r["SiglaEntita"].Equals(siglaEntita)) .Select(r => r[1].ToString()) .FirstOrDefault(); DefinedNames df = _categoriaNomiDefiniti[dgvBilanciamento["SiglaCategoria", i].Value.ToString()]; Dictionary <string, int> gt = OfferteMIHelper.GetGOTODictionary(siglaEntita, siglaInfo, df); if (gt != null) { cellCombo.DataSource = new BindingSource(gt, null); cellCombo.ValueMember = "Value"; cellCombo.DisplayMember = "Key"; dgvBilanciamento[1, i] = cellCombo; } // seleziono il primo valore della combo //dgvBilanciamento[1, i].Selected = true; //dgvBilanciamento[1, i].Value = gt.First().Key; } } }
protected override bool EsportaAzioneInformazione(object siglaEntita, object siglaAzione, object desEntita, object desAzione, DateTime dataRif, string[] mercati) { DataView entitaAzione = Workbook.Repository[DataBase.TAB.ENTITA_AZIONE].DefaultView; entitaAzione.RowFilter = "SiglaEntita = '" + siglaEntita + "' AND SiglaAzione = '" + siglaAzione + "' AND IdApplicazione = " + Workbook.IdApplicazione; if (entitaAzione.Count == 0) { return(false); } switch (siglaAzione.ToString()) { case "MAIL": Workbook.ScreenUpdating = false; DefinedNames mainDefinedNames = new DefinedNames("Main"); //TODO verificare se è sempre aggiornato //unico caso che non aggiorna è se carico e faccio invia mail conseguentemente Aggiorna a = new Aggiorna(); a.AggiornaPrevisioneRiepilogo(); //salvo i dati Riepilogo r = new Riepilogo(); r.SalvaPrevisione(); if (InviaMail(mainDefinedNames, siglaEntita)) { } Workbook.ScreenUpdating = true; break; } return(true); }
/// <summary> /// http://stackoverflow.com/questions/8405025/set-print-area-openxml-with-excel /// https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.definedname(v=office.14).aspx /// </summary> /// <param name="spreadsheetDoc"></param> /// <param name="sheetName"></param> /// <param name="columnStart">Ex: "$A"</param> /// <param name="columnEnd">EX: "$B"</param> /// null if just not set print areas column or row public static void SetPrintArea(WorkbookPart workbookPart, string sheetName, string columnStart, string columnEnd, string rowStart, string rowEnd) { String definedName = sheetName; var definedNames = workbookPart.Workbook.Descendants <DefinedNames>().FirstOrDefault(); DefinedName name = null; UInt32Value locSheetId; if (definedNames == null) { definedNames = new DefinedNames(); workbookPart.Workbook.Append(definedNames); workbookPart.Workbook.Save(); locSheetId = UInt32Value.FromUInt32(0); } else { int defineNameCount = definedNames.Descendants <DefinedName>().Count(); locSheetId = UInt32Value.FromUInt32((UInt32)defineNameCount); } //_xlnm.Print_Area la tham so de set up cho khoang khong in if (rowStart != null && rowEnd != null && columnEnd != null && columnStart != null) { name = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = locSheetId , Text = String.Format("{0}!${1}${2}:${3}${4}", sheetName, columnStart.Replace("$", ""), rowStart.Replace("$", ""), columnEnd.Replace("$", ""), rowEnd.Replace("$", "")) }; } definedNames.Append(name); workbookPart.Workbook.Save(); }
public override CheckOutput ExecuteCheck(Excel.Worksheet ws, DefinedNames nomiDefiniti, CheckObj check) { _ws = ws; _nomiDefiniti = nomiDefiniti; _check = check; CheckOutput n = new CheckOutput(); switch (check.Type) { case 1: n = CheckFunc1(); break; case 2: n = CheckFunc2(); break; case 3: n = CheckFunc3(); break; case 4: n = CheckFunc4(); break; case 5: n = CheckFunc5(); break; } return(n); }
private void AggiornaCmbStagioni() { //seleziono la stagione nella combo string name = DefinedNames.GetSheetName("CT_TORINO"); if (name != "") { Excel.Worksheet ws = Workbook.Sheets[name]; DefinedNames definedNames = new DefinedNames(ws.Name); Range rng = definedNames.Get("CT_TORINO", "STAGIONE", Date.SuffissoDATA1, Date.GetSuffissoOra(1)); bool enabledEvents = Workbook.Application.EnableEvents; if (enabledEvents) { Workbook.Application.EnableEvents = false; } ((RibbonDropDown)Globals.Ribbons.GetRibbon <ToolsExcelRibbon>().Controls["cmbStagione"]).SelectedItemIndex = (int)(ws.Range[rng.ToString()].Value ?? 1) - 1; if (enabledEvents) { Workbook.Application.EnableEvents = true; } } }
/// <summary> /// Gestisce il caso in cui ci sia una selezione multipla che andrebbe a scrivere su righe nascoste: allerta l'utente e impedisce di procedere con la modifica. /// </summary> /// <param name="Sh">Sheet di provenienza.</param> /// <param name="Target">Range selezionato dall'utente.</param> public static void CellClick(object Sh, Excel.Range Target) { //controllo che la selezione non sia multi-linea con in mezzo delle righe nascoste - nel caso avverto l'utente che non può effettuare modifiche if (Target.Rows.Count > 1) { if (Simboli.ModificaDati) { foreach (Excel.Range r in Target.Rows) { if (r.EntireRow.Hidden) { System.Windows.Forms.MessageBox.Show("Nella selezione sono incluse righe nascoste. Non si può procedere con la modifica...", Simboli.NomeApplicazione + " - ATTENZIONE", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Stop); Target.Cells[1, 1].Select(); break; } } } } else { try { DefinedNames newDefinedNames = new DefinedNames(Target.Worksheet.Name, DefinedNames.InitType.GOTOs); string address = newDefinedNames.GetGotoFromAddress(Range.R1C1toA1(Target.Row, Target.Column)); Goto(address); } catch {} } }
public override CheckOutput ExecuteCheck(Excel.Worksheet ws, DefinedNames definedNames, CheckObj check) { _ws = ws; _nomiDefiniti = definedNames; _check = check; return(CheckFunc1()); }
private void btnImporta_Click(object sender, EventArgs e) { SplashScreen.Show(); Workbook.ScreenUpdating = false; Workbook.Application.Calculation = Excel.XlCalculation.xlCalculationManual; Sheet.Protected = false; foreach (var c in _commonInfo) { if (tvEntitaInformazioni.Nodes[c.SiglaEntita].Checked) { SplashScreen.UpdateStatus("Importo dati per " + c.DesEntita); string foglio = DefinedNames.GetSheetName(c.SiglaEntita); Excel.Worksheet ws = Workbook.Sheets[foglio]; DefinedNames definedNames = new DefinedNames(foglio); foreach (var kv in c.Info) { if (tvEntitaInformazioni.Nodes[c.SiglaEntita].Nodes[kv.Key.ToString()].Checked) { var values = from r in _tabellaImportXML.AsEnumerable() where r["SiglaEntita"].Equals(c.SiglaEntita) && r["SiglaInformazione"].Equals(kv.Key) && (r["Data"].ToString().Substring(0, 8).CompareTo(Workbook.DataAttiva.ToString("yyyyMMdd")) >= 0) select new { Data = r["Data"], Valore = r["Valore"] }; foreach (var val in values) { string suffissoData = Date.GetSuffissoData(val.Data.ToString()); string suffissoOra = Date.GetSuffissoOra(val.Data); Range rng = new Range(); if (definedNames.TryGet(out rng, c.SiglaEntita, kv.Key, suffissoData, suffissoOra)) { object tmpVal = null; double conv; if (Double.TryParse(val.Valore.ToString(), out conv)) { tmpVal = conv; } else { tmpVal = val.Valore; } ws.Range[rng.ToString()].Value = tmpVal; } } } } } } Sheet.Protected = true; Workbook.Application.Calculation = Excel.XlCalculation.xlCalculationAutomatic; SplashScreen.Close(); Workbook.ScreenUpdating = true; }
/// <summary> /// Funizione ereditata dall'interfaccia che viene richiamata nella parte base dell'algoritmo per eseguire l'ottimizzazione. /// </summary> /// <param name="siglaEntita">Entità da ottimizzare.</param> public virtual void EseguiOttimizzazione(object siglaEntita) { try { Workbook.Application.Run("wbSetGeneralOptions", Arg3: "120", Arg13: "1"); _sheet = DefinedNames.GetSheetName(siglaEntita); _definedNames = new DefinedNames(_sheet, DefinedNames.InitType.CheckNaming); string desEntita = (from r in Workbook.Repository[DataBase.TAB.CATEGORIA_ENTITA].AsEnumerable() where r["IdApplicazione"].Equals(Workbook.IdApplicazione) && r["SiglaEntita"].Equals(siglaEntita) select r["DesEntita"].ToString()).First(); _entitaProprieta.RowFilter = "SiglaEntita = '" + siglaEntita + "' AND SiglaProprieta LIKE '%GIORNI_STRUTTURA' AND IdApplicazione = " + Workbook.IdApplicazione; if (_entitaProprieta.Count > 0) { _dataFine = Workbook.DataAttiva.AddDays(int.Parse(_entitaProprieta[0]["Valore"].ToString())); } else { _dataFine = Workbook.DataAttiva.AddDays(Struct.intervalloGiorni); } CheckObj chkObj = _definedNames.Checks.Where(chk => chk.SiglaEntita.Equals(siglaEntita)).FirstOrDefault(); if (chkObj != null) { Excel.Range rng = Workbook.Sheets[_sheet].Range[chkObj.Range.ToString()]; foreach (Excel.Range cell in rng.Cells) { if (cell.Value.Equals("ERRORE")) { SplashScreen.Close(); System.Windows.Forms.MessageBox.Show("Non è possibile ottimizzare l'UP selezionata perché sono presenti degli errori. Controllare i check!", Simboli.NomeApplicazione + " - ATTENZIONE!!!", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning); return; } } } OmitConstraints(); AddAdjust(siglaEntita); AddConstraints(siglaEntita); AddOpt(siglaEntita); SplashScreen.Close(); Execute(siglaEntita); DeleteExistingAdjust(); Workbook.InsertLog(PSO.Core.DataBase.TipologiaLOG.LogGenera, "Eseguita ottimizzazione " + desEntita); } catch (Exception e) { SplashScreen.Close(); Workbook.Application.ScreenUpdating = true; System.Windows.Forms.MessageBox.Show("Si è verificato un errore nel processo di ottimizzazione. Il messaggio dice '" + e.Message + "'", Simboli.NomeApplicazione + " - ERRORE!!!", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } }
public override CheckOutput ExecuteCheck(Excel.Worksheet ws, DefinedNames definedNames, CheckObj check) { //Funzione che non centra nulla con i check ma che permette di effettuare il refresh del riepilogo ad ogni azioni che può modificarlo. Aggiorna aggiorna = new Aggiorna(); aggiorna.AggiornaPrevisioneRiepilogo(); return(new CheckOutput()); }
// 10/01/2017 metod to create a CSV file for ORCO (from excel sheet named "Iren Idro") protected bool CreaOutputCSV(object siglaEntita, string exportPath, string filename, DateTime dataRif) { try { string nomeFoglio = "Iren Idro"; DefinedNames definedNames = new DefinedNames(nomeFoglio); Excel.Worksheet ws = Workbook.Sheets[nomeFoglio]; int oreGiorno = Date.GetOreGiorno(dataRif); string[] lines = new string[oreGiorno]; DataView categoriaEntita = Workbook.Repository[DataBase.TAB.CATEGORIA_ENTITA].DefaultView; categoriaEntita.RowFilter = "SiglaEntita = '" + siglaEntita + "' AND IdApplicazione = " + Workbook.IdApplicazione; object codiceRUP = "UP_OCX"; object companyName = "IREN ENERGIA SPA"; object companyID = "OEIESRD"; DataView entitaAzioneInformazione = Workbook.Repository[DataBase.TAB.ENTITA_AZIONE_INFORMAZIONE].DefaultView; entitaAzioneInformazione.RowFilter = "SiglaEntita = '" + siglaEntita + "' AND SiglaEntitaRif = 'UP_OCX' AND IdApplicazione = " + Workbook.IdApplicazione; string referenceNumber = codiceRUP.ToString().Replace("_", "") + "_" + DateTime.Now.ToString("yyyyMMddHHmmss"); if (entitaAzioneInformazione.Count > 0) { DataRowView info = entitaAzioneInformazione[0]; string gradino = Regex.Match(info["SiglaInformazione"].ToString(), @"\d+").Value; object siglaEntitaRif = info["SiglaEntitaRif"] is DBNull ? siglaEntita : info["SiglaEntitaRif"]; Range rng = definedNames.Get(siglaEntitaRif, "PROGRAMMAQ" + gradino + "_" + Workbook.Mercato) .Extend(rowOffset: 4, colOffset: oreGiorno); for (int i = 0; i < oreGiorno; i++) { //Market string str = Workbook.Mercato + ";"; //UnitReferenceNumber str += codiceRUP + ";"; //Date str += dataRif.ToString("yyyyMMdd") + ";"; //Hour str += (i + 1); for (int quarter = 0; quarter < 4; quarter++) { //Quantity 1..4 str += ";" + GetDecimal(ws, rng.Columns[i].Rows[quarter]); } lines[i] = str; } File.WriteAllLines(Path.Combine(exportPath, filename), lines); } return(true); } catch { return(false); } }
/// <summary> /// Aggiunge i vincoli necessari all'entità da ottimizzare. /// </summary> /// <param name="siglaEntita">Entità da ottimizzare.</param> protected virtual void AddConstraints(object siglaEntita) { _entitaInformazioni.RowFilter = "SiglaEntita = '" + siglaEntita + "' AND SiglaTipologiaInformazione = 'VINCOLO' AND IdApplicazione = " + Workbook.IdApplicazione; foreach (DataRowView info in _entitaInformazioni) { object siglaEntitaInfo = info["SiglaEntitaRif"] is DBNull ? info["SiglaEntita"] : info["SiglaEntitaRif"]; Workbook.WB.Names.Item("WBOMIT" + DefinedNames.GetName(siglaEntitaInfo, info["SiglaInformazione"])).Delete(); } }
public static Range[] GetRangeGiornoGas(DateTime giorno, DataRowView info, DefinedNames definedNames) { int row = definedNames.GetRowByName(info["SiglaEntita"], info["SiglaInformazione"], Date.GetSuffissoData(giorno)); int gasDayStart = TimeZone.CurrentTimeZone.IsDaylightSavingTime(giorno) ? 7 : 6; int remainingHours = 24 - Date.GetOreGiorno(giorno) + gasDayStart; Range rng1 = new Range(row, definedNames.GetColData1H1() + gasDayStart, 1, Date.GetOreGiorno(giorno) - gasDayStart); Range rng2 = new Range(row + 1, definedNames.GetColData1H1(), 1, remainingHours); return(new Range[] { rng1, rng2 }); }
/// <summary> /// Sposta la selezione sul titolo dell'UP scelta e ritorna la sua sigla. /// </summary> /// <returns>Restituisce la sigla dell'UP scelta.</returns> public new object ShowDialog() { base.ShowDialog(); if (comboUP.SelectedIndex != -1) { //non mi serve il nome del foglio perché lavoro direttamente con la siglaEntita DefinedNames n = new DefinedNames("", DefinedNames.InitType.GOTOs); string address = n.GetGotoFromSiglaEntita(comboUP.SelectedValue); Handler.Goto(address); } return(comboUP.SelectedValue); }
private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet) { // Create the Excel file contents. This function is used when creating an Excel file either writing // to a file, or writing to a MemoryStream. spreadsheet.AddWorkbookPart(); spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); DefinedNames definedNamesCol = new DefinedNames(); // My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010) spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView())); // If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file ! WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart <WorkbookStylesPart>("rIdStyles"); workbookStylesPart.Stylesheet = GenerateStyleSheet(); workbookStylesPart.Stylesheet.Save(); // Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each. uint worksheetNumber = 1; Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); foreach (DataTable dt in ds.Tables) { // For each worksheet you want to create string worksheetName = dt.TableName; // Create worksheet part, and add it to the sheets collection in workbook WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>(); Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName }; // If you want to define the Column Widths for a Worksheet, you need to do this *before* appending the SheetData // http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/1d93eca8-2949-4d12-8dd9-15cc24128b10/ sheets.Append(sheet); // Append this worksheet's data to our Workbook, using OpenXmlWriter, to prevent memory problems WriteDataTableToExcelWorksheet(dt, newWorksheetPart, definedNamesCol); worksheetNumber++; } spreadsheet.WorkbookPart.Workbook.Append(definedNamesCol); spreadsheet.WorkbookPart.Workbook.Save(); }
public void InitStrutturaNomi() { this[DataBase.TAB.NOMI_DEFINITI] = DefinedNames.GetDefaultNameTable(DataBase.TAB.NOMI_DEFINITI); this[DataBase.TAB.DATE_DEFINITE] = DefinedNames.GetDefaultDateTable(DataBase.TAB.DATE_DEFINITE); this[DataBase.TAB.ADDRESS_FROM] = DefinedNames.GetDefaultAddressFromTable(DataBase.TAB.ADDRESS_FROM); this[DataBase.TAB.ADDRESS_TO] = DefinedNames.GetDefaultAddressToTable(DataBase.TAB.ADDRESS_TO); this[DataBase.TAB.EDITABILI] = DefinedNames.GetDefaultEditableTable(DataBase.TAB.EDITABILI); this[DataBase.TAB.SALVADB] = DefinedNames.GetDefaultSaveTable(DataBase.TAB.SALVADB); this[DataBase.TAB.ANNOTA] = DefinedNames.GetDefaultToNoteTable(DataBase.TAB.ANNOTA); this[DataBase.TAB.CHECK] = DefinedNames.GetDefaultCheckTable(DataBase.TAB.CHECK); this[DataBase.TAB.SELECTION] = DefinedNames.GetDefaultSelectionTable(DataBase.TAB.SELECTION); this[DataBase.TAB.MODIFICA] = CreaTabellaModifica(DataBase.TAB.MODIFICA); this[DataBase.TAB.EXPORT_XML] = CreaTabellaExportXML(DataBase.TAB.EXPORT_XML); }
public Riepilogo(Excel.Worksheet ws) { _ws = ws; _struttura = new Struct(); _struttura.rigaBlock = 5; _struttura.colBlock = 59; try { _definedNames = new DefinedNames(_ws.Name); } catch { } }
public FormIncremento(Excel.Worksheet ws, Excel.Range rng) { InitializeComponent(); this.Text = Simboli.NomeApplicazione + " - Incremento"; _ws = ws; _ws.SelectionChange += ChangeSelectionToIncrement; _definedNames = new DefinedNames(_ws.Name, DefinedNames.InitType.All); Workbook.Repository.Add(Workbook.Repository.CreaTabellaModifica(MODIFICA)); btnRipristina.Enabled = false; btnApplica.Enabled = false; ChangeSelectionToIncrement(Workbook.Application.Selection); }
public override CheckOutput ExecuteCheck(Excel.Worksheet ws, DefinedNames definedNames, CheckObj check) { _ws = ws; _nomiDefiniti = definedNames; _check = check; CheckOutput n = new CheckOutput(); switch (check.Type) { case 1: n = CheckFunc1(); break; } return(n); }
public SheetExport(Excel.Worksheet ws) { _ws = ws; _mercato = ws.Name; _appID = Workbook.Repository[DataBase.TAB.MERCATI].AsEnumerable() .Where(r => r["DesMercato"].Equals(_mercato)) .Select(r => (int)r["IdApplicazioneMercato"]) .FirstOrDefault(); AggiornaParametriSheet(); _definedNames = new DefinedNames(_mercato); if (_mercato != "MSD1") { _definedNamesMercatoPrec = new DefinedNames(Simboli.GetMercatoPrec(_mercato)); } }
public override bool AzioneInformazione(object siglaEntita, object siglaAzione, object azionePadre, DateTime giorno, string[] mercati, object parametro = null) { bool isGenera = azionePadre.Equals("GENERA"); if (isGenera) { azionePadre = "CARICA"; } bool o = base.AzioneInformazione(siglaEntita, siglaAzione, azionePadre, giorno, mercati, parametro); if (isGenera) { azionePadre = "GENERA"; } //non ho fatto nulla, la connessione non si apre e l'azione padre è CARICA... rientro nel caso del caricamento da XML if (o == false && !DataBase.OpenConnection() && azionePadre.Equals("CARICA")) { //tipo file da caricare string tf = siglaAzione.ToString(); DataTable azioneInformazione = CaricaXML("pathExportFile" + tf, "formatoNomeFile" + tf, siglaEntita, tf) ?? new DataTable(); if (azioneInformazione.Rows.Count == 0) { DataBase.InsertApplicazioneRiepilogo(siglaEntita, siglaAzione, giorno, false); return(false); } else { string sheet = DefinedNames.GetSheetName(siglaEntita); DefinedNames definedNames = new DefinedNames(sheet); ScriviInformazione(siglaEntita, azioneInformazione.DefaultView, definedNames); DataBase.InsertApplicazioneRiepilogo(siglaEntita, siglaAzione, giorno); } } DataBase.CloseConnection(); string name = DefinedNames.GetSheetName(siglaEntita); Sheet s = new Sheet(Workbook.Sheets[name]); s.AggiornaColori(); return(o); }
private string GetInfoFromRange(Excel.Range rng) { DefinedNames d = _categoriaNomiDefiniti .Where(kv => kv.Value.Sheet == rng.Worksheet.Name) .Select(kv => kv.Value) .FirstOrDefault(); string o = null; if (d != null && d.IsDefined(rng.Row)) { string nome = d.GetNameByAddress(rng.Row, rng.Column); //string siglaEntita = nome.Split(Simboli.UNION[0])[0]; o = nome.Split(Simboli.UNION[0])[1]; } return(o); }
/// <summary> /// Blocca le aree su cui non considerare i vincoli. /// </summary> protected virtual void OmitConstraints() { _entitaInformazioni.RowFilter = "SiglaTipologiaInformazione = 'VINCOLO' AND IdApplicazione = " + Workbook.IdApplicazione; string siglaEntita = ""; string nomeFoglio = ""; DateTime dataFine = new DateTime(); DefinedNames definedNames = null; foreach (DataRowView info in _entitaInformazioni) { Helper(info, ref siglaEntita, ref nomeFoglio, ref dataFine, ref definedNames); object siglaEntitaInfo = info["SiglaEntitaRif"] is DBNull ? info["SiglaEntita"] : info["SiglaEntitaRif"]; Range rng = definedNames.Get(siglaEntitaInfo, info["SiglaInformazione"], Date.SuffissoDATA1).Extend(colOffset: Date.GetOreIntervallo(dataFine)); Workbook.Application.Run("WBOMIT", DefinedNames.GetName(siglaEntitaInfo, info["SiglaInformazione"]), "'" + nomeFoglio + "'!" + rng.ToString()); } }
public static Dictionary <String, String> GetAllNamedRanges(SpreadsheetDocument spreadsheet) { var RangedNames = new Dictionary <String, String>(); var wbPart = spreadsheet.WorkbookPart; // Retrieve a reference to the defined names collection. DefinedNames definedNames = wbPart.Workbook.DefinedNames; // If there are defined names, add them to the dictionary. if (definedNames != null) { foreach (DefinedName dn in definedNames) { RangedNames.Add(dn.Name.Value, dn.Text); } } return(RangedNames); }
private void GenerateWorkbookPartContent(WorkbookPart workbookPart, SaveContext context) { if (workbookPart.Workbook == null) workbookPart.Workbook = new Workbook(); var workbook = workbookPart.Workbook; if ( !workbook.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) { workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); } #region WorkbookProperties if (workbook.WorkbookProperties == null) workbook.WorkbookProperties = new WorkbookProperties(); if (workbook.WorkbookProperties.CodeName == null) workbook.WorkbookProperties.CodeName = "ThisWorkbook"; if (Use1904DateSystem) workbook.WorkbookProperties.Date1904 = true; #endregion if (workbook.BookViews == null) workbook.BookViews = new BookViews(); if (workbook.Sheets == null) workbook.Sheets = new Sheets(); var worksheets = WorksheetsInternal; workbook.Sheets.Elements<Sheet>().Where(s => worksheets.Deleted.Contains(s.Id)).ToList().ForEach( s => s.Remove()); foreach (var sheet in workbook.Sheets.Elements<Sheet>()) { var sheetId = (Int32)sheet.SheetId.Value; if (WorksheetsInternal.All<XLWorksheet>(w => w.SheetId != sheetId)) continue; var wks = WorksheetsInternal.Single<XLWorksheet>(w => w.SheetId == sheetId); wks.RelId = sheet.Id; sheet.Name = wks.Name; } foreach (var xlSheet in WorksheetsInternal.Cast<XLWorksheet>().Where(s => s.SheetId == 0).OrderBy(w => w.Position)) { var rId = context.RelIdGenerator.GetNext(RelType.Workbook); while (WorksheetsInternal.Cast<XLWorksheet>().Any(w => w.SheetId == Int32.Parse(rId.Substring(3)))) rId = context.RelIdGenerator.GetNext(RelType.Workbook); xlSheet.SheetId = Int32.Parse(rId.Substring(3)); xlSheet.RelId = rId; var newSheet = new Sheet { Name = xlSheet.Name, Id = rId, SheetId = (UInt32)xlSheet.SheetId }; workbook.Sheets.AppendChild(newSheet); } var sheetElements = from sheet in workbook.Sheets.Elements<Sheet>() join worksheet in ((IEnumerable<XLWorksheet>)WorksheetsInternal) on sheet.Id.Value equals worksheet.RelId orderby worksheet.Position select sheet; UInt32 firstSheetVisible = 0; var activeTab = (from us in UnsupportedSheets where us.IsActive select (UInt32)us.Position - 1).FirstOrDefault(); var foundVisible = false; var totalSheets = sheetElements.Count() + UnsupportedSheets.Count; for (var p = 1; p <= totalSheets; p++) { if (UnsupportedSheets.All(us => us.Position != p)) { var sheet = sheetElements.ElementAt(p - UnsupportedSheets.Count(us => us.Position <= p) - 1); workbook.Sheets.RemoveChild(sheet); workbook.Sheets.AppendChild(sheet); var xlSheet = Worksheet(sheet.Name); if (xlSheet.Visibility != XLWorksheetVisibility.Visible) sheet.State = xlSheet.Visibility.ToOpenXml(); if (foundVisible) continue; if (sheet.State == null || sheet.State == SheetStateValues.Visible) foundVisible = true; else firstSheetVisible++; } else { var sheetId = UnsupportedSheets.First(us => us.Position == p).SheetId; var sheet = workbook.Sheets.Elements<Sheet>().First(s => s.SheetId == sheetId); workbook.Sheets.RemoveChild(sheet); workbook.Sheets.AppendChild(sheet); } } var workbookView = workbook.BookViews.Elements<WorkbookView>().FirstOrDefault(); if (activeTab == 0) { activeTab = firstSheetVisible; foreach (var ws in worksheets) { if (!ws.TabActive) continue; activeTab = (UInt32)(ws.Position - 1); break; } } if (workbookView == null) { workbookView = new WorkbookView {ActiveTab = activeTab, FirstSheet = firstSheetVisible}; workbook.BookViews.AppendChild(workbookView); } else { workbookView.ActiveTab = activeTab; workbookView.FirstSheet = firstSheetVisible; } var definedNames = new DefinedNames(); foreach (var worksheet in WorksheetsInternal) { var wsSheetId = (UInt32)worksheet.SheetId; UInt32 sheetId = 0; foreach (var s in workbook.Sheets.Elements<Sheet>().TakeWhile(s => s.SheetId != wsSheetId)) { sheetId++; } if (worksheet.PageSetup.PrintAreas.Any()) { var definedName = new DefinedName {Name = "_xlnm.Print_Area", LocalSheetId = sheetId}; var worksheetName = worksheet.Name; var definedNameText = worksheet.PageSetup.PrintAreas.Aggregate(String.Empty, (current, printArea) => current + ("'" + worksheetName + "'!" + printArea.RangeAddress. FirstAddress.ToStringFixed( XLReferenceStyle.A1) + ":" + printArea.RangeAddress. LastAddress.ToStringFixed( XLReferenceStyle.A1) + ",")); definedName.Text = definedNameText.Substring(0, definedNameText.Length - 1); definedNames.AppendChild(definedName); } if (worksheet.AutoFilter.Enabled) { var definedName = new DefinedName { Name = "_xlnm._FilterDatabase", LocalSheetId = sheetId, Text = "'" + worksheet.Name + "'!" + worksheet.AutoFilter.Range.RangeAddress.FirstAddress.ToStringFixed( XLReferenceStyle.A1) + ":" + worksheet.AutoFilter.Range.RangeAddress.LastAddress.ToStringFixed( XLReferenceStyle.A1), Hidden = BooleanValue.FromBoolean(true) }; definedNames.AppendChild(definedName); } foreach (var nr in worksheet.NamedRanges.Where(n => n.Name != "_xlnm._FilterDatabase")) { var definedName = new DefinedName { Name = nr.Name, LocalSheetId = sheetId, Text = nr.ToString() }; if (!XLHelper.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; definedNames.AppendChild(definedName); } var definedNameTextRow = String.Empty; var definedNameTextColumn = String.Empty; if (worksheet.PageSetup.FirstRowToRepeatAtTop > 0) { definedNameTextRow = "'" + worksheet.Name + "'!" + worksheet.PageSetup.FirstRowToRepeatAtTop + ":" + worksheet.PageSetup.LastRowToRepeatAtTop; } if (worksheet.PageSetup.FirstColumnToRepeatAtLeft > 0) { var minColumn = worksheet.PageSetup.FirstColumnToRepeatAtLeft; var maxColumn = worksheet.PageSetup.LastColumnToRepeatAtLeft; definedNameTextColumn = "'" + worksheet.Name + "'!" + XLHelper.GetColumnLetterFromNumber(minColumn) + ":" + XLHelper.GetColumnLetterFromNumber(maxColumn); } string titles; if (definedNameTextColumn.Length > 0) { titles = definedNameTextColumn; if (definedNameTextRow.Length > 0) titles += "," + definedNameTextRow; } else titles = definedNameTextRow; if (titles.Length <= 0) continue; var definedName2 = new DefinedName { Name = "_xlnm.Print_Titles", LocalSheetId = sheetId, Text = titles }; definedNames.AppendChild(definedName2); } foreach (var nr in NamedRanges) { var definedName = new DefinedName { Name = nr.Name, Text = nr.ToString() }; if (!XLHelper.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; definedNames.AppendChild(definedName); } workbook.DefinedNames = definedNames; if (workbook.CalculationProperties == null) workbook.CalculationProperties = new CalculationProperties {CalculationId = 125725U}; if (CalculateMode == XLCalculateMode.Default) workbook.CalculationProperties.CalculationMode = null; else workbook.CalculationProperties.CalculationMode = CalculateMode.ToOpenXml(); if (ReferenceStyle == XLReferenceStyle.Default) workbook.CalculationProperties.ReferenceMode = null; else workbook.CalculationProperties.ReferenceMode = ReferenceStyle.ToOpenXml(); }