public bool DeleteExcelSheet(string pExcelPath, string pSheetName, out string pDeleteSheet) { try { object objOpt = Missing.Value; //打开一个Excel应用 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); if (app == null) { throw new Exception("打开Excel应用时发生错误!"); } app.Visible = false; Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks; Microsoft.Office.Interop.Excel._Workbook wb = wbs.Open(pExcelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wb.EnableAutoRecover = false; Microsoft.Office.Interop.Excel.Sheets shs = wb.Sheets; Microsoft.Office.Interop.Excel._Worksheet sh = (Microsoft.Office.Interop.Excel._Worksheet)shs.get_Item(pSheetName); app.DisplayAlerts = false; sh.Delete(); wb.Save(); pDeleteSheet = string.Empty; return(true); } catch (Exception vErr) { pDeleteSheet = vErr.Message; return(false); } finally { KillProcess(); } }
public void SpremanjeFaktura() { string OtvorenaFaktura = Application.StartupPath + "\\CMR\\CMR.xlsx"; //unos podataka ExcelObj = new Microsoft.Office.Interop.Excel.Application(); theWorkbook = ExcelObj.Workbooks.Open(OtvorenaFaktura, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); sheets = theWorkbook.Worksheets; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1); excelApp.Workbooks.Open(OtvorenaFaktura); var BrojOtpremnice = (worksheet.Cells[1, 10] as Microsoft.Office.Interop.Excel.Range).Value; int NoviBrojOtpremnice = Convert.ToInt16(BrojOtpremnice) + 1; excelApp.Cells[1, 10] = NoviBrojOtpremnice; excelApp.ActiveWorkbook.Save(); theWorkbook.Close(0); excelApp.Quit(); foreach (Process proc in Process.GetProcessesByName("EXCEL")) { proc.Kill(); } Process.Start(OtvorenaFaktura); }
private void OnPrintDocument(object sender, PrintPageEventArgs e) { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(pathPlantillaReporte, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad); Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(pathsDictionary["plantillaHallazgos"]); Microsoft.Office.Interop.Excel.Sheets sheets = xlWorkBook.Worksheets; Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.ActiveSheet; xlApp.Visible = true; object misValue = System.Reflection.Missing.Value; xlApp.Sheets.PrintPreview(true); xlApp.Sheets._PrintOut(1, true, 1, false, true, true, false);//How can work this?? }
public static void Test() { Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application(); Excel._Workbook myworkbook = myExcel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Sheets mysheets = myworkbook.Worksheets; for (int i = 1; i <= 12; i++) { myworkbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); Excel.Worksheet oSheet = (Excel.Worksheet)mysheets.get_Item(i); oSheet.Name = i.ToString(); oSheet.Cells[1, 1] = "Date" + i.ToString(); } myExcel.Visible = true; string fileName = GetFileName(); myworkbook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, Missing.Value, Missing.Value, Missing.Value); myworkbook.Saved = true; myworkbook.Close(false, Type.Missing, Type.Missing); }
public void append(string filePath, int index2, User user) { int index = 3; xlApp = new Microsoft.Office.Interop.Excel.Application(); xlApp.Visible = true; xlWorkBook = xlApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); xlBigSheet = xlWorkBook.Worksheets; string x = "Sheet1"; xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBigSheet.get_Item(1); xlWorksheet.Cells[index, 1] = user.name; xlWorksheet.Cells[index, 2] = user.tname; xlWorksheet.Cells[index, 3] = user.login; xlWorksheet.Cells[index, 4] = user.pass; xlWorksheet.Cells[index, 5] = user.question; xlWorksheet.Cells[index, 6] = user.answer; xlWorksheet.Cells[index, 7] = user.date_reg; xlWorksheet.Cells[index, 8] = user.prog_id; xlWorksheet.Cells[index, 9] = user.prog_pass; xlWorksheet.Cells[index, 10] = user.callback_url; xlWorkBook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(misValue, misValue, misValue); xlWorkBook = null; xlApp.Quit(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); //Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); //if (xlApp == null) //{ // throw new Exception("Excel is not properly installed!!"); //} //xlApp = new Excel.Application(); //xlWorkbook = xlApp.Workbooks.Open(filePath); //xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1); //xlRange = xlWorksheet.UsedRange; //if (xlWorksheet.Cells[index, 3].Value2.ToString().Trim() != "") // index++; //xlWorksheet.Cells[index, 1] = user.name; //xlWorksheet.Cells[index, 2] = user.tname; //xlWorksheet.Cells[index, 3] = user.login; //xlWorksheet.Cells[index, 4] = user.pass; //xlWorksheet.Cells[index, 5] = user.question; //xlWorksheet.Cells[index, 6] = user.answer; //xlWorksheet.Cells[index, 7] = user.date_reg; //xlWorksheet.Cells[index, 8] = user.prog_id; //xlWorksheet.Cells[index, 9] = user.prog_pass; //xlWorksheet.Cells[index, 10] = user.callback_url; //xlWorkbook.Save(); //xlWorkbook.Close(true); }
public static void ReadExistingExcel(dynamic data) { string path = @"C:\Users\Meir\Downloads\ABISample\Copy of Boiler Batch OP-42 Form v01,2013 BLANK.XLSX"; oXL = new Microsoft.Office.Interop.Excel.Application(); //oXL.Visible = true; oXL.DisplayAlerts = false; mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //Get all the sheets in the workbook mWorkSheets = mWorkBook.Worksheets; //Get the existing sheets mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("OP-42"); mWSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("csv"); // mWSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("csv"); Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange; // Edit values for OP-42 mWSheet1.Cells[5, 20] = data.id.ToString(); mWSheet1.Cells[6, 21] = data.date.ToString(); mWSheet1.Cells[8, 7] = data.company.ToString(); mWSheet1.Cells[10, 7] = data.name.ToString(); mWSheet1.Cells[12, 6] = data.id.ToString(); mWSheet1.Cells[12, 9] = data.job.ToString(); mWSheet1.Cells[14, 7] = data.address.ToString(); //mWSheet1.Cells[15, 20] = data.number.ToString(); //mWSheet1.Cells[17, 21] = data.number.ToString(); mWSheet1.Cells[17, 10] = data.date.ToString(); mWSheet1.Cells[16, 5] = data.name_2.ToString(); mWSheet1.Cells[18, 10] = data.phone.ToString(); mWSheet1.Cells[18, 5] = data.email.ToString(); mWSheet1.Cells[30, 10] = data.date.ToString(); // Edit values for csv int colCount = range.Columns.Count; int rowCount = range.Rows.Count; // bottom data for (int index = 1; index < 10; index++) { mWSheet2.Cells[0 + index, 1] = data.boro.ToString(); mWSheet2.Cells[0 + index, 2] = data.device.ToString(); mWSheet2.Cells[0 + index, 3] = data.md.ToString(); mWSheet2.Cells[0 + index, 4] = data.serial.ToString(); mWSheet2.Cells[0 + index, 5] = data.house.ToString(); mWSheet2.Cells[0 + index, 6] = data.street.ToString(); mWSheet2.Cells[0 + index, 7] = data.block.ToString(); mWSheet2.Cells[0 + index, 8] = data.lot.ToString(); mWSheet2.Cells[0 + index, 9] = data.date.ToString(); mWSheet2.Cells[0 + index, 10] = data.j.ToString(); mWSheet2.Cells[0 + index, 11] = data.k.ToString(); mWSheet2.Cells[0 + index, 12] = data.l.ToString(); mWSheet2.Cells[0 + index, 13] = data.m.ToString(); mWSheet2.Cells[0 + index, 14] = data.n.ToString(); mWSheet2.Cells[0 + index, 15] = data.o.ToString(); mWSheet2.Cells[0 + index, 16] = data.p.ToString(); mWSheet2.Cells[0 + index, 17] = data.q.ToString(); mWSheet2.Cells[0 + index, 18] = data.r.ToString(); mWSheet2.Cells[0 + index, 19] = data.location.ToString(); mWSheet2.Cells[0 + index, 20] = data.t.ToString(); } SaveFileDialog saveFileDialog2 = new SaveFileDialog { Filter = "xlsx files|*.xlsx", DefaultExt = "xlsx", AddExtension = true }; saveFileDialog2.ShowDialog(); string savePath = saveFileDialog2.FileName; mWorkBook.SaveAs(savePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); mWorkBook.Close(true, "newABIExcel", false); mWSheet1 = null; mWorkBook = null; oXL.Quit(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); }
public static void AddDataToExcel(string SampleID, DateTime SampleDate, string PatientID, double Age, double[] BioData, int rowCount, string FilePath) { string path = FilePath; //xlApp = new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel.Workbook excelWorkbook = xlApp.Workbooks.Open(path); //Microsoft.Office.Interop.Excel._Worksheet sheet = excelWorkbook.Sheets[1]; //TrimRows(sheet); //var LastRow = sheet.UsedRange.Rows.Count; //LastRow = LastRow + sheet.UsedRange.Row - 1; //for (int i = 1; i <= LastRow; i++) //{ // //if (application.WorksheetFunction.CountA(sheet.Rows[i]) == 0) // // (sheet.Rows[i] as Microsoft.Office.Interop.Excel.Range).Delete(); //} oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = false; oXL.DisplayAlerts = false; mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); //Get all the sheets in the workbook mWorkSheets = mWorkBook.Worksheets; //Get the allready exists sheet mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("biomarkers + added parameters"); //TrimRows(mWSheet1); //Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange; //int colCount = range.Columns.Count; //int rowCount = range.Rows.Count; rowCount = rowCount + 1; mWSheet1.Cells[rowCount + 1, 1] = rowCount; //DigiWest ID mWSheet1.Cells[rowCount + 1, 2] = SampleID; //SampleID mWSheet1.Cells[rowCount + 1, 3] = ""; //Patient ID IOTA mWSheet1.Cells[rowCount + 1, 4] = ""; //IOTA Score mWSheet1.Cells[rowCount + 1, 5] = ""; //Certain or Uncertain mWSheet1.Cells[rowCount + 1, 6] = SampleDate; //SampleDate mWSheet1.Cells[rowCount + 1, 7] = ""; //DateOfBirth mWSheet1.Cells[rowCount + 1, 8] = Age; //Age mWSheet1.Cells[rowCount + 1, 9] = ""; //CA-125 mWSheet1.Cells[rowCount + 1, 10] = ""; //NeoOva Score mWSheet1.Cells[rowCount + 1, 11] = BioData[0]; //Neopro1 mWSheet1.Cells[rowCount + 1, 12] = BioData[1]; //Neopro2 mWSheet1.Cells[rowCount + 1, 13] = BioData[2]; //Neopro3 mWSheet1.Cells[rowCount + 1, 14] = BioData[3]; //Neopro4 mWSheet1.Cells[rowCount + 1, 15] = BioData[4]; //Neopro5 mWSheet1.Cells[rowCount + 1, 16] = BioData[5]; //Neopro6 mWSheet1.Cells[rowCount + 1, 17] = PatientID; //PatientID mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value); mWSheet1 = null; mWorkBook = null; oXL.Quit(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); }
internal static void exportarInventarioCompleto() { if ((classGeneradorDeReportes.pathPlantillaInventarios == "") || classGeneradorDeReportes.pathPlantillaInventarios == null) { classGeneradorDeReportes.getpaths(); } //Creamos un objeto misvalue para facilitar las configuraciones. object misValue = System.Reflection.Missing.Value; //Creamos nuestro DataTable DataTable tablaParaExportacion = new DataTable(); string selectInventario = "select " + "a.clave " + ", a.cliente " + ", a.material " + ", a.calibre " + ", a.color " + ", a.corte " + ", a.orientacion " + ", a.perfil " + ", a.frecuencia " + ", a.amplitud " + ", a.lubricante " + ", a.mazos " + ", a.kilos " + ", DATE_FORMAT(a.fechaDeFabricacion, '%d/%m/%Y') as fechaDeFabricacion " + ", a.lote " + ", a.fila " + ", b.nombre " + ", DATE_FORMAT(a.fecha_de_registro, '%d/%m/%Y') as fecha_de_registro " + ", DATE_FORMAT(a.fecha_de_modificacion, '%d/%m/%Y') as fecha_de_modificacion" + ", DATE_FORMAT(a.fecha_de_recepcion_de_material, '%d/%m/%Y') as fecha_de_recepcion_de_material " + ", a.diametro_mazo " + ", c.nombre as presentacion " + ", a.comentarios as Comentarios " + ", a.pedidopmm as Pedido " + "from lu_productos a " + "left join lu_almacenes b on a.id_almacen=b.id_almacen " + "left join cf_presentaciones c on a.id_presentacion = c.id_presentacion " + "where a.mazos<>0; "; //"inner join lu_almacenes b on a.id_almacen=b.id_almacen where a.mazos<>0; "; tablaParaExportacion = Database.runSelectQuery(selectInventario); //En caso de devolver una tabla nula, debemos construirla de nuevo para evitar errores. if (tablaParaExportacion == null) { tablaParaExportacion = new DataTable(); } //Creamos una nueva aplicación de excel. Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); //Abrimos la plantilla de reportes y creamos un nuevo workbook para mostrar ahí el reporte. Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(classGeneradorDeReportes.pathPlantillaInventarios); Microsoft.Office.Interop.Excel.Workbook workbookExportacion; //Obtenemos todas las hojas de la plantilla Microsoft.Office.Interop.Excel.Sheets sheets = xlWorkBook.Worksheets; //Obtenemos la primera hoja de la plantilla Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = xlApp.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; //Se copia la hoja actual y se coloca automáticamente en un nuevo workbook. xlWorkSheet.Copy(Type.Missing, Type.Missing); //Cerramos la plantilla original de excel. xlApp.DisplayAlerts = false; xlWorkBook.Save(); xlWorkBook.Close(0); //Comenzamos a trabajar con el workbook de exportación //Asignamos un identificador para nuestro workbook workbookExportacion = xlApp.ActiveWorkbook; Microsoft.Office.Interop.Excel.Sheets sheetsExportacion = workbookExportacion.Worksheets; Microsoft.Office.Interop.Excel.Worksheet sheetExportacion = xlApp.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; //Primeras 10 columnas de la tabla //Copiamos la tabla de exportación al portaPapeles ClassUtilidades.CopyDataTableToClipboard(tablaParaExportacion); Microsoft.Office.Interop.Excel.Range CR = sheetExportacion.Cells[2, 1] as Microsoft.Office.Interop.Excel.Range; CR.Select(); //string textos = Clipboard.GetText(); sheetExportacion.Paste(CR); Microsoft.Office.Interop.Excel.Range formatRange = sheetExportacion.get_Range("L1", "M1"); formatRange.EntireColumn.NumberFormat = "0.00"; Microsoft.Office.Interop.Excel.Range formatRange2 = sheetExportacion.get_Range("P1", "P1"); formatRange2.EntireColumn.NumberFormat = "0"; Microsoft.Office.Interop.Excel.Range formatRange3 = sheetExportacion.get_Range("N1", "N1"); formatRange3.EntireColumn.NumberFormat = "dd/mm/yyyy"; Microsoft.Office.Interop.Excel.Range formatRange4 = sheetExportacion.get_Range("R1", "T1"); formatRange4.EntireColumn.NumberFormat = "dd/mm/yyyy"; formatRange.NumberFormat = "dd/mm/yyyy"; //Iteramos para poner el formato correcto de número en las celdas. for (int x = 0; x < tablaParaExportacion.Rows.Count; x++) { DateTime fecha = DateTime.Parse(tablaParaExportacion.Rows[x]["fechaDeFabricacion"].ToString()); sheetExportacion.Cells[1 + (x + 1), 14] = fecha.ToOADate(); DateTime fecha_de_recepcion = DateTime.Parse(tablaParaExportacion.Rows[x]["fecha_de_recepcion_de_material"].ToString()); sheetExportacion.Cells[1 + (x + 1), 18] = fecha_de_recepcion.ToOADate(); DateTime fecha_de_registro = DateTime.Parse(tablaParaExportacion.Rows[x]["fecha_de_registro"].ToString()); sheetExportacion.Cells[1 + (x + 1), 19] = fecha_de_registro.ToOADate(); DateTime fecha_de_modificacion = DateTime.Parse(tablaParaExportacion.Rows[x]["fecha_de_modificacion"].ToString()); sheetExportacion.Cells[1 + (x + 1), 20] = fecha_de_modificacion.ToOADate(); sheetExportacion.Cells[2 + x, 12] = tablaParaExportacion.Rows[x]["mazos"].ToString(); sheetExportacion.Cells[2 + x, 13] = tablaParaExportacion.Rows[x]["kilos"].ToString(); sheetExportacion.Cells[2 + x, 16] = tablaParaExportacion.Rows[x]["fila"].ToString(); } //Últimas columnas de la tabla //Copiamos la tabla de exportación al portaPapeles /*classDataBaseManager.CopyDataTableToClipboard(tablaParaExportacion2); * CR = sheetExportacion.Cells[5, 11] as Microsoft.Office.Interop.Excel.Range; * CR.Select(); * sheetExportacion.Paste(CR, Clipboard.GetText()); */ int cantidadRegistros = tablaParaExportacion.Rows.Count; int ultimaCelda = (cantidadRegistros) + 4; // //Establecemos los márgenes para la impresión. sheetExportacion.PageSetup.PrintArea = "A1:M" + ultimaCelda; Microsoft.Office.Interop.Excel.Range aRange = sheetExportacion.get_Range("A5", "M" + ultimaCelda); aRange.Rows.AutoFit(); xlApp.DisplayAlerts = false; //Colocamos los bordes de las celdas sheetExportacion.Range[sheetExportacion.Cells[5, 1], sheetExportacion.Cells[ultimaCelda + 5, 13]].borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; sheetExportacion.Range[sheetExportacion.Cells[5, 1], sheetExportacion.Cells[ultimaCelda + 5, 13]].borders.Weight = 2d; string rutaPDF = System.Windows.Forms.Application.StartupPath + "\\ultimoReporte.pdf"; //MessageBox.Show("Guardado en " + rutaPDF); sheetExportacion.ExportAsFixedFormat( Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, rutaPDF, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, true, false, Type.Missing, Type.Missing, false); xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized; xlApp.Visible = true; //Cerramos el Excel para mostrar solo el PDF. //ClassUtilidades.matarProcesoDeExcel(xlApp); //Mostramos el PDF //ClassUtilidades.verPDF(rutaPDF); }
public void SpremanjeFaktura() { //unos podataka ExcelObj = new Microsoft.Office.Interop.Excel.Application(); excelApp = new Excel.Application(); theWorkbook = ExcelObj.Workbooks.Open(OtvorenaFaktura, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); sheets = theWorkbook.Worksheets; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1); int rowIndex, colIndex; excelApp.Workbooks.Open(OtvorenaFaktura); //DVO var qa = (worksheet.Cells[18, 6] as Microsoft.Office.Interop.Excel.Range).Value; string slovoqa = Convert.ToString(qa); if (slovoqa == "Datum valute:") { rowIndex = 17; colIndex = 8; excelApp.Cells[rowIndex, colIndex] = DatumDvo; } else rowIndex = 18; colIndex = 8; excelApp.Cells[rowIndex, colIndex] = DatumDvo; //valuta var qb = (worksheet.Cells[19, 6] as Microsoft.Office.Interop.Excel.Range).Value; string slovoqb = Convert.ToString(qb); if (slovoqb == "Vrijeme:") { rowIndex = 18; colIndex = 8; excelApp.Cells[rowIndex, colIndex] = DatumValuta; } else rowIndex = 19; colIndex = 8; excelApp.Cells[rowIndex, colIndex] = DatumValuta; //faktura var q = (worksheet.Cells[22, 6] as Microsoft.Office.Interop.Excel.Range).Value; string slovoq = Convert.ToString(q); if (slovoq == "OIB:" || slovoq == "oib:" || slovoq == "Oib:") { rowIndex = 21; colIndex = 7; excelApp.Cells[rowIndex, colIndex] = BrojFakture; } else rowIndex = 22; colIndex = 7; excelApp.Cells[rowIndex, colIndex] = BrojFakture; //cijena, količina, rabat i PDV for (int red = 26; red <= 31; red += 1) { var trazi = (worksheet.Cells[red, 6] as Microsoft.Office.Interop.Excel.Range).Value; string STRtrazi = Convert.ToString(trazi); float val; if (((float.TryParse(STRtrazi, out val))) && STRtrazi.Length != 0) { excelApp.Cells[red, 6] = CijenaFakture; excelApp.Cells[red, 5] = Kolicina; if (rabat != 0) { excelApp.Cells[red, 9] = Convert.ToDouble(rabat); excelApp.Cells[red, 10] = Convert.ToDouble(PDV); } else { excelApp.Cells[red, 9] = Convert.ToDouble(PDV); } break; } } //relacija string RedRelacija = File.ReadAllText(Application.StartupPath + "\\RedRelacija"); string StupacRelacija = File.ReadAllText(Application.StartupPath + "\\StupacRelacija"); excelApp.Cells[Convert.ToInt16(RedRelacija), Convert.ToInt16(StupacRelacija)] = Relacija; File.Delete(Application.StartupPath + "\\RedRelacija"); File.Delete(Application.StartupPath + "\\StupacRelacija"); //pozicija for (int stupac = 1; stupac <= 5; stupac++) { for (int red = 26; red <= 34; red++) { var trazi = (worksheet.Cells[red, stupac] as Microsoft.Office.Interop.Excel.Range).Value; string STRtrazi = Convert.ToString(trazi); Boolean equals = String.Equals(STRtrazi, "Pozicija:", StringComparison.OrdinalIgnoreCase); if (equals == true) { excelApp.Cells[red, stupac + 1] = Pozicija; //MessageBox.Show(red + " " + stupac); break; } } } var datum = Convert.ToDateTime(DatumDvo); int mjesec = datum.Month; int godina = datum.Year; FileInfo fInfo = new FileInfo(PutanjaOtvoreneFakture); string strFilePath = fInfo.DirectoryName; //plačeno,postojeća relacija DirectoryInfo dirPlaceno = new DirectoryInfo(strFilePath + "\\" + mjesec + "-" + godina + "\\PLAČENO\\"); string dir = Convert.ToString(dirPlaceno); //neplačeno,postojeća relacija DirectoryInfo dirNePlaceno = new DirectoryInfo(strFilePath + "\\" + mjesec + "-" + godina + "\\NEPLAČENO\\"); string dirNe = Convert.ToString(dirNePlaceno); //plačeno,nova relacija DirectoryInfo dirPlacenoNovo = new DirectoryInfo(PutanjaOtvoreneFakture + "\\" + Relacija+ "\\" + mjesec + "-" + godina + "\\PLAČENO\\"); string dirNovo1 = Convert.ToString(dirPlacenoNovo); string dirNovo = dirNovo1.Replace("\\\\", "\\"); //neplačeno,nova relacija DirectoryInfo dirNePlacenoNovo = new DirectoryInfo(PutanjaOtvoreneFakture + "\\" + Relacija + "\\" + mjesec + "-" + godina + "\\NEPLAČENO\\"); string dirNeNovo1 = Convert.ToString(dirNePlacenoNovo); string dirNeNovo = dirNeNovo1.Replace("\\\\", "\\"); string open; //nova ruta if (NovaRuta == true) { //stvori oba direktorija,PLAČENO i NEPLAČENO dirPlacenoNovo.Create(); dirNePlacenoNovo.Create(); if (Placeno == true) { excelApp.ActiveWorkbook.SaveCopyAs(dirNovo + BrojFakture + " " + DatumDvo + ".xlsx"); open = dirNovo + BrojFakture + " " + DatumDvo + ".xlsx"; } else { excelApp.ActiveWorkbook.SaveCopyAs(dirNeNovo + BrojFakture + " " + DatumDvo + ".xlsx"); open = dirNeNovo + BrojFakture + " " + DatumDvo + ".xlsx"; } } //normalna faktura,postojeća relacija else { //stvori oba direktorija,PLAČENO i NEPLAČENO dirNePlaceno.Create(); dirPlaceno.Create(); if (Placeno == true) { if (dirPlaceno.Exists) { excelApp.ActiveWorkbook.SaveCopyAs(dir + BrojFakture + " " + DatumDvo + ".xlsx"); open = dir + BrojFakture + " " + DatumDvo + ".xlsx"; } //Ako taj direktorij ne postoji->napravi ga else { excelApp.ActiveWorkbook.SaveCopyAs(dir + BrojFakture + " " + DatumDvo + ".xlsx"); open = dir + BrojFakture + " " + DatumDvo + ".xlsx"; } } else { if (dirNePlaceno.Exists) { excelApp.ActiveWorkbook.SaveCopyAs(dirNe + BrojFakture + " " + DatumDvo + ".xlsx"); open = dirNe + BrojFakture + " " + DatumDvo + ".xlsx"; } //Ako taj direktorij ne postoji->napravi ga else { excelApp.ActiveWorkbook.SaveCopyAs(dirNe + BrojFakture + " " + DatumDvo + ".xlsx"); open = dirNe + BrojFakture + " " + DatumDvo + ".xlsx"; } } } //Otvaranje spremljene fakture SpremljenaFaktura = open.Replace("\\", "\\\\"); SpremanjeZaSync(); Zatvaranje(); System.Diagnostics.Process.Start(SpremljenaFaktura); File.AppendAllText(Application.StartupPath + "\\Fakture\\data", Environment.NewLine + BrojFakture); }
private void imprimeDesdeExcel() { enImpresion = true; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(pathPlantillaReporte, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad); Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(pathsDictionary["plantillaHallazgos"]); Microsoft.Office.Interop.Excel.Sheets sheets = xlWorkBook.Worksheets; Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.ActiveSheet; xlApp.Visible = true; xlWorkBook.Saved = true; object misValue = System.Reflection.Missing.Value; //Modificamos los valores del excel xlWorkSheet.Cells[3, 3] = dateTimePickerFecha.Text; //Renglon 3 columna 7 xlWorkSheet.Cells[3, 7] = registroHistorial.turno; xlWorkSheet.Cells[4, 5] = registroHistorial.identificadoPor; xlWorkSheet.Cells[5, 3] = registroHistorial.cliente; xlWorkSheet.Cells[5, 8] = registroHistorial.pedido; xlWorkSheet.Cells[6, 5] = registroHistorial.responsable; xlWorkSheet.Cells[7, 4] = registroHistorial.supervisorEnTurno; xlWorkSheet.Cells[7, 8] = registroHistorial.operadorEnTurno; xlWorkSheet.Cells[8, 5] = registroHistorial.inspectorDeCalidadEnTurno; xlWorkSheet.Cells[8, 8] = registroHistorial.ayudanteEnTurno; xlWorkSheet.Cells[10, 3] = registroHistorial.material; xlWorkSheet.Cells[11, 3] = registroHistorial.calibre; xlWorkSheet.Cells[12, 3] = registroHistorial.color; xlWorkSheet.Cells[13, 3] = registroHistorial.perfil; xlWorkSheet.Cells[10, 7] = registroHistorial.corte; xlWorkSheet.Cells[11, 7] = registroHistorial.lotes; //xlWorkSheet.Rows("9:9").EntireRow.AutoFit xlWorkSheet.Cells[12, 7] = registroHistorial.cantidadInicial; xlWorkSheet.Cells[12, 9] = registroHistorial.unidadInicial; xlWorkSheet.Cells[13, 7] = registroHistorial.cantidadFinal; xlWorkSheet.Cells[13, 9] = registroHistorial.unidadFinal; xlWorkSheet.Cells[15, 5] = registroHistorial.disposicion; xlWorkSheet.Cells[17, 1] = registroHistorial.descripcion; xlWorkSheet.Cells[19, 1] = registroHistorial.inspeccionesRealizadas; xlWorkSheet.Cells[21, 1] = registroHistorial.probableCausa; xlWorkSheet.Cells[23, 1] = registroHistorial.observaciones; xlWorkSheet.Cells[25, 1] = registroHistorial.enterado; /* * int indice = 0; * for (int x = 0; x < arregloEnterado.Length; x++ ) * { * if (arregloEnterado[x].nombre == comboBoxIdentificadoPor.Text) * { * indice = x; * break; * } * } */ xlWorkSheet.Cells[26, 1] = arregloEnterado[comboBoxEnterado.SelectedIndex].puesto; xlWorkSheet.Cells[25, 8] = "Mauro Corona Martínez"; xlWorkSheet.Cells[26, 8] = "Jefe de Control de Calidad"; Excel.Range aRange = xlWorkSheet.get_Range("A11", "Z23"); aRange.Rows.AutoFit(); xlApp.DisplayAlerts = false; xlWorkBook.Save(); xlApp.Dialogs[Excel.XlBuiltInDialog.xlDialogPrintPreview].Show(); //xlWorkBook.PrintPreview(); //xlWorkBook.PrintPreview(true); //Cerramos la aplicación xlWorkBook.Close(false, misValue, misValue); xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); enImpresion = false; }