//*********************************************************************
// Misc Functions
//*********************************************************************
// https://stackoverflow.com/questions/33115067/how-do-i-format-my-export-to-excel-workbook-in-microsoft-office-interop-excel
public void ExportToExcel(Microsoft.Office.Interop.Excel._Application app, Microsoft.Office.Interop.Excel._Workbook workbook, GridView gridview, string SheetName, int sheetid)
{
// see the excel sheet behind the program
app.Visible = false;
// get the reference of first sheet. By default its name is Sheet1
worksheet = (Excel.Worksheet)workbook.Worksheets.Add();
// changing the name of active sheet
worksheet.Name = SheetName;
int gridViewCellCount = gridview.Rows[0].Cells.Count;
// string array to hold grid view column names.
string[] columnNames = new string[gridViewCellCount];
// gridview.Rows.Count
int gridViewRowCount = gridview.Rows.Count;
for (int i = 0; i < gridViewCellCount; i++)
{
columnNames[i] = ((System.Web.UI.WebControls.DataControlFieldCell)(gridview.Rows[0].Cells[i])).ContainingField.HeaderText;
}
int iCol = 1;
foreach (var name in columnNames)
{
worksheet.Cells[1, iCol] = name;
iCol++;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < gridViewRowCount; i++)
{
for (int j = 0; j < gridViewCellCount; j++)
{
string cv = gridview.Rows[i].Cells[j].Text;
if (gridview.Rows[i].Cells[j].Text != " ")
{
worksheet.Cells[i + 2, j + 1] = gridview.Rows[i].Cells[j].Text;
}
else
{
worksheet.Cells[i + 2, j + 1] = "";
}
}
}
}
public void CrearArchivoExcel(string NombreArchivo, System.Data.DataTable dtInfo, string TituloArchivo, BackgroundWorker Proceso)
{
Microsoft.Office.Interop.Excel._Application xlApp = null;
Workbook xlWorkbook = null;
Sheets xlSheets = null;
Worksheet xlNewSheet = null;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
if (xlApp == null)
{
return;
}
// Uncomment the line below if you want to see what's happening in Excel
// xlApp.Visible = true;
xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//===================== se elimino, este procedimiento permite abrir el archivo tomando como referencia el nombre del mismo =====================
//xlWorkbook = xlApp.Workbooks.Open(RutaDeArchivo, 0, false, 5, "", "",
// false, XlPlatform.xlWindows, "",
// true, false, 0, true, false, false);
//===============================================================================================================================================
//xlWorkbook.CheckCompatibility = false;
//xlWorkbook.Application.DisplayAlerts = false;
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
xlSheets = xlWorkbook.Sheets as Sheets;
xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
xlNewSheet.Name = NombreArchivo;
xlNewSheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible;
EdicionHojaExcel(xlNewSheet, dtInfo, TituloArchivo);
int FilaArchivo = 6;
int i = 0;
foreach (System.Data.DataColumn dColum in dtInfo.Columns)
{
i++;
xlNewSheet.Cells[FilaArchivo, i] = dColum.ToString();
}
//============================================ Funcion que permite usar la opcion FREEZE PANES por codigo ====================================================
// Se declara el punto de referencia tomando en cuenta la celda
Rangos = xlNewSheet.get_Range("A7", "E7");
Rangos.Activate();
Rangos.Application.ActiveWindow.FreezePanes = true;
// =========================================== Esta es la forma como se activa la funcion de FREZEE PANES ====================================================
int Fila = 6;
int Total = dtInfo.Rows.Count;
int Porcentaje = 0;
foreach (DataRow dr in dtInfo.Rows)
{
Fila++;
for (int j = 0; j < dtInfo.Columns.Count; j++)
{
xlNewSheet.Cells[Fila, NombreColumna(j)] = dr[j].ToString();
}
Porcentaje = (Fila / Total) * 100;
Proceso.ReportProgress(Porcentaje);
}
xlApp.Visible = true;
releaseObject(xlApp);
releaseObject(xlWorkbook);
releaseObject(xlSheets);
releaseObject(xlNewSheet);
}
catch (Exception Error)
{
throw new Exception("Error: " + Error.Message);
}
finally
{
Marshal.ReleaseComObject(xlNewSheet);
Marshal.ReleaseComObject(xlSheets);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
}
public void CrearArchivoExcelQueryTables(string NombreArchivo, System.Data.DataTable dtInfo, string TituloArchivo, BackgroundWorker Proceso, string FechaInicial,
string FechaFinal, string SerialQR, string Nameplate, string IdModel, string Query)
{
const string OLEDBConnection = "OLEDB;Provider=SQLOLEDB.1;Data Source=172.20.96.13;UID=sa;PWD=;Initial Catalog=AirBag";
Microsoft.Office.Interop.Excel._Application xlApp = null;
Workbook xlWorkbook = null;
Sheets xlSheets = null;
Worksheet xlNewSheet = null;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
if (xlApp == null)
{
return;
}
// Uncomment the line below if you want to see what's happening in Excel
// xlApp.Visible = true;
xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//===================== se elimino, este procedimiento permite abrir el archivo tomando como referencia el nombre del mismo =====================
//xlWorkbook = xlApp.Workbooks.Open(RutaDeArchivo, 0, false, 5, "", "",
// false, XlPlatform.xlWindows, "",
// true, false, 0, true, false, false);
//===============================================================================================================================================
//xlWorkbook.CheckCompatibility = false;
//xlWorkbook.Application.DisplayAlerts = false;
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
xlSheets = xlWorkbook.Sheets as Sheets;
xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
xlNewSheet.Name = NombreArchivo;
xlNewSheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible;
EdicionHojaExcel(xlNewSheet, dtInfo, TituloArchivo);
//============================================ Funcion que permite usar la opcion FREEZE PANES por codigo ====================================================
// Se declara el punto de referencia tomando en cuenta la celda
Rangos = xlNewSheet.get_Range("A7", "E7");
Rangos.Activate();
Rangos.Application.ActiveWindow.FreezePanes = true;
// =========================================== Esta es la forma como se activa la funcion de FREZEE PANES ====================================================
xlNewSheet.QueryTables.Add(OLEDBConnection, xlNewSheet.get_Range("A6", "E6"), Query.Trim()).Refresh();
xlApp.Visible = true;
releaseObject(xlApp);
releaseObject(xlWorkbook);
releaseObject(xlSheets);
releaseObject(xlNewSheet);
}
catch (Exception Error)
{
throw new Exception("Error: " + Error.Message);
}
finally
{
Marshal.ReleaseComObject(xlNewSheet);
Marshal.ReleaseComObject(xlSheets);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
}
