public void Reset()
{
Cell.Interior.Color = OriginalColor;
Cell.Interior.Pattern = OriginalPattern;
if (Cell.Comment != null)
{
Cell.Comment.Delete();
}
if (OriginalComment != null)
{
Cell.AddComment(OriginalComment.ToString());
}
}
/// <summary>
/// Launcher per la compilazione del riepilogo in seguito allo svolgimento di un'azione.
/// </summary>
/// <param name="siglaEntita">Entità per individuare la riga in cui scrivere.</param>
/// <param name="siglaAzione">Azione per individuare la colonna in cui scrivere.</param>
/// <param name="presente">Se l'azione ha portato a risultati oppure no.</param>
/// <param name="dataRif">La data in cui andare a scrivere. Assieme all'azione indica la colonna.</param>
///
public override void AggiornaRiepilogo(object siglaEntita, object siglaAzione, bool presente, DateTime dataRif)
{
if (dataRif - Workbook.DataAttiva <= new TimeSpan(Struct.intervalloGiorni, 0, 0, 0))
{
if (Struct.visualizzaRiepilogo && !Simboli.EmergenzaForzata)
{
Range cell = _definedNames.Get(siglaEntita, siglaAzione, Date.GetSuffissoData(dataRif));
Excel.Range rng = _ws.Range[cell.ToString()];
if (presente)
{
string commento = "Utente: " + Workbook.NomeUtente + "\nData: " + DateTime.Now.ToString("dd MMM yyyy") + "\nOra: " + DateTime.Now.ToString("HH:mm");
rng.ClearComments();
rng.AddComment(commento).Visible = false;
rng.Value = "OK";
Style.RangeStyle(rng, foreColor: 1, bold: true, fontSize: 9, backColor: 4, align: Excel.XlHAlign.xlHAlignCenter);
}
else
{
rng.ClearComments();
rng.Value = "Non presente";
Style.RangeStyle(rng, foreColor: 3, bold: false, fontSize: 7, backColor: 2, align: Excel.XlHAlign.xlHAlignCenter);
}
}
}
}
private void AddComment()
{
//<Snippet20>
Excel.Range dateComment = this.Application.get_Range("A1");
dateComment.AddComment("Comment added " + DateTime.Now.ToString());
//</Snippet20>
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
//Instantiate the Application object.
Excel.Application excelApp = Application;
//Specify the template excel file path.
string myPath = "Book1.xls";
//Open the excel file.
excelApp.Workbooks.Open(myPath, Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//Get the A1 cell.
Excel.Range rng1 = excelApp.get_Range("A1", Missing.Value);
//Add the comment with text.
rng1.AddComment("This is my comment");
//Save the file.
excelApp.ActiveWorkbook.Save();
//Remove the comment.
rng1.Comment.Delete();
//Quit the Application.
excelApp.Quit();
}
public static Excel.Comment AddComment(Excel.Range range, string message)
{
if (null != range.Comment)
{
range.Comment.Delete();
}
return(range.AddComment("SeleniumExcelAddIn:\n" + message));
}
/// <summary>
/// 向单元格写入数据，对当前WorkSheet操作
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="text">要写入的文本值</param>
public void SetCellComment(int rowIndex, int columnIndex, string comment)
{
try
{
Excel.Range range = workSheet.Cells[rowIndex, columnIndex] as Excel.Range;
range.AddComment(comment);
}
catch
{
this.KillExcelProcess(false);
throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！");
}
}
public static void LogMessagetoExcelFile(IEnumerable <BudgetReport> report)
{
var excelApp = new Excel.Application();
// Make the object visible.
excelApp.Visible = true;
object misValue = System.Reflection.Missing.Value;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a praticular template.
// Because no argument is sent in this example, Add creates a new workbook.
Excel.Workbook workBook = excelApp.Workbooks.Add(misValue);
// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "Category";
workSheet.Cells[1, "B"] = "Total Amount";
var row = 1;
foreach (var budget in report)
{
row++;
workSheet.Cells[row, "A"] = budget.Category;
workSheet.Cells[row, "B"] = budget.TotalAmount;
if (!String.IsNullOrEmpty(budget.Notes))
{
Excel.Range notesCell = excelApp.Application.get_Range("B" + row);
Excel.Comment comment = notesCell.AddComment();
comment.Shape.TextFrame.AutoSize = true;
comment.Text(budget.Notes);
}
}
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
//workBook.SaveAs(GetTempPath() + fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//workBook.Close(true, misValue, misValue);
//excelApp.Quit();
//Marshal.ReleaseComObject(workSheet);
//Marshal.ReleaseComObject(workBook);
//Marshal.ReleaseComObject(excelApp);
}
/// <summary>
/// Sets the cell comment.
/// </summary>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="columnIndex">Index of the column.</param>
/// <returns></returns>
public void SetCellComment(int rowIndex, int columnIndex, string comment)
{
//mWorksheet.get_Range(
App.Range range = mWorksheet.UsedRange;
App.Range row = (App.Range)range.Rows[rowIndex];
App.Range cell = (App.Range)row.Cells[columnIndex];
if (cell.Comment == null)
{
cell.AddComment();
cell.Comment.Text(comment);
}
else
{
cell.Comment.Text(comment);
}
}
/// <summary>
/// 图片插入到指定单元格批注中
/// </summary>
/// <param name="rngPic">单元格</param>
/// <param name="picPath">图片地址</param>
/// <param name="picName">图片名称</param>
public void InsertPicToComment(Excel.Range rngPic, string picPath, string picName)
{
//rngPic.ColumnWidth = rngColWidth;
//rngPic.RowHeight = rngRowHeight;
rngPic.AddComment(); //创建批注
rngPic.Comment.Shape.TextFrame.Characters().Text = picName; //在批注中记录文件名
rngPic.Comment.Shape.TextFrame.Characters().Font.Size = 10; //文件名字体大小
rngPic.Comment.Shape.TextFrame.AutoSize = true; //调整批注大小
rngPic.Comment.Shape.Fill.UserPicture(picPath); //填充图片
//rngPic.Comment.Shape.Height = (float)rngPic.Height;//批注高度
//rngPic.Comment.Shape.Width = (float)rngPic.Width;//批注宽度
rngPic.Comment.Shape.Height = this.GetPicHeight; //批注高度
rngPic.Comment.Shape.Width = this.GetPicWidth; //批注宽度
}
/// <summary>
/// 差異行を出力する
/// </summary>
/// <param name="sheet"></param>
/// <param name="data"></param>
/// <param name="startCol"></param>
/// <param name="startRow"></param>
private void WriteDiffRow(Excel.Worksheet sheet, DataRow data, int startCol, int startRow)
{
this.xlsReport.WriteValues(sheet, data, startRow, startCol);
for (int i = 0; i < data.Table.Columns.Count; i++)
{
var original = data[i, DataRowVersion.Original];
var current = data[i, DataRowVersion.Current];
if (!original.Equals(current))
{
//赤表示
Excel.Range cell = sheet.Cells[startRow, startCol + i];
cell.Font.Color = Excel.XlRgbColor.rgbRed;
string orgVal = original is DBNull ? "<null>" : original.ToString();
cell.AddComment("期待結果:" + orgVal);
}
}
}
protected override void Execute(CodeActivityContext context)
{
Excel.Sheets sheets = null;
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Worksheet workSheet = null;
try
{
var filePath = FilePath.Get(context);
var sheetName = SheetName.Get(context);
var cell = Cell.Get(context);
var comment = Comment.Get(context);
excelApp = new Excel.Application();
excelApp.Visible = false;
workbook = excelApp.Workbooks.Open(filePath);
sheets = workbook.Sheets;
for (int i = 1; i <= sheets.Count; i++)
{
dynamic val = sheets[i];
Excel.Worksheet worksheet = val as Excel.Worksheet;
if (worksheet != null && worksheet.Name.ToLowerInvariant().Equals(sheetName?.ToLowerInvariant()))
{
workSheet = worksheet;
workSheet.Activate();
break;
}
Marshal.ReleaseComObject(val);
}
Excel.Range oCell = workSheet.Range[cell, cell] as Excel.Range;
if (oCell.Comment != null)
{
oCell.Comment.Delete();
}
oCell.AddComment(comment);
workbook.Save();
excelApp.Quit();
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workSheet);
Marshal.ReleaseComObject(excelApp);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
protected void DoComment(CommentEnums CommnetOption, string strComment = "", bool blnShow = false)
{
try
{
base.InitWorkSheet();
ExcelObj.Range xlRange = base.GetSheetRange(strCell);
if (xlRange != null)
{
if (CommnetOption == CommentEnums.AddComment)
{
xlRange.AddComment(strComment);
}
else if (CommnetOption == CommentEnums.DeleteComment)
{
xlRange.Comment.Delete();
}
else if (CommnetOption == CommentEnums.ShowHide)
{
xlRange.Comment.Visible = blnShow;
}
else if (CommnetOption == CommentEnums.GetComments)
{
this.CommentValue = xlRange.Comment.Text();
}
base.SaveWorkBook(true);
}
else
{
base.ClearObject();
throw new Exception("Invalid Range");
}
}
catch (Exception ex)
{
base.ClearObject();
throw ex;
}
}
/// <summary>
/// Carico i dati e i commenti che devono essere scritti nelle celle.
/// </summary>
protected void CaricaDatiRiepilogo()
{
try
{
CicloGiorni((oreGiorno, suffissoData, giorno) =>
{
if (DataBase.OpenConnection())
{
DataView datiRiepilogo = GetDataView_CaricaDatiRiepilogo(giorno);
foreach (DataRowView valore in datiRiepilogo)
{
Range cellaAzione = new Range(_definedNames.GetRowByName(valore["SiglaEntita"]), _definedNames.GetColFromName(valore["SiglaAzione"], suffissoData));
Excel.Range rng = _ws.Range[cellaAzione.ToString()];
if (valore["Presente"].Equals("1"))
{
rng.ClearComments();
DateTime data = DateTime.ParseExact(valore["Data"].ToString(), "yyyyMMddHHmm", CultureInfo.InvariantCulture);
rng.AddComment("Utente: " + valore["Utente"] + "\nData: " + data.ToString("dd MMM yyyy") + "\nOra: " + data.ToString("HH:mm"));
rng.Value = "OK";
Style.RangeStyle(rng, foreColor: 1, bold: true, fontSize: 9, backColor: 4, align: Excel.XlHAlign.xlHAlignCenter);
}
else
{
rng.ClearComments();
rng.Value = "Non presente";
Style.RangeStyle(rng, foreColor: 3, bold: false, fontSize: 7, backColor: 2, align: Excel.XlHAlign.xlHAlignCenter);
}
}
}
});
}
catch (Exception e)
{
Workbook.InsertLog(PSO.Core.DataBase.TipologiaLOG.LogErrore, "CaricaDatiRiepilogo: " + e.Message);
System.Windows.Forms.MessageBox.Show(e.Message, Simboli.NomeApplicazione + " - ERRORE!!", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
}
public bool Resolve(object sender, IBindingContextElement element)
{
ExcelInterop.Range concernedRange = sender as ExcelInterop.Range;
if (concernedRange == null)
{
return(false);
}
try
{
ExcelInterop.Range concernedRangeFirstCell = concernedRange.Cells[1, 1];
// We delete the previous concernedRange comment
ExcelInterop.Comment comment = concernedRangeFirstCell.Comment;
comment?.Delete();
// Invoke decorator resolver
object result = EventCallbacksManager.DecoratorInvoke(Callback, concernedRange, element.DataSource, null);
if (result != null)
{
string commentStr = result as string;
if (!string.IsNullOrEmpty(commentStr))
{
concernedRange.AddComment(commentStr);
ExcelInterop.Comment addedComment = concernedRange.Comment;
ExcelInterop.Shape shape = addedComment.Shape;
ExcelInterop.TextFrame textFrame = shape.TextFrame;
textFrame.AutoSize = true;
}
return(commentStr != null);
}
return(false);
}
catch (Exception ex)
{
log.LogExceptionFormat(LogType.Error, ex, $"Cannot resolve decorator2 '{Ident}':{ex.Message}");
return(false);
}
}
/// <summary>
/// Funzione che scrive le informazioni nella cella indicata dai parametri in input.
/// </summary>
/// <param name="ws">Foglio a cui appartengono le celle da scrivere.</param>
/// <param name="definedNames">Oggetto che contiene l'indirizzamento delle celle per il foglio su cui si sta lavorando.</param>
/// <param name="siglaEntita">Sigla dell'entità su cui scrivere i dati.</param>
/// <param name="info">DataRow contenente le informazioni da scrivere nella cella</param>
/// <param name="suffissoData">Suffisso della data di riferimento necessario per l'indirizzamento.</param>
/// <param name="suffissoOra">Suffisso dell'ora di riferimento necessario per l'indirizzamento.</param>
/// <param name="risultato">Risultato del calcolo da scrivere nella cella.</param>
/// <param name="saveToDB">Flag che indica se l'informazione deve essere salvata o no sul DB in modo da attivare la routine di salvataggio della modifica.</param>
protected virtual void ScriviCella(Excel.Worksheet ws, DefinedNames definedNames, object siglaEntita, DataRowView info, string suffissoData, string suffissoOra, object risultato, bool saveToDB, bool fromCarica)
{
object siglaEntitaRif = siglaEntita;
if (info.DataView.Table.Columns.Contains("SiglaEntitaRif") && info["SiglaEntitaRif"] != DBNull.Value)
{
siglaEntitaRif = info["SiglaEntitaRif"];
}
Range rng = definedNames.Get(siglaEntitaRif, info["SiglaInformazione"], suffissoData, suffissoOra);
Excel.Range xlRng = ws.Range[rng.ToString()];
xlRng.Value = risultato;
if (info["BackColor"] != DBNull.Value)
{
xlRng.Interior.ColorIndex = info["BackColor"];
}
if (info["ForeColor"] != DBNull.Value)
{
xlRng.Font.ColorIndex = info["ForeColor"];
}
xlRng.ClearComments();
if (info["Commento"] != DBNull.Value)
{
xlRng.AddComment(info["Commento"]).Visible = false;
}
if (saveToDB && !fromCarica)
{
Handler.StoreEdit(xlRng, 0, true);
}
}
static void Main(string[] args)
{
Console.WriteLine("Interop Assemblies Performance Test - 15000 Cells.");
Console.WriteLine("Write simple text, change Font, NumberFormat, WrapText and add a comment.");
// start excel, and get a new sheet reference
Excel.Application excelApplication = CreateExcelApplication();
Excel.Workbooks books = excelApplication.Workbooks;
Excel.Workbook book = books.Add(Missing.Value);
Excel.Sheets sheets = book.Worksheets;
Excel.Worksheet sheet = sheets.Add() as Excel.Worksheet;
// do test 10 times
List <MarshalByRefObject> comReferencesList = new List <MarshalByRefObject>();
List <TimeSpan> timeElapsedList = new List <TimeSpan>();
for (int i = 1; i <= 10; i++)
{
sheet.UsedRange.ClearComments();
DateTime timeStart = DateTime.Now;
for (int y = 1; y <= 15000; y++)
{
string rangeAdress = "$A" + y.ToString();
Excel.Range cellRange = sheet.get_Range(rangeAdress);
cellRange.Value = "value";
Excel.Font font = cellRange.Font;
font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.WrapText = true;
Excel.Comment sampleComment = cellRange.AddComment("Sample Comment");
comReferencesList.Add(font as MarshalByRefObject);
comReferencesList.Add(sampleComment as MarshalByRefObject);
comReferencesList.Add(cellRange as MarshalByRefObject);
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
foreach (var item in comReferencesList)
{
Marshal.ReleaseComObject(item);
}
comReferencesList.Clear();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test3-Interop.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(books);
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
}
private void btnReport_Click(object sender, EventArgs e)
{
if (String.Compare(btnExport.Text, "Zatvori") == 0)
{
Application.Exit();
}
else
{
loadInfo.Text = "Učitavam podatke";
Cursor = Cursors.WaitCursor;
List <Siebel_export> dataSiebelExport = new List <Siebel_export>();
dataSiebelExport.Clear();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(excelPath.ToString());
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
Excel.Range a1 = xlWorksheet.Cells[1, 4];
Excel.Range a2 = xlWorksheet.Cells[rowCount, 4];
xlWorksheet.get_Range(a1, a2).NumberFormat = "#";
object[,] _2dArray = new object[rowCount - 1, colCount - 1];
_2dArray = xlRange.Value2;
Siebel_export row_siebel_export = new Siebel_export();
List <Proizvodi> unique_proizvodi = new List <Proizvodi>();
List <string> unique_sims = new List <string>();
List <string> headers = new List <string>();
for (int k = 1; k <= colCount; k++)
{
headers.Add(_2dArray[1, k].ToString());
}
int kor_naplata = headers.IndexOf("Korisnik za naplatu") + 1;
int kor_usluga = headers.IndexOf("Korisnik za uslugu") + 1;
int broj_telefona = headers.IndexOf("Broj telefona") + 1;
int dat_akt = headers.IndexOf("Datum aktivacije") + 1;
int dat_deakt = headers.IndexOf("Datum deaktivacije") + 1;
int status = headers.IndexOf("Status") + 1;
int proizv = headers.IndexOf("Proizvod") + 1;
int prof_napl = headers.IndexOf("Profil naplate") + 1;
int sb_kor = headers.IndexOf("SB korisnik") + 1;
int prof_napl_sb = headers.IndexOf("Profil naplate SB korisnika") + 1;
int ser_sim = headers.IndexOf("Serijski broj SIM-a") + 1;
int dat_poc_uo = headers.IndexOf("Datum početka ugovorne obveze") + 1;
int dat_kraj_uo = headers.IndexOf("Datum isteka ugovorne obveze") + 1;
int pnp = headers.IndexOf("Skraćeni broj (PNP)") + 1;
int odl_prof = headers.IndexOf("VPN odlazni profil") + 1;
int dol_prof = headers.IndexOf("VPN dolazni profil") + 1;
int vpn_budget = headers.IndexOf("Iznos limita - VPN Budget") + 1;
int limit = headers.IndexOf("Iznos limita potrošnje") + 1;
int korp_apn = headers.IndexOf("Korporativni APN") + 1;
int multisim = headers.IndexOf("MultiSIM nominacija") + 1;
int vrsta_usluge = headers.IndexOf("Vrsta usluge") + 1;
int vrsta_proiz = headers.IndexOf("Vrsta proizvoda") + 1;
int klas_proiz = headers.IndexOf("Klasifikacija proizvoda") + 1;
int stat_uo = headers.IndexOf("Status ugovorne obveze") + 1;
int br_dana_uo = headers.IndexOf("Preostalo dana ugovorne obveze") + 1;
int multisimcount = 0;
int korporativniAPN = 0;
int limitPotrosnje = 0;
string temp_broj = _2dArray[2, broj_telefona].ToString();
for (var i = 2; i <= rowCount; i++)
{
if (String.Compare(_2dArray[i, status].ToString(), "Active") == 0 || String.Compare(_2dArray[i, status].ToString(), "Suspended") == 0)
{
if (String.Compare(temp_broj, _2dArray[i, broj_telefona].ToString()) == 0)
{
Proizvodi row_proizvod = new Proizvodi();
Proizvodi unique_proizvod = new Proizvodi();
if (_2dArray[i, kor_naplata] != null && String.Compare(_2dArray[i, kor_naplata].ToString(), "") != 0 && String.Compare(_2dArray[i, kor_naplata].ToString(), "--") != 0)
{
row_siebel_export.KorisnikZaNaplatu = _2dArray[i, kor_naplata].ToString();
}
if (_2dArray[i, kor_usluga] != null && String.Compare(_2dArray[i, kor_usluga].ToString(), "") != 0 && String.Compare(_2dArray[i, kor_usluga].ToString(), "--") != 0)
{
row_siebel_export.KorisnikZaUslugu = _2dArray[i, kor_usluga].ToString();
}
if (_2dArray[i, broj_telefona] != null && String.Compare(_2dArray[i, broj_telefona].ToString(), "") != 0 && String.Compare(_2dArray[i, broj_telefona].ToString(), "--") != 0)
{
row_siebel_export.BrojTelefona = _2dArray[i, broj_telefona].ToString();
}
if (_2dArray[i, status] != null && String.Compare(_2dArray[i, status].ToString(), "") != 0 && String.Compare(_2dArray[i, status].ToString(), "--") != 0)
{
row_siebel_export.Status = _2dArray[i, status].ToString();
}
if (_2dArray[i, prof_napl] != null && String.Compare(_2dArray[i, prof_napl].ToString(), "") != 0 && String.Compare(_2dArray[i, prof_napl].ToString(), "--") != 0)
{
row_siebel_export.ProfilNaplate = _2dArray[i, prof_napl].ToString();
}
if (_2dArray[i, dat_poc_uo] != null && String.Compare(_2dArray[i, dat_poc_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_poc_uo].ToString(), "--") != 0)
{
try
{
if (Convert.ToDateTime(row_siebel_export.PocetakUO) < Convert.ToDateTime(DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_poc_uo].ToString())).ToString("dd.MM.yyyy")))
{
row_siebel_export.PocetakUO = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_poc_uo].ToString())).ToString("dd.MM.yyyy");
}
}
catch
{
if (Convert.ToDateTime(row_siebel_export.PocetakUO) < Convert.ToDateTime(_2dArray[i, dat_poc_uo].ToString().Substring(2, _2dArray[i, dat_poc_uo].ToString().Length - 2)))
{
row_siebel_export.PocetakUO = DateTime.ParseExact(_2dArray[i, dat_poc_uo].ToString().Substring(2, _2dArray[i, dat_poc_uo].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
}
if (_2dArray[i, dat_kraj_uo] != null && String.Compare(_2dArray[i, dat_kraj_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_kraj_uo].ToString(), "--") != 0)
{
try
{
if (Convert.ToDateTime(row_siebel_export.IstekUO) < Convert.ToDateTime(DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_kraj_uo].ToString())).ToString("dd.MM.yyyy")))
{
row_siebel_export.IstekUO = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_kraj_uo].ToString())).ToString("dd.MM.yyyy");
}
}
catch
{
if (Convert.ToDateTime(row_siebel_export.IstekUO) < Convert.ToDateTime(_2dArray[i, dat_kraj_uo].ToString().Substring(2, _2dArray[i, dat_kraj_uo].ToString().Length - 2)))
{
row_siebel_export.IstekUO = DateTime.ParseExact(_2dArray[i, dat_kraj_uo].ToString().Substring(2, _2dArray[i, dat_kraj_uo].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
}
if (_2dArray[i, pnp] != null && String.Compare(_2dArray[i, pnp].ToString(), "") != 0 && String.Compare(_2dArray[i, pnp].ToString(), "--") != 0)
{
row_siebel_export.PNP = _2dArray[i, pnp].ToString();
}
if (_2dArray[i, odl_prof] != null && String.Compare(_2dArray[i, odl_prof].ToString(), "") != 0 && String.Compare(_2dArray[i, odl_prof].ToString(), "--") != 0)
{
row_siebel_export.OdlazniProfil = _2dArray[i, odl_prof].ToString();
}
if (_2dArray[i, dol_prof] != null && String.Compare(_2dArray[i, dol_prof].ToString(), "") != 0 && String.Compare(_2dArray[i, dol_prof].ToString(), "--") != 0)
{
row_siebel_export.DolazniProfil = _2dArray[i, dol_prof].ToString();
}
if (_2dArray[i, stat_uo] != null && String.Compare(_2dArray[i, stat_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, stat_uo].ToString(), "--") != 0)
{
row_siebel_export.StatusUgovorneObveze = _2dArray[i, stat_uo].ToString();
}
if (_2dArray[i, br_dana_uo] != null && String.Compare(_2dArray[i, br_dana_uo].ToString(), "") != 0 && String.Compare(_2dArray[i, br_dana_uo].ToString(), "--") != 0)
{
row_siebel_export.PreostaloDana = _2dArray[i, br_dana_uo].ToString();
}
if (_2dArray[i, multisim] != null && String.Compare(_2dArray[i, multisim].ToString(), "") != 0 && String.Compare(_2dArray[i, multisim].ToString(), "--") != 0)
{
row_siebel_export.MultiSIM_nominacija = _2dArray[i, multisim].ToString();
multisimcount = 1;
}
if (_2dArray[i, korp_apn] != null && String.Compare(_2dArray[i, korp_apn].ToString(), "") != 0 && String.Compare(_2dArray[i, korp_apn].ToString(), "--") != 0)
{
row_siebel_export.KorporativniAPN = _2dArray[i, korp_apn].ToString();
korporativniAPN = 1;
}
if (_2dArray[i, limit] != null && String.Compare(_2dArray[i, limit].ToString(), "") != 0 && String.Compare(_2dArray[i, limit].ToString(), "--") != 0)
{
row_siebel_export.LimitPotrosnje = _2dArray[i, limit].ToString();
limitPotrosnje = 1;
}
if (_2dArray[i, klas_proiz] == null)
{
continue;
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Root Service") == 0)
{
if (_2dArray[i, dat_akt] != null && String.Compare(_2dArray[i, dat_akt].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_akt].ToString(), "--") != 0)
{
try
{
row_siebel_export.DatumAktivacijeUsluge = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy");
}
catch
{
row_siebel_export.DatumAktivacijeUsluge = DateTime.ParseExact(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
row_siebel_export.Usluga = _2dArray[i, proizv].ToString();
}
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Tariff") == 0)
{
if (_2dArray[i, dat_akt] != null && String.Compare(_2dArray[i, dat_akt].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_akt].ToString(), "--") != 0)
{
try
{
row_siebel_export.DatumAktivacijeTarife = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy");
}
catch
{
row_siebel_export.DatumAktivacijeTarife = DateTime.ParseExact(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
row_siebel_export.Tarifa = _2dArray[i, proizv].ToString();
}
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Hardware") == 0)
{
/* simovi lista */
SIM_S sim = new SIM_S();
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
sim.Naziv = _2dArray[i, proizv].ToString();
}
if (_2dArray[i, ser_sim] != null && String.Compare(_2dArray[i, ser_sim].ToString(), "") != 0 && String.Compare(_2dArray[i, ser_sim].ToString(), "--") != 0)
{
sim.Serial = _2dArray[i, ser_sim].ToString();
}
row_siebel_export.Simovi.Add(sim);
if (unique_sims.Find(x => String.Compare(x, sim.Naziv) == 0) == null)
{
unique_sims.Add(sim.Naziv);
}
}
else if (String.Compare(_2dArray[i, klas_proiz].ToString(), "Split Biller") == 0)
{
if (_2dArray[i, sb_kor] != null && String.Compare(_2dArray[i, sb_kor].ToString(), "") != 0 && String.Compare(_2dArray[i, sb_kor].ToString(), "--") != 0)
{
row_siebel_export.SplitBiller = _2dArray[i, sb_kor].ToString();
}
if (_2dArray[i, vpn_budget] != null && String.Compare(_2dArray[i, vpn_budget].ToString(), "") != 0 && String.Compare(_2dArray[i, vpn_budget].ToString(), "--") != 0)
{
row_siebel_export.Vpn_budget = _2dArray[i, vpn_budget].ToString();
}
if (_2dArray[i, prof_napl_sb] != null && String.Compare(_2dArray[i, prof_napl_sb].ToString(), "") != 0 && String.Compare(_2dArray[i, prof_napl_sb].ToString(), "--") != 0)
{
row_siebel_export.ProfilNaplateSB = _2dArray[i, prof_napl_sb].ToString();
}
splitBillerActive = true;
}
else
{
//SVE OSTALO
if (_2dArray[i, dat_akt] != null && String.Compare(_2dArray[i, dat_akt].ToString(), "") != 0 && String.Compare(_2dArray[i, dat_akt].ToString(), "--") != 0)
{
try
{
if (Convert.ToDateTime(row_proizvod.DatumAktivacije) < Convert.ToDateTime(DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy")))
{
row_proizvod.DatumAktivacije = DateTime.FromOADate(Convert.ToDouble(_2dArray[i, dat_akt].ToString())).ToString("dd.MM.yyyy");
}
}
catch
{
if (Convert.ToDateTime(row_proizvod.DatumAktivacije) < Convert.ToDateTime(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2)))
{
row_proizvod.DatumAktivacije = DateTime.ParseExact(_2dArray[i, dat_akt].ToString().Substring(2, _2dArray[i, dat_akt].ToString().Length - 2), "dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
}
}
}
if (_2dArray[i, proizv] != null && String.Compare(_2dArray[i, proizv].ToString(), "") != 0 && String.Compare(_2dArray[i, proizv].ToString(), "--") != 0)
{
row_proizvod.Proizvod = _2dArray[i, proizv].ToString();
unique_proizvod.Proizvod = _2dArray[i, proizv].ToString();
}
if (_2dArray[i, klas_proiz] != null && String.Compare(_2dArray[i, klas_proiz].ToString(), "") != 0 && String.Compare(_2dArray[i, klas_proiz].ToString(), "--") != 0)
{
row_proizvod.KlasifikacijaProizvoda = _2dArray[i, klas_proiz].ToString();
unique_proizvod.KlasifikacijaProizvoda = _2dArray[i, klas_proiz].ToString();
}
row_siebel_export.Proizvodi.Add(row_proizvod);
if (unique_proizvodi.Find(x => String.Compare(x.Proizvod, unique_proizvod.Proizvod) == 0) == null)
{
unique_proizvodi.Add(unique_proizvod);
}
}
if (i != rowCount && String.Compare(temp_broj, _2dArray[i + 1, broj_telefona].ToString()) != 0)
{
row_siebel_export.Simovi = row_siebel_export.Simovi.OrderBy(x => x.Naziv).ToList();
dataSiebelExport.Add(row_siebel_export);
if (i + 1 < rowCount)
{
temp_broj = _2dArray[i + 1, broj_telefona].ToString();
row_siebel_export = new Siebel_export();
}
}
}
}
else if (i + 1 <= rowCount && String.Compare(temp_broj, _2dArray[i + 1, broj_telefona].ToString()) != 0)
{
if (row_siebel_export.Status != null)
{
row_siebel_export.Simovi = row_siebel_export.Simovi.OrderBy(x => x.Naziv).ToList();
dataSiebelExport.Add(row_siebel_export);
}
if (i + 1 < rowCount)
{
temp_broj = _2dArray[i + 1, broj_telefona].ToString();
row_siebel_export = new Siebel_export();
}
}
if (i == rowCount && row_siebel_export.BrojTelefona != null)
{
row_siebel_export.Simovi = row_siebel_export.Simovi.OrderBy(x => x.Naziv).ToList();
dataSiebelExport.Add(row_siebel_export);
}
}
unique_proizvodi = unique_proizvodi.OrderBy(x => x.KlasifikacijaProizvoda).ThenBy(x => x.Proizvod).ToList();
unique_sims = unique_sims.OrderBy(x => x).ToList();
dataSiebelExport = dataSiebelExport.OrderBy(x => x.BrojTelefona).ToList();
xlWorkbook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
loadInfo.Text = "Podatci su učitani";
int rowCountExport = dataSiebelExport.Count + 1;
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workBook = excelApp.Workbooks.Add(Type.Missing);
Excel._Worksheet workSheet = workBook.Worksheets[1];
workSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
object[,] _2dData = new object[rowCountExport, 16 + multisimcount + korporativniAPN + limitPotrosnje + 3 + unique_sims.Count + unique_proizvodi.Count];
workSheet.Name = "Export";
_2dData[0, 0] = "Korisnik za naplatu";
_2dData[0, 1] = "Korisnik za uslugu";
_2dData[0, 2] = "Datum aktivacije usluge";
_2dData[0, 3] = "Usluga";
_2dData[0, 4] = "Broj telefona";
_2dData[0, 5] = "Status";
_2dData[0, 6] = "Profil naplate";
if (splitBillerActive)
{
_2dData[0, 7] = "Split Biller";
_2dData[0, 8] = "Iznos limita - VPN Budget";
_2dData[0, 9] = "Profil Naplate SB";
sbBroj = 3;
}
_2dData[0, 7 + sbBroj] = "Datum aktivacije tarife";
_2dData[0, 8 + sbBroj] = "Tarifa";
_2dData[0, 9 + sbBroj] = "Početak ugovorne obveze";
_2dData[0, 10 + sbBroj] = "Istek ugovorne obveze";
_2dData[0, 11 + sbBroj] = "PNP";
_2dData[0, 12 + sbBroj] = "Odlazni profil";
_2dData[0, 13 + sbBroj] = "Dolazni profil";
_2dData[0, 14 + sbBroj] = "Status ugovorne obveze";
_2dData[0, 15 + sbBroj] = "Preostali broj dana ugovorne obveze";
if (limitPotrosnje == 1)
{
_2dData[0, 16 + sbBroj] = "Iznos limita potrošnje";
}
if (korporativniAPN == 1)
{
_2dData[0, 16 + limitPotrosnje + sbBroj] = "Korporativni APN";
}
if (multisimcount == 1)
{
_2dData[0, 16 + limitPotrosnje + korporativniAPN + sbBroj] = "MultiSIM nominacija";
}
string stupac = GetExcelColumnName(16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_proizvodi.Count + unique_sims.Count);
workSheet.get_Range("a1", stupac + "1").Cells.Interior.Color = System.Drawing.Color.Orange;
workSheet.get_Range("a1", stupac + "1").Cells.Font.Color = System.Drawing.Color.Black;
workSheet.get_Range("a1", stupac + "1").Cells.Font.Bold = true;
workSheet.get_Range("a1", stupac + "1").Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3d;
Excel.Range c1 = workSheet.Cells[1, 17 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj];
Excel.Range c2 = workSheet.Cells[rowCountExport, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count];
Excel.Range rangeNum = workSheet.get_Range(c1, c2);
rangeNum.NumberFormat = "@";
rangeNum.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
for (int k = 1; k <= unique_sims.Count; k++)
{
_2dData[0, 15 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + k] = unique_sims[k - 1];
}
for (int k = 1; k <= unique_proizvodi.Count; k++)
{
_2dData[0, 15 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + k] = unique_proizvodi[k - 1].Proizvod;
}
for (int i = 0; i < dataSiebelExport.Count; i++)
{
_2dData[i + 1, 0] = dataSiebelExport[i].KorisnikZaNaplatu;
_2dData[i + 1, 1] = dataSiebelExport[i].KorisnikZaUslugu;
_2dData[i + 1, 2] = Convert.ToDateTime(dataSiebelExport[i].DatumAktivacijeUsluge);
_2dData[i + 1, 3] = dataSiebelExport[i].Usluga;
_2dData[i + 1, 4] = dataSiebelExport[i].BrojTelefona;
_2dData[i + 1, 5] = dataSiebelExport[i].Status;
_2dData[i + 1, 6] = dataSiebelExport[i].ProfilNaplate;
if (splitBillerActive)
{
_2dData[i + 1, 7] = dataSiebelExport[i].SplitBiller;
_2dData[i + 1, 8] = dataSiebelExport[i].Vpn_budget;
_2dData[i + 1, 9] = dataSiebelExport[i].ProfilNaplateSB;
}
_2dData[i + 1, 7 + sbBroj] = Convert.ToDateTime(dataSiebelExport[i].DatumAktivacijeTarife);
_2dData[i + 1, 8 + sbBroj] = dataSiebelExport[i].Tarifa;
_2dData[i + 1, 9 + sbBroj] = Convert.ToDateTime(dataSiebelExport[i].PocetakUO);
_2dData[i + 1, 10 + sbBroj] = Convert.ToDateTime(dataSiebelExport[i].IstekUO);
_2dData[i + 1, 11 + sbBroj] = dataSiebelExport[i].PNP;
_2dData[i + 1, 12 + sbBroj] = dataSiebelExport[i].OdlazniProfil;
_2dData[i + 1, 13 + sbBroj] = dataSiebelExport[i].DolazniProfil;
_2dData[i + 1, 14 + sbBroj] = dataSiebelExport[i].StatusUgovorneObveze;
_2dData[i + 1, 15 + sbBroj] = dataSiebelExport[i].PreostaloDana;
if (limitPotrosnje == 1)
{
_2dData[i + 1, 16 + sbBroj] = dataSiebelExport[i].LimitPotrosnje;
}
if (korporativniAPN == 1)
{
_2dData[i + 1, 16 + limitPotrosnje + sbBroj] = dataSiebelExport[i].KorporativniAPN;
}
if (multisimcount == 1)
{
_2dData[i + 1, 16 + limitPotrosnje + korporativniAPN + sbBroj] = dataSiebelExport[i].MultiSIM_nominacija;
}
foreach (SIM_S temp in dataSiebelExport[i].Simovi)
{
_2dData[i + 1, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.IndexOf(temp.Naziv)] = temp.Serial;
}
foreach (Proizvodi temp in dataSiebelExport[i].Proizvodi)
{
int index = unique_proizvodi.FindIndex(x => String.Compare(x.Proizvod, temp.Proizvod) == 0);
_2dData[i + 1, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + index] = "X";
}
}
c1 = workSheet.Cells[1, 1];
c2 = workSheet.Cells[rowCountExport, 16 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + unique_proizvodi.Count];
Excel.Range range = workSheet.get_Range(c1, c2);
if (chkDatumi.Checked)
{
for (int i = 0; i < dataSiebelExport.Count; i++)
{
foreach (Proizvodi temp in dataSiebelExport[i].Proizvodi)
{
int index = unique_proizvodi.FindIndex(x => String.Compare(x.Proizvod, temp.Proizvod) == 0);
c1 = workSheet.Cells[i + 2, 17 + multisimcount + limitPotrosnje + korporativniAPN + sbBroj + unique_sims.Count + index];
c1.AddComment(temp.DatumAktivacije);
}
}
}
c1 = workSheet.Cells[1, 5];
c2 = workSheet.Cells[rowCountExport, 7];
rangeNum = workSheet.get_Range(c1, c2);
rangeNum.NumberFormat = "#";
rangeNum.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
c1 = workSheet.Cells[1, 12];
c2 = workSheet.Cells[rowCountExport, 12];
workSheet.get_Range(c1, c2).Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
c1 = workSheet.Cells[1, 16];
c2 = workSheet.Cells[rowCountExport, 16 + sbBroj + unique_proizvodi.Count + unique_sims.Count];
workSheet.get_Range(c1, c2).Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.Value = _2dData;
workSheet.Application.ActiveWindow.SplitRow = 1;
workSheet.Application.ActiveWindow.FreezePanes = true;
Excel.Range firstRow = (Excel.Range)workSheet.Rows[1];
firstRow.Activate();
firstRow.Select();
firstRow.AutoFilter(1,
Type.Missing,
Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
workSheet.get_Range("A:" + stupac, Type.Missing).Columns.AutoFit();
excelApp.DisplayAlerts = true;
excelPath = Path.GetDirectoryName(Environment.GetCommandLineArgs()[0]);
bool tempSave = false;
int saveCounter = 0;
while (!tempSave)
{
try
{
tempSave = true;
if (saveCounter == 0)
{
workSheet.SaveAs(excelPath + "\\" + "export " + dataSiebelExport[0].KorisnikZaNaplatu + " " + DateTime.Now.ToShortDateString() + ".xlsx");
}
else
{
workSheet.SaveAs(excelPath + "\\" + "export " + dataSiebelExport[0].KorisnikZaNaplatu + " " + DateTime.Now.ToShortDateString() + ".xlsx");
}
}
catch
{
tempSave = false;
saveCounter++;
}
}
workBook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
Cursor = DefaultCursor;
loadInfo.Text = "Export završen";
loadInfo.ForeColor = Color.Green;
chkDatumi.Enabled = false;
btnExport.Text = "Zatvori";
btnOpenExport.Enabled = false;
}
}
private Boolean RefreshSheet(CellUpdate uc)
{
Excel.Worksheet ws = null;
Excel.Range thisRange = null;
Excel.Comment comment = null;
try
{
ws = GlobalFunctions.findWorksheetByName(uc.Worksheet);
if (ws == null)
{
throw new Exception("Worksheet not found: " + uc.Worksheet);
}
GlobalFunctions.WaitForApplicationReady();
thisRange = GlobalFunctions.createRange(ws, uc.Row, uc.Col);
if (thisRange != null)
{
CellUpdate oldCell = new CellUpdate(thisRange, uc.TypeEnum);
if (!uc.Equals(oldCell))
{
switch (uc.TypeEnum)
{
case Enums.CellChangeType.Value:
thisRange.Formula = uc.val;
break;
case Enums.CellChangeType.Comment:
comment = thisRange.Comment;
if (comment == null)
{
thisRange.AddComment(uc.val);
}
else
{
if (String.IsNullOrEmpty(uc.val))
{
thisRange.ClearComments();
}
else
{
comment.Text(uc.val);
}
}
break;
}
}
GlobalFunctions.InfoLog("Received", uc);
//RefreshedCell rc = new RefreshedCell(thisRange, uc, oldCell.val);
//RefreshedCell rc = new RefreshedCell(thisRange, uc, "");
Vars.LatestUpdateTime = GlobalFunctions.MaxDate(Vars.LatestUpdateTime, uc.changeTime.AddSeconds(-1));
}
else
{
Marshal.ReleaseComObject(thisRange);
}
return(true);
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex, uc);
throw ex;
}
finally
{
if (thisRange != null)
{
Marshal.ReleaseComObject(thisRange);
}
if (ws != null)
{
Marshal.ReleaseComObject(ws);
}
}
}
public void AddComment(Excel.Range c)
{
c.ClearComments();
c.AddComment(NameInfo + "\n" + DisplayInfo);
}
