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.");
}
/// <summary>
/// 自己定义的
/// </summary>
public static bool Ecu_Id_DataTableToExcel(DataTable datatable, string filepath, out string error)
{
//string file = Application.StartupPath + "\\13id\\" +"id.xls";
//filepath = file;
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
cells.SetColumnWidth(4, 40);
cells.SetColumnWidth(3, 40);
Aspose.Cells.Style style = new Style();
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
style.Pattern = BackgroundType.Solid;
style.Font.IsBold = true;
StyleFlag a = new StyleFlag();
a.WrapText = true;
style.IsTextWrapped = true; //文本换行 这里很重要的；有助于格式的的显示内容
cells.ApplyStyle(style, a);
cells.ImportDataTable(datatable, true, "A5");
workbook.Save(filepath);
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return(false);
}
return(true);
}
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);
}
/// <summary>
/// 标题样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="pRange"></param>
private static void DrawTitleStyle(Workbook workbook, Range pRange)
{
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Gray;
style.ForegroundColor = c;
style.Pattern = BackgroundType.Solid;
style.Font.Color = Color.White;
style.Font.IsBold = true;
//s.Borders.DiagonalColor = Color.Red;
//s.Borders.DiagonalStyle= CellBorderType.Thick;
//s.Borders.SetColor(Color.Red);
//s.Borders.SetStyle(CellBorderType.Thick);
//s.SetBorder(BorderType.Horizontal | BorderType.Vertical, CellBorderType.Dashed , Color.Red);
//s.SetBorder(BorderType.LeftBorder | BorderType.RightBorder | BorderType.BottomBorder | BorderType.TopBorder, CellBorderType.Thick, Color.Red);
style.HorizontalAlignment = TextAlignmentType.Center;
//s.ShrinkToFit = true;
SetBorder(style);
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
styleFlag.Borders = true;
pRange.ApplyStyle(style, styleFlag);
}
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()
{
// 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
}
/// <summary>
/// 設定 Worksheet 的 欄位寬度
/// </summary>
/// <param name="sheet">綁定 DataTable 的 Worksheet </param>
/// <param name="columnInfos">item1:Column Name, item2:Column Width</param>
private static void ChangedSheetColumnStyle(Worksheet sheet, Dictionary <string, Tuple <string, double, Style> > columnInfos, TextAlignmentType headerHorizontalAlignment)
{
//change columnName
if (columnInfos != null)
{
var columnIndex = 0;
foreach (KeyValuePair <string, Tuple <string, double, Style> > columnInfo in columnInfos)
{
if (columnInfo.Value.Item2 > -1)
{
sheet.Cells.SetColumnWidth(columnIndex, columnInfo.Value.Item2);
}
if (columnInfo.Value.Item3 != null)
{
var styleFlag = new StyleFlag();
styleFlag.NumberFormat = true;
styleFlag.HorizontalAlignment = true;
sheet.Cells.Columns[columnIndex].ApplyStyle(columnInfo.Value.Item3, styleFlag);
}
//設定 header 列
var cellStyle = sheet.Cells[columnIndex, 0].GetStyle();
var cellStyleFlag = new StyleFlag();
cellStyleFlag.HorizontalAlignment = true;
cellStyle.HorizontalAlignment = headerHorizontalAlignment;
sheet.Cells[0, columnIndex].SetStyle(cellStyle, cellStyleFlag);
columnIndex++;
}
}
}
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);
// Create a new workbook.
Workbook wb = new Workbook();
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
// Define the styleflag object.
StyleFlag flag;
// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
flag = new StyleFlag();
flag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, flag);
}
// Get the first column style.
style = sheet.Cells.Columns[0].Style;
// Lock it.
style.IsLocked = true;
// Instantiate the flag.
flag = new StyleFlag();
// Set the lock setting.
flag.Locked = true;
// Apply the style to the first column.
sheet.Cells.Columns[0].ApplyStyle(style, flag);
// Protect the sheet.
sheet.Protect(ProtectionType.All);
// Save the excel file.
wb.Save(dataDir + "output.out.xls", SaveFormat.Excel97To2003);
// ExEnd:1
}
public void CreateStaticReport()
{
//Create a workbook.
Workbook workbook = new Workbook();
//Create a new style object.
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
//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 = "Style1";
//Get the first worksheet cells.
Cells cells = workbook.Worksheets[0].Cells;
//Put value in cell
cells["A1"].PutValue("Original Color Red & Modified Color Blue");
//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 struct.
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 blue.
style.Font.Color = System.Drawing.Color.Blue;
//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();
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "ModifyExistingStyle.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "ModifyExistingStyle.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
HttpContext.Current.Response.End();
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.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 collection of the Excel object
int i = workbook.Styles.Add();
//Accessing the newly added Style to the Excel object
Style style = workbook.Styles[i];
//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.xls");
}
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);
}
// Create a new workbook.
Workbook wb = new Workbook();
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
// Define the styleflag object.
StyleFlag flag;
// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
flag = new StyleFlag();
flag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, flag);
}
// Get the first column style.
style = sheet.Cells.Columns[0].Style;
// Lock it.
style.IsLocked = true;
// Instantiate the flag.
flag = new StyleFlag();
// Set the lock setting.
flag.Locked = true;
// Apply the style to the first column.
sheet.Cells.Columns[0].ApplyStyle(style, flag);
// Protect the sheet.
sheet.Protect(ProtectionType.All);
// Save the excel file.
wb.Save(dataDir + "output.out.xls", SaveFormat.Excel97To2003);
// ExEnd:1
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.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");
//Add a style object to the collection.
int i = wb1.Styles.Add();
//Define a style object.
Style style = wb1.Styles[i];
//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);
// 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 column from the Columns collection
Column column = worksheet.Cells.Columns[0];
// Applying the style to the column
column.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 Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
// Create a new workbook.
Workbook wb = new Workbook();
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
// Define the styleflag object.
StyleFlag flag;
// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
flag = new StyleFlag();
flag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, flag);
}
// Get the first row style.
style = sheet.Cells.Rows[0].Style;
// Lock it.
style.IsLocked = true;
// Instantiate the flag.
flag = new StyleFlag();
// Set the lock setting.
flag.Locked = true;
// Apply the style to the first row.
sheet.Cells.ApplyRowStyle(0, style, flag);
// Protect the sheet.
sheet.Protect(ProtectionType.All);
// Save the excel file.
wb.Save(dataDir + "output.xls", SaveFormat.Excel97To2003);
}
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();
}
}
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 Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
// Create a new workbook.
Workbook wb = new Workbook();
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
// Define the styleflag object.
StyleFlag flag;
// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
flag = new StyleFlag();
flag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, flag);
}
// Get the first row style.
style = sheet.Cells.Rows[0].Style;
// Lock it.
style.IsLocked = true;
// Instantiate the flag.
flag = new StyleFlag();
// Set the lock setting.
flag.Locked = true;
// Apply the style to the first row.
sheet.Cells.ApplyRowStyle(0, style, flag);
// Protect the sheet.
sheet.Protect(ProtectionType.All);
// Save the excel file.
wb.Save(dataDir + "output.xls", SaveFormat.Excel97To2003);
}
/// <summary>
/// 设置标题区域的样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="pRange"></param>
private void SetTitleStyle(Workbook workbook, Range pRange)
{
Style style = this.CreateStyle(workbook);
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
styleFlag.Borders = true;
pRange.ApplyStyle(style, styleFlag);
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.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);
}
// Create a new workbook.
Workbook wb = new Workbook();
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
//Define the styleflag object
StyleFlag styleflag;
// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
styleflag = new StyleFlag();
styleflag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, styleflag);
}
// Lock the three cells...i.e. A1, B1, C1.
style = sheet.Cells["A1"].GetStyle();
style.IsLocked = true;
sheet.Cells["A1"].SetStyle(style);
style = sheet.Cells["B1"].GetStyle();
style.IsLocked = true;
sheet.Cells["B1"].SetStyle(style);
style = sheet.Cells["C1"].GetStyle();
style.IsLocked = true;
sheet.Cells["C1"].SetStyle(style);
// Finally, Protect the sheet now.
sheet.Protect(ProtectionType.All);
// Save the excel file.
wb.Save(dataDir + "output.out.xls", SaveFormat.Excel97To2003);
//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
}
/// <summary>
/// 设置摘要区域的样式
/// </summary>
/// <param name="pWorkbook"></param>
/// <param name="pRange"></param>
private void SetSummaryRangeStyle(Workbook pWorkbook, Range pRange)
{
Style dataRangeStyle = pWorkbook.Styles[pWorkbook.Styles.Add()];
SetBorder(dataRangeStyle);
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
styleFlag.Borders = true;
pRange.ApplyStyle(dataRangeStyle, styleFlag);
}
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 Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.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.Styles[workbook.Styles.Add()];
//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);
// 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 async Task <IActionResult> ExportExcel(string receive_Date)
{
var data = await _serviceCompare.GetCompare(receive_Date);
var count = data.Count;
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\CompareReport.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet worksheet = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data);
designer.Process();
// ----------------Add style excel------------------------//
Style stl = designer.Workbook.CreateStyle();
stl.ForegroundColor = Color.FromArgb(210, 105, 30);
stl.Pattern = BackgroundType.Solid;
StyleFlag flg = new StyleFlag();
flg.Font = true;
flg.CellShading = true;
flg.Alignments = true;
for (int i = 17; i < count + 17; i++)
{
var checkCell1 = worksheet.Cells["K" + i].Value.ToString();
var checkCell2 = worksheet.Cells["O" + i].Value.ToString();
Aspose.Cells.Range range = worksheet.Cells.CreateRange(i - 1, 0, 1, 15);
if (checkCell1 == "0" || checkCell2 == "0")
{
range.ApplyStyle(stl, flg);
}
}
//--------------------End add Style-------------------------//
// for (int i = 16; i < count + 16; i++)
// {
// worksheet.Cells["H"+ i].PutValue(receive_Date);
// }
worksheet.Cells["E2"].PutValue(data[0].Freeze_Date);
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "Excel" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.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);
// Create a new workbook.
Workbook wb = new Workbook();
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
//Define the styleflag object
StyleFlag styleflag;
// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
styleflag = new StyleFlag();
styleflag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, styleflag);
}
// Lock the three cells...i.e. A1, B1, C1.
style = sheet.Cells["A1"].GetStyle();
style.IsLocked = true;
sheet.Cells["A1"].SetStyle(style);
style = sheet.Cells["B1"].GetStyle();
style.IsLocked = true;
sheet.Cells["B1"].SetStyle(style);
style = sheet.Cells["C1"].GetStyle();
style.IsLocked = true;
sheet.Cells["C1"].SetStyle(style);
// Finally, Protect the sheet now.
sheet.Protect(ProtectionType.All);
// Save the excel file.
wb.Save(dataDir + "output.xls", SaveFormat.Excel97To2003);
}
public static void StyleFlag___Should_return_same_styleFlag_passed_to_constructor___When_getting()
{
// Arrange
var style = new Style();
var styleFlag = new StyleFlag();
var systemUnderTest = new StyleContainer(style, styleFlag);
// Act
var actual = systemUnderTest.StyleFlag;
// Assert
actual.Should().Be(styleFlag);
}
/// <summary>
/// 设置面包屑导航的样式
/// </summary>
/// <param name="pWorkbook"></param>
/// <param name="pRange"></param>
private void SetNavigationStyle(Workbook pWorkbook, Range pRange)
{
Style style = this.CreateStyle(pWorkbook);
style.HorizontalAlignment = TextAlignmentType.Left;
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
styleFlag.Borders = true;
pRange.ApplyStyle(style, styleFlag);
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.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");
//Add a style object to the collection.
int i = wb1.Styles.Add();
//Define a style object.
Style style = wb1.Styles[i];
//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.xls");
}
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
}
/// <summary>
/// 边框样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="r"></param>
private void DrawBorderAll(Workbook workbook, Range r)
{
Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Black;
s.Borders.SetColor(c);
s.Borders.SetStyle(CellBorderType.Thin);
s.Borders.DiagonalStyle = CellBorderType.None;
s.Pattern = BackgroundType.Solid;
StyleFlag styleFlag = new StyleFlag();
styleFlag.Borders = true;
r.ApplyStyle(s, styleFlag);
}
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()
{
// 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.");
}
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>
/// 必填字段的样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="r"></param>
private void DrawMastFillStyle(Workbook workbook, Range r)
{
Aspose.Cells.Style s = workbook.Styles[workbook.Styles.Add()];
Color c = Color.Maroon;
s.ForegroundColor = c;
s.Pattern = BackgroundType.Solid;
s.Font.Color = Color.White;
s.Font.IsBold = true;
s.HorizontalAlignment = TextAlignmentType.Center;
StyleFlag styleFlag = new StyleFlag();
styleFlag.All = true;
r.ApplyStyle(s, styleFlag);
}
/// <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);
}
/// <summary>
/// Builds a style-container using the existing style on a cell.
/// </summary>
/// <param name="cell">The cell.</param>
/// <returns>
/// A style container.
/// </returns>
/// <exception cref="ArgumentNullException"><paramref name="cell"/> is null.</exception>
public static StyleContainer BuildUsingExistingCellStyle(
Cell cell)
{
if (cell == null)
{
throw new ArgumentNullException(nameof(cell));
}
var style = cell.GetStyle();
var styleFlag = new StyleFlag();
var result = new StyleContainer(style, styleFlag);
return(result);
}
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
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//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.Styles[workbook.Styles.Add()];
//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.xls");
}
public void CreateStaticReport()
{
//Create a new workbook
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get its cells collection
Cells cells = sheet.Cells;
//Create a named range
Range range = sheet.Cells.CreateRange("B1", "E5");
//Set the name of the named range
range.Name = "Range1";
//Create a new style adding to the workbook styles collection
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
//Specify the style's fill color
style.ForegroundColor = System.Drawing.Color.Blue;
style.Pattern = BackgroundType.Solid;
//Create a styleflag object
StyleFlag styleFlag = new StyleFlag();
//Specify all attributes
styleFlag.All = true;
//Apply the style to the range
range.ApplyStyle(style, styleFlag);
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "FormattingRange.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "FormattingRange.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
HttpContext.Current.Response.End();
}
private void FillModelInputs(Workbook workbook)
{
//Get a worksheet named "Model Inputs"
Worksheet sheet = workbook.Worksheets["Model Inputs"];
//Get the cells in the worksheet
Cells cells = sheet.Cells;
//Set the styleflag structure
StyleFlag styleflag = new StyleFlag();
styleflag.All = true;
//Set value and style on Range
//Create Range from Cells C24 to F24
Range range = cells.CreateRange("C24", "F24");
int[] iArray = new int[] { 2800, 1200, 1600, 0 };
cells.ImportArray(iArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style1"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style on Range
//Create Range from Cells from C25 to F25
range = cells.CreateRange("C25", "F25");
iArray = new int[] { 120, 80, 40, 0 };
cells.ImportArray(iArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style2"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set formular and style on range
//Create range from cells C26 to F26
string[] strArray = new string[] { "=+C24*C25", "=+D24*D25", "=+E24*E25", "=+F24*F25" };
range = cells.CreateRange("C26", "F26");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style3"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set formular and style to C28
cells["C28"].Formula = "=SUM(C26:F26)";
cells["C28"].SetStyle(workbook.Styles["Custom_Style3"]);
//Set formular and style on range
//create range from cells C31 to F31
strArray = new string[] { "=C23", "=D23", "=E23", "=F23" };
range = cells.CreateRange("C31", "F31");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style4"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style on range
//create range from cells C32 to F32
double[] dArray = new double[] { 0.5, 0.4, 0.25, 0 };
range = cells.CreateRange("C32", "F32");
cells.ImportArray(dArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style5"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set formular and style on range
//create range from cells C33 to F33
strArray = new string[] { "=+C26*C32", "=+D26*D32", "=+E26*E32", "=+F26*F32" };
range = cells.CreateRange("C33", "F33");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style3"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set formular and style to C35
cells["C35"].Formula = "=SUM(C33:F33)";
cells["C35"].SetStyle(workbook.Styles["Custom_Style3"]);
//Set value and style to C38
cells["C38"].PutValue(0.15);
cells["C38"].SetStyle(workbook.Styles["Custom_Style6"]);
//Set value and style to C40
cells["C40"].PutValue(5);
cells["C40"].SetStyle(workbook.Styles["Custom_Style7"]);
//Set value and style to C42
cells["C42"].PutValue(0.30);
cells["C42"].SetStyle(workbook.Styles["Custom_Style6"]);
//Set value and style to C44
cells["C44"].PutValue(80000);
cells["C44"].SetStyle(workbook.Styles["Custom_Style8"]);
}
private void FillProfitLoss(Workbook workbook)
{
//Get the worksheet named "Profit and Loss"
Worksheet sheet = workbook.Worksheets["Profit and Loss"];
//Get the cells in the worksheet
Cells cells = sheet.Cells;
//Set the styleflag struct
StyleFlag styleflag = new StyleFlag();
styleflag.All = true;
//Set value and style from
object[,] obj2DArray = new object[,] {{"-",0.02,0.04,0.06,0.08},
{"-",0.02,0.04,0.06,0.08},
{0.005,0.005,0.005,0.005,0.005}};
//Create range from cells E8 to I10
Range range = cells.CreateRange("E8", "I10");
cells.ImportTwoDimensionArray(obj2DArray, range.FirstRow, range.FirstColumn);
range.ApplyStyle(workbook.Styles["Custom_Style9"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
//Set value and style on range
//create range from cells E15 to I15
string[] strArray = new string[] { "=+'Model Inputs'!C28", "=+E15*(1+F8)", "=+F15*(1+G8)", "=+G15*(1+H8)", "=+H15*(1+I8)" };
range = cells.CreateRange("E15", "I15");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style on range
//create range from cells E16 to I16
strArray = new string[] { "=+'Model Inputs'!C35", "=+E16*(1+F9)", "=+F16*(1+G9)", "=+G16*(1+H9)", "=+H16*(1+I9)" };
range = cells.CreateRange("E16", "I16");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style11"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style on range
//create range from cells E17 to I17
strArray = new string[] { "=E15-E16", "=F15-F16", "=G15-G16", "=H15-H16", "=I15-I16" };
range = cells.CreateRange("E17", "I17");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
//Set value and Style on range
//Create range from cells E19 to I20
obj2DArray = new object[,] { { 0, 0, 10000, 0, 0 }, { 1000, 0, 0, 0, 0 } };
range = cells.CreateRange("E19", "I20");
cells.ImportTwoDimensionArray(obj2DArray, range.FirstRow, range.FirstColumn);
range.ApplyStyle(workbook.Styles["Custom_Style12"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style on range
//create range from cells E21 to I21
strArray = new string[] { "=+E17+E19+E20", "=+F17+F19+F20", "=+G17+G19+G20", "=+H17+H19+H20", "=+I17+I19+I20" };
range = cells.CreateRange("E21", "I21");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
//Set value and style for E24
cells["E24"].PutValue(40000);
cells["E24"].SetStyle(workbook.Styles["Custom_Style12"]);
//Set value and style on range
//create range from cells F24 to I24
strArray = new string[] { "=+E24*(1+F9)", "=+F24*(1+G9)", "=+G24*(1+H9)", "=+H24*(1+I9)" };
range = cells.CreateRange("F24", "I24");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style for E25
cells["E25"].PutValue(60000);
cells["E25"].SetStyle(workbook.Styles["Custom_Style13"]);
//Set value and style on range
//create range from cells F25 to I25
strArray = new string[] { "=+E25*(1+F9)", "=+F25*(1+G9)", "=+G25*(1+H9)", "=+H25*(1+I9)" };
range = cells.CreateRange("F25", "I25");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style from E26 to I26
strArray = new string[] {
"=('Balance Sheet'!D15+'Balance Sheet'!D17+'Balance Sheet'!D18)/'Model Inputs'!C40",
"=('Balance Sheet'!E15+'Balance Sheet'!E17+'Balance Sheet'!E18)/'Model Inputs'!C40*(1+F9)",
"=('Balance Sheet'!F15+'Balance Sheet'!F17+'Balance Sheet'!F18)/'Model Inputs'!C40*(1+G9)",
"=('Balance Sheet'!G15+'Balance Sheet'!G17+'Balance Sheet'!G18)/'Model Inputs'!C40*(1+H9)",
"=('Balance Sheet'!H15+'Balance Sheet'!H17+'Balance Sheet'!H18)/'Model Inputs'!C40*(1+I9)"};
range = cells.CreateRange("E26", "I26");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style11"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style for E27
cells["E27"].PutValue(40000);
cells["E27"].SetStyle(workbook.Styles["Custom_Style13"]);
//Set value and style from F27 to I27
strArray = new string[] { "=+E27*(1+F9)", "=+F27*(1+G9)", "=+G27*(1+H9)", "=+H27*(1+I9)" };
range = cells.CreateRange("F27", "I27");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style from E28 to I28
strArray = new string[] {
"=+'Balance Sheet'!D18*'Model Inputs'!C38",
"=('Balance Sheet'!E18*'Model Inputs'!C38)*(1+F9)",
"=('Balance Sheet'!F18*'Model Inputs'!C38)*(1+G9)",
"=('Balance Sheet'!G18*'Model Inputs'!C38)*(1+H9)",
"=('Balance Sheet'!H18*'Model Inputs'!C38)*(1+I9)"};
range = cells.CreateRange("E28", "I28");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style11"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style for E29
cells["E29"].PutValue(30000);
cells["E29"].SetStyle(workbook.Styles["Custom_Style13"]);
//Set value and style from F29 to I29
strArray = new string[] { "=+E29*(1+F9)", "=+F29*(1+G9)", "=+G29*(1+H9)", "=+H29*(1+I9)" };
range = cells.CreateRange("F29", "I29");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style for E30
cells["E30"].PutValue(15000);
cells["E30"].SetStyle(workbook.Styles["Custom_Style13"]);
//Set value and style from F30 to I30
strArray = new string[] { "=+E30*(1+F9)", "=+F30*(1+G9)", "=+G30*(1+H9)", "=+H30*(1+I9)" };
range = cells.CreateRange("F30", "I30");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style for E31
cells["E31"].PutValue(18000);
cells["E31"].SetStyle(workbook.Styles["Custom_Style13"]);
//Set value and style from F31 to I31
strArray = new string[] { "=+E31*(1+F9)", "=+F31*(1+G9)", "=+G31*(1+H9)", "=+H31*(1+I9)" };
range = cells.CreateRange("F31", "I31");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style for E32
cells["E32"].PutValue(4000);
cells["E32"].SetStyle(workbook.Styles["Custom_Style13"]);
//Set value and style from F32 to I32
strArray = new string[] { "=+E32*(1+F9)", "=+F32*(1+G9)", "=+G32*(1+H9)", "=+H32*(1+I9)" };
range = cells.CreateRange("F32", "I32");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style from E33 to I33
strArray = new string[] { "=SUM(E24:E32)", "=SUM(F24:F32)", "=SUM(G24:G32)", "=SUM(H24:H32)", "=SUM(I24:I32)" };
range = cells.CreateRange("E33", "I33");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
//Set value and style from E35 to I35
strArray = new string[] { "=E21-E33", "=F21-F33", "=G21-G33", "=H21-H33", "=I21-I33" };
range = cells.CreateRange("E35", "I35");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
//Set value and style from E37 to I37
strArray = new string[] {
"=-SUM('Loan Payment Calculator'!F12:F23)","=-SUM('Loan Payment Calculator'!F24:F35)",
"=-SUM('Loan Payment Calculator'!F36:F47)","=-SUM('Loan Payment Calculator'!F48:F59)",
"=-SUM('Loan Payment Calculator'!F60:F71)"};
range = cells.CreateRange("E37", "I37");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style11"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style from E39 to I39
strArray = new string[] { "=+E35-E37", "=+F35-F37", "=+G35-G37", "=+H35-H37", "=+I35-I37" };
range = cells.CreateRange("E39", "I39");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
//Set value and style from E41 to I42
obj2DArray = new object[,] { { 0, 0, 1000, 0, 0 }, { 0, 0, 0, 0, 0 } };
range = cells.CreateRange("E41", "I42");
cells.ImportTwoDimensionArray(obj2DArray, range.FirstRow, range.FirstColumn);
range.ApplyStyle(workbook.Styles["Custom_Style13"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style from E44 to I44
strArray = new string[] { "=+E39+E41+E42", "=+F39+F41+F42", "=+G39+G41+G42", "=+H39+H41+H42", "=+I39+I41+I42" };
range = cells.CreateRange("E44", "I44");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
//Set value and style for D46
cells["D46"].Formula = "='Model Inputs'!C42";
cells["D46"].SetStyle(workbook.Styles["Custom_Style14"]);
//Set value and style from E46 to I46
strArray = new string[] {
"=IF(E44<0,0,D46*E44)","=IF(F44<0,0,D46*F44)",
"=IF(G44<0,0,D46*G44)","=IF(H44<0,0,D46*H44)",
"=IF(I44<0,0,D46*I44)"};
range = cells.CreateRange("E46", "I46");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style11"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//Set value and style from E48 to I48
strArray = new string[] { "=E44-E46", "=F44-F46", "=G44-G46", "=H44-H46", "=I44-I46" };
range = cells.CreateRange("E48", "I48");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Double, Color.Black);
}
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)
{
}
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
//After page set idle for a while, the license object lost.
//To avoid the evaluation copy issue, we have to set license on every click
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Total.lic");
DataTable dtMain;
using (var ctx = new DBContext())
{
dtMain = ctx.getExportAttys();
//ctx.LogUser(ntID, Global.REPORT_ID);
}
//Create a new worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
//Import data
cells.ImportDataTable(dtMain, true, 5, 0);
//Create range and styles for the headings
Aspose.Cells.Style stl1 = workbook.Styles[workbook.Styles.Add()];
stl1.VerticalAlignment = TextAlignmentType.Center;
stl1.Font.Color = Color.Blue;
stl1.Font.IsBold = true;
stl1.Font.Name = "Arial";
stl1.Font.Size = 10;
StyleFlag flg = new StyleFlag();
flg.Font = true;
Range rngHd = cells.CreateRange("A1", "Z6");
rngHd.ApplyStyle(stl1, flg);
//Format the columns
worksheet.AutoFitColumns();
/*** TESTING ***
// http://www.aspose.com/docs/display/cellsnet/Setting+Display+Formats+of+Numbers+and+Dates
Aspose.Cells.Style stl2 = workbook.Styles[workbook.Styles.Add()];
StyleFlag flg2 = new StyleFlag();
stl2.Number = 15;
cells.Columns[6].ApplyStyle(stl2, flg2);
*** END TESTING ***/
cells.SetColumnWidth(10, 45); //Primary Practice Group
cells.SetColumnWidth(11, 45); //Bar
// Populate header
cells[0, 0].PutValue("Report:");
cells[0, 1].PutValue("LW1190 Brown Book Maintenance");
TimeZoneInfo localZone = TimeZoneInfo.Local;
string strRunDate = String.Format("{0:MM/dd/yyyy hh:mm tt}", DateTime.Now) +
" (" + ((localZone.IsDaylightSavingTime(DateTime.Now)) ? localZone.DaylightName : localZone.StandardName) + ")";
cells[1, 0].PutValue("Run Date:");
cells[1, 1].PutValue(strRunDate);
cells[2, 0].PutValue("Report Type:");
cells[2, 1].PutValue("Associates");
//Save file to the client
string sExcel = "LW1190";
worksheet.Name = sExcel;
sExcel += "_" + String.Format("{0:MM_dd_yyyy_hh_mm_ss_tt}", DateTime.Now) + ".xls";
workbook.Save(this.Response, sExcel, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
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 ActionResult GenerateExcel([DataSourceRequest]DataSourceRequest request, string colsDef, string gridData,
string client, string matter, string currency, string rateGrp, string start, string duration)
{
// A column dataType based on the first record in the result set.
//DataTable dt = (DataTable)JsonConvert.DeserializeObject(gridData, (typeof(DataTable)));
var dtData = JsonConvert.DeserializeObject<DataTable>(gridData);
/*
var dtData = JsonConvert.DeserializeObject<DataTable>(gridData, new JsonSerializerSettings {
// http://stackoverflow.com/questions/26484176/jobject-parse-modifies-end-of-floating-point-values
FloatParseHandling = FloatParseHandling.Decimal,
NullValueHandling = NullValueHandling.Ignore,
MissingMemberHandling = MissingMemberHandling.Ignore,
Formatting = Formatting.None,
DateFormatHandling = DateFormatHandling.IsoDateFormat,
Converters = new List<JsonConverter> { new DecimalConverter() }
});
*/
var dtCols = JsonConvert.DeserializeObject<DataTable>(colsDef);
Dictionary<string, string> dicCols = new Dictionary<string, string>();
#region Need to get dtData to match dtCols: Columns order & title
foreach (DataRow dr in dtCols.Rows)
dicCols.Add((string)dr["field"], (string)dr["title"]);
dtData.SetColumns(dicCols);
// Remove extraneous columns
int cnt = dtData.Columns.Count;
for (int j = dtCols.Rows.Count; j < cnt; cnt--)
{
dtData.Columns.RemoveAt(j);
}
#endregion
//Create a new worksheet
var license = new Aspose.Cells.License();
license.SetLicense("Aspose.Total.lic");
var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
var cells = worksheet.Cells;
//Import data
int firstRow = 9; // zero-based
cells.ImportDataTable(dtData, true, firstRow, 0);
TimeZoneInfo localZone = TimeZoneInfo.Local;
string sRunDate = String.Format("{0:MM/dd/yyyy hh:mm tt}", DateTime.Now) +
" (" + ((localZone.IsDaylightSavingTime(DateTime.Now)) ? localZone.DaylightName : localZone.StandardName) + ")";
cells["A1"].PutValue("Report: LW1020 Rate Modeler");
cells["A2"].PutValue("Run Date: " + sRunDate);
cells["A3"].PutValue(client);
cells["A4"].PutValue(matter);
cells["A5"].PutValue(currency);
cells["A6"].PutValue(rateGrp);
cells["A7"].PutValue(start);
cells["A8"].PutValue(duration);
Style stl1 = workbook.Styles[workbook.Styles.Add()];
stl1.Custom = "#,##0.00";
StyleFlag flg1 = new StyleFlag();
flg1.NumberFormat = true;
Style stl2 = workbook.Styles[workbook.Styles.Add()];
stl2.HorizontalAlignment = TextAlignmentType.Center;
stl2.Font.Color = Color.Blue;
stl2.Font.IsBold = true;
stl2.Font.Name = "Arial";
stl2.Font.Size = 10;
StyleFlag flgTit = new StyleFlag();
flgTit.Font = true;
StyleFlag flgHdr = new StyleFlag();
flgHdr.Font = true;
flgHdr.HorizontalAlignment = true;
Range rngTit = cells.CreateRange("A1", "A8");
Range rngHdr = cells.CreateRange("A10", "BB10");
rngTit.ApplyStyle(stl2, flgTit);
rngHdr.ApplyStyle(stl2, flgHdr);
#region *** Set up subtotal formulas ***
string xlCol = string.Empty;
int firstDataRow = firstRow + 2;
int lastDataRow = firstDataRow + dtData.Rows.Count - 1;
int subRow = lastDataRow + 2;
for (int i = 6; i <= dtData.Columns.Count; i++)
{
xlCol = GetExcelColumnName(i);
switch (i)
{
case 6: // Hours Billed
cells[xlCol + subRow].Formula = "sum(" + xlCol + firstDataRow + ":" + xlCol + lastDataRow + ")";
break;
default: // Amount Billed $,Prop Hours,Prop Calc,Total Proj Calc 1,...
if (i % 2 == 1) cells[xlCol + subRow].Formula = "sum(" + xlCol + firstDataRow + ":" + xlCol + lastDataRow + ")";
break;
}
}
#endregion
//Format the columns
worksheet.AutoFitColumns();
for (int i = 0; i < dtData.Columns.Count; i++)
{
switch (i)
{
case 0:
cells.SetColumnWidth(i, 14);
break;
case 1:
case 2:
case 3:
case 4:
// Do nothing
break;
default:
cells.Columns[i].ApplyStyle(stl1, flg1);
break;
}
}
//Save file
worksheet.Name = Global.REPORT_ID;
string fileName = Global.REPORT_ID + "_" + String.Format("{0:MM_dd_yyyy_hh_mm_ss_tt}", DateTime.Now) + ".xlsx";
// http://www.aspose.com/docs/display/cellsnet/Saving+Files
var ms = new MemoryStream();
workbook.Save(ms, SaveFormat.Xlsx);
ms.Position = 0;
Session[fileName] = ms;
return Json(new { fileName }, JsonRequestBehavior.AllowGet);
}
public void CreateStaticReport()
{
//Open template
string path = System.Web.HttpContext.Current.Server.MapPath("~");
path = path.Substring(0, path.LastIndexOf("\\"));
path += @"\designer\Workbooks\BKRanges.xls";
//Instantiate a new Workbook object.
Workbook workbook = new Workbook(path);
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//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.
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
//Set the shading color with solid pattern type.
style.ForegroundColor = System.Drawing.Color.Green;
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);
}
//Create a style object.
Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()];
//Set the shading color with solid pattern type.
style2.ForegroundColor = System.Drawing.Color.Yellow;
style2.Pattern = BackgroundType.Solid;
//Create a styleflag object.
StyleFlag flag2 = new StyleFlag();
//Apply the cellshading.
flag2.CellShading = true;
//Creates an arraylist.
ArrayList al = new ArrayList();
//Get the arraylist collection and apply the union operation on
//the third and fourth ranges
al = ranges[2].Union(ranges[3]);
//Define a range object.
Range union;
for (int i = 0; i < al.Count; i++)
{
//Get a range.
union = (Range)al[i];
//Apply the style to the range.
union.ApplyStyle(style2, flag2);
}
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "UnionAndIntersection.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "UnionAndIntersection.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
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);
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
}
//Export data file as per selected export type
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);
}
String connString = txtConString.Text;
//**STRING UPDATED**
String Query = "SELECT * FROM " + ddlTables.SelectedValue + " ORDER BY 1";
switch (ddlSource.SelectedValue)
{
case "0":
if (ddlTables.Items.Count <= 0)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerHtml = "There are no tables to select data.";
return;
}
Query = "SELECT * FROM " + ddlTables.SelectedValue + " ORDER BY 1";
break;
case "1":
if (ddlViews.Items.Count <= 0)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerHtml = "There are no views to select data.";
return;
}
Query = "SELECT * FROM " + ddlViews.SelectedValue;
break;
case "2":
if (txtCustomQuery.Text.Trim().Equals(""))
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerHtml = "Please enter custom query.";
return;
}
Query = txtCustomQuery.Text.Trim();
break;
}
success_msg.Visible = false;
error_msg.Visible = false;
using (var cn = new SqlConnection(connString))
{
//opening database connection
cn.Open();
//creating datatable, sqlCommand and sqlReader objects
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(Query, cn);
SqlDataReader myReader = cmd.ExecuteReader();
dt.Load(myReader);
//creating leadoptions for excel file
LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
loadOptions.CheckExcelRestriction = false;
//Instantiate a new Workbook with Load options to prevent max cell string length of 32k error
Workbook book = new Workbook(Server.MapPath("~/App_Data/Sample.xlsx"), loadOptions);
//Clear all the worksheets
book.Worksheets.Clear();
//Add a new Sheet "Data";
Worksheet sheet = book.Worksheets.Add("Exported Data");
//Data Table import to the worksheet, inserting from A1 cell of sheet
sheet.Cells.ImportDataTable(dt, true, "A1");
// Apply Hearder Row/First Row text to Bold
Cells.Style objStyle = new Cells.Style();
objStyle.Font.IsBold = true;
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
sheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
// Fit columns width to contents
sheet.AutoFitColumns();
//Create unique file name
string fileName = System.Guid.NewGuid().ToString() + "." + ExportTypeDropDown.SelectedValue;
//Save workbook as per export type
book.Save(this.Response, fileName, ContentDisposition.Attachment, GetSaveFormat(ExportTypeDropDown.SelectedValue));
Response.End();
}
error_msg.Visible = false;
success_msg.Visible = true;
success_msg.InnerText = "Data exported successfully.";
}
catch (Exception exc)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerText = exc.Message;
}
}
// 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 Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// 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.Styles[workbook.Styles.Add()];
//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);
//Save the excel file.
workbook.Save(dataDir+ "CopyRangeData.xlsx");
}
private void FillCashFlow(Workbook workbook)
{
//Get the worksheet named "Cash Flow"
Worksheet sheet = workbook.Worksheets["Cash Flow"];
//Get the cells collection in the sheet
//Set the styleflag stuct
Cells cells = sheet.Cells;
StyleFlag styleflag = new StyleFlag();
styleflag.All = true;
//Set value(s) and style(s) for cell(s)
//D8 to I8
string[] strArray = new string[] { "=+'Profit and Loss'!E48", "=+'Profit and Loss'!F48", "=+'Profit and Loss'!G48", "=+'Profit and Loss'!H48", "=+'Profit and Loss'!I48", "=SUM(D8:H8)" };
Range range = cells.CreateRange("D8", "I8");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//D9 to I9
strArray = new string[] { "='Profit and Loss'!E26", "='Profit and Loss'!F26", "='Profit and Loss'!G26", "='Profit and Loss'!H26", "='Profit and Loss'!I26", "=SUM(D9:H9)" };
range = cells.CreateRange("D9", "I9");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style11"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//D10 to I10
strArray = new string[] { "=+'Balance Sheet'!D8-'Balance Sheet'!E8", "=+'Balance Sheet'!D8-'Balance Sheet'!F8", "=+'Balance Sheet'!D8-'Balance Sheet'!G8", "=+'Balance Sheet'!D8-'Balance Sheet'!H8", "=+'Balance Sheet'!D8-'Balance Sheet'!I8", "=SUM(D10:H10)" };
range = cells.CreateRange("D10", "I10");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style11"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//D11 to I11
strArray = new string[] { "=+'Balance Sheet'!D9-'Balance Sheet'!E9", "=+'Balance Sheet'!D9-'Balance Sheet'!F9", "=+'Balance Sheet'!D9-'Balance Sheet'!G9", "=+'Balance Sheet'!D9-'Balance Sheet'!H9", "=+'Balance Sheet'!D9-'Balance Sheet'!I9", "=SUM(D11:H11)" };
range = cells.CreateRange("D11", "I11");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style11"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//D12 to I12
strArray = new string[] { "=+'Balance Sheet'!D30-'Balance Sheet'!E30", "=+'Balance Sheet'!D30-'Balance Sheet'!F30", "=+'Balance Sheet'!D30-'Balance Sheet'!G30", "=+'Balance Sheet'!D30-'Balance Sheet'!H30", "=+'Balance Sheet'!D30-'Balance Sheet'!I30", "=SUM(D12:H12)" };
range = cells.CreateRange("D12", "I12");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style11"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//D13 to D15
int[] iArray = new int[] { 0, 0, 0 };
range = cells.CreateRange("D13", "D15");
cells.ImportArray(iArray, range.FirstRow, range.FirstColumn, true);
range.ApplyStyle(workbook.Styles["Custom_Style13"], styleflag);
//E13 to I13
strArray = new string[] { "=D13", "=E13", "=F13", "=G13", "=SUM(D13:H13)" };
range = cells.CreateRange("E13", "I13");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style15"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//E14 to I14
strArray = new string[] { "=D14", "=E14", "=F14", "=G14", "=SUM(D14:H14)" };
range = cells.CreateRange("E14", "I14");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style15"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//E15 to I15
strArray = new string[] { "=D15", "=E15", "=F15", "=G15", "=SUM(D15:H15)" };
range = cells.CreateRange("E15", "I15");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style15"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//D16 to I16
strArray = new string[] { "=SUM(D8:D15)", "=SUM(E8:E15)", "=SUM(F8:F15)", "=SUM(G8:G15)", "=SUM(H8:H15)", "=SUM(D16:H16)" };
range = cells.CreateRange("D16", "I16");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
range = cells.CreateRange("I8", "I16");
range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
//D19 to I19
strArray = new string[] { "=-1 * MAX(0,SUM('Balance Sheet'!E15:E18)-SUM('Balance Sheet'!D15:D18))", "=-1 * MAX(0,SUM('Balance Sheet'!F15:F18)-SUM('Balance Sheet'!E15:E18))", "=-1 * MAX(0,SUM('Balance Sheet'!G15:G18)-SUM('Balance Sheet'!F15:F18))", "=-1 * MAX(0,SUM('Balance Sheet'!H15:H18)-SUM('Balance Sheet'!G15:G18))", "=-1 * MAX(0,SUM('Balance Sheet'!I15:I18)-SUM('Balance Sheet'!H15:H18))", "=SUM(D19:H19)" };
range = cells.CreateRange("D19", "I19");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//D20 to H20
iArray = new int[] { 0, 0, 0, 0, 0 };
range = cells.CreateRange("D20", "H20");
cells.ImportArray(iArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style13"], styleflag);
//I20 to I22
strArray = new string[] { "=SUM(D20:H20)", "=SUM(D21:H21)", "=SUM(D22:H22)" };
range = cells.CreateRange("I20", "I22");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, true);
range.ApplyStyle(workbook.Styles["Custom_Style11"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//D21 to H21
strArray = new string[] { "=(-1*MIN(0,SUM('Balance Sheet'!E15:E18)-SUM('Balance Sheet'!D15:D18))-'Profit and Loss'!E41)", "=(-1*MIN(0,SUM('Balance Sheet'!F15:F18)-SUM('Balance Sheet'!E15:E18))-'Profit and Loss'!F41)", "=(-1*MIN(0,SUM('Balance Sheet'!G15:G18)-SUM('Balance Sheet'!F15:F18))-'Profit and Loss'!G41)", "=(-1*MIN(0,SUM('Balance Sheet'!H15:H18)-SUM('Balance Sheet'!G15:G18))-'Profit and Loss'!H41)", "=(-1*MIN(0,SUM('Balance Sheet'!I15:I18)-SUM('Balance Sheet'!H15:H18))-'Profit and Loss'!I41)" };
range = cells.CreateRange("D21", "H21");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
//D22 to H22
iArray = new int[] { 0, 0, 0, 0, 0 };
range = cells.CreateRange("D22", "H22");
cells.ImportArray(iArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style13"], styleflag);
//D23 to I23
strArray = new string[] { "=SUM(D19:D22)", "=SUM(E19:E22)", "=SUM(F19:F22)", "=SUM(G19:G22)", "=SUM(H19:H22)", "=SUM(D23:H23)" };
range = cells.CreateRange("D23", "I23");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
range = cells.CreateRange("I19", "I23");
range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
//D26 to I26
strArray = new string[] { "= 'Balance Sheet'!E37+'Balance Sheet'!E38-('Balance Sheet'!D37+'Balance Sheet'!D38)", "= 'Balance Sheet'!F37+'Balance Sheet'!F38-('Balance Sheet'!E37+'Balance Sheet'!E38)", "= 'Balance Sheet'!G37+'Balance Sheet'!G38-('Balance Sheet'!F37+'Balance Sheet'!F38)", "= 'Balance Sheet'!H37+'Balance Sheet'!H38-('Balance Sheet'!G37+'Balance Sheet'!G38)", "= 'Balance Sheet'!I37+'Balance Sheet'!I38-('Balance Sheet'!H37+'Balance Sheet'!H38)", "=SUM(D26:H26)" };
range = cells.CreateRange("D26", "I26");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//D27 to D30
iArray = new int[] { 0, 0, 0, 0 };
range = cells.CreateRange("D27", "D30");
cells.ImportArray(iArray, range.FirstRow, range.FirstColumn, true);
range.ApplyStyle(workbook.Styles["Custom_Style13"], styleflag);
//E27 to I27
strArray = new string[] { "=D27", "=E27", "=F27", "=G27", "=SUM(D27:H27)" };
range = cells.CreateRange("E27", "I27");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style15"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//E28 to I28
strArray = new string[] { "=D28", "=E28", "=F28", "=G28", "=SUM(D28:H28)" };
range = cells.CreateRange("E28", "I28");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style15"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//E29 to I29
strArray = new string[] { "=D29", "=E29", "=F29", "=G29", "=SUM(D29:H29)" };
range = cells.CreateRange("E29", "I29");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style15"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//E30 to I30
strArray = new string[] { "=D30", "=E30", "=F30", "=G30", "=SUM(D30:H30)" };
range = cells.CreateRange("E30", "I30");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style15"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//D31 to I31
strArray = new string[] { "=SUM(D26:D30)", "=SUM(E26:E30)", "=SUM(F26:F30)", "=SUM(G26:G30)", "=SUM(H26:H30)", "=SUM(D31:H31)" };
range = cells.CreateRange("D31", "I31");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
range = cells.CreateRange("I26", "I31");
range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
//D33 to I33
strArray = new string[] { "=D16+D23+D31", "=E16+E23+E31", "=F16+F23+F31", "=G16+G23+G31", "=H16+H23+H31", "=SUM(D33:H33)" };
range = cells.CreateRange("D33", "I33");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
//D35 to H35
strArray = new string[] { "=+'Balance Sheet'!D7", "=D35", "=E35", "=F35", "=G35" };
range = cells.CreateRange("D35", "H35");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
//D36 to H36
strArray = new string[] { "=D35+D33", "=E35+E33", "=F35+F33", "=G35+G33", "=H35+H33" };
range = cells.CreateRange("D36", "H36");
cells.ImportFormulaArray(strArray, range.FirstRow, range.FirstColumn, false);
range.ApplyStyle(workbook.Styles["Custom_Style10"], styleflag);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
range = cells.CreateRange("I33", "I36");
range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
//I34 to I36
range = cells.CreateRange("I34", "I36");
range.ApplyStyle(workbook.Styles["Custom_Style18"], styleflag);
range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black);
}
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);
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//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.Styles[workbook.Styles.Add()];
//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 with formatting.
range2.Copy(range);
//Save the excel file.
workbook.Save(dataDir+ "CopyRange.out.xlsx");
}
// Save data to excel file
protected void ProcessButton_Click(object sender, EventArgs e)
{
try
{
if (Session["AsposeDynamicFormsdataTable"] != null)
{
DataTable dt = (DataTable)Session["AsposeDynamicFormsdataTable"];
if (dt != null)
{
if (dt.Rows.Count > 0)
{
DataRow[] drs = dt.Select("[Field ID] = '" + txtFieldId.Text + "'");
if (drs != null)
{
if (drs.Length > 0)
{
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();
drs[0]["Modified By"] = UserId.ToString();
drs[0]["Modified On"] = DateTime.Now.ToString("MM-dd-YYYY");
dt.AcceptChanges();
}
else
{
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 By"] = UserId.ToString();
dr["Modified On"] = DateTime.Now.ToString("MM-dd-YYYY");
dt.Rows.Add(dr);
}
Session["AsposeDynamicFormsdataTable"] = dt;
// 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("~/DesktopModules/Aspose.DNN.DynamicFormGenerator.Excel/Docs/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");
// Apply Hearder Row/First Row text to Bold
Cells.Style objStyle = new Cells.Style();
objStyle.Font.IsBold = true;
StyleFlag objStyleFlag = new StyleFlag();
objStyleFlag.FontBold = true;
worksheet.Cells.ApplyRowStyle(0, objStyle, objStyleFlag);
//Auto-fit all the columns
workbook.Worksheets["Data"].AutoFitColumns();
workbook.Save(Server.MapPath("~/DesktopModules/Aspose.DNN.DynamicFormGenerator.Excel/Docs/AsposeDynamicFormsDataFile.xlsx"), SaveFormat.Xlsx);
ClearFormButton_Click(sender, e);
}
}
}
}
ProcessButton.Text = "Add New Field";
}
catch (Exception exc)
{
success_msg.Visible = false;
error_msg.Visible = true;
error_msg.InnerText = exc.Message;
}
}
