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