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