private static void CopyRowAsRange(IXLWorksheet originalSheet, int originalRowNumber, IXLWorksheet destSheet, int destRowNumber)
{
{
var destinationRow = destSheet.Row(destRowNumber);
destinationRow.Clear();
var originalRow = originalSheet.Row(originalRowNumber);
int columnNumber = originalRow.LastCellUsed(true).Address.ColumnNumber;
var originalRange = originalSheet.Range(originalRowNumber, 1, originalRowNumber, columnNumber);
var destRange = destSheet.Range(destRowNumber, 1, destRowNumber, columnNumber);
originalRange.CopyTo(destRange);
}
}
/// <summary>
/// Checks the records in the sheet
/// </summary>
/// <param name="sheet">Sheet to check</param>
/// <param name="date">Current date</param>
/// <param name="yesterday">Yesterdays date</param>
private static void CheckRecords(
IXLWorksheet sheet,
DateTime date,
DateTime yesterday)
{
// Check the header is bold
Assert.AreEqual(true, sheet.Row(1).Style.Font.Bold);
// Check the header row
Assert.AreEqual("FirstColumn", sheet.Cell(1, 1).Value);
Assert.AreEqual("Int Column", sheet.Cell(1, 2).Value);
Assert.AreEqual("StringColumn", sheet.Cell(1, 3).Value);
Assert.AreEqual("TypeConvertedColumn", sheet.Cell(1, 4).Value);
Assert.AreEqual("BoolColumn", sheet.Cell(1, 5).Value);
Assert.AreEqual("DoubleColumn", sheet.Cell(1, 6).Value);
Assert.AreEqual("DateTimeColumn", sheet.Cell(1, 7).Value);
Assert.AreEqual("NullStringColumn", sheet.Cell(1, 8).Value);
Assert.AreEqual("FormulaColumn", sheet.Cell(1, 9).Value);
// Check the first record
Assert.AreEqual("first column", sheet.Cell(2, 1).Value);
Assert.AreEqual((double)1, sheet.Cell(2, 2).Value);
Assert.AreEqual("string column", sheet.Cell(2, 3).Value);
Assert.AreEqual("test", sheet.Cell(2, 4).Value);
Assert.AreEqual("true", sheet.Cell(2, 5).Value);
Assert.AreEqual(12.34, sheet.Cell(2, 6).Value);
Assert.AreEqual(date, sheet.Cell(2, 7).Value);
Assert.AreEqual("", sheet.Cell(2, 8).Style.DateFormat.Format); // TODO: Do we need a different default here for dates?
Assert.AreEqual("", sheet.Cell(2, 8).Value);
Assert.AreEqual("1+2", sheet.Cell(2, 9).FormulaA1);
Assert.AreEqual((double)3, sheet.Cell(2, 9).Value);
// Check the second record
Assert.AreEqual("first column 2", sheet.Cell(3, 1).Value);
Assert.AreEqual((double)2, sheet.Cell(3, 2).Value);
Assert.AreEqual("string column 2", sheet.Cell(3, 3).Value);
Assert.AreEqual("test", sheet.Cell(3, 4).Value);
Assert.AreEqual("false", sheet.Cell(3, 5).Value);
Assert.AreEqual(43.21, sheet.Cell(3, 6).Value);
Assert.AreEqual(yesterday, sheet.Cell(3, 7).Value);
Assert.AreEqual("", sheet.Cell(3, 7).Style.DateFormat.Format); // TODO: Do we need a different default here for dates?
Assert.AreEqual("", sheet.Cell(3, 8).Value);
Assert.AreEqual("not a formula", sheet.Cell(3, 9).Value);
}
private static void WriteEmployeeRows(ReportDTO <QuickJobTimeReportDTO> report, IXLWorksheet excelSheet)
{
var columns = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I" };
var row = EMPLOYEE_ROW_START;
if (report.Data.Employees.Any())
{
excelSheet.Row(7).InsertRowsAbove(report.Data.Employees.Count());
}
foreach (var employeeRow in report.Data.Employees.OrderBy(x => x.TaskCategory).ThenBy(x => x.TaskName).ThenBy(x => x.EmployeeName))
{
SetCellContentWithBorder(excelSheet, row, 1, employeeRow.EmployeeName, alignLeft: true, addLeftBorder: true);
excelSheet.Cell(row, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
SetCellContentWithBorder(excelSheet, row, 3, employeeRow.TaskCategory, alignLeft: true);
SetCellContentWithBorder(excelSheet, row, 4, employeeRow.TaskName, alignLeft: true);
excelSheet.Cell(row, 5).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
SetCellContentWithBorder(excelSheet, row, 6, (double)employeeRow.Regular);
SetCellContentWithBorder(excelSheet, row, 7, (double)employeeRow.Overtime);
excelSheet.Cell(row, 8).SetFormulaA1($"SUM({columns[5]}{row },{columns[6]}{row})");
excelSheet.Cell(row, 8).Style.Border.RightBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 8).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
row++;
}
if (report.Data.Employees.Any())
{
excelSheet.Cell(row, 6).SetFormulaA1($"SUM({columns[5]}{EMPLOYEE_ROW_START }:{columns[5]}{row - 1})");
excelSheet.Cell(row, 6).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 6).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 6).Style.Font.Bold = true;
excelSheet.Cell(row, 7).SetFormulaA1($"SUM({columns[6]}{EMPLOYEE_ROW_START }:{columns[6]}{row - 1})");
excelSheet.Cell(row, 7).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 7).Style.Font.Bold = true;
excelSheet.Cell(row, 8).SetFormulaA1($"SUM({columns[5]}{row }:{columns[6]}{row})");
excelSheet.Cell(row, 8).Style.Border.RightBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 8).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
excelSheet.Cell(row, 8).Style.Font.Bold = true;
}
}
/// <summary>
/// Retrieve column position by name in first row.
/// </summary>
/// <param name="sheet">Page to find column</param>
/// <param name="columnName">Name to find column in first row</param>
/// <returns>Position of column name or 0 when not found</returns>
/// <exception cref="ArgumentNullException">When <paramref name="columnName"/> is null o whitespace</exception>
public static int ColumnByName(this IXLWorksheet sheet, string columnName)
{
if (string.IsNullOrWhiteSpace(columnName))
{
throw new ArgumentNullException(nameof(columnName), "Enter the column name to get position.");
}
if (sheet.RowCount() <= 0)
{
return(0);
}
for (var i = 1; i <= sheet.ColumnUsedCount(); i++)
{
if (sheet.Row(1).Cell(i).Value.ToString()?.ToUpper() == columnName.ToUpper())
{
return(i);
}
}
return(0);
}
private static int insertHardwareHeaders(this IXLWorksheet worksheet, int row)
{
IXLRow headerRow = worksheet.Row(row);
headerRow.Style.Font.SetBold();
headerRow.Cell("A").Value = "Name";
headerRow.Cell("B").Value = "Model Number";
headerRow.Cell("C").Value = "Manufacturer";
headerRow.Cell("D").Value = "Quantity";
headerRow.Cell("E").Value = "List Price";
headerRow.Cell("F").Value = "Unit Cost";
headerRow.Cell("G").Value = "Total Cost";
headerRow.Cell("H").Value = "Unit Labor";
headerRow.Cell("I").Value = "Total Labor";
row++;
return(row);
}
public bool DeleteEmployee(int id)
{
try
{
string filePath = Path.Combine(_hostingEnvironment.WebRootPath, "Database", "Employees.xlsx");
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
workSheet.Row(id).Delete();
}
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
private int GetColumnNumber(String sheetName, String colName)
{
worksheet = workbook.Worksheet(sheetName);
IXLRow r = worksheet.Row(1);
while (r != worksheet.LastRowUsed())
{
foreach (IXLCell c in r.CellsUsed())
{
if (c.Value.Equals(colName))
{
return(c.Address.ColumnNumber); // Starts with 0
}
}
r = r.RowBelow();
}
return(-1);
}
private async Task DrawStockOutTable(IXLWorksheet ws)
{
//string fontName = "Arial";
//double fontSize = 10;
ws.Row(8).Height = 30;
ws.Cell("A8").Value = "Order No";
ws.Range("A8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("A8").Style.Font.FontColor = XLColor.White;
ws.Cell("B8").Value = "Order Date";
ws.Range("B8:C8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("B8:C8").Style.Font.FontColor = XLColor.White;
ws.Range("B8:C8").Merge();
ws.Cell("D8").Value = "Item Name";
ws.Range("D8:G8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("D8:G8").Style.Font.FontColor = XLColor.White;
ws.Range("D8:G8").Merge();
ws.Cell("H8").Value = "Qty";
ws.Range("H8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("H8").Style.Font.FontColor = XLColor.White;
ws.Cell("I8").Value = "Price";
ws.Range("I8:J8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("I8:J8").Style.Font.FontColor = XLColor.White;
ws.Range("I8:J8").Merge();
ws.Cell("K8").Value = "Discount";
ws.Range("K8:L8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("K8:L8").Style.Font.FontColor = XLColor.White;
ws.Range("K8:L8").Merge();
ws.Cell("M8").Value = "Amount Paid";
ws.Range("M8:N8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96);
ws.Range("M8:N8").Style.Font.FontColor = XLColor.White;
ws.Range("M8:N8").Merge();
await Task.Delay(0);
}
public bool ReadLine()
{
if (CurrentRowNumber == 0)
{
CurrentRowNumber = _firstRowNumber - 1;
}
while (CurrentRowNumber < _lastRowNumber)
{
CurrentRowNumber++;
_currentRow = _worksheet.Row(CurrentRowNumber);
if (!_currentRow.IsEmpty())
{
return(true);
}
}
return(false);
}
/// <summary>
/// parse fixed type workbook ; if worksheet_name specific it will locate the matching one
/// </summary>
public XlsxParser(XLWorkbook wb, IEnumerable <XlsxColumn> required_columns,
bool column_name_match_case = false,
string worksheet_name = null, bool worksheet_name_matchcase = false)
{
Columns = required_columns;
if (worksheet_name != null)
{
ws = wb.Worksheets.First(w => string.Equals(w.Name, worksheet_name, worksheet_name_matchcase ? StringComparison.CurrentCulture : StringComparison.CurrentCultureIgnoreCase));
}
else
{
ws = wb.Worksheets.First();
}
var header = ws.Row(1);
foreach (var cell in header.CellsUsed())
{
if (cell.Value == null || !(cell.Value is string))
{
continue;
}
var val = cell.Value as string;
foreach (var r in required_columns.Where(t => t.ColumnIndex == -1))
{
if (string.Equals(r.Name, val, column_name_match_case ? StringComparison.CurrentCulture : StringComparison.CurrentCultureIgnoreCase))
{
r.ColumnIndex = cell.WorksheetColumn().ColumnNumber();
continue;
}
}
}
var q = required_columns.FirstOrDefault(w => w.ColumnIndex == -1);
if (q != null)
{
throw new Exception($"can't find required column [{q.Name}]");
}
RowCount = ws.RangeUsed().RowCount();
}
private void FillWorksheet(IXLWorksheet worksheet, List <CategorizedWord> catWords)
{
worksheet.Cell(WORD_CELL).Value = WORD;
worksheet.Cell(LANG_CELL).Value = LANG;
worksheet.Cell(CAT_CELL).Value = CAT;
worksheet.Cell(TRAN_CELL).Value = TRAN;
worksheet.Row(1).Style.Font.Bold = true;
for (int i = 0; i < catWords.Count; i++)
{
var word = _context.Words.Find(catWords[i].WordId);
var language = _context.Languages.Find(word.LanguageId);
var category = _context.Categories.Find(catWords[i].CategoryId);
var translations = _context.Translations.Where(t => t.CategorizedWordId == catWords[i].Id).ToList();
worksheet.Cell(i + 2, WORD_IND).Value = word.ThisWord;
worksheet.Cell(i + 2, LANG_IND).Value = language.Name;
worksheet.Cell(i + 2, CAT_IND).Value = category.Name;
worksheet.Cell(i + 2, TRAN_IND).Value = _helper.TranslationsToString(translations);
}
}
public Employee GetEmployeeById(int id)
{
string filePath = Path.Combine(_hostingEnvironment.WebRootPath, "Database", "Employees.xlsx");
var emp = new Employee();
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
var empRow = workSheet.Row(id);
if (empRow.CellsUsed() != null)
{
emp.Id = empRow.Cell(1).GetValue <int>();
emp.FirstName = empRow.Cell(2).GetValue <string>();
emp.LastName = empRow.Cell(3).GetValue <string>();
emp.Team = empRow.Cell(4).GetValue <string>().ToTeam();
emp.Email = empRow.Cell(5).GetValue <string>();
}
}
return(emp);
}
public async Task <string> GetGroupByNameAsync(string excelPath, ExcelCell groupByNameCell)
{
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetGroupByNameAsync - Creating a new file stream to extract names from source Excel at { excelPath }");
}
string groupName;
using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
XLWorkbook excel = new XLWorkbook(fs);
int workSheetCount = excel.Worksheets.Count;
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetGroupByNameAsync - workSheetCount = { workSheetCount }");
}
IXLWorksheet worksheet = excel.Worksheet(workSheetCount);
groupName = worksheet.Row(groupByNameCell.Row).Cell(XLHelper.GetColumnNumberFromLetter(groupByNameCell.Column)).Value.ToString();
}
return(groupName);
}
///<summary>
///returns next spare row number
///</summary>
public static int AddLinesSummaryStatisticToSheet(IXLWorksheet toSheet, int startRow, List <LinesTotalStatisticDto> data)
{
//Table Header
toSheet.Cell(startRow, 1).Value = DEBS.Translate("Line.SummaryReport");
RowStyle_H2(toSheet.Row(startRow));
startRow++;
int colIndex = 1;
toSheet.Cell(startRow, colIndex).Value = DEBS.Translate("Report.Month");
toSheet.Cell(startRow + 1, colIndex).Value = DEBS.Translate("Report.linesCount");
toSheet.Cell(startRow + 2, colIndex).Value = DEBS.Translate("Report.totalStudents");
toSheet.Cell(startRow + 3, colIndex).Value = DEBS.Translate("Report.totalPrice");
colIndex++;
for (int i = 0; i < data.Count; i++)
{
toSheet.Cell(startRow, colIndex + i).Value = i + 1;
toSheet.Cell(startRow + 1, colIndex + i).Value = data[i].linesCount;
toSheet.Cell(startRow + 2, colIndex + i).Value = data[i].totalStudents;
toSheet.Cell(startRow + 3, colIndex + i).Value = data[i].totalPrice;
}
return(startRow + 4);
}
public void ExportToExcel(List <DataTable> datatables, string sheet_prefix)
{
if (datatables == null)
{
return;
}
foreach (DataTable dt in datatables)
{
if (dt.Rows.Count == 0)
{
continue;
}
IXLWorksheet worksheet = null;
lock (workbook)
{
worksheet = workbook.Worksheets.Add(sheet_name(sheet_prefix, dt.TableName));
}
// speedup by doing this in parallel
worksheet.Cell(1, 1).InsertTable(dt);
foreach (var col in worksheet.Columns())
{
try
{
col.Cells(2, worksheet.LastRowUsed().RowNumber()).DataType =
Double.TryParse(worksheet.Row(2).Cell(col.ColumnNumber()).Value.ToString(), out double is_number) ?
XLCellValues.Number :
XLCellValues.Text;
}
catch
{
col.Cells(2, worksheet.LastRowUsed().RowNumber()).DataType = XLCellValues.Text;
}
}
}
}
private void AssertHistorySheet(IXLWorksheet worksheet, ExportTagDto expectedTagData)
{
Assert.IsNotNull(worksheet);
AssertHeadingsInHistorySheet(worksheet);
var expectedData = expectedTagData.History;
Assert.AreEqual(expectedData.Count + 1, worksheet.RowsUsed().Count());
var rowIdx = 2; // Start at 2 because Row(1) is the header
foreach (var historyDto in expectedData)
{
var row = worksheet.Row(rowIdx++);
Assert.AreEqual(expectedTagData.TagNo, row.Cell(ExcelConverter.HistorySheetColumns.TagNo).Value);
Assert.AreEqual(historyDto.Description, row.Cell(ExcelConverter.HistorySheetColumns.Description).Value);
AssertDateTime(historyDto.CreatedAtUtc, row.Cell(ExcelConverter.HistorySheetColumns.Date));
Assert.AreEqual(historyDto.CreatedBy, row.Cell(ExcelConverter.HistorySheetColumns.User).Value);
AssertInt(historyDto.DueInWeeks, row.Cell(ExcelConverter.HistorySheetColumns.DueInWeeks).Value);
}
}
private HttpResponseMessage writeToExcel(DataTable dt)
{
XLWorkbook Workbook = new XLWorkbook(@"c:\temp\Sample.xlsx");
HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
IXLWorksheet Worksheet = Workbook.Worksheets.First();
Debug.WriteLine(Worksheet);
IXLCell cellfornewdata = Worksheet.Cell(8, 1);
cellfornewdata.InsertTable(dt.AsEnumerable());
Worksheet.Row(8).Delete();
Workbook.SaveAs(@"c:\temp\Sample.xlsx");
try
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"c:\temp\Sample.xlsx");
workbook.ConverterSetting.SheetFitToPage = true;
workbook.CustomDocumentProperties.Add("_MarkAsFinal", true);
workbook.CustomDocumentProperties.Add("The Editor", "E-iceblue");
workbook.CustomDocumentProperties.Add("Phone number1", 81705109);
workbook.CustomDocumentProperties.Add("Revision number", 7.12);
workbook.CustomDocumentProperties.Add("Revision date", DateTime.Now);
workbook.SaveToFile(@"c:\temp\Sample.pdf", FileFormat.PDF);
Process.Start(@"c:\temp\Sample.pdf");
var pdf = @"c:\temp\Sample.pdf";
var stream = new FileStream(pdf, FileMode.Open, FileAccess.Read);
result.Content = new StreamContent(stream);
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/pdf");
return(result);
}
catch (Exception ex)
{
Debug.WriteLine("Ëxception is thrown");
Debug.WriteLine(ex.Message);
return(result);
}
}
private static void TryParseRow(Config config, IXLWorksheet ws, JsonSerializerSettings settings, string json)
{
try
{
var data = JsonConvert.DeserializeObject <Dictionary <string, object> >(json, settings);
var row = ws.Row(RowI);
for (var i = 0; i < config.Columns.Count; ++i)
{
var columnI = i + 1;
var column = config.Columns[i];
var value = data.ContainsKey(column.Name) ? data[column.Name] : null;
column.CreateCell(row.Cell(columnI), value, config.SubHeader && RowI == 2 ? "string" : null);
}
RowI += 1;
}
catch (Exception x)
{
Console.Error.WriteLine(x.ToString());
}
}
public bool EditEmployee(Employee emp)
{
try
{
string filePath = Path.Combine(_hostingEnvironment.WebRootPath, "Database", "Employees.xlsx");
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
var empRow = workSheet.Row(emp.Id);
empRow.Cell(2).SetValue(emp.FirstName);
empRow.Cell(3).SetValue(emp.LastName);
empRow.Cell(4).SetValue(emp.Team.ToString());
empRow.Cell(5).SetValue(emp.Email);
}
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
public void zCreateEvaluationPlansReport(DataTable dt)
{
ClosedXML.Excel.XLWorkbook workbook = new XLWorkbook(ClosedXML.Excel.XLEventTracking.Disabled);
// workbook.Worksheets.Add(dt.TableName.ToString());
// var worksheet = workbook.Worksheet(dt.TableName.ToString());
IXLWorksheet worksheet = workbook.Worksheets.Add(dt.TableName.ToString());
int dtcolumncount = dt.Columns.Count;
for (int i = 0; i < dtcolumncount; i++)
{
worksheet.Cell(1, (i + 1)).Value = dt.Columns[i].Caption;
worksheet.Range(2, i + 1, dt.Rows.Count + 1, i + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dtcolumncount; j++)
{
worksheet.Cell(i + 2, j + 1).Value = dt.Rows[i][j];
}
}
//Format top row
var rangeheader = worksheet.Range(1, 1, 1, dtcolumncount);
rangeheader.Style.Font.Bold = true;
worksheet.Row(1).Height = 30;
worksheet.Columns(1, dtcolumncount).AdjustToContents(1, dt.Rows.Count + 1);
SaveWorkbook(ref workbook);
workbook.Dispose();
}
static void Main(string[] args)
{
IXLWorkbook workbook = new XLWorkbook(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\Excel Örnek\\Verilerim.xlsx");
IXLWorksheet worksheet = workbook.Worksheet("Veriler");
IXLRange range = worksheet.RangeUsed();
IXLRangeRow rangeRow = range.FirstRow();
IXLRow row = worksheet.Row(5);
IXLColumn column = worksheet.Column("C");
double width = column.ColumnRight(5).Width;
Console.WriteLine("Sütun Genişliği:" + width);
Console.WriteLine("Value : " + row.FirstCell().Value);
Console.WriteLine("C5'in Değeri: " + worksheet.Cell("C5").Value);
IXLAddress address = worksheet.Cell("C5").Address;
IXLCell cell = worksheet.Cell("C5");
for (int i = 1; i <= range.RowCount(); i++)
{
if (range.Row(i).Cell(2).Value.ToString() == "Yılmaz")
{
IXLAddress address1 = range.Row(i).Cell(2).Address;
Console.WriteLine(address1.RowNumber + " / " + address1.ColumnLetter);
break;
}
}
Console.Read();
}
private void AssertTagSheet(IXLWorksheet worksheet, IList <ExportTagDto> expectedTagData)
{
Assert.IsNotNull(worksheet);
AssertHeadingsInTagSheet(worksheet);
Assert.AreEqual(expectedTagData.Count + 1, worksheet.RowsUsed().Count());
for (var i = 0; i < expectedTagData.Count; i++)
{
var tag = expectedTagData.ElementAt(i);
var row = worksheet.Row(i + 2); // + 2 because Row(1) is the header
Assert.AreEqual(tag.TagNo, row.Cell(ExcelConverter.TagSheetColumns.TagNo).Value);
Assert.AreEqual(tag.Description, row.Cell(ExcelConverter.TagSheetColumns.Description).Value);
Assert.AreEqual(tag.NextDueAsYearAndWeek, row.Cell(ExcelConverter.TagSheetColumns.NextInYearAndWeek).Value);
AssertInt(tag.NextDueWeeks, row.Cell(ExcelConverter.TagSheetColumns.NextDueWeeks).Value);
Assert.AreEqual(tag.Journey, row.Cell(ExcelConverter.TagSheetColumns.Journey).Value);
Assert.AreEqual(tag.Step, row.Cell(ExcelConverter.TagSheetColumns.Step).Value);
Assert.AreEqual(tag.Mode, row.Cell(ExcelConverter.TagSheetColumns.Mode).Value);
Assert.AreEqual(tag.PurchaseOrderTitle, row.Cell(ExcelConverter.TagSheetColumns.Po).Value);
Assert.AreEqual(tag.AreaCode, row.Cell(ExcelConverter.TagSheetColumns.Area).Value);
Assert.AreEqual(tag.ResponsibleCode, row.Cell(ExcelConverter.TagSheetColumns.Resp).Value);
Assert.AreEqual(tag.DisciplineCode, row.Cell(ExcelConverter.TagSheetColumns.Disc).Value);
Assert.AreEqual(tag.Status, row.Cell(ExcelConverter.TagSheetColumns.PresStatus).Value);
Assert.AreEqual(tag.RequirementTitles, row.Cell(ExcelConverter.TagSheetColumns.Req).Value);
Assert.AreEqual(tag.Remark, row.Cell(ExcelConverter.TagSheetColumns.Remark).Value);
Assert.AreEqual(tag.StorageArea, row.Cell(ExcelConverter.TagSheetColumns.StorageArea).Value);
Assert.AreEqual(tag.ActionStatus, row.Cell(ExcelConverter.TagSheetColumns.ActionStatus).Value);
Assert.AreEqual(tag.CommPkgNo, row.Cell(ExcelConverter.TagSheetColumns.CommPkg).Value);
Assert.AreEqual(tag.McPkgNo, row.Cell(ExcelConverter.TagSheetColumns.McPkg).Value);
AssertInt(tag.ActionsCount, row.Cell(ExcelConverter.TagSheetColumns.Actions).Value);
AssertInt(tag.OpenActionsCount, row.Cell(ExcelConverter.TagSheetColumns.OpenActions).Value);
AssertInt(tag.OverdueActionsCount, row.Cell(ExcelConverter.TagSheetColumns.OverdueActions).Value);
AssertInt(tag.AttachmentsCount, row.Cell(ExcelConverter.TagSheetColumns.Attachments).Value);
AssertBool(tag.IsVoided, row.Cell(ExcelConverter.TagSheetColumns.Voided).Value);
}
}
private void CreateColumns(IXLWorksheet worksheet)
{
var rowAdress = 5;
foreach (Columns column in Enum.GetValues(typeof(Columns)))
{
worksheet.Cell(rowAdress, (int)column).Value = column.ToString();
if (column == Columns.勤務時間 || column == Columns.労働時間 || column == Columns.休憩時間)
{
worksheet.Column((int)column).Width = 12;
}
if (column == Columns.詳細)
{
worksheet.Column((int)column).Width = 6;
}
}
var columncells = worksheet.Row(rowAdress).CellsUsed();
columncells.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
columncells.Style.Fill.BackgroundColor = XLColor.FromHtml("#f0ffff");
SetBorder(columncells);
}
private static void AddSummaryWorksheet(IXLWorksheet sheet, List <string> list)
{
sheet.Row(1).Cell(1).Value = "Compte";
sheet.Row(1).Cell(2).Value = "Nombre";
sheet.Row(1).Cell(3).Value = "Prix";
var index = 2;
foreach (var item in list)
{
sheet.Row(index).Cell(1).Value = item;
sheet.Row(index).Cell(2).FormulaA1 = $"={item}!_{item}_COUNT";
sheet.Row(index).Cell(3).FormulaA1 = $"={item}!_{item}_PRICE";
sheet.Row(index).Cell(3).Style.NumberFormat.Format = "#,##0.00";
index++;
}
var used = sheet.RangeUsed().Rows(1, index - 1);
used.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
used.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
}
private static void AddSummaryTable(IXLWorksheet sheet, int index, int lastDataRow)
{
index += 2;
sheet.Row(index).Cell(3).FormulaR1C1 = string.Format("=COUNTIF(H:H,\"CMU\")+COUNTIF(H:H,\"CMU_AZ\")+COUNTIF(H:H,\"CMU_UNIGE\")+COUNTIF(H:H,\"CMU_DBU\")+COUNTIF(H:H,\"CMU_HUG\")+COUNTIF(H:H,\"CMU_IEH2\")+COUNTIF(H:H,\"ARVE-UNIGE\")+COUNTIF(H:H,\"ARVE-BELS\")+COUNTIF(H:H,\"ARVE-CUI\")+COUNTIF(H:H,\"ARVE-DBU\")+COUNTIF(H:H,\"ARVE-ISE\")+COUNTIF(H:H,\"ARVE_AZ\")+COUNTIF(H:H,\"ARVE-MATH\")+COUNTIF(H:H,\"ARVE-OBS\")+COUNTIF(H:H,\"ARVE-TERRE\")+COUNTIF(H:H,\"ARVE\")", lastDataRow);
sheet.Row(index).Cell(4).Value = "commandes UNIGE";
sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00";
sheet.Row(index).Cell(7).FormulaR1C1 = string.Format("=SUMIF(H:H,\"CMU\",G:G)+SUMIF(H:H,\"CMU_AZ\",G:G)+SUMIF(H:H,\"CMU_UNIGE\",G:G)+SUMIF(H:H,\"CMU_DBU\",G:G)+SUMIF(H:H,\"CMU_HUG\",G:G)+SUMIF(H:H,\"CMU_IEH2\",G:G)+SUMIF(H:H,\"ARVE-UNIGE\",G:G)+SUMIF(H:H,\"ARVE-BELS\",G:G)+SUMIF(H:H,\"ARVE-CUI\",G:G)+SUMIF(H:H,\"ARVE-DBU\",G:G)+SUMIF(H:H,\"ARVE-ISE\",G:G)+SUMIF(H:H,\"ARVE_AZ\",G:G)+SUMIF(H:H,\"ARVE-MATH\",G:G)+SUMIF(H:H,\"ARVE-OBS\",G:G)+SUMIF(H:H,\"ARVE-TERRE\",G:G)+SUMIF(H:H,\"ARVE\",G:G)", lastDataRow);
index++;
sheet.Row(index).Cell(3).FormulaR1C1 = string.Format("=COUNTIF(H:H,\"CMU_GE\")+COUNTIF(H:H,\"CMU_AUTRESUISSE\")+COUNTIF(H:H,\"CMU_IDS\")+COUNTIF(H:H,\"CMU_ILLRERO\")+COUNTIF(H:H,\"CMU_NEBIS\")+COUNTIF(H:H,\"CMU_RENOUVAUD\")+COUNTIF(H:H,\"CMU_NEBIS\")+COUNTIF(H:H,\"ARVE-GE\")+COUNTIF(H:H,\"ARVE_AUTRESUISSE\")+COUNTIF(H:H,\"ARVE-IDS\")+COUNTIF(H:H,\"ARVE-ILLRERO\")+COUNTIF(H:H,\"ARVE-NEBIS\")+COUNTIF(H:H,\"ARVE-RENOUVAUD\")", lastDataRow);
sheet.Row(index).Cell(4).Value = "commandes Suisse";
sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00";
sheet.Row(index).Cell(7).FormulaR1C1 = string.Format("=SUMIF(H:H,\"CMU_GE\",G:G)+SUMIF(H:H,\"CMU_AUTRESUISSE\",G:G)+SUMIF(H:H,\"CMU_IDS\",G:G)+SUMIF(H:H,\"CMU_ILLRERO\",G:G)+SUMIF(H:H,\"CMU_NEBIS\",G:G)+SUMIF(H:H,\"CMU_RENOUVAUD\",G:G)+SUMIF(H:H,\"CMU_NEBIS\",G:G)+SUMIF(H:H,\"ARVE-GE\",G:G)+SUMIF(H:H,\"ARVE_AUTRESUISSE\",G:G)+SUMIF(H:H,\"ARVE-IDS\",G:G)+SUMIF(H:H,\"ARVE-ILLRERO\",G:G)+SUMIF(H:H,\"ARVE-NEBIS\",G:G)+SUMIF(H:H,\"ARVE-RENOUVAUD\",G:G)", lastDataRow);
index++;
// sheet.Row(index).Cell(3).FormulaR1C1 = string.Format("=COUNTIF(H:H,\"SUBITO\")", lastDataRow);
// sheet.Row(index).Cell(4).Value = "commandes Subito (All.)";
// sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00";
// sheet.Row(index).Cell(7).FormulaR1C1 = string.Format("=SUMIF(H:H,\"SUBITO\",G:G)", lastDataRow);
//
// index++;
sheet.Row(index).Cell(3).FormulaR1C1 = string.Format("=COUNTIF(H:H,\"CMU_ETRANGER\")+COUNTIF(H:H,\"CMU_BL\")+COUNTIF(H:H,\"CMU_NLM\")+COUNTIF(H:H,\"CMU_SUBITO\")+COUNTIF(H:H,\"CMU_SUDOC\")+COUNTIF(H:H,\"ARVE-ETRANGER\")+COUNTIF(H:H,\"ARVE-NLM\")+COUNTIF(H:H,\"ARVE-SUBITO\")+COUNTIF(H:H,\"ARVE-SUDOC\")", lastDataRow);
sheet.Row(index).Cell(4).Value = "commandes Etranger";
sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00";
sheet.Row(index).Cell(7).FormulaR1C1 = string.Format("=SUMIF(H:H,\"CMU_ETRANGER\",G:G)+SUMIF(H:H,\"CMU_BL\",G:G)+SUMIF(H:H,\"CMU_NLM\",G:G)+SUMIF(H:H,\"CMU_SUBITO\",G:G)+SUMIF(H:H,\"CMU_SUDOC\",G:G)+SUMIF(H:H,\"ARVE-ETRANGER\",G:G)+SUMIF(H:H,\"ARVE-NLM\",G:G)+SUMIF(H:H,\"ARVE-SUBITO\",G:G)+SUMIF(H:H,\"ARVE-SUDOC\",G:G)", lastDataRow);
index++;
sheet.Row(index).Cell(3).FormulaR1C1 = string.Format("=COUNTIF(H:H,\"CMU_OA\")+COUNTIF(H:H,\"ARVE_OA\")", lastDataRow);
sheet.Row(index).Cell(4).Value = "Open Access online";
sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00";
sheet.Row(index).Cell(7).FormulaR1C1 = string.Format("=SUMIF(H:H,\"CMU_OA\",G:G)+SUMIF(H:H,\"ARVE_OA\",G:G)", lastDataRow);
sheet.Row(index).Cells(3, 7).Style.Border.BottomBorder = XLBorderStyleValues.Thick;
index++;
sheet.Row(index).Cells(3, 7).Style.Font.Bold = true;
sheet.Row(index).Cell(3).FormulaA1 = string.Format("=SUM(C{0}:C{1})", index - 5, index - 1);
sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00";
sheet.Row(index).Cell(7).FormulaA1 = string.Format("=SUM(G{0}:G{1})", index - 5, index - 1);
}
private static void AppendArray(IXLWorksheet worksheet, string[] headers)
{
for (int index = 0; index < headers.Length; index++) {
worksheet.Row(1).Cell(index + 1).Value = headers[index];
}
}
private static void AddOrdersWorksheet(IXLWorksheet sheet, IList <Order> orders)
{
var cell = 1;
sheet.Row(1).Cell(cell++).Value = "Compte";
sheet.Row(1).Cell(cell++).Value = "Nom";
sheet.Row(1).Cell(cell++).Value = "Prénom";
sheet.Row(1).Cell(cell++).Value = "Email";
sheet.Row(1).Cell(cell++).Value = "N° commande";
sheet.Row(1).Cell(cell++).Value = "Date commande";
sheet.Row(1).Cell(cell++).Value = "Date envoi";
sheet.Row(1).Cell(cell++).Value = "Prix";
sheet.Row(1).Cell(cell++).Value = "Localisation";
sheet.Row(1).Cell(cell++).Value = "Type Doc";
sheet.Row(1).Cell(cell++).Value = "Périodique";
sheet.Row(1).Cell(cell++).Value = "Année";
sheet.Row(1).Cell(cell++).Value = "Vol";
sheet.Row(1).Cell(cell++).Value = "pp.";
var index = 2;
sheet.ColumnWidth = 11;
foreach (var order in orders)
{
sheet.Row(index).Cell(1).Value = order.InvoiceAccount;
sheet.Row(index).Cell(2).Value = order.Name;
sheet.Row(index).Cell(3).Value = order.FirstName;
sheet.Row(index).Cell(4).Value = order.EMail;
sheet.Row(index).Cell(5).Value = order.IlLinkId;
sheet.Row(index).Cell(6).Style.DateFormat.Format = "dd.MM.yyyy";
sheet.Row(index).Cell(6).Value = order.OrderDate;
sheet.Row(index).Cell(7).Style.DateFormat.Format = "dd.MM.yyyy";
sheet.Row(index).Cell(7).Value = order.SendDate;
sheet.Row(index).Cell(8).Style.NumberFormat.Format = "#,##0.00";
sheet.Row(index).Cell(8).Value = order.Price;
sheet.Row(index).Cell(9).Value = order.Localisation;
sheet.Row(index).Cell(10).Value = order.DocumentType;
sheet.Row(index).Cell(11).Value = order.Title;
sheet.Row(index).Cell(12).Value = order.Year;
sheet.Row(index).Cell(13).Value = order.Volume;
sheet.Row(index).Cell(14).Value = order.Pages;
index++;
}
var tablename = $"Table_{sheet.Name}";
var table = sheet.Range(sheet.FirstColumn().FirstCell(), sheet.LastRowUsed().LastCellUsed()).CreateTable(tablename);
table.ShowAutoFilter = true;
table.Theme = XLTableTheme.TableStyleMedium2;
}
private static void AddDetailsWorksheet(IXLWorksheet sheet, IList <Order> orders, DateTime startDate, DateTime endDate)
{
sheet.PageSetup.PageOrientation = XLPageOrientation.Landscape;
sheet.PageSetup.PaperSize = XLPaperSize.A4Paper;
var cell = 1;
sheet.Row(1).Cell(cell++).Value = "Compte";
sheet.Row(1).Cell(cell++).Value = "Nom";
sheet.Row(1).Cell(cell++).Value = "Prénom";
sheet.Row(1).Cell(cell++).Value = "Email";
sheet.Row(1).Cell(cell++).Value = "N° commande";
sheet.Row(1).Cell(cell++).Value = "Date envoi";
sheet.Row(1).Cell(cell++).Value = "Prix";
sheet.Row(1).Cell(cell++).Value = "Localisation";
sheet.Row(1).Cell(cell++).Value = "Type Doc";
sheet.Row(1).Cell(cell++).Value = "Périodique";
sheet.Row(1).Cell(cell++).Value = "Année";
sheet.Row(1).Cell(cell++).Value = "Vol";
sheet.Row(1).Cell(cell++).Value = "pp.";
var index = 2;
sheet.ColumnWidth = 11;
foreach (var order in orders)
{
sheet.Row(index).Cell(1).Value = order.InvoiceAccount;
sheet.Row(index).Cell(2).Value = order.Name;
sheet.Row(index).Cell(3).Value = order.FirstName;
sheet.Row(index).Cell(4).Value = order.EMail;
sheet.Row(index).Cell(5).Value = order.IlLinkId;
sheet.Row(index).Cell(6).Style.DateFormat.Format = "dd.MM.yyyy";
sheet.Row(index).Cell(6).Value = order.SendDate;
sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00";
sheet.Row(index).Cell(7).Value = order.Price;
sheet.Row(index).Cell(8).Value = order.Localisation;
sheet.Row(index).Cell(9).Value = order.DocumentType;
sheet.Row(index).Cell(10).Value = order.Title;
sheet.Row(index).Cell(11).Value = order.Year;
sheet.Row(index).Cell(12).Value = order.Volume;
sheet.Row(index).Cell(13).Value = order.Pages;
index++;
}
var lastDataRow = index - 1;
var tablename = $"Table_{sheet.Name}";
var table = sheet.Range(1, 1, index - 1, 13).CreateTable(tablename);
table.ShowAutoFilter = false;
table.Theme = XLTableTheme.TableStyleMedium2;
sheet.Row(index).Cell(1).FormulaA1 = $"=COUNT(G2:G{lastDataRow})";
sheet.Row(index).Cell(1).AddToNamed("Count");
sheet.Row(index).Cell(2).Value = string.Format("commandes du {0:dd MMMM} au {1:dd MMMM yyyy}", startDate, endDate);
sheet.Row(index).Cell(6).Value = "Total:";
sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00";
sheet.Row(index).Cell(7).FormulaA1 = $"=SUM(G2:G{lastDataRow})";
sheet.Range(index, 1, index, 1).AddToNamed($"_{sheet.Name}_COUNT", XLScope.Worksheet);
sheet.Range(index, 7, index, 7).AddToNamed($"_{sheet.Name}_PRICE", XLScope.Worksheet);
sheet.LastRowUsed().Cells(1, 13).Style.Border.BottomBorder = XLBorderStyleValues.Thick;
sheet.LastRowUsed().Cells(1, 13).Style.Font.Bold = true;
// Add table if more than 10 order or more than 1 location
if (orders.Count >= 10 || orders.Select(o => o.Localisation).Distinct().Count() != 1)
{
AddSummaryTable(sheet, index, lastDataRow);
}
}
public ActionResult ExportDataSetToExcel(DataSet dsExcelData, string sheetName1, string sheetName2, string fileNameWithoutExtension, bool totalRequired = true, bool passwordRequired = true)
{
using (XLWorkbook wb = new XLWorkbook())
{
//sheetName1 = sheetName1.Replace(" ", "_");
//sheetName2 = sheetName2.Replace(" ", "_");
fileNameWithoutExtension = fileNameWithoutExtension.Replace(" ", "_");
//if (dsExcelData.Tables[0].Rows.Count > 0)
//{
dsExcelData.Tables[0].TableName = sheetName1;
IXLWorksheet workSheet = wb.Worksheets.Add(dsExcelData.Tables[0]);
IXLRow row = workSheet.Row(dsExcelData.Tables[0].Rows.Count + 1);
IXLRange range = workSheet.RangeUsed();
IXLTable xlTable = range.AsTable();
if (totalRequired)
{
string colLetterForSNo = GetExcelColumnLetter(xlTable, "#");
if (!string.IsNullOrEmpty(colLetterForSNo))
{
row.Cell(colLetterForSNo).Value = string.Empty;
}
string colLetterForDaysPresent = GetExcelColumnLetter(xlTable, "Days Present");
if (!string.IsNullOrEmpty(colLetterForDaysPresent))
{
row.Cell(colLetterForDaysPresent).Value = string.Empty;
}
}
if (passwordRequired)
{
wb.Worksheet(sheetName1).Protect("123456");
}
//}
//if (dsExcelData.Tables[1].Rows.Count > 0)
//{
dsExcelData.Tables[1].TableName = sheetName2;
IXLWorksheet workSheet2 = wb.Worksheets.Add(dsExcelData.Tables[1]);
IXLRow row2 = workSheet2.Row(dsExcelData.Tables[1].Rows.Count + 1);
IXLRange range2 = workSheet2.RangeUsed();
IXLTable xlTable2 = range2.AsTable();
if (totalRequired)
{
string colLetterForSNo = GetExcelColumnLetter(xlTable2, "#");
if (!string.IsNullOrEmpty(colLetterForSNo))
{
row2.Cell(colLetterForSNo).Value = string.Empty;
}
string colLetterForDaysPresent = GetExcelColumnLetter(xlTable2, "Days Present");
if (!string.IsNullOrEmpty(colLetterForDaysPresent))
{
row2.Cell(colLetterForDaysPresent).Value = string.Empty;
}
}
if (passwordRequired)
{
wb.Worksheet(sheetName2).Protect("123456");
}
//}
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename= " + fileNameWithoutExtension + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
return(null);
}
}
/// <summary>
/// In bảng thống kê toàn trường
/// </summary>
/// <param name="sheet">sheet</param>
/// <param name="indexs">danh sách index hàng chứa tổng hợp các khối</param>
/// <param name="rowStart">chỉ số hàng bắt đầu in</param>
/// <param name="columns">danh sách cột</param>
/// <returns>chỉ số hàng tiếp theo có thể được In tiếp dữ liệu</returns>
private List <int> SetToanTruong(IXLWorksheet sheet, List <int> indexs, ref int rowStart, List <ColumnDetail> columns)
{
List <int> range = new List <int>();
sheet.Row(rowStart).InsertRowsBelow(indexs.Count + 1);
int rowBegin = Convert.ToInt32(rowStart.ToString());
string[] khoi = new string[] { "6", "7", "8", "9" };
for (int j = 0; j < indexs.Count; j++)
{
for (int i = 1; i <= columns.Count; i++)
{
var cell = sheet.Cell(rowStart, i);
cell.Style.Font.Bold = false;
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
#region Thiết lập cột
switch (columns[i - 1].FieldName)
{
case "TEN_TRUONG":
cell.Value = khoi[j];
break;
case "SO_LUONGHS":
cell.FormulaA1 = string.Format("=SUM({0},{1},{2},{3},{4})", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 5).Address.ToString(), sheet.Cell(rowStart, 7).Address.ToString(), sheet.Cell(rowStart, 9).Address.ToString(), sheet.Cell(rowStart, 11).Address.ToString());
break;
case "GIOI_TS":
cell.FormulaA1 = string.Format("={0}", sheet.Cell(indexs[j], 3).Address.ToString());
break;
case "GIOI_TL":
cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString());
break;
case "KHA_TS":
cell.FormulaA1 = string.Format("={0}", sheet.Cell(indexs[j], 5).Address.ToString());
break;
case "KHA_TL":
cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString());
break;
case "TRUNGBINH_TS":
cell.FormulaA1 = string.Format("={0}", sheet.Cell(indexs[j], 7).Address.ToString());
break;
case "TRUNGBINH_TL":
cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString());
break;
case "YEU_TS":
cell.FormulaA1 = string.Format("={0}", sheet.Cell(indexs[j], 9).Address.ToString());
break;
case "YEU_TL":
cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString());
break;
case "KEM_TS":
cell.FormulaA1 = string.Format("={0}", sheet.Cell(indexs[j], 11).Address.ToString());
break;
case "KEM_TL":
cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString());
break;
case "TBTROLEN_TS":
cell.FormulaA1 = string.Format("=SUM({0},{1},{2})", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 5).Address.ToString(), sheet.Cell(rowStart, 7).Address.ToString());
break;
case "TBTROLEN_TL":
cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 13).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString());
break;
case "TBTROLEN_XEP_HANG":
cell.FormulaA1 = cell.FormulaA1 = string.Format("=RANK({0},{1}:{2},0)", sheet.Cell(rowStart, 14).Address.ToString(), this.GetAddressString(sheet.Cell(rowBegin, 14), true), this.GetAddressString(sheet.Cell(rowBegin + khoi.Length - 1, 14), true));
break;
default:
break;
}
#endregion
//set cell type
if (columns[i - 1].DataType == XLCellValues.Number)
{
cell.DataType = columns[i - 1].DataType;
cell.Style.NumberFormat.NumberFormatId = 10;
}
}
range.Add(rowStart);
rowStart++;
}
return(range);
}
private static void ImportTache(IXLWorksheet sheet, ref int line, Tache t, JourEvenement jour, Planning p, FestivArtsContext ctx)
{
IXLRow timeRow = sheet.Row(line);
line++;
int maxB = t.GetMaxBenevoleByDay(jour.Id);
Regex regex = new Regex("^([0-9]+)");
var aAjouter = new Dictionary <int, HashSet <int> >();
for (int l = 0; l < maxB; l++)
{
IXLRow r = sheet.Row(line);
int i = ExcelUtils.FIRST_PLAN_COLUMN;
foreach (CreneauDef d in jour.CreneauDefs.OrderBy(s => s.NoCreneau))
{
var cell = r.Cell(i);
string benStr = cell.Value.ToString();
if (!string.IsNullOrWhiteSpace(benStr))
{
var m = regex.Match(benStr.Trim());
if (m.Success)
{
int id = int.Parse(m.Groups[0].Captures[0].Value);
var b = ctx.Benevoles.FirstOrDefault(s => s.Id == id);
if (b == null)
{
throw new ImportException(string.Format("Cell ({0}) Tache {1} : n° de bénévole introuvable : {2}'", cell.Address.ToStringRelative(true), t.Nom, benStr));
}
var c = t.Creneaux.FirstOrDefault(s => s.CreneauDefId == d.Id);
if (c == null)
{
throw new ImportException(string.Format("Cell ({0}) Tache {1} : creneau introuvable. Creneau def {2}'", cell.Address.ToStringRelative(true), t.Nom, d.Id));
}
if (!aAjouter.ContainsKey(b.Id))
{
aAjouter.Add(b.Id, new HashSet <int>());
}
if (!aAjouter[b.Id].Contains(c.Id))
{
aAjouter[b.Id].Add(c.Id);
}
}
else
{
throw new ImportException(string.Format("Cell ({0}) ne correspond pas a un n° de bénévole : {1}'", cell.Address.ToStringRelative(true), benStr));
}
}
i++;
}
line++;
}
if (aAjouter.Count > 0)
{
var anciensA = ctx.Affectations.Where(s => s.Creneau.CreneauDef.JourId == jour.Id &&
s.PlanningId == p.Id && s.Creneau.TacheId == t.Id).ToList();
foreach (Affectation a in anciensA)
{
if (aAjouter.ContainsKey(a.BenevoleId) && aAjouter[a.BenevoleId].Contains(a.CreneauId))
{
//L'affectation existait déjà, on ne fait rien
aAjouter[a.BenevoleId].Remove(a.CreneauId);
}
else
{
//N'existe plus, on la supprime;
ctx.Affectations.Remove(a);
}
}
foreach (var kv in aAjouter.Where(s => s.Value.Count > 0).ToList())
{
foreach (var v in kv.Value)
{
Affectation a = new Affectation()
{
BenevoleId = kv.Key,
CreneauId = v,
PlanningId = p.Id
};
ctx.Affectations.Add(a);
}
}
ctx.SaveChanges();
}
}
public static XLWorkbook GetExcel(TableModel[] models)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet sheet = wb.Worksheets.Add("Sheet1");
// ЗАГОЛОВКИ
IXLRange range = sheet.Range(sheet.Cell(1, "A"), sheet.Cell(2, "I"));
// заливка
range.Style.Fill.SetBackgroundColor(XLColor.FromHtml("#fcecdc"));
// жирный шрифт
range.Style.Font.SetBold();
// выравнивание
range.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
range.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
// ширина заголовков
double factor = 1.145; // множитель (чтобы ширина была как у оригинального excel)
sheet.Column("A").Width = 24.13 * factor;
sheet.Column("B").Width = 33.63 * factor;
sheet.Column("C").Width = 11.88 * factor;
sheet.Column("D").Width = 9.25 * factor;
sheet.Column("E").Width = 10.5 * factor;
sheet.Column("F").Width = 52.75 * factor;
sheet.Column("G").Width = 8.75 * factor;
sheet.Column("H").Width = 8.75 * factor;
sheet.Column("I").Width = 33.25 * factor;
// подписи у заголовков и объединение ячеек для заголовков
sheet.Range(sheet.Cell(1, "A"), sheet.Cell(2, "A"))
.Merge().Value = "Component type";
sheet.Range(sheet.Cell(1, "B"), sheet.Cell(2, "B"))
.Merge().Value = "Name";
sheet.Range(sheet.Cell(1, "C"), sheet.Cell(2, "C"))
.Merge().Value = "Nominal";
sheet.Range(sheet.Cell(1, "D"), sheet.Cell(2, "D"))
.Merge().Value = "Deviation";
sheet.Range(sheet.Cell(1, "E"), sheet.Cell(2, "E"))
.Merge().Value = "Case type";
sheet.Range(sheet.Cell(1, "F"), sheet.Cell(2, "F"))
.Merge().Value = "Comment";
sheet.Range(sheet.Cell(1, "G"), sheet.Cell(1, "H"))
.Merge().Value = "quantity";
sheet.Cell(2, "G").Value = "00";
sheet.Cell(2, "H").Value = "01";
sheet.Range(sheet.Cell(1, "I"), sheet.Cell(2, "I"))
.Merge().Value = "Remark:";
// границы у заголовков
XLBorderStyleValues styleBorder = XLBorderStyleValues.Medium;
range.Style.Border.SetOutsideBorder(styleBorder);
range.Style.Border.SetInsideBorder(styleBorder);
#region ЛОГИКА ВЫВОДА ДАННЫХ
int currentRow = 3;
IXLCells cellRange;
for (int i = 0; i < models.Length; i++)
{
TableModel tm = models[i];
#region ЛОГИКА ОТДЕЛЕНИЯ ГРУПП КОМПОНЕНТОВ
// чтобы не выйти за пределы массива
if (i > 0 && tm.ComponentType != models[i - 1].ComponentType)
{
range = sheet.Range(sheet.Cell(currentRow, "A"), sheet.Cell(currentRow, "i")).Merge();
// границы
range.Style.Border.SetOutsideBorder(XLBorderStyleValues.Medium);
// заливка
range.Style.Fill.SetBackgroundColor(XLColor.FromHtml("#f8f4f4"));
currentRow++;
}
#endregion
cellRange = sheet.Row(currentRow).Cells("A", "I");
// выравнивание текста
cellRange.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
cellRange.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
// границы
styleBorder = XLBorderStyleValues.Thin;
cellRange.Style.Border.SetInsideBorder(styleBorder);
cellRange.Style.Border.SetOutsideBorder(styleBorder);
sheet.Cell(currentRow, "A").Value = tm.ComponentType;
sheet.Cell(currentRow, "B").Value = tm.Name;
sheet.Cell(currentRow, "C").Value = tm.Nominal;
sheet.Cell(currentRow, "D").Value = tm.Deviation;
sheet.Cell(currentRow, "E").SetValue(tm.CaseType);
sheet.Cell(currentRow, "F").Value = tm.Comment;
sheet.Cell(currentRow, "G").Value = tm.Quanity.ZeroZero;
sheet.Cell(currentRow, "H").Value = tm.Quanity.ZeroOne;
currentRow++;
}
#endregion
// СТИЛИ ДЛЯ ВСЕХ ЯЧЕЕК
cellRange = sheet.CellsUsed();
// шрифт
cellRange.Style.Font.SetFontName("Arial Cyr");
cellRange.Style.Font.SetFontSize(9);
return(wb);
}
private static IXLRow FindRowById(string id, IXLWorksheet worksheet)
{
var lastRowUsed = worksheet.LastRowUsed().RowNumber();
for (int rowIndex = 0; rowIndex <= lastRowUsed; rowIndex++) {
if (worksheet.Row(rowIndex).Cell(1).GetValue<string>().ToLower() == id.ToLower()) {
return worksheet.Row(rowIndex);
}
}
return null;
}
private int GetColumnNumber(String sheetName, String colName)
{
worksheet = workbook.Worksheet(sheetName);
IXLRow r = worksheet.Row(1);
while (r != worksheet.LastRowUsed())
{
foreach(IXLCell c in r.CellsUsed())
{
if (c.Value.Equals(colName))
return c.Address.ColumnNumber; // Starts with 0
}
r = r.RowBelow();
}
return -1;
}
public void AddContainerInfo(IXLWorksheet ws, Container container)
{
ws.Cell("A1").SetValue(container.ExporterName).Style.Font.FontSize = 20;
ws.Range("A1:E1").Merge();
ws.Cell("A2").SetValue(container.ExporterAddress).Style.Alignment.WrapText = true;
ws.Range("A2:B3").Merge();
ws.Cell("A5").SetValue("Shipped Per");
ws.Cell("B5").SetValue(container.ShippedPer);
ws.Range("B5:C5").Merge();
ws.Cell("A6").SetValue("On/About");
ws.Cell("B6").SetValue(container.OnAbout);
ws.Range("B6:C6").Merge();
ws.Cell("A7").SetValue("From");
ws.Cell("B7").SetValue(container.From);
// ws.Row(7)
// .Style
// .Alignment.SetVertical(XLAlignmentVerticalValues.Top)
// .Alignment.SetWrapText(true);
ws.Range("B7:C7").Merge();
ws.Row(7).Height = 70;
ws.Cell("A8").SetValue("Airway Bill No. \nor B/L No.");
ws.Cell("B8").SetValue(container.AirwayBillNumber);
ws.Range("B8:C8").Merge();
ws.Row(8).Height = 30;
// ws.Row(8).Style.Alignment.SetWrapText(true);
ws.Cell("A9").SetValue("Letter of\nCredit No.");
ws.Cell("B9").SetValue(container.LetterOfCreditNumber);
ws.Range("B9:C9").Merge();
ws.Row(9).Height = 30;
ws.Cell("A10").SetValue("Drawn Under");
ws.Cell("B10").SetValue(container.DrawnUnder)
.Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
// .Alignment.SetWrapText(true);
ws.Range("B10:C10").Merge();
ws.Row(10).Height = 70;
// ws.Row(10).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
ws.Range("A1:A10").Style.Font.Bold = true;
ws.Range("B5:C10").Style.Border.BottomBorder = XLBorderStyleValues.Thin;
// ws.Range("B5:C10").Style.Alignment.WrapText = true;
ws.Rows("5:10").Style.Alignment.SetWrapText(true)
.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
//Importer
ws.Cell("E5").SetValue(container.ImporterName +
"\n" + container.ImporterAddress +
"\n" + "(TAX CERTIFICATE NO. " + container.ImporterTaxCertificateNumber + ")")
.Style
.Alignment.SetVertical(XLAlignmentVerticalValues.Top)
.Alignment.SetWrapText();
ws.Range("E5:H10").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
//Container Number + Container Date
ws.Cell("F2").SetValue("INVOICE NO:");
ws.Cell("G2").SetValue(container.CustomsInvoiceNumber);
ws.Cell("F3").SetValue("DATE:");
ws.Cell("G3").SetValue(container.Date);
ws.Range("F2:F3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
ws.Range("G2:H2").Merge();
ws.Range("G3:H3").Merge();
ws.Range("G2:G3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Range("F2:H3").Style.Font.Bold = true;
}
private void AddPerformanceTitles(IXLWorksheet performanceSheet)
{
//global styles
performanceSheet.Style.Font.FontName = "verdana";
performanceSheet.Style.Font.FontSize = 10d;
//headings
performanceSheet.Cell(1, 1).Value = "Date";
performanceSheet.Cell(1, 2).Value = "Comment";
performanceSheet.Cell(1, 3).Value = "DAU";
performanceSheet.Cell(1, 4).Value = "Q Users";
performanceSheet.Cell(1, 5).Value = "% Users";
performanceSheet.Cell(1, 6).Value = "MAU";
performanceSheet.Cell(1, 7).Value = "Q users";
performanceSheet.Cell(1, 8).Value = "% users";
performanceSheet.Cell(1, 9).Value = "DAU/MAU";
performanceSheet.Cell(1, 10).Value = "DAU INDEX";
performanceSheet.Cell(1, 11).Value = "Instalaciones";
performanceSheet.Cell(1, 12).Value = "K Factor (overall)";
performanceSheet.Cell(1, 13).Value = "K Factor invite";
performanceSheet.Cell(1, 14).Value = "K Notification";
performanceSheet.Cell(1, 15).Value = "Viral Installs";
performanceSheet.Cell(1, 16).Value = "MKT Installs";
performanceSheet.Cell(1, 17).Value = "Visits";
performanceSheet.Cell(1, 18).Value = "Daily Sessions";
performanceSheet.Cell(1, 19).Value = "Time on site";
performanceSheet.Cell(1, 20).Value = "Daily Revenue";
performanceSheet.Cell(1, 21).Value = "ARP DAU";
performanceSheet.Cell(1, 22).Value = "ARP MAU";
performanceSheet.Cell(1, 23).Value = "ARP PU";
performanceSheet.Cell(1, 24).Value = "Pay Users";
performanceSheet.Cell(1, 25).Value = "% Pay. Users";
performanceSheet.Cell(1, 26).Value = "LTV";
performanceSheet.Cell(1, 27).Value = "CPA";
performanceSheet.Cell(1, 28).Value = "LVL 1";
performanceSheet.Cell(1, 29).Value = "%DAU";
performanceSheet.Cell(1, 30).Value = "LVL 2";
performanceSheet.Cell(1, 31).Value = "%DAU";
performanceSheet.Cell(1, 32).Value = "% Pasaje";
performanceSheet.Cell(1, 33).Value = "LVL 3";
performanceSheet.Cell(1, 34).Value = "%DAU";
performanceSheet.Cell(1, 35).Value = "% Pasaje";
performanceSheet.Cell(1, 36).Value = "LVL 4";
performanceSheet.Cell(1, 37).Value = "%DAU";
performanceSheet.Cell(1, 38).Value = "% Pasaje";
performanceSheet.Cell(1, 39).Value = "LVL5";
performanceSheet.Cell(1, 40).Value = "%DAU";
performanceSheet.Cell(1, 41).Value = "% Pasaje";
performanceSheet.Cell(1, 42).Value = "Weekly Average";
performanceSheet.Cell(1, 43).Value = "Monthly Average";
//row one styles
performanceSheet.Row(1).Style.Font.Bold = true;
}
public static IEnumerable<TranslationColumn> ReadCultures(IXLWorksheet worksheet)
{
var textValues = worksheet.Row(1).Cells().Where(cell => cell.Value != null).Select(cell => cell.Value.ToString()).ToList<String>();
var cols = textValues.Skip(2).Where(s => !s.Contains("[Comments]")).ToList<String>();
var list = new List<TranslationColumn>();
foreach (var s in cols)
{
try
{
var textColumn = new TranslationColumn(new CultureInfo(s));
textColumn.TextColumnIndex = textValues.IndexOf(s);
string commentsKey = s;
if (s != "")
commentsKey += " [Comments]";
else
commentsKey += "[Comments]";
var commentColumn = textValues.Skip(2).Where(t => t.Equals(commentsKey)).FirstOrDefault();
if (commentColumn != null)
textColumn.CommentColumnIndex = textValues.IndexOf(commentColumn);
list.Add(textColumn);
}
catch (CultureNotFoundException)
{
}
}
return list;
}
public int GetColumnCount(string sheetName, int rowNumber)
{
worksheet = workbook.Worksheet(sheetName);
return worksheet.Row(rowNumber).CellsUsed().Count();
}
private void CreateVerticalNumberHeader(IXLWorksheet workSheet, string HeadLineText,
int LineNumber, int StartColumnIndex)
{
workSheet.Cell(LineNumber, StartColumnIndex).Style
.Font.SetBold()
.Font.SetFontSize(9)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left)
.Alignment.SetTextRotation(90);
workSheet.Cell(LineNumber, StartColumnIndex).Style
.Alignment.WrapText = true;
workSheet.Cell(LineNumber, StartColumnIndex).Value = HeadLineText;
workSheet.Row(LineNumber).Height = 75;
workSheet.Column(StartColumnIndex).Width = 4;
}
private static void SetRows(IXLWorksheet ws)
{
ws.Cell(10, 1).Value = Model.NameOfMaterial;
ws.Cell(20, 43).Value = Model.NameOfMaterial;
var rowCount = Model.ResultTemperaturesModels.Count;
if (rowCount > 1)
{
ws.Row(20).InsertRowsBelow(rowCount - 1);
ws.Range(20, 43, 19 + rowCount, 44).Merge();
ws.Row(10).InsertRowsBelow(rowCount - 1);
ws.Range(10, 1, 9 + rowCount, 1).Merge();
}
}
private void AddFacebookInsight(IXLWorksheet performanceSheet, int n, FacebookInsights insight)
{
performanceSheet.Cell(n, 1).Value = insight.Date; //Date
performanceSheet.Cell(n, 3).Value = insight.DailyActiveUsers; //DAU
performanceSheet.Cell(n, 4).FormulaA1 = string.Format("C{0}-C{1}", n, n + 1); //Q Users
performanceSheet.Cell(n, 5).FormulaA1 = string.Format("IF(C{0}=0,0, D{1}/C{2})", n + 1, n, n + 1); //% Users
performanceSheet.Cell(n, 6).Value = insight.MonthlyActiveUsers; //MAU
performanceSheet.Cell(n, 7).FormulaA1 = string.Format("F{0}-F{1}", n, n + 1); //Q users
performanceSheet.Cell(n, 8).FormulaA1 = string.Format("IF(F{0}=0,0,G{1}/F{2})", n + 1, n, n + 1); //% Users
performanceSheet.Cell(n, 9).FormulaA1 = string.Format("IF(F{0}=0,0,C{1}/F{2})", n, n, n); //DAU/MAU
performanceSheet.Cell(n, 10).FormulaA1 = string.Format("IF(G{0}=0,0,D{1}/G{2})", n, n, n); //DAU INDEX
performanceSheet.Cell(n, 11).Value = insight.DailyAppInstalls; //Instalaciones
var virality = repository.Get<Virality, DateTime>(insight.Date);
if (virality == null) virality = new Virality();
performanceSheet.Cell(n, 12).Value = virality.Total; //K Factor (overall)
performanceSheet.Cell(n, 13).Value = virality.Invite; //K Factor invite
performanceSheet.Cell(n, 14).Value = virality.Notification; //K Notification
performanceSheet.Cell(n, 15).Value = virality.TotalInstallations; //Viral Installs
performanceSheet.Cell(n, 16).FormulaA1 = string.Format("K{0}-O{0}", n, n); //MKT Installs
var analytics = repository.Get<Analytics, DateTime>(insight.Date);
if (analytics == null) analytics = new Analytics();
performanceSheet.Cell(n, 17).Value = analytics.Visits; //Visits
performanceSheet.Cell(n, 18).FormulaA1 = string.Format("IF(C{0}=0,0,Q{1}/C{2})", n, n, n); //Daily Sessions
TimeSpan timeOnSite = new TimeSpan(0, 0, analytics.TimeOnSite);
performanceSheet.Cell(n, 19).Value = timeOnSite.ToString(); //Time on site
var revenue = repository.Get<Revenue, DateTime>(insight.Date);
if (revenue == null) revenue = new Revenue();
performanceSheet.Cell(n, 20).Value = revenue.TotalRevenue; //Daily Revenue
performanceSheet.Cell(n, 21).FormulaA1 = string.Format("IF(C{0}=0,0,T{1}/C{2})", n, n, n); //ARP DAU
performanceSheet.Cell(n, 22).FormulaA1 = string.Format("IF(F{0}=0,0,T{1}/F{2})", n, n, n); //ARP MAU
performanceSheet.Cell(n, 23).FormulaA1 = string.Format("IF(X{0}=0,0, T{1}/X{2})", n, n, n); //ARP PU
performanceSheet.Cell(n, 24).Value = revenue.TransactionCount; ///Pay Users
performanceSheet.Cell(n, 25).FormulaA1 = string.Format("X{0}/C{0}", n, n); //% Pay. Users
performanceSheet.Cell(n, 26).FormulaA1 = string.Format("L{0}*T{0}*I{0}", n, n); //LTV
performanceSheet.Cell(n, 27).Value = string.Empty; //CPA
performanceSheet.Cell(n, 28).FormulaA1 = "K" + n.ToString(); // Level 1
performanceSheet.Cell(n, 29).FormulaA1 = string.Format("IF(C{0}=0,0,AB{1}/C{2})", n, n, n); //%DAU
performanceSheet.Cell(n, 30).Value = analytics.Level2; //Level 2
performanceSheet.Cell(n, 31).FormulaA1 = string.Format("IF(C{0}=0,0,AD{1}/C{2})", n, n, n); //%DAU
performanceSheet.Cell(n, 32).FormulaA1 = string.Format("IF(AB{0}=0,0,AD{1}/AB{2})", n, n, n); //% Pasaje
performanceSheet.Cell(n, 33).Value = analytics.Level3; //Level 3
performanceSheet.Cell(n, 34).FormulaA1 = string.Format("IF(C{0}=0,0,AG{1}/C{2})", n, n, n); //%DAU
performanceSheet.Cell(n, 35).FormulaA1 = string.Format("IF(AD{0}=0,0,AG{1}/AD{2})", n, n, n); //% Pasaje
performanceSheet.Cell(n, 36).Value = analytics.Level4; //Level 4
performanceSheet.Cell(n, 37).FormulaA1 = string.Format("IF(C{0}=0,0,AJ{1}/C{2})", n, n, n); //%DAU
performanceSheet.Cell(n, 38).FormulaA1 = string.Format("IF(AG{0}=0,0,AJ{1}/AG{2})", n, n, n); //% Pasaje
performanceSheet.Cell(n, 39).Value = analytics.Level5; //Level 5
performanceSheet.Cell(n, 40).FormulaA1 = string.Format("IF(C{0}=0,0,AM{1}/C{2})", n, n, n); //%DAU
performanceSheet.Cell(n, 41).FormulaA1 = string.Format("IF(AJ{0}=0,0,AM{1}/AJ{2})", n, n, n); //% Pasaje
performanceSheet.Cell(n, 42).Value = string.Empty; //Weekly Average
performanceSheet.Cell(n, 43).Value = string.Empty; //Monthly Average
if (insight.Date.DayOfWeek == DayOfWeek.Saturday || insight.Date.DayOfWeek == DayOfWeek.Sunday)
{
performanceSheet.Row(n).Style.Fill.BackgroundColor = XLColor.Yellow;
}
}
