public MemoryStream ExportByForm(string formId)
{
using (ExcelGenerator excel = new ExcelGenerator())
{
var form = _formService.Get(formId);
var formDatas = Get(m => m.FormId == formId);
excel.AddRow(row =>
{
foreach (var item in form.FormFields)
{
row.AppendCell(item.DisplayName);
}
});
foreach (var data in formDatas)
{
excel.AddRow(row =>
{
foreach (var item in Get(data.ID).Form.FormFields)
{
row.AppendCell(item.DisplayValue());
}
});
}
return(excel.ToMemoryStream());
}
}
private void btnTotalProfitCalculator_Click(object sender, RoutedEventArgs e)
{
try
{
var path = "total-profit.xlsx";
var mySqlCOntext = new MySqlModel();
var products = ProductsReportsLoader.GetReportsFromMySql(mySqlCOntext);
// TODO: Change it with entity framework
var vendorsProduct = SqliteParser.GetVendorsProducts();
ExcelGenerator.Generate(products, vendorsProduct, path);
MessageBox.Show("The total profit report was successfully generated!",
"Generated successfully",
MessageBoxButton.OK,
MessageBoxImage.Information);
Process.Start(path);
}
catch (Exception)
{
MessageBox.Show("Cannot generate the total profit report!",
"Generation failed",
MessageBoxButton.OK,
MessageBoxImage.Error);
}
}
public static bool generateAreaStructure(List <Operation> listOp, WorkbookPart workbookPart)
{
Console.WriteLine("Creating AreaStructure tab of Excel");
string sheetName = "AreaStructure";
uint row, col;
row = 3; // starting row position
col = 1;
foreach (var op in listOp)
{
// Console.WriteLine("[{0:D}, {1:D}]", row, col);
ExcelGenerator.addCellData(workbookPart, sheetName, row++, col, op.Name);
foreach (var phase in op.getPhases())
{
// Console.WriteLine("[{0:D}, {1:D}]", row, col);
ExcelGenerator.addCellData(workbookPart, sheetName, row, col, op.Name);
// Console.WriteLine("[{0:D}, {1:D}]", row, col + 1);
ExcelGenerator.addCellData(workbookPart, sheetName, row++, col + 1, phase.Name);
}
}
return(true);
}
private void 个签意见书ToolStripMenuItem_Click(object sender, EventArgs e)
{
HashSet <Visa> set = new HashSet <Visa>();
Model.Visa visaModel = null;
int idx = 0; //visaModel的下标
for (int i = 0; i < rowMergeView1.SelectedRows.Count; i++)
{
for (int j = 0; j < _listVisa.Count; j++)
{
if (_listVisaInfo[j].Contains(_listDgv[rowMergeView1.SelectedRows[i].Index]))
{
if (!set.Contains(_listVisa[j]))
{
set.Add(_listVisa[j]);
visaModel = _listVisa[j];
idx = j;
}
}
}
}
if (set.Count > 1)
{
MessageBoxEx.Show("请选择同一个团号的签证进行导出!");
return;
}
if (visaModel == null)
{
return;
}
ExcelGenerator.GetIndividualVisaExcel(_listVisaInfo[idx], visaModel.Remark, visaModel.GroupNo);
}
public void TestMethod1()
{
var uatConnectionString = "";
var qaConnectionString = "";
var uatQuery =
@"";
var qaQuery = uatQuery;
var uatSqlServerHelper = new SQLServerHelper(uatConnectionString);
var qaSqlServerHelper = new SQLServerHelper(qaConnectionString);
var uatResult = uatSqlServerHelper.GetDataTable(uatQuery);
var qaResult = qaSqlServerHelper.GetDataTable(qaQuery);
uatResult.PrimaryKey = new DataColumn[] {
uatResult.Columns[""]
};
qaResult.PrimaryKey = new DataColumn[] {
qaResult.Columns[""]
};
var diff = DataTableMerger.Merge(uatResult, qaResult, null, "UAT", "QA");
var path = string.Format(@"E:\Test_{0}.xlsx", DateTime.Now.Ticks);
var excelExport = new ExcelGenerator();
excelExport.Export(uatResult, path, "UAT");
excelExport.Export(qaResult, path, "QA");
excelExport.Export(diff, path, "Diff");
}
public void ExcelGenerator_GetDataFromString()
{
object result;
result = ExcelGenerator.GetDataFromString("test", typeof(string));
Assert.IsNotNull(result);
Assert.AreEqual(typeof(string), result.GetType());
Assert.AreEqual("test", result);
result = ExcelGenerator.GetDataFromString("1", typeof(int));
Assert.IsNotNull(result);
Assert.AreEqual(typeof(int), result.GetType());
Assert.AreEqual(1, result);
result = ExcelGenerator.GetDataFromString("1.5", typeof(double));
Assert.IsNotNull(result);
Assert.AreEqual(typeof(double), result.GetType());
Assert.AreEqual(1.5, result);
result = ExcelGenerator.GetDataFromString("1.5", typeof(decimal));
Assert.IsNotNull(result);
Assert.AreEqual(typeof(decimal), result.GetType());
Assert.AreEqual(1.5m, result);
result = ExcelGenerator.GetDataFromString("2017-04-30", typeof(DateTime));
Assert.IsNotNull(result);
Assert.AreEqual(typeof(DateTime), result.GetType());
Assert.AreEqual(new DateTime(2017, 04, 30), result);
result = ExcelGenerator.GetDataFromString("2017-04-30T20:53:15", typeof(DateTime));
Assert.IsNotNull(result);
Assert.AreEqual(typeof(DateTime), result.GetType());
Assert.AreEqual(new DateTime(2017, 04, 30, 20, 53, 15), result);
}
public void GenerateXlsxStreamTest()
{
var excelGenerator = new ExcelGenerator(new Naturetypekodetre());
var xlsStream = excelGenerator.GenerateXlsxStream(new Collection <NatureAreaExport>());
Assert.NotNull(xlsStream);
}
public HttpResponseMessage GetProjectsInProgressBeforeDate([FromQuery] string date)
{
var dateFormat = date.ToInvariantDateTime("dd/MM/yyyy", out bool succeded);
if (!succeded)
{
HandleBadRequest(new List <string> {
"Invalid date format supplied: Expecting dd/MM/yyyy"
});
}
var projectInProgress = _projectService.GetProjectReport(dateFormat);
var file = ExcelGenerator.ToExcel(projectInProgress.ToList());
var response = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(file)
};
response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = $"Report_{date}"
};
response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
return(response);
}
static void Main(string[] args)
{
List <ExcelColumn> columns = new List <ExcelColumn>()
{
new ExcelColumn {
ColumnName = "Name"
},
new ExcelColumn {
ColumnName = "Customer Reference Number"
},
new ExcelColumn {
ColumnName = "Gender", ValidationDataSet = new List <string>()
{
"Male", "Female"
}
}
};
using (IExcelGenerator generator = new ExcelGenerator("anand.xlsx", "serviceSheet", "data"))
{
ExcelColumns excelColumns = new ExcelColumns(columns);
var dataPopulater = generator.GetExcelDataPopulater(excelColumns);
dataPopulater.PopulateData();
generator.NativeExcelPackageObject.SaveAs(new FileInfo("./students.xlsx"));
}
}
static void Main(string[] args)
{
List <Product> products = new List <Product>(3);
products.Add(new Product()
{
Code = "P_A",
Discount = 0.1F,
Id = 1,
Name = "F-15",
Price = 6000.85m,
AvailableDate = DateTime.Now
});
products.Add(new Product()
{
Code = "P_B",
Discount = 0.2F,
Id = 2,
Name = "F-16",
Price = 5500.50m,
AvailableDate = DateTime.Now.AddDays(-3),
IsOffline = true,
NullableBool = true
});
products.Add(new Product()
{
Code = "P_C",
Discount = 0.22F,
Id = 3,
Name = "F-22",
Price = null,
AvailableDate = DateTime.Now.AddDays(-3),
IsOffline = true,
NullableBool = true
});
products.Add(new Product()
{
Code = "P_D",
Discount = 0.35F,
Id = 4,
Name = "F-18",
Price = 5800.0m,
AvailableDate = DateTime.Now.AddDays(-5)
});
string fileName = Path.Combine(Environment.CurrentDirectory, "Test.xlsx");
// create .xlsx file as MemoryStream
using (MemoryStream ms = ExcelGenerator.CreateStream(products, "Products"))
{
using (FileStream fs = File.Create(fileName))
{
ms.WriteTo(fs);
}
}
// create .xlsx file
//ExcelGenerator.CreateFile(products, "Products", fileName);
}
public FileStreamResult ExportNatureAreasAsXlsxBySearchFilter(
[FromBody] SearchFilterRequest searchFilterRequest)
{
var natureAreas = FindNatureAreasBySearchFilter(searchFilterRequest);
var excelGenerator = new ExcelGenerator(Naturkodetrær.Naturvariasjon);
var xlsxMemoryStream = excelGenerator.GenerateXlsxStream(natureAreas);
return(new FileStreamResult(xlsxMemoryStream,
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
private void 打印报表ToolStripMenuItem_Click(object sender, EventArgs e)
{
var visainfos = GetDgvSelList();
if (visainfos.Count == 0)
{
return;
}
ExcelGenerator.GetPrintTable(visainfos);
}
public static byte[] ExecuteExcelReport(Lite <ExcelReportEntity> excelReport, QueryRequest request)
{
ResultTable queryResult = QueryLogic.Queries.ExecuteQuery(request);
ExcelReportEntity report = excelReport.Retrieve();
AsserExtension(report);
return(ExcelGenerator.WriteDataInExcelFile(queryResult, report.File.BinaryFile));
}
public static async Task <byte[]> ExecuteExcelReportAsync(Lite <ExcelReportEntity> excelReport, QueryRequest request, CancellationToken token)
{
ResultTable queryResult = await QueryLogic.Queries.ExecuteQueryAsync(request, token);
ExcelReportEntity report = excelReport.Retrieve();
AsserExtension(report);
return(ExcelGenerator.WriteDataInExcelFile(queryResult, report.File.BinaryFile));
}
public IActionResult GenerateKpcExcel(int id)
{
//id in this case is a business trip Id and it takes all pieces of this business trip
var pieces = _service.GetAll();
var excelName = _settings.Excel.KpcFileName;
var memoryStream = ExcelGenerator.GetKpcExcelMemoryStream(pieces, _env.WebRootPath, _settings.Excel.FolderName, excelName);
return(File(memoryStream, _excelFileType, excelName));
}
public MemoryStream GetReportTransactional(DateTime startdate, DateTime enddate)
{
MemoryStream ms = null;
var data = _reportRepository.GetReportTransactional(startdate, enddate);
using (var excel = new ExcelGenerator(data))
{
ms = excel.WriteExcelToStream();
}
return(ms);
}
/// <summary>
/// 生成报表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreateReport_Click(object sender, EventArgs e)
{
if (dgvGroupInfo.Rows[0].Cells["Remark"].Value != null)
{
ExcelGenerator.GetIndividualVisaExcel(_dgvList, dgvGroupInfo.Rows[0].Cells["Remark"].Value.ToString(), txtGroupNo.Text);
}
else
{
ExcelGenerator.GetIndividualVisaExcel(_dgvList, string.Empty, txtGroupNo.Text);
}
}
/// <summary>
/// 生成报表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreateReport_Click(object sender, EventArgs e)
{
if (cbCountry.Text == "日本")
{
ExcelGenerator.GetTeamVisaExcelOfJapan(_dgvList, txtGroupNo.Text);
}
else if (cbCountry.Text == "泰国")
{
ExcelGenerator.GetTeamVisaExcelOfThailand(_dgvList, txtGroupNo.Text);
}
}
public void ExportTestThenSuccess()
{
var dataTable = TestHelper.MockDataTable("Test-case");
var path = string.Format(CultureInfo.CurrentCulture, @"D:\Test_{0}.xlsx", DateTime.Now.Ticks);
var workbook = ExcelGenerator.Export(dataTable);
workbook.SaveAs(path);
Assert.IsTrue(File.Exists(path));
File.Delete(path);
}
public FileContentResult GenerateExcelDocument()
{
var values = new Dictionary<string, string>
{
{"ValueA", "90"},
{"ValueB", "300"},
{"Name", "Jhon Doe"}
};
IDocumentGenerator generator = new ExcelGenerator();
var result = generator.GenerateDocument(values, Server.MapPath("~/Content/Files/ExcelTest.xlsx"));
return File(result, ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"), "ExcelTest.xlsx");
}
public static byte[] WriteDataInExcelFile(ResultTable queryResult, byte[] template)
{
using (MemoryStream ms = new MemoryStream())
{
ms.WriteAllBytes(template);
ms.Seek(0, SeekOrigin.Begin);
ExcelGenerator.WriteDataInExcelFile(queryResult, ms);
return(ms.ToArray());
}
}
public DashboardViewModelCore(DustService dustService = null, ExcelGenerator excelGenerator = null, IScreen screen = null)
{
_excelGenerator = excelGenerator ?? Locator.Current.GetService <ExcelGenerator>();
_dustService = dustService ?? Locator.Current.GetService <DustService>();
HostScreen = screen ?? Locator.Current.GetService <IScreen>();
var canLoadDataCommand = this.WhenAnyValue(p => p.SelectedStation)
.Select(p => p != null);
LoadDataCommand = ReactiveCommand.CreateFromObservable <Station, Record[]>(station =>
{
//Info = new DashboardInfo();
_values.Clear();
IObservable <Record[]> s = _dustService.GetAvailableParametersAsync(station)
.Select(@params => @params.Select(p => p.Param).ToArray())
.Select(@params => _dustService.GetStationRecordsAsync(station.Code, @params)).Switch();
return(Observable.Start(() => s, RxApp.TaskpoolScheduler).Switch().TakeUntil(CancelCommand));;
}, canLoadDataCommand);
CancelCommand = ReactiveCommand.Create(
() => { },
this.LoadDataCommand.IsExecuting);
this.WhenActivated(cleanup =>
{
var share = _values.Connect().Publish().RefCount();
share.ToCollection().Select(records =>
{
return(Enum.GetValues(typeof(RecordType)).Cast <RecordType>()
.Select(type => records.LastOrDefault(r => r.Type == type)));
}).BindTo(this, vm => vm.LastRecords).DisposeWith(cleanup);
share.Bind(StationData).Subscribe().DisposeWith(cleanup);
LoadDataCommand.ThrownExceptions.Subscribe(ShowError).DisposeWith(cleanup);
LoadDataCommand.Subscribe(records =>
{
_values.Edit(e => e.AddRange(records));
}).DisposeWith(cleanup);
var canSaveToExcelCommand = StationData.ObserveCollectionChanges().Select(_ => StationData.Count > 0);
SaveToExcelCommand = ReactiveCommand.CreateFromTask <IEnumerable <Record> >(data => _excelGenerator.CreateExcel(SelectedStation.Code, data), canSaveToExcelCommand);
SaveToExcelCommand.ThrownExceptions.Subscribe(ShowError);
this.WhenAnyValue(p => p.SelectedStation).Where(p => p != null).Do(_ => CancelCommand.Execute().Subscribe()).InvokeCommand(LoadDataCommand).DisposeWith(cleanup);;
});
}
public void HighlightTextThenSuccess()
{
var ticks = DateTime.Now.Ticks;
using (var dataTable = TestHelper.MockMergedDataTable())
{
var workbook = ExcelGenerator.Export(dataTable);
var worksheet = workbook.Worksheet("Result");
ExcelGenerator.Highlight(worksheet, "_UAT", "_PROD", "_Gap");
var highlightPath = string.Format(CultureInfo.CurrentCulture, @"D:\hightlight_{0}.xlsx", ticks);
workbook.SaveAs(highlightPath);
}
}
public void returns_nonempty_stream()
{
Timetable timetable = new Timetable(new List <Day>());
timetable.Worker = "a";
ExcelGenerator generator = new ExcelGenerator();
Stream stream = generator.GenerateStreamFromTemplate(new List <Timetable> {
timetable
});
stream.Length.Should().BeGreaterThan(0);
}
public FileContentResult GenerateExcelDocument()
{
var values = new Dictionary <string, string>
{
{ "ValueA", "90" },
{ "ValueB", "300" },
{ "Name", "Jhon Doe" }
};
IDocumentGenerator generator = new ExcelGenerator();
var result = generator.GenerateDocument(values, Server.MapPath("~/Content/Files/ExcelTest.xlsx"));
return(File(result, ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"), "ExcelTest.xlsx"));
}
private void 查看选中项目统计ToolStripMenuItem_Click(object sender, EventArgs e)
{
var selList = GetSelectedModelList();
if (selList.Count > 1)
{
MessageBoxEx.Show("请选中一条进行查看!");
return;
}
var saleBillModellist = _bllSaleBill.GetModelList(" Project = '" + selList[0].Project + "' ");
ExcelGenerator.GetSailBillCount(saleBillModellist);
}
private void 日本送签时间表ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count < 1)
{
return;
}
List <Model.Visa> list = new List <Visa>();
for (int i = 0; i < dataGridView1.SelectedRows.Count; i++)
{
list.Add((dataGridView1.DataSource as List <Model.Visa>)[dataGridView1.SelectedRows[i].Index]);
}
ExcelGenerator.GetAllCountExcel(list);
}
static void Main(string[] args)
{
var hotelInfo = GetHotelInfo();
var excelGenerator = new ExcelGenerator();
var task = excelGenerator.SaveTable(hotelInfo.HotelRates, GetExcelColumnInfos(), $"{hotelInfo.Hotel.Name}.xlsx");
try
{
task.Wait();
Console.WriteLine("Excel file created");
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public ReportModel GetReport(string key)
{
Report report = new Report();
var reportData = reportRepository.GetReport(key, out report);
var fileName = ExcelGenerator.GenerateExcel(reportData, key);
return(new ReportModel()
{
Key = key,
Name = report.Name,
Id = report.Id,
Description = report.Description,
Procedure = report.Procedure,
FileName = fileName,
Type = "Excel"
});
}
private async Task <DialogTurnResult> SelectMonthAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
{
stepContext.Values["Month"] = ((FoundChoice)stepContext.Result).Value;
for (int i = 0; i < months.Length; i++)
{
if (stepContext.Values["Month"].ToString() == months[i])
{
indexer = Convert.ToString(i + 1);
}
}
var orderList = await BotMethods.GetSalaryDeduction(indexer, this.botConfig.Value.GetSalaryDeduction);
bool test = ExcelGenerator.Run(orderList);
await stepContext.EndDialogAsync();
return(await stepContext.BeginDialogAsync(nameof(OverviewDialog), null, cancellationToken));
}
static void Main(string[] args)
{
//Pacote Nuget a ser instalado
//Install-Package EPPlus -Version 4.1.1
var r = new Random();
var data = new List <Pai>();
//data.Add(new Pai(profundidade: 7, largura: 100,nivel:1,r:r));
//data.Add(new Pai(profundidade: 5, largura: 100,nivel:1,r:r));
//data.Add(new Pai(profundidade: 2, largura: 100,nivel:1,r:r));
//data.Add(new Pai(profundidade: 3, largura: 100,nivel:1,r:r));
data.Add(new Pai(profundidade: 10, largura: 1000, nivel: 1, r: r));
//data.Add(new Pai(profundidade: 3, largura: 1000,nivel:1,r:r));
var maiorProfundidade = data.Max(e => e.profundidade);
var a = new ExcelGenerator(@"D:\20180308 MUTANT - EXCEL\console-excel-generator\Planilhas\", data, maiorProfundidade).ExportComplete();
}
