public void AddListToSheet <T>(IEnumerable <T> listOfEntities,
string titleOfTable,
string worksheetName,
IEnumerable <string> headersToDelete = null)
{
if (listOfEntities is null)
{
return;
}
var headers = CreateHeaders(listOfEntities.First());
try
{
AddTitle(titleOfTable);
var emptyRow = ws.LastRowUsed()
.RowBelow();
AddHeadersToWorksheet(emptyRow, headers);
var table = CreateTable(listOfEntities, emptyRow);
DeleteTableColumn(table, headersToDelete);
CreateTableTotalSum(table);
}
catch (Exception ex)
{
throw;
}
ws.LastRowUsed().RowBelow().RowBelow();
}
public void TestColumnOldModel()
{
for (int row_idx = 2; row_idx < sheet.LastRowUsed().RowNumber(); row_idx++)
{
var cur_row = sheet.Row(row_idx);
var cell = cur_row.Cell((int)BikeStoreSheetCols.OldModel);
// Format is not required for this column.
// test if the value is string.
Assert.IsTrue(cell.TryGetValue <string>(out var cell_actual_val));
var cell_expected_val =
cur_row.Cell((int)BikeStoreSheetCols.OrderDate).GetValue <DateTime>().Year >
cur_row.Cell((int)BikeStoreSheetCols.ModelYear).GetValue <int>()
? "YES"
: "NO";
Assert.That(cell_expected_val, Is.EqualTo(cell_actual_val).IgnoreCase, $"Cell {cell.Address} value should be {cell_expected_val} but it is {cell_actual_val}");
// test whether this cell is actually a formula.
Assert.IsTrue(cell.HasFormula, $"Cell {cell.Address} should be formula");
// test whether the formula contains IF.
StringAssert.Contains("IF", cell.FormulaR1C1,
$"Cell {cell.Address} formula should include conditional");
// test whether the formula referencing correct rows and columns.
StringAssert.Contains("-4", cell.FormulaR1C1,
$"Cell {cell.Address} formula should reference column order_date ");
StringAssert.Contains("-12", cell.FormulaR1C1,
$"Cell {cell.Address} formula should reference column model_year");
Assert.IsTrue(Regex.Matches(cell.FormulaA1, cell.Address.RowNumber.ToString()).Count() == 2, $"Cell {cell.Address} is not referencing correct rows. ");
}
}
public void ExportToExcel(List <DataGridView> dgvs, string sheet_prefix)
{
if (dgvs == null)
{
return;
}
foreach (DataGridView dgv in dgvs)
{
DataTable dt = new DataTable();
foreach (DataGridViewColumn col in dgv.Columns)
{
dt.Columns.Add(col.HeaderText);
}
foreach (DataGridViewRow row in dgv.Rows)
{
DataRow new_row = dt.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
if (dgv.Columns[cell.ColumnIndex].Visible)
{
new_row[cell.ColumnIndex] = cell.Value == null || cell.Value.ToString() == "NaN" ? "" : cell.Value;
}
}
lock (dt) dt.Rows.Add(new_row);
}
foreach (DataGridViewColumn col in dgv.Columns)
{
if (!col.Visible)
{
dt.Columns.Remove(col.HeaderText);
}
}
IXLWorksheet worksheet = null;
lock (workbook)
{
worksheet = workbook.Worksheets.Add(dt, sheet_prefix.Substring(0, Math.Min(sheet_prefix.Length, 30 - dgv.Name.Length)) + "_" + dgv.Name);
}
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;
}
}
}
}
public void TestColumnListPrice()
{
for (var row_idx = 2; row_idx < sheet.LastRowUsed().RowNumber(); row_idx++)
{
var cur_row = sheet.Row(row_idx);
var cell = cur_row.Cell((int)BikeStoreSheetCols.ListPrice);
Assert.IsTrue(cell.Style.NumberFormat.Format.Contains("$"), $"cell format {cell.Address} should be $");
Assert.IsTrue(cell.Style.NumberFormat.Format.Contains("0.00"), $"cell format {cell.Address} should be 2 digit accurate");
}
}
private List <int> GetBoldIndex(IXLWorksheet ws1)
{
List <int> indexList = new List <int>();
for (int i = 2; i < ws1.LastRowUsed().RowNumber(); i++)
{
if (ws1.Cell(i, 1).Style.Font.Bold == true && ws1.Cell(i, 1).Value.ToString() != "")
{
indexList.Add(i - 1);
}
}
indexList.Add(ws1.LastRowUsed().RowNumber());
return(indexList);
}
/*
*
*
* functional objects(i.e. buttons, etc that do stuff)
*
*
*/
//receiveing button
private void button1_Click(object sender, EventArgs e)
{
//make sure the workbook file is selected
if (currentWb == null)
{
MessageBox.Show("You must select a file!");
return;
}
//make sure the ticket isn't left blank. This check isn't perfect, but a better one is in the works ;)
if (textBox1.Text == "")
{
MessageBox.Show("You can't receive nothing!");
return;
}
//check if the ticket has already been received or not
if (atIndex(currentWsh, textBox1.Text) > 0)
{
MessageBox.Show("This ticket has already been received!");
}
else
{
//create a new row, add the ticket, and update the received date for said ticket.
int rowNumber = currentWsh.LastRowUsed().RowNumber() + 1;
currentWsh.Cell(rowNumber, 1).Value = textBox1.Text;
currentWsh.Cell(rowNumber, 2).Value = DateTime.Now;
myDt.Rows.Add(textBox1.Text, DateTime.Now);
currentWb.Save();
xlDataGridRefresh(dataGridView1, currentWb, myDt);
MessageBox.Show("Ticket received successfully!");
}
}
// GET: api/Excel
public void Get()
{
XLWorkbook Workbook = new XLWorkbook(@"c:\temp\Sample.xlsx");
IXLWorksheet Worksheet = Workbook.Worksheets.First();
Debug.WriteLine(Worksheet);
int NumberOfLastRow = Worksheet.LastRowUsed().RowNumber();
IXLCell CellForNewData = Worksheet.Cell(8, 1);
DataTable datatable = new DataTable();
datatable.Columns.Add("Name");
datatable.Columns.Add("Marks");
datatable.Columns.Add("Rank");
var i = 10;
while (i > 0)
{
DataRow dr = datatable.NewRow();
dr["Name"] = "shakthi";
dr["Marks"] = "shiva";
dr["Rank"] = "sharan";
datatable.Rows.Add(dr);
--i;
}
CellForNewData.InsertTable(datatable.AsEnumerable());
Worksheet.Row(8).Delete();
Workbook.Save();
}
public bool AddEmployee(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 lastrow = workSheet.LastRowUsed().RowNumber();
var newemp = workSheet.Row(lastrow + 1);
newemp.Cell(1).SetValue(emp.Id.ToString());
newemp.Cell(2).SetValue(emp.FirstName);
newemp.Cell(3).SetValue(emp.LastName);
newemp.Cell(4).SetValue(emp.Team.ToString());
newemp.Cell(5).SetValue(emp.Email);
}
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
private IXLRow GetRow(IReadOnlyDayWorkLog logs, IXLWorksheet worksheet)
{
if (logs.GetStartTime() == null)
{
return(worksheet.LastRowUsed().RowBelow());
}
var LastRowUsed = worksheet.LastRowUsed();
if (GetDayWorkLog(LastRowUsed).GetStartTime()?.Date == logs.GetStartTime()?.Date)
{
return(LastRowUsed);
}
return(LastRowUsed.RowBelow());
}
/// <summary>
/// Parse xlsx sheet data into a list of dynamic objects
/// it will list for given columnNames ( if not null ) or for all columns ( if null )
/// if columnNamesIgnoreCase result object will contains lowercase properties
/// </summary>
public static List <dynamic> ParseXlsxData(this IXLWorksheet ws, HashSet <string> _columnNames = null, bool columnNamesIgnoreCase = true)
{
HashSet <string> columnNames = null;
if (columnNamesIgnoreCase && _columnNames != null)
{
columnNames = _columnNames.Select(w => w.ToLower()).ToHashSet();
}
else
{
columnNames = _columnNames;
}
var res = new List <dynamic>();
var columnDict = new Dictionary <string, int>();
var row = ws.FirstRow();
var lastCol = row.LastCellUsed().Address.ColumnNumber;
for (int ci = 1; ci <= lastCol; ++ci)
{
var cname = (string)row.Cell(ci).Value;
if (string.IsNullOrEmpty((string)cname))
{
continue;
}
if (columnNamesIgnoreCase)
{
cname = cname.ToLower();
}
if (columnNames == null || columnNames.Contains(cname))
{
columnDict.Add(cname, ci);
}
}
var lastRow = ws.LastRowUsed().RowNumber();
for (int ri = 2; ri <= lastRow; ++ri)
{
row = ws.Row(ri);
IDictionary <string, object> eo = new ExpandoObject();
foreach (var c in columnDict)
{
var cell = row.Cell(c.Value);
eo.Add(c.Key, cell.Value);
}
res.Add(eo);
}
return(res);
}
public void ExportToExcel(DataTable resultTable, string fileLocation, string sheetName, string color = "", int numberOfLastRow = 0, int startingCellIndex = 1)
{
XLWorkbook Workbook = new XLWorkbook(fileLocation);
IXLWorksheet Worksheet = Workbook.Worksheet(sheetName);
//Gets the last used row
if (numberOfLastRow == 0)
{
numberOfLastRow = Worksheet.LastRowUsed().RowNumber();
}
//Defines the starting cell for appeding (Row , Column)
IXLCell CellForNewData = Worksheet.Cell(numberOfLastRow + 1, startingCellIndex);
if (!color.Equals(""))
{
for (int i = 0; i < resultTable.Rows.Count; i++)
{
IXLRow RowForNewData = Worksheet.Row(numberOfLastRow + 1 + i);
RowForNewData.Style.Font.FontColor = XLColor.Red;
}
}
//InsertData - the data from the DataTable without the Column names ; InsertTable - inserts the data with the Column names
CellForNewData.InsertData(resultTable);
Workbook.SaveAs(fileLocation);
}
public bool AddHistory(History history)
{
try
{
string filePath = Path.Combine(_hostingEnvironment.WebRootPath, "Database", "History.xlsx");
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
var lastrow = workSheet.LastRowUsed().RowNumber();
var newHistory = workSheet.Row(lastrow + 1);
newHistory.Cell(1).SetValue(history.Id);
newHistory.Cell(2).SetValue(history.EmployeeIds.ToCSVString());
newHistory.Cell(3).SetValue(history.DaysOfWeek.ToString());
newHistory.Cell(4).SetValue(history.LastModified.ToString());
}
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
}
public int GetTableStart(IXLWorksheet worksheet, int tablePosition)
{
var lastRowUsed = worksheet.LastRowUsed();
int currentTablePosition = 1;
if (lastRowUsed == null)
{
throw new Exception("No tables in the worksheet");
}
if (tablePosition == 1)
{
return(1);
}
//If found space between tables add it to the end of tables rows;
for (int i = 1; i <= lastRowUsed.RowNumber(); i++)
{
if (worksheet.Cell(i, 1).IsEmpty() && !worksheet.Cell(i + 1, 1).IsEmpty())
{
currentTablePosition++;
if (currentTablePosition == tablePosition)
{
return(i + 1);
}
}
}
return(lastRowUsed.RowNumber() + 2);
}
public void EditRowColor(string fileLocation, string sheetName, int startIndex, string date)
{
XLWorkbook Workbook = new XLWorkbook(fileLocation);
IXLWorksheet Worksheet = Workbook.Worksheet(sheetName);
int NumberOfLastRow = Worksheet.LastRowUsed().RowNumber();
for (int i = startIndex; i < NumberOfLastRow; i++)
{
var cellValue = (Worksheet.Cell(i, 4).Value).ToString();
if (cellValue.Equals(""))
{
break;
}
var validity = DateTime.ParseExact(cellValue, "dd.MM.yyyy", null);
var todayDate = DateTime.ParseExact(date, "dd.MM.yyyy", null);
IXLRow excelRow = Worksheet.Row(i);
if (DateTime.Compare(validity, todayDate) < 0)
{
excelRow.Style.Fill.BackgroundColor = XLColor.Red;
}
else if (DateTime.Compare(validity, todayDate) == 0)
{
excelRow.Style.Fill.BackgroundColor = XLColor.Yellow;
}
}
Workbook.SaveAs(fileLocation);
}
public ExcelReader(string filename)
{
workbook = new XLWorkbook(File.Open(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite));
worksheet = workbook.Worksheets.First();
totalRows = worksheet.LastRowUsed().RowNumber();
totalColumns = worksheet.LastColumnUsed().ColumnNumber();
}
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 IEnumerable <ValuesBunch> ReadWorkSheet(IXLWorksheet worksheet)
{
var items = new List <ValuesBunch>();
var firstRow = worksheet.FirstRowUsed();
var lastRow = worksheet.LastRowUsed();
IXLRow currentRow = firstRow;
ValuesBunch valueItems = null;
while (currentRow.RowNumber() <= lastRow.RowNumber())
{
var currentRowFirstCell = currentRow.FirstCellUsed();
var correntRowLastCell = currentRow.LastCellUsed();
if (DateTime.TryParse(currentRowFirstCell?.CachedValue.ToString(), out var initialDate) && initialDate < DateTime.Now)
{
InitializeFinalDate(currentRowFirstCell, correntRowLastCell, initialDate, out var finalDate);
if (valueItems != null && valueItems.Count > 0)
{
items.Add(valueItems);
}
if (finalDate.HasValue)
{
valueItems = new ValuesBunch(initialDate, finalDate.Value);
}
else
{
valueItems = new ValuesBunch(initialDate);
}
}
else if (valueItems != null && currentRow != null && currentRow.CellsUsed().Count() > 0)
{
var currentTypeCell = currentRow.FirstCellUsed();
var rowDelta = ParseValueItems(valueItems, currentTypeCell);
if (valueItems != null && valueItems.Count > 0)
{
items.Add(valueItems);
valueItems = null;
}
currentRow = currentRow.RowBelow(rowDelta - currentRow.RowNumber());
}
currentRow = currentRow.RowBelow();
}
if (valueItems != null && valueItems.Count > 0)
{
items.Add(valueItems);
}
return(items);
}
private void AddTableCells(IXLWorksheet worksheet, IEnumerable <Containers> containers)
{
int rowIndex = worksheet.LastRowUsed().RowNumber() + 1;
foreach (var container in containers)
{
AddTableCell(worksheet, container, rowIndex);
rowIndex++;
}
}
/// <summary>
/// Excelのデータをセルごとに読み込んで2次元配列に代入する
/// </summary>
/// <param name="sheetNumber">シート番号(1から)</param>
/// <param name="workBook">XLWorkbook変数</param>
/// <returns>使用しているExcelのセルのデータをstringの2次元配列で返す</returns>
public string[,] ExtractionExcelData(int sheetNumber, XLWorkbook workBook)
{
IXLWorksheet workSheet = workBook.Worksheet(sheetNumber);
(int column, int row)xlCellAddress;
xlCellAddress.column = workSheet.LastColumnUsed().ColumnNumber();
xlCellAddress.row = workSheet.LastRowUsed().RowNumber();
string[,] xlStrDataArray = new string[xlCellAddress.column, xlCellAddress.row];
return(xlStrDataArray);
}
public static double getTotalHeight(IXLWorksheet ws, int startRow)
{
var totalHeight = 0.0;
foreach (var row in ws.Rows(startRow, ws.LastRowUsed().RowNumber()))
{
totalHeight += row.Height;
}
return totalHeight;
}
public void TestYearsTotals()
{
var totals_sheet = workbook.Worksheets.Worksheet(2);
var expected_vals = new Dictionary <int, double>();
for (int row_idx = 2; row_idx < sheet.LastRowUsed().RowNumber(); row_idx++)
{
var cur_row = sheet.Row(row_idx);
var order_date = cur_row.Cell((int)BikeStoreSheetCols.OrderDate).GetDateTime();
var amount = cur_row.Cell((int)BikeStoreSheetCols.LineTotalAfterDiscount).GetDouble();
if (!expected_vals.ContainsKey(order_date.Year))
{
expected_vals.Add(order_date.Year, 0);
}
expected_vals[order_date.Year] += amount;
}
var first_row = totals_sheet.Row(1);
var total_row = totals_sheet.Row(2);
for (int col = 2; ; col++)
{
if (!first_row.Cell(col).TryGetValue <int>(out var header_cell_val))
{
break;
}
var total_cell = total_row.Cell(col);
Assert.IsTrue(total_cell.HasFormula, $"Cell {total_cell.Address} should be formula");
Assert.IsTrue(total_cell.TryGetValue <double>(out var total_actual_value), $"Cell {total_cell.Address} value is not a number");
// test whether the formula contains IF.
StringAssert.Contains("SUMIF", total_cell.FormulaR1C1,
$"Cell {total_cell.Address} formula should include SUMIF");
Assert.That(total_actual_value, Is.EqualTo(expected_vals[header_cell_val]).Within(0.01),
$"Cell {total_cell.Address} value should be {expected_vals[header_cell_val]} but it is {total_actual_value}");
}
}
//Read Excel data to generic list - overloaded version 2.
public static IList <T> GetDataToList <T>(string filePath, string sheetName, Func <IList <string>, T> addRecord)
{
List <T> resultList = new List <T>();
using (XLWorkbook wB = new XLWorkbook(filePath))
{
IXLWorksheet wS = wB.Worksheet(1);
//DataTable dt = new DataTable();
//bool firstRow = true;
//Used for sheet row data to be added through delegation.
var firstRow = true;
int lastRow = wS.LastRowUsed().RowNumber();
var rows = wS.Rows(1, lastRow);
foreach (IXLRow row in rows)
{
if (firstRow)
{
////foreach (IXLCell cell in row.Cells())
////{
//// dt.Columns.Add(cell.Value.ToString());
////}
firstRow = false;
continue;
}
if (row.IsEmpty())
{
continue;
}
var rowData = new List <string>();
//else
//{
//dt.Rows.Add();
//int i = 0;
//rowData.Clear();
foreach (IXLCell cell in row.Cells(wS.FirstCellUsed().Address.ColumnNumber, wS.LastCellUsed().Address.ColumnNumber))
{
rowData.Add(cell.Value.ToString());
}
//foreach (IXLCell cell in row.Cells())
//{
// rowData.Add(cell.Value.ToString());
//}
resultList.Add(addRecord(rowData));
}
// }
return(resultList);
}
}
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;
}
}
}
}
public List <RowRuEng> GetRowsRuEng(int workSheetNumber)
{
IXLWorksheet worksheet = _workbook.Worksheet(workSheetNumber);
var list = new List <RowRuEng>();
if (worksheet.LastRowUsed() == null)
{
return(list);
}
for (int i = 1; i <= worksheet.LastRowUsed().RowNumber(); i++)
{
var ruStr = worksheet.Row(i).Cell(1).GetValue <string>();
var enStr = worksheet.Row(i).Cell(2).GetValue <string>();
var testValue = new RowRuEng(ruStr, enStr, i);
list.Add(testValue);
}
return(list);
}
private void AdicionarEstilo(IXLWorksheet worksheet, DataTable tabelaDados)
{
int ultimaColunaUsada = worksheet.LastColumnUsed().ColumnNumber();
int ultimaLinhaUsada = worksheet.LastRowUsed().RowNumber();
AdicionarEstiloCabecalho(worksheet, ultimaColunaUsada);
AdicionarEstiloCorpo(worksheet, tabelaDados, ultimaColunaUsada, ultimaLinhaUsada);
worksheet.ShowGridLines = false;
worksheet.ColumnsUsed().AdjustToContents();
worksheet.RowsUsed().AdjustToContents();
}
public virtual SheetParseResult <T> ParseSheet <T>(IXLWorksheet sheet, Func <T, List <string> > validateT = null) where T : class, new()
{
if (sheet == null)
{
throw new ArgumentNullException(nameof(sheet));
}
var result = new SheetParseResult <T>();
var headerRow = sheet.FirstRowUsed();
var lastRow = sheet.LastRowUsed();
var mappings = GetDefaultPropertyMapParsers <T>(headerRow);
//foreach over the rows and parse to T
foreach (var _row in sheet.Rows(firstRow: headerRow.RowBelow().RowNumber(), lastRow: lastRow.RowNumber()))
{
result.TotalRecordCount++;
var row = _row; //modified closure
var runningValidation = new List <string>(); //use to give feedback on parse and validation
var t = new T();
foreach (var m in mappings)
{
object val;
var cell = row.Cell(m.ExcelColumnIndex);
var didParse = m.TryGetProperty(propertyInfo: m.ObjectPropertyInfo, input: cell.GetString(), outVal: out val);
if (didParse)
{
m.ObjectPropertyInfo.SetValue(t, val);
}
else
{
runningValidation.Add($"{m.ObjectPropertyInfo.Name} did not parse.");
}
this.FillCellBackground(cell: ref cell, isValid: didParse);
}
if (runningValidation.Count == 0 && validateT != null)
{
runningValidation.AddRange(validateT(t));
}
if (runningValidation.Count == 0)
{
result.ValidList.Add(t);
}
this.FillRowBackgroundWithValidationMessage(row: ref row, isValid: runningValidation.Count == 0, validationMessages: runningValidation);
}
return(result);
}
public void WriteXlsx(string filepath, List <ResourceItem> resourceList)
{
foreach (var resource in resourceList)
{
var currentRow = _sheet.LastRowUsed().RowBelow();
WriteKeyValues(currentRow, resource);
WriteDefaultValue(currentRow, resource);
WriteLocaleValues(currentRow, resource);
}
_sheet.FirstRow().CellsUsed().Style.Fill.BackgroundColor = XLColor.Yellow;
_workBook.SaveAs(filepath);
}
/*
*
*
* FUNCTIONAL METHODS
*
*
*/
//method to return the row in an excel file that a certain item occurs at in the index
public int atIndex(IXLWorksheet sheet, string con)
{
int item;
for (item = 1; item <= sheet.LastRowUsed().RowNumber(); item++)
{
if (sheet.Cell(item, 1).Value.ToString() == con)
{
return(item);
}
}
return(0);
}
/// <summary>
/// Excelのデータをセルごとに読み込んでジャグ配列に代入する
/// </summary>
/// <param name="sheetNumber">シート番号(1から)</param>
/// <param name="workBook">XLWorkbook変数</param>
/// <returns>使用しているExcelのセルのデータをstringのジャグ配列で返す</returns>
public string[][] ExtractionExcelDataJagged(int sheetNumber, XLWorkbook workBook)
{
IXLWorksheet workSheet = workBook.Worksheet(sheetNumber);
(int column, int row)xlCellAddress;
xlCellAddress.column = workSheet.LastColumnUsed().ColumnNumber();
xlCellAddress.row = workSheet.LastRowUsed().RowNumber();
// ジャグ配列にExcelのセルのデータを入れる
string[][] xlStrDataJaggedArray = new string[xlCellAddress.row][];
xlStrDataJaggedArray = Enumerable.Range(0, xlCellAddress.column)
.Select(row => (new string[xlCellAddress.column]).Select(str => workSheet.Cell(1, 1).Value.ToString()).ToArray()).ToArray();
string[][] xlStrDataArray2 = new string[xlCellAddress.row][];
return(xlStrDataJaggedArray);
}
private void LoadEmpExcel(string path)
{
using (XLWorkbook empWb = new XLWorkbook(path))
{
//empRecords.Clear();
empWs = empWb.Worksheet(1);
for (int i = 2; i < empWs.LastRowUsed().RowNumber() + 1; i++)
//for (int i = 2; i < empWs.RowCount(); i++)
{
if (LoadEmpLine(i, path) != null)
{
empRecords.Add(LoadEmpLine(i, path));
}
}
}
}
private void LoadTrackerExcel(string path)
{
using (XLWorkbook trackerWb = new XLWorkbook(path))
{
// trackerRecords.Clear();
trackerWs = trackerWb.Worksheet(1);
//for (int i = 2; i < trackerWs.RowCount(); i++)
for (int i = 2; i < trackerWs.LastRowUsed().RowNumber() + 1; i++)
{
if (LoadTrackerLine(i, path) != null)
{
trackerRecords.Add(LoadTrackerLine(i, path));
}
}
}
}
private static bool SplitKD(IXLWorksheet worksheet, string title, int column)
{
// row to scan for KD
const int row = 6;
try
{
while (!worksheet.Cell(row, column).IsEmpty())
{
// scan for first KD derivative in TRIM LEVEL row
if (worksheet.Cell(row, column).GetString().EndsWith("KD"))
{
// remove existing column grouping - commented out as grouping no longer applied as you can't un-group on a protected worksheet
// worksheet.Columns(8, worksheet.LastColumnUsed().ColumnNumber()).Ungroup();
// add KD SPEC GROUP heading
worksheet.Cell(1, column).Value = title.ToUpper() + " KD";
// insert and size new divider column before KD derivatives
worksheet.Column(column).InsertColumnsBefore(1);
worksheet.Column(column).Width = 8;
// group non-KD derivatives - commented out as you can't un-group on a protected worksheet
// worksheet.Columns(8, column - 1).Group();
// merge non-KD SPEC GROUP heading
worksheet.Range(1, 8, 1, column - 1).Merge();
// group KD derivatives - commented out as you can't un-group on a protected worksheet
// worksheet.Columns(column + 1, worksheet.LastColumnUsed().ColumnNumber()).Group();
// merge KD SPEC GROUP heading
worksheet.Range(1, column + 1, 1, worksheet.LastColumnUsed().ColumnNumber()).Merge();
// add vertical KD title
worksheet.Cell(1, column).Value = title.ToUpper() + " KD";
// merge and format vertical divider heading
worksheet.Range(1, column, 9, column).Merge();
worksheet.Range(1, column, worksheet.LastRowUsed().RowNumber(), column).Style
.Font.SetBold(true)
.Font.SetFontColor(XLColor.White)
.Fill.SetBackgroundColor(XLColor.Black)
.Alignment.SetVertical(XLAlignmentVerticalValues.Bottom)
.Alignment.SetTextRotation(90);
// do for first KD derivative then break out
return true;
}
column = column + 1;
}
return false;
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
private void AddPerformanceFormatting(IXLWorksheet performanceSheet)
{
int lastRowUsed = performanceSheet.LastRowUsed().RowNumber();
//freeze panels
performanceSheet.SheetView.Freeze(1, 2);
//performance global styles
performanceSheet.Range(1, 1, performanceSheet.LastCellUsed().Address.RowNumber, performanceSheet.LastCellUsed().Address.ColumnNumber)
.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
//column specific formatting
//date columns
performanceSheet.Range(string.Format("A2:A{0}", lastRowUsed)).Style.DateFormat.Format = "dd/MM/yyyy";
//percentage formatting (0.00%)
performanceSheet.Ranges(string.Format("E2:E{0},H2:I{1},Y2:Y{2},AC2:AC{3},AE2:AF{4},AH2:AI{5},AK2:AL{6},AN2:AO{7}",
lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.NumberFormatId = 10;
//no decimal points
performanceSheet.Range(string.Format("R2:R{0}", lastRowUsed)).Style.NumberFormat.Format = "0";
//decimal format (0.00)
performanceSheet.Ranges(string.Format("J2:J{0},L2:N{1}",
lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "0.00";
//three decimal points (0.000)
performanceSheet.Range(string.Format("U2:U{0}",
lastRowUsed
)).Style.NumberFormat.Format = "0.000";
//money with two decimals ($ 0.00)
performanceSheet.Ranges(string.Format("T2:T{0},W2:W{1},Z2:Z{2}",
lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "$ 0.00";
//money with three decimals ($ 0.000)
performanceSheet.Range(string.Format("V2:V{0}",
lastRowUsed, lastRowUsed, lastRowUsed
)).Style.NumberFormat.Format = "$ 0.000";
// adjust to content
performanceSheet.Columns().AdjustToContents();
}
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 int GetRowCount(string sheetName)
{
worksheet = workbook.Worksheet(sheetName);
return worksheet.LastRowUsed().RowNumber();
}
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;
}
