/// <summary>
/// This function determines whether the given column in a workbook contains date values.
/// </summary>
/// <param name="workbook">A SmartXLS workbook object.</param>
/// <param name="col">Column to check.</param>
/// <param name="firstRow">First row of the sheet.</param>
/// <param name="maxRows">Number of rows to check.</param>
/// <returns><c>True</c>, if column is a date.</returns>
/// <remarks>
/// <para>In excel date columns contain just double values, but they are formatted as dates.</para>
/// <para>
/// Therefore the format of the range is checked and also the values are checked. If both checks succeed the column
/// will be identified as date column.
/// </para>
/// </remarks>
public static bool IsDateColumn(WorkBook workbook, int col, int firstRow, int maxRows)
{
RangeStyle rs = workbook.getRangeStyle(firstRow, col, firstRow + maxRows, col);
string customFormat = rs.CustomFormat;
short cellType;
int row = 0;
do
{
cellType = Math.Abs(workbook.getType(firstRow + row++, col));
} while ((cellType == WorkBook.TypeEmpty || cellType == WorkBook.TypeError) && row < maxRows);
string text = workbook.getFormattedText(firstRow + row, col);
if (customFormat == "General")
{
return(false);
}
if (!customFormat.Contains("h") && !customFormat.Contains("m") && !customFormat.Contains("s") && !customFormat.Contains("d") && !customFormat.Contains("t") && !customFormat.Contains("y") && !customFormat.Contains("j"))
{
return(false);
}
DateTime dtval;
return(DateTime.TryParse(text, out dtval));
}
public void ReadFile(string filePath, WorkBook workBook)
{
var extension = System.IO.Path.GetExtension(filePath);
if (extension != null)
{
switch (extension)
{
case ".csv":
workBook.CSVSeparator = this.CSVSeparator;
workBook.read(filePath);
RangeStyle rs = workBook.getRangeStyle(0, 0, 0, workBook.LastCol + 1); //column must set to max column number will be used in csv parsing
rs.CustomFormat = "@";
workBook.setRangeStyle(rs, 0, 0, 0xfffff, workBook.LastCol + 1); //row1=0 row2=0xfffff to indicate the whole column
var streamReader = new StreamReader(filePath, GetFileEncoding(), FileEncodingHasByteOrderMark, 512);
workBook.readCSV(streamReader.BaseStream);
streamReader.Dispose();
break;
case ".xls":
workBook.read(filePath);
break;
case ".xlsx":
workBook.readXLSX(filePath);
break;
}
}
}
public static bool toComma(string range)
{
m_RangeStyle = m_WorkBook.getRangeStyle();
m_RangeStyle.CustomFormat = "#,###";
m_WorkBook.setSelection(range);
m_WorkBook.setRangeStyle(m_RangeStyle);
return(true);
}
public static bool toPrice4d(string range)
{
m_RangeStyle = m_WorkBook.getRangeStyle();
m_RangeStyle.CustomFormat = "$* #,##0.0000_);[Red]($* #,##0.0000)";
m_WorkBook.setSelection(range);
m_WorkBook.setRangeStyle(m_RangeStyle);
return(true);
}
public static bool FooterFormat(string range)
{
m_WorkBook.setSelection(range);
m_RangeStyle = m_WorkBook.getRangeStyle();
m_RangeStyle.TopBorder = RangeStyle.BorderMedium;
m_RangeStyle.BottomBorder = RangeStyle.BorderMedium;
m_RangeStyle.HorizontalAlignment = RangeStyle.HorizontalAlignmentCenter;
m_RangeStyle.VerticalAlignment = RangeStyle.VerticalAlignmentBottom;
m_RangeStyle.FontBold = true;
m_RangeStyle.FontSize = 200;
m_WorkBook.setRangeStyle(m_RangeStyle);
return(true);
}
/// <summary>
/// Applies the specified range style to the specified range.
/// </summary>
/// <param name="range">The range to apply to the style to.</param>
/// <param name="rangeStyle">The style to apply.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
/// <exception cref="ArgumentNullException"><paramref name="rangeStyle"/> is null.</exception>
public static void SetRangeStyle(
this Range range,
RangeStyle rangeStyle)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (rangeStyle == null)
{
throw new ArgumentNullException(nameof(rangeStyle));
}
var styleContainer = StyleContainer.BuildNew(range.Worksheet.Workbook);
range.SetBackgroundColor(rangeStyle.BackgroundColor, styleContainer);
range.SetFontColor(rangeStyle.FontColor, styleContainer);
range.SetFontName(rangeStyle.FontName, styleContainer);
range.SetFontSize(rangeStyle.FontSize, styleContainer);
range.SetFontIsItalic(rangeStyle.FontIsItalic, styleContainer);
range.SetFontIsBold(rangeStyle.FontIsBold, styleContainer);
range.SetFontUnderline(rangeStyle.FontUnderline, styleContainer);
range.SetFontRotationAngle(rangeStyle.FontRotationAngle, styleContainer);
range.SetTextIsWrapped(rangeStyle.TextIsWrapped, styleContainer);
range.SetFormat(rangeStyle.Format, styleContainer);
range.SetCustomFormat(rangeStyle.CustomFormatString, styleContainer);
range.SetIndentLevel(rangeStyle.IndentLevel, styleContainer);
range.SetVerticalAlignment(rangeStyle.VerticalAlignment, styleContainer);
range.SetHorizontalAlignment(rangeStyle.HorizontalAlignment, styleContainer);
styleContainer.ApplyToRange(range);
range.SetPerRowHeightInPixels(rangeStyle.RowHeightInPixels);
range.SetPerColumnWidthInPixels(rangeStyle.ColumnWidthInPixels);
range.SetAutofitRows(rangeStyle.AutofitRows);
range.SetMergeCells(rangeStyle.MergeCells);
range.SetInsideBorder(rangeStyle.InsideBorder);
range.SetOutsideBorder(rangeStyle.OutsideBorder);
range.SetDataValidation(rangeStyle.DataValidation);
}
public static string createXSL(DataSet ds, string title)
{
m_WorkBook = new WorkBook();
m_WorkBook.Sheet = 0;
try
{
string filepath = HttpContext.Current.Server.MapPath("~/Report/") + title + "_report_" + DateTime.Now.ToString("dd_MM_yyyy_hh_mm_ss") + ".xls";
//HEADER
int i = 0;
m_WorkBook.setText(0, 0, title);
m_WorkBook.setSelection("A1:U1");
m_RangeStyle = m_WorkBook.getRangeStyle();
m_RangeStyle.MergeCells = true;
m_RangeStyle.HorizontalAlignment = RangeStyle.HorizontalAlignmentCenter;
m_RangeStyle.FontBold = true;
m_RangeStyle.FontSize = 300;
m_WorkBook.setRangeStyle(m_RangeStyle);
m_WorkBook.setText(1, 0, Convert.ToDateTime(DateTime.Now).ToString("dddd MMMM d,yyyy"));
m_RangeStyle = m_WorkBook.getRangeStyle();
m_WorkBook.setSelection("A2:U2");
m_RangeStyle.MergeCells = true;
m_RangeStyle.HorizontalAlignment = RangeStyle.HorizontalAlignmentCenter;
m_RangeStyle.FontBold = true;
m_RangeStyle.FontSize = 200;
m_WorkBook.setRangeStyle(m_RangeStyle);
int row = 3;
{
for (int j = 1; i < ds.Tables[0].Columns.Count; i++)
{
m_WorkBook.setText(2, i, ds.Tables[0].Columns[i].ColumnName);
m_WorkBook.setColWidth(i, 100 * 50);
//i++;
}
m_WorkBook.setSelection("A3:" + Common.Utility.Number2String(i, true) + "3");
m_RangeStyle = m_WorkBook.getRangeStyle();
m_RangeStyle.TopBorder = RangeStyle.BorderMedium;
m_RangeStyle.BottomBorder = RangeStyle.BorderMedium;
m_RangeStyle.HorizontalAlignment = RangeStyle.HorizontalAlignmentCenter;
m_RangeStyle.VerticalAlignment = RangeStyle.VerticalAlignmentBottom;
m_RangeStyle.FontBold = true;
m_RangeStyle.FontSize = 200;
m_RangeStyle.Pattern = RangeStyle.PatternSolid;
m_RangeStyle.PatternFG = 0xeeeeee;
m_RangeStyle.Locked = true;
m_WorkBook.setRangeStyle(m_RangeStyle);
m_RangeStyle = m_WorkBook.getRangeStyle();
i = 0;
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
{
//excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
m_WorkBook.setText(row, k, ds.Tables[0].Rows[j].ItemArray[k].ToString());
}
row++;
}
}
row = row + 3;
m_WorkBook.setText(row, 0, "Summary");
m_RangeStyle = m_WorkBook.getRangeStyle();
//m_WorkBook.setSelection("j" + (row + 1) + ":N" + (row + 1));
m_WorkBook.setSelection("A" + (row + 1) + ":" + Common.Utility.Number2String(ds.Tables[1].Columns.Count, true) + (row + 1));
m_RangeStyle.MergeCells = true;
m_RangeStyle.HorizontalAlignment = RangeStyle.HorizontalAlignmentCenter;
HeaderFormat("A" + (row + 1) + ":" + Common.Utility.Number2String(ds.Tables[1].Columns.Count, true) + (row + 1));
row = row + 1;
int summeryCunter = 0;
for (int j = 1; i < ds.Tables[1].Columns.Count; i++)
{
m_WorkBook.setText(row, summeryCunter, ds.Tables[1].Columns[i].ColumnName);
summeryCunter++;
}
FooterFormat("A" + (row + 1) + ":" + Common.Utility.Number2String(ds.Tables[1].Columns.Count, true) + (row + 1));
row = row + 1;
for (int j = 0; j < ds.Tables[1].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[1].Columns.Count; k++)
{
m_WorkBook.setText(row, k, ds.Tables[1].Rows[j].ItemArray[k].ToString());
}
row++;
}
m_WorkBook.setSelection("A4");
m_WorkBook.write(filepath);
return(filepath);
}
catch (Exception ex)
{
throw ex;;
}
}
public void GetRangeStyle(int Row1, int Col1, int Row2, int Col2)
{
Mr_RangeStyle = Mw_WorkBook.getRangeStyle(Row1, Col1, Row2, Col2);
}
public void GetRangeStyle()
{
Mr_RangeStyle = Mw_WorkBook.getRangeStyle();
}
protected void DownloadTemplate_Click(object sender, DirectEventArgs e)
{
try
{
var serverPath = Server.MapPath(relativePath + "/importHumanRecord.xlsx");
var workbook = new WorkBook();
var dataTable = new DataTable();
dataTable.Rows.Add();
dataTable.Rows.Add();
// create header by property name and description
foreach (var prop in typeof(hr_Record).GetProperties().Select((value, index) => new { value, index }))
{
dataTable.Columns.Add();
// get prop description
var attribute = prop.value.GetCustomAttribute(typeof(DescriptionAttribute));
if (attribute != null)
{
dataTable.Rows[0][prop.index] = ((DescriptionAttribute)attribute).Description;
}
dataTable.Rows[1][prop.index] = prop.value.Name;
// set column datatable name
dataTable.Columns[prop.index].ColumnName = prop.value.Name;
}
workbook.ImportDataTable(dataTable, false, 0, 0, dataTable.Rows.Count + 1, dataTable.Columns.Count + 1);
// set header style
RangeStyle range = workbook.getRangeStyle();
range.VerticalAlignment = RangeStyle.VerticalAlignmentCenter;
range.HorizontalAlignment = RangeStyle.HorizontalAlignmentCenter;
range.FontBold = true;
range.FontSize = 11 * 20;
workbook.setRangeStyle(range, 0, 0, 0, workbook.LastCol);
workbook.setSheetName(0, "Thêm mới thông tin hồ sơ");
workbook.insertSheets(1, 1);
workbook.setSheetName(1, "Info");
workbook.SheetHidden = WorkBook.SheetStateHidden;
workbook.Sheet = 0;
foreach (DataColumn col in dataTable.Columns)
{
switch (col.ColumnName)
{
case nameof(hr_Record.DepartmentId):
CreateDropDownExcel("cat_Department", workbook, col);
break;
case nameof(hr_Record.ManagementDepartmentId):
CreateDropDownExcel("cat_Department", workbook, col);
break;
case nameof(hr_Record.Sex):
var validation = workbook.CreateDataValidation();
validation.Type = DataValidation.eUser;
var validateList = "\"Nam,Nữ\"";
validation.Formula1 = validateList;
workbook.setSelection(2, col.Ordinal, 50, col.Ordinal);
workbook.DataValidation = validation;
break;
case nameof(hr_Record.MaritalStatusId):
CreateDropDownExcel("cat_MaritalStatus", workbook, col);
break;
case nameof(hr_Record.ReligionId):
CreateDropDownExcel("cat_Religion", workbook, col);
break;
case nameof(hr_Record.PersonalClassId):
CreateDropDownExcel("cat_PersonalClass", workbook, col);
break;
case nameof(hr_Record.FolkId):
CreateDropDownExcel("cat_Folk", workbook, col);
break;
case nameof(hr_Record.FamilyClassId):
CreateDropDownExcel("cat_FamilyClass", workbook, col);
break;
case nameof(hr_Record.PositionId):
CreateDropDownExcel("cat_Position", workbook, col);
break;
case nameof(hr_Record.JobTitleId):
CreateDropDownExcel("cat_JobTitle", workbook, col);
break;
case nameof(hr_Record.BasicEducationId):
CreateDropDownExcel("cat_BasicEducation", workbook, col);
break;
case nameof(hr_Record.EducationId):
CreateDropDownExcel("cat_Education", workbook, col);
break;
case nameof(hr_Record.PoliticLevelId):
CreateDropDownExcel("cat_PoliticLevel", workbook, col);
break;
case nameof(hr_Record.ManagementLevelId):
CreateDropDownExcel("cat_ManagementLevel", workbook, col);
break;
case nameof(hr_Record.LanguageLevelId):
CreateDropDownExcel("cat_LanguageLevel", workbook, col);
break;
case nameof(hr_Record.ITLevelId):
CreateDropDownExcel("cat_ITLevel", workbook, col);
break;
case nameof(hr_Record.CPVPositionId):
CreateDropDownExcel("cat_CPVPosition", workbook, col);
break;
case nameof(hr_Record.VYUPositionId):
CreateDropDownExcel("cat_VYUPosition", workbook, col);
break;
case nameof(hr_Record.ArmyLevelId):
CreateDropDownExcel("cat_ArmyLevel", workbook, col);
break;
case nameof(hr_Record.HealthStatusId):
CreateDropDownExcel("cat_HealthStatus", workbook, col);
break;
case nameof(hr_Record.FamilyPolicyId):
CreateDropDownExcel("cat_FamilyPolicy", workbook, col);
break;
case nameof(hr_Record.WorkStatusId):
CreateDropDownExcel("cat_WorkStatus", workbook, col);
break;
case nameof(hr_Record.IndustryId):
CreateDropDownExcel("cat_Industry", workbook, col);
break;
default:
break;
}
}
// auto resize columns
for (var i = 0; i < workbook.LastCol; i++)
{
workbook.setColWidthAutoSize(i, true);
}
// hide prop name row
workbook.setRowHidden(1, true);
workbook.writeXLSX(serverPath);
Response.AddHeader("Content-Disposition", "attachment; filename=" + "importHumanRecord.xlsx");
Response.WriteFile(serverPath);
Response.End();
}
catch (Exception ex)
{
Dialog.ShowError(ex.Message);
}
}
