private void MostrarDatosProducto()
{
string rutaArchivoCompleta = PathA + "Inventario.xlsx";
txtCodigo.Text = Codigo;
SLDocument ArchivoExcel = new SLDocument(rutaArchivoCompleta);
int iRow = 2;
while (!string.IsNullOrEmpty(ArchivoExcel.GetCellValueAsString(iRow, 1)))
{
if (Codigo == ArchivoExcel.GetCellValueAsString(iRow, 1)) //Recorro el archivo y pregunto si existe el IDProducto
{
Fila = iRow; //Obtengo la fila del producto que se va a extraer
}
iRow++;
}
txtDescripcionProducto.Text = ArchivoExcel.GetCellValueAsString(Fila, 3);
double precio = ArchivoExcel.GetCellValueAsDouble(Fila, 4);
txtImporte.Text = precio.ToString("C");
if (txtDescripcionProducto.Text != "")
{
btnActualizarCantidad.Enabled = true;
}
}
private string CreateXlsMasterEmployee()
{
//get data
List <EmployeeDto> employee = _employeeBLL.GetEmployee();
var listData = Mapper.Map <List <EmployeeItem> >(employee);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Master Employee");
slDocument.MergeWorksheetCells(1, 1, 1, 18);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelMasterEmployee(slDocument);
//create data
slDocument = CreateDataExcelMasterEmployee(slDocument, listData);
var fileName = "Master Data Employee " + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "ID Card");
slDocument.SetCellValue(iRow, 2, "Login As");
slDocument.SetCellValue(iRow, 3, "Manager");
slDocument.SetCellValue(iRow, 4, "Printed Name");
slDocument.SetCellValue(iRow, 5, "Phone");
slDocument.SetCellValue(iRow, 6, "Email");
slDocument.SetCellValue(iRow, 7, "Title");
slDocument.SetCellValue(iRow, 8, "Address");
slDocument.SetCellValue(iRow, 9, "Active");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 9, headerStyle);
return(slDocument);
}
private void button8_Click(object sender, EventArgs e)
{
var sl = new SLDocument("MeinZeug.xlsx");
//todo
//sl.GetCells().SelectMany(x => x.Value).Where(x => x.Value is decimal).Sum(x => x.Value. as decimal);
}
private SLDocument CreateHeaderExcelMasterReason(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "Document Type");
slDocument.SetCellValue(iRow, 2, "Reason");
slDocument.SetCellValue(iRow, 3, "Penalty");
slDocument.SetCellValue(iRow, 4, "Created Date");
slDocument.SetCellValue(iRow, 5, "Created By");
slDocument.SetCellValue(iRow, 6, "Modified Date");
slDocument.SetCellValue(iRow, 7, "Modified By");
slDocument.SetCellValue(iRow, 8, "Status");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 8, headerStyle);
return(slDocument);
}
private SLDocument CreateDataExcelCRF(SLDocument slDocument, List <TraCafItemDetails> listData)
{
int iRow = 3; //starting row data
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.DocumentNumber);
slDocument.SetCellValue(iRow, 2, data.DocumentStatusString);
slDocument.SetCellValue(iRow, 3, data.EmployeeId);
slDocument.SetCellValue(iRow, 4, data.EmployeeName);
slDocument.SetCellValue(iRow, 5, data.SirsNumber);
slDocument.SetCellValue(iRow, 6, data.PoliceNumber);
//slDocument.SetCellValue(iRow, 6, data.VehicleUsage);
//slDocument.SetCellValue(iRow, 7, data.EffectiveDate.HasValue ? data.EffectiveDate.Value.ToString("dd-MMM-yyyy hh:mm:ss") : "");
slDocument.SetCellValue(iRow, 7, data.ModifiedBy);
slDocument.SetCellValue(iRow, 8, data.ModifiedDate == null ? "" : data.ModifiedDate.Value.ToString("dd-MMM-yyyy hh:mm:ss"));
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
slDocument.AutoFitColumn(1, 8);
slDocument.SetCellStyle(3, 1, iRow - 1, 8, valueStyle);
return(slDocument);
}
void SetData(SLDocument document, ProjectReportViewModel reportVm)
{
for (int i = 0; i < reportVm.Rows.Count; i++)
{
var currentRow = reportVm.Rows[i];
int rownum = i + 2;
document.SetCellValue(rownum, 1, currentRow.WorkObject);
document.SetCellValue(rownum, 2, currentRow.ProjectCode);
document.SetCellValue(rownum, 3, currentRow.ProjectName);
document.SetCellValue(rownum, 4, currentRow.Name);
for (int j = 0; j < currentRow.Hours.Count; j++)
{
document.SetCellValue(rownum, j + 5, currentRow.Hours[j]);
}
document.SetCellValue(rownum, reportVm.MyColumns.Count + 5, currentRow.TotalHours);
document.SetCellValue(rownum, reportVm.MyColumns.Count + 6, currentRow.Money);
}
}
static private bool listarArchivos(List <string> archivos, List <string> hojas, List <string> renglon, List <string> columna)
{//http://spreadsheetlight.com/sample-code/
System.IO.IOException res = null;
SLDocument sl = null;
try
{
sl = new SLDocument(@"..\archivos.xlsx", "Hoja1");
int cont = 1;
string archivo;
while (!string.IsNullOrWhiteSpace(archivo = sl.GetCellValueAsString(++cont, 1)))
{
archivos.Add(archivo);
hojas.Add(sl.GetCellValueAsString(cont, 2));
renglon.Add(sl.GetCellValueAsString(cont, 3));
columna.Add(sl.GetCellValueAsString(cont, 4));
}
sl.CloseWithoutSaving();
}
catch (System.IO.IOException e)
{
res = e;
Console.WriteLine("el archivo \"archivos.xlsx\" no existe o se encuentra abierto");
Console.ReadKey();
}
return(res == null);
}
static private bool llenarFormatos(List <string> alLista, List <string> archivos, List <string> hojas, List <string> renglon, List <string> columna)
{//http://spreadsheetlight.com/sample-code/
System.IO.IOException res = null;
SLDocument sl = null;
for (int i = 0; i < archivos.Count; i++)
{
try
{
sl = new SLDocument(@"..\" + archivos.ElementAt(i) + ".xlsx", hojas.ElementAt(i));
int alNumero = int.Parse(renglon.ElementAt(i));
for (int j = 0; j < alLista.Count; j++)
{
sl.SetCellValue(alNumero++, int.Parse(columna.ElementAt(i)), alLista.ElementAt(j));
}
string strNombre;
while (!string.IsNullOrWhiteSpace(strNombre = sl.GetCellValueAsString(alNumero, int.Parse(columna.ElementAt(i)))))
{
sl.SetCellValue(alNumero++, int.Parse(columna.ElementAt(i)), "");
}
sl.Save();
}
catch (System.IO.IOException e)
{
res = e;
Console.WriteLine("el archivo \"" + archivos.ElementAt(i) + ".xlsx\" no existe o se encuentra abierto");
Console.ReadKey();
}
}
return(res == null);
}
public static void SetCellHorizoltalAllign(this SLDocument excelDocument, int rowIndex, int columnIndex, HorizontalAlignmentType type)
{
var cellStyle = excelDocument.GetCellStyle(rowIndex, columnIndex);
switch (type)
{
case HorizontalAlignmentType.Left:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
break;
case HorizontalAlignmentType.Right:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
break;
case HorizontalAlignmentType.Center:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
break;
case HorizontalAlignmentType.Justify:
cellStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Justify);
break;
}
excelDocument.SetCellStyle(rowIndex, columnIndex, cellStyle);
}
private SLDocument CreateHeaderExcelMasterDelegation(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "Mst Delegation ID");
slDocument.SetCellValue(iRow, 2, "Employee ID From");
slDocument.SetCellValue(iRow, 3, "Employee Name From");
slDocument.SetCellValue(iRow, 4, "Employee ID To");
slDocument.SetCellValue(iRow, 5, "Employee Name To");
slDocument.SetCellValue(iRow, 6, "Date From");
slDocument.SetCellValue(iRow, 7, "Date To");
slDocument.SetCellValue(iRow, 8, "Is Complaint Form");
slDocument.SetCellValue(iRow, 9, "Created By");
slDocument.SetCellValue(iRow, 10, "Created Date");
slDocument.SetCellValue(iRow, 11, "Modified By");
slDocument.SetCellValue(iRow, 12, "Modified Date");
slDocument.SetCellValue(iRow, 13, "Status");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 13, headerStyle);
return(slDocument);
}
private SLDocument CreateHeaderCfmIdle(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "Police Number");
slDocument.SetCellValue(iRow, 2, "Manufacturer");
slDocument.SetCellValue(iRow, 3, "Model");
slDocument.SetCellValue(iRow, 4, "Series");
slDocument.SetCellValue(iRow, 5, "Body Type");
slDocument.SetCellValue(iRow, 6, "Colour");
slDocument.SetCellValue(iRow, 7, "Group Level");
slDocument.SetCellValue(iRow, 8, "Start Contract");
slDocument.SetCellValue(iRow, 9, "End Contract");
slDocument.SetCellValue(iRow, 10, "Supplier");
slDocument.SetCellValue(iRow, 11, "Cost Center");
slDocument.SetCellValue(iRow, 12, "Start Idle");
slDocument.SetCellValue(iRow, 13, "End Idle");
slDocument.SetCellValue(iRow, 14, "Idle Duration");
slDocument.SetCellValue(iRow, 15, "Monthly Installment");
slDocument.SetCellValue(iRow, 16, "Total");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 16, headerStyle);
return(slDocument);
}
private SLDocument CreateDataExcelMasterSysAccess(SLDocument slDocument, List <SysAccessItem> listData)
{
int iRow = 3; //starting row data
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.RoleName);
slDocument.SetCellValue(iRow, 2, data.RoleNameAlias);
slDocument.SetCellValue(iRow, 3, data.ModulId.ToString());
slDocument.SetCellValue(iRow, 4, data.ModulName);
slDocument.SetCellValue(iRow, 5, data.ReadAccessData == true ? "Yes" : "No");
slDocument.SetCellValue(iRow, 6, data.WriteAccessData == true ? "Yes" : "No");
slDocument.SetCellValue(iRow, 7, data.UploadAccess == true ? "Yes" : "No");
slDocument.SetCellValue(iRow, 8, data.CreatedDate.ToString("dd-MMM-yyyy HH:mm:ss"));
slDocument.SetCellValue(iRow, 9, data.CreatedBy);
slDocument.SetCellValue(iRow, 10, data.ModifiedDate == null ? "" : data.ModifiedDate.Value.ToString("dd-MMM-yyyy HH:mm:ss"));
slDocument.SetCellValue(iRow, 11, data.ModifiedBy);
slDocument.SetCellValue(iRow, 12, data.IsActive == true ? "Active" : "InActive");
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
slDocument.AutoFitColumn(1, 12);
slDocument.SetCellStyle(3, 1, iRow - 1, 12, valueStyle);
return(slDocument);
}
private SLDocument CreateHeaderExcelMasterSysAccess(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "Role Name");
slDocument.SetCellValue(iRow, 2, "Role Name Alias");
slDocument.SetCellValue(iRow, 3, "Modul Id");
slDocument.SetCellValue(iRow, 4, "Modul Name");
slDocument.SetCellValue(iRow, 5, "Read Access");
slDocument.SetCellValue(iRow, 6, "Write Access");
slDocument.SetCellValue(iRow, 7, "Upload Access");
slDocument.SetCellValue(iRow, 8, "Created Date");
slDocument.SetCellValue(iRow, 9, "Created By");
slDocument.SetCellValue(iRow, 10, "Modified Date");
slDocument.SetCellValue(iRow, 11, "Modified By");
slDocument.SetCellValue(iRow, 12, "Status");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 12, headerStyle);
return(slDocument);
}
private string CreateXlsPersonal()
{
List <TraCafDto> CRF = new List <TraCafDto>(); //_CRFBLL.GetCRF();
CRF = _cafBLL.GetCafPersonal(CurrentUser);
var listData = Mapper.Map <List <TraCafItemDetails> >(CRF);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Personal Dashboard CAF");
slDocument.MergeWorksheetCells(1, 1, 1, 8);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelCRF(slDocument);
//create data
slDocument = CreateDataExcelCRF(slDocument, listData);
var fileName = "Data_CAF" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private SLDocument CreateHeaderExcelDashboard(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "PO Number");
slDocument.SetCellValue(iRow, 2, "PO Line");
slDocument.SetCellValue(iRow, 3, "GR Date");
slDocument.SetCellValue(iRow, 4, "Police Number");
slDocument.SetCellValue(iRow, 5, "Start Contract");
slDocument.SetCellValue(iRow, 6, "End Contract");
slDocument.SetCellValue(iRow, 7, "Termination Date");
slDocument.SetCellValue(iRow, 8, "All Quantity");
//slDocument.SetCellValue(iRow, 9, "CRF No");
//slDocument.SetCellValue(iRow, 10, "CRF Status");
slDocument.SetCellValue(iRow, 9, "Quantity Remaining");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 9, headerStyle);
return(slDocument);
}
private SLDocument CreateHeaderExcelCRF(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "CAF No");
slDocument.SetCellValue(iRow, 2, "CRF Status");
slDocument.SetCellValue(iRow, 3, "Employee ID");
slDocument.SetCellValue(iRow, 4, "Employee Name");
slDocument.SetCellValue(iRow, 5, "SIRS Number");
slDocument.SetCellValue(iRow, 6, "Police Number");
slDocument.SetCellValue(iRow, 7, "Modified By");
slDocument.SetCellValue(iRow, 8, "Modified Date");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 8, headerStyle);
return(slDocument);
}
private SLDocument CreateDataExcelDashboard(SLDocument slDocument, List <RptAutoGrItem> listData)
{
int iRow = 3; //starting row data
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.PoNumber);
slDocument.SetCellValue(iRow, 2, data.PoLine);
slDocument.SetCellValue(iRow, 3, data.GrDateString);
slDocument.SetCellValue(iRow, 4, data.PoliceNumber);
slDocument.SetCellValue(iRow, 5, data.StartContractString);
slDocument.SetCellValue(iRow, 6, data.EndContractString);
slDocument.SetCellValue(iRow, 7, data.TerminationDateString);
slDocument.SetCellValue(iRow, 8, data.QtyAutoGr);
//slDocument.SetCellValue(iRow, 9, data.);
//slDocument.SetCellValue(iRow, 10, data.CRFStatus);
slDocument.SetCellValue(iRow, 9, data.QtyRemaining);
//slDocument.SetCellValue(iRow, 10, data.ModifiedDate == null ? "" : data.ModifiedDate.Value.ToString("dd-MMM-yyyy hh:mm:ss"));
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
slDocument.AutoFitColumn(1, 9);
slDocument.SetCellStyle(3, 1, iRow - 1, 9, valueStyle);
return(slDocument);
}
public string CreateFile(ProjectReportViewModel reportVm)
{
string path = AppDomain.CurrentDomain.BaseDirectory + "Content/";
string fileName = Path.GetRandomFileName();//"Отчёт по сотрудникам.xlsx";
string fullPath = path + fileName;
SLDocument sl = new SLDocument();
// set a boolean at "A1"
//sl.SetCellValue("A1", true);
SetColumns(sl, reportVm);
SetData(sl, reportVm);
try
{
if (File.Exists(fullPath))
{
File.Delete(fullPath);
}
}
catch (Exception)
{
var random = new Random();
fullPath = path + random.Next() + "_" + fileName;
}
sl.SaveAs(fullPath);
return(fullPath);
}
public async Task ReadExcelData(string filePath)
{
SLDocument document = new SLDocument(filePath);
//await ReadMovesetExcelData(document);
await ReadPokemonExcelData(document);
}
private void BtnReporte_Click(object sender, RoutedEventArgs e)
{
string datetimeActual = DateTime.Now.ToString("yyyy-MM-ddThh_mm_ss");
string path;
if (banderaFlujoVentas == 0)
{
path = "reporte_ventas_" + datetimeActual + ".xlsx";
}
else if (banderaFlujoVentas == 1)
{
path = "reporte_pedidos_" + datetimeActual + ".xlsx";
}
else if (banderaFlujoVentas == 2)
{
path = "reporte_observaciones_" + datetimeActual + ".xlsx";
}
else
{
path = AppDomain.CurrentDomain.BaseDirectory + "miexcel.xlsx";
//path = AppDomain.CurrentDomain.BaseDirectory + "miexcel.xlsx";
}
connection.Open();
SLDocument documentoExcel = new SLDocument();
connection.Close();
documentoExcel.ImportDataTable(1, 1, data_flujo_ventas, true);
documentoExcel.SaveAs(path);
}
public static string LeerXls(string full)
{
SLDocument documento = new SLDocument(full);
SLWorksheetStatistics propiedades = documento.GetWorksheetStatistics();
int ultimaColumna = propiedades.EndColumnIndex;
ArrayList cabeza = new ArrayList();
for (long i = 0; i < (ultimaColumna); i++)
{
string num = Letracolumna(i);
string ca = documento.GetCellValueAsString(num + 1);
cabeza.Add(ca);
}
List <string> re = new List <string>();
foreach (string sw in cabeza)
{
if (!re.Contains(sw))
{
re.Add(sw);
}
}
string cad = re[0];
for (int i = 1; i < (re.Count); i++)
{
cad = cad + "," + re[i];
}
Console.WriteLine(cad);
return(cad);
}
public List <KSolutionGroup> Convert(string excelFilePath)
{
_sl = new SLDocument(excelFilePath);
var solutionGroupList = GetSolutionGroups();
foreach (var solutionGroup in solutionGroupList)
{
foreach (var solution in solutionGroup.Solution)
{
foreach (var project in solution.Project.Where(p => p is KDataStoreProject)
.Select(p => p as KDataStoreProject))
{
project.MockView.AddRange(GetMockViews(solution.SolutionName));
project.TableType.AddRange(GetTableTypes(solution.SolutionName));
project.StoredProcedure.AddRange(GetStoredProcedures(solution.SolutionName));
}
foreach (var project in solution.Project.Where(p => p is KGrpcProject).Select(p => p as KGrpcProject))
{
project.ProtoFile.Add(GetProtoFile(solution.SolutionName));
}
}
}
return(solutionGroupList);
}
public void Documentar(string novoDocumento)
{
if (this._metaDb.Count() == 0)
{
this.PopMetaDb();
}
SLDocument sl = new SLDocument(novoDocumento, "Planilha1");
string campoDW;
string tabelaDW;
DocInfo doc;
bool docChanged = false;
for (int y = 7; y < 500; y++)
{
campoDW = sl.GetCellValueAsString($"D{y}");
tabelaDW = sl.GetCellValueAsString($"E{y}");
doc = this._metaDb.Where(m => m.TabelaDW == tabelaDW && m.CampoDW == campoDW).FirstOrDefault();
if (doc != null)
{
sl.SetCellValue($"F{y}", doc.CampoOrigem);
sl.SetCellValue($"H{y}", doc.TabelaOrigem);
sl.SetCellValue($"G{y}", doc.BaseOrigem);
docChanged = true;
}
}
if (docChanged)
{
sl.Save();
}
}
public static List <Constants> GetModifiedConstantDefinitions(List <string> dpmIds)
{
List <Constants> allconstsants = new List <Constants>();
List <Constants> constsants = new List <Constants>();
if (File.Exists(_rootFilePath + "//Resources//Constants.xlsx"))
{
using (SLDocument inputExcel = new SLDocument(_rootFilePath + "//Resources//Constants.xlsx"))
{
for (int i = 2; i <= 57407; i++)
{
string definition = inputExcel.GetCellValueAsString(i, 6);
string dpmID = ClassHelpers.Between(definition, "constant ", "=eba_met");
allconstsants.Add(new Constants
{
Definitions = definition,
DPMID = dpmID
});
}
}
foreach (var item in dpmIds)
{
var aaaa = allconstsants.Where(x => x.DPMID == item).FirstOrDefault();
if (aaaa != null)
{
}
constsants.Add(allconstsants.Where(x => x.DPMID == item).FirstOrDefault());
}
}
return(constsants);
}
public void CrearExcelIA() //Metodo para agregar los articulos que estan agotados por PRIMERA VEZ, es decir, que su N° Items = 0
{
try
{
//creamos el objeto SLDocument el cual creara el excel
SLDocument sl = new SLDocument();
//creamos las celdas en diagonal
//utilizando la función setcellvalue pueden navegar sobre el documento
//primer parametro es la fila el segundo la columna y el tercero el dato de la celda
//for (int i = 1; i <= 10; ++i) sl.SetCellValue(i, i, "patito " + i);
sl.SetCellValue(1, 1, "Código");
sl.SetCellValue(1, 2, "N° Items");
sl.SetCellValue(1, 3, "Descripción");
sl.SetCellValue(2, 1, Codigo);
sl.SetCellValue(2, 2, NumItems);
sl.SetCellValue(2, 3, Descripcion);
//Guardar como, y aqui ponemos la ruta de nuestro archivo
sl.SaveAs(rutaArchivoCompleta);
}
catch (Exception ex)
{
MessageBox.Show("¡Algo salió mal :(!" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private string CreateXlsFile()
{
//get data
var listData = Mapper.Map <List <POAViewDetailModel> >(_poaBll.GetAll());
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Master POA");
slDocument.MergeWorksheetCells(1, 1, 1, 9);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcel(slDocument);
//create data
slDocument = CreateDataExcel(slDocument, listData);
var fileName = "MasterData_MasterPoa" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
public List <string> OpenRead(string filename)
{
try
{
SLDocument slDoc = new SLDocument(filename);
lMACs_.Clear();
for (int i = 0; i < 1000; i++)
{
string cellIndex = "A" + (i + 1).ToString();
string curCell = slDoc.GetCellValueAsString(cellIndex);
if (curCell == string.Empty)
{
break;
}
lMACs_.Add(curCell);
Trace.WriteLine(string.Format("Cell value: {0} = {1}", cellIndex, curCell));
}
if (lMACs_.Count > 0)
{
return(lMACs_);
}
}
catch (SystemException ex)
{
Trace.WriteLine(string.Format("{0}(): {1}", System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message), "ERROR");
}
return(null);
}
private void ExportSimpleExcel()
{
if (_openProject == null)
{
return;
}
ExportFileDialog.Filter = "Archivos Excel|*.xlsx";
var result = ExportFileDialog.ShowDialog(this);
if (result == DialogResult.OK)
{
var sl = new SLDocument();
var row = 1;
foreach (var str in _openProject.Strings.Where(x => x.Visible))
{
sl.SetCellValue(row, 1, $"'{str.Original}");
sl.SetCellValue(row, 2, $"'{str.Translation}");
row++;
}
sl.SaveAs(ExportFileDialog.FileName);
}
}
private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 3;
slDocument.SetCellValue(iRow, 1, "ID");
slDocument.SetColumnWidth(1, 10);
//slDocument.MergeWorksheetCells(iRow, 1, iRow + 1, 1);
slDocument.SetCellValue(iRow, 2, "ID Anggota");
slDocument.SetColumnWidth(2, 15);
//slDocument.MergeWorksheetCells(iRow, 2, iRow + 1, 2);
slDocument.SetCellValue(iRow, 3, "Nama Anggota");
slDocument.SetColumnWidth(3, 25);
//slDocument.MergeWorksheetCells(iRow, 3, iRow + 1, 3);
slDocument.SetCellValue(iRow, 4, "Tanggal Gabung");
slDocument.SetColumnWidth(4, 15);
//slDocument.MergeWorksheetCells(iRow, 4, iRow + 1, 4);
slDocument.SetCellValue(iRow, 5, "Status");
slDocument.SetColumnWidth(5, 15);
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
//headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGreen, System.Drawing.Color.LightGreen);
headerStyle.SetWrapText(true);
headerStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
headerStyle.Font.FontSize = 10;
slDocument.SetCellStyle(3, 1, iRow, 5, headerStyle);
return(slDocument);
}