public IList <Style> CheckTest(Workbook workbook)
{
IList <Style> styles = new List <Style>();
Style styleHeader = workbook.CreateStyle();
styles.Add(styleHeader);
styleHeader.Font.Name = "微软雅黑";
styleHeader.Font.Size = 10;
styleHeader.Font.IsBold = true;
styleHeader.Pattern = BackgroundType.Solid;
styleHeader.ForegroundColor = Color.FromArgb(0, 204, 255);
styleHeader.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleHeader.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
Style styleData = workbook.CreateStyle();
styles.Add(styleData);
styleData.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleData.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleData.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleData.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
Style styleAngka = workbook.CreateStyle();
styles.Add(styleAngka);
styleHeader.Font.Name = "微软雅黑";
styleAngka.HorizontalAlignment = TextAlignmentType.Left;
styleAngka.VerticalAlignment = TextAlignmentType.Center;
styleAngka.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styleAngka.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styleAngka.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styleAngka.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
Style stylePemisah = workbook.CreateStyle();
styles.Add(stylePemisah);
stylePemisah.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stylePemisah.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
stylePemisah.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
stylePemisah.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stylePemisah.Pattern = BackgroundType.Solid;
stylePemisah.ForegroundColor = Color.Yellow;
stylePemisah.Font.IsBold = true;
return(styles);
}
public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
//Create workbook
Workbook wb = new Workbook();
//Create an unused named style
wb.CreateStyle().Name = "UnusedStyle_XXXXXXXXXXXXXX";
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Put some value in cell C7
ws.Cells["C7"].PutValue("This is sample text.");
//Specify html save options, we want to exclude unused styles
HtmlSaveOptions opts = new HtmlSaveOptions();
//Comment this line to include unused styles
opts.ExcludeUnusedStyles = true;
//Save the workbook in html format
wb.Save(outputDir + "outputExcludeUnusedStylesInExcelToHTML.html", opts);
Console.WriteLine("ExcludeUnusedStylesInExcelToHTML executed successfully.");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");
// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
var pivot = workbook.Worksheets[0].PivotTables[0];
pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark1;
Style style = workbook.CreateStyle();
style.Font.Name = "Arial Black";
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
pivot.FormatAll(style);
// Saving the Excel file
workbook.Save(dataDir + "output.xls");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");
// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
var pivot = workbook.Worksheets[0].PivotTables[0];
pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark1;
Style style = workbook.CreateStyle();
style.Font.Name = "Arial Black";
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
pivot.FormatAll(style);
// Saving the Excel file
workbook.Save(dataDir + "output.xls");
// ExEnd:1
}
public static void Main()
{
// Load sample Excel file containing cells with formatting.
Workbook wb = new Workbook(sourceDir + "sampleChangeCellsAlignmentAndKeepExistingFormatting.xlsx");
// Access first worksheet.
Worksheet ws = wb.Worksheets[0];
// Create cells range.
Range rng = ws.Cells.CreateRange("B2:D7");
// Create style object.
Style st = wb.CreateStyle();
// Set the horizontal and vertical alignment to center.
st.HorizontalAlignment = TextAlignmentType.Center;
st.VerticalAlignment = TextAlignmentType.Center;
// Create style flag object.
StyleFlag flag = new StyleFlag();
// Set style flag alignments true. It is most crucial statement.
// Because if it will be false, no changes will take place.
flag.Alignments = true;
// Apply style to range of cells.
rng.ApplyStyle(st, flag);
// Save the workbook in XLSX format.
wb.Save(outputDir + "outputChangeCellsAlignmentAndKeepExistingFormatting.xlsx", SaveFormat.Xlsx);
Console.WriteLine("ChangeCellsAlignmentAndKeepExistingFormatting executed successfully.");
}
static void Main(string[] args)
{
Workbook myWorkbook = new Workbook(fileName);
Worksheet mySheet = myWorkbook.Worksheets[myWorkbook.Worksheets.ActiveSheetIndex];
Style style = myWorkbook.CreateStyle();
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;
//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
Column column = mySheet.Cells.Columns[0];
//Assigning the Style object to the Style property of the row
column.ApplyStyle(style, styleFlag);
myWorkbook.Save(fileName);
}
static void Main(string[] args)
{
Workbook myWorkbook = new Workbook(fileName);
Worksheet mySheet = myWorkbook.Worksheets[myWorkbook.Worksheets.ActiveSheetIndex];
Style style = myWorkbook.CreateStyle();
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;
//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
Column column = mySheet.Cells.Columns[0];
//Assigning the Style object to the Style property of the row
column.ApplyStyle(style, styleFlag);
myWorkbook.Save(fileName);
}
public static void Run()
{
// Opening the Excel file through the file stream
Workbook workbook = new Workbook();
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Creating a named range
Range range = worksheet.Cells.CreateRange("B4", "G14");
// Setting the name of the named range
range.Name = "TestRange";
Style st = workbook.CreateStyle();
st.Pattern = BackgroundType.Solid;
st.ForegroundColor = System.Drawing.Color.Yellow;
range.SetStyle(st);
// Saving the modified Excel file
workbook.Save(outputDir + "outputCreateNamedRangeofCells.xlsx");
Console.WriteLine("CreateNamedRangeofCells executed successfully.");
}
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);
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Create a style object .
Style style = workbook.CreateStyle();
// Input a value to A1 cell.
workbook.Worksheets[0].Cells["A1"].PutValue("Test");
// Apply the style to the cell.
workbook.Worksheets[0].Cells["A1"].SetStyle(style);
// Save the Excel 2007 file.
workbook.Save(dataDir + "book1.out.xlsx");
// ExEnd:1
}
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 void Run()
{
// ExStart:ReusingStyleObjects
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook object
Workbook workbook = new Workbook();
// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access cells
Cell cell1 = worksheet.Cells["A1"];
Cell cell2 = worksheet.Cells["B1"];
// Set the styles of both cells to Times New Roman
Style styleObject = workbook.CreateStyle();
styleObject.Font.Color = System.Drawing.Color.Red;
styleObject.Font.Name = "Times New Roman";
cell1.SetStyle(styleObject);
cell2.SetStyle(styleObject);
// Put the values inside the cell
cell1.PutValue("Hello World!");
cell2.PutValue("Hello World!!");
// Save to Pdf without setting PdfSaveOptions.IsFontSubstitutionCharGranularity
workbook.Save(dataDir + "SampleOutput_out.xlsx");
// ExEnd:ReusingStyleObjects
}
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);
}
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Create a style object .
Style style = workbook.CreateStyle();
// Input a value to A1 cell.
workbook.Worksheets[0].Cells["A1"].PutValue("Test");
// Apply the style to the cell.
workbook.Worksheets[0].Cells["A1"].SetStyle(style);
// Save the Excel 2007 file.
workbook.Save(dataDir + "book1.out.xlsx");
// ExEnd:1
}
public static void Run()
{
// ExStart:ReusingStyleObjects
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook object
Workbook workbook = new Workbook();
// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access cells
Cell cell1 = worksheet.Cells["A1"];
Cell cell2 = worksheet.Cells["B1"];
// Set the styles of both cells to Times New Roman
Style styleObject = workbook.CreateStyle();
styleObject.Font.Color = System.Drawing.Color.Red;
styleObject.Font.Name = "Times New Roman";
cell1.SetStyle(styleObject);
cell2.SetStyle(styleObject);
// Put the values inside the cell
cell1.PutValue("Hello World!");
cell2.PutValue("Hello World!!");
// Save to Pdf without setting PdfSaveOptions.IsFontSubstitutionCharGranularity
workbook.Save(dataDir + "SampleOutput_out.xlsx");
// ExEnd:ReusingStyleObjects
}
public FileDto NhaCungUng_ExportToExcel(NHACUNGUNG_DTO input)
{
var workbook = new Workbook();
var sheet0 = workbook.Worksheets[0];
var data = procedureHelper.GetData <NHACUNGUNG_EXPORT_DTO>("NhaCungUng_Filter", input);
sheet0.Cells.ImportCustomObjects(
data, 0, 0,
new ImportTableOptions()
{
DateFormat = "dd/MM/yyyy",
IsFieldNameShown = true,
});
sheet0.Cells.ImportArray(
new string[]
{
"Mã NCU",
"Tên nhà cung ứng",
"Địa chỉ",
"SĐT",
"Mã số thuế",
"Fax",
"Tên người liên hệ",
"Email người liên hệ",
"SĐT người liên hệ",
},
0, 0, false);
var range = sheet0.Cells.MaxDisplayRange;
var cellStyle = workbook.CreateStyle();
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
for (int r = range.FirstRow; r < range.RowCount; r++)
{
for (int c = range.FirstColumn; c < range.ColumnCount; c++)
{
Cell cell = sheet0.Cells[r, c];
cell.SetStyle(cellStyle, new StyleFlag()
{
TopBorder = true, BottomBorder = true, LeftBorder = true, RightBorder = true
});
}
}
sheet0.AutoFitColumns();
var fileName = $"NhaCungUng_{DateTime.Now.ToString("MMddyyyyHHmmss")}.xlsx";
var file = new FileDto(fileName, MimeTypeNames.ApplicationVndOpenxmlformatsOfficedocumentSpreadsheetmlSheet);
var filePath = Path.Combine(_appFolders.TempFileDownloadFolder, file.FileToken);
workbook.Save(Path.Combine(filePath), SaveFormat.Xlsx);
return(file);
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create workbook object from source file containing pivot table
Workbook workbook = new Workbook(sourceDir + "sampleFormatPivotTableCells.xlsx");
// Access the worksheet by its name
Worksheet worksheet = workbook.Worksheets[0];
// Access the pivot table
PivotTable pivotTable = worksheet.PivotTables[0];
// Create a style object with background color light blue
Style style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.LightBlue;
// Format entire pivot table with light blue color
pivotTable.FormatAll(style);
// Create another style object with yellow color
style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.Yellow;
// Format the cells of the first row of the pivot table with yellow color
//Format the pivot table cells from H6 to M6
string[] cells_names = new string[] { "H6", "I6", "J6", "K6", "L6", "M6" };
for (int i = 0; i < cells_names.Length; i++)
{
Cell cell = worksheet.Cells[cells_names[i]];
pivotTable.Format(cell.Row, cell.Column, style);
}
// Save the workbook object
workbook.Save(outputDir + "outputFormatPivotTableCells.xlsx");
Console.WriteLine("FormatPivotTableCells executed successfully.\r\n");
}
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);
}
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Get the first worksheet in the book.
Worksheet WS = workbook.Worksheets[0];
// Create a range of cells.
Aspose.Cells.Range range = WS.Cells.CreateRange(1, 1, 1, 18);
// Name the range.
range.Name = "MyRange";
// Declare a style object.
Style stl;
// Create/add the style object.
stl = workbook.CreateStyle();
// Specify some Font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
// Set the font text color
stl.Font.Color = Color.Red;
// To Set the fill color of the range, you may use ForegroundColor with
// Solid Pattern setting.
stl.ForegroundColor = Color.Yellow;
stl.Pattern = BackgroundType.Solid;
// Create a StyleFlag object.
StyleFlag flg = new StyleFlag();
// Make the corresponding attributes ON.
flg.Font = true;
flg.CellShading = true;
// Apply the style to the range.
range.ApplyStyle(stl, flg);
// Save the excel file.
workbook.Save(dataDir + "rangestyles.out.xls");
// ExEnd:1
}
/// <summary>
/// 导出Excel表格
/// </summary>
/// <param name="list">数据集合</param>
/// <param name="header">数据表头</param>
/// <returns></returns>
public bool ExportExcel(List <SecondaryCleaning> cleaningList, List <string> header)
{
var isSuccess = false;
Workbook wb = new Workbook(FileFormatType.Xlsx);
try
{
Worksheet sheet = wb.Worksheets[0];
sheet.Name = "二级清洗明细";
if (cleaningList.Count <= 0)
{
System.Web.HttpContext.Current.Response.Write("<script>alert('没有检测到需要导出数据!');</script>");
return(isSuccess = false);
}
// 为单元格添加样式
Style style = wb.CreateStyle();
style.HorizontalAlignment = TextAlignmentType.Center; //设置居中
style.Font.Size = 12; //文字大小
style.Font.IsBold = true; //粗体
//style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
int rowIndex = 0;
for (int i = 0; i < header.Count; i++)
{
sheet.Cells[rowIndex, i].PutValue(header[i]);
sheet.Cells[rowIndex, i].SetStyle(style);
sheet.Cells.SetColumnWidth(i, 20); //设置宽度
}
for (int i = 0; i < cleaningList.Count; i++) //遍历DataTable行
{
sheet.Cells[i + 1, 0].PutValue(cleaningList[i].CouponType.ToString());
sheet.Cells[i + 1, 1].PutValue(cleaningList[i].CabLicense.ToString());
sheet.Cells[i + 1, 2].PutValue(cleaningList[i].CabOrgName.ToString());
sheet.Cells[i + 1, 3].PutValue(cleaningList[i].CreatedUser.ToString());
sheet.Cells[i + 1, 4].PutValue(cleaningList[i].ManOrgName.ToString());
sheet.Cells[i + 1, 5].PutValue(cleaningList[i].CompanyName.ToString());
sheet.Cells[i + 1, 6].PutValue(cleaningList[i].OperationDate.ToString());
}
}
catch (Exception e)
{
System.Web.HttpContext.Current.Response.Write("<script>alert('导出异常:" + e.Message + "!');</script>");
return(isSuccess = false);
}
#region 输出到Excel
using (MemoryStream ms = new MemoryStream())
{
wb.Save(ms, new OoxmlSaveOptions(SaveFormat.Xlsx));//默认支持xls版，需要修改指定版本
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", "二级清洗报表" + DateTime.Now.ToString("yyyyMMdd")));
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
wb = null;
System.Web.HttpContext.Current.Response.End();
isSuccess = true;
}
#endregion
return(isSuccess);
}
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
}
private void ExportData(string value)
{
try
{
// open and read file
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"];
DataTable dataTable;
dataTable = worksheet.Cells.Rows.Count <= 0 ? worksheet.Cells.ExportDataTableAsString(0, 0, 1, 1, true) : worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.Rows.Count, 8, true);
//Closing the file stream to free all resources
fstream.Close();
//Instantiate a new Workbook
Workbook book = new Workbook();
//Clear all the worksheets
book.Worksheets.Clear();
//Add a new Sheet "Data";
Worksheet sheet = book.Worksheets.Add("Data");
// import data in to sheet
sheet.Cells.ImportDataTable(dataTable, true, "A1");
// Apply Hearder Row/First Row text to Bold
Aspose.Cells.Style objStyle = workbook.CreateStyle();
objStyle.Font.IsBold = true;
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
sheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
book.Worksheets[0].AutoFitColumns();
//Create unique file name
string fileName = System.Guid.NewGuid().ToString() + "." + value;
//Save workbook as per export type
book.Save(this.Response, fileName, ContentDisposition.Attachment, GetSaveFormat(value));
Response.Flush();
}
catch (Exception exc)
{
}
}
public static void ToExcel(DataTable dt)
{
if (dt.Rows.Count <= 0)
{
System.Web.HttpContext.Current.Response.Write("<script>alert('没有检测到需要导出数据!');</script>");
return;
}
Workbook workbook = new Workbook();
Worksheet cellSheet = workbook.Worksheets[0];
cellSheet.Name = dt.TableName;
int rowIndex = 0;
int colIndex = 0;
int colCount = dt.Columns.Count;
int rowCount = dt.Rows.Count;
//列名的处理
for (int i = 0; i < colCount; i++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);
//cellSheet.Cells[rowIndex, colIndex].SetStyle.Font.IsBold = true;
//cellSheet.Cells[rowIndex, colIndex].Style.Font.Name = "宋体";
colIndex++;
}
// Style style = workbook.Styles[workbook.Styles.Add()];
Style style = workbook.CreateStyle();
style.HorizontalAlignment = TextAlignmentType.Center; //设置居中
style.Font.Name = "Arial";
style.Font.Size = 12;
StyleFlag styleFlag = new StyleFlag();
cellSheet.Cells.ApplyStyle(style, styleFlag);
rowIndex++;
for (int i = 0; i < rowCount; i++)
{
colIndex = 0;
for (int j = 0; j < colCount; j++)
{
cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
colIndex++;
}
rowIndex++;
}
cellSheet.AutoFitColumns();
using (MemoryStream ms = new MemoryStream())
{
workbook.Save(ms, new OoxmlSaveOptions(SaveFormat.Xlsx));//默认支持xls版，需要修改指定版本
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
workbook = null;
System.Web.HttpContext.Current.Response.End();
}
}
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);
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Get the first worksheet in the book.
Worksheet WS = workbook.Worksheets[0];
// Create a range of cells.
Aspose.Cells.Range range = WS.Cells.CreateRange(1, 1, 1, 18);
// Name the range.
range.Name = "MyRange";
// Declare a style object.
Style stl;
// Create/add the style object.
stl = workbook.CreateStyle();
// Specify some Font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
// Set the font text color
stl.Font.Color = Color.Red;
// To Set the fill color of the range, you may use ForegroundColor with
// Solid Pattern setting.
stl.ForegroundColor = Color.Yellow;
stl.Pattern = BackgroundType.Solid;
// Create a StyleFlag object.
StyleFlag flg = new StyleFlag();
// Make the corresponding attributes ON.
flg.Font = true;
flg.CellShading = true;
// Apply the style to the range.
range.ApplyStyle(stl, flg);
// Save the excel file.
workbook.Save(dataDir + "rangestyles.out.xls");
// ExEnd:1
}
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);
}
// Instantiate a new Workbook.
Workbook wb1 = new Workbook();
// Get the first worksheet in the workbook.
Worksheet worksheet1 = wb1.Worksheets[0];
// Create a range.
Range mrange = worksheet1.Cells.CreateRange("A18", "J18");
// Name the range.
mrange.Name = "Details";
// Merge the cells of the range.
mrange.Merge();
// Get the range.
Range range1 = wb1.Worksheets.GetRangeByName("Details");
// Define a style object.
Style style = wb1.CreateStyle();
// Set the alignment.
style.HorizontalAlignment = TextAlignmentType.Center;
// Create a StyleFlag object.
StyleFlag flag = new StyleFlag();
// Make the relative style attribute ON.
flag.HorizontalAlignment = true;
// Apply the style to the range.
range1.ApplyStyle(style, flag);
// Input data into range.
range1[0, 0].PutValue("Aspose");
// Save the excel file.
wb1.Save(dataDir + "mergingrange.out.xls");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a workbook object.
// Open an existing excel file.
Workbook workbook = new Workbook(dataDir + "book1.xls");
// Get the named ranges.
Range[] ranges = workbook.Worksheets.GetNamedRanges();
// Create a style object.
Style style = workbook.CreateStyle();
// Set the shading color with solid pattern type.
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
// Create a styleflag object.
StyleFlag flag = new StyleFlag();
// Apply the cellshading.
flag.CellShading = true;
// Creates an arraylist.
ArrayList al = new ArrayList();
// Get the arraylist collection apply the union operation.
al = ranges[0].Union(ranges[1]);
// Define a range object.
Range rng;
int frow, fcol, erow, ecol;
for (int i = 0; i < al.Count; i++)
{
// Get a range.
rng = (Range)al[i];
frow = rng.FirstRow;
fcol = rng.FirstColumn;
erow = rng.RowCount;
ecol = rng.ColumnCount;
// Apply the style to the range.
rng.ApplyStyle(style, flag);
}
// Save the excel file.
workbook.Save(dataDir + "rngUnion.out.xls");
// ExEnd:1
}
public FileDto PCPTBVT_ExportToExcel(PCPTBVT_DTO input)
{
var workbook = new Workbook();
var sheet0 = workbook.Worksheets[0];
var data = procedureHelper.GetData <PCPTBVT_EXPORT_DTO>("PhieuCapPhatTBVTYT_Filter", input);
sheet0.Cells.ImportCustomObjects(
data, 0, 0,
new ImportTableOptions()
{
DateFormat = "dd/MM/yyyy",
IsFieldNameShown = true,
});
sheet0.Cells.ImportArray(
new string[]
{
"Mã PCP",
"Phòng ban",
"Ngày tạo",
"Ghi chú"
},
0, 0, false);
var range = sheet0.Cells.MaxDisplayRange;
var cellStyle = workbook.CreateStyle();
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
for (int r = range.FirstRow; r < range.RowCount; r++)
{
for (int c = range.FirstColumn; c < range.ColumnCount; c++)
{
Cell cell = sheet0.Cells[r, c];
cell.SetStyle(cellStyle, new StyleFlag()
{
TopBorder = true, BottomBorder = true, LeftBorder = true, RightBorder = true
});
}
}
sheet0.AutoFitColumns();
var fileName = $"PCPTBVT_{DateTime.Now.ToString("MMddyyyyHHmmss")}.xlsx";
var file = new FileDto(fileName, MimeTypeNames.ApplicationVndOpenxmlformatsOfficedocumentSpreadsheetmlSheet);
var filePath = Path.Combine(_appFolders.TempFileDownloadFolder, file.FileToken);
workbook.Save(Path.Combine(filePath), SaveFormat.Xlsx);
return(file);
}
byte[] GenerateExcelData(DataTable dt)
{
var wb = new Workbook();
var sheet = wb.Worksheets[0];
var style = wb.CreateStyle();
style.Number = 14;
var styleFlag = new StyleFlag();
styleFlag.NumberFormat = true;
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[0, i].PutValue(dt.Columns[i]);
if (new[] { "StudentDateOfBirth", "StudentEnrollmentDate", "StudentWithdrawalDate", "StudentCurrentIep", "StudentFormerIep", "StudentNorep" }.Contains(dt.Columns[i].ToString()))
{
sheet.Cells.Columns[i].ApplyStyle(style, styleFlag);
}
}
var cells = sheet.Cells;
for (int i = 1; i < dt.Rows.Count; i++)
{
var row = dt.Rows[i];
var c = 0;
cells[i, c++].PutValue(dt.Rows[i]["StudentDateOfBirth"]);
cells[i, c++].PutValue(dt.Rows[i]["SchoolDistrictInformation"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentId"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentFirstName"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentMiddleInitial"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentLastName"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentGradeLevel"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentDateOfBirth"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentStreet1"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentStreet2"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentCity"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentState"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentZipCode"]);
cells[i, c++].PutValue(dt.Rows[i]["ActivitySchoolYear"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentEnrollmentDate"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentWithdrawalDate"]);
cells[i, c++].PutValue(dt.Rows[i]["StudentNorep"]);
cells[i, c++].PutValue(dt.Rows[i]["LastUpdated"]);
}
using (var xlsxStream = new MemoryStream())
{
wb.Save(xlsxStream, new XlsSaveOptions(SaveFormat.Xlsx));
return(xlsxStream.ToArray());
}
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string filePath = dataDir + "pivotTable_test.xlsx";
// Create workbook object from source file containing pivot table
Workbook workbook = new Workbook(filePath);
// Access the worksheet by its name
Worksheet worksheet = workbook.Worksheets["PivotTable"];
// Access the pivot table
// PivotTable pivotTable = worksheet.PivotTables[0];
// Create a style object with background color light blue
Style style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.LightBlue;
// Format entire pivot table with light blue color
// worksheet.FormatAll(style);
// Create another style object with yellow color
style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.Yellow;
// Format the cells of the first row of the pivot table with yellow color
for (int col = 0; col < 5; col++)
{
// worksheet.Format(1, col, style);
}
dataDir = dataDir + "output.out.xlsx";
// Save the workbook object
workbook.Save(dataDir);
// ExEnd:1
Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a workbook object.
// Open an existing excel file.
Workbook workbook = new Workbook(dataDir + "book1.xls");
// Get the named ranges.
Range[] ranges = workbook.Worksheets.GetNamedRanges();
// Create a style object.
Style style = workbook.CreateStyle();
// Set the shading color with solid pattern type.
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
// Create a styleflag object.
StyleFlag flag = new StyleFlag();
// Apply the cellshading.
flag.CellShading = true;
// Creates an arraylist.
ArrayList al = new ArrayList();
// Get the arraylist collection apply the union operation.
al = ranges[0].Union(ranges[1]);
// Define a range object.
Range rng;
int frow, fcol, erow, ecol;
for (int i = 0; i < al.Count; i++)
{
// Get a range.
rng = (Range)al[i];
frow = rng.FirstRow;
fcol = rng.FirstColumn;
erow = rng.RowCount;
ecol = rng.ColumnCount;
// Apply the style to the range.
rng.ApplyStyle(style, flag);
}
// Save the excel file.
workbook.Save(dataDir + "rngUnion.out.xls");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string filePath = dataDir + "pivotTable_test.xlsx";
// Create workbook object from source file containing pivot table
Workbook workbook = new Workbook(filePath);
// Access the worksheet by its name
Worksheet worksheet = workbook.Worksheets["PivotTable"];
// Access the pivot table
// PivotTable pivotTable = worksheet.PivotTables[0];
// Create a style object with background color light blue
Style style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.LightBlue;
// Format entire pivot table with light blue color
// worksheet.FormatAll(style);
// Create another style object with yellow color
style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.Yellow;
// Format the cells of the first row of the pivot table with yellow color
for (int col = 0; col < 5; col++)
{
// worksheet.Format(1, col, style);
}
dataDir = dataDir + "output.out.xlsx";
// Save the workbook object
workbook.Save(dataDir);
// ExEnd:1
Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Access the cells in the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;
// Create a range of cells.
Range range = cells.CreateRange("A6", "P216");
// Declare style.
Style stl;
// Create the style adding to the style collection.
stl = workbook.CreateStyle();
// Specify the font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
stl.Font.Color = Color.Blue;
// Set the borders
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.TopBorder].Color = Color.Blue;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.LeftBorder].Color = Color.Blue;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.BottomBorder].Color = Color.Blue;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.RightBorder].Color = Color.Blue;
// Create StyleFlag object.
StyleFlag flg = new StyleFlag();
// Make the corresponding formatting attributes ON.
flg.Font = true;
flg.Borders = true;
// Apply the style with format settings to the range.
range.ApplyStyle(stl, flg);
// Save the excel file.
workbook.Save(dataDir + "output.xls");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Access the cells in the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;
// Create a range of cells.
Range range = cells.CreateRange("A6", "P216");
// Declare style.
Style stl;
// Create the style adding to the style collection.
stl = workbook.CreateStyle();
// Specify the font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
stl.Font.Color = Color.Blue;
// Set the borders
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.TopBorder].Color = Color.Blue;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.LeftBorder].Color = Color.Blue;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.BottomBorder].Color = Color.Blue;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
stl.Borders[BorderType.RightBorder].Color = Color.Blue;
// Create StyleFlag object.
StyleFlag flg = new StyleFlag();
// Make the corresponding formatting attributes ON.
flg.Font = true;
flg.Borders = true;
// Apply the style with format settings to the range.
range.ApplyStyle(stl, flg);
// Save the excel file.
workbook.Save( dataDir + "output.xls");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create a workbook.
Workbook workbook = new Workbook();
// Create a new style object.
Style style = workbook.CreateStyle();
// Set the number format.
style.Number = 14;
// Set the font color to red color.
style.Font.Color = System.Drawing.Color.Red;
// Name the style.
style.Name = "Date1";
// Get the first worksheet cells.
Cells cells = workbook.Worksheets[0].Cells;
// Specify the style (described above) to A1 cell.
cells["A1"].SetStyle(style);
// Create a range (B1:D1).
Range range = cells.CreateRange("B1", "D1");
// Initialize styleflag object.
StyleFlag flag = new StyleFlag();
// Set all formatting attributes on.
flag.All = true;
// Apply the style (described above)to the range.
range.ApplyStyle(style, flag);
// Modify the style (described above) and change the font color from red to black.
style.Font.Color = System.Drawing.Color.Black;
// Done! Since the named style (described above) has been set to a cell and range,
// The change would be Reflected(new modification is implemented) to cell(A1) and // Range (B1:D1).
style.Update();
// Save the excel file.
workbook.Save(dataDir+ "book_styles.out.xls");
// ExEnd:1
}
public static void WriteExcel(string fileName, string sheetName, DataTable dataTable,
bool createFileAndSheet, bool exportColumnName, FileFormatType fileFormatType)
{
Workbook workbook = new Workbook();
if (!File.Exists(fileName))
{
if (createFileAndSheet)
{
if (!workbook.Worksheets.OfType <Worksheet>().Select(w => w.Name).Contains(sheetName))
{
workbook.Worksheets.Add(sheetName);
}
workbook.Save(fileName);
}
}
workbook = new Workbook();
workbook.Open(fileName);
int column = 0;
foreach (DataColumn dataColumn in dataTable.Columns)
{
workbook.Worksheets[sheetName].Cells[0, column++].Value = dataColumn.Caption;
}
Style style = workbook.CreateStyle();
style.Number = 49; // TEXT format
int row = 1;
column = 0;
foreach (DataRow dataRow in dataTable.Rows)
{
foreach (object item in dataRow.ItemArray)
{
Cell cell = workbook.Worksheets[sheetName].Cells[row, column];
cell.SetStyle(style);
cell.Value = dataRow[column] + "";
column++;
}
column = 0;
row++;
}
workbook.Save(fileName, fileFormatType);
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string filePath = dataDir + "pivotTable_test.xlsx";
//Create workbook object from source file containing pivot table
Workbook workbook = new Workbook(filePath);
//Access the worksheet by its name
Worksheet worksheet = workbook.Worksheets["PivotTable"];
//Access the pivot table
PivotTable pivotTable = worksheet.PivotTables[0];
//Create a style object with background color light blue
Style style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.LightBlue;
//Format entire pivot table with light blue color
pivotTable.FormatAll(style);
//Create another style object with yellow color
style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.Yellow;
//Format the cells of the first row of the pivot table with yellow color
for (int col = 0; col < 5; col++)
{
pivotTable.Format(1, col, style);
}
//Save the workbook object
workbook.Save(dataDir+ "output.xlsx");
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
string filePath = dataDir + "pivotTable_test.xlsx";
//Create workbook object from source file containing pivot table
Workbook workbook = new Workbook(filePath);
//Access the worksheet by its name
Worksheet worksheet = workbook.Worksheets["PivotTable"];
//Access the pivot table
PivotTable pivotTable = worksheet.PivotTables[0];
//Create a style object with background color light blue
Style style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.LightBlue;
//Format entire pivot table with light blue color
pivotTable.FormatAll(style);
//Create another style object with yellow color
style = workbook.CreateStyle();
style.Pattern = BackgroundType.Solid;
style.BackgroundColor = Color.Yellow;
//Format the cells of the first row of the pivot table with yellow color
for (int col = 0; col < 5; col++)
{
pivotTable.Format(1, col, style);
}
//Save the workbook object
workbook.Save(dataDir + "output.xlsx");
}
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);
// Instantiate a new Workbook.
Workbook wb1 = new Workbook();
// Get the first worksheet in the workbook.
Worksheet worksheet1 = wb1.Worksheets[0];
// Create a range.
Range mrange = worksheet1.Cells.CreateRange("A18", "J18");
// Name the range.
mrange.Name = "Details";
// Merge the cells of the range.
mrange.Merge();
// Get the range.
Range range1 = wb1.Worksheets.GetRangeByName("Details");
// Define a style object.
Style style = wb1.CreateStyle();
// Set the alignment.
style.HorizontalAlignment = TextAlignmentType.Center;
// Create a StyleFlag object.
StyleFlag flag = new StyleFlag();
// Make the relative style attribute ON.
flag.HorizontalAlignment = true;
// Apply the style to the range.
range1.ApplyStyle(style, flag);
// Input data into range.
range1[0, 0].PutValue("Aspose");
// Save the excel file.
wb1.Save(dataDir + "mergingrange.out.xls");
// ExEnd:1
}
public static void Run()
{
// Instantiate a workbook object.
// Open an existing excel file.
Workbook workbook = new Workbook(sourceDir + "sampleUnionOfRanges.xlsx");
// Get the named ranges.
Range[] ranges = workbook.Worksheets.GetNamedRanges();
// Create a style object.
Style style = workbook.CreateStyle();
// Set the shading color with solid pattern type.
style.ForegroundColor = Color.Red;
style.Pattern = BackgroundType.Solid;
// Create a styleflag object.
StyleFlag flag = new StyleFlag();
// Apply the cellshading.
flag.CellShading = true;
// Creates an arraylist.
ArrayList al = new ArrayList();
// Get the arraylist collection apply the union operation.
al = ranges[0].Union(ranges[1]);
// Define a range object.
Range rng;
int frow, fcol, erow, ecol;
for (int i = 0; i < al.Count; i++)
{
// Get a range.
rng = (Range)al[i];
frow = rng.FirstRow;
fcol = rng.FirstColumn;
erow = rng.RowCount;
ecol = rng.ColumnCount;
// Apply the style to the range.
rng.ApplyStyle(style, flag);
}
// Save the excel file.
workbook.Save(outputDir + "outputUnionOfRanges.xlsx");
Console.WriteLine("UnionOfRanges executed successfully.");
}
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();
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create a workbook.
Workbook workbook = new Workbook();
// Create a new style object.
Style style = workbook.CreateStyle();
// Set the number format.
style.Number = 14;
// Set the font color to red color.
style.Font.Color = System.Drawing.Color.Red;
// Name the style.
style.Name = "Date1";
// Get the first worksheet cells.
Cells cells = workbook.Worksheets[0].Cells;
// Specify the style (described above) to A1 cell.
cells["A1"].SetStyle(style);
// Create a range (B1:D1).
Range range = cells.CreateRange("B1", "D1");
// Initialize styleflag object.
StyleFlag flag = new StyleFlag();
// Set all formatting attributes on.
flag.All = true;
// Apply the style (described above)to the range.
range.ApplyStyle(style, flag);
// Modify the style (described above) and change the font color from red to black.
style.Font.Color = System.Drawing.Color.Black;
// Done! Since the named style (described above) has been set to a cell and range,
// The change would be Reflected(new modification is implemented) to cell(A1) and // Range (B1:D1).
style.Update();
// Save the excel file.
workbook.Save(dataDir + "book_styles.out.xls");
// ExEnd:1
}
public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create a workbook.
Workbook workbook = new Workbook();
// Create a new style object.
Style style = workbook.CreateStyle();
// Set the number format.
style.Number = 14;
// Set the font color to red color.
style.Font.Color = System.Drawing.Color.Red;
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = System.Drawing.Color.Yellow;
// Name the style.
style.Name = "MyCustomDate";
// Get the first worksheet cells.
Cells cells = workbook.Worksheets[0].Cells;
// Specify the style (described above) to A1 cell.
cells["A1"].SetStyle(style);
// Create a range (B1:D1).
Range range = cells.CreateRange("B6", "D10");
// Initialize styleflag object.
StyleFlag flag = new StyleFlag();
// Set all formatting attributes on.
flag.All = true;
Style style2 = workbook.GetNamedStyle("MyCustomDate");
// Apply the style (described above)to the range.
range.ApplyStyle(style2, flag);
cells["C8"].PutValue(43105);
// Save the excel file.
workbook.Save(outputDir + "outputModifyThroughStyleObject.xlsx");
Console.WriteLine("ModifyThroughStyleObject executed successfully.");
}
/// <summary>
/// Builds a new style container whose underlying style is registered with the workbook.
/// </summary>
/// <param name="workbook">The workbook.</param>
/// <returns>
/// A new style container, who's underlying style is registered with the workbook.
/// </returns>
/// <exception cref="ArgumentNullException"><paramref name="workbook"/> is null.</exception>
public static StyleContainer BuildNew(
Workbook workbook)
{
if (workbook == null)
{
throw new ArgumentNullException(nameof(workbook));
}
var style = workbook.CreateStyle();
var styleFlag = new StyleFlag();
var result = new StyleContainer(style, styleFlag);
return(result);
}
public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiate a new Workbook
Workbook workbook = new Workbook();
// Get the first worksheet's cells collection
Cells cells = workbook.Worksheets[0].Cells;
Style st = workbook.CreateStyle();
st.Pattern = BackgroundType.Solid;
st.ForegroundColor = System.Drawing.Color.Yellow;
// Add string values to the cells
cells["A1"].PutValue("A1");
cells["A1"].SetStyle(st, true);
st.ForegroundColor = System.Drawing.Color.Red;
cells["C10"].PutValue("C10");
cells["C10"].SetStyle(st, true);
// Get the conditional icon's image data
byte[] imagedata = ConditionalFormattingIcon.GetIconImageData(IconSetType.TrafficLights31, 0);
// Create a stream based on the image data
MemoryStream stream = new MemoryStream(imagedata);
// Add a blank picture to the D1 cell
Picture pic = workbook.Worksheets[0].Shapes.AddPicture(5, 5, stream, 600, 600);
// Specify the formula that refers to the source range of cells
pic.Formula = "A1:C10";
// Update the shapes selected value in the worksheet
workbook.Worksheets[0].Shapes.UpdateSelectedValue();
//Reset the picture to original height and width
pic.HeightScale = 100;
pic.WidthScale = 100;
// Save the Excel file.
workbook.Save(outputDir + "outputInsertPictureCellReference.xlsx");
Console.WriteLine("InsertPictureCellReference executed successfully.");
}
public static void Run()
{
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Access the cells in the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;
// Create a range of cells.
Range range = cells.CreateRange("D6", "M16");
// Declare style.
Style stl;
// Create the style adding to the style collection.
stl = workbook.CreateStyle();
// Specify the font settings.
stl.Font.Name = "Arial";
stl.Font.IsBold = true;
stl.Font.Color = Color.Blue;
// Set the borders
stl.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.TopBorder].Color = Color.Blue;
stl.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.LeftBorder].Color = Color.Blue;
stl.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.BottomBorder].Color = Color.Blue;
stl.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;
stl.Borders[BorderType.RightBorder].Color = Color.Blue;
// Create StyleFlag object.
StyleFlag flg = new StyleFlag();
// Make the corresponding formatting attributes ON.
flg.Font = true;
flg.Borders = true;
// Apply the style with format settings to the range.
range.ApplyStyle(stl, flg);
// Save the excel file.
workbook.Save(outputDir + "outputSetBorderAroundEachCell.xlsx");
Console.WriteLine("SetBorderAroundEachCell executed successfully.");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a workbook object.
// Open an existing excel file.
Workbook workbook = new Workbook(dataDir + "book1.xls");
// Get the named ranges.
Range[] ranges = workbook.Worksheets.GetNamedRanges();
// Check whether the first range intersect the second range.
bool isintersect = ranges[0].IsIntersect(ranges[1]);
// Create a style object.
Style style = workbook.CreateStyle();
// Set the shading color with solid pattern type.
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
// Create a styleflag object.
StyleFlag flag = new StyleFlag();
// Apply the cellshading.
flag.CellShading = true;
// If first range intersects second range.
if (isintersect)
{
// Create a range by getting the intersection.
Range intersection = ranges[0].Intersect(ranges[1]);
// Name the range.
intersection.Name = "Intersection";
// Apply the style to the range.
intersection.ApplyStyle(style, flag);
}
// Save the excel file.
workbook.Save(dataDir + "rngIntersection.out.xls");
// ExEnd:1
}
static void Main(string[] args)
{
Workbook workbook = new Workbook(); // Creating a Workbook object
workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[0];
// Style the cell with borders all around.
Style style = workbook.CreateStyle();
style.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
style.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Green);
style.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Blue);
style.SetBorder(BorderType.TopBorder, CellBorderType.MediumDashed, Color.Black);
Cell cell = worksheet.Cells["A1"];
cell.SetStyle(style);
workbook.Save("test.xlsx", SaveFormat.Xlsx); //Workbooks can be saved in many formats
}
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 an Workbook object
Workbook workbook = new Workbook();
// Adding Orchid color to the palette at 55th index
workbook.ChangePalette(Color.Orchid, 55);
// Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
// Accessing the "A1" cell from the worksheet
Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Hello Aspose!");
// Defining new Style object
Style styleObject = workbook.CreateStyle();
// Setting the Orchid (custom) color to the font
styleObject.Font.Color = Color.Orchid;
// Applying the style to the cell
cell.SetStyle(styleObject);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Auto);
// ExEnd:1
}
// Save data to excel file
protected void ProcessButton_Click(object sender, EventArgs e)
{
try
{
// Check for license and apply if exists
string licenseFile = Server.MapPath("~/App_Data/Aspose.Total.lic");
if (File.Exists(licenseFile))
{
License license = new License();
license.SetLicense(licenseFile);
}
//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(Server.MapPath("~/Addons/Aspose.SiteFinity.FormBuilder.ToExcel/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("~/Addons/Aspose.SiteFinity.FormBuilder.ToExcel/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 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();
// Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();
// Obtaining the reference of the first worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
// Accessing the "A1" cell from the worksheet
Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Hello Aspose!");
// Adding a new Style
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;
// Assigning the Style object to the "A1" cell
cell.SetStyle(style);
// Apply the same style to some other cells
worksheet.Cells["B1"].SetStyle(style);
worksheet.Cells["C1"].SetStyle(style);
worksheet.Cells["D1"].SetStyle(style);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls");
// ExEnd:1
}
private void ExportData(string value)
{
try
{
// Check for license and apply if exists
string licenseFile = Server.MapPath("~/App_Data/Aspose.Total.lic");
if (File.Exists(licenseFile))
{
License license = new License();
license.SetLicense(licenseFile);
}
// open and read file
FileStream fstream = new FileStream((Server.MapPath("~/Addons/Aspose.SiteFinity.FormBuilder.ToExcel/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"];
DataTable dataTable;
dataTable = worksheet.Cells.Rows.Count <= 0 ? worksheet.Cells.ExportDataTableAsString(0, 0, 1, 1, true) : worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.Rows.Count, 8, true);
//Closing the file stream to free all resources
fstream.Close();
//Instantiate a new Workbook
Workbook book = new Workbook();
//Clear all the worksheets
book.Worksheets.Clear();
//Add a new Sheet "Data";
Worksheet sheet = book.Worksheets.Add("Data");
// import data in to sheet
sheet.Cells.ImportDataTable(dataTable, true, "A1");
// Apply Hearder Row/First Row text to Bold
Aspose.Cells.Style objStyle = workbook.CreateStyle();
objStyle.Font.IsBold = true;
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
sheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
book.Worksheets[0].AutoFitColumns();
//Create unique file name
string fileName = System.Guid.NewGuid().ToString() + "." + value;
//Save workbook as per export type
book.Save(this.Response, fileName, ContentDisposition.Attachment, GetSaveFormat(value));
Response.Flush();
}
catch (Exception exc)
{
}
}
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);
// Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Get the first Worksheet Cells.
Cells cells = workbook.Worksheets[0].Cells;
// Fill some sample data into the cells.
for (int i = 0; i < 50; i++)
{
for (int j = 0; j < 10; j++)
{
cells[i, j].PutValue(i.ToString() + "," + j.ToString());
}
}
// Create a range (A1:D3).
Range range = cells.CreateRange("A1", "D3");
// Create a style object.
Style style;
style = workbook.CreateStyle();
// Specify the font attribute.
style.Font.Name = "Calibri";
// Specify the shading color.
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
// Specify the border attributes.
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Blue;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Blue;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Blue;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Blue;
// Create the styleflag object.
StyleFlag flag1 = new StyleFlag();
// Implement font attribute
flag1.FontName = true;
// Implement the shading / fill color.
flag1.CellShading = true;
// Implment border attributes.
flag1.Borders = true;
// Set the Range style.
range.ApplyStyle(style, flag1);
// Create a second range (C10:F12).
Range range2 = cells.CreateRange("C10", "F12");
// Copy the range data only.
range2.CopyData(range);
dataDir = dataDir + "CopyRangeData.out.xlsx";
// Save the excel file.
workbook.Save(dataDir);
// ExEnd:1
Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
Workbook workbook = new Workbook();
// Create a designer workbook
// Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["A1"].PutValue("Husband Name");
worksheet.Cells["A2"].PutValue("&=Husband.Name");
worksheet.Cells["B1"].PutValue("Husband Age");
worksheet.Cells["B2"].PutValue("&=Husband.Age");
worksheet.Cells["C1"].PutValue("Wife's Name");
worksheet.Cells["C2"].PutValue("&=Husband.Wives.Name");
worksheet.Cells["D1"].PutValue("Wife's Age");
worksheet.Cells["D2"].PutValue("&=Husband.Wives.Age");
// Apply Style to A1:D1
Range range = worksheet.Cells.CreateRange("A1:D1");
Style style = workbook.CreateStyle();
style.Font.IsBold = true;
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
StyleFlag flag = new StyleFlag();
flag.All = true;
range.ApplyStyle(style, flag);
// Initialize WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();
// Load the template file
designer.Workbook = workbook;
System.Collections.Generic.List<Husband> list = new System.Collections.Generic.List<Husband>();
// Create an object for the Husband class
Husband h1 = new Husband("Mark John", 30);
// Create the relevant Wife objects for the Husband object
h1.Wives = new List<Wife>();
h1.Wives.Add(new Wife("Chen Zhao", 34));
h1.Wives.Add(new Wife("Jamima Winfrey", 28));
h1.Wives.Add(new Wife("Reham Smith", 35));
// Create another object for the Husband class
Husband h2 = new Husband("Masood Shankar", 40);
// Create the relevant Wife objects for the Husband object
h2.Wives = new List<Wife>();
h2.Wives.Add(new Wife("Karishma Jathool", 36));
h2.Wives.Add(new Wife("Angela Rose", 33));
h2.Wives.Add(new Wife("Hina Khanna", 45));
// Add the objects to the list
list.Add(h1);
list.Add(h2);
// Specify the DataSource
designer.SetDataSource("Husband", list);
// Process the markers
designer.Process();
// Autofit columns
worksheet.AutoFitColumns();
// Save the Excel file.
designer.Workbook.Save(dataDir + "output.xlsx");
// ExEnd:1
}
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("~/Addons/Aspose.SiteFinity.FormBuilder.ToExcel/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("~/Addons/Aspose.SiteFinity.FormBuilder.ToExcel/uploads/AsposeDynamicFormsDataFile.xlsx"), SaveFormat.Xlsx);
PopulateGrid();
ClearFields(); // clear field function which clear all fields while update/adding record
}
}
}
}
}
catch (Exception exc)
{
ShowException(exc);
}
}
private static void CreateCellsFormatting(Workbook workbook)
{
// Define a style object adding a new style to the collection list.
Style stl0 = workbook.CreateStyle();
// Set a custom shading color of the cells.
stl0.ForegroundColor = Color.FromArgb(155, 204, 255);
stl0.Pattern = BackgroundType.Solid;
stl0.Font.Name = "Trebuchet MS";
stl0.Font.Size = 18;
stl0.Font.Color = Color.Maroon;
stl0.Font.IsBold = true;
stl0.Font.IsItalic = true;
// Define a style flag struct.
StyleFlag flag = new StyleFlag();
flag.CellShading = true;
flag.FontName = true;
flag.FontSize = true;
flag.FontColor = true;
flag.FontBold = true;
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.CreateStyle();
// 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);
stl1.Pattern = BackgroundType.Solid;
stl1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
stl1.Borders[BorderType.LeftBorder].Color = Color.White;
stl1.HorizontalAlignment = TextAlignmentType.Center;
stl1.VerticalAlignment = TextAlignmentType.Center;
stl1.Font.Name = "Times New Roman";
stl1.Font.Size = 10;
stl1.Font.Color = Color.White;
stl1.Font.IsBold = true;
// Set a style flag struct.
flag = new StyleFlag();
flag.LeftBorder = true;
flag.Rotation = true;
flag.CellShading = true;
flag.HorizontalAlignment = true;
flag.VerticalAlignment = true;
flag.FontName = true;
flag.FontSize = true;
flag.FontColor = true;
flag.FontBold = true;
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.CreateStyle();
// Set the custom cell shading color.
stl2.ForegroundColor = Color.FromArgb(155, 204, 255);
stl2.Pattern = BackgroundType.Solid;
stl2.Font.Name = "Trebuchet MS";
stl2.Font.Color = Color.Maroon;
stl2.Font.Size = 10;
flag = new StyleFlag();
flag.CellShading = true;
flag.FontName = true;
flag.FontColor = true;
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.CreateStyle();
// Set the custom cell filling color.
stl3.ForegroundColor = Color.FromArgb(124, 199, 72);
stl3.Pattern = BackgroundType.Solid;
cells["A2"].SetStyle(stl3);
// Define a style object adding a new style to the collection list.
Style stl4 = workbook.CreateStyle();
// Set the custom font text color.
stl4.Font.Color = Color.FromArgb(0, 51, 105);
stl4.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl4.Borders[BorderType.BottomBorder].Color = Color.FromArgb(124, 199, 72);
stl4.ForegroundColor = Color.White;
stl4.Pattern = BackgroundType.Solid;
// Set custom number format.
stl4.Custom = "$#,##0.0";
// Set a style flag struct.
flag = new StyleFlag();
flag.FontColor = true;
flag.CellShading = true;
flag.NumberFormat = true;
flag.BottomBorder = true;
// Define a style object adding a new style to the collection list.
Style stl5 = workbook.CreateStyle();
stl5.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
stl5.Borders[BorderType.BottomBorder].Color = Color.FromArgb(124, 199, 72);
stl5.ForegroundColor = Color.FromArgb(250, 250, 200);
stl5.Pattern = BackgroundType.Solid;
// Set custom number format.
stl5.Custom = "$#,##0.0";
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.CreateStyle();
// Set the custom fill color of the cells.
stl6.ForegroundColor = Color.FromArgb(0, 51, 105);
stl6.Pattern = BackgroundType.Solid;
stl6.Font.Name = "Arial";
stl6.Font.Size = 10;
stl6.Font.Color = Color.White;
stl6.Font.IsBold = true;
// Set the custom number format.
stl6.Custom = "$#,##0.0";
// Set the style flag struct.
flag = new StyleFlag();
flag.CellShading = true;
flag.FontName = true;
flag.FontSize = true;
flag.FontColor = true;
flag.FontBold = true;
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);
}
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("~/Addons/Aspose.SiteFinity.FormBuilder.ToExcel/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("~/Addons/Aspose.SiteFinity.FormBuilder.ToExcel/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();
}
