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();
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);
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()
{
InitializeComponent();
}
private
string
DataDir = AppDomain.CurrentDomain.BaseDirectory;
void
Button1Click(
object
sender, EventArgs e)
{
button1.Enabled =
false
;
int
InsertRowIndex=2;
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;
}
private
Stream OpenClasspathResource(String fileName)
{
FileStream file =
new
FileStream(fileName, FileMode.Open, FileAccess.Read);
return
file;
}
private
void
WriteToFile(IWorkbook workbook, String fileName)
{
FileStream file =
new
FileStream(fileName, FileMode.Open, FileAccess.Write);
workbook.Write(file);
file.Close();
}
private
void
MyInsertRow(ISheet sheet,
int
插入行,
int
插入行总数, IRow 源格式行)
{
#region 批量移动行
sheet.ShiftRows(
插入行,
sheet.LastRowNum,
插入行总数,
true
,
false
);
#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.CellStyle = sourceCell.CellStyle;
targetCell.SetCellType(sourceCell.CellType);
}
}
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.CellStyle = firstSourceCell.CellStyle;
firstTargetCell.SetCellType(firstSourceCell.CellType);
}
#endregion
}
}
}