/// <summary>
/// Tạo 1 cell
/// </summary>
/// <returns></returns>
public Workbook BindHeader(Workbook workbook, string title, int column)
{
Worksheet worksheet = workbook.Worksheets[0];
var list = new List <CellOption>();
var cell1 = new CellOption()
{
Merge = true,
Style = StyleTitleFile(),
RequiredType = false,
Type = "String",
Row = 0,
Column = 0,
TotalRow = 1,
TotalColumn = 3,
AutoFitRow = true,
Text = "SYSTEM LUMITEL"
};
list.Add(cell1);
var cell2 = NewCellOption(cell1);
cell2.Column = 3;
cell2.Text = title.ToUpper();
cell2.TotalColumn = column - 3;
list.Add(cell2);
workbook = BindList(workbook, list);
worksheet.Cells.SetRowHeightPixel(0, 50);
return(workbook);
}
/// <summary>
/// Bind dữ liệu vào 1 cell trong grid
/// </summary>
/// <param name="workbook"></param>
/// <param name="cellOption"></param>
/// <remarks>LongLD</remarks>
public Workbook BindText(Workbook workbook, CellOption cellOption)
{
Worksheet worksheet = workbook.Worksheets[0];
// Merge
if (cellOption.Merge)
{
worksheet.Cells.Merge(cellOption.Row, cellOption.Column, cellOption.TotalRow, cellOption.TotalColumn);
}
// PutValue và Style
worksheet = PutValueInType(worksheet, cellOption);
// return
return(workbook);
}
public Workbook BindHeaderCustomer(Workbook workbook, CellOption cellSystem, CellOption cellTitle, int height)
{
Worksheet worksheet = workbook.Worksheets[0];
var list = new List <CellOption>();
cellSystem.Style = StyleTitleFile();
cellSystem.Text = string.IsNullOrEmpty(cellSystem.Text) ? "Lumitel" : cellSystem.Text;
list.Add(cellSystem);
if (cellTitle != null)
{
cellTitle.Style = StyleTitleFile();
list.Add(cellTitle);
workbook = BindList(workbook, list);
}
worksheet.Cells.SetRowHeightPixel(cellSystem.Row, height);
return(workbook);
}
/// <summary>
/// Tạo 1 cell
/// </summary>
/// <returns></returns>
public CellOption NewCellOption(CellOption cell)
{
var newCell = new CellOption()
{
AutoFitRow = cell.AutoFitRow,
Column = cell.Column,
Merge = cell.Merge,
RequiredType = cell.RequiredType,
Row = cell.Row,
Style = cell.Style,
Text = cell.Text,
TotalRow = cell.TotalRow,
Type = cell.Type,
TotalColumn = cell.TotalColumn
};
return(newCell);
}
/// <summary>
/// Putvalue theo kiểu hay ko
/// </summary>
/// <remarks>LongLD</remarks>
private Worksheet PutValue <T>(Worksheet worksheet, CellOption cellOption, string value, T data)
{
// ko rỗng => show
if (!string.IsNullOrEmpty(value))
{
worksheet.Cells[cellOption.Row, cellOption.Column].PutValue(data);
}
else
{
// rỗng nhưng lại buộc theo kiểu dữ liệu => show
if (cellOption.RequiredType)
{
worksheet.Cells[cellOption.Row, cellOption.Column].PutValue(data);
}
// rỗng nhưng lại ko buộc theo kiểu dữ liệu => ko show
else
{
worksheet.Cells[cellOption.Row, cellOption.Column].PutValue("");
}
}
return(worksheet);
}
/// <summary>
/// PutValue theo định dạng
/// </summary>
/// <remarks>LongLD</remarks>
private Worksheet PutValueInType(Worksheet worksheet, CellOption cellOption)
{
switch (cellOption.Type.ToLower())
{
case "date":
var date = !string.IsNullOrEmpty(cellOption.Text) ?
Convert.ToDateTime(cellOption.Text, CultureInfo.GetCultureInfo("vi-vn")) :
new DateTime();
worksheet = PutValue(worksheet, cellOption, cellOption.Text, date);
cellOption.Style.Custom = "dd/MM/yyyy";
break;
case "datetime":
var dateT = !string.IsNullOrEmpty(cellOption.Text) ?
Convert.ToDateTime(cellOption.Text, CultureInfo.GetCultureInfo("vi-vn")) :
new DateTime();
worksheet = PutValue(worksheet, cellOption, cellOption.Text, dateT);
cellOption.Style.Custom = "dd/MM/yyyy HH:ss";
break;
case "datetimefull":
var dateTf = !string.IsNullOrEmpty(cellOption.Text) ?
Convert.ToDateTime(cellOption.Text, CultureInfo.GetCultureInfo("vi-vn")) :
new DateTime();
worksheet = PutValue(worksheet, cellOption, cellOption.Text, dateTf);
cellOption.Style.Custom = "dd/MM/yyyy HH:mm:ss";
break;
case "number":
var n = !string.IsNullOrEmpty(cellOption.Text) ?
decimal.Parse(cellOption.Text) : 0;
worksheet = PutValue(worksheet, cellOption, cellOption.Text, n);
cellOption.Style.Custom = "#,##0";
break;
case "numbern2":
var n2 = !string.IsNullOrEmpty(cellOption.Text) ?
decimal.Parse(cellOption.Text, CultureInfo.GetCultureInfo("en-us")) : 0;
worksheet = PutValue(worksheet, cellOption, cellOption.Text, n2);
cellOption.Style.Custom = "#,##0.00";
break;
default:
cellOption.Style.Number = '@';
worksheet.Cells[cellOption.Row, cellOption.Column].PutValue(cellOption.Text);
break;
}
if (cellOption.Merge)
{
if (cellOption.AutoFitRow)
{
worksheet.AutoFitRow(cellOption.Row, cellOption.Row + cellOption.TotalRow, cellOption.Column, cellOption.Column + cellOption.TotalColumn);
}
for (int i = 0; i < cellOption.TotalRow; i++)
{
for (int j = 0; j < cellOption.TotalColumn; j++)
{
worksheet.Cells[cellOption.Row + i, cellOption.Column + j].SetStyle(cellOption.Style);
}
}
}
else
{
if (cellOption.AutoFitRow)
{
worksheet.AutoFitRow(cellOption.Row, cellOption.Row + 1, cellOption.Column, cellOption.Column + 1);
}
worksheet.Cells[cellOption.Row, cellOption.Column].SetStyle(cellOption.Style);
}
return(worksheet);
}