/// <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); }