public static void FormatWorksheetColumn(
IXLWorkbook workbook,
DataTable dataTable,
string columnName,
XLDataType dataType
)
{
var columnNumber = dataTable.Columns.IndexOf(columnName) + 1;
workbook.Worksheet(1).Column(columnNumber).CellsUsed(c => c.Address.RowNumber != 1)
.SetDataType(dataType);
}
/// <summary>
/// ''' TODO AGGIUNGERE PARAMETRO ROUNDING OPPURE CELLFORMAT
/// ''' </summary>
/// ''' <param name="sheetName"></param>
/// ''' <param name="labelCell"></param>
/// ''' <param name="labelCellValue"></param>
/// ''' <param name="excludeHeaders"></param>
/// ''' <returns></returns>
public ExcelHelper AddRowTotal(string sheetName, string labelCell = "A", string labelCellValue = "TOT.", bool excludeHeaders = true)
{
IXLRange range = this.GetRangeUsed(sheetName);
if (range == null)
{
//SD.Log("range not found in " + sheetName, SD.LogLevel.Error);
return(this);
}
int rowsNumber = range.RowCount();
IXLCell lastCellUsed = range.LastCellUsed();
IXLColumn lastColUsed = lastCellUsed.WorksheetColumn();
IXLRow lastRowUsed = lastCellUsed.WorksheetRow();
string lastColLetter = lastColUsed.ColumnLetter();
int lastRowNumber = lastRowUsed.RowNumber();
IXLRows rows = lastRowUsed.InsertRowsBelow(1);
//IXLRow newRow = rows.Last();
var ws = this._workbook.Worksheets.Worksheet(sheetName);
IXLRow newRow = ws.LastRowUsed().RowBelow();
if (labelCell.Trim() != "")
{
newRow.Cell(labelCell).Value = labelCellValue;
newRow.Cell(labelCell).Style.Font.Bold = true;
}
var firstTotalCellAddress = newRow.FirstCell().CellRight().Address;
var lastTotalCellAddress = newRow.Cell(lastColLetter).Address;
IXLRange rangeTotal = this.GetRangeUsed(sheetName, firstTotalCellAddress, lastTotalCellAddress);
//int i = rangeTotal.Cells().Count() + 1;
int i = rangeTotal.ColumnCount() + 1;
int firstDataRowIndex = 0;
// escludo la riga delle intestazioni
if (excludeHeaders)
{
firstDataRowIndex = 2;
}
for (int k = 1; k <= i; k++)
{
XLDataType colDataType = newRow.Cell(k).CellAbove(1).DataType;
if (colDataType == XLDataType.Number)
{
string colLetter = newRow.Cell(k).Address.ColumnLetter;
string formula = "=SUM(" + colLetter + firstDataRowIndex.ToString() + ":" + colLetter + rowsNumber.ToString() + ")";
this.AddFormula(sheetName, newRow.Cell(k).Address, formula);
}
}
newRow.AsRange().RangeUsed().Style.Border.TopBorder = XLBorderStyleValues.Thick;
return(this);
}
/**************************************************************************/
private void SetContentCellType(
IXLWorksheet ws,
int Row,
int Col,
XLDataType CellType
)
{
try
{
ws.Cell(Row, Col).SetDataType(dataType: CellType);
}
catch (Exception ex)
{
this.DebugMsg(ex.Message);
}
}
internal static string GetName(this XLDataType type)
{
switch (type)
{
case XLDataType.Number: return("Number");
case XLDataType.Text: return("Text");
case XLDataType.Boolean: return("Boolean");
case XLDataType.DateTime: return("DateTime");
case XLDataType.TimeSpan: return("TimeSpan");
default: return("Unknown");
}
;
}
private void ReadAllSheetIndividual()
{
listDateCols.Clear();
listHeaderNameIndividual.Clear();
using (var workBook = new XLWorkbook(txtPathFrom.Text.Trim()))
{
// loop all sheet
foreach (IXLWorksheet worksheet in workBook.Worksheets)
{
string sheetName = worksheet.Name;
var rowUsed = worksheet.RowsUsed();
var colUsed = worksheet.ColumnsUsed();
foreach (var col in colUsed)
{
int colHeader = col.ColumnNumber();
string nameHeader = common.GetValue(worksheet, 1, col.ColumnNumber());
// get value use check col is DateTime type
XLDataType typeValue = worksheet.Cell(2, col.ColumnNumber()).DataType;
// checked type value
if (typeValue == XLDataType.DateTime)
{
listDateCols.Add(new HeaderName(colHeader, nameHeader));
}
else if (nameHeader != "")
{
listHeaderNameIndividual.Add(new HeaderName(colHeader, nameHeader));
}
}
if (listDateCols.Count > 0)
{
break;
}
}
}
// Fill data header name to combobox
this.Invoke(new Action(() => BindDataSourceCombobox()));
}
public IXLRows SetDataType(XLDataType dataType)
{
_rows.ForEach(c => c.DataType = dataType);
return(this);
}
public IXLRangeRow SetDataType(XLDataType dataType)
{
DataType = dataType;
return(this);
}
public IXLRangeColumn SetDataType(XLDataType dataType)
{
DataType = dataType;
return(this);
}
public IXLRangeColumns SetDataType(XLDataType dataType)
{
_ranges.ForEach(c => c.DataType = dataType);
return(this);
}
public ExcelAttribute(XLDataType dataType)
{
DataType = dataType;
}
public IXLColumns SetDataType(XLDataType dataType)
{
_columns.ForEach(c => c.DataType = dataType);
return(this);
}
public IXLRangeRows SetDataType(XLDataType dataType)
{
_ranges.ForEach(c => c.DataType = dataType);
return this;
}
public IXLCells SetDataType(XLDataType dataType)
{
this.ForEach <XLCell>(c => c.DataType = dataType);
return(this);
}
public static void SetValue(IXLWorksheet ws, int row, int column, object value, XLDataType valueDataType)
{
ws.Cell(row, column).SetValue(value).SetDataType(valueDataType);
}
