Datagridview output or save as an Excel file (more than 65536 rows of Multi-sheet output are supported)

///   <Summary>

///Export data from the datagridview control to excel to set the number of rows for each sheet.
///Create multiple worksheets to load more data
/// </Summary>

/// <Param name = "exportgrid">Datagridview Control</Param>

/// <Param name = "fullfilename">Path of the saved file</Param>

/// <Param name = "sheetrowscount">Number of rows per sheet</Param>

/// <Param name = "isopenfile">Open file?</Param>

/// <Returns>True/false</Returns>

Public BoolOutputfiletoexcel (datagridview exportgrid,StringFullfilename,IntSheetrowscount,BoolIsopenfile)

{

IntId =0;

BoolExportsuccess =False;

//If the grid has not been bound to data
If(Exportgrid =Null)

{

Return False;

}

////Excel2003 worksheet size: 65,536 rows multiplied by 256 Columns

//If (exportgrid. Rows. Count> 65536 | exportgrid. columncount> 256)

//{

//Return false;

//}

//Column index, row Index
IntColindex =0;

IntRowindex =0;

IntObjcetrowindex =0;

//Total number of visible columns and total number of visible rows
IntColcount = exportgrid. Columns. getcolumncount (maid. Visible );

IntRowcount = exportgrid. Rows. getrowcount (maid. Visible );

If(Rowcount =0| Colcount =0)//If no row exists in the dview, return
{

Return False;

}

//Create an Excel Object

Microsoft. Office. InterOP. Excel. Application xlapp =NewMicrosoft. Office. InterOP. Excel. Application ();

If(Xlapp =Null)

{

Return False;

}

//Create an Excel worksheet
Microsoft. Office. InterOP. Excel. Workbook xlbook = xlapp. workbooks. Add (True);

Microsoft. Office. InterOP. Excel. Range =Null;

Intptr T =NewIntptr (xlapp. hwnd );

 

ObjectOmissing = system. reflection. Missing. value;

IntJlevel =0;

Jlevel =Int. Parse (math. Ceiling (exportgrid. rowcount +0.00)/Sheetrowscount). tostring ());

Xlbook. worksheets. Add (omissing, omissing, jlevel-1, Omissing );

For(IntI =1; I <xlbook. worksheets. Count +1; I ++)

{

(Microsoft. Office. InterOP. Excel. worksheet) xlbook. worksheets [I]). Name ="Data Table"+ I. tostring ();

}

For(IntJ =1; J <jlevel +1; J ++)

{

Colindex =0;

Objcetrowindex =0;

Microsoft. Office. InterOP. Excel. worksheet xlsheet = (Microsoft. Office. InterOP. Excel. worksheet) xlbook. worksheets. get_item ("Data Table"+ J. tostring ());

// Create cache data
Object [,] objdata = New Object [sheetrowscount + 1 , colcount];

//Obtains the column title. Hidden columns are not processed.
For(IntI =0; I <exportgrid. columncount; I ++)

{

If(Exportgrid. Columns [I]. Visible)

{

Objdata [objcetrowindex, colindex ++] = exportgrid. Columns [I]. headertext;

If(Exportgrid. Columns [I]. valuetype. tostring () ="System. String")

{

// set it to text, which effectively prevents the front 0 from being automatically deleted
range = xlsheet. get_range (xlsheet. cells [ 1 , colindex], xlsheet. cells [sheetrowscount + 1 , colindex]);

Range. numberformat ="@";

}

}

}

 

For(IntI = (j-1) * Sheetrowscount; I <sheetrowscount * j; I ++)

{

Rowindex ++;

Objcetrowindex ++;

Colindex =0;

For(IntK =0; K <exportgrid. columncount; k ++)

{

If(Exportgrid. Columns [K]. Visible)

{

Objdata [objcetrowindex, colindex ++] = exportgrid [K, rowindex-1]. Value;

}

}

Application. doevents ();

 

 

If(I> = exportgrid. rowcount-1)

{

Break;

}

 

}

//Write to excel

Range = xlsheet. get_range (xlsheet. cells [1,1], Xlsheet. cells [sheetrowscount +1, Colcount]);

Range. value2 = objdata;

//Set the column header format
Range = xlsheet. get_range (xlsheet. cells [1,1], Xlsheet. cells [1, Colcount]);

Range. Font. Bold =True;

Range. horizontalalignment = Microsoft. Office. InterOP. Excel. constants. xlcenter;

 

//Set the report table to the optimum width
Xlsheet. cells. entirecolumn. autofit ();

Xlsheet. cells. verticalalignment = Microsoft. Office. InterOP. Excel. constants. xlcenter;

Xlsheet. cells. horizontalalignment = Microsoft. Office. InterOP. Excel. constants. xlleft;

Xlsheet. usedrange. Borders. linestyle = Microsoft. Office. InterOP. Excel. xllinestyle. xlcontinuous;

}

 

//Save
Try

{

Xlbook. Saved =True;

Xlbook. savecopyas (fullfilename );

Exportsuccess =True;

}

Catch

{

Exportsuccess =False;

}

Finally

{

//Release resources and Close processes
Xlapp. Quit ();

Getwindowthreadprocessid (t,OutID );

System. Diagnostics. PROCESS p = system. Diagnostics. process. getprocpolicyid (ID );

P. Kill ();

}

If(Isopenfile =True)

{

HS. Audit. utilite. fileoperate fo =NewHS. Audit. utilite. fileoperate ();

Fo. openfile (fullfilename );

}

ReturnExportsuccess;

}