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