async Task ExportToExcel()
{
var fileName = $"Contacts-{Guid.NewGuid()}.xlsx";
string filepath = excelService.GenerateExcel(fileName);
var data = new ExcelStructure
{
Headers = new List <string>()
{
"Name", "Lastname", "Telephone"
}
};
foreach (var item in contacts)
{
data.Values.Add(new List <string>()
{
item.Name, item.LastName, item.Telephone
});
}
excelService.InsertDataIntoSheet(filepath, "Contacts", data);
await Launcher.OpenAsync(new OpenFileRequest()
{
File = new ReadOnlyFile(filepath)
});
}
public void InsertDataIntoSheet(string fileName, string sheetName, ExcelStructure data)
{
Environment.SetEnvironmentVariable("MONO_URI_DOTNETRELATIVEORABSOLUTE", "true");
using (var document = SpreadsheetDocument.Open(fileName, true))
{
var wbPart = document.WorkbookPart;
var sheets = wbPart.Workbook.GetFirstChild <Sheets>().
Elements <Sheet>().FirstOrDefault().
Name = sheetName;
var part = wbPart.WorksheetParts.First();
var sheetData = part.Worksheet.Elements <SheetData>().First();
var row = sheetData.AppendChild(new Row());
foreach (var header in data.Headers)
{
row.Append(ConstructCell(header, CellValues.String));
}
foreach (var value in data.Values)
{
var dataRow = sheetData.AppendChild(new Row());
foreach (var dataElement in value)
{
dataRow.Append(ConstructCell(dataElement, CellValues.String));
}
}
wbPart.Workbook.Save();
}
}
public static string GetCommaSeperatedColumnNames(ExcelStructure objexceStruct)
{
return("SubCategory" + Constants.CSV_DELIMITER +
"Brand" + Constants.CSV_DELIMITER +
"ModelNumber" + Constants.CSV_DELIMITER +
"Price" + Constants.CSV_DELIMITER +
"Offer" + Constants.CSV_DELIMITER +
"FlipKart" + Constants.CSV_DELIMITER +
"Completed" + Constants.CSV_DELIMITER +
"Comment");
}
public static string GetCommaSepearatedString(ExcelStructure objexceStruct)
{
return(objexceStruct.SubCategory + Constants.CSV_DELIMITER +
objexceStruct.Brand + Constants.CSV_DELIMITER +
objexceStruct.ModelNumber + Constants.CSV_DELIMITER +
objexceStruct.Price + Constants.CSV_DELIMITER +
objexceStruct.Offer + Constants.CSV_DELIMITER +
objexceStruct.FlipKart + Constants.CSV_DELIMITER +
objexceStruct.Completed + Constants.CSV_DELIMITER +
objexceStruct.Comment);
}
public static void PopulateExcelData(String fileName, ref List <ExcelStructure> lobjExcelStruct, Store objStore)
{
CExcelController.InitializeExcel(fileName);
ExcelWorksheet sheet = CExcelController.GetWorkSheet(1);
var start = sheet.Dimension.Start;
var end = sheet.Dimension.End;
//Do not consider row 1 as its the header
for (int Row = start.Row + 1; Row <= end.Row; Row++)
{
ExcelStructure objExcel = new ExcelStructure();
for (int Col = start.Column; Col <= end.Column; Col++)
{
string name = sheet.Cells[start.Row, Col].GetValue <string>();
if (name == null)
{
continue;
}
dynamic dValue = sheet.Cells[Row, Col].Value;
String value = null;
if (dValue != null)
{
value = dValue.ToString();
}
else
{
continue;
}
Excelutilities.PopulateStructure <ExcelStructure>(name, value, ref objExcel);
}
objExcel.objStore = objStore;
lobjExcelStruct.Add(objExcel);
}
CExcelController.CloseExcel();
}
public void RunExcel(string fileName, string inputFolder, string outputFolder)
{
try
{
//Get store id
string storeid = fileName.Split('-').First();
//create csv with column names
ExcelStructure objExcel = new ExcelStructure();
Logger.WriteToCSVFile(CommonMethods.GetCommaSeperatedColumnNames(objExcel), fileName, outputFolder);
Store objStore = DBGetInterface.GetStore(storeid);
if (objStore == null)
{
throw new Exception("Store id is null for excel : " + fileName);
}
//Run the query
List <ExcelStructure> lobjExcel = new List <ExcelStructure>();
CommonMethods.PopulateExcelData(inputFolder + "\\" + fileName, ref lobjExcel, objStore);
foreach (ExcelStructure objExcelStruct in lobjExcel)
{
SubCategory objSubCateogry = DBGetInterface.GetSubCategoryNode(objExcelStruct.SubCategory);
Brand objBrand = DBGetInterface.GetBrandNode(objSubCateogry, objExcelStruct.Brand);
ExcelStructure objexcelpop = CommonMethods.SaveToDB(objExcelStruct, objStore, objSubCateogry, objBrand, m_xmlNode.AppServer.Server, m_xmlNode.AppServer.Port);
Logger.WriteToCSVFile(CommonMethods.GetCommaSepearatedString(objexcelpop), fileName, outputFolder);
}
}
catch (Exception ex)
{
Logger.WriteToLogFile(ex.Message, Constants.FTPSERVER_OUTPUT_ERROR_LOGS, outputFolder);
}
}
public static ExcelStructure SaveToDB(ExcelStructure objExcelStruct, Store objStore, SubCategory objSubCategory, Brand objBrand, string server, string port, GridViewRowInfo row = null)
{
try
{
if (objStore == null)
{
throw new Exception("Please select a store .....");
}
if (objSubCategory == null)
{
throw new Exception("Subcateogry do not match or is empty");
}
if (objBrand == null)
{
throw new Exception("Please add brand for this subcategory ir brand is null or not matching");
}
ItemDescription objItemDescr = DBGetInterface.GetItemDescription(objBrand, objExcelStruct.ModelNumber);
if (objItemDescr == null)
{
//Call flipkart scrapper
CFlipkartScrapper objscrapper = new CFlipkartScrapper();
ItemDescription objItemdesc = ItemFactory.GetItem(objSubCategory);
if (String.IsNullOrEmpty(objExcelStruct.FlipKart))
{
//check if the item link is already scrapped, find in csv
string strFilePath = m_ResourcesFolder + "//" + m_CategoryMappingTxt;
if (System.IO.File.Exists(strFilePath))
{
//File exists
//read the file and check for that file in LinkFodler
try
{
using (System.IO.StreamReader sw = new System.IO.StreamReader(strFilePath))
{
string line = String.Empty;
while ((line = sw.ReadLine()) != null)
{
bool bflag = false;
string[] cat = line.Split(' ');
if (cat[0].Equals(objSubCategory.SubCategoryID.ToString(), StringComparison.InvariantCultureIgnoreCase))
{
//search model number in that subcategory file
string file = Constants.LINK_EXTRACTED_FOLDER + "//" + cat[1] + ".csv";
//search in this csv file
List <List <string> > lmap = Utilities.GetCSVSheet(file);
foreach (List <string> product in lmap)
{
if (product.ElementAt(0).Equals(objExcelStruct.ModelNumber, StringComparison.InvariantCultureIgnoreCase))
{
objExcelStruct.FlipKart = product.ElementAt(2);
bflag = true;
break;
}
}
}
if (bflag)
{
break;
}
}
}
}
catch (Exception ex)
{
}
}
}
objscrapper.SetHTMLDocument(objExcelStruct.FlipKart);
objscrapper.ExtractData(ref objItemdesc);
string image = objItemdesc.image;
objItemdesc.image = null;
//Now do the following
//Create item, create item description.
Item objItem = new Item();
objItem.Price = Convert.ToUInt32(objExcelStruct.Price);
objItem.OfferDescription = objExcelStruct.Offer;
ItemWrapper objWrap = new ItemWrapper();
objWrap.objBrand = objBrand;
objWrap.objStore = objStore;
objWrap.objItemDescription = objItemdesc;
objWrap.objItem = objItem;
DBAddinterface.CreateItemNode(objWrap);
//Copy image
//Create a seperate thread for it as image copying is a tedious process
//Copy image to local machine and then copy it to tomcat machine
//build path
string imageDir = Utilities.GetImageDir(objItemdesc.getLabel());
Utilities.CreateFolder(imageDir);
string imagePath = Utilities.GetImagePath(imageDir, objItemdesc.id);
ImageController.GetImageFromUrlAndSave(image, imagePath);
//Create a seperate thread for this to do
Thread SaveImageThread = new Thread(() => SaveImageToRemoteMachineThread(imageDir, Utilities.AppendImageType(objItemdesc.id), server, port));
SaveImageThread.Start();
objExcelStruct.Completed = Constants.COMPLETED_DONE;
objExcelStruct.Comment = String.Empty;
}
else
{
objExcelStruct.Comment = Constants.ALREADY_IN_DB;
//Just update the price and offer of the item
Item objItem = DBGetInterface.GetItem(objStore, objItemDescr);
if (objItem == null)
{
//Add this node
objItem = new Item();
objItem.Price = Convert.ToUInt32(objExcelStruct.Price);
objItem.OfferDescription = objExcelStruct.Offer;
ItemWrapper objWrap = new ItemWrapper();
objWrap.objBrand = objBrand;
objWrap.objStore = objStore;
objWrap.objItemDescription = objItemDescr;
objWrap.objItem = objItem;
DBAddinterface.CreateItemNode(objWrap);
objExcelStruct.Completed = Constants.COMPLETED_ADDED;
}
else
{
//Check for any updates
bool bUpdate = false;
if (objItem.Price != Convert.ToUInt32(objExcelStruct.Price))
{
objItem.Price = Convert.ToUInt32(objExcelStruct.Price);
bUpdate = true;
}
string offer = String.Empty;
if (objExcelStruct.Offer != null)
{
offer = objExcelStruct.Offer;
}
if (!objItem.OfferDescription.Equals(offer, StringComparison.InvariantCultureIgnoreCase))
{
objItem.OfferDescription = objExcelStruct.Offer;
bUpdate = true;
}
if (bUpdate)
{
objItem.lastUpdated = Utilities.GetDateTimeInUnixTimeStamp();
DBUpdateInterface.UpdateNode <Item>(objItem);
objExcelStruct.Completed = Constants.COMPLETED_UPDATED;
}
else
{
objExcelStruct.Completed = Constants.COMPLETED_NOUPDATES;
}
}
}
}
catch (Exception ex)
{
objExcelStruct.Completed = Constants.COMPLETED_ERROR;
objExcelStruct.Comment = ex.Message;
}
if (row != null)
{
row.Cells["Completed"].Value = objExcelStruct.Completed;
row.Cells["Comment"].Value = objExcelStruct.Comment;
}
return(objExcelStruct);
}
/// <summary>
/// 读取Excel文档结构
/// </summary>
private void InitExcelStructure()
{
try
{
_excelStructure = new ExcelStructure()
{
Name = _fileName,
};
var sheets = _excelStructure.Sheets;
var excelTablesName = GetExcelTablesName(); //获取excel表头
if (excelTablesName == null || !excelTablesName.Any()) //1.0、无工作表
{
_checkReport.ExcelStructureError("无工作表", "没有找到工作表，请检查Excel是否有效!");
}
else
{
var sheetIndex = -1;
foreach (var sheetName in excelTablesName)
{
sheetIndex++;
if (string.IsNullOrWhiteSpace(sheetName))
_checkReport.ExcelStructureError("工作表名称无效", $"----第{sheetIndex + 1}个工作表名称无效!");
else
{
var aSheet = new ExcelSheet()
{
SheetIndex = sheetIndex,
SheetName = sheetName
};
var columns = aSheet.Columns;
var sheet = _workbook.GetSheet(sheetName);
var headerrow = sheet.GetRow(0);
if (headerrow == null)
{
_checkReport.ExcelStructureError("存在空工作表", $"----第{sheetIndex + 1}个工作表,为空工作表!");
}
else
{
var lastCellNum = headerrow.LastCellNum;
var continueNullHeader = 0;
for (int colIndex = 0; colIndex < lastCellNum; colIndex++)
{
try
{
var cell = headerrow.GetCell(colIndex);
if (cell == null || cell.ToString().Trim() == "") //1.3 存在为空列名称
{
//aCol.ColumnName = NullHeader + colIndex;
//cols.Add(aCol); 空列头不加入到表结构中
continueNullHeader++;
if (continueNullHeader > 2) break;
}
else
{
continueNullHeader = 0;
var value = cell.CellType == CellType.String ?
cell.StringCellValue.Trim() : cell.ToString().Trim();
if (columns.Any(f => f.ColumnName == Convert.ToString(value))) //1.4存在重复列名
{
_checkReport.ExcelStructureError("列名称重复", $"----[{sheetName}]工作表,存在多个名称为[{value}]的列!");
}
else
{
var aCol = new ExcelColumn()
{
ColumnIndex = colIndex,
ColumnName = value.Replace(" ", "").Replace("\n", "").Replace("\r", "")
};
columns.Add(aCol);
}
}
}
catch (Exception)
{
_checkReport.ExcelStructureError("列名称读取失败", $"----[{sheetName}]工作表,第{colIndex + 1},列名称读取失败!");
}
}
}
sheets.Add(aSheet);
}
}
}
}
catch (Exception ex)
{
_checkReport.ExcelStructureError("读取表结构出现错误", $"----[错误信息]{ex.Message}");
}
}
async Task ExportToExcel()
{
try
{
_RDetalleVenta _R_detalleVenta = new _RDetalleVenta()
{
fecha_inicio = App._fechaInicioFiltro,
fecha_final = App._fechaFinalFiltro
};
var json = JsonConvert.SerializeObject(_R_detalleVenta);
var content = new StringContent(json, Encoding.UTF8, "application/json");
HttpClient client = new HttpClient();
var result = await client.PostAsync("https://dmrbolivia.com/api_distribuidora/reportes/ReporteDetalleVenta.php", content);
var jsonR = await result.Content.ReadAsStringAsync();
var _dataRDV = JsonConvert.DeserializeObject <List <_RDetalleVenta> >(jsonR);
foreach (var item in _dataRDV)
{
_reporteDV.Add(new Models._RDetalleVenta
{
id_venta = item.id_venta,
nombre = item.nombre,
fecha = item.fecha,
codigo_c = item.codigo_c,
nombre_cliente = item.nombre_cliente,
razon_social = item.razon_social,
nit = item.nit,
telefono = item.telefono,
direccion_cliente = item.direccion_cliente,
geolocalizacion = item.geolocalizacion,
nombre_producto = item.nombre_producto,
precio_producto = item.precio_producto,
cantidad = item.cantidad,
sub_total = item.sub_total,
envases = item.envases,
tipo_venta = item.tipo_venta,
saldo = item.saldo,
estado = item.estado
});
}
}
catch (Exception err)
{
Console.WriteLine("###################################################" + err.ToString());
}
await Task.Delay(1000);
string fechahoy = DateTime.Today.ToString("dd-MM-yyyy");
string fecha = "DetalleDeVenta_" + fechahoy + ".xlsx";
string filePath = excelService.GenerateExcel(fecha);
var header = new List <string>()
{
"ID", "Nombre", "Fecha", "Codigo Cliente", "Nombre Cliente", "Razon Social", "Nit", "Telefono", "Direccion", "Geolocalizacion", "Producto", "Precio", "Cantidad",
"Sub Total", "Envases", "Tipo Venta", "Saldo", "Estado"
};
var data = new ExcelStructure();
data.Headers = header;
foreach (var publication in _reporteDV)
{
var row = new List <string>()
{
publication.id_venta.ToString(),
publication.nombre,
publication.fecha.ToString(),
publication.codigo_c.ToString(),
publication.nombre_cliente,
publication.razon_social,
publication.nit.ToString(),
publication.telefono.ToString(),
publication.direccion_cliente,
publication.geolocalizacion,
publication.nombre_producto,
publication.precio_producto.ToString(),
publication.cantidad.ToString(),
publication.sub_total.ToString(),
publication.envases.ToString(),
publication.tipo_venta,
publication.saldo.ToString(),
publication.estado,
};
data.Values.Add(row);
}
excelService.InsertDataIntoSheet(filePath, "Publications", data);
await Launcher.OpenAsync(new OpenFileRequest()
{
File = new ReadOnlyFile(filePath)
});
}
