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;
}
