C# NPOI操作execl

上一篇介绍了Java操作execl用的是POI,这一篇介绍c#操作execl,采用NPOI，
所需引用：NPOI
NPOI.OOXML
NPOI.OpenXml4Net
NPOI.OpenXmlFormats
下载地址：http://download.csdn.net/download/qq_37791764/10173729

代码部分：

            //读取excel文件的路径            string filePath = @"" + Server.MapPath("~/") + "PBTable\\execl\\排班表.xls";            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);            //创建workbook            HSSFWorkbook wk = new HSSFWorkbook(fs);            //在第一个sheet写入操作            NPOI.SS.UserModel.Sheet sheet = (HSSFSheet)wk.GetSheetAt(0);            //创建            Row row0 = sheet.CreateRow(0);            Row row1 = sheet.CreateRow(1);            Row row2 = sheet.CreateRow(2);            Row row3 = sheet.CreateRow(3);            Row row4 = sheet.CreateRow(4);            HSSFPalette Salepalette = wk.GetCustomPalette();            CellStyle SalecolorStyle = wk.CreateCellStyle();            //设置边框格式              SalecolorStyle.BorderBottom = NPOI.SS.UserModel.CellBorderType.THIN;            SalecolorStyle.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;            SalecolorStyle.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;            SalecolorStyle.BorderTop = NPOI.SS.UserModel.CellBorderType.THIN;            //垂直对齐            SalecolorStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;            //水平对齐            SalecolorStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;            for (int i = 0; i < 35; i++)            {                Cell cell0 = row0.CreateCell(i);                Cell cell1 = row1.CreateCell(i);                Cell cell2 = row2.CreateCell(i);                Cell cell3 = row3.CreateCell(i);                Cell cell4 = row4.CreateCell(i);                if(i==2){                 row1.GetCell(i).SetCellValue("/日期");                 row2.GetCell(i).SetCellValue("节日/活动");                 row3.GetCell(i).SetCellValue("营业展开计划");                 row4.GetCell(i).SetCellValue("事项");                }                if(i==(34)){                    row0.GetCell(i).SetCellValue("工时");                }                if (i >= 3 && i < (MonthDayCount+3))                {                sheet.GetRow(0).GetCell(i).SetCellValue(CaculateWeekDay(FirstMonthDate.AddDays(i - 3).Year, FirstMonthDate.AddDays(i - 3).Month, FirstMonthDate.AddDays(i - 3).Day));                sheet.GetRow(1).GetCell(i).SetCellValue(FirstMonthDate.AddDays(i - 3).ToString("dd/MMM", new System.Globalization.CultureInfo("en-US")));//月份变成英文                }                cell0.CellStyle = SalecolorStyle;                cell1.CellStyle = SalecolorStyle;                cell2.CellStyle = SalecolorStyle;                cell3.CellStyle = SalecolorStyle;                cell4.CellStyle = SalecolorStyle;            }            string strSql = @"SELECT p.positionType ,  p.name, (CASE   WHEN  p.work_no  like '5%' THEN '兼职'  ELSE  '全职' END ) work_no,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d1) d1,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d2) d2,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d3) d3,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d4) d4,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d5) d5,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d6) d6,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d7) d7,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d8) d8,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d9) d9,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d10) d10,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d11) d11,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d12) d12,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d13) d13,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d14) d14,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d15) d15,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d16) d16,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d17) d17,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d18) d18,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d19) d19,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d20) d20,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d21) d21,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d22) d22,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d23) d23,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d24) d24,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d25) d25,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d26) d26,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d27) d27,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d28) d28,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d29) d29,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d30) d30,(SELECT 班次 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d31) d31,(SELECT  工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d1)  dd1,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d2)  dd2,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d3)  dd3,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d4)  dd4,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d5)  dd5,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d6)  dd6,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d7)  dd7,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d8)  dd8,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d9)  dd9,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d10) dd10,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d11) dd11,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d12) dd12,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次= s.d13) dd13,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d14) dd14,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d15) dd15,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d16) dd16,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d17) dd17,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d18) dd18,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d19) dd19,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d20) dd20,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d21) dd21,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d22) dd22,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d23) dd23,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d24) dd24,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d25) dd25,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d26) dd26,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d27) dd27,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d28) dd28,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d29) dd29,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d30) dd30,(SELECT 工作时间 FROM dbo.BCMaintain WHERE BCMaintain.系统班次=s.d31) dd31,(SELECT work_hours '工作时间' FROM [10.10.8.42].hr_nec.dbo.attendance_sum WHERE  person_id=p.id AND calendar_id=c.id) '工作时间' FROM [10.10.8.42].hr_nec.dbo.PERSON  p ,[10.10.8.42].hr_nec.dbo.staff_arrange s ,[10.10.8.42].hr_nec.dbo.calendar c WHERE p.id=s.person_id AND s.calendar_id=c.id AND c.period=@period AND p.deptno=@deptno AND p.status='在职' ORDER  BY p.positionType, p.work_no";            DataTable dt = GetData(strSql,new SqlParameter[]{                 new SqlParameter("@deptno",shopNo),                 new SqlParameter("@period",BeginDate+" - "+EndDate)            });            //数据条数            int dt_i =5;             foreach (DataRow excelDr in dt.Rows)            {               Row row = sheet.CreateRow(dt_i);                for (int j = 0; j < 35;j++ )                {                    Cell cell = row.CreateCell(j);                    cell.CellStyle = SalecolorStyle;                }                sheet.GetRow(dt_i).GetCell(0).SetCellValue(excelDr["positionType"].ToString());                sheet.GetRow(dt_i).GetCell(1).SetCellValue(excelDr["work_no"].ToString());                sheet.GetRow(dt_i).GetCell(2).SetCellValue(excelDr["name"].ToString());                sheet.GetRow(dt_i).GetCell(3).SetCellValue(excelDr["d1"].ToString());                sheet.GetRow(dt_i).GetCell(4).SetCellValue(excelDr["d2"].ToString());                sheet.GetRow(dt_i).GetCell(5).SetCellValue(excelDr["d3"].ToString());                sheet.GetRow(dt_i).GetCell(6).SetCellValue(excelDr["d4"].ToString());                sheet.GetRow(dt_i).GetCell(7).SetCellValue(excelDr["d5"].ToString());                sheet.GetRow(dt_i).GetCell(8).SetCellValue(excelDr["d6"].ToString());                sheet.GetRow(dt_i).GetCell(9).SetCellValue(excelDr["d7"].ToString());                sheet.GetRow(dt_i).GetCell(10).SetCellValue(excelDr["d8"].ToString());                sheet.GetRow(dt_i).GetCell(11).SetCellValue(excelDr["d9"].ToString());                sheet.GetRow(dt_i).GetCell(12).SetCellValue(excelDr["d10"].ToString());                sheet.GetRow(dt_i).GetCell(13).SetCellValue(excelDr["d11"].ToString());                sheet.GetRow(dt_i).GetCell(14).SetCellValue(excelDr["d12"].ToString());                sheet.GetRow(dt_i).GetCell(15).SetCellValue(excelDr["d13"].ToString());                sheet.GetRow(dt_i).GetCell(16).SetCellValue(excelDr["d14"].ToString());                sheet.GetRow(dt_i).GetCell(17).SetCellValue(excelDr["d15"].ToString());                sheet.GetRow(dt_i).GetCell(18).SetCellValue(excelDr["d16"].ToString());                sheet.GetRow(dt_i).GetCell(19).SetCellValue(excelDr["d17"].ToString());                sheet.GetRow(dt_i).GetCell(20).SetCellValue(excelDr["d18"].ToString());                sheet.GetRow(dt_i).GetCell(21).SetCellValue(excelDr["d19"].ToString());                sheet.GetRow(dt_i).GetCell(22).SetCellValue(excelDr["d20"].ToString());                sheet.GetRow(dt_i).GetCell(23).SetCellValue(excelDr["d21"].ToString());                sheet.GetRow(dt_i).GetCell(24).SetCellValue(excelDr["d22"].ToString());                sheet.GetRow(dt_i).GetCell(25).SetCellValue(excelDr["d23"].ToString());                sheet.GetRow(dt_i).GetCell(26).SetCellValue(excelDr["d24"].ToString());                sheet.GetRow(dt_i).GetCell(27).SetCellValue(excelDr["d25"].ToString());                sheet.GetRow(dt_i).GetCell(28).SetCellValue(excelDr["d26"].ToString());                sheet.GetRow(dt_i).GetCell(29).SetCellValue(excelDr["d27"].ToString());                sheet.GetRow(dt_i).GetCell(30).SetCellValue(excelDr["d28"].ToString());                sheet.GetRow(dt_i).GetCell(31).SetCellValue(excelDr["d29"].ToString());                sheet.GetRow(dt_i).GetCell(32).SetCellValue(excelDr["d30"].ToString());                sheet.GetRow(dt_i).GetCell(33).SetCellValue(excelDr["d31"].ToString());                sheet.GetRow(dt_i).GetCell(34).SetCellValue(excelDr["工作时间"].ToString());                if (excelDr["work_no"].ToString()=="全职")                {                    HSSFPalette Salepalette1 = wk.GetCustomPalette();                    CellStyle SalecolorStyle1 = wk.CreateCellStyle();                    //设置边框格式                      SalecolorStyle1.BorderBottom = NPOI.SS.UserModel.CellBorderType.THIN;                    SalecolorStyle1.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;                    SalecolorStyle1.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;                    SalecolorStyle1.BorderTop = NPOI.SS.UserModel.CellBorderType.THIN;                    //垂直对齐                    SalecolorStyle1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;                    //水平对齐                    SalecolorStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;                    SalecolorStyle1.FillForegroundColor = 13;  //具体数字代表的颜色看NPOI颜色对照表                    SalecolorStyle1.FillPattern = FillPatternType.SOLID_FOREGROUND;                    sheet.GetRow(dt_i).GetCell(2).CellStyle = SalecolorStyle1;                 }                 dt_i++;            }     string datetime = DateTime.Now.ToString("yyyyMMddHHmmssffff");            FileStream file = new FileStream(@"" + Server.MapPath("~/") + "PBTable\\execl\\" + datetime + "排班表.xls", FileMode.OpenOrCreate);            wk.Write(file);            file.Flush();            file.Close();            wk = null;