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) }); }