public static void Run()
{
// ExStart:2
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate the workbookdesigner object.
WorkbookDesigner report = new WorkbookDesigner();
// Get the first worksheet(default sheet) in the workbook.
Aspose.Cells.Worksheet sheet = report.Workbook.Worksheets[0];
// Input some markers to the cells.
sheet.Cells["A1"].PutValue("Name");
sheet.Cells["B1"].PutValue("Age");
sheet.Cells["A2"].PutValue("&=MyProduct.Name");
sheet.Cells["B2"].PutValue("&=MyProduct.Age");
// Instantiate the list collection based on the custom class.
IList <Person> list = new List <Person>();
// Provide values for the markers using the custom class object.
list.Add(new Person("Simon", 30));
list.Add(new Person("Johnson", 33));
// Set the data source.
report.SetDataSource("MyProduct", list);
// Process the markers.
report.Process(false);
// Save the excel file.
report.Workbook.Save(dataDir + "Smart Marker Customobjects.xls");
// ExEnd:2
}
protected void btnGenExcel_Click(object sender, EventArgs e)
{
Dictionary <string, string> dictSource = new Dictionary <string, string>();
dictSource.Add("TIS_HANDLE_NO", "T0001");
dictSource.Add("ACCUSE_INDUSTRY", "出租车");
dictSource.Add("ACCUSER_NAME", "张三");
string templateFile = Server.MapPath("./Templates/Advice.xls");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(templateFile);
Aspose.Cells.Worksheet worksheet = designer.Workbook.Worksheets[0];
//使用文本替换
foreach (string name in dictSource.Keys)
{
worksheet.Replace(name, dictSource[name]);
}
//使用绑定数据方式替换
designer.SetDataSource("ACCUSER_SEX", "男");
designer.SetDataSource("ACCUSER_TEL", "1862029207*");
designer.Process();
string saveFileName = "testAdvice.xls";
designer.Workbook.Save(Response.OutputStream, SaveFormat.Excel97To2003);
Response.Charset = "GB2312";
Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-excel/msword";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(saveFileName));
Response.Flush();
}
public static void Main()
{
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string sourceDir = RunExamples.Get_SourceDirectory();
// Instantiating a Workbook object
Workbook workbook = new Workbook(sourceDir + "sampleNamesTable.xlsx");
// Instantiating a WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner(workbook);
// Accessing the range having name "Names"
var range = designer.Workbook.Worksheets.GetRangeByName("Names");
// Instantiating the ExportTableOptions object
ExportTableOptions options = new ExportTableOptions();
// Setting the ExportColumnName flag to true shows that first line is header and not part of data
options.ExportColumnName = true;
// Exporting data with the selected information
var dataTable = range.ExportDataTable(options);
Console.WriteLine("ExportRangeWithFlagToSkipColumnName executed successfully.\r\n");
}
/// <summary>
/// 根据键值对列表的替换模板内容，在Web环境中导出Excel文件。
/// </summary>
/// <param name="templateFile">模板文件路径</param>
/// <param name="saveFileName">保存的文件名称,如a.xls</param>
/// <param name="dictReplace">待替换内容和替换值的键值对</param>
public static void WebExportWithReplace(string templateFile, string saveFileName, Dictionary <string, string> dictReplace)
{
HttpContext curContext = HttpContext.Current;
string physicPath = curContext.Server.MapPath(templateFile);
if (!File.Exists(physicPath))
{
throw new ArgumentException(templateFile, string.Format("{0} 文件不存在，", templateFile));
}
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(physicPath);
Aspose.Cells.Worksheet worksheet = designer.Workbook.Worksheets[0];
foreach (string name in dictReplace.Keys)
{
worksheet.Replace(name, dictReplace[name]);
}
designer.Process();
HttpResponse response = curContext.Response;
designer.Workbook.Save(response.OutputStream, SaveFormat.Excel97To2003);
response.Charset = "GB2312";
response.ContentEncoding = Encoding.GetEncoding("GB2312");
response.ContentType = "application/ms-excel/msword";
response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(saveFileName));
response.Flush();
}
public static void StartCreate(List <ReportTemplate> reportlist)
{
//将list集合转为DataTable方便模板方法使用
ListToDataTableHelper listHelper = new ListToDataTableHelper();
DataTable dt = listHelper.ToDataTable(reportlist);
string basepath = Directory.GetCurrentDirectory();
string path = $"{basepath}\\SummaryTemplate.xlsx";
WorkbookDesigner designer = new WorkbookDesigner();
designer.Open(path);
designer.SetDataSource(dt);
//根据数据源处理生成报表内容
designer.Process();
//保存Excel文件
string fileToSave = GetFilePath();
string filename = "项目统计表-江宁分公司- " + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string fullpath = fileToSave + "\\" + filename;//完整路径
designer.Save(fullpath, FileFormatType.Excel2003);
//提示“保存成功，是否立即打开”
if (MessageBox.Show("已为您保存至:" + fullpath + "\r\n是否立即打开？", "保存成功！", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
//调用系统进程打开文件
System.Diagnostics.Process.Start(fullpath);
}
}
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");
}
/// <summary>
/// 导出数据生成文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void ExportExcel(DataTable dt, string templetePath, string templeteName, string filePath, string fileName)
{
WorkbookDesigner designer = new WorkbookDesigner();
string path = System.IO.Path.Combine(templetePath, templeteName);
FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read);
designer.Workbook = new Workbook(fileStream);
designer.SetDataSource(dt);
designer.Process();
string fileToSave = System.IO.Path.Combine(filePath, fileName);
if (File.Exists(fileToSave))
{
File.Delete(fileToSave);
}
designer.Workbook.Save(fileToSave, SaveFormat.Xlsx);
fileStream.Close();
fileStream.Dispose();
}
public HttpResponseMessage Test()
{
var d = new RowNumModel <DoctorLearnViewModel>();
d.SearchParams = new DoctorLearnViewModel();
var ret = _iADDoctorService.GetDoctorLearn(d, true);
var _filePath = HostingEnvironment.MapPath("/ExcelTemplate/" + "CatCityList.xlsx");
Workbook wb = new Workbook(_filePath);
WorkbookDesigner book = new WorkbookDesigner(wb);
book.SetDataSource("H", ret.Result);
book.Process();
book.Workbook.Worksheets[0].Name = "222";
MemoryStream stream = new MemoryStream();
book.Workbook.Save(stream, SaveFormat.Xlsx);
byte[] bytes = stream.ToArray();
var resp = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(bytes)
};
resp.Content.Headers.ContentType = new MediaTypeHeaderValue("application/x-excel");
resp.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = $"{DateTime.Now.ToString("yyyyMMddHHmmsss")}{".xlsx"}"
};
return(resp);
}
static void Main()
{
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string outputPath = dataDir + "Output.out.xlsx";
//Instantiate a new Workbook designer
WorkbookDesigner designer = new WorkbookDesigner();
/**********************************************************************
//Get the first worksheet of the workbook
Worksheet sheet = report.Workbook.Worksheets[0];
//Set the Variable Array marker to a cell
//You may also place this Smart Marker into a template file manually using Excel and then open this file via WorkbookDesigner
sheet.Cells.Get("A1").PutValue("&=$VariableArray");
//Set the data source for the marker(s)
report.DataSource("VariableArray", new String[] { "English", "Arabic", "Hindi", "Urdu", "French" });
//Set the CallBack property
report.CallBack(new SmartMarkerCallBack(report.Workbook));
//Process the markers
report.Process(false);
//Save the result
report.Workbook.Save(outputPath);
*******************************************/
Console.WriteLine("File saved {0}", outputPath);
}
/// <summary>
/// 导出数据生成文件流
/// </summary>
/// <param name="dt"></param>
/// <param name="templetePath"></param>
/// <param name="templeteName"></param>
/// <returns></returns>
public MemoryStream ExportExcel(DataTable dt, string templetePath, string templeteName)
{
WorkbookDesigner designer = new WorkbookDesigner();
string path = System.IO.Path.Combine(templetePath, templeteName);
using (FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read))
{
designer.Workbook = new Workbook(fileStream);
designer.SetDataSource(dt);
designer.Process();
MemoryStream stream = designer.Workbook.SaveToStream();
fileStream.Close();
fileStream.Dispose();
return(stream);
}
//HttpContext.Current.Response.Clear();
//HttpContext.Current.Response.Buffer = true;
//HttpContext.Current.Response.Charset = "utf-8";
//HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
//HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
//HttpContext.Current.Response.ContentType = "application/ms-excel";
//HttpContext.Current.Response.BinaryWrite(stream.ToArray());
//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 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 Workbook GenerateWorkbook(int periodNumber, IEnumerable <ProviderSubmissionModel> providerSubmissionModels)
{
Workbook workbook = GetWorkbookFromTemplate(TemplateName);
var worksheet = workbook.Worksheets[ProviderSubmissionTabName];
var returned = providerSubmissionModels.Count(x => x.Returned);
var expectedButNotReturned = providerSubmissionModels.Count(x => x.Expected && !x.Returned);
var zeroLearnerReturns = providerSubmissionModels.Count(x => x.Returned && x.TotalValid == 0);
var expected = providerSubmissionModels.Count(x => x.Expected);
var returningExpected = providerSubmissionModels.Count(x => x.Returned && x.Expected);
var returningUnexpected = providerSubmissionModels.Count(x => x.Returned && !x.Expected);
worksheet.Cells[1, 1].PutValue($"ILR Provider Submissions Report - R{periodNumber:D2}");
worksheet.Cells[2, 1].PutValue($"Report Run: {_dateTimeProvider.ConvertUtcToUk(_dateTimeProvider.GetNowUtc()).ToString("u")}");
worksheet.Cells[4, 1].PutValue($"Total No of Returning Providers: {returned}");
worksheet.Cells[5, 1].PutValue($"No of Expected Providers Not Returning: {expectedButNotReturned}");
worksheet.Cells[6, 1].PutValue($"No of \"0 Learner\" Returns: {zeroLearnerReturns}");
worksheet.Cells[7, 1].PutValue($"No of Providers Expected to Return: {expected}");
worksheet.Cells[8, 1].PutValue($"No of Returning Expected Providers: {returningExpected}");
worksheet.Cells[9, 1].PutValue($"No of Returning Unexpected Providers: {returningUnexpected}");
var designer = new WorkbookDesigner
{
Workbook = workbook
};
designer.SetDataSource("ProviderSubmissions", providerSubmissionModels);
designer.Process();
return(workbook);
}
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()
{
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);
}
}
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()
{
// 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 byte[] CommonExportReportTabs(List <object> dataList, string templateName)
{
MemoryStream stream = new MemoryStream();
try
{
string rootStr = _webHostEnvironment.ContentRootPath;
var path = Path.Combine(rootStr, "Resources\\Template\\" + templateName);
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
int index = 0;
foreach (object data in dataList)
{
Worksheet ws = designer.Workbook.Worksheets[index];
designer.SetDataSource(string.Format(@"result{0}", index), data);
index++;
}
designer.Process();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
}
catch (Exception ex)
{
_logger.LogError("!!!!!!CommonExportReportTabs have a exception!!!!!!");
_logger.LogError(ex.ToString());
throw ex;
}
return(stream.ToArray());;
}
public ActionResult PDF()
{
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\Book1.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
ws.Cells["B5"].PutValue("Demo");
MemoryStream stream = new MemoryStream();
// custom size ( width: in, height: in )
ws.PageSetup.FitToPagesTall = 0;
ws.PageSetup.SetHeader(0, "&D &T");
ws.PageSetup.SetHeader(1, "&B Article");
ws.PageSetup.SetFooter(0, "&B SYSTEM BY MINH HIEU");
ws.PageSetup.SetFooter(2, "&P/&N");
ws.PageSetup.PrintQuality = 2400;
designer.Workbook.Save(stream, SaveFormat.Pdf);
byte[] result = stream.ToArray();
return(File(result, "application/pdf", "PDF.pdf"));
}
public Workbook GenerateWorkbook(
int periodNumber,
IEnumerable <DataQualityReturningProviders> returningProviderModels,
IEnumerable <RuleViolationsInfo> ruleViolationsInfoModels,
IEnumerable <ProviderWithoutValidLearners> providersWithoutValidLearners,
IEnumerable <Top10ProvidersWithInvalidLearners> top10ProvidersWithInvalidLearners)
{
Workbook workbook = GetWorkbookFromTemplate(TemplateName);
var worksheet = workbook.Worksheets[DataQualityTabName];
worksheet.Cells[1, 1].PutValue($"ILR Data Quality Reports - R{periodNumber:D2}");
worksheet.Cells[2, 1].PutValue($"Report Run: {_dateTimeProvider.ConvertUtcToUk(_dateTimeProvider.GetNowUtc()).ToString("u")}");
var designer = new WorkbookDesigner
{
Workbook = workbook
};
designer.SetDataSource("ReturningProvidersInfo", returningProviderModels);
designer.SetDataSource("RuleViolationsInfo", ruleViolationsInfoModels);
designer.SetDataSource("ProviderWithoutValidLearnerInfo", providersWithoutValidLearners);
designer.SetDataSource("Top10ProvidersWithInvalidLearners", top10ProvidersWithInvalidLearners);
designer.Process();
return(workbook);
}
public FileStreamResult ExportReport(string ReportID, WorkbookDesigner designer)
{
try
{
string LData = WebConfigurationManager.AppSettings["LData"];
Stream stream = new MemoryStream(Convert.FromBase64String(LData));
stream.Seek(0, SeekOrigin.Begin);
new Aspose.Cells.License().SetLicense(stream);
if (designer != null)
{
stream = new DongA.Core.DongAExcel().SaveToStream(designer.Workbook);
}
// Return excel
return(File(stream, XLSX,
string.Format("{0}.{1}", ReportID, "xlsx")));
}
catch (Exception ex)
{
MemoryStream memStream = new MemoryStream();
return(File(memStream, PDF));
}
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.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 + "out_SM_NestedObjects.xlsx");
}
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
}
private static void AssembleCellsData(string inFilePath, string outPath, string folderName, string templateFilename, string datasourceFilename, string datasourceName)
{
var dirPath = Path.GetDirectoryName(inFilePath) + "/";
var dataXlsx = dirPath + datasourceFilename;
// var dataDir = Path.GetDirectoryName(dataFolderName) + "/";
// var dataXlsx = dataDir + datasourceFilename;
var wb = new Workbook(dataXlsx);
var ws = wb.Worksheets[0];
var totalRows = ws.Cells.LastCell.Row + 1;
var totalColumns = ws.Cells.LastCell.Column + 1;
var dt = ws.Cells.ExportDataTable(0, 0, totalRows, totalColumns, true);
dt.TableName = datasourceName;
var templateXlsx = dirPath + templateFilename;
// var tempDir = Path.GetDirectoryName(tempFolderName) + '/';
// var templateXlsx = tempDir + templateFilename;
var wbTemplate = new Workbook(templateXlsx);
var wbd = new WorkbookDesigner(wbTemplate);
wbd.SetDataSource(dt);
wbd.Process();
wbTemplate.Save(outPath);
}
protected void btnExportExcel_ServerClick(object sender, EventArgs e)
{
HttpCookie getCookies = Request.Cookies["UserLogin"];
if (getCookies != null)
{
//var timeSearch = string.Empty;
//if (string.IsNullOrEmpty(txtTimeSelect.Value.Trim()))
//{
// timeSearch = "NowMonth";
//}
//else
//{
// timeSearch = txtTimeSelect.Value.Trim();
//}
var getMemberExtractData = SearchDataClass.GetSearchSaleCheckData(drpShopSet.SelectedValue, txtTimeSelect.Value.Trim());
//创建一个workbookdesigner对象
WorkbookDesigner designer = new WorkbookDesigner();
//制定报表模板
designer.Open(Server.MapPath(@"model\cocheckreport.xls"));
//设置实体类对象
designer.SetDataSource("Export", getMemberExtractData);
//根据数据源处理生成报表内容
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 async Task <IActionResult> OutputReport(HistoryReportParam param)
{
var data = await _serviceHistoryReport.HistoryReportOutputExcel(param);
var dataResult = new List <HistoryOutputReport>();
dataResult = _mapper.Map <List <HistoryReportOutputDB>, List <HistoryOutputReport> >(data);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\HistoryReportOutput.xlsx");
dataResult.ForEach(item => {
item.StatusPercent = item.Status.ToString() + "%";
});
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", dataResult);
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"));
}
protected void btnExportExcel_ServerClick(object sender, EventArgs e)
{
HttpCookie getCookies = Request.Cookies["UserLogin"];
if (getCookies != null)
{
var timeSearch = string.Empty;
if (string.IsNullOrEmpty(txtTimeSelect.Value.Trim()))
{
timeSearch = "NowMonth";
}
else
{
timeSearch = txtTimeSelect.Value.Trim();
}
var getFenXiaoExtractData = SearchDataClass.ExportFenXiaoExtractInfoData(timeSearch);
//创建一个workbookdesigner对象
WorkbookDesigner designer = new WorkbookDesigner();
//制定报表模板
designer.Open(Server.MapPath(@"model\FenXiaoExtractList.xls"));
//设置实体类对象
designer.SetDataSource("Export", getFenXiaoExtractData);
//根据数据源处理生成报表内容
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()
{
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 async Task <IActionResult> ExportExcelKanbanByRackDetail(string build_id)
{
var data = await _kanbanByRackService.GetDataExcelKanbanByRackDetail(build_id);
var TTL_PRRS = "TTL PRRS: " + (data.Count() > 0 ? data[0].TTL_PRS : 0);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\KanbanByRackDetail.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
// Gan Gia tri vao
ws.Cells["A1"].PutValue(TTL_PRRS);
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", "ExcelKanbanByRackDetail" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".xlsx"));
}
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 async Task <IActionResult> ExportExcelListPoT3(string rackLocation)
{
var data = await _kanbanByRackService.GetDetailByRackT2T3(rackLocation);
var poQty = data.GroupBy(x => x.MO_No).Count();
var TTL_PRRS = "TTL PRRS: " + (data != null ? data[0].TTL_PRS : 0).ToString();
var PO_Qty = "PO: " + poQty.ToString();
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\KanbanByRackPoListDetailT3.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
// Gan Gia tri tĩnh vào file excel
ws.Cells["A1"].PutValue(TTL_PRRS);
ws.Cells["B1"].PutValue(rackLocation);
ws.Cells["C1"].PutValue(PO_Qty);
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 <ActionResult> ExportExcelScoreRecordDetail(string recordId)
{
var data = await _auditRateService.GetScoreRecoreDetail(recordId);
var Building = await _auditPicDService.GetBuildingByID(data.auditRateM.Building);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resource\\Template\\Sixs_Score_Record_Detail_Template.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook();
Worksheet ws = designer.Workbook.Worksheets[0];
// Gan gia tri tinh
ws.Cells["B2"].PutValue(data.auditRateM.Record_Date);
ws.Cells["D2"].PutValue(data.auditRateM.PDC);
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);
ws.Cells["F4"].PutValue(data.auditRateM.Audit_Type2);
designer.SetDataSource("result", data.listAuditRateD);
designer.Process();
MemoryStream stream = new MemoryStream();
designer.Workbook.Save(stream, SaveFormat.Xlsx);
byte[] result = stream.ToArray();
return(File(result, "application/xlsx", "Sixs_Score_Record_Detail" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss")));
}
public async Task <IActionResult> SendEmail(List <Transfer_Form_Generate_Dto> generateTransferForm)
{
var timeNow = DateTime.Now.ToString("yyyy/MM/dd");
string path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\Send_Mail_Suppllier_T3_Template.xlsx");
string forderSaveFile = _webHostEnvironment.WebRootPath + $@"\FileSendEmailPrintTranferForm\";
foreach (var item in generateTransferForm.GroupBy(x => x.T3_Supplier))
{
var dataForExcel = await _transferFormService.GetDataExcelTransferForm(generateTransferForm.Where(x => x.T3_Supplier == item.Key).ToList());
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
var fileExcelName = dataForExcel[0].Subject + "-" + DateTime.Now.ToString("yyyy_MM_dd-HH_mm_ss") + ".xlsx";
if (generateTransferForm.Where(x => x.T3_Supplier == item.Key).FirstOrDefault().Is_Release == "Y")
{
fileExcelName = "Released " + fileExcelName;
}
string pathFileExcel = forderSaveFile + fileExcelName;
designer.SetDataSource("result", dataForExcel);
designer.Process();
designer.Workbook.Save(pathFileExcel, SaveFormat.Xlsx);
//End Excel
//Send Mail
await _transferFormService.SendEmail(generateTransferForm.Where(x => x.T3_Supplier == item.Key).FirstOrDefault(), pathFileExcel);
}
return(NoContent());
throw new Exception("Send mail failed ");
}
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();
}
/// <summary>
/// 生成Excel文档并打印
/// </summary>
/// <param name="ChineseName"></param>
/// <param name="TemplateFilePath"></param>
/// <param name="ds"></param>
/// <param name="dt"></param>
/// <returns></returns>
public string GetExportOPenfileByFilePath(string ChineseName, string TemplateFilePath, DataSet ds, DataTable dt, int PrintCount)
{
string strhtml = string.Empty;
WorkbookDesigner designer = new WorkbookDesigner();
designer.Open(TemplateFilePath);
if (ds != null && ds.Tables.Count > 0)
{
for (int i = 0; i < ds.Tables.Count; i++)
{
designer.SetDataSource(ds.Tables[i]);
}
}
if (dt != null && dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
for (int k = 0; k < dt.Columns.Count; k++)
{
designer.SetDataSource(dt.Columns[k].ToString(), dr[k].ToString());
}
}
designer.Process();
//新工作表
string file = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + "_" + ChineseName + ".xlsx";
string strPath = string.Empty;
string strLocalPath = "";
if (HttpContext.Current != null)
{
strPath = HttpContext.Current.Request.PhysicalApplicationPath + "\\print\\" + DateTime.Now.ToString("yyyyMMdd");
strLocalPath += "/print/" + DateTime.Now.ToString("yyyyMMdd") + "/" + file;
}
else
{
strPath = Directory.GetParent(AppDomain.CurrentDomain.BaseDirectory).FullName + "\\print\\" + DateTime.Now.ToString("yyyyMMdd");
strLocalPath += Directory.GetParent(AppDomain.CurrentDomain.BaseDirectory).FullName + "\\print\\" + DateTime.Now.ToString("yyyyMMdd") + "\\" + file;
}
if (!Directory.Exists(strPath))
{
Directory.CreateDirectory(strPath);
}
strPath += "\\" + file;
try
{
designer.Workbook.Save(strPath, FileFormatType.Xlsx);
if (_IsPrint)
{
PrintExcelBySheet(designer.Workbook.Worksheets[0], _PrintName, file, PrintCount);
}
}
catch (Exception ex)
{
ErrorLog.WriteErrorMessage(ErrorLog.LogType.baselog, ex.ToString());
}
return(strLocalPath);
}
/// <summary>
/// 绑定数据源到指定的报表模板中，并导出Excel文件
/// </summary>
/// <param name="templateFile">模板文件路径</param>
/// <param name="saveFileName">保存的文件名称,如a.xls</param>
/// <param name="datasource">待绑定的数据源</param>
public static string ExportWithDataSource(string templateFile, string saveFileName, Dictionary <string, object> datasource)
{
if (!File.Exists(templateFile))
{
throw new ArgumentException(templateFile, string.Format("{0} 文件不存在，", Path.GetFileName(templateFile)));
}
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(templateFile);
foreach (string key in datasource.Keys)
{
designer.SetDataSource(key, datasource[key]);
}
designer.Process();
string saveFile = FileDialogHelper.SaveExcel(saveFileName, "C:\\");
if (!string.IsNullOrEmpty(saveFile))
{
designer.Workbook.Save(saveFile, SaveFormat.Excel97To2003);
}
return(saveFile);
}
/// <summary>
/// 根据键值对列表的替换模板内容，导出Excel文件。
/// </summary>
/// <param name="templateFile">模板文件路径</param>
/// <param name="saveFileName">保存的文件名称,如a.xls</param>
/// <param name="dictReplace">待替换内容和替换值的键值对</param>
public static string ExportWithReplace(string templateFile, string saveFileName, Dictionary <string, string> dictReplace)
{
if (!File.Exists(templateFile))
{
throw new ArgumentException(templateFile, string.Format("{0} 文件不存在，", Path.GetFileName(templateFile)));
}
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(templateFile);
Aspose.Cells.Worksheet worksheet = designer.Workbook.Worksheets[0];
foreach (string name in dictReplace.Keys)
{
worksheet.Replace(name, dictReplace[name]);
}
designer.Process();
string saveFile = FileDialogHelper.SaveExcel(saveFileName, "C:\\");
if (!string.IsNullOrEmpty(saveFile))
{
designer.Workbook.Save(saveFile, SaveFormat.Excel97To2003);
}
return(saveFile);
}
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\";
//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 async Task <IActionResult> ExportExcelMainDetail(KanbanByPoParam kanbanByPoParam)
{
var data = await _kanbanByPoService.GetKanbanByPoDetailMainExcel(kanbanByPoParam);
var path = Path.Combine(_webHostEnvironment.ContentRootPath, "Resources\\Template\\KanbanByPoMainDetail.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(path);
Worksheet ws = designer.Workbook.Worksheets[0];
designer.SetDataSource("result", data);
designer.Process();
// custom style
for (int i = 2; i < data.Count + 2; i++)
{
//Custom cell Kind nếu kind bằng 6 hoặc 7 thì cho background màu vàng
Cell cellKind = ws.Cells["Q" + i];
CustomStyle(ref cellKind);
}
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 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
}
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 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 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);
}
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 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");
}
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 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);
}
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 static WorkbookDesigner getWorkbookDesigner(string fileTemplate)
{
WorkbookDesigner designer = new WorkbookDesigner();
FileInfo fileInfo = new FileInfo(fileTemplate);
if (fileInfo != null && fileInfo.Extension.ToLower() == ".xls")
{
designer.Workbook.Open(fileTemplate, FileFormatType.Excel97To2003);
}
else if (fileInfo != null && fileInfo.Extension.ToLower() == ".csv")
{
designer.Workbook.Open(fileTemplate, FileFormatType.CSV);
}
else
{
try
{
designer.Workbook.Open(fileTemplate, FileFormatType.Excel97To2003);
}
catch
{
//Common.MessageBoxs("Mgs", LanguageManager.GetString("PleaseSaveAsFileToFormatExcel2003"),
//MessageBox.Icon.INFO, string.Empty);
}
}
return designer;
}
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
}
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;
}
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");
}
