public void ExcelXLS(DirectoryInfo RutaExcel, DataTable Datos, string CadenaConexion) { ConexionBd conexion = new ConexionBd(); var NuevoArchivo = new FileInfo(RutaExcel + @"\xlExcel7Pass2.xls"); if (NuevoArchivo.Exists) { NuevoArchivo.Delete(); NuevoArchivo = new FileInfo(RutaExcel + @"\xlExcel7Pass2.xls"); } Datos.TableName = "Polizas"; int inColumn = 0, inRow = 0; System.Reflection.Missing Default = System.Reflection.Missing.Value; Excel.Application excelApp = new Excel.Application(); Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(1); //Create Excel WorkSheet Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default); excelWorkSheet.Name = "Poliza"; //Name worksheet //Write Column Name for (int i = 0; i < Datos.Columns.Count; i++) { excelWorkSheet.Cells[1, i + 1] = Datos.Columns[i].ColumnName; //.ToUpper(); } //Write Rows for (int m = 0; m < Datos.Rows.Count; m++) { for (int n = 0; n < Datos.Columns.Count; n++) { inColumn = n + 1; inRow = 2 + m; //1 + 2 + m; excelWorkSheet.Cells[inRow, inColumn] = Datos.Rows[m].ItemArray[n].ToString(); if (m % 2 == 0) { excelWorkSheet.get_Range("A" + inRow.ToString(), "W" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#DAA520"); } } } ////Excel Header //OfficeExcel.Range cellRang = excelWorkSheet.get_Range("A1", "O1"); //cellRang.Merge(false); //cellRang.Interior.Color = System.Drawing.Color.Blue; //cellRang.Font.Color = System.Drawing.Color.Black; //cellRang.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter; //cellRang.VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter; //cellRang.Font.Size = 16; //excelWorkSheet.Cells[1, 1] = "Greate Novels Of All Time"; //Style table column names Excel.Range cellRang = excelWorkSheet.get_Range("A1", "W1"); cellRang.Font.Bold = true; cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#00008B"); cellRang = excelWorkSheet.get_Range("X1", "Z1"); cellRang.Font.Bold = true; cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#DAA520"); excelWorkSheet.get_Range("F4").EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; //Formate price column excelWorkSheet.get_Range("O2").EntireColumn.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"; //.NumberFormat = "0.00"; excelWorkSheet.get_Range("O2").EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; //Auto fit columns excelWorkSheet.Columns.AutoFit(); //Delete First Page excelApp.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1]; lastWorkSheet.Delete(); excelApp.DisplayAlerts = true; //Set Defualt Page (excelWorkBook.Sheets[1] as Excel._Worksheet).Activate(); excelWorkBook.SaveAs(NuevoArchivo, Excel.XlFileFormat.xlExcel7, Default, Default, false, Default, Excel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default); //excelWorkBook.SaveAs(NuevoArchivo, OfficeExcel.XlFileFormat.xlExcel5, Default, Default, false, Default, OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default); //excelWorkBook.SaveAs(NuevoArchivo, OfficeExcel.XlFileFormat.xlExcel9795, Default, Default, false, Default, OfficeExcel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default); excelWorkBook.Close(); excelApp.Quit(); }
/// <summary> /// 将DataTable追加到EXCEL页上 /// </summary> /// <param name="FilePath">文件路径</param> /// <param name="SheetName">页名</param> /// <param name="dt">数据表</param> /// <param name="Cover">根据数据表名称,是否覆盖EXCEL的页</param> private void appendTable2Excel(String FilePath, String SheetName, DataTable dt, bool Cover) { if (!File.Exists(FilePath)) { throw new Exception("EXCEL文件路径不存在!请检查此路径是否正确:" + FilePath); } Microsoft.Office.Interop.Excel.Application excel = null; Microsoft.Office.Interop.Excel.Workbook wb = null; try { excel = new Microsoft.Office.Interop.Excel.Application(); wb = excel.Workbooks.Open(FilePath); excel.Visible = false; //关闭提示框,关联到删除sheet、关闭EXCEL进程 excel.DisplayAlerts = false; for (int i = 0, len = wb.Sheets.Count; i < len; i++) { Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(i + 1); if (s.Name.Equals(SheetName) && Cover) { s.Delete(); len--; } else if (s.Name.Equals(SheetName) && !Cover) { return; } } int maxIndex = wb.Sheets.Count; Microsoft.Office.Interop.Excel.Worksheet lastSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(maxIndex); wb.Worksheets.Add(System.Reflection.Missing.Value, lastSheet, System.Reflection.Missing.Value, System.Reflection.Missing.Value); Microsoft.Office.Interop.Excel.Worksheet newSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(maxIndex + 1); newSheet.Name = SheetName; for (int i = 0, len = dt.Columns.Count; i < len; i++) { Microsoft.Office.Interop.Excel.Range range = newSheet.Cells[1, i + 1]; range.NumberFormatLocal = "@"; newSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; } for (int i = 0, len = dt.Rows.Count; i < len; i++) { for (int j = 0, len2 = dt.Columns.Count; j < len2; j++) { Microsoft.Office.Interop.Excel.Range range = newSheet.Cells[i + 2, j + 1]; range.NumberFormatLocal = "@"; newSheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString(); } } wb.Close(true, Type.Missing, Type.Missing); excel.Quit(); } catch (Exception ex) { if (null != wb) { wb.Close(false, Type.Missing, Type.Missing); } if (null != excel) { excel.Quit(); } throw ex; } }
public void generarReporte(DataGridView tabla, string nombreHojaReporte, string tituloReporte, string celdaInicioTitulo, string celdaFinTitulo, int indexInicioTitulo, int indexFinTitulo) { ////Para futura referencia, esta es una forma probable de obtener un rango de celdas basado en indices ////Excel.Range range = hoja.Ranges(hoja.Cells[1, 1], hoja.Cells[1, 2]); string columnaOrdenamiento = "Filtro"; try { Cursor.Current = Cursors.WaitCursor; exportar.Enabled = false; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = excel.Application.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet hojaDatos = wb.ActiveSheet; int IndiceColumna = 0; foreach (DataGridViewColumn col in tabla.Columns) // Columnas { IndiceColumna++; hojaDatos.Cells[1, IndiceColumna] = col.Name; } //agregar campo de ordenamiento hojaDatos.Cells[1, IndiceColumna + 1] = columnaOrdenamiento; int IndiceFila = 0; foreach (DataGridViewRow row in tabla.Rows) // Filas { IndiceFila++; IndiceColumna = 0; foreach (DataGridViewColumn col in tabla.Columns) { IndiceColumna++; hojaDatos.Cells[IndiceFila + 1, IndiceColumna] = "'" + row.Cells[col.Name].Value; } hojaDatos.Cells[IndiceFila + 1, IndiceColumna + 1] = columnaOrdenamiento; } Excel.Worksheet hojaReporte = excel.Sheets.Add(); hojaReporte.Name = nombreHojaReporte; hojaReporte.Activate(); Excel.Range oRange = hojaDatos.UsedRange; Excel.PivotCache oPivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, oRange, Type.Missing); Excel.Range oRange2 = hojaReporte.Cells[5, 2]; Excel.PivotCaches pch = wb.PivotCaches(); pch.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(oRange2, "reportePersonas", Type.Missing, Type.Missing); Excel.PivotTable pvt = hojaReporte.PivotTables("reportePersonas") as Excel.PivotTable; //configuracion de la tabla dinamica pvt.RowGrand = false; //Ocultar los totales y subtotales de la tabla dinamica pvt.ColumnGrand = false; //Ocultar los totales y subtotales de la tabla dinamica pvt.EnableFieldList = false; //desactivar la opcion para apagar o encender campos en la tabla dinamica pvt.ShowDrillIndicators = false; //quitar los simbolos de + en cada celda pvt.EnableDrilldown = false; //no permitir minimizar las filas pvt.InGridDropZones = false; //no permitir drag&drop de las columnas pvt.ShowTableStyleRowHeaders = false; //no mostrar columna de por medio en negrita/otro color, segun el estilo de tabla pvt.TableStyle2 = "PivotStyleMedium9"; //settear estilo de tabla foreach (DataGridViewColumn col in tabla.Columns) // Columnas { Excel.PivotField field = (Excel.PivotField)pvt.PivotFields(col.Name); field.Orientation = Excel.XlPivotFieldOrientation.xlRowField; field.Subtotals[1] = false; } //agregar el PivotField para el campo de ordenamiento Excel.PivotField f = (Excel.PivotField)pvt.PivotFields(columnaOrdenamiento); f.Orientation = Excel.XlPivotFieldOrientation.xlDataField; f.Name = "No remover, ocultar solamente"; //hacer que las columnas tengan el tamaño adecuado hojaReporte.UsedRange.Columns.AutoFit(); //int startIndex = indexColumnaOrdenamiento.IndexOfAny("0123456789".ToCharArray()); //string indicatedColumnLetter = indexColumnaOrdenamiento.Substring(0, startIndex); string column = obtenerNombreColExcel(tabla.Columns.Count + 2); // se agregan mas dos por la posicion inicial de la tabla y la columna de ordenamiento extra hojaReporte.Range[column + "1"].EntireColumn.Hidden = true; //ocultando la columna de sort //agregar el dato de encabezado hojaReporte.Cells[2, 3] = tituloReporte; Excel.Range titulo = hojaReporte.Range[celdaInicioTitulo, celdaFinTitulo]; titulo.Merge(); titulo.Font.Bold = true; titulo.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; titulo.Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = Color.Black; hojaReporte.Cells[3, indexInicioTitulo] = "Fecha:"; hojaReporte.Cells[3, indexInicioTitulo + 1] = DateTime.Today; hojaReporte.Cells[3, indexFinTitulo - 1] = "Hora:"; hojaReporte.Cells[3, indexFinTitulo] = DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString(); //eliminar la hoja de datos excel.DisplayAlerts = false; //bypass del bug que evita que se elimine la hoja hojaDatos.Activate(); hojaDatos.Delete(); hojaReporte.Activate(); excel.DisplayAlerts = true; //retornar la propiedad al valor original MessageBox.Show("Infome generado exitosamente.", "Operación completa", MessageBoxButtons.OK, MessageBoxIcon.Information); exportar.Enabled = true; excel.Visible = true; Cursor.Current = Cursors.Default; } catch (Exception e) { Console.WriteLine(e.StackTrace); Cursor.Current = Cursors.Default; exportar.Enabled = true; MessageBox.Show("Ha ocurrido un error en la creación del documento, póngase en contacto con los desarrolladores del sistema.", "Error - AlbergueHN", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public void ExportToExcel(DataSet dataset, string FilePath, string number, string District, string ReportType = "") { int inHeaderLength = 2, inColumn = 0, inRow = 0; System.Reflection.Missing Default = System.Reflection.Missing.Value; //Create Excel File //strPath += @"\Excel" + DateTime.Now.ToString().Replace(':', '-') + ".xlsx"; Excel.Application excelApp = new Excel.Application(); Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(1); foreach (DataTable dtbl in dataset.Tables) { //Create Excel WorkSheet Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default); excelWorkSheet.Name = dtbl.TableName.ToUpper();//Name worksheet //Write Column Name for (int i = 0; i < dtbl.Columns.Count + 1; i++) { if (i == 0) { excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = "SL NO"; } else { excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dtbl.Columns[i - 1].ColumnName.ToUpper(); } } //Write Rows int ma = dtbl.Rows.Count; int na = dtbl.Columns.Count + 2; for (int m = 0; m < dtbl.Rows.Count; m++) { for (int n = 0; n < dtbl.Columns.Count + 1; n++) { inColumn = n + 1; inRow = inHeaderLength + 2 + m; if (n == 0) { excelWorkSheet.Cells[inRow, inColumn] = m + 1; } else { excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n - 1].ToString(); if (m % 2 == 0) { excelWorkSheet.get_Range("A" + inRow.ToString(), ColumnIndexToColumnLetter(dtbl.Columns.Count + 1) + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FCE4D6"); } na = n; } } } //Excel Header Excel.Range cellRang = excelWorkSheet.get_Range("A1", ColumnIndexToColumnLetter(dtbl.Columns.Count + 1) + "2"); cellRang.Merge(false); cellRang.Interior.Color = System.Drawing.Color.White; cellRang.Font.Color = System.Drawing.Color.Gray; cellRang.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cellRang.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; cellRang.Font.Size = 14; excelWorkSheet.Cells[1, 1] = "Sales Report of " + DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd"); //excelWorkSheet.Cells[60, 5] = "=SUM(E4,E59)"; //excelWorkSheet.Cells[dtbl.Rows.Count + 2, dtbl.Columns.Count].Formula = a; if (ReportType == "ZM") { //Create Total int r = dtbl.Rows.Count + 3; excelWorkSheet.Cells[dtbl.Rows.Count + 4, dtbl.Columns.Count + 1] = "=SUM(" + ColumnIndexToColumnLetter(dtbl.Columns.Count + 1) + "4:" + ColumnIndexToColumnLetter(dtbl.Columns.Count + 1) + "" + (dtbl.Rows.Count + 3) + ")"; //excelWorkSheet.Cells[dtbl.Rows.Count + 4, dtbl.Columns.Count + 1] = "=SUM(BN4:BN" + (dtbl.Rows.Count + 3) + ")"; ((Excel.Range)excelWorkSheet.get_Range((dtbl.Rows.Count + 4) + ":" + dtbl.Columns.Count + 1)).Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; } //Style table column names cellRang = excelWorkSheet.get_Range("A3", ColumnIndexToColumnLetter(dtbl.Columns.Count + 1) + "3"); cellRang.Font.Bold = true; cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31"); excelWorkSheet.get_Range("E4").EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; //Formate price column //excelWorkSheet.get_Range("F5").EntireColumn.NumberFormat = "0.00"; //Auto fit columns excelWorkSheet.Columns.AutoFit(); } //Delete First Page excelApp.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1]; lastWorkSheet.Delete(); excelApp.DisplayAlerts = true; //Set Defualt Page (excelWorkBook.Sheets[1] as Excel._Worksheet).Activate(); excelWorkBook.SaveAs(FilePath, Default, Default, Default, false, Default, Excel.XlSaveAsAccessMode.xlNoChange, Default, Default, Default, Default, Default); excelWorkBook.Close(); excelApp.Quit(); }