public static void DataTableToExcelForSchedule_NPOI(string scheduleTemp_filePath, System.Data.DataTable dt, List <string> weekdays)
{
string scheduled_fileName = string.Format("护理排班表－护理部-{0}.xlsx", weekdays[0]);
string scheduled_fileFullPath = Miscellaneous.GetExcelsFileFullPath(scheduled_fileName);
using (FileStream temp_fs = File.Open(scheduleTemp_filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook temp_workbook = new XSSFWorkbook(temp_fs);
ISheet sheet = temp_workbook.GetSheetAt(0);
///---读取EXcel单元格的值
///---这里是获取固定位置上的值
///---这里获取的是 行索引为0,列索引为0的单元格的值
int dateColIndex = 4;
for (int i = 0; i < weekdays.Count; i++)
{
IRow row = sheet.GetRow(2);
ICell cell = row.GetCell(dateColIndex);
if (cell.IsMergedCell)
{
cell.SetCellValue(Convert.ToDateTime(weekdays[i]).ToString("yyyy-MM.dd"));
}
dateColIndex += 2;
}
//if(File.Exists(scheduled_fileFullPath)){
// File.Delete(scheduled_fileFullPath);
//}
XDocument nurseDoc = XMLHelper.GetNursersAllData(Miscellaneous.GetNurseXMLFullPath());
//保存护士值班数据, NPOI中定义Excel 单元格从0开始，而不是1
int baseDataRow = 6; //从单元格第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();
IRow xlsRow = sheet.CreateRow(baseDataRow);
xlsRow.CreateCell(1).SetCellValue(nurseLevel);
xlsRow.CreateCell(2).SetCellValue(nurseName);
//为每一个填入值班数据，表格为列 5 - 18
xlsRow.CreateCell(4).SetCellValue(monday_mor);
xlsRow.CreateCell(5).SetCellValue(monday_after);
xlsRow.CreateCell(6).SetCellValue(tuesday_mor);
xlsRow.CreateCell(7).SetCellValue(tuesday_after);
xlsRow.CreateCell(8).SetCellValue(wednesday_mor);
xlsRow.CreateCell(9).SetCellValue(wednesday_after);
xlsRow.CreateCell(10).SetCellValue(thursday_mor);
xlsRow.CreateCell(11).SetCellValue(thursday_after);
xlsRow.CreateCell(12).SetCellValue(friday_mor);
xlsRow.CreateCell(13).SetCellValue(friday_after);
xlsRow.CreateCell(14).SetCellValue(saturday_mor);
xlsRow.CreateCell(15).SetCellValue(saturday_after);
xlsRow.CreateCell(16).SetCellValue(sunday_mor);
xlsRow.CreateCell(17).SetCellValue(sunday_after);
ICellStyle style = temp_workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
IFont font = temp_workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
style.BorderBottom = BorderStyle.Thin;
//设置居中显示
for (int style_index = 1; style_index < 18; style_index++)
{
if (xlsRow.GetCell(style_index) != null) //排除cell index =3
{
xlsRow.GetCell(style_index).CellStyle = style;
}
}
baseDataRow++;
}
if (File.Exists(scheduled_fileFullPath))
{
File.Delete(scheduled_fileFullPath);
}
using (FileStream fs = File.Open(scheduled_fileFullPath, FileMode.Create, FileAccess.Write)) {
temp_workbook.Write(fs);
}
}
}
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();
}