コード例 #1
0
ファイル: ExcelHelper.cs プロジェクト: reksHu/ScheduleOrder
        public static void DataTableToExcelForSchedule(string filePath, System.Data.DataTable dt, List <string> weekdays)
        {
            Excel.Application excel = new Excel.Application();
            excel.Visible = false;

            Excel.Workbook workbook = excel.Application.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                                                       , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                                                       , Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets["Sheet1"];

            ///---读取EXcel单元格的值
            ///---这里是获取固定位置上的值
            ///---这里获取的是  行索引为1,列索引为1的单元格的值
            //Excel.Range range = (Excel.Range)sheet.Cells[1, 1];
            int dateColIndex = 5;

            for (int i = 0; i < weekdays.Count; i++)   //读取第三行周时间单元

            {
                Excel.Range mergedCells = sheet.Cells[3, dateColIndex];
                if (mergedCells.MergeCells)
                {
                    sheet.Cells[3, dateColIndex] = Convert.ToDateTime(weekdays[i]).ToString("yyyy-MM.dd");
                }
                dateColIndex += 2;
            }

            //设置护士层级
            XDocument nurseDoc = XMLHelper.GetNursersAllData(Miscellaneous.GetNurseXMLFullPath());



            //保存护士值班数据
            int baseDataRow = 7;

            for (int rowIndex = 0; rowIndex < dt.Rows.Count - 1; rowIndex++)
            {
                string nurseName     = dt.Rows[rowIndex][0] == null?"":dt.Rows[rowIndex][0].ToString();
                var    nurseLevelObj = nurseDoc.Descendants("Nurser").Where(e => e.Element("NurseName").Value.Equals(nurseName)).Select(e => e.Element("Level").Value);
                var    lists         = nurseDoc.Descendants("Nurser").Where(e => e.Element("NurseName").Value.Equals(nurseName)).ToList();
                string nurseLevel    = nurseLevelObj == null ? "" : nurseLevelObj.FirstOrDefault();

                string monday_mor    = dt.Rows[rowIndex][1] == null ? "" : dt.Rows[rowIndex][1].ToString();
                string monday_after  = dt.Rows[rowIndex][2] == null?"":dt.Rows[rowIndex][2].ToString();
                string tuesday_mor   = dt.Rows[rowIndex][3] == null?"":dt.Rows[rowIndex][3].ToString();
                string tuesday_after = dt.Rows[rowIndex][4] == null?"":dt.Rows[rowIndex][4].ToString();

                string wednesday_mor   = dt.Rows[rowIndex][5] == null?"":dt.Rows[rowIndex][5].ToString();
                string wednesday_after = dt.Rows[rowIndex][6] == null?"":dt.Rows[rowIndex][6].ToString();

                string thursday_mor   = dt.Rows[rowIndex][7] == null ? "" : dt.Rows[rowIndex][7].ToString();
                string thursday_after = dt.Rows[rowIndex][8] == null ? "" : dt.Rows[rowIndex][8].ToString();

                string friday_mor   = dt.Rows[rowIndex][9] == null ? "" : dt.Rows[rowIndex][9].ToString();
                string friday_after = dt.Rows[rowIndex][10] == null ? "" : dt.Rows[rowIndex][10].ToString();

                string saturday_mor   = dt.Rows[rowIndex][11] == null ? "" : dt.Rows[rowIndex][11].ToString();
                string saturday_after = dt.Rows[rowIndex][12] == null ? "" : dt.Rows[rowIndex][12].ToString();

                string sunday_mor   = dt.Rows[rowIndex][13] == null ? "" : dt.Rows[rowIndex][13].ToString();
                string sunday_after = dt.Rows[rowIndex][14] == null ? "" : dt.Rows[rowIndex][14].ToString();

                Excel.Range xlsRow = sheet.Rows[baseDataRow, Missing.Value];
                xlsRow.Insert(Excel.XlDirection.xlDown, Missing.Value);
                sheet.Cells[baseDataRow, 2] = nurseLevel;
                sheet.Cells[baseDataRow, 3] = nurseName;

                //为每一个填入值班数据,表格为列 5 - 18
                sheet.Cells[baseDataRow, 5]  = monday_mor;
                sheet.Cells[baseDataRow, 6]  = monday_after;
                sheet.Cells[baseDataRow, 7]  = tuesday_mor;
                sheet.Cells[baseDataRow, 8]  = tuesday_after;
                sheet.Cells[baseDataRow, 9]  = wednesday_mor;
                sheet.Cells[baseDataRow, 10] = wednesday_after;
                sheet.Cells[baseDataRow, 11] = thursday_mor;
                sheet.Cells[baseDataRow, 12] = thursday_after;
                sheet.Cells[baseDataRow, 13] = friday_mor;
                sheet.Cells[baseDataRow, 14] = friday_after;
                sheet.Cells[baseDataRow, 15] = saturday_mor;
                sheet.Cells[baseDataRow, 16] = saturday_after;
                sheet.Cells[baseDataRow, 17] = sunday_mor;
                sheet.Cells[baseDataRow, 18] = sunday_after;

                baseDataRow++;
            }
            //当有拍板信息时候就设置格式
            if (baseDataRow > 7)
            {
                string      endrow = "S" + (baseDataRow - 1);
                Excel.Range range  = sheet.get_Range("A7", endrow);
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //设置文字居中
                range.Borders.LineStyle   = 1;                             //设置
            }
            string fileName  = string.Format("护理排班表-护理部-{0}.xls", weekdays[0]);
            string savedPath = Miscellaneous.GetExcelsFileFullPath(fileName);

            workbook.SaveAs(savedPath);
            workbook.Close();
            sheet    = null;
            workbook = null;
            excel.Quit();
            excel = null;
            ///---回收系统资源
            GC.Collect();
        }