private void excel_btnAc_Click(object sender, EventArgs e)
{
openFileDialog1.Title = "Belge seçiniz...";
openFileDialog1.Filter = "excel 2003 dosyaları (*.xls)|*.xls|excel 2010-2016 dosyaları (*.xlsx) |*.xlsx";
openFileDialog1.FilterIndex = 1;
if (openFileDialog1.ShowDialog() == DialogResult.Cancel)
{
MessageBox.Show("Dosya Seçilmedi....");
}
else
{
Excel.Application ExcelApp = new Excel.Application();
ExcelApp.Workbooks.Open(openFileDialog1.FileName);
ExcelApp.Visible = true;
Excel.Range bulunan = null;
int adet, fıyat;
string ürünkodu, dosya;
Boolean aranan_dosya;
ürünkodu = Interaction.InputBox("Lütfen kodu giriniz :");
ExcelApp.Worksheets[1].Activate();
bulunan = ExcelApp.Cells.Find(What: ürünkodu);
if (bulunan == null)
{
MessageBox.Show("Bulunamadı :");
}
else
{
bulunan.Activate();
MessageBox.Show("No =" + ürünkodu + ".Ürünadı =" + ExcelApp.Worksheets[1].Cells[bulunan.Column + 1, bulunan.Row].value);
}
ExcelApp.Application.Quit();
}
}
/// <summary>
/// HTML转换为Excel
/// </summary>
/// <param name="IsMergeCells"></param>
/// <param name="tableStr"></param>
public void HtmlToExcel(bool IsMergeCells, string tableStr)
{
string a1 = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\n<html xmlns=\"http://www.w3.org/1999/xhtml\">\n<head>\n<title></title>\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\" />\n<style>\n</style>\n</head>\n<body>\n";
string a2 = "\n</body>\n</html>";
tableStr = a1 + tableStr + a2;
if (IsMergeCells)
{
//合并单元格
//m_objRange = m_objSheet.get_Range("A1", "K4");
//m_objRange.Merge(System.Reflection.Missing.Value);
m_objRange = m_objSheet.get_Range("A1", "A1");
//m_objRange.
}
m_objRange.Select();
m_objRange.Cells[1, 1] = tableStr;
m_objRange.Activate();
m_objRange.Copy(Missing.Value);
//m_objSheet.get_Range("B2", "B2")
//.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationSubtract
//, false, false);
m_objRange.EntireColumn.AutoFit();
}
private void cellDown_Click(object sender, EventArgs e)
{
cellDown.Enabled = false;
activerange = book.Application.ActiveCell;
//セルカラーの初期化
if (activerange.Interior.ColorIndex != 38)
{
Cell_color = activerange.Interior.ColorIndex;
}
activerange.Interior.ColorIndex = Cell_color;
int Row = activerange.MergeArea.Row;
int Column = activerange.MergeArea.Column;
Row++;
activerange = jobsheet.Cells[Row, Column];
Column = activerange.MergeArea.Column;
activerange = jobsheet.Cells[Row, Column];
activerange.Activate();
activerange.MergeArea.Select();
Cell_color = activerange.Interior.ColorIndex;
activerange.MergeArea.Interior.ColorIndex = 38;
activeCellUpdate();
nameViewUpdate2(bureauTextBox.Text, gradeTextBox.Text, jobBox.SelectedItem.ToString(), jobBox2.SelectedItem.ToString());
cellDown.Enabled = true;
}
public bool CheckId(bool showMessagebox)
{
string name = app.ActiveWorkbook.FullName;
if (!name.ToLower().EndsWith(".csv"))
{
return(true);
}
Excel.Worksheet workSheet = app.ActiveWorkbook.ActiveSheet;
Excel.Range range = workSheet.UsedRange;
string title = range.Cells[2, 1].Value2.ToString();
if (title != "id" && title != "Name")
{
return(true);
}
Dictionary <string, int> dict = new Dictionary <string, int>();
for (int i = 4; 1 <= range.Rows.Count; i++)
{
Excel.Range cell = range.Cells[i, 1];
if (cell.Value2 == null)
{
break;
}
//int value = 0;
string value = cell.Value2.ToString();
if (string.IsNullOrEmpty(value))
{
MessageBox.Show("第" + i + "行序号错误。", "序号错误", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
else
{
if (!dict.ContainsKey(value))
{
dict[value] = i;
}
else
{
cell.Activate();
MessageBox.Show("第" + i + "行序号[" + value + "]与第" + dict[value] + "行序号重复。", "序号重复", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
}
}
if (showMessagebox)
{
MessageBox.Show("序号检查完成，未发现重复序号。", "检查完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(true);
}
public void FrozenTrailing()
{
string name = app.ActiveWorkbook.FullName;
if (!name.ToLower().EndsWith(".csv"))
{
return;
}
Excel.Worksheet workSheet = app.ActiveWorkbook.ActiveSheet;
Excel.Range range = workSheet.UsedRange;
Excel.Range cell = range.Cells[4, GetForzenColumn(name)];
cell.Activate();
cell.Application.ActiveWindow.FreezePanes = true;
}
public bool CheckContent()
{
string name = app.ActiveWorkbook.FullName;
if (!name.Contains("CSVUTF8Strings"))
{
return(true);
}
Excel.Worksheet workSheet = app.ActiveWorkbook.ActiveSheet;
Excel.Range range = workSheet.UsedRange;
for (int i = 4; 1 <= range.Rows.Count; i++)
{
Excel.Range cell = range.Cells[i, 2];
if (string.IsNullOrEmpty(cell.Value2) || string.IsNullOrWhiteSpace(cell.Value2))
{
cell.Activate();
MessageBox.Show("第" + i + "行内容错误。", "内容错误", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
}
return(true);
}
/// <summary>
/// Add a picture file named <paramref name="pictureFileName"/> on <paramref name="cell"/> and name <paramref name="name"/>.
/// </summary>
/// <param name="cell">The cell to add a picture.</param>
/// <param name="pictureFileName">File name of the picture.</param>
/// <param name="name">Excel name of the picture.</param>
public static void AddPicture(Excel.Range cell, string pictureFileName, string name)
{
cell.Activate();
dynamic sheet = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Shapes shapes = sheet.Shapes;
Excel.Shape shape = shapes.AddPicture(
pictureFileName,
Office.MsoTriState.msoFalse, Office.MsoTriState.msoTrue,
(float)cell.Left, (float)cell.Top, (float)cell.Width, (float)cell.Height);
shape.Placement = Excel.XlPlacement.xlMove;
shape.Name = name;
// if you want to fit pictures with each cell, delte the following lines.
shape.ScaleHeight(1, Office.MsoTriState.msoTrue);
shape.ScaleWidth(1, Office.MsoTriState.msoTrue);
var xyRatioCell = (float)cell.Width / (float)cell.Height;
var xyRatioShape = shape.Width / shape.Height;
float scale = 1;
if (xyRatioCell > xyRatioShape)
{
// fit to cell.Height
scale = (float)cell.Height / shape.Height;
}
else
{
// fit to cell.Width;
scale = (float)cell.Width / shape.Width;
}
shape.ScaleHeight(scale, Office.MsoTriState.msoTrue);
shape.ScaleWidth(scale, Office.MsoTriState.msoTrue);
shape.LockAspectRatio = Office.MsoTriState.msoTrue;
}
public void CrearArchivoExcel(string NombreArchivo, System.Data.DataTable dtInfo, string TituloArchivo, BackgroundWorker Proceso)
{
Microsoft.Office.Interop.Excel._Application xlApp = null;
Workbook xlWorkbook = null;
Sheets xlSheets = null;
Worksheet xlNewSheet = null;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
if (xlApp == null)
{
return;
}
// Uncomment the line below if you want to see what's happening in Excel
// xlApp.Visible = true;
xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//===================== se elimino, este procedimiento permite abrir el archivo tomando como referencia el nombre del mismo =====================
//xlWorkbook = xlApp.Workbooks.Open(RutaDeArchivo, 0, false, 5, "", "",
// false, XlPlatform.xlWindows, "",
// true, false, 0, true, false, false);
//===============================================================================================================================================
//xlWorkbook.CheckCompatibility = false;
//xlWorkbook.Application.DisplayAlerts = false;
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
xlSheets = xlWorkbook.Sheets as Sheets;
xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
xlNewSheet.Name = NombreArchivo;
xlNewSheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible;
EdicionHojaExcel(xlNewSheet, dtInfo, TituloArchivo);
int FilaArchivo = 6;
int i = 0;
foreach (System.Data.DataColumn dColum in dtInfo.Columns)
{
i++;
xlNewSheet.Cells[FilaArchivo, i] = dColum.ToString();
}
//============================================ Funcion que permite usar la opcion FREEZE PANES por codigo ====================================================
// Se declara el punto de referencia tomando en cuenta la celda
Rangos = xlNewSheet.get_Range("A7", "E7");
Rangos.Activate();
Rangos.Application.ActiveWindow.FreezePanes = true;
// =========================================== Esta es la forma como se activa la funcion de FREZEE PANES ====================================================
int Fila = 6;
int Total = dtInfo.Rows.Count;
int Porcentaje = 0;
foreach (DataRow dr in dtInfo.Rows)
{
Fila++;
for (int j = 0; j < dtInfo.Columns.Count; j++)
{
xlNewSheet.Cells[Fila, NombreColumna(j)] = dr[j].ToString();
}
Porcentaje = (Fila / Total) * 100;
Proceso.ReportProgress(Porcentaje);
}
xlApp.Visible = true;
releaseObject(xlApp);
releaseObject(xlWorkbook);
releaseObject(xlSheets);
releaseObject(xlNewSheet);
}
catch (Exception Error)
{
throw new Exception("Error: " + Error.Message);
}
finally
{
Marshal.ReleaseComObject(xlNewSheet);
Marshal.ReleaseComObject(xlSheets);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
}
public void CrearArchivoExcelQueryTables(string NombreArchivo, System.Data.DataTable dtInfo, string TituloArchivo, BackgroundWorker Proceso, string FechaInicial,
string FechaFinal, string SerialQR, string Nameplate, string IdModel, string Query)
{
const string OLEDBConnection = "OLEDB;Provider=SQLOLEDB.1;Data Source=172.20.96.13;UID=sa;PWD=;Initial Catalog=AirBag";
Microsoft.Office.Interop.Excel._Application xlApp = null;
Workbook xlWorkbook = null;
Sheets xlSheets = null;
Worksheet xlNewSheet = null;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
if (xlApp == null)
{
return;
}
// Uncomment the line below if you want to see what's happening in Excel
// xlApp.Visible = true;
xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//===================== se elimino, este procedimiento permite abrir el archivo tomando como referencia el nombre del mismo =====================
//xlWorkbook = xlApp.Workbooks.Open(RutaDeArchivo, 0, false, 5, "", "",
// false, XlPlatform.xlWindows, "",
// true, false, 0, true, false, false);
//===============================================================================================================================================
//xlWorkbook.CheckCompatibility = false;
//xlWorkbook.Application.DisplayAlerts = false;
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
xlSheets = xlWorkbook.Sheets as Sheets;
xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
xlNewSheet.Name = NombreArchivo;
xlNewSheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible;
EdicionHojaExcel(xlNewSheet, dtInfo, TituloArchivo);
//============================================ Funcion que permite usar la opcion FREEZE PANES por codigo ====================================================
// Se declara el punto de referencia tomando en cuenta la celda
Rangos = xlNewSheet.get_Range("A7", "E7");
Rangos.Activate();
Rangos.Application.ActiveWindow.FreezePanes = true;
// =========================================== Esta es la forma como se activa la funcion de FREZEE PANES ====================================================
xlNewSheet.QueryTables.Add(OLEDBConnection, xlNewSheet.get_Range("A6", "E6"), Query.Trim()).Refresh();
xlApp.Visible = true;
releaseObject(xlApp);
releaseObject(xlWorkbook);
releaseObject(xlSheets);
releaseObject(xlNewSheet);
}
catch (Exception Error)
{
throw new Exception("Error: " + Error.Message);
}
finally
{
Marshal.ReleaseComObject(xlNewSheet);
Marshal.ReleaseComObject(xlSheets);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
}
private void btnAccept_Click(object sender, EventArgs e)
{
svdReportStock.FileName = "Bitacora_" + DateTime.Now.ToString("ddMMyyyy_hhmmss") + ".xlsx";
if (svdReportStock.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
string type = null;
var lBinnacle = new posb.Binnacle().List(1);
if (lBinnacle.Count < 1)
{
this.Alert("No se encontraron registros");
return;
}
int index = 3;
Microsoft.Office.Interop.Excel.Application xlApp = null;
Workbook xlWorkBook = null;
Worksheet xlWorkSheetItems = null;
var cc = new ColorConverter();
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
xlApp.EnableEvents = false;
xlWorkBook = xlApp.Workbooks.Open(this.GetPath() + "\\Templates\\" + this.AppSet <string>("BinnalceReport"), Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
//Agrega la hoja de items
xlWorkSheetItems = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (var binnacle in lBinnacle)
{
(xlWorkSheetItems.Cells[index, 1] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 1] = binnacle.Id.ToString().PadLeft(10, '0');
xlWorkSheetItems.Cells[index, 2] = binnacle.Usuario;
xlWorkSheetItems.Cells[index, 3] = binnacle.Accion;
xlWorkSheetItems.Cells[index, 4] = binnacle.Tipo;
(xlWorkSheetItems.Cells[index, 5] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 5] = binnacle.Identificador.ToString().PadLeft(10, '0');
(xlWorkSheetItems.Cells[index, 6] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 6] = binnacle.ValorPrevio;
(xlWorkSheetItems.Cells[index, 7] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 7] = binnacle.ValorActual;
(xlWorkSheetItems.Cells[index, 8] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 8] = binnacle.Fecha;
index++;
}
//Mantiene el encabezado fijo
xlWorkSheetItems.Application.ActiveWindow.SplitRow = 2;
xlWorkSheetItems.Application.ActiveWindow.FreezePanes = true;
//Agrega autofiltros
Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)xlWorkSheetItems.Rows[2];
firstRow.Activate();
firstRow.Select();
firstRow.AutoFilter(2,
Type.Missing,
Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
xlWorkSheetItems.Cells[2, 1].Select();
//Ajusta el ancho de las columnas a su contenido
Microsoft.Office.Interop.Excel.Range aRange = xlWorkSheetItems.get_Range("A1", "ZZ1000000");
aRange.EntireColumn.AutoFit();
xlApp.EnableEvents = true;
xlWorkBook.SaveAs(svdReportStock.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, false);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Application.Quit();
xlApp.Quit();
if (this.Confirm("¿Deseas abrir la bitácora?"))
{
Process.Start(svdReportStock.FileName);
}
}
catch (Exception ex)
{
this.Alert("Error: " + ex.Message);
}
finally
{
this.ReleasingObjects(xlWorkSheetItems, xlWorkBook, xlApp);
}
}
}
private void btnAccept_Click(object sender, EventArgs e)
{
if (this.ValidateForm())
{
this.svdReportStock.FileName = "PagosPorAño_" + DateTime.Now.ToString("ddMMyyyy_hhmmss") + ".xlsx";
if (svdReportStock.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
string type = null;
var lConcentrated = new posb.PaymentPerYear().Report(int.Parse(this.cmbYear.Text));
if (lConcentrated.Count < 1)
{
this.Alert("No se encontraron registros en el año indicado");
return;
}
int index = 3;
Microsoft.Office.Interop.Excel.Application xlApp = null;
Workbook xlWorkBook = null;
Worksheet xlWorkSheetItems = null;
var cc = new ColorConverter();
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
xlApp.EnableEvents = false;
xlWorkBook = xlApp.Workbooks.Open(this.GetPath() + "\\Templates\\" + this.AppSet <string>("PaymentYearReport"), Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
//Agrega la hoja de items
xlWorkSheetItems = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (var concentrated in lConcentrated)
{
(xlWorkSheetItems.Cells[index, 1] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 1] = concentrated.AP.ToString().PadLeft(10, '0');
(xlWorkSheetItems.Cells[index, 2] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 2] = concentrated.Name;
(xlWorkSheetItems.Cells[index, 3] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 3] = concentrated.IdWaterIntake.ToString().PadLeft(10, '0');
(xlWorkSheetItems.Cells[index, 4] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 4] = concentrated.NameWaterIntake;
WriteMonth(xlWorkSheetItems, index, 5, concentrated.Enero);
WriteMonth(xlWorkSheetItems, index, 6, concentrated.Febrero);
WriteMonth(xlWorkSheetItems, index, 7, concentrated.Marzo);
WriteMonth(xlWorkSheetItems, index, 8, concentrated.Abril);
WriteMonth(xlWorkSheetItems, index, 9, concentrated.Mayo);
WriteMonth(xlWorkSheetItems, index, 10, concentrated.Julio);
WriteMonth(xlWorkSheetItems, index, 11, concentrated.Julio);
WriteMonth(xlWorkSheetItems, index, 12, concentrated.Agosto);
WriteMonth(xlWorkSheetItems, index, 13, concentrated.Septiembre);
WriteMonth(xlWorkSheetItems, index, 14, concentrated.Octubre);
WriteMonth(xlWorkSheetItems, index, 15, concentrated.Noviembre);
WriteMonth(xlWorkSheetItems, index, 16, concentrated.Diciembre);
index++;
}
//Mantiene el encabezado fijo
xlWorkSheetItems.Application.ActiveWindow.SplitRow = 2;
xlWorkSheetItems.Application.ActiveWindow.FreezePanes = true;
//Agrega autofiltros
Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)xlWorkSheetItems.Rows[2];
firstRow.Activate();
firstRow.Select();
firstRow.AutoFilter(2,
Type.Missing,
Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
xlWorkSheetItems.Cells[2, 1].Select();
//Ajusta el ancho de las columnas a su contenido
Microsoft.Office.Interop.Excel.Range aRange = xlWorkSheetItems.get_Range("A1", "ZZ1000000");
aRange.EntireColumn.AutoFit();
xlApp.EnableEvents = true;
xlWorkBook.SaveAs(svdReportStock.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, false);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Application.Quit();
xlApp.Quit();
if (this.Confirm("¿Deseas abrir el reporte?"))
{
Process.Start(svdReportStock.FileName);
}
}
catch (Exception ex)
{
this.Alert("Error: " + ex.Message);
}
finally
{
this.ReleasingObjects(xlWorkSheetItems, xlWorkBook, xlApp);
}
}
}
}
private void CreateSheet(Excel.Worksheet oSheet, DateTime dFechaInicio, ref DateTime dFecha, DateTime dFechaFin, ref int contador)
{
bool swFound = false;
int nDay = int.Parse(dFecha.ToString("dd"));
int nMonth = int.Parse(dFecha.ToString("MM"));
int nHour = int.Parse(dFecha.ToString("HH"));
int nMinute = int.Parse(dFecha.ToString("mm"));
int nHourIni = nHour;
int nMinuteIni = nMinute;
int nHourFin = int.Parse(dFechaFin.ToString("HH"));
int nMinuteFin = int.Parse(dFechaFin.ToString("mm"));
int nDayOfWeek = NumberOfDayOfWeek(dFecha);
object missing = Type.Missing;
//string sFechaInicio = null;
//string sFechaFin = null;
// Crear columna 1 con las programaciones de los comandos
oSheet.Name = dFecha.ToString("dd MM yyyy");
int nRow = 1;
oSheet.Cells[nRow, 1] = "Programación";
foreach (CronRegister myCronRegister in myLinesList)
{
if (myCronRegister.bChecked)
{
nRow++;
oSheet.Cells[nRow, 1] = myCronRegister.sMinute + " " +
myCronRegister.sHour + " " +
myCronRegister.sDay_of_month + " " +
myCronRegister.sMonth + " " +
myCronRegister.sDay_of_week;
}
}
// Crear columna 2 con los nombres de los comandos
nRow = 1;
oSheet.Cells[nRow, 2] = "Comando";
foreach (CronRegister myCronRegister in myLinesList)
{
if (myCronRegister.bChecked)
{
nRow++;
string sCommand = null;
bool flag = false;
foreach (string myCommand in myCronRegister.listCommand)
{
if (flag)
{
sCommand += " ";
}
else
{
flag = true;
}
sCommand += myCommand;
}
oSheet.Cells[nRow, 2] = sCommand;
}
}
int nCol = 2; int nMaxCol = nCol;
while (dFecha <= dFechaFin)
{
nCol++;
if (nMaxCol < nCol)
{
nMaxCol = nCol;
}
//((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, nCol]).Name = "H" + dFecha.ToString("HH") + "M" + dFecha.ToString("mm");
//((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, nCol]).
// Establecer HH:MM en la columna
oSheet.Cells[1, nCol] = "[" + dFecha.ToString("HH") + ":" + dFecha.ToString("mm") + "]";
//oSheet.Cells.Name = "H" + dFecha.ToString("HH") + "M" + dFecha.ToString("mm");
nRow = 1;
foreach (CronRegister myCronRegister in myLinesList)
{
if (myCronRegister.bChecked)
{
swFound = false;
foreach (int dayOfWeek in myCronRegister.nDays_of_week)
{
if (nDayOfWeek == dayOfWeek)
{
swFound = true;
break;
}
}
if (swFound)
{
swFound = false;
foreach (int month in myCronRegister.nMonths)
{
if (nMonth == month)
{
swFound = true;
break;
}
}
}
if (swFound)
{
swFound = false;
foreach (int dayOfMonth in myCronRegister.nDays_of_month)
{
if (nDay == dayOfMonth)
{
swFound = true;
break;
}
}
}
if (swFound)
{
swFound = false;
foreach (int hour in myCronRegister.nHours)
{
if (nHour == hour)
{
swFound = true;
break;
}
}
}
if (swFound)
{
swFound = false;
foreach (int minute in myCronRegister.nMinutes)
{
if (nMinute == minute)
{
swFound = true;
break;
}
}
}
nRow++;
if (swFound)
{
oSheet.Cells[nRow, nCol] = "X";
oSheet.Cells[nRow, nCol].Name = "H" + dFecha.ToString("HH") + "_" + dFecha.ToString("mm") + "_" + contador.ToString();
contador++;
}
//oSheet.Cells[nRow, nCol].Name = "pepe_" + contador.ToString();
//try
//{
//}
//catch (Exception ex)
//{
// Console.WriteLine(ex.Message);
//}
//=CONTAR.SI(RN2:RN202;"X")
}
}
//contador = 1;
DateTime newDate = dFecha.AddMinutes(1);
dFecha = newDate;
nHour = int.Parse(dFecha.ToString("HH"));
nMinute = int.Parse(dFecha.ToString("mm"));
if ((nMinute > nMinuteFin) && (nHour == nHourFin))
{
newDate = dFecha.AddDays(1);
dFecha = newDate;
dFecha = DateTime.Parse(newDate.ToString("yyyy/MM/dd") + " " + dFechaInicio.ToString("HH:mm"));
break;
}
if ((nHour == 0) && (nMinute == 0))
{
break;
}
}
nCol = 3;
while (nCol <= nMaxCol)
{
string colInicial = oSheet.Columns[nCol].Address + "2";
colInicial = colInicial.Substring(4);
string colFinal = oSheet.Columns[nCol].Address + nRow.ToString();
colFinal = colFinal.Substring(4);
string colFormula = oSheet.Columns[nCol].Address + (nRow + 1).ToString();
colFormula = colFormula.Substring(4);
try
{
//oSheet.Cells[nRow + 1, nCol].Formula = "=CONTAR.SI(" + colInicial + ":" + colFinal + ";\"X\")";
Excel.Range rangeFormula = oSheet.get_Range(colFormula, colFormula);
rangeFormula.Formula = "=COUNTIF(" + colInicial + ":" + colFinal + "," + '"' + "X" + '"' + ")";
}
catch (COMException ex)
{
Console.Write(ex.ErrorCode);
}
nCol++;
}
Excel.Range last = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, missing);
Excel.Range rangeAll = oSheet.get_Range("A1", last);
Excel.Range rangeHours = oSheet.get_Range("C1", last);
Excel.Range rangeA2 = oSheet.get_Range("A2", "A2");
Excel.Range rangeProg = oSheet.get_Range("A1", "A1");
Excel.Range rangeCommand = oSheet.get_Range("B1", "B1");
//rangeAll.Columns.AutoFit();
rangeProg.Columns.ColumnWidth = 80;
rangeCommand.Columns.ColumnWidth = 100;
rangeHours.Columns.ColumnWidth = 9;
//rangeProgram.Columns.AutoFit();
//range.Columns.NumberFormat = "@";
rangeAll.AutoFilter(1, missing, Excel.XlAutoFilterOperator.xlAnd, missing, true);
rangeA2.Activate();
rangeA2.Select();
rangeA2.Application.ActiveWindow.FreezePanes = true;
}
public void Freeze_panes(int row, int col)
{
Excel.Range myCell = worksheet.Cells[row, col];
myCell.Activate();
myCell.Application.ActiveWindow.FreezePanes = true;
}
private void button1_Click(object sender, EventArgs e)
{
textBox1.AppendText("Start decoupage");
string pathnotapme = @"D:\ptw\notepme";
//pathstylerfinal = @textBox12.Text + "\\changeStyle\\divi\\final";
string openfilex = @"D:\ptw\Histo.xlsx";
////////////////open excel///////////////////////////////////////
Thread.Sleep(3000);
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Workbook xlWorkBookx1;
Excel.Workbook xlWorkBooknewx1;
object misValue = System.Reflection.Missing.Value;
//////////creat modele histox.xls pour fichier diviser////////////////////////////////
Excel.Application xlAppRef;
Excel.Workbook xlWorkBookRef;
xlAppRef = new Excel.ApplicationClass();
xlAppRef.Visible = false;; xlAppRef.DisplayAlerts = false; xlAppRef.ScreenUpdating = false;
xlAppRef.DisplayAlerts = false;
xlWorkBookRef = xlAppRef.Workbooks.Open(openfilex, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//xlWorkBookRef = xlAppRef.Workbooks.Open(openfilex, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
Excel.Worksheet xlWorkSheetRef = (Excel.Worksheet)xlWorkBookRef.Worksheets.get_Item("Historique");
Excel.Range rangeRefall = xlWorkSheetRef.get_Range("A1", "W" + getRowsLimit(xlWorkSheetRef));
object[,] valuess = (object[, ])rangeRefall.Value2;
//bug : le seul moyen pour supprimer la dernière colonne est de chnager la largeur de toutes les colonnes (on ne sait pas pourquoi) !!!
// xlWorkSheetRef.Cells.ColumnWidth = 20;
int rowcount = rangeRefall.Rows.Count;
int colcount = rangeRefall.Columns.Count;
Excel.Range rangeRef = xlWorkSheetRef.get_Range("A" + getRowsLimit(xlWorkSheetRef), "W" + getRowsLimit(xlWorkSheetRef));
rangeRef.EntireRow.Copy(misValue);
rangeRef.EntireRow.PasteSpecial(Excel.XlPasteType.xlPasteValues, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, misValue, misValue);
Excel.Range rangeRefdel = xlWorkSheetRef.UsedRange.get_Range("X1", xlWorkSheetRef.Cells[1, xlWorkSheetRef.UsedRange.Columns.Count - 1]) as Excel.Range;
rangeRefdel.EntireColumn.ClearContents();
rangeRefdel.EntireColumn.ClearFormats();
rangeRefdel.EntireColumn.Clear();
try
{
rangeRefdel = xlWorkSheetRef.UsedRange.get_Range("A" + (1 + getRowsLimit(xlWorkSheetRef)), "A" + xlWorkSheetRef.UsedRange.Rows.Count) as Excel.Range;
}
catch (Exception rx)
{
textBox1.AppendText(rx.ToString());
}
rangeRefdel.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
rangeRefdel = xlWorkSheetRef.UsedRange.get_Range("A1", "W" + (getRowsLimit(xlWorkSheetRef) - 1)) as Excel.Range;
rangeRefdel.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
Excel.Range rangeA1 = xlWorkSheetRef.Cells[1, 1] as Excel.Range;
rangeA1.Activate();
xlWorkSheetRef.SaveAs(@"D:\ptw\Histo.xlsx", misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlWorkBookRef.Close(true, misValue, misValue);
xlAppRef.Quit();
//////////////////////////////////////////////////////////////////////////////////
Thread.Sleep(3000);
xlApp = new Excel.ApplicationClass();
xlApp.Visible = false; xlApp.DisplayAlerts = false; xlApp.ScreenUpdating = false;
xlApp.DisplayAlerts = false;
xlApp.Application.DisplayAlerts = false;
//MessageBox.Show(openfilex);//D:\ptw\Histo.xls
string remplacehisto8 = "[" + openfilex.Substring(7, 9) + "]";
//xlWorkBook = xlApp.Workbooks.Open(openfilex, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
xlWorkBook = xlApp.Workbooks.Open(openfilex, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("Historique");
Excel.Range range = xlWorkSheet.get_Range("A1", "AB2062");
object[,] values = (object[, ])range.Value2;
int rCnt = 0;
int rowx = xlWorkSheet.get_Range("A1", "AB2062").Rows.Count;
int cCnt = 0;
int col = 0;
int col3000 = 0;
int col4000 = 0;
int col5000 = 0;
int col8000 = 0;
int col83000 = 0;
rCnt = xlWorkSheet.get_Range("A1", "AB2062").Rows.Count;
for (cCnt = 1; cCnt <= xlWorkSheet.get_Range("A1", "AB2062").Columns.Count - 1; cCnt++)
{
string valuecellabs = Convert.ToString(values[rCnt, cCnt]);
if (Regex.Equals(valuecellabs, "3000"))
{
col3000 = cCnt;
}
if (Regex.Equals(valuecellabs, "4000"))
{
col4000 = cCnt;
}
if (Regex.Equals(valuecellabs, "5000"))
{
col5000 = cCnt;
}
if (Regex.Equals(valuecellabs, "8000"))
{
col8000 = cCnt;
}
if (Regex.Equals(valuecellabs, "10000"))
{
col = cCnt;
}
if (Regex.Equals(valuecellabs, "83000"))
{
col83000 = cCnt;
break;
}
}
int fileflag = 0;
for (int row = 25; row <= 2061; row++)
{
string value = Convert.ToString(values[row, col]);
if (Regex.Equals(value, "-1"))
{
textBox1.AppendText("Row number:" + row);
Thread.Sleep(3000);
xlWorkBookx1 = xlApp.Workbooks.Open(@"D:\ptw\Histo.xlsx", 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
// xlWorkBookx1 = xlApp.Workbooks.Open( @textBox12.Text + "\\Histox.xlsx", misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
Excel.Worksheet xlWorkSheetx1 = (Excel.Worksheet)xlWorkBookx1.Worksheets.get_Item("Historique");
string[] namestable = { "S-ACT.xlsx", "S-PAS.xlsx", "S-CR.xlsx", "S-ANN3.xlsx", "S-ANN4.xlsx", "S-ANN5.xlsx" };
string divisavenom = pathnotapme + "\\" + namestable[fileflag];
System.IO.Directory.CreateDirectory(pathnotapme);//////////////cree repertoire
xlWorkSheetx1.SaveAs(divisavenom, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlWorkBookx1.Close(true, misValue, misValue);
////////////Grande titre "-1"/////////////////////////////////////////////////////////////////
if (Regex.Equals(Convert.ToString(values[25, col]), "-1"))
{
Excel.Range rangegtitre = xlWorkSheet.Cells[25, col] as Excel.Range;
Excel.Range rangePastegtitre = xlWorkSheet.UsedRange.Cells[24, 1] as Excel.Range;
rangegtitre.EntireRow.Cut(rangePastegtitre.EntireRow);
Excel.Range rangegtitreblank = xlWorkSheet.Cells[25, col] as Excel.Range;
rangegtitreblank.EntireRow.Delete(misValue);
row--;// point important, pour garder l'ordre de ligne ne change pas
}
////////////////////insertion///////////////////////////////////////////////////////////////////
Excel.Range rangeDelx = xlWorkSheet.Cells[row, col] as Excel.Range;
Excel.Range rangediviser = xlWorkSheet.UsedRange.get_Range("A1", xlWorkSheet.Cells[row - 1, col - 1]) as Excel.Range;
Excel.Range rangedelete = xlWorkSheet.UsedRange.get_Range("A25", xlWorkSheet.Cells[row - 1, col]) as Excel.Range;
rangediviser.EntireRow.Select();
rangediviser.EntireRow.Copy(misValue);
//MessageBox.Show(row.ToString());
xlWorkBooknewx1 = xlApp.Workbooks.Open(divisavenom, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//xlWorkBooknewx1 = xlApp.Workbooks.Open(divisavenom, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlApp.DisplayAlerts = false;
xlApp.Application.DisplayAlerts = false;
Excel.Worksheet xlWorkSheetnewx1 = (Excel.Worksheet)xlWorkBooknewx1.Worksheets.get_Item("Historique");
/*Jintao: clearcontents for the worksheet
* Excel.Range erals = xlWorkSheetnewx1.UsedRange;
* erals.ClearContents();*/
//xlWorkBooknewx1.set_Colors(misValue, xlWorkBook.get_Colors(misValue));
Excel.Range rangenewx1 = xlWorkSheetnewx1.Cells[1, 1] as Excel.Range;
rangenewx1.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, misValue);
xlWorkSheetnewx1.SaveAs(divisavenom, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
//modifier lien pour effacer cross file reference!!!!!!!!!!!!!!2003-2010
xlWorkBooknewx1.ChangeLink(openfilex, divisavenom);
xlWorkBooknewx1.Close(true, misValue, misValue);
////////////////////replace formulaire contient ptw/histo8.xls///////////////////
Excel.Workbook xlWorkBookremplace = xlApp.Workbooks.Open(divisavenom, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Excel.Workbook xlWorkBookremplace = xlApp.Workbooks.Open(divisavenom, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
xlApp.DisplayAlerts = false;
xlApp.Application.DisplayAlerts = false;
Excel.Worksheet xlWorkSheetremplace = (Excel.Worksheet)xlWorkBookremplace.Worksheets.get_Item("Historique");
Excel.Range rangeremplace = xlWorkSheetremplace.get_Range("A1", "W" + getRowsLimit(xlWorkSheetremplace));
rangeremplace.Cells.Replace(remplacehisto8, "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, false, Type.Missing, false, false);//NB remplacehisto8 il faut ameliorer pour adapder tous les cas
////////delete col8000 "-2"//////////////////////////////////////////////////
object[,] values8000 = (object[, ])rangeremplace.Value2;
for (int rowdel = 1; rowdel <= rangeremplace.Rows.Count; rowdel++)
{
string valuedel = Convert.ToString(values8000[rowdel, col8000]);
if (Regex.Equals(valuedel, "-2"))
{
Excel.Range rangeDely = xlWorkSheetremplace.Cells[rowdel, col8000] as Excel.Range;
rangeDely.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
rangeremplace = xlWorkSheetremplace.UsedRange;
values8000 = (object[, ])rangeremplace.Value2;
rowdel--;
}
}
///////////////row hide "-5"////////////////////////////////////////////////
for (int rowhide = 1; rowhide <= rangeremplace.Rows.Count; rowhide++)
{
string valuedel = Convert.ToString(values8000[rowhide, col8000]);
if (Regex.Equals(valuedel, "-5"))
{
Excel.Range rangeDely = xlWorkSheetremplace.Cells[rowhide, col8000] as Excel.Range;
rangeDely.EntireRow.Hidden = true;
}
}
///////////////row supprimer "-6"////////////////////////////////////////////////
for (int rowhide = 1; rowhide <= rangeremplace.Rows.Count; rowhide++)
{
string valuedel = Convert.ToString(values8000[rowhide, col8000]);
if (Regex.Equals(valuedel, "-6"))
{
Excel.Range rangeDely = xlWorkSheetremplace.Cells[rowhide, col8000] as Excel.Range;
rangeDely.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
rangeremplace = xlWorkSheetremplace.UsedRange;
values8000 = (object[, ])rangeremplace.Value2;
rowhide--;
}
}
///////////////Hide -1 pour col 83000/////////////////////////////////////////////
//for (int rowhide = 1; rowhide <= rangeremplace.Rows.Count; rowhide++)
//{
// string valuedel = Convert.ToString(values8000[rowhide, col83000]);
// if (Regex.Equals(valuedel, "-1"))
// {
// Excel.Range rangeDely = xlWorkSheetremplace.Cells[rowhide, col83000] as Excel.Range;
// rangeDely.EntireRow.Hidden = true;
// }
//}
/////////////////////////////////////////////////////////////////////////////////
object[,] valuesNX = (object[, ])rangeremplace.Value2;
//string valueNX = Convert.ToString(valuesNX[row, col]);
for (int row3000 = 1; row3000 <= rangeremplace.Rows.Count; row3000++)
{
Excel.Range rangeprey = xlWorkSheetremplace.Cells[row3000, col3000] as Excel.Range;
if (Regex.Equals(Convert.ToString(valuesNX[row3000, col8000]), "-3"))
{
rangeprey.Locked = false;
rangeprey.FormulaHidden = false;
}
if (Regex.Equals(Convert.ToString(valuesNX[row3000, col8000]), "-4"))
{
rangeprey.Value2 = 0;
rangeprey.Locked = true;
rangeprey.FormulaHidden = true;
}
Excel.Range rangeDely = xlWorkSheetremplace.Cells[row3000, col3000] as Excel.Range;
if (rangeDely.Locked.ToString() != "True" && Convert.ToString(valuesNX[row3000, col8000]) != "-7")//-7 non zero
{
rangeDely.Value2 = 0;
}
}
for (int row4000 = 1; row4000 <= rangeremplace.Rows.Count; row4000++)
{
Excel.Range rangeprey = xlWorkSheetremplace.Cells[row4000, col4000] as Excel.Range;
if (Regex.Equals(Convert.ToString(valuesNX[row4000, col8000]), "-3"))
{
rangeprey.Locked = false;
rangeprey.FormulaHidden = false;
}
if (Regex.Equals(Convert.ToString(valuesNX[row4000, col8000]), "-4"))
{
rangeprey.Value2 = 0;
rangeprey.Locked = true;
rangeprey.FormulaHidden = true;
}
Excel.Range rangeDely = xlWorkSheetremplace.Cells[row4000, col4000] as Excel.Range;
if (rangeDely.Locked.ToString() != "True" && Convert.ToString(valuesNX[row4000, col8000]) != "-7")//-7 non zero
{
rangeDely.Value2 = 0;
}
}
for (int row5000 = 1; row5000 <= rangeremplace.Rows.Count; row5000++)
{
Excel.Range rangeprey = xlWorkSheetremplace.Cells[row5000, col5000] as Excel.Range;
if (Regex.Equals(Convert.ToString(valuesNX[row5000, col8000]), "-3"))
{
rangeprey.Locked = false;
rangeprey.FormulaHidden = false;
}
if (Regex.Equals(Convert.ToString(valuesNX[row5000, col8000]), "-4"))
{
rangeprey.Value2 = 0;
rangeprey.Locked = true;
rangeprey.FormulaHidden = true;
}
Excel.Range rangeDely = xlWorkSheetremplace.Cells[row5000, col5000] as Excel.Range;
if (rangeDely.Locked.ToString() != "True" && Convert.ToString(valuesNX[row5000, col8000]) != "-7")//-7 non zero
{
rangeDely.Value2 = 0;
}
}
int countr = xlWorkSheetremplace.UsedRange.Rows.Count;
int countc = xlWorkSheetremplace.UsedRange.Columns.Count;
object[,] valuesx = (object[, ])xlWorkSheetremplace.UsedRange.Value2;
Excel.Range rangeDeletex = xlWorkSheetremplace.UsedRange.get_Range("N1", xlWorkSheetremplace.Cells[1, xlWorkSheetremplace.UsedRange.Columns.Count]) as Excel.Range;
Excel.Range rangeDelete2 = xlWorkSheetremplace.get_Range(xlWorkSheetremplace.Cells[xlWorkSheetremplace.UsedRange.Rows.Count, 1], xlWorkSheetremplace.Cells[xlWorkSheetremplace.UsedRange.Rows.Count, 1]);
rangeDeletex.EntireColumn.Hidden = true;
rangeDelete2.EntireRow.Hidden = true;
////////////////////////////////////////////////////////////////////////////
xlApp.ActiveWindow.SplitRow = 0;
xlApp.ActiveWindow.SplitColumn = 0;
xlWorkBookremplace.Save();
xlWorkBookremplace.Close(true, misValue, misValue);
rangedelete.Copy(misValue);
rangedelete.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
range = xlWorkSheet.UsedRange;
values = (object[, ])range.Value2;
row = 25;//important remise le ligne commencer apres action delete 1:)25ligne
xlWorkSheet.Activate();
fileflag++;
}
}
xlApp.Quit();
MessageBox.Show("jobs done");
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
// заполнение массива Item.bmd d Excel
unsafe private void CreateExcelItem()
{
// Создаем документ с 16 страницами
excelapp = new Excel.Application();
//excelapp.Visible=true;
excelapp.SheetsInNewWorkbook = 1;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Add(Type.Missing);
String[] SheetsName = new String[] { "Sword", "Axe", "MaceScepter", "Spear", "BowCrossbow", "Staff", "Shield", "Helm", "Armor", "Pants", "Gloves", "Boots", "Accessories", "Misc1", "Misc2", "Scrolls" };
// получаем страницы книги
excelsheets = excelappworkbook.Worksheets;
// определяем имена страницам и переходим на страницу
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1);
excelworksheet.Name = SheetsName[0];
excelworksheet.Activate();
excelworksheet.Application.ActiveWindow.SplitColumn = 3;
excelworksheet.Application.ActiveWindow.SplitRow = 2;
excelworksheet.Application.ActiveWindow.FreezePanes = true;
// заполнение Index (0.1.2.3...)
excelcells = excelworksheet.get_Range("B3", Type.Missing);
excelcells.Value2 = 0;
excelcells = excelworksheet.get_Range("B4", Type.Missing);
excelcells.Value2 = 1;
excelcells = excelworksheet.get_Range("B3", "B4");
Excel.Range dest = excelworksheet.get_Range("B3", "B514");
excelcells.AutoFill(dest, Excel.XlAutoFillType.xlFillDefault);
// сворачиваем для увеличения скорости
excelworksheet.Application.WindowState = Excel.XlWindowState.xlMinimized;
excelworksheet.Application.Visible = false;
// оцентровываем первую строку
excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// зажирняем и оцентровываем вторую строку
excelcells = (Excel.Range)excelworksheet.Rows["2", Type.Missing];
excelcells.Font.Bold = true;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// устанавливаем размер колонок
excelcells = (Excel.Range)excelworksheet.Columns["A", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["B", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
excelcells.ColumnWidth = 30;
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = (Excel.Range)excelworksheet.Columns[ColumnTempName[j + 3], Type.Missing];
excelcells.ColumnWidth = MyItemColumns[j].Width;
}
// заполняем первую строку границами как называется не помню
excelcells = excelworksheet.get_Range("C1", Type.Missing);
excelcells.Value2 = "Char[30]";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].ColSize;
excelcells.Activate();
}
// заполняем вторую строку названиями
excelcells = excelworksheet.get_Range("A2", Type.Missing);
excelcells.Value2 = "Type";
excelcells = excelworksheet.get_Range("B2", Type.Missing);
excelcells.Value2 = "Index";
excelcells = excelworksheet.get_Range("C2", Type.Missing);
excelcells.Value2 = "Item Name";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].Name;
excelcells.Activate();
}
// обнуляем все ячейки кроме названия
excelcells = excelworksheet.get_Range("D3", "AR514");
excelcells.Value2 = 0;
// number format 12 232 232 для zen
excelcells = excelworksheet.get_Range("AB3", "AB514");
excelcells.NumberFormat = "# ##0";
// копируем листы
for (int i = 0; i < ITEM_MAX_TYPES - 1; i++)
{
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Copy(Type.Missing, excelworksheet);
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 2);
excelworksheet.Name = SheetsName[i + 1];
}
// заполняем ячейки
for (int i = 0; i < ITEM_MAX_TYPES; i++)
{
// выделяем нужный лист
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Activate();
// заполняем тип вещей
excelcells = excelworksheet.get_Range("A3", "A514");
excelcells.Value2 = i;
progressBar3.Value = i;
// поехали по строкам
for (int j = 0; j < ITEM_MAX_IN_TYPE; j++)
{
progressBar2.Value = j;
// заполняем имя
if (Items[i, j].ItemName[0] != '\0')
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, 3];
excelcells.Value2 = Items[i, j].ItemName;
excelcells.Select();
}
// заполняем остальные ячейки
fixed(Int64 *buff = Items[i, j].Numbers)
{
for (int k = 0; k < MyItemColumns.Length; k++)
{
if (buff != null && *(buff + k) != 0.0f)
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, k + 4];
excelcells.Value2 = *(buff + k);
}
}
}
}
}
// показываем готовый файл
excelapp.Visible = true;
// перепрыгиваем на 1 лист
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1);
excelworksheet.Activate();
progressBar2.Value = 0;
progressBar3.Value = 0;
MessageBox.Show("All Done!");
}
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;
}
}
//todo: Write data to DataBase
internal static void ImportToBase()
{
// If the proccess already started then just exit
if (inProgress)
{
return;
}
InWork(true);
// List of distances. All new created distances and their link will be added here
lDistances = База.DbList;
//try-catch. Get data from list. If there is some error then show alert msg to user
try
{
GetData();
}
catch (Exception)
{
throw;
}
//convert gotten data to models. If there is some error then show alert msg to user
try
{
ConvertDataToModels();
//if entity imported
База.IncrementEntriesCount(nPersons);
//methods finished successfuly
//AlertBoxes.AlertMsg("ГОТОВО!");
}
catch (GroupFullException e)
//If group is already full but user wants add new person
{
AlertBoxes.GroupFullAlert(e.GroupIndexAmount);
}
catch (InvalidFieldTypeException e)
//If field type is incorrect. For example, in the field "group index" is written not numbers
{
//get the cell where the field is incorrect
ExcelInterop.Range rng = oSheet.Range[sDataCellStart].Offset[e.IRow, e.ICol];
//select this cell
rng.Activate();
//show alert message to user
string cellName = "[строка - " + rng.Row + ", столбец - " + rng.Column + "]";
AlertBoxes.FieldTypeAlert(requiredType: e.RequiredType, cell: cellName);
}
catch (GroupNotFullException e)
//If some group is not full
{
AlertBoxes.GroupNotFullAlert(e.StructGroupIndex.GroupIndex, e.StructGroupIndex.Amnt);
}
catch (GroupAlreadyExistsException e)
{
AlertBoxes.GroupAlreadyExistsAlert(e.Struc, e.Group);
}
InWork(false);
}
private void btnAccept_Click(object sender, EventArgs e)
{
svdReportStock.FileName = "Concentrado_" + DateTime.Now.ToString("ddMMyyyy_hhmmss") + ".xlsx";
if (svdReportStock.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
string type = null;
if (this.cmbType.SelectedIndex > 0)
{
type = this.cmbType.Text;
}
var lConcentrated = new posb.Reports().Concentrated(type, this.dtpDate1.Value, this.dtpDate2.Value);
if (lConcentrated.Count < 1)
{
this.Alert("No se encontraron registros con las fechas indicadas");
return;
}
int index = 3;
Microsoft.Office.Interop.Excel.Application xlApp = null;
Workbook xlWorkBook = null;
Worksheet xlWorkSheetItems = null;
var cc = new ColorConverter();
object misValue = System.Reflection.Missing.Value;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
xlApp.EnableEvents = false;
xlWorkBook = xlApp.Workbooks.Open(this.GetPath() + "\\Templates\\" + this.AppSet <string>("ConcentratedReport"), Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
//Agrega la hoja de items
xlWorkSheetItems = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (var concentrated in lConcentrated)
{
xlWorkSheetItems.Cells[index, 1] = concentrated.Type;
(xlWorkSheetItems.Cells[index, 2] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 2] = concentrated.Id.ToString().PadLeft(10, '0');
(xlWorkSheetItems.Cells[index, 3] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 3] = concentrated.Name;
(xlWorkSheetItems.Cells[index, 4] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 4] = concentrated.Description;
(xlWorkSheetItems.Cells[index, 5] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "$###,##";
xlWorkSheetItems.Cells[index, 5] = concentrated.Amount;
(xlWorkSheetItems.Cells[index, 6] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "@";
xlWorkSheetItems.Cells[index, 6] = concentrated.CreatedDate.Value.ToString("dd/MM/yyyy");
index++;
}
xlWorkSheetItems.Cells[index + 2, 4].Font.Size = 13;
xlWorkSheetItems.Cells[index + 2, 4].Font.Bold = true;
xlWorkSheetItems.Cells[index + 2, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
xlWorkSheetItems.Cells[index + 2, 4] = "Total:";
(xlWorkSheetItems.Cells[index + 2, 5] as Microsoft.Office.Interop.Excel.Range).NumberFormat = "$###,##";
xlWorkSheetItems.Cells[index + 2, 5].Font.Size = 14;
xlWorkSheetItems.Cells[index + 2, 5].Font.Bold = true;
xlWorkSheetItems.Cells[index + 2, 5].Formula = string.Format("=SUBTOTAL(9,E2:E{0})", index);
//Mantiene el encabezado fijo
xlWorkSheetItems.Application.ActiveWindow.SplitRow = 2;
xlWorkSheetItems.Application.ActiveWindow.FreezePanes = true;
//Agrega autofiltros
Microsoft.Office.Interop.Excel.Range firstRow = (Microsoft.Office.Interop.Excel.Range)xlWorkSheetItems.Rows[2];
firstRow.Activate();
firstRow.Select();
firstRow.AutoFilter(2,
Type.Missing,
Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
xlWorkSheetItems.Cells[2, 1].Select();
//Ajusta el ancho de las columnas a su contenido
Microsoft.Office.Interop.Excel.Range aRange = xlWorkSheetItems.get_Range("A1", "ZZ1000000");
aRange.EntireColumn.AutoFit();
xlApp.EnableEvents = true;
xlWorkBook.SaveAs(svdReportStock.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, false);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Application.Quit();
xlApp.Quit();
if (this.Confirm("¿Deseas abrir el reporte?"))
{
Process.Start(svdReportStock.FileName);
}
}
catch (Exception ex)
{
this.Alert("Error: " + ex.Message);
}
finally
{
this.ReleasingObjects(xlWorkSheetItems, xlWorkBook, xlApp);
}
}
}
private void LoadExcelFileData()
{
#if EXCEL2003
try
{
this.m_table.Rows.Clear();
this.ws = (Microsoft.Office.Interop.Excel.Worksheet) this.wkb.Sheets[this.cmb_worksheets.SelectedIndex + 1];
this.lb_activesheet.Text = ws.Name;
string rng_des_from = this.tb_descol.Text.Trim() + this.tb_rowfrom.Text.Trim();
string rng_des_to = this.tb_descol.Text.Trim() + this.tb_rowto.Text.Trim();
string rng_disp_from = this.tb_disp_col.Text.Trim() + this.tb_rowfrom.Text.Trim();
string rng_disp_to = this.tb_disp_col.Text.Trim() + this.tb_rowto.Text.Trim();
try
{
this.range_codes = this.ws.get_Range(rng_disp_from, rng_disp_to);
}
catch
{
throw new Exception("Invalid display code range");
}
try
{
this.range_descr = this.ws.get_Range(rng_des_from, rng_des_to);
}
catch
{
throw new Exception("Invalid description range");
}
this.range_descr.Activate();
System.Array ar = (System.Array)range_descr.Cells.Value2;
for (int i = 0; i < ar.GetLength(0); i++)
{
DataRow dr = this.m_table.NewRow();
dr["ObjectDescription"] = ar.GetValue(i + 1, 1);
this.m_table.Rows.Add(dr);
}
range_codes.Activate();
ar = (System.Array)range_codes.Cells.Value2;
for (int i = 0; i < ar.GetLength(0); i++)
{
DataRow dr = this.m_table.Rows[i];
dr["DisplayCode"] = ar.GetValue(i + 1, 1);
}
// this.dataGridView1.DataSource = this.m_table;
this.dataGridView1.Update();
}
catch (Exception e2)
{
MessageBox.Show(e2.Message);
this.Hide();
this.Dispose();
}
#endif
}
private unsafe void CreateExcelItem()
{
// Создаем документ с 16 страницами
excelapp = new Excel.Application();
//excelapp.Visible=true;
excelapp.SheetsInNewWorkbook=1;
Excel.Workbook excelappworkbook = excelapp.Workbooks.Add(Type.Missing);
String[] SheetsName = new String[16] { "Sword", "Axe", "MaceScepter", "Spear", "BowCrossbow", "Staff", "Shield", "Helm", "Armor", "Pants", "Gloves", "Boots", "Accessories", "Misc1", "Misc2", "Scrolls" };
excelsheets = excelappworkbook.Worksheets;
// определяем имена страницам и переходим на страницу
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1);
excelworksheet.Name = SheetsName[0];
excelworksheet.Activate();
excelworksheet.Application.ActiveWindow.SplitColumn = 3;
excelworksheet.Application.ActiveWindow.SplitRow = 2;
excelworksheet.Application.ActiveWindow.FreezePanes = true;
// заполнение Index (0.1.2.3...)
excelcells = excelworksheet.get_Range("B3", Type.Missing);
excelcells.Value2 = 0;
excelcells = excelworksheet.get_Range("B4", Type.Missing);
excelcells.Value2 = 1;
excelcells = excelworksheet.get_Range("B3", "B4");
Excel.Range dest = excelworksheet.get_Range("B3", "B514");
excelcells.AutoFill(dest, Excel.XlAutoFillType.xlFillDefault);
// сворачиваем для увеличения скорости
excelworksheet.Application.WindowState = Excel.XlWindowState.xlMinimized;
excelworksheet.Application.Visible = false;
// оцентровываем первую строку
excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// зажирняем и оцентровываем вторую строку
excelcells = (Excel.Range)excelworksheet.Rows["2", Type.Missing];
excelcells.Font.Bold = true;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
// устанавливаем размер колонок
excelcells = (Excel.Range)excelworksheet.Columns["A", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["B", Type.Missing];
excelcells.ColumnWidth = 5;
excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
excelcells.ColumnWidth = 30;
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = (Excel.Range)excelworksheet.Columns[ColumnTempName[j + 3], Type.Missing];
excelcells.ColumnWidth = MyItemColumns[j].Width;
}
// заполняем первую строку границами как называется не помню
excelcells = excelworksheet.get_Range("C1", Type.Missing);
excelcells.Value2 = "Char[30]";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].ColSize;
excelcells.Activate();
}
// заполняем вторую строку названиями
excelcells = excelworksheet.get_Range("A2", Type.Missing);
excelcells.Value2 = "Type";
excelcells = excelworksheet.get_Range("B2", Type.Missing);
excelcells.Value2 = "Index";
excelcells = excelworksheet.get_Range("C2", Type.Missing);
excelcells.Value2 = "Item Name";
excelcells.Activate();
for (int j = 0; j < MyItemColumns.Length; j++)
{
excelcells = excelapp.ActiveCell.get_Offset(0, 1);
excelcells.Value2 = MyItemColumns[j].Name;
excelcells.Activate();
}
// обнуляем все ячейки кроме названия
excelcells = excelworksheet.get_Range("D3", "AR514");
excelcells.Value2 = 0;
// number format 12 232 232 для zen
excelcells = excelworksheet.get_Range("AB3", "AB514");
excelcells.NumberFormat = "# ##0";
// копируем листы
for (int i = 0; i < 15; i++)
{
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Copy(Type.Missing, excelworksheet);
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 2);
excelworksheet.Name = SheetsName[i + 1];
}
// заполняем ячейки
for (int i = 0; i < 16; i++)
{
// выделяем нужный лист
excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
excelworksheet.Activate();
excelcells = excelworksheet.get_Range("A3", "A514");
excelcells.Value2 = i;
progressBar3.Value = i;
// поехали по строкам
for (int j = 0; j < 512; j++)
{
progressBar2.Value = j;
if (Items[i,j].ItemName[0] != '\0')
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, 3];
excelcells.Value2 = Items[i, j].ItemName;
excelcells.Select();
}
fixed (Int64* buff = Items[i, j].Numbers)
{
for (int k = 0; k < MyItemColumns.Length; k++)
{
if (buff != null && *(buff + k) != 0.0f)
{
excelcells = (Excel.Range)excelworksheet.Cells[j + 3, k + 4];
excelcells.Value2 = *(buff + k);
}
}
}
}
}
// показываем готовый файл
excelapp.Visible = true;
progressBar2.Value = 0;
progressBar3.Value = 0;
MessageBox.Show("All Done!");
}
