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;
}
/// <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);
}
