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!"); }