public static SqlCeDataReader ExecuteReader(string strComando, CommandType Tipo, List <SqlCeParameter> parametersList, SqlCeConnection cnnConexion)
{
SqlCeDataReader rdrLector = null;
try
{
SqlCeCommand cmdComando;
cmdComando = new SqlCeCommand(strComando, cnnConexion);
cmdComando.CommandType = Tipo;
foreach (SqlCeParameter parameter in parametersList)
{
cmdComando.Parameters.AddWithValue(parameter.ParameterName, parameter.Value ?? DBNull.Value);
}
System.Diagnostics.Debug.WriteLine(strComando);
rdrLector = cmdComando.ExecuteReader();
}
catch (Exception ex)
{
TextToFile.Errores(TextToFile.Error(ex));
}
return(rdrLector);
}
public static int ExecuteNonQuery(string strComando, CommandType Tipo, List <SqlCeParameter> parametersList, SqlCeConnection cnnConexion)
{
int intRegsAfectados = 0;
try
{
SqlCeCommand cmdComando;
cmdComando = new SqlCeCommand(strComando, cnnConexion);
cmdComando.CommandType = Tipo;
foreach (SqlCeParameter parameter in parametersList)
{
cmdComando.Parameters.AddWithValue(parameter.ParameterName, parameter.Value ?? DBNull.Value);
}
cmdComando.Prepare();
intRegsAfectados = cmdComando.ExecuteNonQuery();
System.Diagnostics.Debug.WriteLine(strComando);
cmdComando.Dispose();
}
catch (Exception ex)
{
TextToFile.Errores(TextToFile.Error(ex));
}
return(intRegsAfectados);
}
public static bool CreateExcelDocument <T>(List <T> list, string Title, string xlsxFilePath, string[] header, string footer)
{
try
{
DataSet ds = new DataSet();
ds.Tables.Add(ListToDataTable(list, Title));
return(CreateExcelDocument(ds, xlsxFilePath, header, footer));
}
catch (Exception ex)
{
TextToFile.Errores(TextToFile.Error(ex));
return(false);
}
}
public static bool CreateExcelDocument(DataTable dt, string xlsxFilePath, string[] header, string footer)
{
try
{
DataSet ds = new DataSet();
ds.Tables.Add(dt);
bool result = CreateExcelDocument(ds, xlsxFilePath, header, footer);
ds.Tables.Remove(dt);
return(result);
}
catch (Exception ex)
{
TextToFile.Errores(TextToFile.Error(ex));
return(false);
}
}
public static DataSet ExecuteDataSet(DataSet ds, string strComando, CommandType Tipo, string strTabla, List <SqlCeParameter> parametersList, SqlCeConnection cnnConexion, SqlCeDataAdapter daAdapter)
{
try
{
SqlCeCommand cmdComando;
cmdComando = new SqlCeCommand(strComando, cnnConexion);
cmdComando.CommandType = Tipo;
foreach (SqlCeParameter parameter in parametersList)
{
cmdComando.Parameters.AddWithValue(parameter.ParameterName, parameter.Value ?? DBNull.Value);
}
daAdapter = new SqlCeDataAdapter(cmdComando);
daAdapter.Fill(ds, strTabla);
}
catch (Exception ex)
{
TextToFile.Errores(TextToFile.Error(ex));
}
return(ds);
}
public static DataTable ListToDataTable <T>(List <T> list, string Title)
{
DataTable dt = new DataTable(Title);
try
{
foreach (PropertyInfo info in typeof(T).GetProperties())
{
var dc = new DataColumn(info.Name, GetNullableType(info.PropertyType));
dc.Caption = GetName(info);
dt.Columns.Add(dc);
}
foreach (T t in list)
{
DataRow row = dt.NewRow();
foreach (PropertyInfo info in typeof(T).GetProperties())
{
if (!IsNullableType(info.PropertyType))
{
row[info.Name] = info.GetValue(t, null);
}
else
{
row[info.Name] = (info.GetValue(t, null) ?? DBNull.Value);
}
}
dt.Rows.Add(row);
}
}
catch (Exception ex)
{
TextToFile.Errores(TextToFile.Error(ex));
return(null);
}
return(dt);
}
/// <summary>
/// Create an Excel file, and write it to a file.
/// </summary>
/// <param name="ds">DataSet containing the data to be written to the Excel.</param>
/// <param name="excelFilename">Name of file to be written.</param>
/// <param name="header">Header of the table.</param>
/// <returns>True if successful, false if something went wrong.</returns>
public static bool CreateExcelDocument(DataSet ds, string excelFilename, string[] header, string footer)
{
try
{
XLWorkbook wb = new XLWorkbook();
foreach (DataTable dt in ds.Tables)
{
var ws = wb.Worksheets.Add(dt.TableName);
ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
var rgHeader = ws.Cell(1, 1).InsertData(header);
rgHeader.Style.Font.Bold = true;
rgHeader.Style.Fill.BackgroundColor = XLColor.Gainsboro;
int EspacioHeaderTable = 2;
if (dt.TableName == "Control IVA")
{
int filaExtra = header.Length + EspacioHeaderTable;
ws.Cell(filaExtra, 1).Value = "Liquidación BSP";
ws.Cell(filaExtra, 11).Value = "Liquidación BO";
ws.Cell(filaExtra, 20).Value = "Diferencias (BSP - BackOffice) (Solo > 0,50)";
ws.Range(filaExtra, 1, filaExtra, 10).Merge();
ws.Range(filaExtra, 11, filaExtra, 19).Merge();
ws.Range(filaExtra, 20, filaExtra, 23).Merge();
ws.Range(filaExtra, 1, filaExtra, 23).Style.Font.Bold = true;
ws.Range(filaExtra, 1, filaExtra, 23).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
EspacioHeaderTable++;
}
else if (dt.TableName == "Diferencias")
{
int filaExtra = header.Length + EspacioHeaderTable;
ws.Cell(filaExtra, 1).Value = "Liquidación BSP";
ws.Cell(filaExtra, 10).Value = "Liquidación BO";
ws.Cell(filaExtra, 18).Value = "Diferencias (BSP - BackOffice)";
ws.Range(filaExtra, 1, filaExtra, 9).Merge();
ws.Range(filaExtra, 10, filaExtra, 17).Merge();
ws.Range(filaExtra, 18, filaExtra, 23).Merge();
ws.Range(filaExtra, 1, filaExtra, 23).Style.Font.Bold = true;
ws.Range(filaExtra, 1, filaExtra, 23).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
EspacioHeaderTable++;
}
else if (dt.TableName == "BSP-Op.Nro")
{
int filaExtra = header.Length + EspacioHeaderTable;
ws.Cell(filaExtra, 7).Value = "Fecha";
ws.Cell(filaExtra, 9).Value = "Forma de Pago";
ws.Cell(filaExtra, 11).Value = "Impuestos";
ws.Cell(filaExtra, 13).Value = "IVA";
ws.Cell(filaExtra, 14).Value = "Comisión";
ws.Cell(filaExtra, 16).Value = "IVA";
ws.Cell(filaExtra, 17).Value = "Total";
ws.Range(filaExtra, 9, filaExtra, 10).Merge();
ws.Range(filaExtra, 9, filaExtra, 10).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(filaExtra, 11, filaExtra, 12).Merge();
ws.Range(filaExtra, 11, filaExtra, 12).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(filaExtra, 14, filaExtra, 15).Merge();
ws.Range(filaExtra, 14, filaExtra, 15).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(filaExtra, 1, filaExtra, 19).Style.Font.Bold = true;
EspacioHeaderTable++;
}
else if (dt.TableName == "Listado para facturación")
{
int filaExtra = header.Length + EspacioHeaderTable;
ws.Cell(filaExtra, 7).Value = "Fecha";
ws.Cell(filaExtra, 11).Value = "IVA";
ws.Cell(filaExtra, 14).Value = "Comisión";
ws.Cell(filaExtra, 16).Value = "IVA";
ws.Cell(filaExtra, 17).Value = "Total";
ws.Cell(filaExtra, 11).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell(filaExtra, 16).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(filaExtra, 14, filaExtra, 15).Merge();
ws.Range(filaExtra, 14, filaExtra, 15).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Range(filaExtra, 1, filaExtra, 18).Style.Font.Bold = true;
EspacioHeaderTable++;
}
var table = ws.Cell(header.Length + EspacioHeaderTable, 1).InsertTable(dt);
table.ShowAutoFilter = false;
table.Theme = XLTableTheme.None;
table.Row(1).Style.Font.Bold = true;
int colCount = table.ColumnCount();
var rangeHeader = ws.Range(1, 1, header.Length, colCount);
for (int i = 1; i <= header.Length; i++)
{
rangeHeader.Row(i).Merge();
}
if (dt.TableName == "Over" || dt.TableName == "Creditos" || dt.TableName == "Debitos" || dt.TableName == "Reembolsos")
{
table.Rows(x => x.Cell(1).Value.ToString() == "TOTAL").Style.Font.Bold = true;
}
ws.Columns().AdjustToContents();
ws.Cell(table.LastRow().RowNumber() + 2, 1).Value = footer;
}
wb.SaveAs(excelFilename);
return(true);
}
catch (Exception ex)
{
TextToFile.Errores(TextToFile.Error(ex));
return(false);
}
}
