protected void btnProcess_Click(object sender, EventArgs e)
{
//Create a dataset based on the custom method
DataSet ds = CreateDataSource();
//Open the template file which contains smart markers
string path = MapPath("~/Designer/SmartMarkerDesigner.xls");
//Create a workbookdesigner object
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
//Set dataset as the datasource
designer.SetDataSource(ds);
//Set variable object as another datasource
designer.SetDataSource("Variable", "Single Variable");
//Set multi-valued variable array as another datasource
designer.SetDataSource("MultiVariable", new string[] { "Variable 1", "Variable 2", "Variable 3" });
//Set multi-valued variable array as another datasource
designer.SetDataSource("MultiVariable2", new string[] { "Skip 1", "Skip 2", "Skip 3" });
//Process the smart markers in the designer file
designer.Process();
//Save the excel file
designer.Workbook.Save(HttpContext.Current.Response,"SmartMarker.xls", 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);
// Instantiate the workbook from a template file that contains Smart Markers
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
Workbook designer = new Workbook(dataDir + "SmartMarker_Designer.xlsx");
// Export data from the first worksheet to fill a data table
DataTable dt = workbook.Worksheets[0].Cells.ExportDataTable(0, 0, 11, 5, true);
// Instantiate a new WorkbookDesigner
WorkbookDesigner d = new WorkbookDesigner();
// Specify the workbook to the designer book
d.Workbook = workbook;
// Set the table name
dt.TableName = "Report";
// Set the data source
d.SetDataSource(dt);
// Process the smart markers
d.Process();
// Save the Excel file
workbook.Save(dataDir + "output.xlsx", SaveFormat.Xlsx);
// ExEnd:1
}
static void Main(string[] args)
{
string MyDir = @"Files\";
//Get the image data.
byte[] imageData = File.ReadAllBytes(MyDir + "Thumbnail.jpg");
//Create a datatable.
DataTable t = new DataTable("Table1");
//Add a column to save pictures.
DataColumn dc = t.Columns.Add("Picture");
//Set its data type.
dc.DataType = typeof(object);
//Add a new new record to it.
DataRow row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
//Add another record (having picture) to it.
imageData = File.ReadAllBytes(MyDir + "Desert.jpg");
row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
//Create WorkbookDesigner object.
WorkbookDesigner designer = new WorkbookDesigner();
//Open the temple Excel file.
designer.Workbook = new Workbook(MyDir + "ImageSmartBook.xls");
//Set the datasource.
designer.SetDataSource(t);
//Process the markers.
designer.Process();
//Save the Excel file.
designer.Workbook.Save(MyDir + "out_ImageSmartBook.xls");
}
public static void Run()
{
try
{
//ExStart:DynamicFormulas
// 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 WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();
// Open a designer spreadsheet containing smart markers
designer.Workbook = new Workbook(designerFile);
// Set the data source for the designer spreadsheet
designer.SetDataSource(dataset);
// Process the smart markers
designer.Process();
//ExEnd:DynamicFormulas
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static void Run()
{
// ExStart:1
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string outputPath = dataDir + "Output.out.xlsx";
// Creating a DataTable that will serve as data source for designer spreadsheet
DataTable table = new DataTable("OppLineItems");
table.Columns.Add("PRODUCT_FAMILY");
table.Columns.Add("OPPORTUNITY_LINEITEM_PRODUCTNAME");
table.Rows.Add(new object[] { "MMM", "P1" });
table.Rows.Add(new object[] { "MMM", "P2" });
table.Rows.Add(new object[] { "DDD", "P1" });
table.Rows.Add(new object[] { "DDD", "P2" });
table.Rows.Add(new object[] { "AAA", "P1" });
// Loading the designer spreadsheet in an instance of Workbook
Workbook workbook = new Workbook(dataDir + "source.xlsx");
// Loading the instance of Workbook in an instance of WorkbookDesigner
WorkbookDesigner designer = new WorkbookDesigner(workbook);
// Set the WorkbookDesigner.CallBack property to an instance of newly created class
designer.CallBack = new SmartMarkerCallBack(workbook);
// Set the data source
designer.SetDataSource(table);
// Process the Smart Markers in the designer spreadsheet
designer.Process(false);
// Save the result
workbook.Save(outputPath);
// ExEnd:1
}
static void Main(string[] args)
{
string MyDir = @"Files\";
//Instantiate the workbookdesigner object.
WorkbookDesigner report = new WorkbookDesigner();
//Get the first worksheet(default sheet) in the workbook.
Aspose.Cells.Worksheet w = report.Workbook.Worksheets[0];
//Input some markers to the cells.
w.Cells["A1"].PutValue("Test");
w.Cells["A2"].PutValue("&=MyProduct.Name");
w.Cells["B2"].PutValue("&=MyProduct.Age");
//Instantiate the list collection based on the custom class.
IList<MyProduct> list = new List<MyProduct>();
//Provide values for the markers using the custom class object.
list.Add(new MyProduct("Simon", 30));
list.Add(new MyProduct("Johnson", 33));
//Set the data source.
report.SetDataSource("MyProduct", list);
//Process the markers.
report.Process(false);
//Save the excel file.
report.Workbook.Save(MyDir + "Smart Marker Customobjects.xls");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// ****** Program ******
// Initialize WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();
// Load the template file
designer.Workbook = new Workbook(dataDir + "SM_NestedObjects.xlsx");
// Instantiate the List based on the class
System.Collections.Generic.ICollection<Individual> list = new System.Collections.Generic.List<Individual>();
// Create an object for the Individual class
Individual p1 = new Individual("Damian", 30);
// Create the relevant Wife class for the Individual
p1.Wife = new Wife("Dalya", 28);
// Create another object for the Individual class
Individual p2 = new Individual("Mack", 31);
// Create the relevant Wife class for the Individual
p2.Wife = new Wife("Maaria", 29);
// Add the objects to the list
list.Add(p1);
list.Add(p2);
// Specify the DataSource
designer.SetDataSource("Individual", list);
// Process the markers
designer.Process(false);
// Save the Excel file.
designer.Workbook.Save(dataDir+ "output.xlsx");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a new Workbook designer.
WorkbookDesigner report = new WorkbookDesigner();
// Get the first worksheet of the workbook.
Worksheet w = report.Workbook.Worksheets[0];
// Set the Variable Array marker to a cell.
// You may also place this Smart Marker into a template file manually in Ms Excel and then open this file via Workbook.
w.Cells["A1"].PutValue("&=$VariableArray");
// Set the DataSource for the marker(s).
report.SetDataSource("VariableArray", new string[] { "English", "Arabic", "Hindi", "Urdu", "French" });
// Process the markers.
report.Process(false);
// Save the Excel file.
report.Workbook.Save(dataDir + "output.xlsx");
// ExEnd:1
}
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 WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();
//Open a designer spreadsheet containing smart markers
designer.Workbook = new Workbook(designerFile);
//Set the data source for the designer spreadsheet
designer.SetDataSource(dataset);
//Process the smart markers
designer.Process();
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create Students DataTable
DataTable dtStudent = new DataTable("Student");
// Define a field in it
DataColumn dcName = new DataColumn("Name", typeof(string));
dtStudent.Columns.Add(dcName);
dtStudent.Columns.Add(new DataColumn("Age", typeof(int)));
// Add three rows to it
DataRow drName1 = dtStudent.NewRow();
DataRow drName2 = dtStudent.NewRow();
DataRow drName3 = dtStudent.NewRow();
drName1["Name"] = "John";
drName1["Age"] = 23;
drName2["Name"] = "Jack";
drName2["Age"] = 24;
drName3["Name"] = "James";
drName3["Age"] = 32;
dtStudent.Rows.Add(drName1);
dtStudent.Rows.Add(drName2);
dtStudent.Rows.Add(drName3);
string filePath = dataDir + "Template.xlsx";
// Create a workbook from Smart Markers template file
Workbook workbook = new Workbook(filePath);
// Instantiate a new WorkbookDesigner
WorkbookDesigner designer = new WorkbookDesigner();
// Specify the Workbook
designer.Workbook = workbook;
// Set the Data Source
designer.SetDataSource(dtStudent);
// Process the smart markers
designer.Process();
// Save the Excel file
workbook.Save(dataDir + "output.xlsx", SaveFormat.Xlsx);
// ExEnd:1
}
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();
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = workbook;
workbook.Worksheets[0].Cells["A1"].PutValue("&=$VariableArray(HTML)");
designer.SetDataSource("VariableArray", new String[] { "Hello <b>World</b>", "Arabic", "Hindi", "Urdu", "French" });
designer.Process();
workbook.Save(dataDir + "output.xls");
// ExEnd:1
}
public async Task <ActionResult> ExportExcelScoreRecordDetail(string recordId)
{
var data = await _auditRateService.GetScoreRecordDetail(recordId);
var Building = await _auditPicDService.GetBuidingByID(data.auditRateM.Building);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\WaterSpider_Score_Record_Detail_Template.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
// Gán giá trị tĩnh
ws.Cells["B2"].PutValue(data.auditRateM.Record_Date);
ws.Cells["D2"].PutValue(data.auditRateM.PDC_Name);
ws.Cells["F2"].PutValue(Building);
ws.Cells["B3"].PutValue(data.auditRateM.Updated_By);
ws.Cells["D3"].PutValue(data.auditRateM.Updated_Time);
ws.Cells["F3"].PutValue(data.auditRateM.Line_ID_2_Name);
designer.SetDataSource("result", data.listAuditRateD);
designer.Process();
for (var i = 6; i <= data.listAuditRateD.Count + 5; i++)
{
var filePathB4 = "wwwroot\\uploaded\\images\\" + data.listAuditRateD[i - 6].UplloadPicture;
if (System.IO.File.Exists(filePathB4))
{
var pictureIndex = ws.Pictures.Add(i, 6, filePathB4);
Aspose.Cells.Drawing.Picture picture = ws.Pictures[pictureIndex];
picture.Width = 100;
picture.Height = 100;
//margin
picture.Top = 3;
picture.Left = 3;
//set lại Height cho dòng có image
ws.Cells.Rows[i].Height = 80;
}
}
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "WaterSpider_Score_Record_Detail" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
public void ExportExcel(string keyword, WebClient client)
{
var list = new List <ExportedModel>();
list.AddRange(this.existsApps.OrderByDescending(x => x.IsOnline).ThenBy(x => x.AppName));
list.AddRange(this.searchResults.OrderByDescending(x => x.IsOnline).ThenBy(x => x.AppName));
list.AsParallel().ForAll(x => x.UpdateTime = getUpdateTime(x.PackageId));
list.AsParallel().ForAll(x => x.IsOnline = isOnline(x.PackageName));
var templatePath = Path.Combine(Environment.CurrentDirectory, "导出摸板.xlsx");
DataSet dataSource = new DataSet();
var dt = new DataTable("App");
dataSource.Tables.Add(dt);
dt.Columns.Add("Package", typeof(string));
dt.Columns.Add("AppName", typeof(string));
dt.Columns.Add("PackageId", typeof(int));
dt.Columns.Add("Official", typeof(string));
dt.Columns.Add("VersionCode", typeof(int));
dt.Columns.Add("VersionName", typeof(string));
dt.Columns.Add("DownloadCount", typeof(string));
dt.Columns.Add("IsOnline", typeof(string));
dt.Columns.Add("UserContent", typeof(string));
dt.Columns.Add("UpdateTime", typeof(string));
foreach (var item in list)
{
var row = dt.NewRow();
row["Package"] = item.PackageName;
row["AppName"] = item.AppName;
row["PackageId"] = item.PackageId;
row["IsOnline"] = item.IsOnline;
row["UserContent"] = item.UserContent;
row["Official"] = item.Official;
row["VersionCode"] = item.VersionCode;
row["VersionName"] = item.VersionName;
row["DownloadCount"] = item.DownloadCount;
row["UpdateTime"] = item.UpdateTime;
dt.Rows.Add(row);
}
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(templatePath);
designer.SetDataSource(dataSource);
designer.Process();
string filePath = Path.Combine(Environment.CurrentDirectory, "查询结果", GetSafeFileName(keyword));
designer.Workbook.Save(filePath, Aspose.Cells.SaveFormat.Xlsx);
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Create Students DataTable
DataTable dtStudent = new DataTable("Student");
//Define a field in it
DataColumn dcName = new DataColumn("Name", typeof(string));
dtStudent.Columns.Add(dcName);
//Add three rows to it
DataRow drName1 = dtStudent.NewRow();
DataRow drName2 = dtStudent.NewRow();
DataRow drName3 = dtStudent.NewRow();
drName1["Name"] = "John";
drName2["Name"] = "Jack";
drName3["Name"] = "James";
dtStudent.Rows.Add(drName1);
dtStudent.Rows.Add(drName2);
dtStudent.Rows.Add(drName3);
string filePath = dataDir + "TestSmartMarkers.xlsx";
//Create a workbook from Smart Markers template file
Workbook workbook = new Workbook(filePath);
//Instantiate a new WorkbookDesigner
WorkbookDesigner designer = new WorkbookDesigner();
//Specify the Workbook
designer.Workbook = workbook;
//Set the Data Source
designer.SetDataSource(dtStudent);
//Process the smart markers
designer.Process();
//Save the Excel file
workbook.Save(filePath + dataDir + "_out1.xlsx", SaveFormat.Xlsx);
}
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();
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = workbook;
workbook.Worksheets[0].Cells["A1"].PutValue("&=$VariableArray(HTML)");
designer.SetDataSource("VariableArray", new String[] { "Hello <b>World</b>", "Arabic", "Hindi", "Urdu", "French" });
designer.Process();
workbook.Save(dataDir + "output.xls");
// ExEnd:1
}
public static void Run()
{
//ExStart:GroupingData
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create a connection object, specify the provider info and set the data source.
OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=" + dataDir + "Northwind.mdb");
// Open the connection object.
con.Open();
// Create a command object and specify the SQL query.
OleDbCommand cmd = new OleDbCommand("Select * from [Order Details]", con);
// Create a data adapter object.
OleDbDataAdapter da = new OleDbDataAdapter();
// Specify the command.
da.SelectCommand = cmd;
// Create a dataset object.
DataSet ds = new DataSet();
// Fill the dataset with the table records.
da.Fill(ds, "Order Details");
// Create a datatable with respect to dataset table.
DataTable dt = ds.Tables["Order Details"];
// Create WorkbookDesigner object.
WorkbookDesigner wd = new WorkbookDesigner();
// Open the template file (which contains smart markers).
wd.Workbook = new Workbook(dataDir+ "TestSmartMarkers.xlsx");
// Set the datatable as the data source.
wd.SetDataSource(dt);
// Process the smart markers to fill the data into the worksheets.
wd.Process(true);
// Save the excel file.
wd.Workbook.Save(dataDir+ "output.xlsx");
//ExEnd:GroupingData
}
protected byte[] CommonExportReport(object data, string templateName)
{
string rootStr = _webHostEnvironment.ContentRootPath;
var path = Path.Combine(rootStr, "Resources\\Template\\" + templateName);
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data);
designer.Process();
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
return(stream.ToArray());;
}
/// <summary>
/// 导出Excel Aspose 模板模式
/// </summary>
/// <param name="model">要导入的数据</param>
/// <param name="templateFileName">完整文件路径</param>
/// <param name="sheetName">表名</param>
/// <returns></returns>
private static MemoryStream OuModelFileToStream(DataTable model, string templateFileName, string sheetName)
{
WorkbookDesigner designer = new WorkbookDesigner();
//读取模板文件
designer.Open(templateFileName);
//将数据导入进去
designer.SetDataSource(model);
designer.Process();
//判断表名是否为空
if (!string.IsNullOrEmpty(sheetName))
{
designer.Workbook.Worksheets[0].Name = sheetName;
}
//返回文件流
return(designer.Workbook.SaveToStream());
}
public static void Run()
{
//ExStart:GroupingData
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create a connection object, specify the provider info and set the data source.
OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=" + dataDir + "Northwind.mdb");
// Open the connection object.
con.Open();
// Create a command object and specify the SQL query.
OleDbCommand cmd = new OleDbCommand("Select * from [Order Details]", con);
// Create a data adapter object.
OleDbDataAdapter da = new OleDbDataAdapter();
// Specify the command.
da.SelectCommand = cmd;
// Create a dataset object.
DataSet ds = new DataSet();
// Fill the dataset with the table records.
da.Fill(ds, "Order Details");
// Create a datatable with respect to dataset table.
DataTable dt = ds.Tables["Order Details"];
// Create WorkbookDesigner object.
WorkbookDesigner wd = new WorkbookDesigner();
// Open the template file (which contains smart markers).
wd.Workbook = new Workbook(dataDir + "TestSmartMarkers.xlsx");
// Set the datatable as the data source.
wd.SetDataSource(dt);
// Process the smart markers to fill the data into the worksheets.
wd.Process(true);
// Save the excel file.
wd.Workbook.Save(dataDir + "output.xlsx");
//ExEnd:GroupingData
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Create Students DataTable
DataTable dtStudent = new DataTable("Student");
//Define a field in it
DataColumn dcName = new DataColumn("Name", typeof(string));
dtStudent.Columns.Add(dcName);
//Add three rows to it
DataRow drName1 = dtStudent.NewRow();
DataRow drName2 = dtStudent.NewRow();
DataRow drName3 = dtStudent.NewRow();
drName1["Name"] = "John";
drName2["Name"] = "Jack";
drName3["Name"] = "James";
dtStudent.Rows.Add(drName1);
dtStudent.Rows.Add(drName2);
dtStudent.Rows.Add(drName3);
string filePath = dataDir + "TestSmartMarkers.xlsx";
//Create a workbook from Smart Markers template file
Workbook workbook = new Workbook(filePath);
//Instantiate a new WorkbookDesigner
WorkbookDesigner designer = new WorkbookDesigner();
//Specify the Workbook
designer.Workbook = workbook;
//Set the Data Source
designer.SetDataSource(dtStudent);
//Process the smart markers
designer.Process();
//Save the Excel file
workbook.Save(filePath + dataDir+ "_out1.xlsx", SaveFormat.Xlsx);
}
public static void Run()
{
// 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);
}
// Open a designer workbook
WorkbookDesigner designer = new WorkbookDesigner();
// Get worksheet Cells collection
Cells cells = designer.Workbook.Worksheets[0].Cells;
// Set Cell Values
cells["A1"].PutValue("Name");
cells["B1"].PutValue("Age");
// Set markers
cells["A2"].PutValue("&=Person.Name");
cells["B2"].PutValue("&=Person.Age");
// Create Array list
ArrayList list = new ArrayList();
// Add custom objects to the list
list.Add(new Person("Simon", 30));
list.Add(new Person("Johnson", 33));
// Add designer's datasource
designer.SetDataSource("Person", list);
// Process designer
designer.Process(false);
dataDir = dataDir + "result.out.xls";
// Save the resultant file
designer.Workbook.Save(dataDir);
Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir);
}
//
private void CrateExcel(DataTable forExcelDt, string SheetName, string FullPath, string xlsName)
{
forExcelDt.TableName = "data";
WorkbookDesigner designer = new WorkbookDesigner();
string xlsMdlPath = Server.MapPath(FullPath);
designer.Open(xlsMdlPath);
designer.SetDataSource(forExcelDt);
designer.Process();
Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName(SheetName);
string newXls = xlsName + ".xls";
System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel"));
ExcelHelper.deletefile(xlspath);
designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003);
this.PageState.Add("fileName", "../Excel/tempexcel/" + newXls);
}
public async Task <IActionResult> ExportExcelSMERecord([FromQuery] PaginationParams paginationParams, ScoreRecordParam scoreRecordParam)
{
var data = await _waterSpiderRecordService.GetLisWaterSpiderScoreRecord(paginationParams, scoreRecordParam);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resource\\Template\\WaterSpider_Score_Record_Template.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data);
designer.Process();
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "WaterSpider_Score_Record" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Create a connection object, specify the provider info and set the data source.
OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb");
//Open the connection object.
con.Open();
//Create a command object and specify the SQL query.
OleDbCommand cmd = new OleDbCommand("Select * from [Order Details]", con);
//Create a data adapter object.
OleDbDataAdapter da = new OleDbDataAdapter();
//Specify the command.
da.SelectCommand = cmd;
//Create a dataset object.
DataSet ds = new DataSet();
//Fill the dataset with the table records.
da.Fill(ds, "Order Details");
//Create a datatable with respect to dataset table.
DataTable dt = ds.Tables["Order Details"];
//Create WorkbookDesigner object.
WorkbookDesigner wd = new WorkbookDesigner();
//Open the template file (which contains smart markers).
wd.Workbook = new Workbook(dataDir+ "TestSmartMarkers.xlsx");
//Set the datatable as the data source.
wd.SetDataSource(dt);
//Process the smart markers to fill the data into the worksheets.
wd.Process(true);
//Save the excel file.
wd.Workbook.Save(dataDir+ "outSmartMarker_Designer.xlsx");
}
private void ImpExcel()
{
string where = string.Empty;
//权限过滤
var Ent = SurveyQuestion.TryFind(SurveyId);
if (Ent != null && Ent.IsFixed == "2")
{
CommPowerSplit PS = new CommPowerSplit();
if (PS.IsInAdminsRole(UserInfo.UserID) || PS.IsAdmin(UserInfo.LoginName) || PS.IsHR(UserInfo.UserID, UserInfo.LoginName))
{
}
else
{
UserContextInfo UC = new UserContextInfo();
where += " and D.Pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' ";
}
}
tmpSQL = tmpSQL.Replace("##QUERY##", where);
tmpSQL = tmpSQL.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB);
string sql = string.Format(tmpSQL, SurveyId);
string path = RequestData.Get <string>("path");
string fileName = RequestData.Get <string>("fileName");
string xlsName = fileName + "_" + System.DateTime.Now.ToString("yyyMMddhhmmss");
DataTable forExcelDt = DataHelper.QueryDataTable(sql);
if (forExcelDt.Rows.Count > 0)
{
forExcelDt.TableName = "data";
WorkbookDesigner designer = new WorkbookDesigner();
string xlsMdlPath = Server.MapPath(path);
designer.Open(xlsMdlPath);
designer.SetDataSource(forExcelDt);
designer.Process();
Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName(fileName);
string newXls = xlsName + ".xls";
System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel"));
ExcelHelper.deletefile(xlspath);
designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003);
this.PageState.Add("fileName", "/Excel/tempexcel/" + newXls);
}
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Create a connection object, specify the provider info and set the data source.
OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb");
//Open the connection object.
con.Open();
//Create a command object and specify the SQL query.
OleDbCommand cmd = new OleDbCommand("Select * from [Order Details]", con);
//Create a data adapter object.
OleDbDataAdapter da = new OleDbDataAdapter();
//Specify the command.
da.SelectCommand = cmd;
//Create a dataset object.
DataSet ds = new DataSet();
//Fill the dataset with the table records.
da.Fill(ds, "Order Details");
//Create a datatable with respect to dataset table.
DataTable dt = ds.Tables["Order Details"];
//Create WorkbookDesigner object.
WorkbookDesigner wd = new WorkbookDesigner();
//Open the template file (which contains smart markers).
wd.Workbook = new Workbook(dataDir + "TestSmartMarkers.xlsx");
//Set the datatable as the data source.
wd.SetDataSource(dt);
//Process the smart markers to fill the data into the worksheets.
wd.Process(true);
//Save the excel file.
wd.Workbook.Save(dataDir + "outSmartMarker_Designer.xlsx");
}
static void Main(string[] args)
{
string FilePath = @"..\..\..\Sample Files\";
string FileName = FilePath + "Grouping Data OLE DB.xlsx";
//Create a connection object, specify the provider info and set the data source.
OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=~\\..\\..\\..\\Data\\Northwind.mdb");
//Open the connection object.
con.Open();
//Create a command object and specify the SQL query.
OleDbCommand cmd = new OleDbCommand("Select * from [Order Details]", con);
//Create a data adapter object.
OleDbDataAdapter da = new OleDbDataAdapter();
//Specify the command.
da.SelectCommand = cmd;
//Create a dataset object.
DataSet ds = new DataSet();
//Fill the dataset with the table records.
da.Fill(ds, "Order Details");
//Create a datatable with respect to dataset table.
DataTable dt = ds.Tables["Order Details"];
//Create WorkbookDesigner object.
WorkbookDesigner wd = new WorkbookDesigner();
//Open the template file (which contains smart markers).
wd.Workbook = new Workbook(FileName);
//Set the datatable as the data source.
wd.SetDataSource(dt);
//Process the smart markers to fill the data into the worksheets.
wd.Process(true);
//Save the excel file.
wd.Workbook.Save(FileName);
}
static void Main(string[] args)
{
string FilePath = @"..\..\..\Sample Files\";
string FileName = FilePath + "Grouping Data OLE DB.xlsx";
//Create a connection object, specify the provider info and set the data source.
OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=~\\..\\..\\..\\Data\\Northwind.mdb");
//Open the connection object.
con.Open();
//Create a command object and specify the SQL query.
OleDbCommand cmd = new OleDbCommand("Select * from [Order Details]", con);
//Create a data adapter object.
OleDbDataAdapter da = new OleDbDataAdapter();
//Specify the command.
da.SelectCommand = cmd;
//Create a dataset object.
DataSet ds = new DataSet();
//Fill the dataset with the table records.
da.Fill(ds, "Order Details");
//Create a datatable with respect to dataset table.
DataTable dt = ds.Tables["Order Details"];
//Create WorkbookDesigner object.
WorkbookDesigner wd = new WorkbookDesigner();
//Open the template file (which contains smart markers).
wd.Workbook = new Workbook(FileName);
//Set the datatable as the data source.
wd.SetDataSource(dt);
//Process the smart markers to fill the data into the worksheets.
wd.Process(true);
//Save the excel file.
wd.Workbook.Save(FileName);
}
public static void Main()
{
// ExStart:1
// Initialize DataSet object
DataSet ds1 = new DataSet();
// Fill dataset from XML file
ds1.ReadXml(sourceDir + @"sampleIsBlank.xml");
// Initialize template workbook containing smart marker with ISBLANK
Workbook workbook = new Workbook(sourceDir + @"sampleIsBlank.xlsx");
// Get the target value in the XML file whose value is to be examined
string thridValue = ds1.Tables[0].Rows[2][0].ToString();
// Check if that value is empty which will be tested using ISBLANK
if (thridValue == string.Empty)
{
Console.WriteLine("The third value is empty");
}
// Instantiate a new WorkbookDesigner
WorkbookDesigner designer = new WorkbookDesigner();
// Set flag UpdateReference to true to indicate that references in other worksheets will be updated
designer.UpdateReference = true;
// Specify the Workbook
designer.Workbook = workbook;
// Use this flag to treat the empty string as null. If false, then ISBLANK will not work
designer.UpdateEmptyStringAsNull = true;
// Specify data source for the designer
designer.SetDataSource(ds1.Tables["comparison"]);
// Process the smart markers and populate the data source values
designer.Process();
// Save the resultant workbook
workbook.Save(outputDir + @"outputSampleIsBlank.xlsx");
// ExEnd:1
Console.WriteLine("EvaluateIsBlank executed successfully.");
}
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);
}
//Open a designer workbook
WorkbookDesigner designer = new WorkbookDesigner();
//get worksheet Cells collection
Cells cells = designer.Workbook.Worksheets[0].Cells;
//Set Cell Values
cells["A1"].PutValue("Name");
cells["B1"].PutValue("Age");
//Set markers
cells["A2"].PutValue("&=Person.Name");
cells["B2"].PutValue("&=Person.Age");
//Create Array list
ArrayList list = new ArrayList();
//add custom objects to the list
list.Add(new Person("Simon", 30));
list.Add(new Person("Johnson", 33));
//add designer's datasource
designer.SetDataSource("Person", list);
//process designer
designer.Process(false);
//save the resultant file
designer.Workbook.Save(dataDir + "result.xls");
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="templateFileName">模板名称</param>
/// <param name="fileName">导出文件名称</param>
/// <param name="dataSource">数据源</param>
public static void ExportExcels(string fileName, DataTable dataSource)
{
string rootPath = BuildExportTemplate(dataSource);
Workbook wk = new Workbook(rootPath);
//CreateExportData(wk, dataSource, fileName);
WorkbookDesigner designer = new WorkbookDesigner(wk);
designer.SetDataSource(dataSource);
designer.Process();
designer.Workbook.Save(HttpContext.Current.Response, fileName, ContentDisposition.Attachment, designer.Workbook.SaveOptions);
try
{
File.Delete(rootPath);
}
catch (Exception)
{
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="templateFileName">模板名称</param>
/// <param name="fileName">导出文件名称</param>
/// <param name="dataSource">数据源</param>
public static string ExportExcelsTo(string templateFileName, string fileName, DataTable dataSource)
{
string rootPath = HttpContext.Current.Server.MapPath(string.Format("~/ReportTemplate/{0}", templateFileName));
string folderPath = HttpContext.Current.Server.MapPath("~/Temp");
if (System.IO.Directory.Exists(folderPath))
{
System.IO.Directory.CreateDirectory(folderPath);
}
string filePath = System.IO.Path.Combine(folderPath, fileName);
Workbook wk = new Workbook(rootPath);
WorkbookDesigner designer = new WorkbookDesigner(wk);
designer.SetDataSource(dataSource);
designer.Process();
designer.Workbook.Save(filePath);
//designer.Workbook.Save(HttpContext.Current.Response, fileName, ContentDisposition.Attachment, designer.Workbook.SaveOptions);
return(fileName);
}
/// <summary>
/// Xuất báo cáo theo Smartmarkers
/// </summary>
/// <returns></returns>
public MemoryStream ExprortSmartmarkers()
{
MemoryStream dstStream = new MemoryStream();
WorkbookDesigner designer = new WorkbookDesigner()
{
Workbook = new Workbook(this.TemplatePath)
};
#region [Smart markers]
foreach (var smark in this.SmartmarkersObjData)
{
designer.SetDataSource(smark.Key, smark.Value);
}
designer.Process();
#endregion [Smart markers]
designer.Workbook.CalculateFormula();
designer.Workbook.Save(dstStream, SaveFormat.Xlsx);
return(dstStream);
}
public static void Run()
{
// ExStart:1
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiating a WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();
Workbook workbook = new Workbook(sourceDir + "AllowLeadingApostropheSample.xlsx");
workbook.Settings.QuotePrefixToStyle = false;
// Open a designer spreadsheet containing smart markers
designer.Workbook = workbook;
List <DataObject> list = new List <DataObject>
{
new DataObject
{
Id = 1,
Name = "demo"
},
new DataObject
{
Id = 2,
Name = "'demo"
}
};
// Set the data source for the designer spreadsheet
designer.SetDataSource("sampleData", list);
// Process the smart markers
designer.Process();
designer.Workbook.Save(outputDir + "AllowLeadingApostropheSample_out.xlsx");
// ExEnd:1
Console.WriteLine("AllowLeadingApostrophe executed successfully.");
}
public static void Run()
{
// 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);
// Open a designer workbook
WorkbookDesigner designer = new WorkbookDesigner();
// Get worksheet Cells collection
Cells cells = designer.Workbook.Worksheets[0].Cells;
// Set Cell Values
cells["A1"].PutValue("Name");
cells["B1"].PutValue("Age");
// Set markers
cells["A2"].PutValue("&=Person.Name");
cells["B2"].PutValue("&=Person.Age");
// Create Array list
ArrayList list = new ArrayList();
// Add custom objects to the list
list.Add(new Person("Simon", 30));
list.Add(new Person("Johnson", 33));
// Add designer's datasource
designer.SetDataSource("Person", list);
// Process designer
designer.Process(false);
dataDir = dataDir + "result.out.xls";
// Save the resultant file
designer.Workbook.Save(dataDir);
Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir);
}
public static void Run()
{
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Open a designer workbook
WorkbookDesigner designer = new WorkbookDesigner();
// Get worksheet Cells collection
Cells cells = designer.Workbook.Worksheets[0].Cells;
// Set Cell Values
cells["A1"].PutValue("Name");
cells["B1"].PutValue("Age");
// Set markers
cells["A2"].PutValue("&=Person.Name");
cells["B2"].PutValue("&=Person.Age");
// Create Array list
ArrayList list = new ArrayList();
// Add custom objects to the list
list.Add(new Person("Simon", 30));
list.Add(new Person("Johnson", 33));
list.Add(new Person("Peter", 36));
list.Add(new Person("Roy", 32));
list.Add(new Person("James", 37));
list.Add(new Person("Michael", 38));
list.Add(new Person("George", 34));
// Add designer's datasource
designer.SetDataSource("Person", list);
// Process designer
designer.Process();
// Save the resultant file
designer.Workbook.Save(outputDir + "outputAddingAnonymousCustomObject.xlsx");
Console.WriteLine("AddingAnonymousCustomObject executed successfully.\r\n");
}
public static byte[] ExportTemplate(string excelFilePath, Dictionary <string, object> keyValues, string[] sheetName)
{
#if NETSTANDARD2_0
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
#endif
Workbook wb = new Workbook(excelFilePath);
WorkbookDesigner book = new WorkbookDesigner(wb);
foreach (var item in keyValues)
{
book.SetDataSource(item.Key, item.Value);
}
book.Process();
for (int i = 0; i < sheetName.Length; i++)
{
book.Workbook.Worksheets[i].Name = sheetName[i];
}
MemoryStream stream = new MemoryStream();
book.Workbook.Save(stream, SaveFormat.Xlsx);
return(stream.ToArray());
}
public string ExcuteXls <T>(List <T> list) where T : new()
{
this.DeleteOutPutFiles();
var designer = new WorkbookDesigner();
if (File.Exists(this._templateFile))
{
designer.Open(this._templateFile);
}
if (list != null)
{
designer.SetDataSource("Datas", list);
designer.Process();
designer.Save(this._fileName, FileFormatType.Excel2003XML);
}
return(HttpContext.Current.Server.MapPath($"~//App_Data//Out//{this._fileName}"));
}
public static void Run()
{
//ExStart:ImageMarkers
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Get the image data.
byte[] imageData = File.ReadAllBytes(dataDir + @"aspose-logo.jpg");
// Create a datatable.
DataTable t = new DataTable("Table1");
// Add a column to save pictures.
DataColumn dc = t.Columns.Add("Picture");
// Set its data type.
dc.DataType = typeof(object);
// Add a new new record to it.
DataRow row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
// Add another record (having picture) to it.
imageData = File.ReadAllBytes(dataDir + @"image2.jpg");
row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
// Create WorkbookDesigner object.
WorkbookDesigner designer = new WorkbookDesigner();
// Open the template Excel file.
designer.Workbook = new Workbook(dataDir + @"TestSmartMarkers.xlsx");
// Set the datasource.
designer.SetDataSource(t);
// Process the markers.
designer.Process();
// Save the Excel file.
designer.Workbook.Save(dataDir + @"out_SmartBook.out.xls");
//ExEnd:ImageMarkers
}
protected FileDto CreateExcelPackage(string templateFile, DataSet dataSet, string reportName)
{
//
var file = new FileDto(reportName, MimeTypeNames.ApplicationVndOpenxmlformatsOfficedocumentSpreadsheetmlSheet);
var designer = new WorkbookDesigner {
Workbook = new Workbook(templateFile)
};
//Set data row
designer.SetDataSource(dataSet);
designer.Process();
MemoryStream excelStream = new MemoryStream();
designer.Workbook.Save(excelStream, SaveFormat.Xlsx);
_tempFileCacheManager.SetFile(file.FileToken, excelStream.ToArray());
return(file);
}
public async Task <IActionResult> GetMaterialReceiveExcel(MaterialReceiveParam MaterialReceiveParam)
{
var data = await _reportService.GetMaterialReceiveExcel(MaterialReceiveParam);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\MaterialReceive.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data);
designer.Process();
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 async Task <IActionResult> ExportExcelMainSummary([FromQuery] PaginationParams paginationParams, KanbanByPoParam kanbanByPoParam)
{
var data = await _kanbanByPoService.GetKanbanByPo(kanbanByPoParam, paginationParams, false);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\KanbanByPoMainSummary.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data);
designer.Process();
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 = 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);
//Open a designer workbook
WorkbookDesigner designer = new WorkbookDesigner();
//get worksheet Cells collection
Cells cells = designer.Workbook.Worksheets[0].Cells;
//Set Cell Values
cells["A1"].PutValue("Name");
cells["B1"].PutValue("Age");
//Set markers
cells["A2"].PutValue("&=Person.Name");
cells["B2"].PutValue("&=Person.Age");
//Create Array list
ArrayList list = new ArrayList();
//add custom objects to the list
list.Add(new Person("Simon", 30));
list.Add(new Person("Johnson", 33));
//add designer's datasource
designer.SetDataSource("Person", list);
//process designer
designer.Process(false);
//save the resultant file
designer.Workbook.Save(dataDir+ "result.xls");
}
protected void btnExportExcel_ServerClick(object sender, EventArgs e)
{
HttpCookie getCookies = Request.Cookies["UserLogin"];
if (getCookies != null)
{
DateTime dt = DateTime.Now;
DateTime dtBegin = new DateTime();
DateTime dtEnd = new DateTime();
if (string.IsNullOrEmpty(txtTimeSelect.Value.Trim()))
{
DateTime startMonth = dt.AddDays(1 - dt.Day); //本月月初
DateTime endMonth = startMonth.AddMonths(1).AddDays(-1); //本月月末
dtBegin = startMonth;
dtEnd = endMonth;
}
else
{
string[] strDate = txtTimeSelect.Value.Trim().Split('-');
dtBegin = Convert.ToDateTime(strDate[0]);
dtEnd = Convert.ToDateTime(strDate[1]);
}
var getFinanceMonthData = SearchDataClass.ExportFinanceMonthInfoListData(dtBegin, dtEnd);
//创建一个workbookdesigner对象
WorkbookDesigner designer = new WorkbookDesigner();
//制定报表模板
designer.Open(Server.MapPath(@"model\FinanceMonthList.xls"));
//设置实体类对象
designer.SetDataSource("Export", getFinanceMonthData);
//根据数据源处理生成报表内容
designer.Process();
//客户端保存的文件名
string fileName = HttpUtility.UrlEncode("月销售金额报表统计导出") + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
designer.Save(fileName, SaveType.OpenInExcel, FileFormatType.Excel2003, Response);
Response.Flush();
Response.Close();
designer = null;
Response.End();
}
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Creating a DataTable that will serve as data source for designer spreadsheet
DataTable table = new DataTable("OppLineItems");
table.Columns.Add("PRODUCT_FAMILY");
table.Columns.Add("OPPORTUNITY_LINEITEM_PRODUCTNAME");
table.Rows.Add(new object[] { "MMM", "P1" });
table.Rows.Add(new object[] { "MMM", "P2" });
table.Rows.Add(new object[] { "DDD", "P1" });
table.Rows.Add(new object[] { "DDD", "P2" });
table.Rows.Add(new object[] { "AAA", "P1" });
// Loading the designer spreadsheet in an instance of Workbook
Workbook workbook = new Workbook(sourceDir + "sampleGetSmartMarkerNotifications.xlsx");
// Loading the instance of Workbook in an instance of WorkbookDesigner
WorkbookDesigner designer = new WorkbookDesigner(workbook);
// Set the WorkbookDesigner.CallBack property to an instance of newly created class
designer.CallBack = new SmartMarkerCallBack(workbook);
// Set the data source
designer.SetDataSource(table);
// Process the Smart Markers in the designer spreadsheet
designer.Process(false);
// Save the result
workbook.Save(outputDir + "outputGetSmartMarkerNotifications.xlsx");
Console.WriteLine("GetSmartMarkerNotifications executed successfully.");
}
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);
//Instantiating a WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();
//Open a designer spreadsheet containing smart markers
designer.Workbook = new Workbook(designerFile);
//Set the data source for the designer spreadsheet
designer.SetDataSource(dataset);
//Process the smart markers
designer.Process();
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Get the image data.
byte[] imageData = File.ReadAllBytes(dataDir + @"aspose-logo.jpg");
//Create a datatable.
DataTable t = new DataTable("Table1");
//Add a column to save pictures.
DataColumn dc = t.Columns.Add("Picture");
//Set its data type.
dc.DataType = typeof(object);
//Add a new new record to it.
DataRow row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
//Add another record (having picture) to it.
imageData = File.ReadAllBytes(dataDir + @"image2.jpg");
row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
//Create WorkbookDesigner object.
WorkbookDesigner designer = new WorkbookDesigner();
//Open the template Excel file.
designer.Workbook = new Workbook(dataDir + @"TestSmartMarkers.xls");
//Set the datasource.
designer.SetDataSource(t);
//Process the markers.
designer.Process();
//Save the Excel file.
designer.Workbook.Save(dataDir + @"out_SmartBook.xls");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Get the image data.
byte[] imageData = File.ReadAllBytes(dataDir+ "aspose-logo.jpg");
// Create a datatable.
DataTable t = new DataTable("Table1");
// Add a column to save pictures.
DataColumn dc = t.Columns.Add("Picture");
// Set its data type.
dc.DataType = typeof(object);
// Add a new new record to it.
DataRow row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
// Add another record (having picture) to it.
imageData = File.ReadAllBytes(dataDir+ "image2.jpg");
row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
// Create WorkbookDesigner object.
WorkbookDesigner designer = new WorkbookDesigner();
// Open the template Excel file.
designer.Workbook = new Workbook(dataDir+ "TestSmartMarkers.xlsx");
// Set the datasource.
designer.SetDataSource(t);
// Process the markers.
designer.Process();
// Save the Excel file.
designer.Workbook.Save(dataDir+ "output.xls");
// ExEnd:1
}
static void Main(string[] args)
{
string FilePath = @"..\..\..\Sample Files\";
string FileName = FilePath + "Image Markers.xlsx";
//Get the image data.
byte[] imageData = File.ReadAllBytes(FilePath + "Aspose.Cells.png");
//Create a datatable.
DataTable t = new DataTable("Table1");
//Add a column to save pictures.
DataColumn dc = t.Columns.Add("Picture");
//Set its data type.
dc.DataType = typeof(object);
//Add a new new record to it.
DataRow row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
//Add another record (having picture) to it.
//imageData = File.ReadAllBytes(FilePath + "Desert.jpg");
//row = t.NewRow();
//row[0] = imageData;
//t.Rows.Add(row);
//Create WorkbookDesigner object.
WorkbookDesigner designer = new WorkbookDesigner();
//Open the temple Excel file.
designer.Workbook = new Workbook(FileName);
//Set the datasource.
designer.SetDataSource(t);
//Process the markers.
designer.Process();
//Save the Excel file.
designer.Workbook.Save(FileName);
}
static void Main(string[] args)
{
string FilePath = @"..\..\..\Sample Files\";
string FileName = FilePath + "Image Markers.xlsx";
//Get the image data.
byte[] imageData = File.ReadAllBytes(FilePath + "Aspose.Cells.png");
//Create a datatable.
DataTable t = new DataTable("Table1");
//Add a column to save pictures.
DataColumn dc = t.Columns.Add("Picture");
//Set its data type.
dc.DataType = typeof(object);
//Add a new new record to it.
DataRow row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
//Add another record (having picture) to it.
//imageData = File.ReadAllBytes(FilePath + "Desert.jpg");
//row = t.NewRow();
//row[0] = imageData;
//t.Rows.Add(row);
//Create WorkbookDesigner object.
WorkbookDesigner designer = new WorkbookDesigner();
//Open the temple Excel file.
designer.Workbook = new Workbook(FileName);
//Set the datasource.
designer.SetDataSource(t);
//Process the markers.
designer.Process();
//Save the Excel file.
designer.Workbook.Save(FileName);
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Get the image data.
byte[] imageData = File.ReadAllBytes(dataDir+ @"aspose-logo.jpg");
//Create a datatable.
DataTable t = new DataTable("Table1");
//Add a column to save pictures.
DataColumn dc = t.Columns.Add("Picture");
//Set its data type.
dc.DataType = typeof(object);
//Add a new new record to it.
DataRow row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
//Add another record (having picture) to it.
imageData = File.ReadAllBytes(dataDir+ @"image2.jpg");
row = t.NewRow();
row[0] = imageData;
t.Rows.Add(row);
//Create WorkbookDesigner object.
WorkbookDesigner designer = new WorkbookDesigner();
//Open the template Excel file.
designer.Workbook = new Workbook(dataDir+ @"TestSmartMarkers.xls");
//Set the datasource.
designer.SetDataSource(t);
//Process the markers.
designer.Process();
//Save the Excel file.
designer.Workbook.Save(dataDir+ @"out_SmartBook.xls");
}
public static void Run()
{
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Get the images
byte[] photo1 = File.ReadAllBytes(dataDir + "moon.png");
byte[] photo2 = File.ReadAllBytes(dataDir + "moon2.png");
// Create a new workbook and access its worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
// Set the standard row height to 35
worksheet.Cells.StandardHeight = 35;
// Set column widhts of D, E and F
worksheet.Cells.SetColumnWidth(3, 20);
worksheet.Cells.SetColumnWidth(4, 20);
worksheet.Cells.SetColumnWidth(5, 40);
// Add the headings in columns D, E and F
worksheet.Cells["D1"].PutValue("Name");
Style st = worksheet.Cells["D1"].GetStyle();
st.Font.IsBold = true;
worksheet.Cells["D1"].SetStyle(st);
worksheet.Cells["E1"].PutValue("City");
worksheet.Cells["E1"].SetStyle(st);
worksheet.Cells["F1"].PutValue("Photo");
worksheet.Cells["F1"].SetStyle(st);
// Add smart marker tags in columns D, E, F
worksheet.Cells["D2"].PutValue("&=Person.Name(group:normal,skip:1)");
worksheet.Cells["E2"].PutValue("&=Person.City");
worksheet.Cells["F2"].PutValue("&=Person.Photo(Picture:FitToCell)");
// Create Persons objects with photos
List<Person> persons = new List<Person>();
persons.Add(new Person("George", "New York", photo1));
persons.Add(new Person("George", "New York", photo2));
persons.Add(new Person("George", "New York", photo1));
persons.Add(new Person("George", "New York", photo2));
persons.Add(new Person("Johnson", "London", photo2));
persons.Add(new Person("Johnson", "London", photo1));
persons.Add(new Person("Johnson", "London", photo2));
persons.Add(new Person("Simon", "Paris", photo1));
persons.Add(new Person("Simon", "Paris", photo2));
persons.Add(new Person("Simon", "Paris", photo1));
persons.Add(new Person("Henry", "Sydney", photo2));
persons.Add(new Person("Henry", "Sydney", photo1));
persons.Add(new Person("Henry", "Sydney", photo2));
// Create a workbook designer
WorkbookDesigner designer = new WorkbookDesigner(workbook);
// Set the data source and process smart marker tags
designer.SetDataSource("Person", persons);
designer.Process();
// Save the workbook
workbook.Save(dataDir + "UsingImageMarkersWhileGroupingDataInSmartMarkers.xlsx", SaveFormat.Xlsx);
}
public static void Run()
{
// ExStart:CreationOfDesignerSpreadsheet
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create an instance of Workbook
var book = new Workbook();
// Access the first, default Worksheet by passing its index
var dataSheet = book.Worksheets[0];
// Name the Worksheet for later reference
dataSheet.Name = "ChartData";
// Access the CellsCollection of first Worksheet
var cells = dataSheet.Cells;
// Insert static data (headers)
cells["B1"].PutValue("Item 1");
cells["C1"].PutValue("Item 2");
cells["D1"].PutValue("Item 3");
cells["E1"].PutValue("Item 4");
cells["F1"].PutValue("Item 5");
cells["G1"].PutValue("Item 6");
cells["H1"].PutValue("Item 7");
cells["I1"].PutValue("Item 8");
cells["J1"].PutValue("Item 9");
cells["K1"].PutValue("Item 10");
cells["L1"].PutValue("Item 11");
cells["M1"].PutValue("Item 12");
// Place Smart Markers
cells["A2"].PutValue("&=Sales.Year");
cells["B2"].PutValue("&=Sales.Item1");
cells["C2"].PutValue("&=Sales.Item2");
cells["D2"].PutValue("&=Sales.Item3");
cells["E2"].PutValue("&=Sales.Item4");
cells["F2"].PutValue("&=Sales.Item5");
cells["G2"].PutValue("&=Sales.Item6");
cells["H2"].PutValue("&=Sales.Item7");
cells["I2"].PutValue("&=Sales.Item8");
cells["J2"].PutValue("&=Sales.Item9");
cells["K2"].PutValue("&=Sales.Item10");
cells["L2"].PutValue("&=Sales.Item11");
cells["M2"].PutValue("&=Sales.Item12");
// ExEnd:CreationOfDesignerSpreadsheet
// ExStart:ProcessingDesignerSpreadsheet
// Create an instance of DataTable and name is according to the Smart Markers
var table = new DataTable("Sales");
/*
* Add columns to the newly created DataTable while specifying the column type
* It is important that the DataTable should have at least one column for each
* Smart Marker entry from the designer spreadsheet
*/
table.Columns.Add("Year", typeof(string));
table.Columns.Add("Item1", typeof(int));
table.Columns.Add("Item2", typeof(int));
table.Columns.Add("Item3", typeof(int));
table.Columns.Add("Item4", typeof(int));
table.Columns.Add("Item5", typeof(int));
table.Columns.Add("Item6", typeof(int));
table.Columns.Add("Item7", typeof(int));
table.Columns.Add("Item8", typeof(int));
table.Columns.Add("Item9", typeof(int));
table.Columns.Add("Item10", typeof(int));
table.Columns.Add("Item11", typeof(int));
table.Columns.Add("Item12", typeof(int));
// Add some rows with data to the DataTable
table.Rows.Add("2000", 2310, 0, 110, 15, 20, 25, 30, 1222, 200, 421, 210, 133);
table.Rows.Add("2005", 1508, 0, 170, 280, 190, 400, 105, 132, 303, 199, 120, 100);
table.Rows.Add("2010", 0, 210, 230, 140, 150, 160, 170, 110, 1999, 1229, 1120, 2300);
table.Rows.Add("2015", 3818, 320, 340, 260, 210, 310, 220, 0, 0, 0, 0, 122);
// ExEnd:ProcessingDesignerSpreadsheet
// ExStart:ProcessingOfSmartMarkers
// Create an instance of WorkbookDesigner class
var designer = new WorkbookDesigner();
// Assign the Workbook property to the instance of Workbook created in first step
designer.Workbook = book;
// Set the data source
designer.SetDataSource(table);
// Call Process method to populate data
designer.Process();
// ExEnd:ProcessingOfSmartMarkers
// ExStart:CreationOfChart
/*
* Save the number of rows & columns from the source DataTable in seperate variables.
* These values will be used later to identify the chart's data range from DataSheet
*/
int chartRows = table.Rows.Count;
int chartCols = table.Columns.Count;
// Add a new Worksheet of type Chart to Workbook
int chartSheetIdx = book.Worksheets.Add(SheetType.Chart);
// Access the newly added Worksheet via its index
var chartSheet = book.Worksheets[chartSheetIdx];
// Name the Worksheet
chartSheet.Name = "Chart";
// Add a chart of type ColumnStacked to newly added Worksheet
int chartIdx = chartSheet.Charts.Add(ChartType.ColumnStacked, 0, 0, chartRows, chartCols);
// Access the newly added Chart via its index
var chart = chartSheet.Charts[chartIdx];
// Set the data range for the chart
chart.SetChartDataRange(dataSheet.Name + "!A1:" + CellsHelper.ColumnIndexToName(chartCols - 1) + (chartRows + 1).ToString(), false);
// Set the chart to size with window
chart.SizeWithWindow = true;
// Set the format for the tick labels
chart.ValueAxis.TickLabels.NumberFormat = "$###,### K";
// Set chart title
chart.Title.Text = "Sales Summary";
// Set ChartSheet an active sheet
book.Worksheets.ActiveSheetIndex = chartSheetIdx;
// Save the final result
book.Save(dataDir + "report_out.xlsx");
// ExEnd:CreationOfChart
}
public byte[] Print(CustomerSelectionModel selection)
{
var consultant = _consultantContext.Consultant;
using (var designerFileStream = GetLocalizedTemplate("Customer_PrintList_Designer.xls"))
{
if (designerFileStream != null)
{
var consultantName = string.Format(_appSettings.GetValue("ConsultantNameFormat"), consultant.FirstName, consultant.LastName);
var now = DateTime.Now;
var data = new DataSet("PrintList");
var stringsTable = GetStringsTable();
data.Tables.Add(stringsTable);
var customerTable = GetCustomerTable(selection);
data.Tables.Add(customerTable);
var designer = new WorkbookDesigner();
designer.Workbook = new Workbook(designerFileStream, new Aspose.Cells.LoadOptions(LoadFormat.Excel97To2003));
SetWorkbookBuiltInProperties(designer.Workbook, consultantName);
SetWorksheetHeader(designer.Workbook, 0, consultantName, Resources.GetString("PRINTLIST_DOCUMENTTITLE"), now.ToLongDateString());
SetWorksheetFooter(designer.Workbook, 0, Resources.GetString("PRINTLIST_FOOTERLEFTSCRIPT").Trim(), Resources.GetString("PRINTLIST_FOOTERCENTERSCRIPT").Trim(), Resources.GetString("PRINTLIST_FOOTERRIGHTSCRIPT").Trim());
designer.SetDataSource(data);
designer.Process();
var stream = new MemoryStream();
designer.Workbook.Save(stream, new XlsSaveOptions(SaveFormat.Pdf));
// position the stream at the beginnin so it will be ready for writing to the output
stream.Seek(0, SeekOrigin.Begin);
return stream.ToArray();
}
}
return null;
}
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
}
static void Main(string[] args)
{
string MyDir = @"Files\";
//Initialize WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();
//Load the template file
designer.Workbook = new Workbook(MyDir+"SM_NestedObjects.xlsx");
//Instantiate the List based on the class
System.Collections.Generic.ICollection<Individual> list = new System.Collections.Generic.List<Individual>();
//Create an object for the Individual class
Individual p1 = new Individual("Damian", 30);
//Create the relevant Wife class for the Individual
p1.Wife = new Wife("Dalya", 28);
//Create another object for the Individual class
Individual p2 = new Individual("Mack", 31);
//Create the relevant Wife class for the Individual
p2.Wife = new Wife("Maaria", 29);
//Add the objects to the list
list.Add(p1);
list.Add(p2);
//Specify the DataSource
designer.SetDataSource("Individual", list);
//Process the markers
designer.Process(false);
//Save the Excel file.
designer.Workbook.Save(MyDir+"out_SM_NestedObjects.xlsx");
}
