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