C#使用NPOI读取模板生成EXCEL
string currentDirectory = System.AppDomain.CurrentDomain.BaseDirectory;
// 读取Excel模板文件
FileStream fs = new FileStream(currentDirectory + "BoxPackinglist.xlsx", FileMode.Open, FileAccess.Read);
XSSFWorkbook workbook = new XSSFWorkbook(fs);
//模板中的sheet页
ISheet sheetTemp = workbook.GetSheet("Sheet1");
#region 创建sheet
sheetTemp.CopySheet(“新sheet页名字”);
ISheet sheet = workbook.GetSheet(“新sheet页名字”);
#endregion
#region 写入数据
sheet.GetRow(0).GetCell(0).SetCellValue("Packinglist ");
sheet.GetRow(4).GetCell(7).SetCellValue("N.W 总净重 ：" + item.netweightsum + "KG");
sheet.GetRow(4).GetCell(10).SetCellValue("G.W 总毛重：" + item.grossweightsum + "KG");
#endregion
//开始插入行索引从第八行开始复制插入行(带格式)
int startRow = 8;
//插入5行
sheet.ShiftRows(startRow, sheet.LastRowNum, 5, true, false);
for (int row = startRow - 1; row < startRow + 4; row++)
{
if(row >= startRow)
{
var rowSource = sheet.GetRow(7);
var rowStyle = rowSource.RowStyle;//获取当前行样式
var rowInsert = sheet.CreateRow(row);
if (rowStyle != null)
rowInsert.RowStyle = rowStyle;
rowInsert.Height = rowSource.Height;
for (int col = 0; col < rowSource.LastCellNum; col++)
{
var cellsource = rowSource.GetCell(col);
var cellInsert = rowInsert.CreateCell(col);
var cellStyle = cellsource.CellStyle;
//设置单元格样式
if (cellStyle != null)
cellInsert.CellStyle = cellsource.CellStyle;
}
}
}
workbook.RemoveSheetAt(0);
// 保存文件并关闭FileStream
fs.Close();
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.Write(baos);
byte[] bt = baos.ToByteArray();
string base64Str = Convert.ToBase64String(bt);