private void WriteQuestionIntoExcel(DIExcel excelFile, Question xmlQuestion, ref int RowIndex)
{
int DataValueRowIndex = RowIndex + 1;
int BoldStartIndex = -1;
int BoldEndIndex = -1;
int ItalicStartIndex = -1;
int ItalicEndIndex = -1;
string BoldStartTag = "<b>";
string ItalicStartTag = "<i>";
string BoldEndTag = "</b>";
string ItalicEndTag = "</i>";
string QuestionText = xmlQuestion.Text;
//set question no and question text
excelFile.SetCellValue(Constants.SheetIndex, RowIndex, Constants.QuestionNoColumnIndex, xmlQuestion.No);
excelFile.SetColumnWidth(Constants.SheetIndex, 6, RowIndex, Constants.QuestionNoColumnIndex, RowIndex, Constants.QuestionNoColumnIndex);
//replace <br> with new line character and remove enter key
QuestionText = QuestionText.Replace(Char.ConvertFromUtf32(13), string.Empty);
QuestionText = QuestionText.Replace("\n", string.Empty);
QuestionText = QuestionText.Replace(Microsoft.VisualBasic.ControlChars.CrLf, string.Empty);
QuestionText = QuestionText.Replace(Microsoft.VisualBasic.ControlChars.Lf.ToString(), string.Empty);
QuestionText = QuestionText.Replace("<br>", Microsoft.VisualBasic.ControlChars.NewLine);
//apply bold tag
BoldStartIndex = QuestionText.IndexOf(BoldStartTag);
if (BoldStartIndex >= 0)
{
QuestionText = QuestionText.Replace(BoldStartTag, string.Empty);
BoldEndIndex = QuestionText.IndexOf(BoldEndTag);
QuestionText = QuestionText.Replace(BoldEndTag, string.Empty);
}
//apply italic tag
ItalicStartIndex = QuestionText.IndexOf(ItalicStartTag);
if (ItalicStartIndex >= 0)
{
QuestionText = QuestionText.Replace(ItalicStartTag, string.Empty);
ItalicEndIndex = QuestionText.IndexOf(ItalicEndTag);
QuestionText = QuestionText.Replace(ItalicEndTag, string.Empty);
}
//set question text
QuestionText.Replace(ItalicStartTag, string.Empty);
excelFile.SetCellValue(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, QuestionText);
//set bold
if (BoldStartIndex >= 0 & BoldEndIndex >= 0)
{
excelFile.GetCellCharacters(Constants.SheetIndex, Constants.QuestionTextColumnIndex, RowIndex, BoldStartIndex, BoldEndIndex - BoldStartIndex).Font.Bold = true;
}
//set italic
if (ItalicStartIndex >= 0 & ItalicEndIndex >= 0)
{
excelFile.GetCellCharacters(Constants.SheetIndex, Constants.QuestionTextColumnIndex, RowIndex, ItalicStartIndex, ItalicEndIndex - ItalicStartIndex).Font.Italic = true;
}
//set layout of question text cell and question no cell
//excelFile.MergeCells(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, RowIndex, Constants.QuestionTextColumnIndex + 6);
//excelFile.SetHorizontalAlignment(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, SpreadsheetGear.HAlign.Justify);
//excelFile.SetVerticalAlignment(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex, SpreadsheetGear.VAlign.Justify);
//excelFile.GetCellFont(Constants.SheetIndex, RowIndex, Constants.QuestionTextColumnIndex).Bold = true;
excelFile.GetCellFont(Constants.SheetIndex, RowIndex, Constants.QuestionNoColumnIndex).Bold = true;
excelFile.AutoFitColumn(Constants.SheetIndex, Constants.QuestionNoColumnIndex);
this.WriteDataValue(excelFile, xmlQuestion, ref DataValueRowIndex);
RowIndex = DataValueRowIndex + 2;
}
private void WriteHeader(DIExcel excelFile, int rowIndex, string headerText)
{
excelFile.SetCellValue(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex, headerText);
excelFile.GetCellFont(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex).Bold = true;
excelFile.GetCellFont(Constants.SheetIndex, rowIndex, Constants.HeaderTextColumnIndex).Size = 12;
}
private void WriteValueInCell(DIExcel Workbook, string value, int rowNum, int colNum, int fontSize, bool boldFlag, double columnWidth, double rowHeight, int Sheetindex)
{
IFont WorkbookFont = null;
IWorksheet WorkbookSheet = null;
WorkbookFont = Workbook.GetCellFont(Sheetindex, rowNum, colNum);
WorkbookSheet = Workbook.GetWorksheet(Sheetindex);
WorkbookFont.Size = fontSize;
WorkbookFont.Bold = boldFlag;
WorkbookSheet.Cells[rowNum, colNum].Value = value;
WorkbookSheet.Cells[rowNum, colNum].WrapText = true;
WorkbookSheet.Cells[rowNum, colNum].ColumnWidth = columnWidth;
if (rowHeight != 0)
{
WorkbookSheet.Cells[rowNum, colNum].RowHeight = rowHeight;
}
}
private void WriteDataValue(DIExcel excelFile, Question xmlQuestion, ref int dataValueRowIndex)
{
string[] SelectedValues = null;
int Index = 0;
switch (xmlQuestion.AnswerType)
{
case AnswerType.TB:
case AnswerType.TBN:
case AnswerType.RB:
case AnswerType.CB:
case AnswerType.DateType:
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, xmlQuestion.DataValue);
break;
case AnswerType.SCB:
case AnswerType.SRB:
break;
case AnswerType.CH:
SelectedValues = DICommon.SplitStringNIncludeEmpyValue(xmlQuestion.DataValue, ",");
foreach (AnswerTypeOption Option in xmlQuestion.Options)
{
excelFile.GetCellFont(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex).Name = "Wingdings";
if (SelectedValues[Index] == "1")
{
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, Char.ConvertFromUtf32(252));
}
else
{
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex, Char.ConvertFromUtf32(251));
}
excelFile.SetCellValue(Constants.SheetIndex, dataValueRowIndex, Constants.DataValueColumnIndex + 1, Option.Text);
dataValueRowIndex += 1;
Index += 1;
}
break;
case AnswerType.GridType:
int StartRowIndex = dataValueRowIndex;
int EndRowIndex;
int EndColIndex;
excelFile.LoadDataTableIntoSheet(dataValueRowIndex, Constants.DataValueColumnIndex, xmlQuestion.GridTable, Constants.SheetIndex, true);
dataValueRowIndex += xmlQuestion.GridTable.Rows.Count;
EndRowIndex = dataValueRowIndex - 1;
EndColIndex = Constants.DataValueColumnIndex + xmlQuestion.GridTable.Columns.Count - 1;
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.InsideHorizontal);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.InsideVertical);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeBottom);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeTop);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeLeft);
excelFile.SetRangeBorder(Constants.SheetIndex, StartRowIndex, Constants.DataValueColumnIndex, EndRowIndex, EndColIndex,
SpreadsheetGear.LineStyle.Continous, SpreadsheetGear.BorderWeight.Thin, System.Drawing.Color.Black, SpreadsheetGear.BordersIndex.EdgeRight);
break;
case AnswerType.Calculate:
break;
case AnswerType.Aggregate:
break;
default:
break;
}
}
/// <summary>
/// Create DBVersion Sheet of Summary Report
/// </summary>
/// <param name="excelFile">Excel File</param>
internal override void GenerateSheet(ref DIExcel excelFile)
{
// -- Create DBVersion Sheet
int sheetNo = this.CreateSheet(ref excelFile, this.ColumnHeader[DSRColumnsHeader.DATABASE] + " " + this.ColumnHeader[DSRColumnsHeader.VERSION]);
DataTable Table = null;
// -- sheet content
excelFile.SetCellValue(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex, this.ColumnHeader[DSRColumnsHeader.DATABASE] + " " + this.ColumnHeader[DSRColumnsHeader.VERSION]);
excelFile.GetCellFont(sheetNo, Constants.HeaderRowIndex, Constants.HeaderColIndex).Size = Constants.SheetsLayout.HeaderFontSize;
Table = this.GetDBVersionTable();
excelFile.LoadDataTableIntoSheet(Constants.Sheet.DBVersion.DetailsRowIndex, Constants.HeaderColIndex, Table, sheetNo, false);
int LastRow = Constants.Sheet.DBVersion.DetailsRowIndex + Table.Rows.Count;
// -- Apply Font Settings
this.ApplyFontSettings(ref excelFile, sheetNo, Constants.Sheet.DBVersion.DetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.DBVersion.LastColIndex, true);
// -- Set Column Width
excelFile.SetColumnWidth(sheetNo, Constants.SheetsLayout.TimePeriodColWidth , Constants.Sheet.DBVersion.DetailsRowIndex, Constants.Sheet.DBVersion.ColValueIndex, LastRow, Constants.Sheet.DBVersion.ColValueIndex);
excelFile.SetHorizontalAlignment(sheetNo, Constants.Sheet.DBVersion.DetailsRowIndex, Constants.HeaderColIndex, LastRow, Constants.Sheet.DBVersion.ColValueIndex, SpreadsheetGear.HAlign.Left);
}
