private static void ApplyCellStyle(ExcelWorker worker, Level1Fields field, int column)
{
switch (field)
{
case Level1Fields.LastTrade:
case Level1Fields.BestAsk:
case Level1Fields.BestBid:
break;
case Level1Fields.LastTradeId:
case Level1Fields.BidsCount:
case Level1Fields.AsksCount:
case Level1Fields.TradesCount:
worker.SetStyle(column, typeof(long));
break;
case Level1Fields.LastTradeTime:
case Level1Fields.BestAskTime:
case Level1Fields.BestBidTime:
worker.SetStyle(column, typeof(DateTimeOffset));
break;
default:
worker.SetStyle(column, typeof(decimal));
break;
}
}
public static string FormAnnualContractInsertTechlist(string name, string cityId, string cityName, string visible, string minPrice, int time)
{
ExcelWorker ex = new ExcelWorker(Environment.TlAnnualInsertPath);
int i = 1;
while (i <= time + 1)
{
ex.SetCell("A" + (i + 1), "1");
ex.SetCell("B" + (i + 1), name);
ex.SetCell("C" + (i + 1), cityId);
ex.SetCell("D" + (i + 1), cityName);
ex.SetCell("E" + (i + 1), visible);
ex.SetCell("F" + (i + 1), "N");
ex.SetCell("G" + (i + 1), minPrice);
ex.SetCell("H" + (i + 1), time.ToString());
if (i != time + 1)
{
ex.SetCell("K" + (i + 1), i.ToString());
}
ex.SetCell("L" + (i + 1), (i + 5).ToString());
i++;
}
return(Techlist.SaveAndClose(ref ex, "annual_ins"));
}
static void Main(string[] args)
{
List <Congratulation> congratulationList = new List <Congratulation>();
//Congratulation c1 = new BusinessLogic.Congratulation("Игорь", "счастья", "здоровья", "успехов");
//Congratulation c2 = new BusinessLogic.Congratulation("Игорь2", "счастья", "здоровья", "успехов");
//congratulationList.Add(c1);
//congratulationList.Add(c2);
WordWorker ww = new WordWorker();
ExcelWorker ew = new ExcelWorker();
var adressees = new List <string>();
adressees = ew.getNamesOfAdressees();
foreach (var name in adressees)
{
Congratulation c1 = new BusinessLogic.Congratulation(name, "счастья", "здоровья", "успехов");
congratulationList.Add(c1);
}
ew.closeApp();
foreach (var c in congratulationList)
{
ww.createCongratulation(c);
}
ww.showApp();
ww.closeApp();
Console.ReadLine();
}
private static int WriteHeader(EngineerProfile profile, ExcelWorker worker, int row)
{
foreach (var header in profile.Header.Scales)
{
worker.SetAValue(row++, header);
}
return(row);
}
/// <summary>
/// 导出Excel(不带表头)
/// </summary>
/// <returns></returns>
public IActionResult ExportExcel()
{
var data = GetData();
var fileName = $"测试导出";
var excelwork = new ExcelWorker();
var bytes = excelwork.ExportDataTable(data, fileName);
return(File(bytes, "application/x-xls", fileName + ".xls"));
}
/// <summary>
/// 导出Excel(带表头)
/// </summary>
/// <returns></returns>
public IActionResult ExportExcelByTitle(string name = null)
{
var data = GetData();
var fileName = $"测试导出";
var excelwork = new ExcelWorker();
var bytes = excelwork.ExportDataTable(data, fileName, name == null?"测试表头":name);
return(File(bytes, "application/x-xls", fileName + ".xls"));
}
private static void ApplyCellStyle(ExcelWorker worker, Level1Fields field, int column)
{
var type = field.ToType();
if (type != null && !type.IsEnum)
{
worker.SetStyle(column, type);
}
}
public static string FormNetphoneServicesUpdateTechlist(string serviceId, string serviceName, string visible)
{
ExcelWorker ex = new ExcelWorker(Environment.TlNetphoneUpdatePath);
ex.SetCell("A2", serviceId);
ex.SetCell("B2", serviceName);
ex.SetCell("C2", visible);
return(Techlist.SaveAndClose(ref ex, "netphone_serv_upd"));
}
/// <summary>
///
/// </summary>
/// <param name="status">
/// Статус:
/// A - активный публичный,
/// N - публичный только для новых,
/// R - непубличный, подключения только в HD,
/// P - непубличный для партнеров, подключения только в HD и IDMS
/// O - архивный,
/// S - служебный
/// </param>
/// <param name="serviceName"></param>
/// <returns></returns>
public static string FormFttbUpdateTechlist(string idService, string status, string serviceName)
{
ExcelWorker ex = new ExcelWorker(Environment.TlFttbUpdatePath);
ex.SetCell("D2", idService);
ex.SetCell("C2", status);
ex.SetCell("E2", serviceName);
return(Techlist.SaveAndClose(ref ex, "fttb_upd"));
}
public void ExcelWriteGroupAvgMinMaxWithSortingByMaxDescending()
{
//arrange
AllGroupsAvgMaxMinGetter getter = new AllGroupsAvgMaxMinGetter(connectionString);
List <GroupsAvgMinMax> results = getter.GetGroupsAvgMinMax((i) => i.Max, SortType.Descending).ToList <GroupsAvgMinMax>();
//act
bool flag = ExcelWorker.Write(@"GroupAvgMinMaxWithSorting.xlsx", results);
//assert
Assert.IsTrue(flag);
}
public void ExcelWriteSessionResults()
{
//arrange
SessionResultGetter getter = new SessionResultGetter(connectionString);
List <GroupResult> results = getter.GetSessionResult(1).ToList <GroupResult>();
//act
bool flag = ExcelWorker.Write(@"SessionResults.xlsx", results);
//assert
Assert.IsTrue(flag);
}
public void ExcelWriteAverageMarkBySpecification()
{
//arrange
AverageMarkBySpecificationGetter getter = new AverageMarkBySpecificationGetter(connectionString);
List <AverageMarkBySpecification> results = getter.GetAverageMark(1).ToList();
//act
bool flag = ExcelWorker.Write(@"AverageMarkBySpecification.xlsx", results);
//assert
Assert.IsTrue(flag);
}
public void ExcelWriteWithSortingByAverageMarkAverageMarkBySpecification()
{
//arrange
AverageMarkBySpecificationGetter getter = new AverageMarkBySpecificationGetter(connectionString);
List <AverageMarkBySpecification> results = getter.GetAverageMark(1, i => i.AverageMark, SortType.Ascending).ToList();
//act
bool flag = ExcelWorker.Write(@"AverageMarkBySpecificationWithSorting.xlsx", results);
//assert
Assert.IsTrue(flag);
}
public void ExcelWriteWithSortingBySurnameAscendingDropOutStudents()
{
//arrange
DropoutStudentsGetter getter = new DropoutStudentsGetter(connectionString);
List <DropOutStudentsByGroup> results = getter.GetExpelStudents((res) => res.Surname, SortType.Ascending).ToList <DropOutStudentsByGroup>();
//act
bool flag = ExcelWorker.Write(@"DropoutStudentsAscendingWithSorting.xlsx", results);
//assert
Assert.IsTrue(flag);
}
public void ExcelWriteDropOutStudents()
{
//arrange
DropoutStudentsGetter getter = new DropoutStudentsGetter(connectionString);
List <DropOutStudentsByGroup> results = getter.GetExpelStudents().ToList <DropOutStudentsByGroup>();
//act
bool flag = ExcelWorker.Write(@"DropoutStudents.xlsx", results);
//assert
Assert.IsTrue(flag);
}
public void ExcelWriteGroupAvgMinMax()
{
//arrange
AllGroupsAvgMaxMinGetter getter = new AllGroupsAvgMaxMinGetter(connectionString);
List <GroupsAvgMinMax> results = getter.GetGroupsAvgMinMax().ToList <GroupsAvgMinMax>();
//act
bool flag = ExcelWorker.Write(@"GroupAvgMinMax.xlsx", results);
//assert
Assert.IsTrue(flag);
}
public void ExcelWriteSessionResultsWithSortingByDateAscending()
{
//arrange
SessionResultGetter getter = new SessionResultGetter(connectionString);
List <GroupResult> results = getter.GetSessionResult(1, (i) => i.StudentName, SortType.Ascending).ToList <GroupResult>();
//act
bool flag = ExcelWorker.Write(@"SessionResultsWithSorting.xlsx", results);
//assert
Assert.IsTrue(flag);
}
private static void FormEqualString(ref ExcelWorker ex, int index, string cityId, string cityName,
string visible, string effectivePrice, string bundleName)
{
ex.SetCell("A" + index, "1");
ex.SetCell("B" + index, cityId);
ex.SetCell("C" + index, cityName);
ex.SetCell("D" + index, visible);
ex.SetCell("F" + index, effectivePrice);
ex.SetCell("G" + index, bundleName);
ex.SetCell("H" + index, "SERVICE_TYPE");
}
public void ExcelWriteWithSortingDescendingAverageMarkBySubject()
{
//arrange
AverageMarksBySubjectsGetter getter = new AverageMarksBySubjectsGetter(connectionString);
List <AverageMarksBySubjectsInOneYear> results = getter.GetAverageMarks(r => r.AverageMark, SortType.Descending).ToList();
//act
bool flag = ExcelWorker.Write(@"AverageMarkBySubjectWithSorting.xlsx", results);
//assert
Assert.IsTrue(flag);
}
public void ExcelWriteAverageMarkBySubject()
{
//arrange
AverageMarksBySubjectsGetter getter = new AverageMarksBySubjectsGetter(connectionString);
List <AverageMarksBySubjectsInOneYear> results = getter.GetAverageMarks().ToList();
//act
bool flag = ExcelWorker.Write(@"AverageMarkBySubject.xlsx", results);
//assert
Assert.IsTrue(flag);
}
public static string SaveAndClose(ref ExcelWorker ex, string prefix)
{
string filename = prefix + "_" + DateTime.Now.ToShortDateString() + "_" +
new Random().Next(100, 999).ToString();
string fileFullName = Environment.TlOutputPath + filename + ".xls";
ex.SaveAs(fileFullName);
ex.Close();
return(fileFullName);
}
private void ToExcelAward()
{
using (ExcelWorker excel = new ExcelWorker())
{
excel.SetText(1, 13, GetExcelTextAward());
excel.WrapText(1, 1, m_dataSet.Students.Count + 1, 13, true);
excel.SetColumnWidth(2, 13, 15);
excel.SetColumnWidth(1, 1, 4);
excel.WriteDataGrid(GetAwardDataGrid());
excel.Visible = true;
}
}
public static FileStreamResult GetXlsxFile(Action action, Param[] parameters = null)
{
// Заполняем команду названием процедуры из листа и параметрами
var command = new FunctionCommand(action.FunctionName, parameters);
// Создаем воркер, даем ему имя из листа
var worker = new ExcelWorker(action.NameRus);
// Передаем все в каллер и результат работы возвращаем
var caller = new DbCaller(command, worker);
caller.DoWork();
return(DownloadXlsxFile((MemoryStream)caller.GetResult(), action.NameRus));
}
public static string FormIptvServicesInsertTechlist(string cityId, string cityName, string type, string serviceName, string price)
{
ExcelWorker ex = new ExcelWorker(Environment.TlIptvServicesInsertPath);
ex.SetCell("A2", cityId);
ex.SetCell("B2", cityName);
ex.SetCell("C2", type);
ex.SetCell("E2", serviceName);
ex.SetCell("F2", price);
return(Techlist.SaveAndClose(ref ex, "iptv_serv_ins"));
}
public Form1()
{
InitializeComponent();
parser = new ParserWorker <List <ArgumentValue> >();
excelWorker = new ExcelWorker();
parser.OnCompleted += Parser_OnCompleted;
parser.OnNew += Parser_NewObject;
progressBar1.Step = 1;
progressBar1.Minimum = 1;
progressBar1.Maximum = 53;
}
public static string FormAnnualContractUpdateTechlist(string serviceId, string cityId, string cityName, string visible, string serviceName)
{
ExcelWorker ex = new ExcelWorker(Environment.TlAnnualUpdatePath);
ex.SetCell("A2", serviceId);
ex.SetCell("B2", cityId);
ex.SetCell("C2", cityName);
ex.SetCell("D2", visible);
ex.SetCell("E2", "N");
ex.SetCell("F2", serviceName);
return(Techlist.SaveAndClose(ref ex, "annual_upd"));
}
public static string FormNetphoneServicesInsertTechlist(string cityId, string cityName, string visible, string serviceName, string price, string atlId)
{
ExcelWorker ex = new ExcelWorker(Environment.TlNetphoneInsertPath);
ex.SetCell("A2", cityId);
ex.SetCell("B2", cityName);
ex.SetCell("C2", serviceName);
ex.SetCell("D2", atlId);
ex.SetCell("E2", price);
ex.SetCell("F2", visible);
return(Techlist.SaveAndClose(ref ex, "netphone_serv_ins"));
}
private void Do(Action <ExcelWorker> action)
{
if (action == null)
{
throw new ArgumentNullException(nameof(action));
}
using (var worker = new ExcelWorker())
{
action(worker);
worker.Save(Path, false);
}
}
/// <summary>
/// Производит действия по извлечению данных из БД и размещению их в Excel. Далее автоматически отдает на загрузку, если помещен после return
/// </summary>
/// <param name="sheet">Название листа</param>
/// <param name="parameters">Параметр(ы)</param>
public static FileStreamResult GetXlsxFile(string sheet, Param[] parameters = null)
{
// Получаем лист
Sheet currentSheet = Options.Sheets[sheet];
// Заполняем команду названием процедуры из листа и параметрами
var command = new FunctionCommand(currentSheet.Procedure, parameters);
// Создаем воркер, даем ему имя из листа
var worker = new ExcelWorker(currentSheet.Description);
// Передаем все в каллер и результат работы возвращаем
var caller = new DbCaller(command, worker);
caller.DoWork();
return(DownloadXlsxFile((MemoryStream)caller.GetResult(), currentSheet.Description));
}
private static void ApplyCellStyle(ExcelWorker worker, PositionChangeTypes type, int column)
{
switch (type)
{
case PositionChangeTypes.Currency:
case PositionChangeTypes.State:
worker.SetStyle(column, typeof(string));
break;
default:
worker.SetStyle(column, typeof(decimal));
break;
}
}
private static void ApplyCellStyle(ExcelWorker worker, Level1Fields field, int column)
{
switch (field)
{
case Level1Fields.LastTrade:
case Level1Fields.BestAsk:
case Level1Fields.BestBid:
break;
case Level1Fields.LastTradeId:
case Level1Fields.BidsCount:
case Level1Fields.AsksCount:
case Level1Fields.TradesCount:
worker.SetStyle(column, typeof(long));
break;
case Level1Fields.LastTradeTime:
case Level1Fields.BestAskTime:
case Level1Fields.BestBidTime:
worker.SetStyle(column, typeof(DateTimeOffset));
break;
default:
worker.SetStyle(column, typeof(decimal));
break;
}
}
private void Do(Action<ExcelWorker> action)
{
if (action == null)
throw new ArgumentNullException("action");
using (var worker = new ExcelWorker())
{
action(worker);
worker.Save(Path, false);
}
}
