private void MakeGroupData(DataTable EnrollRecordTable, UDT.Hierarchy Hierarchy, string WorksheetName, Dictionary<ReportHelper.CellObject, ReportHelper.CellStyle> dicCellStyles)
{
DataRow pRow = EnrollRecordTable.NewRow();
pRow["No"] = NoToChineseNo(Hierarchy.DisplayOrder) + "、" + Hierarchy.Title;
if (Hierarchy.DisplayOrder == 1)
{
pRow["Option1AnswerCount"] = "(1)";
pRow["Option2AnswerCount"] = "(2)";
pRow["Option3AnswerCount"] = "(3)";
pRow["Option4AnswerCount"] = "(4)";
pRow["Option5AnswerCount"] = "(5)";
pRow["Option6AnswerCount"] = "(6)";
//pRow["Score"] = "評鑑值";
}
EnrollRecordTable.Rows.Add(pRow);
CellObject co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
CellStyle cs = new CellStyle();
// 題目群組首 Style：標楷體、粗、12號字、水平靠左、垂直靠上
cs.SetRowHeight(21.75).SetFontName("標楷體").SetFontSize(12).SetFontBold(true).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Left).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top);
dicCellStyles.Add(co, cs);
// 答案選項標題 Style：TimesNewRoman、10號字、水平置中、垂直靠下
for (int i = 2; i <= 7; i++)
{
co = new CellObject(EnrollRecordTable.Rows.Count - 1, i, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("Times New Roman").SetFontSize(10).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom);
dicCellStyles.Add(co, cs);
}
//// 評鑑值標題
//co = new CellObject(EnrollRecordTable.Rows.Count - 1, 8, EnrollRecordTable.TableName, "DataSection", WorksheetName);
//cs = new CellStyle();
//cs.SetFontName("標楷體").SetFontSize(12).SetFontBold(true).SetFontHorizontalAlignment(HorizontalAlignment.Center).SetFontVerticalAlignment(HorizontalAlignment.Right);
//dicCellStyles.Add(co, cs);
}
private void MakeDetailFourData(DataTable EnrollRecordTable, UDT.Hierarchy Hierarchy, List<XElement> xElements, string WorksheetName, Dictionary<ReportHelper.CellObject, ReportHelper.CellStyle> dicCellStyles)
{
if (Hierarchy.DisplayOrder != 4)
return;
foreach (XElement xElement in xElements)
{
DataRow pRow = EnrollRecordTable.NewRow();
pRow["No"] = xElement.Attribute("No").Value + ".";
pRow["Content"] = HttpUtility.HtmlDecode(xElement.Attribute("Content").Value);
EnrollRecordTable.Rows.Add(pRow);
CellObject co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
CellStyle cs = new CellStyle();
// 題號 Style：Times New Roman、12號字、垂直靠上，水平置中
cs.SetRowHeight(16.5).SetFontName("Times New Roman").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 1, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
// 題目 Style：粗體、底線、標楷體、12號字、垂直靠上，水平靠左
cs.SetFontBold(true).SetFontUnderline(true).SetFontName("標楷體").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Left);
dicCellStyles.Add(co, cs);
IEnumerable<XElement> xAnswers = xElement.Descendants("Answer");
int k = 0;
foreach (XElement xElement_Answer in xAnswers)
{
if (string.IsNullOrWhiteSpace(xElement_Answer.Value))
continue;
DataRow Row = EnrollRecordTable.NewRow();
k++;
Row["No"] = xElement.Attribute("No").Value + "-" + k;
Row["Content"] = HttpUtility.HtmlDecode(xElement_Answer.Value);
EnrollRecordTable.Rows.Add(Row);
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
// 回答項次 Style：Times New Roman、11號字、垂直靠上，水平置中
cs.SetFontName("Times New Roman").SetFontSize(11).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 1, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
// 回答內容 Style：新細明體、12號字、垂直靠上，水平靠左、自動調整列高
cs.SetFontName("新細明體").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Left).SetAutoFitRow(true).Merge(1, 8);
dicCellStyles.Add(co, cs);
}
// 空一行
pRow = EnrollRecordTable.NewRow();
EnrollRecordTable.Rows.Add(pRow);
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetRowHeight(16.5);
dicCellStyles.Add(co, cs);
}
}
private void MakeDetailThreeData(DataTable EnrollRecordTable, UDT.Hierarchy Hierarchy, List<XElement> xElements, string WorksheetName, Dictionary<ReportHelper.CellObject, ReportHelper.CellStyle> dicCellStyles)
{
if (Hierarchy.DisplayOrder != 3)
return;
foreach (XElement xElement in xElements)
{
DataRow pRow = EnrollRecordTable.NewRow();
pRow["No"] = xElement.Attribute("No").Value + ".";
pRow["Content"] = HttpUtility.HtmlDecode(xElement.Attribute("Content").Value);
IEnumerable<XElement> Options = xElement.Descendants("Option");
if (Options.Count() > 0) Options = Options.OrderBy(x => int.Parse(x.Attribute("No").Value));
int j = 0;
foreach (XElement xOption in Options)
{
pRow["Option" + xOption.Attribute("No").Value + "AnswerCount"] = xOption.Attribute("AnswerCount").Value;
}
if (xElement.Attribute("Score") != null)
pRow["Score"] = xElement.Attribute("Score").Value;
EnrollRecordTable.Rows.Add(pRow);
CellObject co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
CellStyle cs = new CellStyle();
// 題號 Style：Times New Roman、12號字、垂直靠上，水平置中
cs.SetRowHeight(18.75).SetFontName("Times New Roman").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 1, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
// 題目 Style：新細明體、12號字、垂直靠上，水平靠左
cs.SetFontName("新細明體").SetFontSize(12).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Left).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetAutoFitRow(true);
dicCellStyles.Add(co, cs);
j = 2;
foreach (XElement xOption in Options)
{
// 答題數 Style：新細明體、12號字、水平置中、垂直靠下
co = new CellObject(EnrollRecordTable.Rows.Count - 1, j, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
j++;
}
// 評鑑值 Style：新細明體、12號字、垂直靠下、水平置中
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 8, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
}
// 空一行
DataRow Row = EnrollRecordTable.NewRow();
EnrollRecordTable.Rows.Add(Row);
CellObject cc = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
CellStyle ss = new CellStyle();
ss.SetRowHeight(11);
dicCellStyles.Add(cc, ss);
}
private void MakeDetailTwoData(DataTable EnrollRecordTable, UDT.Hierarchy Hierarchy, List<XElement> xElements, IEnumerable<XElement> xStatisticsGroup, string WorksheetName, Dictionary<ReportHelper.CellObject, ReportHelper.CellStyle> dicCellStyles, Dictionary<string, Dictionary<string, Color>> dicQuestionBackgroundColor, Dictionary<string, Dictionary<string, Color>> dicEvaluationBackgroundColor, string SurveyID)
{
if (Hierarchy.DisplayOrder != 2)
return;
if (xElements.Count == 0) return;
IEnumerable<XElement> Options = xElements.ElementAt(0).Descendants("Option");
if (Options.Count() > 0) Options = Options.OrderBy(x => int.Parse(x.Attribute("No").Value));
// 列舉答案選項
DataRow pRow = EnrollRecordTable.NewRow();
string option_content = string.Empty;
int j = 0;
foreach (XElement xOption in Options)
{
option_content += "(" + xOption.Attribute("No").Value + ")" + HttpUtility.HtmlDecode(xOption.Attribute("Content").Value) + " ";
}
pRow["Content"] = option_content;
pRow["Score"] = "評鑑值";
EnrollRecordTable.Rows.Add(pRow);
CellObject co = new CellObject(EnrollRecordTable.Rows.Count - 1, 1, EnrollRecordTable.TableName, "DataSection", WorksheetName);
CellStyle cs = new CellStyle();
// 答題選項 Style：粗體、標楷體、12號字、垂直靠下
cs.SetRowHeight(18).SetFontBold(true).SetFontName("標楷體").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom);
dicCellStyles.Add(co, cs);
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 8, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
// 評鑑值 Style：粗體、標楷體、12號字、水平置中、垂直靠下
cs.SetFontName("標楷體").SetFontBold(true).SetFontSize(12).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom);
dicCellStyles.Add(co, cs);
// 列舉答案題號
pRow = EnrollRecordTable.NewRow();
foreach (XElement xOption in Options)
{
pRow["Option" + xOption.Attribute("No").Value + "AnswerCount"] = "(" + xOption.Attribute("No").Value + ")";
}
EnrollRecordTable.Rows.Add(pRow);
// 答案項次 Style：Times New Roman、10號字、水平置中、垂直靠下
j = 2;
foreach (XElement xOption in Options)
{
co = new CellObject(EnrollRecordTable.Rows.Count - 1, j, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetRowHeight(14.25).SetFontName("Times New Roman").SetFontSize(10).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
j++;
}
// 列舉學生做答
foreach (XElement xElement in xElements)
{
if (xElement.Attribute("IsCase").Value == "是")
continue;
pRow = EnrollRecordTable.NewRow();
pRow["No"] = xElement.Attribute("No").Value + ".";
pRow["Content"] = HttpUtility.HtmlDecode(xElement.Attribute("Content").Value);
foreach (XElement xOption in xElement.Descendants("Option"))
{
pRow["Option" + xOption.Attribute("No").Value + "AnswerCount"] = xOption.Attribute("AnswerCount").Value;
}
if (xElement.Attribute("Score") != null)
pRow["Score"] = xElement.Attribute("Score").Value;
EnrollRecordTable.Rows.Add(pRow);
// 題號 Style：Times New Roman、12號字、水平置中、垂直靠上
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetRowHeight(16.5).SetFontName("Times New Roman").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
// 題目 Style：新細明體、12號字、水平靠左、垂直靠上
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 1, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Left).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetAutoFitRow(true);
if (dicQuestionBackgroundColor.ContainsKey(SurveyID))
if (dicQuestionBackgroundColor[SurveyID].ContainsKey(xElement.Attribute("No").Value))
cs.SetFontBackGroundColor(dicQuestionBackgroundColor[SurveyID][xElement.Attribute("No").Value]);
dicCellStyles.Add(co, cs);
for (int zz = 2; zz <= 7; zz++)
{
// 答題數 Style：新細明體、12號字、水平置中、垂直靠下
co = new CellObject(EnrollRecordTable.Rows.Count - 1, zz, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
if (dicQuestionBackgroundColor.ContainsKey(SurveyID))
if (dicQuestionBackgroundColor[SurveyID].ContainsKey(xElement.Attribute("No").Value))
cs.SetFontBackGroundColor(dicQuestionBackgroundColor[SurveyID][xElement.Attribute("No").Value]);
dicCellStyles.Add(co, cs);
}
// 評鑑值 Style：新細明體、12號字、垂直靠下、水平置中
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 8, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
if (dicQuestionBackgroundColor.ContainsKey(SurveyID))
if (dicQuestionBackgroundColor[SurveyID].ContainsKey(xElement.Attribute("No").Value))
cs.SetFontBackGroundColor(dicQuestionBackgroundColor[SurveyID][xElement.Attribute("No").Value]);
dicCellStyles.Add(co, cs);
}
// 平均評鑑值
foreach (XElement xElement in xStatisticsGroup)
{
pRow = EnrollRecordTable.NewRow();
//pRow["No"] = string.Empty;
pRow["Content"] = HttpUtility.HtmlDecode(xElement.Attribute("Content").Value);
pRow["Score"] = xElement.Attribute("Score").Value;
EnrollRecordTable.Rows.Add(pRow);
// 平均評鑑值標題 Style：粗體、底線、標楷體、12號字、水平靠左、垂直靠上
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 1, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetRowHeight(18).SetFontBold(true).SetFontUnderline(true).SetFontName("標楷體").SetFontSize(12).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Left).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top);
dicCellStyles.Add(co, cs);
// 評鑑值 Style：新細明體、12號字、垂直靠下、水平置中
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 8, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
// 評鑑值背景色
if (dicEvaluationBackgroundColor.ContainsKey(SurveyID))
if (dicEvaluationBackgroundColor[SurveyID].ContainsKey(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value)))
cs.SetFontBackGroundColor(dicEvaluationBackgroundColor[SurveyID][HttpUtility.HtmlDecode(xElement.Attribute("Content").Value)]);
dicCellStyles.Add(co, cs);
}
if (xElements.Where(x => x.Attribute("IsCase").Value == "是").Count() > 0)
{
// 空一行
pRow = EnrollRecordTable.NewRow();
EnrollRecordTable.Rows.Add(pRow);
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetRowHeight(9.5);
dicCellStyles.Add(co, cs);
}
// 個案題
foreach (XElement xElement in xElements)
{
if (xElement.Attribute("IsCase").Value == "否")
continue;
pRow = EnrollRecordTable.NewRow();
pRow["No"] = xElement.Attribute("No").Value + ".";
pRow["Content"] = HttpUtility.HtmlDecode(xElement.Attribute("Content").Value);
EnrollRecordTable.Rows.Add(pRow);
// 題號 Style：Times New Roman、12號字、垂直靠上，水平置中
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetRowHeight(18).SetFontName("Times New Roman").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
// 題目 Style：新細明體、12號字、垂直靠上，水平靠左
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 1, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Left).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetAutoFitRow(true);
dicCellStyles.Add(co, cs);
IEnumerable<XElement> xCases = xElement.Descendants("Case");
int z = 0;
foreach (XElement xCase in xCases)
{
z++;
pRow = EnrollRecordTable.NewRow();
pRow["No"] = xElement.Attribute("No").Value + "-" + z;
pRow["Content"] = HttpUtility.HtmlDecode(xCase.Attribute("Content").Value);
pRow["Score"] = xCase.Attribute("Score").Value;
Options = xCase.Descendants("Option");
if (Options.Count() > 0) Options = Options.OrderBy(x => int.Parse(x.Attribute("No").Value));
foreach (XElement xOption in Options)
{
pRow["Option" + xOption.Attribute("No").Value + "AnswerCount"] = xOption.Attribute("AnswerCount").Value;
}
EnrollRecordTable.Rows.Add(pRow);
// 題號 Style：Times New Roman、11號字、垂直靠上，水平置中
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("Times New Roman").SetFontSize(11).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
// 題目 Style：新細明體、12號字、垂直靠上，水平靠左
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 1, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Left).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetAutoFitRow(true);
dicCellStyles.Add(co, cs);
j = 2;
foreach (XElement xOption in Options)
{
// 答題數 Style：新細明體、12號字、水平置中、垂直靠下
co = new CellObject(EnrollRecordTable.Rows.Count - 1, j, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
j++;
}
// 評鑑值 Style：新細明體、12號字、垂直靠下、水平置中
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 8, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
}
}
// 空一行
pRow = EnrollRecordTable.NewRow();
EnrollRecordTable.Rows.Add(pRow);
if (xElements.Where(x => x.Attribute("IsCase").Value == "是").Count() > 0)
{
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetRowHeight(11);
dicCellStyles.Add(co, cs);
}
else
{
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetRowHeight(17.5);
dicCellStyles.Add(co, cs);
}
}
/// <summary>
/// 以實值取代變數
/// </summary>
/// <param name="dataSet">資料來源</param>
/// <param name="instanceSheet">報表</param>
/// <param name="dataIndex">報表列</param>
public static void GenerateSheet(DataSet dataSet, Aspose.Cells.Worksheet instanceSheet, int dataIndex, Dictionary<CellObject, CellStyle> dicCellStyles)
{
// 所有變數
Dictionary<string, List<Aspose.Cells.Cell>> variableCollection = DocumentHelper.DiscoverVariableCells(instanceSheet, dataIndex);
// 資料來源中的所有資料
foreach (KeyValuePair<string, List<Aspose.Cells.Cell>> kv in variableCollection)
{
if (!dataSet.Tables.Contains(kv.Key))
continue;
DataTable dataTable = dataSet.Tables[kv.Key];
// 取得包含變數的儲存格
foreach (Aspose.Cells.Cell cell in variableCollection[kv.Key])
{
int n = 0;
int m = 0;
//Dictionary<int, dynamic> dicCharactersFormatting = BackupSelectedCharactersFormatting(cell);
Aspose.Cells.Range mergedRange = null;
// 若變數內容為影像，如：畢業照
if (dataTable.Columns[0].DataType == Type.GetType("System.Byte[]"))
{
Aspose.Cells.Range imgRange = instanceSheet.Cells[cell.Row, cell.Column].GetMergedRange();
Byte[] imageData = dataTable.Rows[0][0] as Byte[];
MemoryStream ms = new MemoryStream(imageData);
int pictureIndex = 0;
if (ms.Length == 0)
continue;
if (imgRange != null)
pictureIndex = instanceSheet.Pictures.Add(imgRange.FirstRow, imgRange.FirstColumn, (imgRange.FirstRow + imgRange.RowCount), (imgRange.FirstColumn + imgRange.ColumnCount), ms);
else
pictureIndex = instanceSheet.Pictures.Add(cell.Row, cell.Column, cell.Row + 1, cell.Column + 1, ms);
instanceSheet.Pictures[pictureIndex].UpperDeltaX = 10;
instanceSheet.Pictures[pictureIndex].UpperDeltaY = 10;
instanceSheet.Pictures[pictureIndex].LowerDeltaX = -10;
instanceSheet.Pictures[pictureIndex].LowerDeltaY = -10;
cell.PutValue("");
continue;
}
//若變數內容為單一值，如：學號、姓名
if (dataTable.Rows.Count == 1 && dataTable.Columns.Count == 1)
{
string oldValue = cell.Value + "";
//List<int> vIndexs = new List<int>();
//if (oldValue.Length >0)
//{
// int i=0;
// do
// {
// i = oldValue.IndexOf("[[" + dataTable.TableName + "]]", i);
// if (i >= 0)
// {
// vIndexs.Add(i);
// i++;
// }
// }
// while (oldValue.IndexOf("[[" + dataTable.TableName + "]]", i) >= 0);
//}
// 以值取代變數
string newValue = oldValue.Replace("[[" + dataTable.TableName + "]]", dataTable.Rows[0][0].ToString());
//if (newValue == dataTable.Rows[0][0].ToString())
//{
// DateTime date_time_data;
// decimal decimal_data;
// if (DateTime.TryParse(newValue, out date_time_data))
// cell.PutValue(date_time_data.ToString(), true);
// else if (decimal.TryParse(newValue, out decimal_data))
// cell.PutValue(decimal_data + "", true);
// else
// cell.PutValue(newValue);
//}
if (!string.IsNullOrEmpty(newValue))
{
PutValue(cell, newValue, dataTable.Columns[0].DataType);
//cell.PutValue(newValue);
//if ((cell.Value + "").Length != newValue.Length || (!(cell.Value + "").Equals(newValue)))
// cell.PutValue(newValue);
}
CellStyle cell_style = null;
CellObject cell_object = new CellObject(0, 0, dataTable.TableName, dataTable.DataSet.DataSetName, instanceSheet.Name);
if (dicCellStyles != null)
{
foreach (CellObject c in dicCellStyles.Keys)
{
if (c.RowIndex == cell_object.RowIndex && c.ColumnIndex == cell_object.ColumnIndex && c.TableName == cell_object.TableName && c.DataSetName == cell_object.DataSetName && c.WorkSheetName == cell_object.WorkSheetName)
cell_style = dicCellStyles[c];
}
}
if (cell_style != null)
{
SetCellStyle(instanceSheet, cell, cell_style);
continue;
}
}
// 若變數內容為矩陣結構，如：異動資料、學期成績資料
if (dataTable.Rows.Count > 1 || dataTable.Columns.Count > 1)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
m = 0;
for (int j = 0; j < dataTable.Columns.Count; j++)
{
CellStyle cell_style = null;
CellObject cell_object = new CellObject(i, j, dataTable.TableName, dataTable.DataSet.DataSetName, instanceSheet.Name);
if (dicCellStyles != null)
{
foreach (CellObject c in dicCellStyles.Keys)
{
if (c.RowIndex==cell_object.RowIndex && c.ColumnIndex == cell_object.ColumnIndex && c.TableName == cell_object.TableName && c.DataSetName == cell_object.DataSetName && c.WorkSheetName == cell_object.WorkSheetName)
cell_style = dicCellStyles[c];
}
}
// 以值取代變數
string oldValue = (instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Value == null) ? string.Empty : instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Value.ToString();
string newValue = string.Empty;
if (oldValue.Contains("[[" + kv.Key + "]]"))
newValue = oldValue.Replace("[[" + kv.Key + "]]", dataTable.Rows[i][j].ToString());
else if (oldValue.Contains("[[") && oldValue.Contains("]]"))
newValue = dataTable.Rows[i][j].ToString() + oldValue;
else
newValue = oldValue + dataTable.Rows[i][j].ToString();
if (!string.IsNullOrEmpty(newValue))
{
PutValue(instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m], newValue, dataTable.Columns[j].DataType);
//instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].PutValue(newValue);
//if ((instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Value + "").Length != newValue.Length || (!(instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Value + "").Equals(newValue)))
// instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].PutValue(newValue);
}
if (cell_style != null)
{
SetCellStyle(instanceSheet, instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m], cell_style);
//// 粗體
//instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Style.Font.IsBold = cell_style.Bold;
//// 底線
//if (cell_style.Underline)
// instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Style.Font.Underline = FontUnderlineType.Single;
//// 字體名稱
//if (!string.IsNullOrEmpty(cell_style.FontName))
// instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Style.Font.Name = cell_style.FontName;
//// 字體大小
//if (cell_style.FontSize > 0)
// instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Style.Font.Size = cell_style.FontSize;
//// 水平位置
//if (cell_style.HAlignment != null)
// instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Style.HorizontalAlignment = cell_style.HAlignment.Value;
//// 垂直位置
//if (cell_style.VAlignment != null)
// instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Style.VerticalAlignment = cell_style.VAlignment.Value;
//// 列高
//if (cell_style.RowHeight != null)
// instanceSheet.Cells.SetRowHeight(cell.Row + i + n, cell_style.RowHeight.Value);
//// 合併儲存格
//if (cell_style.MergeObject != null)
//{
// instanceSheet.Cells.Merge(cell.Row + i + n, cell.Column + j + m, cell_style.MergeObject.row_length, cell_style.MergeObject.column_length);
//}
// 自動調整列高
//if (cell_style.AutoFitRow)
//{
// if (cell_style.MergeObject != null)
// {
// Range merged_Range = instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].GetMergedRange();
// if (merged_Range != null)
// {
// double column_width = 0.0f;
// for (int c = 0; c < merged_Range.ColumnCount; c++)
// {
// column_width += merged_Range.Worksheet.Cells.GetColumnWidth(merged_Range.FirstColumn + c);
// }
// instanceSheet.Cells.SetRowHeight(cell.Row + i + n, SandBox.Instance.GetFitedRowHeight(instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Value + "", column_width) * 10 / 7.5);
// //AutoFitRowByNPOI();
// Style style = instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Style;
// style.IsTextWrapped = true;
// StyleFlag sf = new StyleFlag();
// sf.All = true;
// merged_Range.ApplyStyle(style, sf);
// }
// }
// else
// {
// //instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Style.IsTextWrapped = true;
// //instanceSheet.AutoFitRow(cell.Row + i + n, 0, instanceSheet.Cells.MaxDataColumn);
// //double row_height_after = instanceSheet.Cells.GetRowHeight(cell.Row + i + n);
// //instanceSheet.Cells.SetRowHeight(cell.Row + i + n, row_height_after * 10 / 9.0);
// instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Style.IsTextWrapped = true;
// double column_width = instanceSheet.Cells.GetColumnWidth(instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Column);
// string content = (instanceSheet.Cells[cell.Row + i + n, cell.Column + j + m].Value + "").Replace(" ", "_");
// double row_height_after = SandBox.Instance.GetFitedRowHeight(content, column_width);
// instanceSheet.Cells.SetRowHeight(cell.Row + i + n, row_height_after * 10 / 7.5);
// }
//}
//// 背景色
//if (cell_style.BackGroundColor != null)
//{
// Cells celice = instanceSheet.Cells;
// Style celicaStil = null;
// celicaStil = celice[cell.Row + i + n, cell.Column + j + m].Style;
// celicaStil.ForegroundColor = cell_style.BackGroundColor.Value;
// celicaStil.Pattern = BackgroundType.Solid;
// celice[cell.Row + i + n, cell.Column + j + m].Style = celicaStil;
//}
}
// 若變數所在儲存格為合併儲存格，則 ColumnIndex 要增加
mergedRange = instanceSheet.Cells[cell.Row, cell.Column + j + m].GetMergedRange();
if (mergedRange != null)
// 合併儲存格 ColumnIndex 的增加量
m += mergedRange.ColumnCount - 1;
}
// 若變數所在儲存格為合併儲存格，則 RowIndex 要增加
mergedRange = instanceSheet.Cells[cell.Row + i + n, cell.Column].GetMergedRange();
if (mergedRange != null)
// 合併儲存格 RowIndex 的增加量
n += mergedRange.RowCount - 1;
}
}
}
}
}
private void MakeDetailOneData(DataTable EnrollRecordTable, UDT.Hierarchy Hierarchy, List<XElement> xElements, string WorksheetName, Dictionary<ReportHelper.CellObject, ReportHelper.CellStyle> dicCellStyles)
{
if (Hierarchy.DisplayOrder != 1)
return;
foreach (XElement xElement in xElements)
{
DataRow pRow = EnrollRecordTable.NewRow();
pRow["No"] = xElement.Attribute("No").Value + ".";
pRow["Content"] = HttpUtility.HtmlDecode(xElement.Attribute("Content").Value);
IEnumerable<XElement> Options = xElement.Descendants("Option");
if (Options.Count() > 0) Options = Options.OrderBy(x => int.Parse(x.Attribute("No").Value));
int j = 0;
string question_content = string.Empty;
foreach (XElement xOption in Options)
{
pRow["Option" + xOption.Attribute("No").Value + "AnswerCount"] = xOption.Attribute("AnswerCount").Value;
question_content += "(" + xOption.Attribute("No").Value + ")" + HttpUtility.HtmlDecode(xOption.Attribute("Content").Value);
}
//if (question_content.EndsWith("、"))
// question_content = question_content.Remove(question_content.Length - 1, 1);
pRow["Content"] += question_content;
//pRow["Score"] = xElement.Attribute("Score").Value;
EnrollRecordTable.Rows.Add(pRow);
CellObject co = new CellObject(EnrollRecordTable.Rows.Count - 1, 0, EnrollRecordTable.TableName, "DataSection", WorksheetName);
CellStyle cs = new CellStyle();
// 題號 Style：Times New Roman、12號字、水平置中、垂直靠上
cs.SetRowHeight(35).SetFontName("Times New Roman").SetFontSize(12).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top);
dicCellStyles.Add(co, cs);
// 題目及答案選項 Style：12號字、水平靠左、垂直靠上
co = new CellObject(EnrollRecordTable.Rows.Count - 1, 1, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Left).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Top).SetAutoFitRow(true);
dicCellStyles.Add(co, cs);
// 答題數
j = 2;
foreach (XElement xOption in Options)
{
co = new CellObject(EnrollRecordTable.Rows.Count - 1, j, EnrollRecordTable.TableName, "DataSection", WorksheetName);
cs = new CellStyle();
cs.SetFontName("新細明體").SetFontSize(12).SetFontVerticalAlignment(CellStyle.VerticalAlignment.Bottom).SetFontHorizontalAlignment(CellStyle.HorizontalAlignment.Center);
dicCellStyles.Add(co, cs);
j++;
}
}
}
// 選擇題-題目
private DataBindedSheet GetQuestion(XElement xElement, UDT.Hierarchy Hierarchy, Workbook wb)
{
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-選擇題-題目"];
DataBindedSheet.DataTables = new List<DataTable>();
// 項次
DataBindedSheet.DataTables.Add((xElement.Attribute("No").Value + ".").ToDataTable("項次", "項次"));
// 題目
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value).ToDataTable("題目", "題目"));
// 題目高度
CellObject co = new CellObject(0, 0, "題目", DataBindedSheet.Key, this.WorksheetName);
CellStyle cs = new CellStyle();
cs.SetAutoFitRow(true).Merge(1, 1);
// 題目背景色
if (this._DicQuestionBackgroundColor.ContainsKey(SurveyID))
{
if (this._DicQuestionBackgroundColor[SurveyID].ContainsKey(xElement.Attribute("No").Value))
{
cs.SetFontBackGroundColor(this._DicQuestionBackgroundColor[SurveyID][xElement.Attribute("No").Value]);
this.dicCellStyles.Add(co, cs);
goto Outline;
}
}
this.dicCellStyles.Add(co, cs);
Outline:
IEnumerable<XElement> Options = xElement.Descendants("Option");
if (Options.Count() > 0) Options = Options.OrderBy(x => int.Parse(x.Attribute("No").Value));
string question_content = string.Empty;
int i = 0;
foreach (XElement xOption in Options)
{
i = i + 1;
// 選項做答人數
DataBindedSheet.DataTables.Add(xOption.Attribute("AnswerCount").Value.ToDataTable("做答人數-" + xOption.Attribute("No").Value, "做答人數-" + xOption.Attribute("No").Value));
question_content += "(" + xOption.Attribute("No").Value + ")" + HttpUtility.HtmlDecode(xOption.Attribute("Content").Value);
// 做答背景色
co = new CellObject(0, 0, "做答人數-" + xOption.Attribute("No").Value, DataBindedSheet.Key, this.WorksheetName);
cs = new CellStyle();
if (this._DicQuestionBackgroundColor.ContainsKey(SurveyID))
{
if (this._DicQuestionBackgroundColor[SurveyID].ContainsKey(xElement.Attribute("No").Value))
{
cs.SetFontBackGroundColor(this._DicQuestionBackgroundColor[SurveyID][xElement.Attribute("No").Value]);
this.dicCellStyles.Add(co, cs);
}
}
}
for (int j = i; j < 6; j++)
{
// 補選項6背景色
DataBindedSheet.DataTables.Add("".ToDataTable("做答人數-" + (j + 1), "做答人數-" + (j + 1)));
co = new CellObject(0, 0, "做答人數-" + (j+1), DataBindedSheet.Key, this.WorksheetName);
cs = new CellStyle();
if (this._DicQuestionBackgroundColor.ContainsKey(SurveyID))
{
if (this._DicQuestionBackgroundColor[SurveyID].ContainsKey(xElement.Attribute("No").Value))
{
cs.SetFontBackGroundColor(this._DicQuestionBackgroundColor[SurveyID][xElement.Attribute("No").Value]);
this.dicCellStyles.Add(co, cs);
}
}
}
// 題目+選項
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value + question_content).ToDataTable("題目+選項", "題目+選項"));
// 評鑑值
DataBindedSheet.DataTables.Add(xElement.Attribute("Score").Value.ToDataTable("評鑑值", "評鑑值"));
// 評鑑值背景色
co = new CellObject(0, 0, "評鑑值", DataBindedSheet.Key, this.WorksheetName);
cs = new CellStyle();
if (this._DicQuestionBackgroundColor.ContainsKey(SurveyID))
{
if (this._DicQuestionBackgroundColor[SurveyID].ContainsKey(xElement.Attribute("No").Value))
{
cs.SetFontBackGroundColor(this._DicQuestionBackgroundColor[SurveyID][xElement.Attribute("No").Value]);
this.dicCellStyles.Add(co, cs);
}
}
return DataBindedSheet;
}
// 問答題-題目
private DataBindedSheet GetEssayQuestion(XElement xElement, UDT.Hierarchy Hierarchy, Workbook wb)
{
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-問答題-題目"];
DataBindedSheet.DataTables = new List<DataTable>();
// 項次
DataBindedSheet.DataTables.Add((xElement.Attribute("No").Value + ".").ToDataTable("項次", "項次"));
// 題目
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value).ToDataTable("題目", "題目"));
// 題目高度
CellObject co = new CellObject(0, 0, "題目", DataBindedSheet.Key, this.WorksheetName);
CellStyle cs = new CellStyle();
cs.SetAutoFitRow(true).Merge(1, 1);
this.dicCellStyles.Add(co, cs);
return DataBindedSheet;
}
// 問答題-做答
private DataBindedSheet GetEssayAnswer(string DisplayOrder, XElement xEssay, UDT.Hierarchy Hierarchy, Workbook wb)
{
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-問答題-做答"];
DataBindedSheet.DataTables = new List<DataTable>();
// 項次
DataBindedSheet.DataTables.Add(DisplayOrder.ToDataTable("項次", "項次"));
// 做答
DataBindedSheet.DataTables.Add(xEssay.Value.ToDataTable("內容", "內容"));
// 題目高度
CellObject co = new CellObject(0, 0, "內容", DataBindedSheet.Key, this.WorksheetName);
CellStyle cs = new CellStyle();
cs.SetAutoFitRow(true).Merge(1, 1);
this.dicCellStyles.Add(co, cs);
return DataBindedSheet;
}
// 個案題-做答
private DataBindedSheet GetCaseAnswer(string DisplayOrder, XElement xCase, UDT.Hierarchy Hierarchy, Workbook wb)
{
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-個案題-做答"];
DataBindedSheet.DataTables = new List<DataTable>();
// 項次
DataBindedSheet.DataTables.Add(DisplayOrder.ToDataTable("項次", "項次"));
// 題目
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xCase.Attribute("Content").Value).ToDataTable("題目", "題目"));
// 題目高度
CellObject co = new CellObject(0, 0, "題目", DataBindedSheet.Key, this.WorksheetName);
CellStyle cs = new CellStyle();
cs.SetAutoFitRow(true).Merge(1, 1);
this.dicCellStyles.Add(co, cs);
// 評鑑值
DataBindedSheet.DataTables.Add(xCase.Attribute("Score").Value.ToDataTable("評鑑值", "評鑑值"));
IEnumerable<XElement> Options = xCase.Descendants("Option");
if (Options.Count() > 0) Options = Options.OrderBy(x => int.Parse(x.Attribute("No").Value));
string question_content = string.Empty;
foreach (XElement xOption in Options)
{
// 選項做答人數
DataBindedSheet.DataTables.Add(xOption.Attribute("AnswerCount").Value.ToDataTable("做答人數-" + xOption.Attribute("No").Value, "做答人數-" + xOption.Attribute("No").Value));
question_content += "(" + xOption.Attribute("No").Value + ")" + HttpUtility.HtmlDecode(xOption.Attribute("Content").Value);
}
// 題目+選項
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xCase.Attribute("Content").Value + question_content).ToDataTable("題目+選項", "題目+選項"));
// 題目高度
co = new CellObject(0, 0, "題目+選項", DataBindedSheet.Key, this.WorksheetName);
cs = new CellStyle();
cs.SetAutoFitRow(true).Merge(1, 1);
this.dicCellStyles.Add(co, cs);
return DataBindedSheet;
}
// 平均評鑑值
private DataBindedSheet GetAvgScore(XElement xElement, UDT.Hierarchy Hierarchy, Workbook wb)
{
DataBindedSheet DataBindedSheet = new DataBindedSheet();
DataBindedSheet.Worksheet = wb.Worksheets[Hierarchy.Title + "-平均評鑑值"];
DataBindedSheet.DataTables = new List<DataTable>();
DataBindedSheet.DataTables.Add(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value).ToDataTable("群組名稱", "群組名稱"));
DataBindedSheet.DataTables.Add(xElement.Attribute("Score").Value.ToDataTable("評鑑值", "評鑑值"));
// 評鑑值背景色
if (this._DicEvaluationBackgroundColor.ContainsKey(this.SurveyID))
{
if (this._DicEvaluationBackgroundColor[this.SurveyID].ContainsKey(HttpUtility.HtmlDecode(xElement.Attribute("Content").Value)))
{
CellObject co = new CellObject(0, 0, "評鑑值", DataBindedSheet.Key, this.WorksheetName);
CellStyle cs = new CellStyle();
cs.SetFontBackGroundColor(this._DicEvaluationBackgroundColor[this.SurveyID][HttpUtility.HtmlDecode(xElement.Attribute("Content").Value)]);
dicCellStyles.Add(co, cs);
}
}
return DataBindedSheet;
}
