protected void btnDownloadSpreadsheet_Click(object sender, EventArgs e)
{
SLExcelData data = (SLExcelData)Session[SessionExcelData];
if (data == null)
{
lblMsgDownload.Text = "File record does not exists.";
}
string path = Server.MapPath("~/Files");
string fileName = "Spreadsheet.xlsx";
string fullPath = Path.Combine(path, fileName);
byte[] file = (new SLExcelWriter()).GenerateExcel(data);
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
//string filename = "Download_Excel_Report" + "_" + DateTime.Now.ToString().Replace("/", "-") + ".xls";
//Response.AddHeader("Content-Disposition", "attachment; filename={0}, " + filename + "");
Response.AddHeader("Content-Disposition", "attachment; filename=ExcelFile.xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(file);
Response.Flush();
Response.Close();
Response.End();
}
public SLExcelData ReadExcel(Sheet sheet)
{
var data = new SLExcelData();
List <Row> rows;
try
{
data.SheetName = sheet.Name;
var workSheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
var columns = workSheet.Descendants <Columns>().FirstOrDefault();
data.ColumnConfigurations = columns;
var sheetData = workSheet.Elements <SheetData>().First();
rows = sheetData.Elements <Row>().ToList();
}
catch (Exception)
{
data.Status.Message = "Unable to open the file";
return(data);
}
// Read the header
if (rows.Count > 0)
{
var row = rows[0];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
data.Headers.Add(text);
}
}
// Read the sheet data
if (rows.Count > 1)
{
for (var i = 1; i < rows.Count; i++)
{
var dataRow = new List <string>();
data.DataRows.Add(dataRow);
var row = rows[i];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
dataRow.Add(text);
}
}
}
return(data);
}
private SLExcelData GetNewDataObject()
{
var data = new SLExcelData();
data.SheetName = "Poll";
data.Headers = new List <string>()
{
"Person Id", "Person Name", "Date", "Comment", "Vote"
};
return(data);
}
private void RefreshButton_Click(object sender, RoutedEventArgs e)
{
SLExcelData processedData = SyncTasksManager.GetInstance().GetProcessData(SyncTask.ID, SyncTask.ProcessID, IsCompleted);
if (processedData == null)
{
SyncTask syncTask = SyncTasksManager.GetInstance().SyncTasks.Where(t => t.ProcessID == SyncTask.ProcessID).FirstOrDefault();
if (syncTask != null)
{
List <ProgressItem> progressItems = new List <ProgressItem>();
progressItems.Add(new ProgressItem()
{
Action = "Synchronization",
Message = SyncTask.DestinationListName,
Status = syncTask.Status,
Path = ""
});
Application.Current.Dispatcher.Invoke(new Action(() =>
{
TasksDataGrid.ItemsSource = null;
TasksDataGrid.ItemsSource = progressItems;
}));
}
}
else
{
try
{
List <ProgressItem> progressItems = new List <ProgressItem>();
foreach (List <string> datarow in processedData.DataRows)
{
progressItems.Add(new ProgressItem()
{
Action = datarow[0],
Message = datarow[2],
Status = datarow[1],
Path = datarow[4]
});
}
Application.Current.Dispatcher.Invoke(new Action(() =>
{
TasksDataGrid.ItemsSource = null;
TasksDataGrid.ItemsSource = progressItems;
}));
}
catch (Exception ex)
{
}
}
}
/// <summary>
/// usage
/// byte[] file = DownloadFile(List_of_any_obj)
///
/// Response.AddHeader("Content-Disposition", "attachment; filename=ExcelFile.xlsx");
/// return new FileContentResult(file,"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">List of any object</param>
/// <returns>byte array of the excel file that is created</returns>
public static byte[] CreateExcel <T>(IEnumerable <T> list, string[] colum_names = null)
{
SLExcelData Data = Convert_To_ExcelData(list, colum_names);
var file = (new SLExcelWriter()).GenerateExcel(Data);
return(file);
// how it would be used
/* Response.AddHeader("Content-Disposition",
* "attachment; filename=ExcelFile.xlsx");
* return new FileContentResult(file,
* "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");*/
}
private static SLExcelData Convert_To_ExcelData <T>(IEnumerable <T> list, string[] colum_names = null)
{
Type type = typeof(T);
var properties = type.GetProperties();
SLExcelData Data = new SLExcelData();
if (colum_names != null)//if specifc rows are selected , add them to headers
{
Data.Headers.AddRange(colum_names);
}
else//else ==> loop through all object's variables
{
foreach (PropertyInfo info in properties)
{
Data.Headers.Add(info.Name);
}
}
foreach (T entity in list)
{
//object[] values = new object[properties.Length];
List <string> values = new List <string>();
for (int i = 0; i < Data.Headers.Count; i++)
{
try
{
values.Add(entity.GetType().GetProperty(Data.Headers[i]).GetValue(entity, null).ToString());
//values.Add(properties[i].GetValue(entity).ToString());
}
catch (Exception)
{
values.Add("");
}
}
Data.DataRows.Add(values);
}
return(Data);
}
public void GetVisitLabs(int subjectId, int groupId, int subGroupId)
{
var data = new SLExcelData();
var headerData = GroupManagementService.GetLabsScheduleVisitings(subjectId, groupId, subGroupId);
var rowsData = GroupManagementService.GetLabsScheduleMarks(subjectId, groupId, subGroupId);
data.Headers.Add("Студент");
data.Headers.AddRange(headerData);
data.DataRows.AddRange(rowsData);
var file = (new SLExcelWriter()).GenerateExcel(data);
Response.Clear();
Response.Charset = "ru-ru";
Response.HeaderEncoding = Encoding.UTF8;
Response.ContentEncoding = Encoding.UTF8;
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", "attachment; filename=LabVisiting.xlsx");
Response.BinaryWrite(file);
Response.Flush();
Response.End();
}
public ActionResult DownloadArchiwum()
{
var data = new SLExcelData();
data.SheetName = "archiwum";
var archiwum = context.OcenaArchiwum.ToList();
var first = true;
foreach (var row in archiwum)
{
var r = new List <string>();
foreach (var p in row.GetType().GetProperties())
{
if (first)
{
data.Headers.Add(p.Name);
}
if (p.GetValue(row, null) != null)
{
r.Add(p.GetValue(row, null).ToString());
}
else
{
r.Add("");
}
}
data.DataRows.Add(r);
first = false;
}
var file = (new SLExcelWriter()).GenerateExcel(data);
//Response.ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Headers.Add("Content-Disposition", $"attachment; filename=Archiwum {DateTime.Now:yyyy-MM-dd}.xlsx");
Response.Headers.Add("Access-Control-Expose-Headers", "content-disposition");
return(new FileContentResult(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
public ActionResult DownloadFile([FromQuery] int[] skills = null,
[FromQuery] int[] departaments = null,
[FromQuery] bool RODO = true)
{
var data = new SLExcelData();
data.SheetName = "lista ocen";
data.Headers.Add("Dział");
data.Headers.Add("Nr osobowy");
data.Headers.Add("Pracownik");
if (skills.Length == 0)
{
skills = null;
}
if (departaments.Length == 0)
{
departaments = null;
}
List <Kwalifikacja> kwalifikacje;
if (skills != null)
{
kwalifikacje = context.Kwalifikacje
.Include(k => k.KwalifikacjaWydzial)
.ThenInclude(w => w.Wydzial)
.Where(k => k.KwalifikacjaWydzial.Any(w => skills.Contains(w.WydzialID)))
.OrderBy(k => k.KwalifikacjaWydzial.FirstOrDefault().WydzialID).ToList();
}
else
{
kwalifikacje = context.Kwalifikacje
.Include(k => k.KwalifikacjaWydzial)
.ThenInclude(w => w.Wydzial)
.OrderBy(k => k.KwalifikacjaWydzial.FirstOrDefault().WydzialID).ToList();
}
//sprawdzić!!!
foreach (var k in kwalifikacje)
{
data.Headers.Add(k.Nazwa);
}
//pracownicy
IEnumerable <Pracownik> pracownicy;
if (departaments != null)
{
pracownicy = context.Pracownicy
.Where(p => departaments.Contains(p.WydzialID))
.Where(p => p.IsActive == true)
.Include(p => p.Oceny)
.Include(p => p.Wydzial)
.OrderBy(p => p.Nazwisko)
.ThenBy(p => p.Imie);
}
else
{
pracownicy = context.Pracownicy
.Where(p => p.IsActive == true)
.Include(p => p.Oceny)
.Include(p => p.Wydzial)
.OrderBy(p => p.Nazwisko)
.ThenBy(p => p.Imie);
}
var r = new List <string>();
Ocena o;
foreach (var p in pracownicy)
{
if (p != null)
{
r = new List <string>
{
p.Wydzial?.Nazwa,
p.NrPersonalny,
RODO ? p.Imie : p.FullName
};
foreach (var k in kwalifikacje)
{
o = p.Oceny.Where(o => o.KwalifikacjaID == k.ID && o.DataDo == DateTime.MaxValue.Date).FirstOrDefault();
r.Add(o == null ? "" : o.OcenaV.ToString());
}
data.DataRows.Add(r);
}
}
var file = (new SLExcelWriter()).GenerateExcel(data);
//Response.ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Headers.Add("Content-Disposition", $"attachment; filename=Oceny {DateTime.Now:yyyy-MM-dd}.xlsx");
Response.Headers.Add("Access-Control-Expose-Headers", "content-disposition");
return(new FileContentResult(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
private async void Save(Object o)
{
IsBusy = true;
try
{
var token = (Application.Current as App).Settings.AccessToken;
// Save settings
(Application.Current as App).Settings.PostUrl = PostUrl;
(Application.Current as App).Settings.FilePath = Path;
Progress = "Getting Post Id...";
var postId = await FacebookHelper.GetPostIdFromUrl(token, PostUrl);
if (postId == null)
{
MessageBox.Show("Could not get the post/page Id");
IsBusy = false;
return;
}
_tokenSource.Token.ThrowIfCancellationRequested();
Progress = "Downloading comments...";
var list = new List <Comment>();
int totalComments = 1;
string from = null;
string after = null;
do
{
var pagedList = await FacebookHelper.GetComments(token, postId, from, after).ConfigureAwait(false);
if (pagedList == null)
{
throw new InvalidDataException();
}
if (pagedList.Children.Count == 0)
{
break;
}
totalComments = pagedList.Summary.TotalCount;
after = pagedList.Paging.Cursors.After;
list.AddRange(pagedList.Children);
_tokenSource.Token.ThrowIfCancellationRequested();
Progress = $"Downloading comments ({list.Count} of {totalComments})...";
} while (list.Count < totalComments);
_tokenSource.Token.ThrowIfCancellationRequested();
Progress = "Saving Excel file...";
SLExcelData data = GetNewDataObject();
foreach (var comment in list)
{
data.DataRows.Add(new List <string>()
{
comment.From.Id, comment.From.Name,
comment.CreatedTime.ToString("g"), comment.Message, FacebookHelper.GetVotes(comment.Message)
});
}
byte[] file = SLExcelWriter.GenerateExcel(data);
using (var fileStream = new FileStream(Path, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None))
{
fileStream.SetLength(file.Length);
await fileStream.WriteAsync(file, 0, file.Length);
}
Process.Start(Path);
}
catch (OperationCanceledException)
{
_tokenSource = new CancellationTokenSource();
}
catch (InvalidDataException)
{
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
IsBusy = false;
}
}
public Documento EventosExportarExcel(EventoFilter filter)
{
SLExcelData data = new SLExcelData();
try
{
data.HeadersFiltro = new List <string>();
data.HeadersFiltro.Add("Caravana");
data.HeadersFiltro.Add("Tipo Evento");
data.HeadersFiltro.Add("Fecha Desde");
data.HeadersFiltro.Add("Fecha hasta");
List <string> rowFiltro = new List <string>();
if (filter.numCaravana != 0)
{
rowFiltro.Add(filter.numCaravana.ToString());
}
else
{
rowFiltro.Add("Sin datos");
}
switch (filter.idTipoEvento)
{
case 1:
rowFiltro.Add("Vacunación");
break;
case 2:
rowFiltro.Add("Antibiótico");
break;
case 3:
rowFiltro.Add("Manejo");
break;
case 4:
rowFiltro.Add("Alimenticio");
break;
default:
rowFiltro.Add("Sin datos");
break;
}
if (filter.fechaDesde != null)
{
rowFiltro.Add(filter.fechaDesde);
}
else
{
rowFiltro.Add("Sin datos");
}
if (filter.fechaHasta != null)
{
rowFiltro.Add(filter.fechaHasta);
}
else
{
rowFiltro.Add("Sin datos");
}
data.DataRowsFiltro = new List <List <string> >();
data.DataRowsFiltro.Add(rowFiltro);
var lista = GetList(filter);
data.Headers.Add("Tipo Evento");
data.Headers.Add("Fecha Evento");
data.Headers.Add("Bovinos que participaron");
foreach (var item in lista)
{
List <string> row = new List <string>()
{
item.tipoEvento.ToString(),
item.fechaHora,
item.cantidadBovinos.ToString()
};
data.DataRows.Add(row);
}
var archivo = StaticFunctions.GenerateExcel(data, "Eventos", filter.campo, filter.usuario, filter.periodo);
return(new Documento()
{
nombre = archivo
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
protected virtual void InsertSheetData(SpreadsheetDocument document, SheetData sheetData, SLExcelData data)
{
var worksheetPart = document.WorkbookPart.WorksheetParts.Last();
// Add header
UInt32 rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, header ?? string.Empty));
}
if (data.Headers.Count > 0)
{
// Add the column configuration if available
if (data.ColumnConfigurations != null)
{
var columns = (Columns)data.ColumnConfigurations.Clone();
worksheetPart.Worksheet
.InsertAfter(columns, worksheetPart
.Worksheet.SheetFormatProperties);
}
}
// Add sheet data
foreach (var rowData in data.DataRows)
{
cellIdex = 0;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var callData in rowData)
{
var cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, callData ?? string.Empty);
row.AppendChild(cell);
}
}
document.WorkbookPart.Workbook.Save();
}
protected void btnImport_Click(object sender, EventArgs e)
{
HttpPostedFile filebase = excelFile.PostedFile;
List <string> data1 = new List <string>();
if (excelFile != null)
{
if (excelFile.PostedFile.ContentType == "application/vnd.ms-excel" || excelFile.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
string filename = excelFile.FileName;
string targetpath = Server.MapPath("~/ExcelFiles/");
excelFile.SaveAs(targetpath + filename);
string pathToExcelFile = targetpath + filename;
string connectionString = "";
if (filename.EndsWith(".xls"))
{
connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
}
else if (filename.EndsWith(".xlsx"))
{
connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
}
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
DataSet ds = new DataSet();
adapter.Fill(ds, "ExcelTable");
DataTable dtable = ds.Tables["ExcelTable"];
ExcelQueryFactory excelFiledata = new ExcelQueryFactory(pathToExcelFile);
SLExcelData datar = (new SLExcelReader()).ReadExcel(filebase);
Session[SessionExcelData] = datar;
string userLdm = Session["User"].ToString();
foreach (List <string> d in datar.DataRows)
{
string BNM = d[1];
string DNM = d[16];
string CNM = d[19];
GetAllBankDetails(BNM);
GetAllDistrictDetails(DNM);
GetAllCategoryDetails(CNM);
//objLDMFunSchema.Sr_No = int.Parse(d[0]);
objLDMFunSchema.BankName = d[1];
objLDMFunSchema.BankBranch = d[2];
objLDMFunSchema.IFSCCode = d[3];
objLDMFunSchema.App_Reg = d[4];
objLDMFunSchema.Loan_Category = d[5];
objLDMFunSchema.FirstName = d[6];
objLDMFunSchema.MiddleName = d[7];
objLDMFunSchema.LastName = d[8];
objLDMFunSchema.Gender = d[9];
objLDMFunSchema.MaritalStatus = d[10];
//objLDMFunSchema.Dob = DateTime.Parse(d[11]);
objLDMFunSchema.Dob = DateTime.FromOADate(double.Parse(d[11]));
objLDMFunSchema.Village = d[12];
objLDMFunSchema.Gram = d[13];
objLDMFunSchema.Tehsil = d[14];
objLDMFunSchema.Block = d[15];
objLDMFunSchema.District = d[16];
objLDMFunSchema.Religion = d[17];
objLDMFunSchema.Minority_Comm = d[18];
objLDMFunSchema.Social_Category = d[19];
objLDMFunSchema.Aadhar = d[20];
objLDMFunSchema.PAN = d[21];
objLDMFunSchema.Mobile = d[22];
objLDMFunSchema.Email = d[23];
objLDMFunSchema.ReqLoanAmnt = Convert.ToDouble(d[24]);
objLDMFunSchema.SanctionAmnt = Convert.ToDouble(d[25]);
//objLDMFunSchema.SanctionDate = DateTime.Parse(d[26]);
objLDMFunSchema.SanctionDate = DateTime.FromOADate(double.Parse(d[26]));
objLDMFunSchema.Business_Activity = d[27];
objLDMFunSchema.Type_Loan = d[28];
objLDMFunSchema.DisbursedAmnt = Convert.ToDouble(d[29]);
//objLDMFunSchema.DisburseDate = DateTime.Parse(d[30]);
objLDMFunSchema.DisburseDate = DateTime.FromOADate(double.Parse(d[30]));
objLDMFunSchema.LoanAmntOutStanding = Convert.ToDouble(d[31]);
objLDMFunSchema.AnualTarget = Convert.ToDouble(d[32]);
objLDMFunSchema.Districtid = GetAllDistrictDetails(DNM);
objLDMFunSchema.CategoryId = GetAllCategoryDetails(CNM); //Mahesh Category
objLDMFunSchema.BankId = GetAllBankDetails(BNM);
objLDMFunSchema.InsertDate = System.DateTime.Now;
objLDMFunSchema.InsertedBy = userLdm;
ds = objLDMFunBAL.LDMInsert(objLDMFunSchema);
}
//if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
//{
// GridView1.DataSource = ds;
// GridView1.DataBind();
//}
//else {
GridView1.DataSource = ds;
lblUploadError.Visible = false;
btnDownloadSpreadsheet.Visible = true;
// GridView1.DataBind();
//}
}
else
{
lblUploadError.Visible = true;
lblUploadError.Text = "please select excell file.";
}
}
object ddnm = Session["LDM_DistrictId"];
GetViewData(Convert.ToInt32(ddnm));
}
public Documento NacimientosExportarExcel(NacimientoFilter filter)
{
SLExcelData data = new SLExcelData();
try
{
data.HeadersFiltro = new List <string>();
data.HeadersFiltro.Add("Caravana Madre");
data.HeadersFiltro.Add("Caravana Padre");
data.HeadersFiltro.Add("Fecha desde");
data.HeadersFiltro.Add("Fecha hasta");
List <string> rowFiltro = new List <string>();
if (filter.numCaravanaMadre != 0)
{
rowFiltro.Add(filter.numCaravanaMadre.ToString());
}
else
{
rowFiltro.Add("Sin datos");
}
if (filter.numCaravanaPadre != 0)
{
rowFiltro.Add(filter.numCaravanaPadre.ToString());
}
else
{
rowFiltro.Add("Sin datos");
}
if (filter.fechaDesde != null)
{
rowFiltro.Add(filter.fechaDesde);
}
else
{
rowFiltro.Add("Sin datos");
}
if (filter.fechaHasta != null)
{
rowFiltro.Add(filter.fechaHasta);
}
else
{
rowFiltro.Add("Sin datos");
}
data.DataRowsFiltro = new List <List <string> >();
data.DataRowsFiltro.Add(rowFiltro);
var lista = GetNacimientos(filter);
data.Headers.Add("Caravana Madre");
data.Headers.Add("Caravana Padre");
data.Headers.Add("Fecha Nacimiento");
foreach (var item in lista)
{
List <string> row = new List <string>()
{
item.numCaravanaMadre,
item.numCaravanaPadre,
item.fechaNacimiento
};
data.DataRows.Add(row);
}
var archivo = StaticFunctions.GenerateExcel(data, "Nacimientos", filter.campo, filter.usuario, filter.periodo);
return(new Documento()
{
nombre = archivo
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
private void SelectImportFileButton_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
if (openFileDialog.ShowDialog() == true)
{
_SelectedFileFullPath = openFileDialog.FileName;
SelectedFileLabel.Content = (new FileInfo(openFileDialog.FileName)).Name;
}
else
{
return;
}
SLExcelReader reader = new SLExcelReader();
SLExcelData excelData = reader.ReadExcel(_SelectedFileFullPath);
SiteSetting siteSetting = ApplicationContext.Current.Configuration.SiteSettings[_MainObject.SiteSettingID];
List <Field> fields = ServiceManagerFactory.GetServiceManager(siteSetting.SiteSettingType).GetFields(siteSetting, _MainObject);
DynamicGrid.ColumnDefinitions.Clear();
ColumnDefinition gridCol1 = new ColumnDefinition();
ColumnDefinition gridCol2 = new ColumnDefinition();
gridCol1.Width = GridLength.Auto;
gridCol2.Width = GridLength.Auto;
DynamicGrid.ColumnDefinitions.Add(gridCol1);
DynamicGrid.ColumnDefinitions.Add(gridCol2);
for (int i = 0; i < excelData.Headers.Count; i++)
{
string headerTitle = excelData.Headers[i];
RowDefinition gridRow1 = new RowDefinition();
gridRow1.Height = new GridLength(45);
DynamicGrid.RowDefinitions.Add(gridRow1);
System.Windows.Controls.ComboBox comboBox = new ComboBox();
comboBox.Width = 200;
comboBox.Height = 30;
comboBox.Background = Brushes.LightBlue;
ComboBoxItem emptycboxitem = new ComboBoxItem();
emptycboxitem.Content = "Select a field";
emptycboxitem.IsSelected = true;
emptycboxitem.Tag = "";
comboBox.Items.Add(emptycboxitem);
foreach (Field field in fields)
{
ComboBoxItem cboxitem = new ComboBoxItem();
cboxitem.Content = field.DisplayName;
cboxitem.Tag = field.Name;
if (field.Name.Equals(headerTitle, StringComparison.InvariantCultureIgnoreCase) == true)
{
cboxitem.IsSelected = true;
}
comboBox.Tag = headerTitle;
comboBox.Items.Add(cboxitem);
}
Grid.SetRow(comboBox, i);
Grid.SetColumn(comboBox, 1);
DynamicGrid.Children.Add(comboBox);
Label label = new Label();
label.Content = headerTitle;
Grid.SetRow(label, i);
Grid.SetColumn(label, 0);
DynamicGrid.Children.Add(label);
}
}
public Documento InconsistenciasExportarExcel(InconsistenciaFilter filter)
{
SLExcelData data = new SLExcelData();
try
{
data.HeadersFiltro = new List <string>();
data.HeadersFiltro.Add("Tipo de Conflicto");
data.HeadersFiltro.Add("Estado");
data.HeadersFiltro.Add("Fecha desde");
data.HeadersFiltro.Add("Fecha hasta");
List <string> rowFiltro = new List <string>();
if (filter.tipo == 1)
{
rowFiltro.Add("Tacto");
}
else if (filter.tipo == 2)
{
rowFiltro.Add("Inseminación");
}
else
{
rowFiltro.Add("Sin datos");
}
if (filter.estado == 1)
{
rowFiltro.Add("Solucionado");
}
else if (filter.estado == 2)
{
rowFiltro.Add("Pendiente");
}
else
{
rowFiltro.Add("Sin datos");
}
if (filter.fechaDesde != null)
{
rowFiltro.Add(filter.fechaDesde);
}
else
{
rowFiltro.Add("Sin datos");
}
if (filter.fechaHasta != null)
{
rowFiltro.Add(filter.fechaHasta);
}
else
{
rowFiltro.Add("Sin datos");
}
data.DataRowsFiltro = new List <List <string> >();
data.DataRowsFiltro.Add(rowFiltro);
var lista = GetList(filter);
data.Headers.Add("Tipo Conflicto");
data.Headers.Add("Fecha");
data.Headers.Add("Estado");
foreach (var item in lista)
{
List <string> row = new List <string>()
{
item.tipo,
item.fecha,
item.estado
};
data.DataRows.Add(row);
}
var archivo = StaticFunctions.GenerateExcel(data, "Conflictos", filter.campo, filter.usuario, filter.periodo);
return(new Documento()
{
nombre = archivo
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public Documento ClientesExportarExcel(ReporteFilter filter)
{
SLExcelData data = new SLExcelData();
try
{
data.HeadersFiltro = new List <string>();
data.HeadersFiltro.Add("Apellido");
data.HeadersFiltro.Add("Nombre");
data.HeadersFiltro.Add("Estado Cuenta");
data.HeadersFiltro.Add("Plan");
data.HeadersFiltro.Add("Fecha Desde");
data.HeadersFiltro.Add("Fecha Hasta");
List <string> rowFiltro = new List <string>();
if (filter.apellido != null)
{
rowFiltro.Add(filter.apellido);
}
else
{
rowFiltro.Add("Sin datos");
}
if (filter.nombre != null)
{
rowFiltro.Add(filter.nombre);
}
else
{
rowFiltro.Add("Sin datos");
}
if (filter.estadoCuenta != "0")
{
rowFiltro.Add(filter.estadoCuenta);
}
else
{
rowFiltro.Add("Sin datos");
}
switch (filter.idPlan)
{
case 1:
rowFiltro.Add("Plan pequeño");
break;
case 2:
rowFiltro.Add("Plan mediano");
break;
case 3:
rowFiltro.Add("Plan grande");
break;
case 4:
rowFiltro.Add("Plan super");
break;
default:
rowFiltro.Add("Sin datos");
break;
}
if (filter.fechaDesde != null)
{
rowFiltro.Add(filter.fechaDesde);
}
else
{
rowFiltro.Add("Sin datos");
}
if (filter.fechaHasta != null)
{
rowFiltro.Add(filter.fechaHasta);
}
else
{
rowFiltro.Add("Sin datos");
}
data.DataRowsFiltro = new List <List <string> >();
data.DataRowsFiltro.Add(rowFiltro);
var lista = GetClientes(filter);
data.Headers.Add("Apellido");
data.Headers.Add("Nombre");
data.Headers.Add("Fecha Alta");
data.Headers.Add("Plan Contratado");
data.Headers.Add("Administradores");
data.Headers.Add("Usuarios");
data.Headers.Add("Campos");
data.Headers.Add("Bovinos");
data.Headers.Add("Estado Cuenta");
foreach (var item in lista)
{
List <string> row = new List <string>();
row.Add(item.apellido);
row.Add(item.nombre);
row.Add(item.fechaAlta);
if (item.idPlan == 1)
{
row.Add("Plan Pequeño");
}
else if (item.idPlan == 2)
{
row.Add("Plan Mediano");
}
else if (item.idPlan == 3)
{
row.Add("Plan Grande");
}
else
{
row.Add("Plan Super");
}
row.Add(item.cantidadAdministradores.ToString());
row.Add(item.cantidadUsuarios.ToString());
row.Add(item.cantidadCampos.ToString());
row.Add(item.cantidadBovinos.ToString());
row.Add(item.estadoCuenta);
data.DataRows.Add(row);
}
var archivo = StaticFunctions.GenerateExcel(data, "Clientes", filter.campo, filter.usuario, filter.periodo);
return(new Documento()
{
nombre = archivo
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public Documento UsuariosExportarExcel(UsuarioFilter filter)
{
SLExcelData data = new SLExcelData();
try
{
data.HeadersFiltro = new List <string>();
data.HeadersFiltro.Add("Nombre");
data.HeadersFiltro.Add("Apellido");
data.HeadersFiltro.Add("Rol");
List <string> rowFiltro = new List <string>();
if (filter.nombre != null)
{
rowFiltro.Add(filter.nombre);
}
else
{
rowFiltro.Add("Sin datos");
}
if (filter.apellido != null)
{
rowFiltro.Add(filter.apellido);
}
else
{
rowFiltro.Add("Sin datos");
}
switch (filter.idRol)
{
case 1:
rowFiltro.Add("Dueño");
break;
case 2:
rowFiltro.Add("Ingeniero");
break;
case 3:
rowFiltro.Add("Peón");
break;
default:
rowFiltro.Add("Sin datos");
break;
}
data.DataRowsFiltro = new List <List <string> >();
data.DataRowsFiltro.Add(rowFiltro);
var lista = GetList(filter);
data.Headers.Add("Usuario");
data.Headers.Add("Nombre");
data.Headers.Add("Apellido");
data.Headers.Add("Rol");
data.Headers.Add("Estado");
data.Headers.Add("Fecha Alta");
data.Headers.Add("Fecha Baja");
foreach (var item in lista)
{
List <string> row = new List <string>();
row.Add(item.usuario);
row.Add(item.nombre);
row.Add(item.apellido);
if (item.idRol == 1)
{
row.Add("Dueño");
}
else if (item.idRol == 2)
{
row.Add("Ingeniero");
}
else
{
row.Add("Peón");
}
row.Add(item.fechaBaja == " " ? "Activo" : "Inactivo");
row.Add(item.fechaAlta != " " ? item.fechaAlta : "-");
row.Add(item.fechaBaja != " " ? item.fechaBaja : "-");
data.DataRows.Add(row);
}
var archivo = StaticFunctions.GenerateExcel(data, "Usuarios", filter.campo, filter.usuario, filter.periodo);
return(new Documento()
{
nombre = archivo
});
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public byte[] GenerateExcel(SLExcelData data)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument
.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = document.WorkbookPart
.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
// Add header
UInt32 rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, header ?? string.Empty));
}
if (data.Headers.Count > 0)
{
// Add the column configuration if available
if (data.ColumnConfigurations != null)
{
var columns = (Columns)data.ColumnConfigurations.Clone();
worksheetPart.Worksheet
.InsertAfter(columns, worksheetPart
.Worksheet.SheetFormatProperties);
}
}
// Add sheet data
foreach (var rowData in data.DataRows)
{
cellIdex = 0;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var callData in rowData)
{
var cell = CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, callData ?? string.Empty);
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
document.Close();
return(stream.ToArray());
}
public SLExcelData ReadExcel(HttpPostedFileBase file)
{
var data = new SLExcelData();
// Check if the file is excel
if (file.ContentLength <= 0)
{
data.Status.Message = "You uploaded an empty file";
return data;
}
if (file.ContentType
!= "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
data.Status.Message
= "Please upload a valid excel file of version 2007 and above";
return data;
}
// Open the excel document
WorkbookPart workbookPart; List<Row> rows;
try
{
var document = SpreadsheetDocument.Open(file.InputStream, false);
workbookPart = document.WorkbookPart;
var sheets = workbookPart.Workbook.Descendants<Sheet>();
var sheet = sheets.First();
data.SheetName = sheet.Name;
var workSheet = ((WorksheetPart)workbookPart
.GetPartById(sheet.Id)).Worksheet;
var columns = workSheet.Descendants<Columns>().FirstOrDefault();
data.ColumnConfigurations = columns;
var sheetData = workSheet.Elements<SheetData>().First();
rows = sheetData.Elements<Row>().ToList();
}
catch (Exception e)
{
data.Status.Message = "Unable to open the file";
return data;
}
// Read the header
if (rows.Count > 0)
{
var row = rows[0];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
data.Headers.Add(text);
}
}
// Read the sheet data
if (rows.Count > 1)
{
for (var i = 1; i < rows.Count; i++)
{
var dataRow = new List<string>();
data.DataRows.Add(dataRow);
var row = rows[i];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
dataRow.Add(text);
}
}
}
return data;
}
