About C # operations Excel, copying sheet records

1. First use Npoi, to do, hssfworkbook inside there is a copy method, but this only supports office2003.

The corresponding Xssfworkbook does not have some methods.

And this method to DevExpress Export 2003 of Excel file can not read, an exception, you need to open with Excel, save a second.

varFS =NewFileStream ("C://pivotgrid.xls", FileMode.Open, FileAccess.Read); Hssfworkbook Workbook=NewHssfworkbook (FS); varSheet = workbook. Getsheetat (0) asHssfsheet; varFS2 =NewFileStream ("C://test3.xls", FileMode.Create); varWorkbook2 =NewHssfworkbook (); Sheet. CopyTo (Workbook2,"a",true,true); Workbook2.            Write (FS2); FS2. Close ();

Some people on the internet have written a similar approach to POI:

http://blog.csdn.net/wutbiao/article/details/8696446

 Public classPoiutils {//    /**//* To copy an Excel cellstyletable to another Excel, here will be an error, can not use this method, do not understand it????? //* @param frombook//* @param tobook//     *///Public static void Copybookcellstyle (Hssfworkbook frombook,hssfworkbook tobook) {//For (short i=0;i<frombook.getnumcellstyles (); i++) {//Hssfcellstyle fromstyle=frombook.getcellstyleat (i);//Hssfcellstyle tostyle=tobook.getcellstyleat (i);//if (tostyle==null) {//Tostyle=tobook.createcellstyle ();//            }//Copycellstyle (fromstyle,tostyle);//        }//    }    /** * Copy a cell style to the destination cell style * @param fromstyle * @param tostyle*/     Public Static voidCopycellstyle (Hssfcellstyle fromstyle, Hssfcellstyle tostyle) {tostyle.setalignment (FromStyle.getA        Lignment ()); //border and border colorTostyle.setborderbottom (Fromstyle.getborderbottom ());        Tostyle.setborderleft (Fromstyle.getborderleft ());        Tostyle.setborderright (Fromstyle.getborderright ());        Tostyle.setbordertop (Fromstyle.getbordertop ());        Tostyle.settopbordercolor (Fromstyle.gettopbordercolor ());        Tostyle.setbottombordercolor (Fromstyle.getbottombordercolor ());        Tostyle.setrightbordercolor (Fromstyle.getrightbordercolor ());                Tostyle.setleftbordercolor (Fromstyle.getleftbordercolor ()); //Background and ProspectsTostyle.setfillbackgroundcolor (Fromstyle.getfillbackgroundcolor ());                Tostyle.setfillforegroundcolor (Fromstyle.getfillforegroundcolor ());        Tostyle.setdataformat (Fromstyle.getdataformat ()); Tostyle.setfillpattern (Fromstyle.getfillpattern ());//Tostyle.setfont (Fromstyle.getfont (null));Tostyle.sethidden (Fromstyle.gethidden ()); Tostyle.setindention (Fromstyle.getindention ());//First Line Indenttostyle.setlocked (fromstyle.getlocked ()); Tostyle.setrotation (Fromstyle.getrotation ());//Rotatetostyle.setverticalalignment (Fromstyle.getverticalalignment ());            Tostyle.setwraptext (Fromstyle.getwraptext ()); }    /** * Sheet copy * @param fromsheet * @param toSheet * @param copyvalueflag*/     Public Static voidCopysheet (Hssfworkbook wb,hssfsheet fromsheet, Hssfsheet toSheet, Boolean copyvalueflag) {//Merge Area processingmergerregion (FromSheet, ToSheet);  for(Iterator Rowit =fromsheet.rowiterator (); Rowit.hasnext ();) {Hssfrow Tmprow=(Hssfrow) rowit.next (); Hssfrow NewRow=Tosheet.createrow (Tmprow.getrownum ()); //Row ReplicationCopyrow (Wb,tmprow,newrow,copyvalueflag); }    }    /** * Line copy function * @param fromrow * @param torow*/     Public Static voidCopyrow (hssfworkbook wb,hssfrow fromrow,hssfrow torow,boolean copyvalueflag) { for(Iterator cellit =fromrow.celliterator (); Cellit.hasnext ();) {Hssfcell Tmpcell=(Hssfcell) cellit.next (); Hssfcell Newcell=Torow.createcell (Tmpcell.getcellnum ());        Copycell (Wb,tmpcell, Newcell, Copyvalueflag); }    }    /** * Copy the merged cell of the original sheet to the newly created sheet * * @param sheetcreat New Creation Sheet * @param sheet original Sheet*/     Public Static voidmergerregion (Hssfsheet fromsheet, Hssfsheet toSheet) {intSheetmergercount =fromsheet.getnummergedregions ();  for(inti =0; i < Sheetmergercount; i++) {Region Mergedregionat=Fromsheet.getmergedregionat (i);       Tosheet.addmergedregion (Mergedregionat); }    }    /** * Copy cells * * @param srccell * @param distcell * @param copyvalueflag * true with CEL The contents of L are copied together*/     Public Static voidCopycell (Hssfworkbook wb,hssfcell Srccell, Hssfcell Distcell, Boolean copyvalueflag) {Hssfcellstyl E NewStyle=Wb.createcellstyle ();        Copycellstyle (Srccell.getcellstyle (), NewStyle);        Distcell.setencoding (Srccell.getencoding ()); //styleDistcell.setcellstyle (NewStyle); //Reviews        if(Srccell.getcellcomment ()! =NULL) {distcell.setcellcomment (srccell.getcellcomment ()); }        //different data type processing        intSrccelltype =Srccell.getcelltype ();        Distcell.setcelltype (Srccelltype); if(copyvalueflag) {if(Srccelltype = =hssfcell.cell_type_numeric) {                if(hssfdateutil.iscelldateformatted (Srccell)) {Distcell.setcellvalue (Srccell.getdatecellvalue ()); } Else{distcell.setcellvalue (Srccell.getnumericcellvalue ()); }            } Else if(Srccelltype = =hssfcell.cell_type_string)            {Distcell.setcellvalue (Srccell.getrichstringcellvalue ()); } Else if(Srccelltype = =Hssfcell.cell_type_blank) {                //Nothing21}Else if(Srccelltype = =Hssfcell.cell_type_boolean)            {Distcell.setcellvalue (Srccell.getbooleancellvalue ()); } Else if(Srccelltype = =hssfcell.cell_type_error)            {Distcell.setcellerrorvalue (Srccell.geterrorcellvalue ()); } Else if(Srccelltype = =Hssfcell.cell_type_formula)            {Distcell.setcellformula (Srccell.getcellformula ()); } Else{//nothing29            }        }    }}

2. Using Microsoft's API for replication:

usingSystem;usingMicrosoft.VisualStudio.TestTools.UnitTesting;usingMicrosoft.Office.Interop.Excel;usingExcel =Microsoft.Office.Interop.Excel;usingSystem.Reflection;namespaceunittestproject1{[TestClass] Public classUnittestexcel {[TestMethod] Public voidTestMethod1 () {Excel.Application app=NewExcel.Application (); Excel.Workbook Workbook1= App. Workbooks._open ("c:\\a.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, T Ype.            Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Worksheet Sheet1= Workbook1. worksheets["Sheet1"] asExcel.Worksheet; Excel.Workbook Workbook2= App. Workbooks._open ("c:\\a1.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Ty Pe.            Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Worksheet Sheet2= Workbook2. worksheets["Sheet1"] asExcel.Worksheet; Sheet2.            Copy (Type.Missing, Sheet1); Workbook1.            Save (); Workbook1. Close (false, Type.Missing, Type.Missing); Workbook2. Close (false, Type.Missing, Type.Missing); }    }}

About C # operations Excel, copying sheet records