NPOI2.0操作导出excel

1.下载NPOI

2.引入命名空间

using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;

3.导出excel代码

/// <summary>
        /// 从数据库查询数据并导出excel
        /// </summary>
        /// <param name="InventBatchId"></param>
        protected void ExportExcel(string g)
        {
            UserProFileBLL bll = new UserProFileBLL();
            System.Data.DataTable dt = new System.Data.DataTable();
            //查询数据库语句
            string sql = "select * from InventBatch a  where a.InventBatchId in(" + g + ")";
            dt = bll.QueryDT(sql);
            if (dt.Rows.Count > 0)
            {
                //打开模板
                string modelExlPath = Server.MapPath(@"~/Template/模板.xls");
                HSSFWorkbook hssfworkbookDown;
                //读入刚复制的要导出的excel文件
                using (FileStream file = new FileStream(modelExlPath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbookDown = new HSSFWorkbook(file);
                    file.Close();
                }
                HSSFSheet sheet1 = (HSSFSheet)hssfworkbookDown.GetSheetAt(0);
                IRow sourceRow = sheet1.GetRow(6);
                

                MyInsertRow(sheet1, 6, dt.Rows.Count - 1);
                int i = 0;
                string Cust = " ";
                
                foreach (DataRow dr in dt.Rows)
                {
                    i++;

                    IRow row = sheet1.CreateRow(5 + i);
                    //获取源行的高度
                    row.Height = sourceRow.Height;
                    //复制单元格样式start
                    for (int m = sourceRow.FirstCellNum; m < sourceRow.LastCellNum; m++)
                    {
                        ICell cell = row.CreateCell(m);
                        // cell.SetCellValue("0");
                        ICellStyle cellStyle = sourceRow.Cells[m].CellStyle;
                        cellStyle.Alignment = HorizontalAlignment.Center;
                        cell.CellStyle = cellStyle;

                        cell.SetCellType(sourceRow.Cells[m].CellType);
                    }
                    //复制单元格样式end
                    //合并单元格
                    sheet1.AddMergedRegion(new CellRangeAddress(5 + i, 5 + i, 4, 5));

                    //指定单元格插入图片start
                    byte[] bytes = System.IO.File.ReadAllBytes(Server.MapPath(@"~/images/IDO.png"));
                    int pictureIdx = hssfworkbookDown.AddPicture(bytes, PictureType.PNG);
                    HSSFSheet sheet2 = (HSSFSheet)hssfworkbookDown.GetSheetAt(0);
                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet2.CreateDrawingPatriarch();
                    HSSFClientAnchor anchor = new HSSFClientAnchor(255, 1, 1023, 100, 8, i+5, 8, i+5);
                    HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                    pict.Resize();
                    //指定单元格插入图片end

                    //向excel单元格中写入数据start
                    if (Cust.IndexOf(dr["a"].ToString()) < 0)
                    {
                        Cust += dr["a"].ToString() + "/";
                    }
                    row.GetCell(0).SetCellValue(i.ToString());
                    string content = "原资料：" + dr["f"].ToString() + " ";
                    if (Convert.ToDouble(dr["e"].ToString()) == 0)
                    {
                        content += "D" + Math.Floor(Convert.ToDouble(dr["KTL_ViceStoneQty"])*100)/100+ "\n";
                    }
                    else
                    {
                        content += "D" + Math.Floor(Convert.ToDouble(dr["d"]) * 100) / 100;
                        if (Convert.ToDouble(dr["c"]) == 0)
                        {
                            content += "\n";
                        }
                        else
                        {
                            content +=" d" + Math.Floor(Convert.ToDouble(dr["b"]) * 100) / 100 + "\n";
                        }
                    }
                    row.GetCell(4).SetCellValue(content);
                    row.GetCell(11).SetCellValue(dr["g"].ToString());
                    //向excel单元格中写入数据end

                }
                IRow headRow = sheet1.GetRow(1);
                headRow.GetCell(11).SetCellValue(DateTime.Now.ToString("yyyy/MM/dd"));
                headRow.GetCell(2).SetCellValue(Cust.Substring(0,Cust.Length-1));
                //添加函数
                IRow footRow = sheet1.GetRow(dt.Rows.Count+6);
                footRow.GetCell(3).SetCellFormula("sum(D7:D" + (dt.Rows.Count + 6) + ")");
                System.IO.MemoryStream ms = new System.IO.MemoryStream();

                //不保存excel在服务器直接输出到客户端进行下载
                hssfworkbookDown.Write(ms);
                Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
                Response.BinaryWrite(ms.ToArray());
                hssfworkbookDown = null;
                ms.Close();
                ms.Dispose();
            }


        }

        /// <summary>
        /// 将指定行数向下移动
        /// </summary>
        /// <param name="targetSheet">sheet</param>
        /// <param name="fromRowIndex">起始第几行</param>
        /// <param name="rowCount">需要移动的行数</param>
        private void MyInsertRow(HSSFSheet targetSheet, int fromRowIndex, int rowCount)
        {

            targetSheet.ShiftRows(fromRowIndex + 1, targetSheet.LastRowNum, rowCount, true, false, true);
        }