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
}
从EXCEL模板中插入新行
最新推荐文章于 2022-12-06 11:01:58 发布