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