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 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 }
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")); }
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 }
/// <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 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); }
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"); }
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"); }
/// <summary> /// ImpExcel /// </summary> private void ImpExcel() { string sql = string.Empty; 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); } }
/// <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(); }
static void Main(string[] args) { string FilePath = @"..\..\..\Sample Files\"; string FileName = FilePath + "Using Nested Object.xlsx"; //Initialize WorkbookDesigner object WorkbookDesigner designer = new WorkbookDesigner(); //Load the template file designer.Workbook = new Workbook(FileName); //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(FileName); }
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); } }
public byte[] CommonExportReportWithATitle(object data, string templateName, string aTitle) { 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); Worksheet ws = designer.Workbook.Worksheets[0]; designer.SetDataSource("title", aTitle); designer.SetDataSource("result", data); 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 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 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 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 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 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 }
/// <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(); }
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(); } }
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 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); // 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 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"); }