/// <summary> /// 导出文件 /// </summary> /// <param name="fileUrl">保存位置</param> /// <param name="files">文件类型</param> public bool ExportFile(string fileUrl, string files) { if (dt == null && dt.Rows.Count > 0) { MessageCommon.ShowErr("结果集不能为空!"); } if (files == FileEnum.JsonFiles) { JsonUtilities jsonUtilities = new JsonUtilities(); return(jsonUtilities.SaveJson(dt, fileUrl)); } else if (files == FileEnum.ExcelFiles) { ExcelUtilities excelUtilities = new ExcelUtilities(); return(excelUtilities.ToExcel(dt, fileUrl)); } else if (files == FileEnum.XMLFiles) { XmlUtilities xmlUtilities = new XmlUtilities(); string xml = xmlUtilities.ConvertDataTableToXML(dt); return(xmlUtilities.SaveXml(xml, fileUrl)); } else { return(false); } }
/// <summary>Called after the user has selected a new met file.</summary> /// <param name="fileName">Name of the file.</param> public void OnBrowse(string fileName) { bool isCsv = Path.GetExtension(fileName) == ".csv"; this.weatherDataView.ShowConstantsFile(isCsv); if (this.weatherData.FullFileName != PathUtilities.GetAbsolutePath(fileName, this.explorerPresenter.ApsimXFile.FileName)) { if (ExcelUtilities.IsExcelFile(fileName)) { // Extend height of Browse Panel to show Drop Down for Sheet names this.weatherDataView.ShowExcelSheets(true); this.sheetNames = ExcelUtilities.GetWorkSheetNames(fileName); this.weatherDataView.PopulateDropDownData(this.sheetNames); // We want to attempt to update the table/summary now. This may fail if the // sheet name is incorrect/not set. this.WriteTableAndSummary(fileName); } else { // Shrink Browse Panel so that the sheet name dropdown doesn't show this.weatherDataView.ShowExcelSheets(false); // as a precaution, set this to nothing this.weatherData.ExcelWorkSheetName = string.Empty; this.WriteTableAndSummary(fileName); } } }
public MemoryStream ObtenerResumenMovimientosEnCSV(List <MovimientoDto> movimientos) { var resumenMovimientos = GenerarResumenMovimientos(movimientos); var memoryExcel = ExcelUtilities <MovimientoResumen> .Export(resumenMovimientos); return(memoryExcel); }
/// <summary>Called after the user has selected a new met file.</summary> /// <param name="fileName">Name of the file.</param> public void OnBrowse(string fileName) { if (this.weatherData.FullFileName != PathUtilities.GetAbsolutePath(fileName, this.explorerPresenter.ApsimXFile.FileName)) { if (Path.GetExtension(fileName) == ExcelUtilities.ExcelExtension) { //// Extend height of Browse Panel to show Drop Down for Sheet names this.weatherDataView.ShowExcelSheets(true); this.sheetNames = ExcelUtilities.GetWorkSheetNames(fileName); this.weatherDataView.PopulateDropDownData(this.sheetNames); // the following is not required here as it happens when the sheet name is changed // this.WriteTableAndSummary(fileName); } else { //// Shrink Browse Panel so that the sheet name dropdown doesn't show this.weatherDataView.ShowExcelSheets(false); // as a precaution, set this to nothing this.weatherData.ExcelWorkSheetName = string.Empty; this.WriteTableAndSummary(fileName); } } }
private static void SortProductCategories(string product, int columnNumber, int startIndex, int endIndex) { var excelPath = "C:/Tigoo/EmoKrastev.xlsx"; var wb = ExcelUtilities.CreateWorkbook(excelPath); try { var excelSheet = ExcelUtilities.SelectExcelSheet(wb, product); for (int i = startIndex; i <= endIndex; i++) { string str = excelSheet.Cells[i, columnNumber].Value.ToString(); List <string> splitCategories = str.Split('.').ToList(); if (splitCategories.Count < 2) { continue; } int lgIndex = Array.FindIndex(splitCategories.ToArray(), x => x.ToLower().Equals(product.ToLower())); splitCategories.RemoveAt(lgIndex); splitCategories.Insert(0, product); str = string.Join(".", splitCategories); excelSheet.Cells[i, columnNumber].Value = str; //Console.WriteLine("Line modified!"); } } catch (Exception e) { Console.WriteLine(e.Message); } finally { wb.Close(); } }
protected void GreetBotton_Click(object sender, EventArgs e) { HelloWorldLabel.Text = "Hello " + TextInput.Text; TextInput.Text = string.Empty; if (FileUpload1.HasFile) { string ext = Path.GetExtension(FileUpload1.FileName).ToLower(); fileName = FileUpload1.FileName; //getting the path of the file FileUpload1.SaveAs(Server.MapPath("~/MyFolder/" + FileUpload1.FileName)); string path = Server.MapPath("~/MyFolder/" + FileUpload1.FileName); //DataTable dt = new DataTable(); dt = ExcelUtilities.ReadDBFfile(path, monthIndex); //return something to UI gvExcelFile.DataSource = dt; gvExcelFile.DataBind(); Label1.Text = "File Uploaded: " + FileUpload1.FileName; } else { Label1.Text = "No File Uploaded."; } }
/// <summary> /// Creates a <see cref="ExcelInterop.PivotTable"/> in a new <see cref="ExcelInterop.Worksheet"/> that contains all <see cref="ExcelInterop.ModelTable"/>s in the active <see cref="ExcelInterop.Workbook"/>. /// </summary> private void CreatePivotTableForAllRelatedModelTables() { if (!CreatePivotTableCheckBox.Checked || PivotTablesComboBox.SelectedIndex != 0) { return; } var activeWorkbook = Globals.ThisAddIn.ActiveWorkbook; var workbookDataModelConnection = activeWorkbook.Connections.Cast <ExcelInterop.WorkbookConnection>().FirstOrDefault(wbConn => wbConn.Name == ExcelUtilities.WORKBOOK_DATA_MODEL_CONNECTION_NAME); if (workbookDataModelConnection == null) { return; } // Create a new Excel Worksheet for the PivotTable since it will contain all related tables in the Workbook's data model var currentWorksheet = activeWorkbook.CreateWorksheet(DATA_MODEL_PIVOT_TABLE_PROPOSED_NAME, true); if (currentWorksheet == null) { return; } ExcelUtilities.CreatePivotTable(workbookDataModelConnection, Globals.ThisAddIn.Application.ActiveCell, DATA_MODEL_PIVOT_TABLE_PROPOSED_NAME); }
public static DataTable ParseCompaniesData(DataTable filteredTable) { DataTable updatedTable = new DataTable(); try { updatedTable = filteredTable.Clone(); int i = 0; foreach (DataRow row in filteredTable.Rows) { bool isCompanyCrawled = false; DataRow dr = crawlRow(row, out isCompanyCrawled); DataRow _dr = updatedTable.NewRow(); updatedTable.ImportRow(dr); i++; if (isCompanyCrawled) { Console.WriteLine("Company Data Found :" + row[CompanyEnum.COMPANY_NAME]); ExcelUtilities.ExportToExcelOleDb(updatedTable, companiesRecordSheet, OutputFilePath, "updatedCompanies.xlsx", true); } } } catch (Exception ex) { LoggerUtility.Write("Data Parsing Failed ", ex.Message); ExcelUtilities.ExportToExcelOleDb(updatedTable, companiesRecordSheet, OutputFilePath, "updatedCompanies.xlsx", true); } return(updatedTable); }
/// <summary> /// Help link: //https://stackoverflow.com/a/25718674 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void DownloadTxt(object sender, EventArgs e) { if (FileUpload1.HasFile) { string ext = Path.GetExtension(FileUpload1.FileName).ToLower(); fileName = FileUpload1.FileName; //getting the path of the file FileUpload1.SaveAs(Server.MapPath(serverStorageLocatn + FileUpload1.FileName)); string path = Server.MapPath(serverStorageLocatn + FileUpload1.FileName); //DataTable dt = new DataTable(); dt = ExcelUtilities.ReadDBFfile(path, monthIndex); //DownloadTxt(fileName); result = ExcelUtilities.DumpDBFToTxt(dt).ToString(); string txtFilePath = monthIndex + "_" + fileName.Replace("DBF", "txt"); Response.Clear(); Response.AddHeader("content-disposition", "attachment; filename=" + serverStorageLocatn + txtFilePath); Response.AddHeader("content-type", "text/plain"); using (StreamWriter writer = new StreamWriter(Response.OutputStream)) { writer.WriteLine(result); } Response.End(); } else { Label1.Text = "No File Uploaded."; } }
/// <summary> /// Export link trackers for monthly click through /// </summary> /// <returns></returns> public HSSFWorkbook Exports(LinkTrackerMonthlyClickThroughSearchModel searchModel) { var data = SearchLinkTrackers(searchModel); var linkTrackers = Maps(data, searchModel); return(ExcelUtilities.CreateWorkBook(linkTrackers)); }
/// <summary> /// Export protected document logs /// </summary> /// <returns></returns> public HSSFWorkbook Exports() { var data = GetAll(); var protectedDocumentLogs = Maps(data); return(ExcelUtilities.CreateWorkBook(protectedDocumentLogs)); }
private static void ExecuteLegrandOperations(string excelPath, int startIndex, int endIndex) { var wb = ExcelUtilities.CreateWorkbook(excelPath); var excelSheet = ExcelUtilities.SelectExcelSheet(wb, "Legrand"); var eanDictionary = new Dictionary <string, string>(); ExcelUtilities.ReadFromExcel(eanDictionary, excelSheet, startIndex, endIndex); var copiedDict = eanDictionary.ToDictionary(entry => entry.Key, entry => entry.Value); var fieldsDict = new Dictionary <string, int>(); foreach (var ean in eanDictionary.Keys) { var legrand = new Legrand(); var unprocessedHtml = HtmlHandler.GetUnprocessedHtml("Legrand", ean); List <Dictionary <string, string> > fieldCollections = new List <Dictionary <string, string> >(); try { copiedDict = legrand.GetMasterData(unprocessedHtml, ean).ToDictionary(k => k.Key, v => v.Value); } catch (Exception) { startIndex++; continue; } fieldCollections.Add(legrand.GetProductChars(unprocessedHtml, ean)); fieldCollections.Add(legrand.GetGeneralChars(unprocessedHtml, ean)); fieldCollections.Add(legrand.GetMoreInfo(unprocessedHtml, ean, 0)); fieldCollections.Add(legrand.GetMoreInfo(unprocessedHtml, ean, 1)); fieldCollections.Add(legrand.GetPicture(unprocessedHtml, ean)); copiedDict = fieldCollections .Where(fieldCollection => fieldCollection != null) .Aggregate(copiedDict, (current, fieldCollection) => current.Union(fieldCollection).ToDictionary(k => k.Key, v => v.Value)); Console.WriteLine(startIndex + ". " + ean + " downloaded"); charDB.AddDic(copiedDict); foreach (var entry in copiedDict) { var fieldIndex = 0; if (!fieldsDict.ContainsKey(entry.Key)) { fieldsDict.Add(entry.Key, 7 + fieldsDict.Count); } fieldIndex = fieldsDict[entry.Key]; excelSheet.Cells.WrapText = true; excelSheet.Cells[1, fieldIndex].Value = entry.Key; excelSheet.Cells[startIndex, fieldIndex].Value = entry.Value; } Console.WriteLine(startIndex + ". " + ean + " written"); startIndex++; } wb.Close(); }
/// <summary> /// Export notification templates /// </summary> /// <param name="si"></param> /// <param name="gridExportMode"></param> /// <returns></returns> public HSSFWorkbook Exports(JqSearchIn si, GridExportMode gridExportMode) { var notificationTemplates = SearchNotificationTemplates(); var exportData = si.Export(notificationTemplates, gridExportMode); return(ExcelUtilities.CreateWorkBook(exportData)); }
public MemoryStream GenerarExcelServiciosResumen(Periodo periodo) { var servicios = _servicioRepositorio.FindBy(s => s.Fecha > periodo.Inicio && s.Fecha < periodo.Fin && s.Estado.Codigo != "AN").OrderBy(s => s.Fecha).ToList(); var serviciosResumen = GenerarServiciosResumen(servicios); var memoryExcel = ExcelUtilities <ServicioResumen> .Export(serviciosResumen); return(memoryExcel); }
/// <summary> /// Export contact communications /// </summary> /// <returns></returns> public HSSFWorkbook Exports(int contactId) { var data = Fetch(c => c.ContactId == contactId); var contactCommunications = Maps(data); return(ExcelUtilities.CreateWorkBook(contactCommunications)); }
/// <summary> /// Export notified contacts /// </summary> /// <param name="si"></param> /// <param name="gridExportMode"></param> /// <param name="notificationId"></param> /// <returns></returns> public HSSFWorkbook ExportsNotifiedContacts(JqSearchIn si, GridExportMode gridExportMode, int notificationId) { var notifiedContacts = SearchNotifiedContacts(notificationId); var exportData = si.Export(notifiedContacts, gridExportMode); return(ExcelUtilities.CreateWorkBook(exportData)); }
/// <summary> /// Export Plugins /// </summary> /// <param name="si"></param> /// <param name="gridExportMode"></param> /// <returns></returns> public HSSFWorkbook Exports(JqSearchIn si, GridExportMode gridExportMode) { var plugins = GetAll().AsQueryable(); var exportData = si.Export(plugins, gridExportMode); return(ExcelUtilities.CreateWorkBook(exportData)); }
public async Task <ActionResult> ExportExcel(int id) { string apiUrl = _appUrlHelper.GetApiUrl(string.Format(ApiUrlPath.COMPUTER_GET_ALL, id)); var result = await HttpUtilities.GetAsyncApi <ReturnResult <ComputerExportModel> >(apiUrl); var file = ExcelUtilities.CreateExcelFile <ComputerExportModel>("computer.xls", result.ItemList); return(File(file, "application/ms-excel", "ComputerList.xlsx")); }
/// <summary> /// Returns a list of columns names that a worksheet contains /// </summary> /// <param name="worksheetName">Worksheet name to get the list of column names from</param> public IEnumerable <string> GetColumnNames(string worksheetName) { if (String.IsNullOrEmpty(FileName)) { throw new NullReferenceException("FileName property is not set"); } return(ExcelUtilities.GetColumnNames(worksheetName, FileName)); }
/// <summary> /// Returns a list of workbook-scope named ranges that the spreadsheet contains /// </summary> public IEnumerable <string> GetNamedRanges() { if (String.IsNullOrEmpty(FileName)) { throw new NullReferenceException("FileName property is not set"); } return(ExcelUtilities.GetNamedRanges(FileName)); }
/// <summary> /// Get data from the weather file and present it to the view as both a table and a summary /// </summary> /// <param name="filename">The filename.</param> private void WriteTableAndSummary(string filename, string sheetName = "") { // Clear any previous summary this.weatherDataView.Summarylabel = string.Empty; this.weatherDataView.GraphSummary.Clear(); this.weatherDataView.GraphSummary.Refresh(); this.weatherDataView.GraphRainfall.Clear(); this.weatherDataView.GraphRainfall.Refresh(); this.weatherDataView.GraphMonthlyRainfall.Clear(); this.weatherDataView.GraphMonthlyRainfall.Refresh(); this.weatherDataView.GraphTemperature.Clear(); this.weatherDataView.GraphTemperature.Refresh(); this.weatherDataView.GraphRadiation.Clear(); this.weatherDataView.GraphRadiation.Refresh(); this.graphMetData = new DataTable(); if (filename != null) { try { if (Path.GetExtension(filename) == ExcelUtilities.ExcelExtension) { ////Extend height of Browse Panel to show Drop Down for Sheet names weatherDataView.BrowsePanelControlHeight = 76; if (sheetNames == null) { sheetNames = ExcelUtilities.GetWorkSheetNames(filename); this.weatherDataView.PopulateDropDownData(sheetNames); } } else { //Shrink Browse Panel so that the sheet name dropdown doesn't show weatherDataView.BrowsePanelControlHeight = 41; } this.weatherData.ExcelWorkSheetName = sheetName; this.weatherData.FullFileName = PathUtilities.GetAbsolutePath(filename, this.explorerPresenter.ApsimXFile.FileName); DataTable data = this.weatherData.GetAllData(); this.WriteTable(data); this.WriteSummary(data); this.DisplayDetailedGraphs(data); this.explorerPresenter.MainPresenter.ShowMessage(" ", DataStore.ErrorLevel.Information); } catch (Exception err) { string message = err.Message; message += "\r\n" + err.StackTrace; this.weatherDataView.Summarylabel = err.Message; this.explorerPresenter.MainPresenter.ShowMessage(message, DataStore.ErrorLevel.Error); } } this.weatherDataView.Filename = PathUtilities.GetRelativePath(filename, this.explorerPresenter.ApsimXFile.FileName); this.weatherDataView.ExcelWorkSheetName = sheetName; }
public static void AddCity(string sheetName) { Logger.WriteToLogFile(Utilities.GetCurrentMethod()); Excel.Worksheet sheet = ExcelController.GetWorkSheet(sheetName); Excel.Range usedRange = sheet.UsedRange; CityWrapper objWrap = new CityWrapper(); Country objCountry = new Country(); State objState = new State(); //Do not consider row 1 as its the header for (int Row = 2; Row <= usedRange.Rows.Count; Row++) { City objCity = new City(); for (int Col = 1; Col <= usedRange.Columns.Count; Col++) { string name = (string)(usedRange.Cells[1, Col] as Excel.Range).Value2; name = name.Trim(); string value = null; if ((usedRange.Cells[Row, Col] as Excel.Range).Value2 != null) { value = (usedRange.Cells[Row, Col] as Excel.Range).Value2.ToString(); } else { continue; } if (name.CompareTo(ExcelSheets.EXCELSHEET_COUNTRY) == 0) { objCountry.Name = value; continue; } else if (name.CompareTo(ExcelSheets.EXCELSHEET_STATE) == 0) { objState.Name = value; continue; } ExcelUtilities.PopulateStructure <City>(name, value, ref objCity); } //Add the country in the Neo4j Database objWrap.objState = objState; objWrap.objCountry = objCountry; objWrap.objCity = objCity; DBAddinterface.CreateCityNode(objWrap); } }
/// <summary> /// Export subscription logs /// </summary> /// <param name="si"></param> /// <param name="gridExportMode"></param> /// <returns></returns> public HSSFWorkbook Exports(JqSearchIn si, GridExportMode gridExportMode) { var data = GetAll(); var subscriptionLogs = Maps(data); var exportData = si.Export(subscriptionLogs, gridExportMode); return(ExcelUtilities.CreateWorkBook(exportData)); }
/// <summary> /// Export user login histories /// </summary> /// <param name="si"></param> /// <param name="gridExportMode"></param> /// <param name="model"></param> /// <returns></returns> public HSSFWorkbook Exports(JqSearchIn si, GridExportMode gridExportMode, UserLoginHistorySearchModel model) { var data = gridExportMode == GridExportMode.All ? GetAll() : SearchUserLoginHistories(model); var userLoginHistories = Maps(data); var exportData = si.Export(userLoginHistories, gridExportMode); return(ExcelUtilities.CreateWorkBook(exportData)); }
/// <summary> /// Export localized resources /// </summary> /// <param name="si"></param> /// <param name="model"></param> /// <param name="gridExportMode"></param> /// <returns></returns> public HSSFWorkbook Exports(JqSearchIn si, GridExportMode gridExportMode, LocalizedResourceSearchModel model) { var data = gridExportMode == GridExportMode.All ? GetAll() : SearchLocalizedResources(model); var localizedResources = Maps(data); var exportData = si.Export(localizedResources, gridExportMode); return(ExcelUtilities.CreateWorkBook(exportData)); }
/// <summary> /// Export the widgets /// </summary> /// <param name="si"></param> /// <param name="gridExportMode"></param> /// <param name="model"></param> /// <returns></returns> public HSSFWorkbook Exports(JqSearchIn si, GridExportMode gridExportMode, WidgetSearchModel model) { var data = gridExportMode == GridExportMode.All ? GetAllWidgets() : SearchWidgets(model); var widgets = data.AsQueryable(); var exportData = si.Export(widgets, gridExportMode); return(ExcelUtilities.CreateWorkBook(exportData)); }
/// <summary> /// Export EventSchedules /// </summary> /// <param name="si"></param> /// <param name="gridExportMode"></param> /// <param name="eventId"></param> /// <returns></returns> public HSSFWorkbook Exports(JqSearchIn si, GridExportMode gridExportMode, int?eventId) { var data = gridExportMode == GridExportMode.All ? GetAll() : SearchEventSchedules(eventId); var eventSchedules = Maps(data); var exportData = si.Export(eventSchedules, gridExportMode); return(ExcelUtilities.CreateWorkBook(exportData)); }
/// <summary> /// Export link trackers /// </summary> /// <param name="si"></param> /// <param name="searchModel"></param> /// <param name="gridExportMode"></param> /// <returns></returns> public HSSFWorkbook Exports(JqSearchIn si, GridExportMode gridExportMode, LinkTrackerSearchModel searchModel) { var data = gridExportMode == GridExportMode.All ? GetAll() : SearchLinkTrackers(searchModel); var linkTrackers = Maps(data, searchModel); var exportData = si.Export(linkTrackers, gridExportMode); return(ExcelUtilities.CreateWorkBook(exportData)); }
/// <summary> /// Export link trackers /// </summary> /// <param name="si"></param> /// <param name="gridExportMode"></param> /// <param name="pageId"></param> /// <returns></returns> public HSSFWorkbook ExportsLinkTrackersByPage(JqSearchIn si, GridExportMode gridExportMode, int pageId) { var data = gridExportMode == GridExportMode.All ? GetAll() : SearchLinkTrackers(pageId); var linkTrackers = Maps(data, new LinkTrackerSearchModel()); var exportData = si.Export(linkTrackers, gridExportMode); return(ExcelUtilities.CreateWorkBook(exportData)); }
/// <summary> /// Export templates /// </summary> /// <param name="si"></param> /// <param name="model"></param> /// <param name="gridExportMode"></param> /// <returns></returns> public HSSFWorkbook Exports(JqSearchIn si, GridExportMode gridExportMode, WidgetTemplateSearchModel model) { var data = gridExportMode == GridExportMode.All ? GetAll() : SearchTemplates(model); var templates = Maps(data); var exportData = si.Export(templates, gridExportMode); return(ExcelUtilities.CreateWorkBook(exportData)); }