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