I have been attempting to generate an excel spreadsheet in a very specific format from a user request. For each group of data (this is by person), there needs to be a group of sub "counts" of the Type column in a vertical layout.
I am not sure if this is possible. I can group my records using Smart Markers.
I am attaching a sample layout.
Any suggestions?
Thank you.
Hi,
Thank you for Considering Aspose.
Well, Currently Smart Marker does not provide exact functionality as per your requirement but I think you should set the formula “=COUNTIF(D4:D6,D4)” in the Cell D8.
Check the following codes with the attached file. Your can modify the code as per your own requirement,
internal int CellsTest()
{
Workbook workbook = new Workbook();
// CreateAsposeCellsFile1(@"F:\FileTemp\dest.xls");
workbook.Open(@"F:\FileTemp\report1.xls");
Cells cells = workbook.Worksheets[0].Cells;
int startRow = 3;
int column = 3;//Column D
int endRow = cells.MaxDataRow;
Hashtable singles = new Hashtable();
for (int row = startRow; row <= endRow; row++)
{
int index = cells.CheckExistence(row, column);
if (index == -1)
{
endRow += this.InsertCountIf(cells, column, ref startRow, ref row, singles);
}
else
{
Cell cell = cells[index];
if (cell.Type == CellValueType.IsNull)
{
endRow += this.InsertCountIf(cells, column, ref startRow, ref row, singles);
}
else
{
string stringValue = cell.StringValue;
if (!singles.ContainsKey(stringValue))
{
singles.Add(stringValue, cell.Name);
}
}
}
}
if (singles.Count > 0)
{
int row = endRow + 1;
endRow += this.InsertCountIf(cells, column, ref startRow, ref row, singles);
}
workbook.Save(@"F:\FileTemp\dest.xls");
}
internal int InsertCountIf(Cells cells, int column,ref int startRow,ref int row, Hashtable singles)
{
if (singles.Count == 0)
{
startRow = row + 1;
return 0;
}
cells.InsertRows(row, singles.Count);
string range = CellsHelper.CellIndexToName(startRow, column) + ":" +
CellsHelper.CellIndexToName(row - 1, column);
row ++;
foreach (string cellName in singles.Values)
{
cells[row ++,column].Formula = "=COUNTIF("+ range+","+cellName+")";
}
int count = singles.Count;
singles.Clear();
startRow = row;
row--;
return count;
}
Thank you & Best Regards,