top of page
Search

NPOI how to insert a row of information into an current Excel file. Txt

In a lot of examples, when working with NPOI to write data to Excel, a new file is created, or perhaps a new file is created via a template, and after that the information is written to output a brand new file.


Now you only want to insert a few pieces of data within the current Excel, how to attain?





Reply to discussion (resolution)

reference:



NPOI insert row



//If I wish to insert three rows inside the third row from the specified table, I can use this to create:

intInsertRowIndex=2;//Specify the number of rows to insert, we make use of the third row here for testing, corresponding for the index value of NPOI 2, because from 0

intInsertRowCount=3;//The number of rows to become inserted

HSSFWorkbookWorkbook=NPOIOpenExcel(@'E:\\TEST\\MyExcelTestFile.xls');//Open workbook

HSSFSheetmySheet=Workbook.GetSheetAt(Workbook.ActiveSheetIndex);//Get worksheet

HSSFRowmySourceStyleRow=mySheet.GetRow(InsertRowIndex-1);//Get supply format line

//Call the insert row process

MyInsertRow(mySheet, InsertRowIndex, InsertRowCount, mySourceStyleRow);

//Parameter Description

//The initial 1: specify the Sheet on the operation.

//Second: specify in which line to point in (insert line position)

//The third: specify how many rows to insert

//Fourth: the row inside the supply cell format,

//Function element:

publicHSSFWorkbookNPOIOpenExcel(stringFileName)


HSSFWorkbookMyHSSFWorkBook;

StreamMyExcelStream=OpenClasspathResource(FileName);

MyHSSFWorkBook=newHSSFWorkbook(MyExcelStream);

returnMyHSSFWorkBook;


privatevoidMyInsertRow (HSSFSheetsheet, int insert row, int insert row total, HSSFRow source format row)


#region bulk move rows

sheet

.ShiftRows

