public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); string InputPath = dataDir + "Book1.xlsx"; // Creating a file stream containing the Excel file to be opened FileStream fstream = new FileStream(InputPath, FileMode.Open); // Opening the Excel file through the file stream Workbook workbook = new Workbook(fstream); // Accessing the first worksheet in the Excel file Worksheet worksheet = workbook.Worksheets[0]; // Auto-fitting the Column of the worksheet worksheet.AutoFitColumn(4); // Saving the modified Excel file workbook.Save(dataDir + "output.xlsx"); // ExEnd:1 }
public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Create another Workbook. Workbook excelWorkbook1 = new Workbook(dataDir + "book1.xls"); // Get the first worksheet in the book. Worksheet ws1 = excelWorkbook1.Worksheets[0]; // Copy the first column from the first worksheet of the first workbook into // The first worksheet of the second workbook. ws1.Cells.CopyColumn(ws1.Cells, ws1.Cells.Columns[0].Index, ws1.Cells.Columns[2].Index); // Autofit the column. ws1.AutoFitColumn(2); // Save the excel file. excelWorkbook1.Save(dataDir + "output.xls"); // ExEnd:1 }
public static void Main(string[] args) { string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); string output1Path = dataDir + "Output.xlsx"; string output2Path = dataDir + "Output.out.ods"; Workbook workbook = new Workbook(); Style style = workbook.CreateBuiltinStyle(BuiltinStyleType.Title); Cell cell = workbook.Worksheets[0].Cells["A1"]; cell.PutValue("Aspose"); cell.SetStyle(style); Worksheet worksheet = workbook.Worksheets[0]; worksheet.AutoFitColumn(0); worksheet.AutoFitRow(0); workbook.Save(output1Path); Console.WriteLine("File saved {0}", output1Path); workbook.Save(output2Path); Console.WriteLine("File saved {0}", output1Path); }
void BGW_DoWork(object sender, DoWorkEventArgs e) { //電子報表 string time = DateTime.Now.Hour.ToString().PadLeft(2) + DateTime.Now.Minute.ToString().PadLeft(2); paperForClass = new SmartSchool.ePaper.ElectronicPaper(string.Format("日常表現記錄表(新制_{0})", time), _Schoolyear.ToString(), _Semester.ToString(), SmartSchool.ePaper.ViewerType.Class); BGW.ReportProgress(1, "取得紙張設定"); //取得列印紙張 int sizeIndex = GetSizeIndex(); BGW.ReportProgress(4, "取得假別設定"); //取得列印假別內容 Dictionary <string, List <string> > UserType = GetUserType(); #region 取得資料 //取得使用者選擇班級 BGW.ReportProgress(8, "取得所選班級"); List <ClassRecord> allClasses = Class.SelectByIDs(K12.Presentation.NLDPanels.Class.SelectedSource); //排序(因為上面沒有照班級排序) int displayOrder; allClasses = allClasses.OrderBy(i => i.GradeYear).ThenBy(i => displayOrder = Int32.TryParse(i.DisplayOrder, out displayOrder)? displayOrder :0).ThenBy(i => i.Name).ToList(); #region 取得使用者所選擇的班級學生 BGW.ReportProgress(12, "取得學生清單"); string classidlist = string.Join(",", K12.Presentation.NLDPanels.Class.SelectedSource); StringBuilder sb = new StringBuilder(); sb.Append("select student.id,student.ref_class_id from student "); sb.Append("join class on class.id=student.ref_class_id "); sb.Append("where student.status=1 "); sb.Append(string.Format("and class.id in ({0})", classidlist)); List <string> StudentIDList = new List <string>(); DataTable dt = _QueryHelper.Select(sb.ToString()); foreach (DataRow row in dt.Rows) { StudentIDList.Add("" + row[0]); } BGW.ReportProgress(15, "取得學生清單"); List <StudentRecord> allStudents = Student.SelectByIDs(StudentIDList); int maxStudents = 0; int totalStudent = allStudents.Count; Dictionary <string, List <StudentRecord> > classStudents = new Dictionary <string, List <StudentRecord> >(); foreach (StudentRecord each in allStudents) { if (!classStudents.ContainsKey(each.RefClassID)) { classStudents.Add(each.RefClassID, new List <StudentRecord>()); } classStudents[each.RefClassID].Add(each); } foreach (string each in classStudents.Keys) { if (classStudents[each].Count > maxStudents) { maxStudents = classStudents[each].Count; } classStudents[each].Sort(SortStudent); } #endregion Dictionary <string, RewardRecord> MeritDemeritAttDic = new Dictionary <string, RewardRecord>(); Dictionary <string, RewardRecord> TotalMeritDemeritDic = new Dictionary <string, RewardRecord>(); BGW.ReportProgress(20, "取得獎勵資料"); #region 獎勵 foreach (SHMeritRecord each in SHMerit.SelectByStudentIDs(StudentIDList)) { // 2018/1/8 羿均 新增 累計獎勵紀錄資料 RewardRecord totalMerit = new RewardRecord(); if (TotalMeritDemeritDic.ContainsKey(each.RefStudentID)) { totalMerit = TotalMeritDemeritDic[each.RefStudentID]; } totalMerit.MeritACount += each.MeritA.HasValue ? each.MeritA.Value : 0; totalMerit.MeritBCount += each.MeritB.HasValue ? each.MeritB.Value : 0; totalMerit.MeritCCount += each.MeritC.HasValue ? each.MeritC.Value : 0; if (!TotalMeritDemeritDic.ContainsKey(each.RefStudentID)) { TotalMeritDemeritDic.Add(each.RefStudentID, totalMerit); } if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) { continue; } RewardRecord rr = new RewardRecord(); if (MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { rr = MeritDemeritAttDic[each.RefStudentID]; } rr.MeritACount += each.MeritA.HasValue ? each.MeritA.Value : 0; rr.MeritBCount += each.MeritB.HasValue ? each.MeritB.Value : 0; rr.MeritCCount += each.MeritC.HasValue ? each.MeritC.Value : 0; if (!MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { MeritDemeritAttDic.Add(each.RefStudentID, rr); } } #endregion BGW.ReportProgress(25, "取得懲戒資料"); #region 懲戒 foreach (SHDemeritRecord each in SHDemerit.SelectByStudentIDs(StudentIDList)) { // 2018/1/8 羿均 新增 累計懲戒紀錄資料 RewardRecord totalDemerit = new RewardRecord(); if (each.Cleared == "是") { continue; } if (TotalMeritDemeritDic.ContainsKey(each.RefStudentID)) { totalDemerit = TotalMeritDemeritDic[each.RefStudentID]; } totalDemerit.DemeritACount += each.DemeritA.HasValue ? each.DemeritA.Value : 0; totalDemerit.DemeritBCount += each.DemeritB.HasValue ? each.DemeritB.Value : 0; totalDemerit.DemeritCCount += each.DemeritC.HasValue ? each.DemeritC.Value : 0; if (!TotalMeritDemeritDic.ContainsKey(each.RefStudentID)) { TotalMeritDemeritDic.Add(each.RefStudentID, totalDemerit); } if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) { continue; } RewardRecord rr = new RewardRecord(); if (MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { rr = MeritDemeritAttDic[each.RefStudentID]; } rr.DemeritACount += each.DemeritA.HasValue ? each.DemeritA.Value : 0; rr.DemeritBCount += each.DemeritB.HasValue ? each.DemeritB.Value : 0; rr.DemeritCCount += each.DemeritC.HasValue ? each.DemeritC.Value : 0; if (!MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { MeritDemeritAttDic.Add(each.RefStudentID, rr); } } #endregion BGW.ReportProgress(30, "取得節次對照"); //取得節次對照表 Dictionary <string, string> periodDic = new Dictionary <string, string>(); foreach (PeriodMappingInfo var in PeriodMapping.SelectAll()) { string name = var.Type; //取得對照表並且對照出節次->類別的清單(99/11/24 by dylan) if (!periodDic.ContainsKey(var.Name)) { periodDic.Add(var.Name, var.Type); } } BGW.ReportProgress(35, "取得缺曠資料"); #region 缺曠 foreach (SHAttendanceRecord each in SHAttendance.SelectByStudentIDs(StudentIDList)) { if (each.SchoolYear != _Schoolyear || each.Semester != _Semester) { continue; } RewardRecord rr = new RewardRecord(); if (MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { rr = MeritDemeritAttDic[each.RefStudentID]; } foreach (AttendancePeriod _Period in each.PeriodDetail) { if (periodDic.ContainsKey(_Period.Period)) { string typename = periodDic[_Period.Period] + "_" + _Period.AbsenceType; if (rr.Attendance.ContainsKey(typename)) { rr.Attendance[typename]++; } else { rr.Attendance.Add(typename, 1); } } } if (!MeritDemeritAttDic.ContainsKey(each.RefStudentID)) { MeritDemeritAttDic.Add(each.RefStudentID, rr); } } #endregion //日常表現資料 BGW.ReportProgress(40, "取得日常表現"); Dictionary <string, SHMoralScoreRecord> SHMoralScoreDic = new Dictionary <string, SHMoralScoreRecord>(); foreach (SHMoralScoreRecord each in SHMoralScore.Select(null, StudentIDList, _Schoolyear, _Semester)) { if (!SHMoralScoreDic.ContainsKey(each.RefStudentID)) { SHMoralScoreDic.Add(each.RefStudentID, each); } } //文字評量對照表 BGW.ReportProgress(45, "取得文字評量"); List <string> TextScoreList = new List <string>(); SmartSchool.Customization.Data.SystemInformation.getField("文字評量對照表"); System.Xml.XmlElement ElmTextScoreList = (System.Xml.XmlElement)SmartSchool.Customization.Data.SystemInformation.Fields["文字評量對照表"]; foreach (System.Xml.XmlNode Node in ElmTextScoreList.SelectNodes("Content/Morality")) { TextScoreList.Add(Node.Attributes["Face"].InnerText); } #endregion #region 產生表格 BGW.ReportProgress(50, "產生報表樣式"); Workbook template = new Workbook(); Workbook prototype = new Workbook(); //列印尺寸 if (sizeIndex == 0) { template.Open(new MemoryStream(Properties.Resources.德行表現總表新制A3), FileFormatType.Excel2003); } else if (sizeIndex == 1) { template.Open(new MemoryStream(Properties.Resources.德行表現總表新制A4), FileFormatType.Excel2003); } else if (sizeIndex == 2) { template.Open(new MemoryStream(Properties.Resources.德行表現總表新制B4), FileFormatType.Excel2003); } prototype.Copy(template); Worksheet templateSheet = template.Worksheets[0]; Worksheet prototypeSheet = prototype.Worksheets[0]; Range tempAbsence = templateSheet.Cells.CreateRange(9, 1, true); Range tempScoreText = templateSheet.Cells.CreateRange(10, 1, true); Range tempAfterOtherDiff = templateSheet.Cells.CreateRange(11, 1, true); Range oder = templateSheet.Cells.CreateRange(12, 1, true); Dictionary <string, int> columnIndexTable = new Dictionary <string, int>(); Dictionary <string, List <string> > periodAbsence = new Dictionary <string, List <string> >(); //紀錄獎懲的 Column Index columnIndexTable.Add("大功", 3); columnIndexTable.Add("小功", 4); columnIndexTable.Add("嘉獎", 5); columnIndexTable.Add("大過", 6); columnIndexTable.Add("小過", 7); columnIndexTable.Add("警告", 8); //缺曠 int ptColIndex = 9; foreach (string var in UserType.Keys) { foreach (string absence in UserType[var]) { if (!periodAbsence.ContainsKey(var)) { periodAbsence.Add(var, new List <string>()); } if (!periodAbsence[var].Contains(absence)) { periodAbsence[var].Add(absence); } prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempAbsence); ptColIndex += 1; } } ptColIndex = 9; foreach (string period in periodAbsence.Keys) { prototypeSheet.Cells.CreateRange(2, ptColIndex, 1, periodAbsence[period].Count).Merge(); prototypeSheet.Cells[2, ptColIndex].PutValue(period); foreach (string absence in periodAbsence[period]) { prototypeSheet.Cells[3, ptColIndex].PutValue(absence); columnIndexTable.Add(period + "_" + absence, ptColIndex); ptColIndex++; } } if (ptColIndex > 9) { prototypeSheet.Cells.CreateRange(1, 9, 1, ptColIndex - 9).Merge(); prototypeSheet.Cells[1, 9].PutValue("缺曠"); } //用來調整Column寬度的定位 int ColumnMax = ptColIndex; //文字評量 foreach (string textscore in TextScoreList) { columnIndexTable.Add(textscore, ptColIndex); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempScoreText); prototypeSheet.Cells[4, ptColIndex].PutValue(textscore); ptColIndex++; } prototypeSheet.Cells[1, ptColIndex - TextScoreList.Count].PutValue("學生綜合表現"); if ((ptColIndex - TextScoreList.Count > 0) && (TextScoreList.Count > 0)) { prototypeSheet.Cells.CreateRange(1, ptColIndex - TextScoreList.Count, 3, TextScoreList.Count).Merge(); } prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(tempAfterOtherDiff); columnIndexTable.Add("評語", ptColIndex++); prototypeSheet.Cells.CreateRange(ptColIndex, 1, true).Copy(oder); columnIndexTable.Add("是否留察", ptColIndex++); //填入製表日期 prototypeSheet.Cells[0, 0].PutValue("製表日期:" + DateTime.Today.ToShortDateString()); //填入標題 prototypeSheet.Cells.CreateRange(0, 3, 1, ptColIndex - 3).Merge(); prototypeSheet.Cells[0, 3].PutValue(K12.Data.School.ChineseName + " " + _Schoolyear + " 學年度 " + ((_Semester == 1) ? "上" : "下") + " 學期 日常表現記錄表(新制)"); Range ptEachRow = prototypeSheet.Cells.CreateRange(5, 1, false); for (int i = 5; i < maxStudents + 5; i++) { prototypeSheet.Cells.CreateRange(i, 1, false).Copy(ptEachRow); } //加上底線 prototypeSheet.Cells.CreateRange(maxStudents + 5, 0, 1, ptColIndex).SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, System.Drawing.Color.Black); for (int i = 12; i >= ptColIndex; i--) { prototypeSheet.Cells.DeleteColumn(i); } Range pt = prototypeSheet.Cells.CreateRange(0, maxStudents + 5, false); #endregion #region 填入表格 BGW.ReportProgress(53, "填入報表資料"); Workbook wb = new Workbook(); wb.Copy(prototype); Worksheet ws = wb.Worksheets[0]; int index = 0; int dataIndex = 0; int classTotalRow = maxStudents + 5; BGW.ReportProgress(57, "填入老師姓名"); #region 取得全校班級,與老師姓名/暱稱(2012/5/24) Dictionary <string, string> TeacherDic = new Dictionary <string, string>(); string st = "SELECT class.id,teacher.teacher_name,teacher.nickname FROM class JOIN teacher ON class.ref_teacher_id = teacher.id"; dt = _QueryHelper.Select(st); foreach (DataRow row in dt.Rows) { string classID = "" + row[0]; string teacherName = "" + row[1]; string teacherNickname = "" + row[2]; if (!TeacherDic.ContainsKey(classID)) { if (string.IsNullOrEmpty(teacherNickname)) { TeacherDic.Add(classID, teacherName); } else { TeacherDic.Add(classID, teacherName + "(" + teacherNickname + ")"); } } } #endregion BGW.ReportProgress(61, "學生留察資料"); #region 取得全校本學年度留查之記錄(2012/5/24) List <string> MeritFlagIs2 = new List <string>(); st = string.Format("SELECT ref_student_id from discipline where merit_flag=2 and school_year={0} and semester={1}", _Schoolyear, _Semester); dt = _QueryHelper.Select(st); foreach (DataRow row in dt.Rows) { if (!MeritFlagIs2.Contains("" + row[0])) { MeritFlagIs2.Add("" + row[0]); } } #endregion BGW.ReportProgress(70, "開始列印資料"); int PeogressNow1 = totalStudent / 30; int PeogressNow2 = 0; int PeogressNow3 = 70; foreach (ClassRecord aClass in allClasses) { //電子報表用 #region 電子報表用 Workbook Paper_wb = new Workbook(); Paper_wb.Copy(prototype); Worksheet Paper_ws = Paper_wb.Worksheets[0]; int Paper_index = 0; int Paper_dataIndex = 0; int Paper_classTotalRow = maxStudents + 5; int Paper_PeogressNow1 = totalStudent / 30; #endregion if (!classStudents.ContainsKey(aClass.ID)) { continue; } string TeacherName = ""; if (TeacherDic.ContainsKey(aClass.ID)) { TeacherName = TeacherDic[aClass.ID]; } //複製完成後的樣板 ws.Cells.CreateRange(index, classTotalRow, false).Copy(pt); Paper_ws.Cells.CreateRange(Paper_index, Paper_classTotalRow, false).Copy(pt); //填入班級名稱 ws.Cells[index + 1, 0].PutValue("班級:" + aClass.Name); Paper_ws.Cells[Paper_index + 1, 0].PutValue("班級:" + aClass.Name); //填入老師名稱 ws.Cells[index + 3, 0].PutValue("教師:" + TeacherName); Paper_ws.Cells[Paper_index + 3, 0].PutValue("教師:" + TeacherName); dataIndex = index + 5; Paper_dataIndex = Paper_index + 5; foreach (StudentRecord aStudent in classStudents[aClass.ID]) { PeogressNow2++; if (PeogressNow2 > PeogressNow1 && PeogressNow3 < 101) { PeogressNow3++; PeogressNow2 = 0; BGW.ReportProgress(PeogressNow3, "開始列印資料"); } ws.Cells[dataIndex, 0].PutValue(aStudent.SeatNo); ws.Cells[dataIndex, 1].PutValue(aStudent.Name); ws.Cells[dataIndex, 2].PutValue(aStudent.StudentNumber); Paper_ws.Cells[Paper_dataIndex, 0].PutValue(aStudent.SeatNo); Paper_ws.Cells[Paper_dataIndex, 1].PutValue(aStudent.Name); Paper_ws.Cells[Paper_dataIndex, 2].PutValue(aStudent.StudentNumber); // 2018/01/17 羿均 // MeritDemeritAttDic 為當學年度學期 缺曠獎懲資料 // TotalMeritDemeritDic 為獎懲累計資料 if (MeritDemeritAttDic.ContainsKey(aStudent.ID) || TotalMeritDemeritDic.ContainsKey(aStudent.ID)) { RewardRecord rr = new RewardRecord(); if (MeritDemeritAttDic.ContainsKey(aStudent.ID)) { rr = MeritDemeritAttDic[aStudent.ID]; } if (TotalMeritDemeritDic.ContainsKey(aStudent.ID)) { RewardRecord totalRecord = TotalMeritDemeritDic[aStudent.ID]; ws.Cells[dataIndex, columnIndexTable["大功"]].PutValue(rr.MeritACount + " / " + totalRecord.MeritACount); ws.Cells[dataIndex, columnIndexTable["小功"]].PutValue(rr.MeritBCount + " / " + totalRecord.MeritBCount); ws.Cells[dataIndex, columnIndexTable["嘉獎"]].PutValue(rr.MeritCCount + " / " + totalRecord.MeritCCount); ws.Cells[dataIndex, columnIndexTable["大過"]].PutValue(rr.DemeritACount + " / " + totalRecord.DemeritACount); ws.Cells[dataIndex, columnIndexTable["小過"]].PutValue(rr.DemeritBCount + " / " + totalRecord.DemeritBCount); ws.Cells[dataIndex, columnIndexTable["警告"]].PutValue(rr.DemeritCCount + " / " + totalRecord.DemeritCCount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大功"]].PutValue(rr.MeritACount + " / " + totalRecord.MeritACount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小功"]].PutValue(rr.MeritBCount + " / " + totalRecord.MeritBCount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["嘉獎"]].PutValue(rr.MeritCCount + " / " + totalRecord.MeritCCount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大過"]].PutValue(rr.DemeritACount + " / " + totalRecord.DemeritACount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小過"]].PutValue(rr.DemeritBCount + " / " + totalRecord.DemeritBCount); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["警告"]].PutValue(rr.DemeritCCount + " / " + totalRecord.DemeritCCount); } else { //有缺曠沒獎懲,要印獎懲0/0 //https://3.basecamp.com/4399967/buckets/15765350/todos/4520462594 ws.Cells[dataIndex, columnIndexTable["大功"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["小功"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["大過"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["小過"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["警告"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大功"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小功"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大過"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小過"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["警告"]].PutValue("0 / 0"); } foreach (string each in rr.Attendance.Keys) { if (columnIndexTable.ContainsKey(each)) { ws.Cells[dataIndex, columnIndexTable[each]].PutValue(rr.Attendance[each]); Paper_ws.Cells[Paper_dataIndex, columnIndexTable[each]].PutValue(rr.Attendance[each]); } } } else { //沒缺曠沒獎懲,要印獎懲0/0 // https://3.basecamp.com/4399967/buckets/15765350/todos/4520462594 ws.Cells[dataIndex, columnIndexTable["大功"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["小功"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["大過"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["小過"]].PutValue("0 / 0"); ws.Cells[dataIndex, columnIndexTable["警告"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大功"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小功"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["嘉獎"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["大過"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["小過"]].PutValue("0 / 0"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["警告"]].PutValue("0 / 0"); } //文字評量部份 SHMoralScoreRecord demonScore; if (SHMoralScoreDic.ContainsKey(aStudent.ID)) { demonScore = SHMoralScoreDic[aStudent.ID]; //文字評量 XmlElement xml = demonScore.TextScore; foreach (XmlElement each in xml.SelectNodes("Morality")) { string strFace = each.GetAttribute("Face"); if (columnIndexTable.ContainsKey(strFace)) { int colIndex = columnIndexTable[strFace]; ws.Cells[dataIndex, colIndex].PutValue(each.InnerText); Paper_ws.Cells[Paper_dataIndex, colIndex].PutValue(each.InnerText); } } //導師評語 ws.Cells[dataIndex, columnIndexTable["評語"]].PutValue(demonScore.Comment); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["評語"]].PutValue(demonScore.Comment); } //留察 if (MeritFlagIs2.Contains(aStudent.ID)) { ws.Cells[dataIndex, columnIndexTable["是否留察"]].PutValue("是"); Paper_ws.Cells[Paper_dataIndex, columnIndexTable["是否留察"]].PutValue("是"); } foreach (int each in columnIndexTable.Values) { if (each >= ColumnMax) { Paper_ws.AutoFitColumn(each); } } dataIndex++; Paper_dataIndex++; } //電子報表 MemoryStream stream = Paper_wb.SaveToStream(); paperForClass.Append(new PaperItem(PaperFormat.Office2003Xls, stream, aClass.ID)); index += classTotalRow + 2; ws.HPageBreaks.Add(index, ptColIndex); } foreach (int each in columnIndexTable.Values) { if (each >= ColumnMax) { ws.AutoFitColumn(each); } } #endregion if (Carty_paper) { BGW.ReportProgress(90, "上傳電子報表"); SmartSchool.ePaper.DispatcherProvider.Dispatch(paperForClass); } BGW.ReportProgress(100, "資料列印完成"); e.Result = wb; }
//The custom DoTest method public void DoTest() { // The path to the documents directory. string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); //Instantiate a workbook object Workbook book = new Workbook(); //Create a worksheet object and get the first worksheet Worksheet sheet1 = book.Worksheets[0]; //Set the first worksheet to _sheet object _sheet = sheet1; //Call different custom methods //These methods exhibits different conditional formatting types with their custom //formattings attributes for MS Excel 2007 .xlsx file format AddDefaultIconSet(); AddIconSet2(); AddIconSet3(); AddIconSet4(); AddIconSet5(); AddIconSet6(); AddIconSet7(); AddIconSet8(); AddIconSet9(); AddIconSet10(); AddIconSet11(); AddIconSet12(); AddIconSet13(); AddIconSet14(); AddIconSet15(); AddIconSet16(); AddIconSet17(); AddIconSet18(); AddDefaultColorScale(); Add3ColorScale(); Add2ColorScale(); AddAboveAverage(); AddAboveAverage2(); AddAboveAverage3(); AddTop10_1(); AddTop10_2(); AddTop10_3(); AddTop10_4(); AddDataBar1(); AddDataBar2(); AddContainsText(); AddNotContainsText(); AddContainsBlank(); AddNotContainsBlank(); AddBeginWith(); AddEndWith(); AddContainsError(); AddNotContainsError(); AddDuplicate(); AddUnique(); AddTimePeriod_1(); AddTimePeriod_2(); AddTimePeriod_3(); AddTimePeriod_4(); AddTimePeriod_5(); AddTimePeriod_6(); AddTimePeriod_7(); AddTimePeriod_8(); AddTimePeriod_9(); AddTimePeriod_10(); //AutoFit M Column in the worksheet _sheet.AutoFitColumn(12); //Specify the output file path string outfn = dataDir + "Testoutput.out.xlsx"; //Save the excel file book.Save(outfn, SaveFormat.Xlsx); }
/// <summary> ///方法,导出DataGridView中的数据到Excel文件 /// </summary> /// <remarks> /// add com "Microsoft Excel 11.0 Object Library" /// using Excel=Microsoft.Office.Interop.Excel; /// using System.Reflection; /// </remarks> /// <param name= "dgv"> DataGridView </param> public void DataGridViewToExcel(DataGridView dgv) { #region 验证可操作性 //申明保存对话框 SaveFileDialog dlg = new SaveFileDialog(); //默然文件后缀 dlg.DefaultExt = "xls "; //文件后缀列表 dlg.Filter = "EXCEL文件(*.XLS)|*.xls "; //默然路径是系统当前路径 dlg.InitialDirectory = Directory.GetCurrentDirectory(); //打开保存对话框 if (dlg.ShowDialog() == DialogResult.Cancel) { return; } //返回文件路径 string fileNameString = dlg.FileName; //验证strFileName是否为空或值无效 if (fileNameString.Trim() == " ") { return; } //定义表格内数据的行数和列数 int rowscount = dgv.Rows.Count; int colscount = dgv.Columns.Count; //行数必须大于0 if (rowscount <= 0) { MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //列数必须大于0 if (colscount <= 0) { MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //行数不可以大于65536 if (rowscount > 65536) { MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //列数不可以大于255 if (colscount > 255) { MessageBox.Show("数据记录行数太多,不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //验证以fileNameString命名的文件是否存在,如果存在删除它 FileInfo file = new FileInfo(fileNameString); if (file.Exists) { try { file.Delete(); } catch (Exception error) { MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } } #endregion try { Workbook workbook = new Workbook(); //工作簿 Worksheet sheet = workbook.Worksheets[0]; //工作表 sheet.Name = "手术查询"; Cells cells = sheet.Cells; //单元格 //为标题设置样式 Style styleTitle = workbook.Styles[workbook.Styles.Add()]; //新增样式 styleTitle.HorizontalAlignment = TextAlignmentType.Center; //文字居中 styleTitle.Font.Name = "宋体"; //文字字体 styleTitle.Font.Size = 18; //文字大小 styleTitle.Font.IsBold = true; //粗体 //样式2 Style style2 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style2.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style2.Font.Name = "宋体"; //文字字体 style2.Font.Size = 12; //文字大小 style2.Font.IsBold = true; //粗体 style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //样式3 Style style3 = workbook.Styles[workbook.Styles.Add()]; //新增样式 style3.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style3.Font.Name = "宋体"; //文字字体 style3.Font.Size = 10; //文字大小 style3.IsTextWrapped = true; //单元格内容自动换行 style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; int Colnum = dgv.Columns.Count; //表格列数 int Rownum = dgv.Rows.Count; //表格行数 int visibleCols = dgv.Columns.Cast <DataGridViewColumn>().Where(x => x.Visible).Count(); //生成行1 标题行 cells.Merge(0, 0, 1, visibleCols); //合并单元格 cells[0, 0].PutValue(string.Format("手术查询({0}至{1})", this.dateTimePicker1.Value.ToString("yyyy-MM-dd"), this.dateTimePicker2.Value.ToString("yyyy-MM-dd"))); //填写内容 cells[0, 0].SetStyle(styleTitle); cells.SetRowHeight(0, 38); int hideIndex = 0; //生成行2 列名行 for (int i = 0; i + hideIndex < Colnum; i++) { while (i + hideIndex < Colnum && !dgv.Columns[i + hideIndex].Visible) { hideIndex++; } if (i + hideIndex >= Colnum) { continue; } cells[1, i].PutValue(dgv.Columns[i + hideIndex].HeaderText); cells[1, i].SetStyle(style2); cells.SetRowHeight(1, 25); //设置表页的列宽度自适应 sheet.AutoFitColumn(i, 0, cells.MaxRow); cells.SetColumnWidthPixel(i, cells.GetColumnWidthPixel(i) + 2); } //生成数据行 for (int i = 0; i < Rownum; i++) { hideIndex = 0; for (int k = 0; k + hideIndex < Colnum; k++) { while (k + hideIndex < Colnum && !dgv.Columns[k + hideIndex].Visible) { hideIndex++; } if (k + hideIndex >= Colnum) { continue; } cells[2 + i, k].PutValue(Convert.ToString(dgv.Rows[i].Cells[k + hideIndex].FormattedValue) ?? ""); cells[2 + i, k].SetStyle(style3); } cells.SetRowHeight(2 + i, 24); //设置表页的行高自适应 sheet.AutoFitRow(i + 2); cells.SetColumnWidthPixel(i + 2, cells.GetColumnWidthPixel(i + 2) + 6); } workbook.Save(fileNameString); } catch (Exception error) { MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } MessageBox.Show(fileNameString + ",导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); }
//The custom DoTest method public void DoTest() { // The path to the documents directory. string dataDir = Path.GetFullPath("../../../Data/"); //Instantiate a workbook object Workbook book = new Workbook(); //Create a worksheet object and get the first worksheet Worksheet sheet1 = book.Worksheets[0]; //Set the first worksheet to _sheet object _sheet = sheet1; //Call different custom methods //These methods exhibits different conditional formatting types with their custom //formattings attributes for MS Excel 2007 .xlsx file format AddDefaultIconSet(); AddIconSet2(); AddIconSet3(); AddIconSet4(); AddIconSet5(); AddIconSet6(); AddIconSet7(); AddIconSet8(); AddIconSet9(); AddIconSet10(); AddIconSet11(); AddIconSet12(); AddIconSet13(); AddIconSet14(); AddIconSet15(); AddIconSet16(); AddIconSet17(); AddIconSet18(); AddDefaultColorScale(); Add3ColorScale(); Add2ColorScale(); AddAboveAverage(); AddAboveAverage2(); AddAboveAverage3(); AddTop10_1(); AddTop10_2(); AddTop10_3(); AddTop10_4(); AddDataBar1(); AddDataBar2(); AddContainsText(); AddNotContainsText(); AddContainsBlank(); AddNotContainsBlank(); AddBeginWith(); AddEndWith(); AddContainsError(); AddNotContainsError(); AddDuplicate(); AddUnique(); AddTimePeriod_1(); AddTimePeriod_2(); AddTimePeriod_3(); AddTimePeriod_4(); AddTimePeriod_5(); AddTimePeriod_6(); AddTimePeriod_7(); AddTimePeriod_8(); AddTimePeriod_9(); AddTimePeriod_10(); //AutoFit M Column in the worksheet _sheet.AutoFitColumn(12); //Specify the output file path string outfn = dataDir+ "Testoutput.xlsx"; //Save the excel file book.Save(outfn, SaveFormat.Xlsx); }
/// <summary> /// 导出投诉统计 /// </summary> /// <param name="dept"></param> /// <param name="deptname"></param> /// <param name="st"></param> /// <param name="et"></param> /// <param name="result"></param> /// <returns></returns> public static bool ComplaintoTable(string dept, string deptname, string st, string et, ref string result) { bool r = false; #region 读取数值 DataTable dt = new DataTable(); string where = ""; if (!string.IsNullOrWhiteSpace(dept)) { where = where + " and Order_Info.DeptID=" + dept; } if (st == et) { if (!string.IsNullOrWhiteSpace(st)) { where = where + " and Order_Complaint.ComplainDate='" + st + "'"; } } else { if (!string.IsNullOrWhiteSpace(st)) { where = where + " and Order_Complaint.ComplainDate>='" + st + "'"; } if (!string.IsNullOrWhiteSpace(et)) { where = where + " and Order_Complaint.ComplainDate<='" + et + "'"; } } Order_ComplaintDAL ocdata = new Order_ComplaintDAL(); DataTable db = ocdata.TableOrder_Complaint1(where); int c = 1; if (db != null && db.Rows.Count > 0) { dt.Columns.Add("序号", typeof(string)); dt.Columns.Add("部门", typeof(string)); dt.Columns.Add("工号", typeof(string)); dt.Columns.Add("姓名", typeof(string)); dt.Columns.Add("退费总额", typeof(string)); dt.Columns.Add("投诉量", typeof(string)); dt.Columns.Add("取消量", typeof(string)); dt.Columns.Add("话务员责任量", typeof(string)); double d1 = 0, d2 = 0, d3 = 0, d4 = 0; ArrayList tempList = new ArrayList(); foreach (DataRow m in db.Rows) { tempList = new ArrayList(); tempList.Add(c); tempList.Add(m["DeptName"]); tempList.Add(m["UserID"]); tempList.Add(m["UserName"]); tempList.Add(m["a1"]); d1 += Convert.ToDouble(m["a1"]); tempList.Add(m["a2"]); d2 += Convert.ToDouble(m["a2"]); tempList.Add(m["a3"]); d3 += Convert.ToDouble(m["a3"]); tempList.Add(m["a4"]); d4 += Convert.ToDouble(m["a4"]); dt.LoadDataRow(tempList.ToArray(), true); c++; } tempList = new ArrayList(); tempList.Add(""); tempList.Add("合计"); tempList.Add(""); tempList.Add(""); tempList.Add(d1); tempList.Add(d2); tempList.Add(d3); tempList.Add(d4); dt.LoadDataRow(tempList.ToArray(), true); } #endregion result = ""; try { //创建文件夹 String savePath = "../upload/file/"; String dirPath = System.Web.HttpContext.Current.Server.MapPath(savePath); if (!Directory.Exists(dirPath)) { Directory.CreateDirectory(dirPath); } DateTime now = DateTime.Now; String ymd = now.ToString("yyyyMMdd", DateTimeFormatInfo.InvariantInfo); dirPath += ymd + "/"; savePath += ymd + "/"; if (!Directory.Exists(dirPath)) { Directory.CreateDirectory(dirPath); } String newFileName = deptname + "投诉统计表" + st + "至" + et; String filepath = dirPath + newFileName + ".xlsx"; Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); try { if (dt == null) { result = "DataTableToExcel:datatable 为空"; return(false); } FileInfo info = new FileInfo(filepath); info.Create().Dispose(); //为单元格添加样式 Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()]; Worksheet sheet = wb.Worksheets[0]; //工作表 Cells cells = sheet.Cells; //单元格 //设置居中 style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.TopBorder].Color = Color.Black; style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.BottomBorder].Color = Color.Black; style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.LeftBorder].Color = Color.Black; style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style.Borders[BorderType.RightBorder].Color = Color.Black; style.IsTextWrapped = true;//单元格内容自动换行 style.Font.IsBold = true; style.Pattern = BackgroundType.Solid; style.ForegroundColor = ColorTranslator.FromHtml("#B6DDE8"); style.Font.Color = Color.Black; cells.Merge(0, 0, 1, 8); //合并单元格 cells[0, 0].PutValue(newFileName); //填写内容 cells[0, 0].SetStyle(style); //给单元格关联样式 cells.Merge(0, 8, 1, 1); //合并单元格 cells[0, 8].PutValue(""); //填写内容 cells.SetRowHeight(0, 25); //设置行高 int rowIndex = 1; for (int i = 0; i < dt.Columns.Count; i++) { DataColumn col = dt.Columns[i]; string columnName = col.Caption ?? col.ColumnName; cells[rowIndex, i].PutValue(columnName); cells[rowIndex, i].SetStyle(style); } cells.SetRowHeight(rowIndex, 25);//设置行高 rowIndex++; style.Font.IsBold = false; foreach (DataRow row in dt.Rows) { for (int i = 0; i < dt.Columns.Count; i++) { style.Font.Color = Color.Black; style.ForegroundColor = Color.White; cells[rowIndex, i].PutValue(row[i].ToString()); if (rowIndex == dt.Rows.Count + 1) { style.Font.IsBold = true; style.ForegroundColor = ColorTranslator.FromHtml("#B6DDE8"); } cells[rowIndex, i].SetStyle(style); } cells.SetRowHeight(rowIndex, 25);//设置行高 rowIndex++; } for (int k = 0; k < dt.Columns.Count; k++) { sheet.AutoFitColumn(k, 0, dt.Rows.Count); } for (int col = 0; col < cells.Columns.Count; col++) { if (cells.GetColumnWidthPixel(col) > 300) { cells.SetColumnWidthPixel(col, 300); } } sheet.FreezePanes("A3", 2, dt.Columns.Count); sheet.AutoFilter.Range = "A2:" + ExcelConvert.ToName(dt.Columns.Count - 1) + "2"; wb.Save(filepath); result = savePath + newFileName + ".xlsx"; return(true); } catch (Exception e) { result = result + " DataTableToExcel: " + e.Message; return(false); } } catch (System.Exception e) { result = "失败:" + e.Message; } return(r); }
private void FillReport(Workbook wb, Dictionary <RowData, int> rowDataIndex) { int errorSheetIndex = wb.Worksheets.Add(); { int errc = 0; #region 命名 for (; ; errc++) { bool pass = true; string n = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")"); foreach (Aspose.Cells.Worksheet var in wb.Worksheets) { if (n == var.Name) { pass = false; break; } } if (pass) { break; } } #endregion wb.Worksheets[errorSheetIndex].Name = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")"); } string errorSheetName = wb.Worksheets[errorSheetIndex].Name; Worksheet errorSheet = wb.Worksheets[errorSheetIndex]; errorSheet.Cells[0, 0].PutValue("行號"); errorSheet.Cells[0, 1].PutValue("種類"); errorSheet.Cells[0, 2].PutValue("說明"); int errorSheetRowIndex = 1; Style errorStyle = wb.Styles[wb.Styles.Add()]; Style warningStyle = wb.Styles[wb.Styles.Add()]; Style errorStyle2 = wb.Styles[wb.Styles.Add()]; Style warningStyle2 = wb.Styles[wb.Styles.Add()]; errorStyle.Font.Color = Color.Red; errorStyle.Font.Underline = FontUnderlineType.Single; warningStyle.Font.Color = wb.GetMatchingColor(Color.Goldenrod); warningStyle.Font.Underline = FontUnderlineType.Single; warningStyle2.Font.Color = wb.GetMatchingColor(Color.Goldenrod); errorStyle2.Font.Color = Color.Red; #region 填入驗證結果 SortedList <int, RowData> markedRow = new SortedList <int, RowData>(); foreach (var row in _ErrorRows.Keys) { if (rowDataIndex.ContainsKey(row)) { markedRow.Add(rowDataIndex[row], row); } } foreach (var row in _WarningRows.Keys) { if (rowDataIndex.ContainsKey(row) && !markedRow.ContainsKey(rowDataIndex[row])) { markedRow.Add(rowDataIndex[row], row); } } foreach (var index in markedRow.Keys) { RowData row = markedRow[index]; if (_ErrorRows.ContainsKey(row)) { errorSheet.Cells[errorSheetRowIndex, 0].PutValue(index + 1); errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤"); errorSheet.Cells[errorSheetRowIndex, 2].PutValue(_ErrorRows[row]); errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle; errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2; errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2; errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[index, 0].Name); wb.Worksheets[0].Cells[index, 0].Style = errorStyle; wb.Worksheets[0].Hyperlinks.Add(index, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name); errorSheet.AutoFitRow(errorSheetRowIndex); errorSheetRowIndex++; } if (_WarningRows.ContainsKey(row)) { errorSheet.Cells[errorSheetRowIndex, 0].PutValue(index + 1); errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告"); errorSheet.Cells[errorSheetRowIndex, 2].PutValue(_WarningRows[row]); errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle; errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2; errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2; errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[index, 0].Name); if (!_ErrorRows.ContainsKey(row)) { wb.Worksheets[0].Cells[index, 0].Style = errorStyle; wb.Worksheets[0].Hyperlinks.Add(index, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name); } errorSheet.AutoFitRow(errorSheetRowIndex); errorSheetRowIndex++; } } #endregion errorSheet.AutoFitColumn(0); errorSheet.AutoFitColumn(1, 1, 500); errorSheet.AutoFitColumn(2, 1, 500); }
//Format report with gridview & excel file private Worksheet FormatReportExcel(Worksheet ws) { // Format file var style = ws.Cells.Rows[0].Style; style.Font.IsBold = true; style.Font.Size = 8; style.Number = 3; var flag = new StyleFlag(); flag.FontBold = true; flag.FontSize = true; flag.NumberFormat = false; // ----- Bold for header ws.Cells.Rows[0].ApplyStyle(style, flag); for (var i = 1; i <= ws.Cells.MaxDataRow; i++) { flag.FontBold = false; flag.FontSize = true; flag.NumberFormat = false; ws.Cells.Rows[i].ApplyStyle(style, flag); } // ----- list full format var listNumber = new List <string> { "Count", "Duration", "Read", "Write", "CPU", "hit" }; var listdatetime = new List <string> { "Time" }; var listdate = new List <string> { "Date", "Day" }; flag.FontBold = false; flag.FontSize = true; flag.NumberFormat = true; // ----- Rule 1: format data type is number for (var i = 0; i <= ws.Cells.MaxColumn; i++) { foreach (string field in listNumber) { if (ws.Cells[0, i].Value.ToString().ToUpper().Contains(field.ToUpper())) // format is number { ws.Cells.Columns[i].ApplyStyle(style, flag); } } } // ----- Rule 2: format data typeis datetime for (var i = 0; i < ws.Cells.MaxColumn; i++) { style.Custom = "yyyy/mm/dd hh:mm:ss"; foreach (string field in listdatetime) { if (ws.Cells[0, i].Value.ToString().ToUpper().Contains(field.ToUpper())) // column had format is datetime { ws.Cells.Columns[i].ApplyStyle(style, flag); } } } // ----- Rule 3: format data type is date for (var i = 0; i < ws.Cells.MaxColumn; i++) { style.Custom = "yyyy/mm/dd"; foreach (string field in listdate) { if (ws.Cells[0, i].Value.ToString().ToUpper().Contains(field.ToUpper())) // column had format is date { ws.Cells.Columns[i].ApplyStyle(style, flag); } } } // ----- auto fit for column for (var i = 0; i < ws.Cells.MaxColumn; i++) { if (ws.Cells[0, i].Value.ToString().ToUpper() != "TEXTDATA") { ws.AutoFitColumn(i); } } return(ws); }
public static MemoryStream ExportExcel(DataTable dt_excel, string sMapPath) { try { DirectoryInfo info = new DirectoryInfo(sMapPath); if (!info.Exists) { info.Create(); } Workbook doc = new Workbook(); doc.Shared = true; Worksheet oSheet = doc.Worksheets[0]; Style style = doc.Styles[doc.Styles.Add()]; style.Font.Name = "Arial"; style.Font.Size = 8; style.IsTextWrapped = true; Aspose.Cells.StyleFlag styleFlag = new Aspose.Cells.StyleFlag(); oSheet.Cells.ApplyStyle(style, styleFlag); for (int i = dt_excel.Columns.Count - 1; i >= 0; i--) { if (dt_excel.Columns[i].ColumnName.Contains("Hidden")) { dt_excel.Columns.RemoveAt(i); } } for (int i = 0; i < dt_excel.Columns.Count; i++) { oSheet.Cells[0, i].PutValue(dt_excel.Columns[i].ColumnName); oSheet.Cells[0, i].Style.Font.IsBold = true; } Style style1 = doc.Styles[doc.Styles.Add()]; //新增样式 style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中 style1.Font.Name = "Arial"; //文字字体 style1.Font.Size = 8; //文字大小 style1.IsLocked = false; //单元格解锁 //style1.Font.IsBold = true;//粗体 style1.ForegroundColor = System.Drawing.Color.FromArgb(0x99, 0xcc, 0xff); //设置背景色 style1.Pattern = BackgroundType.Solid; //设置背景样式 style1.IsTextWrapped = true; //单元格内容自动换行 for (int i = 0; i < dt_excel.Rows.Count; i++) { for (int j = 0; j < dt_excel.Columns.Count; j++) { if (dt_excel.Columns[j].ColumnName == "BookingDate" || dt_excel.Columns[j].ColumnName == "StatusDate") { oSheet.Cells[i + 1, j].PutValue(Convert.ToDateTime(dt_excel.Rows[i][j]).ToString("dd-MM-yyyy HH:mm:ss")); } else if (dt_excel.Columns[j].ColumnName == "WarningAlert" || dt_excel.Columns[j].ColumnName == "ConfirmationComment") { oSheet.Cells[i + 1, j].PutValue(dt_excel.Rows[i][j]); oSheet.Cells[i + 1, j].SetStyle(style1); } //else if (dt_excel.Columns[j].ColumnName == "BookingNumber") //{ // oSheet.Cells[i + 1, j].PutValue(dt_excel.Rows[i][j]); // oSheet.Cells[i + 1, j].SetStyle(style1); //} else { oSheet.Cells[i + 1, j].PutValue(dt_excel.Rows[i][j]); } } } for (int i = 0; i < oSheet.Cells.MaxDataColumn; i++) { if (i != 8 && i != 9) { oSheet.AutoFitColumn(i); } else { oSheet.Cells.SetColumnWidth(i, 50); } } //oSheet.column.SetColumnWidth(9, 40); //oSheet.Cells.SetColumnWidth(10, 40); return(doc.SaveToStream()); } catch (Exception ex) { throw ex; } }
private static void PopulateAttributeBased(Worksheet sheet, IEnumerable items, Type type, ExcelSheetAttribute sheetAttribute) { var columnList = type.GetProperties() .Where(x => x.GetCustomAttribute <ExcelColumnAttribute>() != null) .Select(x => new PropertyAttributeItem(x, x.GetCustomAttribute <ExcelColumnAttribute>())) .OrderBy(x => x.Attribute.Order) .ThenBy(x => x.Property.Name) .ToList(); var currentRow = sheetAttribute.StartRow; var currentColumn = sheetAttribute.StartColumn; if (sheetAttribute.Headers) { foreach (var column in columnList) { var cell = sheet.Cells[currentRow, currentColumn++]; cell.PutValue(column.Attribute.HeaderName ?? column.Property.Name); SetHeaderStyle(cell); } ImplementExcelSheetOptions(sheet, sheetAttribute, currentRow, currentColumn); currentRow++; currentColumn = sheetAttribute.StartColumn; } foreach (var dataRow in items) { for (int i = 0; i < columnList.Count; i++) { var column = columnList[i]; var value = column.Property.GetValue(dataRow); var cell = sheet.Cells[currentRow, currentColumn++]; if (column.Attribute.Formula) { if (column.Property.PropertyType != typeof(string)) { throw new ExcelColumnFormulaMismatchException(type, column.Property.Name); } cell.Formula = (string)value; continue; } cell.PutValue(value); //apply custom formatting. if (!String.IsNullOrEmpty(column.Attribute.Format)) { var style = cell.GetStyle(); style.Custom = column.Attribute.Format; cell.SetStyle(style); } } currentRow++; currentColumn = sheetAttribute.StartColumn; } //check if there are any auto-fit columns. Mostly to keep the currentColumn reset code template. if (columnList.Any(x => x.Attribute.AutoFit)) { foreach (var column in columnList) { if (column.Attribute.AutoFit) { sheet.AutoFitColumn(currentColumn); } currentColumn++; } currentColumn = sheetAttribute.StartColumn; } }