static void printTotalRow(DataTable dt, Worksheet ws, int rowCount)
{
int totalTasks = 0;
int totalSubtasks = 0;
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Black);
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
cellStyle.Font.IsBold = true;
cellStyle.IsTextWrapped = true;
foreach (DataRow row in dt.Rows)
{
totalTasks += row.Field <int>("Tasks"); //computer total tasks/subtasks from parent table
totalSubtasks += row.Field <int>("Subtasks");
}
ws.Cells['B' + rowCount.ToString()].PutValue("Total"); //print to worksheet
ws.Cells['B' + rowCount.ToString()].SetStyle(cellStyle);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
cellStyle.Font.IsBold = false;
ws.Cells['A' + rowCount.ToString()].SetStyle(cellStyle);
ws.Cells['C' + rowCount.ToString()].PutValue(totalTasks);
ws.Cells['C' + rowCount.ToString()].SetStyle(cellStyle);
ws.Cells['D' + rowCount.ToString()].PutValue(totalSubtasks);
ws.Cells['D' + rowCount.ToString()].SetStyle(cellStyle);
}
private static int printHeaderRow(Worksheet ws, String[] SelectedColumns, int rowCount, String Worklog, int mergedCellCount, bool alternateColor)
{
int uniA = (int)'A';
int numberRowsPrinted = 0;
String cellName = "A" + rowCount.ToString();
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.IsTextWrapped = true;
cellStyle.Font.IsBold = true;
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
cellStyle.ForegroundColor = Color.Orange;
cellStyle.Pattern = BackgroundType.Solid;
ws.Cells.Merge(rowCount - 1, 0, 1, mergedCellCount);
ws.Cells[cellName].PutValue(Worklog); //print current worklog, where work log is either production, release, or backlog
ws.Cells[cellName].SetStyle(cellStyle);
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
numberRowsPrinted++;
cellStyle.ForegroundColor = (alternateColor) ? Color.LightGreen : Color.LightBlue;
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.IsTextWrapped = true;
for (int i = 0; i < SelectedColumns.Length; i++) //print the attribute names
{
string column = (SelectedColumns[i] == "Task #" || SelectedColumns[i] == "Sub-Task #") ? SelectedColumns[i] : SelectedColumns[i].Replace("Sub-Task", "").Replace("Task", "");
cellName = (char)(uniA + i) + (rowCount + numberRowsPrinted).ToString();
ws.Cells[cellName].PutValue(column);
ws.Cells[cellName].SetStyle(cellStyle);
}
numberRowsPrinted++;
return(numberRowsPrinted);
}
private static int printChildRows(string[] parentColumns, string[] SummaryOverviews, DataRow parentRow, DataTable childTable, int rowCount, int childRowsPrinted, Worksheet ws)
{
//prints rows from the child table, beginning at childRowsPrinted, where the child table attributes are equal to the parent table attributes. Returns the number of rows printed.
//this method gets called for each parent row, and it picks up where it left off last time in the child table.
int newRowsPrinted = 0;
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.IsTextWrapped = true;
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
for (int i = childRowsPrinted; i < childTable.Rows.Count; i++, newRowsPrinted++)
{
DataRow childRow = childTable.Rows[i];
string childSummaries = "";
string childRanking = "";
int value;
foreach (string column in parentColumns) //check if childrow attributes are equal to the current parent row attributes. If equal print the row, else return.
{
if (parentRow.Field <string>(column) != childRow.Field <string>(column))
{
return(newRowsPrinted); //stop print child rows. Return to this spot in the table to print next time.
}
}
foreach (string column in SummaryOverviews)
{
string rankingColumn = String.Concat(column, " Rank");
childSummaries += childRow.Field <string>(column) + ',';
childRanking += childRow.Field <int>(rankingColumn).ToString() + ',';
}
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
childSummaries = childSummaries.TrimEnd(',');
childRanking = childRanking.TrimEnd(',');
ws.Cells['B' + (rowCount + newRowsPrinted).ToString()].PutValue(" " + childSummaries);
ws.Cells['B' + (rowCount + newRowsPrinted).ToString()].SetStyle(cellStyle);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells['A' + (rowCount + newRowsPrinted).ToString()].PutValue(childRanking);
ws.Cells['A' + (rowCount + newRowsPrinted).ToString()].SetStyle(cellStyle);
value = childRow.Field <Int32>("Tasks");
ws.Cells['C' + (rowCount + newRowsPrinted).ToString()].PutValue(value);
ws.Cells['C' + (rowCount + newRowsPrinted).ToString()].SetStyle(cellStyle);
value = childRow.Field <Int32>("Subtasks");
ws.Cells['D' + (rowCount + newRowsPrinted).ToString()].PutValue(value);
ws.Cells['D' + (rowCount + newRowsPrinted).ToString()].SetStyle(cellStyle);
}
return(newRowsPrinted);
}
private static void printParentRows(string[] parentColumns, DataRow parentRow, int rowCount, Worksheet ws)
{
//prints a row from the parent table.
string parentSummaries = "";
string parentRanking = "";
int value;
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.Pattern = BackgroundType.Solid;
cellStyle.ForegroundColor = Color.LightGray;
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
cellStyle.IsTextWrapped = true;
foreach (string column in parentColumns)
{
string rankingColumn = String.Concat(column, " Rank");
parentSummaries += parentRow.Field <string>(column) + ',';
parentRanking += parentRow.Field <int>(rankingColumn).ToString() + ',';
}
parentSummaries = parentSummaries.TrimEnd(',');
parentRanking = parentRanking.TrimEnd(',');
ws.Cells['B' + rowCount.ToString()].PutValue(parentSummaries);
ws.Cells['B' + rowCount.ToString()].SetStyle(cellStyle);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells['A' + rowCount.ToString()].PutValue(parentRanking);
ws.Cells['A' + rowCount.ToString()].SetStyle(cellStyle);
value = parentRow.Field <Int32>("Tasks");
ws.Cells['C' + rowCount.ToString()].PutValue(value);
ws.Cells['C' + rowCount.ToString()].SetStyle(cellStyle);
value = parentRow.Field <Int32>("Subtasks");
ws.Cells['D' + rowCount.ToString()].PutValue(value);
ws.Cells['D' + rowCount.ToString()].SetStyle(cellStyle);
return;
}
private static int printSelectedColumns(string[] SelectColumns, int rowCount, DataRow row, Worksheet ws)
{
int uniA = (int)'A'; //unicode A value. Used to iterate up through 'A'-'Z'.
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
cellStyle.IsTextWrapped = true;
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
for (int i = 0; i < SelectColumns.Length; i++)
{
string column = SelectColumns[i]; //get column name
string value = row.Field <string>(column); //get the value of that column
string cellName = (char)(uniA + i) + (rowCount).ToString(); //This casts the unicode value back to a character and appends a number 1 - N
ws.Cells[cellName].PutValue(value); //print value
ws.Cells[cellName].SetStyle(cellStyle);
}
return(1);
}
private static void exportResourceOverview(DataTable dt, Worksheet ws, string worksheetName)
{
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
cellStyle.Font.IsBold = true;
cellStyle.Font.Underline = FontUnderlineType.Single;
ws.Cells.ImportDataTable(dt, true, "A3");
cellStyle.IsTextWrapped = true;
cellStyle.Font.Size = cellStyle.Font.Size + 2;
ws.Cells.Merge(0, 0, 1, 3);
ws.Cells["A1"].PutValue(worksheetName);
ws.Cells["A1"].SetStyle(cellStyle);
ws.AutoFitRow(0, 0, 0);
cellStyle.Font.Size = cellStyle.Font.Size - 2;
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
cellStyle.Font.Underline = FontUnderlineType.None;
for (int i = 0; i < dt.Columns.Count; i++) //header row
{
ws.Cells[2, i].SetStyle(cellStyle);
}
cellStyle.Font.IsBold = false;
for (int i = 3; i < dt.Rows.Count + 2; i++) //body
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0) //left column is left alligned.
{
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
ws.Cells[i, j].SetStyle(cellStyle);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
}
else
{
ws.Cells[i, j].SetStyle(cellStyle);
}
}
}
cellStyle.Font.IsBold = true;
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Black);
for (int i = 0; i < dt.Columns.Count; i++) //Total All row
{
if (i == 0)
{
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
ws.Cells[dt.Rows.Count + 2, i].SetStyle(cellStyle);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
}
else
{
ws.Cells[dt.Rows.Count + 2, i].SetStyle(cellStyle);
}
}
ws.AutoFitColumns();
ws.Name = worksheetName;
return;
}
private static void exportSummaryOverviews(DataTable parentTable, DataTable childTable, Worksheet ws, String[] SummaryOverview1 = null, String[] SummaryOverview2 = null)
{
int rowCount = 5; //holds the integer value of the current row in the document to write too.
int childRowsPrinted = 0;
string headerRow = "";
string[] parentColumns = object.ReferenceEquals(SummaryOverview1, null) ? SummaryOverview2 : SummaryOverview1; //it is possible for there to be only one summary level.
bool level1 = !Object.ReferenceEquals(SummaryOverview1, null);
bool level2 = !Object.ReferenceEquals(SummaryOverview2, null);
Aspose.Cells.Style cellStyle = new Aspose.Cells.Style();
cellStyle.HorizontalAlignment = TextAlignmentType.Left;
cellStyle.Font.Underline = FontUnderlineType.Single;
cellStyle.Font.IsBold = true;
cellStyle.IsTextWrapped = true;
cellStyle.Font.Name = "Calibri";
cellStyle.Font.Size = 10;
cellStyle.Font.Size = cellStyle.Font.Size + 2;
if (level1) //if there is a level 1 summary, add the column names to the header row
{
headerRow = String.Join(",", SummaryOverview1);
}
if (level1 && level2) //if there is both a level one and level two summary, add a \ to deliminate the too.
{
headerRow += " \\ ";
}
if (level2) //if there is a level 2 summary, add the column names to the header row.
{
headerRow += String.Join(",", SummaryOverview2);
}
if (headerRow.Length > 90) //if the header row is really long then you have to manually set the column widths so everthing fits.
{
ws.Cells.SetColumnWidthInch(0, 1);
ws.Cells.SetColumnWidthInch(1, 6);
ws.Cells.SetColumnWidthInch(2, 1);
ws.Cells.SetColumnWidthInch(3, 1);
}
ws.Cells.Merge(0, 0, 1, 4);
ws.Cells["A1"].PutValue("Summary Overview");
ws.Cells["A1"].SetStyle(cellStyle);
ws.AutoFitRow(0, 0, 0);
cellStyle.Font.Size = cellStyle.Font.Size - 2;
ws.Cells.Merge(2, 0, 1, 4);
ws.Cells["A3"].PutValue(headerRow);
ws.Cells["A3"].SetStyle(cellStyle);
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
cellStyle.HorizontalAlignment = TextAlignmentType.Center;
ws.Cells["A4"].PutValue("Rank");
ws.Cells["A4"].SetStyle(cellStyle);
ws.Cells["B4"].PutValue(headerRow); //print table header row
ws.Cells["B4"].SetStyle(cellStyle);
ws.Cells["C4"].PutValue("Tasks");
ws.Cells["C4"].SetStyle(cellStyle);
ws.Cells["D4"].PutValue("Sub Tasks");
ws.Cells["D4"].SetStyle(cellStyle);
foreach (DataRow parentRow in parentTable.Rows)
{
printParentRows(parentColumns, parentRow, rowCount, ws);
rowCount++;
if (!Object.ReferenceEquals(childTable, null)) //if there is a child table, print the child rows.
{
int newRowsPrinted = 0;
newRowsPrinted = printChildRows(parentColumns, SummaryOverview2, parentRow, childTable, rowCount, childRowsPrinted, ws);
rowCount += newRowsPrinted;
childRowsPrinted += newRowsPrinted;
}
}
if (headerRow.Length > 90)
{
ws.AutoFitRows();
}
else
{
ws.AutoFitColumns();
}
printTotalRow(parentTable, ws, rowCount);
ws.Name = "Summary Overviews";
return;
}
public void CreateFile(Window owner)
{
try
{
Workbook wb = new Workbook();
wb.Worksheets[0].PageSetup.Orientation = PageOrientationType.Landscape;
DataTable data = GetData();
var column = 66; // Letter B in ASCII
foreach (DataRow dr in data.Rows)
{
char columnChar = (char)column;
Cell header = wb.Worksheets[0].Cells[string.Format("{0}{1}", columnChar, 2)];
Aspose.Cells.Style objstyle = header.GetStyle();
// Specify the angle of rotation of the text.
objstyle.RotationAngle = 60;
objstyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
objstyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
objstyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
objstyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
header.PutValue(dr[0]);
objstyle.Pattern = BackgroundType.Solid;
header.SetStyle(objstyle);
Cell cell = wb.Worksheets[0].Cells[string.Format("{0}{1}", columnChar, 3)];
Aspose.Cells.Style cellStyle = cell.GetStyle();
cellStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
cellStyle.SetBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
cellStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.Black);
cellStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.Black);
cell.PutValue(dr[1]);
cell.SetStyle(cellStyle);
column++;
}
for (var i = 1; i <= data.Rows.Count; i++)
{
wb.Worksheets[0].Cells.SetColumnWidth(i, 4);
}
Cell cosignatureCell = wb.Worksheets[0].Cells[string.Format("{0}{1}", (char)((column + 1)), 2)];
cosignatureCell.PutValue("COSIGNATURE FOR WASTE");
Aspose.Cells.Style cosignatureStyle = cosignatureCell.GetStyle();
cosignatureStyle.IsTextWrapped = true;
cosignatureCell.SetStyle(cosignatureStyle);
wb.Worksheets[0].AutoFitRow(1);
//Save the Shared Workbook
wb.Save(FileName);
MessageBox.Show(owner, "File has been created successfully.", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
PrintFile(owner, FileName);
}
catch (Exception ex)
{
MessageBox.Show(owner, string.Format("An error has occurred while creating document: {0}", ex.Message), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