(

Insert line, //---start line

sheet

.LastRowNum, //---End line

Total number of inserted rows, //-Move size (number of rows)--Move down

true, // whether to copy the row height

false,//Whether to reset the row height

true//Whether to move the annotation

#region Insert blank rows that are vacated after batch movement, create corresponding rows, and format the source as the last row of the inserted row (ie: the row where row -1 is inserted)

for (inti=i insert row + total number of insert rows-i++)


HSSFRowtargetRow=null;

HSSFCellsourceCell=null;

HSSFCelltargetCell=null;

targetRow=sheet.CreateRow(i+

for(intm=source format line. FirstCellNum; m source format line. LastCellNum; m++)


sourceCell=Source format line. 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);


HSSFRowfirstTargetRow=sheet.GetRow (insert row);

HSSFCellfirstSourceCell=null;

HSSFCellfirstTargetCell=null;

for(intm=source format line. FirstCellNum; m supply format line. LastCellNum; m++)


firstSourceCell=Source format line. GetCell(m);

if(firstSourceCell==null)

continue;

firstTargetCell=firstTargetRow.CreateCell(m);

firstTargetCell.Encoding=firstSourceCell.Encoding;

firstTargetCell.CellStyle=firstSourceCell.CellStyle;

firstTargetCell.SetCellType(firstSourceCell.CellType);








Operate Excel employing NPOI to insert rows



PrivateSubInsertRows(ByReftargetSheetAsHSSFSheet, ByValfromRowIndexAsInteger, ByValrowCountAsInteger)

'Move all rows right after the fromRowIndex row down the rowCount row, preserving row height and format

targetSheet.ShiftRows(fromRowIndex+1,targetSheet.LastRowNum,rowCount,True,False,Accurate)

'Get source format line

DimrowSource=targetSheet.GetRow(fromRowIndex)

Dimrowstyle=rowSource.RowStyle

ForrowIndex=fromRowIndex+1TofromRowIndex+rowCount

'New Insert Row

DimrowInsert=targetSheet.CreateRow(rowIndex)

rowInsert.RowStyle=rowstyle

'Set the row height with the inserted row

rowInsert.Height=rowSource.Height

ForcolIndex=0TorowSource.LastCellNum

'Newly insert all of the cells in the row and copy the format on the corresponding cells within the source format row

DimcellSource=rowSource.GetCell(colIndex)

DimcellInsert=rowInsert.CreateCell(colIndex)

IfNotIsNothing(cellSource)Then

cellInsert.CellStyle=cellSource.CellStyle

EndIf

Next

Subsequent

EndSub





reference:



NPOI insert row



//If I desire to insert three rows inside the third row from the specified table, I can use this to write:

intInsertRowIndex=2;//Specify the number of rows to insert, we use the third row here for testing, corresponding towards the index worth of NPOI two, simply because from 0

intInsertRowCount=3;//The number of rows to become inserted

HSSFWorkbookWorkbook=NPOIOpenExcel(@'E:\\TEST\\MyExcelTestFile.xls');//Open workbook

HSSFSheetmySheet=Workbook.GetSheetAt(Workbook.ActiveSheetIndex);//Get worksheet

HSSFRowmySourceStyleRow=mySheet.GetRow(InsertRowIndex-1);//Get supply format line

//Call the insert row system

MyInsertRow(mySheet, InsertRowIndex, InsertRowCount, mySourceStyleRow);

//Parameter Description

//The initial one: specify the Sheet with the operation.

//Second: specify in which line to point in (insert line position)

//The third: specify how many rows to insert

//Fourth: the row in the supply cell format,

//Function portion:

publicHSSFWorkbookNPOIOpenExcel(stringFileName)


HSSFWorkbookMyHSSFWorkBook;

StreamMyExcelStream=OpenClasspathResource(FileName);

MyHSSFWorkBook=newHSSFWorkbook(MyExcelStream);

returnMyHSSFWorkBook;


privatevoidMyInsertRow (HSSFSheetsheet, int insert row, int insert row total, HSSFRow source format row)


#region bulk move rows

sheet

.ShiftRows

(

Insert line, //---start line

sheet

.LastRowNum, //---End line

Total number of inserted rows, //-Move size (number of rows)--Move down

true, // whether to copy the row height

false,//Whether to reset the row height

true//Whether to move the annotation

#region Insert blank rows that are vacated after batch movement, create corresponding rows, and format the source as the last row of the inserted row (ie: the row where row -1 is inserted)

for (inti=i insert row + total number of insert rows-i++)


HSSFRowtargetRow=null;

HSSFCellsourceCell=null;

HSSFCelltargetCell=null;

targetRow=sheet.CreateRow(i+

for(intm=source format line. FirstCellNum; m source format line. LastCellNum; m++)


sourceCell=Source format line. 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);


HSSFRowfirstTargetRow=sheet.GetRow (insert row);

HSSFCellfirstSourceCell=null;

HSSFCellfirstTargetCell=null;

for(intm=source format line. FirstCellNum; m source format line. LastCellNum; m++)


firstSourceCell=Source format line. GetCell(m);

if(firstSourceCell==null)

continue;

firstTargetCell=firstTargetRow.CreateCell(m);

firstTargetCell.Encoding=firstSourceCell.Encoding;

firstTargetCell.CellStyle=firstSourceCell.CellStyle;

firstTargetCell.SetCellType(firstSourceCell.CellType);








Operate Excel making use of NPOI to insert rows



PrivateSubInsertRows(ByReftargetSheetAsHSSFSheet, ByValfromRowIndexAsInteger, ByValrowCountAsInteger)

'Move all rows immediately after the fromRowIndex row down the rowCount row, preserving row height and format

targetSheet.ShiftRows(fromRowIndex+1,targetSheet.LastRowNum,rowCount,True,False,Correct)

'Get source format line

DimrowSource=targetSheet.GetRow(fromRowIndex)

Dimrowstyle=rowSource.RowStyle

ForrowIndex=fromRowIndex+1TofromRowIndex+rowCount

'New Insert Row

DimrowInsert=targetSheet.CreateRow(rowIndex)

rowInsert.RowStyle=rowstyle

'Set the row height of your inserted row

rowInsert.Height=rowSource.Height

ForcolIndex=0TorowSource.LastCellNum

'Newly insert all of the cells inside the row and copy the format with the corresponding cells within the source format row

DimcellSource=rowSource.GetCell(colIndex)

DimcellInsert=rowInsert.CreateCell(colIndex)

IfNotIsNothing(cellSource)Then

cellInsert.CellStyle=cellSource.CellStyle

EndIf

Next

Subsequent

EndSub






Thank you for your answer!


But I didn't succeed inside the test,


There are actually two challenges:


I'm employing NPOI2.0, so the first error is the fact that the ShiftRows technique is overloaded devoid of 6 parameters


There is certainly no OpenClasspathResource approach, I use FileOpen rather.


Immediately after modifying the above two troubles, the plan can run, but no adjustments have been made to Excel.




The problem is that your adjustments aren't saved in Excel.


OpenClasspathResource and WriteToFile functions have already been added. The following is my test code (NPIO2.0 version below the official site):



usingSystem;

usingSystem.Windows.Forms;

usingSystem.IO;

usingNPOI.SS.UserModel;

usingNPOI.HSSF.UserModel;

namespaceNPOISample


///summary

///DescriptionofMainForm.

////summary

publicclassMainForm:Form


///summary

///Designervariableusedtokeeptrackofnon-visualcomponents.

////summary

privateSystem.ComponentModel.IContainercomponents=null;

The

///summary

///Disposesresourcesusedbytheform.

////summary

///paramname='disposing'trueifmanagedresourcesshouldbedisposed;otherwise,false./param

protectedoverridevoidDispose(booldisposing)


if(disposing)

if(components!=null)

components.Dispose();



base.Dispose(disposing);


The

///summary

///ThismethodisrequiredforWindowsFormsdesignersupport.

///Donotchangethemethodcontentsinsidethesourcecodeeditor.TheFormsdesignermight

///notbeabletoloadthismethodifitwaschangedmanually.

////summary

privatevoidInitializeComponent()


this.button1=newSystem.Windows.Forms.Button();

this.SuspendLayout();

//

//button1

//

this.button1.Location=newSystem.Drawing.Point(12,

this.button1.Name='button1';

this.button1.Size=newSystem.Drawing.Size(75,

this.button1.TabIndex=

this.button1.Text='insert row';

this.button1.UseVisualStyleBackColor=true;

this.button1.Click+=newSystem.EventHandler(this.Button1Click);

//

//MainForm

//

this.AutoScaleDimensions=newSystem.Drawing.SizeF(6F,12F);

this.AutoScaleMode=System.Windows.Forms.AutoScaleMode.Font;

this.ClientSize=newSystem.Drawing.Size(128,

this.Controls.Add(this.button1);

this.Name='MainForm';

this.Text='NPOISample';

this.ResumeLayout(false);


privateSystem.Windows.Forms.Buttonbutton1;

The

publicMainForm()


//

//TheInitializeComponent()callisrequiredforWindowsFormsdesignersupport.

//

InitializeComponent();

The

//

//TODO:AddconstructorcodeaftertheInitializeComponent()call.

//


The

privatestringDataDir=AppDomain.CurrentDomain.BaseDirectory;

The

voidButton1Click(objectsender, EventArgse)


button1.Enabled=false;

intInsertRowIndex=2;//Specify the number of rows to insert, we use the third row here for testing, corresponding to the index value of NPOI 2, because from 0

intInsertRowCount=3;//The number of rows to be inserted

IWorkbookWorkbook=NPOIOpenExcel(DataDir+@'MyExcel.xls');//Open the workbook

ISheetmySheet=Workbook.GetSheetAt(Workbook.ActiveSheetIndex);//Get worksheet

IRowmySourceStyleRow=mySheet.GetRow(InsertRowIndex-1);//Get source format line

//Call the insert row method

MyInsertRow(mySheet, InsertRowIndex, InsertRowCount, mySourceStyleRow);

WriteToFile(Workbook,DataDir+@'MyExcel.xls');

button1.Enabled=true;


The

publicIWorkbookNPOIOpenExcel(stringFileName)


IWorkbookMyWorkBook;

The

StreamMyExcelStream=OpenClasspathResource(FileName);

MyWorkBook=newHSSFWorkbook(MyExcelStream);

The

returnMyWorkBook;


/**

*Opensatestsamplefilefromthe'data'sub-packageofthisclass'spackage.

*@returncnullifthesamplefileis1notdeployedontheclasspath.

*/

privateStreamOpenClasspathResource(StringfileName)


FileStreamfile=newFileStream(fileName, FileMode.Open, FileAccess.Read);

returnfile;


The

privatevoidWriteToFile(IWorkbookworkbook,StringfileName)


//Writethestreamdataofworkbooktotherootdirectory

FileStreamfile=newFileStream(fileName,FileMode.Open,FileAccess.Write);

workbook.Write(file);

file.Close();


\t\t//Parameter Description

//The first one: specify the Sheet of the operation.

//Second: specify in which line to point in (insert line position)

//The third: specify how many rows to insert

//Fourth: the row in the source cell format,

privatevoidMyInsertRow (ISheetsheet, int insert row, int insert row total, IRow source format row)


#region bulk move rows

sheet.ShiftRows(

Insert line, //---start line

sheet.LastRowNum,//--End line

Total number of inserted rows, //-Move size (number of rows)--Move down

true, // whether to copy the row height

false//,//Whether to reset the row height

//true//Whether to move the annotation

);

#region Insert blank rows that are vacated after batch movement, create corresponding rows, and format the source as the last row of the inserted row (ie: the row where row -1 is inserted)

for (inti=i insert row + total number of insert rows-i++)


IRowtargetRow=null;

ICellsourceCell=null;

ICelltargetCell=null;

targetRow=sheet.CreateRow(i+

for(intm=source format line. FirstCellNum; m source format line. LastCellNum; m++)


sourceCell=Source format line. GetCell(m);

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);


IRowfirstTargetRow=sheet.GetRow (insert row);

ICellfirstSourceCell=null;

ICellfirstTargetCell=null;

for(intm=source format line. FirstCellNum; m source format line. LastCellNum; m++)


firstSourceCell=Source format line. GetCell(m);

if(firstSourceCell==null)

continue;

firstTargetCell=firstTargetRow.CreateCell(m);

//firstTargetCell.Encoding=firstSourceCell.Encoding;

firstTargetCell.CellStyle=firstSourceCell.CellStyle;

firstTargetCell.SetCellType(firstSourceCell.CellType);









Thank you for the answer!


But lost flash drive data recovery didn't succeed in the test,


You'll find two challenges:


I am utilizing NPOI2.0, so the very first error is the fact that the ShiftRows method is overloaded without having 6 parameters


There is certainly no OpenClasspathResource strategy, I use FileOpen alternatively.


Immediately after modifying the above two problems, the program can run, but no modifications have been made to Excel.



Directly overwrite the original excel file



///summary

///Export Excel with template

////summary

///paramname='table'/param

///paramname='strFileName' export path/param

///paramname='templetPath' template path/param

///paramname='startRow' write data from the first handful of lines, start out from 1/param

publicstaticvoidExportExcelByTemple(Technique.Data.DataTabledtSource,stringstrFileName,stringtempletPath,introwHeight,intstartRow)


try


HSSFWorkbookworkbook=getWorkBook(templetPath);

HSSFSheetsheet=getSheet(workbook);

writeData(workbook,sheet,dtSource,strFileName,rowHeight,startRow);

saveData(workbook,strFileName);


catch(Exceptionex)


LogInfo.Log(ex);

throw



///summary

///Analyze the Excel template and return to WorkBook

////summary

///paramname='templetPath'/param

///returns/returns

privatestaticHSSFWorkbookgetWorkBook(stringtempletPath)


FileStreamfile=newFileStream(templetPath, FileMode.Open, FileAccess.Read);

HSSFWorkbookworkbook=newHSSFWorkbook(file);

returnworkbook;


///summary

///Back to Sheet

////summary

///paramname='workbook'/param

///returns/returns

privatestaticHSSFSheetgetSheet(HSSFWorkbookworkbook)


returnworkbook.GetSheetAt(0);


///summary

///

////summary

///paramname='workbook'/param

///paramname='sheet'/param

///paramname='dtSource'/param

///paramname='strFileName'/param

///paramname='rowHeight'/param

///paramname='startRow'/param

///paramname='size'/param

privatestaticvoidwriteData(HSSFWorkbookworkbook,HSSFSheetsheet,Technique.Data.DataTabledtSource,stringstrFileName,introwHeight,intstartRow)


////Fill the header

HSSFRowdataRow=newHSSFRow();

//Fill content

for(inti=idtSource.Rows.Count;i++)


dataRow=sheet.CreateRow(i+startRow-

dataRow.Height=(short)(rowHeight*

for(intj=jdtSource.Columns.Count;j++)


stringdrValue=dtSource.Rows[i][j].ToString();

dataRow.CreateCell(j).SetCellValue(drValue);




///summary

///save information

////summary

///paramname='workbook'/param

///paramname='strFileName'/param

privatestaticvoidsaveData(HSSFWorkbookworkbook,stringstrFileName)


//save

using(MemoryStreamms=newMemoryStream())


using(FileStreamfs=newFileStream(strFileName, FileMode.Create, FileAccess.Write))


workbook.Write(fs);




May well be interested

[Original]. Net generate Excel file (insert information, modify format, generate chart) approach. [Original] .Net make Excel file (insert data, modify format, produce chart) process. Net build Excel file (insert information, The approach of modifying format and producing chart) (Microsoft.Workplace.Interop.Excel) speak about .NET technology, the strategy of .NET creating Excel file (inserting data, modifying format, generating chart). The way to export DataGridview data in .Net to excel sheet hdu1045FireNet (one particular line becomes a number of lines, 1 column becomes a number of columns, minimum vertex coverage). Net utilizes config file to save details. Objects in .NET database connection. MVC information verification doubt remedy in .net From: http://bbs. csdn.net/topics/390817921

 
 
 

Recent Posts

See All

Bình luận

bottom of page