4816
社区成员
NPOI如何向已存在的Excel文件中插入一行数据
很多例子在利用NPOI向Excel写数据时，都是创建一个新的文件，或者通过模版创建新文件，然后再写数据，输出新的文件。
现在就只需要在已有的Excel中插入几条数据，该如何实现？
现在就只需要在已有的Excel中插入几条数据，该如何实现？
...全文
请发表友善的回复…
发表回复
熙风 2014-06-24
- 打赏
- 举报
/// <summary>
/// 用模板导出Excel
/// </summary>
/// <param name="table"></param>
/// <param name="strFileName">导出路径</param>
/// <param name="templetPath">模板路径</param>
/// <param name="startRow">从第几行开始写数据,从1开始</param>
public static void ExportExcelByTemple(System.Data.DataTable dtSource, string strFileName, string templetPath, int rowHeight, int startRow)
{
try
{
HSSFWorkbook workbook = getWorkBook(templetPath);
HSSFSheet sheet = getSheet(workbook);
writeData(workbook, sheet, dtSource, strFileName, rowHeight, startRow);
saveData(workbook, strFileName);
}
catch (Exception ex)
{
LogInfo.Log(ex);
throw ex;
}
}
/// <summary>
/// 解析Excel模板，返回WorkBook
/// </summary>
/// <param name="templetPath"></param>
/// <returns></returns>
private static HSSFWorkbook getWorkBook(string templetPath)
{
FileStream file = new FileStream(templetPath, FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook = new HSSFWorkbook(file);
return workbook;
}
/// <summary>
/// 返回Sheet
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
private static HSSFSheet getSheet(HSSFWorkbook workbook)
{
return workbook.GetSheetAt(0);
}
/// <summary>
///
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="dtSource"></param>
/// <param name="strFileName"></param>
/// <param name="rowHeight"></param>
/// <param name="startRow"></param>
/// <param name="size"></param>
private static void writeData(HSSFWorkbook workbook, HSSFSheet sheet, System.Data.DataTable dtSource, string strFileName, int rowHeight, int startRow)
{
// //填充表头
HSSFRow dataRow = new HSSFRow();
//填充内容
for (int i = 0; i < dtSource.Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + startRow - 1);
dataRow.Height = (short)(rowHeight * 20);
for (int j = 0; j < dtSource.Columns.Count; j++)
{
string drValue = dtSource.Rows[i][j].ToString();
dataRow.CreateCell(j).SetCellValue(drValue);
}
}
}
/// <summary>
/// 保存数据
/// </summary>
/// <param name="workbook"></param>
/// <param name="strFileName"></param>
private static void saveData(HSSFWorkbook workbook, string strFileName)
{
//保存
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
}泛用人形代码复制机 2014-06-23
- 打赏
- 举报
直接覆盖原有excel文件
save4me 2014-06-22
- 打赏
- 举报
参考:
NPOI 插入行
//假如我要在指定的表的第3行插入3行，可以使用这样写：
int InsertRowIndex=2;//指定在第几行插入，我们这里测试用第3行，对应NPOI的索引值2，因为从0起
int InsertRowCount=3;//要插入的行数
HSSFWorkbook Workbook=NPOIOpenExcel(@"E:\TEST\MyExcelTestFile.xls");//打开工作薄
HSSFSheet mySheet= Workbook.GetSheetAt(Workbook.ActiveSheetIndex);//获取工作表
HSSFRow mySourceStyleRow=mySheet.GetRow(InsertRowIndex-1);//获取源格式行
//调用插入行方法
MyInsertRow(mySheet,InsertRowIndex,InsertRowCount,mySourceStyleRow);
//参数说明
//第一个:指定操作的Sheet。
//第二个:指定在第几行指入（插入行的位置）
//第三个:指定要插入多少行
//第四个:源单元格格式的行，
//函数部分：
public HSSFWorkbook NPOIOpenExcel(string FileName)
{
HSSFWorkbook MyHSSFWorkBook;
Stream MyExcelStream = OpenClasspathResource(FileName);
MyHSSFWorkBook = new HSSFWorkbook(MyExcelStream);
return MyHSSFWorkBook;
}
private void MyInsertRow(HSSFSheet sheet, int 插入行, int 插入行总数, HSSFRow 源格式行)
{
#region 批量移动行
sheet
.ShiftRows
(
插入行, //--开始行
sheet
.LastRowNum, //--结束行
插入行总数, //--移动大小(行数)--往下移动
true, //是否复制行高
false, //是否重置行高
true //是否移动批注
);
#endregion
#region 对批量移动后空出的空行插，创建相应的行，并以插入行的上一行为格式源(即：插入行-1的那一行)
for (int i = 插入行; i < 插入行 + 插入行总数 - 1; i++)
{
HSSFRow targetRow = null;
HSSFCell sourceCell = null;
HSSFCell 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);
}
HSSFRow firstTargetRow = sheet.GetRow(插入行);
HSSFCell firstSourceCell = null;
HSSFCell 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
}
Private Sub InsertRows(ByRef targetSheet As HSSFSheet, ByVal fromRowIndex As Integer, ByVal rowCount As Integer)
'将fromRowIndex行以后的所有行向下移动rowCount行，保留行高和格式
targetSheet.ShiftRows(fromRowIndex + 1, targetSheet.LastRowNum, rowCount, True, False, True)
'取得源格式行
Dim rowSource = targetSheet.GetRow(fromRowIndex)
Dim rowstyle = rowSource.RowStyle
For rowIndex = fromRowIndex + 1 To fromRowIndex + rowCount
'新建插入行
Dim rowInsert = targetSheet.CreateRow(rowIndex)
rowInsert.RowStyle = rowstyle
'设置插入行的行高
rowInsert.Height = rowSource.Height
For colIndex = 0 To rowSource.LastCellNum
'新建插入行的所有单元格，并复制源格式行相应单元格的格式
Dim cellSource = rowSource.GetCell(colIndex)
Dim cellInsert = rowInsert.CreateCell(colIndex)
If Not IsNothing(cellSource) Then
cellInsert.CellStyle = cellSource.CellStyle
End If
Next
Next
End Sub
save4me 2014-06-22
- 打赏
- 举报
问题出在你的修改没有保存会Excel。
OpenClasspathResource和WriteToFile函数补上了。下面是我的测试代码(官网下的NPIO 2.0版本)：
using System;
using System.Windows.Forms;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
namespace NPOISample
{
/// <summary>
/// Description of MainForm.
/// </summary>
public class MainForm : Form
{
#region MainForm.Designer
/// <summary>
/// Designer variable used to keep track of non-visual components.
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// Disposes resources used by the form.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
if (disposing) {
if (components != null) {
components.Dispose();
}
}
base.Dispose(disposing);
}
/// <summary>
/// This method is required for Windows Forms designer support.
/// Do not change the method contents inside the source code editor. The Forms designer might
/// not be able to load this method if it was changed manually.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(12, 12);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(75, 23);
this.button1.TabIndex = 0;
this.button1.Text = "插入行";
this.button1.UseVisualStyleBackColor = true;
this.button1.Click += new System.EventHandler(this.Button1Click);
//
// MainForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(128, 46);
this.Controls.Add(this.button1);
this.Name = "MainForm";
this.Text = "NPOISample";
this.ResumeLayout(false);
}
private System.Windows.Forms.Button button1;
#endregion
public MainForm()
{
//
// The InitializeComponent() call is required for Windows Forms designer support.
//
InitializeComponent();
//
// TODO: Add constructor code after the InitializeComponent() call.
//
}
private string DataDir = AppDomain.CurrentDomain.BaseDirectory;
void Button1Click(object sender, EventArgs e)
{
button1.Enabled = false;
int InsertRowIndex=2;//指定在第几行插入，我们这里测试用第3行，对应NPOI的索引值2，因为从0起
int InsertRowCount=3;//要插入的行数
IWorkbook Workbook=NPOIOpenExcel(DataDir + @"MyExcel.xls");//打开工作薄
ISheet mySheet= Workbook.GetSheetAt(Workbook.ActiveSheetIndex);//获取工作表
IRow mySourceStyleRow=mySheet.GetRow(InsertRowIndex-1);//获取源格式行
//调用插入行方法
MyInsertRow(mySheet, InsertRowIndex, InsertRowCount, mySourceStyleRow);
WriteToFile(Workbook, DataDir + @"MyExcel.xls");
button1.Enabled = true;
}
public IWorkbook NPOIOpenExcel(string FileName)
{
IWorkbook MyWorkBook;
Stream MyExcelStream = OpenClasspathResource(FileName);
MyWorkBook = new HSSFWorkbook(MyExcelStream);
return MyWorkBook;
}
/**
* Opens a test sample file from the 'data' sub-package of this class's package.
* @return <c>null</c> if the sample file is1 not deployed on the classpath.
*/
private Stream OpenClasspathResource(String fileName)
{
FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read);
return file;
}
private void WriteToFile(IWorkbook workbook, String fileName)
{
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Write);
workbook.Write(file);
file.Close();
}
//参数说明
//第一个:指定操作的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
}
}
}
小和尚到禅师 2014-06-22
- 打赏
- 举报
谢谢你的回答！
但我测试时没有成功，
有两个问题：
我使用的是NPOI 2.0 ，因此提示第一个错误 ShiftRows方法是没有6个参数的重载
无OpenClasspathResource 方法，我用FileOpen代替了。
修改以上两个问题后 ，程序能够运行，但没对Excel进行过任何修改。