Hi, I’m trying to set data source and process each worksheet for each record in my “Group” mode.
Here is what I’ve done so far:
using Bravo.Core;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using c = Aspose.Cells;
namespace ExportTemplate
{
public class ExcelExport
{
private DataSet dataSource = null;
private c.Workbook _book = null;
private c.Worksheet _worksheet = null;
private string zGroupName = null;
private c.WorkbookDesigner _designer = null;
public ExcelExport(DataSet pDs)
{
dataSource = pDs ?? throw new ArgumentNullException(nameof(pDs));
}
public void beginExport(string pzTempFilePath, string pzGroupName, string pzPassword = null)
{
if (string.IsNullOrEmpty(pzTempFilePath))
return;
var _loadOpts = new c.LoadOptions { MemorySetting = c.MemorySetting.MemoryPreference, Password = pzPassword };
if (_book == null)
_book = new c.Workbook(pzTempFilePath, _loadOpts);
_worksheet = _book.Worksheets[0];
zGroupName = pzGroupName;
if (!string.IsNullOrEmpty(pzGroupName) && dataSource != null && dataSource.Tables.Count > 0)
{
if (!dataSource.Tables.Contains("Table1"))
throw new NullReferenceException("Table1 does not exists.");
if (!dataSource.Tables["Table1"].Columns.Contains(pzGroupName))
throw new NullReferenceException(string.Format("Group '{0}' does not exists.", pzGroupName));
};
}
public void execute()
{
if (dataSource == null || dataSource.Tables.Count < 1)
return;
c.FindOptions _findOptions = new c.FindOptions();
_findOptions.LookInType = c.LookInType.Values;
_findOptions.LookAtType = c.LookAtType.StartWith;
_findOptions.SearchBackward = false;
_findOptions.SeachOrderByRows = true;
if (_designer == null)
_designer = new c.WorkbookDesigner();
_designer.Workbook = _book;
if (!string.IsNullOrEmpty(zGroupName))
{
Type _dataType = null;
if (dataSource.Tables["Table1"].Columns.Contains(zGroupName))
_dataType = dataSource.Tables["Table1"].Columns[zGroupName].DataType;
var uniqueGroupValues = dataSource.Tables["Table1"].AsEnumerable()
.Select(row => GetTypedValue(row[zGroupName], _dataType))
.Distinct();
foreach (var groupValue in uniqueGroupValues)
createAndProcessSheet(groupValue, _findOptions);
}
else
{
createAndProcessSheet(null, _findOptions);
}
}
private object GetTypedValue(object pValue, Type pTargetType)
{
if (pTargetType == null)
return null;
try
{
return Convert.ChangeType(pValue, pTargetType);
}
catch
{
return null;
}
}
private void createAndProcessSheet(object pzGroupValue, c.FindOptions pFindOptions)
{
var _newSheet = pzGroupValue != null ? _book.Worksheets.Add(pzGroupValue.ToString()) : _worksheet;
if (!string.IsNullOrEmpty(pzGroupValue?.ToString()))
_newSheet.Copy(_worksheet);
var _newSheetCell = _newSheet.Cells.Find("{=", null, pFindOptions);
string _zTableName = null;
while (_newSheetCell != null)
{
string _zCellValue = _newSheetCell.StringValue;
if (_zCellValue.StartsWith("{=") && _zCellValue.EndsWith("}"))
{
string _zFieldName = _zCellValue.TrimStart('{').TrimEnd('}').Substring(1);
bool _bTableNameProvided = _zFieldName.Contains(".");
if (_bTableNameProvided)
{
var parts = _zFieldName.Split('.');
_zTableName = parts[0];
_zFieldName = parts[1];
}
else
{
_zTableName = "Table1";
}
string _zNewMarker = "&=" + _zTableName + "." + _zFieldName;
if (!string.IsNullOrEmpty(pzGroupValue?.ToString()))
_zNewMarker = string.Format("{0}(group:normal)", _zNewMarker);
_newSheetCell.PutValue(_zNewMarker);
Trace.WriteLine(_zNewMarker);
}
_newSheetCell = _newSheet.Cells.Find("{=", null, pFindOptions);
}
if (!string.IsNullOrEmpty(zGroupName))
{
var _singleRowTable = new DataTable();
_singleRowTable.TableName = _zTableName;
foreach (DataRow row in dataSource.Tables[_zTableName].Rows)
{
if (GetTypedValue(row[zGroupName], pzGroupValue?.GetType()).Equals(pzGroupValue))
_singleRowTable.ImportRow(row);
}
_designer.SetDataSource(_singleRowTable);
// Processing specific sheet by its index
_designer.Process(_newSheet.Index, false);
_designer.ClearDataSource();
}
else
{
_designer.SetDataSource(dataSource);
_designer.Process();
}
}
public string save(string pzFileName, bool pbOverwriteExistingFile, string pzFilePassword = null)
{
if (_book == null || _worksheet == null || string.IsNullOrEmpty(pzFileName))
throw new NullReferenceException("There is no data to export.");
var _zExt = Path.GetExtension(pzFileName).ToLower().TrimStart('.');
var _zTempPath = pzFileName;
var _zDir = Path.GetDirectoryName(pzFileName);
var _zFileName = Path.GetFileNameWithoutExtension(pzFileName);
if (!pbOverwriteExistingFile)
{
var _nExisted = 1;
while (Directory.Exists(_zTempPath) || File.Exists(_zTempPath))
_zTempPath = Path.Combine(_zDir, string.Format(
BravoCulture.ci, "{0} ({1:G}).{2}", _zFileName, ++_nExisted, _zExt));
}
pzFileName = _zTempPath;
var _sf = c.FileFormatUtil.ExtensionToSaveFormat(_zExt);
if (_sf != c.SaveFormat.Xlsx && _sf != c.SaveFormat.Csv)
return pzFileName;
if (!string.IsNullOrEmpty(pzFilePassword))
_book.Settings.Password = pzFilePassword;
_book.Save(pzFileName, _sf);
Trace.WriteLine($"_____Saved file to: {pzFileName}");
return pzFileName;
}
}
}
//How I call it:
string _zPath = @“D:\.NETPROJECT\.NET PROJECT\SmartMakerAsposeCells\template.xlsx”;
var _ds = new DataSet();
var _tb1 = new DataTable();
_tb1.TableName = “Table1”;
_tb1.Columns.Add(“Id”, typeof(int));
_tb1.Columns.Add(“Name”, typeof(string));
_tb1.Columns.Add(“Description”, typeof(string));
_tb1.Columns.Add(“BirthDay”, typeof(DateTime));
_tb1.Columns.Add(“Weight”, typeof(double));
_tb1.Rows.Add(1, “A”, “ABC”, DateTime.Now,70);
_tb1.Rows.Add(1, “A”, “XYZ”, DateTime.Now, 75);
_tb1.Rows.Add(2, “B”, “DEF”, DateTime.Now, 60);
_tb1.Rows.Add(2, “B”, “OIU”, DateTime.Now, 65);
_tb1.DefaultView.Sort = “Id ASC”;
_tb1 = _tb1.DefaultView.ToTable();
_ds.Tables.Add(_tb1);
var _excel = new ExcelExport(_ds);
_excel.beginExport(_zPath, “Id”);
_excel.execute();
_excel.save(“result.xlsx”,true);
Template file: Template file
Result: Result file
Is something incorrect here ? result.xlsx doesn’t have any value that matched. Please help me.