public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the first (default) worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];
// Adding a new Style to the styles
Style style = workbook.CreateStyle();
// Setting the vertical alignment of the text in the "A1" cell
style.VerticalAlignment = TextAlignmentType.Center;
// Setting the horizontal alignment of the text in the "A1" cell
style.HorizontalAlignment = TextAlignmentType.Center;
// Setting the font color of the text in the "A1" cell
style.Font.Color = Color.Green;
// Shrinking the text to fit in the cell
style.ShrinkToFit = true;
// Setting the bottom border color of the cell to red
style.Borders[BorderType.BottomBorder].Color = Color.Red;
// Setting the bottom border type of the cell to medium
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;
// Creating StyleFlag
StyleFlag styleFlag = new StyleFlag();
styleFlag.HorizontalAlignment = true;
styleFlag.VerticalAlignment = true;
styleFlag.ShrinkToFit = true;
styleFlag.Borders = true;
styleFlag.FontColor = true;
// Accessing a row from the Rows collection
Row row = worksheet.Cells.Rows[0];
// Assigning the Style object to the Style property of the row
row.ApplyStyle(style, styleFlag);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls");
// ExEnd:1
}
public static Row SetFontUnderline(this Row row, FontUnderlineType value)
{
var style = row.Style;
style.Font.Underline = value;
row.ApplyStyle(style, new StyleFlag {
FontUnderline = true
});
return(row);
}
public static Row SetFontBold(this Row row, bool value = true)
{
var style = row.Style;
style.Font.IsBold = value;
row.ApplyStyle(style, new StyleFlag {
FontBold = true
});
return(row);
}
public static Row SetFontSize(this Row row, int value = Settings.FontSize)
{
var style = row.Style;
style.Font.Size = value;
row.ApplyStyle(style, new StyleFlag {
FontSize = true
});
return(row);
}
public static Row SetFontName(this Row row, string value = Settings.FontName)
{
var style = row.Style;
style.Font.Name = value;
row.ApplyStyle(style, new StyleFlag {
FontName = true
});
return(row);
}
public static Row SetVerticalAlignment(this Row row, TextAlignmentType value)
{
var style = row.Style;
style.VerticalAlignment = value;
row.ApplyStyle(style, new StyleFlag {
VerticalAlignment = true
});
return(row);
}
public static Row SetFontColor(this Row row, Color value)
{
var style = row.Style;
style.Font.Color = value;
row.ApplyStyle(style, new StyleFlag {
FontColor = true
});
return(row);
}
public static Row SetTextWrapped(this Row row, bool value = true)
{
var style = row.Style;
style.IsTextWrapped = value;
row.ApplyStyle(style, new StyleFlag {
WrapText = true
});
return(row);
}
public static Row SetFormat(this Row row, string value, params object[] args)
{
var style = row.Style;
style.Custom = string.Format(value, args);
row.ApplyStyle(style, new StyleFlag {
NumberFormat = true
});
return(row);
}
public static Row SetFormat(this Row row, int value)
{
var style = row.Style;
style.Number = value;
row.ApplyStyle(style, new StyleFlag {
NumberFormat = true
});
return(row);
}
public static Row SetForegroundColor(this Row row, System.Drawing.Color value)
{
var style = row.Style;
style.ForegroundColor = value;
style.Pattern = BackgroundType.Solid;
row.ApplyStyle(style, new StyleFlag {
CellShading = true
});
return(row);
}
private void UpdateSheet(DataTable dt)
{
Session["AsposeDynamicFormsdataTable"] = dt;
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(Server.MapPath("~/Uploads/AsposeDynamicFormsDataFile.xlsx"), FileMode.Open, FileAccess.Read);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing a worksheet using its sheet name
Worksheet worksheet = workbook.Worksheets["Settings"];
//Closing the file stream to free all resources
fstream.Close();
workbook.Worksheets.RemoveAt("Settings");
worksheet = workbook.Worksheets.Add("Settings");
worksheet.Cells.ImportDataTable(dt, true, "A1");
Aspose.Cells.Style objStyle = workbook.CreateStyle();
objStyle.Font.IsBold = true;
//Bold style flag options
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
//Apply this style to row 1
Row row1 = workbook.Worksheets[0].Cells.Rows[0];
row1.ApplyStyle(objStyle, objStyleFlag);
worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
workbook.Worksheets["Data"].AutoFitColumns();
workbook.Save(Server.MapPath("~/uploads/AsposeDynamicFormsDataFile.xlsx"), SaveFormat.Xlsx);
PopulateGrid();
if (!ProcessButton.Text.Equals("Update"))
{
lbl_Msg.Visible = true;
lbl_Msg.CssClass = "Success";
lbl_Msg.Text = "Field added successfully";
}
ClearFields();
}
protected void btnUpdate_OnClick(object sender, EventArgs e)
{
try
{
if (Session["AsposeDynamicFormsdataTable"] != null)
{
DataTable dt = (DataTable)Session["AsposeDynamicFormsdataTable"];
if (dt != null)
{
if (dt.Rows.Count > 0)
{
string setupSeletectedItemId = (string)Session["SetupSeletectedItemID"];
DataRow[] drs = dt.Select("[Field ID] = '" + setupSeletectedItemId + "'");
if (true)
{
if (drs.Length > 0)
{
drs = dt.Select("[Field ID] = '" + txtFieldId.Text.Trim() + "'"); // checking field type constraint
if (drs != null)
{
if (drs.Length > 0)
{
if (setupSeletectedItemId == txtFieldId.Text.Trim())
{
drs[0]["Field Type"] = ddlFieldType.SelectedValue.Trim();
drs[0]["Field ID"] = txtFieldId.Text.Trim();
drs[0]["Field Label Text"] = txtFieldLableText.Text.Trim();
drs[0]["Field Values"] = txtFieldValues.Text.Trim();
if (chkIsDisplay.Checked)
{
drs[0]["Is Display"] = "TRUE";
}
else
{
drs[0]["Is Display"] = "FALSE";
}
drs[0]["Sort ID"] = txtSortId.Text.Trim();
lbl_Msg.Visible = true;
lbl_Msg.CssClass = "alertupdate";
lbl_Msg.Text = "Field updated successfully";
dt.AcceptChanges();
}
else
{ // on updating field user Enter other Field ID
lbl_Msg.Visible = true;
lbl_Msg.CssClass = "alertdanger";
lbl_Msg.Text = "Field ID already exists ";
btnUpdate.Visible = true;
return;
}
}
else
{
lbl_Msg.Visible = true;
lbl_Msg.CssClass = "alertdanger";
lbl_Msg.Text = "Field ID not exists ";
btnUpdate.Visible = true;
return;
}
}
}
else
{
// new field record case
DataRow dr = dt.NewRow();
dr["Field Type"] = ddlFieldType.SelectedValue.Trim();
dr["Field ID"] = txtFieldId.Text.Trim();
dr["Field Label Text"] = txtFieldLableText.Text.Trim();
dr["Field Values"] = txtFieldValues.Text.Trim();
if (chkIsDisplay.Checked)
{
dr["Is Display"] = "TRUE";
}
else
{
dr["Is Display"] = "FALSE";
}
dr["Sort ID"] = txtSortId.Text.Trim();
dr["Modified On"] = DateTime.Now.ToString("MM-dd-YYYY");
dt.Rows.Add(dr);
lbl_Msg.Visible = true;
lbl_Msg.CssClass = "Success";
lbl_Msg.Text = "Field added successfully";
}
Session["AsposeDynamicFormsdataTable"] = dt;
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(Server.MapPath("~/Uploads/AsposeDynamicFormsDataFile.xlsx"), FileMode.Open, FileAccess.Read);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing a worksheet using its sheet name
Worksheet worksheet = workbook.Worksheets["Settings"];
//Closing the file stream to free all resources
fstream.Close();
workbook.Worksheets.RemoveAt("Settings");
worksheet = workbook.Worksheets.Add("Settings");
worksheet.Cells.ImportDataTable(dt, true, "A1");
Aspose.Cells.Style objStyle = workbook.CreateStyle();
objStyle.Font.IsBold = true;
//Bold style flag options
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
//Apply this style to row 1
Row row1 = workbook.Worksheets[0].Cells.Rows[0];
row1.ApplyStyle(objStyle, objStyleFlag);
worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
workbook.Worksheets["Data"].AutoFitColumns();
workbook.Save(Server.MapPath("~/uploads/AsposeDynamicFormsDataFile.xlsx"), SaveFormat.Xlsx);
PopulateGrid();
ClearFields(); // clear field function which clear all fields while update/adding record
}
}
}
}
}
catch (Exception exc)
{
ShowException(exc);
}
}
// Save data to excel file
protected void ProcessButton_Click(object sender, EventArgs e)
{
try
{
// Check for license and apply if exists
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(Server.MapPath("~/uploads/AsposeDynamicFormsDataFile.xlsx"),
FileMode.Open, FileAccess.Read);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing a worksheet using its sheet name
//Worksheet worksheet = workbook.Worksheets["Data"];
//Worksheet worksheet1 = workbook.Worksheets["Settings"];
Worksheet worksheet = workbook.Worksheets["Data"];
Worksheet worksheet1 = workbook.Worksheets["Settings"];
//Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable
DataTable dataTable = null;
if (worksheet.Cells.Rows.Count <= 0)
{
dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, 1, 1, true);
}
else
{
dataTable = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.Rows.Count,
worksheet.Cells.Columns.Count, true);
}
//Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable from setting
DataTable dataTable1 = worksheet1.Cells.ExportDataTableAsString(0, 0, worksheet1.Cells.Rows.Count, 10,
true);
if (dataTable1 != null)
{
if (dataTable != null)
{
if (dataTable.Columns.Count <= 1)
{
dataTable.Columns.RemoveAt(0);
}
foreach (DataRow row in dataTable1.Rows)
{
if (!row[1].ToString().Trim().Equals("Title") && !row[1].ToString().Trim().Equals("Success"))
{
foreach (string strItem in row[2].ToString().Trim().Split(';'))
{
if (!strItem.Trim().Equals(""))
{
if (dataTable.Columns[strItem.Trim()] == null)
{
dataTable.Columns.Add(strItem.Trim());
}
}
}
}
}
}
}
//Closing the file stream to free all resources
fstream.Close();
DataRow dr = dataTable.NewRow();
foreach (Control ctrl in myPlaceHolder.Controls)
{
if (ctrl != null)
{
if (ctrl is TextBox)
{
dr[ctrl.ID] = ((TextBox)ctrl).Text.Trim();
continue;
}
if (ctrl is RadioButton)
{
if (((RadioButton)ctrl).Checked)
{
dr[ctrl.ID] = ((RadioButton)ctrl).Text.Trim();
continue;
}
}
if (ctrl is CheckBox)
{
if (((CheckBox)ctrl).Checked)
{
dr[ctrl.ID] = ((CheckBox)ctrl).Text.Trim();
continue;
}
}
if (ctrl is DropDownList)
{
dr[ctrl.ID] = ((DropDownList)ctrl).SelectedItem.Text.Trim();
continue;
}
}
}
dataTable.Rows.Add(dr);
workbook.Worksheets.RemoveAt("Data");
worksheet = workbook.Worksheets.Add("Data");
worksheet.Cells.ImportDataTable(dataTable, true, "A1");
// Apply Hearder Row/First Row text to Bold
Aspose.Cells.Style objStyle = workbook.CreateStyle();
objStyle.Font.IsBold = true;
//Bold style flag options
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
//Apply this style to row 1
Row row1 = workbook.Worksheets[0].Cells.Rows[0];
row1.ApplyStyle(objStyle, objStyleFlag);
worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
workbook.Worksheets["Data"].AutoFitColumns();
workbook.Save(Server.MapPath("~/uploads/AsposeDynamicFormsDataFile.xlsx"), SaveFormat.Xlsx);
error_msg.Visible = false;
success_msg.Visible = true;
}
catch (Exception exc)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerText = exc.Message;
}
}
public List <CustomApartmentDTO> Apartment_ExportExcel(List <CustomApartmentDTO> listInput)
{
if (listInput == null)
{
return(null);
}
//Create the workbook and the worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Danh sách căn hộ";
//Set a title and format its style
var cell = sheet.Cells["A1"];
cell.PutValue("DANH SÁCH CĂN HỘ");
Style style = cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Size = 20;
style.Font.IsBold = true;
style.ForegroundColor = Color.AliceBlue;
style.Pattern = BackgroundType.Solid;
cell.SetStyle(style);
List <string> listProperty = new List <string> {
"Mã căn hộ", "Tên căn hộ", "Loại căn hộ", "Tòa nhà", "Tầng", "Số người ở", "Giá", "Trạng thái căn hộ", "Trạng thái duyệt"
};
int countProperty = 0;
for (char c = 'A'; c <= 'Z' && countProperty < listProperty.Count; c++)
{
var headerCell = sheet.Cells[c + "3"];
headerCell.PutValue(listProperty[countProperty]);
countProperty++;
}
Row header = sheet.Cells.Rows[2];
StyleFlag headerFlag = new StyleFlag()
{
All = true
};
style = header.Style;
style.HorizontalAlignment = TextAlignmentType.Center;
style.ForegroundColor = Color.LightGreen;
style.BackgroundColor = Color.LightGreen;
style.Font.Size = 15;
style.Font.IsBold = true;
header.ApplyStyle(style, headerFlag);
sheet.Cells.ImportCustomObjects((System.Collections.ICollection)listInput
, new string[] { "APARTMENT_CODE", "APARTMENT_NAME", "APARTMENT_TYPE_NAME", "BUILDING_NAME", "Floor_NAME", "NUMBER_OF_PEOPLE", "APARTMENT_PRICE", "APARTMENT_STATUS", "AUTH_STATUS" }
, false, 3, 0, listInput.Count
, false
, "dd/mm/yyyy"
, false);
//Set the columns to fit the size of their content
sheet.AutoFitColumns();
//Save the Excel workbook
var fileName1 = sheet.Name + ".xlsx";
workbook.Save(fileName1, SaveFormat.Xlsx);
return(listInput);
}
public List <NSX_DTO> NSX_ExportExcel(List <NSX_DTO> listInput)
{
if (listInput == null)
{
return(null);
}
//Create the workbook and the worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Danh sách nhà sản xuất";
//Set a title and format its style
var cell = sheet.Cells["A1"];
cell.PutValue("DANH SÁCH NHÀ SẢN XUẤT");
Style style = cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Size = 20;
style.Font.IsBold = true;
style.ForegroundColor = Color.AliceBlue;
style.Pattern = BackgroundType.Solid;
cell.SetStyle(style);
List <string> listProperty = new List <string> {
"Mã nhà sản xuất", "Tên nhà sản xuất", "Địa chỉ", "Ghi chú"
};
int countProperty = 0;
for (char c = 'A'; c <= 'D'; c++)
{
var headerCell = sheet.Cells[c + "3"];
headerCell.PutValue(listProperty[countProperty]);
countProperty++;
}
Row header = sheet.Cells.Rows[2];
StyleFlag headerFlag = new StyleFlag()
{
All = true
};
style = header.Style;
style.HorizontalAlignment = TextAlignmentType.Center;
style.ForegroundColor = Color.LightGreen;
style.BackgroundColor = Color.LightGreen;
style.Font.Size = 15;
style.Font.IsBold = true;
header.ApplyStyle(style, headerFlag);
sheet.Cells.ImportCustomObjects((System.Collections.ICollection)listInput
, new string[] { "NSX_CODE", "NSX_NAME", "NSX_FROM", "NOTES" }
, false, 3, 0, listInput.Count
, false
, "dd/mm/yyyy"
, true);
//Set the columns to fit the size of their content
sheet.AutoFitColumns();
//Save the Excel workbook
var fileName1 = sheet.Name + ".xlsx";
workbook.Save(fileName1, SaveFormat.Xlsx);
return(listInput);
}
public PagedResultDto <PTX_DTO> PTX_ExportExcel()
{
List <PTX_DTO> listPTX = procedureHelper.GetData <PTX_DTO>("PTX_SEARCH", new PTX_DTO());
int count = listPTX.Count();
if (listPTX == null)
{
return(null);
}
//Create the workbook and the worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Danh sách phiếu thuê";
//Set a title and format its style
var cell = sheet.Cells["A1"];
cell.PutValue("DANH SÁCH PHIẾU THUÊ");
Style style = cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Size = 20;
style.Font.IsBold = true;
style.ForegroundColor = Color.AliceBlue;
style.Pattern = BackgroundType.Solid;
cell.SetStyle(style);
List <string> listProperty = new List <string> {
"Mã phiếu thuê", "Ngày thuê", "Ngày hết hạn", "Ngày trả", "Tổng tiền", "Ghi chú", "Mã xe", "Mã người thuê", "Ngày tạo"
};
int countProperty = 0;
for (char c = 'A'; c <= 'I'; c++)
{
var headerCell = sheet.Cells[c + "3"];
headerCell.PutValue(listProperty[countProperty]);
countProperty++;
}
Row header = sheet.Cells.Rows[2];
StyleFlag headerFlag = new StyleFlag()
{
All = true
};
style = header.Style;
style.HorizontalAlignment = TextAlignmentType.Center;
style.ForegroundColor = Color.LightGreen;
style.BackgroundColor = Color.LightGreen;
style.Font.Size = 15;
style.Font.IsBold = true;
header.ApplyStyle(style, headerFlag);
sheet.Cells.ImportCustomObjects((System.Collections.ICollection)listPTX
, new string[] { "PTX_CODE", "PTX_RENT_DT", "PTX_EXP_DT", "PTX_RETURN_DT", "PTX_PRICE", "PTX_NOTE", "XE_ID", "NTX_ID", "CREATED_DT" }
, false, 3, 0, listPTX.Count
, false
, "dd/mm/yyyy"
, false);
//Set the columns to fit the size of their content
sheet.AutoFitColumns();
//Save the Excel workbook
var fileName1 = sheet.Name + ".xlsx";
workbook.Save(fileName1, SaveFormat.Xlsx);
return(new PagedResultDto <PTX_DTO> {
TotalCount = count, Items = listPTX
});
}
private static void CreateCellsFormatting(Workbook workbook)
{
//Define a style object adding a new style
//to the collection list.
Style stl0 = workbook.Styles[workbook.Styles.Add()];
//Set a custom shading color of the cells.
stl0.ForegroundColor = Color.FromArgb(155, 204, 255);
//Set the solid background fillment.
stl0.Pattern = BackgroundType.Solid;
//Set a font.
stl0.Font.Name = "Trebuchet MS";
//Set the size.
stl0.Font.Size = 18;
//Set the font text color.
stl0.Font.Color = Color.Maroon;
//Set it bold
stl0.Font.IsBold = true;
//Set it italic.
stl0.Font.IsItalic = true;
//Define a style flag struct.
StyleFlag flag = new StyleFlag();
//Apply cell shading.
flag.CellShading = true;
//Apply font.
flag.FontName = true;
//Apply font size.
flag.FontSize = true;
//Apply font color.
flag.FontColor = true;
//Apply bold font.
flag.FontBold = true;
//Apply italic attribute.
flag.FontItalic = true;
//Get the first row in the first worksheet.
Row row = workbook.Worksheets[0].Cells.Rows[0];
//Apply the style to it.
row.ApplyStyle(stl0, flag);
//Obtain the cells of the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;
//Set the height of the first row.
cells.SetRowHeight(0, 30);
//Define a style object adding a new style
//to the collection list.
Style stl1 = workbook.Styles[workbook.Styles.Add()];
//Set the rotation angle of the text.
stl1.RotationAngle = 45;
//Set the custom fill color of the cells.
stl1.ForegroundColor = Color.FromArgb(0, 51, 105);
//Set the solid background pattern for fillment.
stl1.Pattern = BackgroundType.Solid;
//Set the left border line style.
stl1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
//Set the left border line color.
stl1.Borders[BorderType.LeftBorder].Color = Color.White;
//Set the horizontal alignment to center aligned.
stl1.HorizontalAlignment = TextAlignmentType.Center;
//Set the vertical alignment to center aligned.
stl1.VerticalAlignment = TextAlignmentType.Center;
//Set the font.
stl1.Font.Name = "Times New Roman";
//Set the font size.
stl1.Font.Size = 10;
//Set the font color.
stl1.Font.Color = Color.White;
//Set the bold attribute.
stl1.Font.IsBold = true;
//Set the style flag struct.
flag = new StyleFlag();
//Apply the left border.
flag.LeftBorder = true;
//Apply text rotation orientation.
flag.Rotation = true;
//Apply fill color of cells.
flag.CellShading = true;
//Apply horizontal alignment.
flag.HorizontalAlignment = true;
//Apply vertical alignment.
flag.VerticalAlignment = true;
//Apply the font.
flag.FontName = true;
//Apply the font size.
flag.FontSize = true;
//Apply the font color.
flag.FontColor = true;
//Apply the bold attribute.
flag.FontBold = true;
//Get the second row of the first worksheet.
row = workbook.Worksheets[0].Cells.Rows[1];
//Apply the style to it.
row.ApplyStyle(stl1, flag);
//Set the height of the second row.
cells.SetRowHeight(1, 48);
//Define a style object adding a new style
//to the collection list.
Style stl2 = workbook.Styles[workbook.Styles.Add()];
//Set the custom cell shading color.
stl2.ForegroundColor = Color.FromArgb(155, 204, 255);
//Set the solid background pattern for fillment color.
stl2.Pattern = BackgroundType.Solid;
//Set the font.
stl2.Font.Name = "Trebuchet MS";
//Set the font color.
stl2.Font.Color = Color.Maroon;
//Set the font size.
stl2.Font.Size = 10;
//Set the style flag struct.
flag = new StyleFlag();
//Apply cell shading.
flag.CellShading = true;
//Apply the font.
flag.FontName = true;
//Apply the font color.
flag.FontColor = true;
//Apply the font size.
flag.FontSize = true;
//Get the first column in the first worksheet.
Column col = workbook.Worksheets[0].Cells.Columns[0];
//Apply the style to it.
col.ApplyStyle(stl2, flag);
//Define a style object adding a new style
//to the collection list.
Style stl3 = workbook.Styles[workbook.Styles.Add()];
//Set the custom cell filling color.
stl3.ForegroundColor = Color.FromArgb(124, 199, 72);
//Set the solid background pattern for fillment color.
stl3.Pattern = BackgroundType.Solid;
//Apply the style to A2 cell.
cells["A2"].SetStyle(stl3);
//Define a style object adding a new style
//to the collection list.
Style stl4 = workbook.Styles[workbook.Styles.Add()];
//Set the custom font text color.
stl4.Font.Color = Color.FromArgb(0, 51, 105);
//Set the bottom border line style.
stl4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//Set the bottom border line color to custom color.
stl4.Borders[BorderType.BottomBorder].Color = Color.FromArgb(124, 199, 72);
//Set the background fill color of the cells.
stl4.ForegroundColor = Color.White;
//Set the solid fillcolor pattern.
stl4.Pattern = BackgroundType.Solid;
//Set custom number format.
stl4.Custom = "$#,##0.0";
//Set a style flag struct.
flag = new StyleFlag();
//Apply font color.
flag.FontColor = true;
//Apply cell shading color.
flag.CellShading = true;
//Apply custom number format.
flag.NumberFormat = true;
//Apply bottom border.
flag.BottomBorder = true;
//Define a style object adding a new style
//to the collection list.
Style stl5 = workbook.Styles[workbook.Styles.Add()];
//Set the bottom borde line style.
stl5.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//Set the bottom border line color.
stl5.Borders[BorderType.BottomBorder].Color = Color.FromArgb(124, 199, 72);
//Set the custom shading color of the cells.
stl5.ForegroundColor = Color.FromArgb(250, 250, 200);
//Set the solid background pattern for fillment color.
stl5.Pattern = BackgroundType.Solid;
//Set custom number format.
stl5.Custom = "$#,##0.0";
//Set font text color.
stl5.Font.Color = Color.Maroon;
//Create a named range of cells (B3:M25)in the first worksheet.
Range range = workbook.Worksheets[0].Cells.CreateRange("B3", "M25");
//Name the range.
range.Name = "MyRange";
//Apply the style to cells in the named range.
range.ApplyStyle(stl4, flag);
//Apply different style to alternative rows
//in the range.
for (int i = 0; i <= 22; i++)
{
for (int j = 0; j < 12; j++)
{
if (i % 2 == 0)
{
range[i, j].SetStyle(stl5);
}
}
}
//Define a style object adding a new style
//to the collection list.
Style stl6 = workbook.Styles[workbook.Styles.Add()];
//Set the custom fill color of the cells.
stl6.ForegroundColor = Color.FromArgb(0, 51, 105);
//Set the background pattern for fillment color.
stl6.Pattern = BackgroundType.Solid;
//Set the font.
stl6.Font.Name = "Arial";
//Set the font size.
stl6.Font.Size = 10;
//Set the font color
stl6.Font.Color = Color.White;
//Set the text bold.
stl6.Font.IsBold = true;
//Set the custom number format.
stl6.Custom = "$#,##0.0";
//Set the style flag struct.
flag = new StyleFlag();
//Apply cell shading.
flag.CellShading = true;
//Apply the arial font.
flag.FontName = true;
//Apply the font size.
flag.FontSize = true;
//Apply the font color.
flag.FontColor = true;
//Apply the bold attribute.
flag.FontBold = true;
//Apply the number format.
flag.NumberFormat = true;
//Get the 26th row in the first worksheet which produces totals.
row = workbook.Worksheets[0].Cells.Rows[25];
//Apply the style to it.
row.ApplyStyle(stl6, flag);
//Now apply this style to those cells (N3:N25) which
//has productwise sales totals.
for (int i = 2; i < 25; i++)
{
cells[i, 13].SetStyle(stl6);
}
//Set N column's width to fit the contents.
workbook.Worksheets[0].Cells.SetColumnWidth(13, 9.33);
//ExEnd:1
}
public static MemoryStream ToExcel <T>(this List <T> list)
{
//configure embeded license
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Cells.lic");
try
{
//Create excel workbook
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
List <string> objHeaders = new List <string>();
PropertyInfo[] headerInfo = typeof(T).GetProperties();
foreach (var property in headerInfo)
{
var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false)
.Cast <DisplayNameAttribute>().FirstOrDefault();
//Avoid columns from Base Business Entity
if (property.Name == "ChangedFields")
{
break;
}
objHeaders.Add(property.Name);
}
sheet.Cells.ImportCustomObjects(list, objHeaders.ToArray(), true, 1, 0, list.Count, true, string.Empty, false);
#region Header style
Style style = book.CreateStyle();
style.Font.IsBold = true;
// Define a style flag struct.
StyleFlag flag = new StyleFlag();
flag.FontBold = true;
// Get the first row in the first worksheet.
Row row = book.Worksheets[0].Cells.Rows[1];
// Apply the style to it.
row.ApplyStyle(style, flag);
#endregion
// Auto-fit all the columns
book.Worksheets[0].AutoFitColumns();
MemoryStream outputStream = new MemoryStream();
//Save workbook as stream instead of saving it to the Disk
book.Save(outputStream, SaveFormat.Xlsx);
//Call dispose methods
sheet.Dispose();
book.Dispose();
outputStream.Position = 0;
return(outputStream);
}
catch (Exception)
{
// TODO : Handle out of memory exception
throw;
}
}
public dynamic TTYCSC_ExportExcel(int pageNumber, int pageSize, string TTYCSCName, string TTYCSCCode, string TTYCSCDesc, string APPROVEStatus)
{
try
{
var list = procedureHelper.GetData <dynamic>("TTYCSC_SearchFilter", new
{
PageNumber = pageNumber,
PageSize = pageSize,
TTYCSC_NAME = TTYCSCName,
TTYCSC_CODE = TTYCSCCode,
TTYCSC_DESC = TTYCSCDesc,
APPROVE_STATUS = APPROVEStatus
});
if (list == null)
{
return(null);
}
list.ForEach(l =>
{
l.AUTH_STATUS = l.AUTH_STATUS == "A" ? "Đã duyệt" : (l.AUTH_STATUS == "U" ? "Từ chối" : "Chưa duyệt");
});
Workbook book = new Workbook(); // Create A Workbook
Worksheet sheet = book.Worksheets[0]; // Create a worksheet
sheet.Name = "Danh sách căn hộ";
var cell = sheet.Cells["A1"];
cell.PutValue("DANH SÁCH TRẠNG THÁI YÊU CẦU SỬA CHỮA");
// Create Styles
Style style = cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Size = 20;
style.Font.IsBold = true;
style.ForegroundColor = Color.AliceBlue;
style.Pattern = BackgroundType.Solid;
cell.SetStyle(style);
List <string> listProperty = new List <string> {
"Mã trạng thái", "Tên trạng thái", "Chi tiết trạng thái", "Trạng thái duyệt"
};
int countProperty = 0;
for (char c = 'A'; c <= 'Z' && countProperty < listProperty.Count; c++)
{
var headerCell = sheet.Cells[c + "3"];
headerCell.PutValue(listProperty[countProperty]);
countProperty++;
}
Row header = sheet.Cells.Rows[2];
StyleFlag headerFlag = new StyleFlag()
{
All = true
};
style = header.Style;
style.HorizontalAlignment = TextAlignmentType.Center;
style.ForegroundColor = Color.LightGreen;
style.BackgroundColor = Color.LightGreen;
style.Font.Size = 15;
style.Font.IsBold = true;
header.ApplyStyle(style, headerFlag);
sheet.Cells.ImportCustomObjects((System.Collections.ICollection)list
, new string[] { "TTYCSC_CODE", "TTYCSC_NAME", "TTYCSC_DECS", "AUTH_STATUS" }
, false, 3, 0, list.Count
, false
, "dd/mm/yyyy"
, false);
//Set the columns to fit the size of their content
sheet.AutoFitColumns();
//Save the Excel workbook
var fileName1 = sheet.Name + ".xlsx";
book.Save(fileName1, SaveFormat.Xlsx);
return(list);
}
catch
{
return(null);
}
}
public List <BD_DTO> ExportData(List <BD_DTO> listInput)
{
if (listInput == null)
{
return(null);
}
//Create the workbook and the worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Danh Sách Phiếu Bảo Dưỡng";
//Set a title and format its style
var cell = sheet.Cells["A1"];
cell.PutValue("BẢO DƯỠNG XE");
Style style = cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Size = 20;
style.Font.IsBold = true;
style.ForegroundColor = Color.AliceBlue;
style.Pattern = BackgroundType.Solid;
cell.SetStyle(style);
List <string> listProperty = new List <string> {
"Mã Bảo Dưỡng", "Garage", "Địa chỉ", "Tổng tiền", "Mã xe", "Ngày bắt đầu", "Ngày kết thúc", "Ngày tạo"
};
int countProperty = 0;
for (char c = 'A'; c <= 'H'; c++)
{
var headerCell = sheet.Cells[c + "2"];
headerCell.PutValue(listProperty[countProperty]);
countProperty++;
}
Row header = sheet.Cells.Rows[1];
StyleFlag headerFlag = new StyleFlag()
{
All = true
};
style = header.Style;
style.HorizontalAlignment = TextAlignmentType.Center;
style.ForegroundColor = Color.LightGreen;
style.BackgroundColor = Color.LightGreen;
style.Font.Size = 15;
style.Font.IsBold = true;
header.ApplyStyle(style, headerFlag);
sheet.Cells.ImportCustomObjects((System.Collections.ICollection)listInput
, new string[] { "BD_CODE", "BD_GARAGE", "BD_ADDRESS", "BD_PRICE", "XE_ID", "BD_FROM_DT", "BD_TO_DT", "CREATED_DT" }
, false, 3, 0, listInput.Count
, false
, "dd/mm/yyyy"
, false);
//Set the columns to fit the size of their content
sheet.AutoFitColumns();
//Save the Excel workbook
var fileName1 = sheet.Name + ".xlsx";
workbook.Save(fileName1, SaveFormat.Xlsx);
return(listInput);
}
public List <XE_DTO> XE_ExportExcel(List <XE_DTO> listInput)
{
if (listInput == null)
{
return(null);
}
//Create the workbook and the worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Danh sách xe";
//Set a title and format its style
var cell = sheet.Cells["A1"];
cell.PutValue("DANH SÁCH XE");
Style style = cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Size = 20;
style.Font.IsBold = true;
style.ForegroundColor = Color.AliceBlue;
style.Pattern = BackgroundType.Solid;
cell.SetStyle(style);
List <string> listProperty = new List <string> {
"Mã xe", "Tên xe", "Màu xe", "Số chỗ ngồi", "Tên mẫu", "Chứng chỉ", "Giá", "Tiêu thụ", "Ghi chú"
, "Tốc độ tối đa", "Nhà sản xuất", "Năm sản xuất", "Trạng thái", "Thời gian tạo", "Tổng quãng đường đi"
};
int countProperty = 0;
for (char c = 'A'; c <= 'O'; c++)
{
var headerCell = sheet.Cells[c + "3"];
headerCell.PutValue(listProperty[countProperty]);
countProperty++;
}
Row header = sheet.Cells.Rows[2];
StyleFlag headerFlag = new StyleFlag()
{
All = true
};
style = header.Style;
style.HorizontalAlignment = TextAlignmentType.Center;
style.ForegroundColor = Color.LightGreen;
style.BackgroundColor = Color.LightGreen;
style.Font.Size = 15;
style.Font.IsBold = true;
header.ApplyStyle(style, headerFlag);
sheet.Cells.ImportCustomObjects((System.Collections.ICollection)listInput
, new string[] { "XE_CODE", "XE_NAME", "XE_COLOR", "XE_SEATS", "XE_MODEL", "XE_LICENSE_PLATE", "XE_PRICE", "XE_CONSUMPTION", "XE_NOTES"
, "XE_MAX_SPEED", "XE_MANUFACTURER", "XE_MANUFACTURER_YEAR", "XE_STATUS", "CREATED_DT", "XE_TOTAL_KM" }
, false, 3, 0, listInput.Count
, false
, "dd/mm/yyyy"
, false);
//Set the columns to fit the size of their content
sheet.AutoFitColumns();
//Save the Excel workbook
var fileName1 = sheet.Name + ".xlsx";
workbook.Save(fileName1, SaveFormat.Xlsx);
return(listInput);
}
