从EXCEL模板中插入新行

来源：互联网发布：阿里云服务器是干嘛的编辑：程序博客网时间：2024/06/06 17:05
using NPOI.SS.UserModel;using NPOI.HSSF.UserModel;public void Export(){HSSFWorkbook workbook;string modelExlPath = Server.MapPath("../") + "\\" + Text.xls;            //读入模板            using (FileStream file = new FileStream(modelExlPath, FileMode.Open, FileAccess.Read))            {                workbook = new HSSFWorkbook(file);                file.Close();            }        ISheet sheet = workbook.GetSheet("Sheet1");         IRow row = null;         ICell cell = null;           int InsertRowIndex = 13;//指定在第几行插入，我们这里测试用第3行，对应NPOI的索引值2，因为从0起            int InsertRowCount =10;//要插入的行数             IRow mySourceStyleRow = sheet.GetRow(InsertRowIndex - 1);//获取源格式行            MyInsertRow(sheet, InsertRowIndex, InsertRowCount, mySourceStyleRow);   MemoryStream stream = new MemoryStream();            workbook.Write(stream);            NPOIHelper.RenderToBrowser(stream, HttpContext.Current, "插入新行.xls");}//浏览器输出     public  static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)       {           //if (context.Request.Browser.Browser == "IE")               fileName = HttpUtility.UrlEncode(fileName);           context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);           context.Response.BinaryWrite(ms.ToArray());       }        //第一个:指定操作的Sheet。        //第二个:指定在第几行指入（插入行的位置）        //第三个:指定要插入多少行        //第四个:源单元格格式的行，        private void MyInsertRow(ISheet sheet, int 插入行, int 插入行总数, IRow 源格式行)        {            #region 批量移动行            sheet.ShiftRows(                插入行,                                 //--开始行                sheet.LastRowNum,                      //--结束行                插入行总数,                             //--移动大小(行数)--往下移动                true,                                  //是否复制行高                false//,                               //是否重置行高                //true                                 //是否移动批注            );            #endregion            #region 对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源(即：插入行-1的那一行)            for (int i = 插入行; i < 插入行 + 插入行总数 - 1; i++)            {                IRow targetRow = null;                ICell sourceCell = null;                ICell targetCell = null;                targetRow = sheet.CreateRow(i + 1);                for (int m = 源格式行.FirstCellNum; m < 源格式行.LastCellNum; m++)                {                    sourceCell = 源格式行.GetCell(m);                    if (sourceCell == null)                        continue;                    targetCell = targetRow.CreateCell(m);                    //targetCell..Encoding = sourceCell.Encoding;                    targetCell.CellStyle = sourceCell.CellStyle;                    targetCell.SetCellType(sourceCell.CellType);                }                //CopyRow(sourceRow, targetRow);                //Util.CopyRow(sheet, sourceRow, targetRow);            }            IRow firstTargetRow = sheet.GetRow(插入行);            ICell firstSourceCell = null;            ICell firstTargetCell = null;            for (int m = 源格式行.FirstCellNum; m < 源格式行.LastCellNum; m++)            {                firstSourceCell = 源格式行.GetCell(m);                if (firstSourceCell == null)                    continue;                firstTargetCell = firstTargetRow.CreateCell(m);                //firstTargetCell.Encoding = firstSourceCell.Encoding;                firstTargetCell.CellStyle = firstSourceCell.CellStyle;                firstTargetCell.SetCellType(firstSourceCell.CellType);            }            #endregion        }
0 0