将班表信息导出Excel表格保存到本地
要求：文档名称为【XXXX】年X月值班表
文档显示的效果：
实现代码：
1 //导出Excel值班表 2 private void btn_export_1_Click(object sender, RoutedEventArgs e) 3 { 4 try 5 { 6 //表格数据源为空是不执行导出操作 7 if (ltDataSchedule == null || ltDataSchedule.Count() <= 0) return; 8 9 //获取选择的年和月份 10 int select_year = rcb_year.SelectedItem == null ? DateTime.Now.Year : Convert.ToInt32((rcb_year.SelectedItem as RadComboBoxItem).Tag); 11 int select_month = rcb_month.SelectedItem == null ? DateTime.Now.Month : Convert.ToInt32((rcb_month.SelectedItem as RadComboBoxItem).Tag); 12 13 string title = select_year + "年" + select_month + "月值班表"; 14 15 SaveFileDialog dialog = new SaveFileDialog(); 16 dialog.DefaultExt = "xls"; 17 dialog.Filter = "Excel文件(.xls)|*.xls"; 18 dialog.FilterIndex = 1; 19 dialog.DefaultFileName = title; 20 if (dialog.ShowDialog() == true) 21 { 22 using (Stream stream = dialog.OpenFile()) 23 { 24 XlsDocument xls = new XlsDocument(); //新建一个xls文档 25 Worksheet sheet = xls.Workbook.Worksheets.Add(title);//填加名为"第一个Sheet Demo"的sheet页 26 //表格列数 27 for (int i = 0; i <= totalDays; i++) 28 { 29 ColumnInfo conlInfo = new ColumnInfo(xls, sheet); 30 conlInfo.ColumnIndexStart = (ushort)i; 31 conlInfo.ColumnIndexEnd = (ushort)i; 32 conlInfo.Width = 18 * 256; // 列的宽度计量单位为 1/256 字符宽 33 sheet.AddColumnInfo(conlInfo); 34 } 35 Cells cells = sheet.Cells; 36 37 #region 创建单元格样式 38 //标题:文字垂直水平居中、加粗 39 XF xfTitle = xls.NewXF(); 40 xfTitle.Font.Bold = true; // 是否加粗 41 xfTitle.Font.Weight = org.in2bits.MyXls.FontWeight.ExtraBold;// 字体宽度 42 xfTitle.Font.Height = 300;// 设定字大小 43 44 //表头样式 45 XF xf_head = xls.NewXF(); 46 xf_head.Font.Bold = true; 47 xf_head.Font.Weight = org.in2bits.MyXls.FontWeight.ExtraBold; 48 xf_head.HorizontalAlignment = HorizontalAlignments.Centered;// 设定文字居中 49 xf_head.VerticalAlignment = VerticalAlignments.Centered;// 垂直居中 50 xf_head.Font.Height = 250; 51 xf_head.VerticalAlignment = VerticalAlignments.Centered; 52 xf_head.Font.FontName = "仿宋"; 53 54 //内容样式 55 XF xf = xls.NewXF(); 56 xf.HorizontalAlignment = HorizontalAlignments.Centered;// 设定文字居中 57 xf.VerticalAlignment = VerticalAlignments.Centered;// 垂直居中 58 xf.TextWrapRight = false; 59 xf.Font.Height = 220; 60 #endregion 61 #region 填充第一行的列标题 62 //标题 63 MergeArea maTitle = new MergeArea(1, 1, 1, totalDays + 1);// 先行后列：合并单元格(合并第1行、第1列 到 第1行、第13列) 64 sheet.AddMergeArea(maTitle); 65 cells.Add(1, 1, title, xfTitle); 66 //姓名 67 MergeArea maorg1 = new MergeArea(2, 2, 1, 1); 68 sheet.AddMergeArea(maorg1); 69 cells.Add(2, 1, "姓名", xf_head); 70 71 for (int i = 1; i <= totalDays; i++) 72 { 73 MergeArea maorg = new MergeArea(2, 2, i + 1, i + 1); 74 sheet.AddMergeArea(maorg); 75 DateTime dtime = Convert.ToDateTime(select_year + "-" + select_month + "-" + i); 76 string cols = i + "号/" + week_str[Convert.ToInt16(dtime.DayOfWeek)]; 77 cells.Add(2, i + 1, cols, xf_head); 78 } 79 #endregion 80 #region 循环数据源 逐行添加数据 81 82 for (int i = 0; i < ltDataSchedule.Count(); i++) 83 { 84 int StatRow = i + 3;//从表格的第三行开始添加数据 85 for (int k = 0; k <= totalDays; k++) 86 { 87 #region 填充列值 88 if (k == 0) 89 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].name, xf); //给单元格赋值：行，列，列值，单元格样式 90 else if (k == 1) 91 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day1, xf); 92 else if (k == 2) 93 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day2, xf); 94 else if (k == 3) 95 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day3, xf); 96 else if (k == 4) 97 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day4, xf); 98 else if (k == 5) 99 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day5, xf); 100 else if (k == 6) 101 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day6, xf); 102 else if (k == 7) 103 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day7, xf); 104 else if (k == 8) 105 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day8, xf); 106 else if (k == 9) 107 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day9, xf); 108 else if (k == 10) 109 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day10, xf); 110 else if (k == 11) 111 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day11, xf); 112 else if (k == 12) 113 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day12, xf); 114 else if (k == 13) 115 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day13, xf); 116 else if (k == 14) 117 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day14, xf); 118 else if (k == 15) 119 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day15, xf); 120 else if (k == 16) 121 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day16, xf); 122 else if (k == 17) 123 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day17, xf); 124 else if (k == 18) 125 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day18, xf); 126 else if (k == 19) 127 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day19, xf); 128 else if (k == 20) 129 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day20, xf); 130 else if (k == 21) 131 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day21, xf); 132 else if (k == 22) 133 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day22, xf); 134 else if (k == 23) 135 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day23, xf); 136 else if (k == 24) 137 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day24, xf); 138 else if (k == 25) 139 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day25, xf); 140 else if (k == 26) 141 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day26, xf); 142 else if (k == 27) 143 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day27, xf); 144 else if (k == 28) 145 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day28, xf); 146 else if (k == 29) 147 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day29, xf); 148 else if (k == 30) 149 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day30, xf); 150 else if (k == 31) 151 sheet.Cells.Add(StatRow, k + 1, ltDataSchedule[i].day31, xf); 152 else { sheet.Cells.Add(StatRow, k + 1, "", xf); } 153 #endregion 154 } 155 } 156 #endregion 157 xls.Save(stream); 158 } 159 MessageBox.Show("导出成功"); 160 } 161 } 162 catch (Exception ex) 163 { 164 RadWindow.Alert(ex.Message); 165 } 166 }