/// <summary>
/// 复制当前Sheet到指定的sheet之后
/// </summary>
/// <param name="iIndex">定位的索引</param>
/// <param name="bAfter">在此之后</param>
public void CopySheet(int iIndex, bool bAfter)
{
if (bAfter)
{
xSheet.Copy(Missing.Value, xWorkBook.Worksheets[iIndex]);
}
else
{
xSheet.Copy(xWorkBook.Worksheets[iIndex], Missing.Value);
}
}
public void Run()
{
string template = "E:/Dev/OfficeAddInLearning/PowerPointAddInLearning/embeddedExcel.xlsx";
string tempFolderPath = "E:/Dev/OfficeAddInLearning/PowerPointAddInLearning/";
var missing = Type.Missing;
//Load Excel
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(template);
string calendar1 = "Calendar1";
string calendar2 = "Calendar2";
string calendar3 = "Calendar3";
string calendar4 = "Calendar4";
string calendar1Area = "";
string calendar2Area = "";
string calendar3Area = "";
string calendar4Area = "";
foreach (Excel.Name name in xlWorkbook.Names)
{
if (name.NameLocal.Contains(calendar1))
{
calendar1Area = name.RefersToLocal;
}
else if (name.NameLocal.Contains(calendar2))
{
calendar2Area = name.RefersToLocal;
}
else if (name.NameLocal.Contains(calendar3))
{
calendar3Area = name.RefersToLocal;
}
else if (name.NameLocal.Contains(calendar4))
{
calendar4Area = name.RefersToLocal;
}
// Do something to theRange
}
var calendar1Array = calendar1Area.Substring(1, calendar1Area.Length - 1).Split('!');
Excel.Range rangeCal1 = xlWorkbook.Worksheets[calendar1Array[0]].Range(calendar1Array[1], missing);
var calendar2Array = calendar2Area.Substring(1, calendar2Area.Length - 1).Split('!');
Excel.Range rangeCal2 = xlWorkbook.Worksheets[calendar2Array[0]].Range(calendar2Array[1], missing);
var calendar3Array = calendar3Area.Substring(1, calendar3Area.Length - 1).Split('!');
Excel.Range rangeCal3 = xlWorkbook.Worksheets[calendar3Array[0]].Range(calendar3Array[1], missing);
var calendar4Array = calendar4Area.Substring(1, calendar4Area.Length - 1).Split('!');
Excel.Range rangeCal4 = xlWorkbook.Worksheets[calendar4Array[0]].Range(calendar4Array[1], missing);
Excel.Application newXlAppCal1 = new Excel.Application();
Excel.Workbook xlWBCal1 = newXlAppCal1.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet wsCal1 = xlWBCal1.Worksheets[1];
wsCal1.Name = calendar1Array[0];
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//xlWorkbook.Worksheets[calendar1Array[0]]
//Excel.Range rangeCal1Dest = xlWBCal1.Worksheets[calendar1Array[0]].Range(calendar1Array[1], missing);
//rangeCal1.Copy(rangeCal1Dest);
Excel.Application xlApp1 = new Excel.Application();
Excel.Workbook wb1 = xlApp1.Workbooks.Open(tempFolderPath + "Template4.xlsx");//Excel.Workbooks.Open(tempFolderPath + "Template4.xlsx", missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet wx1 = wb1.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
wx1.Copy(xlWorkbook.Worksheets[calendar1Array[0]]);
wb1.Save();
xlApp1.Quit();
//string tempExel1 = tempFolderPath + calendar1Array[0].ToString() + ".xlsx";
//xlWBCal1.SaveAs(tempExel1,
// Excel.XlFileFormat.xlOpenXMLWorkbook,
// missing,
// missing,
// missing,
// missing,
// Excel.XlSaveAsAccessMode.xlExclusive,
// missing,
// missing,
// missing,
// missing,
// missing);
xlWBCal1.Close();
newXlAppCal1.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wsCal1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWBCal1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(newXlAppCal1);
//Excel.Application newXlAppCal2 = new Microsoft.Office.Interop.Excel.Application();
//Excel.Workbook xlWBCal2 = newXlAppCal2.Workbooks.Add(missing);
//var wsCal2 = xlWBCal2.Worksheets.get_Item(1);
//wsCal2.Name = calendar2Array[0];
//Excel.Range rangeCal2Dest = xlWBCal2.Worksheets[calendar2Array[0]].Range(calendar2Array[1], missing);
//rangeCal2.Copy(rangeCal2Dest);
//xlWBCal2.SaveAs(tempFolderPath + calendar2Array[0] + ".xlsx");
//Excel.Application newXlAppCal3 = new Microsoft.Office.Interop.Excel.Application();
//Excel.Workbook xlWBCal3 = newXlAppCal3.Workbooks.Add(missing);
//var wsCal3 = xlWBCal3.Worksheets.get_Item(1);
//wsCal3.Name = calendar3Array[0];
//Excel.Range rangeCal3Dest = xlWBCal3.Worksheets[calendar3Array[0]].Range(calendar3Array[1], missing);
//rangeCal3.Copy(rangeCal3Dest);
//xlWBCal3.SaveAs(tempFolderPath + calendar3Array[0] + ".xlsx");
//Excel.Application newXlAppCal4 = new Microsoft.Office.Interop.Excel.Application();
//Excel.Workbook xlWBCal4 = newXlAppCal4.Workbooks.Add(missing);
//var wsCal4 = xlWBCal4.Worksheets.get_Item(1);
//wsCal4.Name = calendar4Array[0];
//Excel.Range rangeCal4Dest = xlWBCal4.Worksheets[calendar4Array[0]].Range(calendar4Array[1], missing);
//rangeCal4.Copy(rangeCal4Dest);
//xlWBCal4.SaveAs(tempFolderPath + calendar4Array[0] + ".xlsx");
}
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);
}
